# SQL SELECT Queries (Visually Explained) for Beginners | All Essential Clauses | #SQL Course 4

https://www.youtube.com/watch?v=TDKoTItCVMU

[00:00] All right, my friends.
[00:01] So now what we're going to do, we're going to go and learn how to query our data using SQL.
[00:05] And we're going to learn the basic sections and the clauses inside a select statements like the where, order by, group by, having and so on.
[00:14] And at the end, you will understand the coding order and the execution order of the query.
[00:18] So let's start.
[00:23] Okay.
[00:23] So now we can understand exactly what is an SQL query.
[00:27] Now normally your data is inside the table and your table is inside the database and now you might have a question from the business like what is the total sales what is the total number of customers so any question that you have in your mind and you want to go and ask your data you want to go and retrieve data from the database and in order to do that you have to talk to the database using its language the SQL so in order to do that you're going to go and write a query where you write inside the query something called select statement.
[00:57] And with that you are asking the database for data.
[00:58] So once you execute your query.
[01:01] The database going to go and fetch your data and then it prepares a result to be sent back to you.
[01:07] So with that you are asking the database a question by writing a query and the database going to process your query and answer your question by sending back data.
[01:16] And with that we are like reading our data from the database and the queries will not modify anything will not change the data inside your tables or even change the structure of the database.
[01:27] So you use select statement only in order to read something from the database.
[01:32] You just want to retrieve data from the database.
[01:34] So this is what we mean with a query.
[01:39] And now my friends each SQL query has usually different sections different components.
[01:45] We call them clauses and this is amazing because you're going to have enough tools to write a query that matches any question that you have about your data.
[01:53] So what we're going to do, we're going to cover all those clauses step by step in order to write any query that you need.
[01:58] So now we're going to start with two clauses that makes the.
[02:02] Simplest query in SQL.
[02:05] The select and from.
[02:10] So let's start with that.
[02:10] All right.
[02:11] All right.
[02:12] So now it's really important for me that you understand how SQL works with the code with the queries.
[02:15] So now what I'm going to do, I'm going to show you on the right side the syntax of the query in SQL and then on the left side I'm going to show you exactly step by step how SQL going to go and execute your query.
[02:27] So now we have the table customers inside our database and we will start with the easiest form where we're going to select everything.
[02:33] Select the star.
[02:35] So the select star is going to go and retrieve all the columns from your table.
[02:40] So everything and the from clause it's going to tell SQL where to find your data.
[02:44] So with the select we select the columns that we want and the from you specify the table where your data come from.
[02:50] So the syntax going to be very simple.
[02:52] In each query we start always with the select and now since we want all the columns we're going to write star and with that SQL going to understand I want to see everything.
[03:01] And then after that comes the keyword from.
[03:03] And now we want to tell SQL where the data come from.
[03:07] So we have to specify the table name and that's it.
[03:09] This is all what you need to do.
[03:10] So once you execute it, what's going to happen?
[03:12] SQL going to go and execute first the from clause.
[03:14] So it's going to go and retrieve all the data from the database to the results.
[03:19] And then in the next step, SQL going to go and check the select statement.
[03:22] So which columns we have to keep in the result since you are saying star then the SQL going to keep everything all the columns and with that you will see in the result everything all the columns and all the rows.
[03:33] So that's it.
[03:34] This is how it works.
[03:35] Now let's go back to scale in order to select few data from our database.
[03:39] Okay, so back to our studio.
[03:41] Let's go and start a new query and let's go and find our database just to expand it and our tables.
[03:46] Now it is very important to make sure that you are connected to the correct database.
[03:49] So go to the top left in the menu over here and make sure to select your database.
[03:55] So my database like this or we have a command for that called use and then just write the database name like this.
[04:00] So I'm telling SQL just use my database like this.
[04:02] And
[04:04] With that is going to switch to your database.
[04:06] Now if you are learning any new programming language, it is very important to understand about the comments.
[04:11] So comments are like notes that you add to your code in order to understand what is going on.
[04:17] And of course the engine, the database will not go and execute it.
[04:18] It's going to go and ignore everything inside it.
[04:22] And there is like two ways on how to do that.
[04:24] Either you make inline comments by typing two dashes like this and then you write anything.
[04:28] This is a comment.
[04:31] So now in SQL if you see it is green that means it is a comment.
[04:36] Now the other type you can have multiple line comments and in order to do that what you can do you can write slash and then star and then you can write anything this and then start a new line is a comment.
[04:47] So as you can see all the lines after the slashstar it is getting green that means it is a comment and now let's say that you are at the end so in order to close it you write again star and then slash and with that you are telling a scale I'm done with my comments.
[05:01] So those are the two types of writing comments in SQL.
[05:03] Now back to our query.
[05:04] Let's say.
[05:05] That we have the following task says retrieve all customer data.
[05:07] So I would like to see in the results all the data of my customers.
[05:11] Everything, all the rows and all the columns.
[05:13] So currently our data is stored inside a table called customer and I need to see all the data in the output.
[05:19] In order to do that we're going to write a query and all our queries start always with a select.
[05:23] And since I need everything, all the columns, we write star and then a new line.
[05:27] Let's go and specify for SQL from where it going to go and get the data.
[05:31] So it's going to be from and then we going to write the name of the table.
[05:35] It must be exactly like it is in the database.
[05:37] So it's called customers and you have to have it here as a customers.
[05:41] So that's it.
[05:43] Let's go and execute it.
[05:45] And now if you look to the results you can see we have four columns and five rows.
[05:47] So with that you are seeing everything inside the table customers.
[05:52] You can see we have five customers and you can see all the columns about the customers.
[05:56] So this is very simple.
[05:58] We have asked question for the database using SQL query and the database would answer our question by returning our data in the results.
[06:05] All.
[06:06] Right.
[06:08] So now let's move to another task.
[06:09] I'm going to go and create a new query and this time we're going to retrieve all the order data.
[06:13] So that means I would like to see all the data inside the orders.
[06:14] So let's go and write a very simple query.
[06:16] We start as usual with select and since we want everything.
[06:20] So it is select star from our table orders.
[06:22] So that's it.
[06:25] Let's go and execute.
[06:27] And with that you can see in the output we have again four columns but this time we have only four rows.
[06:30] So that means in this table we have four orders and we can see all the data inside this table.
[06:36] So with that we can understand we have five customers inside our database and these customers did generate four orders.
[06:43] So as you can see we are now talking to our database and this is the simplest form of query in SQL.
[06:53] All right.
[06:53] Right.
[06:55] So now let's move to the next step in our query where you say you know what I don't want to see all the columns from the database.
[06:58] I want to be more specific.
[07:00] So I would like to select exactly the columns that I need.
[07:05] So now we want to select few.
[07:07] Columns from the database where we select only the columns that we need instead of everything.
[07:11] Now about the syntax we're going to go and change a little thing.
[07:14] So instead of using star we're going to go and make a list of columns that we want to see in the output.
[07:20] So we're going to select column one, column two and we're going to separate them using a comma.
[07:25] So we are just writing a list of columns exactly after the select.
[07:28] And for the from it going to stay as it is.
[07:30] So from a table.
[07:32] Now if you execute this what going to happen as usual SQL going to start with the from.
[07:35] So it's going to go and get the data from the database.
[07:39] And then the next step is going to go and check the select.
[07:40] So what going to happen?
[07:42] And SQL going to go and keep only two columns like for example the name and the country and all the columns that are not mentioned in the select statements will be excluded.
[07:51] So SQL going to go and remove it from the results and keeps only the columns that we mentioned in our query.
[07:57] So this time instead of having four columns in the output we're going to have only two.
[08:00] So with that you are like filtering the columns and you are selecting exactly what you need.
[08:06] So now let's go back to SQL in order to.
[08:08] Practice this.
[08:09] All right.
[08:10] So now we have the following task and it says retrieve each customer's name, country and score.
[08:15] So that means I don't want to see everything from the table customers.
[08:19] I need only to see the three columns.
[08:21] So let's see how we can do that.
[08:23] As usual, we start with select and I'm going to go with the star in order to see the whole table first from the table customers.
[08:29] So it's exactly like before.
[08:31] Let's go and execute it.
[08:32] And now I can see everything inside the table customers.
[08:35] But the task says I need only three columns.
[08:37] So now what we're going to do instead of the star we're going to make a list of columns.
[08:40] So we start a new line and then we write the name of the first column.
[08:45] So the first name and a new line for the second column for the country and then again a comma and then we write a score.
[08:51] So with that we have the three columns.
[08:54] Now what I usually do I go and select them and give it then a push using a tab.
[08:58] This just looks nicer and easier to read.
[09:00] So with that we have now between the select and from list of columns.
[09:04] Now there is like mistake that happens a lot where we go and type a comma after the last column.
[09:08] So if you do that and
[09:10] Execute it, you will get an error because SQL going to expect from you a column after the comma.
[09:14] And since there is no column and immediately you have a from, you will get an error.
[09:19] So there is no need for a comma after the last column.
[09:21] Now let's remove it and execute.
[09:23] And now that you can see in the output we don't have four columns, we have only three.
[09:27] The first name, the country, and the score.
[09:29] And by the way, they are ordered exactly like you selected in your query.
[09:34] So first we have the first name and then the country and then the last one the score.
[09:38] So that means if I go and now change the order.
[09:40] So let's get the country at the end and execute.
[09:45] You will see the country at the end.
[09:47] But I'm going to go and put it back in between to match exactly like the task and remove the last comma.
[09:51] So execute again.
[09:54] And with that we have selected few columns from our table.
[09:55] So we are more specific to what we need.
[09:58] Okay.
[10:00] So that we have covered the two select and from next we're going to talk about the wear clause that you can use in order to filter your data.
[10:07] So let's.
[10:11] Go.
[10:11] So what is exactly where?
[10:15] We use where in order to filter our data based on a condition and any data that fulfill the condition going to stay in the output in the result and the data that don't meet the condition will be filtered out of the results.
[10:28] Condition could be anything like for example we say the score must be higher than 500 or you can say the country must be equal to Germany so any condition that you have in your question.
[10:36] Now let's see the syntax in SQL.
[10:38] As usual we start with a select we select the columns that we need then we write from where the data come from and then after the from we're going to write the where and exactly after that you specify your condition.
[10:51] So now let's see how SQL going to execute this.
[10:52] First SQL start as usual from the from so it's going to go and get your data from the database case and after that SQL going to go and execute the wear clause.
[11:01] So let's say that the condition should be higher than 500.
[11:05] And now what can happen?
[11:05] SQL going to check each row whether it meets this condition or not.
[11:10] So for example for Maria she.
[11:12] Doesn't fulfill the condition because her score the 350 is not higher than 500.
[11:17] So she doesn't fulfill the condition and SQL going to go and remove completely this row this record from the results.
[11:24] Now SQL going to go to the second record.
[11:26] So Joan is fulfilling the condition.
[11:28] So he going to stay in the result.
[11:30] The same thing for George.
[11:32] Now moving on to the fourth one, Martin.
[11:34] So this customer is not fulfilling the condition and SQL going to go and remove it from the results.
[11:39] The same things happen for the last customer.
[11:40] The score is zero and not fulfilling the condition.
[11:43] So that means if we apply this filter, SQL going to return only two customers out of five.
[11:48] So with that we are filtering the rows based on condition using the wear clause.
[11:53] Now as you can see in the result we are getting all the columns but if you specify in the query like for example only two columns like the name and the country then SQL going to start removing as well the columns of the results and this means in the output we will get only two columns and two rows.
[12:09] So with that you.
[12:12] Are filtering the columns and the rows of your results.
[12:14] So now let's go back to scale in order to practice this.
[12:17] All right.
[12:18] So let's have the following task and it says retrieve customers with a score not equal to zero.
[12:22] So now if you are looking to our task you see we have like here a condition.
[12:26] The condition says the score must not be equal to zero.
[12:30] So I don't want to see all the customers.
[12:32] I want to see only the customers that fulfill this condition.
[12:34] So it's like we have to filter the data.
[12:36] So let's go and solve the task.
[12:38] Let's start as usual.
[12:40] Select star.
[12:42] There's no specifications about the columns from our table customers.
[12:44] Okay.
[12:46] So I'm going to start with this.
[12:47] Let's go and execute it.
[12:48] Now if you look at the result you can see like almost all the customers are fulfilling their condition their scores are not equal to zero only one the last customer his score is zero.
[12:59] So this customer does not fulfill our condition.
[13:01] Now let's go and build filter for that.
[13:03] So we're going to say where and now there will be a section that is only focusing on how to build conditions and filtering in SQL.
[13:08] So don't worry a lot about the syntax of the conditions.
[13:12] We're going to cover that later of course but it is very simple.
[13:14] Now for the condition we need a column.
[13:17] So in which column is our condition based on?
[13:19] It's going to be on the score.
[13:21] So we're going to write here score.
[13:22] And since we are saying not equal, there is like an operator in SQL called not equal.
[13:27] And then we have to write a value after that.
[13:28] It's going to be a zero.
[13:30] So again the condition is like this.
[13:32] The score must not be equal to zero.
[13:34] It's very simple, right?
[13:36] And with that we have our condition and we are using the where in order to filter the data.
[13:40] So let's go and execute it.
[13:42] And now as you can see SQL did remove the last customer because he is not fulfilling this condition.
[13:47] And we have now only the rows that fulfill our condition.
[13:50] So as you can see it is very simple how to filter the data.
[13:53] All what you have to do is to write where clouds after the from and then write a condition after that.
[13:59] Now let's have another task like for example it says retrieve customers from Germany.
[14:03] So I don't want to see all customers from different countries.
[14:05] I just want to see the customers that come from Germany.
[14:09] So that means we have a condition here.
[14:11] Country of the customer must be equal to.
[14:13] Germany.
[14:13] So let's go and remove the current condition.
[14:15] It is not the one that we need and execute.
[14:17] If you are looking to the results, we have two customers that come from Germany.
[14:20] And we are interested only to show those two customers.
[14:23] So let's go and make a filter for that.
[14:25] We're going to write where close and after that we need a column.
[14:29] The column going to be the country.
[14:31] So we're going to write here country.
[14:32] And this time the country must be equal to Germany.
[14:34] So we're going to write an equal operator.
[14:37] So we're going to write Germany like this exactly like the value inside our data.
[14:40] But now as you can see we are getting like an error here.
[14:43] And that's because in SQL if you want to write a value that contains characters then you have to put it between two single quotes.
[14:51] So at the start you put a single quote and as well at the end.
[14:55] And now as you can see the red line is away and the value now is red.
[14:59] And that's because it is a string value.
[15:01] It is a value that contains characters.
[15:04] And with that you will not get an error.
[15:06] So if your columns contains only numbers you can write it without single quotes.
[15:08] But if your values contains characters then
[15:14] You have to write it between two single quotes.
[15:15] Okay.
[15:17] So now back to our condition.
[15:19] The country must be equal to Germany.
[15:21] Let's go and execute it.
[15:21] And it is working.
[15:23] So as you can see now we are seeing in the output only the customers does fulfill my condition where the country is equal to Germany.
[15:29] So this is exactly how we work with the wear clause in order to filter our data.
[15:34] So my friends, this is how you filter your rows.
[15:35] And now let's say that I would like to filter the rows together with the columns.
[15:38] So I just want to keep the first name and the country and not interested to see the scores and the ids.
[15:46] So in order to do that, we're going to go to the select and list the columns that we want to see.
[15:50] So the first name and after that a comma, then the country and that's it.
[15:55] So let's go and give it a push and execute it.
[15:58] So we have two rows and two columns.
[16:00] So guys, as you can see, SQL is very simple.
[16:02] All right.
[16:02] Right.
[16:04] So with that you have learned how to filter your data using the wear clause.
[16:07] Next we're going to talk about how to sort your data using the order by.
[16:10] So let's
[16:15] Go.
[16:15] Okay.
[16:16] So what is exactly order by?
[16:18] You can use this type of clause in order to sort your data.
[16:20] And of course in order to sort your data you have to decide on two mechanism.
[16:23] Either you want to sort your data ascending from the lowest value to the highest value or exactly the opposite way using descending from the highest value to the lowest and the syntax kind of looks like this.
[16:36] So as usual we start with a select and then from and after the from you can specify order by and with that you are telling SQL we have to sort the data and you have to specify two things.
[16:45] First you have to specify for SQL the column that should be used in order to sort the results.
[16:51] So for example you can say score and after the column name you have to specify the mechanism.
[16:55] So for example you say ascending from the lowest to the highest and in SQL if you don't specify the mechanism the default going to be ascending.
[17:04] So you will not get an error if you don't specify anything after the column name.
[17:08] But my advice here is always to specify something after the column easier because it's just.
[17:15] Straightforward and easier to understand.
[17:17] And if someone reads it, can understand immediately.
[17:19] It's going to be ascending because maybe not everyone knows what is the default in SQL.
[17:22] So always specify a value even if it's like easier to skip it.
[17:27] And if you want to store the data from the highest to the lowest, then you can specify descending.
[17:31] So as usual, SQL is going to go and start from the from; it's going to go and grab your data from database.
[17:36] Then the second step is going to go and sort the result.
[17:39] So the order by is going to be executed and going to see, "Okay, I'm going to sort it by the score and using descending mechanism."
[17:45] And it's going to go and start like moving around your rows where the first row is going to be the customer with the highest score.
[17:52] And in this example, John has the highest score, the 900.
[17:56] So John is going to appear as a first row at the results and that's because his score.
[18:01] And after that, the second highest is going to be George with 750.
[18:05] And SQL is going to go and keep sorting the data.
[18:08] And then we have 500, then 350.
[18:10] And the last row is going to be the customer with the lowest score.
[18:15] Zero.
[18:15] So this is how SQL executes your order by.
[18:17] Now let's go back to SQL in order to practice.
[18:19] All right.
[18:19] So now we have the forming task and it says retrieve all customers and sort the result by the highest score first.
[18:28] So now by looking at the task we need all the customers.
[18:29] So there is like no conditions or anything to filter but we have to sort the results.
[18:34] So let's go and do that.
[18:36] We're going to start as usual by selecting all the columns from the table customers.
[18:40] So now if you go and execute it you will get all your customers and you are now seeing the data exactly like stored in the database.
[18:47] And you can see the result is not sorted by the scores.
[18:49] So we have here a low score then high score then low and so on.
[18:54] Now the task says we have to sort the results.
[18:56] So we have to go and use the order by and now you have to understand from which column and we can get that from the task.
[19:02] So it says it should be sorted by the score.
[19:05] So we're going to go and define the score here.
[19:08] And the final thing that you have to define is the mechanism descending or ascending.
[19:12] And you can get it as well from the task.
[19:14] So we have to sort the data by the highest score first.
[19:15] So the
[19:18] Highest first and then the lowest.
[19:19] So that means we're going to go and use the descending.
[19:22] So that's all.
[19:23] Let's go and execute it.
[19:24] Now, as you can see in the results, the first customer has the highest score.
[19:28] Then we have the second one with the second highest until the last one with the lowest score.
[19:32] That's it.
[19:33] This is how you sort your data.
[19:35] And with that, we have solved the task.
[19:36] Now let's do exactly the opposite.
[19:38] So we want to sort the results by the lowest score first.
[19:41] So that means we want to see first the customers with the lowest score.
[19:45] Like here in this example, we should see the ID number five as the first because he has the lowest score, the zero.
[19:51] Now, in order to do that, all what you have to do is to switch the mechanism.
[19:54] Instead of descending, we can use ascending.
[19:57] Let's go and execute it.
[19:58] And that's it.
[19:59] As you can see, now we have the lowest score, then the second lowest score until the last row.
[20:04] It's going to be the customer with the highest score.
[20:06] So, the lowest score comes first.
[20:08] So, it is very simple.
[20:10] This is how you sort your data using SQL.
[20:16] And now I'm going to show you one more thing that you can do with the order by.
[20:19] You can sort your data using multiple
[20:21] columns and we call it nested sorting.
[20:23] So now let's take this very simple
[20:25] example where you want to sort your data
[20:27] using country. So we are saying order by
[20:30] the column country and the mechanism
[20:32] going to be ascending. So from the
[20:34] lowest to the highest. Now if you do
[20:35] that it's still going to go and sort the
[20:37] data this time based on the country. So
[20:39] we're going to have like the first two
[20:40] customers from Germany is sorting it
[20:42] alphabetically. Then we have the UK and
[20:44] the last two going to be from USA. Now
[20:46] if you are checking the final results
[20:48] you might say you know what there is
[20:50] like something wrong. The data is not
[20:52] completely sorted correctly. So if you
[20:54] are looking to the first two customers
[20:56] that's come from country Germany. You
[20:58] can see the scores are sorted in
[21:00] ascending way from the lowest to the
[21:02] highest. So first we have 350 then 500.
[21:05] Then UK it's fine because we have only
[21:07] one customer. Now if you look to the
[21:09] customers from USA you see that it is
[21:11] like sorted the way around. It is sorted
[21:13] descending from the highest to the
[21:15] lowest. So first we have the score 900
[21:17] then zero. So there is like no clean way
[21:20] on how the data is sorted and the result
[21:22] is not really clean and this issue
[21:24] happens usually if you are sorting your
[21:26] data based in a column that has
[21:28] repetition like here the country we have
[21:29] twice Germany and twice USA. So now in
[21:32] order to refine the sorting and make it
[21:34] more correct, we can include in the
[21:37] sorting another column in this scenario
[21:39] for example the score. So we can make a
[21:41] list of columns in the order by and we
[21:43] can separate them using the comma. And
[21:45] of course you can have different
[21:46] mechanism for each column like for the
[21:48] country we are saying it is ascending
[21:50] but for the score we say you know what
[21:52] let's make it descending. It will not be
[21:54] only one for all columns. So now what
[21:56] can happen is we're going to start
[21:57] sorting the data for each section. So
[21:59] for the two customers from Germany the
[22:02] sorting going to be from the highest to
[22:03] the lowest. So Isco going to go and
[22:05] switch the two customers. So Martin
[22:07] going to be first because he has higher
[22:09] score than Maria. And with that we are
[22:11] refining the scores based on the same
[22:13] value of course the country. Now for the
[22:15] UK nothing going to happen because we
[22:16] have only one value. And for the USA as
[22:19] well nothing going to happen because it
[22:21] is already sorted in the correct way
[22:23] from the highest to the lowest. So as
[22:24] you can see if you are including a
[22:26] second column you are refining your
[22:28] sorting and as well my friends the order
[22:31] is very important. So this is how you
[22:33] can do nested sorting in SQL. Let's go
[22:36] back to our SQL and start practicing.
[22:38] All right. So now we have the following
[22:39] task and it says retrieve all customers
[22:42] and sort the results by the country and
[22:45] then by the highest score. So again we
[22:47] need all customers. So select everything
[22:50] from customers table. And now the task
[22:52] says we have to sort the result by the
[22:54] country. So we're going to start with
[22:56] the order by and since it says by the
[22:58] country, we're going to go with the
[22:59] country and we're going to sort it
[23:01] alphabetically. So it's going to be
[23:02] ascending. So let's go execute it. Now
[23:04] you can see the data is sorted
[23:06] completely differently by the country.
[23:08] So we have first Germany, UK, and then
[23:10] USA. But that's not all and says then by
[23:13] the highest score. So we have to go and
[23:16] include another column in the sorting.
[23:18] And we can go and add that by adding a
[23:20] comma and then mention another column
[23:21] the score. And now we have to specify
[23:23] the mechanism. It says by the highest
[23:25] score. So the highest must come first.
[23:28] And with that we are using descending.
[23:30] Now what is the current situation in
[23:31] that? If you look to the results for
[23:33] example for those two customers we have
[23:35] 350 and then 500. So that means the
[23:38] scores are sorted ascending right. The
[23:40] same thing for USA. So from the lowest
[23:43] to the highest. Now if you go and do it
[23:45] like this what going to happen?
[23:46] basically going to go and switch it. So
[23:48] you can see over here now for Germany
[23:50] first comes the highest the 500 and then
[23:52] the 350 and for USA as well they
[23:55] switched. So we have the highest and
[23:57] then the lowest and with that we have
[23:58] solved the task. Now again the order of
[24:00] those columns are very important. So
[24:02] since the scores comes after the country
[24:04] we will not get the highest scores first
[24:07] at the results. So we will not get the
[24:09] 900 as a first row and that's because
[24:12] the scores must be sorted after the
[24:14] country. So the country has more
[24:16] priority. Now if you go and flip that.
[24:18] So let's go over here and says sort
[24:20] first the score and then the country. So
[24:22] let's go and execute it. School has
[24:24] first to sort the scores. So with that
[24:26] you will get the 900 first, right? And
[24:29] then the countries. And since there is
[24:31] like no duplicates in the scores, this
[24:33] makes no sense at all. So you can go and
[24:35] skip it. So nested sorting only makes
[24:37] sense if you have repetition in your
[24:40] results. And you can use the help of a
[24:43] second column in order to make the
[24:45] sorting perfect. So that's it. And with
[24:47] that of course we have solved the task.
[24:49] All right. So with that you have learned
[24:50] how to sort your data using order by.
[24:53] Now in the next step we're going to talk
[24:54] about how to aggregate and group up your
[24:57] data using group by. And we're going to
[24:59] put it between the where and the order
[25:01] by because in the order of the query the
[25:04] group by comes between the where and the
[25:06] order by. So let's go.
[25:11] Okay. So what is exactly group by? It's
[25:13] going to go and combine the rows with
[25:15] the same value. So it's going to go and
[25:17] combine and smash press your rows to
[25:20] make it aggregated and more combined. So
[25:22] all what group by does it aggregates a
[25:24] column by another column like for
[25:26] example if you want to find the total
[25:29] score by country. So you aggregate all
[25:31] the scores value for one country. If you
[25:34] have this kind of tasks then you can use
[25:36] the group I. Let's see the syntax of
[25:38] that. We will start as usual with the
[25:40] select. And now what we want to see in
[25:42] the result is two columns. So we have to
[25:44] specify like a category like the
[25:46] country. This is the value that you want
[25:48] to group the data by. And another one
[25:50] where you are doing the aggregations. So
[25:52] for example you are saying I would like
[25:53] to see the total score. So we use the
[25:55] function sum in order to summarize the
[25:57] values of the score. After that as usual
[25:59] we use the from in order to select the
[26:01] data from specific table. And now comes
[26:04] the magic. We use after the from group
[26:06] by. And now understands okay I have now
[26:08] to combine the data I have to group up
[26:11] the data by something and this time we
[26:13] are saying you have to group up the data
[26:14] by the country. So that means each value
[26:17] of the country must be presented in the
[26:19] output only once and for each country we
[26:22] want to see the aggregation and that is
[26:24] the total score. So let's see how going
[26:26] to execute it. So it's going to first
[26:28] start with the from it's going to go and
[26:29] get the data from the database and then
[26:31] scale going to execute the group by and
[26:33] now scale understand okay I have to
[26:34] group up now the data by the country and
[26:37] it understands it has to aggregate the
[26:39] scores for that. So it's still going to
[26:41] go and identify the rows that are
[26:43] sharing the same value like for example
[26:45] here we have two rows for Germany and
[26:47] it's going to bring it to the results.
[26:48] So now we have two rows for the same
[26:50] country. But since we are saying group
[26:52] by country, SQL going to try and combine
[26:55] them, smash them together in only one
[26:57] row. So each value of the country must
[26:59] exist at maximum once. We cannot leave
[27:02] it like this. So now what we going to do
[27:04] with the scores? We have two scores. Now
[27:05] SQL going to check the aggregate
[27:07] function. It is the summarization. So
[27:09] and it's going to go and add those
[27:10] values. 350 + 500. And with that we're
[27:13] going to get the total score of 850. And
[27:16] with that as you can see scale is
[27:18] combining those two rows into one. So in
[27:21] the output Germany will exist only one
[27:23] and about the scores we will get the
[27:25] total score. And the same thing going to
[27:26] happen for the next value in the country
[27:28] we have the USA we have it twice. So
[27:31] we're going to get two rows and scale
[27:33] going to combine those two rows in one
[27:35] because USA must exist only once and
[27:37] with the scores we will have the total
[27:39] scores. So 900s + 0 we will get 900. And
[27:42] with that it's still converted those two
[27:45] rows into one. And for the last value in
[27:47] the countries we have the UK. It's going
[27:49] to stay as it is. There is no need to
[27:51] smash and combine anything because it's
[27:53] already one value. So my friends if you
[27:55] are looking to the output you can see we
[27:57] grouped the original data by the country
[28:00] and that means we're going to get one
[28:02] row for each value inside the country
[28:04] column. So my friends the original data
[28:06] you have five rows in the output if
[28:08] you're using group by like this you will
[28:10] get only three rows. So this is exactly
[28:13] how the group by works. Let's go back to
[28:15] scale and practice. Okay. So we have the
[28:17] following task and it says find the
[28:19] total score for each country. So from
[28:21] reading this you can understand we have
[28:23] to do aggregations and we have to
[28:25] combine the data by a column. So now
[28:27] usually I start like this. I start
[28:29] selecting the columns that I need in
[28:31] order to solve this task. So what do we
[28:33] need? We need the country and score from
[28:36] our table customers. So let's start like
[28:39] this. Now you can see we have the
[28:40] countries and the scores. And the task
[28:42] says we have to group up the data by the
[28:44] country. So that means this is the
[28:46] column where we're going to do the group
[28:47] by and the total scores will be
[28:49] aggregated. So what we have to do? We're
[28:51] going to use the group by since it says
[28:53] for each country. We're going to use it
[28:55] over here. Group by country. And now we
[28:57] have to go and aggregate the scores. We
[28:59] cannot leave it like this. So we're
[29:01] going to say the sum of the score. So
[29:03] let's go and execute it. And with that,
[29:05] as you can see, we are getting the total
[29:08] scores for each country. So now instead
[29:10] of having five customers, we have only
[29:13] three rows now. And that's because the
[29:15] countries has three rows. And now if you
[29:17] check the result, you can see something
[29:18] weird. It says no column name. And
[29:21] that's because we have changed the
[29:22] scores. It's not anymore the original
[29:25] score. It is it is the total scores. We
[29:28] have summarized those values. So SQL
[29:30] don't know how we going to call it. So
[29:32] those values doesn't come directly from
[29:35] the database. It is manipulation that
[29:37] you have done here. Now in order to give
[29:39] a nice name for that, we can go and add
[29:41] aliases. An alias it is only like a name
[29:43] that lives inside your query. So we can
[29:46] do it like this as and you can specify
[29:48] any name you want like for example total
[29:50] score. And now scale can understand okay
[29:52] this is the name for this column and if
[29:55] you go and execute it you will see the
[29:57] new name in the results. But you have to
[29:59] understand this name exists only in this
[30:01] query. You are not renaming anything
[30:03] inside your database and you cannot use
[30:05] it in any other queries. It is just
[30:07] something that is known inside this
[30:09] query and only for your results. And of
[30:12] course you can rename anything any
[30:13] column like for example here you can say
[30:16] this is the customer country and if you
[30:19] execute it you are just renaming the
[30:20] column in the output. So this is really
[30:22] nice in SQL. Okay. So now there is like
[30:25] one more thing about the group by the
[30:27] non-aggregated columns that you are
[30:29] adding in the select must be as well
[30:31] mentioned in the group by. So now for
[30:33] example let's say that okay I'm seeing
[30:35] now the countries the total scores I
[30:37] would like to see as well the first
[30:38] name. So you go over here and say you
[30:40] know what let's get the first name. So
[30:43] country first name the total scores and
[30:45] execute. You will get an error because
[30:47] it's going to tell you I need only the
[30:49] columns that you want to group the data
[30:51] by or should be aggregated. So now the
[30:53] first name it is not aggregated and as
[30:56] well not used for the group I. So it is
[30:58] just here to confuse SQL and it will not
[31:01] work. So if you bring a column either it
[31:03] should be in the aggregation or it
[31:05] should be part of the group I. So in
[31:07] order to fix this and you really want to
[31:09] see the first name you can go over here
[31:11] and say you know what let's add it to
[31:13] the group I and execute. This time it
[31:16] going to work because all the columns
[31:17] that are mentioned here is as well part
[31:20] of the group I. So now as you can see we
[31:22] have the countries the first name and
[31:24] the total scores and you can see again
[31:26] we have five rows we don't have three
[31:28] rows and that's because now you are
[31:29] combining the data by the country and as
[31:32] well the first name and now you can see
[31:34] in the output we are getting five rows
[31:36] we are not getting anymore the three
[31:38] rows there are three countries and
[31:40] that's because SQL now grouping the data
[31:42] by two columns the combination of the
[31:44] country and the first name and those two
[31:46] columns gives five combinations and that
[31:48] means you will get five rows so that
[31:50] means you have to be really careful what
[31:52] you are defining in the group I and the
[31:54] number of the unique values that those
[31:57] columns are generating going to define
[31:59] the output the results. So if you go and
[32:02] remove the first name and from here as
[32:04] well you are grouping by only one column
[32:06] and this column has only three values
[32:08] and that's why you are getting three
[32:10] rows and with that of course we have
[32:11] solved the task and now let's extend the
[32:13] task and say find the total score and
[32:16] total number of customers for each
[32:18] country. So that means we need two
[32:19] aggregations. We have the total score
[32:22] and as well we need the total number of
[32:24] customers. So from reading this you can
[32:26] understand we still want to group up the
[32:28] data by the country but this time we
[32:30] need two type of aggregations. We need
[32:32] the total number of customers and the
[32:34] total scores. So we have almost
[32:36] everything but what is missing is the
[32:38] second aggregation. Now what you can do
[32:40] you can go over here and add another
[32:42] aggregate function called the count. And
[32:44] what we want to count is the number of
[32:46] customers. So we can go and add the ID
[32:48] over here and call it total customers.
[32:51] So now of course SQL G. So now if you go
[32:53] and execute it, you will get as well the
[32:56] total customers by the country. And now
[32:58] as you can see SQL has no problem with
[33:00] the ID and that's because you are
[33:02] aggregating the ID. So SQL know what to
[33:04] do with it and how to combine it. So
[33:06] that means you don't have to mention the
[33:08] ID in the country because you are
[33:10] aggregating it. So that's all with that
[33:12] we have solved as well the task. All
[33:14] right. Right. So with this you have
[33:15] learned how to group up your data using
[33:17] the group I. Next we're going to talk
[33:18] about another technique on how to filter
[33:20] your data but this time using the having
[33:23] clause. So let's
[33:27] go. All right. So what is exactly
[33:30] having? You can use it in order to
[33:32] filter your data but after the
[33:34] aggregation. So that means we can use
[33:37] the having only after using the group
[33:40] by. So let's see the syntax of that. So
[33:42] again like the previous example we are
[33:44] finding the total score by country. So
[33:46] we have our select from group by and now
[33:48] you say you know what I would like to
[33:50] filter the end results and in order to
[33:52] do that we use the having after the
[33:54] group I and now like the wear clause you
[33:57] have to specify a condition. So we have
[33:59] the following condition where we want to
[34:00] see in the results only the countries if
[34:03] their total score is higher than 800. So
[34:07] this going to be our condition. So now
[34:08] you might noticing something with the
[34:10] group I we are using the country the
[34:12] column where we are grouping the data by
[34:14] its value but with the having we are
[34:16] using the aggregated column the sum of
[34:19] the score. So this is how the syntax
[34:21] works and now let's see how is going to
[34:23] execute it. So as usual SQL start with
[34:25] the from we are getting our data and
[34:27] then the second step going to go and
[34:30] aggregate the data by the country. So
[34:32] it's like before SQL going to group the
[34:34] rows with the same value of the country.
[34:36] So we're going to have one row for each
[34:38] country. And this is what going to
[34:40] happen if you use group I. And with that
[34:42] we have now aggregated values right and
[34:44] after the group IQL going to go and
[34:46] execute the having. So having it is like
[34:49] a filter. Now we have a nice condition.
[34:51] The total sale must be higher than 800.
[34:53] And is going to go and check the new
[34:55] results after the aggregation. So in
[34:57] Germany we have the total sales of 850.
[35:00] So it meets the condition and it going
[35:02] to stay in the results. The same thing
[35:03] for USA it is higher as well than 900.
[35:06] But for UK it is not meeting the
[35:08] condition 750. It is not higher than 800
[35:11] and SQL going to go and filter out this
[35:14] row. So that means after applying the
[35:16] having we will get only two countries
[35:18] because they have values that is
[35:20] fulfilling the condition. And that's it.
[35:22] This is what going to happen if you are
[35:23] using having. It is simply filtering the
[35:25] data. But now you might be confused. You
[35:27] say you know what we have used the wear
[35:29] clause to filter the data. So why we
[35:31] have in SQL another clause to filter my
[35:33] data. Can't we just use the where? Well,
[35:36] in SQL there are like different ways on
[35:38] how to filter your data based on the
[35:40] scenario. So now let's go and add both
[35:41] of the filters in my query. We are
[35:43] already using the having after the group
[35:45] by and now let's go and add the wear.
[35:48] Usually the wear comes between the from
[35:50] and the group by. So directly after the
[35:52] from and here we are saying the score
[35:54] must be higher than 400. So now we are
[35:56] filtering based on the scores twice,
[35:58] right? Once we are saying the score
[35:59] higher than 400 and by having we are
[36:02] saying the sum of score must be higher
[36:04] than 800. So what is the big difference?
[36:07] It is when the filter is happening. If
[36:09] you want to filter the data before the
[36:11] aggregation, you want to filter the
[36:13] original data, then you can go and use
[36:15] the wear clause. But if you want to
[36:17] filter the data after the aggregations,
[36:19] after the group by then you can go and
[36:22] use having. So it's really all about
[36:24] when the filter is happening. So let's
[36:26] see how is still going to execute this.
[36:27] So as usual, first the from going to be
[36:29] executed to get the data. Then after
[36:31] that the second step, the wear going to
[36:33] be executed. This is our first filter.
[36:36] So SQL going to filter the data using
[36:38] where before doing any aggregations and
[36:40] based on our condition the first
[36:42] customer will be filtered out because
[36:44] score is less than 400. And the same
[36:47] thing for the last customer. Now after
[36:48] the applying the wear clouds we will get
[36:51] only three rows only three customers.
[36:53] And now next SQL going to go and execute
[36:55] the group by. So SQL going to go and
[36:57] group the data by the country. So now we
[36:59] have fewer data to be combined. So the
[37:01] values will not be summarized because we
[37:03] have only one row for each country. Now
[37:05] after the data is aggregated by the
[37:07] group by then SQL going to activate the
[37:10] second filter having. So the next step
[37:12] is still going to execute the having and
[37:14] here going to filter the new results
[37:16] based on the total scores and still
[37:18] going to check one by one. So USA is
[37:20] meeting the condition UK going to be
[37:22] filtered out because it is not higher
[37:23] than 800 and this time Germany as well
[37:26] will be filtered out because this time
[37:28] it is not fulfilling the condition. In
[37:30] the previous example without the wear,
[37:32] we had more scores for Germany. That's
[37:34] why it passed the test. But this time
[37:36] since we filtered a lot of customers
[37:38] using the wear, Germany will not have
[37:40] enough scores pass the second filter. So
[37:43] with that in the output we will get only
[37:45] one row and that's because we are
[37:46] filtering a lot of data. So it is very
[37:48] simple where going to be executed before
[37:50] the group by before the aggregations
[37:52] having going to be executed after the
[37:55] group by after the aggregations. So now
[37:57] let's go back to scale in order to
[37:58] practice. Okay. So now we have very
[38:00] interesting task. Find the average score
[38:02] for each country considering only
[38:04] customers with a score not equal to
[38:06] zero. So it sounds like condition and
[38:09] return only those countries with an
[38:11] average score greater than 430. So this
[38:14] is again another condition. So I know
[38:16] there is a lot of things that's going
[38:17] on. Let's do it step by step. Usually I
[38:20] start by doing a very simple select
[38:22] statement with the columns and data that
[38:24] I need. So let's start with a simple
[38:26] select. So what do we need over here? We
[38:28] need a score. We need a country again.
[38:30] We need a score country. So all what we
[38:32] need is two columns. Now I'm going to go
[38:34] and select the ID just to see the
[38:36] customer ID. Then let's go and get the
[38:38] country score from our table customers.
[38:41] So let's go and query that. So now as
[38:42] you can see I start with the basics
[38:44] query the data and then build up on top
[38:46] of it the second step. Now what do we
[38:48] have in the task? We have to find the
[38:49] average score for each country. That
[38:52] means we have to do some aggregations.
[38:53] And here we have two conditions. The
[38:55] first condition says we need only the
[38:57] customers with a score not equal to
[38:59] zero. And the second one we need only
[39:01] the countries with an average score
[39:03] greater than 430. Now you have to decide
[39:05] for each condition whether you're going
[39:07] to use the where or having. Now for the
[39:10] first one we want to filter based on the
[39:13] scores. So that means we want to filter
[39:15] before the aggregations. It's not saying
[39:17] the average score, it's saying the score
[39:19] itself. So that means we can use for
[39:21] this aware condition. Now about the
[39:24] second one says countries with an
[39:26] average score greater than 430. That
[39:28] means we want to filter the data after
[39:30] aggregating the score. So that means for
[39:32] this condition we have to use the
[39:34] having. Now what I would like to do is
[39:36] to implement the first condition. It's
[39:39] very simple. We're going to say where
[39:40] after the from the score is not equal to
[39:44] zero. So let's go and execute it. And
[39:46] with that we don't have any customers
[39:48] where the scores is not equal to zero.
[39:51] So that we have solved this part. But
[39:53] now for the second condition, first we
[39:55] have to do the aggregations. So we're
[39:56] going to start with the average score.
[39:58] We're going to go over here and say
[40:00] average and we're going to call it
[40:01] average score. Now we don't want to see
[40:04] only the average score. We want to see
[40:06] the average score for each country. So
[40:08] that means we have to aggregate by the
[40:10] country and for that we use the group
[40:12] by. Group by comes always after the wear
[40:15] clause. So group by and which column?
[40:17] It's going to be the country. So
[40:19] country. Now there is like an issue
[40:20] here. You cannot execute it like this.
[40:22] We have to go and get rid of the ID. We
[40:24] don't need it at all. So let's go and
[40:27] execute it. So with that we have the
[40:29] average score for each country and we
[40:31] have solved the first part. So that
[40:33] means the first and the second part they
[40:35] are completed. Now we're going to talk
[40:37] about the last part. The average score
[40:39] must be higher than 430. And for that
[40:41] we're going to use the having and having
[40:43] comes after the group by. Now we need to
[40:46] specify the condition. it must be the
[40:48] aggregated column. So we're going to
[40:50] take the average score from here and put
[40:52] it after the having and it should be
[40:54] greater than 430. So that's it with us.
[40:57] We have the last part as well. Let's go
[40:59] and execute it now. And with that my
[41:01] friends, we have filtered the data after
[41:04] the aggregation. So this is how I decide
[41:06] between the where and having. It is very
[41:09] simple. All right. So with that you have
[41:10] learned how to filter the aggregated
[41:12] data using the having. And now next
[41:14] we're going to go back to the top where
[41:16] we can use there the keyword distinct
[41:18] exactly after the select. So let's go
[41:20] now and learn about the
[41:25] distinct. Okay. So what is exactly
[41:27] distinct? If you use it in SQL, it's
[41:29] going to go and remove duplicates in
[41:31] your data. Duplicates are like repeated
[41:33] values in your data and it's going to
[41:35] make sure that each value appears only
[41:38] once in the results. So it sounds very
[41:40] simple and as well the syntax is easy.
[41:42] So as usual we start always with a
[41:44] select but directly after the select we
[41:46] use the keyword distinct so there is
[41:49] nothing between them and then the normal
[41:50] stuff we specify the columns and then
[41:52] the from in order to get the data from
[41:54] table let's say that I would like to get
[41:56] a list of unique values of the country
[41:58] so the first thing that is going to do
[42:00] of course is to get the data from the
[42:01] database using the from and now the
[42:03] second step is the select soc going to
[42:06] execute it and going to select only one
[42:08] column the country all other columns
[42:10] going to be excluded and removed removed
[42:12] from the results and now scale going to
[42:14] go to the third step. It's going to go
[42:15] and apply the distincts on the country
[42:17] values. So it acts like a filter where
[42:20] it's going to make sure each value
[42:21] happens only once. So it's going to
[42:23] start with the first value Germany. Now
[42:25] it's going to look to the results. Do we
[42:26] have Germany? Well, we don't have
[42:28] anything yet. So that's why it's going
[42:29] to include it in the results. Then the
[42:31] next value is going to be USA. The same
[42:33] thing, we don't have USA in the results.
[42:35] So it's going to go and include it. And
[42:37] this happens as well for the UK. We
[42:39] don't have UK in the final results. So
[42:41] that's why it's going to go as well
[42:42] included. Now comes Germany again. Now
[42:45] it's going to say wait we have it
[42:46] already. So it will not go and add it
[42:48] again in the output because it must
[42:50] appear only once. So we will not have
[42:52] Germany twice. And as well for the last
[42:55] value the USA we have it already in the
[42:57] results. That's why it will not appear
[42:59] again. And with that we have removed the
[43:01] duplicates or the repetition inside our
[43:03] data. So each value is unique. Now let's
[43:05] go back to SQL. Okay. The task is very
[43:08] simple. It says return unique list of
[43:10] all countries. So let's go and do that.
[43:13] It's going to be funny. So select and
[43:15] now let's get the column country from
[43:17] our table customers like this. Now you
[43:19] can see we have a list of all countries
[43:21] but the task says we need a unique list.
[43:23] So that means I cannot have here
[43:25] repetitions inside it. And with that
[43:26] we're going to use the very nice
[43:28] distinct. So if you do it like this
[43:31] let's go and execute. You will see there
[43:33] will be no duplicates in your results
[43:35] and all the values in the result going
[43:36] to be unique. So with that we have
[43:38] solved the task. It's it's very simple.
[43:40] Now there is like one thing about the
[43:41] distinct that I see a lot of people
[43:43] using it a lot in cases that it's not
[43:46] really necessary. So for example let's
[43:48] go and get the ID. Now if you go and
[43:50] execute it you can see here we have a
[43:52] list of all ids and there are no
[43:53] duplicates. But now if I go and remove
[43:55] the distinct and execute it we will get
[43:58] the same results because the ids are
[44:00] usually unique. So it really makes no
[44:02] sense to go and say distinct because as
[44:04] you can see the database has to go and
[44:06] make sure each value happens only once.
[44:08] So there is like extra work for the SQL
[44:11] and it is usually an expensive
[44:12] operation. So if your data is already
[44:15] unique don't go and apply distinct only
[44:17] if you see repetitions and duplicates
[44:19] and you don't want to see that only in
[44:21] this scenario go and apply the distinct.
[44:23] Don't go blindly for each query applying
[44:25] distinct just in case there is
[44:27] duplicates. This is usually bad
[44:29] practices. Okay, so that's all for
[44:30] distinct. Okay my friends, so with that
[44:32] you have learned how to remove the
[44:34] duplicates using the distinct. In the
[44:36] next step, we're going to talk about
[44:37] another keyword that you can use
[44:39] together with the select. You can use
[44:41] top in order to limit your data. So now
[44:43] let's go and understand what this
[44:48] means. Okay. So what is exactly top or
[44:51] in other databases we call it limit. So
[44:53] it is again some kind of filtering in
[44:55] SQL. If you use it, it's going to go and
[44:57] restrict the number of rows returned in
[45:00] the results. So you have a control on
[45:01] how many rows you want to see in the
[45:03] results. The syntax is very simple as
[45:05] well. Directly after the selects, you're
[45:07] going to use the keyword top and then
[45:09] you specify the number of rows you want
[45:11] to see in the results. So for example,
[45:13] three and then only after that you
[45:15] specify the columns that you want and
[45:17] then from which table. Now let's see how
[45:19] going to execute it. So as usual the
[45:21] from going to be executed we will get
[45:23] our data and then the second step is
[45:25] going to go and select the columns in
[45:26] this case all the columns going to stay
[45:28] and then after that it's going to
[45:29] execute that job. So how it works? It's
[45:32] very simple. For each row in database,
[45:34] we have a row number. It has nothing to
[45:36] do with your data with the ids. For
[45:38] example, here like in the current
[45:40] result, we have row number 1 2 3 4 5.
[45:43] Those numbers are not your actual data.
[45:45] It is something technical from the
[45:47] database. So it is not equal to the ids.
[45:49] For example, the ids is actually your
[45:51] content your data. So here we are not
[45:54] filtering based on the data based on the
[45:56] row numbers. So since here we have
[45:58] defined three SQL going to count. Okay.
[46:00] row number one 2 three and that's it. So
[46:02] it's going to make a cut and all the
[46:04] rows after number three they will be
[46:07] excluded from the results and you will
[46:09] get only the three rows at the results.
[46:11] So now as you can see this type of
[46:12] filtering is not based on a condition or
[46:15] something. It's just based on the row
[46:17] numbers. So whatever results you have in
[46:19] your data it will go and make a cut at
[46:21] specific row. So let's go to scale and
[46:24] practice that. Okay. So now we have a
[46:25] very simple task. It says retrieve only
[46:28] three customers. So let's go and do
[46:30] that. We're going to go and select star
[46:31] from our table customers and execute it.
[46:34] Now as you can see in the output we have
[46:36] five customers. But the task says we
[46:38] want only three. And there is no
[46:40] specifications at all about any
[46:42] condition. So I don't have to go and
[46:43] make a work clause where we write a
[46:45] condition based on our data. We just
[46:47] want three customers. So we can do that
[46:50] very simply by just adding top exactly
[46:52] after the select and then specify the
[46:55] number of rows you want to see from the
[46:56] output. So select top three and then the
[46:59] star. Let's go ahead and execute it. And
[47:01] with that we are getting three
[47:02] customers. That's it. It's very simple.
[47:04] All right. Now moving on to another
[47:05] task. It says retrieve the top three
[47:08] customers with the highest scores. Now
[47:11] of course this is like a mix between
[47:13] ordering the data and filtering the
[47:15] data. Right? So we usually sort the data
[47:17] by the scores from the highest to the
[47:19] lowest. But now it's like we are doing
[47:20] both together. So let's do it again step
[47:23] by step. I will just back to the select
[47:25] star from customers. Now what we can do
[47:27] we can go and sort the data by the score
[47:29] from the highest to the lowest using the
[47:31] order by so order by score and then
[47:34] descending. So let's go and execute it.
[47:36] And now you can see the first customer
[47:38] is with the highest score and then the
[47:39] second highest and so on. Now I think
[47:42] you already got it in order to get the
[47:44] top three customers with the highest
[47:46] scores. What you have to do is to just
[47:48] go over here and say top three and
[47:51] execute it. And with that you have now a
[47:53] really nice analyzis on your data. It's
[47:55] like a reports where we are finding the
[47:57] top customers with the highest score. So
[48:00] this is really amazing and very easy. So
[48:02] as you can see mixing the top with the
[48:04] sorting the data you can make top end
[48:06] analyzes or bottom end analyzers. So
[48:08] let's have this task retrieve the lowest
[48:10] two customers based on the score. So now
[48:13] we want to get the lowest scores in our
[48:15] table. And in order to do that is very
[48:17] simple. What we're going to do we're
[48:18] going to flip that. So we're going to
[48:19] sort our data based on the scores
[48:21] ascending from the lowest to the
[48:23] highest. And since we want only the
[48:24] lowest two customers, we're going to
[48:26] replace the three with a two and execute
[48:28] it. And with that, we're going to get at
[48:29] the lowest two customers. It is Peter
[48:31] and Maria. They have the lowest scores.
[48:33] Again, it's very easy. Okay, this is
[48:34] fun. Let's go to the next one. Get the
[48:37] two most recent orders. Well, this time
[48:39] we are speaking about another table.
[48:41] Let's go and select everything from the
[48:43] table orders like this. So now, as you
[48:46] can see, we have here four orders and we
[48:48] want the two most recent orders. So most
[48:51] recent means we have to deal with the
[48:53] order dates and we can build that by
[48:56] sorting the data by the order dates. So
[48:58] order by order dates and since we are
[49:01] saying the most recent orders so from
[49:04] the highest date to the lowest that
[49:06] means descending right let's go and
[49:08] execute it and as you can see based on
[49:09] our data and now we can look to our
[49:11] result this is the last order in our
[49:13] business based on the order date and
[49:15] this one is one of the earliest orders.
[49:17] So with that we have sorted the data and
[49:19] since we want the two most recent orders
[49:22] we go over here and say we go exactly
[49:24] after the select and say top two and
[49:26] execute and with that we have now the
[49:29] last two orders in our business. So as
[49:31] you can see combining the top with the
[49:33] order by you can do amazing analyszis.
[49:36] All right so this is how you limit your
[49:37] data using top and with that you have
[49:39] learned the basics everything that you
[49:41] can learn and with that you have learned
[49:42] all the clauses the sections that you
[49:45] can use in any query in SQL. Now next
[49:47] what we're going to do we're going to
[49:48] put everything together in one query in
[49:51] order to learn how SQL going to go and
[49:53] deal with all those clauses and how SQL
[49:56] going to go and executed. So let's go
[49:57] and do
[50:01] that. Okay. So now I'm going to show you
[50:03] the coding order of a query compared to
[50:06] the execution order that happens in the
[50:09] database. So the coding order of a query
[50:11] starts always with a select and then
[50:13] exactly after that you can put a
[50:14] distinct and then after the distinct you
[50:16] can put a top. So this is the order of
[50:18] all those keywords and then you can go
[50:20] and select like few columns and after
[50:22] you specify the columns separated with a
[50:24] comma you tell SQL from which table your
[50:27] data come from using the from clause.
[50:29] Now after that if you want to filter the
[50:31] data before the aggregation you can use
[50:33] the where clause and this always comes
[50:35] directly after the from. And if you want
[50:37] to group the data then you have to do it
[50:39] after the work clause using the group by
[50:42] and after the group by comes the having
[50:44] if you want to filter the data. And the
[50:46] last thing that you can specify in query
[50:48] it is always the order by. So this is
[50:51] the order of all those components of the
[50:53] query. And if you don't follow this
[50:54] order you will get an error from the
[50:56] database. Now if you look to this query
[50:58] there are a lot of things that's going
[51:00] to filter your data. So let's check them
[51:02] one by one. The first thing that you can
[51:03] do is to filter the columns. If you
[51:05] don't want to see all the columns, you
[51:07] want to see only specific columns, you
[51:08] use the select and of course you must
[51:10] use it. So the columns that you specify
[51:13] will be shown in the results. So it's
[51:14] like filtering the columns. Now there is
[51:16] another type of filter where you filter
[51:18] out the duplicates if you want to see
[51:20] unique results and that's using the
[51:22] distinct. So this is another type of
[51:24] filter. Moving on, we can filter the
[51:26] result based on the row numbers. So we
[51:29] can limit the result using the top. But
[51:31] this type of filter doesn't need any
[51:33] conditions. It's purely based on the row
[51:35] number in the results. Now moving on, if
[51:37] you want to filter your data based on
[51:39] conditions based on your data, you can
[51:41] filter the rows before the aggregation
[51:44] using the wear clause. And the last type
[51:46] of filtering, you can filter your rows
[51:48] after the aggregation using the having.
[51:50] So as you can see, we have like five
[51:52] different types and how to filter the
[51:55] results in SQL. So now let's see the
[51:57] execution order. As we learned the first
[51:59] thing that's going to happen is that SQL
[52:01] going to execute the from clause. So SQL
[52:04] going to go and find your data in the
[52:06] database where all the next steps going
[52:08] to be pasted on this data. Now the next
[52:10] step that is going to do is that it's
[52:11] going to go and filter the data using
[52:14] the wear clause. This has to be happen
[52:16] before anything else. So before any
[52:17] aggregations and so on we have to make
[52:19] scope of the data. So once SQL apply it
[52:22] maybe some of the rows going to be
[52:23] removed and once the data is filtered
[52:26] the third step SQL going to execute the
[52:28] group I so going to take the results and
[52:31] start combining the similar values in
[52:33] one row and start aggregating the data
[52:36] based on the aggregate function that you
[52:37] have specified. So now after the group
[52:39] by after aggregating the data what SQL
[52:41] going to do now it's going to go and
[52:43] apply the second type of filter the
[52:44] having. So based on the condition the
[52:46] SQL going to go and start removing few
[52:48] aggregated data away and keep the rest.
[52:51] Now moving on to the step number five.
[52:53] Finally it's going to go and execute the
[52:55] select distincts. So SQL going to go and
[52:57] start selecting the columns that we need
[52:59] to see in the results and remove the
[53:01] other stuff. And once the columns are
[53:03] selected SQL going to go and execute the
[53:06] order by. So SQL going to start sorting
[53:08] the data based on the column that you
[53:10] have specified and the mechanism as
[53:12] well. So the data will be sorted
[53:13] differently. And my friends the last
[53:15] step that going to happen in your query
[53:18] will be always the top statements. So
[53:20] based on the final final results SQL
[53:23] going to go and execute the top. So here
[53:25] we are saying top two that means we want
[53:27] to keep only the first two rows without
[53:29] any conditions. So SQL going to count
[53:31] okay row number one two and after that
[53:33] it's going to make cuts and remove
[53:35] anything after that. So this is the last
[53:37] filter that's going to happen and as
[53:39] well the last step. So now if you sit
[53:41] back and look at this the coding order
[53:43] is completely different than the
[53:44] execution order in the coding we have
[53:46] first to specify the select actually the
[53:48] select going to be executed just almost
[53:51] at the end. So at the step number five
[53:53] and once you understand how SQL execute
[53:55] your query you can understand how to
[53:57] build correct queries. Okay my friends
[53:59] so with that we have learned the basics
[54:01] about SQL query the basic components of
[54:04] the select statements and with that you
[54:06] can talk to our database in order to get
[54:08] data. Now, in the next chapter, we're
[54:09] going to learn how to define the
[54:11] structure of our database. So, we're
[54:12] going to learn the data definition
[54:14] language, DDL. If you like this video
[54:16] and you want me to create more content
[54:17] like this, I'm going to really
[54:19] appreciate it if you support the channel
[54:21] by subscribing, liking, sharing,
[54:23] commenting, all those stuff going to
[54:25] help the channel with the YouTube
[54:27] algorithm and as well my content going
[54:29] to reach the others. So, thank you so
[54:32] much for watching and I will see you in
[54:34] the next tutorial.
[54:35] Bye. Heat. Heat.
[54:43] [Music]