Master SQL: Comprehensive Guide to Advanced Data Analytics and Optimization

Hello and welcome to this unique course to master SQL. My name is Barzalini and I lead big data projects at

Mercedes-Benz over a decade of experience in SQL data engineering, building data warehouses and data

analytics. Now, of course, the first question is what makes this course so special. Well, not only you will learn

how to write SQL codes, but more important than that, you will learn how exactly SQL works behind the scenes. So

I'm going to break complex concept in SQL using hundreds of animated visuals. This makes it really easier to

understand SQL and as well it is more fun than just sharing my screen and I just show you code. Right. The second

reason is this course is taught by me. I have industrial experience and I will be sharing with you everything that I know

about SQL and how I use it in my real projects. So I will be sharing with you hundreds of best practices, tips and

tricks and I'm going to show you my decision-m process in SQL. So by the end of this course, you will be ready to

solve any complex task like I do using SQL. So now I designed this course to cover the basics like writing your first

SQL query and then we're going to keep progressing in the course by covering advanced techniques in SQL like the

window functions, stored procedures, indexes and even at the end we're going to build a data warehouse using SQL. And

this course is suitable for anyone data engineers, data analyst, data scientist and even for students. And by the way

the good news everything is for free from the start until the ends I will be sharing with you as well a lot of

materials code presentations and animations and there are no hidden costs. So you don't have to pay for

anything. But my friends in return I really appreciate it if you support the channel in order to grow. All right my

friends I'm really excited about it. I don't know about you. If you are motivated join me learning SQL. This is

going to be amazing. So let's go. All right. Now I'm going to show you the road map in order to learn

everything about SQL starting from very basics and then advance step by step until we have very advanced topics. So

now at the start we have to understand few stuff like what is SQL, why to learn it, what are databases and the types of

databases and after the theory we're going to prepare your PC with data and the softwares. Now once we have

everything then we can go to the next chapter. This is the basics how to query data using SQL and here we're going to

cover the basic components in each SQL query like select from where those basics. Now once you understand how to

query the data, how to get the data out of the database the next step we're going to go and learn how to define the

structure of the database. How to create a new table add a new column remove column and as well how to drop a table.

So with that you are defining new stuff in the database and then the next chapter you have to learn about the data

manipulation. This time we're going to go inside the table and we're going to learn how to insert a new data, how to

update the data and as well delete few rows from our database. So with that you have the basics how to query data, how

to define the structure of your tables and how to manipulate your data. And I can say with that you cover the basics

about SQL. Now after that we start with the intermediate phase where we're going to deep dive into topics like how to

filter your data. Here we're going to learn about the comparison operators, logical operators, between and like. So

all the operators that you can use in order to build a condition in order to filter your data. Then after that it's

going to be very interesting topic. You have to learn how to combine them. And here we have two mechanism either using

the join or using the set operators. And oh my god joining data. It's going to be very interesting topic. Here we're going

to cover like a lot of stuff like we're going to start with the basic joins and then we go to advanced and then you have

to learn how to choose the right join and after that you have to learn about the set operators and here you have like

four methods union union all except intersects. So with that you learn how to combine multiple tables by combining

the columns or the rows of your tables. So this is very important. Now moving on in our course. Now using SQL you can do

a lot of stuff cleaning up the data a lot of data preparations and at the end you can do a lot of analytics and

aggregations. So there are like two families of functions. The first one is the role level functions and here we

have a lot of stuff you can transform your string values the numbers date and time and how to handle the nulls in SQL

and at the end the amazing case statements. So all those stuffs are transformation for only one single

value. We call it role level functions. And after you learn how to do data transformations, then you have to learn

about how to do data analytics and aggregations using SQL functions. So we're going to start with very basics

like the aggregate functions. And then we're going to deep dive into the window functions, analytical functions. And

here we have like aggregates, ranking and value functions. Those are very important tool for any data analyst or

data scientist doing analytics task in SQL. So I can say the rowle functions is for data engineers and the analytical

functions are for data analysts. So at the chapter 8 we can say you have covered now the intermediate level and

the last four chapters they will be the advanced stuff in SQL. So here there are a lot of techniques that you have to

