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 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 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, 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, 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 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. What, you thought we just wrote
all that down by hand on paper. 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. 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 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. 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. 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, 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 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 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. 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. 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. 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. 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, 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. 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 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. Once you download the file, let's
click on this option that says device. Then click on the ellipsis and here click on add. 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. 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. 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. 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, 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. 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 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 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. 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, 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. 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, 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, 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. 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. 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. 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, 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. 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, 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. 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 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. 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. 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. 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. 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, 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. 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. 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, 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. 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 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, 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 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. 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. 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. 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. 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, 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. 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. 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. 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, 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 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, 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, 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, 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 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 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. 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. 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, I'm going to insert enters here just
so it's a little bit easier to read. 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. 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. To join on that column, I simply type in on and here I'm going
to type in on dbo.orders.customerID. 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 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 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. 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, 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. 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. 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. 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 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, 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 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. 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 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? 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. 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. 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. 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, 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. 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 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. 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. 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. 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.
