MySQL Tutorial for Beginners [Full Course]

Hello, my name is Mosh Hamedani and I am very excited to be your instructor. In this three-hour

course

, you will learn everything you need to get started with SQL or SQUEL. First, I'll give you a three-minute introduction to SQL, then we'll install the necessary tools and write our first SQL query. This

course

is ideal for anyone who wants to learn SQL from scratch, but also for anyone with some basic knowledge who wants to fill in the missing gaps. By the end of this course, you will be able to recover inserted, updated and deleted data in databases.

We will talk about tables, relationships, different types of joins, subqueries, regular expressions and much, much, more. These are the essential concepts that every software developer or data scientist should know. This SQL course is packed with tons of exercises to help you learn and remember SQL syntax. Additionally, there is a table of contents below this video so you can quickly access specific

tutorial

s. Now, let's jump in and get started. Let's start the course with a quick overview of databases, what they are, and how we use them. A database is a collection of data stored in a format that can be easily accessed.

To manage our databases, we use a software application called a database management system or DBMS. We connect to a DBMS and give it instructions to query or modify data. The DMBS will execute our instructions and send the results. Now we have several database management systems, and these fall into two categories, relational and non-relational, also called NoSQL. In relational databases, we store data in tables that are linked together using relationships. That's why we call these databases relational databases, each table stores data about a specific type of object, like customer product, order, etc. SQL or SQUEL is the language we use to work with these relational database management systems.

Does it look like this. We use SQL to query or modify our data. In this course you will learn everything there is to know about this powerful language. Now, there are many different relational database management systems; some of the most popular are MySQL, SQL Server, Microsoft and Oracle. But of course, there are many more. Each database management system has a different version of SQL. But all these implementations are very similar and are based on the standard SQL specification. Therefore, most of the SQL code you will learn in this course will work with any database management system.

In this course, we will use MySQL, which is the most popular open source database in the world. Ok, let's go back to this diagram, what about non-relational databases? In non-relational databases, we have no tables or relationships, these databases are different from relational databases, but that is a topic for a completely different course. What you need to know is that non-relational database systems do not understand SQL. They have their own query language. So, we use SQL to work with relational database management systems. Now, before you get started and install MySql, let me quickly clarify something: When you talk to different people, you will hear two different pronunciations of SQL.

SQUEL or SQL. What is the correct way? Well, it depends on who you ask, and of course everyone thinks their way of pronouncing this word is the correct one. But here is a story about this language. SQL was originally developed at IBM in the 1970s and was initially called SQUEL, short for Structured Query Language. But they changed the acronym to SQL, because SQUEL was the trademark of an airplane company. To this day, there has been a discussion about what the law is in general terms; People in non-English speaking countries call it SQL. I'm used to calling it SQUEL because it's shorter and nicer than SQL.

But if you prefer to call it SQL, that's totally fine with me, I'm not going to get mad at you. That is the story behind this language. But what about MySQL as a software product? The developers of this product prefer to call it MySQL instead of MySQL. But they don't care if they call it MySQL. In this course, I will teach you SQL with MySQL. Hey guys, Mosh here. I just wanted to let you know that you don't really have to memorize anything in this course, because I've created an entire cheat sheet with summary notes for you.

You can find it below this video in the description box. So I've done my best to create the best and most comprehensive SQL course possible for you, and I would really appreciate it if you would support my hard work by liking and sharing this video. Thank you very much, now let's continue. In this

tutorial

, I will show you how to install MySQL on your computer. I'm using a Mac now, so I'll first show you how to install MySQL on a Mac computer and then show you how to install it on Windows. So if you are a Windows user, feel free to skip this tutorial.

Now, open your browser and go to MySQL.com. Then go to the downloads page and scroll down. Here you should see the MySQL Community Edition, it is absolutely free and we will be using it throughout this course. So let's go ahead and look at that, now on this page, click on MySQL, community server, and then on this page, you should see the versions available for Mac OS. So in this list download the first item which is a dmg file. Alright, now on the next page click no thanks, just start my download. Well, as you can see, we get a dmg file, which is basically a setup wizard.

Alright, now the DMG is downloaded, so let's open it and then double click on this package, this will start an installation wizard which is quite easy to use, so just click continue and again and again, accept the agreement license. and install MySQL, it will ask you for your password, this is the password you used to log in to your computer, so let's put it here, okay, now here we need to set a root password for the administrator user. So click Next and in this box, type a complex password. Alright, now let's finish the installation and enter your computer password once again, and that's it!

That was super easy and sweet. Alright, we installed the MySQL community server, now we need the graphical tool to connect to this server and manage our databases. So, go back to the downloads page, once again scroll to the bottom and go to the MySQL community addition, and on this page, somewhere you should see MySQL Workbench. This is a graphical tool that we use to connect to our database server. So let's go ahead and download this as well. Now once again on this page we need to download a dmg file so download it and again we have to say no we don't want to log in or register so let's go ahead and download the dmg and then open Well you will see something like this , so drag this MySQL workbench and drop it into the applications folder.

So let's go ahead with that, now we're going to copy this into the applications folder, beautiful, that's how we're done with the installation, that was super easy, now press command and space, and look for MySQL, workbench, there Go, let's open it, now the first time we receive this message, because it is an application that we downloaded from the Internet. So we need to tell Mac that we trust this, let's move on with that, so this is the MySQL workbench, now by default you will see a connection here, if you don't see it, you need to create it.

Let me show you how to do it. So for this demo, I'm going to right click and delete this connection. Okay, now let's create a connection from scratch. So click on this plus icon, on this page, give a name to this connection, let's say local instance, now the connection method we are going to use is tcip, which is set by default, the hostname is 127.0.0.1 , which is the address of the local machine and the port is 33 06, this is the default port for the MySQL server, that is the administrator username, now we need to enter the password. This is the password we set during installation.

So, click on store in keychain and in this box type the MySQL server password. Alright and finally, let's test the connection, okay, we success

full

y connect to the MySQL server on the local machine, beautiful, let's click OK, and here we have a connection on the MySQL Workbench home page, every time we open MySQL Workbench , we use this connection to connect to our local server. Alright, we're done with installing MySQL on a Mac. Next I'll talk about MySQL on a Windows computer, so feel free to skip that tutorial. In this tutorial, I will show you how to install MySQL on Windows.

So open your browser and go to MySQL.com, then go to the download page now here, scroll down, we will use the MySQL community edition, which is absolutely free. So let's continue with this, now select the MySQL community server and then scroll down so here you should see something like this, MySQL installer for Windows. This is the recommended method to install MySQL on Windows. So click on this, okay, on the next page, scroll down and download the first installer here. On the next page, scroll down and click No thanks, just start my download. Otherwise, you need to create an account and log in, which is unnecessary to know this course, so let's go ahead with this, save this file on our computer and then run it.

Alright, we will use this setup wizard to install MySQL on our computer. This is very easy, all you have to do is click Next, but there are a couple of places where you need to enter a password, let me show you. So on the first page, for the configuration type, we will use the default developer configuration. Go to the next page. Now here we are getting a small warning, because this installation wizard wants to install the connector for Python 3.7, but I don't have Python on this machine, that's why I am getting this warning, now on your machine, you may or may not get this error, just click next and once again, so here are the products I want to install: the first is the MySQL server, the second is the MySQL workbench, this is the graphical tool we use to connect to our core server and manage our database.

You will see it soon. So click run, now this will take about 5 to 10 minutes, so I'm going to pause the recording. Alright, all the products are installed, beautiful, let's go to the next page, and again, here is our group replication page, also click next, and on the next page which is about networking, let's leave all the default settings , so, go to the next page, now we need to set a password for the root of the administrator user. So, click Next and in this box, type a password for the administrator user. Alright, and then let's go to the next page, once again let's leave all the default settings, click on Next and run once again, okay, and now let's finish our installation, once again we have to click on Next and then finish.

Again, there are so many steps. Now here is the page where you need to enter the administrator password, then the page is called connect to server, you can see that the username is root which represents the administrator user, so in this box enter the password that you said earlier, then click Verification, okay, the connection is successful, let's go to the next page, click run, and finally finish. There you have it, we have one more step, then finally after all these steps the installation is complete. Now, this is going to start, the MySQL workbench, which is the graphical tool that we use to manage our database and run SQL queries.

So click finish, now we have a command prompt window, where we can type instructions to talk to our MySQL server, we don't really need this, so close it, and here is the MySQL workbench, now the first time opens it. this page, by default you should see a connection here, if you don't see it, click on this plus icon, on this page give a name to this connection, let's say local instance, now leave all your settings at their default value, but here To obtain the password, click on the corresponding store. And in this box, type the password that you used for the administrator user.

So, I'm going to put that here, okay, now click on test connection, okay, we success

full

y connect to the MySQL server on this computer, okay, so, click on OK, now we click on this connection to connect to our database server. Alright, here is the interface that you will use throughout this course, on the left side we have the navigation panel,In the middle we have the query editor whose size we can change, this is where we will write our SQL queries. , and on the right side we have additions. We are now done with MySQL on our computer.

Next, I'll show you how to create our databases for this query. In this tutorial, I will show you how to create the databases for this course. So here I have the MySQL workbench open. Let me quickly give you an overview of this interface because the first time you open it it can seem a little intimidating. But it's really not that difficult. Here at the top we have this toolbar with these buttons to create a new tab to write SQL code and open a SQL file. And next to it we have a lot of buttons to create a database, create new tables, etc.

On the left side we have the navigation panel with two administration and outline tabs. We use the administration tab to perform administrative work, such as starting or stopping our server. Import or export data, etc. The schemas tab shows the databases we have on the current database server. We currently only have one database, which is sis, and this is the database that MySQL uses internally to do its job. Now, in the middle we have this query editor window, this is where we write our SQL code, so we will spend most of our time in this course, in this window, and on the right side we have another panel with two tabs, retained contacts and fragments, now this interface will probably look slightly different in Windows, but what I'm showing you here is almost identical to what we have in Windows.

So don't worry if it looks slightly different on your machine. Really do not care. Now up here we have these buttons to show or hide these panels, so to clean up this interface, I'm going to hide this panel on the right side. As well as the panel at the bottom. That is better. Now, to create the databases for this course, download the attached zip file, below this video. When you extract (?), you'll see a bunch of SQL files like this. So the main one that you will use in this tutorial is called create databases.SQL. So this file contains all the SQL code to create all the databases that we need in this course, now we also have individual files to create individual databases, I just added these files in case you need to recreate one of these databases in the future.

But for now don't worry about them. Now, coming back to the MySQL workbench, let's open the main SQL file. That is creating databases. This is an example of SQL code. Now this may seem complex at first, but trust me, as you progress through the course you will understand exactly how everything works here. You will be able to write SQL code like this. So you want to run this to create all the databases for this course. To do that, we click on this icon, this yellow thunder icon that we have here in this toolbar. This will run the selection or the entire code if nothing is selected, for example if I select this line here and click this icon, it will run just this line.

In this case we want to run the entire code, so we don't want to select anything, and now let's run this, beautiful, now down here, we have this panel called output window that shows all the operations performed on our database server, so that we can see all the operations completed successfully, or something went wrong. As you can see, it has these green ticks next to each operation. Beautiful, so I'm going to close this panel, that's better. Now on the left side of this schemas tab, you don't see this new database, so we're going to have to refresh this view, beautiful.

So we have all these databases that have the prefix SQL or SQL, I decided to prefix them with SQL so that we know that these are the databases for this course, they don't accidentally collide with a database with the same name as your server. database. Now, at the time of recording this video, there are only 4 databases here, but as we progress through the course, I will update the script to create the databases, so, when you see this course, it is likely that we will see more databases data here. Don't worry about the difference. Now, as an example, let's explore these databases.

And by the way, we don't need this tab anymore, so let's close it. That is better. Let's expand the SQL warehouse database, now in each database we have these objects, we have tables, this is where we store our data, we have views, which are like virtual wires, so we can combine data from multiple tables, and put them in one view. And this is especially powerful for creating reports; You will learn about them in the future. It also has storage procedures and functions, and these are little programs that we store inside our database to query data. For example, you can have a store procedure to get all the customers in a certain city.

So we call that procedure and say hey, give me all the clients in San Francisco. And this will bring back all the San Francisco customers. Okay, now let's expand the tables, so here are the tables for this database. We have clients, we have orders, products, carriers, etc. Now, select this customer table every time you hover over this element and see these three elements on the right side. Click the plus icon on the right, which looks like a table with thunder. With this we can see all the data, in this table. This is our clients' table. In this table, we have these columns, such as Customer ID, which we use to uniquely identify customers.

We all have a first name, last name, date of birth, telephone number, address, etc. These are the columns of this table and each row is called a record. So each row represents a customer and this is the information we know about each customer. Now let's see another table, let's open the orders table, in this table we have these columns like order ID, customer ID, order date, status etc. What is this client ID here? We use this column to identify who placed each order. Now, the interesting thing here is that you have referred to this customer using its customer ID, which uniquely identifies it.

In other words, if Jon Smith has placed an order, we don't store John Smith here, we only store John's customer ID. Why is that? Here is the reason. John Smith may have placed multiple orders in our system. Now, every time John places his order, we must look up his address and phone number to send his order. It is now possible that some of this information may change in the future. John could move to a new place or change his phone number, he could even change his name, if he repeats all that information next to each order, then we will have to go back and make changes in several places.

On the contrary, with this design we only store John's ID here, so whenever we want to change any information about John, instead of changing it here. We go back to our customer table, so let's look at the customer with ID 6, which is actually called (?). Here is all the information about Elka. This is his phone number, this is his address, and by the way, these are all dummy data that I created using a tool. So if any information about elka changes in the future, this is the only place we need to modify. This is how these databases work.