learn about SQL. So the first one is the subquery query inside another query and the very famous CTE common table

expression. A lot of developers like this one and then you will learn about how to create views in the database.

This technique if you learn it you're going to be really professional in SQL. Then we're going to learn how to create

tables using select the temporal tables and then we're going to learn about the third procedures how to write a program

in SQL and after that of course comes the triggers. So those are the advanced techniques that you have to learn in SQL

in order to do advanced projects using SQL. So now once you learn all those concepts and you start writing a lot of

SQL codes you will notice that some queries going to be really slow and for that you have to learn how to optimize

the performance of your queries and here there are a lot of techniques. The most famous one is to create an index in the

database or create a partition and at the end I will be sharing with you the top 10 best practices that I have

learned in my projects on how to optimize the performance of your queries. So this is very important and

then we're going to move to very interesting one. I will be sharing with you how I use AI like shy GBT or copilot

as I'm using SQL in my projects. So here you have to learn how to write correct prompts to get assistance from AI as you

are using SQL. And finally and my favorite one it will be about SQL projects. So my friends here you have to

bring everything that you have learned about SQL in handon projects. With real projects you will get challenges and

struggle and here going to happen the magic and the real learning and here there are three types of projects. The

first one is data warehousing project. This is very data engineering focused project where you're going to learn how

to build real data warehouse where you're going to take the data from the raw formats and then process it in

different layers. Once you build it then you jump to another project. Here you're going to start exploring the data and

start getting the first insights about the business. And the last project that you can do is the advanced data

analytics project. So this is very important section where you do SQL projects. So my friends this is the road

map on how to learn SQL. So as you can see it takes you step by step from basics to intermediate and you will end

up having advanced topics and with that I can tell you you will learn everything about SQL. Okay. So now let's start with

the first chapter the introduction to SQL and here we're going to cover few topics. So we have to understand first

what is exactly SQL? Why we have to learn it? what are databases and the different SQL commands that we have in

SQL. So it is the basics the theory about SQL. So what is exactly SQL? Let's go. So what is exactly SQL? Everything

generate data and data is everywhere. Your first name is data your mobile and everything inside the mobile is data.

Car is as well generating a lot of data. Bank, your finance statements, everything is data. And now of course

the question is where do we store our data? Personally we store a lot of our data in like excels, spreadsheets in a

text file. So you store a lot of your data in different files. Now how about companies? They have a lot of things

that generate a lot of data that the products that they produce their customers as well generating a lot of

data and sales informations and a lot of things. So companies generate massive amount of data. So now the big question

is how they handle the data how they store it. Of course, they cannot go unused like simple files. They need

something bigger, stronger and smarter. And here where the database comes in. So think about the database. It's like a

container for storing data. But instead of just dumping files into folders, the database organized the data. So it is

easy to access, to manage and to search. So a database simply it is a container that stores data. So now you might ask

why we are using database. Can't we just use files like I do it personally? Well, let me tell you why we use databases.

Imagine that someone asks the following question. Go and find the total spending in your data. So now, in order for Mike

to find the total spending and the costs, he will be opening each of those files one by one, searching for the

costs trying to combine the data and it's going to be very long and messy process. But now in the other side, if

your data in database and you want to ask a question, it's going to be very easy. So all what you have to do is to

talk to the database to ask a question and the database can answer your question with a result. And now comes of

course the question how do we talk to a database? Well we use SQL. SQL is the language that you use in order to talk

to the database. It stands for structured query language SQL. And here you have people that call it SQL like me

and others that call it SQL. There is no right and wrong but if you follow me through the course I think you will

start saying SQL. So by using SQL you can ask the database you can ask your data and the database going to answer

your question by sending you a result. So this process is very easy simple and fast and this is way better than having

your data stored in different files. Another reason why we use databases is that they can handle really huge amount

of data. So sometimes we have like millions of data inside our database but in the other side if you are storing

your data inside spreadsheets and you have like massive amount of data what can happen your spreadsheets going to

just break they simply can't handle big data and another reason why we use databases is that it is just secure. It

is safer to store important and critical data inside the database than just storing it in spreadsheets and files. So

the databases are secure and you can control who is accessing what. So it is just more professional to store the data

