Full transcript of “SQL Full Course for Beginners (30 Hours) – From Zero to Hero”. Click a timestamp to open the video at that moment. Need another video? Use the YouTube transcript generator.
0:00 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
0:46 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
1:32 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
2:18 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
3:05 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
3:51 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
4:36 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
5:21 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
6:08 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
6:53 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
7:42 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
8:27 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
9:13 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
9:58 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
10:44 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
11:30 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
12:15 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
13:01 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
13:47 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,
14:34 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
15:20 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
16:06 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,
16:52 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
17:37 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
18:24 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
19:11 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
19:58 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
20:43 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
21:29 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
22:16 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
23:02 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
23:48 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
24:34 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
25:22 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
26:09 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
26:55 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
27:41 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
28:26 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
29:12 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
29:59 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
30:46 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
31:32 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
32:18 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
33:03 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
33:49 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
34:36 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
35:23 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
36:09 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
36:56 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.
37:42 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
38:29 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
39:15 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
40:01 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
40:47 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
41:33 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
42:18 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
43:05 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
43:53 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
44:39 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
45:25 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
46:12 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
46:57 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
47:42 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
48:28 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
49:13 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?
49:59 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
50:45 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
51:32 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.
52:18 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.
53:04 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
53:50 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
54:36 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
55:22 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
56:07 second column you are refining your sorting and as well my friends the order is very important. So this is how you
…