We refer to these databases as relational databases. Basically, that means that in this type of databases we have several tables that are related to each other using a relationship. So internally there is a relationship between the customer table and the orders table. So the Customer ID column in the Customers table is related, linked or associated with the Customer ID column. In the order table. Here is the orders table and here we have the customer ID column. This was a brief introduction to relational databases, you learned about databases, tables, columns, rows and relationships. In the next section, I will show you how to retrieve data from a single table in this database.

But before we continue as an exercise, I want you to explore the billing database. Look at all the tables, look at all the data to get an ID of the type of data we have in this database. You're going to be using this database a lot in the future, so let's take a couple of minutes to explore it. In this tutorial, I will show you how to recover data from a single panel. Now look at the browser panel. Currently, none of our databases are shown in bold, and that means that none of these databases are selected for your query.

So the first step in qualifying a query to get data from a database is to select a database. The query we will write will be executed against that database. In this demo, we will use the SEQUEL store database. Then we write our use SQL_ store. Now use is a keyword in the SQL language and is shown in blue. Now, SQL is not a case-sensitive language, and that means we can use uppercase or lowercase characters, it doesn't really matter, but as a best practice, we should capitalize SQL keywords and use lowercase characters. lowercase for everything else. . Now, let's go ahead and run this query.

Okay, look, the SQL warehouse database is now displayed in bold. Now in MySQL Workbench, we can also select a database by double clicking on it, so now I double click on SQL Invoice and it is the current database. Now, if we run this query again, the SQL warehouse database is selected. Alright, now let's write our first query to retrieve all the customers in this database. So after the you statement, we will use the select statement. Here is the basic syntax or basic structure of the select statement, we write select in front of that we specify the columns that we want to retrieve, for example we can retrieve the customer ID column as well as the name column or we can retrieve all the columns using an asterisk.

Now after that we use the from clause and this is where we specify the table we want to query, in this case the customer table. So this is the simplest query to select all customers in a given table; Now, whenever you have multiple SQL statements, you'll need to end each statement using a semicolon. So, look, we have a red underline here indicating an error. If you mouse over here, you can see this tooltip that says the selection is invalid at this position. Because we didn't end the first statement with a semicolon. Okay, now let's run this query one more time, once again, we can click this button here, or we can use a shortcut, so look at the query menu at the top, the first line is run.

Now here is the shortcut for this command, on Mac, it is the Shift and Enter command. On Windows it will be different, honestly I'm not sure. So whatever it is, use it. So I'm going to press the Shift Enter command and here are all the customers in this table. So the following statement has two clauses, the select clause and the origin clause. But there are other clauses that we can use to filter and sort data. For example, you can use the attrition clause to filter the result and get the customer with ID one. So we can write an expression like this.

Where _ID = 1. Now when we execute this query, we will only get the customer with ID 1. This is the where clause. We can also sort the data so that after use we use the order by clause and here we specify the columns from which we are going to sort the results. Let's say we want to sort these customers by name, so we type first_name. That's the name of one of the columns in this table, right? Now if you run this query, this order doesn't really have an impact, because we only get one record in the result, so let me temporarily remove the where clause, to do that we can put two dashes in front of this line.

Now this line is treated as a comment, which means that the SQL engine is not going to execute this, okay, so let's execute this query once again, now all the clients we get are sorted according to their name. So that's the basic idea. Now, in the upcoming tutorials, you will learn more about these clauses in detail. But what you should learn from this tutorial is that these 3 clauses from where an order is placed are optional. As you can see in this example, I am not using where clause, we can also comment out the order by clause, we can also comment out the from clause, so instead of selecting all the columns, in a given table, you can select some values ​​as one and two.

Now if you run this query one more time, the result we will get something like this. Two columns called one and two, and in these columns we have these values. So all these clauses are optional, but in the real world we often use them all. Now the thing to understand here is that the order of these clauses matters, so we always select first,then from, then where and finally we sort by. We cannot change the order of these clauses; otherwise we will get a syntax error. Which basically means that the syntax, grammar or structure of our simple statement is wrong.

So it cannot be executed. And one last thing before I finish this tutorial, you can see that I have listed all these clauses on a new line, now technically you don't have to do this, because line breaks, whitespace and tabs are ignored when executing SQL statements . . So we could come back here and put in front of select, so select store all on one line, and that's perfectly acceptable for simple queries, but as your queries get more complex, it's better to put each clause on a new line . That's all for this tutorial. In the next tutorial, we will explore the selection clause in detail.

In this tutorial, we will look at the selection clause in detail. So since our current database is the SQL store, to clean things up I'm going to remove the first statement, you don't really need it now, also I'm going to remove these two comments, we just want to focus on the select clause. Okay, so what can we do with this select clause? Well, in the last tutorial we learned that if you use an asterisk, this will return all the columns. Alternatively, we can specify any columns we want and this is useful in situations where you have a large table with so many columns and perhaps millions of records.

If you want to recover all that data, it will put a lot of pressure on the database server, in this case MySQL, as well as the network. That's when we explicitly specify the columns we want to get. Let's say we want to get the first name and last name columns. Run the query, as you can see we only get these two columns. And they are in the same order that we specify here. So if we change the order and put the last name first and run the query again, we can now see that the last name column appears first.

Now let's add a new column, at the end let's also get the points for each customer, run the query, so these are the points for each customer which are calculated based on their purchases. Now let's say we want to get these points and put them into a mathematical formula to calculate the discount we can give to each customer. So here we can use an arithmetic expression, that is, points plus ten. This is an arithmetic expression. Now we can run this query for the first record. You can see that your points will end up being 2283. Let's run the query one more time.

Now we can put the original column of points here for clarity. So points, points plus 10. Let's run the query one more time, now you can see the original points and next to it you can see the value that we will use to calculate the discount. Now here we are using the plus operator which is for addition, we also have multiplication, division, subtraction and modulus which is the remainder of division. So let's change this to something more complex. Let's say we want to get the points, multiply them by 10 and then add 100 to it. Now we can immediately see that this line is getting too long and doesn't fit on the screen.

In situations like this, you can split the select clause by placing each column on a new line, hence select the last name. then first name points finally points multiplied by 10, plus 100. So let's run this query one more time. This is our new column with the new calculated value. Now, one thing you need to understand in this arithmetic expression is the order of the operators, and this is based on the order of operators in mathematics. So in mathematics, the multiplication and division operators have lower precedence than addition and subtraction, so in this expression, the points are multiplied by 10 and then the result is added to 100.

If this is not what you want , you can always change the order Using parentheses, as an example, let's change this multiplication to addition and then put that multiplication here. In this expression, 10 is first multiplied by 100 and then the result is added to the points. Now let's say this is not what we want, then we can change the order using parentheses here. With these parentheses, we first get the points, add 10 to them, and then multiply the result by 100. So, these parentheses are useful for changing the order of operations and for adding clarity to our code. So that someone else reading this code can easily understand it. the order of these operations.

Now let's run this query one more time, all right, now look at the name of this column here, it's set to the expression we have on line 5. That doesn't make much sense, you want to have a clear descriptive name, so we can give it a alias this column using the as keyword. So as we move forward, we give it a name as discount discount factor. Let's run the query again. Now the name of this column has changed, so this is the benefit of using an alias, we can give descriptive names to the columns and result sets.

Now, if you want to have a space in the column name, you need to surround it with quotes, either single or double. So we put quotes here and then we can add a space between these two words. Let me run the query one more time. Now we have discount factor. So let's quickly recap everything you learned about the select clause. We make an asterisk return the columns, or we can explicitly specify the columns to return. We can also use arithmetic expressions here and optionally we can even mock an alias for each column in the result set.

Now there is one more thing you should know about the select clause. So let's delete this query and select the status column from the customer table. Have a look. These are the states in which our clients are located. Currently, in the sample data we do not have any duplicates; In other words, we do not have multiple clients in any of these states. But for this demo I want to change the state of the first client to Virginia, so we'll end up with duplicates in the result set. So let's open the browser panel, here's our customer table, look at all the data, and here's our first customer, here you can see it's located in the state of Massachusetts.

Now I want to change this to Virginia. So, double click on VA for Virginia, enter, now in the bottom right corner of the screen, you should see two buttons, apply and revert. Unfortunately I can't show you this button because the recording window is a little smaller than MySQL. But look down in the bottom right corner, click and apply. You'll see a dial-up box like this asking you to review the changes, so go ahead and click that button one more time. Okay, now let's go back to our first query window and run this query one more time.

As you can see, the first two customers are in Virginia. What if you wanted to get a single list of states in the result set? That's when we use a different keyword. So select a different set. With your query, we will retrieve a unique list of states from the customers table. So with the distinct keyword that we can remove, let's run the query one more time, now you can see that Virginia is not duplicated. Alright, here's an exercise. I want you to write a SQL query to return all the products in our database in the result set.

I want to see three columns, name, unit price and a new column called new price which is based on this expression, unit price multiplied by 1.1. So let's say you want to increase the price of each product by 10%. With this query you want to get all the products the original price and the new price. So pause the video and spend a minute or two on this exercise; when you're done, go back and look at my solution. Alright, this is pretty easy, so we start by selecting now what columns do we want to select? Name, unit price and then here we will use an arithmetic expression to calculate the new price.

So we write the unit price multiplied by 1.1 and then we give it an alias, either as new_price or we can put this in quotes and put a space between new and price. Now, where do we want to select these columns from? From the product table, that is, of products. Please note that I have used uppercase characters for all SQL keywords and lowercase characters for everything else. So let's go ahead and run this query, this is what we get, so these are all the products, you can see their original price as well as the new price which is 10% more expensive.

In this tutorial, we will look at the where clause in SQL. Before I told you that we use the where clause to filter data. For example, let's say we only want to get customers with points above 3000. So here, in the where clause, you can write the condition like this. Points, more than 3000. When we execute this query, the query execution engine in MySQL will iterate over all the customers in the customers table. For each client, you will evaluate this condition; if this condition is true, it will return that customer in the result set. So let's go ahead and run this, and here is the result, as you can see, you only have two clients with points greater than 3000.

So, thesis is what we call the greater than operator, which is one of the comparison operators in SQL . Let me show you the completeness of comparison operators, so, we have greater than, greater than or equal to, we have less than, less than or equal to, here is the equality operator, and for non-equality, we can use an exclamation. followed by an equal sign, or something like this. So both are not equal operators. Let me show you examples of these operators. So I'm going to delete all of these and bring back the previous query, let's say we want to get only the customers in the state of Virginia.

Then we can change our condition to something like this. Where the state is equal to Virginia. Note that I have put Virginia in quotes, because this is what we call a string. A string is a sequence of characters. So whenever you work with a sequence of characters, or basically texture data, we need to enclose their values ​​in single or double quotes. But very often, by convention, we use single quotes, so let's run this query and here is the result. You can see we only have these two customers with ID 1 and 2, which are located in Virginia. And it doesn't matter if you use uppercase or lowercase characters, so if you type a in lowercase and run the query, you'll get exactly the same result.

Now, what if you want to get all clients outside the state of Virginia? You can use the unequal operator. So we can prefix this with an exclamation or use this other notation. Either way we get the same result. These are customers who are not located in Virginia. Now we can also use these comparison operators for date values. For example, let's say you want to get only customers born after January 1, 1990. Then we change the condition to the first date, greater than then, once again using quotes to represent date values, even though the dates are not actually chains.

But in later language, we need to enclose dates in quotes, so here we write 1990, 01 for January, -01 for date. This is the standard or default format for representing dates in MySQL. 4 digits for the year, two digits for the month and 2 digits for the day. So let's go ahead and run this query. In fact, I made a mistake here, so we don't see the result, but rather we see the action result or result window. If you scroll to the bottom, you can see the details of the error. So here I used the wrong name for the column, which should separate these two words with an underscore.

That's the name of our column. So, let's run the query one more time, so that we only have 3 customers born after January 1, 1990. These are examples of comparison operators in SQL. In the next tutorial, I will show you how to combine multiple conditions when filtering data. Alright, here's your exercise. I want you to write a query to get the orders that are placed this year, so look at the orders table, see what columns we have there and based on that write the query with a where clause. Here is the order table, in this table we have this column, order date.

We can use this problem to see the orders that are placed this year, so here is our query, select, start from the order where order_date is greater than or equal to 2019, assuming this is the current year, so 2019, 0101. Now , since we are currently in the year 2019, this query will return all orders placed this year. But next year this consultation will not give us the correct result. But don't worry, later in the course I'll show you how to write a query to get orders placed in the current year, so for the purposes of the new exercise, this is a valid solution.

Now let's run this query and see what we get. So we only have one order, order ID 1, which ismade in the current year. In this tutorial, I'll show you how to combine multiple search conditions when filtering data. So let's say you want to get all customers who were born after January 1, 1990 and who also have more than 1000 points. This is where we use the and operator. So we write and, and then we write another condition. Similar points, greater than 1000. Now when we run this query, we only get customers who have both conditions. Let's take a look, so run, we only have 2 clients and if you look, both people were born after 1990 and have more than 1000 points.

So this is the and operator. When we use this operator, both conditions should be true. Unlike the and operator, we have the or operator. So if at least one of these conditions is true, that row will be returned in the result set. We'll see. Now we run this query again, 2 records we have enough records. So for example, we have this person who was not born after 1990, but if you look at his points, he has more than 1000 points. Therefore, all customer records that meet at least one of these conditions will be returned. Now let's take this to the next level.

