SQL SELECT Queries: All Essential Clauses Explained for Beginners | #SQL Course 4

Summary

In this comprehensive tutorial by Data with Baraa, you will learn how to effectively query data using SQL. The session covers essential clauses in SQL SELECT statements including WHERE, ORDER BY, GROUP BY, and HAVING. The tutorial begins with a simple explanation of the SELECT and FROM clauses, advancing step-by-step into more complex queries that allow for data filtering, sorting, and summarization. By the end of this video, you will understand the execution order of SQL queries and be able to construct queries that retrieve exactly the data you need from your databases.

Highlights

• SQL queries are a powerful way to retrieve specific data from databases without altering data. 🤓
• The SELECT and FROM clauses are fundamental and serve as the starting point for all SQL queries. 🛠
• WHERE clauses filter data before aggregation, while HAVING filters post-aggregation. 🗝
• ORDER BY helps in sorting data, with ASCENDING and DESCENDING options for clarity. 📈
• GROUP BY is crucial for grouping data, often used with aggregate functions like SUM or COUNT. 🧮

Key Takeaways

• Master the art of querying data with SQL and understand key clauses. 🎯
• Learn to filter data with WHERE and HAVING clauses for precise results. 🔍
• Understand the importance of GROUP BY for summarizing results. 🗂
• Discover how ORDER BY and LIMIT can refine and control your query output. 📊
• Grasp the execution order of SQL query clauses for optimal database interaction. 🚀

Overview

The tutorial kicks off with Data with Baraa walking us through the basics of SQL SELECT queries. Starting with simple SELECT and FROM clauses, Baraa covers how these fundamental components help in fetching all data from specified tables. He demonstrates their usage with practical examples that illustrate how SQL interprets these commands.

Moving deeper, the video elaborates on filtering techniques using WHERE and HAVING clauses. These are essential for refining your data queries, allowing selective retrieval of records based on specific conditions. The example scenarios make it clear how WHERE filters data before any grouping or summing, while HAVING works on already grouped data.

The session wraps up by explaining advanced query management using ORDER BY for sorting and LIMIT for controlling the number of returned results. These techniques are crucial for managing data output efficiently, ensuring you can customize your data views to fit any analysis. By mastering these clauses, viewers are equipped to handle complex data tasks with ease.

Chapters