inside a database. All right my friends so far what we have learned most of the companies stores their data inside a

container called a database and for you in order to ask questions and to talk to your database you have to speak the

language of SQL. Now I'm going to show you how it looks like usually in companies. So we

have our data inside the database and then you will have multiple people with multiple roles that are just writing

different SQLs in order to talk to the data. But now not only employees and people interact with the database. You

could build a website or an application that as well interacts with the database by sending different SQLs. And of

course, depend on how many people are interacting with the application and the website, it might generate really

massive amount of SQLs that sent to the database. And not only that, you might has as well tools in order to do data

visualizations where you have like a dashboard or reports maybe created using PowerBI or Tableau and it is used by

stakeholders and managers in order to make decisions and as well those tools will be connected to the database and

creating SQLs. So now as you can see we have a lot of interactions with the database from people applications tools

a lot of things are generating SQLs and interacting with the database but the database is just a container and storage

right so we need something a software that manage all those requests and that's why we have something called

database management system DPMS so it is a software that going to manage all those different requests to our database

and it going to make the priority which SQL must be executed First, this software can as well manage the security

whether the SQL is allowed to be executed in the first place. So my friends, the DPMS is the software that

going to manage the database. And now we are not done yet. There is something missing. So we have our data, we have

the software. What is missing here is the hardware. So in real companies, we cannot run that on our PC because first

our PC is weak and as well it goes offline. That's why we need a server. server it is like very powerful PC and

as well it lives 24/7 so it is always available and here we can decide whether we're going to have a server inside the

company or we can use cloud services in order to run our database so my friends so far what we have learned the database

it is container to store the data the SQL it is the language in order to talk to the database the DPMS it is the

manager it manage the database and the server it is the physical machine where the database lives so this is how it

looks Like and now my friends there are different types of databases. So let's

see what do we have. The first and the most famous one it is the relational database. It is very simple. It is like

spreadsheets call them table where we have columns and rows and then there is like a relationship between those tables

to describe how they relate to each other and that's why we call it relational database. So if people hear a

database they're going to think about this one. Now we have another type of databases called key value. This time

the data is organized completely different where you have pairs of keys and values. Think about it. It's like a

big dictionary where you have a word like the key and the definition of the word this is the value. And now moving

on to the next one. This is as well important column based. So now instead of grouping the data by the rows this

type of databases group the data into columns. That's why it's called column paste. And this is very advanced

database in order to handle huge amount of data where the main purpose is to search for data. Moving on to another

database called graph database. The main focus here is the relationship between objects. So the main idea here is how to

connect my data points. And now finally we have the document database. The data is stored as entire documents where the

structure of the data is not that important. What is more important is to fit everything in one page in one

document. And now if you look to those five types, we can group the document, graph, column based, key value, all

those databases called NoSQL databases and the relational database, SQL database. And in this course, we will be

focusing of course on the relational database. And I'm sure you have heard about like the Microsoft SQL server, the

MySQL, the possesses they are SQL relational database. And for the key value you have

the radius the Amazon Dynamo DB and we have for the column paste we have the Cassandra and the red shift. For the

graph database we have the Neo 4G and the very famous database the MongoDB as a document database. Now my friends for

this course we're going to be focusing on the SQL relational databases because it is the most famous one and the most

used one in companies and I will be focusing on the Microsoft SQL server. So those are the different types of

databases. Now the databases are very structured and organized. It has the following

hierarchy. The starting point is the server as we learned it is powerful PC and it is where the database lives and

inside it we can have multiple databases. So maybe you have a database for the sales and another one for the

HR. So the server can host multiple databases and as we learned a database is a container of your data. Now moving

on to the next level. In each database we can have multiple schemas. A schema it is like category or you can call it a

logical container that we can use it in order to group up related objects like let's say you have hundred of tables. So

you can split all the tables that has to do with the orders in one schema and then another group of tables with the

schema customers and so on. So it help you to organize your tables and your objects in the database. And now if you

go inside schema you can have multiple objects like tables. So now of course the question is what is a table? It is

like spreadsheet. It organize your data into columns. The column define the data that you store inside it. So you have

