Beginner to T-SQL [Full Course]

hello and good morning good afternoon good evening wherever you are my name is mitchell pearson here at pragmatic works and welcome to another learn with the nerds event today we're going to be taking a look at t-sql t-sql is pivotal it's very important to our jobs for anybody who really works with the data and so we felt it was pertinent and important to go ahead and go through a three hour event live with you where we could do an event on sql server all right so let's jump right in and talk a little bit about myself i'm the training manager here at pragmatic works i've been here for about nine and a half years now started off in software then consulting for a few years and i've been working in training for about four years now working with the training department that role includes creating a lot of new classes for our on-demand learning platform and then also teaching and doing classes like what we're doing here today i've been fortunate to author a couple of different books on power bi sql server 2019 when that was released and then also the power platform power bi power apps power automate i blog at mitchellpearson.com i have a wife and three kids here in florida inside of the united states and i specify that because i see we have people from literally all over the world joining us here today and then i enjoy playing tabletop games so if you've ever played ticket to ride or katan or something like that those are things that i enjoy doing obviously you found us through youtube so make sure to take a moment to click that like and subscribe button so that you get notified when we do future events in the future all right so what is the agenda today we have three hours together what are we going to cover first of all we're going to talk a little bit about what is sql right what is sql what is it why is it important we're going to take a look at the select clause and the from clause we're going to look at how do we filter down those queries using the where clause we're also going to take a look at something very important which is using joins the join allows us to combine multiple tables together and then of course we're going to take a look at writing aggregate queries queries where we can return multiple aggregations like sum min max etc and we're going to do that right there within our sql query language the logistics for today is going to be eastern time it's going to be from 11 until 2 p.m eastern time we will take a break around 12 15 eastern time 12 20 and we'll take a 15 minute break that'll give you time to get up and kind of stretch your legs and then also there is a download that i've provided that has some information in it it has pretty much all of the sql scripts that i'm going to run today it also has a pdf document that shows you how to connect to sql server management studio and using the credentials that i'm using how you can use sql server management studio to connect to an azure sql database if you want to try to follow along the only thing that i would warn you if you say hey i think i'm going to try to follow along just make sure you have a second monitor make sure you have kind of some familiarity with what we're working with today so you don't get frustrated because there are going to be probably over a thousand people in this live event and there's just no way we're going to be able to stop and do any kind of troubleshooting so the event will be recorded you can go back and watch it at any time you want on youtube after the fact then you can go back through all of those scripts that i've given you and if you run into problems we can probably try to help you with that after the fact here's a what you're going to get when you download those files you're going to get essentially two things you're going to get the sql scripts folder right here that has all of the sql scripts i'm going to be using today and then we're also going to be connecting or we're going to use this pdf right here that shows you how to use ssms to connect to an azure sql database make sure to remember to put in the database name because i'm using database scoped credentials for this example all right so i'm going to move that back over there those are the files that you're going to want if you want to try to follow along at all at any point today all right sslms is also a requirement we sent that out in the email ssms is a free tool you can go to aka dot lms forward slash ssl ms and download that file you can download it you can install it it's free and then that will allow you to connect you do want to be on one of the newer versions of it because on older versions there's sometimes connectivity issues to certain things in azure so being on a newer version of ssms is going to be very beneficial to you there we go all right trying to watch the chat window a little bit over there as well so let's jump right in i have about three or four slides here and then we're going to jump right into the demos all right so sql databases why do we have sql databases right what is it what does it mean a sql database is a place that we can store and work with all of our organizational data right if you go way way back we used to store all of the data on files on paper in filing cabinets that's obviously not the most effective way to store our information is not the most effective way to retrieve that information and so obviously if you've ever worked with the data today you've probably worked with the database in some form or some fashion and the database gives us a way to not only store the data but to keep it in an organized fashion based on whatever our requirement is whether i'm trying to load data for my web application or whether i'm trying to report from that database for our reports and our dashboards and our machine learning right so that right there is what a database is a database is an organized structure for our data now if we have databases in our organization in the cloud wherever they might be how do we get data into those databases and then how do we get data out of those databases we do that using the structured query language which is sql or some people call it sql i mostly say sql myself but it's the structured query language we use the structured query language to communicate with our databases to insert data in retrieve data from it and also edit existing data who should know sql that's a great question we do a lot of training on microsoft azure we do a ton of training on power bi and it's interesting to me the number of people that are jumping straight to microsoft azure or they're jumping straight to power bi and they've never looked at sql they don't even know what it is so if you work with data if you connect to databases even if it's from excel you should probably know the fundamentals of using sql to connect to a database and retrieve information right that's going to definitely in a lot of use cases make your life a lot easier that's why we're going through this session today so we have somewhere that we can point our customers just to get a quick little ramp up on how to work with sql all right so everybody essentially who's working with data should be working with that so kind of circling back real fast to what is a database a database is a container for data that's structured within our organization for very specific use cases it could be for one or more applications and it could be accessible in a number of various ways maybe we're accessing it from sql ssms or excel or power bi or maybe from some application that's been developed that's going to write data in and pull data out right so those are databases all of us have worked with databases but i wanted to make sure to kind of just jump in there and cover the fundamentals now like i said only a couple slides we're going to jump right in and start writing sql the first thing we need to do is connect and authenticate to the azure sql database so i am going to walk you through those steps i saw a couple of questions coming through and a couple of concerns about that but the first thing i want to do is if you have sql server management studio and you've downloaded it this is the time when we want to open that up on our machine and so i'm going to go ahead and open it up on my machine as well and once you open up sql server management studio you're going to get prompted with this connect box that appears right here this authentication window i'm pausing for just a little bit so you can follow along alright so you're going to see this window right here and this is where you'll put in that authentication information that was provided in the class files that i gave you now in the server name you're going to put in the server name now let's say that you canceled this on accident you're like mitchell how do i get back to that connection screen i'm going to cancel mine for just a moment but don't cancel yours i'll cancel it and then right here under the object explorer i can tell that i want to connect and then i can tell it that i want to connect to my database engine all right database engine there we go and so now we're going to type in here pragmaticworkspublic.database.windows.net this is going to be connecting to my azure sql database that i've opened up so that all of you can follow along if you want keep in mind there's a couple of limitations here one a lot of companies not a lot of companies but some companies will block port 1433 port 1433 is required to be open to connect to azure sql database so if you're on a vpn or you're at your company and you're not able to connect it's possible that your port is blocked by your networking team and for today there's nothing we're going to be able to do to help you so just be aware of that so if you can't connect it might be that so we're going to type in pragmaticworkspublic.database.windows.net and my co-worker manuel will drop that right there in the chat window for you and then we're going to type in our login information right pw student and then the password is pw5tud 3nt once again we're going to drop that in the chat window and that was part of the class files if you went through that pdf document that we provided all right so once you put in your server name and you connect with your username and your password we're not ready to connect yet because we've created something known as a database scoped credential so you have to connect directly to a database in order to use these credentials so the next thing that we're going to do is let me put in mine we're going to go to options here and when you click on options it's going to bring you to connection properties and we're going to tell it specifically what database we are connecting to and it's going to be adventure works all right adventure works hello ricardo i see you there and so once you type in adventure works now you're ready to go you've put in your username your password the server you can click connect and you will be good to go unless either you type something incorrectly or port 1433 is blocked as you can see there's a lot of people in here so if there's an issue we won't be able to troubleshoot it for you today all right so i'm going to leave that up there for just one more moment there and i'm waiting to see information dropping into the chat window so there we go it's coming up right now so you can start copying that out we have about a 30 second delay from live to when you're receiving it i'm going to click connect and if it works i'll be able to connect so let me try that one more time here i'm going to go back over to my login pw student might have typed it in wrong there we go and then we'll click connect again oh it's not working for me did i type in my database oh you know what mine's different than yours so i don't run into problems my fault so yours is adventure works all right so once you've connected now we're connected to the server and we'll be able to see any databases that we have access to right and so if i extend the databases over here on the left by clicking that plus icon i'll be able to see my database and then i can click the plus button again that's going to open up a bunch of information now you don't need to know all of that stuff that's there you don't need to know about stored procedures and views and security all you need to know is how do i find my table so that i can extract data from those tables right so now i'm going to extend expand expand the tables folder and that's going to show me all the tables we have available and we have a lot now based on your organization when you connect to your organizational data you might not see all the data because maybe they block something from you but if you're connecting with me today you should see all the same tables that i'm looking at now what we're going to do is we're going to start walking through the process of actually connecting to our data and writing queries i want to jump right in and get started today right and so for this class the easiest way to query data is actually to come over to the object explorer go down to the table that you want to query so i'm going to use person.person right click on it and then you can tell it that you want to select the top 1000 rows and this will actually write the query for you this will get us started on our path with writing sql now we're going to write much more than this but this will get us started right so right click on any table i'm going to use person.person and then i'm going to go ahead and select the top 1000 rows all right and that will open that up right here now once it opens it up it will also execute that sql query so down here at the bottom we're going to be able to see the results and i can look through here i can see all of the different columns that are associated with this table and i can see the first 1000 rows and i'll talk a little bit more about top and what that does here in just a moment all right so that's that now if you wanted to make a modification to this sql query you can and once you modify it then you'll come back up to the top right here and you'll click execute the other option to execute a sql query in ssms is you can hit the f5 button on your keyboard so you can either hit f5 or you can hit execute either one of those will work all right so here's what we're going to do we've seen a very basic query you've been able to test out your permissions and verify that you are going to be able to follow along today let me show you the script that we're going to be walking through so in your labs and i'm going to pull it over i won't use it myself but in your labs if you downloaded the files the very first one is called 0-1 introduction i'm going to take that and i'm going to just drag it over and drop it right here in sslms you can also just double click on it to open it up as well but if you just drag it over and drop it right here it'll open up a new tab across the top that has all of the scripts we're going to use for the very first demo and as we go from one demo to the next you can continue to just add them into your ssms and so now you'll see that i've added it right here it's already connected so if i come down here and select this very first statement and hit execute it'll give me results down here at the bottom now if you just want to follow along and type along with me you don't have to use this script but this will give you the sql queries that we're working through so that you don't have to worry about making errors as you're following along with me today got it all right so i'm going to go ahead and not use that because i got notes on the other screen and i'll be going through those the first thing i want to do here is let's talk about this this right here is us specifying exactly what data we want to get out of the database what columns we want to get out and what rows that's very important that's what you'll spend the majority of your time doing when you're retrieving information from a database so i'm going to go ahead and i'm going to get rid of all of that all right so what we have now is i have select from and this is the table i'm pulling data from now within the select clause i have to determine what columns i want to return the easiest way just to return all the columns is actually to use the asterisk right so if i hit shift 8 and i use the asterisk here and i run this code by hitting f5 it's going to return all of the columns from the person.person table also it's going to run all of it's going to return all of the rows of data so if i go down and look at the very bottom right of ssl mass it's going to show me that we returned 19972 rows of data now what we're going to talk about as we go through this three hour session together today is how do we limit the number of rows that are being returned and how do we also limit the number of columns and that's exactly what we're going to do right now so if you don't want to return all the columns which you probably don't most of the time also a lot of the times it's considered a best practice or it's considered a bad practice to return everything if you don't need it especially if you're working in some of those other business intelligence tools i talked about earlier like power bi then what i want to do is come up here and let's get rid of the star so i don't want to return all columns instead i want to return columns from this table so we see down here the list of columns i'm going to return first name you'll notice that when i start typing the intellisense or the autocomplete functionality will pop up to help me out so i'm going to go down and actually hit tab to return first name then i'm going to put a comma and hit enter because we're going to return more than one co one in one column and when we return more than one column we separate those columns with a comma right so we're going to return the first name next let's also return the last name so now i'm going to go in here and say hey i also want to return the last name so we're returning two columns i'm going to go ahead and run this again your script should look exactly like mine by hitting f5 and now you'll see that i'm returning all of the rows but i'm only returning two columns so what we've done is we've restricted the amount of data that's being returned from this database to only those two columns but we're still returning all the rows later on when we get to the where clause we'll talk about how to restrict what rows get returned right if i only want to return specific people that have a certain last name how do i do that and i do that through a where clause all right so that's the first thing i wanted to show you the next thing i'm going to take a look at is something called aliasing a column so the way that we store data in a database is a lot of times not the way that we would represent that data and present it to our end users right we name things very interestingly like for example this is first name where we kind of have the uppercase f and the uppercase n i wouldn't usually put that into a report looking like that so if you want to alias this and give it a different name notice how the result says first name what you can do is you can type in as and then you can give it a different name so if i use a single quote here i could say as first all right so i'm giving this a different name and i'm calling it first if i hit f5 again to run this code you'll see that right here the name of that column has been adjusted to the name that i gave it so you can change the name of these columns when you pull the data out so that it's a little bit more explicit and easier to read and more understandable for your reports all right so that's one way of doing it this is another tricky way to elias columns that i want to show you i can also do this i can also type in here something like second or last name let's do last name or we'll just do last notice that i'm aliasing this column but i'm not typing in the keyword as that does work that does work in fact you could do something like um what would be another way to do this let's just call this name i could do something like name now name is a reserved word so you got to be careful with that but if i run this you'll notice that it does change it to name now this is dangerous i don't like doing this without using the as keyword and i'll show you why here in just a moment all right so normally if i'm going to give it an alias i'm going to use the keyword as so it's very explicit and i can read my code and then i'll give it the name that i want to give it and we'll go with last and we'll hit f5 again and there we go so we're returning two columns and we're now renaming those columns all right so the next thing i want to talk about is how do we order the data right so if i wanted to see all of the data that right here is being ordered in alphabetical order by the first name how would i do that well in sql we use the order by clause right and so i'm going to hit enter go over here and i'll type in order by and then i'm going to tell it what column i want to order by now you can order by more than one column if you want i'm going to order by my first name so we'll do first name and then i'll run this and i'll hit f5 again and when i hit f5 that is going to return all of those same rows that we had before but now it's ordering them based on the first name all right now notice that it's also doing it in ascending order it's not doing it in descending order right so it's doing it in ascending order if i come back over here and i type in descending order by doing just d e s c and i run this again by hitting f5 it's now going to return those results in descending order you can also you can also sort this you'll notice right here that we have zoe and then zoe is a bunch of different first names they actually look like they might be in order oddly enough but i'm going to change this over so let's do something different here let's order by two separate columns i'm going to start off by using last name so i'm going to order by last name let's say ascending so we'll do default and then we're going to order by first name also so we're going to order by both last name and first name here and i'll go ahead and tell it for first name let's do ascending as well so if i hit f5 that's going to order my by the last name right here and then it's going to order by the first name if there's any ties right so if you go down to like atoms right here you can see it's ordering all of these alphabetically and then for the first name all of those will be alphabetical as well so we're ordering the result set the final result set by more than one column and that's very easy to do inside of ssl now the other thing that you can do that's a little tricky and i'm just going to show you this i do not recommend this this is not something i would normally do personally but the other thing you can do is you can order by kind of the ordinal position of the column in your select statement so what i mean by that is i if i want to so if i want to order by first name i could order by one and then hit f5 and so i'm ordering by the ordinal position the only reason i show you that even though i don't recommend doing that because i recommend being more explicit with your code is because you might find code online that's doing that and it might be confusing to you and that's what they're doing they're just ordering it by that ordinal position all right so this is pretty cool pretty easy stuff pretty basic if you've never seen sql before maybe not so basic but for those of you who have worked with sql this is pretty simple stuff now what about here's the other thing i want to do i'm going to get rid of that order by right there what if i want to combine for my report the first name and the last name together how would i do that well the way that i would do that here is i would come up here and delete what's there and then i'm going to say hey i want to do first name auto complete i'm going to go ahead and do a plus and then we'll do let's do a space in between here so we want to add in a literal string so we're going to hard code a space in there and then we'll do a plus again and we're going to do the last name all right now this is going to when i run this it's going to return the full name but here's a couple of things one notice that it did not have a column name there's no column name associated with this so we do have to alias that expression so whenever you're writing an expression in sql which you're going to do a lot you need to make sure to give that expression a name when you're done so we're going to go back and do that real quick and the way we do that is we just come to the end we type as like we did before and then we say full name right hit f5 again to execute that code and now it does have a name right here boom there we go easy breezy alright so that's how we get just kind of doing some basic concatenation and we will do concatenation a lot in fact another way to do this is a lot of people say hey i want to see the last name first all right well i'll just delete this right here and then now we'll say plus comma space plus first name right so if i do that i'm saying all right i want to do the last name first and we're going to add in a comma and a space and then we're going to do the first name and we're going to do that as full name so we execute that query again and now we see our final result so now we're doing last name first name so this is once again kind of very basic stuff that we're working through here now we're going to move on and this is the basics of working with sql all we're trying to do here is extract basic information from a table now we're going to dive in and we're going to start looking at some of the functions and some of the expressions that we can do inside of sql when we're pulling data from our database so if you go back and look inside of your class files real quick we're going to be using this script right here zero two functions and expressions and if you remember all you have to do is just drag that into ssl ms or you can double click on it and it'll add it into ssl mass for you all right so that's the one that we're going to be working on now 0 2 functions and expressions all right so let me clean mine up now if you're working through the script in fact let me show this because it might be a little confusing here if you're working through the script what you'll do is you'll want to manually highlight the code you're running because if i hit f5 right now it will run every single script that's inside of this sql expression i should have mentioned that earlier my apologies on that but if you're working through the script kind of one sql query at a time you want to highlight the code you're running and then hit f5 or then hit execute so that it's not executing everything every time so my apologies if that's been causing you a headache i should have mentioned that earlier that's a failure on my part all right so let's go back and look at this real quick the first thing that we're going to do is i'm actually going to be querying from another table so when i am writing sql and this is just me different people do this different ways but when i'm writing sql i like to always put in the tables that i'm pulling data from first so select from and then i'll type in the name of the table so i'm going to go with sales dot sales order detail you'll notice that the autocomplete is still going to be my best friend here so i'm going to hit auto complete right there by hitting tab and then i'm going to come up here and specify the columns that i want to return and so the first column i want to return here is going to be called unit price all right now if i run this query right here and i hit f5 i'm now returning from the sales order detail table just the unit price so this has all of my sales information so as you can imagine we might do a lot with this in order to get some pretty important information in fact if i wanted to see the data beforehand i might do select star remember select star select star from it and hit f5 again and now i can see all the rows and all of the columns and in here i can see something like order quantity and unit price so if i wanted to find out the line item or the sales amount of this line item right here this row i would have to multiply the order quantity pi by the unit price right we'd have to do that multiplication there and that's not being done it is actually being done for us right here but i'm going to show you how to do it if it wasn't being done all right so that's what we're going to do so we're going to go back and we're going to do unit price just like you have in your lab notes we'll go down to the next line and then let's just do some basic mathematical expressions here so i could do unit price plus 10 and then i can give this a name right so as plus 10 so we're going to give it a name make sure to put a comma after each one of these columns if you don't put a column or comma there it will cause some problems for you later and then we're gonna do unit price times two so what if we just doubled the unit price and that right there might be let's do i'm gonna format this just a little bit here there we go and that would be as doubled so we know that we doubled the amount and then i'm going to do the one we were talking about earlier i want to return the unit price times the order quantity right so unit price and we'll do times order quantity and we'll call that one as the line total calculation there we go and so what we've done here is some really basic mathematical expressions some basic functions in order to get that to work all right now somebody said in the notes here paul said hey intellisense isn't working for me any tips the best tip i can give you is always put your table in first and then type in your select clause also under i think it's under edit under intellisense you might have to toggle on completion mode right here so up here at the top under edit and then under intellisense there is the option to turn on and turn off completion mode so it is possible that maybe yours is disabled so just be aware that that is a thing all right so hopefully that helps you out i'm going to go ahead and run this again and we're going to see that we get our unit price unit price plus 10 unit price doubled and then also our line total calculation and that is going to be right here it's doubled and when we multiply it times the order quantity which maybe we should have returned just for validation purposes it's giving us that number times 3. all right so that right there is awesome now the next thing we're going to do we want to keep building on this that was just basic mathematical expressions i'm going to go back to the person.person table here so let me delete this stuff again remember i like to start with my from clause so we're going to say person dot person so we get that auto complete functionality and we're going to do our first name now a very common function that you'll use a lot when you're working with sql are string functions right and one of the ones i used a lot was the left function so if i use left right here what the left function does is the left function returns a specified number of characters from a string so you pass in the string that you want to return information from and then you tell it the number of characters you want to return from the left side of a string so i'm going to type in first name comma three and then i'm gonna do a closing comma and we're gonna say as and we'll call this first three right so just the first three characters here all right comma don't forget to put a comma in there and then we'll go down to the next line and now i'm going to say write and i'm going to do first name again and i'm going to do three what the right function does is it returns a specified number of characters from the right side of a string from the end of the string right so we're gonna do three and then we'll say as last three all right there we go we're gonna hit execute real quick we'll hit f5 and you'll see that that's exactly what we're getting here now this is very basic stuff right this is going to get much more complex as you dive deeper into the sql language but you can do a lot with these basic functions all right so that right there gives us our first introduction into string functions now another thing that you can do here and i'm just going to kind of build on this a little bit is you could do something like upper and so if i type upper in here and i put in first name i can call this one as upper and i'll call it maybe upper first right so upper first i also like to format my code so that my aliases are all right there next to each other and easy to find so you might see me kind of cleaning this up a little bit as we go and then we'll put a comma here and we're going to do another one and we'll call this one lower and this is going to be my first name as well we'll close that up and then we'll say as lower first or first lower or whatever all right and then we'll run that again and now we see we have the first three the last three we have all of the characters being uppercased and we have all of the characters being lower case now you can start combining these functions together right if i wanted to take the first character of this first name and make it uppercase and then combine it with the rest of the character i could just get the first character using left and then i could take that one character and make it upper and then join it back in with the other ones using the concatenation we did earlier remember earlier we did first name plus last name so you could take left one plus all of the rest of the characters but on the left one you uppercase it right kind of makes sense it's basic expressions here so this is how you start tying those together and kind of building this chain of functions within sql server all right so let me go ahead and replace that see if there's anything popping up here in the chat window that i can help out with no good job i see a lot of people helping each other out in the chat window i love it everybody gets mitchell bucks mitchell bucks for everybody all right so now i'm going to come over here and show you another function this is called the replace function this is a great function right the replace function will look for a specific character or sequence of characters in a string and replace it with another value so if i know that inside of my first name any time a specific character occurs i want to get rid of it then what i'll do is say all right look at the first name column look for the value of a and replace that with a star in fact let's go ahead and return the first name here as well so i'm going to return the first name just so i can see the results all right and i'm going to give this some intellisense right here i need to put a single quote around that that's my fault and we'll call this as replace a all right so we're going to go ahead and run that code again and now what you see is any time a shows up it gets replaced now some people are going to ask me right now they're going to say mitchell does this only work for lower case a or does it work for uppercase a is this case sensitive or is it not case sensitive that question actually depends on how you've set up your coalition on your database but the replace function is not is not case sensitive so i think there's another function called substitute i can't remember i might be confusing different languages because i'll work with a bunch of different ones but replace is not case sensitive and you can see that right here if you come down here to the bottom and you look at aaron or whoever that is you can see the first character of that which was uppercased is now a star so it will work for both lowercase and uppercase so just be aware of that whether that's what you want or not that's what it's going to do now let me show you another function we're going to kind of keep burning through a bunch of these the next function i'm going to look at here is the length function what the length function will do is it's going to return the number of characters essentially that correlates to the length of that string right and so we're going to say length and we're going to do linked on the first name here and then we'll say as and then let's give this a name something like length of name all right and then we'll run that again all right we'll hit f5 and now we can look at the length of the name and we can start getting some really good information there now one of the things i want to point out as we're kind of working through this is that i'm showing you a lot of individual string functions but be aware that when you get into really authoring more advanced dax for your queries for your reports you're going to be tying a lot of these together right the link function is a lot of times used inside of other functions like the substring function which tells it exactly what characters to return from inside a string so just be aware of that all right so we're going to now jump into and start looking at a little bit of date functions this is of course another very common one that you're going to be working with and so we're going to look at date functions in fact i'm going to go ahead and make my text here a little bit bigger so i don't have to keep zooming in and out so i'm just going to use the mouse wheel on the control button and the mouse will to just zoom this in and out all right i'm going to come over here and use a function and i'm actually going to get rid of all of this and i'm going to say select get date all right open close parentheses and then i'll hit execute and what get date does is get data is actually going to return the current system date and the current system time as you can see right here this is exactly what time it is right now eastern standard time it is uh actually that's not accurate that is a little bit that's utc time it looks like so i would have to convert that but that's exactly what time it is here all right so that's going to be the date now i'm going to go ahead and give this a name and i'll call this as system time you'll notice that i have no from clause here because i'm not actually retrieving this information from a table i'm just saying select the current date all right what if i get that date down there and i just want to extract the year how do i get that how do i get the year from that well there's actually a couple different ways of doing that and so i'm going to show you two different ways to do that here one is i'm going to go down to the next line and i'm going to use something known as date part all right date part and within date part you can tell it what part of the date you want to return right so i'm going to say look i want to return the year from get date so from that get date function that we did earlier i want to return the system year right so we're going to say as system time and then as system year hit f5 again and now down here at the bottom we see that we have not only the system time but we also have the system year so we have both now date part is okay but i'm not really a big fan of date part because there's an easier way to do this and the easier way to do this is actually using the year function or the month function that exists which is a simpler version of it right so for example that gives me my system year right there but if i come in here and put a comma and go down to the next line i could also say return the year from git date we close that up let's give that a name right there we call that system year two gonna give us the exact same results right so i hit f5 go down to the bottom right here and we'll see both of these expressions essentially return the same thing so one of the things about sql that you're going to find out as you start authoring sql and working with it and getting more familiar with it is that as your knowledge grows you're going to find out four five six different ways to do the same thing now a good developer is eventually going to figure out the most effective way to write queries so that they return in a performant manner and so you're going to learn how to performance tune your queries so it's good to learn different methods and different ways of doing things right all right so that is the year now let's also go ahead and return the month we could use date part for this or we could use the month function what i'm going to do is go ahead and just type in month open that up we'll do get date open close parentheses and then one more closing parenthesis on the end and then i'm going to type in as system month so we're giving all of these columns in all of these expressions a name remember what i said before actually if i were to get rid of this it will still work but it actually won't have a name on that column right it has no column name so you do have to remember to kind of alias those columns and give them the appropriate name the other thing that i want to show you and i've been holding off to kind of show you this is that you got to be careful um when you are selecting so let's do this i'm going to go down to the bottom real quick to show you the problem let me move this down a little bit so i'm going to say from all right and i'm going to say person dot person all right so from person.person and i'm going to say you know what i actually want to return the first name also for whatever reason so i'm going to go ahead and say all right let's return the first name now the chat window is a little bit delayed here but i'm going to let i'm going to ask the question anyway what is going to happen when i run this query right now when i run this query what's going to happen is it going to return the first name and pay very close attention to what i'm doing i'm going to go ahead and run it we'll see what you guys say and you'll see that i'm getting something very interesting here for the first name i'm getting the value of four well it's april so the month is actually returning four but the name of my column is first name like what what's going on what's going on here is i did not separate this expression from this column with a comma i didn't do it and because i didn't add a comma the sql engine thinks that i'm trying to alias the month as a new column name of first name so that's why i tell you i prefer to use the as keyword here to be very explicit in my code so that i know that i'm giving it an alias if i put a comma in here now and i hit f5 again now it does what i would expect it gives me my system time year year two gives me my month and then it gives me my first name so that's just one of those gotchas that gets a lot of new people yeah there you go kumar great job edison got it too good job forgot the comma right so very important to do that i'm going to come over here and say as and we'll do system month right there and now i'll add a comma on the end and run it again and now we get the system time the year the month the month two and the first name all right so that is kind of just a quick introduction into date functions now let's keep working down this path here right we're going to keep working down this path so the next thing that i want to do and i'm looking at my notes over here on the right so i stay in kind of lock step with what you're doing as well is we're going to look at the date add function here all right we're going to look at the date add function so and we're going to be using the sales order header table so let me delete all of this code i'm going to re-point to another table right cells dot sales order header right there and then from the sales order table or from the sales order header table we're going to go in there and say let's return the order date all right i'm going to show you a couple things we can do here with the order date so we're going to do order date all right and then we're going to go in there and we're going to do date add and date ad will allow us to add or subtract time from a date now a lot of times right now i'm actually looking at my order date in my sales order header table and i want to subtract time from it and i'll talk about a use case for this here in just a moment and so i'm going to say hey i want to add a year but i'm actually going to do -1 so i'm going to subtract a year and i'm going to subtract a year from the value of order date that we saw above right and so we'll give this an alias here we'll call this one something like as from [Music] let's see what's going on here in just a moment should be good all right as previous year so i'll call this something like previous year right there and we'll pull that all right so that cleaned it up right there and we run the code again and we hit f5 all right and when you hit f5 and we come down here to the bottom you'll notice that we took the order date and we extracted one year from the order date now i see there's a lot of great questions about sql that are coming through in the chat window what i'll do is after we take our first break i'm going to come back and i'll go through a couple of those and i'll actually show you how to do some of the questions that you guys have so make sure that you come back after the first break and we'll take some of those q and a questions because it'll be a little bit more efficient to do it at that time all right so this right here is going to give me my sales order header now there's some other things we can do with this right for example for example um what if i wanted to say that this is the date that somebody ordered something and the due date right let me see if i have this in my notes i don't good so this is going to be a little bit extra this is not in your notes but the due date is going to be in three days right so anytime somebody orders something they need to receive that order within three days how can i within my query generate the due date well i would say all right let's take the order date and we're going to add three days so we're going to add here the day we're going to go in here and then add in three so we're going to add in three days to the order date and we'll call this one our due date all right that's going to be the due date that's when it's due that's when it should have been delivered so if i run this query again and i hit f5 and we come down here we will see that from the day of when it was ordered we've added three days to it and that's now the due date right so that's a more practical example of something that you will probably be doing when you're extracting information from the database in fact when we're teaching power bi and i bet a bunch of people that are in here today are power bi users when we're teaching power bi we always tell people it's a best practice to actually write a query against your database and extract exactly the data you want and you can perform expressions during that operation so that you don't have to clean the data and transform the data inside of power bi desktop right and this is exactly what we're talking about is if you want to set up a due date that's three days from the order date you just do what we just did all right very easy now let's take a look at another function here i'm going to leave that there and we're going to use the date diff function all right so we're going to say date diff and inside of our table we have two columns we have a column for order date and we have a column for ship date right and so if it was ordered on the 5th but it was shipped on the 10th then that means it took 5 or 6 days for that order to be shipped and so i want to return the number of days it took to ship so we can look through all the transactions figure out which ones took too long and then we can go and look at our process and try to fix that right so i'm going to say i want to get the difference in the number of days so we'll start with days between our order date and our ship date all right and let's give this a name i'm going to call this our days to ship all right days to ship so we're getting the difference between the order date and the ship date right just tell me how many days what is the difference here and we're going to hit f5 again and i can see that it looks like the majority of them are seven days now there might be some that are less than seven days and i don't want to sort through 32 000 rows so what i can do is let's build on what we did earlier i'm going to order this by days to ship so i'm going to hit enter go back i'm going to say order by and then i'm going to type in here days to ship all right in ascending order because we want to start from the lowest number first and ascending is the default functionality of the order by so we'll hit f5 and we got some that are null that's kind of odd actually let's see is everything null i would have to really dive in so it might be that we have some that haven't shipped let's look at that so i'm going to bring in the ship date here whoops we're going to talk about null values later let me type in ship date and then we're going to run this query again and there you go we have some that have a null ship date there's no value for it in the system and so therefore it's returning null i'm going to talk about how to deal with nulls later in this session because nulls are very very important when you're working with databases all right so that shows us a little bit more though right how we can continue working with these date functions these data expressions to return that information and return that data from our system all right i have one more example in this lab that we're going to take a look at and that's figuring out how many months it's been since the order took place so i want to compare the order date to the current date today well we already know how to get the current date today we use the get date function right so all i need to do is say what is the difference between the order date and get date at the month level so we're going to use date diff so i'm just going to add once again to the expression that i have because it's easy so i'll do a comma we'll do date dip here and then we're going to do a difference at the month level so we'll do month and we're going to say what is the difference between the order date and get date which is today right open and close parentheses there and i'll just call it month's difference months since order is what i have in our notes all right and i'll go ahead and hit f5 to run that again and now this is going to tell me how long it's been since those orders took place and if we scroll down we'll be able to see some other examples that show up like 105 and keep going through and we'll see some different things here right but this is how you can start building on that data so we don't just pull data out of the database when we pull it out we can clean it we can transform it we can modify it we can use sql or sql to do that and that's exactly what we use all of these expressions and functions for in fact a lot of times you might be pulling this data from a staging system where the data is raw it's never been cleaned it's never been transformed and you need to load that into a very specific table in maybe your reporting database and so you have to clean it up as part of that moving process right so sql is actually very very important here for those types of we call it etl extract the data transform it and then load it in its new destination right sql is a very big part of etl operations all right so we're going to jump into the next section here and it's going to continue to get a little bit more complex as we continue to move along but the next section is going to be looking at let's see what it is actually i think it's going to be null functions yes so now we're going to start talking about null and what null is because it can be a little bit frustrating to work with in databases so grab that drag it over drop it right inside of ssl ms or you can double click on it and if you have ssl mess open already it'll just add it to your existing ssms and we're going to now work with these null functions and kind of talk about that a little bit uh brian says cleaning data will it cause a load on the server that's a great question brian uh the answer is yes of course it will right uh so what happens is you got to be careful this is where we talk about performance actually if you're extracting data and you write some very complex queries it can it can actually not only put a load on the server you could actually lock up tables you could lock up the temp table you could cause a lot of problems based on writing in efficient queries now the queries that we're writing today would not cause those kind of problems these are very straightforward select these columns and do this but when you get into working with temporary tables and variables and common table expressions and you get into really a lot more advanced sql that's when you have to be very aware of what you're doing and look at execution plans and stuff like that now i'm going to tell you about this later but at pragmatic works we have an introduction and an advanced dax class we have a dax boot camp and we dive into those very advanced topics there that's why we do these events right we want to sign you up as customers make no doubt about it but yeah it can absolutely cause a load on the server that's an awesome question all right so let's take a look here and i'm going to flip over in my notes to the null and the top distinct so now we're going to take a look at the null function now what is a null value a null value in a database is not a blank it's not an empty what it means is unknown null values represent an unknown value for example a lot of times when people fill out information inside of a form they'll put their first name and they'll put their last name but a lot of times we don't put our la our middle name right so we don't put it so when you put it into the database you don't put that they have no middle name you just put null you're like i don't know what their middle name is so it's null it's unknown so null kind of represents an unknown value so here's a question for you what is one plus null if null is unknown then the result set is unknown what is five plus null unknown it's null what is first name plus middle name which is null plus last name the result is going to be null right and so sanat said uh i should have said advanced sql and introduction to sql class so i apologize if i said dax i should have said introduction and advanced sql class those are completely different topics i probably misspoke so my apologies thank you for letting me know that so when we're working with nulls you've got to account for the fact that they're unknown and you're going to see some interesting problems here as we work through this next example so let me clean this up just a little bit here and then i will get rid of the order by and then i'll get rid of the from clause all right all right so here we go select from and we're going to start with person dot person right back to our same table we've been working with so far and in the select clause i'm going to tell it that i want to return the first name right and then we'll do a plus and then we're going to do a double or a single quote space single quote plus and i'm going to add in here the middle name and then we'll do plus single quote single quote plus and then we'll do the last name now remember we also want to give this a name so i'll call this full name and then for the sake of just being able to read the code a little bit better i'm going to add the first name middle name and last name as separate columns in our final result set so we can validate the code here right all right so here we go we're going to do first name middle name and i'm typing it a little bit fast but you already have the completed example so just keep that in mind and then i'm going to go ahead and run that code again and we're going to see some interesting results but it goes back to the fact that a null value is unknown right so let me zoom in here you'll see that for the very first row we have syed e abbas and that one looks correct it looks awesome but when we get a little bit further down for example right here to kim abercrombie we're getting a null value for the full name and the reason for that is because the first name is kim the middle name is unknown and the last name is abercrombie right so what's happening is it's saying look we don't know what the middle name is so we can't get you the final result set because first name plus unknown equals unknown all right so that's where we need to be kind of careful here uh when we're writing code that works with null so how do we solve this mitchell what do i do to solve this problem well there's a few different methods for working with null values as i mentioned before everybody's going to have their own way of writing code that makes sense to them but let me show you an example if i know that middle name is a nullable column meaning that it could have no values because when we set up here let me give you a little bit of a breakdown here when you set up a table or your engineers or your database administrators whoever is responsible for creating a table let me kind of break it down whenever you create a table you're going to do some stuff on the back end of that table you're going to specify what the data type is of that column you're also going to specify whether a column can be null or not so you'll notice person type is not allowed to be null if you try to load an unknown value into the person type of this table it will fail right good job i see p a b beater said use coal s yes we're going to do that good good good good observation there you'll notice that suffix can be null but last name cannot so if you know by looking at what we just did right here that a table can be null you probably want to account for that in your code right because you don't want to accidentally report on data that's null and then you get unknowns so if i know that middle name can be null which it can what i'm going to do is come in here and say all right if it's null so we'll say if it's null we'll use is null here and i'll show coalesce in a minute because i actually prefer it better if middle name is null just go ahead and return a blank don't return anything now let me be very clear this is not a complete example because when i run this it will fix the first problem but now we have two spaces that we're adding in which is kind of a little bit wonky so i'd have to build some conditional logic around this one right here to say look if middle name was null also return a blank space here don't return a space just don't return anything right so let me go ahead and run this again real quick we'll hit f5 so that's one way you can use is null but i like the recommendation there right we could use coalesce so let me show you what coales does and how we can use it so you'll notice right here that for kim abercrombie that value is no longer returning unknown because we just said look if it is unknown just return it with a blank so our expression now says first name plus blank plus last name right so it's no longer unknown good job now i like coalesce a little bit better coalesce has a lot of cool functionality what coalesce does is coalesce says return the first non-null value in a list right so if i were to do something like coalesce and i said all right first if middle name is not blank return middle name all right so we'll say middle name just like this but then we'll say if middle name is blank then return the next column which is going to be a string now you could have multiple columns or multiple values in here and it'll grab the first one right so for example i'll just go with this this is fine so if i hit f5 again you'll look and you'll see the results are exactly the same as what i did a moment ago and exactly the same as what is inside of your notes inside of the lab notes that i provided for you right so that's one way of working with nulls we're going to keep doing this we're going to have a couple other examples as we progress through the three hours i think there's a couple more examples when we get into where clauses on how to deal with nulls because they can cause quite a bit of frustration and confusion if you do not account for them all right so what's the next thing we want to take a look at at the very beginning today we ran a query that was select top 100 star or select top 1000 star from person.person and i told you that that returns the top 1000 rows from the table it doesn't return all the rows in the result set it just returns the top 1000. so i want to take a look at that again and really kind of hone in on what that does and what we can do with the top function so i'm going to delete everything in my select clause all right and then once we get rid of the select clause here i'm going to come in here and say all right from and the from we're going to use is going to be sales order detail for this one so we'll do sales dot cells order detail and then up here in my select clause i'm going to just do star right so i'll just do star select star remember what star does it returns all the columns from the table and i'm going to hit f5 and when i hit f5 if we go down to the very bottom right you will be able to see i'm probably blocking the screen there but you'll be able to see that we have 122 000 rows of data that's being returned all right so if i escape and we come back what we want to do is reduce the number of rows that are being returned right and so what i can do here is i could say top 10 star so i'm saying all right return all the columns but only return the top 10 rows which is just going to be the first 10 rows that it comes across so i'll hit f5 again to run that code and you can very very clearly see in the result set that i only have 10 rows of data that's being returned right and so this is pretty nifty pretty handy there are times where you might want to return your top five sales people return your top 10 regions your top 10 departments and so you set up your entire expression you order it and then you return the top 10 from that expression i'll show you that here in just a moment but this is a quick way also to sample your data if you have a billion rows and a table i don't want to do select star from my billion row table i'd rather say select top 10 from that table and just kind of sample the data real quick and then i can write my expression another way that you can do this is you can do something called top 10 percent so if the table has 121 000 rows of data in it right if the table has 121 thousand days of ro data rows of data you could do top 10 percent and top 10 percent would return 12 000 rows right i was trying to look at the chat window there and got somebody said repeat something again but unfortunately there's a delay so i just don't know what it was and i'll hit f5 all right so this is going to return once again only 10 percent of the rows of data another kind of a cool way to work with your data by just doing something like top 10 percent all right so this is top 10 we're going to kind of build on this a little bit more let's go ahead and instead of doing star we're going to actually select the columns that we want so i'm going to tell it that i want to return the sales order id from my table and i'll do a comma go down to the next line in fact the way i like to do this is i'll put top up here all right and then we're also going to return the sales order detail id all right so we're only going to return 10 rows from the table but we're specific telling it exactly what columns we want to return sales order id and sales order detail id now i'm going to come down to the bottom right here i'll click execute and when i click execute we're getting exactly what we wanted to return which is just going to be those top 10 rows all right now that's going to be top 10 another function and i'll do one more function and then we'll kind of break for we'll take a lunch break here or 15-minute break in just a moment but the next thing i want to talk about a little bit is distinct right so if you were querying data and pulling data from let's say your product table and somebody comes to you so here's the request right somebody comes to you and says hey can you give me a list of all the products or really just all the different colors of products that we have so this is your job i want you to go to the sql database go to the production.product table and return a list of all the colors that we have for different products so you say hey this is going to be easy i'm going to do select and i'm going to say color from production.product and then i'm going to hit f5 and when i hit f5 i get a list but the list is a little bit confusing here right like it's a lot of rows black is showing up multiple times null is showing up multiple times so how do we fix that right how do we get rid of those duplicates when we're returning that information well i can use the distinct function right the distinct function will do exactly that so i'll come over here and type in distinct and that's going to get rid of any duplicates that's in my data so then we'll come over here and click f5 again and now we're getting a much more comprehensive list that's exactly what we were looking for right now we're not done we have this is scratching the surface when we come back we're going to dive into joins and aggregations and the really cool stuff that people get interested in right but before we do that as i mentioned before one of the things that we're here and what we do at pragmatic works is we do training we do live training we do virtual mentoring hackathons and of course on-demand learning and so as part of this event we are going to have a sale where you can buy our on-demand learning at 50 off for 247 dollars now if you're watching this on the recording later unfortunately that sell is probably not going to still be available if it's more than five days from today but for those of you who are here we have a sale where you can kind of pick that up now on our on-demand learning platform we have two classes here that you can take a look at one is going to be our introduction to sql class the other is going to be our advanced sql class and as you can see right here at the bottom the introduction to t sql class is almost nine hours in length and the advanced t sql class is almost 14 hours in length and those are recorded that is a lot of content we also if you go to our website we also have a dax boot camp that's coming up i don't have the exact date with me right now but we have a dax boot camp that's going to be coming up here in a couple of maybe a month and you could join me in that dax boot camp and we'll have a lot more time to really get into the intricacies of sql i might have said dax again i do so much dax i apologize but we'll have a lot of time there to really dive into sql and talk about those different topics so that's my spiel not going to go any further there but if you're interested in training that's what we do that's how we stay in business so just reach out and let us know and feel free to sign up for that subscription online all right so i'm going to put a timer on the screen 15 minutes go get yourself something to drink and when we return we're going to continue working through sql all right thank you everybody we'll see you back here in just a little bit all right welcome back everybody we are going to jump right back in where we left off but first i want to take a look at some of the questions that we had not a lot of questions manuel's been hitting them as soon as they come into the chat window but i want to take a look at a couple of the quick questions so one of them was how can i return the three digit month now a lot of you who probably have worked with dax or taken our dax training are probably like oh i know how to do that because it's actually the exact same function so if you want to return the three digit month you can use the format function in dax and that will return the three digit month and so that was from kumar this expression right here is essentially how you would do that and i'll put that in the chat window here in just a moment also we got another question how can i return the first day of the month the first day of the year well really that's going to be a combination of functions now how you do it is going to be up to you because there's a couple different ways you can do it but what i did here is i'm first going to return and just let me go ahead and run this entire script real quick what i want to do here is i want to return first of all the last date the end of the month of the get data expression that we're returning right so get the end of the month so if we're looking at april get april 30th and that's exactly what you're going to see right here then i take april 30th and i add minus one month to go back to the prior month all right so that's pretty easy all right and then from there you'd have to get the end of that month and then go forward one day so you just keep building out these expressions to ultimately get there once again i just put this together real quick on break but there's a couple different ways you can get the answer to this uh this is just one way that you can do it by kind of leveraging date add end-of-month functions to make that happen all right so that right there is that now there was another question there was two more questions one nebula was asking us about removing duplicates right so if you have a lot of duplicates in your data and you're trying to return the query and you're having a hard time finding those duplicates that's actually a little bit tricky my method and the method that i prefer is out of the scope of this three hour session but i prefer to put all of that kind of into like a temporary table or like a common table expression where i actually give anything that's a duplicate or row number right so if it's the same it'll have row number one row number two row number three then in a subsequent query i'll say all right select everything from that temporary table or from that common table expression that has a row number equal to one so then i only return the distinct values once again depends on how complex your scenario is because you could get away with just using distinct if it was just a couple of very simple columns right but a lot of times on more complex examples you might have to do something that requires a little bit more work like common table expressions temporary tables things like that now the other question we got and this was a ton i saw it just getting bombarded in the chat window there is is null versus coalesce all right so let's revisit this real quick before we jump back into our demo is null in coalesce are a lot of ways they are alike right the big difference here is that if i do first name plus middle name but i want to make sure i don't return the null value if it's a null value i can use is null to simply say look if middle name is null return this value whatever i specify i could say return hi my name is mitchell i can return whatever i want there right but i could replace that value with something else is null only accepts two parameters so if the first one is null return this expression coalesce is a lot more flexible and versatile than is null so for example you'll see in my coalesce expression right here i said first name plus take the what coalesce does is you can pass in multiple values and it will return the first non-null value from a list so middle name if it's null it'll check the next item in the list well null is obviously null so it'll skip that and then it'll return this item hi i'm not null right so i actually can't put a i don't want to get into qualifying that hi not null all right so something like that so coalesce gives you a little bit more flexibility and versatility but if you just want to check a single value see if it's null and just replace it with something else just use is null it's super easy but coalesce gives you more functionality right so if i run this right here and i execute that code you'll see that coal s right here that i didn't give a name if there's a null it's returning kim high not null because that's the expression that i gave it the option to return a little bit confusing but it's the same in a lot of ways coalesce and is null are the ones you'll use to replace a null value with something else which one you use is going to be up to you nebula says can you use distinct on multiple fields the answer is yes in fact distinct unfortunately will always work on multiple fields so when you are bringing back multiple columns but you only want to bring back the distinct first name it doesn't work that way it will do us distinct across all the columns in your query that's why when you have multiple columns that define uniqueness but not all columns you might have to do something a little bit more complex so great question all right uh is there something like not no yeah there's a function called is not where is not null yeah and we'll get to that later tom that's a great question as well all right so here we go we're going to get back on track here lots of great questions what i'll try to do after this event and i try to do this after a lot of my events is i'll either do a blog or i'll do a follow up kind of 15 minute youtube video where we go through and answer a lot of those questions because these questions are gold they're great i love them and so we'll definitely circle back all right so we're going to go back and look at distinct right we were working with distinct right before we went on break and what we're going to look at now is we got distinct yeah so let's do this if i go back and say select star from production.product here and i hit f5 and we go down to the very bottom right you'll notice that there are 504 rows of data in our table right 504 rows of data in our table now what if and we just had a question from somebody just a moment ago what if i go in here and i were to say distinct star what distinct star does is it's saying look at all of the columns so literally look at every single column here and if you find any row where all the columns are exactly the same if you find any row where all the columns are exactly the same only return the the unique ones but it is going to look at every single column by default that's just the way it works so if i run this and i hit f5 and we go back down to the bottom again you'll notice that we still have 504 rows so distinct made absolutely positively no difference here distinct essentially said look every single row of data for the columns that you gave me every single row of data is unique so it's going to return all of those rows of data so that is a gotcha when it comes to distinct and that's where you might have to start using things like common table expressions temporary tables row number functions window functions and things like that really cool stuff i love writing sql but distinct does have that limitation all right so we did color we did that let's start taking a look at the where clause so if you think about where we're at so far right so far what we've been doing is we've been just bringing back every single row from all of our tables we haven't really been result like reducing our result set and as a best practice if you're connecting to a fact table and that fact table has millions of rows or hundreds of millions of rows of data we need to reduce that amount of data only bring back the data you want so if i want the last month of data i'm only going to bring back the last month but how do i do that we do that in sql through the where clause all right so let's do this i'm going to switch my query back over to sales order header all right and i see people like ricardo and lucas and other people answering questions in the chat window so thank you guys for doing that we will get into aggregate functions here very soon so we will finish that up before the end of the day today we still have an hour and a half together so we have a lot of time left to cover a lot more topics all right so we're going to be pulling data from sales dot sales order header just trying to make sure i'm in my notes correctly so it matches what you guys have almost there there we go so we're going to be pulling data from cells.cell's order header table and i'm going to bring back a couple of different columns i'm going to say all right let's bring back customer id i'll move my mouse out of the way and we're also going to bring back our sales order id and then let's also bring back the order date now if i hit f5 right now and i execute this query it is going to return all of the data that's in that table at least all of the rows we've limited it down to three columns but it will reduce all of the the the we've limited it to columns but we haven't reduced the number of rows yet so how do we do that we do that with the where clause so i'm going to go down to the next row we'll say where and this is where we can start doing some filtering so i'm going to say where customer id equals and we're going to go with 298 29825 which is one of the customer ids where someone has made purchases so pretend excuse me for just a moment that this is your customer id at some restaurant that you go to at some grocery store that you go to and so every time you make a purchase at that location your customer id is recorded in the transaction table right so we're trying to figure out for this customer what is a list of all of their transactions so now i can hit f5 and when i hit f5 it's going to return for me all of the transactions for that specific customer so let me zoom in right there and you'll notice the customer id is exactly the same on every single one i can see every order for that customer so now i can do things like the minimum order date to get the first order date for that customer the maximum the total number of orders for that customer and that's where we start getting into aggregates right that's where we can really start getting some pretty cool stuff with sql but right now we're just limiting the result set that gets returned and so we're saying hey filter this down to where customer id equals two nine eight two five now you can do some other kind of odd stuff here if i were to say where one equals zero just think about that for a moment what do you think is going to get returned from this table well the answer is nothing right because 1 never equals 0. now this seems like a really odd scenario here but there are times when i've written sql in the past where i have used that expression specifically when i create a table from a table and i just don't want to load any data from it i'll do where one is zero so this is a thing that you can do when you are doing this kind of where clause all right all right so let's take a look at something else that's a little bit more complicated with the where clause still using the sales order header table i want to tell it that i want to return all of the sales from my sales order header table for the year 2013. right so imagine that we're looking at this data and we have data for all years but we only want to return the data for that year specifically year 2013. so now i can come in here and say where order date right equals 2013. the problem with that is that order date is not a year it's an entire date so that is not actually going to work the way that i want it to work and so we're going to combine some of the things we've learned so far where the year so extract the year from the order date equals 2013. so i'll do this and i'll say year so we extract the year equals 2013. now i can hit f5 and this is only going to return we can look at the order date here it's only going to return the data from that table that is equal to 2013. now it is very important that i explain to you that this can be a bad query that can cause performance problems because when you're working in tables in a database a lot of times we will index columns so if you want to know we're not going to get into this too much because it's complicated but think about a phone book right and some of you maybe maybe you're so young you've never seen a phone book but in a phone book if you go to the very back you have an index and in that index if i want to find out mitchell pearson's phone number i'll look i'll go to the p section i'll find pearson and it'll tell me exactly what page mitchell pearson's information is on right that's awesome it's fast well sql server has something very similar where we can put indexes on columns so that it keeps that information if i had an index on order dates where my query could go look up the pages where those order dates are so it doesn't have to read all the data and it will perform better i just invalidated that by what i did so you got to be careful how you write your where clause because even though you can do this it might not be a best practice in fact a better way to write this might be where order date is greater than or equal to 2013 january 1st and order date is less than 2014. that would hit the index that would perform better now once again we got three hours together that's more complex that requires more knowledge of databases and the internals of databases but it behooves me to at least give you a little bit of that information all right so this does work we can look through we can see that we have 14 182 rows if i were to hide my face come down here to the bottom so that is significantly less rows than what's in that table i think the table has 122 000 so that filter did work let's look at another filter do you remember earlier how we were working with the left function so we were working with a string function well we can do that here as well i could say all right return from the cells order header table let me make sure we're oh yeah it is a different table that's what i was thinking all right so let's switch this over to a different table here person dot person oh okay so we got a question from tom here how do you know when to use is or equals so tom i really only ever use is when i'm working with null values so where because a null value doesn't equal so order date equals null is not a thing it's where order date is null so where i use is is when you're working with nulls and we're actually going to get to an example of that here in just a minute so great question we'll get there in just a moment outside of that i always use equals or less than or greater than a normal operator all right so we're going to do from person.person and let me go ahead and delete all of this right here and let's see what information we want to return so we'll return of course our first name we'll return our last name and then down here in the where clause we're going to say where first name starts with or last name starts with s so if we want to return all of our customers where their last name begins with the letter s we'll say left remember how we did left earlier left last name one we only return one character equals the character of s right and so once again if you have an index on the last name this is not efficient this is not a good way to write this query there's other ways we can try to write this query to get better performance and so i'm going to go ahead and run this query real quick hit f5 and if we go through and we look at the results every single row that gets returned is only going to be characters are only going to be customers that start with the letter s so we're writing the where clause now to filter down the results that are being returned very important many different ways we can do this all right so let's do this i am going to actually i apologize i meant to send everybody this information earlier in the chat window and i did not so i'm going to copy that real quick there you go and then the other one that i wanted to share with everybody here and i'll put that in fact let me get two more i apologize let me get your name out of there there we go all right and i'll share that for everybody as well all right so the next thing we're going to do is go back over to our sales dot sales order header table let me make sure that's correct yes it is and we're going to return some information again so we'll get rid of the where clause i'm going to change my select statements once again if you're following along in the files i gave you you're not having to delete and type everything just move down to the next block of code and i'm going to change this to customer id so let's say that from the sales order header table i want to return all of the customer ids where the customer id is equal to 11 000 right so if i run that code right there i should only see the record for the customer id where it's eleven thousand so i can see that this customer had three transactions in our database now what if i wanna see all of the customers that are not somebody asked about this earlier i want to see all the customers that are not 11 000 right i want to see all the customers that are not 11 000. what i can do is come over here and say where it's not equal to 11 000 like that and hit f5 again and now we're not getting 11 000 but we're getting everybody else right all of the other customers are showing up another way to write not equal and i saw somebody put this in the chat window earlier is you can come over here and hit exclamation point equal that also is going to be the equivalent of a not equal sign so where customer id is not equal to 11 000. we hit f5 again same exact result as before it's just another way to write that query now we got a question just a moment ago from who wrote this question i see one there how do you know if the info is indexed from the rachel the rachel dooley a little bit tricky rachel because it's a little bit beyond what we're going to be talking about in this class but if you come over to your table you can come in and look at the actual indexes that have been put on that table and then you can script those out and you can look at those indexes so depending on what level of permission you've been given you can come in here and see that on my table i have an index on business entity id all right so that's how you get into it you do have to sometimes script them out because the name might not be indicative of what the index is so you'll script out the code by right-clicking and scripting it to a new window all right do functions not allow for alias use do i need to use the original name no so not whenever you use a function like first you can alias it when you get done yeah all right so we did not equals 2 and equals 2. also you can do greater than right so if you want to use operators here like greater than 11 000 you can do greater than so i can say where customer id is greater than 11 000. or let's do something else let's say where customer id is greater than 11 000 five and i'm going to go ahead and order this by so we can see it order by customer id so we're going to order by customer id in ascending order and when i run that you'll see right here that we are not getting any customer ids that are less than 11006 because it has to be greater than right so you can use operators and i brought this up earlier when we were doing a filter on the date and i said more effectively a more efficient way to retrieve data from that table would be to say look where the date is greater than or equal to january 1st of 2013 and the order date is a less than 2014 that's a more efficient way to use the index if it exists than to say where year of order date equals 2013 because the year of the order date is not an index column so therefore the engine would have to read all the data and it could take longer all right now once again that's a little bit more complex but i do want you to know that there are efficient ways to query your data to improve performance and sometimes it's the little things that matter all right so that's going to be ordering by customer id using that we could also do something like less than right of course we could do where customer id is less than 11 000 five and we can run that what if we wanted to do an end condition so where customer id is greater than 11005 and customer id is less than let's say 11040 i don't know and we'll hit f5 so now we're saying the criteria is it has to be greater than and it has to be less than so if we look at this here and we go all the way down to the bottom it stops at 11 039 so you do have the ability to do a little bit more complex criteria right now the next one i'm going to look at is very similar what we just did you'll notice that this does not include 11005. so if you want to include 11005 you would add an equal sign here and you'd add an equal sign here so where it's greater than or equal to 11005 and where the customer id is less than or equal to 11 040. another way to do this exact same expression is actually to use something known as the between operator right so i could come in here and do something like this excuse me where customer id and i'm going to say is between right so instead of doing greater than we'll say is between 11005 and we'll give it another number here so we'll go back and it's between i don't know 11 0 15 a little bit less this time all right and i'll hit f5 again and then we'll see that we are getting it does include the value so we're getting 11 05 all the way through 11 015 all right let's see here yep all right so just looking at the comments it's not an interruption at all the questions are great i try to make sure i answer them at specific time so we can keep the class flowing but this is how you do a between operation here now you can also do alpha characters right so if i wanted to do something like let's see we're going to transition back over to person.person table and we'll do person dot person and then i'm going to do first name last name and then let's say we want to do where the last name so watch this this is pretty cool i wouldn't recommend it but it's pretty cool we'll say where the last name is between right a and c all right so select all of the rows from the person person table where the last name is between a and c now you might think that it's going to return all of the last names that start with a and all the last names that start with c however if i run this you'll notice right here at the bottom in fact let me go to the very bottom here that it actually will end at the letter b if i had a last name that was exactly c c and only c it would work but since i don't anything that's c a or c b or c whatever that is greater than just the character c so it doesn't get included now i don't know when you would actually ever do this but it is something that you should be aware of it does respect that now let's look at something that's cool really cool we're going to look at something known as the like function what the like function allows you to do is the like function allows you to come in here and filter it on some kind of kind of wild card criteria so for example if i want to return all of the last names that start with the character s earlier i showed you we could use the left function left last name character 1 equals s right you can also use the like function so if i did like and then i did character s and then i did wild card what i'm saying here is return all of the first name and last name rose from the person.person table where the last name is alike s with any number of characters afterwards right so as long as it starts with s so if i run this query right here you'll notice down here in my result set that the only thing that gets returned are names that have the character of s the last name right unfortunately there's a lot of rows so we have to kind of filter through it and look through it but that's what that does now you can also do something like this what if i just want to return any last name that has s anywhere in the name doesn't start with it doesn't end with it but anywhere at the beginning the middle the end anywhere what i can do is actually put a wildcard on the beginning and say hey just return anything that has an s anywhere in that code hit f5 again and you'll notice right out of the gate we're getting ones like abbas here where the s is at the end and if we go down further eventually we'll find some where the s is in the middle like with alonso or allison right and so that's another way to use the like expression and so even though sometimes the like function is not necessarily the most effective from a performance and an indexing it can be really really helpful when you're trying to find very specific data or you're troubleshooting or debugging or whatever it is right all right another way to use like a little bit less common would be maybe to do something like this so i'm going to say anderson and then instead of typing anything there i'm going to put an underscore and then we'll do an n and we'll close that up and so what i'm saying is where it is ander s any character here it doesn't matter and then s so let's go ahead and hit f5 to run this and we're getting a bunch of records in fact i am going to comment out the first name and i'm just going to return a distinct list of last names here so i'm only going to return the last names that are being returned a distinct list a unique list and i'm going to hit f5 and you'll see that i'm returning anderson with an e and anderson with an o interesting right so we know that people spell their names a lot of times very very differently from other people and it's just the way it was spelled so if you're trying to look for everybody who has a name that's very similar you can use something just like this i want to take a moment also to thank manuel who is working like a madman in the chat window to answer all these questions he's doing an awesome job thank you manuel for all your help there i know it's a lot of questions coming in all right so that's how you can do that right there the other way you can do this is you could be more specific by using an open in a square bracket and then specifying what those characters can be so if you don't want it to be anything you could specify very specifically what those characters could be by saying e or o and then if i run it again it's essentially going to give me pretty much the same result that i got before and you come down here and it's the same result it's just a different way to get the same thing but this is more explicit right we're adding some specificity to what we're doing all right let me look it down a little bit further in my notes i think we're almost done with this section here we actually got quite a bit left all right so here's another one that's really cool that us sql developers really love to use right so instead of using the like clause here we can use something known as the n clause now in you provide a list and say return all of the rows from this table where it's in this list so return all of the records from this table where the last name is in the list that i provide so for example if i come in here and say in and i do smith and then i also do anderson like this right here and i close that up and let me add first name back in real quick so we'll get a combination of unique first name and last name combos this will only return the rows where the last name is in this list either smith or anderson and you can make this list as long as you want the n clause is very very common when you're working inside of sql so i'll hit f5 and then we're going to get a very long list of all of the people with the last name anderson and then all of the users who have the last name smith if there's any duplicates we got rid of those because we did use distinct but that is going to give us that right there so that's the n clause another thing we can do here is we could come in here and say where the let me see i'm going to skip that one we'll say where the let's try this so where i do want to do one more in clause but i think i think this is good enough so we're going to look at the person.person table and we're going to say where the first name all right equals haley and the last name equals barnes so this means that both of these criteria must be true we're using a an and condition here and we're saying look the first name must be haley the last name must be bards so i'm going to go ahead and run that again by hitting f5 and i'll get rid of the distinct now and we get one record that matches that criteria that's exactly what we were looking for right we knew there were a bunch of barns we knew there were a bunch of hayley's we wanted to pull back that exact customer and that's how we can do it by putting in two different criteria where they both have to equal true now you could also do an or condition and that means where the first name is haley or the last name is barnes so maybe we have an idea of what the name is but we're not exactly sure how to spell it so just bring back all the records that are similar to that or whatever and then you'll see everybody who has the first name haley or has the last name barnes is going to be returned right and so that's pretty straightforward i think you just got to get used to writing these where clauses but we're going to say or all right also there like i said before if you wanted you could try to do something like first name equals haley or first name equals haley without the i so without the i i could do either one and if i run this again now we're going to get a combination where some of them have the i and some of them do not so we're saying one or the other now in this example here we could have used a like condition we could have used the n clause you already in the very little time that we've been working together can probably see that we could have written this where clause in a couple of different ways edison says does capitalization matter so generally no most databases are set up with a correlation that does not that's not case sensitive right so it all comes down to case sensitivity of your database my database is not case sensitive most of them are not so capitalization does not matter in fact just to prove it if i were to make this a lowercase h and i did the same thing over here and i run it with f5 we're gonna get the same results back again so good question and i just don't see all the questions guys so i grab the ones i see that pop into my uh purview there all right so that's pretty cool let's see what else we got now order of operations check this out order of operations matters so now we're going to say all right where the first name is haley or the first name is haley or actually and so we only want to return records where the first name is haley and the last name is barnes we don't care what the first name kind of odd results for example i'm getting a lot of people without the last name barnes and that's not what i wanted i'm like for me in this example i want to make sure that we only return records where the last name is barnes and the first name is either haley or haley without an i that's not what i got so order of operations matter you want to make sure if you're doing some more complex logic like this that you use things like open parentheses right so i'll do an open parenthesis here to say look this or this has to be true and this has to be true now if i do that we're going to get much better results here and you'll see we only get those two results all right so definitely we want to include parentheses there to get the best results so nothing that you don't know from a basic mathematical class same kind of concept will matter here like what we've been looking at before all right the not predicate so there is something else i don't like this predicate but i'm going to show it to you because to me it can be more confusing than not let's say that we were doing where last name equals smith right so where last name equals smith one way to say where last name does not equal smith is you can actually go to the beginning and say not last name equals smith and hit f5 i'm not a big fan of that right i would much rather just say where last name is not equal to smith by doing like we did before so you got those brackets right there but there are once again multiple different ways you can write sql which includes adding that not keyword at the beginning remember earlier we did in so we said where last name let's say is in and then when we say n we'll give it a list we'll say is in smith and it's in barnes now in this situation here not can be really really helpful so where the last name is in smith and it's in barnes now what i could do is come in here and say where it's not in smith and it's not in barnes and now if i run that again we're going to get all of the records where the last name is not smith and it's not barnes so that is very very helpful but in most cases i try to avoid negative logic because it's confusing to me and from my experience of working with other people it can get to be a little bit hard to um kind of nail down when you're working through more complex code so i try to avoid it when it makes sense and when i can do something a little bit easier to read all right so we did not in we did like earlier you can also with the like statement so remember how we said where last name is like and then we did something like s percent and we ran it that works and it gets us all the last names that are like s we already know that we already did this earlier you can also say where last name is not like s meaning return all the rows where the last name does not start with s right and so then i can execute this query again and we're going to get all of the records from that table where the last name does not start with s and so we're just kind of building on this concept of using that not keyword in order to return kind of the opposite of what you might expect here so not in not like not equal to you can use that in many many different places now we're going to transition a little bit that was working with the where clause and we're going to keep working with the where clause but now we're going to take a look at working with null values right so for example i might have the first name the middle name so i can get this working for me correctly and the last name right and if i run this query right here and i hit f5 this is going to return every single row from my person.person table 19972 records right all right however you'll notice here that we have a lot of records that have a null value so what if and this goes back i think tom asked earlier somebody asked earlier how do you return the rows that are that don't have null values so i could come in here and say where right where middle name is no now this will only this is where you use is or is not instead of equal because null is an unknown null never equals anything so you'd have to say where middle name is null you can't say where middle name equals null so we'll say where middle name is null this will only return the rows from this table where the middle name is null and we'll see down here at the bottom that's 8499 rows you can also do something like say where the middle name is not null by typing in this right here and that will return all of the rows where the middle name is not null so i'll go ahead and hit f5 again and you'll see that we're now returning all the rows where the middle name is not null all right pretty cool stuff there but you do have to be aware of the fact that a lot of databases almost all databases somewhere have no values in them and you have to account for that when you're writing your sql queries especially if you're doing something like where middle name is equal to b right so watch this if i say where middle name equals b and we're going to write down the number of rows here and my my head is going to be blocking the bottom right hand corner but if i run this down in the bottom right hand corner it says that this returned 291 rows right 291 rows if i change this and i say where it's not equal to b and i run it again it's going to tell me down here in the bottom right that it is 11 182 rows now stick with me this is really important so when the middle name is b it equals 11 291 when it's not equal to b so when b is not equal it's going to be 11 182. well here's the thing our data set has 19 000 rows of data so where are the other 8 000 rows of data the other 8 thousand rows of data do not get picked up when you do an equal and a not equal because null is not one of those null is unknown so you almost have this like three predicate logic you have the the equal the not equal and then you have the is null so if you want to get the full 19 000 rows and you're writing your logic like this you have to include not null somewhere now you could say where middle name is not b and watch that and remember that and middle name is no and now if i run that that is not going to give me actually oh or sorry about that i messed up or middle name is null and if i go down to the bottom now we're now going to see that we have 19681 not the 11 000 we had a minute ago so now we're taking in account the null values very important to not underestimate this i've been on some big consulting projects where we had hundreds of lines thousands of lines of code and whoever was writing the code forgot to take into account the nulls and we would spend hours trying to validate and figure out where the problem was and just to find out that that's why the numbers weren't adding up so be careful because that can definitely make a big difference all right let's see here all right this is good this gets us right where we want to be we're moving on to the next section that's gonna be module four so i'm gonna go back over to once again our class labs and in the class labs we're gonna now open up joins joins union and cast all right so this is this is something you're going to be doing normally when we do our uh we do a free we used to do we don't do it as much now because of how often you know we haven't been traveling and stuff but we used to have a lot of people come to our office and we did something called the foundations class and it was a full week of sequel that we did for free for people who kind of were in the military changing jobs um and stuff like that and so they could get an opportunity to start with this technology and where people would really get caught up was day three when we got to joins because joins can be a little bit confusing right so drag it in once you drag it in you'll have that sql query right there that you can use obviously i'm going to keep working with this query right here and we'll just keep working through that in fact let me open up my notes over here all right so let's talk a little bit about what a join does right if i am looking at let me kind of modify this real quick so i'm going to come over here and get information from my sales dot sales order header table all right and then up here in the top i'm going to get some information from that table so we'll call this our customer id from my sales table and then i'm also going to return my c dot account number well we don't have that yet we'll get that in a minute let's also just go ahead and return our sales order id all right now if i run this this is going to give me some really good information in fact let's grab the order date as well all right so we're going to go ahead and hit f5 and this returns my customer id my sales order id and my order date which is great but you are building this query for somebody else on your team right you follow me and the other person on your scene says wait a minute i want to see the customer name and you're like well the name isn't in this table right like just to be clear if i come up here and i return all columns from this table the customer name is not in it the customer name is stored in a different table right so how do i get the customer name and the customer information from that table into this result set so i can see both that's where joins come in we join the tables together if you're familiar with excel it's a vlookup if you're familiar with dax and power bi it's the equivalent of doing a merge right or if you're inside of dax it's the equivalent of doing like related or a lookup so what we're going to look at here is how do i do a join now there's a couple things i want to talk about let's do the join first so first and by the way there's different types of joins there's inner join left outer join right outer join full outer join then there's some kind of non-equi-joints i'm not going to get into that level of complexity today so we're going to focus with just inner joins we do have some other youtube videos that i've done long time ago where i think i'd do an advanced sequel video from a few years ago that kind of probably talks about this more but i just don't have time today to dive into that so do be aware there are different types of joins and it does make a big big difference but i'm going to do what's known as an inner join and i'm going to type right here and say inner join and i'm going to join the sales order header table to my sales.customer table so cells.custom all right now you also have to tell it what column they're related to so think about this for a minute you're looking at a page so you pull out a page of information you're looking at that page and you go down the list and you're like oh i see this order right here and it has a customer id and then you know over here you have another customer table so you're going to go look at that customer table and you're going to look for that customer id once you find the customer id then you can find their name and everything else so the condition that we join these tables on is the customer id the customer id lives in both tables so i have to tell it i want to do it on condition on customer.customerid salesorderheader.customerid dot to now i can't just say customer id because that's going to cause confusion for the query because customer id lives in this table and customer id lives in this table so one thing i could do is i could type out you know sales sales order header dot customer id and that will kind of work but it's a lot of work i don't want to type all that out so what i'm going to do instead is remember how we aliased column names earlier you can do that with tables so instead of typing out sales order header i'm going to give this a name and i'm going to say all right sales order header let's give it a new name of soh sales order header just to make this query easier i'm going to do the same thing for customer as well we'll say as right and we'll just say cuss all right and so now i can come down here and say all right let's do the join on soh dot customer id equals cuss dot customer id and then up here at the top this is a problem because i'm telling it to return customer id but the query says what customer id the one from the sales order header table or the one from the customer table right and the one that we want is actually going to be the one from the customer table so we'll do that now i also from the customer table i also want to return here the account number i guess maybe i don't have the name in there but we can grab like the account number maybe that gives me more information so we're going to return the entire purpose of doing this join was to now get information about our customer in the final result set along with their sales order id now when we get to aggregate queries here in just a little bit you're going to see where this really adds a ton of value because we can get the name in addition to the total number of purchases the total number of transactions their last order date all of that we can return in our aggregate query but if i run this query now we just did a join between those two tables and so now i have the sales order id and the order date from my sales order header table and i've brought in and i'll put that in red the customer id and the account number i've brought that in from the customer table and we did the join on customer id so i know the information as long as my data is valid and not bad i know the information is correct and so now i'm getting more information about that right so that is an inner join now by the way if you don't specify inner join right here it will do an inner join by default so you don't have to specify inner join although i would recommend it just to be more explicit with your code but if i run this right here it's going to give me the exact same results but keep in mind there are different types of joins there's left outer joins right outer joins inner joins so you do want to be a little bit more explicit with your code when you're writing this out all right so the next thing i want to look at here is and i'm going to slide down a little bit we're going to jump into i'm trying to look we're going to jump into union so joins are quite complex we definitely spend a lot of time in our dax classes online and our boot camp talking about joins the different kinds how to find bad records how to find duplicates all kind of stuff like that but what i want to do now is talk about something else we're going to talk about the union function now the union function allows you to bring two tables together so pretend that you have a historical sales table or a historical products table and you have your new products right and you want to add all of your products together in a single table well how do you do that you can use the union function now there's two different functions we're going to talk about here one is called union and one and the other if you will is union all so if i did select one and i ran that query right there select one is going to return select one just one just a literal value of one now if i want to combine that result set with another result set what i can do here is i could say union and then my other result set would be select two and when i run this i'm joining them together into one single result set right so i have two rows of data now instead of one now the interesting thing about this is watch this i'm going to do let's make this union all for just a minute let's add another one i'm going to do union all again and i'm going to say select one so when i run this you should expect that we're going to have three records at the end we have one two and then we have one right so we have three records now if i change these from union all to just union you're going to notice something very interesting when i run it we now only have two records so by default you got to be very careful with which one you use here by default when you use the union function it will return it will get rid of all duplicates right so since one was a duplicate and this is sometimes a really good thing when you're joining two tables together that might have a lot of duplicate data because you have you know different systems that are storing it union can be really great to get rid of those duplicates automatically union all will keep all records regardless of whether there's duplicates or not so you do got to be aware of that now let's look at this a more practical example here and this one let's see this one's a little bit longer so i'm going to copy it over so we don't have to type it all out and i'll just talk about it but this is where you can combine information from three different tables so let me zoom in real quick and this is the exact code that you have in your notes in your lab documents so what i'm doing is selecting the business entity id i'm combining the first name and the last name as the full name and then i'm saying this is employee i'm going to do a join over to my person person table on business entity id equals business entity id now once again you're new to sql today this is going to be a little bit complex for you to look at but so don't worry about it i'm going to take all of that result set right there so if i run that i can run it independently it's going to give me this right here i'm going to take that result set and i'm going to do a union all and i'm going to combine that to this table right here that has all of my vendors purchasing.vendor and then i'm going to join that to this table right here that has all my customers so i'm taking all of the people that are in my database the the employees that are in the employee table the vendors in the vendor table the customer and the customer table i'm taking all of that and i'm going to put it into a single table that has my employees my vendors and my customers right so if i come back out and i run this code right here and i hit f5 and we go through it you'll notice that we have employees right there at the top let me see if i can find a quick little cutoff here so you can see it yeah it's taken way too long there we go employee vendor right there so you got that cut off and then customer right there so we have all the records from our customer table all the vis records from our vendor and our employee table or whatever it was so we have all of those users in a single table right and so union is a set based operation it's one of many that we'll use inside of sql but it's an easy way just to combine tables together once again if you're from power bi desktop in power bi desktop this is the append operation when you're appending data together to kind of match that data up all right so that's the union and the union all you can also do um a union between data sets that in some ways don't even make sense so be careful with that the other criteria to a union that you should be aware of is when you union multiple tables together they need to have the same number of columns so if i were to go to my first result set right here that has three columns business or two column well three yeah business entity id and then it has my full name right here and then it has employee if i were to add a column to that and let's just say i added the hard coded value of one as as a random new column so i add a new column here if i run this code again it's actually not going to work because it's telling me that all queries that are using functions like union intersect or accept operator must have the same number of expressions or same number of columns so that is a very big thing to be aware of whereas in like power bi if you're using this functionality with the append operation in the query editor that's not a requirement so that's a little bit different there also you want to put them in the same order so whatever the first column is here it's going to be mapped to the first column here so make sure the order of those expressions are the same in each of those different result sets as well all right so a question here from jennifer jennifer says when would you use a union versus a join so they're significantly different right a union is where you take two result sets and i have a hundred rows here and i have 100 rows here and i want to have 200 rows that has all the rows of data like this example so that's a union a join is significantly different because what a join does is allows me not to add more rows but add new columns so maybe i have a customer table that has the customer name the customer email the customer phone number and i want to add to that result set the total sales for that customer the total number of transactions for that customer the last order date that would be new columns that we're adding to that result set we're not adding new rows right so a join is when you want to get information from a related table and you want to add it there as a new column a union is when you want to combine those two tables together all right so hopefully that made a little bit of sense it does take a while just to get familiar and comfortable with those all right so i'm looking over at the notes real quick union union all we took a look at that that looks good okay so one more thing i do want to take a look at here that is pretty important is cast operation so doing a cast so watch this if i were to say i'm going to delete all of this and i'm going to make this very simple if i said select one and then i said union all and i said select a we now have a expression that has two different data types this is an integer and this is a text value and so what it's telling me down here is i'm getting a failure message so for those of you who maybe haven't written sql before and you've worked with like power bi the dax language is very forgiving because dax doesn't require you to always convert your data types sql is not a lot of programming languages like net and sql are not very forgiving you have to specify what the data types are and you have to convert them and so right here it's saying look you can't combine these two together because it's trying to convert the letter a to a data or a numerical value and that doesn't work so what i'm going to do is come up here and change the value of 1 to a string value so i'll come in here and say cast right cast the value of one as varchar all right and so you can do something like this where you say just cast it as var car actually don't need that there and i'll say as id so i'll give it a name so that we have a name at the end and now if i run this query again now it works i can have both the value of 1 and the value of a in the same column because they're both string values okay so you will have to do things when you're writing sql and you're trying to combine columns together if you're trying to combine one plus a that might cause a data type problem so you'd have to convert the integer column the product id the business entity id whatever it is convert it to a varchar a string that's what a varchar is and then you can combine it with the numerical value all right yes michael says can you just mitchell you're making this too difficult can you just put quotes around the one yes you can right so i could technically just say one and that'll work that wouldn't work for a column if you were pulling a column from a table like business entity id but in a situation like this when we're hard coding the values michael yes you could just put quotes around it good question all right so will the access to the database remain after this class the answer to that david davis is yes the database will be there after the class is over we use this one for a lot of different training events we do on azure power bi sql so it will be there and it will be available so feel free to come back and go through those demos again now we're going to move into the last section here probably the favorite section the best section where we get into actually writing aggregate queries so that's going to be the next query in your results set it's going to be number five right so we're moving on to module five where we're going to be writing aggregate queries so go ahead and open that up double click on it drag it into ssms and you will be able to follow along also with this next section now this is pretty cool i like aggregate queries and you will be doing a lot with aggregate queries also i should tell you my favorite functions in all of sql are window functions now we're not going to get into window functions today they are a little bit more advanced but i love window functions and you can do some really cool things with them so let me do this i'm going to go over here and we're going to write a new one we're going to do a okay so when we're talking about aggregates we're talking about doing things like count min max sum just basic aggregate queries right so for example if i say select oh what just happened there messed up control z all right select from the table and the table we're going to use is going to be a table that has a lot of aggregate data in it so we're going to go with sales order header and what if i want to just find out what is the number of total transactions in that table i just want to count the number of total transactions well what i can do is i can say count star so just count you know everything in the table from sales order header and when i run that it's going to show me right here that that table has 32 332 rows in fact i can give this a name and i can say something like as total rows or total transactions in that table now you can also be more explicit and you could do something like count and you can add a very specific column like sales person id and that should unless you have null values once again this is where we talk about blanks or nulls unless you have null values this should give you the exact same result as total rows so i could call this one as count of salespeople all right now we're not doing a distinct count we're just doing a regular account so the numbers that we get should be pretty similar here but they're not and so this is where you got to start looking at your data finding out if i have blanks if i have nulls what's going on with that right and so this is another way you can count a specific column now what i want to show you is a couple of different functions that we can do and so we've got the total rows here i'm also going to do a sum and we're going to do a sum on our total due column so there's a column in there called totaldue that's how much somebody owes us from that transaction and we're going to say as total sales right so that's our total sales we're also going to do a min on that exact same column here so that's going to be min of total and that's going to be as the minimum cell so what was the smallest cell that we had across all of those records right so that would be our smallest cell or our minimum cell then we could do maximum here so just showing you a bunch of quick aggregate queries to get you familiar with them of total do and we're going to say that's our maximum cell and then for the final one we're actually going to do average of total due and that'll be as average cell all right so if i run that query real quick and i come down here to the bottom this is going to show me the total number of transactions the total amount of sales the minimum cell one dollar and 50 cents the maximum the biggest order that we had 187 thousand dollars in our average sale in fact if we were to take the average cell and do it across our total rows it might come pretty close to what our total sales were right so this gives us a lot of aggregate information now there's a little bit of a problem with aggregate queries that really throw people off okay so this is just basics basic stuff here you have that exact example in your sql query that i provided let me clean this up though real quick i'm going to get rid of everything here and then we're going to do another query and what i want to do in this one is i want to say all right let's do customer id and then i'm going to come down here and say let's sum up so instead of just getting the total sales for all customers or all products i want to get the total sales for my i want to get the total sales per customer the sum of sales per customer so we're going to say sum and i think the column is called total due right and we'll say as total sales but if i run this i'm actually going to get an error message and this throws a lot of people off if you run a query where you're doing a select statement and you have any items in your select statement that are not aggregated and you have items that are aggregated you must put that item in a group by clause right so for example if i comment this out let me get rid of this real quick if i comment out this code and i run it it runs fine no problems right i get my customer every record for that customer and it works but as soon as you introduce aggregations minimum maximum sum average as soon as you introduce those you now have to start thinking about the group by clause and so for example the sum is an aggregation it's good to go but the customer is not we're not doing minimum customer we're not doing maximum customer we're just doing customers so what you have to do is go down here and type in group by and then you want to group by the customer id now i'm even going to take it a step further and let's say order by and we're going to order by total sales descending so we're going to order by this new column that we created now it might be complaining about it because it's saying hey we can't order by the column that you just created here so you might have to actually type out some of totaldue which i would not expect it to do that unless it's complaining about the group by needing to come later let me see here but i thought order by was last yeah it's odd let me mess with that here in just a second but let's do the group by first and then i'll come back and show you how we can do that so we'll do this hit f5 and then what you're going to get over here is every customer id is going to show up one time and only one time only because we're grouping by the customer id so whereas customer 11 000 might be in fact let's do the order by on customer 11 on customer so order by right customer id if i hit f5 here and you come back down you'll notice that customer id even though 11 000 really shows up in this table many many times we're only getting one record because we're grouping by that customer and we're getting their total sales also if i want to know the total transactions for that customer i could do that i could do count star right as total transactions right how many times did they show up in the order header table how many times have they ordered so i think 11 000 that customer should be three if i remember so if we run it again and you come down here that customer has three total transactions they've frequented our business three times and so you can continue to build on this but just be aware if you come in here and you add any columns to this select statement that are not an aggregation you're going to have to add them to your group by clause right and that's going to be pretty important to be aware of all right so let's do another example here order by two works yep all right so let's do this i'm going to come over here and instead of doing customer id let's say we want to look at ourselves by year right so instead of doing it by customer we want to look at ourselves by year so we're going to use a function we learned earlier in this workshop we're going to use the year function right so we'll do year and then we'll come in here and say we want to pull the year from the order date just making sure i got the columns correct all right and we'll say as order year put a comma in there there we go now this is going to be this is not an aggregation this is not an aggregation so if i run this query real quick let me get rid of all of this if i run this i'm going to get an error message again and it's telling me that the order date which is appearing in my select clause is not aggregated so this is not going to work so this expression right here has to be put into a group by clause if you want this to work right so this is where a lot of people who are new to sql really get frustrated and aggravated is doing joins and doing aggregations all right and so what we're going to do is go down to the next line and we're going to say group by and we're going to group by the year of the order date all right we're going to be very specific and then we'll hit f5 and now when we come down here i can see my sales by year and the total number of transactions that occurred each year right so we're doing these aggregate queries and getting some really good information here inside of that all right so this is good let me look back over at the notes we did that right there did that that's perfect oh this is a good one all right so let's circle back to the where clause this is pretty good so let's say that this query is fine i don't have any problem with this query let's say that you only want to return rows where the sum of total due is greater than 10 000 right in fact let's do switch it back to customer id so i'm going to switch it back to customer id i'll put a comma in there we're going to group by customer id down here all right and then we're going to go ahead and order by customer id again so you can see the results so let's do order by customer id all right so we're going to do it again all right f5 to run that this looks great but you're going to see that some customers have 9 000 in sales some have 125 dollars in sales and then if we go down further we'll see some that have 10 000 in sales 12 000 in sales so we have people that are all over the place so here's the request your manager comes to you and your manager says i want you to return a list of all of the customers that have nine thousand dollars in sales or greater and you're like oh no problem i already have the query i just need to filter it down to where the sum of total due is greater than 9000. like that's super easy right so there's a couple ways you can try to do this first of all i'm going to say where one thing you've got to get used to at sql also is the order of where you put things like the where clause the having clause the group by the order by there's different ones here but i'm going to say where and let's just do total do so i'm going to say where total do is greater than 9000 right and if i hit f5 that will work that will work but it's only going to work for single transactions well actually no if there is total do it'll work but what i actually want to do here is i don't want to do total due i want to do the sum of total due right because total due is individual transactions that occur so we're going to say sum of total due now this is going to cause a problem i'm trying to look and see if anybody is already telling us the answer and they're not but i'm going to do where the sum of total due is greater than 9000. now when i run this we're going to kind of get some unexpected results right so we'll do f5 wait a minute we're getting a failure it says an aggregate may not appear in the where clause unless it is a sub query we're not getting into sub queries today but that kind of falls into that category of temp tables and ctes and derived tables in a having cl oh a having clause okay so what it's telling me is we actually need to do this in a having clause not inside of the where clause all right so if you're doing some form of aggregation and you want to filter down by the aggregation you actually have to put that in something known as a having clause this is going to take you just a while of writing code to get used to but if i want to filter by this result set directly from my select clause i need to do the having now if you were to put this into a temporary table or a common table expression or a sub query which is what it's saying and then you select from that sub query then you could say where total sales is greater than nine thousand but since i'm doing the where clause immediately after the select that's not going to work and so i'm going to go ahead and get rid of all of that right there and then i'm going to go after the group by clause and we're going to say all right having so this is where you'll type out having having a sum of total due greater than we could do equal to if we want 9 000. all right so where we have a sum and i'm going to put this on the next line greater than or equal to 9000 if we run that we are now only going to get a list of customers where the total sales is greater than 9000 period you will not see and that's that's across all their transactions not just a single transaction right so if i have a three thousand dollar transaction i have a five thousand dollar transaction i have a two thousand dollar transaction that's going to be very very important here now one of the things that you're noticing as we go through this is that where you put your select statement your from statement your group by is going to matter so if i try to do the having clause before the group by doesn't really make a whole lot of sense right first you have to group it to do having clause because it's getting grouped so you'll see i get an error message here and it says incorrect syntax near group and the reason it's incorrect is because the having clause needs to come after the group by now the where clause would have come before because first you filter it down so let's let's spend a couple of minutes here getting a little bit more advanced all right um if you come in here and get rid of this right here the basic syntax if you are doing a sql query you always want to have a where clause in there and the where clause is going to filter down the result set really most times before you do other things so for example if i am writing a basic query and in that query first i'm going to tell it from and you tell it the table that you want to pull data from right and then the next thing you will do is essentially you're going to filter it down and say all right remove these rows from table makes sense and so that's kind of our order of operations where we're going to be starting here so first select the data from this table then remove these rows you don't actually get to the select statement until after the rows have been filtered right and that makes sense because i don't want to do a sum or a max or a min or an average over a billion rows and then filter them out afterwards so this is very effective from the sql execution plan that gets generated it's going to look at the from it's going to filter it down then it essentially starts working on the select statement after that okay and then it'll go through those other operations where it does the group by it does the having clause it does the order by and so in the order of operations if you're looking at this the order by is going to essentially be the very last thing that gets executed within that so everything gets done everything gets performed so then you can run your order by clause at the very end right and so this is the fundamental order of operations there might be a little bit in here that i didn't cover just really quickly off the cuff but it is important to understand the order and that order affects performance but it also affects how you have to write your sql query when you start getting into things like group by clause order by clause where you know what comes before what in that statement all right all right so robin says this is great stuff i love it perfect best comment of the day yeah everybody who works with data you need to write sql right you need to understand the fundamentals because sql isn't super difficult all i'm trying to do is retrieve data from the database now there's a lot more you can do with sql creating tables writing you know stored procedures creating views inserting data updating data but for what we're talking about here today just extracting data and retrieving data from a database you're just writing where clauses you're just writing select statements and pulling information out now let me look over at the notes again we just did the having clause you can also have both the where clause and a having clause in the same one in fact let me just look at this real quick with you so on the cells order header table i'm going to come over here and i'm going to add in a where clause and i'm going to say where and we're going to do territory id right now the territory id that i want to put into my where clause right here when i'm doing the where and the having clause this is not something i'm doing an aggregation on i'm not getting the minimum or the maximum or the sum so therefore it doesn't go into the having clause right it goes into the where clause like a normal filter would 95 98 percent of the time you're going to be working with the where clause you're very rarely going to use the having clause um and that's just because of the complexity of how you will write sql statements but you do write it from time to time like we did here so i'm going to say where product or territory id equals one execute that code and this works perfectly so i'm only so here it is walk with me through this one more time first we're getting all the data from sales order header then the engine is filtering down and getting rid of all the rows of data that are not territory id one then it's doing a sum in fact you'll notice customer id 11 000 all the way through 11 011 they apparently never had a transaction in territory id1 they do not get returned in the result set so after all of those rows are filtered out then we come up here and we select the customer id perform the sum perform the count and then you'll do like your order by and all of that right so this is very very important to understand all right now you can also have a where clause and a having clause in the same statement so for example we have a bunch of customers that have like 89 in sales 125. if i want to exclude them from the final result set we can't do that in the where clause remember we do a having clause having sum of total due greater than let's say 2500. so i'm going to go under the group by clause here all right we're going to go to the group by clause we're going to say having right so having let's do sum of total do greater than or equal to 2500. all right and we'll hit f5 again and then we come down here and now we're only getting records where the territory id is one where the total of the sum of the total due is greater than 2500 customer id total sales total transactions this is a ton of great information here this is how you do aggregate queries group by having all of that all right let me see here i think we are very close to the end of the day but i'm going to do one more query here one more query and then i'll kind of start doing some wrap up and maybe some final questions here at the end of the day give you some time back in your day so the last thing i want to do is instead of doing a having clause which we just did i might also want to do something like a having clause on the count right so maybe i don't want only and i'm doing this a little bit different than what you have in your notes but maybe i want to do where the total due is greater than 2500 and the total transactions is greater than one right so think about that for a minute how would i do that if i wanted to return only the records down here where the total sales was greater than 2500 and the total transactions is greater than one how would you modify your sql query to do that that's the question right the way that i would do that is i'll come up here and say and so this is going to be in the having clause and not or and total well we'll type it out so let's do and count star is greater than one we'll go ahead and hit f5 and now we're only getting the records right here where the total is greater than 2500 and the count is greater than one now see we're getting all kinds of garbage in the chat window there all right so that really brings us to the end of the session today we got done a few minutes early answered a ton of questions thank you manuel for your help in the chat window that was awesome manuel's going to feed me a few questions over here because i'm not going to be able to go back through it all what i do want to say as we're wrapping things up and i'll we'll probably get done here in about five or so minutes is that you have access to the class files the database and the server you're connecting to is going to be available i do want to remind you also that for the people who are on this call that we do have a sale going on and you can purchase our on-demand learning for half price right now we have two awesome classes that dive way deeper into sql than what i just covered in three hours you get you know 20 hours of recorded content with no interruptions whatsoever and we have a sql bootcamp that you can join as well but part of that subscription we have over 70 classes that talk about microsoft azure um power bi sql server ssis ssas just a ton of different things that we've been teaching for many many years so it is an incredible value and we would definitely encourage you to sign up right away thank you everybody for joining us today i hope you got the most out of it manuel also has a certificate that you can fill out a form for as well as a survey we would love to hear from you and hear what you liked what you didn't like and what would you like to see from us in the future on our youtube channel let us know and keep us informed can we extract the data and export to a csv file using sql um that's a good question i don't know about that one can i extract the data using sql not to my knowledge i don't think i've ever done that i've done it with r i've done it with python but with sql i don't think there i mean i think there's a way you could right click down here and you could copy the data so the easiest way would probably be to come down here right click save the results as or copy data right here in the result set so if you want to do it that would probably be the easiest way programmatically probably not i'd use a tool like ssis or some kind of etl tool for that yep all right so yeah melissa said the same thing just copy and paste good job thank you for everybody for the great comments just trying to look through the comments the questions can we pay monthly yes we do have a monthly subscription plan you can sign up and pay each month individually instead of doing yearly and you can still get a the discount with the monthly subscription so great question there thank you so much thank you julie appreciate that so there's no major difference yeah so good question so t sql is transact sql and that's the microsoft flavor of ansi sql but you have t sql which is transact sql for microsoft you have pl sql which is for oracle they all have to adhere to very specific rules so if you write your code in a specific way it can actually translate from t sql to pl sql no problem but yeah t sql is microsoft's flavor it's kind of like thinking of somebody from america how we might pronounce things and say things and somebody from england right there's you can still understand both people if you speak english there's just maybe an accent there and there's some differences yeah but you'll see sql in a lot of places like when i write notebooks in azure there's spark sql when i uh connect to a force there's soql when you uh actually writing and pulling data from data lake using serverless on demand you use uh sql to do that so sql is used in a lot of different places to pull information from that adrian interested in the monthly subscription great i'm happy to hear that awesome awesome all right i think that is going to wrap up the class i'm going to do a follow-up youtube video i'm committed to it there were a ton of questions manuel did a great job maybe manuel will join me and we'll do one together we'll do a follow-up video where we'll answer a lot of these questions there so i hope to see you in that follow-up youtube video enjoy the rest of your

Channel: Pragmatic Works

Views: 198,286

Keywords: pragmatic works, t-sql, query language, t-sql fundamentals, beginner to t-sql, intro to t-sql, what it t-sql, t-sql full course, t-sql tutorial, t-sql queries, table queries, multi-table joins, select query, sql server, microsoft sql server, microsoft sql, sql (programming language), how to query microsoft sql server, learn t-sql, SELECT query, UPDATE query, INSERT query, t-sql overview, basic tutorial on t-sql, t-sql for beginners, tsql, transact-sql

Id: cACat4KNncg

Length: 165min 54sec (9954 seconds)

Published: Thu Apr 07 2022