Skip to main content
Loading Explore guides…
SQL for Data Analysis · Acedra
Home
Explore
Computer Science
SQL for Data Analysis
Sign in to save
Chat
If YouTube asks you to sign in, open the video on YouTube.
Watch on YouTube
Reload player
Transcript
100 segments
Copy
Hide video
0:00
Hi everyone, Kevin here. Today we are going to walk through step-by-step how you can use SQL or ‘sequel’ for data reporting and analysis. I'm going to assume you have absolutely no background in SQL, and we're also going to use all free tools, so there's really no barrier to learning. By the end of this video, my goal is that you will know how to retrieve data from databases, and if you stick around until the end, I will also share a really neat trick
0:32
that helps you write some of the most complex queries with very little effort. So first off, why would you even want to learn SQL? I mean there are so many other videos that you could be watching on YouTube. Well, all businesses keep track of data in databases, and when I worked at Microsoft as a Product Manager, I used SQL all the time to get insights from data. I worked on the website office.com, and I could write a SQL query to tell me how
1:01
many people clicked on this button, and I could use that data then to decide, well, should we make this button stand out a little bit more? Should we move it to a different location? Without being able to get that data using SQL, I would have had no idea what to do. Your data contains all sorts of hidden insights, and with SQL, you can unlock some of that hidden value. In this video, we're going to focus on how you can retrieve data from databases. This is really perfect if, say,
1:31
you're a business analyst or a product manager, or maybe you're a researcher. We're not going to focus on how you can create a new database, or how you could add new tables, or even how you can add or remove data. If that's something you want to see in a future video, let me know down below in the comments. What is SQL? Well, SQL stands for structured query language, or SQL. It's a language that you can use to interact with databases. You can retrieve data, you can filter,
2:05
you can sort, you can add, update, and remove data from a database. The great thing is that anyone can learn how to write SQL queries. You definitely do not need a degree in computer science, and once you learn SQL, this will be another superpower that you have in your toolbox at work. So, what is even a database? A database is a collection of tables, and those tables have relationships, and don't worry if
2:37
that sounds confusing, we're going to make it real coming up shortly with an example. If you look at any major website, whether it's Facebook, Twitter, LinkedIn, all of those are powered by databases. Even here at the Kevin Cookie Company, we use databases. We track our customers, our orders, and all of our product information.
2:58
What, you thought we just wrote all that down by hand on paper.
3:05
We recently transitioned over to databases. To interface with a database, we are going to use something called an RDBM. A what? Well, that stands for relational database management system. I know they get kind of crazy with their acronyms. You have a lot of very popular options, and you've probably even heard of some of these before. You have Oracle, Postgres SQL, Microsoft SQL Server, MySQL, is just a few of the many options.
3:38
In this video, we are going to use Microsoft SQL Server, and the reason why is, well, first off, I'm a Microsoft boy, so I'm a little biased, but also it happens to be one of the most popular options and when you're learning, the tools are completely free. Also, once you learn one RDBMS, there's that acronym again, you'll be able to easily pick up any other system. You'll know the essentials of SQL, and there may be some slight syntax differences or the
4:07
way the code is written, but you'll understand all of the core concepts. To get started, we're going to use Microsoft SQL Server and you can download that at the following website. You'll find the link up above, and I've also included a link down below in the description. On this website, if you scroll down just a little bit, we have two different editions that we can choose from, and for this tutorial, either one will work.
4:33
You can get either the developer edition or the Express edition. But what is the difference and why should you choose one versus the other? The express version can be used for business purposes, but it's a little bit more limited. You don't have the full functionality, your database can only be up to 10 gigabytes in size, and it only supports 4 cores. But once again, for this tutorial, that's totally fine.
4:58
Then you also have the developer version and this cannot be used for business purposes. It has full functionality, but once again, it can only be used for development and testing purposes. For this tutorial, I'm going to download the developer edition, but once again you can choose either one. When you kick off the install, you'll see a prompt that looks like this, and you have three different options. You can proceed with basic, and this is what I'm going to do,
5:26
but you could also choose custom where you get to choose what components are installed. You also have the option that says download media. This is if say you want to install SQL Server on multiple machines, you can simply download the installation media and then you can use it elsewhere, so you don't have to re-download. Let's continue by clicking on basic. Once you finish installing, you won't see any icons on your taskbar or on your
5:51
desktop and you might be wondering, well, what did I install and is it even running? Let's click on the Start menu and then click on all apps and here let's navigate down to M and right here I see that I've now installed Microsoft SQL Server 2019. When I drop this down, we have a few different apps. Let's click on the one titled Configuration Manager. This opens up the Configuration Manager and we're not going to make any changes here, but I want to
6:20
show you that the service is running. Here we can see that SQL Server is currently running. Right up here, we can stop it if we want to. We could also restart it. You can pause it and if it were stopped, here we could start the service again. Right over here, we can also see the start mode, and it's currently set to automatic. This means that any time you boot your PC, the SQL Server instance will automatically start up.
6:45
But once again, we simply open this just to confirm that SQL Server is currently running. We can now close this out. Now that we have our server running and our database engine in place, we also need a graphical front end that will allow us to write queries against our database, and for that we are going to use a tool called SQL Server Management Studio or what's also referred to as SSMS. Head to the following website.
7:13
I've included a link up above and you'll also find it down below in the description. Scroll down just a little bit and here you'll see a link that says free download for SQL Server Management Studio. Click on that and then run through the install process. Once you finish installing SQL Server Management Studio, launch the app and you should see an interface that looks like this. A prompt should also appear asking you to connect to a server.
7:39
The first item we installed provided us with the server, and now this second item that we installed will allow us to connect to that server. Right here, for the server name, it should be your PC’s name, but if you don't see anything prepopulated in here, you can click on this drop down and then click on browse for more. Expand the database engine category and then you can click on your PC name. Once you do that it should fill it in in this field.
8:06
For the authentication, I'll leave it set to Windows authentication, and once all of that is set, let's click on connect. This drops us into the main SQL Server management view, and over on the left-hand side, we have something called the Object Explorer. Right up here we can click on this plug icon, and this allows us to connect to additional servers. So maybe at work you want to connect to multiple servers at the same time, you can do that,
8:33
and once you connect to additional servers, you'll simply see more in this list. If you no longer need access to one of these servers, you can simply click on this icon to disconnect.
8:44
Right up here at the top, I can see the instance or the server that I'm currently connected to, and I'm currently connected to my own computer because I set up SQL Server there. Underneath that, we'll see a tree view of all of these different folders. You have databases, you have security, and the list goes on and on. In this tutorial, we're only going to use the one titled databases. When I click on the plus icon, you'll see that there are no databases yet and
9:12
we need to create one, and to do that, let's right click on here. Now we could start a new database from scratch, but we are going to restore a database for the Kevin Cookie Company so we can use some existing data to analyze. Let's click on this. If you'd like to follow along today, I've included a sample database up above, and you'll also find it down below in the description. Probably the best way to learn is to follow along, but you can also just watch this video.
9:39
Once you download the file, let's click on this option that says device. Then click on the ellipsis and here click on add.
9:47
Next, navigate to where you saved your backup file. Here, I'll click on the backup file and then click on OK. Once again, let's click on OK, and here too, I'll click on OK.
10:00
Here I see that the database KCC restored successfully. That's great. Let's click on OK. Over on the left-hand side, you'll now see a new cylinder icon. This is the database icon, and the database is called the KCC for the Kevin Cookie Company. I'll click on this plus icon, and this expands it. Within here, we have database diagrams, tables, and the list goes on. The one that we're most interested in today is the one titled Tables.
10:30
Here I can click on the plus icon, and we see that this database has four different tables. We have one titled Customers, Order Product, Orders, and also Product. You might be wondering, why do we break a database up into separate tables, can’t we just have everything in one big file, and here's what that would look like, and if you look closely, you might start to notice some issues here.
10:56
Here we repeat the customer name again and again, and all of their address and contact information. We also repeat the cookie name again and again. Instead of repeating this information again and again, we can break it up into separate tables. So here for example, imagine I have an orders table and for this order it's for customer 5. So instead of putting all of that customer’s information here, instead I can say oh customer 5,
11:22
and then I can jump to another table with all of the details on customer 5, and here's all of their contact information. This is the core idea behind databases. We can use various IDs to connect together these tables and that's why it's referred to as a relational database, because we have relations between these different tables. When you're designing a database, you want your data to repeat as few times as possible.
11:48
The benefit of that is that it takes up less space and it's also easier to get back to data, and this process is referred to as normalization. So here I've now separated all of the data out into separate tables so there's a Customers table, an Orders table, a table with all of the Orders and the Products in those orders, and then one table with all of the Products, and if you look at these four tables, this takes
12:12
up a lot less space than this one massive flat file. Back within SQL Server Management Studio, here you'll see our four different tables, and each table name starts with dbo. This beginning portion is the default schema name, and the schema is simply a way that you can bucket together different tables. You can also use it to provide access to certain tables, so certain users might have access to
12:40
one schema and other users might have access to a different schema. For this tutorial, we're simply going to stick with the default of dbo. Let's now look at what's contained within one of these tables. Here I can right click on the customers table, and I can select the top 1000 rows. Here when I select that, here you'll see the SQL query that gives me the results or all of the data within the table, and in a moment, we'll come back to how we can write these queries on our own.
13:09
But first, let's look down below at the results. Here, this looks similar to what you would find in, say, a spreadsheet. You have a whole bunch of columns, and you also have a number of different rows. These columns here, within a database, these are referred to as fields and then the rows are referred to as records. Within the table, you might also be wondering, why do we have separate columns for all of this information, like the address,
13:34
the city, the state, and the reason why is if we just put it all together in one big column, it would be harder to get back and to query against that data, so if we break it down to the most granular level, that makes it easier to access that data again in the future. Now that we've looked into one of our tables, let's take a look into another one of our tables and look at how we'll make a connection between them.
13:58
I'll go over to orders, right click and here too, just like we did before, let's select the top 1000 rows. Here once again, we see the SQL query that gives us these results. So here we have all of our order information, so we have orders 1 through 50, and here in this table you'll see that it references a customer ID. So here this order number 1 references customer ID number 5. If I go back to the customer table, here I can see who customer #5 is,
14:30
so we can relate these tables using the customer ID. The customer ID in this table also happens to be what's called the primary key, and this is an important concept for databases. The primary key is the minimum number of columns that you need to uniquely identify a record. So, let's just work through this. Could you use the customer name as the primary key? Well, here you have Tres Delicious. So, if I say, hey, Trey Delicious, in this case,
15:03
you cannot uniquely identify the row or the record, because here we have Trey Delicious twice. We also have the phone number show up twice. You could have an address repeat. You could have a city repeat. So, you can't use any of this information to uniquely identify one of these records. Instead, we have the customer ID, and with this customer ID, you know exactly which row we're talking about and that's why it's referred to as the primary key.
15:30
In the orders table, we have two different ID but four orders. The order ID is the unique identifier and that's why it's the primary key, and the reason why is, here if we look at customer ID, you see that customers can have multiple orders and so here we see the customer ID show up multiple times, so once again, the primary key is the minimum number of columns to uniquely identify a row, and so in this case the order ID is the primary key.
15:58
Now that we've touched on primary keys and also how we relate these different tables, we can actually visualize how all these tables are connected. Go up to database diagrams and expand this category. If you want to create your own database diagram, you can right click here, and then you can create a new database diagram, but it should already be part of the database. Simply click on this option.
16:23
This opens up our database diagram, and here we can see the four different tables that are part of our database, and here you can also see all the different fields that are within each one of these tables, and right here we can see which one is the primary key. You can see this small yellow key icon. So here we can see what the primary key is in each table. In the order product table, there is no individual primary key to uniquely identify an item,
16:51
it's a combination of the order ID and the cookie ID. So here just to walk through this, for every single customer, you can have many different orders. So here we have the infinity symbol indicating that it's a one-to-many relationship, and for each order, you can have many products part of the order, so for one order, you can have many different products, and then for all of the different products, we have our product table.
17:18
So, for one product, it can show up in multiple different orders. Setting up a database diagram is a nice way to get a visual of how a database is laid out, and also, when you're querying against a database, it helps you understand how they relate. You can also visualize how a table is organized over on the left-hand side in the object explorer. Here for example, I have my orders table. I can click on this plus icon and here I can click on the plus next to columns,
17:46
and here I can see all of the different columns that are contained within this table, and here I see a visual indication of what the primary key is. Here the primary key is the order ID.
17:58
I can also review all of the different data types. Here I'll right click on orders and here I can select design. Within the design view, here I can see all of the different column names and I could also see the associated data type. Now this will be important, especially as we start querying. For example, for customer ID, the data type is set to integer, so it has to be a number. So, you wouldn't want to say, compare it or filter for a customer ID with a
18:27
letter because that isn't part of the integer data type. With all of this foundational information now out of the way, you're in a good spot to start querying. To write our first query, let's go up to the top bar and then click on new query. You can also press the shortcut key control + n. This opens up a new window where we can now write our query, but before we do that, let's just check a few things. Right up here you'll see a drop down and it currently says the KCC database.
18:58
When we write our query in this window, it’s going to execute against this database. If you have multiple databases, here you can choose which database you want the query to execute against. Also, to help us with writing this query, we're going to write a query against the customers table. To help us with this, let's click on the plus icon and this way we can see all the different column names. For the first query, I simply want to see a list of all of our customers.
19:29
When I look over here at the customers table, here I see that one of the columns is called customer name. I just want to see all of the different customer names in this table. To start the query, let's first type in select. Select allows us to retrieve data from a table and once again I want to get the customer name, so I'll type in select and then customer name. Here it matches the column header name exactly, and next I need to specify where I want to get the customer name from.
20:02
So next I'll enter in from and I want to get it from the customers table, so I'm going to type in dbo.customers and this is our query. That's all we have to enter in. Now that we've finished entering the query, up here we can click on execute and that'll run the query, or alternatively, as I hover over that button, you might have noticed that the shortcut key is F5. Here I'll press F5 and that runs the query.
20:31
Here we can see all the different customer names contained within the customers table, and congratulations, you just wrote your first query. See, that wasn't that hard. Now let's say I want to get more information than just the customer name. Let's say I want to see all of the notes associated with each one of these customers. Right now, I'm just selecting one of these columns, but if I want to include another column,
20:57
I simply enter a comma and then I can type in the other column name that I want. I also want to get the notes, so I'll type in comma and notes and the rest of the query is exactly the same. And now once again I want to run this, so I'm going to press the F5 key, and here now I can see all of the different customer names along with the associated notes. As you start working with more and more SQL queries, you'll likely find yourself jumping in between different databases.
21:26
Here, for instance, I'll move from the Kevin Cookie Company database to the master database. Now when I try to execute this query, I get an error message because in the master database there's no table called dbo.customers, so my query fails. If you want to prevent this from happening, instead of just specifying the table that you want to get this data from, you can also specify the database. Now remember that this database is called KCC.
21:56
So, before the table name, I can type in KCC.dbo.customers. So now not only does it specify the table name, but also the database name. Now when I try to execute this query, I get the same results that I got before, even though right up here the master database is currently the active one. Within the results, you'll see that the customer name column doesn't have a space. I can customize or define what this column header should look like. Right up above in the select statement,
22:29
I select the customer name, and right after that I can type in as and then I can insert a bracket and within here I can type in exactly how I want the name to appear. So, I want to type in customer name with a space. Now when I run this query, you'll notice that the column header now has a space. Now once again, I can type in any value into this alias. Next, I'm going to remove notes from the query, and once again, when I run this, I see a list of all of our different customers.
23:00
One thing you might notice is within this customer list, Tres Delicious shows up twice. That's because the customer has two different headquarters and we've included them in our customer table two times, but let's say I just want a list of all of the distinct or unique customers that we work with. Before the customer name in the select statement, here I can type in distinct, and then I’ll enter a space, and when I run this query again, here I only see
23:30
Tres Delicious once because it's only showing me all of the distinct values. So far, we've been looking at how we can get specific columns back, but what if I want to get all of the columns back in this table? Do I have to enter in all of the different column headers within the select statement? Of course not, we can have the computer do this for us. Here I'm going to remove this portion of the query,
23:56
and here I can enter the asterisk symbol or the wild card, and now when I press F5, here this returns all of the different columns and all of the different records within this table. Now let's say I don't want to see everything, but I just want a sample of the data. I can type in top, here I'll type in the top, and maybe I just want the top three records, and here once again, I could run this, and here I just see the top three records. As we've been
24:26
running these queries, there's a yellow bar down below that tells us whether or not the query executed successfully. Also over on the right-hand side, you'll see some helpful information telling you how many rows you got back. So here we wanted the top three, and I can confirm that we got three rows back. Especially when you start running some more complex queries, here you'll see the runtime to see how long your query has been running for.
24:51
Now that we know how to get some basic data back using queries, how can you filter the data? So, let's say for example, I want to get a list back of all of our customers in the state of Washington. Here I'll remove the top three portion of our query. So right now, when I run this, we get all the customer information back. At the very end of this query, I can enter where and once again I want to find all customers in the state of Washington.
25:19
So here I'll type in state equals Washington, and that's all I need to enter in. Now when I press F5, we only get back this one customer that's based out of Seattle, WA. To make this query easier to read, here I can insert spacing, I can also press enter, and I can add newlines, and when I run this query, I get the exact same result. So, as you're pulling together your queries, feel free to insert spaces or character returns to make it easier for you to follow along.
25:52
Another thing that will help you follow along with your code and also help other people with your code is entering comments. Here for example, I can enter two dashes and I can write a comment. Here I typed in this returns all customers in Washington state. Alternatively, instead of entering the dash dash, I could also enter a slash in the asterisks and at the end I can enter the asterisks and a slash again, and this will also create a comment.
26:20
So, you have those two different options. In this example, I'm looking at just customers located in Washington state, but instead of saying equal to Washington state, I could also say not equal to Washington state. So, show me all the customers who are not located here. When I press F5 to run, here I see the five other customers who are not based out of Washington state, and here I could also enter the exclamation mark in the equal sign. That also means not equal to,
26:48
and when I press F5, once again I get this same exact result. In my where clause, I can also use an or statement. So, let's say I want a list back of all of my customers who are in Washington state or in New York state. Here I can enter in New York and when I run my query, here I see my two different customers, one’s in Washington and one’s in New York. Now let's say I also want to see customers who are in Utah.
27:16
Now, once again, I could type in or state equals and let's now look for Utah. I’ll press F5 and here I see that I have three customers and each one is in one of these states, but especially as I start expanding this, it becomes somewhat unwieldy. Instead, I can use in. Here I'll change this to in and I'll insert a parenthesis, and let me leave Washington, but I'll remove or and state. I'll insert a comma and then I'll remove this portion and then I'll leave in Utah.
27:49
Now I'll close the parentheses and when I press F5, I get the exact same result, but the code is a little bit cleaner and a little bit more efficient. Currently, this query shows me all of my customers who are in either Washington or New York or in Utah, but what if I want to see all of my customers who are not in these states. Right here in front of in, I can type in not in, and here I'll press F5, and this shows me all of those customers who are not in those states.
28:19
I've now gone back to the base query where we get all the records back in this table. Now let's say I want to filter down to the customer Tres Delicious who's located in the United States. Now once again, I can enter where, and here I'll type in customer name equals Tres Delicious. When I run this query, I get two customers back, but once again, I only want to see the one in the United States. So, unlike the previous query where we used or,
28:50
here we can type in and, then I’ll type in country equals United States. Now when I run the query, I just get this result back. So, we can also use and to filter our data. We can also combine ands together with ors. Right now, we're looking for the customer name, and this country. Let me enter down to a new line. Here I could also type in or and maybe I say country equals let's say France. When I press F5, here we're looking for this customer and
29:24
either the United States or France, and I get these two results back. Especially when you start adding a lot of ands and ors, it might be confusing as to how the query is going to run. Which part does it execute first? Which part does it execute second? To make it extremely clear, you can use parentheses. So, I want the customer to be Tres Delicious, and I want the country to be either the United States or France, and it works as is right now,
29:51
but to make it clear, I can insert a parentheses here and here. So, this way I know that it evaluates this customer name and this portion here. When I press F5, it gives me this same result, but it's a little bit easier and cleaner to read. So far in our where clause, we've been looking for a specific customer name, but what if I just want to see all customers who say begin with the letter A? Instead of entering the equal sign here, I can type in like,
30:21
and here I'll remove the name, and let me type in an A and next I'll type in the percent sign. So, I want it to begin with an A, but there can be any number of characters after the A. When I press F5 to run, here I see that there are two customers that begin with an A. Now instead of saying like, I could also say not like, and when I run this, I see all the customers who do not begin with the letter A. Next, let's shift our focus over to the orders table,
30:51
and I want to show you how you can also filter based on numbers and numerical values. Here I'll right click on orders and let's select the top 1,000 rows. Here we see the query that returns the top 1,000 rows. Now this table only has 50 rows, so we see all the different data contained within this table. Now let's say that I want to see all of the orders that exceed $1,000. Here, once again, I can type in where and I can then type in order total is greater than 1,000. Now when
31:26
I run the query, I only see all the different orders where the order total exceeds 1,000. Along with that, I could also say greater than or equal to. Here I could also enter in less than or equal to. When I run this again here, I see all of the orders that are less than 1000, and I could also use something called between. Here I'll type in between and let's say between 1,000 and 2,000. Here once again, I’ll press F5
31:52
to run the query and now we only see orders with an order total between 1,000 and 2,000. Now one thing to note, this will be inclusive of both 1,000 and 2,000. So far, we've been looking at how you can retrieve data from just one table. We pulled some data from the customers table, and we pulled some data from the orders table. But what if you want to pull data from let's say two tables or even three different tables.
32:20
Here I can see all of our order data, so we have quite a few orders here. But here it simply refers to the customer ID, and that doesn't really do me much good. If let's say there's an order and I want to follow up with the customer on that order, I need the customer name, and I also need their phone number. So, it'd be really nice if I could also see those two columns alongside all of the order information. To join these two tables together, let's start from scratch.
32:48
I'm going to remove this query altogether and once again I want to pull some information from the orders table. I want the order ID and the order date and the order total. So here I'll type in select and then I'll type in the order ID, the order date, and the order total, and once again, this is coming from the orders table, so I'll type in from dbo.orders, and when I run this, here's all of the data that I want back. To make things a little bit cleaner,
33:21
I'm going to insert enters here just so it's a little bit easier to read.
33:25
Here now this is exactly the same and when I run it, once again, I get the same result. Now I mentioned earlier that I also want to get the customer name back and the phone number. So here in the select statement, let me also type in the customer name, and right here I'm also going to type in phone number. Now when I execute this query, I get an error message back, and the reason why is in the orders table, there's no customer name and there's no phone number.
33:51
First I need to connect the orders table to the customers table, and we can do that by using a join. I want to join my orders table with my customers table. So here I'll type in join and then I'll type in dbo.customers. So right now, I've joined these tables, but I need to specify how I'm going to join these, and here when I look at the customer table, there's a customer ID, and here when we look at the order table, there's also a customer ID here, so I can join on that column.
34:25
To join on that column, I simply type in on and here I'm going to type in on dbo.orders.customerID.
34:34
So here once again I'm referring to this value right here, and I want to join it on the customer ID in dbo.customers. So here I'll type in dbo.customers.customerID. Now when I run this query, here I get back all of the different orders along with the customer name and the phone number. That's exactly what I want. To clean things up, I can also use an alias here. So here from dbo.orders, instead of referring to dbo.orders, I can simply
35:06
refer to O and the same down here where I say dbo.customers, here maybe I use an alias of C. The benefit of that is here I can remove the reference to dbo.orders and instead I can insert an O, and right down here where I say dbo.customers, here I can insert a C. When I press F5 to run, I get the exact same result, but my code is a little bit easier to read now. When we ran this query, we did something called an inner join, and in fact I can go back to this
35:38
join statement, and I can type in inner join, and here when I run it, we get the exact same result. In fact, when you just enter join, that by default runs an inner join. So, what is an inner join? Well an inner join gives me all the customers back who have an order, and it also gives me back all orders that have a customer associated with it. So let's say there were, let's say a customer that doesn't have any orders. That would not show up in this list.
36:09
Basically, there has to be overlap between orders and customers for the result to show up down below. Now let's say I have a customer who doesn't have any orders, but I still want to see the customer in the results down below. Let me bring this line up just to make it a little bit easier to visualize. Here I'll remove the portion that says inner join and here I'll type in right outer join, and here when I run this, let's go to the very bottom of the list,
36:38
and here now we see that there's one customer without any order. So, what does a right outer join do? Well, here it's saying the right item here, which is dbo.customers. I want to see all customers irrespective of whether they have an order. So here I get all customers back even if that customer has no order. So here for the order value down below, it says null. Now let's say I want to see all orders whether or not there's a customer.
37:06
Well orders is on the left side over here, so instead of saying right outer join. I want to do a left outer join instead and now when I run this query, it gives me the same result as the inner join, but the only reason why is every order happens to also have a customer. But if there were, say, an order without a customer, the left outer join would have given me orders and then for the customer name and phone number it would have given me null values.
37:34
In my query, let's say I also want to get back the customer ID. So right up here in the select portion of the query, I'll insert a comma, and here let's type in customer ID.
37:46
When I run the query though, I get an error message, and it tells me that it's an ambiguous column name, and the reason why is here in the customer table, I have a customer ID, and also down below in the orders table, there's also a customer ID, so it doesn't know which customer ID it should use. Well, that's simple to solve, I could simply tell it which table I want to use, and let's just pull it from the customers table. Here the alias for that is C so I can enter a C dot
38:18
and then customer ID, and now when I run it, once again, it works, and here I have the customer ID. Now that I have these results back, let's say I want to order these by the order total. Right now, they're all just kind of randomly mixed in. I have large values, smaller values, and large value again. At the very bottom of this query, I can add order by and here I want to order by the order total column. Here I can type in order total, and then run my query again,
38:45
and here you see that it's in ascending order. So here I start with 39 and then we finish off at 3,518. Instead, I could also look at it in descending order. Here I'll type in descending, execute, and here you see that we start large and then it goes down to the smallest value. SQL also gives you access to a number of different functions, so you could do things like get the current date, or you could sum up values, or you can
39:13
get a count. Those are just a few examples of the functions that you have access to. Here in this example, I want to see all of the orders from the past month. So here I'm selecting all orders from the orders table and I'm looking at where the order date is greater than or equal to a date that's one month in the past, but let's say in a week from now, I want to run this same query again where I look at all of the orders from the past.
39:39
Well, this date would no longer be just the past month, so I would have to update this date. Instead, I could replace this with a function. Here I'll remove this date and let's use the date add function. Here I need to specify what the interval is, and I want to look at the last month, so I'll type in month, and I want to look at the last month, so for the increment, here I'll type in a negative 1, and for the expression, that's the current time, and for this I can
40:11
use yet another function. I'll type in get date, and then I'll close the parentheses, and here I can run it, and I get the exact same results that I got before. Except now when I run this again next week, it'll also show me all the orders from the past month, but I no longer have to go in and update this date manually. I can also use aggregate functions, so instead of selecting all orders, what if I just want to see a count of all orders from the last month?
40:40
Here I could type in count and let's count all of the different rows. Now when I run this, I see that there were 16 orders in the last month. Instead of counting, I can also sum up all the different order totals from the last month. Here I'll type in order total and run again, and here I can see that in the last month, we had over $17,000 of revenue. Not too bad for a cookie company. Now let's say I want to see the order total by the customer ID.
41:09
Down at the end of this query, I can type in something called group by, and here I'll type in the customer ID. Now when I run this, I see the sum of the order total by the various customer IDs in the last month. We've written some fairly complex queries, but next I want to show you how you can write just about any query with ease, and we're going to use something called the query designer.
41:35
Here in a new query, simply press your right mouse button, and then select design query in editor. This opens up the query designer and first we can choose the tables that we would like to query against. Now I want to pull data from the customers table and the orders table. So here I'll press control and select both of these tables and then click on add.
41:58
Next, I’ll click on close. Within the query designer, here I can see all of my different tables, and all of the different columns within those tables, and here I could also see how these two tables are related. So, a really nice way to visualize, but now look at how easy it makes it to pull together a query. Let's say I want to get a customer name and all of their different orders. Here I simply click on customer name and here I can click on let's say the order ID,
42:26
maybe I want the date, and also the order total. Here I see all those different fields or columns populate in this grid down below, and here you see that it automatically writes the SQL statement for me, and it joins these two tables together. When I click on OK, here I can now run the query and here I get all the customer names with the order ID, the order date and the order total. That was really easy to pull together.
42:52
To go back to the query editor, here I can highlight my query and then I can right click and once again I'll click on design query in editor. Back within the query editor, here I could also add an alias, so we see that customer name doesn't have a space. Here I can type in customer name and that automatically adds an alias down below. I can also add filters. Let's say I just want to see all orders from the
43:17
last month. Over here in filter, I could say greater than or equal to 2/18/2022 and when I enter that in it automatically adds where and now it'll just provide me with those orders.
43:31
If I don't want to show the output from order date, here I can check this box, and it leaves the where condition, but it removes it from the select statement. Here I can also select the sort type, the sort order, and if I right click up here, I can even add the group by. So, let's say I want to pull a sum of the order total by customer name. I could do that as well. Here for instance, I'll change order total to sum, and let me remove the order ID.
43:58
So here I want to see all of the customer names and the sum of their order total in the past month. I'll click on OK, and when you look at this, this is fairly complex query, but here when I execute it, it just runs easily. All right, well, that's how you can get started using SQL, and hopefully you agree with me now that SQL is not really that hard to get started with, and it's really tremendous what types of insights you can extract from your data.
44:26
Let me know in the comments, what other topics related to SQL would you like to see on this channel? To watch more videos like this one, please consider subscribing. I'll see you in the next video.
Here to help you learn
Sign in to keep a private conversation with your course tutor.
Ask me anything about the material…
Sign in to chat