one column about the customer ID. Another column about the names, the scores, the birthday. So each column is

about one type of data and sometimes we call the columns as fields. Now the other thing that we have in tables is

the rows or sometimes we call it records. It is where actually the data is stored. Now in this example each

record represent one customer one person. So we have one record for Maria, John and Peter. Those we call them rows.

Now in each table there is like one very important column called the primary key. It is always very important to have like

one unique identifier for each customer for each row and we use it for different purposes in order to combine it with

another table in order to identify quickly one customer. So it is unique. It's like fingerprint and there is no

two customers having the same ID. Now the overlapping between the columns and the rows we have a single value a cell

and each value each column stores specific data type. A data type it is like what kind of data we are storing

like an integer 1 2 30 or a decimal where you have a decimal point 3.14. Now if you want to store characters we have

different data types for that like you want to store the name or the description. So here we can use the char

or the vchar. So you store inside them like the first name Maria or something. Now you might ask what is a char or

vchar. So the char always a fixed one. So if you define it like five characters always it's going to go and reserve five

characters from the space. But if you want things more dynamic then you go with the vchar. And now moving on we

have another data types called the date and time. So if you want to store a date like the birth dates and if you want to

store the time information you can use the time data type. So we call those stuff int, decimal, char, date, time.

They are data types. So my friends, as you can see, SQL databases are very organized and

structured. Okay. So now let's focus more about the SQL itself. We have in SQL different type of commands. So let's

say that we have a database and this database is empty. So we have nothing inside it. Now, of course, the first

thing that you have to do is to write an SQL with the command create in order to create brand new table in the database.

So, once you executed the database going to go and build one, but this table is empty. So, we have nothing inside it. So

now what you have done here is you have defined something new, right? And we call this type of commands the data

definition language, the DDL. We have create to create something new, alter in order to edit something that already

exists and drop in order to delete something. to drop for example a table. So this is the first family of commands.

Now if you look at our table, it is empty. What do we need? We need data. So let's say that we have a website or an

application. Now this application is generating a lot of data. Now in order for this application to move the data

inside our new table, it must use the SQL command insert. So if you execute insert, you can add a new data inside

your table. This type of commands we call it data manipulation language. And here we have three commands. insert in

order to insert a new data, update in order to update an already existing data and delete in order to go and delete

data from your table and that's why we call it data manipulation language because you are manipulating your data.

So what do we have now? We have table, we have data inside the table. Now what we can do we can start asking questions.

So let's say that you have analytical question about your data. Now all what you have to do is to write something

called SQL query and inside it you use the command select but the whole thing we call it a query. So you send a query

to the database, you have a question and the database can return for you the result, the data answering your query,

your question and we call this type of activities using SQL, the data query language. And here we have only one and

it is very famous. We have the select. We can use it in order to query our data. So those are the three different

commands in SQL. And of course, we're going to learn all of them, but we will spend most of our time learning how to

write the correct query for the correct answer. And now you might ask me, Barra, why we have to learn SQL? And if the

time goes back, are you going to learn SQL again? Well, for sure, of course. And here are the top three reasons that

I have. The first one, you have to learn it in order to talk to the data. You know, most of the companies stores their

data in databases, and this is a standard way. This is how they do it. And if you want to work on the company

in the data field and you want to talk to their data, then you have to use SQL. It's like you move to another country

where they speak another language and you want to live there for a long time, you have to speak their language. The

same thing here. If you want to work with data, you have to learn the language in order to speak to the

database, the SQL. So this is for me the most important reason why we have to learn SQL and SQL it is in high demand.

If you go now and check the job description of the software developer, data analyst, data engineer, data

scientist, I promise you you will find there that they going to demand for SQL. So you will find they going to ask for

SQL skills almost in each job description. So if you check for any data related jobs, you will find that

they going to ask for SQL skills. Now another reason that I have is it is industry standard. So if you go and

check multiple modern data platforms and tools like PowerBI, Tableau, Kafka, Spark, Synaps, you will understand that

there will be always a section where you have to enter SQL code. So most of those vendors adopt SQL because it is the

standard. It is widely used. It is like selling points that their tools are easy. So those are my top three reasons