• 00:00 - 00:30: Introduction and Overview The chapter begins with an introduction to querying data using SQL. It covers the basic sections and clauses within a SELECT statement, including WHERE, ORDER BY, GROUP BY, HAVING, and others. The goal of the chapter is to ensure that by the end, the reader understands both the coding order and the execution order of a query. The chapter starts by explaining the foundational concept that data is stored in tables which are inside databases.
• 00:30 - 01:00: Basic Concepts of SQL This chapter covers the fundamental concepts of SQL, focusing on how it enables users to communicate with databases. It explains the necessity of using SQL to extract data by responding to business questions such as 'What is the total sales?' or 'What is the total number of customers?'. To retrieve data, users must write queries using SQL, specifically employing a 'select' statement to ask the database for the needed information.
• 01:00 - 01:30: Executing SQL Queries In this chapter, the process of executing SQL queries is explored. The fundamental concept emphasized is that SQL queries are essentially questions posed to the database. When a query is executed, the database processes it and returns the requested data. Importantly, using a 'select' statement in your query solely allows for reading data; it does not alter the data within the tables or the structure of the database. This understanding is crucial for utilizing SQL effectively without unintended modifications to the database system.
• 01:30 - 02:00: Components of an SQL Query This chapter introduces the concept of SQL queries, emphasizing that queries are used to retrieve data from a database. It explains that SQL queries are composed of various components or 'clauses,' which enable the user to tailor the query to answer specific questions about the data. The chapter promises a step-by-step exploration of these clauses to equip the reader with the necessary tools to construct any needed queries.
• 02:00 - 02:30: The SELECT and FROM Clauses The chapter introduces the SELECT and FROM clauses which form the basis of the simplest SQL query. It emphasizes the importance of understanding SQL operations and query syntax. The instructor plans to demonstrate the step-by-step execution of SQL queries using example syntax and the customer table in a database.
• 02:30 - 03:00: Using SELECT * This chapter explains the basic usage of the SQL SELECT * statement which is used to fetch all columns from a table. The SELECT * syntax is introduced as the simplest form of selecting data. By using SELECT *, one chooses all columns available in the table specified in the FROM clause. The chapter emphasizes that SQL queries typically start with the SELECT keyword and include the FROM clause to designate the data source. This is an elementary SQL query setup designed for retrieving full table data.
• 03:00 - 04:00: The WHERE Clause This chapter focuses on the WHERE clause in SQL. It explains that after selecting the data source using the FROM clause, SQL retrieves all data from the specified table. The subsequent step involves SQL checking the SELECT statement to determine which columns to retain in the result set. When using the '*' symbol, all columns are retained in the result.
• 04:00 - 05:00: The ORDER BY Clause This chapter focuses on the importance of selecting the correct database when querying data using SQL. It emphasizes making sure the right database is connected before running any queries. The transcript mentions starting a new query and finding the database by expanding the database list in the SQL environment, such as an SQL management studio or a similar tool. There's also a brief mention of using an SQL command (`USE`) to switch between different databases.
• 05:00 - 06:00: Nested Sorting and ORDER BY This chapter introduces the concept of nested sorting and the ORDER BY clause in SQL. It starts by explaining how to select and switch databases using SQL commands. Then, it delves into the importance of comments within any programming language, including SQL, highlighting how comments can help in making the code more understandable without being executed. The chapter explains that there are two types of comments: inline comments using two dashes, and presumably some others, although the text does not specify further.
• 06:00 - 07:00: The GROUP BY Clause The chapter explains how to make comments in SQL using single-line and multi-line formats. Single-line comments are created using two hyphens, and every character following these hyphens on that line becomes part of the comment. Multi-line comments start with a slash and an asterisk (/*), and every character following this until a closing asterisk and slash (*/) are part of the comment. The transcript demonstrates these methods with examples, indicating comments by the green color coding in their SQL editor.
• 07:00 - 08:00: The HAVING Clause The chapter titled 'The HAVING Clause' begins by discussing two types of comments in SQL. The focus then shifts to executing a query that retrieves all customer data from a table named 'customer'. The query involves selecting all columns and rows, emphasizing the use of 'SELECT' followed by '*' to denote selection of all data.
• 08:00 - 09:00: The DISTINCT Keyword The chapter titled 'The DISTINCT Keyword' seems to introduce the basics of retrieving data from a SQL database. It delves into forming a SQL query by specifying the source table (in this case, the 'customers' table), and emphasizes the importance of correctly naming the table as it appears in the database. The discussion highlights executing the query to view the resultant data, which spans four columns across five rows. This setup allows users to view all data pertaining to five customers including the columns relevant to these entries. The explanation is aimed at simplifying the process of query execution to display database contents.
• 09:00 - 10:00: The TOP Keyword This chapter discusses working with databases using SQL queries. It begins by explaining how to query the database to retrieve desired data. The specific task outlined involves retrieving all the order data from a table. The chapter details writing a simple SQL query using 'SELECT * FROM orders' to extract all data entries from the 'orders' table and interpreting the resulting data, which includes four columns.
• 10:00 - 11:00: Putting it All Together In the chapter titled 'Putting it All Together,' the transcript discusses database queries in SQL. It begins by mentioning a table with four rows, representing four orders, from which all data can be observed. This data shows there are five unique customers who collectively generated four orders. The focus then shifts to interacting with the database using SQL queries, emphasizing the basic form of querying. The narrative then sets the stage for further steps in querying, specifically differentiating between selecting all columns and the desire to view specific data columns from the database.
• 11:00 - 11:30: Conclusion and Next Steps In the 'Conclusion and Next Steps' chapter, the focus is on optimizing database queries by selecting specific columns rather than retrieving all data. The chapter outlines the importance of specifying the needed columns to improve efficiency and clarity. It discusses changing the syntax to list the desired columns after the SELECT statement, using commas to separate them, while the FROM part of the query remains unchanged. This approach is crucial for managing data retrieval effectively.

SQL SELECT Queries: All Essential Clauses Explained for Beginners | #SQL Course 4 Transcription

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