SQL Tutorial for Beginners [Ultimate Full Course] - From Zero to HERO

Hi, and welcome to this very unique SQL course. I'm Bara Zalkini, IT solution architect with over a decade of experience in real IT projects. I will put everything that I know about SQL into a 4-hour tutorial. In this course, you will learn everything that you need about one of the most in-demand skills: SQL, from basics to advanced topics. So, by the end of the course, you will be able to write SQL queries very easily. We can work with one of the most popular versions of SQL, MySQL, but the syntaxes and the skills that you're gonna learn from this course can be used in any other databases or applications using SQL.

I designed this course to take you from zero to hero. So, if you are a beginner, don't worry about it. I'm gonna explain everything from the scratch, step by step. So now, if you ask me what makes your course very special compared to the other courses, in this course you will not only learn how to write SQL queries, but also you will learn the SQL concepts behind them, and especially how the SQL processes the queries behind the scene. And this can help you to understand why we write SQL queries, and it's gonna make you more creative with your query statements. In this course, you will have tons of best practices and tips and tricks that I collected in the last years, and we will have many SQL tasks, and then we are going to solve them together, step by step. And I will be providing you with a lot of free materials. All the content of this course is also available on my website, datawidbara.com. You can use it later as a reference. I will provide you as well with an SQL sheet where you can find all the tasks and the SQL syntaxes, so you don't have to memorize all of them. I've also prepared for this course a database where we can use it in all our tasks and examples during the tutorials. And if you are new to my channel, make sure to subscribe as I upload videos about SQL and Tableau. So now, let's jump in and get started. [Music]

All right, everyone. So now I would like to show you the road map of the entire SQL course for beginners. The SQL course is divided into nine chapters. First, we're gonna start with the basics where you can learn the basic concepts about SQL, like the concept of databases, SQL tables concept, the basic SQL commands, and the main elements of the SQL statements. In the next chapter, we're gonna start preparing your environment so you can practice with me. I will walk you through the steps of downloading and installing MySQL. Then we will take a quick tour of the interface, and at the end we're gonna install the database of our course. And then finally, you will begin to use SQL syntax to query the database and the tables that you just created in the previous section using the select statements. After that, you will learn how to filter your data using the WHERE clause and learn some SQL operators. In the next chapter, we're gonna step up the level where we're going to learn how to combine our SQL tables using joins and UNION. After that, we're going to learn many important SQL functions, like aggregations and string functions. Then, in the next chapter, we can raise the level again by learning advanced topics in SQL, like GROUP BY, HAVING, and subqueries. Then we're gonna learn how to modify our data inside our tables using INSERT, UPDATE, and DELETE. And in the last chapter of this course, we will learn how to define our data using SQL, like CREATE, ALTER, and DROP tables. So those are all the topics that we're gonna cover in this SQL course.

All right, everyone. So with this, I could say let's jump in and start our SQL course. All right, so we're gonna start with the first chapter here. We're gonna talk about the SQL Basics and Concepts, and we're gonna start now with an introduction to SQL. [Music]

All right, so we will start with the SQL Basics, the terms that you'll be hearing during the tutorials. For example, what is data? So data are facts or statistics that are stored somewhere or moving around the network. Generally, they are like raw materials. For example, if you order some things online, a lot of data will be generated; for example, the customer ID, the order number, order dates, shipping dates, and so on. Another term that we have is information. So the data that we have, we could like reprocess it, structure it, or translate it to a new form called information, which it has more logical meaning, and we could use it in the analysis. For example, if we aggregate the order dates over the years, we could see how the company is growing over the years. That means we converted the raw data into meaningful information.

All right, so what are databases? A shortcut: DB. By definition, a database is a collection of structured and related data that are stored or organized in a way that the data are easily to be accessed and managed. In shortcuts, it is one way to store your data. You deal with databases every day and everywhere. So, for example, if you order some things online, even if you store your photo at your smartphone gallery, this gallery is a database. We have around many different databases. The most famous one is that the one that we're gonna learn is the relational SQL databases. Other ones are NoSQL databases. We have distributed databases, cloud databases, data warehouses, and so on. So now I'm gonna go and explain SQL and NoSQL databases because they are the most famous ones. So SQL or relational databases, they store the data inside tables. Tables are like containers with the fixed structure, and usually they are related to each others using relationships. That's why we have the name of relational databases. So if your data are very structured and easy to understand, it would be good if you use SQL databases to store your data. In the other hand, we have NoSQL databases, or not only SQL databases, and here you have different types of options how you're going to store your data. For example, you have the key-value methods where you're gonna define the keys and the value inside them. You have the graph store, you have the column store, which is great for Big Data. Some tools like Tableau for data visualization, they use this method to store the data because it gives great performance and analyzes. And as well you have the document. So if you are in a project where the requirements are changing a lot, so or that are hard to understand, they don't have like clear structures and so on, it would be good if you use the NoSQL databases to store your data, to use one of those methods. But in many companies, a lot of like projects are storing the data inside SQL databases because they are easy to understand and very widely used. And in our tutorials, we will be focusing on these types of databases, SQL relational databases.

Now, in order to manage all those databases, we use a software called Database Management System, or DBMS. It is like an application with an interface where you can log in and start doing something inside your database. You can do stuff like creating new tables or changing your data, querying your data, and so on. And currently, we have almost 380 different DBMS's, and according to the survey of Stack Overflow this year, I'm going to leave the link in the description. You can see here a ranking of the top and most used databases between developers. So you can see here MySQL is number one, then PostgreSQL, and so on. We have another ranking website; it's called DB-Engines ranking. If we go there, you find the list or rank of the top used or most popular DBMS's in the world. They are using different criteria in order to calculate that, but you can see here MySQL is in the top three in the list. In our tutorials, we will be using MySQL, and we will learn it, which is the most famous and commonly used databases these days.

Now, finally, what is SQL? It stands for Structured Query Language. So, by definition, SQL is the query language that we use in order to retrieve, manage, manipulate, store data in databases. In short, SQL is the language that you need to master in order to talk to databases. So now, in the internet, there is a never-ending battle in how to pronounce it. Some developers call it sequels, and other call it, like me, SQL. It's really depends on the country that you come from or the project that you are working in. In my project, everyone called it SQL, so it's really up to you which one that you're gonna use.

All right, you might ask me now, Baram, how really SQL works. Let's check this. On the right side, we have our relational database where you stored your data inside tables, and here we have our DBMS managing our database. So the first thing that you're gonna do is to log into the DBMS in order to interact with it, or if you are building an application, you need to connect them to the DBMS. After that, you start writing some SQL statements, some instructions, and then hit the button execute. After that, the DBMS will start processing and do some magic to it and send it to the database. Once the database gets such a query, it starts performing some operations or searching for the data that you asked for. Once it's ready, the database will answer to the DBMS with the result that you wanted.

All right, guys. So that was a quick introduction to SQL. Next, we're gonna start talking about why SQL is important and why you should learn it. [Music]

I just want to quickly motivate why you should still learn SQL. Here are some facts. SQL is old; SQL is 47 years old, that is 14 years older than me. You can do the math. So SQL is the Grandaddy of the programming world. There are over 700 computer languages that you could learn. You might as well heard about that NoSQL movement where everybody say that NoSQL gonna kill the SQL databases. So you might ask now, why we still use SQL? Why should I learn SQL? Why SQL didn't die like many other languages did, like BASIC or Pascal? Well, the quick answer for that is SQL still works; it does the jobs, and you cannot ask more than that. Here are my four reasons why you should still learn SQL. Reason number one: SQL is the most used technology in the entire tech industry. If we check now with the survey of Stack Overflow this year, I will leave the link in the description. In this chart, we can see the most used technologies, and you can see here SQL is ranked as the first commonly used technology among all developers. That means SQL still in trend. Reason number two is SQL in high demand. Most of the companies in all industries, they use some kind of SQL databases to store their data. That means they always gonna need someone with SQL skills in order to create, manage, analyze, and understand their data. So now let's do a quick check in the job platform like Indeed and search for the keyword SQL. So SQL find jobs. Let's see the results. So you can see here over 170,000 jobs are looking for SQL Developer or someone with SQL skills. That means SQL skills are really in high demand, and that's because data analyze is becoming a very important part in many jobs. The third reason is SQL is almost everywhere. If you are in projects and you are working with data, for example, data mining, data engineering, data science, or data visualizations, you will be end up using a lot of Big Data tools and programming languages, and most of them they tend to offer you places to write some kind of SQL statements. For example, if you are using Tableau, it is a very famous data visualization tool; there is places where you need to write some SQL statement in order to prepare the data. Or if you are a project where you are doing like data streaming using Kafka, for example, there you will find a lot of functions or models where you have to write some SQL statements. They do that to make stuff easier. So that means with the time you will see that almost in each tool you can use SQL statements and SQL skills. So now for the last reason, unlike other languages, SQL is simple and easy. It is easy to learn, easy to write, easy to read because the SQL syntaxes are based in very common, easy English words; for example, SELECT, FROM, WHERE, tables, and so on. And SQL managed perfectly to hide all the complicated processes from you. So that's why a lot of people tends to learn SQL because it's really easy.

All right, so now let's sum up. SQL has the best combinations; SQL is very high in demand, and as well it is easy to learn, which makes learning SQL is always a smart move and one of the impactful career improvements any IT developer can unlock. All right, so that was my top reasons why you should learn SQL. Next, we're gonna talk about the database Concepts. [Music]

All right, so now let's understand how SQL databases are organized. It's very important to understand that because once you start writing SQL statements or SQL queries, it's very important to understand the terms that are commonly used in databases, or how to browse your database, or how to find your data. If you learned that at the start, it's going to make the learning process of writing your SQL statements much faster. Okay, so now just to make it easier to understand, think of the following analogy: a database is like your city library. We have in Suceava a very beautiful library; it's really amazing. I spent a lot of time there; I just like it. So yeah, a database is like libraries, and libraries are divided usually into categories, like science fictions, romance, history, sport, and so on. So category is going to help you to find quickly the materials that you are searching for. So categories are like grouping up those similar books underneath the same category. We have the same concept as well in databases, and we call it schemas or schemata; pick the one that you likes. And of course, in libraries we have as well books. We have the similar stuff in databases, and we call it tables, where it contains the actual data. So as you saw in the examples, databases are organized in like hierarchies. Let's see MySQL how they organize the data because not all databases are following the same concepts on how to organize the data. So at the start with MySQL, we have the database server; it's like a machine containing software and hardware's in order to run our DBMS's and databases. Usually, database server is like a high-end computer with a lot of CPUs and RAMs, but in our tutorials we will install a database server at our local computer or laptop, and we call it a local server. Inside the server, you can create then multiple databases. In MySQL, databases and schemas they are synonyms. So a schema, by definition, it is like a logical containers that contains similar tables, with that you get a lot of benefits. For example, imagine you have a big database with a lot of tables; grouping up those similar tables underneath schemas it's gonna make it easier for you to manage the users, for examples, or to manage the tables, reduce complexity, and as well if you have like two tables with the same names, you could like store them in different schemas. So it's a really nice way how to organize a database. Inside the schema, then we will have different tables. Tables are the most important object on the whole database because it is the place where you can store your data. Without tables, we have no database. And inside the tables you will have at least then one column or different columns. I will go in details explaining those tables as a next step.

Okay, so now I just want to show you quickly how other databases like Microsoft SQL Server or PostgreSQL how they organize the data compared to MySQL. So as you can see here, the key difference is that they they split databases from schemas. So a database here it is like the main container, a discrete unit on its own where you can have logs, jobs, schemas, data, and you can do backups. Schemas over here it is like a folder inside the database; it's like a logical layer containing different tables. In my opinion, MySQL is little bits like misleading or confusing developers. For example, if you go and create a schema, the DBMS of MySQL will be creating a database, so I find it at the starts little bit confusing.

All right, so that was it about the database Concepts. Next, we're gonna start talking about the SQL table Concepts. [Music]

All right, so now let's talk about SQL tables because they are really important in the databases, and understanding them it's going to help you to write better SQL statements. The problem with that, we have around 380 different databases, and they use different terms in the documentations. Another aspect is that we use different terms in different areas or forks. For example, if you are a database developer, you will start using terms like tables, columns, rows, but if you are in the university, you will hear about relations, tuples, and as a data modeler you will start saying entity and attributes. That's why I would like to give you like a short overview of those terms to make it simpler. All right, so now we have here a very simple example of SQL tables. In our tutorial database, we have one table called customers. These tables contains all the data about our customers. Another name that we have for tables is objects, entity, and relation. Okay. Next, we have columns. Columns are the vertical group of cells that are describing one type of information. In our example, we have four columns: customer ID, first name, last name, and Country. Each column has two informations: the column name, for example here we have the first name, and the values inside it, like Maria, John, and so on. All right, so next we have rows. Rows are the horizontal group of cells that are describing one individual topic, and they are related to each other as well. So, for example, here we have the customer ID 2 belongs to John, and John lives in the USA. In this table, we have five rows. Another name for rows are records and tuples. Now, the intersections between columns and rows, this piece of data, we call it a cell. Another names we have: data items, column value. It is one single value. Another example is number four or Germany or George and so on. The last component we have is the primary key. The primary key is a column or set of columns that can uniquely identify each row in the table, and they could be used as a link within other tables. In our example, we have the customer ID, and this is our primary key. You can see it has unique value for each customer. Another name for it: key fields. All right, that's what the main component of the SQL tables.

All right, so that was the concept and the main components of SQL tables, and next we're gonna start talking about the different types of SQL commands. [Music]

All right, so now let's talk about SQL commands. In SQL, we have around 12 main commands and 900 different keywords. Of course, I will not be explaining all of them. Instead of that, in our tutorials, I will be focusing on the most used SQL commands and statements that I use in my projects in the last 10 years. To make our life easier, SQL commands are divided into different groups depending on their purposes. All right, let's start with the first group: Data Definition Language, DDL. As the name suggests, here you will find all the commands that allow you to define your database, like creating tables, dropping columns, changing tables, anything that's gonna change the structure of your database. Underneath this group, you can find commands like CREATE, which helps you to create anything new in the database, like create new table, create new views, store procedures, and so on. One more we have here, the DROP commands that allow you to delete one object from your database. And the last one, ALTER, it helps you to edit the structure of your database, like altering one table to change a column or to add a new column.

Okay, so now to the second group, we have Data Query Language, DQL. It contains only one command, and that's enough; it's called the SELECT command. SELECT helps you to retrieve your data from your database. SELECT is the most important command that we have in SQL, and the one that you need to master in order to be good in SQL. In my tutorials, I will be explaining everything about the SQL SELECT statements because if you start working with SQL, you will be end up writing tons of SELECT statements, so don't worry about it.

All right, let's go now to the next group. We have Data Manipulation Language, DML. DML contains all the SQL commands that you could use in order to manipulate your data inside your database. So we have commands like INSERT in order to insert new data inside your tables, or we have DELETE to delete some data from your tables, or UPDATE to update the content of existing data inside tables. So as you see, it is really easy; the names tells everything.

All right, so now we have two groups of commands that is really more for

SQL database administrators. So the next one we have is Data Control Language (DCL). DCL contains SQL commands that allow you to give access to specific users to your database, or to tables or schemas, and so on. So here we have two commands: Grants. You could use Grants to give someone access to your objects in databases, or Revoke to remove such access from a specific user. Okay, so now to the last group that we have: the Transaction Control Language (TCL). In TCL, you will find the SQL commands that are going to help you to manage the database transactions in order to maintain the integrity of your data. So here we have commands like Commit, in order to save the changes in your database; Rollback, to restore the database to the last commit or to the last save point; if you have some errors, you could use that save point. You can define save points in the transactions, which you could use later to roll back. All right.

So now about those names: DDL, DCL, TCL, and so on. You don't have to memorize them. Maybe only the important one is DDL, sometimes here in the project. So if someone says, "I will be creating some DDL scripts," that means he or she is going to create SQL statements to change the structure of the database, like creating a new table or dropping something. All right.

So in our SQL tutorials, we will be focusing on the first three groups of the SQL commands. We will start with the most famous one: the SQL select statements. And after that, we're going to deal with all those DDL scripts. And finally, I'm going to explain insert, delete, and updates. All right, so that was the main types of SQL commands. Next, you will learn the basic elements of SQL statements. [Music]

Right, so now let's start with the basics. I want you to understand, at the start, the basic elements inside each SQL statement. We have over here very simple select statements. Don't worry about the content; I will be explaining that later. So the whole text that's going to be sent to the database, we're going to call it SQL statements, or sometimes we call it a query if it is a select statement. So it doesn't matter whether you are retrieving data from the database or creating a new table or updating contents; we can always call it an SQL statement. Okay.

So now let's talk about the components inside our SQL statement. Let's start with the first line over here, the green one. We call it an SQL comment. In the SQL command, you could write anything you want, and once you hit execute for the whole SQL statement, the database is just going to ignore it; that means nothing is going to happen. There are some benefits of SQL comments; we could use it to describe our code, so later it's going to be easier to read it. And because the database is going to ignore it and nothing is going to happen, we use it to deactivate the part of our code. For example, if I don't want to use such a filter over here, I could make it as a comment, and the database will not execute it. Okay.

So now SQL statements are usually divided into different parts; we call them clauses. Each part is responsible for a specific action. In our example over here, we have three clauses: select, from, and where clauses, and each of them has its own unique function. For example, in select, you're going to list the names of the columns that you want; in from, you're going to call the tables; where you're going to define the filters. So as you can see, SQL is really nicely split into functions, and which makes it really easy to read and easy to write, and make the whole SQL language a very easy one. Okay.

So next, as you might already notice, we have those blue words; we call them keywords. In our example, we have four keywords: select, from, where; and those keywords are predefined and reserved in SQL; that means you cannot use them as a table name or column name. In MySQL, we have over 900 keywords. We will not go through all of them; I'm just going to focus in the tutorials on the most used keywords. In the link in the description, you will see a list of all keywords that we have in MySQL. All right. Okay.

So now let's check the next element: we have identifiers. Identifiers are any name that you give to any object in your database; for example, a table name, a column name, even the database name itself; it is an identifier. In our example here, we have four column names: first name, last name, team, country, and score; and we have as well here a table name called customers. All of those things, they are identifiers. All right.

So now to the last element that we have; we call them operators in SQL. There are many different operators; they have different shapes and forms. For example, they could be symbols, like what we have here: equals, smaller; or they could be keywords, for example, and; we call it as well an operator. So as I said, in SQL there are different SQL operators, like there are arithmetic operators: plus and minus; there are comparison operators, as our example: equal and smaller, and so on. All right, so that was the basic elements inside SQL statements.