Let's say we want to get customers who were born after 1990 or who have at least 1000 points and live in Virginia, this is how we do it: we type and then add another condition, state equals Virginia. Let's run this query and see what we get. We only get 4 records, so these are customers who were born after 1990 or have over 1000 points and live in Virginia. If you look at the first customer here, this person was not born after 1990. But you can see that he lives in Virginia and has over 1000 points. So the last two conditions. are true for this client.

Now, when combining multiple logical operators. It is necessary to know the order of these operators. Earlier I talked about the order of arithmetic operators. I told you that multiplication has a higher order than addition and subtraction. And we can use parentheses to override the default order. We have the same concept in logical operator, so the and operator is always evaluated first. So when this query is executed, the query execution engine first evaluates this condition, because here we are using one and it doesn't matter that we have written this condition after the first condition. Because the and operator has higher priority.

Now you can always change the order using parentheses. And this also makes your code cleaner and easier to understand. So here we can put parentheses around these last two conditions, and we can also put them on a new line for clarity. Something like this. So anyone reading this code can easily understand what the intent of this query is. Now we also have a logical operator called not. And we use that to negate a condition. So, I'm going to simplify our where clause. Let's say we are looking for clients who were born after 1990 or we have more than 1000 points.

If we run this query we get these people. Clients with ID 1, 3, 5 and so on. Now we can use the not operator to negate this condition. So we don't apply here, and preferably we also put parentheses around this condition. Now when we run this query, we see other clients that are not in the current result set. We'll see. So instead of customers with ID 1356 and so on, we get customers with ID 2, 4 and 10. Now, technically, these customers were born before 1990. And we have less than 1000 points. So if you look here, this first customer was born before 1990 and has less than 1000 points.

How do they know that? Let me show you a trick I learned in math. As long as you have a not operator. You can simplify your expression this way. We apply the not operator to the first condition. People who were born after 1990, how can we deny this condition? Well, the major operator that becomes less than or equal to. That is the inverse of that condition. Now we apply no to o to negate the o. What do we get? We get and. Finally apply the not operator in the last condition. We both have more than 1000 points. When we deny this condition, we get clients with less than or equal to 1000 points.

Now, you can remove the not operator to simplify this; We no longer need parentheses because we only have 2 conditions that are combined with an ending. Here is the result. As you can see, this is much easier to read and understand for people who were born before this day and have less than 1000 points. Okay, here's your exercise. From the order items table, get the items for order number 6, where the total price of the total item is greater than 30. Okay, here is the order items table, in this table we have these order ID columns , product ID, quality, and unit price.

If we multiply the quantity by the unit price we can obtain the total cost of that item. And then we can compare it to 30. So let's go ahead and write this query. Select star from the order items. Where here we need two conditions, one is for the order, so the _id of the order should be 6, and the second condition we want to calculate the total price. So we get the unit price, we multiply it by the quantity and this value must be greater than 30. As you can see, we can use an arithmetic expression in the where clause.

It is not limited to the select clause. Well? Now let's run this query and see what we get. We should get only 1 item, that is, for product 1, here in quantities 4 and the unit price is just over 8 dollars, so the total price of this item is more than 30. Hi guys, Mosh here. In case you haven't seen my website yet, head over to codewithmosh.com. This is my coding school where you can find many courses on web and mobile app development. In fact, I recently published a complete SQL course that lasts about 10 hours and teaches you everything you need to know, from basic topics to advanced topics, such as database design, security, writing complex queries, transactions, events and much more. further. .

These are the topics that every software engineer must master. This YouTube course you are watching is the first 3 hours of my full SQL course which is about 10 hours long. So if you want to master SQL and prepare for work, I recommend that you enroll in my complete SQL course. You can watch it anytime, anywhere, as many times as you want, you can watch it online or download the videos. The course comes with a 30-day money-back guarantee and a certificate of completion that you can add to your resume. The price of this course is $149, but the first 200 students can get it for just over $10.

So if you're interested, the link is below this video. In this tutorial, I will show you how to use the in operator in SQL. So, as an example, let's say you want to get clients who are in Virginia, Florida, or Georgia. One way to write this query is like this. So where one state equals Virginia, or one state equals Georgia, or one state equals Florida. Now, people who are new to the SQL language or programming in general find this expression a bit strange. They ask, "Mosh, why can't we write this expression like this?" where the state is equal to Virginia, Georgia or Florida.

This is why we use the or operator to combine multiple conditions. So here we have a condition or an expression more exactly, but on the right side of this u operator we have a string. In SQL we cannot combine a string with a boolean expression that expresses a boolean value that can be true or false. That's why we have to write our query this way. So we have multiple expressions or multiple conditions and we combine them using the or operator. So if we run this query, we get these clients here. clients here, but there is a shorter, cleaner way to get the same result.

Instead of combining multiple conditions using the or operator we can use the in operator. So where is the state and then in parentheses we add all the values, like Virginia, comma, Florida, comma Georgia and the order doesn't matter, this query is exactly equivalent to the one we had before, but you can see that it is shorter and more easy to understand. So let's run it, look, we get exactly the same result. Now here we can also use the not operator. Let's say you want the client to be outside of these states, so we can use rare states, which are not in this list.

Now if you run this query, we get clients located in Colorado, Texas, etc. Therefore, use the in operator whenever you want to compare an attribute with a list of values. Now here is your exercise. I want you to write a query to get the products whose stock quantity is equal to one of these values. 49, 38 and 72, so pause the video, do this exercise and then come back and continue watching it. Alright, this is pretty easy, so we select a star to get all the columns of the products table, where we use the in operator to compare this attribute with these values. 49, 38 and 72, let's run the query, we get only 2 records because we don't have a product with a quantity in stock equal to 72.

In this tutorial, we will see the enter operator in SQL, That means we want to get customers who have more than 1000 and less than 3000 points. What made you write these queries this way? Where points, greater than a thousand, more exactly greater than or equal to 1000. And points less than or equal to 3000. When we run this query we get how many, we get 4 people who satisfy my criteria. Now, whenever you compare an attribute with a range of values, you can use the between operator, and that makes your code shorter and cleaner. So, we can rewrite this expression like this, where the points, between 1000 and 3000.

This is exactly equivalent to what we had before, so these range values ​​are also inclusive, meaning that the points will be greater than or equal to 1000 . or greater than or equal to 3000. Let's run the query, we get exactly the same result. Alright, now as an exercise, I want you to write a query to get the clients who were born between January 1, 1990 and January 21, 2000. Alright, we start with the selected star of clients, where the date of birth is between then, what does it matter here? is that we can also use the between operator. It is not limited to the use of numbers.

So with the date of birth between now, we need to provide two date values, so as I told you before, the format for dates is four digits for the year i.e. 1990, two digits for the month and two digits for the date. So the date of birth must be between this value and here is a second value. 2000, 0, 1 and 01. Let's run this query, we get only 3 people who match this criteria. In this tutorial, I'll show you how to retrieve rows that match a specific string pattern, for example, let's say we only want to reach customers whose last name starts with 3.

So here in the clause where we write we find out where last name, this is where we use the like operator, and immediately after that we have a string pattern, so you want customers whose last name starts with b and we have any number of characters after b. Then use the percent sign to indicate any number of characters, you can have one character after b or no character or 10 characters, with this pattern, you get all clients whose patterns start with b. And it doesn't matter if it is uppercase or lowercase b. So let's run this query, there you have it, so you only have 3 customers whose last name starts with b.

As another example, let's say we only want to get customers whose last name begins with brush. So, we change our pattern to brush, percentage. Now, let's run the query, here we only get this client. Now, this percentage sign doesn't have to be at the end of the pattern, it can be anywhere. For example, let's say we want to find customers who have an e in their last name, whether at the beginning, middle, or end, then we change our pattern to percentage e percentage. This pattern means that we can have any number. of characters before or after b.

Let's run the query, these are the customers that have a b somewhere in their last name. It doesn't matter if b is at the beginning, in the middle or at the end. Let's look at another example, I want to get all the customers whose last name ends in y, so this is the pattern we use, let's run this query, so we have 5 customers whose last name ends in y. This is how we use the percentage sign. Now we also have an underscore that matches a single character, so with this pattern you get a customer whose last name is exactly two characters, we don't care what the first character is, but the second character must be y.

Let's run yours for you. Obviously we don't have any clients whose last name matches this pattern. But if you change this pattern to 5 under score, then 1234 5, followed by a y, we should get these customers. So your last name is exactly 6 characters, we don't care about the first 5 characters, but they all end with a y. Now, as another example, we can replace thefirst underscore with b, and that means we want customers whose last names start with b, and after b we have exactly 4 characters followed by a y. Let's run this query. So we only have one client that matches this pattern.

This is how we use the light operator, we use the percentage sign to represent any number of characters and an underscore to represent a single character. Now this similar operator in MySQL is an older operator, but we also have a newer one which is more powerful and allows us to search for any string pattern. And that's what I'm going to show you next. Alright. Here I am going to give you two exercises for the similar operator. First, I want you to get the customers whose addresses contain business or avenue, and then I want you to get the customers whose phone numbers end in 9.

Okay, let's start with the first exercise. So, start from the customers whose addresses are like now. Here we want to use a search pattern like this. You want to have a tray (?), but the tray can be anywhere in the address. So, we put a percentage before and after the tray. Next, we must use the or operator to find another pattern. Or direction like, let me put this on a new line that's better, direction once again, percentage, avenue percentage, that's it. So let's run this query, here is the result, you should get the customers with IDs 2, 9 and 10.

If you look at their addresses, they all have tray or avenue in their address. Now let's work on the second exercise: you want to get the customers whose phone numbers end in 9. That's easy enough, so let me change our where clause. On the phone, we again use the like operator and a percentage, followed by a 9. That's all you have to do. Let's run the query. So, here is the result, customers with IDs 3 and 7, their phone numbers and 9. This is how we use the similar operator. And by the way, you can always use the not operator here, let's say you want to get the customers whose phone numbers don't end in 9, so we just prefix it not.

Now if we run this query one more time, we get all the other clients in the database. In the last tutorial you learned about the Like operator in SQL. As another example, let's say you want to find customers who have the words field in their last name. So we write the where clause this way, where last name, as percentage, field, percentage, so the word field can be anywhere in the last name. Let's run this query, we get just one client, beautiful. Now we also have another operator in MySQL, reg x, which is short for regular expression, and regular expressions are extremely powerful when it comes to searching for strings.

That is why they allow us to look for more complex patterns. Here is an example. If I want to rewrite this last where clause using a regular expression, it seems. Where the last name ragexp now here in our string pattern, we don't have to write the person size, we just write the field, so what we have on line 4 is exactly identical to what we have on line 3, let's run this query we get. Same result, beautiful, now here in regular expressions, we have extra characters that we don't have when we use the like operator. For example, we can use the carrot sign to indicate the beginning of a string.

So if I put a carrot right before the word field, that means our last name should start with field. Obviously, if you run this query, we don't get anyone that matches this criteria, so we use the carrot sign to represent the beginning of a string. We also have a dollar sign to represent the end of a string. So this pattern means that the last name must end with the field. Let's run this query, you will get the same result as before. Now we can also search for multiple words here, for example, let's say we want to find customers who have the word field or mac in their last name.

So, we use a pipe, a vertical bar and write another pattern. Let's run this query, so here we have two clients, one of them has the word mac, the other has the word field and last name. Now we can take this to the next level. Let's say we want to find customers who have the words field, mac, or rows in their last name. Let's run the query, we get 3 clients. Beautiful. So we use a vertical pipeline or board to represent multiple search patterns. Now, as another example, we can change our first search pattern to something like this.

Now this pattern means that this last name must start with the word field, or it must have the word mac, or it must have the word rows. Let's run the query, now we only get two clients, because our client with the last name brush field does not match this pattern. However, if we change our first pattern to the $ field and run the query we have 3 people here, 3 clients. This is how we can combine multiple special characters when building a complex pattern. Now let's look at another example, let's say you want to find customers who have an e in their last name.

So these are all people, all right, now let's say you want to make sure that before the letter e, we should have a g or an i. This is where we use square brackets. And inside the brackets we add several characters like g, i, m and that matches any client that has ge, ie or me in her last name. So any of these characters can come before e. Now let's run this query, there you have it, we only get 2 customers, and the first example before e we have i which is one of the characters inside the square brackets, in the second example, before e we have a g which is also another character valid before and once again the square brackets don't have to be before we can add them after e, any client who has e followed by an f or an m or a q in their last name, can return with this pattern, if we don't have anyone in the database, this is how we use square brackets, now we can also provide an array of characters, for example we can have e and just before e you can have as many characters from a to h, you don't need to write them explicitly like abcdefg, that is very detailed, so we can write from a to h.

And then if we run this query, we get these three people. So let's quickly recap everything you learned about regular expressions in this tutorial. We use a carrot to represent the beginning of a string, so starting we use a dollar sign to represent the end of a string, we use a vertical bar or pipe, to represent a logic or so that we can provide multiple search patterns we use square brackets to match the individual characters listed in square brackets, and finally, we use square brackets with a hyphen to represent a range. So any character from a to f.

Technically, MySQL supports more special characters, but honestly, the ones I've listed in this tutorial are the ones you'll use 90% of the time. So memorize them and go. Honestly, many

beginners

find the syntax of regular expressions confusing, so in this video I will give you 4 exercises that I have carefully designed to help you quickly learn about this syntax. This is the first exercise: get the name of the customer whose name is Elka or Ambur. And note that this is Ambur with a U. Now for the second episode, return the name of the customer whose names end in ei or on.

Here is the third exercise: get the customer's last names that start with my or contain se and finally, as the last exercise, return the customer's last names that contain e followed by r or u. So, go ahead and spend 2 or 3 minutes on this exercise; When you're done, come back and continue watching. Alright, let's eliminate the first exercise. So we'll get permissions, there's a c here, so let's start with the regular expression, and here's our pattern, it'll look for two words, Elka or Ambur. As simple as that. Let's run this query, we should get two clients, there you have it, Ambur and Elka.

Alright. Now, let's eliminate the second exercise. So I'll delete them, we don't need them anymore. Therefore, we want clients to select starting from clients, where the last name must end with ey or om. So in the search pattern we type ey followed by a dollar sign to indicate the end of a string and then add a vertical bar to provide the second search pattern. So on and again the dollar sign. Let's run this query. Oops, I forgot to write the regex, there you go. So let's run this query and it should get these four customers with ID one three, five and seven.

The first three, five and seven, the first three, their last names end in ey, and the last customer, their last name ends in on. Very good, now let's work on the third exercise. So here I'm just going to change the regex, we want to get the customers whose last name starts with my or contains se. So we use a carrot to indicate the beginning of a string, so it should start with my, or it should contain se, again, very easy, let's run this query and get the customers with ID 4, 8 and 10 and finally we want to get the customers whose last names contain e, so let's change the search pattern, we should have a b, followed by r or u.

Now there are two ways to write this regular expression, we can use square brackets to have b followed by r or u, that's one way, or the other is to use a vertical bar. So b r o bu. Both are valid solutions. I hope you have solved these exercises. In the next tutorial I will show you how to get the records with missing values. In this tutorial, I'll show you how to find records that are missing an attribute. For example, if you select all customers in our database, you can see that the customer with id 5 does not have a full number.

So if you look closely here, you can see the value of null. Null means the absence of a value. Now let's say we want to find all the customers who don't have a phone. You might want to email them and say, "your phone is not in our system." So how can we get these clients? That's very easy? We use operator is null, so in the where clause we write where phone is null. Now let's run this query, we only get a customer who does not have a phone, now here we can also use the not operator to get the customers who do have a phone.

Then we change the conditions so that they are not null. Let's run the query, now in the query results, each customer has a phone number. For this exercise, I want you to write a query to get the orders that have not yet been shipped. This is a very useful query that is used in many real applications. For example, let's say you are an administrator of an online store. You want to view orders that have not yet been shipped so you can see them all. So write an inquiry and receive these orders. Here we have the order table.

Let's take a quick look at the data in this table. So if you pay close attention you will see that some of these orders do not have a date. And these orders also do not have a shipper ID, which is a unique number to identify shippers. Therefore, any order that does not include the ship date value or shipper ID is considered a non-shipping order. So let's go ahead and write a query to get these orders. So let's go back to our query editor, select the orders star where ship_date is null. You can also write that the sender id is null.

They both have the same reason. So let's run this query and it should get 5 orders. Orders 1, 3, 4, 6 and 8. In this tutorial, I will show you how to order data in your subsequent queries. So here you have a query to select all customers from the customers table. If you look at the query result, you can see that our client is sorted by id, so we have clients 1, 2, 3, 4, etc. in. This is the default sort column. But we can always change this by using the order by clause. But first, let me explain why the customer id column is the default sort column.

So first of all, I'm going to open the browsers panel on the left side, here's the customer table, now let's click on this middle icon that looks like a tool. This opens our customer table in design mode, here we can change our columns, we can add a new column or delete existing ones or change that name and order etc. Now, if you pay close attention, you can see a yellow key right before it. This means that this column is the primary key column. For this query. So in relational databases, each table must have a primary key column and the values ​​in that column must uniquely identify the records in that table.

Going back to our query window, you can see that the values ​​in this column uniquely identify each customer, so the customer id column is the primary key column in this table and that's why when you write a query In this table, our clients will sort by id by default. Now,let me show you how to sort clients by a different column. So here in the order by clause, type the name of another column, like name. Let's run the query, now we can see that our clients are no longer sorted by their id, but by their name in ascending order.

Now, if you want to reverse the sort order, simply type de, e, sc, which is short for descending. Now, you are arranging the customers in this order. Okay, we can also sort the data by multiple columns, for example, let's say we first want to sort the customers based on their status and then within each status, we want to sort them. by his first name. So, we write several columns here, status and name. Let's run the query now, you can see that the first state we have here is California, followed by Colorado, and new here in Florida, you have two clients and these clients are sorted by name.

Let's take a closer look here, first we have Ambur and then we have other clients here. Now we can also use the descending argument anywhere here. For example, we can sort these customers by status in descending order. And then sort them by name in ascending order or once again in descending order. So there are several ways we can sort the data. Now, one of the differences between MySQL and other database management systems is that in MySQL you can sort data by any column, whether that column is in the select clause or not. For example, let's say you only want to select the first and last name of each customer.

Now we can sort the result by any column in this table, it doesn't have to be first and last name. For example, we can sort them by their date of birth, take a look, this is a valid query in MySQL, but other DBAs sometimes yell at you when you update a query like this. Now we can also sort the data by an alias, for example, here in our select clause let's add the number ten and give it an alias. Like let's say points, points is not a valid column in this table, it is simply an alias or expression, in this case a simple number.

And here we could have a complex mathematical expression, it doesn't really matter, we can still sort the data by Alias, so we can sort by points and then by name. Again, this is a valid query from MySQL's point of view. Now one last thing before we finish this tutorial, I have seen some tutorials that teach you how to sort data by calling positions, for example, here we can sort by 1, 2, and that basically means sorting the data by the first. first name and then the last name. So these are the orders of these columns, if you run this query you can see that our customers are sorted by their first name and then by last name.

Why this approach works is something you should avoid. Because if in the future you come back here and add a new column, in front of the name column, let's say first, date. Now our clients are no longer arranged in this order. Therefore, sorting data by calling positions produces unexpected results and is something you should avoid. Always sort by column names first. Alright, here is the exercise for this tutorial. In this database we have this table, called order items, where we find the items for each order. Now, I have written the query that you cannot see here, because that is the solution to the exercise that I am going to give you, that query produces these results.

So, we only have the items for order with ID 2 and we have sorted them based on the total price of each item. So, the total price of each item is equal to the quantity multiplied by the unit price. In this case the total price of product 1 is just over 18 dollars. So go ahead and write a query to select all items to order with ID2. And sort them by their total price in descending order. Alright, let's select everything from the order items, where the order id equals 2. That returns all the items for this order. Now we want to make sure we sort them by their total price, so here, sorted by clause, we write an expression.

Quantity multiplied by the unit price, this returns the total price of each item. And then we add the descending argument here. So once again, the expression we use in the order by clause doesn't have to be the column name, it can be an alias or an arithmetic expression like this. Let's go ahead and run this query. This is what we get now for clarity. I would like to add another column in the result. So let's say quantity times unit price. We give it an alias as the total price. Let's run the query, you can clearly see that this data is sorted by total rice in descending order.

However, there is a bit of duplication in our query, we need to repeat this expression in two places. Now we can simplify our by clause, using an alias that is the total price. And we got exactly the same result. Net, I'm going to show you how to mimic the number of records returned by your queries. In this tutorial, I will show you how to limit the records returned by the query. For example, when we return this query we get all the customers in the customers table, so we have ten customers here. Now, what if we only want to get the first 3 clients?

That's where we use the limit clause. Let me teach you. So after, write limit 3 and this will return only the first 3 clients. Now, if the argument we pass here is greater than the number of records our query produces, we will get the records in the query result. For example, if I pass 300 here, obviously we don't have 300 customers in this table, so when we run this query. We get the ten clients in this table. This is how the limit clause works. Now here we can optionally provide an offset, and this is very useful in situations where you want to paginate the data.

For example, let's say we have a website and on this website we have a web page for the user to see all the customers in the database. Now, for simplicity, let's imagine, we want to show only there customers per page. So what are the customers you will have on page 1? We will have 123 customers. On page 2 we will have 456 customers and on page 3 we will have 789 customers. Now, let's say we want to write a query to retrieve the customers on page 3. . How can we do that? Well, you want to skip the first 6 records and then choose 3 records.

So, we changed our limit clause to something like this. 6 and 3. So 6 is what we call an offset. And that basically tells MySQL to skip the first few records and then grab 3 records. Let's run this query. Alright, now we have 789 customers. Now for exercise, I want you to get your top 3 loyal customers. These are the customers who have more points than everyone else. Alright, first we select everything from the customers table, now we need to sort these customers by their points, in descending order. So if you look at the query result, you can see that the customers are sorted by their loyalty.

So the most loyal customers come first, now we want to choose only the first three customers, and that's where we demand the limit clause, so let's limit, let's run this query. And these are the most loyal customers, customers with IDs 56 and 3. Now, there is one thing I want you to remember and that is the order of the limit clause. The limit clause should always come last. So first we have the select clause and then we have from, optionally, where can have, followed by order by and finally limit. The order of these clauses matters. If you change the order, MySQL will yell at you.

So pay attention to the order when writing your queries. So far you have only selected columns from a single one, but in the real world, we very often select columns from multiple tables. And that's what I'm going to show you in the next few tutorials, on the left side if you put in our order table. Let's select all the data here, in this table we use the customer id column to identify the customer who placed each order. Now, as I told you before, we do not store customer information here, such as their phone number, email, and address, because this information may change in the future.

And if even a customer has placed multiple orders, then we have to change multiple records, you don't want to do that. That's why we have separate tables for customers and orders. Now in this tutorial, I will show you how to select orders in the orders table, but instead of showing the customer ID, we show the full name of each customer. So let's go back to our query window. Okay, so we want to select everything from the order table. Now we should combine the columns of this table with the columns of the customers table. That's where we use the join keyword.

And here we can optionally write inner join, because in SQL we have two types of inner join and over join. You'll see outer joins later in this section, so for now we just use the inner join and these inner keywords are actually optional so we don't have to write them. So, you want to join the orders table with the customers table. Now, on what basis do we want to join these tables? Well, here in the customer table we have this customer id column. So if you put these two tables next to each other, you'll want to align the records so that the customer IDs are the same.

That's where we use the phrase on. So after writing a condition, here is a condition we need to write: orders.customer_id. Must be equal to clients.clientid. Now, this is going off the screen, so let's split the line, that's better, so with this query, we tell MySQl that, hey, whenever you go to the orders table with the customers table, make sure that The Customer ID column in the Orders table is the same as the Customer ID column in the Customers table. Now let's run this query, look at the result, since we are selecting everything here, the first columns are from the orders table, because we have listed them first after all the columns from the customers table.

So the customer ID is the first name, last name, etc. Now let's simplify the result set and select only the edge id, first name and last name, so that in the query we select the order id, first name and last name. Now let's run the query which is better. So next to each order ID, you can see the name of the customer who placed that order. Now what if you want to display the customer ID here as well? Well, let's leave that here and see what happens. Customer ID, when we run the query we get an error, so if you look at the output window, at the bottom, we should see an error saying column, customer ID, field, this is ambiguous.

Unfortunately, I can't show you this error now because my recording window size is smaller than the MySQL workbench. But apart from that, let me explain why we are getting this error. Because we have this customer ID column in both the orders and customers table, MySQL is not sure which table we want to select this column from. That's why it says this column is ambiguous, so we need to qualify this column by prefixing it with a table name. We can choose it from the orders table or from the customers table, it doesn't really matter, because the values ​​are the same, right?

So in situations where you have the same column in multiple tables, you should qualify them by prefixing the table name. Okay, now we say consult one more time, there you have it, you have order. ID, client ID and full name. Now one more thing before we finish this tutorial, if you pay close attention we have repeated the word order in several places, you have it here, as well as in the join condition, the same goes for the customer table, you have repeated it. that here. We can get rid of this repetition and simplify our code by using an alias.

So write after each table, you can do it in aliases, like an abbreviation of orders, so by convention we abbreviate the table name. Now, wherever we have orders, you need to replace them with or. So here, in the join condition, we will replace orders with or, and also once again, in the select clause. There you go. You can also alias a client table, call it c, and then simplify our join condition like this. This is how we can join columns for multiple tables. Now, for your exercise, I want you to look at the order items table.

So in this table we have these columns, Order ID, Column ID, Product ID, Quantity and Unit Price. Now I want you to write a query and join this table to the products table so that for each order it returns both the product id and this name, followed by the quantity and unit price from the order items table. And by the way, be sure to use an alias to simplify your code. Alright, let's first select everything from the order items table and then join it with the products table. How are we going to join this table? in order_items.

Well, actually let's keep thisin Alias ​​immediately, so we use oi, as an abbreviation for order items. And p as an abbreviation for products. So oi.product id, must be equal to p o products. Product ID. And by the way, remember that alias for the table, you should use that alias everywhere. So here I can't write product, MySQL is going to yell at me. So let's use the abbreviation. Alright, this is how we join these tables, let's run this query up to this point. Okay, so we see all the items in the order items table, followed by the products table.

Now, you want to explicitly select some columns here. So to sort the items table you need to select order_id. We take it then we don't have to prefix it with a table name because this slander is not repeated in multiple places, so it is unambiguous. So let's make the code shorter, that's better, now we want to select the Product ID column, but because this column exists in both tables, both tables need to prepend a column name to it. Oi or p doesn't really matter. So next we want to select the quantity and finally the price.

Now, here you have this unit price column. In both tables, this is the unit price in the order item table and this is the unit price in the product table. Now you might be curious why we have this column in 2 places, because the price of the product can change, so for each item in the order, you want to have the price at the time the user placed the order. So this is a snapshot of the price at any given time. The unit price we have for the product table is the current price at this moment. This is very important for recording, otherwise we will not be able to calculate sales correctly.

