User: Generate 5 technical multiple-choice quiz questions from this transcript about language learning and directions:

Hi, my name is <PRESIDIO_ANONYMIZED_PERSON>, and I&amp;#39;m  super excited to be your instructor. In this three hour course, you&amp;#39;re going to learn everything  you need to get started with SQL or SQUEL. First I&amp;#39;m going to give you a three minute introduction to SQL, then we&amp;#39;re going to 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  anyone with some basic knowledge who wants to fill in the missing gaps. By the end  of this course, you&amp;#39;ll be able to retrieve insert, update, and  delete data in databases. We&amp;#39;ll talk about tables, relationships,  different types of joints, sub-queries regular expressions and  much, much, more. These are the essential concepts every software developer  or data scientist must know. This SQL course is packed with tons  of exercises that help you both learn and remember the SQL syntax. Also, there is a table of content below this video so you can quickly jump to specific tutorials, now let&amp;#39;s jump in and get started.   Let&amp;#39;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. In order to manage our databases, we use a software application called database management system,  or, DBMS. We connect to a DBMS and give it  instructions for querying or modifying data. The DMBS will execute our instructions and send results back. Now we have  several database management systems out there, and these are classified into two categories, relational and non relational, also  called NoSQL. In relational databases, we store data in tables that are linked to each other using relationships. That&amp;#39;s why we call these databases relational databases, each table  stores data about a specific type of object, like customer product, order and so on. SQL or SQUEL is the language that we use to work with these relational database management systems. It looks like this. We use SQL to query or modify our data. In this course you&amp;#39;re going to learn everything there is to know about this powerful  language. Now, there are many different relational database management systems,  out there, some of the most popular ones are MySQL, SQL Server my Microsoft, and Oracle. But of course, there are plenty  more. Each database management system has a different flavor of SQL.  But all these implementations are very similar and are based on the standard SQL specification. So most of the SQL  code that you will learn in this course will work with any database management system.  In this course, we&amp;#39;ll be using MySQL, which is the most popular  open source database in the world. Okay, now back to this diagram,  what about non relational databases? In non relational databases,  we don&amp;#39;t have tables or relationships, these databases are different from relational databases, but that&amp;#39;s a topic for an entirely different course.  What you need to know is non relational database systems  don&amp;#39;t understand SQL. They have their own query language.  So, we use SQL to work with relational database management  systems. Now, before we jump in and install MySql, let me clarify  something quickly, as 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 everybody thinks their way of pronouncing this word is the right way. But here&amp;#39;s a  history about this language. SQL was originally developed at IBM in the 70s and back then it was initially called SQUEL short for structured English query language. But they changed the acronym to SQL, because SQUEL was the trademark of an airplane  company. So to this day, there has been an argument about what is the right generally speaking, people in non-English speaking  countries call it SQL. I&amp;#39;m used to calling it SQUEL because it&amp;#39;s shorter and sweeter than SQL. But if you prefer to call it SQL, that&amp;#39;s totally fine with me, I&amp;#39;m not going to get mad at you. So that&amp;#39;s the history behind this language. But what about MySQL as a  software product? Developers of this product prefer to call it MySQL rather than mySQL. But they don&amp;#39;t mind if they call it MySQL.  In this course, I&amp;#39;ll be teaching you SQL with MySQL.  Hey guys, Mosh here. I just wanted to let you know that  you really don&amp;#39;t have to memorize anything in this course, because I&amp;#39;ve created a  complete cheat sheet with summary notes for you. You can find it below this video  in the description box. So I have done my best to create the best possible, most comprehensive SQL course for you, and I would really appreciate it if you would support my hard work by liking and sharing this  video. Thank you so much, now, let&amp;#39;s continue.  In this tutorial,  I&amp;#39;m going to show you how to install MySQL on your computer. Now I&amp;#39;m using a Mac, so first I will show you how to install MySQL on a Mac  computer, and then I will show you how to install it on Windows. So if you&amp;#39;re a Windows user,  feel free to skip this tutorial. Now, open up your browser and head over to MySQL.com. Then, go to the downloads page, and scroll down to the bottom. Here you should see MySQL community edition, this is absolutely  free, and we&amp;#39;re going to use that throughout this course. So let&amp;#39;s go ahead  and look at that, now on this page, click on MySQL,  community server, and then on this page,  you should see available releases for Mac OS.  So, in this list, download the first item which is a dmg archive.  Alright, now, on the next page, click on no thanks, just start my download. Okay, as you can see, we are getting a dmg file, which is basically a setup wizard.  Alright, now the DMG is downloaded, so let&amp;#39;s open it,  and then double click on this package, this will launch an installation wizard which is pretty easy to use, so simply  click on continue and again, and again,  agree with the license agreement, and  install MySQL, it&amp;#39;s going to ask for your password, this is the password  that you used to log into your computer, so let&amp;#39;s put that here,  alright, now here we need to set a password to the root for the admin user. So click next, and  in this box, type a complex password,  alright, now, let&amp;#39;s finish the installation,  and enter your computer&amp;#39;s password one more time,  and we are done!  That was super easy and sweet.  Alright, we installed MySQL community server, now we  need the graphical tool to connect with this server and manage our databases.  So, back to the downloads page, one more time,  scroll to the bottom, and go to 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&amp;#39;s go ahead and  download this as well. Now once again on this page, we need to download a dmg archive, so,  download, and, again we have  to say no we don&amp;#39;t want to login or sign up, so let&amp;#39;s go ahead and download  the dmg, and then  open it, alright, you&amp;#39;re going to see something like this, so  drag this MySQL workbench and drop it onto the applications folder.  So, let&amp;#39;s go ahead with that, now it&amp;#39;s going to copy  this into the applications folder, beautiful,  so we&amp;#39;re done with the installation, that was super easy, now press command and space, and search for MySQL,  work bench, there you go, let&amp;#39;s open it, now the  first time we get this message, because this is an application we downloaded from the internet.  So, we need to tell Mac that we trust this, let&amp;#39;s go ahead with that,  so this is MySQL workbench, now by default  you see a connection here, if you don&amp;#39;t see that, you need to create it. Let me show you how to do that. So for this demo I&amp;#39;m going to right click this and delete this connection, alright,  now let&amp;#39;s create a connection from scratch. So click on this plus icon,  on this page, give this connection a name, let&amp;#39;s  say local instance, now the connection method  we&amp;#39;re going to use is tcip, which is set by default, the  host name 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 MySQL server, that is the user name of the admin, now we need to enter the password. This is the password that we set during the installation. So, click on  store in keychain, and in this box, type the  password for the MySQL server. Alright  and finally, let&amp;#39;s test the  connection, okay, we successfully connected to MySQL server on the local machine, beautiful, let&amp;#39;s click okay,  and here we have a connection on the homepage of MySQL workbench, every time we open MySQL workbench, we use this connection to connect to our local server. Alright, we&amp;#39;re done with the installation of MySQL on a Mac, next I will talk about MySQL on a Windows computer, so feel free to skip that tutorial.  In this tutorial, I&amp;#39;m going to show you how to install MySQL on Windows. So open up your browser and head over to MySQL.com, then, go to the downloads page now here, scroll down to the bottom,  we&amp;#39;re going to use MySQL community edition which is absolutely  free. So, let&amp;#39;s go with this, now,  select MySQL community server,  and then scroll down so here you should see something like this, MySQL installer for Windows. This is the  recommended method for installing MySQL on Windows. So,  click on this, alright, on the next page, scroll down,  and  download the first installer here. On the next page  scroll downs nd click on no thanks just start my  download. Otherwise you have to create an account and log, which is unnecessary for knowing this course, so, let&amp;#39;s go with this,  and save this file to our computer, and then  run it.  Alright, we&amp;#39;re going to 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 places where you need to enter a password, let me show you. So, on the first page, for the setup type we&amp;#39;re going to use the  developer default setup. Go to the next page. Now here we&amp;#39;re getting a little warning, because this installation wizard wants to install the connector for python 3.7, but I don&amp;#39;t have Python on this machine, so that&amp;#39;s why I&amp;#39;m getting this warning, now on your  machine, you might or you might not get this error, just click  next, and one more time, so here are the products that I want to get installed the first one is MySQL server,  the second one is MySQL workbench, this is the graphical tool that we use to connect to our database server, and manage our database.  You&amp;#39;re going to see that soon. So click on execute,  now this is going to take about 5-10 minutes, so,  I&amp;#39;m going to pause the recording.  Alright, all the products are installed, beautiful, let&amp;#39;s go to the next page,  and again, here our the group replication  page, also click on next, and the next page which is  about networking, leave all the default settings, so,  let&amp;#39;s go to the next page, now we should set a password for the root for  the admin user. So, click on next,  and in this box, type a password for the admin user.  Alright, and then  let&amp;#39;s go to the next page, once again leave all the default settings,  and click on next, and execute one more time,  alright, and now  let&amp;#39;s finish our installation, once again we have to click on next,  and then finish. One more time,  there are so many steps. Now here&amp;#39;s the page where you need to enter the admin password, so the page is called connect to server, you can see the username is root, which represents the admin user,  so in this box enter the password that you said earlier,  then click on check, okay, connection was successful  beautiful, let&amp;#39;s go to the next page, and  click on execute, and finally finish.  There you go, we have one more step, next the  finally after all these steps the installation is complete. Now, this is going to start, MySQL workbench, which is the graphical tool we use to manage our database and run SQL queries.  So, click on finish, so now we have a command  prompt window, where we can type instructions to talk to our MySQL server,  we don&amp;#39;t really need this, so close it,  and here&amp;#39;s MySQL workbench, now the first time you open this page,  by default you should see a connection here, if you don&amp;#39;t see it,  click on this plus icon,  on this page give this connection a name, let&amp;#39;s say local  instance, now leave all their settings to their default value,  but here for the password click on store involved.  And in this box, type the password that you used for the admin user.  So, I&amp;#39;m going to put that here, okay,  now click on test connection,  alright, we successfully connected to the MySQL server on this  computer, alright, then, click on okay,  now we click on this connection to connect to our database server.  Alright, so here&amp;#39;s the interface you&amp;#39;ll be using throughout this course,  on the left hand side we have the navigator panel, in the middle we have the query editor which we can resize  this is where we&amp;#39;re going to write our SQL queries, and on the right side we have  additions. So we&amp;#39;re done with MySQL  on our computer, next, I&amp;#39;m going to show you how to create our databases for this query.   In this tutorial, I&amp;#39;m going to show you how to create the databases for this course. So here I&amp;#39;ve got MySQL workbench open, let me quickly give you an overview of this interface because  the first time you open it it might appear a little bit intimidating.  But actually it&amp;#39;s not that difficult. So here on the top we have this tool bar with these buttons for creating a new tab for writing SQL code  as well as opening a SQL file. And next to that  we&amp;#39;ve got a bunch of buttons for creating a database, creating new tables and  so on. On the left side, we&amp;#39;ve got the navigator panel with two tabs  administration and schemas. We use the administration tab to do administrative work, such as starting or stopping our server. Importing or exporting data and so on. The schemas  tab shows the databases that we have in the current database server.  So currently we only have one database, that is sis, and this is the database that MySQL uses internally to do it&amp;#39;s work.  Now, in the middle we&amp;#39;ve got this query editor window, this is where we write our SQL code, so we&amp;#39;ll be spending most of our time in this course, in this window, and on the right side we&amp;#39;ve got another panel with two tabs,  contacts held, and snippets, now chances are this interface  might look slightly different on Windows, but what I&amp;#39;m showing you here is  almost identical to what we have on Windows. So don&amp;#39;t get hung up if it  looks slightly different on your machine. It doesn&amp;#39;t really matter. Now up here,  we&amp;#39;ve got these buttons for showing or hiding these panels, so to clean this interface, I&amp;#39;m going to hide this panel on the right side.  As well as the panel on the bottom. That is better. Now,  to create the databases for this course, download the zip file attached,  below this video. When you extract the (?) you&amp;#39;re going to see a bunch of SQL files like this. So the main one you&amp;#39;ll be using 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 for creating  individual databases, I&amp;#39;ve just added these files in case you need to recreate  one of these databases in the future. But for now don&amp;#39;t worry about them. Now,  back to MySQL work bench, let&amp;#39;s open  the main SQL file. That is create databases.  So this is an example of SQL code.  Now this may look complex at the beginning, but trust me, as you go through the course  you&amp;#39;re going to understand exactly how everything works here. You&amp;#39;re going to be able to write SQL code like this. So, you want to execute  this, to create all the databases for this course. To do that, we click  on this icon, this yellow thunder icon that we have  on this tool bar here. This will execute either the selection or the  entire code if there is nothing selected for example, if I select this line here,  and click on this icon, this will execute only this line. In this case we want to execute the entire code, so we shouldn&amp;#39;t select anything,  and now let&amp;#39;s execute this, beautiful, now here down on the bottom,  we have this panel called the output window that shows all the  operations perform in our database server, so we can see  all the operations completed successfully, or something went wrong.  As you can see you&amp;#39;ve got these green ticks next to each operation.  Beautiful, so I&amp;#39;m going to close this panel, that&amp;#39;s better.  Now on the left side in this schemas tab you don&amp;#39;t see this new database  so we&amp;#39;ll have to refresh this view, beautiful.  So we&amp;#39;ve got all these databases that are prefixed with SQL or SQL, I decided to prefix them with SQL so we know that  these are the databases for this course, they don&amp;#39;t accidentally clash with  a database on the same name of your database server. Now,  at the time of recording this video, there are only 4 databases here, but as we go through  the course, I&amp;#39;m going to update the script for creating the databases, so,  when you watch this course, chances are you&amp;#39;re going to see more databases here. Don&amp;#39;t worry  worry about the difference. Now as an example let&amp;#39;s explore  these databases. And by the way we don&amp;#39;t need this tab anymore so let&amp;#39;s close it.  That&amp;#39;s better. Let&amp;#39;s expand the SQL store database, now in every database we have these objects, we have  tables, this is where we store our data, we have views,  which are kind of like virtual cables, so we can combine data from multiple  tables, and put them in a view. And this is especially powerful  for creating reports, you&amp;#39;re going to learn about them in the future. You also have  store procedures and functions, and these are little programs that we  stored inside of our database for querying data. For example,  you can have a store procedure for getting all the customers in a given city.  So we call that procedure and we say hey, give me all the customers in San Francisco  And this will return all the customers in San Francisco.  Okay, now, let&amp;#39;s expand the tables,  so here are the tables in this database. We have customers,  we have orders, products, shippers and so on.  Now, select this customers table whenever you hover your mouse  over this item and see these three items on the right side.  Click on the right most icon, that looks like a table with a  thunder. With this we can see all the data, in this  table. So this is our customers table. In this table, we have these columns like customer id which we use to uniquely identify  customers. We all have first name, last name, birth  date, phone, address and so on. So these are the columns  in this table and every row is called a  record. So every row represents one customer and these are the pieces of  information we know for each customer. Now let&amp;#39;s look at  another table, let&amp;#39;s open the orders table,  in this table we have these columns like order ID, customer ID,  order date, status, and so on. What is this customer ID here.  We use this column to identify who has placed  each order. Now what is interesting here is you have referred to this customer  using their customer ID which uniquely identifies that.  In other words, if Jon Smith has placed an order, we don&amp;#39;t  store John Smith here, we only store John&amp;#39;s customer ID.  Why is that? Here is the reason. It is possible  John Smith might have placed multiple orders in our system.  Now every time John places his order, we need to look up his address and  phone to ship his order. Now it is possible that some of this information  might change in the future. John might move to a new place or change his  phone number, he might even change his name, if you repeat all that information next to each order, then we&amp;#39;ll have to come back and make changes in multiple places. In contrast, with this design we only  store the ID of john here, so anytime we want to change  any information about John, instead of changing that here. We go back to our customers table, so let&amp;#39;s look at  customer with ID 6, that is actually called (?) So here is all the information about Elka. This is her phone number, this is her address, and by the way this is all dummy data that I created using a tool.  So, if any information about elka changes in the future,  this is the only place that we need to modify. So  this is how these databases work. We refer to these databases as  relational databases. That basically means in these kind of databases we have  multiple tables that are related to each other using  a relationship. So internally there is a relationship between the customers table, and the orders table. So the customer  ID column in the customers table is related  or linked or associated with the customer ID column.  In the orders table. Here&amp;#39;s the orders table, and here we have  the customer ID column. So this was a brief introduction to  relational databases, you learned about databases, tables,  columns, rows and relationships. In the next section I&amp;#39;m going to show you how to retrieve data from a single table in this database.  But, before going any further as an exercise I want you to explore  the invoicing database. Look at the all the tables, look at all the data to get an id of the kind of data that we have in this database. You&amp;#39;re going to use this database  a lot in the future, so let&amp;#39;s spend a couple minutes to explore this database.  In this tutorial I&amp;#39;m going to show you how  to retrieve data from a single panel. Now look at the navigator panel.  Currently none of our databases is displayed in bold, and that means none of these databases are selected for query. So the first step to rate a query to get data from a database, is to select a database.  The query that we&amp;#39;ll write will be executed against that database.  In this demo, we&amp;#39;re going to use the SEQUEL store database.  So we type our use SQL_ store. Now use is a keyword in the SQL language, and that&amp;#39;s  displayed in blue. Now SQL is not a case sensitive language,  and that means we can use upper case or lower case characters, it doesn&amp;#39;t really  matter, but as a best practice, we should capitalize the  SQL keywords, and use lower case characters for everything else.  So now, let&amp;#39;s go ahead and execute this query. Alright,  Look, the SQL store database is now displayed in bold.  Now in MySQL workbench, we can also select a database  by double clicking that, so now I double click SQL invoice  and it&amp;#39;s the current database. Now if we run this query again,  the SQL store database becomes selected.  Alright, now let&amp;#39;s write our first query to retrieve all the customers in this database. So after the you statement we&amp;#39;re going to use  select statement. Here is the basic syntax or basic structure  of the select statement, we type out 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 first name column or  we can retrieve all columns using an asterisk.  Now after that we use the from clause and this is where we specify the  table that we want to query, in this case the customer&amp;#39;s table.  So this is the simplest query to select all the customers in a given table,  now whenever you have multiple SQL statements you need to terminate each statement using a semicolon. So,  look, we have a red underline here that indicates an error, if you hover your mouse over here,  you can see this tool tip saying select is not valid at this position.  Because we didn&amp;#39;t terminate the first statement with a semi colon.  Okay, now let&amp;#39;s execute this query one more time, once again,  we can click on this button here, or we can use a short cut,  so look at the query menu on the top, the first line is execute. Now here&amp;#39;s the shortcut for this command, on Mac, it&amp;#39;s shift  command and enter. On Windows its going to be different honestly  I&amp;#39;m not sure. So whatever it is, use that. So I&amp;#39;m going to press  shift command enter, and here are  all the customers in this table. So this  next statement has two clauses, the select clause and the  from clause. But there are other clauses we can use to filter  and sort data. For example, you can use the wear 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&amp;#39;ll only get the customer with ID  1. So this is the where clause. We can also sort  the data so after wear we use the order by  clause and here we specify the columns that we&amp;#39;re going to sort the results from on. Let&amp;#39;s say we want to sort these customers by their first name so we type out  first_name. That is the name of one of the columns in this  table, right? Now if you execute this query this order by doesn&amp;#39;t really have  an impact, because we only get one record in the result  so let me temporarily take out the where clause,  to do that we can put two hyphens in front of this line, now this line,  is treated as a comment, which means the SQL engine is not going to execute this, okay, so let&amp;#39;s execute this query one more time,  now all the customers that we get our sorted  based on their first name. So that&amp;#39;s the basic idea.  Now over the next few tutorials your&amp;#39;e going to learn more about these clauses in detail. But what you need to take away in this tutorial is that  is that these 3 clauses from where an order by are  optional. As you can see in this example, I&amp;#39;m not using the 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, we can select some values like one and two. Now,  if you execute this query one more time, in the result, we 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 quite often use all of them. Now what you need to understand here is that  the order of these clauses matter, so we always have select first,  then we have from, then where, and finally order by.  We cannot change the order of these clauses, otherwise we get a syntax error.  Which basically means the syntax, or the grammar or the structure  of our simple statement is incorrect. So it cannot be executed.  And one last thing before we finish this tutorial, you can see I&amp;#39;ve listed all these clauses on a  new line, now technically you don&amp;#39;t have to do this, because line breaks, white spaces and tabs are ignored  when executing SQL statements. So we could come back here and  put from in front of select, so select store  from customers all in one line, and that&amp;#39;s perfectly acceptable for simple queries,  but as your queries get more complex, it&amp;#39;s better to put each clause on a new line. So that&amp;#39;s all for this tutorial. In the next  tutorial, we&amp;#39;ll explore the select  clause in detail.   In this tutorial, we&amp;#39;re going to look at the select clause in detail.  So, since our current database is SQL store, to  clean things up I&amp;#39;m going to remove the first statement, you don&amp;#39;t really need it now,  also, I&amp;#39;m going to delete these two comments, we just want  to focus on the select clause. Alright, 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  the columns that we want and this is helpful in situations where you have a  big table with so many columns and perhaps millions of record.  If you want to bring back all that data, that&amp;#39;s going to put a lot of pressure  on the database server, in this case MySQL, as well as the network.  So, that&amp;#39;s when we explicitly specify the columns that we  want to get. Let&amp;#39;s say we want to get the first name  and last name columns. Execute the query, as you can see, we only get these two columns.  And they are in the same order we specified here. So if we  change the order and put the last name first and execute the  query again, now we can see the last name column comes first.  Now let&amp;#39;s add a new column, at the end, let&amp;#39;s get the points for each customer as well, run the query, so these are the points for each customer which are calculated based on their shopping.  Now let&amp;#39;s say we want to get these points and put them in a mathematical formula  to calculate the discount that we can give to each customer.  So here we can use an arithmetic expression, that&amp;#39;s points plus ten.  This is an arithmetic expression. So now we can execute this query  for the first record you can see that their points will end up being 2283. Let&amp;#39;s run the query one more time there you go. Now we can put the original points column here for clarity.  So points, points plus 10. Let&amp;#39;s run the query one more time, now you can see the original points, and next  to that you can see the value that we&amp;#39;re going to use to calculate the discount.  Now here we&amp;#39;re using the plus operator which is for addition, we also have  multiplication, division, subtraction, and  module which is the remainder of the division. So let&amp;#39;s change this to something  more complex. Let&amp;#39;s say we want to get the points, multiply by 10,  and then add 100 to it.  Now we can immediately see that this line one is getting too long  and it doesn&amp;#39;t fit on the screen, in situations like this, you can break up the select clause by placing each column on a new line, so,  select last name, then first name  points finally points times 10,  plus 100. So let&amp;#39;s execute this query one more time.  So 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 operators, and this is based on the order of operators in math.  So in math, the multiplication and division  operators have nighter precedence then addition and subtraction, so in this expression, points is multiplied by 10 and then the result is added  to 100. If this is not what you want you can always change the order by using parenthesis, as an example, let&amp;#39;s change this multiplication to addition and then put that  multiplication here. In this expression, first 10 is  multiplied by 100, and then the result is added to the points.  Now let&amp;#39;s say this is not what we want, so we can change the order by using parenthesis here.  With these parenthesis, first we get the points add 10 to them  and then multiply the result by 100. So this parenthesis are useful for changing the order of operations as well as  adding clarity to our code. So someone else reading this code can easily understand.  the order of these operations. Now let&amp;#39;s execute this query one more time,  alright, now look at the name of this column here,  its set to the expression that we have on line 5.  That doesn&amp;#39;t quite make sense you want to have a clear descriptive name,  so we can give this column an alias using the as keyword.  So, as and then we give it a name like discount  discount _factor. Let&amp;#39;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 the results sets. Now if you want to have a space in the  column name, you need to surround it with quotes, either  single, or double quotes. So we put quotes here and then  we can add a space in between these two words. Let me  execute the query one more time. Now we have discount factor. So let&amp;#39;s quickly recap everything you learned about the select  clause. We cause an asterisk to return the columns, or  we can explicitly specify the columns that you want to return.  We can also use, arithmetic expressions here, and optionally, we can even  gibe an alias to each column in the results set. Now there is one more thing you need to know about the select clause. So let&amp;#39;s delete this query  and select the state column,  of the customers table. Take a look. These are the  states in which our customers our located. Now currently in the sample  data we don&amp;#39;t have any duplicates, in other words we don&amp;#39;t have multiple customer in any of these states. But for this demo I want to change the  state of the first customer to Virginia, so we end up with duplicates  in the result set. So let&amp;#39;s open up navigator panel here&amp;#39;s our customers table, let&amp;#39;s look at all the data,  and here&amp;#39;s our first customer,  here a you can see, it&amp;#39;s located in the state of Massachusetts  now I want to change this to Virginia. So double click VA for Virginia, enter, now  on the bottom right corner of the screen, you should see two buttons,  apply and revert. Unfortunately I cannot show you this button  because the recording window is a bit smaller then MySQL  But look down in the bottom right hand corner, click and apply. You&amp;#39;re going to see a dial up box like this asking you to review the changes, so go ahead and click that button one more time alright, now, let&amp;#39;s go back to our first query window, and execute this query one more time.  As you can see, the first two customers are located in Virginia. What if you wanted to get a unique list of states in the results set, that&amp;#39;s when we use a distinct  keyword. So select distinct set.  With his query, we&amp;#39;ll retrieve a unique list of states from the  customers table. So with the distinct keyword we can remove  let&amp;#39;s execute the query one more time, now you can see Virginia is not duplicated.  Alright here&amp;#39;s an exercise for you.  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  times 1.1. So let&amp;#39;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  one or two minutes on this exercise, when you are done come back and see my solution.   Alright this is pretty easy, so we start with select  now what columns do we want to select? Name, unit _price and then here we&amp;#39;re going to use an arithmetic  expression to calculate the new price. So we type  out unit price times 1.1 and then give it a  alias, o 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 products table, so from  products. Note that I&amp;#39;ve used upper case characters for all the SQL keywords and lowercase characters for everything else. So,  let&amp;#39;s go ahead and execute tis 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&amp;#39;re going to look at the where clause in SQL. So earlier I told you that we use the where clause to filter  data. For example, let&amp;#39;s say we only want to get the customers with  points greater than 3000. So here in the where clause you can type out the condition like this. Points, greater than  3000. When we execute this query, the query execution engine in MySQL is going to iterate over all the customers  in the customers table. For each customer it&amp;#39;s going to evaluate this condition  if this condition is true, it will return that customer in the  result set. So let&amp;#39;s go ahead and execute this,  and here&amp;#39;s the result, as you can see you only have two customers  with points greater than 3000.  So, thesis 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 then,  less than or equal to, here&amp;#39;s the equality  operator, and for not equality, we can use an exclamation  followed by an equal sign, or something like this. So both  of these are not equal operators. Let me show you examples of  these operators. So I&amp;#39;m going to delete all these and bring back the previous query, let&amp;#39;s say we want to get only the customers in the state of Virginia. So we can change our condition to something like this.  Where state equals Virginia.  Note that I&amp;#39;ve put Virginia in quotes, because this is what we call a  string. A string is a sequence of characters. So whenever you&amp;#39;re dealing with a sequence of characters, or basically textural data, we  need to enclose your values with either single or double quotes.  But quite often, by convention, we use single quotes  so let&amp;#39;s execute this query and here&amp;#39;s the result, you can see we only have these two customers with ID 1 and 2, who are located in Virginia. And it doesn&amp;#39;t matter if you use upper case or lower case characters, so if you type out the a in lower case and execute the query you get the exact same result. Now what if you want to get all the customers  outside of the state of Virginia, you can use the not equal operator.  So, we can either prefix this with an exclamation  or use this other notation.  Either way we get the same result. So these are  the customers that are not located in Virginia. Now we can  use these comparison operators for date values as well. For example,  let&amp;#39;s say you want to get only the customers born after January  1st, 1990. So we change out condition to first date, greater then, once again we use quotes,  for representing date values even though dates are actually not  strings. But in the sequel language, we should enclose dates with quotes,  so, here we type out 1990,  01 for January, -01 for date. So 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&amp;#39;s go ahead and execute this query. I actually made a mistake here, so we don&amp;#39;t see the  result, instead we see the action output, or the output 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 is the name of our column. So,  let&amp;#39;s execute the query one more time, so we only have 3 customers born after January 1st 1990. So these  are examples of comparison operators in SQL. In the next tutorial I&amp;#39;m going to show you how to combine multiple conditions when filtering data.   Alright, here&amp;#39;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 to do we have there, and based on that write query with a  where clause.   So here&amp;#39;s the orders 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&amp;#39;s our query, select, start from order  where order_date, is  greater than or equal to 2019, assuming  this is the current yer, so 2019, 0101.  Now since currently we are in the year 2019, this query will return all the orders placed this year. But next year this query  is not going to give us the right result. But don&amp;#39;t worry about it, later in the course I will show you how to write a query to get the orders placed in the current year,  so for the purpose of the new exercise, this is a valid solution. Now let&amp;#39;s execute this query and see what we get. So, we have  only one order, order ID 1, that is placed in the current  year.  In this tutorial I&amp;#39;m going to show you how to combine multiple search conditions when filtering data. So, let&amp;#39;s say you want to get all the customers that were born after January 1st, 1990, who also happen to have more then  1000 points. So this is where we use the and operator.  So we type out and, and after we type out another condition.  Like points, greater than 1000.  Now when we execute this query, we only get customers who have  both these conditions. Let&amp;#39;s take a look, so execute  we only have 2 customers and if you look both these people are born after 1990, and they have more then  1000 points. So this is the and operator. When we use this  operator, both these conditions should be true. In contrast to the and  operator, we have the or operator. So with or if at least one of these conditions is true, that row will be returned in the  result set. Let&amp;#39;s take a look. Now we execute this query again,  2 records we have quite a few records. So for example,  we have this person who&amp;#39;s not born after 1990  but if you look at their points they have more then 1000 points.  So any customer records that satisfies at least one of these conditions will be returned. Now let&amp;#39;s take this to the next level. Let&amp;#39;s say we want to get customers who are either born after 1990, or  they should have at least 1000 points and live in Virginia  so this is how we do this, we type out and  and then we add another condition, state equals  Virginia. Let&amp;#39;s execute this query and see what we get. We only get 4 records, so these are the customers that are either born after 1990 or they have more then 1000 points and live  in Virginia. If you look at the first customer here this person is not born after 1990. But you can see that she lives in Virginia and she has more than 1000 points. So the last two conditions.  are true for this customer. Now when combining multiple logical operators. You need to be aware of the order of these operators.  So earlier I talked about he order of arithmetic operators. I told you that multiplication have a higher order then addition and  subtraction. And we can use parenthesis 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&amp;#39;re using an and it doesn&amp;#39;t matter that we typed out this condition after the first condition.  Because the and operator has a higher precedence. Now you can always change the order using parenthesis. And this also makes your code cleaner  and easier to understand. So here we can put parenthesis,  around these last two conditions,  and also we can put these in a  new line for  clarity. Something like this. So anyone  who reads this code can easily understand what is the intent of this query.  Now we also have logical operator called  not. And we use that to negate a condition. So,  I&amp;#39;m going to simplify our where clause. Let&amp;#39;s say we&amp;#39;re searching for customers who were born after 1990 or  we have more then 1000 points. If we execute this query  we get these people. Customers with  ID 1, 3, 5 and so on. Now we can use the not operator  to negate this condition. So we apply not here, and  preferably we also put parenthesis around this condition.  Now when we execute this query, we see other customers that are not  in the current results set. Let&amp;#39;s take a look. So,  instead of customers with id&amp;#39;s 1356 and so on, we get customers with id&amp;#39;s 2 4 and 10.  Now technically these customers were born before 1990. And we have less then 1000 points. So if you look here,  this first customer was born before 1990 and  he has less then 1000 points. How do they know that? Let me show you a trick that I learned in math. Whenever you have a not  operator. You can simplify your expression like this. We apply the not operator  to the first condition. People who were born after 1990, how can we negate this condition? Well,  the greater than operator becomes less then or equal to.  That is the inverse of that condition. Now we  apply the not to or to negate the or. What do we get? We get and. Finally apply the not operator on the last condition. We both have more then 1000 points.  When we negate this condition we get customers with less then or equal  to 1000 points. Now, you can remove the not operator  to simplify this, we don&amp;#39;t need parenthesis anymore because we  only have 2 conditions that are combined with an end. Here is the result.  As you can see, this is much easier to read and understand  people who were born before this day and they have less then 1000 points   Alright, here&amp;#39;s your exercise. From the order items  table, get the items for order number 6,  where the total price for the total item is greater then  30.  Alright, here&amp;#39;s the order items table, in this table we have these columns order id, product id, quality, and unit  price. If we multiply the quantity by unit price we can  get the total cost of that item. And then we can compare it with  30. So, let&amp;#39;s go ahead and write this query.  Select star  from order items. Where  here we need two conditions one is for order, so order  _id should be 6, and the second condition  we want to calculate the total price. So we get the unit price multiply it by quantity and this  value should be greater than 30. So as you can see,  we can use an arithmetic expression in the where clause. It&amp;#39;s not limited  to the select clause. Okay? Now let&amp;#39;s execute this query and see what we get. We should get only 1 item,  that is for product 1, here in quantities 4 and unit price  is just over 8 dollars, so the total price for this item, is greater than 30.  Hey guys, Mosh here.  In case you haven&amp;#39;t seen my website yet, head over to codewithmosh.com.  This is my coding school where you can find plenty of courses  on web and mobile application development. In fact recently I  published a complete SQL course that is about 10 hours long,  and it teaches you everything you need to know from the basic to advanced topics  such as database design, security, writing complex queries,  transactions, events, and much much more. These are the topics that every software engineer must master. This YouTube course  you&amp;#39;re watching, is the first 3 hours of my complete SQL course that is about 10 hours long. So if you want to master SQL and get job ready, I highly encourage you to 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 for this course is $149 dollars, but the first 200 students can get it for just over  10 dollars. So if you&amp;#39;re interested the link is below this video.   In this tutorial I&amp;#39;m going to show you how to use the  in operator in SQL. So, as an example, let&amp;#39;s say you want to get the customers that are in Virginia or Florida, or Georgia. One way to write this query is like this. So, where state equals Virginia, or  a state equals Georgia, or a state equals Florida. Now people who are new to the SQL language or  programming in general find this expression a little bit strange.  They ask, &amp;quot;Mosh, why can&amp;#39;t we write this expression like this?&amp;quot; where state equals Virginia or Georgia or  Florida. Here&amp;#39;s the reason, we use the or operator  to combine multiple conditions. So here we have a condition  or an expression more accurately, but on the right side of this or operator  we have a string. In SQL we cannot combine a string  with a boolean expression that expresses a boolean value which can be  true or false. So that is why we have to write our query  like this. So we have multiple expressions or multiple conditions  and we&amp;#39;re combining them using the or operator.  So, now if we execute this query we get these customers here.  customers here but there is a shorter and cleaner way to get the same result.  Instead of combining multiple conditions using the or operator we can use the in operator. So, where state  is in and then in parenthesis we add all the values,  like Virginia, comma, Florida, comma Georgia and the order doesn&amp;#39;t matter, this query is exactly equivalent  to what we had earlier, but a you can see it&amp;#39;s shorter and easier to understand.  So, let&amp;#39;s execute it, look, we get the exact same result. Now here we can also use the not operator. Let&amp;#39;s say you want to get the customer&amp;#39;s outside of these states, so we can use  rare state, not in this list.  Now if you execute this query, we get customers who are located in Colorado,  Texas and so on. So use the in operator whenever  you want to compare an attribute to a list of values.  Now here is your  exercise. I want you to write a query to get the products where  their quantity in stock equals on of these values.  49, 38, and 72, so pause the video, do this exercise, and  then come back and continue watching.  Alright, this is pretty easy, so we do a  select star to get all the columns from the products table,  where quantity in stock  in we use the in operator to compare this attribute with these values.  49, 38, and 72, let&amp;#39;s execute the query,  we get only 2 records because we  don&amp;#39;t have a product with quantity in stock equal to 72.   In this tutorial, we&amp;#39;re going to look at the between operator  in SQL, so that means we want to get the customers who have more then 1000 and less then 3000 points. What made you write these queries like this? Where, points, greater than thousand, more accurately greater than or equal to 1000.  And, points less then or equal to  3000. When we execute this query we get  how many, we get 4 people that satisfy my criteria.  Now whenever you&amp;#39;re comparing 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 points,  between 1000 and 3000.  This is exactly equivalent to what we had before, so these range values are also inclusive, so that means points is going to be  greater than or equal to 1000 or greater than or equal to  3000. Let&amp;#39;s execute the query, we get the exact same result.   Alright, now as an exercise, I want you to write a query, to get the customers  that are born between January 1st 1990 and January  21st, 2000.  Alright, so we start the  select star from customers,  where birth_date between  so what matters here is we can use the between operator  as well. It&amp;#39;s not limited to using numbers. So with birth date between now we need to supply two date values, so as I told you before,  the format for dates is four digits for the year, so  1990, two digits for the month, and two digits for the date.  So, the birthdate should be between this value and  here&amp;#39;s a second value. 2000, 0, 1, and  01. Let&amp;#39;s execute this query, we get  only 3 people who match this criteria.   In this tutorial, I&amp;#39;m going to show you how to retrieve  rows that match a specific string pattern, for example,  let&amp;#39;s say we only want to get to customers who&amp;#39;s last name start with  3. So, here in the where clause we type out where last name this is where we use the like operator,  and right after that we have a string pattern, so,  you want to get the customers who&amp;#39;s last name start with b and we have  any number of characters after b. So use the  percent sign to indicate any number of characters, you may have  one character after b or no characters or 10 characters, with this pattern, you get all the customers who&amp;#39;s patterns start with b.  And also it doesn&amp;#39;t matter if it&amp;#39;s an upper case or lower case b.  So, let&amp;#39;s execute this query, there you go, so you only have  3 customers who&amp;#39;s last name starts with b. As another  example, let&amp;#39;s say we only want to get the customers who&amp;#39;s last name starts with  brush. So, we change our pattern to brush,  percent. Now, let&amp;#39;s execute the query, we only get  this one customer here. Now this percent sign doesn&amp;#39;t have to be  at the end of the pattern, it can be anywhere. For example, let&amp;#39;s say we want  to search for customers who have an e in their last name, whether it&amp;#39;s at the beginning  in the middle or at the end, so we change our pattern to  percent e percent this pattern means we can  have any number of characters before or after b. Let&amp;#39;s execute the query, these are the customers that have a b  somewhere in their last name. It doesn&amp;#39;t matter if b is in the beginning  or in the middle or at the end. Let&amp;#39;s look at another example,  I want to get all the customers who&amp;#39;s last names end  with y, so here&amp;#39;s the pattern we use,  let&amp;#39;s execute this query, so we have 5 customers  who&amp;#39;s last name ends with a y. So this is how we use the  percent sign. Now we also have an underscore and  that matches a single character so with this pattern you get customer  who&amp;#39;s last name is exactly two characters long,  we don&amp;#39;t care what the first character is, but the second character should be y.  Let&amp;#39;s execute his for you. Obviously we don&amp;#39;t have any customers who&amp;#39;s last  name matches this pattern. But if you change this pattern to  5 under score, so 1234 5, followed by a y we should get  these customers. So their last name is exactly  6 characters, we don&amp;#39;t care about the first 5 characters, but all of them end with a y. Now, as another example,  we can replace the first underscore with b, and that means  we want to get the customers who&amp;#39;s last names start with b, and  after b we have exactly 4 characters followed by a  y. Let&amp;#39;s execute this query. So we only have one customer,  that matches this pattern. So this is how we use the  light operator, use the percent sign to represent  any number of characters and an underscore  to represent a single character.  Now this like operator in MySQL is an older operator  but we also have a newer one that is more powerful and it allows us to search for any string patterns. And that&amp;#39;s what I&amp;#39;m going to show you next.  Alright.  Here I&amp;#39;m going to give you two exercises for the like operator.  First, I want you to get the customers who&amp;#39;s addresses contain trade or avenue, and next I want you to get the customers who&amp;#39;s phone  numbers end with 9.   Alright, let&amp;#39;s get started with the first exercise. So  start from customers where address like now here we want to use a search pattern like this.  You want to have tray (?) but tray can be anywhere in the address. So,  we put a percent before and after tray.  Next, we should use the or operator to search for another pattern.  Or address like, let me  put this on a new line that is better, address once again, percent, avenue percent, that&amp;#39;s it.  So, let&amp;#39;s execute this query,  here is the result, you should get the customers with ID&amp;#39;s  2, 9, and 10. If you look at their addresses, all of them have either tray or avenue in their address. Now let&amp;#39;s work on the second exercise, you want to get  the customers who&amp;#39;s phone numbers end with 9. That is pretty easy,  so, let me change our where clause. Where  phone once again we use the like operator and a percent,  followed by a 9. That&amp;#39;s all you have to do. Let&amp;#39;s execute the  query. So, here&amp;#39;s the result, customers with  id&amp;#39;s 3 and 7, their phone numbers, and  9. So this is how we use the like operator. And by the way you can always use the not operator here let&amp;#39;s say you want to get the  customers who&amp;#39;s phone numbers don&amp;#39;t end with 9,  so we simply prefix like with not. Now,  if we execute this query one more time, we get all the other  customers in the database.  In the last tutorial you learned about the like operator in SQL. So as another example  let&amp;#39;s say you want to search for the customers who have the word  field in their last name. So we type out the where clause like this,  where last name, like, percent,  field, percent, so the word field,  can be anywhere in the last name. Let&amp;#39;s execute this query, we get  only one customer, beautiful. Now we also have another operator in MySQL, that is reg x which is  short for regular expression, and regular expressions are extremely  powerful when it comes to searching for strings. So they allow us to search for more complex patterns. Here&amp;#39;s an example. If I want to rewrite this last where clause using a regular  expression it looks like. Where last name  ragexp now here in our string pattern  we don&amp;#39;t have to type out the person&amp;#39;s size we only type  out field so what we have on line 4 is exactly  identical to what we have on line 3, let&amp;#39;s execute this query  we get the same result, beautiful, now here in regular  expressions, we have additional characters that we don&amp;#39;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  just before the word field, that means our  last name must start with field. Obviously, if you execute this query, we don&amp;#39;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 the last name must end with field.  Let&amp;#39;s execute this query, you get the same result as before. Now we can also search for multiple words here, for example let&amp;#39; say we want to find  the customers who have the word field or mac in their last name So, we use a pipe, a vertical bar,  and type out another pattern. Let&amp;#39;s execute this query, so here we have two customers, one of them has the word  mac, the other has the word field and the last name.  Now we can take this to the next level. Let&amp;#39;s say we want  to find the customers who have the words field, or Mac, or rows  in their last name. Let&amp;#39;s execute the query we get 3 customers.  Beautiful. So we use a pipe or a vertical 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 this last name should either start  with the word field, or it should have the word mac in it or it should have the word rows. Let&amp;#39;s execute the query, now we get only two customers, because our customer with the last name brush field doesn&amp;#39;t match this pattern.  However, if we change our first pattern to field  $ and execute the query we get 3 people here,  3 customers. So this is how we can combine multiple special characters when building a complex pattern. Now let&amp;#39;s look at another  example, let&amp;#39;s say you want to search for customers who have an e in their last name.  So these are all the people, alright, now let&amp;#39;s say  you want to make sure that before the letter e, we should either have a  g or an i. So this is where we use square brackets.  And inside the brackets we add multiple characters like g, i, m and that matches any customers who have  ge or ie or me  in their last name. So any of these characters can come before e.  Now, let&amp;#39;s execute this query,  there you go, we only get 2 customers, and the first example before e we have i which is one of the characters  inside the brackets, in the second example, before e we have  a g which is also another valid character before  and once again the square brackets don&amp;#39;t have to be before we could add them after e, any customers who have e  followed by an f or an m or a q in their last  name, can be returned with this pattern,  if we don&amp;#39;t have anyone in the database so this is how we use square brackets,  now we can also supply a range of characters, for example  we can have e and just before e you can have  as many characters from a to h, you don&amp;#39;t have to type them out explicitly like abcdefg, that&amp;#39;s very  verbose, so, we can type out a to h.  And then if we execute this query we get these three people.  So, let&amp;#39;s quickly recap everything you learned about regular expressions in this tutorial. Use a carrot to represent the beginning of a string, so beginning, we use a dollar sign to represent  the end of a string, we use a vertical bar or pipe,  to represent a logical or so we can supply multiple search patterns, we use square brackets to  match any single characters listed in the brackets, and finally  we use square brackets with a  hyphen to represent a range.  So any characters from a to f.  Technically MySQL supports more special characters but quite honestly, the ones that I&amp;#39;ve listed in this tutorial are the ones that you&amp;#39;ll be using 90% of the time. So just memorize these and you&amp;#39;re good to go.  With honesty a lot of beginners find the syntax for regular expressions confusing, so in this video I&amp;#39;m going to give you 4 exercises that I have carefully  designed to help you quickly learn about this syntax. Here&amp;#39;s the first exercise, get the customer&amp;#39;s who&amp;#39;s first names are Elka  or Ambur. And note that this is Ambur with a U.  Now for the second episode return the customer&amp;#39;s who&amp;#39;s names  end with ei or on.  Here&amp;#39;s the third exercise get the customer&amp;#39;s who&amp;#39;s last names start with my or it contains se, and finally  as the last exercise return the customer&amp;#39;s who&amp;#39;s last names contain e followed by r or a u.  So, go ahead and spend 2-3 minutes on this exercise,  when you&amp;#39;re done come back and continue watching.   Alright, let&amp;#39;s knock out the first exercise. So we&amp;#39;ll get  allows, there&amp;#39;s a c here, so let&amp;#39;s start from   regular expression, and here&amp;#39;s our pattern, you&amp;#39;re going to search  for two words either Elka or Ambur.  As simple as that. Let&amp;#39;s execute this query, we should get two customers, there you go, Ambur and Elka. Alright. Now, let&amp;#39;s knock out the second exercise. So I&amp;#39;m going to delete these  we don&amp;#39;t need them anymore. So we want to get the customers  select start from customers, where  last name should end with either ey or  om. So, in the search pattern we type out  ey followed by a dollar sign to indicate the end of a string then we add a vertical bar to supply the second search pattern. So On and once again dollar sign.  Let&amp;#39;s execute this query, oops I forgot to type out regular expression, there you go. So,  Let&amp;#39;s execute this query, and you should get  these four customers with ID&amp;#39;s one  three, five and seven. The first three, five, and seven, the first three, their last names  end with ey, and the last customer, his or her last  name ends with on. Alright, Now, let&amp;#39;s  work on the third exercise. So I&amp;#39;m just going to change the regular expression  here, we want to get the customers whose last names start 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&amp;#39;s execute this query  and we get the customer&amp;#39;s with ID&amp;#39;s  4, 8, and 10 and finally  we want to get the customers who&amp;#39;s last names contain e  so, let&amp;#39;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 so  we have b followed by r or u, that&amp;#39;s one way, or the other way is to use a vertical bar. So b r or bu. These are both valid solutions. So, I hope you knocked out these exercises, in the next tutorial I&amp;#39;m going to show you  how to get the records with missing values.   In this tutorial, I&amp;#39;m going to show you how to look for records that miss  an attribute. For example, if you select all the customers in our database,  you can see that the customer with id 5 doesn&amp;#39;t 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&amp;#39;s say we want to search for all the customers who don&amp;#39;t have a phone. Perhaps you want to send them an email and say hey, your phone is missing in our system. So how can we get these customers? That is very easy? We use the is null operator, so in the where clause, we type out  where phone is null. Now let&amp;#39;s execute  this query, we only get one customer who doesn&amp;#39;t have a phone,  now here we can also use the not operator to get  the customers who do have a phone. So we change the conditions to is not null. Let&amp;#39;s execute the query,  now in the query results, every customer  does have a phone number.  For this exercise, I want you to write a query  to get the orders that are not shipped yet. This is a very useful query  that is used in a  lot of real applications. For example, let&amp;#39;s say you&amp;#39;re an admin for online shop. You want to see the orders that are not shipped yet, so you can see  them all. So write a query, and get these orders.   So here we have the orders table, let&amp;#39;s have a quick look at the data in this table. So if you pay close attention  you see some of these orders don&amp;#39;t have a date. And these orders also don&amp;#39;t have a shipper id which  is a unique number for identifying the shippers. So any order that misses the value for the ship date or shipper ID,  is considered an order that is not shipped. So let&amp;#39;s go ahead and write  a query to get these orders. So back to our query editor  select star from orders where  ship_date is  null. You could also write shipper id is null. They&amp;#39;re both equally correct. So let&amp;#39;s execute this query, and you should get  5 orders. Orders 1, 3, 4, 6,  and 8.  In this tutorial I&amp;#39;m going to show you how to sort data in your sequel queries. So here in your have a query to select all the customers from the customers table, if you look at  the query result, you can see that our customer&amp;#39;s or sorted by id,  so we have customers 1, 2, 3, 4, and so on. This is the  default sort column. But we can always change this using the  order by clause. But first, let me explain  why the customer id column is the default sort column. So first of all the first I&amp;#39;m going to open up the navigators panel on the left side here&amp;#39;s the customers table, now let&amp;#39;s click on this middle icon here that looks like  tool. This opens up our customers table in the design mode,  here we can change our columns we cana dd new column or  remove existing ones or change that name and order and so on.  Now if you pay close attention you can see a yellow key just before  This means that this column is the primary key column.  For this query. So in relational databases every table should have a primary key column, and the values in that column  should uniquely identify the records in that table. So  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 is why when you write a  query against this table, our customers will sort it by id by default.  Now, let me show you how to sort customers by a different column.  So, here in the order by clause, you type out the name of another column, like first name. Let&amp;#39;s execute the query, now we can  see our customers are no longer sorted by their id, instead  they are sorted by their first name in ascending order. Now,  if you want to reverse the sort order, simply type out de,  e, sc, which is short for descending. Now,  you&amp;#39;re sorting the customers in this order, okay,  we can also sort data by multiple columns, for example,  let&amp;#39;s say first we want to sort customers based on their state,  and then within each state, we want to sort them by their first name.  So, we type out multiple columns here, state  and first name. Let&amp;#39;s execute 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 customers  and these customers are sorted by their first name. Let&amp;#39;s have a  close look here, so first we have Ambur and then we have other customers here. Now we can also use the descending argument anywhere here.  For example, we can sort these customers by their state in descending  order. And then sort them by their first name in ascending order  or once, again in descending order. So there are various ways we can sort data. Now one of the differences between MySQL, and  other database management systems, is that in MySQL you can sort data by any columns whether that column is in the select clause  or not. For example, let&amp;#39;s say you only want to select  the first and last name for each customer. Now we can sort the result by any columns in this table, they don&amp;#39;t have to be first name and last names For example, we can sort them by their birthdate,  take a look, so this is a valid query in MySQL, but other databases management sometimes yell at you when you update a query like this.  Now we can also sort data by an alias for example, here in out select clause let&amp;#39;s add the number ten and give it a  alias. As let&amp;#39;s say points, so points is not a valid column in this table, it&amp;#39;s simply an alias  or expression, in this case a simple number. And here we could have a complex  mathematical expression, it doesn&amp;#39;t really matter, we can still sort  data by Alias, so we can order by points  and then first name. Once again this is a valid,  query from MySQL&amp;#39;s point of view. Now one last thing before we finish  this tutorial, I&amp;#39;ve seen some tutorials that teach you how to sort data by calling positions, for example, here we can order it by  1, 2, and that basically means sort the data by the first name,  and then, the last name. So these are the orders of these columns,  if you execute this query, you can see that our customers are sorted by their first  name and then last name. Why this approach works, it&amp;#39;s something that you should avoid. Because if in the future you come back here and add  a new column, in front of the first name column, let&amp;#39;s say  first, date. Now our customers are no longer sorted  in this order. So sorting data by calling positions  produces unexpected results and is something you should avoid.  always sort by column names like first,   Alright, here&amp;#39;s your exercise for this tutorial. In this database, we have this table,  called order items, where we find the items for each order. Now,  I&amp;#39;ve written the query that you cannot see here, because that&amp;#39;s the solution to the exercise  I&amp;#39;m going to give you, that query produces this results. So  we only have the items for the order with ID 2, and we  have sorted these items based on the total price  for each item. So the total price for each item equals quantity times 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 the items for order with ID2. And  sort them by their total price in descending order.   Alright, let&amp;#39;s select everything from order  items, where order id equals 2.  that returns all the items for this order. Now we want to make sure  to sort them by their total price, so,  here in order by clause, we write an expression. Quantity times  unit price, this returns the total price for each item.  And then we add the descending argument here. So,  once again the expression that we use in the order by clause doesn&amp;#39;t have to be  column name, it can be an alias or an arithmetic expression like this.  Let&amp;#39;s go ahead and execute this query. This is what we get  now for clarity, I would like to add another column in the result  So, let&amp;#39;s say quantity times unit _price. We give it an alias like total  price. Let&amp;#39;s execute the query, you can clearly  see, that this data ia sorted by the total rice in descending order.  However, there is a bit of duplication in our query, you have to repeated this expression in two places. So now we can simplify our  by clause, by using an alias that is total price.  And we gwet the exact same result. Net I&amp;#39;m going to show you how to mimic the number of records returned form your queries.   In this tutorial, I&amp;#39;m going to show you how to limit the  records returned from the query. For example, when we return this query  we get all the customers in the customer, table, so we have to  ten customers here. Now what if we only want to get,  the first 3 customers. That&amp;#39;s where we use the limit clause.  Let me show you. So, after from, you type out limit 3, and this will return only the first 3  customers. Now if the argument that we pass here is greater than the number of records that our query produces, we&amp;#39;ll get  the records in the query result. For example, if I pass 300 here,  obviously we don&amp;#39;t have 300 customers in this table, so when we execute this query. We get all the ten customers in this table.  So this is how the limit clause works.  Now here we can optionally supply an offset, and this is very  useful in situations where you want to paginate the data. For example,  let&amp;#39;s say we have a website, and on this website we have a web page for the user to see all the customers int he database. Now for simplicity  let&amp;#39;s imagine, we want to show only there customers per page. So,  what are the customer&amp;#39;s you&amp;#39;re going to have on page 1? We&amp;#39;re going to have  customers 123. On page 2 we&amp;#39;re going to have customers 456 and on page 3 we&amp;#39;re going to have customers 789.  Now, let&amp;#39;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 pick 3 records. So,  we change 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 records  and then take 3 records. Let&amp;#39;s execute this query. Alright, now we get  customers 789.  Now, for your  exercise, I want you to get the top 3 loyal customers. These are the customers that have more points then everyone else.   Alright, first we select everything from the customers table,  now we need to order these customers by their points,  in descending order. So if you look at the query result  you can see that customers are sorted by their loyalty. So,  the most loyal customers come first, now we want to pick  only the first three customers, and that&amp;#39;s where  we sue the limit clause, so limit,  let&amp;#39;s execute this query. And these are the  most loyal customers, customers with id&amp;#39;s 56 and 3. Now, here&amp;#39;s one thing I want you to remember and that is  the order of the limit clause. The limit clause should always  come at the end. So first we have the select clause and then we have from optionally you can have where followed by order by and finally limit. Order of these clauses matter  If you change the order MySQL is going to yell at you. So pay attention to the order when writing your queries.   So far you have only selected columns from a single but in a real  world, we quite often select columns from multiple tables. And that&amp;#39;s what I&amp;#39;m going to show you over the next few tutorials, so on the left side if you put  our orders table. Let&amp;#39;s select all the data here,  in this table we&amp;#39;re using the customer id column to identify the customer  that has placed each order. Now as I have told you before  we don&amp;#39;t store customers information here like their phone number, their email their address,  because this information can change in the future. And if  even a customer has placed multiple orders then we have to change multiple records, you don&amp;#39;t want to do that. That&amp;#39;s why we have separate tables  for customers and orders. Now in this tutorial I&amp;#39;m going to show you how to select the orders in the orders table, but instead of showing the customer id, we showed full name for each customer. So lets go back to our query window. Okay, so,  we want to select everything from the orders table. Now we should combine the  columns in this table with he columns in the customers table. That  is where we use the join keyword. And here we can optionally  type inner join, because we in SQL we have two types of join inner join and over join. You&amp;#39;ll look at outer joins later in this section,  so for now we are only using inner join and these inner keyword,  is actually optional, so we don&amp;#39;t have to type it. So, you want to join the orders table with the customer table. Now, on what basis do we want to join these tables?  Well, here in the customers table, we have this customer id column.  So if you put these two tables next to each other you want to line up the records  such that the customer ID&amp;#39;s are equal. That is where  we use the on phrase. So after we type out a condition, here&amp;#39;s a condition we need to type out,  orders.customer_id. Should be equal to  customers.customer id.  Now, this is getting outside of the screen, so let&amp;#39;s break up the line,  that&amp;#39;s better, so with this query, we&amp;#39;re telling MySQl that hey, whenever you&amp;#39;re going the orders table  with the customers table, make sure that the customer ID column,  in the orders table, equals the customer ID  column, in the customers table. Now let&amp;#39;s execute this query,  look at the result,  since we are selecting everything here, the first few columns are from the orders table, because we have listed that first  now after all the columns in the  customer table. So customer ID first name, last name and so on.  Now let&amp;#39;s simplify the result set and select only  border id, first name, and last name, so query  we select order ID, first  name, and last name. Now let&amp;#39;s execute the query  that is better. So next to each order ID, you can see the  name of the customer that placed that order. Now what if you want to display  the customer ID here as well. Well, let&amp;#39;s put that here and  see what happens. Customer ID, execute the query  we get an error, so if you look at the output window, down at the bottom,  we should see an error saying column, customer id, field this is ambiguous. Now unfortunately I cannot show you this error, because the size of my recording window is smaller then MySQL workbench. But that aside, let me explain why we&amp;#39;re getting this error. Because we have this customer ID  column in both the orders and the customers table,  so MySQL is not sure which table we want to select this column from. That is why it&amp;#39;s saying this column is ambiguous  so we need to qualify this column by prefixing it with a table name.  We can either pick it from order table or  the customers table, it doesn&amp;#39;t really matter, because the values are equal, right? So, in situations where you have  the same column in multiple tables, you need to qualify them,  by prefixing them with the name of the table, okay, now,  we say tot he query one more time, there you go, you have order ID,  customer ID and the full name. Now one more thing  before we finish this tutorial, if you pay close attention we have repeated the word  orders in multipel places, you have it here, as well as  in the join condition, the same is true about he customers table, you have repeated that here. We can get rid of this repetition  and make our code simpler by using an alias. So write  after each table you can do it in alias,  as a short for orders, so by convention we abbreviate the table&amp;#39;s name.  Now, wherever we have orders you should replace that with  o. So here in the join condition, we&amp;#39;re going to replace orders  with o, and also one more time, in the select  clause. There you go. You can also apply an alias for  a customer&amp;#39;s table, call it c, and then simplify  our join condition like this. So 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 with the products table so for each order  return both the product id as well as this name, followed by the  quantity, and the unit price form the order items table. And by the way  make sure to use an alias to simplify your code,   Alright, first let&amp;#39;s select everything from the order  items table, and then join it with the  products table. How are we going to join this table. On order_items. Well actually  let&amp;#39;s just keep this on Alias right away, so we use oi,  as an abbreviation for order items. And p as  a short for products. So oi.product id, should be equal to p or products. product id. And by the way, remember that alias for table, you have to use that alias everywhere. So here I cannot type out product, MySQL is going to yell at me. So let&amp;#39;s use the abbreviation. Alright, this is how we join these tables,  let&amp;#39;s execute this query up to this point. Alright,  so we see all the items form the order items table, followed by  from the products table. Now, you want  to explicit select a few column here. So, to  order items table you want to select order_id.  We take it then we don&amp;#39;t have to prefix it with a table name  because this calumnies not repeated in multiple places, so it&amp;#39;s not ambiguous.  So, let&amp;#39;s make the code shorter, that&amp;#39;s better, now  we want to select the product ID column, but because this column exists in both tables, you have to both prefix it with a column name. Either oi or p it doesn&amp;#39;t really matter. So,  next, we want to select quantity, and finally you want  price. Now actually here, you have this unit price column.  In both tables, so this is the unit price in order items table  and this is the unit price in the products table,  now you might be curious why we have this column in 2 places,  is that the price of product can change, so for each  order item, you want to have the price at the time the user  placed the order. So this is a snapshot of the price  at a given point in time. The unit price that we have for the products table  is the current price right now. This is very important for the  recording, otherwise we cannot calculate the sales properly. So,  because we have the unit price column in two places, in this case we should pick it from the quarter items table because this is the price at the time of now, let&amp;#39;s execute query. So here&amp;#39;s  the final result.  In the real world, when you work as a developer or a data base administrator quite often you will  have to work with multiple databases. In this tutorial I&amp;#39;m going to show you how to combine columns from tables in multiple places.  That&amp;#39;s pretty easy. So, in the SQL store database, you have this tables  that you&amp;#39;re fairly familiar with. Now imagine this products table was 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.  Now technically this is not a good design, you don&amp;#39;t want to have the same table  repeated in multiple places. But for this demo, let&amp;#39;s just imagine  that we don&amp;#39;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&amp;#39;s get started. So select  everything from the order items table,  let&amp;#39;s give it an alias straight away, you want to join this with the products table. This products table is part of the products inventory  database. So we&amp;#39;ll have to prefix this with the name of it&amp;#39;s database.  So, we type out SQL inventory. Now once again we can give this an alias like p,  then type out our joint condition, so oi.product  id should be the same as p.product id.  Let&amp;#39;s run the query there you go, so we successfully joined  tables across multiple databases. Now note that we&amp;#39;re prefixing the products table with the name of the database because  the current database that we&amp;#39;re writing this query with is the SQL stored  database. Take a look, in the navigator panel, in the SQL  store database is displayed in full. Because earlier, the  root we use statement to select  a database, that will SQL store. Now what if we select  the SQL inventory database. So, let&amp;#39;s see what happens SQL  inventory, now because we have multiple statements we have  to terminate this with a semi colon. Now we want to select everything with a  order items table. But we don&amp;#39;t have this table inside of this  database. So now we&amp;#39;ll have to prefix this table  with the name of it&amp;#39;s database, that is SQL underline  story. Let&amp;#39;s execute the query, okay,  everything works, beautifully. So here&amp;#39;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&amp;#39;s take a look at the  data int he employees table.  There you go. So here we have these columns,  employee id, first name, last name, salary  and reports 2. This is the id of  the manager for this person or this employee. Now once again,  you don&amp;#39;t want to repeat the managers information here, like the phone number, the address because this information can change in the future, so we are only using  your identifier 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 look on the website, here is the ID of that manager which is another employee. That was the manager for this employee. We don&amp;#39;t have any values here so  the value for the sale is null. So this employee doesn&amp;#39;t have a manager  and that means they are the CEO. So let&amp;#39;s go ahead and  write a query to join this table with itself so we can select the name of each employee and their manager. Back to our query window,  first we need to select the SQL hr database.  Next, we select everything from  the employees table, we give an alias like b now we need to join this table with itself. So once again we type  out the 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&amp;#39;s type our our joint condition.  So from the employees table, we need to join  the reports_2 column. To the manager  table which is basically an employees table itself, and  that is employee_ id. Now let&amp;#39;s execute this query and see what we get.  So, we see all the columns from the employees table  repeated the first set of columns, represent the information about the  employees, and after that, we have the information about the managers in this case we have only one manager in this table.  But with this technique we can easily create an organization trial. We can have  a hierarchy of managers. Now let&amp;#39;s  only the name of the employee and the manager. So,  right here, since every column in the employees table is  repeated twice we need to prefix each column with a table name.  For example, from the employee&amp;#39;s table you want to get employee ID, as well as the first  name, and then for the managers table, we want to select  the first name as well. So every column  should be prefixed with a table name or more accurately the alias because all these columns exist in two tables, right?  Let&amp;#39;s go ahead and execute this query, so,  this is what we get, employee id, and here&amp;#39;s the managers  first name. We can improve this query by giving an alias to this column. Because it doesn&amp;#39;t make sense to have two first name columns.  So, let&amp;#39;s give an alias to the third column,  manager. Now, let&amp;#39;s execute it one more time.  And, here&amp;#39;s the end result. So, we have the empty ID first name, and object. So,  joining a table with itself is pretty much the same as joining a table with another table. The only difference is that we have to use  different alias&amp;#39;s. And we have to prefix each column with an alias.  This is what we call a self join. Next, I&amp;#39;m going to show you how to join more then two tables.  In this tutorial,  I&amp;#39;m going to show you how to join more then two tables in writing a  query. For example, 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. So the name of the  status&amp;#39;s are not stored in this table, they are somewhere else in the  order status&amp;#39;s table. Let&amp;#39;s have a quick look here.  Our orders can be either processed, shipped or delivered. And these are the identifiers for each of these status&amp;#39;s. Now back to our orders table. In the status column you store status id. So now we should write a query to join the orders table, be two tables. The customers table and orders  status&amp;#39;s table. The result of this query is going to look like this.  So for each order we see the order id, the date,  the first and last name of the customer, and finally the status of the order.  This is a beautiful report we can create for our users. So 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&amp;#39;s going to be the alias,  next we need to join this with the customers table.  On, that customer id should  be equal to see the customer id. Nothing  new so far. Exactly like before. Now here we can write another joint keyword. To join the others keyword  order status&amp;#39;s table. So we type out order status&amp;#39;s.  And also give it an alias, OS,  what is our join condition? Well, back in the  orders table, here we have the status column. So the  value in this column should be equal to the order status ID,  column in order status&amp;#39;s table. Right? So back to the query, so orders table.status should be equal to order status&amp;#39;s.order status ID. Make sure to get the name right, otherwise you&amp;#39;re going to get an error So this is how we can join three tables.  Now in the real world as you work on more complex problems, you&amp;#39;ll end up joining  even ten tables. So this is not uncommon in the SQL world.  Now let&amp;#39;s go ahead and execute 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 so complex and hard to extract information.  from. So, let&amp;#39;s explicitly select  a few columns here. From the orders table, you want to select  the order iD column, as well as the order date. Then, from the customers table, we want to select  the first name, and last name. And finally form the order status&amp;#39;s, we select the name  column. So we can give this an alias like status that&amp;#39;s better. Let&amp;#39;s execute the query one more time.  So, here&amp;#39;s the end result, we have order ID,  order date, the name of the customer, followed by the status of the order.   Alright, for exercise, take a look at the SQL invoicing database. Here we have this table, payments,  and these are the payments that each client has made towards either invoice  Let&amp;#39;s take a look at the data, so we have these columns,  like client id, that identifies the client, so we can join this table  with the client&amp;#39;s table to see the name of the clients. Next we  have invoice ID, we also have date,  payment method. So similarly we can join this table with the payment method  table here, let&amp;#39;s have a look at the data in this table, these are the  payment methods, credit card, cash, PayPal, wire transfer. So,  back to the payments table, I want you to write a query and join this table  with the payment methods table as well as the client&amp;#39;s table.  Produce a report that shows the payments, with more details, such as the name of the client,  and the payment method.  Alright, first we need to use, the SQL invoicing database, now we can  select everything from the payments table which we call e, next we need to join this with the client&amp;#39;s table which we call c, on p.clientid  should eb equal to c.client id. Let me double  check the column name to make sure I got it right, so back to the payments table the column is called client item. You also have a column called  payment method, that we should join to the payment  method ID column of the payment methods table.  So, back to the query, once again we use a joint  statement here, join with payment methods, we give it an alias  pm, on p. payment _method should be equal to pm.  payment method id. Make sure to type it out correctly  otherwise you&amp;#39;re going to get an error. So let&amp;#39;s go ahead and execute the query  up to this point, finally let&amp;#39;s hand pick  the columns that make the most sense. So,  from the payments table, let&amp;#39;s select the date, followed by invoice id,  what else do we have here. So we have client id, invoice  id, date, and now the payment method, I&amp;#39;m going to pick the amount  column from here as well, so back to the query, p  .amount. Now we need to add information about the client.  Let&amp;#39;s take a look at this table, clients,  so here we have columns like name, address,  city and so on. All we need here is the name column. So back to the query,  of the client&amp;#39;s table let&amp;#39;s select the name column and  finally form the payment method table, let&amp;#39;s select, what is that column called it&amp;#39;s called name. So back to the query pm.name. So here&amp;#39;s the end result.  Now we can put this column in any order that we want, it doesn&amp;#39;t really matter,  let&amp;#39;s execute the query, and make sure everything works.  So, on this date, on this invoice,  we have a payment for this amount by this client using a  credit card.  Ina let he examples you have seen so far,  we use a single column to uniquely identify the rows  in a given table. For example, for example, in the customers table,  we have this customer ID column which uniquely identifies  the rows in this table. But there are times where we cannot use a single  column to uniquely identify columns in a given table. For example, look at the order items table. In this table, we have columns like order id, product id, and so on. 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. So we cannot use this column on it&amp;#39;s own to uniquely identify each record.  The same is true for the product id. The values for this column  are also duplicated. So in this table we use the combination of the values in both these columns to uniquely identify  each oder item. 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 unit price. So if we use the combination of the values in both these values,  we can uniquely identify each order item, in other words, we don&amp;#39;t have  two records for order ID 2, and product ID 1,  we only have a single record for that item,  now let&amp;#39;s open this table in the design mode. So over here we  find this middle icon that looks like a tool. Note that this yellow  key that represents the primary key exists on both these columns.  This is what we call a composite primary key. The composite primary key  contains more then 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, that we use to keep notes in each order item. Let&amp;#39;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 order ID and product ID.  you learn that the combination of these two columns uniquely represents  and order ID. So here for order number 2,  for product number 1, we have two notes. Now let me show you how join this table with the order items table. So, back to our  query, you can see that I have already selected the SQL store database, so I&amp;#39;m not going to type out a U statement. Alright, let&amp;#39;s select  like everything from the order  items table. Give it an alias, now we need to join this with order item notes, also  we give it an alias. How are we going to join these tables?  Based on two columns, back to the order items table  these are the columns that we need to use in our joint condition.  So, in the order items table we have this  order ID column, these should be equal to the same column in order item notes table. So,  in.order id. But this is not enough,  we should also join these tables based on the product ID number.  So, we type out and, and then type out the  second condition, so order items. product ID should be equal to 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&amp;#39;m going to talk about the implicit joint syntax in MySQL.  So here we have a basic inner join, we&amp;#39;re selecting everything from the orders table,  joining it with customers table on, orders.customer id. Equal to customers.customerid. 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 type out multiple 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&amp;#39;m going to copy this from here,  type out the where clause, and paste the condition. These two queries,  are equivalent. What we have here is called implicit  joint syntax. Now even though MySQL supports the syntax  it&amp;#39;s something that I suggest you not to use, because if you accidentally forget  to type out the where clause, you will get a cross join. Let me show you what I mean. So first I&amp;#39;m going to delete the first query.  And execute this  so we get ten records because we have  10 orders in this database. So far so good. What happens if you accidentally type out the where clause. Instead of 10 records we&amp;#39;re going to get  probably 100 records. Because every record in the order table, is now joined with every record in the  customers table. This is what we call a cross turn. Now later in this section I&amp;#39;m going to talk about cross joins in more detail, but what I want to talk about  in this tutorial, is that it&amp;#39;s better to use an explicit  joint syntax, so, we use, join  because this syntax forces you to type out the joint condition, if  you simply join orders with customers, without typing  the join condition, you&amp;#39;re going to get a syntax error. So to recap,  be aware of the implicit join syntax, but write all of  your joints using the explicit syntax.   Earlier in this section I told you that in SQL we have two types of forms.  Inner joins and outer joins. And so far you have  only seen examples of inner joins. And I also told you that this inner keyword is  optional, so whenever you type out a join, you&amp;#39;re using an inner join.  In this tutorial, we&amp;#39;re going to look at outer joins and the problems they solve. So, let&amp;#39;s start by writing a query that uses an  inner join, and then we&amp;#39;ll convert hat inner join and outer join.  So, select everything from the customers table  join it in the orders table,  on c.customer id should be equal to  o.customer ID. Pretty basic  right? Now for clarity, let&amp;#39;s pick a few columns from these two tables .So for the customers table I want to pick customer  id. And first name,  and from the orders table, I want to pick order id.  Now finally, let&amp;#39;s sort the results so we can clearly see what  we get. So order by c.customer  id. Let&amp;#39;s execute that query and see what we get.  So, here&amp;#39; the result. For customer number 2 called  inis or inis whatever, you have 2 orders, order 4,  and order 7. Similarly for customer number 5, we have  2 orders and so on. Now there is something missing in 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 customers table, you can see that  you have other customers like customer number 10, customers number 3, and so on.  Currently we don&amp;#39;t have any orders for these customers, and that&amp;#39;s the reason  we don&amp;#39;t see them in this results set. But what if you want to see all the  customers whether they have an order or not? That&amp;#39;s when we use an  outer join. Let me show you how that works. So back to our query  the reason we only saw customers who have an order was because  of this join condition, When joining these two tables, you are only  returning records that match his condition. So for a given customer, if we do have an order, that record is returned. But as you saw a second ago, some customers don&amp;#39;t have  an order. So for those customers this condition is not  valid. And that is the reason 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 the records from the left table, in this case customers are  returned whether this condition is true or not. So we  get all the customers, and if they do have an order, we&amp;#39;ll see the order id  as well. Let&amp;#39;s execute this query and see what we get. So,  there you go. Customer number 1,  doesn&amp;#39;t have an order and that&amp;#39;s why we get null in this cell.  Customer number 2 has two orders, 4, and 7, customer  number 3 also doesn&amp;#39;t have an order so we get null for order id.  This is the result of a left join. So back to our query  when we use a left join, all the records from the left  table are returned whether this condition is true or not.  Now what if we use a right join. In this  case, all the records from the orders table are returned whether this condition is  true or not. Let&amp;#39;s execute this query and see what we get.  So, we get he 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&amp;#39;t see all the customers  we see all the orders. Now if you want to use a right join, and  still see all the customers, you need to swap the order of these tables. So,  we put the orders table first that&amp;#39;s going to be our left table.  And then, we put the customers on the right side,  so now with this query we&amp;#39;ll return all the records from the right table  which is the customers table. We execute this we get  all the customers whether they have an order or not.  Beautiful. Now one last thing before we finish this tutorial,  I&amp;#39;ve seen developers use the outer keyword here. So either  right outer join or left outer join. But technically  the outer keyword is optional just like the inner keyword, so you don&amp;#39;t have to type it out.  So I&amp;#39;m going to remove this to make this code shorter and easier to  understand. So to recap, if you have the join keyword directly, and  if you do a left or right join you&amp;#39;re doing a  inner outer join.  Here&amp;#39;s 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 that I picked from the  order items table, so here we need to join the products table.  With order items table. So 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&amp;#39;m doing an outer join, so,  product number 7, has never been ordered, but still exists in the result.  Is it null, or the quantity? So go ahead and write an outer  join, or use this result.  Alright, first we select  everything from the products table and then  to a left join with the order items table.  Our join condition is p.product  id equals oi.product id.  So because we&amp;#39;ll get all the products  in the products table whether this condition is true or not. If you have never  been ordered, you still see them in the result. Now, let&amp;#39;s  pick a few columns for clarity. So p.productid  p.name and oi. quantity. That&amp;#39;s it. Let&amp;#39;s execute the query. We get the same result as before. So all the products are here.  And product number 7 has never been ordered so we see  null for the quantity.  Similar  to inner joins, we can use outer joins between multiple tables. Let me show you. So here&amp;#39;s the query that we wrote in the last tutorial, you are doing a  left join between customers and orders tables.  So when we execute this query, you get all the customer  whether they have an order or not. If you have an order you 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. So now let&amp;#39;s join  orders table with the shippers table to display the name of the shipper in the result.  So back to our query, after this left join,  let&amp;#39;s write another join, here I&amp;#39;m doing an inner join.  So let&amp;#39;s inner join the orders table with the shippers table.  Join shippers, we call it sh, on  what is the join condition. Well, oh.shipper  id, should be equal to sh.shipper  id. Alright? So in this query we have  a left outer join and inner join. Let&amp;#39;s see  what we get, alright, we only  see 5 records. But you have more orders. So here you have the same problem  we have before. Some of our orders don&amp;#39;t have a shipper, and that is why  they are not returned here. In other words, this join condition is not true, for some of our orders. So back to the orders table,  a an example, this first order doesn&amp;#39;t have a shipper shipper id is null, and that is why it is not returned with a query result.  So to solve this problem, you should use a left join, you want to make sure that all orders are returned whether they have a shipper or not.  So back to the query, you need to replace this inner join  with the left join, so let&amp;#39;s execute the query and see what happens.  Now we should have quite a few more orders, there you go, now to make this example more interesting, I&amp;#39;m going to add the ship -per name here. So back to our select boss, let&amp;#39;s add a new column, shipper.name.  Now we can give it an alias like shipper let&amp;#39;s execute the query and here&amp;#39;s the result. So we get all the customers whether they have an order or  not. And for those who do have an order, we get all the orders whether we have a shipper 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 or the right join, you just have to swap the order of the tables. However, as a best practice of what to avoid using right joins, because when you&amp;#39;re joining multiple tables and you have  left and right and inner join, things get really complex. So,  someone else reading your code, will have difficulty visualizing how you&amp;#39;re joining these tables.  As an example, if you have a right join here and then a left join  after, it will be harder to visualize how these tables are getting joined.  So, 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, the first name of the customer the shipper and we can see that some of our orders are not shipped yet.  So here we have null, and here finally, we have the status.  So go ahead, spend 2-3 minutes on this exercise, when you&amp;#39;re done continue  watching.  Alright, let&amp;#39;s select everything  from the orders table, now you should join this with customers, on, hold.customerid.  Should be equal to c.customer id. Here I&amp;#39;m using an inner join, because every order does have a customer. So,  this condition is always valid, it doesn&amp;#39;t matter if we use a left join or a  inner join here, now, okay? Now before going any further let&amp;#39;s  take our columns. So from the orders table, I&amp;#39;m going to pick order id.  Followed by order date. And then,  customer.firstname.  Which we can optionally rename to customer.  Alright, next we need to select the shipper. So, we join the result with the shippers table,  on order.shipperid equal  to shipper.shipperid. However, if we use an inner join here, because some of our orders don&amp;#39;t have a shipper, we are only  going to see the orders that have been shipped. Let me show you. So,  for clarity, I&amp;#39;m going to add the shippers name here, so shipper .name as shipper. Let&amp;#39;s execute the query  there you go, we only see the orders that have been shipped.  But we want to see all the orders, right? So, we need to  change the second joint to a left join.  So all orders are returned whether they have a shipper or not.  Let&amp;#39;s execute the query one more time,  there you go. Now we see all the orders from number 1-10.  Beautiful. Finally, we need to add the status comment here.  So, we need to do another join here, join with order status&amp;#39;s, which we aggregate  as os, on oh.status equals  to os.orderstatusid.  We can see that I have designed our database such that sometimes our column names  are exactly identical, but in other cases they don&amp;#39;t match.  So in order table we call this column, status, as opposed to  order status id, and this is deliberate because a lot of  real databases are like that. Alright, now let&amp;#39;s add the status name here, so order, status.name as status. Execute the query,  and we can see all the orders here,  for each order we have the data, the customer, the supper and the status.   Earlier, we talked about self joins in SQL. So here in the SQLhr database, we have this employees table, we rote a query to get  all the employees and their manager. So here we have this column, reports  to that specifies the manager for each employee. So let&amp;#39;s go back and rewrite this query to get all the employees and their manager.  Back to our query editor window, first let&amp;#39;s use a SQL hr database. Then select everything  from the employees table, we give it an alias and then join it with itself.  So this is what we call a self join. Now we&amp;#39;re going to use a different alias  like m for managers. Now, what is the joint condition e.reports to, should be equal to  m.employee ID, right?  Now for clarity, let&amp;#39;s pick only three columns, so either the id  employee.first name.  And m.firstname which we rename to  manager. Alright, let&amp;#39;s execute this query and see what we get.  So, here&amp;#39;s the result  as you can see all these employees have the same manager.  However, there is something missing here. We don&amp;#39;t have a record for this person, this  manager himself. So what is missing is a record where  we can see the employee id for this person, their name and their  manager which should be null because this person is the CEO or the  head of the company. But why is this happening? The reason is,  our inner join, because this condition we have here will only  return people who have manager. We can solve this problem by using a  left join. So, we do a left join, because we want to get every employee in this table whether they have a manager or not.  Okay, now let&amp;#39;s execute the query one more time.  There you go. Now we have a record for this person  the manager, as you can see this person does not have a manger, that&amp;#39;s why we have null here.  Back to our SQL store database  here we have a simple query that joins the orders table,  with a customers table. And here&amp;#39;s our join condition. We have several examples  of this before. Now as our queries get more complex  these joint conditions get in the way, they make our queries hard to read, but the good news  is that in MySQL, we have a powerful feature for simplifying  these queries. If the column name is exactly the same  across these two tables, we can replace the on clause.  With a using clause which is simpler and shorter.  So I&amp;#39;m going to comment out this line and instead type out using  in parenthesis, we type out the column name, that is custom.  customer id. What we have in line 7 is exactly identical to what we have  on line 6. That is shorter and easier to read. So,  let me delete this line. We can add another join statement here to join the orders for the shippers here, so join with  shippers using  shipper id. In both these tables we have a column with the exact  same name. Alright, now let&amp;#39;s execute this query  this is what we get, you have the order id followed by the  first name of the customer. Let&amp;#39;s add a new column here,  so, I&amp;#39;m going to add sh  .name that is the name of the shipper,  now obviously because some of our orders are not shipped, we have to replace this  inner join to the left join. So we can use the using keyword with both inner and outer joins. Let&amp;#39;s execute  the query one more time. There you go.  Now, we have the name of the shipper next to each order, beautiful,  however, we cannot use this technique to join the result with the  order status&amp;#39;s table. Because in the orders table we have  this column called status, but in order status&amp;#39;s table, this column has a different  name. It&amp;#39;s order status id. Let me show you. So,  order status&amp;#39;s. columns, there you go, order status id.  So the using keyword only works if the column name is  exactly the same across different tables. Now what if we have multiple  columns in our join condition. For example, earlier we talked about  tis 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 order item notes table. In our join  join condition, we should compare both these columns with their corresponding  columns in the order item notes table. So let&amp;#39;s quickly  write that query and then simplify it with the using keyword.  So, select everything from  order items, now, join it with order  item notes on  so here we need to compare oi.order  id with yn.orderid, and  oi.productid= to oin.productid. This join  condition is kind of messy, it&amp;#39;s hard to read this query. Now let&amp;#39;s simplify this query with the using keyword. So we type out using. In parenthesis  we add both columns and then separate them using a comma.  So, ordered and product  id. Isn&amp;#39;t that better?  Now, for our exercise, back to our SQL invoicing database write a query to select the payment  from the payments table and produce something like this. So in this table we have the date  the client, the amount and the payment method, we can see.  On what date who has paid how much using what payment method.   Alright, I&amp;#39;m going to use the SQL invoicing database, and then select everything from the  payments table, join it with the clients table.  Using client id because in  both these tables, you have the client id table. Next we need to join this with  payment methods, however, they  column name between these two tables is different, so in the payment  table we have a column called payment method. But in payment  methods table, our column is called payment method ID. So here we  cannot use the using keyword, and we&amp;#39;ll have to use the on clause. So on p.payment  underline method equals pm.  paymentmethodid. Now let&amp;#39;s pick our columns.  So, payment.date client.name.  And we rename this as client, next we pick m out, and finally, the payment method,  so, let&amp;#39;s rename that to payment_method  and execute 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 tales, it&amp;#39;s called a natural join, and it&amp;#39;s easier to code, but it&amp;#39;s not something to recommend, because sometimes it produces unexpected results. But let me quickly show you how it works in case you see it somewhere so at least you&amp;#39;re familiar with it. So back to the previous example,  let&amp;#39;s select everything from the orders table, that we should do a natural join with the customer&amp;#39;s table.  Now with this natural join, we don&amp;#39;t exactly specify the column name.  So the database engine will look at these two tables and it will join  them based on the common columns. The columns don&amp;#39;t have the same  name. And that is the reason this query is shorted right.  So, for clarity, let&amp;#39;s pick a couple of columns here, o.order  id and c. let&amp;#39;s say first name.  Let&amp;#39;s execute the query, there you go.  so we see all the orders and the customers replace them.  So natural joins are really easy to code but they can  be a little bit dangerous, because we&amp;#39;re letting the database engine  guess the join, you don&amp;#39;t have control over it. For this very reason,  natural joins can produce unexpected results, and that&amp;#39;s why I discourage you to use them.  In this tutorial,  were going to look at cross joins in SQL. We use cross joins to mine or join every record from the first table, with every record,  in a second table. Here is an example, let&amp;#39;s select  everything from the customers table to  now here we do a cross join with the products table.  So every record in the customers table, will be combined with  every record in the products table. And that is why we don&amp;#39;t have  a condition here. Okay? So this is what we call a cross join  now for clarity let&amp;#39;s pick a couple of columns like see that first name we rename it as customer and then product.name which we rename to  product. Also, let&amp;#39;s sort the result  by customer. first name.  Now, let&amp;#39;s execute the query, here&amp;#39;s the result of the cross join,  so first we have amber as the customer, and here are all combinations of amber with different products.  Next we have Barbara or whatever it is, and again we have the combination of this custom  with all the products. Now in this particular example, it doesn&amp;#39;t really make sense to use a cross join, a real example for using  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 combine all the sizes with all the  colors. That is when you use a cross join.  Wha we have here is called the explicit syntax for cross join, you also have the implicit syntax which looks like this. Instead of typing out the cross join, you type out multiple tables in the  from clause. So customers and orders.  Both these queries produce the same result. But I personally prefer to use  the explicit syntax because it&amp;#39;s more clear.   And here&amp;#39;s a simple exercise for you. Do a cross join between shippers and  products. First do it using the implicit syntax,  and then using the explicit syntax. It&amp;#39;s pretty straight forward, I just want you to  get your hands dirty in the code and get used to this syntax.   Alright, first I&amp;#39;m going to use the explicit syntax, and then I&amp;#39;m going to place the syntax. So let&amp;#39;s start by selecting everything from  2 tables, shippers and products,  now for clarity I&amp;#39;m going to pick two columns, shipper. name which we rename to the shipper. And product.name which we  order everything by  shipper Let&amp;#39;s execute the query, this is what we get. So the combination of all shippers and all products beautiful,  now let&amp;#39;s use the  explicit syntax, so we select everything from the base table,  in this case shippers and then do a cross join with products.  That produces the same exact result.   We covered everything about joins, you learned  that with joins we can combine columns with multiple tables, but  in SQL we can also combine rows with multiple tables, and this is extremely powerful. And let me show you how this works. First we have  a quick look at our orders 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 the other orders were placed in previous years. Now let&amp;#39;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 is  going to be active, and if the order is placed in previous years, you want to label it  as archives. So, let&amp;#39;s change our  query and change our condition here. First you want to get all the orders in the current year. So where 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 hard  coded 2019. So if you execute this query next year, we are not  going to get the right result. But don&amp;#39;t worry about this for now. Later in the course I will show you how to  get the orders in the current year, without hard coding a date here.  So, let&amp;#39;s execute this query, now we get  only one order. Let&amp;#39;s hand pick a couple of columns here.  So, order id,  and order date. And also I want to add a string,  literal here, like active. Right?  Let&amp;#39;s execute this query, this is what we get. We get 3  columns, order id, order date, and active, and in this column,  argument we have this string value. Active. Now let&amp;#39;s rename this column to status, alright? And execute the query, that is better,  now we want to write another query similar to this that will return the order  in the previous year, but with a different label, archive.  So, to save time, I&amp;#39;m going to copy this few lines and paste them right after our first select statement. Now note that here we have a syntax error, because we didn&amp;#39;t  terminate the first select statement with a semi colon, but don&amp;#39;t worry about it, we&amp;#39;re going to get back to this in a second so for the second  query, we want to return a different a different label, archive, and we want  to change our condition to less then  2019. Now, select only these few lines.  And execute this query, either by clicking on this icon here,  or using the keyword shortcut you learned earlier in the course.  There you go, here are all the  orders from the previous year, with the label archive.  This query returns 9 records. The previous query returned 1 record.  now using the union operator we can combine data from these two queries, so, in between  our select statement we type out union  now let&amp;#39;s execute the query one more time, so here&amp;#39;s our first  order in the current year that is active and below that we have  the orders in the previous years. So using the union operator we can combine records for multiple queries. Now in this example, both our queries are against the same table, but we can also have queries against different  tables and then combine the result into one result set.  Let me show you another example. So I&amp;#39;m going to delete everything here. Let&amp;#39;s select the first name,  from the customers table, and then we can union that with select  the name from the shippers table.  Let&amp;#39;s execute the query&amp;#39;s in one result set we can see all the customers and the shippers. Now as far as  I know, there is no real world care for this particular query. But what I Want to point out is that with union we can combine results from multiple  query&amp;#39;s. These query&amp;#39;s can be against the same table or different  tables. In your database, you can have a table like archive orders, and another table like order  and then you could combine all the archive and active orders into one  result set. Just remember, that the number of columns that the query  returns should be equal, otherwise you&amp;#39;re going to get an error. For example,  let&amp;#39;s select the first name and last name from customers  and then union that with the name of  shippers. When we execute 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&amp;#39;t know how to combine these records.  And one last thing before we finish 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 first name, and that&amp;#39;s why this column is called  first name. If you change the order of these queries,  and move this union up here, now let&amp;#39;s run this query  as you can see our column is called name.  So whatever we have in this first query is used to determine the name of  columns. Here we can also rename the column to full name.  There you go.   Here&amp;#39;s your exercise for this tutorial. Write a query for this report.  So here we have four columns, customer ID, points, and  title. Now as you know we don&amp;#39;t have this column in the customers table,  so we have calculated the values of this column, based on  the points each customer had. If they have less then 2,000 points,  their type is bronze. If they have between 2,000 and  3,000 points they are silver customers, and if they have more then  3,000 points, they are gold customers. Also note that here we have sort  the result by the first name. So go ahead and spend two minutes to write this query.  Alright, first let&amp;#39;s get the bronze customers,  so select everything from customers where  points is less then 2,000.  Now here we want to pick 3 columns, customer id,  first name, end points. And finally we  add a new column to a string literal, bronze  let&amp;#39;s run this query and see what we get. So these are all the bronze  customers, but the name of this column in bronze, we don&amp;#39;t want that. So, let&amp;#39;s rename this to type. Now this is off the screen so  I&amp;#39;m going to break this up into multiple lines  that makers our query bigger and easier to read.  There you go, let&amp;#39;s run the query one more time, now the column is called type.  Beautiful. Now we should do union  and repeat this query, but extract the silver customers.  So I&amp;#39;m going to paste this query here and then make a  couple of changes here, I&amp;#39;m going to replace bronze with silver,  and change the condition to between 2,000  and 3,000 let&amp;#39;s run our query, see what we get  so, we have all the bronze customers first,  followed by all the silver customers. So the order of these records is based  on our queries. In our first query we got the bronze customers, which is why  they are listed first. But this is not what we want. You want  to order the result by the first name of our customers. So,  let&amp;#39;s apply an order by at the end so order by first name. Now there is one more piece remaining.  You should do a union one more time and write a query to get  the gold customers. SO I&amp;#39;m going to select these few lines.  And paste them here.  Now let&amp;#39;s change silver to gold,  and the condition to (?) greater then 3000.  And finally we do an imported by. Let&amp;#39;s run the query one more time.  And here&amp;#39;s the end result, our customers  are sorted by first name, bronze, silver and gold customers.  In this section I&amp;#39;m going to teach you how to insert, update, and delete data. Before we get started  let&amp;#39;s have a closer look at our customers table. So click on  this middle icon to open this table in the design mode.  What you see here might look a little bit intimidating at first, but trust me  it&amp;#39;s so easy, and in this tutorial, I&amp;#39;m going to explain exactly what we have in these columns. So on the left side, you can see the column  next to that you can see the data type for each column, so our customer id column  can only accept integer value. Integers are whole numbers like 1234 and so on. You don&amp;#39;t have decimal points, etc.  First thing is a var chart which is short for variable  character. And in parenthesis, you can see 50, that basically  means in this column you can have a maximum  of characters now if the name of a customer is only 5 characters long the only  store those 5 characters. So even though the max  length for this column is 50, you&amp;#39;re not going to waste the space if this customer  name is less then 50 characters. That is why here we have  4char which is short for variable. In contrast we have  another data type that is character. If you had character 50 here,  and the name of the customer was only 5 characters long, MySQL  will insert additional 45 spaces to fill this column.  So this is a waste of space. So as a best practice,  Most oft he type we use var char to store strings  or textural values. Now here on the right side we have this column  pk, which is short for primary key. So customer  id is marked as the primary key, and that is why we have this yellow key here,  so the values in this column uniquely identify each  customer. Next to that we have nn which is short for not null and that determines if this column can except null values or not.  In this case, every customer record, must have the customer id.  First name, last name, as well as these other attributes.  But birthdays and phone are optional. So in these columns we  can have null values, Now we have another calling here, ai,  which is short for auto increment and this is often used  with primary key columns, so every time we insert a new  record in this table, we let MySQL or our database engine insert  a value in this column, so essentially it gets the customer id  for the last row, and it will increment it by 1, by the time you serve a new record. So if you look at the data you can  see that currently we only have 10 customers here. So if you have a new customer here,  MySQL will sign 11 to the new customer, okay? And finally here we have another column that specifies  the default value for each column. For example, for birthday  and phone columns, the default values or null,  So if you don&amp;#39;t supply a value MySQL will supply the null values  for these columns. Similarly we have another null value,  for the points column, so if we don&amp;#39;t supply the points for a customer, MySQL will use 0, now we have a few other columns here which are not important  at this stage, you will learn about them later in this course. So now  you understand the attribute of each column, let&amp;#39;s go ahead  and insert data into this table.  In this tutorial,  you&amp;#39;re going to learn how to insert a row into a table. For that we&amp;#39;re going to use  the insert into statement. Where are we going to  insert this row? Into the customers table, so we type out  the name of the table here followed by the values clause.  And here in parenthesis we supply the values for every  column in this table, so back to our table definition,  these are all the columns, first we need to supply 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 don&amp;#39;t supply a value,  MySQL will generate a unique value for us.  So we can go back to our statement and either assign  an explicit value or use default to let MySQL  take care of generating this value. This is the preferred approach. Because if we use this value  an explicit value like 200 it is possible that you might have  another customer with the same id. So when you execute the statement, you&amp;#39;re going to get an error.  Because you cannot have duplicate values in this column. Every value should be unique, so here we&amp;#39;re going to use the default keyword to let you generate a unique value for the customer id, and after that we need to  supply a value or the first name and last name columns.  So, let&amp;#39;s say Jon Smith.  Note that I have enclosed these values with quotes, because as I have told you,  string and for in SQL, we should always enclose string and values with quotes. Either single or double quotes, okay?  Now, what else? Back to our customer table,  after the last name you have birthdate, however,  as we can see, this column is optional because this check box is not checked. So  here we can use null or an explicit value. Null means the absence  of a value. So back t our statement, you can type out  the birthdate, like 1990 January 1st or  you can use the null keyword to leave out this value.  Now in this demo, I&amp;#39;m going to use a value date,  now to make this code cleaner and more readable, I&amp;#39;m going to break it up into  multiple lines. That&amp;#39;s better,  now back to our table, next we have phone and phone is  also optional, because this checkboxes not checked, and null is the  default value for this column. So here we can explicitly pass  null, or use the default keyword and then put null into this  column. It&amp;#39;s exactly the same. So, back to our  statement, you can pass null or default.  Both these keywords will have the same result. In this case I&amp;#39;m going to use the null  keyword. Alright, let&amp;#39;s have one more look at our table,  next we have 4 more columns that are  required, so address, city, state, and points. And note that points  has the default value of 0, so we can either use an explicit value like  total value like 200, or use the default keyword and let MySQL generate 0. So, back to our statement, let&amp;#39;s type out  an address, it doesn&amp;#39;t really matter.  Followed by a city, and a state, let&amp;#39;s say california,  and finally points, again we use explicit value  or default, so this is how we can insert a row into  a table. However, in this example, you&amp;#39;re only  supplying values for first name, last name, birthdate and  these address fields. So you&amp;#39;re leaving out phone number  the customer id and the points. So there&amp;#39;s another way  to write the statement. Let me show you. So after the table name you can  optionally supply the stuff columns that you want  to insert values into. In this case first name,  last name, birthdate  and once again I&amp;#39;m going to break up this statement into multiple lines  So 3 more columns,  address, city, and state,  so these are the 6 columns that we&amp;#39;re going to supply values for. With this change, we don&amp;#39;t have to use these default  or null values, we only supply values for these columns.  So I&amp;#39;m going to remove default from here, and null, and finally  this last default keyword.  So, the 6 values that you supplied here are used for the 6th column.  Now with this change, you can also reorder the columns.  We don&amp;#39;t have to list them in the same order, they were defined  in the customers table, for example you can put the last name first,  and then obviously we should also swap the order of these values,  so we can list them in any orders. Now we can execute  the statement, now if you look at the output window  down on the bottom you should see the statement followed by one rows  unfortunately I cannot resize this window to show you this message, but if you look down below you can see that one word was affected, which basically means  one record was inserted into this table. Now,  let&amp;#39;s look at the data in the customers table  so the last row is the one that we inserted, we can see that the  MySQL automatically generated the value 11, this is the effect  of auto increment attribute. So it takes the value of the last row,  and incrememnts it by 1. So here we have the first name. Last name,  birthdate, you didn&amp;#39;t supply a value for the phone attribute, so,  that&amp;#39;s why we have null here, we also have address, city  state, and the default value of 0 for the points.   In this tutorial, you&amp;#39;re going to learn how to  insert multiple rows in one go. For this we&amp;#39;re going to use the shippers  table, let&amp;#39;s have a quick look at the table definition  so here we have 2 columns, shipper id and name. Shipper id is  primary key, it&amp;#39;s not nullable, and it&amp;#39;s an auto incremented  column. So we&amp;#39;re going to let MySQL generate values for this column. Easy.  You only need to supply a value for the name column. So back to our  query editor window, we type out insert  into shippers in parenthesis we specify the name of the column  we want to insert values into, in this case name  followed by the values clause. Here we add a pair of  parenthesis with a value like shipper one. Now insert multiple rows all you have to do is insert a comma  followed by another pair of parenthesis. So,  shipper 2, one more time, comma, shipper 3,  this is how you can insert multiple rows in 1 row,  now let&amp;#39;s execute this statement, alright,  and then inspect the data in the shippers data.  There you go. So initially we only had 5 shippers and here are the 3 new shippers that we inserted. Note that MySQL  automatically generated the values for the shipper id column.  So we have 67 and 8.  Alright, here&amp;#39;s your exercise for this tutorial. Write a statement to insert three rows  in the products table, it&amp;#39;s really easy you can knock it out in a minute,   So here&amp;#39;s our products table, we only have 4 columns,  we&amp;#39;re going to leave out the first column because it&amp;#39;s an auto increments  column  so we only have to supply values for name, quality, unit price.  Back to our query editor window, let&amp;#39;s insert  into products the columns are  name, quanitty in stock, and unit  price. And the values are going to be let&amp;#39;s say product 1,  stock is going to be 10 and the unit price is going to be 1,  .95. Now I&amp;#39;m going to select these values. copy, paste it, like this,  change the values accordingly, and  finally the last row for the 3  there you go, let&amp;#39;s execute this statement, alright  and then verify the result so in the products table, now we should have 3 new records,  now the id&amp;#39;s you see here are 15, 16, and 17.  Because before recording this video I actually inserted a few records in this table and then  deleted them. So I had product ID&amp;#39;s 11, 12 13, and 14. And even though they&amp;#39;re actually deleted from  this table, MySQL remembers their id&amp;#39;s so instead of incrementing  10 x 11, it incremented 14 which is the last record  from before. And that&amp;#39;s why you generated 15. On your computer,  the items are going to be 11, 12, 13.   So far you have only learned how to insert data into a singular table.  In this tutorial, I&amp;#39;m going to show you how to insert data into multiple tables. Here&amp;#39;s  a really 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 has placed the order, we know the date of the order,  you know the status, comments, as well as 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 order these items  for. We have the product id, so we know what product  order, in what quantity, and in what price. So an actual  order can have 1 or more order items.  This is what we call a parent child relationship. So this relationship  the orders table is the parent, and the order items table is the  child. So one row in the orders table can have 1 or more children  inside the order items table. Now in this tutorial, I&amp;#39;m going to show you  how to insert an order and all it&amp;#39;s items. So you will learn  how to insert data into multiple cables. alright, back  to our query editor window. First we need to insert the order,  so insert, into, let me close  the navigator panel, we want to insert a record into the order  table, now what columns do we have here? So we have these columns but only the first four are the  and actually the first one, we don&amp;#39;t want to worry about, because that&amp;#39;s an auto increment column.  So we only want to supply values for customer id, order date,  and status. So back to our query, specifying those columns here, customer id, order date,  and status, now let&amp;#39;s supply the value.  In the customer id column, we need to add a  valid customer id. So let&amp;#39;s have a quick look at our customers  table, there you go. In this table,  we have 11 records. So these are the valid customer id&amp;#39;s.  Now back to our query, let&amp;#39;s use 1 for customer query  and then 2019, January the 2nd  for the order date, and one for the order status, once again  in this column, we need to insert a valid order status  id. If you don&amp;#39;t supply a valid id, MySQL is going to get ahead of us. So we insert an order here,  the items, now back to our order items table,  in this table, we have this order id column, so here&amp;#39;s the  as soon as we insert an order, MySQL is going to generate  an id for our new order now we need to access that id in  order to insert the items in this table. How can we do that?  Well, 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 ourself. Just like a function is a feature  in your tv. Every tv comes with a bunch of functions like  power on, power off, change the volume, change the channel so on.  So MySQL and other database engines come with a  bunch of built in functions  that we can use in our programs. One of these funcitons is last insert id. We can call or  execute this function, by adding parenthesis here, and this will return the ID  that MySQL generates when we insert a new row.  So, before going any further, let&amp;#39;s just select these,  and make sure you get the right result. Now we have a syntax error here because we didn&amp;#39;t terminate  the first statement with a semi colon, alright, now let&amp;#39;s execute this query,  alright, so the id of the new order is  12. Let&amp;#39;s verify that. So back to the orders  table let&amp;#39;s look at the data. On my machine I have  12 records here, I actually created 1 just before recording this video, so on your  machine you&amp;#39;re going to have 11 orders.  Now 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, we&amp;#39;re going to write another insert statement,  insert into order items, let&amp;#39;s have  another look at the columns in this table. So we have 4 columns  on all of them are required, so there&amp;#39;s really no need to specify the column name  in our insert statement, you simply supply values or order id,  product id, quantity and unit price. So, here  in the values clause, we add parenthesis,  what is our order id? That is the value returned  from calling this function. So I&amp;#39;m going to cut this from here and  next we need to supply a valid photo id,  but let&amp;#39;s say product 1, quantity, let&amp;#39;s say 1.  And the unit price 2.95 dollars.  Now let&amp;#39;s delete this select we don&amp;#39;t really need it anymore  let&amp;#39;s add another set of values, so once again, we&amp;#39;re going to call last insert id, to get the id of a new order, we&amp;#39;re going to  change the product to product number 2, and add a different price.  That&amp;#39;s it. Now let&amp;#39;s execute this  statement and see what we get. Alright, so,  back to our orders table, let&amp;#39;s refresh the records here, alright  so we have a new order, order number 13,  beautiful, now let&amp;#39;s look at the orders items table, here&amp;#39;s the order items, let&amp;#39;s open the table. So we should have  2 items, or order number 13. Beautiful.  So this is how you insert hierarchical data in SQL.   In this tutorial, I&amp;#39;m going to show you how to copy data from one  table to another. For example, in our orders table, currently we have  about a dozen records. Now let&amp;#39;s say we want to create a copy of this table  called orders archive and you want to insert every row that we have  in this table into that new table. If you have 10 orders,  we don&amp;#39;t want to quote an insert statement with a ten set of values,  that is very 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, order  archive. For that we&amp;#39;re going to use the create  table as statement. So create table  orders archive as.  Now right after that we write a select statement  to get everything from the orders table.  Now let&amp;#39;s see what happens when we execute this query, there you go. So back in the navigator panel, we have to refresh this view by clicking on this icon over here, now we  have a new table, orders archive, let&amp;#39;s look at the data,  so you can see all the orders are here and we have the exact same column  as the orders table. However, if you open this table in the  design mode, you cans ee that in this table, we don&amp;#39;t have a primary key.  So the order id column is not marked as a primary key.  And also it&amp;#39;s not marked as an auto increment column, so when we create a table using this technique MySQl will ignore these attributes,  and that means if you want to explicitly insert the record  into this new table, you have to supply a value for order id,  because this column is no longer an auto increment column.  So, using create table as statement you can quickly create  a copy of a table, now we refer to this select statement  as a sub query, so a subquery is a select statement  that is part of another SQL statement.  Now we can also use a sub query and an insert statement and that is a very powerful technique. It allows us to do really cool things. Let me show you.  So first, let&amp;#39;s right click the orders archive table,  and click on truncate table, because we want to delete  all the data in this table. Alright, it&amp;#39;s asking for confirmation  let&amp;#39;s truncate the table, so now back to this table.  Let&amp;#39;s refresh the table, we don&amp;#39;t have any records here, alright?  Now back to our query editor, let&amp;#39;s say you want to copy  only a subset of records from the orders table into this table,  like all the orders placed before 2019. So first let&amp;#39;s select  everything from the orders table where  order date is less then 2019, January 1st.  So, these are all the orders, order s copy these orders into he orders archive table. So we can use this select statement as a subquery   in an insert statement, we write insert  into orders archive  now we don&amp;#39;t need to supply the column names, because we&amp;#39;re going to  play values for every column that we have in this query.  So, we did that, and  this is ane xample of using a select statement as a subquery  in an insert statement. Let&amp;#39;s execute this,  alright, now back to the table, let&amp;#39;s refresh the records  we only have the orders placed before 2019.   Alright, here&amp;#39;s a really, really, really coo exercise  back to our SQL invoicing database. Look at the invoices table.  So in this table, we have these columns,  (?) id number, client id number which is associated or related  to the client id column, in the clients table, followed by  a few other columns, now let&amp;#39;s say you want to create a copy of the record  in this table, and put them in a new table called invoices archive.  However in that table, instead of the client id column, we want to have the client name column, so you want to join  table with the clients table, and then use that query as a sub query in a create table statement.  Also, to mae the exercise more interesting,  I want you to copy only the invoices that do have a payment. So if  you look over here, this payment data column here, determines  if a payment has been made, towards this invoice or not. So select  only the invoices that do have a payment date, it&amp;#39;s a really really good  exercise, spend two to three minutes on this and then come back and continue watching.   Alright, first I&amp;#39;m going to use the SQL invoicing data,  base, now let&amp;#39;s select everything from the  invoices table and join it to the clients table.  here I&amp;#39;m going to use the using statement to simplify  my join. What column are we going to use for joining? The client id  column. Let&amp;#39;s execute this query up to this point.  Alright, so first we see client id column  is used for joining these tables, after that we have the columns from  the invoices table like invoice id number and so on. Followed by  the columns from the clients table. Name  address and so on. Obviously we don&amp;#39;t want all these columns, we only want  the columns from the invoices table but we should replace the client id  column with he client name column. So let&amp;#39;s have  a quick look at the design of the invoices table  here we have invoices id, number,  client id, we want to replace this column with the client name.  Back to our query, I&amp;#39;m going to pick,  invoice id, number  and then client.name, let&amp;#39;s rename it to client  what other columns do we have here? We have  invoice total and payment total so let&amp;#39;s add those as well,  invoice total as well as payment total,  we also have three columns,  for dates, invoice date, due date, and payment date,  so, let me close the navigator panel. Invoice  date payment date and  due date. Now technically because these columns only exist  in the invoices table, we don&amp;#39;t have to prefix them with the table alias. So we can simplify the code like this, however,  I personally prefer to prefix them, because that gives me a clear  picture of how I&amp;#39;m joining these tables. It&amp;#39;s just a personal preference  another developer might disagree and that&amp;#39;s fine, so whatever you prefer. That&amp;#39;s perfectly fine, let&amp;#39;s execute the query and make sure  we get the right result, so we have the invoice  ID  number client beautiful, 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 either  return records that have a payment date, or the records that have  a payment total of greater then 0. Both are perfectly  fine, so back to our query,  down at the bottom, let&amp;#39;s add the where clause, where payment  date is not null. That&amp;#39;s better.  Let&amp;#39;s execute the query one more time, now we get only these  handful of invoices, beautiful, finally let&amp;#39;s use our  query, as a subquery in a create table as statement. So right before  select we type create table, invoices,  archive, as there you go, let&amp;#39;s execute  the query beautiful, now back to the navigator panel,  let&amp;#39;s refresh the view, so here&amp;#39;s our new table,  invoice and archive, let&amp;#39;s look at the data, there you go.  You only have the invoices page and here&amp;#39;s the name of the client for each invoice. Beautiful. Now just note that  if you execute this query one more time, you&amp;#39;re going to get an error because we already have a  table called invoices archive. Later in the course I will show you how  to draw tables, that&amp;#39;s pretty easy, but for now you can just right click and go to draw table and then confirm  alright, and then you can run the query one more time.   In this tutorial I&amp;#39;m going to show you how to update data in SQL. So back to our invoices table,  look at the first record here. The payment total for  this record is 0, and obviously there is no payment date.  Now let&amp;#39;s imagine that there was a problem int he system that recorded this information.  Let&amp;#39;s say the client paid ten dollars for the invoice. So we should update this record in this table. That is very easy, back ti 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 or columns. In this case we want to update payment total. Let&amp;#39;s say to ten dollars. We should also update  payment date. So here&amp;#39;s a column to add more columns  we set this to date value, let&amp;#39;s say  2019, March 1st, and then we type  out a condition, with this condition we identify  the record or the records that need to be updated. In this case we want to update the invoice number 1. So,  back to our query, we type out invoice id  equal to 1. Let&amp;#39;s execute this. Beautiful,  Now back to our table, let&amp;#39;s refresh the data by clicking on this icon.  Alright, we can see that payment total is updated to ten.  And we also have a payment date. Beautiful. Now lets say we actually  updated the wrong record, maybe we should have updated invoice  number 3, so we should update this table one more time, and restore the  original values in this column, back to our query  we can set the payment total to 0, and  the payment date to null. So we can use the null keyword to insert the null value in a column that accepts  null values. Now back to the navigator panel, let&amp;#39;s open the  table in the design mode. We can see that the payment total column  has a default value of 0, and the payment date column  has the default value of null. So back in our query  we can also set payment total tod default and MySQL  will use the default value for this column, which is in this case 0.  So, let&amp;#39;s execute this statement one more time, beautiful.  Now, back to the table, let&amp;#39;s refresh the data, so payment total is set to 0. And payment date is null. Beautiful.  Now let&amp;#39;s go ahead and update the third payment. Look at the invoice total. That is $147. For this example let&amp;#39;s  imagine that the client made 50% of the total  amount on the due date. So, back to our query  editor window, instead of using a literal value window here like $70 we can write an expression, we want to calculate 50% of invoice total. So invoice total,  x 0.5. Now let me break this code into multiple  lines so we can see clearly, now we should  set the payment date, as I told you this client made the payment  on the due date, so we can set this to due date.  Whatever 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&amp;#39;s refresh the data.  Alright look, payment total is updated, and it&amp;#39;s set to almost 50% of he invoice total. However,  this number is truncated so we don&amp;#39;t have the digits after the decimal point.  Don&amp;#39;t worry about it for now, we&amp;#39;ll come back to this when we talk about data points  later in the course. Also we can see that payment date is set  to the same value 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 type out here, has to be more general.  For example, back to the invoices table, you can see that we have multiple  invoices for client number 3. You can write a statement  to update all the invoices for this client. So back to our query editor window, we change our condition to,  where client id equals 3. However, if  you execute this statement with MySQL workbench which is the software we&amp;#39;ve been using  in this course, you&amp;#39;re going to get an error, because by default, MySQL workbench  runs in the safe update mode. So it allows you to update only  a single record. You&amp;#39;re not going to have this problem if you use another client for  MySQL or if you write this statement in your application code  this is only specific for MySQL workbench. Now let me show you how to get around this. On the top we go to MySQl workbench menu and then preface this.  On this dial up box, on the left side, click on SQL editor  and then down on the bottom,  untick this checkbox, save updates, so this  prevents you from accidentally updating or deleting a bunch of records in a table,  so let&amp;#39;s goa head with this, now we need to  reconnect to this instance of MySQL. So, let&amp;#39;s copy  all the code here, and close this local instance  window. Alright? Now, on the home page, double click this connection to reconnect, there you go, and then paste  all that Sql code. Now let&amp;#39;s  execute this, beautiful, all the invoices for client number 3 are updated.  Here we can also use the in operator, let&amp;#39;s say  we want to update all the invoices for clients number 3 and number 4.  So all the operators 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, you simply leave it out.   Alright here&amp;#39;s your exercise for this tutorial. Back to our SQL store database,  write a SQL statement to give any customers,  born before 1990, 15 extra points.   Alright first we&amp;#39;re going to use SQL  store, then, write an update statement. To update the customers table,  set the points to point  plus 15, so here we&amp;#39;re using the expression to update  the points column, for anyone born before  1990. So where birth date is less then  1990, January 1st.  Alright, let&amp;#39;s execute this query, alright, now  let&amp;#39;s open up the customers table one more time,  so anyone who was born before 1990 now has an extra  50 points.  In this tutorial we&amp;#39;re going to learn how to  use subqueries in an update statement, it&amp;#39;s extemely powerful,  so we&amp;#39;re going to continue with the example from the last tutorial, but we&amp;#39;re going to make it more interesting. Here we&amp;#39;re updating all the invoices for  client number 3, but what if we don&amp;#39;t have the id of the client? We only have the name? For example, you might even have an application, and in that application the user types in the name of the client, so first we should find the  id for that client, and then use that id to update all the invoices.  How do we do that? Well, let&amp;#39;s have a quick look at our clients  table, so here we have this client  my words, let&amp;#39;s say you have the name, you want to find the id.  So back to our query window. After our update statement  I&amp;#39;m going to write the select statement to select the client id column,  from the clients table, where name equals  my works. Now here we have  a syntax error, because we didn&amp;#39;t terminate the last  don&amp;#39;t worry about that, we&amp;#39;re going to get back to that in a second. Let&amp;#39;s just select these few lines and  execute this query. The idea of this client is  2. Beautiful. Now we can use this select statement as  a subquery in a update statement. So as I told you before,  a subquery is a select statement that is within another SQL  statement. So, instead of hard coding 3 here,  we&amp;#39;re going to use this select statement as a subquery, but  we need to put it in parenthesis, so MySQL will  execute this query first, it will return the client ID and then use it in this condition. So for clarity  let&amp;#39;s remove the line break and indent  these few lines. So this is the end result. Now  we execute this query, and this updated all the invoices for this client.  Now, what if this query returns multiple clients?  For example, back to the clients table, let&amp;#39;s imagine you want to update  the invoices for all clients located in New York or California.  So, we need to update our subquery,  like this, where state  in California, and  New York. Now before executing the entire statement,  let&amp;#39;s just select our subquery and execute it to see what we get.  So we get two client ID&amp;#39;s, one and  three, beautiful, now because this query, this subquery  returns mutliple records, we cannot use an equal sign  here anymore, so we need to replace this with an in operator.  And now this statement updates the invoices for all the clients  located in these two statements. Let&amp;#39;s execute it,  beautiful, everything worked. So as a best practice, before executing your  update statement, run your query to see what records you&amp;#39;re going to update. So you don&amp;#39;t  accidentally update the records that shouldn&amp;#39;t be updated. Now here we  have a subquery but even if we didn&amp;#39;t have a subquery, you could still query the records that we&amp;#39;re going to update. Let me show you. So let&amp;#39;s imagine  we&amp;#39;re going to update all the invoices where payment  date is null. Before executing this entire update  statement, I would run a query like this. Select, start  I would start from invoices where payment date is null.  Now let&amp;#39;s execute this query,  these are the two records that don&amp;#39;t have payment date, so once we&amp;#39;re confident that we&amp;#39;re updating the right records, then, we come back here,  and get rid of this select statement, and just attach the where clause  to our update statement.  Alright, here&amp;#39;s your  exercise for this tutorial, back to our SQL store database, look at the  orders table, as you can see, several orders don&amp;#39;t have a  comment, I want you to write a SQL statement to update the comments or orders for customers who have more then 3000 points.  So customers who have more then 3000 points, regard them as gold customers. Find their orders, if they have placed an order update the comments column and set it to gold customer.  That&amp;#39;s a really good exercise.  Alright, first we need to find  the gold customers, so select everything from the customers table,  where points is greater then 3000.  And by the way, because current database is SQL invoice  we either have to type out a use statement on the top, or  double click this database before executing this query. So,  there you go. We have three gold customers  now we need to get the ID of these customers to use them in an update  statement. So, we only select customers  Id here and then select this statement as a subquery  in an update statement. So,  update, orders, set comments to  gold customer, where  customer id because we&amp;#39;re dealing with multiple customer id&amp;#39;s  we need to use the in operator and then to use it,  as a subquery, we need to enclose it in parenthesis.  Let&amp;#39;s indent the code, that&amp;#39;s better, so here  is the final solution.   So you have learned how to insert and update data, in this lecture, we&amp;#39;re going to finish this section  by learning how to delete data. That is very easy. We use the delete  from statement to delete records from a table.  Let&amp;#39;s say the invoices table. Now optionally we can add a search condition  to identify the records we want to delete. If you don&amp;#39;t write this where clause with this statement, we delete all the records in this table.  And obviously, that&amp;#39;s very dangerous, so be very careful when executing this statement. Now here let&amp;#39;s say we want to delete the invoice  with the id 1, so where invoice id goes to 1.  Now here we can also use subqueries, let&amp;#39;s say we want to delete all the invoices for  the client called my works. First let&amp;#39;s  find this client, so select everything from the client&amp;#39;s table  where name equals to my works.  Let&amp;#39;s execute the second query, so here&amp;#39;s our client,  now we can get this client  id and use it in our search condition. So where  client id equals 2, this is where we add our subquery, there you go, just like before,  beautiful. So this is how we can delete data,  in SQL.   Alright, we&amp;#39;re done with this section, but before going to the next section, I want you to restore all these databases to their original state. Because in this section we  added some data, we updated some data, we deleted some records,  so if you don&amp;#39;t restore these databases you may see different results going forward.  So, restoring these databases is pretty easy. Here in My SQL workbench, on the top go to the file menu  and open SQL script. Then navigate to the directory  where you stored the SQL scripts for this course, in case you lost  that directory, go back to the first section, you have a lecture for downloading the supplementary materials. So in this directory open  create databases.SQL.  Now execute this script to recreate all of our databases.  Alright, beautiful, now let&amp;#39;s open up the navigator panel, you can see the  databases disappear from here, simply click on this refresh icon.  Beautiful. Alright, we&amp;#39;re done with this section, I will see you in the next  section. Hey guys, Mosh here,  in case you haven&amp;#39;t seen my website yet, head over to codewithmosh .com. This is my coding school where you can find plenty of courses  on web and mobile application development. In fact recently I  published a complete SQL course that is about 10 hours long,  and it teaches you everything you need to know from the basic to advanced topics such as database design, security, writing complex queries,  transactions, events and much, much more. These are the topics  that every software engineer must master. This Youtube  course you&amp;#39;ve been watching is the first three hours of my complete SQL course that is about 10 hours long. So if you want to master SQL  and get job ready, I highly encourage you to get involved 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 for this course is $149 but the first 200 students can get it for just over $100. So if you&amp;#39;re interseted, the link is below this video.  