So to wrap up over here, we have the whole text; we call it an SQL statement. The green ones, we call them comments. In SQL, we have different clauses, different parts. The blue ones, they are the keywords. We have our names that we give in the database; we call them identifiers; and at the end, we have operators in our statements. All right, everyone. So with that, we have finished the first chapter of the SQL course. We have now a lot of knowledge about the SQL basics and concepts. In the next chapter, we will start preparing your environment so we can start practicing SQL, and we will start by downloading and installing MySQL. [Music]

Now, if you don't have MySQL already installed, then you can follow me; I'm going to show you step by step how we're going to download and install MySQL in Windows. This is so important so you can practice and run the tutorials at your computer. Let's start by downloading MySQL. Okay, let's go to our browser. We will go to the official website of MySQL, mysql.com. Uh, then, you know, you will find your downloads; click on that. Then scroll down until you find MySQL Community Downloads; click on it. You will have a bunch of installers; the one that we need is MySQL Installer for Windows. Let's go there. Here you have two options: a smaller one and a bigger one. So the small one, it's like it's going to download some packages as you install MySQL, or you can download the whole package at the start. So I recommend you to go with the bigger one, so we have everything downloaded at the start. Click on download. At this page, it asks you to log in, to create a new account; it's not necessary for the tutorial, so you can skip that. So I'm going to go with "No thanks, just start my download." So it's going to now start downloading the installer, but because I already done it, I don't want to waste the time now, so I'm going to go to downloads and I'm going to start the installation.

Okay, let's start now the installer. I'm going to click on it, press Yes, and now we are at the first step of the installations. Before we proceed, I'm going to tell you there will be a lot of, like, steps: 30, I think. We're just going to press Start, Next, Finished, Yes, and so on. We will not change a lot of configurations; maybe we're going to put some password, but that's it. So it's really easy. Let's start with the first step. It's going to tell us here, for example, a developer server, client, and so on. We will stay with the developer default, so click Next. After that, it's going to check the path; we're going to stay with the defaults, press Next. Yes, I'm sure. So here it's going to check the requirements; they will do a lot of steps like this, checking the requirements, so we stay with the defaults, press Yes, and now it's going to show you all the packages that's going to be installed. So we will not change anything; let's everything to be downloaded. So now I'm going to click Execute, and it's going to start installing all those components at my PC, one by one. All right.

So now we have all the products installed; we will click on Next. Then we have some product configurations; just click Next. And now you can see about the networking. Well, the most important thing here is to know that we have the following port number for our local database, but we will not change anything; we're going to leave it like this. Then click Next. We're going to stay with the recommended settings for the authentications; click Next. And now we have to set up, finally, the password for our root user, or we call it an admin user for the database. So this is very important to memorize or write it down somewhere. So now I'm going to give for our admin user the following password. Oh, it's weak! So next, we will stay with the recommended stuff; not going to change anything, and we're going to click now Execute to apply our configurations. Okay, after all configurations are completed, we're going to click on Finish. After that, there will be more configurations. Okay, Next; don't change anything; we're going to stay with those configurations; we're going to click on Finish. After that, some more configurations. Okay, Finish. Okay, now we're going to test our connection to the database server. You see here the username is root, and we're going to type here the password that we gave previously for the admin user. So I'm going to give here the password and click Check. So if you get it like here: Connection succeeded, that means we are successfully connected to our SQL database and everything is fine. So let's click Next, Apply Configurations. Okay, Execute. So everything is green; click Finish. We have more configurations; guess what? Next. All right, installation completed. So let's click now one more Finish. After the installation is completed, it's going to start, like, MySQL Workbench for you, and as well another shell script. Let's check here. So we don't need this one; you could close it. We will stay with the MySQL Workbench; this is exactly what we need for the tutorials. So you can see over here: Local instance MySQL80; this is your local database at your machine. So we're going to log in and try to see whether everything is fine. So you see here the admin user root, and we type the password we gave in the installation; this is mine. Click OK, and now I am inside my database. If you are exactly at this step, that means you downloaded, installed, and logged into your database successfully, so congrats.

All right, so with that we have downloaded and installed MySQL successfully on our system. Next, I'm going to take you on a very quick tour in the interface of MySQL. [Music]

I would like to give you now a real quick overview of the interface of MySQL Workbench, because I remember when I first started using such database applications, it was a little bit confusing, overwhelming, having all those panels, options, and toolbars, but actually it was not that hard. I'm not going to go and explain every single detail, but instead I will give you a general overview of the interface. If you need more details about the tool, visit the MySQL manual; I will leave the link in the description. So now let's start explaining the main sections in MySQL Workbench.

All right, let's start on the left side. We have a very important section called Navigator, and in the Navigator you can see two tabs: Schemas and Administration. As a default, you will be landed in the schema. So you can see in the schema, it allows you to navigate or browse through your database objects. For example, I can see here I have three databases; as default, we got it from the installation. So if I want to see inside this database called world, I'm going to double click on it, and I'm going to see the tables, views, stored procedures, and functions. So I can browse furthermore, and I want to see what is inside the tables; we will see that we have three tables: City, Country, and CountryLanguage. So I can see, okay, I have three tables in the database. Let's see now which columns contain those tables. I can click on the City and expand, and I will see, okay, I have the following columns: ID, Name, and so on. So with the schema Navigator, you can navigate through your database to understand the contents of it. Let's go now to the second tab: Administration. Here you will find a lot of info, a lot of tools to manage your SQL server. For example, you can check the server status; double click on it, you'll see on the right side here: Server status is running; or you can manage the connections, manage the users, and so on. It is interesting if you're going to be, like, a database administrator, to understand all those things. We are now learning about SQL, and it is a different topic now, so let's go back to the schema where we can browse our databases. All right, let's close this one over here; I don't need it; go away. Right. So at next, we have a toolbar; we have two toolbars. The first one called Main Toolbar; it is like the most frequently used functions in SQL; for example, to create a new SQL statement, or to create a new schema or database, create a new table, new view, new stored procedures, and so on. So it gives you like a quick access to create new stuff in the Main Toolbar. The second toolbar, it is over here; it is the Query Toolbar; it contains all the actions that are related to the query that you are writing in the query editor, and the most important one is the execution. So once you write your SQL statement over here, you click on execution, and it will be run on the database. You have some other options, for example, to save the SQL statements or to open one that's already saved, and so on. All right.

Next, we have very important sections; it's called the Query Editor. Here we will write our SQL statements and queries, and so on. It is our main place where we will work. For example, I'm going to write the following statement: select star from City. Don't worry about the syntax; I will be explaining everything about the select statements in the next tutorials. So now let's hit the run or execute. After we run the query, you will see that we have here a new section; it's called the Result Grids. Here you will find the results, the data that are returned from the database after we executed the query or the select statements, and the data is presented as a table form. Underneath that, you will find another section that's called the Output. So let me just make it bigger a little bit. So in this section, you will find a lot of information; it is like logs. So you can see the execution time, how long it took the server to execute your query; you can see as well whether it was successful or you have some problems and the syntax, or you have some errors, or you can see it over here, and you can see the error message as well, and so on. Okay. Now if you go to the right side over here, we will find another section; it's called SQL Additions. It is like a help tool from MySQL that gives you descriptions for the SQL statement syntax, the usage, documentations, and so on. I usually hide it to save some space in the application by clicking over here. Uh, it's really up to you; it is personal preference. All right, that's what the main sections of MySQL Workbench are, and really need it in the SQL tutorials. So I hope it helps. Don't worry about it; you need some more time using such applications in order to understand them and to navigate through them, and it will be less overwhelming. All right. So with that, we have learned how to navigate through the MySQL interface, and next we are going to install the database for practicing. [Music]

All right, so so far we have installed the MySQL application locally at our computer. As the next step, we're going to create a tutorial database for this SQL series. I have prepared a special database just for practicing and tutorial purposes. In this tutorial database, we will have three tables with few data, so all our next tutorials will be based on this tutorial database. What we're going to do, I'm going to show you, like, some tasks, and we're going to try to solve those tasks using SQL codes on top of our tutorial database. So as a next, I'm going to show you step by step how to create our tutorial database. Okay.

So now the first step is that we're going to go to the video description, and there you will find the link to my website, and with that you will find our SQL tutorial database. So it will look something like this. So this is one big chord in SQL, around 53 rows. So you don't have to understand all those things at the start, but after you finish the series you will understand what we have done over here. So you will understand how to create a new database, tables, how to insert new data, and so on. So what we're going to do is that's now is just to copy this script. So in order to do that, you can go over here and click copy, or just go and select everything and copy it. So once we copied our tutorial database script, we're going to go to our MySQL database and run that. All right.

Step number two: go back to MySQL Workbench, and there we're going to execute our code. So we're going to open a new tab, SQL Editor, and here we're going to paste our code. So it is around 53 rows in the code, and we're going to hit Run. So once we run, we have to validate whether everything went perfectly. So if you check the left side over here, you will find, okay, we have three databases. So where is my tutorial database we just installed? In order to see that, you're going to hit on Refresh. So once you hit Refresh, you will see, okay, we have now our tutorial database: dbSQLtutorial. So in order now to browse our new database, we're going to do the following: just double click on it, and then go to the tables, and there you will find our three tables. So there we have the table customers, employees, and orders. Okay.

So now let's check whether we have all the data in our tutorial database. In order to do that, we can open a new tab; just follow me with those steps. All the commands, you can explain them later in the tutorials. So I'm just going to retrieve all the information from each table to check, do we have all the data? So select star from customers. So this guy retrieves the data from the table customers, and as you can see, we have here a table called customers with five customers. So we have Maria, John, George, Martin, and Peter, and in this table we are storing the general information about each customer, like the first name, the last name, country, and score. Okay. So now let's check one more table; let's check the orders. So instead of customers, I'm going to replace it with orders and click Execute. So with that, we're going to see that we have a table orders that stores all the orders that are placed for our customers. So we can see over here we have the customer ID, and we have the order ID, and the date when the order is placed, and the quantity. So if you want to see the information of the orders, we're going to check the table orders. If we're going to see that information about the customers, we're going to check the table customers, and so on. So if you have done all these three steps and you have checked the data, that means you have now our tutorial database installed at your local machine, and we could proceed with our tutorials. All right.

So with that, we have a database with data, and before we start writing our SQL code, we have to learn how to style it.

Foreign... As soon as you start learning how to code in SQL, I really have to mention this: when you start learning any new programming language, it's really not enough to learn how to code it, but also you need to learn many other things, for example, how to solve a task with few lines without making things complicated, or how to write code that delivers good performance, and finally, and the most important, how to write code that looks good, that is easy for you to read and for others. So if you are working on a project, you will notice that developers always have different opinions about how to style code, but all of them will agree that the code should be readable and following some styling guide. Okay.

You might ask me now, Bara, do I really need to style my code? Is it not enough that my code is working correctly? Well, no; there are two reasons for that. If you are working on a team or project, sometimes your code should be reviewed from others, and if your code is hard to read, you will give them a hard time reading your code and even end up that they're going to rewrite your code in order to read it. Another reason is that if you find out there are some errors or some problems in your code, you will have a hard time searching for the error to find out in which line you have the problem. So especially if you are...

Beginner in SQL or in any programming language, at the start, you will not pay attention to the styling guides. You will just make sure that you learn the codes and the statements. So my advice here: don't develop any bad habits at the start, because later it's going to be really hard to break them. Alright guys and girls, I want to share with you now my three golden rules that I always follow when I start writing SQL codes. Let's check this example over here; it's a very simple statement query, a select statement. Where at the start, to be honest, I had a really hard time understanding what is going on, so let's try to make it beautiful following the three rules.

Rule number one: Always add new lines for keywords and as well for each column. So let's start doing that. We have here the select statement, so let's add the new lines for each column. I'm going to do that, so all of those stuffs are new columns or new lines for each column, and as well from; we have it here as a new line, so that's okay. Join; we could add a new line for it, on as well. So just add the new lines for each keyword as well here for the end. So as you can see, it already looks better. I added new lines for each keyword and for each column.

Rule number two: Let's make all those keywords uppercase. So let's do that. Select is lowercase; let's make it uppercase. The same goes for FROM, JOIN. Let's make everything uppercase. Why do we do that? It's because it's easier to read what is a keyword and what is other stuff like identifiers, operators, and so on. So it's much easier to read.

So in rule number three, is that we're going to go and add some white spaces around. So let's check that, and in the WHERE statements, we could like split this condition with white spaces. It's just easier to read if you add white spaces as well here on the condition of the JOIN; we could add white spaces. So as you can see, we can read it better, as everything like stuck together. So now as well for the columns, I always add a tab for it. So now that's it. Now I have applied my three rules, and you could see it's really much easier to read. We can see here, okay, SELECT, FROM, JOIN, WHERE, and so on. I could read it really easier compared to the first one. Alright, so now let's look at both of the scripts side by side. Can you see the differences? Which one is more readable? It's straightforward that the script with the style has a proper format that helps you and others to read it easily and as well to find errors and problems if you have any.

Alright guys, so with that, we have now MySQL server database and data up and running on our PC, so everything is ready to start practicing SQL. And now in the next chapter, you will finally begin to use SQL syntax to query the database and tables using the very famous SELECT statement.

[Music]

So now we're going to focus and start on the SELECT command. So here's going to be our focus; we're going to learn how to query our data, and this is going to take almost eighty percent of our tutorials because SQL is all about how to query our data. Then other on our dots, we're going to talk about the data manipulations and data definitions at the end. So now let's start with the SELECT command. Alright, so before we start writing our first SELECT statements, I want to mention the following: and that's in a SELECT statement, there is a lot of clauses. This is not really bad; this gives like SQL dynamic and easy way to use SQL, and each of those clauses has his own definition and own function, which makes it really easy to use. So we have the SELECTS in order to select our columns, FROM to select the tables that we need, JOINS in order to connect two tables together, WHERE in order to filter our data, GROUP BY to aggregate the data, HAVING is another way to filter our data, ORDER BY is to list our results, and LIMIT is just to limit our results. So those clauses, don't worry about them; I'm going to explain all of them step by step with examples and tasks and everything, and at the end you can understand all of them.

One more very important aspect to understand in SQL statements is that the order of those clauses are very, very important. So, for example, I cannot use at the start FROM, then we write down the SELECT. So this order is very strict, and if you switch between them, you will get immediately an error in SQL. So that means pay attention to the order of those clauses; don't miss between them. You need to follow those rules in order to get like your query executed in SQL without any rows.

Alright, so now with the first thing that we need to learn is how to fetch our data from the database, how to retrieve all those records and rows from our tables, and to do so we use the most fundamental SQL statements; we call it SELECT statements or sometimes SELECT query. So now in order to understand all those SQL statements like SELECT, WHERE, JOIN, FROM, I will be giving you like one task, then we're going to try to figure out together how we're going to solve it using our tutorial database. In our tutorial database, we have two tables: customers and orders. In the customers' tables, we have five customers, and in the orders we have four orders.

Alright, so let's start with the first task: Retrieve all data and columns from customers. So that means our focus here on the customer's table, and all data that means all rows, so we need everything, all rows and all columns. Okay, so now before we start writing our first query, we need to make sure that we are selecting the right database. As you installed MySQL Workbench, you will be getting some default databases, and after that we installed our database for the tutorials. So to make sure that we are selecting the right one that we need, either you're going to double click on it or you're going to write this statement. So we can write USE, then the database name DB_SQL_tutorial, and then run. So with that, we make sure that we are on the right database, so we don't get any errors.

Alright, so now let's write our query for the task. So we need all the data from the customers. So the first thing that we specify in the SQL statements for the query is SELECT keyword. After that, since we saved all the columns, we're going to use *. * means all the columns inside this table. After that, we need to tell the database which table that we need, so since we need the customers, we're going to select the table customers. So we're going to say FROM customers. So we have now the query that's going to select all columns from the table, and here we don't have any like filters or anything, so this is the basics form of SQL. Let's hit run, and as you can see here now we have the results; we have all five customers from the table customers. And don't forget in SQL the order is very important, so it always starts with SELECT, then comes FROM clauses, because if you do the way around, you will get an error. So make sure that you are getting the right order while you are writing any SQL statements.

So now let's do another task where to say, Okay, I want to see all the data from orders. So let's do that: all data, all columns, that means SELECT *, FROM, and now our table is orders, so I'm going to select the table orders here and then execute. And as you can see now, we can see the database retrieve four orders, and that's right because this is all what we have in our database.

Alright, so now you might be saying, I'm not really interested in all the columns from my table; I want to specify a few columns from the table to retrieve. So let's say we have the following task: Retrieve only the first name and the country of all customers. So here the difference from the previous one is that we don't need all the columns; we just need few columns. So let's see how we can solve that. So I'm going to remove this one and start with SELECT, and now I cannot use * because I don't want to have all the columns. So we are interested on the first name, so we write down first_name, then comma, the second one is country, and now we need to tell the database from which table. So FROM customers, and let's run. As you can see here now, we have only two columns: first_name and country, and we don't see here the other columns like customer_ID or score. So with that, we selected only two columns without using *, and we solve the task.

Okay, so now just to understand how the database are reacting to our query, I'm going to show you now step by step what is going on in the database once you query this statement. So the database starts from the table, so we said FROM customers, that's mean the database is going to focus on the customers table, and then it's going to check, okay, which column we need. So we say first_name and country, and since in our SQL statements there is no filters, it's going to select all the data, so it's going to select everything from the table and as well for countries, and that's how the database implemented our query.

Alright, so with that we have learned how to use the SELECT statement. Next, we're going to talk about how to retrieve unique values using the DISTINCT.

[Music]

Alright, so the SELECT statement, as a default, it will not remove any duplicates from the results. So sometimes you might be in a situation where you have some duplicates inside your tables, and you want to remove them from their results. So removing duplicates from results, not from table. In order to do that, to remove those duplicates, we use in the SELECT statement a keyword called DISTINCT. So in order to understand that, let's have the following tasks: List all countries of all customers without duplicates.

Alright, so now let's try to figure out how we're going to solve this task. As you can see, we need the customers, that means we're going to focus on the table customers, and we need all the countries, that means we need only one column called country. So let's do a basic query; we're going to start always with SELECT, and the column that we need called country, so we're going to write down country, then FROM our table is customers. So now let's just check whether it's in duplicates and see the results. So execute, and now we can see the results: Germany, USA, UK, Germany, USA. As you can see, there is duplicates; we have Germany twice, and as well the same we have USA twice. So now the task is saying without having any duplicates. So in order to solve that, we're going to type DISTINCT exactly after the SELECT. So we're going to use DISTINCT over here, and this keyword always comes after SELECT, and only by doing that, it's like magic words; it's going to remove all the duplicates. So let's check that. So execute. As you can see now, the list contains only unique entries; we have Germany only once, USA as well, and UK as well. So here we have a unique list of all countries of all customers, and we solve the task.