So because we have the unit price column in two places, in this case we need to select it from the quarter items table because this is the price right now, let's run the query. So here is the final result. In the real world, when you work as a developer or database administrator, you will quite often have to work with multiple databases. In this tutorial, I'll show you how to combine table columns in multiple places. That's pretty easy. So in the SQL warehouse database, you have these tables that you are quite familiar with. Now imagine that this product table is not here.

Now if you look at the database, you can see here another products table, this products table is exactly the same as the products table that we have in a SQL store database, so it has the same columns and the same data. Technically this is not a good design, you don't want the same table repeated in multiple places. But for this demo, let's imagine that we don't have the products table here, so we want to join the order items table with the products table in the SQL inventory database. Let us begin. So select everything from the order items table, let's give it an alias right away, you want to join this with the products table.

This product table is part of the product inventory database. So we'll have to prepend this with the name of your database. So, we write the SQL inventory. Now, once again, we can give this an alias like p, then write our joint condition, so oi.product id should be the same as p.product id. Let's run the query there, to successfully join tables in multiple databases. Now note that we are prepending the products table with the database name because the current database we are writing this query against is the database stored in SQL. Take a look, in the browser panel, the SQL warehouse database is displayed complete.

Because before, the root we use to select a database, which will be stored in SQL. Now what happens if we select the SQL inventory database? So let's see what happens with SQL inventory. Now, because we have multiple statements, we have to end this with a semicolon. Now we want to select everything with an order items table. But we don't have this table inside this database. So now we'll have to prepend this table with the name of your database, which is the underlined history of SQL. Let's run the query, okay, everything works wonderfully. So here's the lesson.

You only have to prefix the tables that are not part of the current database. In other words, the query will be different depending on the database. In SQL we can also join a table with itself. Let me show you an example. Take a look at this database. SQL hr, in this database we have these two tables, employees and offices. Let's take a look at the data in the employees table. There you go. Here we have these columns, employee id, first name, last name, salary and reports 2. This is the manager id of this person or this employee.

Now once again you don't want to repeat the administrators information here like phone number, address because this information may change in the future so we only use their handle or their id to refer to them in this table . Now where can we find information about this? This manager is actually an employee of the same organization, so look at this example, the manager ID is 37 370. Now, if you search the website, here is the ID of that manager, which is another employee. That was this employee's manager. We have no value here, so the sale value is zero. So this employee doesn't have a manager and that means he is the CEO.

So let's go ahead and write a query to join this table to itself so that we can select the name of each employee and their manager. Back to our query window, we first need to select the SQL hr database. Next we select everything from the employees table, give it an alias like b, now we need to join this table with itself. So once again we type employees, but we need a different alias. What should we call this alias? Well, you want to join this table with itself so we can find the managers, right? So we can use m as a shortcut for managers.

Now, let's write our joint condition. So, from the employees table, we need to join to the reports_2 column. To the manager table, which is basically an employee table, and that's Employee_id. Now let's run this query and see what we get. So, we see that all the columns of the employees table repeat the first set of columns, they represent the information about the employees and after that, we have the information about the managers. In this case, we only have one manager in this table. But with this technique we can easily create an organization test. We can have a hierarchy of managers.

Now let's just say the name of the employee and the manager. So right here, since each column in the employees table is repeated twice, we need to prepend each column with a table name. For example, from the employees table you want to get the employee ID as well as the name, and then for the managers table, we also want to select the name. So each column must be prefixed with a table name or, more accurately, the alias because all these columns exist in two tables, right? Let's go ahead and run this query, so this is what we get, employee id and here is the manager name.

We can improve this query by giving this column an ​​alias. Because it doesn't make sense to have two name columns. So let's give it an alias.To the third column, manager. Now, let's run it one more time. And here is the final result. So, we have the empty ID, name and object. So joining a table to itself is practically the same as joining a table to another table. The only difference is that we have to use different aliases. And we have to precede each column with an alias. This is what we call self-union. Next, I'll show you how to join more than two tables.

In this tutorial, I will show you how to join more than two tables when writing a query. For example, going back to our SQL database, look at the orders table, now you know how to write a query to join this table with the customers table to return information about the customer to place each order. But here we also have another column, status, which is similar to the customer ID status. Therefore, the status names are not stored in this table, but are somewhere else in the order status table. Let's take a quick look here. Our orders can be processed, shipped or delivered.

And these are the identifiers of each of these states. Now let's go back to our order table. In the state column it stores the state ID. So now we should write a query to join the orders table, which will be two tables. The customer table and the order status table. The result of this query will look like this. So for each order we see the order ID, the date, the customer's first and last name, and finally the order status. This is a beautiful report that we can create for our users. Let me show you how to write this query.

Back to our query editor, first we need to select the SQL Store database, now we need to select everything from the orders table, that will be the alias, then we need to join this with the customers table. On, that customer id must be the same to see the customer id. Nothing new so far. Exactly as before. Now here we can write another joint keyword. To join the order status table of other keywords. Then we write the order status. And also give it an alias, SO, what is our join condition? Well, back in the orders table, here we have the status column.

Therefore, the value of this column must be equal to the order status ID column in the order status table. Good? So let's go back to the query, so the order table status should be equal to the order status ID. Make sure you type the correct name; otherwise you will get an error. This is how we can join three tables. Now, in the real world, while working on more complex problems, you will end up joining even ten tables. So this is not uncommon in the SQL world. Now let's go ahead and run this query. First we get the column from the orders table, followed by the orders from the customers table, and so on.

This result is very complex and difficult to extract information. of. So let's explicitly select some columns here. In the orders table, you want to select the order id column as well as the order date. Then, from the customer table, we want to select the first and last name. And finally from the order status, we select the name column. So we can give this an alias-like state which is better. Let's run the query one more time. So, here is the final result: we have the order ID, the order date, the customer name, followed by the order status.

Alright, for a workout, take a look at the SQL billing database. Here we have this table, payments, and these are the payments that each customer has made for any of the invoices. Let's take a look at the data, so we have these columns, like customer id, which identifies the customer, so we can join this table. with the customers table to see the name of the customers. Next we have the invoice ID, we also have the date, the payment method. Similarly, we can join this table with the payment methods table here, let's take a look at the data in this table, these are the payment methods, credit card, cash, PayPal, bank transfer.

So, going back to the payment table, I want you to write a query and join this table to the payment methods table as well as the customer table. Generate a report showing payments, with more details such as the customer name and payment method. Alright, first we need to use the SQL billing database, now we can select everything from the payments table which we call e, then we need to join this with the customer table which we call c, in p.clientid it should be equal to c.client id. Let me double check the column name to make sure I got it right, so let's go back to the paytable, the column is called customer element.

You also have a column called payment method, which we need to join to the payment method ID column of the payment methods table. So, coming back to the query, once again we use a join statement here, join with payment methods, give it an alias pm, on p. the payment method must be equal to pm. identification of the payment method. Make sure you type it correctly, otherwise you will get an error. So let's go ahead and run the query up to this point, finally manually selecting the columns that make the most sense. So in the payment table, let's select the date, followed by the invoice ID, what else do we have here?

So we have the customer ID, the invoice ID, the date and now the payment method. I'm going to select the amount column from here as well, so let's go back to the query, p .amount. Now we need to add information aboutthe client. Let's take a look at this table, customers, here we have columns like name, address, city, etc. All we need here is the name column. So, going back to the query, from the customer table let's select the name column and finally form the payment methods table, let's select what that column is called, it's called name.

So let's go back to the pm.name query. So here is the final result. Now we can put this column in any order we want, it doesn't really matter, let's run the query and make sure everything works. So on this date, on this invoice, we have a payment for this amount from this customer using a credit card. In the examples you've seen so far, we use a single column to uniquely identify the rows in a given table. For example, in the customers table, we have this customer ID column that uniquely identifies the rows in this table.

But there are times when we cannot use a single column to uniquely identify the columns of a given table. For example, look at the order items table. In this table, we have columns like Order ID, Product ID, etc. Now, if you look at the data, you can see that the values ​​in the order id column are repeated. They are duplicated. You have 2226 and so on. Therefore, we cannot use this column alone to uniquely identify each record. The same goes for product identification. The values ​​in this column are also duplicated. So in this table we use the combination of the values ​​in both columns to uniquely identify each element in order.

As an example, in this order we have 3 items, for products 1, 4 and 6, and for each product we have a quantity and a unit price. So if we use the combination of the values ​​in both values, we can uniquely identify each item in the order; In other words, we don't have two records for Order ID 2 and Product ID 1, we only have a single record for that item, now let's open this table in design mode. Here we find this middle icon that looks like a tool. Note that this yellow key representing the primary key exists in both columns. This is what we call a composite primary key.

The composite primary key contains more than one column. Now, why does this matter? Well, when you have a table with a composite primary key. You need to learn how to join that table with other tables. For example, here we have this table, Order Item Notes, which we use to keep notes on each order item. Let's look at the data here. So we have this column note id, right? Which uniquely identifies the records in this table, next to that we have the order ID and the product ID. You will learn that the combination of these two columns uniquely represents an order ID.

Here, for order number 2, for product number 1, we have two notes. Now let me show you how to join this table with the order items table. So, going back to our query, you can see that I've already selected the SQL warehouse database, so I'm not going to write a U statement. Okay, let's select like everything from the order items table. Give it an alias, now we need to match this with the order item notes, we also give it an alias. How are we going to join these tables? Based on two columns, going back to the order items table, these are the columns we should use in our joint condition.

So in the order item table we have this order ID column, which should be equal to the same column in the order item notes table. So, order id. But this is not enough, we should also join these tables based on the product ID number. So, we write and, then we write the second condition, to sort the items. The product ID must match the order item notes. Product ID. This is what we call a compound joint condition. So we have multiple conditions to join these two tables. In this tutorial I am going to talk about implicit joint syntax in MySQL.

So here we have a basic inner join, we select everything from the orders table and join it with the customers table on orders.customerid. Equals clients.clientid. Pretty basic. There is another way to write this query using implicit join syntax. Let me show you how this works. So, we select everything, from now here we can write several table names. so, orders, customers. And we can also give them an alias, so c and o. And then we move this join condition to the where clause, so I'm going to copy this from here, type the where clause, and paste the condition.

These two queries are equivalent. What we have here is called implicit joint syntax. Now, although MySQL supports the syntax, it's something I suggest you don't use, because if you accidentally forget to type the where clause, you'll get a cross join. Let me show you what I mean. First I'm going to delete the first query. And run this to get ten records because we have 10 orders in this database. So far, so good. What happens if you accidentally type the where clause? Instead of 10 records, we will probably get 100 records. Because every record in the orders table is now joined to every record in the customers table.

This is what we call cross twist. Later in this section I will talk about cross joins in more detail, but what I want to talk about in this tutorial is that it is best to use an explicit join syntax, so we use join because this syntax forces you to write the join condition; If you simply join orders with customers, without writing the join condition, you will get a syntax error. In summary, keep in mind the implicit join syntax, but write all your joins using the explicit syntax. Earlier in this section I told you that in SQL we have two types of forms.

Interior unions and exterior unions. And so far you've only seen examples of inner joins. And I also told you that this inner keyword is optional, so every time you type a join, you are using an inner join. In this tutorial, we'll look at outer joins and the problems they solve. So let's start by writing a query that uses an inner join and then we'll convert that inner join and outer join. So select everything from customer table and join it in orders table, in c.customer id should be equal to o.customer ID. Pretty basic right? Now, for clarity, let's select some columns from these two tables.

So for the customer table I want to choose the customer id. And the name, and from the order table, I want to choose the order id. Now, finally, let's sort the results so we can clearly see what we get. Then sort by c.customer id. Let's run that query and see what we get. So here is the result. For customer number 2 called inis or inis whatever, he has 2 orders, order 4 and order 7. Similarly, for customer number 5, we have 2 orders and so on. Now, something is missing from this result. We only see customers who have an order in our system, these are customers 2, 5, 6, 7, 8 and 10.

But if you look at the customer table, you can see that you have other customers like customer number 10, customers number 3 , and so on. We currently do not have any orders for these customers and that is the reason we do not see them in this result set. But what if you want to see all customers, whether they have an order or not? That's when we use an outer join. Let me show you how it works. So, going back to our query, the reason we only saw clients who had an order was because of this join condition. Joining these two tables returns only records that match your condition.

So for a given customer, if we have an order, that record is returned. But as you saw a second ago, some customers don't have an order. So for those clients this condition is not valid. And that is the reason why they are not returned in the result set. To solve this problem we use an outer join. Now in SQL we have two types of outer joins. We have left joins and right joins. When we use a left join, all records in the left table, in this case customers, are returned regardless of whether this condition is true or not.

So we get all the customers and if they have an order, we will also see the order id. Let's run this query and see what we get. There you go. Customer number 1 does not have an order and that is why we get null in this cell. Customer number 2 has two orders, 4 and 7, customer number 3 also does not have an order, so we get a null value for the order id. This is the result of a left join. So, going back to our query, when we use a left join, all records in the left table are returned whether this condition is true or not.

Now, what happens if we use a correct combination? In this case, all records in the orders table are returned whether this condition is true or not. Let's run this query and see what we get. So, we get the same result as before. when we use an inner join, because we are selecting all the records from the right table which is the orders table, so we don't see all the customers, we see all the orders. Now if you want to use a correct join and still see all customers, you must change the order of these tables. So, we place the order table first, which will be our table on the left.

And then we put the customers on the right side, so now with this query we will return all the records from the right table, which is the customers table. We run this and get all the customers whether they have an order or not. Beautiful. Now, one last thing before we end this tutorial, I've seen developers use the external keyword here. So, right outer join or left outer join. But technically the external keyword is optional just like the internal keyword, so you don't need to type it. So I'm going to remove this to make the code shorter and easier to understand.

