Advanced SQL Tutorial | Stored Procedures + Use Cases
Вставка
- Опубліковано 25 чер 2024
- Take my Full MySQL Course Here: bit.ly/3tqOipr
In today's Advanced SQL lesson we walk through how to use Stored Procedures.
Link to SQL Code: github.com/AlexTheAnalyst/SQL...
____________________________________________
SUBSCRIBE!
Do you want to become a Data Analyst? That's what this channel is all about! My goal is to help you learn everything you need in order to start your career or even switch your career into Data Analytics. Be sure to subscribe to not miss out on any content!
____________________________________________
RESOURCES:
Coursera Courses:
Google Data Analyst Certification: coursera.pxf.io/5bBd62
Data Analysis with Python - coursera.pxf.io/BXY3Wy
IBM Data Analysis Specialization - coursera.pxf.io/AoYOdR
Tableau Data Visualization - coursera.pxf.io/MXYqaN
Udemy Courses:
Python for Data Analysis and Visualization- bit.ly/3hhX4LX
Statistics for Data Science - bit.ly/37jqDbq
SQL for Data Analysts (SSMS) - bit.ly/3fkqEij
Tableau A-Z - bit.ly/385lYvN
Please note I may earn a small commission for any purchase through these links - Thanks for supporting the channel!
____________________________________________
SUPPORT MY CHANNEL - PATREON/MERCH
Patreon Page - / alextheanalyst
Alex The Analyst Shop - teespring.com/stores/alex-the-analyst-shop
____________________________________________
Websites:
GitHub: github.com/AlexTheAnalyst
____________________________________________
All opinions or statements in this video are my own and do not reflect the opinion of the company I work for or have ever worked for
I like how you run the code (before it is fully complete) to show the error messages you expect and then show how to fix said error messages. This really helps with understanding SQL and helps with understanding troubleshooting code in the future. Keep up the content, Alex!
So glad to hear that it's helpful! Thanks for watching! :D
So far partition and stored procedures are the 2 videos that I found difficult. But I've learned a lot from this series. Thanks, Alex!
me too i didn't understand it
I thought I was the only one
same here and still try understand
Same, it’s comforting seeing other people who have the same struggle😅
Keep it in mind like a macro. Like with a macro you type a set of actions and save it under a name or button, stored procedures are like that but you save it only with name. When you want to do the same job again simply EXEC the name and it runs. The advanced step is to give it variables with @ and then it runs with the variables.
I was slightly struggling with it to follow along so here is the steps to actually get the same result:
1) You write a stored procedure: drop table if exists, create table, inset into and select - finish with GO (you can also wrap the whole thing in: BEGIN (all queries) END
2) Run what you wrote (all of it)
3) Exec [store procedure name]
4) Modify - add parameter
5) Run the whole thing again ( I mean the modify query)
6) Exec [store procedure name] [your parameter]
Thank you! I was really stuck on this query, and your comment saved me from struggling too badly.
Thank You 😘
Thank you!!!! Was seriously stuck and frustrated.
Thank you Alex. Your videos are literally the best. You came down to the beginners level and walked us from ground to top.
Cool. This lesson was much more harder than previous one.
I'll check for myself what means "@" and other stuff.. Definitely I'll rewatch this video again.
Thank you for this interesting lesson.
Thank you very much Alex. Your videos are adding much values in my learnings.
Please keep these style of videos coming,very insightful!
So glad to hear that! Thanks for watching! :D
This was more intense than the previous sessions.
you explain this so easily. appreciate this!
Best UA-cam channel for Data Analysts!!❤️🔥
😁😁
Thank you Alex, your content is so useful. I was waiting for your next advanced series. Do you think you could show how to make advanced triggers?
Good idea! I'll look into that and see if I will add it to this series
Great content Alex! Keep up the good work 👍
this tutorial changed my mind am now on the real track for the progress thanks alex>>>
Superb explanation and content, keep it up Alex!
Great video! I've worked with SQL a few times but I still got a lot to learn. This was very helpful!
Great to hear!
You are very 'on point'. Very precise
Yes Alex, Nice work there!
Thank you I’ve been confused on this one for ages
Super video ! Breve y muy práctico. Muy bien explicado. Muchas gracias !
I subscribe straight along the way....
Your tutorial is awesome
I appreciate the fact that you used The Office names here
stored procedure is the most difficult one so far, but explained very well
Your Tutorials are amazing
Hey Alex, thank you for providing quality learning content for the community.
You're more than welcome! Thanks for watching!
Thank you Alex, it was really a good content.
I am definitely rewatching over and over until I fully understand it
Hi Alex, I am a huge fan of your channels. Recently I am looking for a job of data analyst.
Could you please show us some behavior questions which might be asked frequently during the interview session.
Again, thank you so much for your videos, it is really helpful to me.
Hey Alex, Can you show us (a) how to compare two databases and their tables for gap analysis using Python -
(b) and also how to compare text file data with table data using python assuming both have similar table structure...
would appreciate your work and contribution for students and learners.
Thank you very much Alex
Thank you very much for your excellent tutorial video clips. I regularly watch these clips, and they are incredibly beneficial. I am new to the store procedure. Any help creating an SQL server store procedure connecting two servers would be highly appreciated.
Seems more like how we write a function in python. Thanks for the video Alex.
Great tutorial! I had never seen anyone using @ symbols in SQL code before so I wasn’t sure how it changed your code.
Thank you! Yeah this was a fairly simple example of using that - it can get quite complicated! A good place to start though 👍
I am following this series from beginning and now this is the first video that i find difficult to understand 😐 still learning....
Love the database from The Office
Thanks buddy very informative
Hi Alex,
Thank you so much for sharing all this info and Kudos for owning now your own educational SQL platform. O hope this content doesn't disapper!
If i might give you my 2 cents. i think parameter should have his own video or maybe making this one longer with a specified section for parameters.
Bye!
Awesome! Thank you!
Thank you Alex.
Really cool!
It was nice video and easy to understand. Thank you so much
Thanks. Nice explaination
Clear explanation 👏👏👏👏
Thanks for this channel
excellent tutorial...
Hey Alex great video! I was following along and wondered what the differences are when using Postgresql as I tried to run the stored procedure and it didnt work. So how would I be able to create a procedure temp table in postgre or would I just have to create a function instead?
Thanks for sharing
Thanks Alex
Please make video regarding difference between Stored procedures and views.
Thank you 🙏
Thank you for the video, i have a question;;;in 2.54 when trying to create a stored procedure for temp_employees, you did select * from #temp_employee, are you saying to create the procedure, we have to first add the select statement to the query or are we creating the table first then inserting the select statement before we then create the stored procedure?? sorry im just a bit confused and appreciate your response
Thank you for a very informative series. Regarding the Stored Procedure modification, how do you save the modification? Doesn't look like it auto save, and the save options will save the SQL form and not the procedure itself.
It's funny how most SQL tutorials I've encountered use the employee names from The Office haha
This has been an amazing tool to help fortify what I have already learned. My question would be what would cause the opposite to happen? I pulled the code from your GitHub, ran what you ran, and made the same changes. I did this under SQLTutorial instead of Master. Would that cause this to happen?
Thankyou🙏
Thank you Alex... Could we get questions on SQL???
You have to also notice potential parameter sniffing issues when you design your procedure.
Thank you so much for the tutorial. I really appreciate it. I have a question. After I add a parameter to the procedure and execute the procedure again, the result is the same as the procedure without a parameter. No error or warning. It seems that my parameter didn't come into effect. What should I do? Thank you!
You have not mentioned that after modifying the stored procedure we need to execute it in order to ensure the modified stored procedure is the one that runs when we do EXEC Test_Employee
Great video! Could you make one explaining more in deep the Parameters in SQL?
That will be very helpful Alex
Thank you so much, this video was very helpful. I think Stanley feels a little young however. 10/10
Hi Alex, hope you are having a good time
I have a question for you, I have found very helpful the store procedures but in a data analyst role do you think it is also important to learn how to create triggers and functions? thank you
Depends on the role you have - in a previous role I was in I QA'd a lot of that stuff, but didn't write it a lot. Some won't ever touch it. Good to know in my opinion.
I tried relating it to a function how it takes arguments and you can use argument in the function block so whatever you pass as a parameter while calling the fn will replace the values in the function.
I will have to review and practice this one many times. I was asked in an interview if I wrote stored procedures. It was not something I did. Now I will practice based on what you have shown here. Also, how do you reference more than one parameter. Do you add another Where clause, use AND, ?????
I'm looking to optimize where clause using udt and if I can switch from using (not exists(select 1 from @param) or exists (select 1 from @param where value = mainqueryvalue)). Any suggestions?
Is semicolon required to seperate these two code blocks? I see examples with semicolons on the net.
Also, on some websites, Go keyword is used at the end of stored procedure. It is also not a compulsory thing as I see.
When I try to insert value into #temp_employee it says invalid object name 2:33 .
Stored Procedures are somewhat complex. In what context would we need to use it?
how did you do the refreshing?
this lesson is abit hard but i will gett through it
Will you be doing Triggers at all?
what would happen if the parameter @jobtitle already has a default value like where jobtitle ='manager' then we excuuted the procedure like exce temp_employee @jobtitle = 'sales man' does it keep its old value or updates to the new one?
thanks in advance
Do you have the data analyst courses in uedmy, please share it
Thank you for creating all these great tutorials. I have followed along with all of them and have learned a lot. However, in this video, when executing the following last line:
EXEC Temp_Employee @JobTitle = 'Salesman'
My results only showed the message (1 row affected)" without displaying the actual rows affected in the EXEC statement. Any thoughts as to why?
Either way, your tutorials are excellent. They have propelled me further than the Google Analytics Certification program has in regards to SQL. Thank you once again for your invaluable resources!
Thanks Alex for this tutorial. I just have one question: Why do you include the 'GO' statement at the end of the select statement in github code?
Just a habit from my work lol
Awesome as always! Do you have an open email for questions? I just wanted to know if this side project of mine was decent for a portfolio project. I checked out your discord, but I didn't know if that was an appropriate place to ask or get responses as I don't know the background of anyone responding. Or if I asked in your discord would you answer?
Also a friend of mine who is at SWE at a big company said a lot of firms are moving away from stored procedures for hard coding. Is that true? I know you are planning on making some python courses, will that be covered? He doesn't explain things very clearly as he likes to sound super smart by being super technical when talking to me so I have really 0 idea why his claim might be true.
What the... this went a little faster than I expected. Lost track after you wanted to create the Temp_Employee 😅
Time to rewatch
It took me a re-watch to understand. I just need to practice the alter procedures to understand it a little bit better 😄
After you run the Temp_Employee stored procedure, where does the table #temp_employee get created? I can't see it under Tables in the db tree even after refreshing.
even i refresh the stored procedures there is no data appear in stored procedure where did the data go also i created another data temp _employee i cant see the data in stored procedures what shoud i do?
Hey Alex !! Just wondering how insightful it would it be if you could post a video comparing the Google data Analytics Certification and the IBM data analyst certification
I plan on doing that!
@@AlexTheAnalyst yaay ! Thanks :)
H1! for someone who is looking for an entry level data analyst job and has recently finished python for everybody course on coursera , is IBM data analyst professional certificate course better or the GOOGLE data analyst course? please help
where can I find the #temp_employee table you created within the procedure? I could not find it anywhere
How could I do to alter the procedure using mysql? I didn't manage to adjust the syntax.
Hey Alex! there is any way to make other video with different example I know this is an advance video but I don't get it, is so confusing and frustrating. I feel all I did was copy and page and yeah I get the result but still lost with this, thank you
Hye guys, great video as always. I just wondering, why i can't find my stored procedure although i already refresh the 'Stored Procedure ' folder many times. Hope someone can help me. 🙏
Hi Alex, I am stacking in the initial creation of Procedure. Making error in the code when executing it.
Its not validating to Employeedemography word, showing invalid
After modifying the temp_Employee i dont get an error it just shows me the info again. Am I missing something? I deleted and tried again multiple times. :(
Great tutorial. Practicing this on azure and I keep getting this error: 'CREATE PROCEDURE' must be the only statement in the batch. I don't know what it means. Can anyone help?
Hello! When I create 'Stored Procedure', it is created in the Master Database, is there any way to stop this?
Hello Mr.Alex,
Please make videos on projects that make job seekers portfolio strong.
Nice idea
Hello. Can someone explain what '@' means? when, how and where Do we need to attached it? Thanks before.
Difference between stored procedure and an SQL script? They both seem to have same purposes
I supplied an argument and when I ran it, it gave an error. When I tried to exec the procedure and I don't add the parameter it works.
deos anyone know why my procedure temp_employee appear in the master system database instead of in the SQLTutorial database?
Once modified, I execute the parameter query but I get an error that states that I 'Must declare scalar variable "@Salesman" ' . Can anybody help ?
When i modified mine and execute the Temp_Employee i didnt get the error message and im a dummy all i need to do is execute the modified procedure so and now its working
I was mid-training and i lost the training video threads for SSMS its only My SQL now, can anyone help with redirecting ?
Can someone explain when to use MySQL and when to use SSMS
I think storage procedures are like functions in (c++,python,javascript) correct me if i am wrong.
Where to add this. Actualy i didnt understand it.
Done