Alright, so now in order to understand DISTINCT, I'm going to show you how the database is implementing our query. So we said in our query we need the data from customers, so the database is going to focus on the table customers, and we said as well we need only one column called country, so the database is going to select it in the results. We said, okay, we need all data but in DISTINCT, without having any duplicates. So the database can start, okay, Germany; it's not in the results; it's going to put it there. USA; we don't have it in the results; it's going to put it there. UK; the same; we don't have it in the list; I'm going to put it. But now it comes to Germany again; it says, okay, we have it already, so it will not include it in the list, and same goes for USA; we have the USA already here; it will not include it in the list, and with that we will have our unique list of all countries.

Alright, so that's all about that DISTINCT, and next we are going to learn how to sort our data using ORDER BY.

[Music]

Alright guys and girls, so now once you start using SELECT statements in order to retrieve your data from your database, the results that you are getting is not sorted in any particular order. That means the DBMS or the database is sending the data back to you in unspecified order. So now if you want to apply some rules or you want to sort the results, we could use the clause ORDER BY. So now in order to understand the ORDER BY, we're going to check the following task: Retrieve all the customers where the results are sorted by scores and the smallest should be first.

So now let's try to figure out how we're going to write the SQL statement in order to solve these tasks. So now since we need the customers, that means we are focusing on the table customers. Let's write our SELECT statement first. So SELECT, there is no specifications about the columns; I'm going to use a * FROM customers. So let's run that and see. As you can see, we have all the customers, but as you can see, it is not sorted by the score. The task is to sort it by the score, the smallest first, then come the highest. In order to do that, we're going to use the keyword ORDER BY. So let's have a new line ORDER BY. After that, we need to specify the column that we're going to use to sort our data. The task says it should be sorted by score, that means our column is score, so the column name score. Now we have here two options how we can sort our data; we have two ways: ascending and descending. In the task it says it should be sorted by score, the smallest first, that means we need to use ascending. In SQL we have the keyword ASC, that means it is ascending. So now we have the ORDER BY clause, and we should be fine. Let's run the query. Now if we check the results, you already might notice that the result is sorted differently from the start; that means we have different sorting now after the score. So the first one is NULL; it's because the NULL considered to be the smallest inserting. After that we have 350; it's the smallest score from all those customers, then comes the higher and higher and so on. So now we first or we put a rule how to sort our data, and we have a solution for our task. One more thing to notice is that in SQL the default sorting in ORDER BY is ascending, that means if I go here and remove the ASC this keyword and start the query again, I will get exactly the same results, because if I don't specify anything after the column name, the default gonna be ascending.

Okay, so now let's consider one more quick task, and it says almost the same: Retrieve all customers and the results should be sorted by score, but this time the highest should be first. So that's means we need to use the method of descending, the highest first and the smallest. So that means we have the same query; we don't have to change anything, but now after the column name, if I leave it empty, it's going to be ascending, but this time we need to be using descending, so we're going to use this keyword DESC, that means descending. So let's run this query. So now let's check the result; we can see already that the list is sorted the way around. So now we have the first three chord with the highest score; John has 900, and it is the highest, then come the smallest and so on. So now we are sorting the list or the result with the descending way.

Alright, so now using ORDER BY, sometimes it gets a little bit more complicated if you are using not only one column, maybe different columns to sort your results, especially if you have a lot of duplicates inside your data. So using one column will not help you; you're going to be end up using multi-columns in the ORDER BY. So in order to understand that, we're going to have the following task: Retrieve all the customers whether result is sorted by country in alphabetical order and then by score where the highest is first.

So let's try to figure out how to write the SQL for that step by step. So now I'm going to remove everything over here; I write it down ORDER BY the first one called country, so the column we need is country; alphabetical order, that means it is ascending, so we could leave it as a default or we could write ASC; doesn't matter; we're going to have the same result. So now let's check the result for that. So now as you can see that we have the results already sorted by country in ascending way, that everything is fine. So we have Germany first, then UK, USA, so it's already sorted, but that is not enough, because the task it says, okay, after that you need to sort it by the score, the highest first. If you check now here the example, those two customers, Marty and Martin, both of them comes from Germany, but Maria comes as first, and even though that she has lower score. So that means after we start to do the country, we need to sort again those scores. In order to do that, we're going to put here comma, and then write down the score, then the option here gonna be the same; descending, the highest first. So this, so that means we could use in the ORDER BY here two columns, and for each column we could use different methods in order how to sort it. So now let's run this, and as you can see here again, that's okay; we have it sorted by country, but now Martin comes first because he is higher score than Maria, and this is exactly how we're going to like sort the data using multi-columns.

One more note about ORDER BY that we could use instead of the column name the position of the column. So if you can see over here that the country had the position four, so this is the first column, second, third, fourth, and fifth. That means the country had a position of four. So instead of writing country, I could write four. So here the score is the last one; it's the fifth. So this is like an easy way how to sort the data or use ORDER BY. And if I run this query, I will get exactly the same results, but I really don't recommend that because if like you change any structure of your data, like let's say the country will be the position two and the score gonna be position three, then after you change the structure of your data, you have to go and edit your query; that means I need to change those numbers again, and that is really bad because you might forget about it. So if you write the name, it doesn't matter any change that's going to happen on the schema or on the table, your query gonna deliver the same results, and using the numbers you need to adjust this. So I really don't recommend using those numbers; the better is to write the full name of the column.

Alright, so now in order to understand the ORDER BY, I'm going to show you step by step what the database is doing in order to execute with our statements. So first it's going to choose the table, so our table is the customers; we are using the *, that means you can select all the columns; let's gonna put it in the results. So now once we are not using anywhere or filters, it's going to select all the data, but it notices that there is ORDER BY, so it's going to like sort the results by each column. So the first column is the country, so it's going to sort it by the country first. So first the first customer gonna come here, Germany as well, Martin, then after that comes UK; I'm going to sort it over here, and then after that come John from USA. So it starts sorting the results, so we can have here that the country is sorted, and this is the first step. The next step is going to go to the second column in the ORDER BY, in the score, so it's going to sort the results again, so it's going to check, okay, those two customers, and it's going to see, okay, Martin has higher score and gonna switch it, so let me just do it like this, and Martin gonna be the first on the list, second we have UK, so that's okay, and then we have those two; we have 900 and NULL; NULL is the smallest, and it is okay. So now this is how the database is sorting using the ORDER BY.

Alright, so that was it for this chapter; we have learned how to query our data using the SELECT statement and how to sort the result using ORDER BY clause. In the next chapter, we're

Gonna learn how to filter our data using the WHERE clause, where we're gonna learn many important operators.

[Music]

All right guys and girls, so now we have learned how to retrieve all our data from the database using very basic keywords: SELECT, FROM, AS. As a next step, we need to learn how to filter our data using WHERE clauses, because in real-world scenarios you are not interested in all records in the tables. So usually you will be interested only in the rows that fulfill a certain condition. For example, we don't need all the customers in the results; we need only the customers that come from a certain country or have a specific score. So, in order to understand that, let's check a very simple task. The task says: list only German customers. So that means we are not interested in all customers; we need to see in the results only the customers that come from Germany. Okay, so now let's try to figure out how we're gonna solve this task using an SQL query. In the task, we will be focusing on the customers; that means we will be querying the customers table. And since there is no specification about the columns, we could go and retrieve all the columns. So let's try to write now the SQL statement for that: SELECT, as usual, then no specifications about the columns—we're gonna select everything; we use * FROM our table, which is customers. And let's run this and see. As usual, we have all the data, all the customers from Germany, from the USA, UK, and so on. But the task says only the German customers; that means we have to do some filters now. In order to do that, we're gonna use the WHERE clause, and usually we put it immediately after FROM. All right, so now we need to write down the keyword WHERE. After the WHERE, we need to specify our condition. The condition should be based on the countries; that means country should be equal to Germany. So we write down now the column name, country, equal operator, and now here we need to enter the value that is exactly like it's written inside the database: Germany, like this. So we write down Germany. Let's start now the execution and see the results. As you can see, we don't have all the customers; we have only two customers that fulfill this condition: Maria and Martin. Other customers, like John, George, and Bitter, they all don't fulfill the condition and they are excluded from the results. All right, so as you can see, SQL is pretty easy to write and read. Like, check this: SELECT all columns FROM customers WHERE the customer's country should be equal to Germany. So it's really easy to read; it's using English words and in the logical order. Okay, let's have now another quick task. It says: select customers whose score is greater than 500. So it's based on the same table, so we will not change here a lot of stuff; the only part that changes is the condition. So now we're gonna remove this. Our condition here is based on the score, so we have the column score; the operator is not anymore equal; it should be greater, so we need another operator, and the value is 500. So we write down here 500. Let's execute that, and now we can see the customers whose score is greater than 500. As you can see, it's pretty easy to use the WHERE statement. All right, so now in order to understand the WHERE clause, I'm going to show you step by step what the database is doing once we execute our query. So the database is going to check which table; so it's going to focus on the customers. Then it's gonna check which columns do we need. As we write down the star, that means the database is going to select all the columns and their results. But then the database can check: okay, there is a filter, that means not all the data should be in the results; so it's gonna check it. So now the first record is going to check the score over here. The score is 350, that means it is not greater than 500; it will not include it in the result. The next one is greater than 500, that means it's gonna take it. The next customer, the same, fulfills the condition. Oops, I need to write it down over here. All right. Now the first customer: 500; it is not greater or equal, it's only greater than 500; that means it will not consider it. And the last one, it's NULL, that means it's empty; it will not fulfill the condition; that means we have only two customers, and that's how WHERE is working inside the database.

All right, guys, so in SQL there are many different types of operators that you could use inside the WHERE clause in order to filter your data. And in SQL they are split into two groups: on the left side we have the comparison operators, and on the right side we have the logical operators. The comparison operators, you could use them in order to compare two values. For example, we have the equal, not equal, greater than, less than, greater than or equal to, less than or equal to. The logical operators, you could use them once you want to combine two different conditions, and and as a result you're gonna get true or false. For example, we have AND operator; it returns true if both of the conditions are true. We have OR; it returns true if one of the conditions is true. Then we have NOT, IN, BETWEEN, LIKE, and so on. So in the previous examples, in the workloads, I showed you two comparison operators; it was the equal and greater than. So as a next step, I'm gonna go through all of them in order to show you how you could use them inside the query and some examples. So you don't worry about it. All right, so that was it for the WHERE clause. Next we're gonna talk about the comparison operators.

[Music]

All right, now we're gonna focus on the comparison operators and learn how to build up our conditions inside WHERE clauses. The comparison operators, as I said, are used in order to compare two values, and it is the most basic way how to filter data using SQL. Okay, so now in order to understand them, let's have the following tasks: First, find all customers whose score is less than 500. So that means we're going to focus on the customers table, and there is no specification about the columns; we're going to use SELECT * FROM customers. So now let's run this. As you can see, we have all the customers, but we need to filter the data: score less than 500. So we're gonna use the WHERE clause. The column is score, the less operator, and then we're gonna type 500. So let's check the results and run it. So we have only one customer whose score is less than 500. Okay, so now in order to understand why we had only one customer in the results, I'm gonna show you what the database has done once we executed our query. So we said SELECT * FROM customers. The database is going to focus on the customers. We said *, that means we need all the columns in the results. And then, since we have a WHERE clause, it can filter the data. So it's gonna, like, go through all the records and try to find whether it fulfills the condition or not. So I'm going to use the like and dislike button to say whether this is true or false. So the first customer, her score is less than 500, that means it's gonna be shown in the result because it fulfills that condition. And then we have the next one; score is 900; it is not less than 500, so that means false. The next one, the same: 750; it is not less than 500. The next one is interesting; it is exactly 500, but since the condition says less than 500, if it doesn't fulfill the condition, then the NULL, it is anyway false. So that's why we had only one customer in the results. Okay, so now let's have another task, and it says: find all customers whose score is less than or equal to 500. So almost the same, but we have here as well the customers that are equal to 500. So let's check that. We can have the same query, so we'll not change anything over here, only the operator. So we need the less than, so it can stay like this, but we need as well equal to. So there is another operator; it's called less than or equal to, and it looks like this. So we have them both like this, and let's run it and see the result. So as you can see now, we have customer number four, Martin; he has scored 500, and now it should be shown in the result. So we have the first one, Maria, less than 500, and we have Martin; he has exactly 500. So this is the less than or equal to. So as you can see, it's pretty simple. Let's go with another operator with the following task: find all customers whose score is higher or equal to 500. So that means it's almost the same, but we need to use the other operator: greater than or equal to. So it looks like this: greater or higher than equal, and let's check the result. So as you can see here now, we have all those scores that are higher than 500. So we have John with 900, we have George with 750, and Martin stays here because his score is equal to 500. So as you can see, it's really easy. All right, so now we have one more, last task. It says: find all non-German customers. So let's try to solve that. We're gonna stay with the table customers, so SELECT * FROM customers, and we need to filter now the data using not score, but the country. So we're gonna now here country, and since it says non-German customers, that means the country should not be equal to Germany. So the not equal operator, it looks like this, and then we need the value Germany. So with this query you are saying, okay, give me all the customers whose country is not equal to Germany. So let's run that, and as you can see here, we don't have a country called Germany in the results, and you could see, like, or have the same result using this operator as well; it stays as well not equal. So if I run that, we're gonna get the same result. So you could use either one of them; there is no difference between them. Okay, so now let's see how the database solves that. We say SELECT * FROM customer; that means that it's going to focus on the customers; * means all the columns, as usual. So we're gonna put it over here. We have in the WHERE, it says country not equal Germany. So the database is going to focus on this column for the condition. So let's see the first customer; the country equals to Germany, so that means it's false; we will not see it as a result. The next one, the country is not equal to Germany, so that is positive; we're gonna see it in the results. The next one is the same; the country is not equal to Germany; we will see it as well in the results. And the first customer, the country is equal to Germany, so that means it is false; we will not see it in the results. And the last one, the country is not equal to Germany, so it is true; we will see it in the results. So that's why we saw three customers in the results. All right, so now we covered all those operators inside the comparisons. They are pretty easy; they always, like, compare two values, and I would suggest that you go and, like, play with them until you understand how they work. But as a next step, we're gonna go and start working on the logical operators. They are, like, a little bit more difficult, so don't worry about it; I'm gonna explain that in detail, with examples and everything, but they are very important using SQL because you will be ending up using them a lot. All right, so that was it for the first group of operators. Next we're gonna talk about the other group: the logical operators AND, OR, NOT.

[Music]

All right, guys, so now we're gonna talk about the second group of operators that you could use inside the WHERE clause, and they're called the logical operators. We will focus on these three: AND, OR, NOT. In the previous examples, you learned how to filter your data using only one condition, but in real-life scenarios things get more complicated where you have to combine the results of two or more conditions. And in order to do that, you could use the operators AND or OR. Okay, so now let's start with the first operator: the AND operator. It says the following: it returns true only if both conditions are true; otherwise, it can be false. So let's say we have condition A, condition B, and we want to combine them using AND. So the first situation: we have in condition A true, and in condition B we have true. If you do the AND, we will get as well true because it fulfills the requirement; so both conditions are true, we will get true. Let's have the second scenario: condition A as well true, but in condition B we have false. So here, not both of them are true, and we will get in the result false. So now the other way around: condition A has false, and condition B has true; not both of them are true, that means the result can be false. And the last scenario where you have both of them are false; as a result, you're gonna get false. So that means the AND operator is really strict; both of the conditions should be true in order to get true; otherwise, it's gonna be always false. Okay, let's jump to the next one; we have the OR operator. It says it returns true if one of the conditions is true. So that means the OR operator is gonna be happy if you have one of those conditions with true to give you true; otherwise, it's going to give you false. So let's check again the same example; we have here condition A, condition B, but now we're going to apply the OR. So we have in the first scenario true in A, true in B; it fulfills the requirements; both of them are true, so that means with the OR we have true. The next one: we have in A true, but B false. So now it says at least one should be true, so that means with the OR you're gonna get as well true because you have it here in A; it is true. So the next scenario where it is the opposite, where you have in A false and B true; it fulfills the requirements; at least one of them is true to give you true. But only the last scenario where you both are false; with this scenario you will get false. So as you can see, the OR operator is less strict than AND; it can be happy if you have somewhere true to give you true, and you will get more results. Okay, let's move to the last one: the NOT operator. It says it's gonna reverse the result of any Boolean operator. So that means it's going to be always giving you the opposite. For example, if you say left, it's gonna go right; if you say go right, it's gonna go left. So here you have always the opposite in the results; it's gonna work only with only one condition, so it's not combining two conditions like AND and OR. So here we have the condition A; if you have here true and you use the NOT, so that means you will get false; so it's going to do the opposite. And the same, if you have false and you use the NOT operator on it, you will get true. So it's always like reversing the results; if you have true, you're gonna get false; if you have false, it's gonna get true.

[Music]