So to summarize, if you have the join keyword directly and if you do a left or right join, you are doing an inner outer join. Here is your exercise for this tutorial. I want you to write a query that produces this result. So we should have three columns here. Product ID, name and quantity I selected from order item table, so here we need to join to product table. With order items table. This way we can see how many times each product is ordered. However, in an inner join we will only see the products that have an order, but here I am doing an outer join, so product number 7 has never been ordered, but still exists in the result.

Is it null or the amount? So go ahead and write an outer join or use this result. Alright, first we select everything from the products table and then join on the left with the order items table. Our join condition is p.product id equals oi.product id. So, we will get all the products in the products table whether this condition is true or not. If you have never received an order, you will still see it in the result. Now, let's choose some columns for clarity. So p.productid p.name and oi. amount. That's all. Let's run the query.

We obtain the same result as before. So all the products are here. And product number 7 has never been ordered, so we see a zero quantity. Similar to inner joins, we can use outer joins between multiple tables. Let me teach you. Here is the query we wrote in the last tutorial: You are doing a left join between the customer and orders tables. So when we run this query, we get all the customers whether they have an order or not. If you have an order, you will see the order ID. Beautiful. Now if you look at the orders table, you can see that some of our orders have a shipper ID.

These are the orders that have been shipped. Now let's join the orders table with the shippers table to display the shipper name in the result. So let's go back to our query, after this left join, let's write another join, here I'm doing an inner join. So let's internally join the orders table with the shippers table. Join carriers, we call it sh, depending on what the joining condition is. Well, oh.shipper id, it should be equal to sh.shipper id. Alright? So in this query we have a left outer join and an inner join. Let's see what we get, okay, we only see 5 records.

But you have more requests. So here you have the same problem we had before. Some of our orders do not have a return address, and that is why they are not returned here. In other words, this join condition is not true for some of our orders. Going back to the orders table, for example, this first order does not have a sender identifier that is null and that is why it is not returned with a query result. Then forTo solve this problem, you must use a left join; You want to make sure that all orders are returned whether they have a shipper or not.

So coming back to the query, you need to replace this inner join with the left join, so let's run the query and see what happens. We should have quite a few more orders now, there you have it. Now, to make this example more interesting, I'm going to add the ship -by name here. So let's go back to our select boss, add a new column, shipper.name. Now we can give it an alias as sender, let's run the query and here is the result. So we welcome all customers, whether they have an order or not. And for those who have an order, we receive all orders whether we have a carrier or not.

This is the power of outer joins. Now, in the last tutorial, you learned that you can get the same result in both the left join and the right join, you just have to swap the order of the tables. However, as a best practice on what to avoid using right joins, because when you join multiple tables and have left, right, and inner joins, things get really complex. Therefore, someone else reading your code will have a hard time visualizing how you join these tables. For example, if you have a right join here and then a left join after, it will be more difficult to visualize how these tables are joined.

Therefore, as a best practice, avoid right joins and use left joins instead. And here is your exercise for this tutorial. I want you to write a query that produces this result. So here you have these columns order date, order id, customer name, shipper and we can see that some of our orders have not been shipped yet. So here we have null and here, finally, we have the state. So go ahead and spend 2-3 minutes on this exercise and when you're done, continue watching it. Alright, let's select everything from the orders table, now you need to match this with the customers, in hold.customerid.

It must be equal to c.client id. Here I am using an inner join, because each order has a customer. So this condition is always valid, no matter if we use a left join or an inner join here, okay? Now before we continue, let's take our columns. So from the order table, I will choose the order id. Followed by the order date. And then, client.name. We can optionally change the name to client. Very good, next we must select the sender. Then, we join the result with the shippers table, in order.shipperid equal to shipper.shipperid.

However, if we use an inner join here, because some of our orders do not have a shipper, we will only see the orders that have been shipped. Let me teach you. So for clarity, I'll add the sender name here, so sender .name as sender. Let's run the query there you have it, we only see the orders that have been sent. But we want to see all the orders, right? So, we need to change the second join to a left join. Therefore, all orders are returned whether they have a shipper or not. Let's run the query one more time, done.

Now we see all the orders from number 1 to 10. Beautiful. Finally, we need to add the status comment here. So we need to do another join here, join with the order status, which we add as os, in oh.status equals os.orderstatusid. We can see that I have designed our database in such a way that sometimes our column names are exactly identical, but in other cases they do not match. So in the orders table we call this column status, as opposed to order status ID, and this is deliberate because many real databases are like this. Alright, now let's add the state name here, so sort, status.name as state.

We execute the query, and here we can see all the orders, for each order we have the data, the customer, the dinner and the status. Previously, we talked about self-joins in SQL. So here in SQLhr database, we have this employee table, we perform a query to get all the employees and their manager. Here we have this column, reports that specify the manager of each employee. So let's go back and rewrite this query to get all the employees and their manager. Back to our query editor window, let's first use a SQL hr database. We then select everything from the employees table, give it an alias, and then join it to itself.

This is what we call self-union. Now we will use a different alias like m for administrators. Now what is the joint condition at which e.reports should be equal to m.employee ID right? Now, for clarity, let's choose just three columns, so employee ID or name. And m.firstname, whose name we change to administrator. Alright, let's run this query and see what we get. So here is the result, as you can see all these employees have the same manager. However, something is missing here. We have no background on this person, this manager himself. So what we're missing is a record where we can see this person's employee ID, his name, and his manager, which should be null because this person is the CEO or director of the company.

But why does this happen? The reason is our internal join, because this condition that we have here will only return people who have an administrator. We can solve this problem using a left join. So we do a left join, because we want to include all employees in this table, whether they have a manager or not. Okay, now let's run the query one more time. There you go. Now we have a record for this person, the administrator, as you can see, this person does not have an administrator, so we have null here. Back to our SQL store database, here we have a simple query that joins the orders table to a customer table.

And here is our union condition. We have several examples of this before. Now, as our queries become more complex, these joint conditions get in the way and make our queries difficult to read, but the good news is that in MySQL we have a powerful feature to simplify these queries. If the column name is exactly the same in these two tables, we can replace the on clause. With a simpler and shorter use clause. So I'm going to comment out this line and instead of writing using parentheses, we write the column name, that's custom. Customer identification. What we have on line 7 is exactly identical to what we have on line 6.

It's shorter and easier to read. So let me delete this line. We can add another join statement here to join the orders from the senders here, so join the senders using the sender id. In both tables we have a column with exactly the same name. Okay, now let's run this query. This is what we get: It has the order ID followed by the customer name. Let's add a new column here, so I'll add sh .name, which is the sender name. Now obviously because some of our orders are not shipping, we have to replace this inner join with the left join.

So we can use the using keyword with inner and outer joins. Let's run the query one more time. There you go. We now have the shipper name next to each order, beautiful, however we cannot use this technique to join the result with the order status table. Because in order table we have this column called status but in order status table this column has different name. It is the identification of the order status. Let me teach you. So, the order status. columns, there you have it, order status identification. Therefore, the use keyword only works if the column name is exactly the same in different tables.

Now what if we have multiple columns in our join condition? For example, before we talked about this order items table, I told you that in this table we have a composite primary key, which basically means a primary key that consists of multiple columns. So the combination of these two columns uniquely identifies each record in this table. Now if I join this table, with the order item notes table. In our join condition, we need to compare both columns with their corresponding columns in the order item notes table. So let's quickly write that query and then simplify it with the using keyword.

So select everything from the order items, now join it with the order item notes, so here we need to compare oi.order id with yn.orderid and oi.productid= with oin.productid. This join condition is a bit confusing, it's hard to read this query. Now let's simplify this query with the using keyword. So we write using. In parentheses we add both columns and then separate them with a comma. So, order and product identification. Isn't that better? Now for our exercise, let's go back to our SQL billing database, write a query to select the payment from the payment table, and produce something like this.

So in this table we have the customer's date, the amount and the payment method, we can see. On what date, who paid how much and with what payment method? Alright, I'll use the SQL billing database and then select everything from the payments table and join it to the customer table. Using customer id because in both tables, you have customer id table. Next, we need to match this with the payment methods; However, the column name between these two tables is different, so in the payment table we have a column called payment method. But in the payment methods table, our column is called Payment Method ID.

So here we cannot use the using keyword and we will have to use the on clause. So, the underlined payment method e.g. is equal to pm. payment method. Now let's choose our columns. So, date.payment.name.customer. And we rename it to customer, then we select m and finally the payment method, so let's rename it to payment_method and run the query, there we go, this is what we get. The date, the client, the amount and the payment method. In MySQL we also have another simpler way to join two stories, it is called natural union and it is easier to code, but it is not recommended, because sometimes it produces unexpected results.

But let me quickly show you how it works in case you see it somewhere so you're at least familiar with it. So, going back to the previous example, let's select everything from the orders table, which we should make a natural join with the customer table. Now, with this natural join, we don't specify exactly the name of the column. The database engine will then look at these two tables and join them based on common columns. The columns do not have the same name. And that is the reason why this query is short. So for clarity, let's select a couple of columns here, o.order id and c.

Let's say name. Let's run the query, there you have it. then we see all the orders and customers replace them. So natural joins are really easy to code, but they can be a little dangerous, because we let the database engine guess the join, you don't need any control over it. For this same reason, natural unions can produce unexpected results and that is why I advise against using them. In this tutorial, we will look at cross joins in SQL. We use cross joins to extract or join each record in the first table, with each record, in a second table.

Here is an example, let's select everything from the customer table to now here we do a cross join with the products table. Therefore, each record in the customer table will be combined with each record in the product table. And that's why we don't have a condition here. Well? So this is what we call a cross join, for clarity, let's choose a couple of columns like see name, we rename it to customer and then product.name, which we rename to product. Also, let's sort the result by client. First name. Now let's run the query, here is the result of the cross join, so first we have amber as a customer, and here are all the combinations of amber with different products.

Then we have Barbara or whatever, and again we have the combination of this custom with all the products. Now in this particular example it doesn't really make sense to use a cross join, a real example to use a cross join is where you have a table of sizes like small, medium, large and a table of colors like red, blue, green whatever. And then you want to match all sizes with all colors. That's when you use a cross join. What we have here is called explicit syntax for cross join, it also has implicit syntax which looks like this.

Instead of writing the cross join, write multiple tables in the from clause. So customers and orders. Both queries produce the same result. But I personally prefer to use the explicit syntax because it is clearer. And here you have a simple exercise.Perform a cross join between carriers and products. Do it first using the implicit syntax and then using the explicit syntax. It's pretty simple, I just want you to get your hands dirty with the code and get used to this syntax. Alright, first I'm going to use the explicit syntax and then I'm going to place the syntax.

So let's start by selecting everything from 2 tables, senders and products. Now, for clarity, I'll choose two columns, sender. name that we change to the sender. And product.name we sort everything by sender. Let's run the query, this is what we get. So the combination of all the shippers and all the products is beautiful. Now let's use the explicit syntax, so we select everything from the base table, in this case the senders, and then do a cross join with the products. That produces exactly the same result. We covered everything about joins, you learned that with joins we can join columns with multiple tables, but in SQL we can also join rows with multiple tables, and this is extremely powerful.

And let me show you how this works. First, we take a quick look at our order table and select everything from the order table. Now if you look at the data, we can see that the first order was placed in the current year, 2019, all other orders were placed in previous years. Now let's say you want to create a report, get all the orders, and next to each order add a label. If the order is placed in the current year, the table will be active, and if the order is placed in previous years, you will need to tag it as files.

So let's change our query and change our condition here. First you want to get all the orders for the current year. So when the order date is greater than or equal to 2019, 0101. Now I just want to highlight that this is not the ideal way to get the orders in the current year, because here we have coded 2019. So if you run this query The year that comes we are not going to get the correct result. But don't worry about this for now. Later in the course, I'll show you how to get the current year's orders, without hardcoding a date here.

So let's run this query, now we get just one order. Let's manually select a couple of columns here. So, order id and order date. And I also want to add a string, literal here, as active. Good? Let's run this query, this is what we get. We get 3 columns, order id, order date and asset, and in this column argument we have this string value. Asset. Now let's rename this column to status, shall we? And run the query, that's better, now we want to write another query similar to this one that will return the previous year's order, but with a different label, file.

So to save time, I'll copy these few lines and paste them right after our first select statement. Now note that we have a syntax error here, because we didn't end the first select statement with a semicolon, but don't worry, we'll come back to this in a second, so for the second query, we want to return a label and a different file and we want to change our condition to less than 2019. Now, select just these few lines. And run this query, either by clicking on this icon here or by using the keyword shortcut that you learned earlier in the course.

There you have it, here you have all the orders from the previous year, with the label file. This query returns 9 records. The previous query returned 1 record. now using union operator we can combine data from these two queries so between our select statement we write union now let's run the query once again so here is our first order in the current year which is active and below that we have have orders in previous years. So using join operator we can combine records for multiple queries. Now in this example, our two queries are performed on the same table, but we can also have queries on different tables and then combine the result into a result set.

Let me show you another example. So I'm going to delete everything here. Let's select the first name, from the customer table, and then we can join it with select name from the sender table. Let's run the query on a result set. We can see all customers and carriers. As far as I know, there is no real-world support for this particular query. But what I want to point out is that with union we can combine results from multiple queries. These queries can be performed on the same table or on different tables. In your database you can have one table as File Orders and another table as Order and then you can combine all the File and Active Orders into one result set.

