Advanced SQL Tutorial

[Music] all right so today we're gonna be talking about some advanced sequel concepts so let's just jump right in so let's suppose we got a requirement from the business where they want you to run a report or run a query that shows them the product ID the amount sold at the stores and the amount sold at EECOM now let's suppose that your store sales and your EECOM sales are in separate tables okay so let's take a look at these two tables here let's run this query ok now we can see they have the prot each table has Product ID description color and quantity but you can see some products have there have in common like Row one product ID seven one one is in both a comment store but these other products are exclusively sold either in store or an econ ok which means you're not going to find a matching record if you were to do an inner join on these two tables by product so let's good but let's go ahead and see what that looks like anyway so here we have taken we are energized so let's run this see what it looks like and as you can see as you might expect you're only going to get matching records product 7 1 1 so that's not gonna work so now let's left outer join these tables and see what that looks like alright so now as you can see this didn't work either because now you only have matching records right product IDs 7 1 1 or all the records from the left table the stores table which is product IDs 7 2 5 & 7 2 3 so that didn't work either so what can we do in this case right in this situation you'd want to use a full outer join so let's change left to full and let's rerun this and now you can see we're looking much better we have all the records from all the tables alright let's finish our query here I think we're getting closer so let's type s dot product ID okay let's also bring in our econ product ID field and now let's add s dot quantity as alias this has store quantity then e dot quantity as become quantity now let's run this alright so now we have our store quantity our econ quantity and we don't want these nulls so we can simply do is null comma zero and we'll do the same thing over here is null so this quantity is null make it a zero now let's rerun this again okay now we're looking a little better still but we only want one product ID column we don't want to write the business just wants one so what do we do in this case right so the answer is there's a function called colas okay and what this will do will wrap these in colas and will give an alias as product ID and what this will do is this will take the first occurrence that is not empty okay so in the case where these are both populated it'll just grab the first one from here from the stores right but if the stores is null it will look to this one the econ product ID and if that's on they'll will grab that so now let's run this again and see what this looks like and there you have it these are the results we want so now we've successfully used a full outer join with Col s to get the business the report they wanted all right so now the next thing I want to show you guys is how to use a cross apply and an outer apply now they're similar to inner and outer joins and you can actually use them to join tables in that same way where they really come in handy is when you're using functions okay so what we have here on the left here is this is a is a function that returns rows right it returns records called get non-exclusive product info and basically it's just a function where you pass it the product ID okay and it will get if it's non-exclusive product it will get you the additional attributes okay and by non-exclusive we mean a product that is sold in both the stores and EECOM okay so this is our original query with the full outer join now what we're gonna do here is we're gonna add a cross apply okay and then we're going to add our function here we're gonna add parentheses and we're gonna pass in this coal s value here right this is gonna be our parameter right so that's gonna basically just pass the product ID okay and we're going to give this an alias we're gonna name this any X not exclusive okay and then we're gonna add here just any X dot star so we're just gonna return everything returned by the function okay so let's take a look at what this does so let's run this AHA now you can see it only returns one product that products seven one one okay now why is that well if you remember from our first two queries that is the only product that is not exclusive so that is the only product found in both EECOM and our stores table okay so that's basically like an inner join right it basically by doing a cross apply it in your join to this function and only pulled matching records okay so now let's change this to an outer apply okay and then let's see what this looks like let's run this aha and now we got everything right so here's our additional attributes over to the right so you can see it only populated for the non exclusive item that product ID 7 1 1 and where it didn't find any matching rows they would just return nulls so this is similar to an outer join alright so now the next thing I want to show you guys is how to use the intersect keyword okay so now suppose we only have this query here from our store sales table select product and product description right so this gives us all the products in our store sales table but suppose we only wanted to choose products that were also sold in our EECOM table right also found in our income table so let's go ahead and type the word intersect down here and then let's write another query now the columns have to match in this case okay so we'll copy and paste that and we'll select from the EECOM sales by product okay so now let's run this and see what happens uh-huh and as you can see it extracted anything that wasn't also found in our econ sales table now another thing you can do here let's say you wanted the opposite let's say we didn't want any econ products from our query okay we can change intersect to accept and now when we run this we'll get everything but product seven-11 okay so that took everything that wasn't found in our econ table okay so that's intersect and accept all right so now the last thing I want to show you is how to do a pivot all right so what we're gonna start with a simple query here we just want to count the number of our products and or any urn each category right so here we're just selecting the category count of products and we're just joining our product table to our category table that's it and we're grouping by product that's it so simple query here these are results but let's suppose we want to we don't want this as rows right let's suppose we want our each of our categories to be a column right so we kind of want to transpose the rows to the columns okay so how would you do this all right so the answer is you'd use a pivot so let's see how this works so the first thing I'm going to do is I'm gonna grab my join condition here just so I don't have to retype it then we're gonna come over to a new query window and we're gonna do we're gonna select well I'll paste my join condition but we're gonna one select our products and categories okay so we will select our P dot product ID and C dot name as category okay so now let's run this okay so now you can see just selects a list about products and their categories so all our products and all our categories that's all this does okay but now we're gonna wrap this in another select okay so this is gonna be a dry like act like a derived table basically is what it's called okay so we'll wrap this in parenthesis and we'll do select star from okay now we'll elías this sub-query okay st space we'll use the t we'll use the key word pivot and then we'll open another parenthesis and what do we want to do what do we want our values to be well we want them to be a count of products so we'll count products here and then we'll do four for category alright so I want to count the products for each category and then we'll type in now we'll open another parentheses here and this is where we have to list each category out so this is how we're gonna turn our rows into columns to do but to do that you have to enter the actual values okay so within brackets so we'll enter brackets here and that will just cut and paste our values so we know components is one cat one column we're gonna want let's scroll down here we know bikes it's another column we're gonna need that's another category accessories there it is that's another one and finally clothing okay now we'll close parentheses close them again and we'll alias this as count of products okay and now let's run that and there you have it so now you can see we've turned our rows into columns but now let's break this query down for a second here just to make sure you understand it okay so what this first query is doing here this is feeding our pivot statement over here okay so it's counting the products from this query for each category in this query where the where these are found okay it's applying this count of products to each of these categories okay and then we're just selecting everything from there okay all right so that'll do it thank you for watching you

Channel: James Oliver

Views: 66,936

Keywords: advanced sql tutorial, sql server tutorial, sql tutorial, full outer join, sql pivot, sql cross apply outer apply

Id: vDMehWw11wI

Length: 11min 25sec (685 seconds)

Published: Mon Oct 21 2019