Hey guys and girls, so enough with the theory; let's have some tasks in order to learn that in SQL. So we have the following tasks: find all customers who come from Germany and their score is less than 400. So we have here two conditions; let's try to solve that. So as usual, we're gonna use SELECT, no specifications about the columns, * FROM our table, which is customers. And now in the WHERE condition we have two conditions. So the country is Germany, so we can write country equals the value Germany. And now we have another condition; it says the score should be less than 400. So score, less operator, 400. So now I have two conditions, and I need to combine them, and the task it says AND, that means both of the conditions should be fulfilled. So I need to write now the operator AND between both of those conditions. So let's run this and see. So with these conditions, we have only one customer that fulfills both of the conditions, so we have Maria; she comes from Germany, and her score is less than 400. Okay, guys and girls, so now let's see what the database did once we executed the AND operator. So we have, as usual, SELECT * FROM customers; the database focuses on the customers table; * means we need all the columns, so we're gonna see all the columns in the results. So now the database is going to go through each row and try to find out whether it fulfilled the requirements to be in the results. So let's start with the first one: the first customer, Maria; she comes from Germany, so this is the first true in the first condition. The second condition: we have score 350; it's less than 400, so that means we have another true. And since we are using AND, both of them are true, we will get true as a result. So that means the database is gonna go and put her in the results. So the next one: we have John; the country is USA, so this is the first false over here in the first condition. The second condition as well, it is higher than 400, so it's gonna put it as well false; both false; the AND operator is going to put it as false. So the next one, we have the same situation as well: the country is not Germany, and the score is not less than 400, so both of them false; the AND operator is going to put it as false. And the fourth one: we have Martin; the country is Germany, so we have the first true, but the condition is not less than 400, of course, sorry. So we have here false; with the AND it will not work, so that means it's gonna put false as a result because both of them are not true. And the last one: both of the conditions are false; the country is not Germany, and we don't have a score, so that means we have as well false. So only one customer fulfilled both of the conditions with true, and once you use AND, you will get only one record. Okay, so now let's jump to the next one, and we have the OR operator. The task says: find all customers that come from Germany or their score is less than 400. So we have almost the same setup, but here we have the logical operator OR. So we have the same conditions: country equals Germany, score less than 400, but now we're gonna connect them with the OR operator. So now let's check the results; I'm gonna execute that, and as you might already noticed, we have now more customers as a result for this setup. So let's check what happened. Okay, so now at the start, as usual, we told the database SELECT * FROM customers; it is focusing on the customers; all the columns because of the *. And now we have here the same conditions: country equal to Germany, score is less than 400, but the only difference is that we are using the logical operator OR, so the result is going to be different. So the database is going to go through each row and see whether it fulfills the requirement or not. So with the OR, it is enough to have only one true to give it true as a result. So as you can see here, the first customer, both of them are true, that means we will have true as a result, so we will see Maria in the results. After that, those two customers, they don't have any true in any condition, that means it's gonna be false in the results. But customer number four, Martin, he has one true, so that means this is enough; we will get that as a result, so Martin's gonna be in the results. And the last customer, the same, so he has both false; we don't have any true, that means our operator's gonna put false. So that's why we got more customers as a result. All right, so now let's move to the last one; we have the NOT operator, and we have the following task: find all customers whose score is not less than 400. So that means we have only one condition, and we have the NOT. So let's try to solve that. So here we have only one condition; it is about the score; it didn't say anything about the country; I'm gonna remove this part of it. So we have score is less than 400, but it says it should not be less than 400. So all

What we can do is just to add a not operator. It's very simple. So let's run this. As you can see over here, that's all customers; they don't have a score less than 400. Okay, so now let's see what the database did once we executed the not operator. So, as usual, we'll get all the columns because of the star, and then we have the condition: score less than 400, but with the operator not. So without the not, we will have only one customer that's fulfilling this requirement. So we have only one true, and the rest are false. With the not operator, it's going to reverse everything; that means if you have true, it's going to show it as false, and if you have false, you can show it as true. So it's just going to do the opposite. So here we have true, and the result is going to be false. The next three are all false, so we will get true. But here you need to be careful with something. So here it is null; so the database doesn't know whether it's less or like greater or something like that, so it will like treat it as unknown and it will not show it at the results, so because it is empty or null. So that's why we have at the results those truths; that means we will have only those three customers. Alright, so that was it for the three operators: and, or, not. Next, we're gonna learn about the logical operator between.

[Music]

All right, guys and girls, so now we're gonna talk about one more logical operator that you could use inside the where clause in order to filter your data, and that is the between. Between is a logical operator that allows you to select only the rows that fall within a specific range. And in order to work with between in SQL, you need to define like boundaries: two boundaries, two values that specify the range. So here we need to define in between the min value and the max value. It could be anything: like text, number, and date. So here in SQL, any value between those two boundaries they are going to be considered as true, and the values or the rows that are outside those boundaries are going to be considered as false. And one more very important information is that those boundaries, the min value and the max value, are included in the condition. So it's really—I see in the projects a lot of people that forget about it or to like ask again, are those boundaries in the condition or not—so it's really confusing a lot. Don't forget those values are included in the condition. So now in order to understand that, we're gonna have some tasks, and we're gonna try to solve it with SQL.

All right, so now we have the following tasks: find all customers whose score falls within the range of 100-500. So let's try to solve that with SQL. So, as usual, select star; there is no specification about the columns; our table is customers; and now we need to filter the data, so we're going to use where, and here the column that we need to use is score because it says score should be between 100 and 500. So we're gonna write down score, and now the syntax for between: you need to write the keyword between, and here now we need to specify the minimal value, so the min value, the first boundary, it is the 100, and then we're going to use the operator and, and then the max value, and that's it. So for the between, you need to write down the column name, between, min value, and max value. So that's it. Let's now try to execute the query and see the results. As you can see, those two customers have the scores that are between 100 and 500. Okay, so now let's see what the database does once we executed the query with the between operator. So now, as usual, select star from customers; that means in the results we need all the columns, and we have where, so that's mean the database should filter the results, and we have the condition between 100 and 500. So let's go through all the customers. So the first one, we have the score 350; it is between this range, so between 100 and 500, so we have the first true, and we will see it in the results. So the next one, it is 900, so it is like outside of the max boundary, that makes it as a false. The same goes for George; we have 750; it is as well outside of the 500, so it's outside of the boundaries, not between those two values; we have the false. And now it is interesting; we have the 500. So the 500, it is not within the range; it is exactly the boundary, and with that in between, it's going to consider it as true, so we have it as true. And the last one, we have null, so it is unknown, so it will not return it. So here, that's why in the result we saw two customers: Maria and Martin, because they fall within the range between 100 and 500, and Martin is exactly the max boundary; that's why it is considered as true.

Okay, guys, so there is another way how to solve such tasks without using between, and instead of that we can use two conditions and connect them with the and operator. So I'm gonna show you that: select star from customers, like usual, and now we're gonna write the where conditions. So first, the score should be greater and equal than 100, so we're gonna use operator greater equal to 100, and and then you can write the second part of the second boundary: the score should be smaller and equal to 500, so we're gonna use this operator: so less and equal to 500. So with that, we redefine the between function, and if I run this—so I'm gonna remove this part over here and execute it—we will get exactly the same result because we just redefined it in another way. Some developers, like me, tend not to use between, and instead of that we use such conditions, because for me it's more easier to read what the query is doing instead of using between, because I need to remember when I use between that, for example, the boundaries are included, and if you forget that you need to search about that, so it's really easier just to read exactly what the query is doing. So I tend to avoid between; I use the two conditions with and. So one more like advantage about that: you could control it better. So, for example, I could use for the boundary with the max value only less without the equal, so you could define it more flexible than the between. All right, so that was it for the operator between. Next, we're gonna learn about the in operator.

[Music]

All right, guys and girls, so now we're gonna talk about one more logical operator that you could use inside the where clause in order to filter your data, and that is the in operator. The in operator allows you to define a list of values that you would like to see at the results or to be included as a result. So how it can work: as I said, you can define like a checklist, a list of values where you are telling SQL only those values are allowed at the results. So here you can define multiple values; it's not like the between where you define the boundaries; here is a list of values. So the database can start like asking for each value: is a value inside this list? If the answer is yes, then it's going to be true; if the answer is no, it's going to be simply false.

All right, so now, as usual, in order to understand that, we're going to have one task and try to solve it at SQL. The task says: find customers whose customer ID equal to one of the values 1, 2, or 5. So let's try to solve that. As usual, there is no specification about the column, so we're gonna select star from customers, and now we need to filter the data, so we're gonna use where clause, and here we start. So it says the customer ID, so that means this is the column that we're going to use in order to filter the data, so customer ID, and now we have a set of values: 1, 2, and 5. So in order to use that, we're going to use the in operator, and we start defining now the list, the checklist: open brackets, the first value is 1, then comma 2, comma 5, then close brackets. So we defined the list of values that we want to see at the results, and with that we gonna run that query and see what's gonna happen. So as you can see, the query is run, and we have the list of customers that exactly match our list: the customer ID 1, 2, and 5.

Okay, so now let's see what the database did once we executed the in operator. So, as usual, select star from customers means I want to see all the columns at the results, and the database can select that, and since we have where clause, it's gonna start checking the condition. So the condition should say: is customer ID should be in this list? So the data is going to check each customer. So here we have customer ID 1, and it is in the list; that's why we're gonna get a true over here for this condition, and we're gonna see it at the results. The next one is 2, so here as well we have true for this one, and we're gonna get it as a result. The third customer's customer ID equals 3, and it is not in the list; that's why we're gonna get false over here. The same for 4, so 4 is not in the list; it will ignore it. And the last one: customer ID equals 5, and it is in the list, so we will get a true for that. And this is how the database can process our query.

All right, so you might tell me now: wait a minute, Vara, I just learned about the or operator and how I combine different conditions using the or, and I could solve this task using that instead of using in and like a checklist. So let's see how we could do that. I agree; it's gonna work as well. So select star from customers where customer ID equals 1, so the first one, then we write or customer ID equals 2, and go on: customer ID equals 5. So if I run this query, we will get exactly the same results, but like I agree on that, but as you can see here, it is more compact and much easier to read; like you make a list, and that's it. Here you can define all those values with like multiple conditions and connect them with the or. So imagine you have 10 values; you will have here 10 rows of code. So I really like it with the in operator; it is more compact and easier to read. Alright, so that's all about the in operator. Next, we're gonna learn a very important operator; it is the like.

[Music]

All right, guys and girls, so now we have the final logical operator that you could use inside the where clause in order to filter your data, and that is the like operator. It is a little bit more complicated than the others; don't worry about it; I'm gonna explain that step by step with examples. So once you understand it, it's going to be more easy and fun to use. So in the other examples with the where clause, we always define the whole value, the complete value in the where clause, but sometimes you might be in a situation where you still don't know the values you are searching for; some values, and you have like a pattern at your head. For example, you are searching for customers where their name starts with M. So here you don't know the whole value; you are searching for something, and you have a pattern. So you could use the like operator with a pattern in order to find those customers, or there is a lot of values at the database or in SQL where it's going to be almost impossible to define all of those values in the where clause. So instead of that, you're gonna define like a pattern, and you tell SQL: I'm searching for something like this.

So now the like works like this: it's returned true if a value matches a pattern; otherwise, it's gonna return false. So that means we need to build up like a pattern in SQL, and in SQL we have two tools in order to do that: we have the percent, where we say it matches anything, or we have the underscore, it matches exactly one character. So now let's have the example in order to understand that. So we have the first example: find names that begin with M; that means you know that the names begin with M, and you don't care about the other characters. So now we need to build up such a pattern; we can write down the M and the percentage; you are saying here for SQL that they begin with M, and the others it doesn't matter; so it could be empty, it could be like characters, multiple characters, doesn't matter, but for you it's very important that they start with M. Now we have another one: it says find names that end with n. So that means it could start with anything, so we're gonna start with percentage, and it should end with the n. And here you need to be careful that they are case sensitive over here, so there is a difference between small n and big N. So this pattern tells SQL: starts with anything, but I need it to be ended with n. Now we have the example where you say: okay, it should not be the first or the last; the name should contain somewhere the r character. So find names containing the R. So you are not defining whether they are at the start or at the end. So with that, you could use the following pattern: it could be started with anything, then R, and end up with anything. So here you don't know where exactly they are; the name should contain somewhere an R character. And now the next one: you could be more specific where you can say: okay, find me the names that containing the r, but exactly at the third position. So it's a little bit more complicated, and with that you're gonna use the underscore. So with the underscore, you say: okay, the first position could be anything, the second position could be as well anything, but the third should be exactly the r, and afterward it's gonna be anything: like empty characters and so on. So with that, you are mixing those two tools: underscore and the percent. So now we're gonna go more in details and more with examples in order to understand how it works.

Okay, so now we're gonna go and deep dive in each of those examples and explain for you what is going on in the database or in SQL once you define those patterns. So the first example we have: find names that begin with M. Our pattern is M and percentage; that means anything after that we don't care about that; it should start with M. And in our database we have those five values, those five names, and let's start one by one. So Maria, it starts with the m, so that means it is matching our pattern, so SQL gonna return for that a true. The next one we have: John. So the J over here is not matching our pattern; that means SQL gonna put false on it. Then George, the same thing; it starts with G and not matching our pattern; it should start with M to get like a true, so we have false for that. Martin here starts with M, that means it is matching our pattern, and we're gonna get for that it's true. And the last one: Peter; we have p, and it is not matching our pattern, and we're gonna get false. So if you define those patterns in the SQL, you will get those true and false from the database.

Okay, so in the next example we have: find names ends with n, small n. So our pattern is like anything, the percentage, and then small n. Let's go through the names. So the first one: Mario. And the database is going to check the last one; okay, the last one is a, not matching our n; it's gonna reject it; you're gonna get false. So we have John; John has at the last character n, and it is matching our pattern; database gonna put true on it. So the second one we have: George. George ends up with G; it is not matching the pattern; false. Martin n; we have true here, so the last character matches our pattern. And Peter, we have the r over here; it is not matching the pattern. So if you run such a pattern on your database, you will get only Jon and Martin as a result. So let's find the next one. So the next one it says: find names containing R, and we didn't specify anything for that; somewhere should be R. So the pattern it says: percent R percent; that means somewhere there is an R. So with the Martin, somewhere there is R, so here over here we have the r, and it's going to return true. With John, there is nowhere an R, like there is no character over here with the r; that means the database is going to return false. George, we have over here an R, so it's gonna return true. Martin, the same, and Peter as well the same. So as you can see, if you like start with the percent and end with the percent, the database can find somewhere your character, and it's gonna return it as true, as you see here. Peter ends with R; Martin, in the middle, somewhere there is R. So here you don't care about the position where is your character.

Okay, so now we come to the final one: it says find names containing the r at the third position. Here we are very specific; we are saying exactly the third should be the R. So in order to do that, we will not use the percent in our pattern; we're gonna use the underscore. It says: the first character could be anything, the second character could be as well anything, but the third character should be exactly the r, and after that it could be anything; it could be empty, like bunch of characters; we don't care about that. So let's go through our values and see how the database is going to react. So Maria, it starts with m; it's okay; A, it's okay; the third should be R, and we have here a match; afterwards, like it doesn't matter, so this is a matching to our pattern, so Maria gonna get a true from the database. The next one: John; like the first two characters are okay, but the third one is not matching the pattern; it is the H; that's why we're gonna get a false for that. The third one: you can see the third position is O as well, not matching our pattern. Martin is matching because we have the first character is M; could be anything; the second one as well A, and the third is R, so this is matching our pattern; the rest could be anything, so that's why Martin is matching exactly our pattern. The last one: Peter doesn't match our pattern because at the third position we have the T. So with that, if you run such a pattern at your database and you are specific with that, you will get only Maria and Martin as a result.

Okay, so now as the next, we're gonna learn how to write SQL statements using the like operator in order to understand the syntax and to solve those four tasks. So we're gonna start with the first one: find all customers whose first name starts with m. So, as usual, we're gonna select star; no specification about the columns; our table is customers; and now we have to filter the data with our pattern, so where clause; the column that we're going to use in our pattern is the first name; then we can write down the like keyword; after that, we're going to specify now the pattern; so it starts with the high comma, then big M percent, and then close it with the high comma. So with that, we specify the pattern for the like operator, and let's run it. So as you can see in the results, we got those two customers that have a big M at the start of the first name. So this is how we're gonna do it using the like operator. So the next one it says: find all customers whose first names end with a small n. So we're gonna have the same stuff over here, but we need to redefine the pattern. So okay, that was in German, and then anything like the percent, and then small n, then you close it. So let's run that, and as you can see, we got those two customers: John and Martin, because they have their first name, and they end up with the N. All right, so now to the third task: it says find customers whose first names containing somewhere an R, small R. So let's do that; we're gonna have the same setup over here, but we need to change the pattern: high comma, then percent small R percent, then high comma. With that, as I said, you...

are not specifying any position. Somewhere should be an R, so let's run that and check our query. You can see here Marie has an R somewhere. George has an R somewhere. Martin and beta, so we got those four customers, but John, we don't get him because he doesn't have an r in his first name. Okay, so now to the last ones. The task says find all customers where the first names containing the r character and the third position. So here the same stuff over here; we need to change only the pattern. So `LIKE`, the first character should be anything, so underscore again underscore, the second character could be anything, and here we define the r, and then we say anything after that. Then `LIKE`, it's on once, so we write down here the pattern after the `LIKE`, and let's run that. And as you can see, only Maria and Martin, as we discussed, that containing uh the third character the R. So with that, you have those four examples with the `LIKE` operator. It's really fun once you start like practicing with that, so try now. I would say to make some pattern at your head and try to write it down and see how SQL can react. That only with the practice you're gonna get some good results, and you're gonna like understand it. Alright, so that's all for this chapter. We have learned how to filter our data using the `WHERE` clause and many important operators. In the next chapter, we're gonna step up the level where we're gonna learn how to combine our SQL tables using joins and Union. [Music]

All right, guys and girls. So so far, we have learned how to query only one table in all our examples. We focus on the table `customers`. We done selects where we filter the data and so on. That was only one table. In real-life scenarios, you will be working with a real database that contains a lot of different tables, and once you start writing SQL statements, you will be end up querying not only one table but maybe multiple tables in order to get something meaningful of the data. So that means you need to start learning how to combine different tables, how to join those tables together in one SQL statement. This is very important in order to learn SQL because once you master this, you will be good in SQL. Now in our tutorial database, we will be working now with two tables: the `customers` and the `orders`. In the `orders` table, you can see which customer displays which order. So now, in order to join those two tables, you have to specify two things. First, you need to determine what is the join key. So a join key, it is like a column that exists in both of the tables. For example, the `customerID`. We can see it here in the `customers` and as well in the `orders`. So that means the `customerID` is a good candidate in order to join those tables, and it's gonna be our join key. The second thing that you need to specify is the type of the join. In SQL, we have four different types of joins: we have the `INNER JOIN`, `LEFT JOIN`, `RIGHT JOIN`, and `FULL JOIN`. It might be complicated at the stars, but don't worry about it. I'm gonna explain all of those types step by step with examples. I'm gonna show you as well how SQL works with those types. Alright, so now let's start with the first type of joins. We have the `INNER JOIN`. The `INNER JOIN`, it is the most commonly used type of joins between developers. I as well tend to use a lot of `INNER JOIN`s in my SQL statements, so it is widely spread to use `INNER JOIN`s. So in SQL, there is a very important aspect that you need to understand once you work with SQL joins, and that is in SQL there is always a left table and a right table, and that's really depends how you are writing the script. You we will see that in the examples. So in the SQL joins, there is the left table; we have the `customers`, and the right table, it is the `orders`. In the `INNER JOIN`, it doesn't matter because in the results, once you are using `INNER JOIN`, only the matching rows will be presented at the result. So if you use `INNER JOIN`, you will exclude all those results that are not matching, and you will see as a result only the matching rows between those two tables. Okay, now to the second type of joins. We have the `LEFT JOIN`. As the name says, it is a left join, that means we are depending on the left table more than to the right table. So once you are specifying the left join in your SQL script, you are telling the database or SQL that I want everything, all the rows from the left table, and from the right table only the matching rows. So once you are saying, okay, left join, that's when you will find all the records from left and only the matching rows from the right side. So let's go to the next one. We have the `RIGHT JOIN`. It is exactly the opposite, so you are saying here in your SQL script, `RIGHT JOIN`, you are depending completely on the right table. So that means once you write that in your script, the SQL will present all the records from the right table and the results and from the left table only the matching record, only the matching rows. So it's really the way opposite as the `LEFT JOIN`. Then we have the last type of joins. We have the `FULL JOIN`. Once you say in your script, I want to have `FULL JOIN`, that means you want everything from both of the tables. That means from the left table, it's going to retain all the rows. From the right table, you will get as well all the rows. So with `FULL JOIN`, as the name says, it is everything. All right, so with that, we have an overview about the joins, and now before we start talking about the first type, the `INNER JOIN`, we will learn quickly about the SQL aliases. It's like a hidden tutorial, not on the roadmap, but we have to learn it before we start writing SQL joins. [Music]