Each question must have 4 options, 1 correct answer, focusing on vocabulary, phrases, or concepts. Format each question as: 
1. Question text
A) Option1
B) Option2
C) Option3
D) Option4
Correct: C
User: Generate 5 technical multiple-choice quiz questions from this transcript about language learning and directions:

Hi, my name is <PRESIDIO_ANONYMIZED_PERSON>, and I&amp;#39;m  super excited to be your instructor. In this three hour course, you&amp;#39;re going to learn everything  you need to get started with SQL or SQUEL. First I&amp;#39;m going to give you a three minute introduction to SQL, then we&amp;#39;re going to 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  anyone with some basic knowledge who wants to fill in the missing gaps. By the end  of this course, you&amp;#39;ll be able to retrieve insert, update, and  delete data in databases. We&amp;#39;ll talk about tables, relationships,  different types of joints, sub-queries regular expressions and  much, much, more. These are the essential concepts every software developer  or data scientist must know. This SQL course is packed with tons  of exercises that help you both learn and remember the SQL syntax. Also, there is a table of content below this video so you can quickly jump to specific tutorials, now let&amp;#39;s jump in and get started.   Let&amp;#39;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. In order to manage our databases, we use a software application called database management system,  or, DBMS. We connect to a DBMS and give it  instructions for querying or modifying data. The DMBS will execute our instructions and send results back. Now we have  several database management systems out there, and these are classified into two categories, relational and non relational, also  called NoSQL. In relational databases, we store data in tables that are linked to each other using relationships. That&amp;#39;s why we call these databases relational databases, each table  stores data about a specific type of object, like customer product, order and so on. SQL or SQUEL is the language that we use to work with these relational database management systems. It looks like this. We use SQL to query or modify our data. In this course you&amp;#39;re going to learn everything there is to know about this powerful  language. Now, there are many different relational database management systems,  out there, some of the most popular ones are MySQL, SQL Server my Microsoft, and Oracle. But of course, there are plenty  more. Each database management system has a different flavor of SQL.  But all these implementations are very similar and are based on the standard SQL specification. So most of the SQL  code that you will learn in this course will work with any database management system.  In this course, we&amp;#39;ll be using MySQL, which is the most popular  open source database in the world. Okay, now back to this diagram,  what about non relational databases? In non relational databases,  we don&amp;#39;t have tables or relationships, these databases are different from relational databases, but that&amp;#39;s a topic for an entirely different course.  What you need to know is non relational database systems  don&amp;#39;t understand SQL. They have their own query language.  So, we use SQL to work with relational database management  systems. Now, before we jump in and install MySql, let me clarify  something quickly, as 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 everybody thinks their way of pronouncing this word is the right way. But here&amp;#39;s a  history about this language. SQL was originally developed at IBM in the 70s and back then it was initially called SQUEL short for structured English query language. But they changed the acronym to SQL, because SQUEL was the trademark of an airplane  company. So to this day, there has been an argument about what is the right generally speaking, people in non-English speaking  countries call it SQL. I&amp;#39;m used to calling it SQUEL because it&amp;#39;s shorter and sweeter than SQL. But if you prefer to call it SQL, that&amp;#39;s totally fine with me, I&amp;#39;m not going to get mad at you. So that&amp;#39;s the history behind this language. But what about MySQL as a  software product? Developers of this product prefer to call it MySQL rather than mySQL. But they don&amp;#39;t mind if they call it MySQL.  In this course, I&amp;#39;ll be teaching you SQL with MySQL.  Hey guys, Mosh here. I just wanted to let you know that  you really don&amp;#39;t have to memorize anything in this course, because I&amp;#39;ve created a  complete cheat sheet with summary notes for you. You can find it below this video  in the description box. So I have done my best to create the best possible, most comprehensive SQL course for you, and I would really appreciate it if you would support my hard work by liking and sharing this  video. Thank you so much, now, let&amp;#39;s continue.  In this tutorial,  I&amp;#39;m going to show you how to install MySQL on your computer. Now I&amp;#39;m using a Mac, so first I will show you how to install MySQL on a Mac  computer, and then I will show you how to install it on Windows. So if you&amp;#39;re a Windows user,  feel free to skip this tutorial. Now, open up your browser and head over to MySQL.com. Then, go to the downloads page, and scroll down to the bottom. Here you should see MySQL community edition, this is absolutely  free, and we&amp;#39;re going to use that throughout this course. So let&amp;#39;s go ahead  and look at that, now on this page, click on MySQL,  community server, and then on this page,  you should see available releases for Mac OS.  So, in this list, download the first item which is a dmg archive.  Alright, now, on the next page, click on no thanks, just start my download. Okay, as you can see, we are getting a dmg file, which is basically a setup wizard.  Alright, now the DMG is downloaded, so let&amp;#39;s open it,  and then double click on this package, this will launch an installation wizard which is pretty easy to use, so simply  click on continue and again, and again,  agree with the license agreement, and  install MySQL, it&amp;#39;s going to ask for your password, this is the password  that you used to log into your computer, so let&amp;#39;s put that here,  alright, now here we need to set a password to the root for the admin user. So click next, and  in this box, type a complex password,  alright, now, let&amp;#39;s finish the installation,  and enter your computer&amp;#39;s password one more time,  and we are done!  That was super easy and sweet.  Alright, we installed MySQL community server, now we  need the graphical tool to connect with this server and manage our databases.  So, back to the downloads page, one more time,  scroll to the bottom, and go to 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&amp;#39;s go ahead and  download this as well. Now once again on this page, we need to download a dmg archive, so,  download, and, again we have  to say no we don&amp;#39;t want to login or sign up, so let&amp;#39;s go ahead and download  the dmg, and then  open it, alright, you&amp;#39;re going to see something like this, so  drag this MySQL workbench and drop it onto the applications folder.  So, let&amp;#39;s go ahead with that, now it&amp;#39;s going to copy  this into the applications folder, beautiful,  so we&amp;#39;re done with the installation, that was super easy, now press command and space, and search for MySQL,  work bench, there you go, let&amp;#39;s open it, now the  first time we get this message, because this is an application we downloaded from the internet.  So, we need to tell Mac that we trust this, let&amp;#39;s go ahead with that,  so this is MySQL workbench, now by default  you see a connection here, if you don&amp;#39;t see that, you need to create it. Let me show you how to do that. So for this demo I&amp;#39;m going to right click this and delete this connection, alright,  now let&amp;#39;s create a connection from scratch. So click on this plus icon,  on this page, give this connection a name, let&amp;#39;s  say local instance, now the connection method  we&amp;#39;re going to use is tcip, which is set by default, the  host name 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 MySQL server, that is the user name of the admin, now we need to enter the password. This is the password that we set during the installation. So, click on  store in keychain, and in this box, type the  password for the MySQL server. Alright  and finally, let&amp;#39;s test the  connection, okay, we successfully connected to MySQL server on the local machine, beautiful, let&amp;#39;s click okay,  and here we have a connection on the homepage of MySQL workbench, every time we open MySQL workbench, we use this connection to connect to our local server. Alright, we&amp;#39;re done with the installation of MySQL on a Mac, next I will talk about MySQL on a Windows computer, so feel free to skip that tutorial.  In this tutorial, I&amp;#39;m going to show you how to install MySQL on Windows. So open up your browser and head over to MySQL.com, then, go to the downloads page now here, scroll down to the bottom,  we&amp;#39;re going to use MySQL community edition which is absolutely  free. So, let&amp;#39;s go with this, now,  select MySQL community server,  and then scroll down so here you should see something like this, MySQL installer for Windows. This is the  recommended method for installing MySQL on Windows. So,  click on this, alright, on the next page, scroll down,  and  download the first installer here. On the next page  scroll downs nd click on no thanks just start my  download. Otherwise you have to create an account and log, which is unnecessary for knowing this course, so, let&amp;#39;s go with this,  and save this file to our computer, and then  run it.  Alright, we&amp;#39;re going to 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 places where you need to enter a password, let me show you. So, on the first page, for the setup type we&amp;#39;re going to use the  developer default setup. Go to the next page. Now here we&amp;#39;re getting a little warning, because this installation wizard wants to install the connector for python 3.7, but I don&amp;#39;t have Python on this machine, so that&amp;#39;s why I&amp;#39;m getting this warning, now on your  machine, you might or you might not get this error, just click  next, and one more time, so here are the products that I want to get installed the first one is MySQL server,  the second one is MySQL workbench, this is the graphical tool that we use to connect to our database server, and manage our database.  You&amp;#39;re going to see that soon. So click on execute,  now this is going to take about 5-10 minutes, so,  I&amp;#39;m going to pause the recording.  Alright, all the products are installed, beautiful, let&amp;#39;s go to the next page,  and again, here our the group replication  page, also click on next, and the next page which is  about networking, leave all the default settings, so,  let&amp;#39;s go to the next page, now we should set a password for the root for  the admin user. So, click on next,  and in this box, type a password for the admin user.  Alright, and then  let&amp;#39;s go to the next page, once again leave all the default settings,  and click on next, and execute one more time,  alright, and now  let&amp;#39;s finish our installation, once again we have to click on next,  and then finish. One more time,  there are so many steps. Now here&amp;#39;s the page where you need to enter the admin password, so the page is called connect to server, you can see the username is root, which represents the admin user,  so in this box enter the password that you said earlier,  then click on check, okay, connection was successful  beautiful, let&amp;#39;s go to the next page, and  click on execute, and finally finish.  There you go, we have one more step, next the  finally after all these steps the installation is complete. Now, this is going to start, MySQL workbench, which is the graphical tool we use to manage our database and run SQL queries.  So, click on finish, so now we have a command  prompt window, where we can type instructions to talk to our MySQL server,  we don&amp;#39;t really need this, so close it,  and here&amp;#39;s MySQL workbench, now the first time you open this page,  by default you should see a connection here, if you don&amp;#39;t see it,  click on this plus icon,  on this page give this connection a name, let&amp;#39;s say local  instance, now leave all their settings to their default value,  but here for the password click on store involved.  And in this box, type the password that you used for the admin user.  So, I&amp;#39;m going to put that here, okay,  now click on test connection,  alright, we successfully connected to the MySQL server on this  computer, alright, then, click on okay,  now we click on this connection to connect to our database server.  Alright, so here&amp;#39;s the interface you&amp;#39;ll be using throughout this course,  on the left hand side we have the navigator panel, in the middle we have the query editor which we can resize  this is where we&amp;#39;re going to write our SQL queries, and on the right side we have  additions. So we&amp;#39;re done with MySQL  on our computer, next, I&amp;#39;m going to show you how to create our databases for this query.   In this tutorial, I&amp;#39;m going to show you how to create the databases for this course. So here I&amp;#39;ve got MySQL workbench open, let me quickly give you an overview of this interface because  the first time you open it it might appear a little bit intimidating.  But actually it&amp;#39;s not that difficult. So here on the top we have this tool bar with these buttons for creating a new tab for writing SQL code  as well as opening a SQL file. And next to that  we&amp;#39;ve got a bunch of buttons for creating a database, creating new tables and  so on. On the left side, we&amp;#39;ve got the navigator panel with two tabs  administration and schemas. We use the administration tab to do administrative work, such as starting or stopping our server. Importing or exporting data and so on. The schemas  tab shows the databases that we have in the current database server.  So currently we only have one database, that is sis, and this is the database that MySQL uses internally to do it&amp;#39;s work.  Now, in the middle we&amp;#39;ve got this query editor window, this is where we write our SQL code, so we&amp;#39;ll be spending most of our time in this course, in this window, and on the right side we&amp;#39;ve got another panel with two tabs,  contacts held, and snippets, now chances are this interface  might look slightly different on Windows, but what I&amp;#39;m showing you here is  almost identical to what we have on Windows. So don&amp;#39;t get hung up if it  looks slightly different on your machine. It doesn&amp;#39;t really matter. Now up here,  we&amp;#39;ve got these buttons for showing or hiding these panels, so to clean this interface, I&amp;#39;m going to hide this panel on the right side.  As well as the panel on the bottom. That is better. Now,  to create the databases for this course, download the zip file attached,  below this video. When you extract the (?) you&amp;#39;re going to see a bunch of SQL files like this. So the main one you&amp;#39;ll be using 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 for creating  individual databases, I&amp;#39;ve just added these files in case you need to recreate  one of these databases in the future. But for now don&amp;#39;t worry about them. Now,  back to MySQL work bench, let&amp;#39;s open  the main SQL file. That is create databases.  So this is an example of SQL code.  Now this may look complex at the beginning, but trust me, as you go through the course  you&amp;#39;re going to understand exactly how everything works here. You&amp;#39;re going to be able to write SQL code like this. So, you want to execute  this, to create all the databases for this course. To do that, we click  on this icon, this yellow thunder icon that we have  on this tool bar here. This will execute either the selection or the  entire code if there is nothing selected for example, if I select this line here,  and click on this icon, this will execute only this line. In this case we want to execute the entire code, so we shouldn&amp;#39;t select anything,  and now let&amp;#39;s execute this, beautiful, now here down on the bottom,  we have this panel called the output window that shows all the  operations perform in our database server, so we can see  all the operations completed successfully, or something went wrong.  As you can see you&amp;#39;ve got these green ticks next to each operation.  Beautiful, so I&amp;#39;m going to close this panel, that&amp;#39;s better.  Now on the left side in this schemas tab you don&amp;#39;t see this new database  so we&amp;#39;ll have to refresh this view, beautiful.  So we&amp;#39;ve got all these databases that are prefixed with SQL or SQL, I decided to prefix them with SQL so we know that  these are the databases for this course, they don&amp;#39;t accidentally clash with  a database on the same name of your database server. Now,  at the time of recording this video, there are only 4 databases here, but as we go through  the course, I&amp;#39;m going to update the script for creating the databases, so,  when you watch this course, chances are you&amp;#39;re going to see more databases here. Don&amp;#39;t worry  worry about the difference. Now as an example let&amp;#39;s explore  these databases. And by the way we don&amp;#39;t need this tab anymore so let&amp;#39;s close it.  That&amp;#39;s better. Let&amp;#39;s expand the SQL store database, now in every database we have these objects, we have  tables, this is where we store our data, we have views,  which are kind of like virtual cables, so we can combine data from multiple  tables, and put them in a view. And this is especially powerful  for creating reports, you&amp;#39;re going to learn about them in the future. You also have  store procedures and functions, and these are little programs that we  stored inside of our database for querying data. For example,  you can have a store procedure for getting all the customers in a given city.  So we call that procedure and we say hey, give me all the customers in San Francisco  And this will return all the customers in San Francisco.  Okay, now, let&amp;#39;s expand the tables,  so here are the tables in this database. We have customers,  we have orders, products, shippers and so on.  Now, select this customers table whenever you hover your mouse  over this item and see these three items on the right side.  Click on the right most icon, that looks like a table with a  thunder. With this we can see all the data, in this  table. So this is our customers table. In this table, we have these columns like customer id which we use to uniquely identify  customers. We all have first name, last name, birth  date, phone, address and so on. So these are the columns  in this table and every row is called a  record. So every row represents one customer and these are the pieces of  information we know for each customer. Now let&amp;#39;s look at  another table, let&amp;#39;s open the orders table,  in this table we have these columns like order ID, customer ID,  order date, status, and so on. What is this customer ID here.  We use this column to identify who has placed  each order. Now what is interesting here is you have referred to this customer  using their customer ID which uniquely identifies that.  In other words, if Jon Smith has placed an order, we don&amp;#39;t  store John Smith here, we only store John&amp;#39;s customer ID.  Why is that? Here is the reason. It is possible  John Smith might have placed multiple orders in our system.  Now every time John places his order, we need to look up his address and  phone to ship his order. Now it is possible that some of this information  might change in the future. John might move to a new place or change his  phone number, he might even change his name, if you repeat all that information next to each order, then we&amp;#39;ll have to come back and make changes in multiple places. In contrast, with this design we only  store the ID of john here, so anytime we want to change  any information about John, instead of changing that here. We go back to our customers table, so let&amp;#39;s look at  customer with ID 6, that is actually called (?) So here is all the information about Elka. This is her phone number, this is her address, and by the way this is all dummy data that I created using a tool.  So, if any information about elka changes in the future,  this is the only place that we need to modify. So  this is how these databases work. We refer to these databases as  relational databases. That basically means in these kind of databases we have  multiple tables that are related to each other using  a relationship. So internally there is a relationship between the customers table, and the orders table. So the customer  ID column in the customers table is related  or linked or associated with the customer ID column.  In the orders table. Here&amp;#39;s the orders table, and here we have  the customer ID column. So this was a brief introduction to  relational databases, you learned about databases, tables,  columns, rows and relationships. In the next section I&amp;#39;m going to show you how to retrieve data from a single table in this database.  But, before going any further as an exercise I want you to explore  the invoicing database. Look at the all the tables, look at all the data to get an id of the kind of data that we have in this database. You&amp;#39;re going to use this database  a lot in the future, so let&amp;#39;s spend a couple minutes to explore this database.  In this tutorial I&amp;#39;m going to show you how  to retrieve data from a single panel. Now look at the navigator panel.  Currently none of our databases is displayed in bold, and that means none of these databases are selected for query. So the first step to rate a query to get data from a database, is to select a database.  The query that we&amp;#39;ll write will be executed against that database.  In this demo, we&amp;#39;re going to use the SEQUEL store database.  So we type our use SQL_ store. Now use is a keyword in the SQL language, and that&amp;#39;s  displayed in blue. Now SQL is not a case sensitive language,  and that means we can use upper case or lower case characters, it doesn&amp;#39;t really  matter, but as a best practice, we should capitalize the  SQL keywords, and use lower case characters for everything else.  So now, let&amp;#39;s go ahead and execute this query. Alright,  Look, the SQL store database is now displayed in bold.  Now in MySQL workbench, we can also select a database  by double clicking that, so now I double click SQL invoice  and it&amp;#39;s the current database. Now if we run this query again,  the SQL store database becomes selected.  Alright, now let&amp;#39;s write our first query to retrieve all the customers in this database. So after the you statement we&amp;#39;re going to use  select statement. Here is the basic syntax or basic structure  of the select statement, we type out 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 first name column or  we can retrieve all columns using an asterisk.  Now after that we use the from clause and this is where we specify the  table that we want to query, in this case the customer&amp;#39;s table.  So this is the simplest query to select all the customers in a given table,  now whenever you have multiple SQL statements you need to terminate each statement using a semicolon. So,  look, we have a red underline here that indicates an error, if you hover your mouse over here,  you can see this tool tip saying select is not valid at this position.  Because we didn&amp;#39;t terminate the first statement with a semi colon.  Okay, now let&amp;#39;s execute this query one more time, once again,  we can click on this button here, or we can use a short cut,  so look at the query menu on the top, the first line is execute. Now here&amp;#39;s the shortcut for this command, on Mac, it&amp;#39;s shift  command and enter. On Windows its going to be different honestly  I&amp;#39;m not sure. So whatever it is, use that. So I&amp;#39;m going to press  shift command enter, and here are  all the customers in this table. So this  next statement has two clauses, the select clause and the  from clause. But there are other clauses we can use to filter  and sort data. For example, you can use the wear 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&amp;#39;ll only get the customer with ID  1. So this is the where clause. We can also sort  the data so after wear we use the order by  clause and here we specify the columns that we&amp;#39;re going to sort the results from on. Let&amp;#39;s say we want to sort these customers by their first name so we type out  first_name. That is the name of one of the columns in this  table, right? Now if you execute this query this order by doesn&amp;#39;t really have  an impact, because we only get one record in the result  so let me temporarily take out the where clause,  to do that we can put two hyphens in front of this line, now this line,  is treated as a comment, which means the SQL engine is not going to execute this, okay, so let&amp;#39;s execute this query one more time,  now all the customers that we get our sorted  based on their first name. So that&amp;#39;s the basic idea.  Now over the next few tutorials your&amp;#39;e going to learn more about these clauses in detail. But what you need to take away in this tutorial is that  is that these 3 clauses from where an order by are  optional. As you can see in this example, I&amp;#39;m not using the 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, we can select some values like one and two. Now,  if you execute this query one more time, in the result, we 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 quite often use all of them. Now what you need to understand here is that  the order of these clauses matter, so we always have select first,  then we have from, then where, and finally order by.  We cannot change the order of these clauses, otherwise we get a syntax error.  Which basically means the syntax, or the grammar or the structure  of our simple statement is incorrect. So it cannot be executed.  And one last thing before we finish this tutorial, you can see I&amp;#39;ve listed all these clauses on a  new line, now technically you don&amp;#39;t have to do this, because line breaks, white spaces and tabs are ignored  when executing SQL statements. So we could come back here and  put from in front of select, so select store  from customers all in one line, and that&amp;#39;s perfectly acceptable for simple queries,  but as your queries get more complex, it&amp;#39;s better to put each clause on a new line. So that&amp;#39;s all for this tutorial. In the next  tutorial, we&amp;#39;ll explore the select  clause in detail.   In this tutorial, we&amp;#39;re going to look at the select clause in detail.  So, since our current database is SQL store, to  clean things up I&amp;#39;m going to remove the first statement, you don&amp;#39;t really need it now,  also, I&amp;#39;m going to delete these two comments, we just want  to focus on the select clause. Alright, 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  the columns that we want and this is helpful in situations where you have a  big table with so many columns and perhaps millions of record.  If you want to bring back all that data, that&amp;#39;s going to put a lot of pressure  on the database server, in this case MySQL, as well as the network.  So, that&amp;#39;s when we explicitly specify the columns that we  want to get. Let&amp;#39;s say we want to get the first name  and last name columns. Execute the query, as you can see, we only get these two columns.  And they are in the same order we specified here. So if we  change the order and put the last name first and execute the  query again, now we can see the last name column comes first.  Now let&amp;#39;s add a new column, at the end, let&amp;#39;s get the points for each customer as well, run the query, so these are the points for each customer which are calculated based on their shopping.  Now let&amp;#39;s say we want to get these points and put them in a mathematical formula  to calculate the discount that we can give to each customer.  So here we can use an arithmetic expression, that&amp;#39;s points plus ten.  This is an arithmetic expression. So now we can execute this query  for the first record you can see that their points will end up being 2283. Let&amp;#39;s run the query one more time there you go. Now we can put the original points column here for clarity.  So points, points plus 10. Let&amp;#39;s run the query one more time, now you can see the original points, and next  to that you can see the value that we&amp;#39;re going to use to calculate the discount.  Now here we&amp;#39;re using the plus operator which is for addition, we also have  multiplication, division, subtraction, and  module which is the remainder of the division. So let&amp;#39;s change this to something  more complex. Let&amp;#39;s say we want to get the points, multiply by 10,  and then add 100 to it.  Now we can immediately see that this line one is getting too long  and it doesn&amp;#39;t fit on the screen, in situations like this, you can break up the select clause by placing each column on a new line, so,  select last name, then first name  points finally points times 10,  plus 100. So let&amp;#39;s execute this query one more time.  So 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 operators, and this is based on the order of operators in math.  So in math, the multiplication and division  operators have nighter precedence then addition and subtraction, so in this expression, points is multiplied by 10 and then the result is added  to 100. If this is not what you want you can always change the order by using parenthesis, as an example, let&amp;#39;s change this multiplication to addition and then put that  multiplication here. In this expression, first 10 is  multiplied by 100, and then the result is added to the points.  Now let&amp;#39;s say this is not what we want, so we can change the order by using parenthesis here.  With these parenthesis, first we get the points add 10 to them  and then multiply the result by 100. So this parenthesis are useful for changing the order of operations as well as  adding clarity to our code. So someone else reading this code can easily understand.  the order of these operations. Now let&amp;#39;s execute this query one more time,  alright, now look at the name of this column here,  its set to the expression that we have on line 5.  That doesn&amp;#39;t quite make sense you want to have a clear descriptive name,  so we can give this column an alias using the as keyword.  So, as and then we give it a name like discount  discount _factor. Let&amp;#39;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 the results sets. Now if you want to have a space in the  column name, you need to surround it with quotes, either  single, or double quotes. So we put quotes here and then  we can add a space in between these two words. Let me  execute the query one more time. Now we have discount factor. So let&amp;#39;s quickly recap everything you learned about the select  clause. We cause an asterisk to return the columns, or  we can explicitly specify the columns that you want to return.  We can also use, arithmetic expressions here, and optionally, we can even  gibe an alias to each column in the results set. Now there is one more thing you need to know about the select clause. So let&amp;#39;s delete this query  and select the state column,  of the customers table. Take a look. These are the  states in which our customers our located. Now currently in the sample  data we don&amp;#39;t have any duplicates, in other words we don&amp;#39;t have multiple customer in any of these states. But for this demo I want to change the  state of the first customer to Virginia, so we end up with duplicates  in the result set. So let&amp;#39;s open up navigator panel here&amp;#39;s our customers table, let&amp;#39;s look at all the data,  and here&amp;#39;s our first customer,  here a you can see, it&amp;#39;s located in the state of Massachusetts  now I want to change this to Virginia. So double click VA for Virginia, enter, now  on the bottom right corner of the screen, you should see two buttons,  apply and revert. Unfortunately I cannot show you this button  because the recording window is a bit smaller then MySQL  But look down in the bottom right hand corner, click and apply. You&amp;#39;re going to see a dial up box like this asking you to review the changes, so go ahead and click that button one more time alright, now, let&amp;#39;s go back to our first query window, and execute this query one more time.  As you can see, the first two customers are located in Virginia. What if you wanted to get a unique list of states in the results set, that&amp;#39;s when we use a distinct  keyword. So select distinct set.  With his query, we&amp;#39;ll retrieve a unique list of states from the  customers table. So with the distinct keyword we can remove  let&amp;#39;s execute the query one more time, now you can see Virginia is not duplicated.  Alright here&amp;#39;s an exercise for you.  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  times 1.1. So let&amp;#39;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  one or two minutes on this exercise, when you are done come back and see my solution.   Alright this is pretty easy, so we start with select  now what columns do we want to select? Name, unit _price and then here we&amp;#39;re going to use an arithmetic  expression to calculate the new price. So we type  out unit price times 1.1 and then give it a  alias, o 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 products table, so from  products. Note that I&amp;#39;ve used upper case characters for all the SQL keywords and lowercase characters for everything else. So,  let&amp;#39;s go ahead and execute tis 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&amp;#39;re going to look at the where clause in SQL. So earlier I told you that we use the where clause to filter  data. For example, let&amp;#39;s say we only want to get the customers with  points greater than 3000. So here in the where clause you can type out the condition like this. Points, greater than  3000. When we execute this query, the query execution engine in MySQL is going to iterate over all the customers  in the customers table. For each customer it&amp;#39;s going to evaluate this condition  if this condition is true, it will return that customer in the  result set. So let&amp;#39;s go ahead and execute this,  and here&amp;#39;s the result, as you can see you only have two customers  with points greater than 3000.  So, thesis 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 then,  less than or equal to, here&amp;#39;s the equality  operator, and for not equality, we can use an exclamation  followed by an equal sign, or something like this. So both  of these are not equal operators. Let me show you examples of  these operators. So I&amp;#39;m going to delete all these and bring back the previous query, let&amp;#39;s say we want to get only the customers in the state of Virginia. So we can change our condition to something like this.  Where state equals Virginia.  Note that I&amp;#39;ve put Virginia in quotes, because this is what we call a  string. A string is a sequence of characters. So whenever you&amp;#39;re dealing with a sequence of characters, or basically textural data, we  need to enclose your values with either single or double quotes.  But quite often, by convention, we use single quotes  so let&amp;#39;s execute this query and here&amp;#39;s the result, you can see we only have these two customers with ID 1 and 2, who are located in Virginia. And it doesn&amp;#39;t matter if you use upper case or lower case characters, so if you type out the a in lower case and execute the query you get the exact same result. Now what if you want to get all the customers  outside of the state of Virginia, you can use the not equal operator.  So, we can either prefix this with an exclamation  or use this other notation.  Either way we get the same result. So these are  the customers that are not located in Virginia. Now we can  use these comparison operators for date values as well. For example,  let&amp;#39;s say you want to get only the customers born after January  1st, 1990. So we change out condition to first date, greater then, once again we use quotes,  for representing date values even though dates are actually not  strings. But in the sequel language, we should enclose dates with quotes,  so, here we type out 1990,  01 for January, -01 for date. So 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&amp;#39;s go ahead and execute this query. I actually made a mistake here, so we don&amp;#39;t see the  result, instead we see the action output, or the output 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 is the name of our column. So,  let&amp;#39;s execute the query one more time, so we only have 3 customers born after January 1st 1990. So these  are examples of comparison operators in SQL. In the next tutorial I&amp;#39;m going to show you how to combine multiple conditions when filtering data.   Alright, here&amp;#39;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 to do we have there, and based on that write query with a  where clause.   So here&amp;#39;s the orders 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&amp;#39;s our query, select, start from order  where order_date, is  greater than or equal to 2019, assuming  this is the current yer, so 2019, 0101.  Now since currently we are in the year 2019, this query will return all the orders placed this year. But next year this query  is not going to give us the right result. But don&amp;#39;t worry about it, later in the course I will show you how to write a query to get the orders placed in the current year,  so for the purpose of the new exercise, this is a valid solution. Now let&amp;#39;s execute this query and see what we get. So, we have  only one order, order ID 1, that is placed in the current  year.  In this tutorial I&amp;#39;m going to show you how to combine multiple search conditions when filtering data. So, let&amp;#39;s say you want to get all the customers that were born after January 1st, 1990, who also happen to have more then  1000 points. So this is where we use the and operator.  So we type out and, and after we type out another condition.  Like points, greater than 1000.  Now when we execute this query, we only get customers who have  both these conditions. Let&amp;#39;s take a look, so execute  we only have 2 customers and if you look both these people are born after 1990, and they have more then  1000 points. So this is the and operator. When we use this  operator, both these conditions should be true. In contrast to the and  operator, we have the or operator. So with or if at least one of these conditions is true, that row will be returned in the  result set. Let&amp;#39;s take a look. Now we execute this query again,  2 records we have quite a few records. So for example,  we have this person who&amp;#39;s not born after 1990  but if you look at their points they have more then 1000 points.  So any customer records that satisfies at least one of these conditions will be returned. Now let&amp;#39;s take this to the next level. Let&amp;#39;s say we want to get customers who are either born after 1990, or  they should have at least 1000 points and live in Virginia  so this is how we do this, we type out and  and then we add another condition, state equals  Virginia. Let&amp;#39;s execute this query and see what we get. We only get 4 records, so these are the customers that are either born after 1990 or they have more then 1000 points and live  in Virginia. If you look at the first customer here this person is not born after 1990. But you can see that she lives in Virginia and she has more than 1000 points. So the last two conditions.  are true for this customer. Now when combining multiple logical operators. You need to be aware of the order of these operators.  So earlier I talked about he order of arithmetic operators. I told you that multiplication have a higher order then addition and  subtraction. And we can use parenthesis 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&amp;#39;re using an and it doesn&amp;#39;t matter that we typed out this condition after the first condition.  Because the and operator has a higher precedence. Now you can always change the order using parenthesis. And this also makes your code cleaner  and easier to understand. So here we can put parenthesis,  around these last two conditions,  and also we can put these in a  new line for  clarity. Something like this. So anyone  who reads this code can easily understand what is the intent of this query.  Now we also have logical operator called  not. And we use that to negate a condition. So,  I&amp;#39;m going to simplify our where clause. Let&amp;#39;s say we&amp;#39;re searching for customers who were born after 1990 or  we have more then 1000 points. If we execute this query  we get these people. Customers with  ID 1, 3, 5 and so on. Now we can use the not operator  to negate this condition. So we apply not here, and  preferably we also put parenthesis around this condition.  Now when we execute this query, we see other customers that are not  in the current results set. Let&amp;#39;s take a look. So,  instead of customers with id&amp;#39;s 1356 and so on, we get customers with id&amp;#39;s 2 4 and 10.  Now technically these customers were born before 1990. And we have less then 1000 points. So if you look here,  this first customer was born before 1990 and  he has less then 1000 points. How do they know that? Let me show you a trick that I learned in math. Whenever you have a not  operator. You can simplify your expression like this. We apply the not operator  to the first condition. People who were born after 1990, how can we negate this condition? Well,  the greater than operator becomes less then or equal to.  That is the inverse of that condition. Now we  apply the not to or to negate the or. What do we get? We get and. Finally apply the not operator on the last condition. We both have more then 1000 points.  When we negate this condition we get customers with less then or equal  to 1000 points. Now, you can remove the not operator  to simplify this, we don&amp;#39;t need parenthesis anymore because we  only have 2 conditions that are combined with an end. Here is the result.  As you can see, this is much easier to read and understand  people who were born before this day and they have less then 1000 points   Alright, here&amp;#39;s your exercise. From the order items  table, get the items for order number 6,  where the total price for the total item is greater then  30.  Alright, here&amp;#39;s the order items table, in this table we have these columns order id, product id, quality, and unit  price. If we multiply the quantity by unit price we can  get the total cost of that item. And then we can compare it with  30. So, let&amp;#39;s go ahead and write this query.  Select star  from order items. Where  here we need two conditions one is for order, so order  _id should be 6, and the second condition  we want to calculate the total price. So we get the unit price multiply it by quantity and this  value should be greater than 30. So as you can see,  we can use an arithmetic expression in the where clause. It&amp;#39;s not limited  to the select clause. Okay? Now let&amp;#39;s execute this query and see what we get. We should get only 1 item,  that is for product 1, here in quantities 4 and unit price  is just over 8 dollars, so the total price for this item, is greater than 30.  Hey guys, Mosh here.  In case you haven&amp;#39;t seen my website yet, head over to codewithmosh.com.  This is my coding school where you can find plenty of courses  on web and mobile application development. In fact recently I  published a complete SQL course that is about 10 hours long,  and it teaches you everything you need to know from the basic to advanced topics  such as database design, security, writing complex queries,  transactions, events, and much much more. These are the topics that every software engineer must master. This YouTube course  you&amp;#39;re watching, is the first 3 hours of my complete SQL course that is about 10 hours long. So if you want to master SQL and get job ready, I highly encourage you to 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 for this course is $149 dollars, but the first 200 students can get it for just over  10 dollars. So if you&amp;#39;re interested the link is below this video.   In this tutorial I&amp;#39;m going to show you how to use the  in operator in SQL. So, as an example, let&amp;#39;s say you want to get the customers that are in Virginia or Florida, or Georgia. One way to write this query is like this. So, where state equals Virginia, or  a state equals Georgia, or a state equals Florida. Now people who are new to the SQL language or  programming in general find this expression a little bit strange.  They ask, &amp;quot;Mosh, why can&amp;#39;t we write this expression like this?&amp;quot; where state equals Virginia or Georgia or  Florida. Here&amp;#39;s the reason, we use the or operator  to combine multiple conditions. So here we have a condition  or an expression more accurately, but on the right side of this or operator  we have a string. In SQL we cannot combine a string  with a boolean expression that expresses a boolean value which can be  true or false. So that is why we have to write our query  like this. So we have multiple expressions or multiple conditions  and we&amp;#39;re combining them using the or operator.  So, now if we execute this query we get these customers here.  customers here but there is a shorter and cleaner way to get the same result.  Instead of combining multiple conditions using the or operator we can use the in operator. So, where state  is in and then in parenthesis we add all the values,  like Virginia, comma, Florida, comma Georgia and the order doesn&amp;#39;t matter, this query is exactly equivalent  to what we had earlier, but a you can see it&amp;#39;s shorter and easier to understand.  So, let&amp;#39;s execute it, look, we get the exact same result. Now here we can also use the not operator. Let&amp;#39;s say you want to get the customer&amp;#39;s outside of these states, so we can use  rare state, not in this list.  Now if you execute this query, we get customers who are located in Colorado,  Texas and so on. So use the in operator whenever  you want to compare an attribute to a list of values.  Now here is your  exercise. I want you to write a query to get the products where  their quantity in stock equals on of these values.  49, 38, and 72, so pause the video, do this exercise, and  then come back and continue watching.  Alright, this is pretty easy, so we do a  select star to get all the columns from the products table,  where quantity in stock  in we use the in operator to compare this attribute with these values.  49, 38, and 72, let&amp;#39;s execute the query,  we get only 2 records because we  don&amp;#39;t have a product with quantity in stock equal to 72.   In this tutorial, we&amp;#39;re going to look at the between operator  in SQL, so that means we want to get the customers who have more then 1000 and less then 3000 points. What made you write these queries like this? Where, points, greater than thousand, more accurately greater than or equal to 1000.  And, points less then or equal to  3000. When we execute this query we get  how many, we get 4 people that satisfy my criteria.  Now whenever you&amp;#39;re comparing 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 points,  between 1000 and 3000.  This is exactly equivalent to what we had before, so these range values are also inclusive, so that means points is going to be  greater than or equal to 1000 or greater than or equal to  3000. Let&amp;#39;s execute the query, we get the exact same result.   Alright, now as an exercise, I want you to write a query, to get the customers  that are born between January 1st 1990 and January  21st, 2000.  Alright, so we start the  select star from customers,  where birth_date between  so what matters here is we can use the between operator  as well. It&amp;#39;s not limited to using numbers. So with birth date between now we need to supply two date values, so as I told you before,  the format for dates is four digits for the year, so  1990, two digits for the month, and two digits for the date.  So, the birthdate should be between this value and  here&amp;#39;s a second value. 2000, 0, 1, and  01. Let&amp;#39;s execute this query, we get  only 3 people who match this criteria.   In this tutorial, I&amp;#39;m going to show you how to retrieve  rows that match a specific string pattern, for example,  let&amp;#39;s say we only want to get to customers who&amp;#39;s last name start with  3. So, here in the where clause we type out where last name this is where we use the like operator,  and right after that we have a string pattern, so,  you want to get the customers who&amp;#39;s last name start with b and we have  any number of characters after b. So use the  percent sign to indicate any number of characters, you may have  one character after b or no characters or 10 characters, with this pattern, you get all the customers who&amp;#39;s patterns start with b.  And also it doesn&amp;#39;t matter if it&amp;#39;s an upper case or lower case b.  So, let&amp;#39;s execute this query, there you go, so you only have  3 customers who&amp;#39;s last name starts with b. As another  example, let&amp;#39;s say we only want to get the customers who&amp;#39;s last name starts with  brush. So, we change our pattern to brush,  percent. Now, let&amp;#39;s execute the query, we only get  this one customer here. Now this percent sign doesn&amp;#39;t have to be  at the end of the pattern, it can be anywhere. For example, let&amp;#39;s say we want  to search for customers who have an e in their last name, whether it&amp;#39;s at the beginning  in the middle or at the end, so we change our pattern to  percent e percent this pattern means we can  have any number of characters before or after b. Let&amp;#39;s execute the query, these are the customers that have a b  somewhere in their last name. It doesn&amp;#39;t matter if b is in the beginning  or in the middle or at the end. Let&amp;#39;s look at another example,  I want to get all the customers who&amp;#39;s last names end  with y, so here&amp;#39;s the pattern we use,  let&amp;#39;s execute this query, so we have 5 customers  who&amp;#39;s last name ends with a y. So this is how we use the  percent sign. Now we also have an underscore and  that matches a single character so with this pattern you get customer  who&amp;#39;s last name is exactly two characters long,  we don&amp;#39;t care what the first character is, but the second character should be y.  Let&amp;#39;s execute his for you. Obviously we don&amp;#39;t have any customers who&amp;#39;s last  name matches this pattern. But if you change this pattern to  5 under score, so 1234 5, followed by a y we should get  these customers. So their last name is exactly  6 characters, we don&amp;#39;t care about the first 5 characters, but all of them end with a y. Now, as another example,  we can replace the first underscore with b, and that means  we want to get the customers who&amp;#39;s last names start with b, and  after b we have exactly 4 characters followed by a  y. Let&amp;#39;s execute this query. So we only have one customer,  that matches this pattern. So this is how we use the  light operator, use the percent sign to represent  any number of characters and an underscore  to represent a single character.  Now this like operator in MySQL is an older operator  but we also have a newer one that is more powerful and it allows us to search for any string patterns. And that&amp;#39;s what I&amp;#39;m going to show you next.  Alright.  Here I&amp;#39;m going to give you two exercises for the like operator.  First, I want you to get the customers who&amp;#39;s addresses contain trade or avenue, and next I want you to get the customers who&amp;#39;s phone  numbers end with 9.   Alright, let&amp;#39;s get started with the first exercise. So  start from customers where address like now here we want to use a search pattern like this.  You want to have tray (?) but tray can be anywhere in the address. So,  we put a percent before and after tray.  Next, we should use the or operator to search for another pattern.  Or address like, let me  put this on a new line that is better, address once again, percent, avenue percent, that&amp;#39;s it.  So, let&amp;#39;s execute this query,  here is the result, you should get the customers with ID&amp;#39;s  2, 9, and 10. If you look at their addresses, all of them have either tray or avenue in their address. Now let&amp;#39;s work on the second exercise, you want to get  the customers who&amp;#39;s phone numbers end with 9. That is pretty easy,  so, let me change our where clause. Where  phone once again we use the like operator and a percent,  followed by a 9. That&amp;#39;s all you have to do. Let&amp;#39;s execute the  query. So, here&amp;#39;s the result, customers with  id&amp;#39;s 3 and 7, their phone numbers, and  9. So this is how we use the like operator. And by the way you can always use the not operator here let&amp;#39;s say you want to get the  customers who&amp;#39;s phone numbers don&amp;#39;t end with 9,  so we simply prefix like with not. Now,  if we execute this query one more time, we get all the other  customers in the database.  In the last tutorial you learned about the like operator in SQL. So as another example  let&amp;#39;s say you want to search for the customers who have the word  field in their last name. So we type out the where clause like this,  where last name, like, percent,  field, percent, so the word field,  can be anywhere in the last name. Let&amp;#39;s execute this query, we get  only one customer, beautiful. Now we also have another operator in MySQL, that is reg x which is  short for regular expression, and regular expressions are extremely  powerful when it comes to searching for strings. So they allow us to search for more complex patterns. Here&amp;#39;s an example. If I want to rewrite this last where clause using a regular  expression it looks like. Where last name  ragexp now here in our string pattern  we don&amp;#39;t have to type out the person&amp;#39;s size we only type  out field so what we have on line 4 is exactly  identical to what we have on line 3, let&amp;#39;s execute this query  we get the same result, beautiful, now here in regular  expressions, we have additional characters that we don&amp;#39;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  just before the word field, that means our  last name must start with field. Obviously, if you execute this query, we don&amp;#39;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 the last name must end with field.  Let&amp;#39;s execute this query, you get the same result as before. Now we can also search for multiple words here, for example let&amp;#39; say we want to find  the customers who have the word field or mac in their last name So, we use a pipe, a vertical bar,  and type out another pattern. Let&amp;#39;s execute this query, so here we have two customers, one of them has the word  mac, the other has the word field and the last name.  Now we can take this to the next level. Let&amp;#39;s say we want  to find the customers who have the words field, or Mac, or rows  in their last name. Let&amp;#39;s execute the query we get 3 customers.  Beautiful. So we use a pipe or a vertical 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 this last name should either start  with the word field, or it should have the word mac in it or it should have the word rows. Let&amp;#39;s execute the query, now we get only two customers, because our customer with the last name brush field doesn&amp;#39;t match this pattern.  However, if we change our first pattern to field  $ and execute the query we get 3 people here,  3 customers. So this is how we can combine multiple special characters when building a complex pattern. Now let&amp;#39;s look at another  example, let&amp;#39;s say you want to search for customers who have an e in their last name.  So these are all the people, alright, now let&amp;#39;s say  you want to make sure that before the letter e, we should either have a  g or an i. So this is where we use square brackets.  And inside the brackets we add multiple characters like g, i, m and that matches any customers who have  ge or ie or me  in their last name. So any of these characters can come before e.  Now, let&amp;#39;s execute this query,  there you go, we only get 2 customers, and the first example before e we have i which is one of the characters  inside the brackets, in the second example, before e we have  a g which is also another valid character before  and once again the square brackets don&amp;#39;t have to be before we could add them after e, any customers who have e  followed by an f or an m or a q in their last  name, can be returned with this pattern,  if we don&amp;#39;t have anyone in the database so this is how we use square brackets,  now we can also supply a range of characters, for example  we can have e and just before e you can have  as many characters from a to h, you don&amp;#39;t have to type them out explicitly like abcdefg, that&amp;#39;s very  verbose, so, we can type out a to h.  And then if we execute this query we get these three people.  So, let&amp;#39;s quickly recap everything you learned about regular expressions in this tutorial. Use a carrot to represent the beginning of a string, so beginning, we use a dollar sign to represent  the end of a string, we use a vertical bar or pipe,  to represent a logical or so we can supply multiple search patterns, we use square brackets to  match any single characters listed in the brackets, and finally  we use square brackets with a  hyphen to represent a range.  So any characters from a to f.  Technically MySQL supports more special characters but quite honestly, the ones that I&amp;#39;ve listed in this tutorial are the ones that you&amp;#39;ll be using 90% of the time. So just memorize these and you&amp;#39;re good to go.  With honesty a lot of beginners find the syntax for regular expressions confusing, so in this video I&amp;#39;m going to give you 4 exercises that I have carefully  designed to help you quickly learn about this syntax. Here&amp;#39;s the first exercise, get the customer&amp;#39;s who&amp;#39;s first names are Elka  or Ambur. And note that this is Ambur with a U.  Now for the second episode return the customer&amp;#39;s who&amp;#39;s names  end with ei or on.  Here&amp;#39;s the third exercise get the customer&amp;#39;s who&amp;#39;s last names start with my or it contains se, and finally  as the last exercise return the customer&amp;#39;s who&amp;#39;s last names contain e followed by r or a u.  So, go ahead and spend 2-3 minutes on this exercise,  when you&amp;#39;re done come back and continue watching.   Alright, let&amp;#39;s knock out the first exercise. So we&amp;#39;ll get  allows, there&amp;#39;s a c here, so let&amp;#39;s start from   regular expression, and here&amp;#39;s our pattern, you&amp;#39;re going to search  for two words either Elka or Ambur.  As simple as that. Let&amp;#39;s execute this query, we should get two customers, there you go, Ambur and Elka. Alright. Now, let&amp;#39;s knock out the second exercise. So I&amp;#39;m going to delete these  we don&amp;#39;t need them anymore. So we want to get the customers  select start from customers, where  last name should end with either ey or  om. So, in the search pattern we type out  ey followed by a dollar sign to indicate the end of a string then we add a vertical bar to supply the second search pattern. So On and once again dollar sign.  Let&amp;#39;s execute this query, oops I forgot to type out regular expression, there you go. So,  Let&amp;#39;s execute this query, and you should get  these four customers with ID&amp;#39;s one  three, five and seven. The first three, five, and seven, the first three, their last names  end with ey, and the last customer, his or her last  name ends with on. Alright, Now, let&amp;#39;s  work on the third exercise. So I&amp;#39;m just going to change the regular expression  here, we want to get the customers whose last names start 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&amp;#39;s execute this query  and we get the customer&amp;#39;s with ID&amp;#39;s  4, 8, and 10 and finally  we want to get the customers who&amp;#39;s last names contain e  so, let&amp;#39;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 so  we have b followed by r or u, that&amp;#39;s one way, or the other way is to use a vertical bar. So b r or bu. These are both valid solutions. So, I hope you knocked out these exercises, in the next tutorial I&amp;#39;m going to show you  how to get the records with missing values.   In this tutorial, I&amp;#39;m going to show you how to look for records that miss  an attribute. For example, if you select all the customers in our database,  you can see that the customer with id 5 doesn&amp;#39;t 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&amp;#39;s say we want to search for all the customers who don&amp;#39;t have a phone. Perhaps you want to send them an email and say hey, your phone is missing in our system. So how can we get these customers? That is very easy? We use the is null operator, so in the where clause, we type out  where phone is null. Now let&amp;#39;s execute  this query, we only get one customer who doesn&amp;#39;t have a phone,  now here we can also use the not operator to get  the customers who do have a phone. So we change the conditions to is not null. Let&amp;#39;s execute the query,  now in the query results, every customer  does have a phone number.  For this exercise, I want you to write a query  to get the orders that are not shipped yet. This is a very useful query  that is used in a  lot of real applications. For example, let&amp;#39;s say you&amp;#39;re an admin for online shop. You want to see the orders that are not shipped yet, so you can see  them all. So write a query, and get these orders.   So here we have the orders table, let&amp;#39;s have a quick look at the data in this table. So if you pay close attention  you see some of these orders don&amp;#39;t have a date. And these orders also don&amp;#39;t have a shipper id which  is a unique number for identifying the shippers. So any order that misses the value for the ship date or shipper ID,  is considered an order that is not shipped. So let&amp;#39;s go ahead and write  a query to get these orders. So back to our query editor  select star from orders where  ship_date is  null. You could also write shipper id is null. They&amp;#39;re both equally correct. So let&amp;#39;s execute this query, and you should get  5 orders. Orders 1, 3, 4, 6,  and 8.  In this tutorial I&amp;#39;m going to show you how to sort data in your sequel queries. So here in your have a query to select all the customers from the customers table, if you look at  the query result, you can see that our customer&amp;#39;s or sorted by id,  so we have customers 1, 2, 3, 4, and so on. This is the  default sort column. But we can always change this using the  order by clause. But first, let me explain  why the customer id column is the default sort column. So first of all the first I&amp;#39;m going to open up the navigators panel on the left side here&amp;#39;s the customers table, now let&amp;#39;s click on this middle icon here that looks like  tool. This opens up our customers table in the design mode,  here we can change our columns we cana dd new column or  remove existing ones or change that name and order and so on.  Now if you pay close attention you can see a yellow key just before  This means that this column is the primary key column.  For this query. So in relational databases every table should have a primary key column, and the values in that column  should uniquely identify the records in that table. So  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 is why when you write a  query against this table, our customers will sort it by id by default.  Now, let me show you how to sort customers by a different column.  So, here in the order by clause, you type out the name of another column, like first name. Let&amp;#39;s execute the query, now we can  see our customers are no longer sorted by their id, instead  they are sorted by their first name in ascending order. Now,  if you want to reverse the sort order, simply type out de,  e, sc, which is short for descending. Now,  you&amp;#39;re sorting the customers in this order, okay,  we can also sort data by multiple columns, for example,  let&amp;#39;s say first we want to sort customers based on their state,  and then within each state, we want to sort them by their first name.  So, we type out multiple columns here, state  and first name. Let&amp;#39;s execute 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 customers  and these customers are sorted by their first name. Let&amp;#39;s have a  close look here, so first we have Ambur and then we have other customers here. Now we can also use the descending argument anywhere here.  For example, we can sort these customers by their state in descending  order. And then sort them by their first name in ascending order  or once, again in descending order. So there are various ways we can sort data. Now one of the differences between MySQL, and  other database management systems, is that in MySQL you can sort data by any columns whether that column is in the select clause  or not. For example, let&amp;#39;s say you only want to select  the first and last name for each customer. Now we can sort the result by any columns in this table, they don&amp;#39;t have to be first name and last names For example, we can sort them by their birthdate,  take a look, so this is a valid query in MySQL, but other databases management sometimes yell at you when you update a query like this.  Now we can also sort data by an alias for example, here in out select clause let&amp;#39;s add the number ten and give it a  alias. As let&amp;#39;s say points, so points is not a valid column in this table, it&amp;#39;s simply an alias  or expression, in this case a simple number. And here we could have a complex  mathematical expression, it doesn&amp;#39;t really matter, we can still sort  data by Alias, so we can order by points  and then first name. Once again this is a valid,  query from MySQL&amp;#39;s point of view. Now one last thing before we finish  this tutorial, I&amp;#39;ve seen some tutorials that teach you how to sort data by calling positions, for example, here we can order it by  1, 2, and that basically means sort the data by the first name,  and then, the last name. So these are the orders of these columns,  if you execute this query, you can see that our customers are sorted by their first  name and then last name. Why this approach works, it&amp;#39;s something that you should avoid. Because if in the future you come back here and add  a new column, in front of the first name column, let&amp;#39;s say  first, date. Now our customers are no longer sorted  in this order. So sorting data by calling positions  produces unexpected results and is something you should avoid.  always sort by column names like first,   Alright, here&amp;#39;s your exercise for this tutorial. In this database, we have this table,  called order items, where we find the items for each order. Now,  I&amp;#39;ve written the query that you cannot see here, because that&amp;#39;s the solution to the exercise  I&amp;#39;m going to give you, that query produces this results. So  we only have the items for the order with ID 2, and we  have sorted these items based on the total price  for each item. So the total price for each item equals quantity times 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 the items for order with ID2. And  sort them by their total price in descending order.   Alright, let&amp;#39;s select everything from order  items, where order id equals 2.  that returns all the items for this order. Now we want to make sure  to sort them by their total price, so,  here in order by clause, we write an expression. Quantity times  unit price, this returns the total price for each item.  And then we add the descending argument here. So,  once again the expression that we use in the order by clause doesn&amp;#39;t have to be  column name, it can be an alias or an arithmetic expression like this.  Let&amp;#39;s go ahead and execute this query. This is what we get  now for clarity, I would like to add another column in the result  So, let&amp;#39;s say quantity times unit _price. We give it an alias like total  price. Let&amp;#39;s execute the query, you can clearly  see, that this data ia sorted by the total rice in descending order.  However, there is a bit of duplication in our query, you have to repeated this expression in two places. So now we can simplify our  by clause, by using an alias that is total price.  And we gwet the exact same result. Net I&amp;#39;m going to show you how to mimic the number of records returned form your queries.   In this tutorial, I&amp;#39;m going to show you how to limit the  records returned from the query. For example, when we return this query  we get all the customers in the customer, table, so we have to  ten customers here. Now what if we only want to get,  the first 3 customers. That&amp;#39;s where we use the limit clause.  Let me show you. So, after from, you type out limit 3, and this will return only the first 3  customers. Now if the argument that we pass here is greater than the number of records that our query produces, we&amp;#39;ll get  the records in the query result. For example, if I pass 300 here,  obviously we don&amp;#39;t have 300 customers in this table, so when we execute this query. We get all the ten customers in this table.  So this is how the limit clause works.  Now here we can optionally supply an offset, and this is very  useful in situations where you want to paginate the data. For example,  let&amp;#39;s say we have a website, and on this website we have a web page for the user to see all the customers int he database. Now for simplicity  let&amp;#39;s imagine, we want to show only there customers per page. So,  what are the customer&amp;#39;s you&amp;#39;re going to have on page 1? We&amp;#39;re going to have  customers 123. On page 2 we&amp;#39;re going to have customers 456 and on page 3 we&amp;#39;re going to have customers 789.  Now, let&amp;#39;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 pick 3 records. So,  we change 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 records  and then take 3 records. Let&amp;#39;s execute this query. Alright, now we get  customers 789.  Now, for your  exercise, I want you to get the top 3 loyal customers. These are the customers that have more points then everyone else.   Alright, first we select everything from the customers table,  now we need to order these customers by their points,  in descending order. So if you look at the query result  you can see that customers are sorted by their loyalty. So,  the most loyal customers come first, now we want to pick  only the first three customers, and that&amp;#39;s where  we sue the limit clause, so limit,  let&amp;#39;s execute this query. And these are the  most loyal customers, customers with id&amp;#39;s 56 and 3. Now, here&amp;#39;s one thing I want you to remember and that is  the order of the limit clause. The limit clause should always  come at the end. So first we have the select clause and then we have from optionally you can have where followed by order by and finally limit. Order of these clauses matter  If you change the order MySQL is going to yell at you. So pay attention to the order when writing your queries.   So far you have only selected columns from a single but in a real  world, we quite often select columns from multiple tables. And that&amp;#39;s what I&amp;#39;m going to show you over the next few tutorials, so on the left side if you put  our orders table. Let&amp;#39;s select all the data here,  in this table we&amp;#39;re using the customer id column to identify the customer  that has placed each order. Now as I have told you before  we don&amp;#39;t store customers information here like their phone number, their email their address,  because this information can change in the future. And if  even a customer has placed multiple orders then we have to change multiple records, you don&amp;#39;t want to do that. That&amp;#39;s why we have separate tables  for customers and orders. Now in this tutorial I&amp;#39;m going to show you how to select the orders in the orders table, but instead of showing the customer id, we showed full name for each customer. So lets go back to our query window. Okay, so,  we want to select everything from the orders table. Now we should combine the  columns in this table with he columns in the customers table. That  is where we use the join keyword. And here we can optionally  type inner join, because we in SQL we have two types of join inner join and over join. You&amp;#39;ll look at outer joins later in this section,  so for now we are only using inner join and these inner keyword,  is actually optional, so we don&amp;#39;t have to type it. So, you want to join the orders table with the customer table. Now, on what basis do we want to join these tables?  Well, here in the customers table, we have this customer id column.  So if you put these two tables next to each other you want to line up the records  such that the customer ID&amp;#39;s are equal. That is where  we use the on phrase. So after we type out a condition, here&amp;#39;s a condition we need to type out,  orders.customer_id. Should be equal to  customers.customer id.  Now, this is getting outside of the screen, so let&amp;#39;s break up the line,  that&amp;#39;s better, so with this query, we&amp;#39;re telling MySQl that hey, whenever you&amp;#39;re going the orders table  with the customers table, make sure that the customer ID column,  in the orders table, equals the customer ID  column, in the customers table. Now let&amp;#39;s execute this query,  look at the result,  since we are selecting everything here, the first few columns are from the orders table, because we have listed that first  now after all the columns in the  customer table. So customer ID first name, last name and so on.  Now let&amp;#39;s simplify the result set and select only  border id, first name, and last name, so query  we select order ID, first  name, and last name. Now let&amp;#39;s execute the query  that is better. So next to each order ID, you can see the  name of the customer that placed that order. Now what if you want to display  the customer ID here as well. Well, let&amp;#39;s put that here and  see what happens. Customer ID, execute the query  we get an error, so if you look at the output window, down at the bottom,  we should see an error saying column, customer id, field this is ambiguous. Now unfortunately I cannot show you this error, because the size of my recording window is smaller then MySQL workbench. But that aside, let me explain why we&amp;#39;re getting this error. Because we have this customer ID  column in both the orders and the customers table,  so MySQL is not sure which table we want to select this column from. That is why it&amp;#39;s saying this column is ambiguous  so we need to qualify this column by prefixing it with a table name.  We can either pick it from order table or  the customers table, it doesn&amp;#39;t really matter, because the values are equal, right? So, in situations where you have  the same column in multiple tables, you need to qualify them,  by prefixing them with the name of the table, okay, now,  we say tot he query one more time, there you go, you have order ID,  customer ID and the full name. Now one more thing  before we finish this tutorial, if you pay close attention we have repeated the word  orders in multipel places, you have it here, as well as  in the join condition, the same is true about he customers table, you have repeated that here. We can get rid of this repetition  and make our code simpler by using an alias. So write  after each table you can do it in alias,  as a short for orders, so by convention we abbreviate the table&amp;#39;s name.  Now, wherever we have orders you should replace that with  o. So here in the join condition, we&amp;#39;re going to replace orders  with o, and also one more time, in the select  clause. There you go. You can also apply an alias for  a customer&amp;#39;s table, call it c, and then simplify  our join condition like this. So 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 with the products table so for each order  return both the product id as well as this name, followed by the  quantity, and the unit price form the order items table. And by the way  make sure to use an alias to simplify your code,   Alright, first let&amp;#39;s select everything from the order  items table, and then join it with the  products table. How are we going to join this table. On order_items. Well actually  let&amp;#39;s just keep this on Alias right away, so we use oi,  as an abbreviation for order items. And p as  a short for products. So oi.product id, should be equal to p or products. product id. And by the way, remember that alias for table, you have to use that alias everywhere. So here I cannot type out product, MySQL is going to yell at me. So let&amp;#39;s use the abbreviation. Alright, this is how we join these tables,  let&amp;#39;s execute this query up to this point. Alright,  so we see all the items form the order items table, followed by  from the products table. Now, you want  to explicit select a few column here. So, to  order items table you want to select order_id.  We take it then we don&amp;#39;t have to prefix it with a table name  because this calumnies not repeated in multiple places, so it&amp;#39;s not ambiguous.  So, let&amp;#39;s make the code shorter, that&amp;#39;s better, now  we want to select the product ID column, but because this column exists in both tables, you have to both prefix it with a column name. Either oi or p it doesn&amp;#39;t really matter. So,  next, we want to select quantity, and finally you want  price. Now actually here, you have this unit price column.  In both tables, so this is the unit price in order items table  and this is the unit price in the products table,  now you might be curious why we have this column in 2 places,  is that the price of product can change, so for each  order item, you want to have the price at the time the user  placed the order. So this is a snapshot of the price  at a given point in time. The unit price that we have for the products table  is the current price right now. This is very important for the  recording, otherwise we cannot calculate the sales properly. So,  because we have the unit price column in two places, in this case we should pick it from the quarter items table because this is the price at the time of now, let&amp;#39;s execute query. So here&amp;#39;s  the final result.  In the real world, when you work as a developer or a data base administrator quite often you will  have to work with multiple databases. In this tutorial I&amp;#39;m going to show you how to combine columns from tables in multiple places.  That&amp;#39;s pretty easy. So, in the SQL store database, you have this tables  that you&amp;#39;re fairly familiar with. Now imagine this products table was 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.  Now technically this is not a good design, you don&amp;#39;t want to have the same table  repeated in multiple places. But for this demo, let&amp;#39;s just imagine  that we don&amp;#39;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&amp;#39;s get started. So select  everything from the order items table,  let&amp;#39;s give it an alias straight away, you want to join this with the products table. This products table is part of the products inventory  database. So we&amp;#39;ll have to prefix this with the name of it&amp;#39;s database.  So, we type out SQL inventory. Now once again we can give this an alias like p,  then type out our joint condition, so oi.product  id should be the same as p.product id.  Let&amp;#39;s run the query there you go, so we successfully joined  tables across multiple databases. Now note that we&amp;#39;re prefixing the products table with the name of the database because  the current database that we&amp;#39;re writing this query with is the SQL stored  database. Take a look, in the navigator panel, in the SQL  store database is displayed in full. Because earlier, the  root we use statement to select  a database, that will SQL store. Now what if we select  the SQL inventory database. So, let&amp;#39;s see what happens SQL  inventory, now because we have multiple statements we have  to terminate this with a semi colon. Now we want to select everything with a  order items table. But we don&amp;#39;t have this table inside of this  database. So now we&amp;#39;ll have to prefix this table  with the name of it&amp;#39;s database, that is SQL underline  story. Let&amp;#39;s execute the query, okay,  everything works, beautifully. So here&amp;#39;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&amp;#39;s take a look at the  data int he employees table.  There you go. So here we have these columns,  employee id, first name, last name, salary  and reports 2. This is the id of  the manager for this person or this employee. Now once again,  you don&amp;#39;t want to repeat the managers information here, like the phone number, the address because this information can change in the future, so we are only using  your identifier 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 look on the website, here is the ID of that manager which is another employee. That was the manager for this employee. We don&amp;#39;t have any values here so  the value for the sale is null. So this employee doesn&amp;#39;t have a manager  and that means they are the CEO. So let&amp;#39;s go ahead and  write a query to join this table with itself so we can select the name of each employee and their manager. Back to our query window,  first we need to select the SQL hr database.  Next, we select everything from  the employees table, we give an alias like b now we need to join this table with itself. So once again we type  out the 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&amp;#39;s type our our joint condition.  So from the employees table, we need to join  the reports_2 column. To the manager  table which is basically an employees table itself, and  that is employee_ id. Now let&amp;#39;s execute this query and see what we get.  So, we see all the columns from the employees table  repeated the first set of columns, represent the information about the  employees, and after that, we have the information about the managers in this case we have only one manager in this table.  But with this technique we can easily create an organization trial. We can have  a hierarchy of managers. Now let&amp;#39;s  only the name of the employee and the manager. So,  right here, since every column in the employees table is  repeated twice we need to prefix each column with a table name.  For example, from the employee&amp;#39;s table you want to get employee ID, as well as the first  name, and then for the managers table, we want to select  the first name as well. So every column  should be prefixed with a table name or more accurately the alias because all these columns exist in two tables, right?  Let&amp;#39;s go ahead and execute this query, so,  this is what we get, employee id, and here&amp;#39;s the managers  first name. We can improve this query by giving an alias to this column. Because it doesn&amp;#39;t make sense to have two first name columns.  So, let&amp;#39;s give an alias to the third column,  manager. Now, let&amp;#39;s execute it one more time.  And, here&amp;#39;s the end result. So, we have the empty ID first name, and object. So,  joining a table with itself is pretty much the same as joining a table with another table. The only difference is that we have to use  different alias&amp;#39;s. And we have to prefix each column with an alias.  This is what we call a self join. Next, I&amp;#39;m going to show you how to join more then two tables.  In this tutorial,  I&amp;#39;m going to show you how to join more then two tables in writing a  query. For example, 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. So the name of the  status&amp;#39;s are not stored in this table, they are somewhere else in the  order status&amp;#39;s table. Let&amp;#39;s have a quick look here.  Our orders can be either processed, shipped or delivered. And these are the identifiers for each of these status&amp;#39;s. Now back to our orders table. In the status column you store status id. So now we should write a query to join the orders table, be two tables. The customers table and orders  status&amp;#39;s table. The result of this query is going to look like this.  So for each order we see the order id, the date,  the first and last name of the customer, and finally the status of the order.  This is a beautiful report we can create for our users. So 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&amp;#39;s going to be the alias,  next we need to join this with the customers table.  On, that customer id should  be equal to see the customer id. Nothing  new so far. Exactly like before. Now here we can write another joint keyword. To join the others keyword  order status&amp;#39;s table. So we type out order status&amp;#39;s.  And also give it an alias, OS,  what is our join condition? Well, back in the  orders table, here we have the status column. So the  value in this column should be equal to the order status ID,  column in order status&amp;#39;s table. Right? So back to the query, so orders table.status should be equal to order status&amp;#39;s.order status ID. Make sure to get the name right, otherwise you&amp;#39;re going to get an error So this is how we can join three tables.  Now in the real world as you work on more complex problems, you&amp;#39;ll end up joining  even ten tables. So this is not uncommon in the SQL world.  Now let&amp;#39;s go ahead and execute 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 so complex and hard to extract information.  from. So, let&amp;#39;s explicitly select  a few columns here. From the orders table, you want to select  the order iD column, as well as the order date. Then, from the customers table, we want to select  the first name, and last name. And finally form the order status&amp;#39;s, we select the name  column. So we can give this an alias like status that&amp;#39;s better. Let&amp;#39;s execute the query one more time.  So, here&amp;#39;s the end result, we have order ID,  order date, the name of the customer, followed by the status of the order.   Alright, for exercise, take a look at the SQL invoicing database. Here we have this table, payments,  and these are the payments that each client has made towards either invoice  Let&amp;#39;s take a look at the data, so we have these columns,  like client id, that identifies the client, so we can join this table  with the client&amp;#39;s table to see the name of the clients. Next we  have invoice ID, we also have date,  payment method. So similarly we can join this table with the payment method  table here, let&amp;#39;s have a look at the data in this table, these are the  payment methods, credit card, cash, PayPal, wire transfer. So,  back to the payments table, I want you to write a query and join this table  with the payment methods table as well as the client&amp;#39;s table.  Produce a report that shows the payments, with more details, such as the name of the client,  and the payment method.  Alright, first we need to use, the SQL invoicing database, now we can  select everything from the payments table which we call e, next we need to join this with the client&amp;#39;s table which we call c, on p.clientid  should eb equal to c.client id. Let me double  check the column name to make sure I got it right, so back to the payments table the column is called client item. You also have a column called  payment method, that we should join to the payment  method ID column of the payment methods table.  So, back to the query, once again we use a joint  statement here, join with payment methods, we give it an alias  pm, on p. payment _method should be equal to pm.  payment method id. Make sure to type it out correctly  otherwise you&amp;#39;re going to get an error. So let&amp;#39;s go ahead and execute the query  up to this point, finally let&amp;#39;s hand pick  the columns that make the most sense. So,  from the payments table, let&amp;#39;s select the date, followed by invoice id,  what else do we have here. So we have client id, invoice  id, date, and now the payment method, I&amp;#39;m going to pick the amount  column from here as well, so back to the query, p  .amount. Now we need to add information about the client.  Let&amp;#39;s take a look at this table, clients,  so here we have columns like name, address,  city and so on. All we need here is the name column. So back to the query,  of the client&amp;#39;s table let&amp;#39;s select the name column and  finally form the payment method table, let&amp;#39;s select, what is that column called it&amp;#39;s called name. So back to the query pm.name. So here&amp;#39;s the end result.  Now we can put this column in any order that we want, it doesn&amp;#39;t really matter,  let&amp;#39;s execute the query, and make sure everything works.  So, on this date, on this invoice,  we have a payment for this amount by this client using a  credit card.  Ina let he examples you have seen so far,  we use a single column to uniquely identify the rows  in a given table. For example, for example, in the customers table,  we have this customer ID column which uniquely identifies  the rows in this table. But there are times where we cannot use a single  column to uniquely identify columns in a given table. For example, look at the order items table. In this table, we have columns like order id, product id, and so on. 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. So we cannot use this column on it&amp;#39;s own to uniquely identify each record.  The same is true for the product id. The values for this column  are also duplicated. So in this table we use the combination of the values in both these columns to uniquely identify  each oder item. 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 unit price. So if we use the combination of the values in both these values,  we can uniquely identify each order item, in other words, we don&amp;#39;t have  two records for order ID 2, and product ID 1,  we only have a single record for that item,  now let&amp;#39;s open this table in the design mode. So over here we  find this middle icon that looks like a tool. Note that this yellow  key that represents the primary key exists on both these columns.  This is what we call a composite primary key. The composite primary key  contains more then 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, that we use to keep notes in each order item. Let&amp;#39;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 order ID and product ID.  you learn that the combination of these two columns uniquely represents  and order ID. So here for order number 2,  for product number 1, we have two notes. Now let me show you how join this table with the order items table. So, back to our  query, you can see that I have already selected the SQL store database, so I&amp;#39;m not going to type out a U statement. Alright, let&amp;#39;s select  like everything from the order  items table. Give it an alias, now we need to join this with order item notes, also  we give it an alias. How are we going to join these tables?  Based on two columns, back to the order items table  these are the columns that we need to use in our joint condition.  So, in the order items table we have this  order ID column, these should be equal to the same column in order item notes table. So,  in.order id. But this is not enough,  we should also join these tables based on the product ID number.  So, we type out and, and then type out the  second condition, so order items. product ID should be equal to 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&amp;#39;m going to talk about the implicit joint syntax in MySQL.  So here we have a basic inner join, we&amp;#39;re selecting everything from the orders table,  joining it with customers table on, orders.customer id. Equal to customers.customerid. 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 type out multiple 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&amp;#39;m going to copy this from here,  type out the where clause, and paste the condition. These two queries,  are equivalent. What we have here is called implicit  joint syntax. Now even though MySQL supports the syntax  it&amp;#39;s something that I suggest you not to use, because if you accidentally forget  to type out the where clause, you will get a cross join. Let me show you what I mean. So first I&amp;#39;m going to delete the first query.  And execute this  so we get ten records because we have  10 orders in this database. So far so good. What happens if you accidentally type out the where clause. Instead of 10 records we&amp;#39;re going to get  probably 100 records. Because every record in the order table, is now joined with every record in the  customers table. This is what we call a cross turn. Now later in this section I&amp;#39;m going to talk about cross joins in more detail, but what I want to talk about  in this tutorial, is that it&amp;#39;s better to use an explicit  joint syntax, so, we use, join  because this syntax forces you to type out the joint condition, if  you simply join orders with customers, without typing  the join condition, you&amp;#39;re going to get a syntax error. So to recap,  be aware of the implicit join syntax, but write all of  your joints using the explicit syntax.   Earlier in this section I told you that in SQL we have two types of forms.  Inner joins and outer joins. And so far you have  only seen examples of inner joins. And I also told you that this inner keyword is  optional, so whenever you type out a join, you&amp;#39;re using an inner join.  In this tutorial, we&amp;#39;re going to look at outer joins and the problems they solve. So, let&amp;#39;s start by writing a query that uses an  inner join, and then we&amp;#39;ll convert hat inner join and outer join.  So, select everything from the customers table  join it in the orders table,  on c.customer id should be equal to  o.customer ID. Pretty basic  right? Now for clarity, let&amp;#39;s pick a few columns from these two tables .So for the customers table I want to pick customer  id. And first name,  and from the orders table, I want to pick order id.  Now finally, let&amp;#39;s sort the results so we can clearly see what  we get. So order by c.customer  id. Let&amp;#39;s execute that query and see what we get.  So, here&amp;#39; the result. For customer number 2 called  inis or inis whatever, you have 2 orders, order 4,  and order 7. Similarly for customer number 5, we have  2 orders and so on. Now there is something missing in 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 customers table, you can see that  you have other customers like customer number 10, customers number 3, and so on.  Currently we don&amp;#39;t have any orders for these customers, and that&amp;#39;s the reason  we don&amp;#39;t see them in this results set. But what if you want to see all the  customers whether they have an order or not? That&amp;#39;s when we use an  outer join. Let me show you how that works. So back to our query  the reason we only saw customers who have an order was because  of this join condition, When joining these two tables, you are only  returning records that match his condition. So for a given customer, if we do have an order, that record is returned. But as you saw a second ago, some customers don&amp;#39;t have  an order. So for those customers this condition is not  valid. And that is the reason 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 the records from the left table, in this case customers are  returned whether this condition is true or not. So we  get all the customers, and if they do have an order, we&amp;#39;ll see the order id  as well. Let&amp;#39;s execute this query and see what we get. So,  there you go. Customer number 1,  doesn&amp;#39;t have an order and that&amp;#39;s why we get null in this cell.  Customer number 2 has two orders, 4, and 7, customer  number 3 also doesn&amp;#39;t have an order so we get null for order id.  This is the result of a left join. So back to our query  when we use a left join, all the records from the left  table are returned whether this condition is true or not.  Now what if we use a right join. In this  case, all the records from the orders table are returned whether this condition is  true or not. Let&amp;#39;s execute this query and see what we get.  So, we get he 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&amp;#39;t see all the customers  we see all the orders. Now if you want to use a right join, and  still see all the customers, you need to swap the order of these tables. So,  we put the orders table first that&amp;#39;s going to be our left table.  And then, we put the customers on the right side,  so now with this query we&amp;#39;ll return all the records from the right table  which is the customers table. We execute this we get  all the customers whether they have an order or not.  Beautiful. Now one last thing before we finish this tutorial,  I&amp;#39;ve seen developers use the outer keyword here. So either  right outer join or left outer join. But technically  the outer keyword is optional just like the inner keyword, so you don&amp;#39;t have to type it out.  So I&amp;#39;m going to remove this to make this code shorter and easier to  understand. So to recap, if you have the join keyword directly, and  if you do a left or right join you&amp;#39;re doing a  inner outer join.  Here&amp;#39;s 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 that I picked from the  order items table, so here we need to join the products table.  With order items table. So 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&amp;#39;m doing an outer join, so,  product number 7, has never been ordered, but still exists in the result.  Is it null, or the quantity? So go ahead and write an outer  join, or use this result.  Alright, first we select  everything from the products table and then  to a left join with the order items table.  Our join condition is p.product  id equals oi.product id.  So because we&amp;#39;ll get all the products  in the products table whether this condition is true or not. If you have never  been ordered, you still see them in the result. Now, let&amp;#39;s  pick a few columns for clarity. So p.productid  p.name and oi. quantity. That&amp;#39;s it. Let&amp;#39;s execute the query. We get the same result as before. So all the products are here.  And product number 7 has never been ordered so we see  null for the quantity.  Similar  to inner joins, we can use outer joins between multiple tables. Let me show you. So here&amp;#39;s the query that we wrote in the last tutorial, you are doing a  left join between customers and orders tables.  So when we execute this query, you get all the customer  whether they have an order or not. If you have an order you 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. So now let&amp;#39;s join  orders table with the shippers table to display the name of the shipper in the result.  So back to our query, after this left join,  let&amp;#39;s write another join, here I&amp;#39;m doing an inner join.  So let&amp;#39;s inner join the orders table with the shippers table.  Join shippers, we call it sh, on  what is the join condition. Well, oh.shipper  id, should be equal to sh.shipper  id. Alright? So in this query we have  a left outer join and inner join. Let&amp;#39;s see  what we get, alright, we only  see 5 records. But you have more orders. So here you have the same problem  we have before. Some of our orders don&amp;#39;t have a shipper, and that is why  they are not returned here. In other words, this join condition is not true, for some of our orders. So back to the orders table,  a an example, this first order doesn&amp;#39;t have a shipper shipper id is null, and that is why it is not returned with a query result.  So to solve this problem, you should use a left join, you want to make sure that all orders are returned whether they have a shipper or not.  So back to the query, you need to replace this inner join  with the left join, so let&amp;#39;s execute the query and see what happens.  Now we should have quite a few more orders, there you go, now to make this example more interesting, I&amp;#39;m going to add the ship -per name here. So back to our select boss, let&amp;#39;s add a new column, shipper.name.  Now we can give it an alias like shipper let&amp;#39;s execute the query and here&amp;#39;s the result. So we get all the customers whether they have an order or  not. And for those who do have an order, we get all the orders whether we have a shipper 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 or the right join, you just have to swap the order of the tables. However, as a best practice of what to avoid using right joins, because when you&amp;#39;re joining multiple tables and you have  left and right and inner join, things get really complex. So,  someone else reading your code, will have difficulty visualizing how you&amp;#39;re joining these tables.  As an example, if you have a right join here and then a left join  after, it will be harder to visualize how these tables are getting joined.  So, 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, the first name of the customer the shipper and we can see that some of our orders are not shipped yet.  So here we have null, and here finally, we have the status.  So go ahead, spend 2-3 minutes on this exercise, when you&amp;#39;re done continue  watching.  Alright, let&amp;#39;s select everything  from the orders table, now you should join this with customers, on, hold.customerid.  Should be equal to c.customer id. Here I&amp;#39;m using an inner join, because every order does have a customer. So,  this condition is always valid, it doesn&amp;#39;t matter if we use a left join or a  inner join here, now, okay? Now before going any further let&amp;#39;s  take our columns. So from the orders table, I&amp;#39;m going to pick order id.  Followed by order date. And then,  customer.firstname.  Which we can optionally rename to customer.  Alright, next we need to select the shipper. So, we join the result with the shippers table,  on order.shipperid equal  to shipper.shipperid. However, if we use an inner join here, because some of our orders don&amp;#39;t have a shipper, we are only  going to see the orders that have been shipped. Let me show you. So,  for clarity, I&amp;#39;m going to add the shippers name here, so shipper .name as shipper. Let&amp;#39;s execute the query  there you go, we only see the orders that have been shipped.  But we want to see all the orders, right? So, we need to  change the second joint to a left join.  So all orders are returned whether they have a shipper or not.  Let&amp;#39;s execute the query one more time,  there you go. Now we see all the orders from number 1-10.  Beautiful. Finally, we need to add the status comment here.  So, we need to do another join here, join with order status&amp;#39;s, which we aggregate  as os, on oh.status equals  to os.orderstatusid.  We can see that I have designed our database such that sometimes our column names  are exactly identical, but in other cases they don&amp;#39;t match.  So in order table we call this column, status, as opposed to  order status id, and this is deliberate because a lot of  real databases are like that. Alright, now let&amp;#39;s add the status name here, so order, status.name as status. Execute the query,  and we can see all the orders here,  for each order we have the data, the customer, the supper and the status.   Earlier, we talked about self joins in SQL. So here in the SQLhr database, we have this employees table, we rote a query to get  all the employees and their manager. So here we have this column, reports  to that specifies the manager for each employee. So let&amp;#39;s go back and rewrite this query to get all the employees and their manager.  Back to our query editor window, first let&amp;#39;s use a SQL hr database. Then select everything  from the employees table, we give it an alias and then join it with itself.  So this is what we call a self join. Now we&amp;#39;re going to use a different alias  like m for managers. Now, what is the joint condition e.reports to, should be equal to  m.employee ID, right?  Now for clarity, let&amp;#39;s pick only three columns, so either the id  employee.first name.  And m.firstname which we rename to  manager. Alright, let&amp;#39;s execute this query and see what we get.  So, here&amp;#39;s the result  as you can see all these employees have the same manager.  However, there is something missing here. We don&amp;#39;t have a record for this person, this  manager himself. So what is missing is a record where  we can see the employee id for this person, their name and their  manager which should be null because this person is the CEO or the  head of the company. But why is this happening? The reason is,  our inner join, because this condition we have here will only  return people who have manager. We can solve this problem by using a  left join. So, we do a left join, because we want to get every employee in this table whether they have a manager or not.  Okay, now let&amp;#39;s execute the query one more time.  There you go. Now we have a record for this person  the manager, as you can see this person does not have a manger, that&amp;#39;s why we have null here.  Back to our SQL store database  here we have a simple query that joins the orders table,  with a customers table. And here&amp;#39;s our join condition. We have several examples  of this before. Now as our queries get more complex  these joint conditions get in the way, they make our queries hard to read, but the good news  is that in MySQL, we have a powerful feature for simplifying  these queries. If the column name is exactly the same  across these two tables, we can replace the on clause.  With a using clause which is simpler and shorter.  So I&amp;#39;m going to comment out this line and instead type out using  in parenthesis, we type out the column name, that is custom.  customer id. What we have in line 7 is exactly identical to what we have  on line 6. That is shorter and easier to read. So,  let me delete this line. We can add another join statement here to join the orders for the shippers here, so join with  shippers using  shipper id. In both these tables we have a column with the exact  same name. Alright, now let&amp;#39;s execute this query  this is what we get, you have the order id followed by the  first name of the customer. Let&amp;#39;s add a new column here,  so, I&amp;#39;m going to add sh  .name that is the name of the shipper,  now obviously because some of our orders are not shipped, we have to replace this  inner join to the left join. So we can use the using keyword with both inner and outer joins. Let&amp;#39;s execute  the query one more time. There you go.  Now, we have the name of the shipper next to each order, beautiful,  however, we cannot use this technique to join the result with the  order status&amp;#39;s table. Because in the orders table we have  this column called status, but in order status&amp;#39;s table, this column has a different  name. It&amp;#39;s order status id. Let me show you. So,  order status&amp;#39;s. columns, there you go, order status id.  So the using keyword only works if the column name is  exactly the same across different tables. Now what if we have multiple  columns in our join condition. For example, earlier we talked about  tis 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 order item notes table. In our join  join condition, we should compare both these columns with their corresponding  columns in the order item notes table. So let&amp;#39;s quickly  write that query and then simplify it with the using keyword.  So, select everything from  order items, now, join it with order  item notes on  so here we need to compare oi.order  id with yn.orderid, and  oi.productid= to oin.productid. This join  condition is kind of messy, it&amp;#39;s hard to read this query. Now let&amp;#39;s simplify this query with the using keyword. So we type out using. In parenthesis  we add both columns and then separate them using a comma.  So, ordered and product  id. Isn&amp;#39;t that better?  Now, for our exercise, back to our SQL invoicing database write a query to select the payment  from the payments table and produce something like this. So in this table we have the date  the client, the amount and the payment method, we can see.  On what date who has paid how much using what payment method.   Alright, I&amp;#39;m going to use the SQL invoicing database, and then select everything from the  payments table, join it with the clients table.  Using client id because in  both these tables, you have the client id table. Next we need to join this with  payment methods, however, they  column name between these two tables is different, so in the payment  table we have a column called payment method. But in payment  methods table, our column is called payment method ID. So here we  cannot use the using keyword, and we&amp;#39;ll have to use the on clause. So on p.payment  underline method equals pm.  paymentmethodid. Now let&amp;#39;s pick our columns.  So, payment.date client.name.  And we rename this as client, next we pick m out, and finally, the payment method,  so, let&amp;#39;s rename that to payment_method  and execute 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 tales, it&amp;#39;s called a natural join, and it&amp;#39;s easier to code, but it&amp;#39;s not something to recommend, because sometimes it produces unexpected results. But let me quickly show you how it works in case you see it somewhere so at least you&amp;#39;re familiar with it. So back to the previous example,  let&amp;#39;s select everything from the orders table, that we should do a natural join with the customer&amp;#39;s table.  Now with this natural join, we don&amp;#39;t exactly specify the column name.  So the database engine will look at these two tables and it will join  them based on the common columns. The columns don&amp;#39;t have the same  name. And that is the reason this query is shorted right.  So, for clarity, let&amp;#39;s pick a couple of columns here, o.order  id and c. let&amp;#39;s say first name.  Let&amp;#39;s execute the query, there you go.  so we see all the orders and the customers replace them.  So natural joins are really easy to code but they can  be a little bit dangerous, because we&amp;#39;re letting the database engine  guess the join, you don&amp;#39;t have control over it. For this very reason,  natural joins can produce unexpected results, and that&amp;#39;s why I discourage you to use them.  In this tutorial,  were going to look at cross joins in SQL. We use cross joins to mine or join every record from the first table, with every record,  in a second table. Here is an example, let&amp;#39;s select  everything from the customers table to  now here we do a cross join with the products table.  So every record in the customers table, will be combined with  every record in the products table. And that is why we don&amp;#39;t have  a condition here. Okay? So this is what we call a cross join  now for clarity let&amp;#39;s pick a couple of columns like see that first name we rename it as customer and then product.name which we rename to  product. Also, let&amp;#39;s sort the result  by customer. first name.  Now, let&amp;#39;s execute the query, here&amp;#39;s the result of the cross join,  so first we have amber as the customer, and here are all combinations of amber with different products.  Next we have Barbara or whatever it is, and again we have the combination of this custom  with all the products. Now in this particular example, it doesn&amp;#39;t really make sense to use a cross join, a real example for using  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 combine all the sizes with all the  colors. That is when you use a cross join.  Wha we have here is called the explicit syntax for cross join, you also have the implicit syntax which looks like this. Instead of typing out the cross join, you type out multiple tables in the  from clause. So customers and orders.  Both these queries produce the same result. But I personally prefer to use  the explicit syntax because it&amp;#39;s more clear.   And here&amp;#39;s a simple exercise for you. Do a cross join between shippers and  products. First do it using the implicit syntax,  and then using the explicit syntax. It&amp;#39;s pretty straight forward, I just want you to  get your hands dirty in the code and get used to this syntax.   Alright, first I&amp;#39;m going to use the explicit syntax, and then I&amp;#39;m going to place the syntax. So let&amp;#39;s start by selecting everything from  2 tables, shippers and products,  now for clarity I&amp;#39;m going to pick two columns, shipper. name which we rename to the shipper. And product.name which we  order everything by  shipper Let&amp;#39;s execute the query, this is what we get. So the combination of all shippers and all products beautiful,  now let&amp;#39;s use the  explicit syntax, so we select everything from the base table,  in this case shippers and then do a cross join with products.  That produces the same exact result.   We covered everything about joins, you learned  that with joins we can combine columns with multiple tables, but  in SQL we can also combine rows with multiple tables, and this is extremely powerful. And let me show you how this works. First we have  a quick look at our orders 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 the other orders were placed in previous years. Now let&amp;#39;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 is  going to be active, and if the order is placed in previous years, you want to label it  as archives. So, let&amp;#39;s change our  query and change our condition here. First you want to get all the orders in the current year. So where 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 hard  coded 2019. So if you execute this query next year, we are not  going to get the right result. But don&amp;#39;t worry about this for now. Later in the course I will show you how to  get the orders in the current year, without hard coding a date here.  So, let&amp;#39;s execute this query, now we get  only one order. Let&amp;#39;s hand pick a couple of columns here.  So, order id,  and order date. And also I want to add a string,  literal here, like active. Right?  Let&amp;#39;s execute this query, this is what we get. We get 3  columns, order id, order date, and active, and in this column,  argument we have this string value. Active. Now let&amp;#39;s rename this column to status, alright? And execute the query, that is better,  now we want to write another query similar to this that will return the order  in the previous year, but with a different label, archive.  So, to save time, I&amp;#39;m going to copy this few lines and paste them right after our first select statement. Now note that here we have a syntax error, because we didn&amp;#39;t  terminate the first select statement with a semi colon, but don&amp;#39;t worry about it, we&amp;#39;re going to get back to this in a second so for the second  query, we want to return a different a different label, archive, and we want  to change our condition to less then  2019. Now, select only these few lines.  And execute this query, either by clicking on this icon here,  or using the keyword shortcut you learned earlier in the course.  There you go, here are all the  orders from the previous year, with the label archive.  This query returns 9 records. The previous query returned 1 record.  now using the union operator we can combine data from these two queries, so, in between  our select statement we type out union  now let&amp;#39;s execute the query one more time, so here&amp;#39;s our first  order in the current year that is active and below that we have  the orders in the previous years. So using the union operator we can combine records for multiple queries. Now in this example, both our queries are against the same table, but we can also have queries against different  tables and then combine the result into one result set.  Let me show you another example. So I&amp;#39;m going to delete everything here. Let&amp;#39;s select the first name,  from the customers table, and then we can union that with select  the name from the shippers table.  Let&amp;#39;s execute the query&amp;#39;s in one result set we can see all the customers and the shippers. Now as far as  I know, there is no real world care for this particular query. But what I Want to point out is that with union we can combine results from multiple  query&amp;#39;s. These query&amp;#39;s can be against the same table or different  tables. In your database, you can have a table like archive orders, and another table like order  and then you could combine all the archive and active orders into one  result set. Just remember, that the number of columns that the query  returns should be equal, otherwise you&amp;#39;re going to get an error. For example,  let&amp;#39;s select the first name and last name from customers  and then union that with the name of  shippers. When we execute 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&amp;#39;t know how to combine these records.  And one last thing before we finish 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 first name, and that&amp;#39;s why this column is called  first name. If you change the order of these queries,  and move this union up here, now let&amp;#39;s run this query  as you can see our column is called name.  So whatever we have in this first query is used to determine the name of  columns. Here we can also rename the column to full name.  There you go.   Here&amp;#39;s your exercise for this tutorial. Write a query for this report.  So here we have four columns, customer ID, points, and  title. Now as you know we don&amp;#39;t have this column in the customers table,  so we have calculated the values of this column, based on  the points each customer had. If they have less then 2,000 points,  their type is bronze. If they have between 2,000 and  3,000 points they are silver customers, and if they have more then  3,000 points, they are gold customers. Also note that here we have sort  the result by the first name. So go ahead and spend two minutes to write this query.  Alright, first let&amp;#39;s get the bronze customers,  so select everything from customers where  points is less then 2,000.  Now here we want to pick 3 columns, customer id,  first name, end points. And finally we  add a new column to a string literal, bronze  let&amp;#39;s run this query and see what we get. So these are all the bronze  customers, but the name of this column in bronze, we don&amp;#39;t want that. So, let&amp;#39;s rename this to type. Now this is off the screen so  I&amp;#39;m going to break this up into multiple lines  that makers our query bigger and easier to read.  There you go, let&amp;#39;s run the query one more time, now the column is called type.  Beautiful. Now we should do union  and repeat this query, but extract the silver customers.  So I&amp;#39;m going to paste this query here and then make a  couple of changes here, I&amp;#39;m going to replace bronze with silver,  and change the condition to between 2,000  and 3,000 let&amp;#39;s run our query, see what we get  so, we have all the bronze customers first,  followed by all the silver customers. So the order of these records is based  on our queries. In our first query we got the bronze customers, which is why  they are listed first. But this is not what we want. You want  to order the result by the first name of our customers. So,  let&amp;#39;s apply an order by at the end so order by first name. Now there is one more piece remaining.  You should do a union one more time and write a query to get  the gold customers. SO I&amp;#39;m going to select these few lines.  And paste them here.  Now let&amp;#39;s change silver to gold,  and the condition to (?) greater then 3000.  And finally we do an imported by. Let&amp;#39;s run the query one more time.  And here&amp;#39;s the end result, our customers  are sorted by first name, bronze, silver and gold customers.  In this section I&amp;#39;m going to teach you how to insert, update, and delete data. Before we get started  let&amp;#39;s have a closer look at our customers table. So click on  this middle icon to open this table in the design mode.  What you see here might look a little bit intimidating at first, but trust me  it&amp;#39;s so easy, and in this tutorial, I&amp;#39;m going to explain exactly what we have in these columns. So on the left side, you can see the column  next to that you can see the data type for each column, so our customer id column  can only accept integer value. Integers are whole numbers like 1234 and so on. You don&amp;#39;t have decimal points, etc.  First thing is a var chart which is short for variable  character. And in parenthesis, you can see 50, that basically  means in this column you can have a maximum  of characters now if the name of a customer is only 5 characters long the only  store those 5 characters. So even though the max  length for this column is 50, you&amp;#39;re not going to waste the space if this customer  name is less then 50 characters. That is why here we have  4char which is short for variable. In contrast we have  another data type that is character. If you had character 50 here,  and the name of the customer was only 5 characters long, MySQL  will insert additional 45 spaces to fill this column.  So this is a waste of space. So as a best practice,  Most oft he type we use var char to store strings  or textural values. Now here on the right side we have this column  pk, which is short for primary key. So customer  id is marked as the primary key, and that is why we have this yellow key here,  so the values in this column uniquely identify each  customer. Next to that we have nn which is short for not null and that determines if this column can except null values or not.  In this case, every customer record, must have the customer id.  First name, last name, as well as these other attributes.  But birthdays and phone are optional. So in these columns we  can have null values, Now we have another calling here, ai,  which is short for auto increment and this is often used  with primary key columns, so every time we insert a new  record in this table, we let MySQL or our database engine insert  a value in this column, so essentially it gets the customer id  for the last row, and it will increment it by 1, by the time you serve a new record. So if you look at the data you can  see that currently we only have 10 customers here. So if you have a new customer here,  MySQL will sign 11 to the new customer, okay? And finally here we have another column that specifies  the default value for each column. For example, for birthday  and phone columns, the default values or null,  So if you don&amp;#39;t supply a value MySQL will supply the null values  for these columns. Similarly we have another null value,  for the points column, so if we don&amp;#39;t supply the points for a customer, MySQL will use 0, now we have a few other columns here which are not important  at this stage, you will learn about them later in this course. So now  you understand the attribute of each column, let&amp;#39;s go ahead  and insert data into this table.  In this tutorial,  you&amp;#39;re going to learn how to insert a row into a table. For that we&amp;#39;re going to use  the insert into statement. Where are we going to  insert this row? Into the customers table, so we type out  the name of the table here followed by the values clause.  And here in parenthesis we supply the values for every  column in this table, so back to our table definition,  these are all the columns, first we need to supply 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 don&amp;#39;t supply a value,  MySQL will generate a unique value for us.  So we can go back to our statement and either assign  an explicit value or use default to let MySQL  take care of generating this value. This is the preferred approach. Because if we use this value  an explicit value like 200 it is possible that you might have  another customer with the same id. So when you execute the statement, you&amp;#39;re going to get an error.  Because you cannot have duplicate values in this column. Every value should be unique, so here we&amp;#39;re going to use the default keyword to let you generate a unique value for the customer id, and after that we need to  supply a value or the first name and last name columns.  So, let&amp;#39;s say Jon Smith.  Note that I have enclosed these values with quotes, because as I have told you,  string and for in SQL, we should always enclose string and values with quotes. Either single or double quotes, okay?  Now, what else? Back to our customer table,  after the last name you have birthdate, however,  as we can see, this column is optional because this check box is not checked. So  here we can use null or an explicit value. Null means the absence  of a value. So back t our statement, you can type out  the birthdate, like 1990 January 1st or  you can use the null keyword to leave out this value.  Now in this demo, I&amp;#39;m going to use a value date,  now to make this code cleaner and more readable, I&amp;#39;m going to break it up into  multiple lines. That&amp;#39;s better,  now back to our table, next we have phone and phone is  also optional, because this checkboxes not checked, and null is the  default value for this column. So here we can explicitly pass  null, or use the default keyword and then put null into this  column. It&amp;#39;s exactly the same. So, back to our  statement, you can pass null or default.  Both these keywords will have the same result. In this case I&amp;#39;m going to use the null  keyword. Alright, let&amp;#39;s have one more look at our table,  next we have 4 more columns that are  required, so address, city, state, and points. And note that points  has the default value of 0, so we can either use an explicit value like  total value like 200, or use the default keyword and let MySQL generate 0. So, back to our statement, let&amp;#39;s type out  an address, it doesn&amp;#39;t really matter.  Followed by a city, and a state, let&amp;#39;s say california,  and finally points, again we use explicit value  or default, so this is how we can insert a row into  a table. However, in this example, you&amp;#39;re only  supplying values for first name, last name, birthdate and  these address fields. So you&amp;#39;re leaving out phone number  the customer id and the points. So there&amp;#39;s another way  to write the statement. Let me show you. So after the table name you can  optionally supply the stuff columns that you want  to insert values into. In this case first name,  last name, birthdate  and once again I&amp;#39;m going to break up this statement into multiple lines  So 3 more columns,  address, city, and state,  so these are the 6 columns that we&amp;#39;re going to supply values for. With this change, we don&amp;#39;t have to use these default  or null values, we only supply values for these columns.  So I&amp;#39;m going to remove default from here, and null, and finally  this last default keyword.  So, the 6 values that you supplied here are used for the 6th column.  Now with this change, you can also reorder the columns.  We don&amp;#39;t have to list them in the same order, they were defined  in the customers table, for example you can put the last name first,  and then obviously we should also swap the order of these values,  so we can list them in any orders. Now we can execute  the statement, now if you look at the output window  down on the bottom you should see the statement followed by one rows  unfortunately I cannot resize this window to show you this message, but if you look down below you can see that one word was affected, which basically means  one record was inserted into this table. Now,  let&amp;#39;s look at the data in the customers table  so the last row is the one that we inserted, we can see that the  MySQL automatically generated the value 11, this is the effect  of auto increment attribute. So it takes the value of the last row,  and incrememnts it by 1. So here we have the first name. Last name,  birthdate, you didn&amp;#39;t supply a value for the phone attribute, so,  that&amp;#39;s why we have null here, we also have address, city  state, and the default value of 0 for the points.   In this tutorial, you&amp;#39;re going to learn how to  insert multiple rows in one go. For this we&amp;#39;re going to use the shippers  table, let&amp;#39;s have a quick look at the table definition  so here we have 2 columns, shipper id and name. Shipper id is  primary key, it&amp;#39;s not nullable, and it&amp;#39;s an auto incremented  column. So we&amp;#39;re going to let MySQL generate values for this column. Easy.  You only need to supply a value for the name column. So back to our  query editor window, we type out insert  into shippers in parenthesis we specify the name of the column  we want to insert values into, in this case name  followed by the values clause. Here we add a pair of  parenthesis with a value like shipper one. Now insert multiple rows all you have to do is insert a comma  followed by another pair of parenthesis. So,  shipper 2, one more time, comma, shipper 3,  this is how you can insert multiple rows in 1 row,  now let&amp;#39;s execute this statement, alright,  and then inspect the data in the shippers data.  There you go. So initially we only had 5 shippers and here are the 3 new shippers that we inserted. Note that MySQL  automatically generated the values for the shipper id column.  So we have 67 and 8.  Alright, here&amp;#39;s your exercise for this tutorial. Write a statement to insert three rows  in the products table, it&amp;#39;s really easy you can knock it out in a minute,   So here&amp;#39;s our products table, we only have 4 columns,  we&amp;#39;re going to leave out the first column because it&amp;#39;s an auto increments  column  so we only have to supply values for name, quality, unit price.  Back to our query editor window, let&amp;#39;s insert  into products the columns are  name, quanitty in stock, and unit  price. And the values are going to be let&amp;#39;s say product 1,  stock is going to be 10 and the unit price is going to be 1,  .95. Now I&amp;#39;m going to select these values. copy, paste it, like this,  change the values accordingly, and  finally the last row for the 3  there you go, let&amp;#39;s execute this statement, alright  and then verify the result so in the products table, now we should have 3 new records,  now the id&amp;#39;s you see here are 15, 16, and 17.  Because before recording this video I actually inserted a few records in this table and then  deleted them. So I had product ID&amp;#39;s 11, 12 13, and 14. And even though they&amp;#39;re actually deleted from  this table, MySQL remembers their id&amp;#39;s so instead of incrementing  10 x 11, it incremented 14 which is the last record  from before. And that&amp;#39;s why you generated 15. On your computer,  the items are going to be 11, 12, 13.   So far you have only learned how to insert data into a singular table.  In this tutorial, I&amp;#39;m going to show you how to insert data into multiple tables. Here&amp;#39;s  a really 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 has placed the order, we know the date of the order,  you know the status, comments, as well as 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 order these items  for. We have the product id, so we know what product  order, in what quantity, and in what price. So an actual  order can have 1 or more order items.  This is what we call a parent child relationship. So this relationship  the orders table is the parent, and the order items table is the  child. So one row in the orders table can have 1 or more children  inside the order items table. Now in this tutorial, I&amp;#39;m going to show you  how to insert an order and all it&amp;#39;s items. So you will learn  how to insert data into multiple cables. alright, back  to our query editor window. First we need to insert the order,  so insert, into, let me close  the navigator panel, we want to insert a record into the order  table, now what columns do we have here? So we have these columns but only the first four are the  and actually the first one, we don&amp;#39;t want to worry about, because that&amp;#39;s an auto increment column.  So we only want to supply values for customer id, order date,  and status. So back to our query, specifying those columns here, customer id, order date,  and status, now let&amp;#39;s supply the value.  In the customer id column, we need to add a  valid customer id. So let&amp;#39;s have a quick look at our customers  table, there you go. In this table,  we have 11 records. So these are the valid customer id&amp;#39;s.  Now back to our query, let&amp;#39;s use 1 for customer query  and then 2019, January the 2nd  for the order date, and one for the order status, once again  in this column, we need to insert a valid order status  id. If you don&amp;#39;t supply a valid id, MySQL is going to get ahead of us. So we insert an order here,  the items, now back to our order items table,  in this table, we have this order id column, so here&amp;#39;s the  as soon as we insert an order, MySQL is going to generate  an id for our new order now we need to access that id in  order to insert the items in this table. How can we do that?  Well, 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 ourself. Just like a function is a feature  in your tv. Every tv comes with a bunch of functions like  power on, power off, change the volume, change the channel so on.  So MySQL and other database engines come with a  bunch of built in functions  that we can use in our programs. One of these funcitons is last insert id. We can call or  execute this function, by adding parenthesis here, and this will return the ID  that MySQL generates when we insert a new row.  So, before going any further, let&amp;#39;s just select these,  and make sure you get the right result. Now we have a syntax error here because we didn&amp;#39;t terminate  the first statement with a semi colon, alright, now let&amp;#39;s execute this query,  alright, so the id of the new order is  12. Let&amp;#39;s verify that. So back to the orders  table let&amp;#39;s look at the data. On my machine I have  12 records here, I actually created 1 just before recording this video, so on your  machine you&amp;#39;re going to have 11 orders.  Now 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, we&amp;#39;re going to write another insert statement,  insert into order items, let&amp;#39;s have  another look at the columns in this table. So we have 4 columns  on all of them are required, so there&amp;#39;s really no need to specify the column name  in our insert statement, you simply supply values or order id,  product id, quantity and unit price. So, here  in the values clause, we add parenthesis,  what is our order id? That is the value returned  from calling this function. So I&amp;#39;m going to cut this from here and  next we need to supply a valid photo id,  but let&amp;#39;s say product 1, quantity, let&amp;#39;s say 1.  And the unit price 2.95 dollars.  Now let&amp;#39;s delete this select we don&amp;#39;t really need it anymore  let&amp;#39;s add another set of values, so once again, we&amp;#39;re going to call last insert id, to get the id of a new order, we&amp;#39;re going to  change the product to product number 2, and add a different price.  That&amp;#39;s it. Now let&amp;#39;s execute this  statement and see what we get. Alright, so,  back to our orders table, let&amp;#39;s refresh the records here, alright  so we have a new order, order number 13,  beautiful, now let&amp;#39;s look at the orders items table, here&amp;#39;s the order items, let&amp;#39;s open the table. So we should have  2 items, or order number 13. Beautiful.  So this is how you insert hierarchical data in SQL.   In this tutorial, I&amp;#39;m going to show you how to copy data from one  table to another. For example, in our orders table, currently we have  about a dozen records. Now let&amp;#39;s say we want to create a copy of this table  called orders archive and you want to insert every row that we have  in this table into that new table. If you have 10 orders,  we don&amp;#39;t want to quote an insert statement with a ten set of values,  that is very 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, order  archive. For that we&amp;#39;re going to use the create  table as statement. So create table  orders archive as.  Now right after that we write a select statement  to get everything from the orders table.  Now let&amp;#39;s see what happens when we execute this query, there you go. So back in the navigator panel, we have to refresh this view by clicking on this icon over here, now we  have a new table, orders archive, let&amp;#39;s look at the data,  so you can see all the orders are here and we have the exact same column  as the orders table. However, if you open this table in the  design mode, you cans ee that in this table, we don&amp;#39;t have a primary key.  So the order id column is not marked as a primary key.  And also it&amp;#39;s not marked as an auto increment column, so when we create a table using this technique MySQl will ignore these attributes,  and that means if you want to explicitly insert the record  into this new table, you have to supply a value for order id,  because this column is no longer an auto increment column.  So, using create table as statement you can quickly create  a copy of a table, now we refer to this select statement  as a sub query, so a subquery is a select statement  that is part of another SQL statement.  Now we can also use a sub query and an insert statement and that is a very powerful technique. It allows us to do really cool things. Let me show you.  So first, let&amp;#39;s right click the orders archive table,  and click on truncate table, because we want to delete  all the data in this table. Alright, it&amp;#39;s asking for confirmation  let&amp;#39;s truncate the table, so now back to this table.  Let&amp;#39;s refresh the table, we don&amp;#39;t have any records here, alright?  Now back to our query editor, let&amp;#39;s say you want to copy  only a subset of records from the orders table into this table,  like all the orders placed before 2019. So first let&amp;#39;s select  everything from the orders table where  order date is less then 2019, January 1st.  So, these are all the orders, order s copy these orders into he orders archive table. So we can use this select statement as a subquery   in an insert statement, we write insert  into orders archive  now we don&amp;#39;t need to supply the column names, because we&amp;#39;re going to  play values for every column that we have in this query.  So, we did that, and  this is ane xample of using a select statement as a subquery  in an insert statement. Let&amp;#39;s execute this,  alright, now back to the table, let&amp;#39;s refresh the records  we only have the orders placed before 2019.   Alright, here&amp;#39;s a really, really, really coo exercise  back to our SQL invoicing database. Look at the invoices table.  So in this table, we have these columns,  (?) id number, client id number which is associated or related  to the client id column, in the clients table, followed by  a few other columns, now let&amp;#39;s say you want to create a copy of the record  in this table, and put them in a new table called invoices archive.  However in that table, instead of the client id column, we want to have the client name column, so you want to join  table with the clients table, and then use that query as a sub query in a create table statement.  Also, to mae the exercise more interesting,  I want you to copy only the invoices that do have a payment. So if  you look over here, this payment data column here, determines  if a payment has been made, towards this invoice or not. So select  only the invoices that do have a payment date, it&amp;#39;s a really really good  exercise, spend two to three minutes on this and then come back and continue watching.   Alright, first I&amp;#39;m going to use the SQL invoicing data,  base, now let&amp;#39;s select everything from the  invoices table and join it to the clients table.  here I&amp;#39;m going to use the using statement to simplify  my join. What column are we going to use for joining? The client id  column. Let&amp;#39;s execute this query up to this point.  Alright, so first we see client id column  is used for joining these tables, after that we have the columns from  the invoices table like invoice id number and so on. Followed by  the columns from the clients table. Name  address and so on. Obviously we don&amp;#39;t want all these columns, we only want  the columns from the invoices table but we should replace the client id  column with he client name column. So let&amp;#39;s have  a quick look at the design of the invoices table  here we have invoices id, number,  client id, we want to replace this column with the client name.  Back to our query, I&amp;#39;m going to pick,  invoice id, number  and then client.name, let&amp;#39;s rename it to client  what other columns do we have here? We have  invoice total and payment total so let&amp;#39;s add those as well,  invoice total as well as payment total,  we also have three columns,  for dates, invoice date, due date, and payment date,  so, let me close the navigator panel. Invoice  date payment date and  due date. Now technically because these columns only exist  in the invoices table, we don&amp;#39;t have to prefix them with the table alias. So we can simplify the code like this, however,  I personally prefer to prefix them, because that gives me a clear  picture of how I&amp;#39;m joining these tables. It&amp;#39;s just a personal preference  another developer might disagree and that&amp;#39;s fine, so whatever you prefer. That&amp;#39;s perfectly fine, let&amp;#39;s execute the query and make sure  we get the right result, so we have the invoice  ID  number client beautiful, 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 either  return records that have a payment date, or the records that have  a payment total of greater then 0. Both are perfectly  fine, so back to our query,  down at the bottom, let&amp;#39;s add the where clause, where payment  date is not null. That&amp;#39;s better.  Let&amp;#39;s execute the query one more time, now we get only these  handful of invoices, beautiful, finally let&amp;#39;s use our  query, as a subquery in a create table as statement. So right before  select we type create table, invoices,  archive, as there you go, let&amp;#39;s execute  the query beautiful, now back to the navigator panel,  let&amp;#39;s refresh the view, so here&amp;#39;s our new table,  invoice and archive, let&amp;#39;s look at the data, there you go.  You only have the invoices page and here&amp;#39;s the name of the client for each invoice. Beautiful. Now just note that  if you execute this query one more time, you&amp;#39;re going to get an error because we already have a  table called invoices archive. Later in the course I will show you how  to draw tables, that&amp;#39;s pretty easy, but for now you can just right click and go to draw table and then confirm  alright, and then you can run the query one more time.   In this tutorial I&amp;#39;m going to show you how to update data in SQL. So back to our invoices table,  look at the first record here. The payment total for  this record is 0, and obviously there is no payment date.  Now let&amp;#39;s imagine that there was a problem int he system that recorded this information.  Let&amp;#39;s say the client paid ten dollars for the invoice. So we should update this record in this table. That is very easy, back ti 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 or columns. In this case we want to update payment total. Let&amp;#39;s say to ten dollars. We should also update  payment date. So here&amp;#39;s a column to add more columns  we set this to date value, let&amp;#39;s say  2019, March 1st, and then we type  out a condition, with this condition we identify  the record or the records that need to be updated. In this case we want to update the invoice number 1. So,  back to our query, we type out invoice id  equal to 1. Let&amp;#39;s execute this. Beautiful,  Now back to our table, let&amp;#39;s refresh the data by clicking on this icon.  Alright, we can see that payment total is updated to ten.  And we also have a payment date. Beautiful. Now lets say we actually  updated the wrong record, maybe we should have updated invoice  number 3, so we should update this table one more time, and restore the  original values in this column, back to our query  we can set the payment total to 0, and  the payment date to null. So we can use the null keyword to insert the null value in a column that accepts  null values. Now back to the navigator panel, let&amp;#39;s open the  table in the design mode. We can see that the payment total column  has a default value of 0, and the payment date column  has the default value of null. So back in our query  we can also set payment total tod default and MySQL  will use the default value for this column, which is in this case 0.  So, let&amp;#39;s execute this statement one more time, beautiful.  Now, back to the table, let&amp;#39;s refresh the data, so payment total is set to 0. And payment date is null. Beautiful.  Now let&amp;#39;s go ahead and update the third payment. Look at the invoice total. That is $147. For this example let&amp;#39;s  imagine that the client made 50% of the total  amount on the due date. So, back to our query  editor window, instead of using a literal value window here like $70 we can write an expression, we want to calculate 50% of invoice total. So invoice total,  x 0.5. Now let me break this code into multiple  lines so we can see clearly, now we should  set the payment date, as I told you this client made the payment  on the due date, so we can set this to due date.  Whatever 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&amp;#39;s refresh the data.  Alright look, payment total is updated, and it&amp;#39;s set to almost 50% of he invoice total. However,  this number is truncated so we don&amp;#39;t have the digits after the decimal point.  Don&amp;#39;t worry about it for now, we&amp;#39;ll come back to this when we talk about data points  later in the course. Also we can see that payment date is set  to the same value 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 type out here, has to be more general.  For example, back to the invoices table, you can see that we have multiple  invoices for client number 3. You can write a statement  to update all the invoices for this client. So back to our query editor window, we change our condition to,  where client id equals 3. However, if  you execute this statement with MySQL workbench which is the software we&amp;#39;ve been using  in this course, you&amp;#39;re going to get an error, because by default, MySQL workbench  runs in the safe update mode. So it allows you to update only  a single record. You&amp;#39;re not going to have this problem if you use another client for  MySQL or if you write this statement in your application code  this is only specific for MySQL workbench. Now let me show you how to get around this. On the top we go to MySQl workbench menu and then preface this.  On this dial up box, on the left side, click on SQL editor  and then down on the bottom,  untick this checkbox, save updates, so this  prevents you from accidentally updating or deleting a bunch of records in a table,  so let&amp;#39;s goa head with this, now we need to  reconnect to this instance of MySQL. So, let&amp;#39;s copy  all the code here, and close this local instance  window. Alright? Now, on the home page, double click this connection to reconnect, there you go, and then paste  all that Sql code. Now let&amp;#39;s  execute this, beautiful, all the invoices for client number 3 are updated.  Here we can also use the in operator, let&amp;#39;s say  we want to update all the invoices for clients number 3 and number 4.  So all the operators 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, you simply leave it out.   Alright here&amp;#39;s your exercise for this tutorial. Back to our SQL store database,  write a SQL statement to give any customers,  born before 1990, 15 extra points.   Alright first we&amp;#39;re going to use SQL  store, then, write an update statement. To update the customers table,  set the points to point  plus 15, so here we&amp;#39;re using the expression to update  the points column, for anyone born before  1990. So where birth date is less then  1990, January 1st.  Alright, let&amp;#39;s execute this query, alright, now  let&amp;#39;s open up the customers table one more time,  so anyone who was born before 1990 now has an extra  50 points.  In this tutorial we&amp;#39;re going to learn how to  use subqueries in an update statement, it&amp;#39;s extemely powerful,  so we&amp;#39;re going to continue with the example from the last tutorial, but we&amp;#39;re going to make it more interesting. Here we&amp;#39;re updating all the invoices for  client number 3, but what if we don&amp;#39;t have the id of the client? We only have the name? For example, you might even have an application, and in that application the user types in the name of the client, so first we should find the  id for that client, and then use that id to update all the invoices.  How do we do that? Well, let&amp;#39;s have a quick look at our clients  table, so here we have this client  my words, let&amp;#39;s say you have the name, you want to find the id.  So back to our query window. After our update statement  I&amp;#39;m going to write the select statement to select the client id column,  from the clients table, where name equals  my works. Now here we have  a syntax error, because we didn&amp;#39;t terminate the last  don&amp;#39;t worry about that, we&amp;#39;re going to get back to that in a second. Let&amp;#39;s just select these few lines and  execute this query. The idea of this client is  2. Beautiful. Now we can use this select statement as  a subquery in a update statement. So as I told you before,  a subquery is a select statement that is within another SQL  statement. So, instead of hard coding 3 here,  we&amp;#39;re going to use this select statement as a subquery, but  we need to put it in parenthesis, so MySQL will  execute this query first, it will return the client ID and then use it in this condition. So for clarity  let&amp;#39;s remove the line break and indent  these few lines. So this is the end result. Now  we execute this query, and this updated all the invoices for this client.  Now, what if this query returns multiple clients?  For example, back to the clients table, let&amp;#39;s imagine you want to update  the invoices for all clients located in New York or California.  So, we need to update our subquery,  like this, where state  in California, and  New York. Now before executing the entire statement,  let&amp;#39;s just select our subquery and execute it to see what we get.  So we get two client ID&amp;#39;s, one and  three, beautiful, now because this query, this subquery  returns mutliple records, we cannot use an equal sign  here anymore, so we need to replace this with an in operator.  And now this statement updates the invoices for all the clients  located in these two statements. Let&amp;#39;s execute it,  beautiful, everything worked. So as a best practice, before executing your  update statement, run your query to see what records you&amp;#39;re going to update. So you don&amp;#39;t  accidentally update the records that shouldn&amp;#39;t be updated. Now here we  have a subquery but even if we didn&amp;#39;t have a subquery, you could still query the records that we&amp;#39;re going to update. Let me show you. So let&amp;#39;s imagine  we&amp;#39;re going to update all the invoices where payment  date is null. Before executing this entire update  statement, I would run a query like this. Select, start  I would start from invoices where payment date is null.  Now let&amp;#39;s execute this query,  these are the two records that don&amp;#39;t have payment date, so once we&amp;#39;re confident that we&amp;#39;re updating the right records, then, we come back here,  and get rid of this select statement, and just attach the where clause  to our update statement.  Alright, here&amp;#39;s your  exercise for this tutorial, back to our SQL store database, look at the  orders table, as you can see, several orders don&amp;#39;t have a  comment, I want you to write a SQL statement to update the comments or orders for customers who have more then 3000 points.  So customers who have more then 3000 points, regard them as gold customers. Find their orders, if they have placed an order update the comments column and set it to gold customer.  That&amp;#39;s a really good exercise.  Alright, first we need to find  the gold customers, so select everything from the customers table,  where points is greater then 3000.  And by the way, because current database is SQL invoice  we either have to type out a use statement on the top, or  double click this database before executing this query. So,  there you go. We have three gold customers  now we need to get the ID of these customers to use them in an update  statement. So, we only select customers  Id here and then select this statement as a subquery  in an update statement. So,  update, orders, set comments to  gold customer, where  customer id because we&amp;#39;re dealing with multiple customer id&amp;#39;s  we need to use the in operator and then to use it,  as a subquery, we need to enclose it in parenthesis.  Let&amp;#39;s indent the code, that&amp;#39;s better, so here  is the final solution.   So you have learned how to insert and update data, in this lecture, we&amp;#39;re going to finish this section  by learning how to delete data. That is very easy. We use the delete  from statement to delete records from a table.  Let&amp;#39;s say the invoices table. Now optionally we can add a search condition  to identify the records we want to delete. If you don&amp;#39;t write this where clause with this statement, we delete all the records in this table.  And obviously, that&amp;#39;s very dangerous, so be very careful when executing this statement. Now here let&amp;#39;s say we want to delete the invoice  with the id 1, so where invoice id goes to 1.  Now here we can also use subqueries, let&amp;#39;s say we want to delete all the invoices for  the client called my works. First let&amp;#39;s  find this client, so select everything from the client&amp;#39;s table  where name equals to my works.  Let&amp;#39;s execute the second query, so here&amp;#39;s our client,  now we can get this client  id and use it in our search condition. So where  client id equals 2, this is where we add our subquery, there you go, just like before,  beautiful. So this is how we can delete data,  in SQL.   Alright, we&amp;#39;re done with this section, but before going to the next section, I want you to restore all these databases to their original state. Because in this section we  added some data, we updated some data, we deleted some records,  so if you don&amp;#39;t restore these databases you may see different results going forward.  So, restoring these databases is pretty easy. Here in My SQL workbench, on the top go to the file menu  and open SQL script. Then navigate to the directory  where you stored the SQL scripts for this course, in case you lost  that directory, go back to the first section, you have a lecture for downloading the supplementary materials. So in this directory open  create databases.SQL.  Now execute this script to recreate all of our databases.  Alright, beautiful, now let&amp;#39;s open up the navigator panel, you can see the  databases disappear from here, simply click on this refresh icon.  Beautiful. Alright, we&amp;#39;re done with this section, I will see you in the next  section. Hey guys, Mosh here,  in case you haven&amp;#39;t seen my website yet, head over to codewithmosh .com. This is my coding school where you can find plenty of courses  on web and mobile application development. In fact recently I  published a complete SQL course that is about 10 hours long,  and it teaches you everything you need to know from the basic to advanced topics such as database design, security, writing complex queries,  transactions, events and much, much more. These are the topics  that every software engineer must master. This Youtube  course you&amp;#39;ve been watching is the first three hours of my complete SQL course that is about 10 hours long. So if you want to master SQL  and get job ready, I highly encourage you to get involved 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 for this course is $149 but the first 200 students can get it for just over $100. So if you&amp;#39;re interseted, the link is below this video.  

Each question must have 4 options, 1 correct answer, focusing on vocabulary, phrases, or concepts. Format each question as: 
1. Question text
A) Option1
B) Option2
C) Option3
D) Option4
Correct: C
Assistant: