Learn Intermediate SQL in 20 Mins
![*](https://i0.wp.com/allprowebdesigns.com/wp-content/uploads/2023/12/1703592395_maxresdefault.jpg?resize=840%2C430&ssl=1)
Video Title: Learn Intermediate SQL in 20 Mins
What is up everyone welcome to sequel visualized part 2 in this video we’re gonna jump from the basics of sequel into the more intermediate range of sequel and hopefully at the end of this video we’ll all understand the concepts that a data analyst or someone with a
Similar skill set would be able to pick up and use in their daily life so this is sequel visualized part 2 the first thing we’re going to do is just refresh back to what a joint is it’s definitely one of the most important concepts in programming so if you remember the join
We have table 1 and we have table to see there’s are two separate tables but they have a common column and that’s that blue column and if you’ll notice that there’s colors in that blue column that correspond say the colors in the table to column R corresponding to some color
In table 1 even if they’re not exactly matched up in the same row so what’s gonna happen during the join is we’re gonna find that common data in common I’ll say comment a lot and we’re gonna go ahead and link or join the two tables together to create sort of like an ultra
Table you can see kind of that process here the rows move and then the tables join together on their common rows now what I didn’t tell you last time is that there’s a lot more to just you know a simple rearranging of the rows for example if there were duplicate records
In table 2 for example to dark green columns dark green cells in that blue column and we did that join there would actually be it would create two new rows in that new data set so we basically have a duplicate row with all the extra data from table one the table to table
Two here on the right I guys it’s hard to remember there would be duplicate there’d be two different rows to correspond with the two different rows here in table 2 in the is just one of several kind of complications when it comes to joints I don’t think the duplicate thing is that
Hard to grasp if so let me know maybe I’ll do another video on it but I do think that the next topic is going to be a really important thing for you to grasp if you’re going to be using joints in your everyday life so that’s just the
End of our select statement we pull it down we have the data from the two different tables combined into one nice select statement all right the left join is one of the new concepts I’m going to go over in this video and then the left join is a similar to the join obviously
But there’s one crucial difference and that is that the left join is going to make a distinction between data we pull from table one and data we pull from Table two and it makes that distinction based on availability so table two one here is on the left and I did that on
Purpose a left join you have to think about it as reading left to right so basically a left join says that the thing you read first in this case Table one is going to be the left side of the equation and the thing you read second
In this case Table two is going to be the right side so we’re joining on that blue column still but when the left join says is I only want to take all the data from table one I don’t care if it doesn’t match if there’s no match in Table two
I want all that data and in table two we’re gonna do what would be a regular join so if there is no match we’re just gonna lose that data and almost rate here is we kind of noticed that I did something I pulled a fast one on you and
Basically the last two rows here the last row in table one does not match up to the row in table two so when we actually do that do the join Table one is going to arrange itself before that actual join and it’s gonna just leave
Off the rows as it merges with Table one so now at the bottom of our table of art of our combined join table here we just have a whole bunch of nulls so now as we get ready to do our select statement we’re only going to pull down the purple
Column from table 1 and the yellow table will cut from column 2 but we’re gonna leave out we’re just gonna get a null value for that last column that last value there in table 2 in the yellow column so we’ll see that there then how
That works in sequel is it would be null that’s capital and ull if you were to port that into Excel it might just be blank or might be an N a N and some other kind of programming language so that’s the left join in a nutshell in
That with that I think you can do a lot more advanced queries than you could do with just a plain regular join all right the next big concept to go over is the sub query and this is a concept that I’ll admit took me a few tries to really
Grasp it’s kind of when we go from I think it’s a good example of going from level 1 to level 2 sequel programming it’s how you know when you got it you’re ready to start getting getting deep so what is the sub query well the sub query
Is basically a way that we can summarize some data that we need later in the query so we’re gonna we’re gonna summarize data and then query that data that was just summarized let me give you an example this is table 1 we have a bunch of monkeys you know plus 1 point
If you could figure out how I named these guys we have Kenny Stan Kyle one day Wendy Beibei and butters and then we have the nut mount of apples they eat per week so let’s say that I wanted to say to query not you know having a blind
To look at this data what was the monkey that eats the most apples in a week so I would start off with something like selecting the max of apples as the max apples because I’m going to know that value from Table one but then I can’t just select the max
Because I’m not interested in the amount of apples I’m interested in the monkey who’s eating all those apples and costing us so much money at our zoo or whatever so to do that I’m gonna need to keep this result for this result somewhere and use it somewhere else in
My query and that’s where the concept of the sub-query come kind of comes into play so in sequel we’re gonna wrap those in parentheses and we’re going to give it some sort of nickname now I called it sub query but it doesn’t have to be named sub query I just did that because
It’s going to be an easy thing to reference so now that we have this value what sequel does is once it gets to these parentheses it just evaluates that expression and so at the end of it what we’re gonna end up having is the max of
You know the amount of apples the max apples that’s just what Siegel is gonna see there so it’s gonna see that number 45 which is Stan’s max apples but as we look for the monkey who has the most it wheats the most apples we’re going to go
Ahead and build the rest of our query and so what we’re gonna do is we’re gonna select and what we can do it instead of say in a table name we can actually just say the name of that sub query and we will we will select from
That sub query and we will join on to table 1 on the value from the sub query called max apples to table ones apples and when they join together we see we get this this final statement and then we select the column monkey from that resulting joined table so that’s all it
Is to a sub query it’s it’s definitely maybe a little more complicated than that in practice a lot of times but what it does is it opens you up to basically ten times as many possibilities as a regular select statement so the really neat all right the next concept to get
Into is the temp table now the temp table is something similar to the sub query it’s a way to aggregate data in the way that’s going to be useful for you the difference is that when you’re building a temp table it actually exists out in sequel outside
Of your query it’s a little bit like creating those real sequel tables that you probably assume you know some sequel dev somewhere makes but the temp table only exists for the period of your session as you’re querying the data so to see why this might be useful I have
An example of our monkey data set again we have table one which is the old table we just saw on the sub-query example with the monkeys and the number of apples that they eat and then we have table two which is the monkeys with number of oranges they eat because they
Have a multi fruit diverse all fruit diet apparently so in the previous example we wanted to know which monkey ate the most apples but what if we want to know which monkey ate the most fruit in general or what if we wanted to know which monkey ate the
Most apples compared to oranges or you know which monkey ate the second most fruit what there’s a lot of different questions we might want to ask this data and we might be really it might just be helpful to have it aggregated somewhere then we can reference it throughout our
Analysis session and that’s where the temp table comes in so we build a temp table by first declaring this statement right here create temp table and this is all you know sequel this is not my nickname’s and then fruit habits which is my name of the table as and then we
Some sort of we give it some sort of name to create the table and that’s going to be a select statement so as an in this case select star so star means give me every item in the table and if you join tables it’s gonna give you
Every item in both tables in the join of the tables if you want to just do it from one table you could do something like table one dot star and that would give you all the information from table one only but that’s just a little option
If you want it all right so we’re gonna obviously want to join table two on table one on the monkey name so we Table one monkey equals Table two monkey now here there’s definitely a one-to-one between these two columns again I didn’t want to be too crazy complicated but
Just know that if there was no match since we’re doing our inner join rows that don’t have a match in the subsequent table would go away and if there are duplicates we’d create two records or however many duplicates there are in our new joint table so good to
Just refresh on that so finally obviously we want to summarize a little bit so we’re gonna add the table 1 oranges and Table two dot or table 1 got apples table 2 dot oranges and have a full fruit in column and now we have a table in our data that gives us more
Information than just the two different tables separated so now we can I think creates some more simple queries and without having to resort to a whole bunch of sub queries or something like that and we can use this information and get a lot of different summary data from
It so that’s the idea of a temp table not all sequel languages support 10 tables and not all sequel environments support temp tables but some do and if you have that as available as an analyst I think you’ll find that it’s usually really the way to go because it’s really
Flexible it’s something you can still reference multiple times and you only have to create it once so that’s that’s the temp tables and that brings me to the last subject of this video and it’s been a lot of fun so far but this subject I think is is we’re
Geing on you know kind of that middle ground of intermediate like this is this is getting into mmm you know a much higher skill set I think than some of the previous concepts so this is the CTE and that stands for common table expression the common table expression
Is I think you can think of it sort of as a combination of a temp table and a sub-query and what it does is it lets you define some data summarize so something like a sub query would be and it lets you reference that multiple times but it doesn’t do that by
Creating a new table in the workspace like a temp table does it does that by simply creating it during your select statement and then you’re able to reference it multiple times so why will we use this when we could be used as sub-query there’s a few technical
Reasons but the main reason is it’s it’s just going to be a lot simpler to use if your data would gain from accessing the same table several times so let’s say that we wanted to do an example where we wanted to find out which monkey eats the
Most fruit of of our cohort here and to do that obviously as we saw in the last example we’re have to join on monkey table 1 and table 2 and add those two columns of fruit together so let’s first try an example where we try and do that in a sub-query so we’re
Gonna make our first sub query obviously we want a max then let’s get that max fruit value so we’re gonna have to join the columns together and then add the two columns together we’re enjoying the tables together and then add the two columns together to get the max fruit
Number and that’s in our sub query 1 you’ll remember we did the same thing in the sub query example but then so our next step was to join to rejoin the original table on that max fruit value so that we could see which monkey a correspondent to but there’s a little
Problem here and that’s that we don’t have a max fruit column in either table so we’re gonna have to do another sub query we’re gonna have to join select star and then oh man this is gonna get pretty nuts because not only are we gonna have to do another sub query it’s
Gonna have to be super long we’re have to add you know the total of the apples and the oranges together and we’re have to do it from these these two tables join together and trust me a lot of queries can get way more complicated than even that so when you have this
Case where the sub queries are just going to be basically referencing the same exact data set over and over and over it’s much better to use a common table expression or CTE so what does that look like so instead of defining the sub-query in a parenthesis like you do with the
Sub-query with the CTE what you’re gonna do is you’re gonna start out at the beginning of the query by defining the CTE that you’re gonna reference so the and I think that one of the key words here is common it’s something that you can reference over and over again it’s
Like a like a common little table that’s shared in space that your query can access multiple times so in this case I am using the CTE here as a nickname you can call the CTE anything you want I’m just saying CTE here because it’s going to be helpful to remember what it
Is so the first thing you have to do in this expression is define the columns that you’re going to have in your CTE so thinking the sub-query in this case we’re gonna want to have the name of the monkey in the total fruit and then you
Can define that CTE and in this case we’re gonna use kind of that base table it’s actually the exact same table we had in our temp table so it’s going to be monkey but with the exception that we’re not gonna keep the apples in the oranges columns because all we need
Really need is just the monkey and the total fruit to eventually get that monkey who eats the most so we’ll we’ll have the select with the monkey name the total fruit and then we’ll have table 1 joined on table 2 so with that simple common expression we can now see what
Happens to our data it becomes this little CTE with total fruit and monkey together and now we can just simply reference that several times in our select statement so with CTE monkey fruit as then our little table we can now select the monkey from CTE and I’m
Gonna reference it and I gave it a nickname here that’s why it seems like it’s repeated in a weird way it’s from CTE CTE one is my nickname join select max total fruit as max fruit from CTE I give another little nickname here CTE 2 and notice that it is a little
Sub-query yes but it’s a much shorter sub-query now that we’re just referencing that comment ape expression on CTE one or joining on the total fruit which ever monkey has the maxford that’s who we want so we’re gonna pull out that Max fruit first value that’s eighty one and we’re going
To join it back to our table we’re gonna realize oh it’s that monkey stand again and he’s he’s the one eating the most fruits so that is a CTE in a nutshell if I’m ever fill up to the task I might do an advanced sequel programming video in
A which case I might get into some of the more nuanced methods for a CTE there’s a lot more you can do with them than just having a simple easy way to access data but I do think that’s a really good introduction to what a CTU
Teep can be useful for well that’s all I have in this video I hope that this has been helpful I hope it’s been a good summarization of some of the different aggregates and left joins and in ways that you can sort of get a little more creative and pulling data I do think
That sequel is really fun because it allows you to really express yourself and your creativity by limiting you know how much you have to work with it’s all tabular data there you don’t have to like you know create you find some esoteric package it’s all the rules are
Really well-defined and you can do some extremely intricate and exciting things with just those commands in sequel and understanding these intermediate aggregations and left joins there’s a great way to get you there and get you to that expert space well until next time have a great day
-
Sale!
Wireless WIFI Repeater Extender Amplifier Booster 300Mbps
$29.99$14.99 Add to cartWireless WIFI Repeater Extender Amplifier Booster 300Mbps
Categories: Electronics, Wi-Fi Router, Wireless Wi-Fi Extender Tags: 300Mbps, 802.11N, Amplifier, Booster, Extender, mobile wi-fi booster, Remote, WIFI, Wireless, Wireless WIFI, Wireless WIFI Repeater, Wireless WIFI Repeater Extender, Wireless WIFI Repeater Extender Amplifier, Wireless WIFI Repeater Extender Amplifier Booster, Wireless WIFI Repeater Extender Amplifier Booster 300Mbps$29.99$14.99 -
Sale!
Full RGB Light Design Gaming Headset Headphones with Mic
$24.99$14.99 Add to cartFull RGB Light Design Gaming Headset Headphones with Mic
Categories: Electronics, Gaming, Gaming Headsets Tags: Design, Full, Full RGB Light Design Gaming Headset, Full RGB Light Design Gaming Headset Headphones, Full RGB Light Design Gaming Headset Headphones with Mic, Gamer, Gaming, Gaming Headset Headphones, gaming headset wireless, Headphone, Headphones, Headset, Light, Mic, Package, RGB$24.99$14.99 -
Sale!
Wireless BlueTooth Multi-Device Keyboard Mouse Combo
$39.99$19.99 Add to cartWireless BlueTooth Multi-Device Keyboard Mouse Combo
Categories: Electronics, Gaming, Gaming Keyboards, Keyboard Mouse Combos Tags: Combo, Keyboard, keyboard mouse combos, Mouse, MultiDevice, Set, WireKeyboard Mouse Combo, Wireless, Wireless BlueTooth Keyboard Mouse Combo, Wireless BlueTooth Keyboard Mouse Combos, Wireless BlueTooth Multi-Device Keyboard Mouse Combo, Wireless BlueTooth Multi-Device Keyboard Mouse Combos$39.99$19.99 -
Sale!
High Back Leather Executive Adjustable Swivel Gaming Chair with Headrest and Lumbar
$199.99$139.99 Add to cartHigh Back Leather Executive Adjustable Swivel Gaming Chair with Headrest and Lumbar
Categories: Gaming, Gaming Chairs Tags: Adjustable, Chair, computer chairs, Desk, Executive, Gaming, Girl, Headrest, High, High Back Leather Executive Adjustable Swivel Gaming Chair, High Back Leather Executive Adjustable Swivel Gaming Chair with Headrest, High Back Leather Executive Adjustable Swivel Gaming Chair with Headrest and Lumbar, High Back Leather Executive Adjustable Swivel Gaming Chairs, Leather, Lumbar, Office, Racing, Swivel$199.99$139.99 -
Sale!
Professional LED Light Wired Gaming Headphones with Noise Cancelling Microphone
$29.99$19.99 Select optionsProfessional LED Light Wired Gaming Headphones with Noise Cancelling Microphone
SKU: N/A Categories: Electronics, Gaming, Gaming Headsets Tags: Cancelling, Gaming, Gaming Headphones with Noise Cancelling Microphone, gaming headset, Headphones, Headset, LED, Light, Mic, Microphone, Noise, Professional, Professional LED Light Wired Gaming Headphones, Professional LED Light Wired Gaming Headphones with Noise Cancelling Microphone, Wired, Wired Gaming Headphones, Wired Gaming Headphones with Noise Cancelling Microphone$29.99$19.99 -
Sale!
Gaming Desk with LED Lights USB Power Outlets and Charging Ports
$349.99$249.99 Select optionsGaming Desk with LED Lights USB Power Outlets and Charging Ports
SKU: N/A Categories: Computer Desk, Gaming, Gaming Desk Tags: and Charging Ports, Charging, Desk, Desks, Gaming, gaming desk with led lights, Gaming Desks with LED Lights, Home, LED, Lights, Monitor, Office, Outlets, Port, Power, Room, Stand, USB, USB Power Outlets, White, Workstation$349.99$249.99 -
Sale!
Wired Mixed Backlit Anti-Ghosting Gaming Keyboard
$99.99$79.99 Add to cartWired Mixed Backlit Anti-Ghosting Gaming Keyboard
Categories: Electronics, Gaming, Gaming Keyboards Tags: Antighosting, Backlit, Blue, brown, Gaming, Gaming Keyboard, gaming keyboards, gaming keyboards and mouse, Keyboard, Laptop, Switch, Wired, Wired Mixed Backlit Anti-Ghosting Gaming Keyboard, Wired Mixed Backlit Anti-Ghosting Gaming Keyboards, Wired Mixed Backlit Gaming Keyboard$99.99$79.99 -
Sale!
Wireless Bluetooth 5.3 ANC Noise Cancellation Hi-Res Over the Ear Headphones Headset
$119.99$59.99 Add to cartWireless Bluetooth 5.3 ANC Noise Cancellation Hi-Res Over the Ear Headphones Headset
Categories: Electronics, Gaming, Gaming Headsets Tags: 5.3 ANC Noise Cancellation Hi-Res Over the Ear Headphones Headset, ANC, Audio, Bluetooth, Cancellation, Ear, Earphone, gaming headset, Headphones, Headset, Hi-Res Over the Ear Headphones Headset, HiRes, Noise, Wireless, Wireless Bluetooth 5.3 ANC Noise Cancellation Hi-Res Headphones, Wireless Bluetooth 5.3 ANC Noise Cancellation Hi-Res Over the Ear Headphones Headset, Wireless Bluetooth 5.3 ANC Noise Cancellation Hi-Res Over the Ear Headphones Headsets$119.99$59.99 -
Sale!
Wired Sports Gaming Headset Earbuds with Microphone
$19.99$9.99 Select optionsWired Sports Gaming Headset Earbuds with Microphone
SKU: N/A Categories: Gaming, Gaming Headsets Tags: Accessories, Earbud, Earphone, Earphones, Gaming, gaming headset with microphone, Headphones, Headset, IOS, Microphone, Sports, Wired, Wired Sports Gaming Headset Earbuds, Wired Sports Gaming Headset Earbuds with Microphone, Wired Sports Headset Earbuds$19.99$9.99 -
Sale!
150W Universal Multi USB Fast Charger 16 Port MAX Charging Station
$49.99$29.99 Add to cart150W Universal Multi USB Fast Charger 16 Port MAX Charging Station
Categories: Charging Stations, Electronics Tags: 150W, 150W Charging Station, 150W Universal Multi USB Charging Station, 150W Universal Multi USB Fast Charger 16 Port MAX Charging Station, 150W Universal Multi USB Fast Charger 16 Port MAX Charging Stations, 150W Universal Multi USB MAX Charging Station, 16 Port MAX Charging Station, 3.5A, Charger, Charging, Fast, laptop charging stations, Max, Multi, Port, Stand, Station, Universal, USB$49.99$29.99
You're a livesaver, so clear and concise. However, I really got caught off guard by the fact that you chose apples for the monkeys and not bananas. Like, come on man. Obviously monkeys eat bananas, not apples. I hope you enjoyed making me cry and suffer. Anyways, Have a good day.
I know how to do subqueries but the explanation around 8:15 made me forget it 😂 good job on everything else though 🙂
I love the south park reference! haha awesome video
Hey I was wondering why we need join on single table can't we just use where clause in the 1st example of subquery
Could you please explain what is tthe purpose by making two sub- query? Why do we not select max(total_fruit) From CTE? I hope I could get an answer. Thank you so much
4:27 Table_1.Blue_Column should be Table_2.Blue_Column right?
It took me less than 5 seconds to figure out where those names came from. Not sure if this is something I should be proud of…?
Wow this was really helpful for me! Thank you very much! I especially like the visualizations and they make a big difference!
It was nice seeing names of South Park characters in the video
it sounds like you're walking in circles around the mic 😮
This is an excellent video, James, and precisely what I too would consider 'intermediate' topics. The visual element is brilliant. Many thanks for the refresher!
Thanks for this SQL series. Too bad I'm colour-blinded.
Genious video. Thank you SO much for your work. 🙂
Warning: this video covers CTE which belongs to Microsoft Server technology stack. It is not present in many other SQL dialects. For example: Oracle or ClickHouse (based on MySQL) technology doesn't use them.
Real life saver. I've got 2 hours until my exam, and I think I'll get a good score. Thanks! 🙂
WELL DONE. THANKS
Please for the love of all that is good and holy, make some more of these SQL visualized videos for those of us out here who are really struggling as aspiring data analysts who have never coded before and therefore are really struggling with learning SQL. These 4 videos are head and shoulders above any of the other hundreds of SQL tutorial videos on YouTube, LinkedIn Learning and Udemy I have watched in the last 14 months without learning all that much.
Thanks for the recap man
Have to learn this for my new job. Thank you for posting such a thorough walkthrough.
NICEE
Good video. Let's have some more.
I’m confused by the video being theoretical. It would be clearer if we can see the real thing….where do you type these commands? What does the results look like?
Oh my god he killed kenny!
Hi James, your videos are really Awesome. I really like the way these videos help visualise the table content.
Please make more videos.
Hit 👍if you all agree.
DUDE YOU ARE EPIC!!!
Please fix your mic overlay. put a piece of tissue paper on it to keep you from popping our ears when you get too close…
Good content, but definitely check a video out about splitting audio from video, cleaning the audio up with audacity, and then rejoining audio to video.
the page @4:30, is there any typo that need to correct it as table_1.blue = table_2.blue?
25 apples and 18 oranges make up a total of 43 fruits for Kenny, he cheated 🙂 @12:20
lm using borland database engine and subquery is no supported
Your mic is kinda broken :/
it is quite interesting but man, sometimes your syntax is confusing, and like in 14:21, not the entire code is showing. pls consider that for your next videos, thanks
Southpark Yay!!!!!
great video..do you know why left join is called a left join
These are awesome videos!!! Can you please make more and an advanced one too! Visualisation makes it sooo much better! Defo a fan
hi James, do I understand correctly, that @ 3.55 when you specify, "On Table_1.Blue_Column = Table_1.Blue_Column" this indicates that you only want ALL data from Table one?
Thank you for the informative videos! Nice! 😀