Okay, so now before we start having some examples in order to understand and learn how to join tables using SQL, we have to learn very important things in SQL, and that is SQL aliases. You need to learn that once you start querying multiple tables in one SQL statement. So let's check this. If I want only to select the `customerID` from `customers`, this should not be a problem. So if I execute this, I will get all the `customerID`s. But once I specify multiple tables in one query, you need to tell the database which `customerID` in which table because, as you see in our example, we have the `customerID`s in two tables: in `customers` and in `orders`. And if you leave it like this, you will get an error where the database is going to tell you, I don't really understand which column do you mean? Do you mean the column from `customers` or from `orders`? That's why we need to specify one more thing near the column name, and that is the table name. So we're at `customers`.`customerID`, and with that you are telling the database, I want the `customerID` from the `customers`. So if I execute this, I will get as well the same result. There is no problem here, but you need to specify that once you are working with multiple tables. But the annoying thing here, if you just always like write the table name over here, it's going to be really annoying to write. That's why we're gonna work with aliases. So we're gonna give the tables like a nickname, and we call it in SQL aliases. Okay, so now in order to do that in SQL, we're gonna go just beside the table name, and we're gonna write down the keyword `AS`, then give the alias name or their nickname. So I'm gonna use the `C` instead of `customers`, and now the database understand, okay, in my script he is using `C` instead of `customers`, so I can go everywhere and instead of using the `customers`, I could say `C`. So if I run the results, I will get exactly the same thing. There is no error, but now as you can see, it is much easier to handle my script. I'm gonna just write `C.customerID` instead of the `customers.customerID`. So it's really an easier way to handle stuff, and I always tend to do that, so I really recommend to use aliases in order to have like smaller scripts. You could do as well the same for the columns. So, for example, we have here the `customerID`. I could go and rename that, and to do that it's the same stuff. I go right beside it and write `AS`. So instead of having `customerID`, I'm gonna write like `CID`. So let's run this, and as you see, SQL understood that, and he is printing at the result as well `CID`. So he understands I'm renaming this column in my result as `CID`. There is a very important aspect here to understand is that it's gonna rename that only in my script and in the results. The database will not go to the tables. I'm gonna rename the tables or gonna rename the columns. That is a different query to do that. So this command, the `AS`, it is only temporarily at my script and the results, so nothing is changing in the data model or in the database. It's gonna stay the table's `customers`, and the column name gonna stay the `customerID`. This is only like a tool to help you once you are writing SQL statements and as well to help you rename stuff very fast to have it as a result. Alright, so now we have everything to start with the first type of joins and the `INNER JOIN`. [Music]

Okay, so now let's start with the task. In order to understand how to write SQL statements to join two tables, we're gonna start with the first task. It says find all `customerID`, `firstName`, `orderID`, the `orderQuantity`, excluding those customers who didn't place any orders. So in this example, as you see, it is not only the `customers`; we need some columns from the `customers` tables and some columns from the `order` tables, and we have to join them in order to do that. So let's start doing that step by step using SQL. So first, we're going to start with the `SELECT`. Since in the task it is like specifying the columns, we will not use the star. So `SELECT`, we need a `customerID`, then the `firstName`, and the `orderID` and `quantity`. So now we need to specify the tables. We're gonna start from the `customers` with the `INNER JOIN`. Here it doesn't matter whether you are starting from left or from right, so I'm gonna start from the `customers`, and now in order to specify the second table, we're going to use the `JOIN` statements. So we're gonna say `INNER JOIN`, and with that I'm saying, okay, we're gonna join now the `customers` with another table. So we're gonna `INNER JOIN` the `orders`. With that, you are connecting two tables: the `customers` and the `orders`. As I said, you need to specify two things: the join type and the join key. We have already here specified the `INNER JOIN` because we don't need those customers that didn't place any orders, so we're going to use the `INNER JOIN` over here. And the second thing that you need to specify here, what is the join key? How are you going to connect those tables? You need to specify that for SQL in order to do that. So we're gonna now go to the new line and say `ON`, so the joining on those columns. So in order to specify the columns, I'm gonna give now only some aliases. So instead of `customers`, I'm gonna say, okay, I'm gonna call you `C`, and instead of all those, I'm gonna call you as `O`. So now in order to join those tables, we need to find out what is our join key, what which column here exists in both of the tables. So we can see the `customerID`; we can find it in the `customers` and in the `orders`, and it is the perfect column to join those tables. So we're gonna connect both of them with the `ON`. So I'm gonna say, okay, let's take the `customerID` from `customers`; it should equal to the `customerID` in the `orders`. So `O.customerID`. With that, I specify the rule or the key how the table is going to be joined. I said the `customerID` from the left table should be exactly the `customerID` from the right table, from `customers` and `orders`, and with that I specify the rule. I specified over here as well the join type, and with that we connected two tables. All right, so now before I go, I run this query, we still have one problem, and the `customerID` in the `SELECT`, I didn't specify from which table, and if I run it like this, we will get an error. You could try it, but now we need to specify which `customerID` I want. Is it from the `customers` or from the `order`? So in order to do that, we're gonna use the `C.`, the table name or the alias in order to specify, okay, I want the `customerID` from the `customers`. For the rest, you don't need to do that because it is a unique name like the `firstName`; it's a unique column name only on the `customers`, but I really recommend you once you are trying like to join some tables, it is a very nice way to document your stuff to say, okay, the `firstName`, it is from the `customers`, because with the time you could forget that, or if you don't understand or don't know the data model, it will be hard to understand whether, okay, this `firstName` and the `customers` or in the `orders`. So it is a really nice way to document that if you put just a table name or the alias at the starts. With that, you could see very quickly, okay, those two columns come from the `orders`, and those two columns come down from the `customers`. And one more thing to make it looks nicer, I'm just gonna use tab. So now we are ready, I think. Let's try to query that. So as you can see now in the results, we got the columns from both of the tables. So we have the `customerID`, the `firstName` from `customers`, the `orderID` and the `quantity` from the `orders`. Okay, so now let's understand what the database was doing once we executed the `INNER JOIN`. So first, it's going to select, okay, which tables do we need in the script? We have the `FROM customers`, so it can read the table `customers`, and then they have the join table `orders`. So that means the database is going to focus on both of the tables. Then it's going to define, okay, which table is left and which table is right. Since we have first the `customers` in the `FROM`, it's going to consider the `customer` tables as the left table, and then since we have the `orders` in the `JOIN` as the next, it's going to consider it as a right table. This is very important to do the joins, but since we are using the `INNER JOIN`, it doesn't really matter for us whether we use first `customers` or `orders`, but in the database it's going to follow the script. Okay, so now as the next step, the database is going to check, okay, which column do we need in our SQL script statement? We said we need only the `customerID`, `firstName` from `customers`, and from `orders` we need the `orderID` and `quantity`. All right, so now as the next step, the database is going to check, okay, which rows should be presented as the results, and here is like the most important thing. We are using now the `INNER JOIN`, that means that database should present only the record that is matching. So in order to do the match, it needs like the key column for the join, so we specified and said, okay, you need to check the `customerID` between those two tables. So let's go through that. The first `customerID` one, we have it at the `customers`, and as well we have it as records in the `orders`. So that means there is a match between those two tables, and this customer will be presented. So here we will get the `customerID` one, `firstName` Maria, and her order was 1001, and we have this quantity. So here we have the whole records of Maria from both of the tables. So we go now to the next one. We have John. John present as well as a `customerID` 2 in that table `order`, so there is a match and it will be presented as well in the results, and his order is 1002, and he has this quantity. So it can proceed in the third customer. The third customer exists in both of the tables, in `customers` and `orders`, and it will be as well listed and our results, and his `orderID` this with quantity 500. So now it comes to the `customerID` 4. The `customerID` 4 exists only in the `customers`, and we don't hide it in the `orders`. That's why there is no match, and the database can ignore this customer, and it gonna proceed as well over here. It's gonna check, okay, we have the `customerID` 5; it is only as well exists in the `customers` and not in the `orders`. There is no match. We have one more thing that we have `customerID` number six over here. We have it only on the `orders`, but we don't have it in your customer, so there is no match. So with the `INNER JOIN`, only if the customer or if the key exists in both of the tables, it gonna be presented as a result. Alright, so that's all for the `INNER JOIN`. Alright, so that's all for the `INNER JOIN`. Next, we're gonna talk about the `LEFT JOIN`.

Okay, so now let's go to the next tasks, and we have the following: find all `customerID`, `firstName`, `orderID`, `quantity`, but include those customers who didn't place any orders. So for us, that means we need to see at the results of the `customers`, not only those customers that did place an order, but all the customers. In order to do that, we're gonna use the `LEFT JOIN`. So we're gonna have exactly the same query; there is nothing has changed, the same columns, the same tables, but instead of saying `INNER JOIN`, we're gonna work with a `LEFT JOIN`, and saying `LEFT JOIN`, that means okay for the SQL, it's gonna list all the `customers`. So let's see what's gonna happen if we do that. Let me make it bigger a little bit. So as you can see here, as I said `LEFT JOIN`, we have all the informations from the `customers`, and only the magic ones from the `orders`.

All right, guys, again, let's understand what the database was doing once we executed the `LEFT JOIN`. So that way, it's going to focus on the `customers` and the `orders`. The database understand, okay, `customers` is the left table because it comes as first with the `FROM`, and the `orders` is the right table because it comes in the `LEFT JOIN` in the query as a second. So after that, it can specify, okay, the columns again. We have the `customerID`, `firstName`, `orderID`, and `quantity`. And so now it's gonna start doing the matching. I'm gonna check, okay, which join type would we have? We have the left. So since we said, okay, it is the `LEFT JOIN`, the database is going to say, okay, I need everything from the left table without doing any matches. So we need everything, so it's gonna list all the IDs and as well all the names without checking anything. But from the right side, we need only the matching records, so it's gonna really check each one of them. So here `customerID`, it exists and the `customers`, so it's gonna take it and put it as a result. So now for `customerID` 2, we have as well one; it's gonna put it at the results. The `customerID` 3, there is matching, but now for Martin, he doesn't have any orders, so the database is going to show `NULL`s instead of that. `NULL` it means like an empty; there is no value found or unknown. And for Beta as well, there is no `customerID` with the number five, that means there is nothing at the right side; we will have as well and empty. So this is how it looks once you execute the `LEFT JOIN`. You will get everything from the left and only the matching from the right. If there is anything missing, it's gonna put `NULL`s. All right, so that's all for the `LEFT JOIN`. Next, we're gonna start talking about the `RIGHT JOIN`. It is very similar to the `LEFT JOIN`.

Okay, so now let's jump to the next one. We're gonna talk about the `RIGHT JOIN`. We have the following task; it's almost the same: find all `customerID`, `firstName`, `orderID`, `quantity`, but this time include all orders regardless of whether there is a matching customer. That means for us, okay, we need all the orders from the right table, from the `orders`, and in order to do that, we have the same setup over here in SQL; we just need to change the type of join. So we're gonna

Write here right once you do that, you are controlling, okay, how the database is going to match and gonna present the results. We will have the same setup over here; will not change anything, and let's run this. And with that, you can see, okay, the database did list all the orders from the order table and from the left side only the matching customers.

Okay, so as usual, let's see what the database did once we executed the right join. We have the same setup: customers is a left table, orders is the right table, and we have the same column as well: customer ID, first name, order ID, and as well we have the quantity. But now here the difference is that we said it is a right join. So, in order to do that in SQL, it's gonna like present all the results from the right table without checking whether there is a match with the lift. So the database is going to select everything from here, so all the orders and all the quantities without checking anything from the left side. So now, from the left side, it's gonna only present what is matching; so it's gonna check, okay, do we have customer ID 1? Yeah, we have it, so it can present the result over here on the left side. Do we have customer two? We have it as well. Customer three, we have George over here, but now we don't have a customer number six; that means it's gonna be null again, so it's gonna be empty. We don't have a customer with the ID six in the customer tables. So with that, we presented everything: all the orders from the right sides and only the matching informations from the customers.

All right, everyone, so that's all for the right join. Next, we're gonna start talking about the last type of joins: the full outer join. All right, let's move to the last one. We have the full join, and we have the following task: list customer ID, first name, order ID, quantity, but this time include everything: all orders and all customers.

Okay, with the full join, I have two things to say. First is that the full join is only supported in some databases like Microsoft SQL. So with MySQL, Oracle, you could not use the full join, but instead of that, I'm gonna show you some work around how to do full join with MySQL. So don't worry about it, but we need to twist some stuff in order to create the full join. If you are using Microsoft SQL, you can just go and say full join. The second thing is that full join has sometimes bad performance if you have big tables. So try really to avoid using the full join in my projects. I always tend to use like inner join, left join, right join, or full outer joins. I really try to avoid using that. Full join has really bad performance. So if you have small tables, it should not be a problem, but once the table gets like big, the full join is gonna be really slow because you are saying, okay, give me everything from left, give me everything from right, and that has sometimes bad performance. So try to avoid that.

So now the question: how we gonna do full join if we don't have in MySQL a full keyword? In order to do that, so as I said, we're gonna use some work around. So following on this, so a full join is actually is a combination between a left and right: left join plus right join. So what I'm gonna do, I'm just gonna go and duplicate this script, so we have twice the same query, but when we say left join and in the other we say right join. As the next tutorial, we're gonna talk about how to combine two statements in one. So in order to do that, we will use the keyword Union. So once I put Union, I'm just like adding two statements in one. So here I'm saying, okay, give me all the results from the left and combine it with the result from right. And if you execute it, you will get exactly the same result as the full join. So with that, you could see, okay, here I have all the customers and here as well I have all the orders, so we have here a full join.

All right, guys, so now let's see what additive is done once we executed the full join or the scripts that I showed you with left Union right. So we have the same setup: customers, orders, and we have those four columns. So since it's full join, that means all the records from left and all the record from right. So it's gonna start from the left; we will have all the customers and all the first names, and then it's gonna start matching on the right side. So Maria, it has this order and this quantity. Customer ID has this order and this quantity. The three, we have this ID and this quantity. But for Martin and Peter, we don't have any orders from them, so we're gonna see nulls over here and null over here. But there is still something missing: that we don't have all the orders over here; that's why the database is going to go and present this order ID and this quantity. And with that, it's gonna match on the left side; it says, okay, there is no customers on the left side, and it's gonna put over here some nulls. So with that, you got all the customers and you have all the orders that is matching for them and the way around. With that, you have all orders and all customers using the full join.

All right, guys, so with that, we have learned all different types of joins. Next, we're gonna talk about a similar concept: it is the union and Union all. [Music]

Right, so now we're gonna learn how to combine tables using Union. Union is very important tools in SQL in order to combine tables and very powerful. So previously we have learned how to combine tables using the join methods. So what we are doing in Joins: we have two tables, customers and orders, and we are joining the columns together to add the results. We're gonna get one big table, one wide table with all the columns from left and the from the right. But with Union, we are as well like combining two tables, but instead of combining the columns here, we're gonna combine the rows together. So here we're gonna get very long table including all the rows from the left and from the right, but having the same columns. So we will not get all columns from left and right; instead of that, we will get all the rows from left and all the roads from right. Okay, so now in order to understand the union, we're gonna have the following example. So in our tutorial database, we have two tables: we have the table customers and we have the table employees. So now we have the following tasks: make a list of all persons from customers and from employees where we have the first name, last name, and the country. So that means doesn't matter whether the person is a customer or employee; we're gonna have make a list with everything. So in order to solve these tasks, we're going to use the union operator between two tables: customers and employees. So if we check this closely, you will find those three informations in both of the tables. So we have first name and customers; we have as well the same in employees. Last name and customers, last name employees, and we have the country and employees and the same as customers. This is very important that we have the matching columns from both of them. So the database, if we start the union between both of them, the database can select the columns only from the left table. So we will have first name, last name, and Country, and we will not have here again the same columns from the right one. It's not joined; it is a union. So the left one gonna decide what are the column names. So this is as well very important. So the database is going to go and select everything from the left table and put it as a result, and it's gonna do the same for the right one. So that for the employees, you can select all the records and put it over here, and with that we have a full list of all persons from customers and as well from employees in one results. This is very important that both of the tables at the SQL query should have exactly the same number of columns and as with the same order. So if we are doing like in the employees first the last name then the first name in the results, we will get that switch as well. So be careful with the order of columns, and the number of columns should be matching between left and right. One more thing is very important is that there is two types of Union. Time number one: that is the union all, where we're gonna get the result exactly like this. So that means if there is any duplicates between the table one and the table two, those doubly gets gonna stay at the result. So there is no check the uniqueness of the results. If there is any like person on the left and where the same person otherwise nothing can happen; we will get the whole results. But if you wish to remove those duplicates, so if you check the results over here, you can see John; he is customer and at the same time he is as well employee. So this could happen, yeah. So in order to remove such a like duplicates, we could use the other type of Union, and that is only the union without Union all. So I'm gonna show you that once we are writing the SQL statements. So this is as well very important to understand. And then you Union, if you want to have that duplicates like exactly like the data inside the tables, then you should use union all. If you want to remove the duplicates, then use Union.