Just remember that the number of columns returned by the query must be equal; otherwise you will get an error. For example, let's select the first and last name of the customers and then combine them with the name of the carriers. When we run this query, we get an error because the first part of this query returns two columns, but the second part returns one column. So MySQL doesn't know how to combine these records. And one last thing before finishing this tutorial. If you look at the result here, the name of this column is based on this first query, so the first query returns the name and that's why this column is called name.

If you change the order of these queries and move this join up here, now let's run this query as you can see, our column is called name. So everything we have in this first query is used to determine the name of the columns. Here we can also change the column name to the full name. There you go. Here is your exercise for this tutorial. Write a query for this report. Here we have four columns, customer ID, points and title. Now, as you know, we do not have this column in the customers table, so we have calculated the values ​​of this column, based on the points that each customer had.

If they have less than 2000 points, their type is bronze. If they have between 2,000 and 3,000 points they are Silver customers, and if they have more than 3,000 points, they are Gold customers. Also note that here we have sorted the result by name. Go ahead and spend two minutes writing this query. Alright, first let's get the bronze customers, so select all of the customers whose points are less than 2000. Now here we want to choose 3 columns, customer id, name and endpoints. And finally we add a new column to a string literal, bronze, let's run this query and see what we get.

These are all bronze customers, but the name of this column in bronze, we don't want that. So let's rename this to type. Now this is off the screen, so I'll split it into multiple lines to make our query larger and easier to read. Ready, let's run the query one more time, now the column is called type. Beautiful. Now we should do join and repeat this query, but extract the silver clients. So I'm going to paste this query here and then I'm going to make a couple of changes here, I'm going to replace the bronze with silver and I'm going to change the condition to between 2000 and 3000.

Let's run our query and see what we get. Have all bronze customers first, followed by all silver customers. So the order of these records is based on our queries. In our first consultation we got the bronze clients, which is why they appear first. But this is not what we want. You want to sort the result by the name of our clients. So let's apply a sort at the end to sort by name. Now there is one more piece left. You should join once again and write a query to get Gold clients. So I'm going to select these few lines.

And paste them here. Now let's change silver to gold and the condition to (?) greater than 3000. And finally we do an imported by. Let's run the query one more time. And here is the final result: our clients are sorted by name, bronze, silver and gold clients. In this section I am going to teach you how to insert, update and delete data. Before we begin, let's take a closer look at our clients' table. Then click this middle icon to open this table in design mode. What you see here may seem a little intimidating at first, but trust me, it's very easy and in this tutorial I will explain exactly what we have in these columns.

So on the left side you can see the column next to which you can see the data type for each column so our customer id column can only accept integer values. Integers are whole numbers like 1234 and so on. You don't have decimal points, etc. The first thing is a var chart which is short for variable character. And in parentheses, you can see 50, that basically means that in this column you can have a maximum of characters now if a customer name is only 5 characters long and it only stores those 5 characters. So even though the maximum length for this column is 50, it won't waste space if this customer name is less than 50 characters.

So here we have 4char, which is short for variable. In contrast we have another type of data which is the character. If you had the 50th character here and the customer name was only 5 characters long, MySQL will insert an additional 45 spaces to populate this column. So this is a waste of space. So as a best practice, most of the time we use var char to store strings or texture values. Now here on the right side we have this column pk, which is short for primary key. So the customer ID is marked as the primary key and that's why we have this yellow key here, so that the values ​​in this column uniquely identify each customer.

Next to that we have nn, which is short for not null and which determines whether this column can accept null values ​​or not. In this case, each customer record must have the customer ID. First name, last name, as well as these other attributes. But birthdays and phone numbers are optional. So in these columns we can have null values. Now we have another call here, ai, which is short for auto increment and is often used with primary key columns, so every time we insert a new record into this table, we let MySQL or our database engine inserts a value into this column, so it basically gets the customer id for the last row and will increment it by 1 when it delivers a new record.

So if you look at the data, you can see that we currently only have 10 clients here. So if you have a new client here, MySQL will sign 11 for the new client, okay? And finally here we have another column that specifies the default value for each column. For example, for the birthday and phone columns, the default or null values, so if you do not provide a value, MySQL will provide the null values ​​for these columns. Similarly, we have another null value for the points column, so if we don't provide the points for a client, MySQL will use 0.

Now we have some other columns here which are not important at this stage, you will learn about later in this course. Now that you understand the attribute of each column, let's go ahead and insert data into this table. In this tutorial, you will learn how to insert a row into a table. For that we will use the insert into statement. Where are we going to insert this row? In the customers table, we write here the table name followed by the values ​​clause. And here, in parentheses, we provide the values ​​for each column of this table, so let's go back to our table definition, these are all the columns, first we need to provide a value for the customer id column.

However, in this column, the auto-increment attribute is enabled and as I told you before, if we do not provide a value, MySQL will generate a unique value for us. We can then go back to our statement and assign an explicit value or use the default value to let MySQL take care of generating this value. This is the preferred approach. Because if we use this value as an explicit value like 200, you might have another client with the same id. So when you run the statement you will get an error. Because you can't have duplicate values ​​in this column.

Each value must be unique, so here we will use the default keyword to allow you to generate a unique value for the customer ID, and then we must provide a value or the first name and last name columns. So, let's say Jon Smith. Please note that I have enclosed these values ​​with quotes, because as I have told you, string and for in SQL, we should always enclose strings and values ​​in quotes. Whether single or double quotes, okay? Now what else? Going back to our customer table, after the last name you have date of birth, however as we can see this column is optional because this checkbox is not checked.

So here we can use a null or explicit value. Null means the absence of a value. So, going back to our statement, you can type the date of birth, such as January 1, 1990, or you can use the null keyword to skip this value. Now in this demonstration, I will use avalue date. Now, to make this code cleaner and more readable, I will split it into multiple lines. That's better, now let's go back to our table, next we have phone and phone is also optional, because these checkboxes are not checked and null is the default value for this column.

So here we can either pass null explicitly or use the default keyword and then put null in this column. It's exactly the same. So going back to our declaration, you can pass null or default. Both keywords will have the same result. In this case I'm going to use the null keyword. Alright, let's take a look more at our table, below we have 4 more columns which are mandatory i.e. Address, City, State and Points. And note that points default to 0, so we can use an explicit value as the total value like 200, or use the default keyword and let MySQL output 0.

So, going back to our statement, let's write one direction, it really doesn't matter. Followed by a city and a state, say California, and finally points, again we use the explicit or default value, this is how we can insert a row into a table. However, in this example, you only provide values ​​for the first name, last name, date of birth, and address fields. Then you are leaving out the phone number, customer ID and points. So there is another way to write the statement. Let me teach you. So after the table name you can optionally provide the material columns into which you want to insert values.

In this case, first name, last name, date of birth and once again I'm going to split this statement into multiple lines. So 3 more columns, address, city and state, these are the 6 columns that we are going to provide. values ​​for. With this change, we don't have to use these default or null values, we just provide values ​​for these columns. So I'm going to remove default from here, null and finally this last keyword default. So the 6 values ​​you provided here are used for the 6th column. Now with this change, you can also reorder the columns. We don't have to list them in the same order, they were defined in the customer table, for example you can put the last name first and then obviously we also need to swap the order of these values, so we can list them in any order.

Now we can execute the statement. Now if you look at the output window at the bottom, you should see the statement followed by a row. Unfortunately, I can't resize this window to show you this message, but if you look down, you can see that. a word was affected, which basically means that a record was inserted into this table. Now let's look at the data in the customer table so that the last row is the one we inserted, we can see that MySQL automatically generated the value 11, this is the effect of the auto increment attribute. So it takes the value from the last row and increments it by 1.

So here we have the first name. Last name, date of birth, you didn't provide a value for the phone attribute, so we have null here, we also have address, city state, and the default value of 0 for points. In this tutorial, you will learn how to insert multiple rows in one go. For this we will use the sender table, let's take a quick look at the table definition, here we have 2 columns, sender id and name. The sender id is the primary key, is non-nullable, and is an auto-incrementing column. Then we'll let MySQL generate values ​​for this column.

Easy. You only need to provide a value for the name column. So, returning to our query editor window, we type insert into shippers in parentheses and specify the name of the column into which we want to insert values, in this case the name followed by the values ​​clause. Here we add a couple of parentheses with a value like sender. Now insert multiple rows, all you have to do is insert a comma followed by another pair of parentheses. So, sender 2, once again, comma, sender 3, this is how you can insert multiple rows into 1 row, now let's execute this statement, okay, and then let's inspect the data in the sender data.

There you go. Initially we only had 5 carriers and here are the 3 new carriers we inserted. Note that MySQL automatically generated the values ​​for the sender id column. So we have 67 and 8. Alright, here's the exercise for this tutorial. Write a statement to insert three rows into the products table, it is very easy, you can delete it in a minute. So here is our products table, we only have 4 columns, we are going to skip the first column because it is an increment column, so we only have to provide values ​​for name, quality and unit price. Back in our query editor window, let's insert into the products the columns that are name, quantity in stock and unit price.

And the values ​​will be, let's say, product 1, the stock will be 10 and the unit price will be 1, 0.95. Now I'm going to select these values. copy it, paste it, like this, change the values ​​accordingly, and finally the last row for the 3 there you have it, let's run this statement, okay and then check the result in the products table, now we should have 3 new records, Now the IDs you see here are 15, 16 and 17. Because before recording this video I inserted some records into this table and then deleted them. So you had product IDs 11, 12, 13, and 14. And even though they were actually deleted from this table, MySQL remembers their IDs, so instead of incrementing 10 x 11, it incremented 14, which is the last record above.

And that's why you generated 15. On your computer, the elements will be 11, 12, 13. So far you've only learned how to insert data into a singular table. In this tutorial, I will show you how to insert data into multiple tables. Here is a very good example, look at the orders table, what columns do we have? We have the order id, we have the customer id, so we know who placed the order, we know the date of the order, you know the status, feedback and shipping information, but the actual items for this order are not in this table, they are in the order items table.

So in this table, we have 4 columns, we have the order id, so we know what to order these items for. We have the product ID, so we know what product is ordered, in what quantity and at what price. Therefore, an actual order may have 1 or more order items. This is what we call parent-child relationship. So in this relationship, the order table is the parent and the order item table is the child. So a row in the orders table can have 1 or more children within the order items table. Now, in this tutorial, I will show you how to insert an order and all its items.

Then you will learn how to insert data into multiple cables. Alright, let's go back to our query editor window. First we need to insert the order, so insert, in, let me close the browser panel, we want to insert a record into the orders table, now what columns do we have here? We have these columns, but it's only the first four, and we actually don't want to worry about the first one, because it's an auto-increment column. So we only want to provide values ​​for the customer id, order date, and status. So let's go back to our query, specifying those columns here, customer id, order date and status, now let's provide the value.

In the customer id column, we need to add a valid customer id. So let's take a quick look at our customer table, there you have it. In this table, we have 11 records. These are valid client IDs. Now, let's go back to our query, let's use 1 for the customer query and then 2019, January 2 for the order date and one for the order status. Again, in this column, we need to insert a valid order status ID. If you don't provide a valid ID, MySQL will beat us to it. So we insert an order here, the items, now we go back to our order items table, in this table, we have this order id column, so here it is, as soon as we insert an order, MySQL will generate an id for our new order now we need to access that id to insert the elements into this table.

How can we do that? Well, going back to our query editor window, in MySQL we have a bunch of built-in functions, and a function is basically a piece of code that we use ourselves. Just like a function is a feature of your TV. Every TV comes with a ton of features like power on, off, change volume, change channel, etc. So MySQL and other database engines come with a lot of built-in functions that we can use in our programs. One of these functions is the last insert id. We can call or run this function by adding parentheses here, and this will return the ID that MySQL generates when we insert a new row.

So before we continue, let's select these and make sure we get the correct result. Now we have a syntax error here because we didn't end the first statement with a semicolon, okay, now let's run this query, okay, so the new order id is 12. Let's check that. So let's go back to the orders table, look at the data. On my machine I have 12 records, in fact I created 1 right before recording this video, so on your machine you will have 11 orders. Now, let's go back to our query window, now that we know how to get the id of the newly inserted record, we can use that id to insert the child records.

So let's write another insert statement, insert into order items, let's take another look at the columns in this table. So we have 4 columns, all of them required, so there is really no need to specify the column name in our insert statement, just provide values ​​or order id, product id, quantity and unit price. So here in the values ​​clause, we add parentheses, what is our order id? That is the value returned when calling this function. So I'm going to cut this from here and next we need to provide a valid photo ID, but let's say product 1, quantity, let's say 1.

And the unit price $2.95. Now let's remove this selection, we don't really need it anymore, let's add another set of values, so once again, we are going to call the last insert ID, to get the ID of a new order, we are going to change the product to product number 2 and add a different price. That's all. Now let's run this statement and see what we get. Alright, so let's go back to our order table, let's update the records here, okay, so we have a new order, order number 13, beautiful, now let's look at the order items table, here are the order items, let's open the board.

So we should have 2 items, or order number 13. Beautiful. This is how you insert hierarchical data in SQL. In this tutorial, I will show you how to copy data from one table to another. For example, in our orders table, we currently have about a dozen records. Now let's say we want to create a copy of this table called orders file and we want to insert every row we have in this table into that new table. If you have 10 orders, we don't want to quote an insert statement with a set of ten values, that's time-consuming, so I want to show you a powerful technique to uniquely copy data from one table to another.

First we need to create this new table, orders file. For that we will use the creation table as a declaration. So create a table order file like. Now, right after that, we write a select statement to get everything from the order table. Now let's see what happens when we run this query, there you have it. So back in the browser panel, we have to refresh this view by clicking on this icon here, now we have a new table, orders file, let's look at the data, so you can see that all the orders are here and we have the exactly the same column as the orders table.

