Expert Level SQL Tutorial

so what does it mean to be a sequel expert I'm gonna show you all right so today we're gonna be talking about expert level sequel now to start things off we're gonna be talking about unions okay not necessarily because this is expert level material but more so because we're gonna build on this with with the next lesson when we talk about recursive queries and such so let's take a look at that would I have up here I have two tables here store sales and e-commerce ales now let's take a look at these so I'm gonna run this okay so now as you can see these are two tables one contains all the orders or sales from our stores that's this query at the top and the other contains all our orders from our online sales so these are two separate tables they're identical as you can see they have the same columns but the only difference is one contains orders from our stores and one contains orders from our econ Channel so now what if we needed to write a query and we need to combine the results from both of these tables what would we do would we join these tables no because we're not looking to join columns right these have the identical columns we don't we don't so joining these tables wouldn't make sense what we want to do is we don't want to join columns we want to join the rows okay and that's where unions come into play so let's take a look at an example let's see what this looks like so let's say we wanted let's say we'll do select sales order ID say you want ordered date and let's say we want subtotal okay now let's say we want to again what we're trying to do is we want to merge the rows we don't want we want this to be an to show up in one query so we're gonna so we're gonna select these three columns and we're gonna say select from and we'll select this from our store sales table and now the keyword we're going to use here is Union ball now there's two different things there's a Union and then there's a Union all the difference is if you just use a regular Union if there are duplicate rows it will eliminate those if you include all here it will include all rows even if there are duplicates in our case it's not going to matter but we'll stick with Union all so now I'm going to copy and paste this okay and then we're gonna add select oops okay alright so here's our final query so we're gonna say select these three fields from store sales Union all I'm gonna select the same fields from our e-commerce a label so now let's run this and see what it looks like and now you can see we have merged sales from our stores in our econ so we have all our rows here but let's make this a little more interesting let's say since these are our store sales we want to add a column here and we'll say store let's say this is gonna be our sales type alright and here we'll hard-code EECOM now let's run this and there you have it so now you can see we added a column here to differentiate our store records from our econ records now the one thing you want to keep in mind here is when you're using a union you must have the same amount of fields right so let's say let's say salesperson ID right so that let's suppose that EECOM did not have the column salesperson ID okay well let's say we wanted that in here from our store side right so let's say that column was not available in econ because there is no salesperson if it if they're online sales right so let's let's run that and see what happens it should fail and it'll fail see because we still have to preserve our grid right so there's got to be the same amount of columns okay so all you do in that case is you would just add a blank and now we can run this and now you can see for our store records we have salesperson ID but for our econ records we have a blank or in this case a zero so that is a union and the reason why that's showing up is zero is because of the data type of salesperson ID is a number that's why so we could so in case you're wondering ok so that's a union pretty straightforward but that's basically that's if you want to combine Rose remember joins are for joining columns you know bringing in columns from different tables in this case unions are for joining rows all right so now we're gonna get into some really advanced sequel here we're going to talk about recursion right and how we can show hierarchies and how we can enter in our query results okay so I have a simple query up on the screen here let's run this and see what we get okay now this is our employee table so let's talk through this real quick to show you so you can understand what we're gonna be trying to do here okay so now this is a simple two employee table right we have employee key that's like our employee ID and I'm just showing first last name and parent employee key so what does that mean that means that guy Gilbert his employee ID is one his parent employee key is 18 that means that his manager is employee 18 right here who's Joe Brown now Joe Brown has a parent employee key of 23 which means that his manager is Peter Krebbs okay now Peter Krebbs has an apparently ID Paran play key of 112 that's all the way down here and that is Ken Sanchez who's ultimately a key is null because he is the CEO so he is the boss so there's no one above him okay so that's the hierarchy we're going to be looking to show with our query here so now let's go ahead and take a look at how we can do this all right so now in order to show this we're gonna use with right what's called a recursive query so let's go ahead and jump right in so I'm gonna come down here I'm gonna write with we'll call this employee hierarchy as open parentheses and now we're gonna define our query now we're going to use our Union okay so let's go ahead and write our first so now that remember there's only one boss okay and he doesn't have a parent employee key it's gonna be null so we're gonna have to write that separately and that's where our Union comes in so let's go ahead and write the quit what the query would look like for our CEO okay and I think this will make sense as we go okay so let's go ahead we're gonna select employee key we'll select okay and then let's say we because we're showing a hierarchy let's say we also want to show the level okay what level they're on like where they are on the tree okay so we'll boss is gonna be a hard coded as a 1 okay and that'll be as level okay great and we'll do from and I'll just copy and paste this table here and then that's where the parent employee key is null no so this I'll put a comment up here is our boss right he is the CEO okay so this is going to be a simple query that stands alone and returns one record and this has to be done separately in our hierarchy because the parent employee key is null so yeah so now we're going to use our Union that we learned that we just learned so we're gonna do Union all and now we're we're gonna write our recurse where we're gonna start to build in our recursion okay so now we're gonna do select the same fields I'll copy in case that only instead of hard-coding a one okay it's going to be eh which is up this will be with the alias we use later dot level okay so we're gonna reuse that field okay plus one so for each each time the query is called recursively it's going to add a 1 it's going to add to that level right so and then we're gonna do from obviously dim employee okay now's where we're gonna join to our cells so we're gonna do an inner join here and we're gonna use this this table that we defined up here right so we're joining two ourselves now okay this is this is where the magic happens so to speak and we're gonna give that an alias of eh employee hierarchy that we used here right on and let's Ilyas this here and this is just a we'll call this so on e dot employee key I'm sorry e H dot employee key equals e dot parents employee key great okay and then oh yeah because we don't want to select our CEO because we already defined him up here we'll do where let's copy this parent employee key is not long is not null okay and now let's close parenthesis so now this is like think of this as like a little table here okay that we've created okay little hierarchy table we've created so now outside these parentheses we can reference this so let's do select star from our employee hierarchy table defined here now let's go ahead and write like actually you know what let's do this UDOT I'm gonna have to fully qualify these because we're joining to a table to our hierarchy okay great and this is well okay now we should be good let's go ahead and run this okay now let's scroll through the results here and see what this looks like so here's our CEO of the boss and now as you can see his employee key is 112 so now next level down these are level 2 employees there that you could see that he is their boss and their level 2 and these are the employees David Bradley Terry Duffy right and the next level down is level 3 employees Amy Albert's right and then next level down is level 4 right so you get the idea on the lowest level looks like five so you see you can see what's happening here okay now but the cool thing about this is let's say we only wanted to see level two employees we can then add a where clause here where level equals two right and now we can run this again and now all we have are level two employees see so pretty cool all right now let's take a step back and talk about this a little bit let's talk about what's happening all right so this first query where we defined our CEO right this is called our anchor query okay and this is where this level field gets materialized right and that's why we're able to call this in our recursive query down here I should also point out that it's called the anchor query because that's really where this recursive process begins right so that first query we're only selecting our CEO who has a parent employee key of 112 right and then when that recursive query runs it's going to join to itself so it's going to join to all the IDS that have a parent ID of 112 okay and that's going to return level two then it's going to run again and it's going to join those level twos to their parent IDs which will be the level threes so hopefully that makes sense and you can see how how this is recursive because sometimes it's difficult to kind of wrap your brain around it by just looking at the query okay now this inner join down here like I said it's kind of where the magic happens right this is where where we're actually calling joining to ourselves okay to do the recursion to increment the level and keep track of what level these employees are at so now let's and like and let you know like I said so this take this this we're calling we're then able to outside the parentheses call this like it was a normal table but keep in mind this is like a temporary table it only exists for the life of the query okay it's not written to your database or anything like that okay just think of it like a temporary table okay right we could do order by level okay and we could run that oops order by and then we have our results down here and you can see our lowest our lowest level is a level five employee okay so now that's that's how to do hierarchical queries with sequel alright so now we're going to talk about partitioning and this is something I personally find really cool so the first thing I'm gonna show you is an example that I think demonstrates what it is how it works and then I'm going to show you kind of a more real-world example of how it can be used so let's let's jump right in all right so we're gonna type a query here and then I'll type the query and I'll kind of explain as I go so we're gonna select okay row number this is a built in season function that you can use to kind of create like sequential row numbers right starting from one to three that kind of thing okay so we're gonna do but let's first let's do from snails snails water header okay results here we're sales person ID is not null and they are we just wanna I'm just trying to filter down the results here so that it's easy to look at that's equal okay so we only want sales in May that actually had we're sold by a salesperson okay so let's continue on with our selects so we're gonna select row number okay over that's the keyword open parentheses and we're gonna say partition by salesperson ID okay then we're gonna do order by order date okay and we're gonna call this as row no let's run this oh and you know what just so let's select some other fields just so we can see we're looking at will do sales person obviously ID and order and date okay since these it this is the field we're partitioning by and this is the field we're sorting by oops okay so we have partition by salesperson ID order by order date and we're just gonna keep an incremental count of the rows so let's see what this does I think this will highlight what's happening here let's run this okay so let's take a look at these results now here's our column row num here's our salesperson ID and order date so what's happening here well you can see row number is incrementing one the first rows one salesperson ID is 274 then two then three then four then it resets to one on a new salesperson ID 275 so now let's scroll down to the next salesperson ID and then you'll see here it reset again at salesperson ID 276 we scroll down and that went all the way up to 31 records right and then then on the next salesperson ID the count reset again and continue to increment so this is how it's so you could see it's partitioning the database on salesperson ID now the other thing I want to point out is our order by was also important because our order by dictated where the row numbers started counting right so in this case the first order date per salesperson will get the first row num so the rights of the earliest date and as you can see right May 2012 I got what May 30 2012 got 1 then at 2 and then 2 that may into that May 1st 2014 got the 3 and the 4 right so it that's that dictated watch records will get that will start the counting right will get the first row num right so that so both parts were important the partitioning obviously and the order by we're also important so you hopefully that that should clearly show you what's going on so now now that we you see how partitioning works with a simple example let's let me show you an example of how you can use this because I think that's really what that will tie this all together ok so now we're gonna start a new query here and this is gonna be more of a real-world example so let's suppose that we want to know every day we want to know what percent of our total orders for that day each order made up right so we want to know what percent of the total do though are those orders for that or is a particular order for that day right so let's take it let's let's start the query here and I think as we go and as I show you it'll make more sense so let's let's start select and we're gonna be doing using our store sales table so sales dot store sales okay and let's say we're going to select our order gate sales order ID so the sales order ID this represents a particular order right and then we want the total do first let's take a look at this to show you what we're gonna be doing here ok so now you can see on May 31st 2011 this order was for this amount ok but we want to know is of all of the total sales on May 31st 2011 what percentage did this did this transaction make up right what percent of the total now in order to do that we need two things we need the total sales for May 31st okay and then we need to divide that by the total due right and that'll give us the percent of the total okay so let's go ahead and do that now now we're gonna eat for that we're gonna use partitioning okay so let's add another column here and we're gonna call this we need the sum of the total dough okay over and we're gonna partition by boarder date okay and this is gonna move and call this as total sales that'll be our total so now let's run this and see what we get okay now let's do this let's order by order date and sales okay let's rerun this okay yeah so now let's take a look at this so now on May 31st we have this sales order day this amount due and then this is our total sales so let's scroll down though let's look for a new date okay here's a new date July 1st as you can see there's our different total sales so our total sales of five hundred fifty one thousand twenty three dollars is repeating for every date right for each date so it's going to just repeat cuz that's the total sales for May 31st and just to show you that it's changes for the next date so on July 1st this is the total sales so if we were to add up all this all of these sales for July 1st it would equal this total 210 thousand 964 okay so now the last thing we need to do is we need to calculate the percent of total so we will take our total due now and we will divide by our sum total we do and again we just need to copy this partition portion right so all we're doing is using the same value here same value here and we're just dividing by the total do right so total 2 divided by our total and we'll call this as percent total oh and then you know what let's add times 100 to make it a true percent okay so now let's run this and there we have it so now here's our results so this on this day May 31st order ID 4 3 659 is 4.2 percent of our total sales of 551 thousand ok if we scroll down you can see so let's go down to this one on July 1st 2011 order ID 4 3 8 4 3 was 17.5% of our total sales for that day of two hundred ten thousand nine sixty four and that amount was 37 thousand 106 so this is a good example a good real-world example of how you can use partitioning and you're in your sequel so hopefully all this made sense if you understand all this and the previous video I made you should be on par with experts at sequel alright so that'll do it please don't forget to Like subscribe and hit the bell and also check out James tech tips comm for more BR related content and thanks for watching you

Channel: James Oliver

Views: 91,232

Keywords: expert sql tutorial, sql tutorial, advanced sql tutorial, super advanced sql, advanced sql examples, expert sql, expert sql examples

Id: C7CPXeEvKN0

Length: 23min 26sec (1406 seconds)

Published: Mon Mar 25 2019