So now let's see how we're going to do that in SQL. So this is really easy to do in SQL. All what we're gonna do is that we're gonna write two queries: one for customers, one for employees, and then just put Union between them, and we're gonna get the results. So let's try building the first one. So select first name, last name, and we need the country from customers. So this is the first query; let's just execute that and see. Okay, now I have a list from the customers, and then we're gonna write that again for the employees. So select in employees; we have as well first name, last name, and amp country from employees. So let's run the query and see. So now we have the list from employees. So as you can see, we have now two queries: one for customers and one employees. In order to do the union like to maintain all the duplicates as well, we're gonna write the keyword between them: Union all. So now we're gonna run the whole thing and let's check. So with that, we got all the first name, last name, country from both of the tables: from customers and employees. And as you can see, this list contains duplicates because, for example, John is in customer as well in the employees. So if we wish like to remove such a duplicates between in customers and employees or other results, we just remove the all from here; we just use the union. So let's run that again. So now we're gonna get a unique list of information, so John can only happen once over here. So this is how we're gonna do it in Union. One more thing is about how to control the column names. So as you can see, the first name, last name, country, this comes from the query above. So this query over here is going to control the naming of our table. So if you wish to have like different column name, so don't change it over here because nothing can happen; database is going to just ignore it. So here we're gonna control the name. So if I wish to add, for example, let's say person first name and here person last name and here comparison country, and we rerun the query, as you can see, we have the names over here. And if you change anything over here in the query below, nothing can happen. So let's have first name. So let's run the query; you see nothing can happen. So now let's test few things over here. So if I just make a problem where I'm gonna have first we have the last name and then comes the first name, so it is the opposite as the first query. So let's run this; as you can see, the database will not notice that we have here mistake or we have problem where we have above the first name, last name, and then here we have last name then first name, because the database doesn't care about that; it only cares that both have the same data type. Like since we have here VAR character and here we have fire character, it could present the results. So for the database, it doesn't care about like whether you are doing it rightly or not; the column name don't say anything for it. So that's why be careful about the order of the columns when you are doing the union between two tables. So now if we go and try another data type, for example customer ID, so customer ID is integer and the first name over here is VAR character. So if I run the query, we will get an error because I think it's hidden over here because there is mismatching between the data type; the database cannot lie combine strings and then after that we're gonna have integer; that's why the data type is very important for SQL. So let me just repair everything and run. So now it works because the data type is same. So let's try some other errors; I'm just making things broken. So above we have three columns: we have first name, last stream, country, and we have here the same. So if I have like different number of columns between the two of the tables, let's say have salary. So now we have four columns in one SQL and the other we have three. If I run this query, we will get us again an error because it's gonna says you have different number of columns between those queries, and we can not do the union; that's why the data type is very important; the number of columns is very important, and as well the order of the columns should be matching.

All right, everyone, so with that we have covered the SQL joins, and now you know how to combine SQL tables together. And in the next chapter, we will learn many important SQL functions, and we will start with the aggregation functions. [Music]

All right, so so far we have learned how to get, how to retrieve our data out of our database and tables, but in real life scenarios we will be doing a lot of calculations, aggregations on top of the data in order to get something meaningful of it, in order to get some useful informations of the data. So in SQL projects, we tend to use a lot of aggregations in order to understand the data because we have in the data model sometimes like big tables, and just reading the raw data we will not get any user information of it. So we have to do some aggregations on top of it in order to understand the data. So that means understanding the SQL aggregate functions are very important and very essential in learning SQL in order to get some informations out of the data. In SQL, we have the following aggregate functions: they are really easy. So if you just read the function name, you will understand what SQL gonna do once you execute those functions. So the counts, it can return the number of rows in a table. Sum, it can summarize the values. We have the average, then we have Max, Min to return the maximum value and the minimum value. I will go through all of them, explain that step by step with examples as usual, but here it is very important to understand how each functions cannot deal with the nulls, those empty fields that we don't have a value, because each function is going to deal with the nulls differently.

All right, so now let's start with the first function we have; it is the count. It is as well the easiest one with that we have in the aggregation functions. In many situations, once you are working like let's say new project, you have a lot of tables; the first thing that I tend to use is to see, okay, how many like customers do we have, how many orders, how many let's say employees we have, depend on the table. So I usually always check that to see how many Rico words do we have in each table; is it like big table, is it small table? So if we have the following task that says find the total number of customers in the database, okay, so let's solve that using SQL. First, I want to get like all the data from the table customers; we usually do that using select star from customers. So that is easy. Now we can see, okay, we have five customers at the table, but the task it says find the total number of customers; that means I want to see as a result only the number five, the total number of customers. So in order to do that, we're going to use the function count. So after the select, I'm gonna type here the keyword count, open brackets and close brackets, and inside the account you could specify either star or the name of the column. So let's see with the star and execute that, and as you can see now we got like 5 as the row numbers of customers in the table. So here we have now counted how many customers do we have, but as you can see here the name of the column I don't really like it; it's like the function name. So let's rename it for the results as total customers. So let's re-execute that, and now it looks better. So the total number of customers we have it as five. As I said, we could use here like star or column name. So this is the easiest way to do a count on the table using the star, but if you now include the column name, it's going to be a little bit more tricky because of the nulls. So let's see what's gonna happen if I type over here customer ID and run the query; we will get the same informations like five. But if I bought over here not the customer ID but the score, and you will see we have now four. So here we have like four scores; we don't have like five customers. So what happened over here? So now let me explain you what the database is doing once you say count star or count a column. If you say count star, you are not specifying any column; the database is going to go to the table and gonna just count how many rows we have in the table. So the database is gonna count one, two, three, four, and five. So we have five rows in the table, and as a result you will get five. But if you say, okay, count score, if you put the score inside the count, the database is going to count how many values do we have inside the score, and it's gonna ignore the nulls, and here it is the problem or like let's say the tricky part. So if the database is gonna count how many scores do we have, it's gonna count only four. So in order to count how many customers do we have, either you're gonna say, okay, count star, or you're gonna like count how many customer IDs do we have, and you will get the same results; you will get five. But if you are counting like a column that contains nulls here, you will have like fewer records in the results, like the score; we have only four; with the ID we have like five. Okay, so now let's move to the next one; we have the sum. Unlike the count, the sum works only on the columns that contain numbers. For example, you could do the sum on the customer ID because we have numbers inside it, on the score, on the quantity, on the order IDs, but you cannot sum the first name or sum the last names. With the count, you could do that on any type of columns; like you could do count first name, count countries, and so on. So with the sum, you deal only with numbers, and one more thing: if you have nulls, the sum can deal with it as a zero, so it will not ignore it; it's gonna deal with that as a zero. So let's have the following task: find the total quantity of all orders. So that means we're gonna focus on the table orders, and we're gonna summarize all the quantities of all orders. It's really easy; let's do that. So first of all, I would like always to start with the star from orders, and let's run this. So now I have here the table orders, and we're gonna focus on the quantity, and we have to summarize it. So in order to do that, we're gonna use the keyword sum, open brackets, and now type here quantity, close brackets, and run this. So with

That you got the total number, or the total of the quantity. We summarized all the rows in one cell. So here, as usual, we have this ugly name over here, so we're gonna rename it "some quantity," run it again. So now we have a better name at the result. So the sum of the quantity, we have here 2650. Okay. So now let's move to the next one. We have the average. The average is one more aggregate function in SQL, and you could use it in order to find the average of one column. It is almost the same as SUM, so it works with the columns that has some numbers. It will not work the average if you use it on the first name or last name; there are characters, so only on the numbers. But the only difference is that's how average gonna deal with the NULLs.