However, if you open this table in design mode, you can see that in this table we do not have a primary key. Therefore, the order id column is not marked as primary key. And it is also not marked as an auto-increment column, so when we create a table using this technique MySQl will ignore these attributes, and that means that if you want to explicitly insert the record into this new table, you must provide a value for the order. id, because this column is no longer an auto-increment column. So by using create table as a statement, you can quickly create a copy of a table;

We now refer to this select statement as a subquery, so a subquery is a select statement that is part of another SQL statement. Now we can also use asubquery and an insert statement and that is a very powerful technique. It allows us to do really interesting things. Let me teach you. So first, let's right click on the order file table and click truncate table, because we want to delete all the data from this table. Alright, it's asking for confirmation, we're going to truncate the table, so now let's go back to this table. Let's update the table, we don't have any records here, okay?

Now, let's go back to our query editor, let's say you want to copy only a subset of records from the orders table into this table, like all orders placed before 2019. So, let's first select everything from the orders table where the date of the order is less than 2019., January 1. So these are all the orders, copy these orders into the orders archive table. So we can use this select statement as a subquery in an insert statement, we write insert in the orders file. Now we don't need to provide the column names, because we are going to reproduce values ​​for each column we have in this query. .

So we did it, and this is an example of using a select statement as a subquery in an insert statement. Let's run this, okay, now let's go back to the table, let's update the records, we only have the orders placed before 2019. Alright, here's a really cool exercise back to our SQL billing database. Look at the invoice table. So in this table, we have these columns, (?) id number, customer id number which is associated or related to the customer id column, in the customers table, followed by some other columns, now let's say that You want to create a copy of the record in this table and place them in a new table called invoice file.

However, in that table, instead of the customer id column, we want to have the customer name column, so we want to join the table with the customers table and then use that query as a subquery in a statement. table creation. Also, to make the exercise more interesting, I want you to copy only the invoices that do have payment. So if you look here, this payment data column here determines whether a payment has been made, for this invoice or not. So select only the invoices that do have a payment date. It's a really good exercise. Spend two or three minutes on this and then come back and continue observing.

Alright, first I'm going to use the SQL billing database, now let's select everything from the invoice table and join it to the customer table. here I'm going to use the using statement to simplify my join. What column are we going to use to join? The customer identification column. Let's run this query up to this point. Alright, first we see that the customer id column is used to join these tables, then we have the invoice table columns such as the invoice id number etc. Followed by the columns of the customer table. Name address, etc. Obviously we don't want all of these columns, we only want the columns from the invoice table, but we need to replace the customer id column with the customer name column.

So let's take a quick look at the layout of the invoice table. Here we have the invoice id, number, customer id, we want to replace this column with the customer name. Going back to our query, I'm going to select the invoice ID, the number, and then the customer name. Let's change the name to client. What other columns do we have here? We have the invoice total and the payment total, so let's add those too, the invoice total and the payment total, we also have three columns, for dates, invoice date, due date and payment date, so let me close the browser panel.

Payment date and invoice due date. Now, technically, because these columns only exist in the invoices table, we don't have to prepend the table alias to them. We can simplify the code like this; However, I personally prefer to prefix them, because that gives me a clear idea of ​​how to join these tables. It's just a personal preference that another developer might not agree with and that's okay, so whatever you prefer. That's perfectly fine, let's run the query and make sure we get the correct result, so we have the beautiful customer invoice ID number, followed by these other columns.

Now I want to filter the result and return only the invoices that do have a payment. So we can return records that have a payment date or records that have a total payment greater than 0. Both are perfectly fine, so let's go back to our query, at the bottom, let's add the where clause, where payment date. It is not null. That is better. Let's run the query one more time, now we just get this handful of invoices, beautiful, finally let's use our query, as a subquery on a create table as a statement. So just before selecting we type create table, invoices, file, and that's it, let's run the beautiful query, now let's go back to the browser panel, refresh the view, so here is our new table, invoice and file, see the data , there you have it.

You only have the invoices page and here you have the name of the client of each invoice. Beautiful. Now just keep in mind that if you run this query one more time, you will get an error because we already have a table called invoice file. Later in the course I'll show you how to draw tables, that's pretty easy, but for now you can just right click and go to draw table and then confirm that's okay, and then you can run the query one more time. In this tutorial, I will show you how to update data in SQL.

Going back to our invoice table, look at the first record here. The payment total for this record is 0 and there is obviously no payment date. Now let's imagine that there was a problem in the system that recorded this information. Let's say the customer paid ten dollars for the invoice. So we should update this record in this table. That's very easy, back in our query editor window, we use the update statement to update one or more records in a table. What table? In this case invoices. Next we add a seth clause and this is where we specify a new value for one more column.

In this case we want to update the payment total. Let's say ten dollars. We must also update the payment date. Here is a column to add more columns, we set this with the date value, say 2019, March 1, and then we write a condition, with this condition we identify the record or records that need to be updated. In this case we want to update the invoice number 1. So, going back to our query, we write the invoice ID equal to 1. Let's execute this. Beautiful, now let's go back to our table, update the data by clicking on this icon. Okay, we can see that the payment total is updated to ten.

And we also have a payment date. Beautiful. Now let's say we actually updated the wrong record, maybe we should have updated invoice number 3, so we should update this table once again and restore the original values ​​in this column, going back to our query we can set the total payment to 0 ., and the payment date to null. Then we can use the null keyword to insert the null value into a nullable column. Now, let's go back to the browser panel, open the table in design mode. We can see that the payment total column defaults to 0 and the payment date column defaults to null.

So in our query we can also set the default total payment and MySQL will use the default value for this column, which in this case is 0. So let's run this statement one more time, beautiful. Now, let's go back to the table, update the data, so that the payment total is set to 0. And the payment date is null. Beautiful. Now let's go ahead and update the third payment. Look at the total bill. That's $147. For this example, let's imagine that the client won 50% of the total amount on the due date. So, going back to our query editor window, instead of using a literal value window like $70, we can write an expression, we want to calculate 50% of the total invoice.

Then the total of the invoice, x 0.5. Now let me split this code into multiple lines so we can see it clearly, now we need to set the payment date, as I told you, this customer made the payment on the due date, so we can set this due date. Any value we have in this column will be used to set the payment date. Now obviously we need to update the invoice id, number 3, now back in the table, let's update the data. Okay, look, the payment total has been updated and is set to almost 50% of the invoice total.

However, this number is truncated so we do not have the digits after the decimal point. Don't worry about that for now, we'll come back to this when we talk about data points later in the course. We can also see that the payment date is set to the same value that we have in the due date column. In the last tutorial we learned how to update a single record using the updates statement. Now, if you want to update multiple records, the syntax is exactly the same, but the condition you write here needs to be more general. For example, going back to the invoices table, you can see that we have several invoices for customer number 3.

You can write a statement to update all invoices for this customer. So, going back to our query editor window, we change our condition to where the client id is equal to 3. However, if you run this statement with MySQL Workbench, which is the software we've been using in this course, you'll get a error, because by default, MySQL Workbench runs in safe update mode. Therefore, it allows you to update only one record. You won't have this problem if you use another client for MySQL or if you write this statement in your application code, this is only specific to MySQL Workbench.

Now let me show you how to fix this. At the top we go to the MySQl workbench menu and then present this. In this dial-up box, on the left side, click on the SQL editor and then at the bottom, uncheck this checkbox, save updates, to prevent you from accidentally updating or deleting a bunch of records in a table, so let's move on. With this, we now need to reconnect to this MySQL instance. So let's copy all the code here and close this local instance window. Alright? Now on the home page, double click on this connection to reconnect, done, and then paste all that Sql code.

Now let's run this, beautiful, all invoices for client number 3 are up to date. Here we can also use the in operator, let's say we want to update all invoices for customers number 3 and number 4. So all the operators that you learned to use in the where clause also apply here. Now technically this where clause is optional, so if you want to update all the records in a table, just skip it. Alright, here is your exercise for this tutorial. Back in our SQL Store database, write an SQL statement to give customers born before 1990 15 extra points. Alright, first we will use the SQL store and then write an update statement.

To update the customer table, set the points to dot plus 15, so here we use the expression to update the points column, for anyone born before 1990. So when the date of birth is less than 1990, 1 from January. Alright, let's run this query, alright, now let's open the customer table once again, so that anyone born before 1990 now has 50 extra points. In this tutorial we will learn how to use subqueries in an update statement, it is extremely powerful, so we will continue with the example from the last tutorial, but we will make it more interesting. Here we are updating all the invoices for customer number 3, but what if we don't have the customer id?

Do we only have the name? For example, you could even have an application and in that application the user types the name of the customer, so we first need to find the id of that customer and then use that id to update all the invoices. How do we do that? Well, let's take a quick look at our customer table, so here we have this customer, my words, let's say you have the name and you want to find the id. So let's go back to our query window. After our update statement, I will write the select statement to select the customer id column, from the customers table, where name equals my jobs.

Now here we have a syntax error, because we didn't finish last, don't worry about that, we'll come back to that in a second. Let's select these few lines and run this query. This client's idea is 2. Beautiful. Now we can use this statementselection as a subquery in an update statement. As I told you before, a subquery is a select statement that is inside another SQL statement. So instead of coding 3 here, we will use this select statement as a subquery, but we need to put it in parentheses, so MySQL will execute this query first, return the customer ID, and then use it in this condition.

So for clarity, let's remove the line break and indent these few lines. So this is the final result. Now we run this query and this updated all the invoices for this customer. Now, what if this query returns multiple clients? For example, returning to the customers table, let's say you want to update invoices for all customers located in New York or California. So, we need to update our subquery, like this, where is the state of California and New York. Now, before running the entire statement, let's select our subquery and run it to see what we get. So we get two customer IDs, one and three, beautiful, now because this query, this subquery returns multiple records, we can no longer use an equal sign here, so we need to replace this with an in operator.

And now this statement updates the invoices of all the customers located in these two statements. Let's run it, beautiful, everything worked. So as a best practice, before running your update statement, run your query to see which records you are going to update. So as not to accidentally update records that shouldn't be updated. Now here we have a subquery, but even if we didn't have a subquery, you could still query the records that we are going to update. Let me teach you. So let's imagine that we are going to update all the invoices where the payment date is null.

Before running this full update statement, you would run a query like this. Select, start. I would start with invoices where the payment date is null. Now let's run this query, these are the two records that have no payment date, so once we're sure we're updating the correct records, we come back here and get rid of this select statement, and just attach the where clause to our update statement. Alright, here is your exercise for this tutorial, go back to our SQL store database, look at the orders table, as you can see, several orders have no comments, I want you to write a SQL statement to update the comments or orders . for clients who have more than 3000 points.

Therefore, customers who have more than 3000 points are considered gold customers. Find their orders, if they have placed an order, update the comments column and set them as a gold customer. That's a really good exercise. Alright, first we need to find the Gold customers, so select everything from the customers table, where the points are greater than 3000. And by the way, because the current database is a SQL invoice, we have to write a statement usage at the top, or double-click this database before running this query. There you go. We have three Gold clients now we need to get the ID of these clients to use them in an update statement.

So here we just select the customer ID and then select this statement as a subquery in an update statement. So update, order, set comments in golden client, where the client id, because we are dealing with multiple client ids, we need to use the in operator and then to use it, as a subquery, we need to enclose it in parentheses. Let's indent the code, that's better, so here is the final solution. You have now learned how to insert and update data. In this lesson, we will finish this section by learning how to delete data. That's very easy.

We use the delete from statement to delete records from a table. Let's say the invoice table. Now we can optionally add a search condition to identify the records we want to delete. If you don't write this where clause with this statement, we will delete all records from this table. And obviously, that's very dangerous, so be very careful when executing this statement. Now here let's say we want to delete the invoice with id 1, then the invoice id goes to 1. Now here we can also use subqueries, let's say we want to delete all the invoices of the client named my works.

Let's first find this client, so select everything from the client table where the name is equal to my jobs. Let's run the second query, so here is our customer, now we can get this customer id and use it in our search condition. So where the customer id is equal to 2, this is where we add our subquery, there you have it, as before, beautiful. This is how we can delete data in SQL. Alright, we're done with this section, but before we move on to the next section, I want you to restore all of these databases to their original state.

Because in this section we added some data, updated some data, deleted some records, so if you don't restore these databases, you may see different results in the future. So restoring these databases is quite easy. Here in My SQL Workbench, at the top, go to the File menu and open the SQL script. Then navigate to the directory where you stored the SQL scripts for this course, in case you have lost that directory, go back to the first section, you have a lecture to download the supplemental materials. So, in this directory open create databases.SQL. Now run this script to recreate all our databases.

Alright, beautiful, now let's open the browser panel, you can see the databases disappear from here, just click on this refresh icon. Beautiful. Alright, we're done with this section, see you in the next section. Hey guys, Mosh here. In case you haven't seen my website yet, head over to codewithmosh .com. This is my coding school where you can find many courses on web and mobile app development. In fact, I recently published a complete SQL course that lasts about 10 hours and teaches you everything you need to know, from basic topics to advanced topics, such as database design, security, writing complex queries, transactions, events and much more. , much more. .

These are the topics that every software engineer must master. This Youtube course you've been watching is the first three hours of my full SQL course which is approximately 10 hours long. So if you want to master SQL and prepare for work, I highly recommend taking my complete SQL course. You can watch it anytime, anywhere, as many times as you want, you can watch it online or download the videos. The course comes with a 30-day money-back guarantee and a certificate of completion that you can add to your resume. The price of this course is $149 but the first 200 students can get it for just over $100.

So if you're interested, the link is below this video.