why SQL is still relevant and why you have to learn it. Okay, my friends. So with that we have now clear

understanding what is an SQL why we need it what are databases and their different types why do we have DBMS

servers and as well now you have understanding how things are very organized and structured inside the

databases so that's all this is SQL all right so with that we have covered the basics about what is SQL and databases

now in the next step we're going to go and set up our environments so that means we're going to prepare your PC

with the data with the databases and all the tools that you need in order to learn

SQL. Okay. So now go to the link in the description and you will land here in my newsletter website and you can subscribe

if you want to get weekly news about my content. I make as well post about data and many other projects. So once you do

that what we're going to do now we're going to go to the downloads over here and you will find here all the materials

of different courses and the one that we want is SQL ultimate course. Let's go over here. Now once you do that you will

land to this page where I have listed all the important links. So the first one and the most important one is to go

and download the course materials. Here you can find everything code the slides the presentations the whole course or if

you don't want that you can go to my get repository and there you will find exactly the same materials. So let's go

and download everything. Okay. So now go and put the downloaded folder somewhere safe and let's go inside it. And here

you can find three things. The first one is the data sets. Here if you go inside it you will find the data for the course

the databases that we will be using in order to practice SQL. So everything is available here. Now the second folder

you can find all the documentations. So that means all the visuals the presentation slides everything that I

present during the course. It is available here as a documentation notes for you. Now moving on to the third one

we have the scripts. So during the course we will be writing a lot of SQL codes and all those codes are here

available. So that means those are all the codes that is used in the course. Okay. So with that you have now all the

course materials. All right. So now the next step is that we have to go and download the SQL Server Express and you

can find the link as well over here. So let's go there SQL Server Express. And now we're going to land on the Microsoft

page where we can see the different offering from Microsoft where it's called server. So either we have it on

the Azure or we can download it on the on premises. But we don't want those stuff. Just scroll down to see those two

options. So the first option on the left side we have the developer edition. You will get all the features and services

that Microsoft offers with the SQL server. It is as well free but the installation here is little bit

complicated. But in the second option on the right side we have the express edition. Installation here going to be

really fast and very easy. You will get as well all the stuff that you need for practicing SQL and learn SQL. So both of

the options are free. It's just a matter of the installation. We will go now for the express edition. So go and click

download now and it's very small file. So let's go and start it. And now the installation going to start. So we have

basic, custom and download media. So download media means download now and later we're going to do the

installation. Custom means we have more control on how to download and install the stuff. The basic is the easiest one

and the quickest one. So let's go with the basics and click on that. And let's go and accept all those stuff. And now

let's click on install. So now it's going to install the applications, drivers and so on. It may take a little

bit time. So in order to do that, let's go and click on install SS SMS. So let's

click on that and as well we can find the link over here. So let's go to SQL Server Management Studio. So let's click

on that. You can find of course this link as well with the other links that I have collected. So now we are again at

Microsoft page. Let's go scroll down and now we will see the following link free download for SQL Server Management

Studio SS SMS. So let's go and click on that and then it's going to go and download it. Let's go and start it. So

the first thing that we have to define the location. I will go with the default stuff. So let's click on

install. Okay. Setup completed. We just installed SM SS SMS. So let's go and close it. So now let's go and start it.

If you go to your menu over here, search for SQL Server and you will find it here. SQL Server Management Studio.

Let's go and start it. Okay, so now we're going to get this window in order to connect to our server. So again, what

is our server? It is the one we have installed at the first step, SQL Server Express. And that's why you're going to

see in the server name, your PC name, of course, like it's not going to be my PC name. But here we have something called

SQL Express. This is the server we just installed. So in the first option, we have database engines. We have reporting

services. Those are different stuff from Microsoft. We're going to leave it as a database engine. And it should be like

this. SQL Express. Now, how to access this database? We have the following stuff. We can do that using the window

authentications or SQL server authentications. I'm going to say that let's stick with the window

authentication. And the username going to be the PC name and as well the window user. If you don't have it for some

reason those informations, you can go to your search search for cmd and then here you can say who am I?

And with that you will get the PC name and as well the user that you are currently logged in. And this is exactly

what I'm seeing over here. One more thing if you're having issue connecting to your database make sure to check the

encryption. It should be mandatory and to click on the trust server certificates. So once you do that you

will be able to connect. Okay. So with that we have the server we have the client. And now the last step we have to

go and create the database. We want to insert our data. So now if you look to the object explorer and open the

databases you can see that we don't have any database. So now let's do something about it. Go back to the course

materials inside the data sets you will find the following. You will find we have here three folders MySQL postcress

and SQL server. So if you want to follow with this course using different database like MySQL and Postgress you

can find the exact same data for the database that you are using. But now in this course we are using the SQL server.

So if you follow me with that go inside the SQL server folder and here you will find four files with different

extensions. So what is going on here? Now for this course we have two databases. One that is very simple

called my database and second one that has more tables called sales DB. And now in SQL server there are multiple ways on

how to create databases. I will show you now two methods on how to create the database. Now the first option we want

to create the database from a script. And if you look to those files, we have here two files with the extension SQL.

Those are files with SQL code. So let's start with the first one, the init SQL server my

database.SQL. Go inside it. And now here we have the SQL code. Copy everything. And now let's go back to our studio and

then go to the menu and click on new query. And here in the middle you can paste the code. So now we have the code

for the first database. And all what you have to do is to go and execute it. So once we executed you will see we will

not get any error. And now on the left side we don't see yet our database because we have to refresh. So right

click on the databases and click refresh. And now you can see it my database. So now let's see the content.

Go extend it and then go extend the tables. And now you see here our two tables customers and orders. Inside

those tables we can find our data. In order to see the data right click for example of the customers and let's go

with the option select top 1,000 rows. Once you do that you can see now in the results we have here five customers.

This is our data inside the table customers. So here again about the interface on the left side we have the

object explorer where you can see the whole structure of the database from server to databases to tables. So you

can see the whole structure on the top we have a menu with a lot of icons and then in the middle this place here we

call it the SQL editor. We're going to go and write their SQL codes and then once you execute it at the bottom you

will get the result and messages and below the SQL editor we have the output. So here you can see for example the data

the results or different messages from the database. So the interface is very simple. Now we have to go and get our

second database. So if you go back to our files you can find a second SQL file the initql server sales db.sql. Open

that and let's go and copy everything here and let's go back to our studio. Same thing you have to go and create a

new query then paste the whole code and this database is about the sales DB. So let's go and execute it and with that we

will not get any errors and now we go to the left side and we do the same thing refresh and we can see the second

database sales DB. Now we can go and explore it. So extend it go to the tables and here you can see five tables

customers employees orders products. So here this is the intermediate database for our course. So now let's go and

check our data. For example, let's go to the orders, right click on it and select top 10,00. And those are the orders of

our database. Perfect. So everything is working. So those are the main two databases that we will be working

through the whole course. And of course if you want to go and practice using another database, it's totally fine. For

example, in Microsoft, there are a database called Adventure Works. It is really amazing. And I'm going to show

you now how to import it. We can go over here the adventure works. So let's click on this link. So now we are again in

Microsoft page. If you scroll down you can see here three different types of databases. The OLTB, data warehouse and

lightweights. So they are like different databases. The OLTP is the most like complicated one. A lot of tables and

transactions and so on. The data warehouse it is like really nice one in order to do data analyzes and stuff. The

lightweight it is the simplest one. So let's go for example and get the data warehouse. So click on that and now as

you can see the extension of this file isbak and now I'm going to show you the second way on how to create databases in

SQL server. So now all what you have to do is to go to the following path. It really depends where you have installed

the SQL server. So for me I have installed it in the program files Microsoft SQL Server MSSQL SQL Express

then MSSQL backup. You have to go there. So here what you can do you can place all the files with the extension bak.

For example, the adventure works that we just installed. This is a backup file for the database and we want to go and

restore it and with that you are creating like a database. So this is the second method on how to create databases

in SQL server by restoring the database. If for some reason the script didn't work for you. Now let me show you

quickly how we can do that. Let's go back to our studio. Right click on the database and then here we have an option

called restore database. Click on that. And now here we have two options under the source database and device. The

default going to be database but we have to switch to a device because we want to import it from files. And then we go to

these three dots. Click on that. And now we have to go to the option add. And now it's going to take you to the place

where the SQL server creates backups. So here we can find our files and what we want you to create is the adventure

works. Select that. Then okay, one more okay and one final okay. So now the database will be restored and it is

successfully. So now on the left side we can see our third database. If you don't see it go and refresh of course and here

you will find a lot of tables in the adventure works. And as usual we can go and explore the data by selecting top

thousand rows. So my friends now you have three databases but of course our focus is only the first two that we have

done my database and sales DB. And with that you have learned two ways on how to import databases into SQL server. So

with that my friends we have prepared everything. We have the SQL Server Express running on your local PC. We

have the studio the clients where we're going to use it in order to interact with the database and we have created

our two databases that we will be using in order to practice SQL. So we are ready. All right my friends. So with

that we are done with the first chapter. We have our introduction to SQL and now we're going to start learning the first

thing in SQL and that is how to query our data. So let's go and start with that.

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 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

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 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 start with two clauses

that makes the simplest query in SQL. the select and from. So let's start with that. 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 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 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 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 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.

So I'm telling SQL just use my database like this and with that SQL 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 if you see it is green that means it is a comments. 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 start 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 slash star 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 that you

are telling SQL I'm done with my 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 the 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 query 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 for SQL from where it's 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 ask question for the database using SQL query and the database should 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 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. 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 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's going to stay as it is. So from a 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? 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 can 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 a star in order to

see the whole table first from the table customers. So it's exactly like before. Let's go and 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. 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 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. 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 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 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 and after that SQL going to go and execute

the wear clause. So let's say that the condition should be higher than 500. And now what going to 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 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 work 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 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 don't want to see all the customers. I want to see only the customers thus 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 the condition. Their

scores are not equal to zero. Only one. The last customer his score is zero. So this customer does 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 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 clause after the from and then write a condition after that. Now let's have

another task like for example it says 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

clause and after that we need a column. 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 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 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 is very simple. All 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 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 the 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 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 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 SQL going to go and sort the result. So the order by going to be executed and SQL

going to see okay I'm going to sort it by the score and using the sending mechanism and still 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 row at the result 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 scale in order to practice. All right. So now we have the

firming 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 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 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 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 when you can use ascending. Let's go and execute

it. And that's it. As you can see now we have the lowest score then 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 column country and

the mechanism going to be ascending. So from the lowest to the highest. Now if you do that 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. It 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 come from country Germany. You can see the scores are sorted in ascending

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 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 the sorting going to be from the highest

to the lowest. So it's 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 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 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 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 it's 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 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. It's called has first to sort the scores. So with that you will get the

900 first, right? And then the countries. And 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 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 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 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 is

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 it's still 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 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 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 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 900 plus zero 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 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 are 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 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 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 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 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 I the non-aggregated columns that you are adding in the select must be as well

mentioned in the group I. 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 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 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 the 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 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

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 going to 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 SSQL has no problem with 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 eye. 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? 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 I. 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 I 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 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 by 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 is going to go and aggregate the data by the country. So it's like before 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

SQL going to go and check the new results after the aggregation. So in Germany we have the total sales of 850.

So it meets the condition and it going to stay in the results. The same thing for USA it is higher as well than 900s

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 is what can 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 clouds to filter the data so why we have

in SQL another cloud how 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 I and now let's go and add the wear. Usually the wear comes between the from

and the group I 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 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 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 it's still going to go and group the data by the country. So

now we have fewer data to be 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 going to execute the having and here SQL 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 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 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. 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. 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 a wear condition. Now about the second one it says countries with an average score greater than 430. That

means we want to filter the data after aggregating the 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 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 I

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

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

that we have the last part as well. Let's go and execute 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. 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 SQL

going to do of course is to get the data from the database using the from. And now the second step is the select. So

SQL going to execute it and going to select only one column the country. All other columns going to be excluded and

removed from the results. And now SQL 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 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 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. 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 duplicates or the repetition inside our

data. So each value is unique. Now let's go back to SQL. Okay that 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 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 executed, we will get

the same results because the 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's 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 distincts.

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 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 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 top. So 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. 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 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 and 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 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