For example, over here we have the NULL in the score; it will not consider it as a zero as a SUM, but it will ignore it completely. Because if it's considered as a zero, it's gonna be a really problem using the average function. So in average, the NULLs will be completely ignored. So let's have the following example, or the task: find the average score of all customers. So let's try to solve that. We will be focusing on the table "customers," as usual. I'm just going to select everything to check the result over here. So we need the column "score," and we need the average of those values. So in order to do that, we're gonna write the keywords AVG(open brackets and then the column name and close brackets. So let's run this. So with that, you got the average score of all customers. The NULLs are ignored, and I like to rename it, so "avoid score," run it again. So it looks better now. We have the average score 625. All right. So now we're gonna move to my favorite aggregate function. We have MIN and MAX. I use it a lot once I'm doing like data profiling in order to understand my data.

For example, if I am profiling or like checking the table "orders" for the first time, I will be interested in what is the latest date, or what when was the latest order date. So in order to do that, we could use the MAX function on the order date, and we're gonna get the latest value. Or, for example, I'm gonna check, okay, which customer has the highest score? So I could go to the score and do the MAX function. So the MAX and MIN, it's like the COUNT; you could use it in any type of columns. So you could use it on numbers, on characters, on dates; it's gonna work. And here about the NULLs, it's gonna be ignored. So if you are going to say, okay, what is the minimum value on the score, you will not get the NULL; you will get 350 with Maria. So let's have some example and tasks in order to understand how to work with MIN and MAX. All right. We have the following task; it says find the highest score, the maximum score in our customers table. We have the same table over here, so I'm going to remove the average, select the data. So I want to get the highest score; this should be John. In order to do that, we're going to use the function MAX(open bracket score close brackets, and run this. If we do that, we're gonna get the 900s, and that is true. Just gonna rename that column. So let's run that again. We have the max score as 900. So let's now find the lowest score. So the lowest score over here should be with Maria, 350. In order to do that, we're going to use the function MIN on the score as well. We change the name just to look better and run that again. So with the MIN score, we're gonna get the 350 and not the NULL. So this is very important.

All right. So now let's keep playing with the data. Let's check the order. So I'm gonna get the earliest date on the order date and the latest. So let's try to do that. I'm just gonna remove that and select the table "orders." So now we want to get the earliest dates and the maximum date, or the latest dates from the column "order dates." In order to do that, we're gonna use the function MIN(open brackets order date and then close it, and just rename it for the results "main order dates." So let's run this, and with that we got the minimum dates and the order date. So this is was the first order date in the table, and let's get now the latest one. So in order to do that, I'm just gonna change the function to MAX and just change the name of it for the result and see. So this date is the latest date that we have as an order. All right guys, so with that we have learned all the aggregate functions in SQL. They are really important for data analytics and data science. Next we're gonna cover the string functions, where we're going to learn how to manipulate the text data. [Music]

So as the next, we're gonna learn how to clean up our data using the SQL string functions. In many cases, if you are working with a big database, you will have a lot of columns that includes values like text or characters; we call it string, and the data quality inside such columns might be sometimes bad. So you will be ending up needing some functions in order to manipulate the structures of those values. So in SQL, we have the following SQL string functions: we have the CONCATs in order to connect two strings in one value; the LOWER and UPPER in order to transfer the data to a lowercase or to uppercase; TRIM, if you have some white spaces at the start or the ends of the value, you could remove them; the LENGTH in order to calculate the length of the character or of the value; then we have the SUBSTRING in order to return a sub part of the string. All right. So now we're going to have some tasks in order to understand how to work with those string functions. The first one it says list all customers' names where the customer name is the combination between first name and last name in one column. So let's try to do that. So we need the list of all customers' names; we have here first name and we have as well the last name from "customers." So if I execute this query, I will get the following. So we have now a list of all customers' names, but now we didn't solve really the tasks, because the task says we want to have customer name where the first name and the last name in one column, and as you can see here we have it separated in the database. So in order now to connect those two strings in one, we're gonna use the function CONCATs. So let's see how we're gonna do that. So we need the keyword CONCATs(open brackets, and here we're gonna list the first column "first name," comma "last name." So I'm gonna remove those here, and let's see the results. So as you can see, okay, now we have the first name and the last name together in one column. So if we want to separate them as well from each other, we could use one more string. I'm gonna put the minus between them, so I'm now connecting three strings: the first name, minus this is from me, and then the last name. So let's check how it can look like. So as you can see, "Maria - Minos Kramer." So with that we have like a list of all customers' names with the first name and the last name on it, but I just want to rename it as well to the customer name. Let me make it smaller. All right. So let's create that. As you can see now, we have a column called "customer names," and we have exactly the information that we need. So if you want to connect like two strings or more strings, you could use the function CONCATs.

Another task that might be, okay, I want all the first names to be in uppercase or in lowercase. So let's see how we can do that. So now we're gonna remove this, and we're gonna now transfer the first name to uppercase. So if I just query now the first name, you can see it is not uppercase; it starts with Big M, then the rest are small. So in order to convert everything to uppercase, we're gonna use the keyword or the function UPPER(brackets close it, and I'm gonna rename it to "Upper first name." Let's run this, and as you can see now all the names now with the uppercase. You could do as well the same with the lowercase. So I'm gonna use now the function LOWER "first name" as "lower's name." So let's run this, and as you can see now I transferred the string from like the uppercase to the lowercase. One more thing to notice here, so any changes that now I'm doing in the query, it will not update the content of the table; that means the first name gonna stay like before, that's Maria with the first character M and there are small. So now we are just changing or transforming the data at the result set that I'm getting as output, so nothing gonna change on the table unless we do some updates. We're gonna learn that later. So now we are just transforming the data for our results. Okay. So now let's talk about the TRIM. This is interesting; sometimes in the database you might find something like this, like the name Maria, and before that we have an empty space. So someone before like entering the name Maria, they entered white space before that; it happens, or at the end someone intertwines white space; usually this is like bad data and we have to remove it. So in order now to work with that in our query, we could use the function TRIM. For the left one, we call it the left space; for the right one, we call it the right space. So in order to remove the left spaces from the name, we could use the function LTRIM, that means left trim, and if you execute that, this white space will be removed from the query, from the results. And if you have on the right side you have as well white space, you could use another function that is called RTRIM, that means right trim, and if you execute that, it's going to remove any white spaces at the end of the string. And if you have the situation where you have both, so either you're going to apply LTRIM and RTRIM, or you can use the function TRIM; TRIM is going to remove both of the sides, the LTRIM and the RTRIM, and you will not have in the results any white spaces at the string. Okay. So now let's have some examples to learn about the TRIM. So if you check our tutorial database, you might already find out there are some white spaces around. So if you check the table "customers," exactly in the last name, you will find here some leading, or some left white spaces. So let's query that and check that: SELECT "last name" FROM "customers." So now if you check the results, you might find, okay, there is here left at left white space, but I have here for you a tip in order to find all those white spaces that are hidden. So for example, we have as well at "Kramer" as well white space, but you cannot see it if you check the results. So I would say just copy the value and put it at the editor. So if I put it at the editor, you could see there is like a right white space. And let's check all the values; let's see, still is clean, so there is no white space around, and "Pips." Can I remove those? So "Pips" has like a left toy space and a right white space, so we have to repair that now. "Muller" Mueller is safe; we don't have white spaces around; "Ranking" as well, I think the same; yeah, we don't have white spaces. So let's try to repair that; we're just gonna use the function TRIM. So the keyword TRIM(brackets as usual, and I'm gonna call it "clear clean last name." So let's run the query and check the results. So let's check whether there is any white spaces around. So as you can see, it's clean. Let's have another example; our "Pips" as well clean, so we don't have any left white spaces or right white spaces. You could use the function TRIM in order to remove them. Okay. So now let's move to the next function; we have the LENGTH. If you want to calculate how many characters do we have in one string, you could use the LENGTH function. For some reason, if you want to calculate how many characters do we have in the last name, we could do it like this; I'm just gonna extend our query to calculate that. So in order to do that, you're going to use the keyword LENGTH, and inside it we're gonna put the last name to calculate how many characters do we have there. I'm just gonna rename it to "Lin last name." So let's run the query, and you can see the database already calculated how many characters do we have in the last names. So you might already noticed it is not really true, because we have here "Kramer"; it's only six characters, but the database is showing seven, and that's because we have white spaces. So this is a really nice way in order to find out whether there is white spaces or not. So in order now to clean that, you could like merge those two functions in one. So I can put first the TRIM inside the LENGTH. So first I'm cleaning the data, and after that I want to calculate the length. So in order to do that, I'm gonna make a new column. So first I'm gonna TRIM the last name, and after that I'm gonna apply again another function LENGTH. So I embedded two functions in one, as let's say, let's call it "clean... it's getting long name, but anyway, let's see the results. And as you can see now, we have the clean length of the last name. So we have exactly here six, five, and as you can see here there is like two white spaces, and those names don't have any white spaces because we have exactly the same number of characters. Okay. So now let's move to the last string function that we have; it is the fun one, SUBSTRING. So let's say we have in the database the following name: we have Maria; each character in the database has the position; for example, M is one, A is two, R is three, and so on. And if I want in the query to subtract this name and I just want a part of it, I could use the function SUBSTRING. So the SUBSTRING had the following syntax: I need to define inside it the column name or the string, then the start position and the length. So let's have the following example: if I say I want to SUBSTRING Maria starting from 2 and the length is 3, so we have here two pointers: the first pointer is where to start, so we're gonna start with the position 2, so it's gonna calculate one, two, and this is our starting position, and from this point we're gonna like calculate three steps, so here we set three as the length or steps, so one, two, three. With that we have like starting point and ending point for the substring. So if we execute this query over here, you will get as a result "aria." Okay. So now let's have some live example; we're gonna like apply the same rule on the last name. So I'm gonna remove the old part over here. So I'm gonna write the same function, so SUBSTRING, and we need to define now the column name; it's the last name; the starting position is 2; the length or how many steps is 3. So let's call it "sub last name," and let's run this and see the results. So if we check the result now, we can see that we don't have the whole last name, but only a part of it, because we defined the substring on it. So instead of "Kramer," we have only "ram." So it started with the position 2 and we cut three characters: r, a, m. From "Steel," we started with T and we have "ee." All right everyone, so that's all for this chapter. We have learned many important SQL functions, and now in the next chapter we will raise the level again by learning advanced topics in SQL, and we will start with the GROUP BY clause. [Music]

All right guys, so so far we have learned how to aggregate our data using SQL aggregate functions. For example, if you want to get the total number of customers, you're gonna go and use the COUNT(*) on the table "customers," and you're gonna get five. So sometimes this is not enough; sometimes you need to group up the rows by a column value. For example, we don't want to get the total number of customers of the whole table, and instead of that we want to get the total number of customers by the country values. For example, I want to see how many customers we have from Germany, how many customers we have from UK, USA, and so on. So here we are grouping up those customers by the country values, and in SQL, in order to do that, we're gonna use the clause GROUP BY. All right. So now we have new clauses in our query, and as you know SQL is very sensitive about the order of those clauses, so we have to follow the rules here. We cannot go and say, okay, let's start with WHERE, then SELECT FROM; no, we have to follow the rules. So we start to SELECT, FROM, JOINs, WHERE, and the GROUP BY it comes always after the WHERE. So we cannot place it before the WHERE. So if you have any filter, you should do the filters on the tables, and then comes the GROUP BY as well. GROUP BY it is optional; it is not the must clause; it's not like SELECT FROM. So if you need GROUP BY, you're gonna include it, but after the WHERE; this is very important. Okay. So now in order to understand the GROUP BY, we're gonna have one task and try to solve it using SQL. Let's go. So the task says find the total number of customers for each country. So that means we need to group up the customers by the column "country." So we're gonna build this step by step. So we're gonna start with the SELECT * FROM "customers," just to check what we have in the "customers," as usual. So now we need to count how many customers do we have, and with that we learned we're going to use the function COUNT, and we're gonna close it like this. I'm just gonna rename it as "total customers." So let's run this. So now we have the total number of customers, five, but now we want it to be divided on the countries, to group up by the country. In order to do that, we're gonna use the clause now GROUP BY. So GROUP BY keywords, and after that we're gonna name the column that we want to group out by. So in our example it is the column "country," but this is not enough; we want to include it as well as the SELECT statement. So in order to do that, let me just SELECT as well that "country." So with that we say, it's okay, I want to count the total number of customers together with the country, and then group it by the country. So let's run this, and as you can see now we have not only the total number of customers, we have as well the country, and the customers are grouped up by the values of the country. So in Germany we have two customers, in USA we have as well two customers, and in UK we have one customer. So with that we did the total number of customers by specific column. All right guys, so now let's check step by step what the database done once we executed the GROUP BY. So first it's going to ask us, okay, which table do we need? We have the table FROM "customers," so it's going to focus on the table "customers," and then says, okay, which columns do we need? We need the column COUNT, and then as well the new column "total customers." All right. So now after that it's gonna check, okay, there is GROUP BY and COUNT. So with the GROUP BY, what SQL gonna do, it's gonna go to the column values in the "country" and only list the unique value, that distinct values that it finds inside the "country." So it's gonna go one by one, okay, Germany, it's gonna be listed over here, USA, UK, but it will not list again Germany because we have it already in the list, and USA we have it as well already in the list. So it's gonna go and aggregate all the rows for the column Germany, so it's gonna see, okay, for the column Germany we have it twice, so it's gonna type over here, let me just do it like this, two; then it's gonna go to the next column, okay, how many

USA customers? Do we have it's gonna count one and two. I'm gonna put as well over here too, and then for the last value, it gonna group up or count how many customers we have for UK, and we have exactly only one. So that's how the SQL works and why we get these results. Okay.

So now we could extend our task and we say, I want the same results, but the total number of customers should be sorted with the lowest first, then the highest. So in order to do that, we're gonna use the `order by`, and here it's very important that the `order by` comes after the `group by`. So `order by`, we are ordering by the `count(*)`, so the total number of customers, and here you could use the `*` or without it because it is the defaults. So let's execute this, and you can see, okay, the result is sorted now by the total customers where the lowest fares and then the highest. Okay.

So now let's add another example for the `group by`, and the task says find the highest score for each country. So this times we don't need the `count` function; we need the `max` function, as you notice already with the `group by`, we need always those aggregate functions, but it is not a must. So let's try that in the scratch. So `select * from`, oh, let's make it big, `customers`, and we want now the the highest score, so we're going to use the function `max` (our column is `score`), and we're gonna rename it `max_score`. So this is not enough because if I execute this query, I'm gonna get the highest score of all countries, but this time we need to like group it by by the country. So in order to do that, I'm gonna list in the `selects` the `country`, and let's make it more beautiful, and then use the clause `group by country`, so that I'm finding now the highest score for each country. So let's run this, and with that you can see, okay, the highest score in Germany is 500, the highest score in USA is 900, and for UK is 750. Okay.

So let's check what the database done. We selected the table `customers`. We said we need the column `country` and a new column called `max_score`, and in the SQL we have the `group by` of countries. So that means the database is going to go and select all those values and put only the unique values, so that means Germany, USA, and the UK. Then it's gonna start like finding the max of each those countries. So it's going to select first for the Germany; we have two rows, customer four and one, and it's gonna find the maximum value of those two values, so 350 and 500; it's going to select this value in the results because it is the highest. Then it's gonna select for the USA the two records over here, so we have USA over here and one here, and the max value of those two values, 900 and null, it's gonna be the 900, so it's gonna put it at the results. For the UK we have only one record, so the max value is going to be the same, so it's going to be the 750, and that's how the database build up this results from our query. All right everyone, so that's all for the `group by` clause, and next we're gonna talk about a related topic; it is the `having` clause. [Music]

Alright, so so far we have learned how to group up our data using the `group by` SQL clause, but sometimes you might be in situation where you are working with really big table where you have in one column many different values. In our example we have only three values; it's just to make it simple, but in real world scenarios you will have really a lot of values in one column, and you will be first to use some like filters on the results. So in order now to filter the results that we have from the `group by` in SQL, we have one more new clause, and that's called `having`. Alright, since this is the new clause, we need to understand where we're gonna place the `having` clause, because as you know, SQL is sensitive about the order of those clauses. So we will have the `having` clause exactly after the `group by`. So once you define the `group by`, after that you're gonna define the `having` clause, and it is as well an optional. Once you want to filter the aggregations functions, you could use the `having` clause. So with that we have all the clauses about the `select` statements or the query, so it starts with `select`, `from`, `joins`, `where`, `group by`, `having`, and lastly we have the `order by` and `limit`. Okay.

So now in order to understand the `having`, we're gonna have one task, and we're gonna try to solve it using SQL. The task says find the total number of customers for each country, but include those countries that has more than one customer. So that means we have here like a condition to filter our data. So let's try to solve that using SQL. So as usual, we're gonna start with querying our data; we're gonna focus on the table `customers` over here. So now we need now to have the total number of customers by country; that means I need to do `group by` and use the aggregate function `count`. So like before, I'm gonna use the keyword `count(*)` and rename it to look good at the results. So `count(*)`, we call it `total_customers`, and since we're gonna `group by` by country, we have to include the `country` as a `select`, and after that we just gonna like `group by` the `country`. So let's run this; we see at the results we have now all the countries, and we have the total number of customers, but our task is not solved yet because we still have a country where its total number of customers is not greater than one. So we need to filter this data. In order to do that with the `group by`, we're going to use the clause `having`, and think about it; it is like exactly like the `where` clause; we're gonna write down one condition. So our condition says the total number of customers should be greater than one, so the total number, that's mean the `count`, should be greater than one. So we have defined our condition; it's exactly like the `where` clause, and let's run this, and as you can see, we don't have now the UK with the one customer; we have now all the customers aggregated by the country, and only the country that has more than one customer in their result. With that we filtered our data, and we have exactly what we want. All right.

So now you might be wondering, and you want to ask me, Bara, why do we have such a clause called `having` in SQL? We could just go and use the `where` clause, because there we could filter our data; we could define exactly the same condition and we filter our data. Why SQL has one more function or clause that does exactly as `where`? So the answer for that is `where`; you could use it only on the columns that exist in the database. For example, if I want to filter the `country` or I want to filter the `score` or `last name`, so any columns that I have in the database, I could filter it with the `where`, but once I want to filter the data based on a column that doesn't exist in the database, for example the `count(*)` or the `max`, `min`, so any aggregated function that we are using in the query and we want to build up like a filter on top of such a function, then we cannot use the `where`; we should use `having`. So `having` only works with the `group by`. Once we are doing aggregation, we could define here a filter on top of it, but the `where` clause works only on the columns that we have already exist in the database. So that means if I have this results and I want to filter the data where I don't want to see the country USA at the results, I should use the `where` clause. So let's do that; the `where` comes after the `from`, so `where` our column is `country` not equal to USA. So let's run this, and with that you see, okay, we have filtered the data; we don't have the USA at the results. So if I want to filter the country, I need to use the `where` clause; if I want to filter the aggregate function or the `group by`, I have to use the `having`. All right guys, so with that we have covered the `having` clause, and next we're going to talk about the concept of subqueries in SQL, where we're gonna cover `exists` and `in` and learn the differences between them. [Music]

Alright, so now we're going to learn about how to do subqueries using SQL. This is extremely powerful in SQL. Once you learn how to do the subqueries, you will be able to do a lot of complex and important tasks using SQL. So what is a subquery? It is like you have different queries that are nested to each other, that as you have one query embedded in the other query. So in the neural situations, in the previous tutorials, we had only one query, one statement that is querying our data, for example the `customers`, but with the subqueries you will have different queries that are depending to each other. For example, we have here query number one that asking the data from the table `customers` and then present their results, then we will have another query, query number two, that is depending on the results and making, let's say, another `select` statements. With that we're gonna call the query number one as a subquery. So this will be the basis for the next query that we have. So with that you could do really nested queries, not only two, maybe three, four, and so on. So with that you could do nested queries and not only one. All right.

So now we're gonna learn how to do subqueries using SQL, and for that we have two options: either we're going to use the operator `in` or `exists`. So now we're going to focus on the operator `in` in order to solve the following tasks. So the following task says find all orders that are placed from customers with a score higher than 500, but using the customer ID. Let's try to solve that. So that means we're going to focus on both of the tables, `orders` and `customers`, and since at the end result we should present all the orders, I'm gonna start with that query first. So we're going to say `select * from orders`. So as you can see, we have now all the orders, but the task says it should contains only the customers that has higher than 500 as a score. So that means I need to find out which customer's ID over here has a score higher than 500. In order to do that, we need to check another table. So `select * from customers`, and now we need to put the filter that we need. So `where score` is higher than 500. So let's run this; you could like run this separately if you highlight it and then execute. So with that we know that, okay, customer ID 2 and 3 are the customers with the score higher than 500. So I could go back to my original query and make this filter. So I'm gonna say `where customer_id` I would say `in` 2 and 3. So with that, with this filter, I'm saying, okay, those customers had a higher score than 500. So let's run only the other parts and check the results. Now I have the orders for those customers, and with that I solved the query. And now comes the buds; this is really bad to do because it has two problems. First of all, I went to another table; I found out those IDs manually, so it was like we can do it with small table, but imagine if you have like big table with a lot of IDs, so you need to get them extra in the next query, and sometimes it is almost impossible. So with this small example it is okay, but with big tables this is impossible to do. Second problem is that once you are having changing data, for example we are getting more customers, we are getting more orders, that means each time like I'm getting new data in my tables, I'm gonna go and check the query over here and like adjust our query; this is not dynamic. So this is really bad. So instead of that, we're gonna do small trick that's gonna solve everything and make our life easier with the subqueries. So instead of having those static numbers in the filter over here, I'm gonna remove them, and instead of that I'm gonna say this query gonna be my subquery, and this over here gonna be my main query. So the results that I'm getting over here, let's check that again. So the results that I'm getting over here it's gonna be like feeding the other query. So for that what I need is really to have two and three; I just need the `customer_id`, so I don't need all those columns. So instead of the star, I'm gonna say `customer_id`. Let's run this again, and as you can see, we have now two and three. So it doesn't matter how many like new customers I'm gonna get; I'm gonna always have a full and right list for the next query. So what I'm gonna do, I'm just gonna like cut it and paste it over here; I'm just gonna put it in a new line, so it looks much better. So with that I embedded one query in the next one. So this is the subquery; it has always those open brackets and close brackets. With that I'm indicating for SQL we have here a subquery, and here we have the main queries. So let's run this and check the results. So as you can see, I got exactly those orders from the customers whose score is higher than 500, and now we could have new orders, new customers; I don't have to deal with that; always my query will solve my problem, and I don't have to add all those IDs in the `in`. Instead of that we're gonna have it very dynamically and very powerful. So this is much better solution than having like static IDs inside the `in` statements, and we are very dynamically. If you like just go through that and do more nested queries and so on, you will be able to solve a lot of complex and important tasks using SQL.

Alright, so now we're gonna try to solve the same tasks using `exists`. `exists` is little bit different than `in`; like both of them you're gonna get the same results, but with `exists` you're gonna get better performance if you're having like big tables. So if you're having big tables and you are suffering with performance from the `in` operator, you could start using the `exists`, and to check whether you will have better performance. So we tend to use `exists` more than `in` if you are facing performance problems, but it is little bit more complicated than the `in`, because there is no clear separations between the query 1 and query 2 or the subquery and main query. So let's see how we're gonna do that using `exists`. I'm going to open a new tab, so we will have the same setup. So `select * from orders`, but now we're gonna have some like aliases because it is something like joins. So I'm gonna have the name `o` as alias for the `orders`, and now we're gonna type the filter. So `where`, then after that we're gonna type exactly the `exists` statements. `where exists`, then we will have the subquery. So now we're gonna write the subquery. So we're gonna `select`, and now here we could write anything as columns, so the `exists` will not depend on the selected columns over here, so you could write anything like `customer_id` or `*` or anything you want; we tend any SQL to write just one, so because we don't care about that; just to make sure that the result from the SQL subquery is not important; it is like the join. So `select 1 from customers`; I will give it a name `c`. Now we need to add the filter, and here it is exactly like you are the doing the joins. So `c.customer_id` equals to `orders.customer_id`. So as I said, it's like a join, and after that we have another filter on the `customers`, and in that we need the `score` to be higher than 500. So with that we have over here our subquery; it looks little bit like complicated compared to the `in`. So here we have like some kind of like inner join; I cannot run this part of SQL; I will get an error because I have such a kind of like those conversion between the IDs. So in order to get the result, I need to run the whole thing. So let's see and run this. You can see I got exactly the same results. So `exists` and `in` will will give you the same results. I tend to use `in` like when it is like, let's say, small tables and so on, but once I have bad performance, I will switch to `exists`, and yeah, so it's up to you which one you're gonna use, but both of them are doing the subqueries and doing this dynamic in SQL. All right guys, so that's all for this chapter; we have learned some advanced topics in SQL, and next we will start learning how to modify our data inside our SQL tables, and we will start with the `insert` statement. [Music]

Alright, so so far we have learned how to query, how to retrieve our data from the database without changing anything, without changing the content of the tables or changing the columns. So we have used the command `select` in order to retrieve our data, and with that those commands will not change our data inside our database. So as an ex, we're gonna learn how to manipulate our data inside our database in order to change the contents, and for that we have a new set of commands inside the new SQL category that is called DML, data manipulation language, and inside it we have three main commands: we have the `insert`; we could use it if we want to insert the new data inside our tables; we have `delete`; if we have some existing rows and we want to delete it from the database, we could use the `delete` command; and the last one we have `update`; if you want to update or to change the content of existing rows in our tables, we could go and use the `update` command. All right.

So now we're going to start with the first command; we have the `insert` command. We're gonna learn now how to insert new rows to our database. So we're going to focus on the table `customers`. As you know, in our tutorial database we have five customers, and now we're gonna practice by adding one more new customer to our database to learn how to work with the `insert` command. So before now we are inserting any new stuff to our database, we really have to understand the structure of the table, the structure of the columns, because if we don't know the structure and the definitions of those stuff, we will be having some errors while we are inserting the data. So just on knowing that we have like five columns inside the table `customers` that is not enough; so we really need to understand the definitions of the tables before we start inserting any new data to our table `customers`, and to do that I usually use the following keyword: so `describe customers`, the table name. So what I'm saying now to the SQL, give me the definition of the table `customers`, so I can have a look what do we have for each column. At the first look it might look little bit complicated; don't worry about it; I'm gonna explain all those stuff step by step. So we are saying, okay, database, explain for me or describe for me the table `customers`. As you know that each tables contain multiple columns, so we can see in the results we have here five columns: we have `customer_id`, `first_name`, `last_name`, `country`, and `score`, and those are the column names, and for each column we have over here descriptions or properties that describing each column. So we have here the data types; for example, if you check here now our table `customers`, we have in the `customer_id` only numbers, and they are unique, so we have one, two, three, four, five, and those are numbers, so the data type for the `customer_id` is like something like numbers, and in database we call them integers or `int`, and the `first_name`, it's like we don't have only numbers; we have characters, so we have Maria, John, and they are like text, and we call them in the database `varchar`. There is different types for so such a characters; for example, we have `character` or `char` and so on, but in the best practices we use `varchar` because they optimize the spaces or the sizes in our database. So as well we can see here there is like the size of the `varchar`; we have here 50, that means the maximum allowed size for the `first_name` is only 50. So if you're having like more than 50

characters in the first name, the database will cut it and insert only 50 characters for the first name. So here we are like putting some rules for each column. So the first name should maximum be 50 characters, the same for the last name and the country. So if you have a really long name that is more than 50 characters, it will not fit this column, and the database is going to cut it. So you could apply as well as the data type over here, some rules about the size of each column. And we have as well the score. As you can see in this course, we don't have any characters; they are only like numbers, we call them integer. So with that, you can see, okay, each column has a different data type and you have more like understanding of that this description of the columns.

After that, there is the field called nulls, and you can see here only no and yes. It says are the nulls allowed in each column or not. So for example, on the customer ID, we are not allowing any null. So here, the database, if you insert any null, the database will say no, it's not allowed. So in the definitions, there is no null allowed, and the same goes for the first name and the last name. So once we insert data to the customers, we always have to have customer ID, first name, and last name. But now with the score and the country, we said yes, so the nulls are allowed. For example, as you can see in the score, we have here one null, and in the country, if you don't specify anything in the insert statements, there will be no problem, and the database can see, gonna show us a null. So here we can see the definition where we can add nulls and where it is not allowed.

So we have over here as well a key for each table in SQL databases; we have primary keys. It is the key that defines each customer or each row. For example, in our table over here, customers, we have the customer ID as a primary key. And once we say primary key, here comes two stuff: first, it is not allowed to be null, and second, it should be unique. That means it is not allowed to have two customers with the same ID. So Maria and John should always have different customer IDs; we cannot have both of them, for example, the customer ID one. So here should not exist any doubly kids, and this is unique. So this is the most important thing to understand about the primary key, that they are unique. So if I go now and insert one more new customer and say, okay, we have a new customer called Anne, and she has the customer ID 5, but since in the database we have already the customer ID 5, the database is going to give you an error. So here it's very important to understand in the structure which column over here is our primary key.

Then we have some other informations; for example, we have here extracts; it says it is an auto increment. Auto increments means like if I add a new customer, the database is going to increment the customer ID automatically. For example, if I add in one more new customer, I don't have to specify like the customer ID should be number six; the database is going to do it automatically. So here we have added some extra informations that it tells us this ID will be generated from the database, and we don't have to specify it. So now we have more insights about the table customers; we know the definition of each column, and we could start now inserting new records or new rows to the table customers. So I'm gonna open a new tab, and we're gonna start using the insert. So I'm gonna type here insert into keyword, and then we have to specify the table name where we can insert our data, in the table customers. Then we have now to specify the values for each column, so values, open brackets, and now we're gonna start one by one.

So the customer ID, I want to check that again; the customer ID is integer, it is the primary key and auto increments. That means the database is gonna like increment the new ID; I don't have to do it myself. So I could go and say defaults. Defaults means the database is going to take care of that. I'm going to insert the customer ID 6. You could go and say, okay, instead of that, I'm gonna type number six, but I really don't recommend it because if you have like a big database and someone else is doing insert or you forget about what is the last customer ID we have in the database, so just make your life easier and type defaults. So now we have to enter the first name; I'm gonna use, for example, that first name Anna. Here we have a problem in SQL database that you cannot just type the first name like this; it is a string, and in string we have to boot it always inside single quotes or double quotes. So for example, I'm gonna use the double quotes in order to like to deal with the strings. If you don't do that, you will get an error. I usually use single quotes to insert the strings, so with that it should be okay. The last name is the same thing; it's this VAR character, and we have to put a name on it. So I'm gonna use Nixon as a last name. So we have now the three columns: customer ID, first name, last name. Now we have country and score, so let's check the country. The country, it says it is VAR character, so we have to specify something over here, and we could leave it empty, so I don't have here really to insert anything if I don't want, and the same goes for the score; it is but integer, but we could leave it as well empty. So what I'm gonna do, I'm just gonna add the country. The country, it is VAR characters, so it's a string; I need to put it in single quotes. I'm gonna use the country UK. Okay, so now to the last column, we have the score. Let's check that in the description. So we have score; it is integer, so that means only numbers should be inside the score. It is nullable, so I could leave it empty, and it is not primary key and so on. So that means I could leave it as a null, and that makes sense because Anna is a new customer, and she doesn't have yet any like scores in our database or systems. So that's why I could just write over here a null, or I could leave it like zero if I want. So with that, I will just leave it as a null, and let's just execute another query and see whether we have everything right. So he will not get any like results yet; we will just get here the information that everything is green, and we have inserted the data. So in order to check now this user inside our database, we're gonna open a new tab, select star from customers, and see whether Anna is in the database, and yes, we have now one more customer called Anna Nixon from country UK; the score is null; she's new, and we have the new generated ID customer ID from the database.

Okay, so sorry, practicing and add one more customer, our customer number seven in our database. So let's go and do that. So I'm going to move everything and start from a scratch: insert into our table customers, and now we're gonna add the values. So as usual, our first value, the customer ID is going to be defaults; the first name I'm gonna use Max, and the last name I'm gonna use Lang. But now the country and score, I could leave them empty, so I'm gonna use the null and as well for the score now. So now, as you might already notice what I have really done over here, I just gave a first name and last name, and for all others I'm using some nulls and defaults. So we could like skip that and make our life easier with just adding the first name and the last name. So if I just remove the null over here and that defaults, I run the query, I will get an error because the database is not understanding what is Max; is Max like the country? Is Max the first name? The last name, the Lang as well, is it like the first name, last name? So we need to specify for the database what are those values to which column. So in order to do that, I'm gonna open here new brackets and say, okay, I'm gonna type the column name first name, and the second one we are using the last name. So with that, we are telling the database, okay, the first value belongs to the column first name, and the second value belongs to the column last name. And if I run this, we will not get an error because we have already done the mapping, and everything else is done automatically. So that means the database knows the customer ID; it is like automatically generated, so it's going to generate a new ID, and since the database didn't find any information about the country and the score, it's gonna put it as a default as a null. So let's check now the results; if I query now the same select star from customers, and we can see the database done that and inserted our new customer Max Lang; she understood that the country, or it understood that the country is a null and score is a null, and generated the ID of seven. So as you can see, it's more compact, and I don't have to add all those nulls because imagine if you have a big table with like 50 columns and you have a lot of nulls, the query gonna look really bad. So here I'm just inserting what I need, and the rest gonna do the database for me if it is allowed. So for example, if the country should not be null, I have to insert you something about the country, but since we are allowing the nulls in the country and the score, we could just ignore it and leave it like this. Alright, so with that we have learned how to insert data in our SQL tables. Next, we're gonna talk about the update statement. [Music]

All right, so now we're gonna talk about one more command in order to manipulate our data inside the database, and that is the update commands. So you could use updates in order to change the values of an already existing row in your tables. Okay, so let's have now the following task: we just added a new customer with the insert statements, and that is Max, the customer number seven, and as you already noticed, this is the only customer that we don't have a country specified in the database, and the task is now is just to add the country Germany to this record. So now we have to update the content of this customer by changing the null to Germany's. So now we're gonna start with the keyword update, and now we have to specify the table name that should be changed, so we're gonna have the table name customers, and after that to the new line we're gonna have the keyword sets. With that, we can specify new values for the columns that should be changed. So we want to change the column country, and we have a new value; instead of the null, we need to give the value Germany as a new value for that country. Now here you need to be really careful about that; if I execute this, don't do this; if I execute this command, what can happen? The database is going to go and updates all the values for all customers underneath the country to the new value Germany, because if you read this, we are telling the database that update the table customers and set country to Germany without specifying any customer. That means if we run that, all the countries will be in the table as a Germany. So don't do that; our task is here is only to change it for the new customer. So as you can here see, our customer Max has an empty value at the country, and we only need to change it. So in order to do that, we're gonna filter, or we're gonna put like a condition for the updates, and in order to do that, we're going to use the primary key customer ID number seven. I don't recommend to use any other columns, like for example the first name or the last name, because if you have a big table, the first name Max may be presented in other customers, so maybe you have different customers with the same first name, and if you run the query on the first name, all customers with the first name Max will have the country as Germany. So to make sure to update the right record, the right row, we're gonna use the primary key, the customer ID. In order to do that, let's go back over here, and we're gonna write the where command exactly like the selects, and we're gonna say we need to change the customer ID number seven. With that, we are telling exactly the database we have now a new value on the country, and that is only for the customer ID number seven. So let's run this and go over here and run this again to check the value. So here we have it empty or null, and after the updates now we have Germany inside the country.

Alright, let's have another task where we're gonna manipulate and update the content of our tables. The task says our new customer Anna, she was active; she bought something in our website, and she has now the score of 100. So instead of having the score of null because she was a new customer, we have now 100 for Anna, and not only that, we have entered by mistake the country UK instead of USA. So Anna comes from USA, and we have to update as well the country. So let's do that using the update command. All right, so we're gonna check over here. So before we start like updating the values in the columns, let's go and make it sure that we have the right customer, so we are not updating a different customer or updating the whole table. So let's make sure that we are selecting everything right in the where command. So Anna has the customer ID number six, so instead of seven, we're gonna write here number six. So now we are focusing on the right row, and now the country should be USA. So now we are giving a new value for Anna in the country field, and we want now to specify one more column to be changed. So in order to do that, we have the comma; I like to put it in a new line, and the score should be equal to 100. So with that, you are specifying like multiple columns in one update, and you can split them by that comma. So if I want to change one more column, I could do it all in one command; I don't have to have like different commands for each column; I could boot everything in one. So now what we are saying: update the table customers where the customer ID is number six, and the country should be equal to USA, and the score should be 100. So let's run this and then go back to our select star from customers to check whether everything was okay. So I'm gonna refresh that, and you can see now Anna has the country USA, and the score is now 100. So it's really easy to manipulate the data using the update command. All right, everyone, so that's all for the update statement, and next we are going to learn the delete and truncate statements. [Music]

Okay, so now we're gonna move to the last command that we have under the data manipulation section, and that is the delete command. So in order to delete rows from our tables, we could use the delete, and let's have the following tasks. The task says, wait a minute, all the new users since yesterday or since today they were wrong inserted in our systems, and we have to delete them. So we have the customer Anna, the customer Max, they should be deleted from our database, from our tables. So in order to do that, it's pretty simple; we're gonna use the command delete. All right, so in order to solve that, we're gonna write a very easy command, and as well it is very dangerous. So we're gonna start by writing the keyword delete from, and then comes the table name. So we need to delete from customers. As you can see, it's only three words; it's very easy, but if I execute this, be careful with that; it's gonna delete everything inside the table customers. So I'm not specifying anything; I'm saying delete from customers, and if I run it, the database is gonna delete all our customers from the database. So be careful with that; always specify what you want to delete exactly. So with that, it's like the updates; we're gonna use the where command and use the primary key, the customer ID. So we want to delete the customer ID number, let me check again, number six and seven. So in order to do that, I'm gonna use the in operator, in six seven. So any customer IDs in six and seven gonna be deleted. So this is my filter condition, and if I run this, both of the users going to be deleted. So let's check that; if I run this over here, you can see both of the customers are deleted. And with that, we have deleted some records from our customers, but be really careful what you are specifying in the deletes, so you don't delete all your records. You might be during the like the development of your tables; you are inserting data like test data, and you want to delete all of them. So if you want to make a table empty, you could go and say, okay, delete from table name, and you're gonna make the table empty, and then insert again its data. But if you are like deleting only few records, be careful what you are writing in the where condition, so you don't lose all your data. One more thing here to talk about about deleting rows, that you might be in situations sometimes you have a very big table, and the mission is over here is to delete everything, to delete all the rows from this big table. So if you are using the delete from commands, it might take a long time because what SQL is doing is gonna go like for each bunch of data deleted, then go to the next one, so it's gonna do it like an iterative manner, and it may take a really long time. So instead of using delete, if you are sure that's okay, I want to make a table empty, I want to delete everything from the table, I just want to have the columns and nothing inside it, so instead of using delete, there is a best practice to use another SQL command to delete the rows, and that is truncate. Keyword and customers. As you can see, it's only two, two words to destroy everything, so it's very like a short command, truncate customers. You are telling the SQL, okay, delete everything; I don't want to see any records inside my table. So the database is going to do it really fast. So if I'm gonna run this query over here, so I'm just gonna remove the delete from; we are deleting everything in the table customers. So if I do select stuff from customers, the table is going to be empty. So if you have done that and you want to have the test data again, just go to the tutorial database and rerun the whole script, then you will have exactly the same situation before you are deleting the data from customers. All right, everyone, so that's all for this chapter; we have learned how to modify our data inside SQL tables, and now we're gonna jump to the last chapter where we're gonna learn how to define our data using SQL, and first we will learn how to create SQL tables. [Music]

All right guys and girls, so so far we have learned how to query our data using the select commands, and as well how to manipulate our data, the values inside our tables using insert, delete, update. As the next, we're gonna focus on a new group, and that is the data definition language, DDL. It is about how to change the structure of our database, how to change the tables themselves. So we have here three commands: create, to create something new, like create a new table or create new objects; we have drop, to drop a table or delete a table; alter, it is to change the structure of one table. Okay, so now we're gonna start talking about the first command; we have the create command. If you want to create something new in the database, new objects, for example, a new table or new view, start procedures in the databases, there is like different types of objects, not only tables. So you could go and use the command create. In our tutorials, we will be focusing on creating new tables. So in order to create new tables, you have to define the structure of each column inside it, and in order to do that, we have to specify those three informations for each column. So each column should have a name; this could be anything, depends on your requirement.

That you have so it must have a name, and after that, it must have a data type, and exactly only one data type. So you cannot specify multi-data types for each column—exactly one. In MySQL, there is like a big list of all available data types. I'm gonna leave the link in the description so you could check that. The most famous ones are INT, VARCHAR, DATE, CHAR, and so on. Those data types should be assigned for each column, and as well, you could assign inside them the size of each each column, the maximum allowed size. Like it's a rule that you're gonna apply. If you leave it empty like this, only the data type is gonna get a default one from the SQL. So if you define, like in our last example, the VARCHAR for the last name VARCHAR(50), that means the maximum allowed size for the last name is going to be 50. Anything that can exceed the 50 characters it's gonna be cut down, and only allowed 50 characters inside the last name. So here you could specify the data type and as well the size of the data type.

After that, you have like a bunch of constraints that you can define on your database in order to have some data quality. For example, you have the constraint PRIMARY KEY. You say this column is the primary key, and immediately it's gonna be like unique and not allow any NULLs inside it. And you could define for each column multiple constraints, not only one constraint. So you could say this is PRIMARY KEY and NOT NULL and UNIQUE and so on. So you could define multiple ones. So we have as constraints in the database PRIMARY KEY, NOT NULL, so you are not allowing the NULL values; UNIQUE, that means the value inside it should not be like duplicated; and then we have DEFAULT. DEFAULTs means if we are inserting any data and we didn't specify a value for this column, the database is going to use the default value that we have defined in that column. So those constraints, as I said, you could use like all of them if you want for each column. So it's really dependent on the requirements and on the data quality requirements as well, but the data types should be only one and for each column we have only one name for it. All right. So now let's learn how to create a new tables using SQL, and we have the following tasks: create a new table called persons, and inside it we're going to have four columns: ID, person name, birth dates, and the phone.

As you know, in our tutorial database we have only three tables. So if you check here the left side, we have the customers, employees, and orders, and now we're gonna add one more table called persons. So let's do that. All right. So now let's start creating our table. We're gonna start with the command `CREATE TABLE`, and after that we need to specify now the table name. But before that, we have to enter the database name or another databases; it is the schema name. So as you might already notice in MySQL, we have different databases; we have our tutorial database and some default ones. So we're gonna put this table in our tutorial database, and that is `DB_SQL_tutorial`, then a dot, and here we're gonna put now that table name, and we have the `persons`. After that, we're gonna open two brackets, and inside them we're gonna define now the column structure. So let's start with the first column: we have the ID. This is our primary key, the most important like column in the whole table; it's something like the customer ID in the table `customers`. So the name of it gonna be `ID`. After that, I'm gonna have a space, and then we have to define now the data type. Since it's going to be a sequence of numbers 1, 2, 3, 4, and so on, we're gonna use the data type `INTEGER` (`INT`). I will not define the size; I'm going to use the one that we I have as a default from MySQL. So now we're gonna define the constraints that we want for this column. So here, since it is our primary key, we're going to use the constraint `PRIMARY KEY`. We don't have here to specify `NOT NULL` because as a default, if you are saying this is `PRIMARY KEY`, you will get inside the two things: first, it's gonna be unique, and as well `NOT NULL`. So it is two constraints in one, the primary key. So after that, I don't want to generate those IDs by myself manually by doing the inserts; I want that the database take care of that. So to do that, we can define it as `AUTO_INCREMENT`. So with that, if you are using `DEFAULT` or you are not specifying anything in the INSERT statements, the ID gonna be generated automatically from the database. So with that, I have the column name, I have the data type, and I have two constraints. So now we're gonna jump to the next column; we have the person name. So I'm gonna add a comma and a new line for that. So here we're gonna have the `person_name` as a column name, space, after that we need to define the data type. So a sense it's going to include some characters and so on, I'm gonna use the `VARCHAR` and define as a size 50. So more than 50, the data gonna be cutted and inserted in the database. So this is my rule as well; I want that each person has a name, so we don't want to have some NULLs. So now we can define the constraints; so this should not be a NULL. So that's it; I don't want to have some unique constraint and so on. So we allow that we have two persons with the same names, but they will have different IDs. So that's enough for this column; we're gonna jump to the next one. We're gonna add the birthday. So the name of that gonna be `birthday`, space; the data type of that's gonna be `DATE`, and now I don't really want to specify any constraints because this column could be optional, so we will not add anything. So that should be enough; we have the column name and the data type. The comma, and the last one, we're gonna have the `phone` as a column name. The phone could be like characters as well, so `VARCHAR` or `CHAR`, and I am gonna allow only 15 characters to be inside the phones or some data qualities. So the phones should not be NULL, so here I'm gonna add a constraint `NOT NULL`, and one more thing that I could add as a constraint on this table is that each person should has a unique phone number. So we should not have two persons with the same phone number. In order to force such a quality at your table, we could add the `UNIQUE` constraint, and with that we are telling in this column we should has only unique phones and duplicates are not allowed. So now we have all our four columns; we have specified the data types and the constraints, and that's it. We could run the query over here. So we don't have any errors. If we check on the left sides, we don't have yet the `persons`; that's why because we have to refresh the data over here. So click on refresh, and you will see we have one more table called `persons`. Okay. So now let's check some stuff. For example, if I go and say `SELECT * FROM persons` just to check the table structure. So here I can see, okay, I have a table called `persons`, I have my four columns, and everything is empty. You could go and as well check the `DESCRIBE` command for `persons` and query that, and you can see, okay, we have the fields, the data types, what is NULL, what is NOT NULL, the primary key, and what is unique, the `AUTO_INCREMENT`. So you could check that everything is fine and as wanted.

All right, everyone. So that's all about how to create a SQL table, and next we are going to talk quickly about the `ALTER TABLE`. Okay. So now let's move to the next command; we have `ALTER TABLE`, and that you could use it in order to change the definition of a table. So let's say, okay, we need to add one more column to our new table `persons`, and that is the `emails`. So in order to do that, it's pretty simple. So we could use—I'm going to remove this—we could use the keyword `ALTER TABLE` and the table name `persons`, and after that we're gonna add the keyword `ADD`. Now we are adding a new column; it's like in the `CREATE TABLE`. So we need the column name, and that is `email`. Then after that, we need to define the data type; it's going to be `VARCHAR(15)` as well as a rule, and here as well we need to add some constraints if you want for some data quality. You say, okay, this is `NOT NULL`. So with that, I'm changing now the already existing table that's called `persons`, and I'm adding now a new column. So let's run this and let's check again our table. Refresh. Let's select the table `persons` and see the results, and as you can see at the end we have a new column, and always SQL can add the new columns at the ends. So if I check this as well, `DESCRIBE persons`, just to make sure that everything is fine, we can see here we have one more column that's called `emails`, `VARCHAR(15)`, and this should not be NULL. All right. So that's all about how to alter a table, and now we are going to learn how to drop a table; it's really easy.

All right. So now let's jump to the last command that we have in order to change the structure of our database, and that is a `DROP` command. If you want to delete a table, so you say, okay, this table is completely wrong; I don't want it at my database. You could go and drop the table, and that's pretty easy; you could do it like this. So let's say we want to drop the new table that you have that's called `persons`. So we use the keyword `DROP TABLE` and just write down here the table name, and that's it. So once you execute that, the table `persons` will not exist at your database. So I'm gonna delete it, and as you can see on the left side, you will not have a table `persons`. So it's really simple. All right, guys. So that's all for the last chapter, and not only that, that's all for this SQL course. Hey, I'm really proud of you that you made it until the end, and I hope that you enjoyed the journey. If this video helped you to start with SQL, I really appreciate it if you support me by liking and sharing the content. And in the next SQL course, you will learn more advanced SQL topics, especially if you are planning to be a data science or a data analyst. We're gonna cover topics like the window functions, which is very important for analytics like ranking and aggregate functions. Then we're gonna do a deep dive into subqueries in SQL, and after that we're gonna learn about the common table expression CTE. And then we have another chapter where we're gonna talk about the set operations like UNION, INTERSECT, and EXCEPT. And at the end, we're gonna cover the most important date and time functions in SQL. And now, if you enjoyed this course and you want to support my content, then you can go and support, like, and comment; this really helps with the YouTube algorithm, and of course, if you have any questions or topics that you want me to cover in the future or you want to give me feedback, leave a comment down below. Well, nothing left to say; thank you so much for watching, and I will see you in the next tutorial. Bye [Music]