SQL Course for Beginners [Full Course]

0:00 Hi, my name is Mosh Hamedani, and I'm super excited to be your instructor.

0:05 In this three hour course,

0:06 you're going to learn everything you need to get started with SQL or SQUEL.

0:10 First I'm going to give you a three minute introduction to SQL,

0:14 then we're going to install the necessary tools and write our first SQL query.

0:19 This course is ideal for anyone who wants to learn SQL from scratch,

0:23 but also anyone with some basic knowledge who wants to fill in the missing gaps.

0:27 By the end of this course,

0:29 you'll be able to retrieve insert, update, and delete data in databases.

0:33 We'll talk about tables, relationships, different types of joints,

0:38 sub-queries regular expressions and much, much, more.

0:41 These are the essential concepts every

0:43 software developer or data scientist must know.

0:45 This SQL course is packed with tons of exercises

0:48 that help you both learn and remember the SQL syntax.

0:52 Also, there is a table of content below

0:54 this video so you can quickly jump to specific tutorials,

0:57 now let's jump in and get started.

1:04 Let's start the course with a quick overview of databases,

1:06 what they are and how we use them.

1:09 A database is a collection of data stored

1:12 in a format that can be easily accessed.

1:14 In order to manage our databases,

1:16 we use a software application called database management system, or, DBMS.

1:20 We connect to a DBMS and give it instructions for querying or modifying data.

1:26 The DMBS will execute our instructions and send results back.

1:30 Now we have several database management systems out there,

1:34 and these are classified into two categories,

1:37 relational and non relational, also called NoSQL.

1:41 In relational databases,

1:42 we store data in tables that are linked to each other using relationships.

1:47 That's why we call these databases relational databases,

1:51 each table stores data about a specific type of object,

1:55 like customer product, order and so on.

1:58 SQL or SQUEL is the language that we

2:01 use to work with these relational database management systems.

2:04 It looks like this.

2:06 We use SQL to query or modify our data.

2:08 In this course you're going to learn everything

2:10 there is to know about this powerful language.

2:12 Now, there are many different relational database management systems, out there,

2:16 some of the most popular ones are MySQL, SQL Server my Microsoft, and Oracle.

2:22 But of course, there are plenty more.

2:24 Each database management system has a different flavor of SQL.

2:28 But all these implementations are very similar

2:30 and are based on the standard SQL specification.

2:33 So most of the SQL code that you will learn

2:37 in this course will work with any database management system.

2:40 In this course, we'll be using MySQL,

2:42 which is the most popular open source database in the world.

2:46 Okay, now back to this diagram, what about non relational databases?

2:50 In non relational databases, we don't have tables or relationships,

2:54 these databases are different from relational databases,

2:57 but that's a topic for an entirely different course.

3:00 What you need to know is non relational database systems don't understand SQL.

3:05 They have their own query language.

3:08 So, we use SQL to work with relational database management systems.

3:12 Now, before we jump in and install MySql, let me clarify something quickly,

3:16 as you talk to different people,

3:18 you will hear two different pronunciations of SQL.

3:21 SQUEL, or SQL.

3:24 What is the correct way?

3:25 Well, it depends on who you ask, and of course everybody thinks their way

3:29 of pronouncing this word is the right way.

3:31 But here's a history about this language.

3:33 SQL was originally developed at IBM in the 70s and back

3:37 then it was initially called SQUEL short for structured English query language.

3:42 But they changed the acronym to SQL,

3:45 because SQUEL was the trademark of an airplane company.

3:48 So to this day, there has been

3:50 an argument about what is the right generally speaking,

3:53 people in non-English speaking countries call it SQL.

3:57 I'm used to calling it SQUEL because it's shorter and sweeter than SQL.

4:02 But if you prefer to call it SQL,

4:04 that's totally fine with me, I'm not going to get mad at you.

4:07 So that's the history behind this language.

4:09 But what about MySQL as a software product?

4:12 Developers of this product prefer to call it MySQL rather than mySQL.

4:16 But they don't mind if they call it MySQL.

4:20 In this course, I'll be teaching you SQL with MySQL.

4:24 Hey guys, Mosh here.

4:25 I just wanted to let you know that you

4:28 really don't have to memorize anything in this course,

4:30 because I've created a complete cheat sheet with summary notes for you.

4:34 You can find it below this video in the description box.

4:37 So I have done my best to create the best possible,

4:41 most comprehensive SQL course for you,

4:43 and I would really appreciate it if you would

4:45 support my hard work by liking and sharing this video.

4:48 Thank you so much, now, let's continue.

4:52 In this tutorial, I'm going to show you how to install MySQL on your computer.

4:59 Now I'm using a Mac,

5:00 so first I will show you how to install MySQL on a Mac computer,

5:04 and then I will show you how to install it on Windows.

5:06 So if you're a Windows user, feel free to skip this tutorial.

5:10 Now, open up your browser and head over to MySQL.com.

5:14 Then, go to the downloads page, and scroll down to the bottom.

5:20 Here you should see MySQL community edition, this is absolutely free,

5:24 and we're going to use that throughout this course.

5:26 So let's go ahead and look at that, now on this page, click on MySQL,

5:32 community server, and then on this page,

5:36 you should see available releases for Mac OS.

5:40 So, in this list, download the first item which is a dmg archive.

5:44 Alright, now, on the next page, click on no thanks, just start my download.

5:50 Okay, as you can see, we are getting a dmg file,

5:54 which is basically a setup wizard.

5:56 Alright, now the DMG is downloaded, so let's open it,

6:00 and then double click on this package,

6:03 this will launch an installation wizard which is pretty easy to use,

6:07 so simply click on continue and again,

6:10 and again, agree with the license agreement,

6:15 and install MySQL, it's going to ask for your password,

6:18 this is the password that you used to log into your computer,

6:22 so let's put that here, alright,

6:24 now here we need to set a password to the root for the admin user.

6:30 So click next, and in this box, type a complex password, alright, now,

6:37 let's finish the installation,

6:40 and enter your computer's password one more time, and we are done!

6:48 That was super easy and sweet.

6:52 Alright, we installed MySQL community server,

6:55 now we need the graphical tool to connect

6:57 with this server and manage our databases.

7:00 So, back to the downloads page, one more time, scroll to the bottom,

7:06 and go to MySQL community addition,

7:09 and on this page, somewhere you should see MySQL workbench.

7:13 This is a graphical tool that we use to connect to our database server.

7:18 So let's go ahead and download this as well.

7:21 Now once again on this page, we need to download a dmg archive, so, download,

7:28 and, again we have to say no we don't want to login or sign up,

7:34 so let's go ahead and download the dmg, and then open it, alright,

7:41 you're going to see something like this, so drag

7:44 this MySQL workbench and drop it onto the applications folder.

7:48 So, let's go ahead with that, now it's

7:50 going to copy this into the applications folder,

7:55 beautiful, so we're done with the installation,

7:58 that was super easy, now press command and space,

8:01 and search for MySQL, work bench, there you go, let's open it,

8:07 now the first time we get this message,

8:09 because this is an application we downloaded from the internet.

8:12 So, we need to tell Mac that we trust

8:14 this, let's go ahead with that, so this is MySQL workbench,

8:18 now by default you see a connection here,

8:21 if you don't see that, you need to create it.

8:24 Let me show you how to do that.

8:25 So for this demo I'm going to right click this and delete this connection,

8:31 alright, now let's create a connection from scratch.

8:34 So click on this plus icon, on this page,

8:37 give this connection a name, let's say local instance,

8:41 now the connection method we're going to use is tcip,

8:46 which is set by default, the host name is 127.0.0.1,

8:51 which is the address of the local machine,

8:54 and the port is 33 06, this is the default port for MySQL server,

8:59 that is the user name of the admin, now we need to enter the password.

9:03 This is the password that we set during the installation.

9:06 So, click on store in keychain, and in this box,

9:11 type the password for the MySQL server.

9:15 Alright and finally, let's test the connection, okay,

9:21 we successfully connected to MySQL server on the local machine,

9:26 beautiful, let's click okay,

9:28 and here we have a connection on the homepage of MySQL workbench,

9:32 every time we open MySQL workbench,

9:34 we use this connection to connect to our local server.

9:38 Alright, we're done with the installation of MySQL on a Mac,

9:42 next I will talk about MySQL on a Windows computer,

9:45 so feel free to skip that tutorial.

9:52 In this tutorial, I'm going to show you how to install MySQL on Windows.

9:57 So open up your browser and head over to MySQL.com,

10:00 then, go to the downloads page now here, scroll down to the bottom,

10:08 we're going to use MySQL community edition which is absolutely free.

10:12 So, let's go with this, now, select MySQL community server,

10:20 and then scroll down so here you should

10:23 see something like this, MySQL installer for Windows.

10:26 This is the recommended method for installing MySQL on Windows.

10:31 So, click on this, alright, on the next page,

10:35 scroll down, and download the first installer here.

10:42 On the next page scroll downs nd click on no thanks just start my download.

10:48 Otherwise you have to create an account and log,

10:50 which is unnecessary for knowing this course,

10:53 so, let's go with this, and save this file to our computer, and then run it.

11:04 Alright, we're going to use this setup wizard to install MySQL, on our computer.

11:08 This is very easy, all you have to do is click next,

11:12 but there are a couple places where you

11:13 need to enter a password, let me show you.

11:16 So, on the first page,

11:17 for the setup type we're going to use the developer default setup.

11:21 Go to the next page.

11:24 Now here we're getting a little warning,

11:26 because this installation wizard wants to install the connector for python 3.7,

11:31 but I don't have Python on this machine,

11:33 so that's why I'm getting this warning, now on your machine,

11:36 you might or you might not get this error, just click next, and one more time,

11:42 so here are the products that I want

11:45 to get installed the first one is MySQL server,

11:48 the second one is MySQL workbench,

11:50 this is the graphical tool that we use to connect to our database server,

11:54 and manage our database.

11:56 You're going to see that soon.

11:58 So click on execute, now this is going to take about 5-10 minutes,

12:03 so, I'm going to pause the recording.

12:08 Alright, all the products are installed, beautiful, let's go to the next page,

12:12 and again, here our the group replication page, also click on next,

12:17 and the next page which is about networking, leave all the default settings, so,

12:24 let's go to the next page,

12:25 now we should set a password for the root for the admin user.

12:29 So, click on next, and in this box, type a password for the admin user.

12:36 Alright, and then let's go to the next page,

12:41 once again leave all the default settings, and click on next,

12:45 and execute one more time, alright, and now let's finish our installation,

12:53 once again we have to click on next, and then finish.

12:58 One more time, there are so many steps.

13:01 Now here's the page where you need to enter the admin password,

13:04 so the page is called connect to server,

13:07 you can see the username is root, which represents the admin user,

13:12 so in this box enter the password that you said earlier,

13:16 then click on check, okay,

13:18 connection was successful beautiful, let's go to the next page,

13:23 and click on execute, and finally finish.

13:28 There you go, we have one more step,

13:31 next the finally after all these steps the installation is complete.

13:36 Now, this is going to start, MySQL workbench,

13:38 which is the graphical tool we use to manage our database and run SQL queries.

13:44 So, click on finish, so now we have a command prompt window,

13:48 where we can type instructions to talk to our MySQL server,

13:52 we don't really need this, so close it,

13:56 and here's MySQL workbench, now the first time you open this page,

14:00 by default you should see a connection here, if you don't see it,

14:04 click on this plus icon, on this page give this connection a name,

14:10 let's say local instance, now leave all their settings to their default value,

14:16 but here for the password click on store involved.

14:20 And in this box, type the password that you used for the admin user.

14:24 So, I'm going to put that here, okay, now click on test connection,

14:32 alright, we successfully connected to the MySQL server on this computer,

14:36 alright, then, click on okay,

14:40 now we click on this connection to connect to our database server.

14:44 Alright, so here's the interface you'll be using throughout this course,

14:48 on the left hand side we have the navigator panel,

14:50 in the middle we have the query editor which we can

14:55 resize this is where we're going to write our SQL queries,

14:58 and on the right side we have additions.

15:00 So we're done with MySQL on our computer, next,

15:04 I'm going to show you how to create our databases for this query.

15:12 In this tutorial, I'm going to show you

15:15 how to create the databases for this course.

15:18 So here I've got MySQL workbench open,

15:20 let me quickly give you an overview of this interface because

15:24 the first time you open it it might appear a little bit intimidating.

15:28 But actually it's not that difficult.

15:29 So here on the top we have this tool bar with these buttons for creating

15:33 a new tab for writing SQL code as well as opening a SQL file.

15:38 And next to that we've got a bunch of buttons for creating a database,

15:42 creating new tables and so on.

15:44 On the left side, we've got the navigator

15:46 panel with two tabs administration and schemas.

15:49 We use the administration tab to do administrative work,

15:53 such as starting or stopping our server.

15:56 Importing or exporting data and so on.

15:59 The schemas tab shows the databases that we have in the current database server.

16:04 So currently we only have one database, that is sis,

16:07 and this is the database that MySQL uses internally to do it's work.

16:12 Now, in the middle we've got this query editor window,

16:14 this is where we write our SQL code,

16:16 so we'll be spending most of our time in this course, in this window,

16:20 and on the right side we've got another panel with two tabs,

16:24 contacts held, and snippets,

16:25 now chances are this interface might look slightly different on Windows,

16:29 but what I'm showing you here is almost identical to what we have on Windows.

16:34 So don't get hung up if it looks slightly different on your machine.

16:37 It doesn't really matter.

16:39 Now up here, we've got these buttons for showing or hiding these panels,

16:43 so to clean this interface, I'm going to hide this panel on the right side.

16:48 As well as the panel on the bottom.

16:50 That is better.

16:51 Now, to create the databases for this course,

16:54 download the zip file attached, below this video.

16:56 When you extract the(?) you're going to see a bunch of SQL files like this.

17:01 So the main one you'll be using in this tutorial is called create databases.SQL.

17:06 So this file contains all the SQL code to create

17:10 all the databases that we need in this course,

17:13 now we also have individual files for creating individual databases,

17:16 I've just added these files in case you need

17:19 to recreate one of these databases in the future.

17:21 But for now don't worry about them.

17:23 Now, back to MySQL work bench, let's open the main SQL file.

17:30 That is create databases.

17:32 So this is an example of SQL code.

17:36 Now this may look complex at the beginning, but trust me,

17:38 as you go through the course you're

17:40 going to understand exactly how everything works here.

17:42 You're going to be able to write SQL code like this.

17:46 So, you want to execute this, to create all the databases for this course.

17:50 To do that, we click on this icon,

17:53 this yellow thunder icon that we have on this tool bar here.

17:57 This will execute either the selection or the entire

18:00 code if there is nothing selected for example, if I select this line here,

18:04 and click on this icon, this will execute only this line.

18:07 In this case we want to execute the entire code,

18:10 so we shouldn't select anything,

18:12 and now let's execute this, beautiful, now here down on the bottom,

18:16 we have this panel called the output window

18:18 that shows all the operations perform in our database server,

18:22 so we can see all the operations completed successfully,

18:26 or something went wrong.

18:28 As you can see you've got these green ticks next to each operation.

18:32 Beautiful, so I'm going to close this panel, that's better.

18:36 Now on the left side in this schemas tab you don't

18:38 see this new database so we'll have to refresh this view, beautiful.

18:44 So we've got all these databases that are prefixed with SQL or SQL,

18:48 I decided to prefix them with SQL so we

18:51 know that these are the databases for this course,

18:54 they don't accidentally clash with a database

18:56 on the same name of your database server.

18:59 Now, at the time of recording this video,

19:01 there are only 4 databases here, but as we go through the course,

19:04 I'm going to update the script for creating the databases, so,

19:08 when you watch this course, chances are you're going to see more databases here.

19:11 Don't worry worry about the difference.

19:13 Now as an example let's explore these databases.

19:16 And by the way we don't need this tab anymore so let's close it.

19:20 That's better.

19:21 Let's expand the SQL store database,

19:24 now in every database we have these objects,

19:27 we have tables, this is where we store our data,

19:30 we have views, which are kind of like virtual cables,

19:34 so we can combine data from multiple tables, and put them in a view.

19:38 And this is especially powerful for creating reports,

19:40 you're going to learn about them in the future.

19:43 You also have store procedures and functions,

19:45 and these are little programs that we

19:48 stored inside of our database for querying data.

19:51 For example, you can have a store procedure

19:53 for getting all the customers in a given city.

19:56 So we call that procedure and we say hey,

19:58 give me all the customers in San Francisco

20:00 And this will return all the customers in San Francisco.

20:04 Okay, now, let's expand the tables, so here are the tables in this database.

20:10 We have customers, we have orders, products, shippers and so on.

20:16 Now, select this customers table whenever you hover your mouse over

20:20 this item and see these three items on the right side.

20:24 Click on the right most icon, that looks like a table with a thunder.

20:28 With this we can see all the data, in this table.

20:32 So this is our customers table.

20:35 In this table, we have these columns like

20:37 customer id which we use to uniquely identify customers.

20:40 We all have first name, last name, birth date, phone, address and so on.

20:46 So these are the columns in this table and every row is called a record.

20:52 So every row represents one customer and these are

20:55 the pieces of information we know for each customer.

20:58 Now let's look at another table, let's open the orders table,

21:04 in this table we have these columns like order ID,

21:07 customer ID, order date, status, and so on.

21:10 What is this customer ID here.

21:12 We use this column to identify who has placed each order.

21:16 Now what is interesting here is you have referred

21:19 to this customer using their customer ID which uniquely identifies that.

21:24 In other words, if Jon Smith has placed an order,

21:27 we don't store John Smith here, we only store John's customer ID.

21:32 Why is that?

21:33 Here is the reason.

21:34 It is possible John Smith might have placed multiple orders in our system.

21:40 Now every time John places his order,

21:41 we need to look up his address and phone to ship his order.

21:45 Now it is possible that some of this information might change in the future.

21:49 John might move to a new place or change his phone number,

21:52 he might even change his name,

21:54 if you repeat all that information next to each order,

21:57 then we'll have to come back and make changes in multiple places.

22:01 In contrast, with this design we only store the ID of john here,

22:06 so anytime we want to change any information about John,

22:09 instead of changing that here.

22:11 We go back to our customers table, so let's look at customer with ID 6,

22:17 that is actually called(?) So here is all the information about Elka.

22:22 This is her phone number, this is her address,

22:24 and by the way this is all dummy data that I created using a tool.

22:28 So, if any information about elka changes in the future,

22:32 this is the only place that we need to modify.

22:35 So this is how these databases work.

22:38 We refer to these databases as relational databases.

22:40 That basically means in these kind of databases we have

22:44 multiple tables that are related to each other using a relationship.

22:48 So internally there is a relationship between the customers table,

22:53 and the orders table.

22:54 So the customer ID column in the customers table is

22:59 related or linked or associated with the customer ID column.

23:04 In the orders table.

23:05 Here's the orders table, and here we have the customer ID column.

23:09 So this was a brief introduction to relational databases,

23:13 you learned about databases, tables, columns, rows and relationships.

23:17 In the next section I'm going to show you how

23:20 to retrieve data from a single table in this database.

23:24 But, before going any further as an exercise

23:26 I want you to explore the invoicing database.

23:28 Look at the all the tables, look at all the data to get an id

23:32 of the kind of data that we have in this database.

23:34 You're going to use this database a lot in the future,

23:37 so let's spend a couple minutes to explore this database.

23:44 In this tutorial I'm going to show you how to retrieve data from a single panel.

23:50 Now look at the navigator panel.

23:52 Currently none of our databases is displayed in bold,

23:55 and that means none of these databases are selected for query.

23:58 So the first step to rate a query to get data from a database,

24:02 is to select a database.

24:04 The query that we'll write will be executed against that database.

24:08 In this demo, we're going to use the SEQUEL store database.

24:12 So we type our use SQL_ store.

24:16 Now use is a keyword in the SQL language, and that's displayed in blue.

24:21 Now SQL is not a case sensitive language,

24:24 and that means we can use upper case or lower case characters,

24:27 it doesn't really matter, but as a best practice,

24:30 we should capitalize the SQL keywords,

24:32 and use lower case characters for everything else.

24:36 So now, let's go ahead and execute this query.

24:39 Alright, Look, the SQL store database is now displayed in bold.

24:44 Now in MySQL workbench,

24:45 we can also select a database by double clicking that, so

24:49 now I double click SQL invoice and it's the current database.

24:53 Now if we run this query again, the SQL store database becomes selected.

25:00 Alright, now let's write our first query

25:02 to retrieve all the customers in this database.

25:04 So after the you statement we're going to use select statement.

25:08 Here is the basic syntax or basic structure of the select statement,

25:13 we type out select in front of that we

25:16 specify the columns that we want to retrieve,

25:18 for example we can retrieve the customer ID column as well

25:22 as the first name column or we can retrieve all columns using an asterisk.

25:28 Now after that we use the from clause and this is

25:30 where we specify the table that we want to query,

25:34 in this case the customer's table.

25:36 So this is the simplest query to select all the customers in a given table,

25:40 now whenever you have multiple SQL statements you

25:44 need to terminate each statement using a semicolon.

25:47 So, look, we have a red underline here that indicates an error,

25:50 if you hover your mouse over here,

25:52 you can see this tool tip saying select is not valid at this position.

25:56 Because we didn't terminate the first statement with a semi colon.

26:00 Okay, now let's execute this query one more time,

26:03 once again, we can click on this button here, or we can use a short cut,

26:08 so look at the query menu on the top, the first line is execute.

26:12 Now here's the shortcut for this command, on Mac, it's shift command and enter.

26:17 On Windows its going to be different honestly I'm not sure.

26:20 So whatever it is, use that.

26:22 So I'm going to press shift command enter,

26:26 and here are all the customers in this table.

26:31 So this next statement has two clauses, the select clause and the from clause.

26:36 But there are other clauses we can use to filter and sort data.

26:41 For example, you can use the wear clause to filter

26:44 the result and get the customer with ID one.

26:48 So we can write an expression like this.

26:51 Where _ID= 1.

26:53 Now when we execute this query, we'll only get the customer with ID 1.

27:00 So this is the where clause.

27:02 We can also sort the data so after wear we use the order by clause

27:08 and here we specify the columns that we're going to sort the results from on.

27:12 Let's say we want to sort these customers

27:14 by their first name so we type out first_name.

27:16 That is the name of one of the columns in this table, right?

27:20 Now if you execute this query this order by doesn't really have an impact,

27:24 because we only get one record in the result

27:28 so let me temporarily take out the where clause,

27:32 to do that we can put two hyphens in front of this line,

27:35 now this line, is treated as a comment,

27:37 which means the SQL engine is not going to execute this, okay,

27:41 so let's execute this query one more time,

27:44 now all the customers that we get our sorted based on their first name.

27:50 So that's the basic idea.

27:52 Now over the next few tutorials your'e going

27:54 to learn more about these clauses in detail.

27:57 But what you need to take away in this tutorial is

27:59 that is that these 3 clauses from where an order by are optional.

28:04 As you can see in this example, I'm not using the where clause,

28:08 we can also comment out the order by clause,

28:10 we can also comment out the from clause,

28:13 so instead of selecting all the columns,

28:16 in a given table, we can select some values like one and two.

28:19 Now, if you execute this query one more time,

28:22 in the result, we get something like this.

28:24 Two columns called one and two, and in these columns we have these values.

28:29 So all these clauses are optional but in the real

28:32 world we quite often use all of them.

28:34 Now what you need to understand here is that the order of these clauses matter,

28:38 so we always have select first,

28:40 then we have from, then where, and finally order by.

28:44 We cannot change the order of these clauses, otherwise we get a syntax error.

28:48 Which basically means the syntax,

28:49 or the grammar or the structure of our simple statement is incorrect.

28:54 So it cannot be executed.

28:56 And one last thing before we finish this tutorial,

28:59 you can see I've listed all these clauses on a new line,

29:02 now technically you don't have to do this, because line breaks,

29:05 white spaces and tabs are ignored when executing SQL statements.

29:09 So we could come back here and put from in front of select,

29:14 so select store from customers all in one line,

29:17 and that's perfectly acceptable for simple queries,

29:20 but as your queries get more complex,

29:22 it's better to put each clause on a new line.

29:25 So that's all for this tutorial.

29:27 In the next tutorial, we'll explore the select clause in detail.

29:36 In this tutorial, we're going to look at the select clause in detail.

29:40 So, since our current database is SQL store,

29:43 to clean things up I'm going to remove the first statement,

29:46 you don't really need it now, also, I'm going to delete these two comments,

29:50 we just want to focus on the select clause.

29:54 Alright, so what can we do with this select clause?

29:57 Well in the last tutorial we learned that if you use an asterisk,

30:00 this will return all the columns.

30:02 Alternatively we can specify the columns that we

30:05 want and this is helpful in situations

30:06 where you have a big table with so many columns and perhaps millions of record.

30:12 If you want to bring back all that data,

30:14 that's going to put a lot of pressure on the database server,

30:17 in this case MySQL, as well as the network.

30:20 So, that's when we explicitly specify the columns that we want to get.

30:25 Let's say we want to get the first name and last name columns.

30:31 Execute the query, as you can see, we only get these two columns.

30:36 And they are in the same order we specified here.

30:39 So if we change the order and put

30:41 the last name first and execute the query again,

30:44 now we can see the last name column comes first.

30:48 Now let's add a new column, at the end,

30:51 let's get the points for each customer as well,

30:54 run the query, so these are the points

30:56 for each customer which are calculated based on their shopping.

31:00 Now let's say we want to get these points and put them

31:03 in a mathematical formula to calculate the discount

31:05 that we can give to each customer.

31:08 So here we can use an arithmetic expression, that's points plus ten.

31:12 This is an arithmetic expression.

31:13 So now we can execute this query for the first record

31:17 you can see that their points will end up being 2283.

31:20 Let's run the query one more time there you go.

31:24 Now we can put the original points column here for clarity.

31:28 So points, points plus 10.

31:30 Let's run the query one more time, now you can see the original points,

31:35 and next to that you can see the value

31:37 that we're going to use to calculate the discount.

31:40 Now here we're using the plus operator which is for addition,

31:43 we also have multiplication, division, subtraction,

31:47 and module which is the remainder of the division.

31:50 So let's change this to something more complex.

31:52 Let's say we want to get the points, multiply by 10, and then add 100 to it.

32:00 Now we can immediately see that this line one is

32:03 getting too long and it doesn't fit on the screen,

32:06 in situations like this, you can break up the select

32:08 clause by placing each column on a new line,

32:11 so, select last name, then first name points finally points times 10, plus 100.

32:20 So let's execute this query one more time.

32:24 So this is our new column with the new calculated value.

32:28 Now one thing you need to understand

32:30 in this arithmetic expression is the order of operators,

32:32 and this is based on the order of operators in math.

32:36 So in math, the multiplication and division

32:40 operators have nighter precedence then addition and subtraction,

32:45 so in this expression,

32:47 points is multiplied by 10 and then the result is added to 100.

32:52 If this is not what you want you

32:54 can always change the order by using parenthesis,

32:57 as an example, let's change this multiplication

33:00 to addition and then put that multiplication here.

33:05 In this expression, first 10 is multiplied by 100,

33:09 and then the result is added to the points.

33:12 Now let's say this is not what we want,

33:14 so we can change the order by using parenthesis here.

33:20 With these parenthesis,

33:21 first we get the points add 10 to them and then multiply the result by 100.

33:27 So this parenthesis are useful for changing the order

33:30 of operations as well as adding clarity to our code.

33:33 So someone else reading this code can easily understand.

33:36 the order of these operations.

33:37 Now let's execute this query one more time, alright,

33:40 now look at the name of this column here,

33:44 its set to the expression that we have on line 5.

33:48 That doesn't quite make sense you want to have a clear descriptive name,

33:52 so we can give this column an alias using the as keyword.

33:56 So, as and then we give it a name like discount discount _factor.

34:01 Let's run the query again.

34:04 now the name of this column has changed,

34:06 so this is the benefit of using an alias,

34:09 we can give descriptive names to the columns and the results sets.

34:13 Now if you want to have a space in the column name,

34:16 you need to surround it with quotes, either single, or double quotes.

34:21 So we put quotes here and then we can add a space in between these two words.

34:27 Let me execute the query one more time.

34:30 Now we have discount factor.

34:32 So let's quickly recap everything you learned about the select clause.

34:36 We cause an asterisk to return the columns,

34:39 or we can explicitly specify the columns that you want to return.

34:44 We can also use, arithmetic expressions here, and optionally,

34:47 we can even gibe an alias to each column in the results set.

34:50 Now there is one more thing you need to know about the select clause.

34:54 So let's delete this query and select the state column, of the customers table.

35:01 Take a look.

35:02 These are the states in which our customers our located.

35:06 Now currently in the sample data we don't have any duplicates,

35:09 in other words we don't have multiple customer in any of these states.

35:13 But for this demo I want to change the state of the first customer to Virginia,

35:18 so we end up with duplicates in the result set.

35:21 So let's open up navigator panel here's our customers table,

35:25 let's look at all the data, and here's our first customer,

35:32 here a you can see, it's located in the state

35:35 of Massachusetts now I want to change this to Virginia.

35:38 So double click VA for Virginia, enter,

35:43 now on the bottom right corner of the screen,

35:46 you should see two buttons, apply and revert.

35:49 Unfortunately I cannot show you this button

35:52 because the recording window is a bit smaller then MySQL But look down

35:57 in the bottom right hand corner, click and apply.

36:00 You're going to see a dial up box like this asking you to review the changes,

36:04 so go ahead and click that button one more time alright, now,

36:10 let's go back to our first query window, and execute this query one more time.

36:16 As you can see, the first two customers are located in Virginia.

36:20 What if you wanted to get a unique list of states in the results set,

36:25 that's when we use a distinct keyword.

36:28 So select distinct set.

36:32 With his query, we'll retrieve a unique list of states from the customers table.

36:36 So with the distinct keyword we can

36:39 remove let's execute the query one more time,

36:42 now you can see Virginia is not duplicated.

36:48 Alright here's an exercise for you.

36:52 I want you to write a SQL query to return

36:54 all the products in our database in the result set.

36:57 I want to see three columns, name, unit price,

37:01 and a new column called new price which is based on this expression,

37:07 unit price times 1.1.

37:08 So let's say you want to increase the price of each product by 10%.

37:12 With this query you want to get all

37:15 the products the original price and the new price.

37:18 So pause the video and spend one or two minutes on this exercise,

37:21 when you are done come back and see my solution.

37:28 Alright this is pretty easy,

37:30 so we start with select now what columns do we want to select?

37:35 Name, unit _price and then here we're going

37:38 to use an arithmetic expression to calculate the new price.

37:42 So we type out unit price times 1.1 and then give it a alias,

37:48 o as new_price or we can put this in quotes

37:52 and put a space between new and price.

37:54 Now where do we want to select these columns from?

37:57 From the products table, so from products.

38:00 Note that I've used upper case characters for all

38:04 the SQL keywords and lowercase characters for everything else.

38:07 So, let's go ahead and execute tis query,

38:10 this is what we get so these are all the products,

38:14 you can see their original price as well

38:16 as the new price which is 10% more expensive.

38:24 In this tutorial, we're going to look at the where clause in SQL.

38:28 So earlier I told you that we use the where clause to filter data.

38:32 For example, let's say we only want

38:34 to get the customers with points greater than 3000.

38:37 So here in the where clause you can type out the condition like this.

38:42 Points, greater than 3000.

38:44 When we execute this query, the query execution engine in MySQL is going

38:49 to iterate over all the customers in the customers table.

38:53 For each customer it's going to evaluate

38:55 this condition if this condition is true,

38:57 it will return that customer in the result set.

39:00 So let's go ahead and execute this, and here's the result,

39:05 as you can see you only have two customers with points greater than 3000.

39:12 So, thesis what we call the greater than operator

39:14 which is one of the comparison operators in SQL.

39:17 Let me show you the completeness of comparison operators, so,

39:22 we have greater than, greater than, or equal to, we have less then,

39:28 less than or equal to, here's the equality operator, and for not equality,

39:34 we can use an exclamation followed by an equal sign, or something like this.

39:39 So both of these are not equal operators.

39:42 Let me show you examples of these operators.

39:44 So I'm going to delete all these and bring back the previous query,

39:48 let's say we want to get only the customers in the state of Virginia.

39:52 So we can change our condition to something like this.

39:56 Where state equals Virginia.

40:00 Note that I've put Virginia in quotes, because this is what we call a string.

40:04 A string is a sequence of characters.

40:06 So whenever you're dealing with a sequence of characters,

40:10 or basically textural data,

40:11 we need to enclose your values with either single or double quotes.

40:16 But quite often, by convention,

40:18 we use single quotes so let's execute this query and here's the result,

40:24 you can see we only have these two customers with ID 1 and 2,

40:28 who are located in Virginia.

40:30 And it doesn't matter if you use upper case or lower case characters,

40:34 so if you type out the a in lower case

40:37 and execute the query you get the exact same result.

40:40 Now what if you want to get all the customers outside of the state of Virginia,

40:45 you can use the not equal operator.

40:48 So, we can either prefix this with an exclamation or use this other notation.

40:56 Either way we get the same result.

40:58 So these are the customers that are not located in Virginia.

41:02 Now we can use these comparison operators for date values as well.

41:07 For example, let's say you want to get

41:10 only the customers born after January 1st, 1990.

41:13 So we change out condition to first date,

41:16 greater then, once again we use quotes,

41:20 for representing date values even though dates are actually not strings.

41:24 But in the sequel language, we should enclose dates with quotes,

41:28 so, here we type out 1990, 01 for January, -01 for date.

41:36 So this is the standard or default format for representing dates in MySQL.

41:41 4 digits for the year, two digits for the month, and 2 digits for the day.

41:46 So let's go ahead and execute this query.

41:48 I actually made a mistake here, so we don't see the result,

41:52 instead we see the action output, or the output window.

41:56 If you scroll to the bottom, you can see the details of the error.

41:59 So here I used the wrong name for the column,

42:02 which should separate these two words with an underscore.

42:05 That is the name of our column.

42:07 So, let's execute the query one more time,

42:10 so we only have 3 customers born after January 1st 1990.

42:14 So these are examples of comparison operators in SQL.

42:18 In the next tutorial I'm going to show

42:21 you how to combine multiple conditions when filtering data.

42:28 Alright, here's your exercise.

42:29 I want you to write a query to get the orders that are placed this year,

42:33 so look at the orders table, see what columns to do we have there,

42:37 and based on that write query with a where clause.

42:44 So here's the orders table, in this table we have this column, order date.

42:48 We can use this problem to see the orders

42:50 that are placed this year so here's our query, select,

42:54 start from order where order_date, is greater than or equal to 2019,

43:03 assuming this is the current yer, so 2019, 0101.

43:08 Now since currently we are in the year 2019,

43:11 this query will return all the orders placed this year.

43:14 But next year this query is not going to give us the right result.

43:17 But don't worry about it, later in the course I will show you how to write

43:21 a query to get the orders placed in the current year,

43:24 so for the purpose of the new exercise, this is a valid solution.

43:27 Now let's execute this query and see what we get.

43:30 So, we have only one order, order ID 1, that is placed in the current year.

43:40 In this tutorial I'm going to show you

43:43 how to combine multiple search conditions when filtering data.

43:47 So, let's say you want to get all

43:50 the customers that were born after January 1st,

43:52 1990, who also happen to have more then 1000 points.

43:56 So this is where we use the and operator.

44:00 So we type out and, and after we type out another condition.

44:04 Like points, greater than 1000.

44:08 Now when we execute this query,

44:10 we only get customers who have both these conditions.

44:13 Let's take a look, so execute we only have 2 customers

44:17 and if you look both these people are born after 1990,

44:21 and they have more then 1000 points.

44:24 So this is the and operator.

44:26 When we use this operator, both these conditions should be true.

44:30 In contrast to the and operator, we have the or operator.

44:34 So with or if at least one of these conditions is true,

44:38 that row will be returned in the result set.

44:40 Let's take a look.

44:42 Now we execute this query again, 2 records we have quite a few records.

44:46 So for example, we have this person who's not born after 1990

44:52 but if you look at their points they have more then 1000 points.

44:56 So any customer records that satisfies at least

44:58 one of these conditions will be returned.

45:00 Now let's take this to the next level.

45:02 Let's say we want to get customers who are either born after 1990,

45:07 or they should have at least 1000 points and live in Virginia so this is

45:13 how we do this, we type out and and then we add another condition,

45:19 state equals Virginia.

45:20 Let's execute this query and see what we get.

45:23 We only get 4 records,

45:24 so these are the customers that are either born after 1990

45:28 or they have more then 1000 points and live in Virginia.

45:32 If you look at the first customer here this person is not born after 1990.

45:37 But you can see that she lives in Virginia and she has more than 1000 points.

45:42 So the last two conditions.

45:44 are true for this customer.

45:46 Now when combining multiple logical operators.

45:48 You need to be aware of the order of these operators.

45:52 So earlier I talked about he order of arithmetic operators.

45:54 I told you that multiplication have

45:57 a higher order then addition and subtraction.

46:00 And we can use parenthesis to override the default order.

46:05 We have the same concept in logical operator

46:08 So the and operator is always evaluated first.

46:12 So when this query is executed,

46:14 the query execution engine first evaluates this condition,

46:17 because here we're using an and it doesn't matter

46:20 that we typed out this condition after the first condition.

46:24 Because the and operator has a higher precedence.

46:26 Now you can always change the order using parenthesis.

46:29 And this also makes your code cleaner and easier to understand.

46:33 So here we can put parenthesis, around these last two conditions,

46:40 and also we can put these in a new line for clarity.

46:44 Something like this.

46:46 So anyone who reads this code can easily

46:49 understand what is the intent of this query.

46:52 Now we also have logical operator called not.

46:56 And we use that to negate a condition.

46:59 So, I'm going to simplify our where clause.

47:03 Let's say we're searching for customers who were born

47:06 after 1990 or we have more then 1000 points.

47:10 If we execute this query we get these people.

47:14 Customers with ID 1, 3, 5 and so on.

47:18 Now we can use the not operator to negate this condition.

47:21 So we apply not here,

47:23 and preferably we also put parenthesis around this condition.

47:28 Now when we execute this query,

47:29 we see other customers that are not in the current results set.

47:34 Let's take a look.

47:35 So, instead of customers with id's 1356 and so

47:40 on, we get customers with id's 2 4 and 10.

47:44 Now technically these customers were born before 1990.

47:48 And we have less then 1000 points.

47:50 So if you look here,

47:52 this first customer was born before 1990 and he has less then 1000 points.

47:57 How do they know that?

47:59 Let me show you a trick that I learned in math.

48:02 Whenever you have a not operator.

48:04 You can simplify your expression like this.

48:06 We apply the not operator to the first condition.

48:09 People who were born after 1990, how can we negate this condition?

48:15 Well, the greater than operator becomes less then or equal to.

48:20 That is the inverse of that condition.

48:23 Now we apply the not to or to negate the or.

48:26 What do we get?

48:28 We get and.

48:30 Finally apply the not operator on the last condition.

48:34 We both have more then 1000 points.

48:36 When we negate this condition we get customers

48:38 with less then or equal to 1000 points.

48:41 Now, you can remove the not operator to simplify this, we don't need

48:46 parenthesis anymore because we only have 2

48:48 conditions that are combined with an end.

48:50 Here is the result.

48:52 As you can see, this is much easier to read and understand people

48:56 who were born before this day and they have less then 1000 points Alright,

49:04 here's your exercise.

49:06 From the order items table, get the items for order number 6,

49:12 where the total price for the total item is greater then 30.

49:20 Alright, here's the order items table,

49:23 in this table we have these columns order id,

49:26 product id, quality, and unit price.

49:28 If we multiply the quantity by unit price

49:31 we can get the total cost of that item.

49:34 And then we can compare it with 30.

49:36 So, let's go ahead and write this query.

49:40 Select star from order items.

49:47 Where here we need two conditions one is for order, so order _id should be 6,

49:54 and the second condition we want to calculate the total price.

49:58 So we get the unit price multiply it

50:01 by quantity and this value should be greater than 30.

50:06 So as you can see, we can use an arithmetic expression in the where clause.

50:11 It's not limited to the select clause.

50:13 Okay?

50:13 Now let's execute this query and see what we get.

50:17 We should get only 1 item, that is for product 1,

50:21 here in quantities 4 and unit price is just over 8 dollars,

50:25 so the total price for this item, is greater than 30.

50:28 Hey guys, Mosh here.

50:32 In case you haven't seen my website yet, head over to codewithmosh.com.

50:36 This is my coding school where you can find

50:39 plenty of courses on web and mobile application development.

50:42 In fact recently I published a complete SQL course that is about 10 hours long,

50:48 and it teaches you everything you need to know

50:50 from the basic to advanced topics such as database design,

50:54 security, writing complex queries, transactions, events, and much much more.

50:58 These are the topics that every software engineer must master.

51:02 This YouTube course you're watching,

51:04 is the first 3 hours of my complete SQL course that is about 10 hours long.

51:10 So if you want to master SQL and get job ready,

51:13 I highly encourage you to enroll in my complete SQL course.

51:16 You can watch it anytime, anywhere, as many times as you want,

51:20 you can watch it online or download the videos.

51:23 The course comes with a 30 day money back guarantee,

51:26 and a certificate of completion that you can add to your resume.

51:30 The price for this course is $149 dollars,

51:32 but the first 200 students can get it for just over 10 dollars.

51:36 So if you're interested the link is below this video.

51:44 In this tutorial I'm going to show you how to use the in operator in SQL.

51:49 So, as an example, let's say you want to get

51:53 the customers that are in Virginia or Florida, or Georgia.

51:57 One way to write this query is like this.

52:00 So, where state equals Virginia,

52:03 or a state equals Georgia, or a state equals Florida.

52:08 Now people who are new to the SQL language

52:11 or programming in general find this expression a little bit strange.

52:16 They ask, "Mosh, why can't we write this expression

52:19 like this?" where state equals Virginia or Georgia or Florida.

52:24 Here's the reason, we use the or operator to combine multiple conditions.

52:29 So here we have a condition or an expression more accurately,

52:33 but on the right side of this or operator we have a string.

52:37 In SQL we cannot combine a string with a boolean expression

52:41 that expresses a boolean value which can be true or false.

52:44 So that is why we have to write our query like this.

52:48 So we have multiple expressions or multiple conditions

52:52 and we're combining them using the or operator.

52:56 So, now if we execute this query we get these customers here.

53:00 customers here but there is a shorter and cleaner way to get the same result.

53:04 Instead of combining multiple conditions using

53:06 the or operator we can use the in operator.

53:10 So, where state is in and then in parenthesis we add all the values,

53:16 like Virginia, comma, Florida, comma Georgia and the order doesn't matter,

53:22 this query is exactly equivalent to what we had earlier,

53:25 but a you can see it's shorter and easier to understand.

53:28 So, let's execute it, look, we get the exact same result.

53:32 Now here we can also use the not operator.

53:34 Let's say you want to get the customer's outside of these states,

53:38 so we can use rare state, not in this list.

53:44 Now if you execute this query,

53:45 we get customers who are located in Colorado, Texas and so on.

53:49 So use the in operator whenever you want

53:52 to compare an attribute to a list of values.

53:56 Now here is your exercise.

54:00 I want you to write a query to get the products

54:03 where their quantity in stock equals on of these values.

54:08 49, 38, and 72, so pause the video,

54:10 do this exercise, and then come back and continue watching.

54:16 Alright, this is pretty easy,

54:18 so we do a select star to get all the columns from the products table,

54:24 where quantity in stock in we use

54:28 the in operator to compare this attribute with these values.

54:32 49, 38, and 72, let's execute the query,

54:36 we get only 2 records because we don't have

54:40 a product with quantity in stock equal to 72.

54:48 In this tutorial, we're going to look at the between operator in SQL,

54:52 so that means we want to get the customers

54:55 who have more then 1000 and less then 3000 points.

54:58 What made you write these queries like this?

55:02 Where, points, greater than thousand,

55:04 more accurately greater than or equal to 1000.

55:08 And, points less then or equal to 3000.

55:12 When we execute this query we get how many,

55:16 we get 4 people that satisfy my criteria.

55:20 Now whenever you're comparing an attribute with a range of values,

55:24 you can use the between operator, and that makes your code shorter and cleaner.

55:28 So, we can rewrite this expression like this, where points,

55:32 between 1000 and 3000.

55:36 This is exactly equivalent to what we had before,

55:39 so these range values are also inclusive,

55:41 so that means points is going to be greater than

55:44 or equal to 1000 or greater than or equal to 3000.

55:48 Let's execute the query, we get the exact same result.

55:56 Alright, now as an exercise, I want you to write a query,

55:59 to get the customers that are born

56:01 between January 1st 1990 and January 21st, 2000.

56:08 Alright, so we start the select star from customers,

56:16 where birth_date between so what matters here is

56:21 we can use the between operator as well.

56:24 It's not limited to using numbers.

56:26 So with birth date between now we need to supply two date values,

56:30 so as I told you before,

56:32 the format for dates is four digits for the year, so 1990,

56:36 two digits for the month, and two digits for the date.

56:40 So, the birthdate should be between this value and here's a second value.

56:46 2000, 0, 1, and 01.

56:48 Let's execute this query, we get only 3 people who match this criteria.

57:00 In this tutorial, I'm going to show you how

57:03 to retrieve rows that match a specific string pattern,

57:07 for example, let's say we only want to get

57:10 to customers who's last name start with 3.

57:12 So, here in the where clause we type out where

57:16 last name this is where we use the like operator,

57:20 and right after that we have a string pattern,

57:23 so, you want to get the customers who's last name start

57:26 with b and we have any number of characters after b.

57:30 So use the percent sign to indicate any number of characters,

57:34 you may have one character after b or no characters or 10 characters,

57:39 with this pattern, you get all the customers who's patterns start with b.

57:44 And also it doesn't matter if it's an upper case or lower case b.

57:48 So, let's execute this query, there you go,

57:50 so you only have 3 customers who's last name starts with b.

57:55 As another example, let's say we only want

57:57 to get the customers who's last name starts with brush.

58:00 So, we change our pattern to brush, percent.

58:04 Now, let's execute the query, we only get this one customer here.

58:09 Now this percent sign doesn't have to be

58:12 at the end of the pattern, it can be anywhere.

58:14 For example, let's say we want to search

58:16 for customers who have an e in their last name,

58:18 whether it's at the beginning in the middle or at the end,

58:22 so we change our pattern to percent e percent this pattern

58:26 means we can have any number of characters before or after b.

58:31 Let's execute the query,

58:33 these are the customers that have a b somewhere in their last name.

58:37 It doesn't matter if b is in the beginning or in the middle or at the end.

58:42 Let's look at another example,

58:44 I want to get all the customers who's last names end with y,

58:49 so here's the pattern we use, let's execute this query,

58:53 so we have 5 customers who's last name ends with a y.

58:58 So this is how we use the percent sign.

59:00 Now we also have an underscore and that matches a single character so

59:06 with this pattern you get customer who's

59:08 last name is exactly two characters long,

59:12 we don't care what the first character is, but the second character should be y.

59:16 Let's execute his for you.

59:17 Obviously we don't have any customers who's last name matches this pattern.

59:21 But if you change this pattern to 5 under score,

59:26 so 1234 5, followed by a y we should get these customers.

59:33 So their last name is exactly 6 characters,

59:36 we don't care about the first 5 characters, but all of them end with a y.

59:42 Now, as another example, we can replace the first underscore with b,

59:46 and that means we want to get the customers who's last names start with b,

59:51 and after b we have exactly 4 characters followed by a y.

59:56 Let's execute this query.

59:57 So we only have one customer, that matches this pattern.

1:00:01 So this is how we use the light operator,

1:00:05 use the percent sign to represent any number

1:00:09 of characters and an underscore to represent a single character.

1:00:16 Now this like operator in MySQL is an older operator but we also have a newer

1:00:21 one that is more powerful and it allows us to search for any string patterns.

1:00:25 And that's what I'm going to show you next.

1:00:28 Alright.

1:00:32 Here I'm going to give you two exercises for the like operator.

1:00:36 First, I want you to get the customers who's addresses contain trade or avenue,

1:00:40 and next I want you to get the customers who's phone numbers end with 9.

1:00:52 Alright, let's get started with the first exercise.

1:00:55 So start from customers where address like now here

1:01:01 we want to use a search pattern like this.

1:01:04 You want to have tray(?) but tray can be anywhere in the address.

1:01:07 So, we put a percent before and after tray.

1:01:12 Next, we should use the or operator to search for another pattern.

1:01:16 Or address like, let me put this on a new line that is better,

1:01:23 address once again, percent, avenue percent, that's it.

1:01:28 So, let's execute this query, here is the result,

1:01:33 you should get the customers with ID's 2, 9, and 10.

1:01:37 If you look at their addresses,

1:01:39 all of them have either tray or avenue in their address.

1:01:44 Now let's work on the second exercise,

1:01:46 you want to get the customers who's phone numbers end with 9.

1:01:50 That is pretty easy, so, let me change our where clause.

1:01:55 Where phone once again we use the like operator and a percent, followed by a 9.

1:02:01 That's all you have to do.

1:02:03 Let's execute the query.

1:02:04 So, here's the result, customers with id's 3 and 7, their phone numbers, and 9.

1:02:12 So this is how we use the like operator.

1:02:14 And by the way you can always use the not operator here let's

1:02:18 say you want to get the customers who's phone numbers don't end with 9,

1:02:24 so we simply prefix like with not.

1:02:27 Now, if we execute this query one more time,

1:02:30 we get all the other customers in the database.

1:02:36 In the last tutorial you learned about the like operator in SQL.

1:02:42 So as another example let's say you want to search

1:02:45 for the customers who have the word field in their last name.

1:02:49 So we type out the where clause like this, where last name, like,

1:02:55 percent, field, percent, so the word field, can be anywhere in the last name.

1:03:02 Let's execute this query, we get only one customer, beautiful.

1:03:05 Now we also have another operator in MySQL,

1:03:09 that is reg x which is short for regular expression,

1:03:13 and regular expressions are extremely powerful

1:03:16 when it comes to searching for strings.

1:03:18 So they allow us to search for more complex patterns.

1:03:21 Here's an example.

1:03:22 If I want to rewrite this last where

1:03:26 clause using a regular expression it looks like.

1:03:30 Where last name ragexp now here in our string pattern

1:03:36 we don't have to type out the person's size we

1:03:39 only type out field so what we have on line

1:03:42 4 is exactly identical to what we have on line 3,

1:03:46 let's execute this query we get the same result,

1:03:50 beautiful, now here in regular expressions,

1:03:52 we have additional characters that we don't have when we use the like operator.

1:03:56 For example, we can use the carrot sign to indicate the beginning of a string.

1:04:01 So if I put a carrot just before the word field,

1:04:06 that means our last name must start with field.

1:04:10 Obviously, if you execute this query,

1:04:12 we don't get anyone that matches this criteria,

1:04:14 so we use the carrot sign to represent the beginning of a string.

1:04:19 We also have a dollar sign to represent the end of a string.

1:04:24 So this pattern means the last name must end with field.

1:04:28 Let's execute this query, you get the same result as before.

1:04:31 Now we can also search for multiple words here,

1:04:34 for example let' say we want to find the customers who

1:04:36 have the word field or mac in their last name So,

1:04:40 we use a pipe, a vertical bar, and type out another pattern.

1:04:46 Let's execute this query, so here we have two customers,

1:04:50 one of them has the word mac, the other has the word field and the last name.

1:04:56 Now we can take this to the next level.

1:04:58 Let's say we want to find the customers who have the words field,

1:05:02 or Mac, or rows in their last name.

1:05:05 Let's execute the query we get 3 customers.

1:05:08 Beautiful.

1:05:08 So we use a pipe or a vertical board to represent multiple search patterns.

1:05:14 Now as another example,

1:05:16 we can change our first search pattern to something like this.

1:05:20 Now this pattern means this last name should either start with the word field,

1:05:25 or it should have the word mac in it or it should have the word rows.

1:05:31 Let's execute the query, now we get only two customers,

1:05:34 because our customer with the last name brush field doesn't match this pattern.

1:05:40 However, if we change our first pattern to field$

1:05:44 and execute the query we get 3 people here, 3 customers.

1:05:48 So this is how we can combine

1:05:51 multiple special characters when building a complex pattern.

1:05:54 Now let's look at another example,

1:05:56 let's say you want to search for customers who have an e in their last name.

1:06:00 So these are all the people, alright,

1:06:02 now let's say you want to make sure that before the letter e,

1:06:06 we should either have a g or an i.

1:06:09 So this is where we use square brackets.

1:06:12 And inside the brackets we add multiple characters like g, i,

1:06:16 m and that matches any customers who have ge or ie or me in their last name.

1:06:25 So any of these characters can come before e.

1:06:28 Now, let's execute this query, there you go, we only get 2 customers,

1:06:34 and the first example before e we have i

1:06:38 which is one of the characters inside the brackets, in the second example,

1:06:42 before e we have a g which is also another valid character before and once

1:06:48 again the square brackets don't have to be before we could add them after e,

1:06:54 any customers who have e followed by an f or an m or a q in their last name,

1:07:00 can be returned with this pattern,

1:07:04 if we don't have anyone in the database so this is how we use square brackets,

1:07:08 now we can also supply a range of characters,

1:07:11 for example we can have e and just before e

1:07:14 you can have as many characters from a to h,

1:07:18 you don't have to type them out explicitly like abcdefg,

1:07:23 that's very verbose, so, we can type out a to h.

1:07:28 And then if we execute this query we get these three people.

1:07:32 So, let's quickly recap everything you

1:07:34 learned about regular expressions in this tutorial.

1:07:37 Use a carrot to represent the beginning of a string, so beginning,

1:07:41 we use a dollar sign to represent the end of a string,

1:07:45 we use a vertical bar or pipe,

1:07:48 to represent a logical or so we can supply multiple search patterns,

1:07:53 we use square brackets to match any single characters listed in the brackets,

1:07:59 and finally we use square brackets with a hyphen to represent a range.

1:08:04 So any characters from a to f.

1:08:08 Technically MySQL supports more special characters but quite honestly,

1:08:12 the ones that I've listed in this tutorial are

1:08:14 the ones that you'll be using 90% of the time.

1:08:16 So just memorize these and you're good to go.

1:08:24 With honesty a lot of beginners

1:08:26 find the syntax for regular expressions confusing,

1:08:28 so in this video I'm going to give you 4 exercises

1:08:31 that I have carefully designed to help you quickly learn about this syntax.

1:08:35 Here's the first exercise,

1:08:37 get the customer's who's first names are Elka or Ambur.

1:08:40 And note that this is Ambur with a U.

1:08:44 Now for the second episode return the customer's who's names end with ei or on.

1:08:52 Here's the third exercise get the customer's who's

1:08:54 last names start with my or it contains se,

1:08:58 and finally as the last exercise return the customer's who's

1:09:03 last names contain e followed by r or a u.

1:09:08 So, go ahead and spend 2-3 minutes on this exercise,

1:09:12 when you're done come back and continue watching.

1:09:20 Alright, let's knock out the first exercise.

1:09:22 So we'll get allows, there's a c here, so let's start from regular expression,

1:09:32 and here's our pattern,

1:09:34 you're going to search for two words either Elka or Ambur.

1:09:40 As simple as that.

1:09:41 Let's execute this query, we should get two customers,

1:09:44 there you go, Ambur and Elka.

1:09:47 Alright.

1:09:48 Now, let's knock out the second exercise.

1:09:50 So I'm going to delete these we don't need them anymore.

1:09:53 So we want to get the customers select start from customers,

1:09:59 where last name should end with either ey or om.

1:10:04 So, in the search pattern we type out ey followed by a dollar sign to indicate

1:10:10 the end of a string then we add

1:10:13 a vertical bar to supply the second search pattern.

1:10:16 So On and once again dollar sign.

1:10:20 Let's execute this query,

1:10:21 oops I forgot to type out regular expression, there you go.

1:10:27 So, Let's execute this query,

1:10:30 and you should get these four customers with ID's one three, five and seven.

1:10:37 The first three, five, and seven, the first three, their last names end with ey,

1:10:41 and the last customer, his or her last name ends with on.

1:10:46 Alright, Now, let's work on the third exercise.

1:10:49 So I'm just going to change the regular expression here,

1:10:52 we want to get the customers whose last names start with my or contains se.

1:10:58 So we use a carrot to indicate the beginning of a string

1:11:03 so it should start with my, or it should contain se,

1:11:08 again, very easy, let's execute this query

1:11:12 and we get the customer's with ID's 4, 8, and 10 and finally we want to get

1:11:21 the customers who's last names contain e so,

1:11:24 let's change the search pattern, we should have a b, followed by r or u.

1:11:29 Now there are two ways to write this regular expression,

1:11:33 we can use square brackets so we have b followed by r or u,

1:11:38 that's one way, or the other way is to use a vertical bar.

1:11:43 So b r or bu.

1:11:45 These are both valid solutions.

1:11:48 So, I hope you knocked out these exercises,

1:11:49 in the next tutorial I'm going to show

1:11:51 you how to get the records with missing values.

1:12:00 In this tutorial, I'm going to show you

1:12:02 how to look for records that miss an attribute.

1:12:04 For example, if you select all the customers in our database,

1:12:08 you can see that the customer with id 5 doesn't have a full number.

1:12:12 So if you look closely here, you can see the value of null.

1:12:16 Null means the absence of a value.

1:12:18 Now let's say we want to search for all the customers who don't have a phone.

1:12:22 Perhaps you want to send them an email and say hey,

1:12:24 your phone is missing in our system.

1:12:27 So how can we get these customers?

1:12:29 That is very easy?

1:12:30 We use the is null operator, so in the where clause,

1:12:34 we type out where phone is null.

1:12:38 Now let's execute this query, we only get one customer who doesn't have a phone,

1:12:44 now here we can also use the not operator

1:12:47 to get the customers who do have a phone.

1:12:50 So we change the conditions to is not null.

1:12:53 Let's execute the query, now in the query results,

1:12:58 every customer does have a phone number.

1:13:04 For this exercise, I want you to write a query

1:13:08 to get the orders that are not shipped yet.

1:13:10 This is a very useful query that is used in a lot of real applications.

1:13:14 For example, let's say you're an admin for online shop.

1:13:16 You want to see the orders that are not shipped yet, so you can see them all.

1:13:20 So write a query, and get these orders.

1:13:28 So here we have the orders table,

1:13:30 let's have a quick look at the data in this table.

1:13:33 So if you pay close attention you see some of these orders don't have a date.

1:13:40 And these orders also don't have a shipper id

1:13:43 which is a unique number for identifying the shippers.

1:13:46 So any order that misses the value for the ship date or shipper ID,

1:13:52 is considered an order that is not shipped.

1:13:54 So let's go ahead and write a query to get these orders.

1:13:58 So back to our query editor select star from orders where ship_date is null.

1:14:08 You could also write shipper id is null.

1:14:11 They're both equally correct.

1:14:12 So let's execute this query, and you should get 5 orders.

1:14:17 Orders 1, 3, 4, 6, and 8.

1:14:24 In this tutorial I'm going to show you how to sort data in your sequel queries.

1:14:30 So here in your have a query

1:14:32 to select all the customers from the customers table,

1:14:35 if you look at the query result,

1:14:36 you can see that our customer's or sorted by id,

1:14:40 so we have customers 1, 2, 3, 4, and so on.

1:14:43 This is the default sort column.

1:14:45 But we can always change this using the order by clause.

1:14:49 But first, let me explain why the customer id column is the default sort column.

1:14:54 So first of all the first I'm going to open up

1:14:57 the navigators panel on the left side here's the customers table,

1:15:00 now let's click on this middle icon here that looks like tool.

1:15:04 This opens up our customers table in the design mode,

1:15:08 here we can change our columns we cana dd new column

1:15:11 or remove existing ones or change that name and order and so on.

1:15:16 Now if you pay close attention you can see a yellow key

1:15:19 just before This means that this column is the primary key column.

1:15:24 For this query.

1:15:25 So in relational databases every table should have a primary key column,

1:15:30 and the values in that column should

1:15:32 uniquely identify the records in that table.

1:15:35 So back to our query window,

1:15:37 you can see that the values in this column uniquely identify each customer,

1:15:41 so the customer id column is the primary key column in this table,

1:15:46 and that is why when you write a query against this table,

1:15:49 our customers will sort it by id by default.

1:15:52 Now, let me show you how to sort customers by a different column.

1:15:56 So, here in the order by clause,

1:15:58 you type out the name of another column, like first name.

1:16:01 Let's execute the query,

1:16:02 now we can see our customers are no longer sorted by their id,

1:16:07 instead they are sorted by their first name in ascending order.

1:16:11 Now, if you want to reverse the sort order,

1:16:14 simply type out de, e, sc, which is short for descending.

1:16:19 Now, you're sorting the customers in this order, okay,

1:16:24 we can also sort data by multiple columns, for example,

1:16:28 let's say first we want to sort customers based on their state,

1:16:32 and then within each state, we want to sort them by their first name.

1:16:36 So, we type out multiple columns here, state and first name.

1:16:41 Let's execute the query now,

1:16:44 you can see that the first state we have here is california,

1:16:47 followed by Colorado, and new here in Florida,

1:16:50 you have two customers and these customers are sorted by their first name.

1:16:55 Let's have a close look here,

1:16:56 so first we have Ambur and then we have other customers here.

1:17:00 Now we can also use the descending argument anywhere here.

1:17:04 For example, we can sort these customers by their state in descending order.

1:17:08 And then sort them by their first name in ascending order or once,

1:17:12 again in descending order.

1:17:13 So there are various ways we can sort data.

1:17:16 Now one of the differences between MySQL, and other database management systems,

1:17:21 is that in MySQL you can sort data by any

1:17:25 columns whether that column is in the select clause or not.

1:17:28 For example, let's say you only want to select

1:17:32 the first and last name for each customer.

1:17:34 Now we can sort the result by any columns in this table,

1:17:37 they don't have to be first name and last names For example,

1:17:40 we can sort them by their birthdate,

1:17:44 take a look, so this is a valid query in MySQL,

1:17:47 but other databases management sometimes yell at you

1:17:49 when you update a query like this.

1:17:52 Now we can also sort data by an alias for example,

1:17:55 here in out select clause let's add the number ten and give it a alias.

1:18:00 As let's say points, so points is not a valid column in this table,

1:18:06 it's simply an alias or expression, in this case a simple number.

1:18:10 And here we could have a complex mathematical expression,

1:18:12 it doesn't really matter,

1:18:14 we can still sort data by Alias, so we can order by points and then first name.

1:18:21 Once again this is a valid, query from MySQL's point of view.

1:18:25 Now one last thing before we finish this tutorial,

1:18:28 I've seen some tutorials that teach you how

1:18:30 to sort data by calling positions, for example, here we can order it by 1, 2,

1:18:36 and that basically means sort the data by the first name,

1:18:40 and then, the last name.

1:18:41 So these are the orders of these columns, if you execute this query,

1:18:45 you can see that our customers are sorted

1:18:47 by their first name and then last name.

1:18:49 Why this approach works, it's something that you should avoid.

1:18:52 Because if in the future you come back here and add a new column,

1:18:57 in front of the first name column, let's say first, date.

1:19:00 Now our customers are no longer sorted in this order.

1:19:05 So sorting data by calling positions produces

1:19:08 unexpected results and is something you should avoid.

1:19:12 always sort by column names like first,

1:19:20 Alright, here's your exercise for this tutorial.

1:19:22 In this database, we have this table, called order items,

1:19:25 where we find the items for each order.

1:19:27 Now, I've written the query that you cannot see here,

1:19:30 because that's the solution to the exercise I'm going to give you,

1:19:33 that query produces this results.

1:19:35 So we only have the items for the order with ID 2,

1:19:39 and we have sorted these items based on the total price for each item.

1:19:44 So the total price for each item equals quantity times unit price.

1:19:48 In this case the total price of product 1 is just over 18 dollars.

1:19:53 So go ahead and write a query to select all the items for order with ID2.

1:19:59 And sort them by their total price in descending order.

1:20:08 Alright, let's select everything from order items, where order id equals 2.

1:20:16 that returns all the items for this order.

1:20:18 Now we want to make sure to sort them by their total price,

1:20:23 so, here in order by clause, we write an expression.

1:20:27 Quantity times unit price, this returns the total price for each item.

1:20:32 And then we add the descending argument here.

1:20:35 So, once again the expression that we use

1:20:37 in the order by clause doesn't have to be column name,

1:20:40 it can be an alias or an arithmetic expression like this.

1:20:44 Let's go ahead and execute this query.

1:20:46 This is what we get now for clarity,

1:20:48 I would like to add another column in the result So,

1:20:52 let's say quantity times unit _price.

1:20:56 We give it an alias like total price.

1:21:00 Let's execute the query, you can clearly see,

1:21:04 that this data ia sorted by the total rice in descending order.

1:21:08 However, there is a bit of duplication in our query,

1:21:11 you have to repeated this expression in two places.

1:21:14 So now we can simplify our by clause, by using an alias that is total price.

1:21:20 And we gwet the exact same result.

1:21:22 Net I'm going to show you how to mimic

1:21:25 the number of records returned form your queries.

1:21:32 In this tutorial, I'm going to show you

1:21:34 how to limit the records returned from the query.

1:21:37 For example, when we return this query we get all the customers in the customer,

1:21:42 table, so we have to ten customers here.

1:21:45 Now what if we only want to get, the first 3 customers.

1:21:49 That's where we use the limit clause.

1:21:52 Let me show you.

1:21:53 So, after from, you type out limit 3,

1:21:56 and this will return only the first 3 customers.

1:22:00 Now if the argument that we pass here is

1:22:04 greater than the number of records that our query produces,

1:22:07 we'll get the records in the query result.

1:22:09 For example, if I pass 300 here,

1:22:12 obviously we don't have 300 customers in this table,

1:22:15 so when we execute this query.

1:22:17 We get all the ten customers in this table.

1:22:20 So this is how the limit clause works.

1:22:24 Now here we can optionally supply an offset,

1:22:26 and this is very useful in situations where you want to paginate the data.

1:22:31 For example, let's say we have a website, and on this website we have a web page

1:22:35 for the user to see all the customers int he database.

1:22:39 Now for simplicity let's imagine, we want to show only there customers per page.

1:22:43 So, what are the customer's you're going to have on page 1?

1:22:46 We're going to have customers 123.

1:22:50 On page 2 we're going to have customers 456

1:22:55 and on page 3 we're going to have customers 789.

1:23:00 Now, let's say we want to write a query to retrieve the customers on page 3.

1:23:04 How can we do that?

1:23:05 Well, you want to skip the first 6 records and then pick 3 records.

1:23:11 So, we change our limit clause to something like this.

1:23:15 6 and 3.

1:23:16 So 6 is what we call an offset.

1:23:20 And that basically tells MySQL to skip

1:23:22 the first records and then take 3 records.

1:23:26 Let's execute this query.

1:23:28 Alright, now we get customers 789.

1:23:36 Now, for your exercise, I want you to get the top 3 loyal customers.

1:23:43 These are the customers that have more points then everyone else.

1:23:52 Alright, first we select everything from the customers table,

1:23:56 now we need to order these customers by their points, in descending order.

1:24:01 So if you look at the query result you

1:24:04 can see that customers are sorted by their loyalty.

1:24:07 So, the most loyal customers come first,

1:24:10 now we want to pick only the first three customers,

1:24:14 and that's where we sue the limit clause, so limit, let's execute this query.

1:24:22 And these are the most loyal customers, customers with id's 56 and 3.

1:24:28 Now, here's one thing I want you to remember

1:24:31 and that is the order of the limit clause.

1:24:34 The limit clause should always come at the end.

1:24:37 So first we have the select clause and then we have

1:24:40 from optionally you can have where followed by order by and finally limit.

1:24:46 Order of these clauses matter If you change

1:24:48 the order MySQL is going to yell at you.

1:24:51 So pay attention to the order when writing your queries.

1:25:00 So far you have only selected columns from a single but in a real world,

1:25:04 we quite often select columns from multiple tables.

1:25:06 And that's what I'm going to show you over the next few tutorials,

1:25:09 so on the left side if you put our orders table.

1:25:13 Let's select all the data here, in this table we're using the customer id column

1:25:18 to identify the customer that has placed each order.

1:25:21 Now as I have told you before we

1:25:24 don't store customers information here like their phone number,

1:25:26 their email their address, because this information can change in the future.

1:25:31 And if even a customer has placed multiple

1:25:34 orders then we have to change multiple records, you don't want to do that.

1:25:38 That's why we have separate tables for customers and orders.

1:25:41 Now in this tutorial I'm going to show you

1:25:44 how to select the orders in the orders table,

1:25:46 but instead of showing the customer id, we showed full name for each customer.

1:25:50 So lets go back to our query window.

1:25:54 Okay, so, we want to select everything from the orders table.

1:25:58 Now we should combine the columns in this table

1:26:01 with he columns in the customers table.

1:26:03 That is where we use the join keyword.

1:26:06 And here we can optionally type inner join,

1:26:08 because we in SQL we have two types of join inner join and over join.

1:26:13 You'll look at outer joins later in this section,

1:26:16 so for now we are only using inner join and these inner keyword,

1:26:20 is actually optional, so we don't have to type it.

1:26:24 So, you want to join the orders table with the customer table.

1:26:28 Now, on what basis do we want to join these tables?

1:26:32 Well, here in the customers table, we have this customer id column.

1:26:36 So if you put these two tables next to each other you

1:26:38 want to line up the records such that the customer ID's are equal.

1:26:42 That is where we use the on phrase.

1:26:46 So after we type out a condition,

1:26:49 here's a condition we need to type out, orders.customer_id.

1:26:52 Should be equal to customers.customer id.

1:27:00 Now, this is getting outside of the screen, so let's break up the line,

1:27:04 that's better, so with this query, we're telling MySQl that hey,

1:27:09 whenever you're going the orders table with the customers table,

1:27:13 make sure that the customer ID column,

1:27:16 in the orders table, equals the customer ID column, in the customers table.

1:27:22 Now let's execute this query, look at the result,

1:27:28 since we are selecting everything here,

1:27:30 the first few columns are from the orders table,

1:27:33 because we have listed that first now

1:27:36 after all the columns in the customer table.

1:27:40 So customer ID first name, last name and so on.

1:27:44 Now let's simplify the result set and select only border id, first name,

1:27:49 and last name, so query we select order ID, first name, and last name.

1:27:57 Now let's execute the query that is better.

1:28:00 So next to each order ID,

1:28:02 you can see the name of the customer that placed that order.

1:28:06 Now what if you want to display the customer ID here as well.

1:28:10 Well, let's put that here and see what happens.

1:28:13 Customer ID, execute the query we get an error,

1:28:17 so if you look at the output window,

1:28:19 down at the bottom, we should see an error saying column,

1:28:22 customer id, field this is ambiguous.

1:28:24 Now unfortunately I cannot show you this error,

1:28:26 because the size of my recording window is smaller then MySQL workbench.

1:28:31 But that aside, let me explain why we're getting this error.

1:28:34 Because we have this customer ID column

1:28:36 in both the orders and the customers table,

1:28:40 so MySQL is not sure which table we want to select this column from.

1:28:44 That is why it's saying this column is ambiguous so we

1:28:48 need to qualify this column by prefixing it with a table name.

1:28:52 We can either pick it from order table or the customers table,

1:28:57 it doesn't really matter, because the values are equal, right?

1:29:01 So, in situations where you have the same column in multiple tables,

1:29:06 you need to qualify them, by prefixing them with the name of the table,

1:29:11 okay, now, we say tot he query one more time,

1:29:14 there you go, you have order ID, customer ID and the full name.

1:29:18 Now one more thing before we finish this tutorial,

1:29:21 if you pay close attention we have repeated the word orders in multipel places,

1:29:25 you have it here, as well as in the join condition,

1:29:29 the same is true about he customers table, you have repeated that here.

1:29:33 We can get rid of this repetition and make our code simpler by using an alias.

1:29:39 So write after each table you can do it in alias,

1:29:44 as a short for orders, so by convention we abbreviate the table's name.

1:29:48 Now, wherever we have orders you should replace that with o.

1:29:52 So here in the join condition, we're going to replace orders with o,

1:29:56 and also one more time, in the select clause.

1:30:00 There you go.

1:30:01 You can also apply an alias for a customer's table,

1:30:05 call it c, and then simplify our join condition like this.

1:30:09 So this is how we can join columns for multiple tables.

1:30:16 Now for your exercise, I want you to look at the order items table.

1:30:24 So, in this table we have these columns,

1:30:26 order ID, column ID, product id, quantity, and unit price.

1:30:29 Now I want you to write a query and join this table with the products

1:30:34 table so for each order return both the product id as well as this name,

1:30:39 followed by the quantity, and the unit price form the order items table.

1:30:42 And by the way make sure to use an alias to simplify your code, Alright,

1:30:52 first let's select everything from the order items table,

1:30:57 and then join it with the products table.

1:31:00 How are we going to join this table.

1:31:03 On order_items.

1:31:05 Well actually let's just keep this on Alias right away,

1:31:10 so we use oi, as an abbreviation for order items.

1:31:14 And p as a short for products.

1:31:18 So oi.product id, should be equal to p or products.

1:31:24 product id.

1:31:25 And by the way, remember that alias for table,

1:31:29 you have to use that alias everywhere.

1:31:31 So here I cannot type out product, MySQL is going to yell at me.

1:31:35 So let's use the abbreviation.

1:31:37 Alright, this is how we join these tables,

1:31:40 let's execute this query up to this point.

1:31:43 Alright, so we see all the items form the order items table,

1:31:47 followed by from the products table.

1:31:50 Now, you want to explicit select a few column here.

1:31:55 So, to order items table you want to select order_id.

1:32:00 We take it then we don't have to prefix it

1:32:02 with a table name because this calumnies not repeated in multiple places,

1:32:06 so it's not ambiguous.

1:32:08 So, let's make the code shorter, that's better,

1:32:11 now we want to select the product ID column,

1:32:14 but because this column exists in both tables,

1:32:16 you have to both prefix it with a column name.

1:32:20 Either oi or p it doesn't really matter.

1:32:23 So, next, we want to select quantity, and finally you want price.

1:32:28 Now actually here, you have this unit price column.

1:32:32 In both tables, so this is the unit price in order

1:32:35 items table and this is the unit price in the products table,

1:32:40 now you might be curious why we have this column in 2 places,

1:32:44 is that the price of product can change, so for each order item,

1:32:48 you want to have the price at the time the user placed the order.

1:32:53 So this is a snapshot of the price at a given point in time.

1:32:57 The unit price that we have for the products

1:32:59 table is the current price right now.

1:33:02 This is very important for the recording,

1:33:04 otherwise we cannot calculate the sales properly.

1:33:07 So, because we have the unit price column in two places,

1:33:10 in this case we should pick it from the quarter items

1:33:13 table because this is the price at the time of now, let's execute query.

1:33:18 So here's the final result.

1:33:24 In the real world, when you work as a developer or a data

1:33:30 base administrator quite often you will have to work with multiple databases.

1:33:33 In this tutorial I'm going to show you

1:33:35 how to combine columns from tables in multiple places.

1:33:40 That's pretty easy.

1:33:40 So, in the SQL store database,

1:33:42 you have this tables that you're fairly familiar with.

1:33:46 Now imagine this products table was not here.

1:33:49 Now, if you look at the database, you can see here another products table,

1:33:55 this products table is exactly the same as the products

1:33:58 table that we have in a SQL store database,

1:34:01 so it has the same columns and the same data.

1:34:04 Now technically this is not a good design,

1:34:06 you don't want to have the same table repeated in multiple places.

1:34:09 But for this demo, let's just imagine

1:34:12 that we don't have the products table here,

1:34:14 so we want to join the order items table,

1:34:17 with the products table in the SQL inventory database.

1:34:21 Let's get started.

1:34:22 So select everything from the order items table,

1:34:28 let's give it an alias straight away,

1:34:30 you want to join this with the products table.

1:34:33 This products table is part of the products inventory database.

1:34:36 So we'll have to prefix this with the name of it's database.

1:34:40 So, we type out SQL inventory.

1:34:44 Now once again we can give this an alias like p,

1:34:48 then type out our joint condition,

1:34:51 so oi.product id should be the same as p.product id.

1:34:56 Let's run the query there you go,

1:34:58 so we successfully joined tables across multiple databases.

1:35:02 Now note that we're prefixing the products table with the name of the database

1:35:07 because the current database that we're writing

1:35:09 this query with is the SQL stored database.

1:35:12 Take a look, in the navigator panel,

1:35:14 in the SQL store database is displayed in full.

1:35:18 Because earlier, the root we use statement to select a database,

1:35:24 that will SQL store.

1:35:26 Now what if we select the SQL inventory database.

1:35:29 So, let's see what happens SQL inventory,

1:35:32 now because we have multiple statements we

1:35:35 have to terminate this with a semi colon.

1:35:37 Now we want to select everything with a order items table.

1:35:41 But we don't have this table inside of this database.

1:35:44 So now we'll have to prefix this table with the name of it's database,

1:35:50 that is SQL underline story.

1:35:52 Let's execute the query, okay, everything works, beautifully.

1:35:57 So here's the lesson.

1:35:58 You only have to prefix the tables that are not part of the current database.

1:36:03 In other words the query will be different depending on the database.

1:36:12 In SQL we can also join a table with itself.

1:36:16 Let me show you an example.

1:36:18 Take a look at this database.

1:36:20 SQL hr, in this database we have these two tables, employees and offices.

1:36:26 Let's take a look at the data int he employees table.

1:36:32 There you go.

1:36:33 So here we have these columns, employee id,

1:36:37 first name, last name, salary and reports 2.

1:36:41 This is the id of the manager for this person or this employee.

1:36:46 Now once again, you don't want to repeat the managers information here,

1:36:50 like the phone number,

1:36:51 the address because this information can change in the future,

1:36:54 so we are only using your identifier or their id to refer to them in this table,

1:37:00 now where can we find information about this This manager

1:37:04 is actually an employee of the same organization,

1:37:07 so, look at this example, the manager ID is 37 370.

1:37:12 Now if you look on the website,

1:37:14 here is the ID of that manager which is another employee.

1:37:18 That was the manager for this employee.

1:37:20 We don't have any values here so the value for the sale is null.

1:37:26 So this employee doesn't have a manager and that means they are the CEO.

1:37:30 So let's go ahead and write a query to join this table

1:37:33 with itself so we can select the name of each employee and their manager.

1:37:38 Back to our query window, first we need to select the SQL hr database.

1:37:44 Next, we select everything from the employees table,

1:37:49 we give an alias like b now we need to join this table with itself.

1:37:54 So once again we type out the employees, but we need a different alias.

1:37:59 What should we call this alias.

1:38:00 Well you want to join this table with itself so we can find the managers right?

1:38:05 So we can use m as a shortcut for managers.

1:38:08 Now, let's type our our joint condition.

1:38:12 So from the employees table, we need to join the reports_2 column.

1:38:18 To the manager table which is basically an employees table itself,

1:38:23 and that is employee_ id.

1:38:28 Now let's execute this query and see what we get.

1:38:32 So, we see all the columns from the employees

1:38:35 table repeated the first set of columns,

1:38:38 represent the information about the employees,

1:38:40 and after that, we have the information about the managers

1:38:44 in this case we have only one manager in this table.

1:38:48 But with this technique we can easily create an organization trial.

1:38:51 We can have a hierarchy of managers.

1:38:54 Now let's only the name of the employee and the manager.

1:38:59 So, right here, since every column in the employees table is

1:39:04 repeated twice we need to prefix each column with a table name.

1:39:08 For example, from the employee's table you want to get employee ID,

1:39:13 as well as the first name,

1:39:16 and then for the managers table, we want to select the first name as well.

1:39:22 So every column should be prefixed with a table name or more

1:39:26 accurately the alias because all these columns exist in two tables, right?

1:39:32 Let's go ahead and execute this query, so,

1:39:36 this is what we get, employee id, and here's the managers first name.

1:39:40 We can improve this query by giving an alias to this column.

1:39:44 Because it doesn't make sense to have two first name columns.

1:39:48 So, let's give an alias to the third column, manager.

1:39:52 Now, let's execute it one more time.

1:39:56 And, here's the end result.

1:39:58 So, we have the empty ID first name, and object.

1:40:03 So, joining a table with itself is pretty much

1:40:06 the same as joining a table with another table.

1:40:08 The only difference is that we have to use different alias's.

1:40:12 And we have to prefix each column with an alias.

1:40:16 This is what we call a self join.

1:40:18 Next, I'm going to show you how to join more then two tables.

1:40:24 In this tutorial, I'm going to show you how

1:40:29 to join more then two tables in writing a query.

1:40:32 For example, back to our SQL database, look at the orders table,

1:40:36 now you know how to write a query to join this table

1:40:40 with the customers table to return information

1:40:42 about the customer to place each order.

1:40:45 But here we also have another column, status,

1:40:48 which is similar to the customer ID status.

1:40:50 So the name of the status's are not stored in this table,

1:40:54 they are somewhere else in the order status's table.

1:40:57 Let's have a quick look here.

1:41:00 Our orders can be either processed, shipped or delivered.

1:41:03 And these are the identifiers for each of these status's.

1:41:06 Now back to our orders table.

1:41:09 In the status column you store status id.

1:41:12 So now we should write a query to join the orders table, be two tables.

1:41:17 The customers table and orders status's table.

1:41:20 The result of this query is going to look like this.

1:41:24 So for each order we see the order id, the date,

1:41:28 the first and last name of the customer, and finally the status of the order.

1:41:32 This is a beautiful report we can create for our users.

1:41:35 So let me show you how to write this query.

1:41:37 Back to our query editor, first we need to select the SQL store database,

1:41:41 now, we need to select everything from the orders table,

1:41:45 that's going to be the alias,

1:41:48 next we need to join this with the customers table.

1:41:52 On, that customer id should be equal to see the customer id.

1:41:59 Nothing new so far.

1:42:01 Exactly like before.

1:42:02 Now here we can write another joint keyword.

1:42:05 To join the others keyword order status's table.

1:42:09 So we type out order status's.

1:42:12 And also give it an alias, OS, what is our join condition?

1:42:18 Well, back in the orders table, here we have the status column.

1:42:23 So the value in this column should be equal to the order status ID,

1:42:28 column in order status's table.

1:42:30 Right?

1:42:31 So back to the query,

1:42:34 so orders table.status should be equal to order status's.order status ID.

1:42:40 Make sure to get the name right,

1:42:42 otherwise you're going to get an error So this is how we can join three tables.

1:42:48 Now in the real world as you work on more complex problems,

1:42:51 you'll end up joining even ten tables.

1:42:53 So this is not uncommon in the SQL world.

1:42:56 Now let's go ahead and execute this query.

1:42:58 First we get the column from the orders table,

1:43:01 followed by the orders from the customers table, and so on.

1:43:04 This result is so complex and hard to extract information.

1:43:08 from.

1:43:08 So, let's explicitly select a few columns here.

1:43:13 From the orders table, you want to select the order iD column,

1:43:17 as well as the order date.

1:43:20 Then, from the customers table, we want to select the first name, and last name.

1:43:28 And finally form the order status's, we select the name column.

1:43:32 So we can give this an alias like status that's better.

1:43:36 Let's execute the query one more time.

1:43:40 So, here's the end result, we have order ID, order date,

1:43:44 the name of the customer, followed by the status of the order.

1:43:52 Alright, for exercise, take a look at the SQL invoicing database.

1:43:57 Here we have this table, payments,

1:44:00 and these are the payments that each client has made

1:44:03 towards either invoice Let's take a look at the data,

1:44:06 so we have these columns, like client id, that identifies the client,

1:44:10 so we can join this table with the client's

1:44:12 table to see the name of the clients.

1:44:15 Next we have invoice ID, we also have date, payment method.

1:44:20 So similarly we can join this table with the payment method table here,

1:44:24 let's have a look at the data in this table,

1:44:27 these are the payment methods, credit card, cash, PayPal, wire transfer.

1:44:31 So, back to the payments table, I want you to write a query and join this table

1:44:36 with the payment methods table as well as the client's table.

1:44:40 Produce a report that shows the payments, with more details,

1:44:42 such as the name of the client, and the payment method.

1:44:48 Alright, first we need to use, the SQL invoicing database,

1:44:54 now we can select everything from the payments table which we call e,

1:45:00 next we need to join this with the client's table which we call c,

1:45:05 on p.clientid should eb equal to c.client id.

1:45:10 Let me double check the column name to make sure I got it right,

1:45:14 so back to the payments table the column is called client item.

1:45:18 You also have a column called payment method, that we should join to the payment

1:45:24 method ID column of the payment methods table.

1:45:28 So, back to the query, once again we use a joint statement here,

1:45:32 join with payment methods, we give it an alias pm, on p.

1:45:40 payment _method should be equal to pm.

1:45:44 payment method id.

1:45:45 Make sure to type it out correctly otherwise you're going to get an error.

1:45:49 So let's go ahead and execute the query up to this point,

1:45:54 finally let's hand pick the columns that make the most sense.

1:45:59 So, from the payments table, let's select the date,

1:46:04 followed by invoice id, what else do we have here.

1:46:10 So we have client id, invoice id, date, and now the payment method,

1:46:14 I'm going to pick the amount column from here as well,

1:46:17 so back to the query, p .amount.

1:46:20 Now we need to add information about the client.

1:46:24 Let's take a look at this table, clients,

1:46:28 so here we have columns like name, address, city and so on.

1:46:32 All we need here is the name column.

1:46:34 So back to the query, of the client's table let's select the name

1:46:39 column and finally form the payment method table,

1:46:41 let's select, what is that column called it's called name.

1:46:45 So back to the query pm.name.

1:46:48 So here's the end result.

1:46:52 Now we can put this column in any order that we want,

1:46:55 it doesn't really matter, let's execute the query,

1:46:57 and make sure everything works.

1:47:00 So, on this date, on this invoice,

1:47:04 we have a payment for this amount by this client using a credit card.

1:47:12 Ina let he examples you have seen so far,

1:47:16 we use a single column to uniquely identify the rows in a given table.

1:47:21 For example, for example, in the customers table,

1:47:24 we have this customer ID column which

1:47:27 uniquely identifies the rows in this table.

1:47:29 But there are times where we cannot use a single

1:47:32 column to uniquely identify columns in a given table.

1:47:35 For example, look at the order items table.

1:47:39 In this table, we have columns like order id, product id, and so on.

1:47:42 Now if you look at the data,

1:47:44 you can see that the values in the order id column are repeated.

1:47:48 They are duplicated.

1:47:49 You have 2226 and so on.

1:47:52 So we cannot use this column on it's own to uniquely identify each record.

1:47:56 The same is true for the product id.

1:47:58 The values for this column are also duplicated.

1:48:01 So in this table we use the combination of the values

1:48:04 in both these columns to uniquely identify each oder item.

1:48:09 As an example, in this order we have 3 items, for products, 1,

1:48:14 4, and 6, and for each product, we have a quantity and unit price.

1:48:20 So if we use the combination of the values in both these values,

1:48:24 we can uniquely identify each order item, in other words,

1:48:27 we don't have two records for order ID 2, and product ID 1,

1:48:32 we only have a single record for that item,

1:48:36 now let's open this table in the design mode.

1:48:38 So over here we find this middle icon that looks like a tool.

1:48:42 Note that this yellow key that represents

1:48:45 the primary key exists on both these columns.

1:48:48 This is what we call a composite primary key.

1:48:50 The composite primary key contains more then one column.

1:48:54 Now why does this matter?

1:48:56 Well, when you have a table with a composite primary key.

1:49:00 You need to learn how to join that table with other tables.

1:49:03 For example, here we have this table, order item notes,

1:49:06 that we use to keep notes in each order item.

1:49:09 Let's look at the data here.

1:49:12 So we have this column note id, right?

1:49:14 Which uniquely identifies the records in this table,

1:49:17 next to that we have order ID and product ID.

1:49:20 you learn that the combination of these two

1:49:22 columns uniquely represents and order ID.

1:49:25 So here for order number 2, for product number 1, we have two notes.

1:49:30 Now let me show you how join this table with the order items table.

1:49:34 So, back to our query,

1:49:36 you can see that I have already selected the SQL store database,

1:49:40 so I'm not going to type out a U statement.

1:49:43 Alright, let's select like everything from the order items table.

1:49:48 Give it an alias, now we need to join this with order item notes,

1:49:55 also we give it an alias.

1:49:57 How are we going to join these tables?

1:50:00 Based on two columns, back to the order items table these are the columns

1:50:05 that we need to use in our joint condition.

1:50:08 So, in the order items table we have this order ID column,

1:50:13 these should be equal to the same column in order item notes table.

1:50:19 So, in.order id.

1:50:21 But this is not enough,

1:50:24 we should also join these tables based on the product ID number.

1:50:28 So, we type out and, and then type out the second condition, so order items.

1:50:36 product ID should be equal to order item notes.product id.

1:50:40 This is what we call a compound joint condition.

1:50:44 So we have multiple conditions to join these two tables.

1:50:52 In this tutorial I'm going to talk about the implicit joint syntax in MySQL.

1:51:00 So here we have a basic inner join,

1:51:02 we're selecting everything from the orders table,

1:51:04 joining it with customers table on, orders.customer id.

1:51:08 Equal to customers.customerid.

1:51:10 Pretty basic.

1:51:12 There is another way to write this query using implicit join syntax.

1:51:16 Let me show you how this works.

1:51:19 So, we select everything from now here we can type out multiple table names.

1:51:28 so, orders, customers.

1:51:32 And we can also give them an alias, so c and o.

1:51:36 And then we move this join condition, to the where clause,

1:51:41 so I'm going to copy this from here,

1:51:44 type out the where clause, and paste the condition.

1:51:47 These two queries, are equivalent.

1:51:48 What we have here is called implicit joint syntax.

1:51:52 Now even though MySQL supports the syntax it's

1:51:56 something that I suggest you not to use,

1:51:58 because if you accidentally forget to type out the where clause,

1:52:01 you will get a cross join.

1:52:03 Let me show you what I mean.

1:52:05 So first I'm going to delete the first query.

1:52:08 And execute this so we get ten records

1:52:14 because we have 10 orders in this database.

1:52:17 So far so good.

1:52:19 What happens if you accidentally type out the where clause.

1:52:25 Instead of 10 records we're going to get probably 100 records.

1:52:30 Because every record in the order table,

1:52:33 is now joined with every record in the customers table.

1:52:36 This is what we call a cross turn.

1:52:38 Now later in this section I'm going to talk about cross joins in more detail,

1:52:42 but what I want to talk about in this tutorial,

1:52:45 is that it's better to use an explicit joint syntax,

1:52:49 so, we use, join because this syntax forces you to type out the joint condition,

1:52:55 if you simply join orders with customers,

1:52:59 without typing the join condition, you're going to get a syntax error.

1:53:03 So to recap, be aware of the implicit join syntax,

1:53:06 but write all of your joints using the explicit syntax.

1:53:16 Earlier in this section I told you that in SQL we have two types of forms.

1:53:20 Inner joins and outer joins.

1:53:22 And so far you have only seen examples of inner joins.

1:53:25 And I also told you that this inner keyword is optional,

1:53:28 so whenever you type out a join, you're using an inner join.

1:53:32 In this tutorial, we're going to look

1:53:34 at outer joins and the problems they solve.

1:53:36 So, let's start by writing a query that uses an inner join,

1:53:40 and then we'll convert hat inner join and outer join.

1:53:44 So, select everything from the customers table join it in the orders table,

1:53:52 on c.customer id should be equal to o.customer ID.

1:53:58 Pretty basic right?

1:54:00 Now for clarity, let's pick a few columns from these two

1:54:04 tables .So for the customers table I want to pick customer id.

1:54:09 And first name, and from the orders table, I want to pick order id.

1:54:16 Now finally, let's sort the results so we can clearly see what we get.

1:54:21 So order by c.customer id.

1:54:24 Let's execute that query and see what we get.

1:54:28 So, here' the result.

1:54:29 For customer number 2 called inis or inis whatever,

1:54:33 you have 2 orders, order 4, and order 7.

1:54:37 Similarly for customer number 5, we have 2 orders and so on.

1:54:41 Now there is something missing in this result.

1:54:44 We only see customers who have an order in our system,

1:54:47 these are customers 2, 5, 6, 7, 8, and 10.

1:54:51 But if you look at the customers table,

1:54:54 you can see that you have other customers like customer number 10,

1:54:58 customers number 3, and so on.

1:55:00 Currently we don't have any orders for these customers,

1:55:02 and that's the reason we don't see them in this results set.

1:55:05 But what if you want to see all the customers whether they have an order or not?

1:55:10 That's when we use an outer join.

1:55:12 Let me show you how that works.

1:55:14 So back to our query the reason we only saw

1:55:17 customers who have an order was because of this join condition,

1:55:21 When joining these two tables,

1:55:23 you are only returning records that match his condition.

1:55:26 So for a given customer, if we do have an order, that record is returned.

1:55:32 But as you saw a second ago, some customers don't have an order.

1:55:36 So for those customers this condition is not valid.

1:55:40 And that is the reason they are not returned in the result set.

1:55:44 To solve this problem we use an outer join.

1:55:46 Now in SQL we have two types of outer joins.

1:55:50 We have left joins and right joins.

1:55:52 When we use a left join, all the records from the left table,

1:55:58 in this case customers are returned whether this condition is true or not.

1:56:03 So we get all the customers, and if they do have an order,

1:56:06 we'll see the order id as well.

1:56:08 Let's execute this query and see what we get.

1:56:11 So, there you go.

1:56:14 Customer number 1, doesn't have an order

1:56:17 and that's why we get null in this cell.

1:56:20 Customer number 2 has two orders, 4, and 7,

1:56:23 customer number 3 also doesn't have an order so we get null for order id.

1:56:28 This is the result of a left join.

1:56:30 So back to our query when we use a left join,

1:56:34 all the records from the left table are

1:56:36 returned whether this condition is true or not.

1:56:40 Now what if we use a right join.

1:56:43 In this case, all the records from the orders

1:56:46 table are returned whether this condition is true or not.

1:56:49 Let's execute this query and see what we get.

1:56:52 So, we get he same result as before.

1:56:56 when we use an inner join, because we are selecting all the records

1:56:59 from the right table which is the orders table,

1:57:01 so we don't see all the customers we see all the orders.

1:57:05 Now if you want to use a right join,

1:57:07 and still see all the customers, you need to swap the order of these tables.

1:57:11 So, we put the orders table first that's going to be our left table.

1:57:16 And then, we put the customers on the right side,

1:57:20 so now with this query we'll return all the records

1:57:22 from the right table which is the customers table.

1:57:26 We execute this we get all the customers whether they have an order or not.

1:57:32 Beautiful.

1:57:32 Now one last thing before we finish this tutorial,

1:57:36 I've seen developers use the outer keyword here.

1:57:39 So either right outer join or left outer join.

1:57:43 But technically the outer keyword is optional just like the inner keyword,

1:57:46 so you don't have to type it out.

1:57:48 So I'm going to remove this to make this code shorter and easier to understand.

1:57:52 So to recap, if you have the join keyword directly,

1:57:55 and if you do a left or right join you're doing a inner outer join.

1:58:04 Here's your exercise for this tutorial.

1:58:06 I want you to write a query that produces this result.

1:58:10 So we should have three columns here.

1:58:11 Product ID, name, and quantity that I picked from the order items table,

1:58:17 so here we need to join the products table.

1:58:20 With order items table.

1:58:21 So we can see how many times each product is ordered.

1:58:24 However, in an inner join we will only see the products that have an order,

1:58:29 but here I'm doing an outer join, so, product number 7,

1:58:32 has never been ordered, but still exists in the result.

1:58:36 Is it null, or the quantity?

1:58:37 So go ahead and write an outer join, or use this result.

1:58:44 Alright, first we select everything from the products table

1:58:50 and then to a left join with the order items table.

1:58:56 Our join condition is p.product id equals oi.product id.

1:59:04 So because we'll get all the products in the products

1:59:08 table whether this condition is true or not.

1:59:10 If you have never been ordered, you still see them in the result.

1:59:15 Now, let's pick a few columns for clarity.

1:59:19 So p.productid p.name and oi.

1:59:24 quantity.

1:59:24 That's it.

1:59:25 Let's execute the query.

1:59:28 We get the same result as before.

1:59:30 So all the products are here.

1:59:32 And product number 7 has never been ordered so we see null for the quantity.

1:59:40 Similar to inner joins, we can use outer joins between multiple tables.

1:59:47 Let me show you.

1:59:48 So here's the query that we wrote in the last tutorial,

1:59:51 you are doing a left join between customers and orders tables.

1:59:56 So when we execute this query,

1:59:58 you get all the customer whether they have an order or not.

2:00:02 If you have an order you see the order id.

2:00:05 Beautiful.

2:00:05 Now if you look at the orders table,

2:00:08 you can see that some of our orders have a shipper id.

2:00:12 These are the orders that have been shipped.

2:00:14 So now let's join orders table with the shippers table

2:00:17 to display the name of the shipper in the result.

2:00:20 So back to our query, after this left join,

2:00:24 let's write another join, here I'm doing an inner join.

2:00:28 So let's inner join the orders table with the shippers table.

2:00:32 Join shippers, we call it sh, on what is the join condition.

2:00:38 Well, oh.shipper id, should be equal to sh.shipper id.

2:00:44 Alright?

2:00:45 So in this query we have a left outer join and inner join.

2:00:51 Let's see what we get, alright, we only see 5 records.

2:00:56 But you have more orders.

2:00:58 So here you have the same problem we have before.

2:01:00 Some of our orders don't have a shipper,

2:01:02 and that is why they are not returned here.

2:01:05 In other words, this join condition is not true, for some of our orders.

2:01:10 So back to the orders table, a an example,

2:01:13 this first order doesn't have a shipper shipper id is null,

2:01:17 and that is why it is not returned with a query result.

2:01:20 So to solve this problem, you should use a left join,

2:01:22 you want to make sure that all orders

2:01:25 are returned whether they have a shipper or not.

2:01:28 So back to the query, you need to replace this inner join with the left join,

2:01:33 so let's execute the query and see what happens.

2:01:36 Now we should have quite a few more orders, there you go,

2:01:40 now to make this example more interesting,

2:01:42 I'm going to add the ship -per name here.

2:01:45 So back to our select boss, let's add a new column, shipper.name.

2:01:52 Now we can give it an alias like

2:01:55 shipper let's execute the query and here's the result.

2:02:00 So we get all the customers whether they have an order or not.

2:02:04 And for those who do have an order,

2:02:06 we get all the orders whether we have a shipper or not.

2:02:10 This is the power of outer joins.

2:02:12 Now in the last tutorial, you learned that you can get the same result,

2:02:16 in both the left join or the right join,

2:02:18 you just have to swap the order of the tables.

2:02:21 However, as a best practice of what to avoid using right joins,

2:02:25 because when you're joining multiple tables and you

2:02:27 have left and right and inner join, things get really complex.

2:02:31 So, someone else reading your code,

2:02:33 will have difficulty visualizing how you're joining these tables.

2:02:36 As an example, if you have a right join here and then a left join after,

2:02:40 it will be harder to visualize how these tables are getting joined.

2:02:44 So, as a best practice, avoid right joins, and use left joins instead.

2:02:52 And, here is your exercise for this tutorial.

2:02:56 I want you to write a query that produces this result.

2:02:58 So here you have these columns order date, order id,

2:03:01 the first name of the customer the shipper and we

2:03:05 can see that some of our orders are not shipped yet.

2:03:08 So here we have null, and here finally, we have the status.

2:03:12 So go ahead, spend 2-3 minutes on this exercise,

2:03:14 when you're done continue watching.

2:03:20 Alright, let's select everything from the orders table,

2:03:25 now you should join this with customers, on, hold.customerid.

2:03:32 Should be equal to c.customer id.

2:03:34 Here I'm using an inner join, because every order does have a customer.

2:03:39 So, this condition is always valid,

2:03:41 it doesn't matter if we use a left join or a inner join here, now, okay?

2:03:45 Now before going any further let's take our columns.

2:03:48 So from the orders table, I'm going to pick order id.

2:03:52 Followed by order date.

2:03:54 And then, customer.firstname.

2:04:00 Which we can optionally rename to customer.

2:04:04 Alright, next we need to select the shipper.

2:04:07 So, we join the result with the shippers table,

2:04:12 on order.shipperid equal to shipper.shipperid.

2:04:16 However, if we use an inner join here,

2:04:20 because some of our orders don't have a shipper,

2:04:23 we are only going to see the orders that have been shipped.

2:04:26 Let me show you.

2:04:27 So, for clarity, I'm going to add the shippers name here,

2:04:31 so shipper .name as shipper.

2:04:33 Let's execute the query there you go,

2:04:37 we only see the orders that have been shipped.

2:04:40 But we want to see all the orders, right?

2:04:42 So, we need to change the second joint to a left join.

2:04:48 So all orders are returned whether they have a shipper or not.

2:04:52 Let's execute the query one more time, there you go.

2:04:57 Now we see all the orders from number 1-10.

2:05:00 Beautiful.

2:05:00 Finally, we need to add the status comment here.

2:05:04 So, we need to do another join here, join with order status's,

2:05:10 which we aggregate as os, on oh.status equals to os.orderstatusid.

2:05:20 We can see that I have designed our database

2:05:22 such that sometimes our column names are exactly identical,

2:05:25 but in other cases they don't match.

2:05:28 So in order table we call this column, status, as opposed to order status id,

2:05:33 and this is deliberate because a lot of real databases are like that.

2:05:38 Alright, now let's add the status name here, so order, status.name as status.

2:05:45 Execute the query, and we can see all the orders here,

2:05:52 for each order we have the data, the customer, the supper and the status.

2:06:00 Earlier, we talked about self joins in SQL.

2:06:04 So here in the SQLhr database, we have this employees table,

2:06:09 we rote a query to get all the employees and their manager.

2:06:13 So here we have this column,

2:06:15 reports to that specifies the manager for each employee.

2:06:18 So let's go back and rewrite this query

2:06:21 to get all the employees and their manager.

2:06:24 Back to our query editor window, first let's use a SQL hr database.

2:06:30 Then select everything from the employees table,

2:06:36 we give it an alias and then join it with itself.

2:06:40 So this is what we call a self join.

2:06:42 Now we're going to use a different alias like m for managers.

2:06:46 Now, what is the joint condition e.reports

2:06:50 to, should be equal to m.employee ID, right?

2:06:56 Now for clarity, let's pick only three columns,

2:06:58 so either the id employee.first name.

2:07:04 And m.firstname which we rename to manager.

2:07:08 Alright, let's execute this query and see what we get.

2:07:12 So, here's the result as you can see all these employees have the same manager.

2:07:20 However, there is something missing here.

2:07:21 We don't have a record for this person, this manager himself.

2:07:24 So what is missing is a record where we can see the employee id for this person,

2:07:30 their name and their manager which should be null because

2:07:33 this person is the CEO or the head of the company.

2:07:37 But why is this happening?

2:07:39 The reason is, our inner join,

2:07:41 because this condition we have here will only return people who have manager.

2:07:45 We can solve this problem by using a left join.

2:07:48 So, we do a left join, because we want to get every employee

2:07:52 in this table whether they have a manager or not.

2:07:56 Okay, now let's execute the query one more time.

2:08:00 There you go.

2:08:01 Now we have a record for this person the manager,

2:08:04 as you can see this person does not have a manger, that's why we have null here.

2:08:12 Back to our SQL store database here we

2:08:16 have a simple query that joins the orders table, with a customers table.

2:08:21 And here's our join condition.

2:08:22 We have several examples of this before.

2:08:25 Now as our queries get more complex these joint conditions get in the way,

2:08:29 they make our queries hard to read, but the good news is that in MySQL,

2:08:33 we have a powerful feature for simplifying these queries.

2:08:36 If the column name is exactly the same across these two tables,

2:08:41 we can replace the on clause.

2:08:44 With a using clause which is simpler and shorter.

2:08:48 So I'm going to comment out this line and instead type out using in parenthesis,

2:08:52 we type out the column name, that is custom.

2:08:56 customer id.

2:08:56 What we have in line 7 is exactly identical to what we have on line 6.

2:09:01 That is shorter and easier to read.

2:09:03 So, let me delete this line.

2:09:06 We can add another join statement here to join the orders for the shippers here,

2:09:11 so join with shippers using shipper id.

2:09:16 In both these tables we have a column with the exact same name.

2:09:21 Alright, now let's execute this query this is what we get,

2:09:25 you have the order id followed by the first name of the customer.

2:09:29 Let's add a new column here, so,

2:09:32 I'm going to add sh .name that is the name of the shipper,

2:09:40 now obviously because some of our orders are not shipped,

2:09:42 we have to replace this inner join to the left join.

2:09:46 So we can use the using keyword with both inner and outer joins.

2:09:51 Let's execute the query one more time.

2:09:54 There you go.

2:09:56 Now, we have the name of the shipper next to each order, beautiful, however,

2:10:00 we cannot use this technique to join the result with the order status's table.

2:10:05 Because in the orders table we have this column called status,

2:10:09 but in order status's table, this column has a different name.

2:10:12 It's order status id.

2:10:14 Let me show you.

2:10:15 So, order status's.

2:10:16 columns, there you go, order status id.

2:10:20 So the using keyword only works if the column

2:10:23 name is exactly the same across different tables.

2:10:26 Now what if we have multiple columns in our join condition.

2:10:29 For example, earlier we talked about tis order items table,

2:10:33 I told you that in this table we have a composite primary key,

2:10:37 which basically means a primary key that consists of multiple columns.

2:10:41 So the combination of these two columns

2:10:43 uniquely identifies each record in this table.

2:10:46 Now if I join this table, with order item notes table.

2:10:50 In our join join condition,

2:10:52 we should compare both these columns with their corresponding

2:10:56 columns in the order item notes table.

2:10:58 So let's quickly write that query and then simplify it with the using keyword.

2:11:04 So, select everything from order items, now, join it with order item notes on so

2:11:16 here we need to compare oi.order id with yn.orderid,

2:11:23 and oi.productid= to oin.productid.

2:11:30 This join condition is kind of messy, it's hard to read this query.

2:11:35 Now let's simplify this query with the using keyword.

2:11:38 So we type out using.

2:11:39 In parenthesis we add both columns and then separate them using a comma.

2:11:44 So, ordered and product id.

2:11:49 Isn't that better?

2:11:52 Now, for our exercise,

2:11:54 back to our SQL invoicing database write a query to select

2:11:59 the payment from the payments table and produce something like this.

2:12:02 So in this table we have the date the client,

2:12:04 the amount and the payment method, we can see.

2:12:08 On what date who has paid how much using what payment method.

2:12:16 Alright, I'm going to use the SQL invoicing database,

2:12:21 and then select everything from the payments table,

2:12:25 join it with the clients table.

2:12:28 Using client id because in both these tables, you have the client id table.

2:12:34 Next we need to join this with payment methods,

2:12:38 however, they column name between these two tables is different,

2:12:42 so in the payment table we have a column called payment method.

2:12:46 But in payment methods table, our column is called payment method ID.

2:12:51 So here we cannot use the using keyword, and we'll have to use the on clause.

2:12:58 So on p.payment underline method equals pm.

2:13:04 paymentmethodid.

2:13:04 Now let's pick our columns.

2:13:08 So, payment.date client.name.

2:13:12 And we rename this as client,

2:13:14 next we pick m out, and finally, the payment method, so,

2:13:20 let's rename that to payment_method and execute the query,

2:13:25 there we go, this is what we get.

2:13:28 The date, the client, the amount, and the payment method.

2:13:36 In MySQL, we also have another simpler way,

2:13:40 to join two tales, it's called a natural join,

2:13:43 and it's easier to code, but it's not something to recommend,

2:13:46 because sometimes it produces unexpected results.

2:13:48 But let me quickly show you how it works in case

2:13:51 you see it somewhere so at least you're familiar with it.

2:13:54 So back to the previous example, let's select everything from the orders table,

2:13:59 that we should do a natural join with the customer's table.

2:14:04 Now with this natural join, we don't exactly specify the column name.

2:14:08 So the database engine will look at these two tables

2:14:10 and it will join them based on the common columns.

2:14:14 The columns don't have the same name.

2:14:16 And that is the reason this query is shorted right.

2:14:20 So, for clarity, let's pick a couple of columns here, o.order id and c.

2:14:25 let's say first name.

2:14:28 Let's execute the query, there you go.

2:14:32 so we see all the orders and the customers replace them.

2:14:36 So natural joins are really easy to code but they can be a little bit dangerous,

2:14:41 because we're letting the database engine guess the join,

2:14:44 you don't have control over it.

2:14:46 For this very reason, natural joins can produce unexpected results,

2:14:50 and that's why I discourage you to use them.

2:14:56 In this tutorial, were going to look at cross joins in SQL.

2:15:02 We use cross joins to mine or join every record from the first table,

2:15:07 with every record, in a second table.

2:15:09 Here is an example, let's select everything from the customers table

2:15:15 to now here we do a cross join with the products table.

2:15:20 So every record in the customers table,

2:15:22 will be combined with every record in the products table.

2:15:25 And that is why we don't have a condition here.

2:15:28 Okay?

2:15:29 So this is what we call a cross join

2:15:32 now for clarity let's pick a couple of columns

2:15:35 like see that first name we rename it

2:15:39 as customer and then product.name which we rename to product.

2:15:44 Also, let's sort the result by customer.

2:15:50 first name.

2:15:52 Now, let's execute the query, here's the result of the cross join,

2:15:56 so first we have amber as the customer,

2:15:58 and here are all combinations of amber with different products.

2:16:04 Next we have Barbara or whatever it is,

2:16:05 and again we have the combination of this custom with all the products.

2:16:09 Now in this particular example,

2:16:10 it doesn't really make sense to use a cross join,

2:16:14 a real example for using cross join is

2:16:17 where you have a table of sizes like small,

2:16:20 medium, large, and a table of colors, like red, blue, green whatever.

2:16:24 And then you want to combine all the sizes with all the colors.

2:16:28 That is when you use a cross join.

2:16:32 Wha we have here is called the explicit syntax for cross join,

2:16:35 you also have the implicit syntax which looks like this.

2:16:38 Instead of typing out the cross join,

2:16:41 you type out multiple tables in the from clause.

2:16:45 So customers and orders.

2:16:48 Both these queries produce the same result.

2:16:50 But I personally prefer to use the explicit syntax because it's more clear.

2:17:00 And here's a simple exercise for you.

2:17:02 Do a cross join between shippers and products.

2:17:04 First do it using the implicit syntax, and then using the explicit syntax.

2:17:09 It's pretty straight forward,

2:17:10 I just want you to get your hands dirty in the code and get used to this syntax.

2:17:20 Alright, first I'm going to use the explicit syntax,

2:17:22 and then I'm going to place the syntax.

2:17:25 So let's start by selecting everything from 2 tables, shippers and products,

2:17:32 now for clarity I'm going to pick two columns, shipper.

2:17:36 name which we rename to the shipper.

2:17:38 And product.name which we order everything by shipper Let's execute the query,

2:17:49 this is what we get.

2:17:50 So the combination of all shippers and all products beautiful,

2:17:54 now let's use the explicit syntax, so we select everything from the base table,

2:18:00 in this case shippers and then do a cross join with products.

2:18:04 That produces the same exact result.

2:18:12 We covered everything about joins,

2:18:14 you learned that with joins we can combine columns with multiple tables,

2:18:19 but in SQL we can also combine rows with multiple tables,

2:18:23 and this is extremely powerful.

2:18:24 And let me show you how this works.

2:18:27 First we have a quick look at our orders table,

2:18:30 and select everything from the order table.

2:18:32 Now if you look at the data,

2:18:36 we can see that the first order was placed in the current year,

2:18:40 2019, all the other orders were placed in previous years.

2:18:43 Now let's say you want to create a report,

2:18:45 get all the orders, and next to each order, add a label.

2:18:48 If the order is placed in the current year, the table is going to be active,

2:18:52 and if the order is placed in previous years, you want to label it as archives.

2:18:57 So, let's change our query and change our condition here.

2:19:02 First you want to get all the orders in the current year.

2:19:06 So where order date is greater than or equal to 2019, 0101.

2:19:12 Now I just want to highlight that this is not

2:19:16 the ideal way to get the orders in the current year,

2:19:18 because here we have hard coded 2019.

2:19:20 So if you execute this query next year,

2:19:23 we are not going to get the right result.

2:19:25 But don't worry about this for now.

2:19:26 Later in the course I will show you how to get the orders in the current year,

2:19:30 without hard coding a date here.

2:19:32 So, let's execute this query, now we get only one order.

2:19:37 Let's hand pick a couple of columns here.

2:19:40 So, order id, and order date.

2:19:45 And also I want to add a string, literal here, like active.

2:19:51 Right?

2:19:52 Let's execute this query, this is what we get.

2:19:55 We get 3 columns, order id, order date, and active,

2:19:58 and in this column, argument we have this string value.

2:20:02 Active.

2:20:03 Now let's rename this column to status, alright?

2:20:08 And execute the query, that is better,

2:20:12 now we want to write another query similar

2:20:14 to this that will return the order in the previous year,

2:20:17 but with a different label, archive.

2:20:20 So, to save time, I'm going to copy this few

2:20:24 lines and paste them right after our first select statement.

2:20:28 Now note that here we have a syntax error,

2:20:31 because we didn't terminate the first select statement with a semi colon,

2:20:35 but don't worry about it,

2:20:36 we're going to get back to this in a second so for the second query,

2:20:40 we want to return a different a different label, archive,

2:20:43 and we want to change our condition to less then 2019.

2:20:48 Now, select only these few lines.

2:20:52 And execute this query, either by clicking on this icon here,

2:20:56 or using the keyword shortcut you learned earlier in the course.

2:21:00 There you go, here are all the orders from the previous year,

2:21:06 with the label archive.

2:21:08 This query returns 9 records.

2:21:09 The previous query returned 1 record.

2:21:12 now using the union operator we can combine data from these two queries, so,

2:21:18 in between our select statement we type out

2:21:23 union now let's execute the query one more time,

2:21:26 so here's our first order in the current year that is

2:21:30 active and below that we have the orders in the previous years.

2:21:33 So using the union operator we can combine records for multiple queries.

2:21:37 Now in this example, both our queries are against the same table,

2:21:41 but we can also have queries against different tables

2:21:44 and then combine the result into one result set.

2:21:48 Let me show you another example.

2:21:49 So I'm going to delete everything here.

2:21:52 Let's select the first name, from the customers table,

2:21:57 and then we can union that with select the name from the shippers table.

2:22:04 Let's execute the query's in one result set

2:22:08 we can see all the customers and the shippers.

2:22:10 Now as far as I know, there is no real world care for this particular query.

2:22:15 But what I Want to point out is

2:22:17 that with union we can combine results from multiple query's.

2:22:20 These query's can be against the same table or different tables.

2:22:24 In your database, you can have a table like archive orders,

2:22:28 and another table like order and then you could combine

2:22:33 all the archive and active orders into one result set.

2:22:36 Just remember, that the number of columns

2:22:39 that the query returns should be equal, otherwise you're going to get an error.

2:22:43 For example, let's select the first name and last name

2:22:47 from customers and then union that with the name of shippers.

2:22:52 When we execute this query, we get an error,

2:22:54 because the first part of this query returns two columns,

2:22:57 but the second part returns one column.

2:23:00 So MySQL doesn't know how to combine these records.

2:23:04 And one last thing before we finish this tutorial.

2:23:06 If you look at the result here,

2:23:08 the name of this column is based on this first query,

2:23:12 so the first query returns first name,

2:23:14 and that's why this column is called first name.

2:23:16 If you change the order of these queries, and move this union up here,

2:23:22 now let's run this query as you can see our column is called name.

2:23:28 So whatever we have in this first query

2:23:30 is used to determine the name of columns.

2:23:32 Here we can also rename the column to full name.

2:23:36 There you go.

2:23:44 Here's your exercise for this tutorial.

2:23:46 Write a query for this report.

2:23:48 So here we have four columns, customer ID, points, and title.

2:23:52 Now as you know we don't have this column in the customers table,

2:23:56 so we have calculated the values of this column,

2:23:59 based on the points each customer had.

2:24:01 If they have less then 2,000 points, their type is bronze.

2:24:05 If they have between 2,000 and 3,000 points they are silver customers,

2:24:10 and if they have more then 3,000 points, they are gold customers.

2:24:13 Also note that here we have sort the result by the first name.

2:24:18 So go ahead and spend two minutes to write this query.

2:24:24 Alright, first let's get the bronze customers,

2:24:28 so select everything from customers where points is less then 2,000.

2:24:36 Now here we want to pick 3 columns, customer id, first name, end points.

2:24:42 And finally we add a new column to a string literal,

2:24:47 bronze let's run this query and see what we get.

2:24:50 So these are all the bronze customers,

2:24:52 but the name of this column in bronze, we don't want that.

2:24:56 So, let's rename this to type.

2:24:57 Now this is off the screen so I'm going to break this up

2:25:02 into multiple lines that makers our query bigger and easier to read.

2:25:08 There you go, let's run the query one more time, now the column is called type.

2:25:12 Beautiful.

2:25:12 Now we should do union and repeat this query, but extract the silver customers.

2:25:20 So I'm going to paste this query here and then make a couple of changes here,

2:25:25 I'm going to replace bronze with silver,

2:25:28 and change the condition to between 2,000 and 3,000 let's run our query,

2:25:34 see what we get so, we have all the bronze customers first,

2:25:40 followed by all the silver customers.

2:25:41 So the order of these records is based on our queries.

2:25:44 In our first query we got the bronze customers,

2:25:47 which is why they are listed first.

2:25:49 But this is not what we want.

2:25:51 You want to order the result by the first name of our customers.

2:25:55 So, let's apply an order by at the end so order by first name.

2:26:01 Now there is one more piece remaining.

2:26:04 You should do a union one more time and write a query to get the gold customers.

2:26:09 SO I'm going to select these few lines.

2:26:12 And paste them here.

2:26:16 Now let's change silver to gold, and the condition to(?) greater then 3000.

2:26:24 And finally we do an imported by.

2:26:26 Let's run the query one more time.

2:26:28 And here's the end result,

2:26:30 our customers are sorted by first name, bronze, silver and gold customers.

2:26:40 In this section I'm going to teach you how to insert, update, and delete data.

2:26:46 Before we get started let's have a closer look at our customers table.

2:26:51 So click on this middle icon to open this table in the design mode.

2:26:56 What you see here might look a little bit intimidating at first,

2:26:59 but trust me it's so easy, and in this tutorial,

2:27:01 I'm going to explain exactly what we have in these columns.

2:27:04 So on the left side,

2:27:06 you can see the column next to that you can see the data type for each column,

2:27:10 so our customer id column can only accept integer value.

2:27:14 Integers are whole numbers like 1234 and so on.

2:27:17 You don't have decimal points, etc.

2:27:20 First thing is a var chart which is short for variable character.

2:27:24 And in parenthesis, you can see 50,

2:27:27 that basically means in this column you can have a maximum of characters now if

2:27:33 the name of a customer is only 5

2:27:35 characters long the only store those 5 characters.

2:27:37 So even though the max length for this column is 50,

2:27:41 you're not going to waste the space if

2:27:43 this customer name is less then 50 characters.

2:27:46 That is why here we have 4char which is short for variable.

2:27:50 In contrast we have another data type that is character.

2:27:54 If you had character 50 here,

2:27:56 and the name of the customer was only 5 characters long,

2:27:59 MySQL will insert additional 45 spaces to fill this column.

2:28:04 So this is a waste of space.

2:28:06 So as a best practice,

2:28:08 Most oft he type we use var char to store strings or textural values.

2:28:12 Now here on the right side we have this column pk,

2:28:16 which is short for primary key.

2:28:19 So customer id is marked as the primary key,

2:28:21 and that is why we have this yellow key here,

2:28:24 so the values in this column uniquely identify each customer.

2:28:28 Next to that we have nn which is short for not null

2:28:32 and that determines if this column can except null values or not.

2:28:36 In this case, every customer record, must have the customer id.

2:28:40 First name, last name, as well as these other attributes.

2:28:44 But birthdays and phone are optional.

2:28:46 So in these columns we can have null values,

2:28:49 Now we have another calling here, ai, which is short for auto increment

2:28:54 and this is often used with primary key columns,

2:28:57 so every time we insert a new record in this table,

2:29:01 we let MySQL or our database engine insert a value in this column,

2:29:05 so essentially it gets the customer id for the last row,

2:29:09 and it will increment it by 1, by the time you serve a new record.

2:29:13 So if you look at the data you can

2:29:16 see that currently we only have 10 customers here.

2:29:18 So if you have a new customer here,

2:29:20 MySQL will sign 11 to the new customer, okay?

2:29:24 And finally here we have another column

2:29:27 that specifies the default value for each column.

2:29:30 For example, for birthday and phone columns, the default values or null,

2:29:36 So if you don't supply a value MySQL

2:29:38 will supply the null values for these columns.

2:29:41 Similarly we have another null value, for the points column,

2:29:45 so if we don't supply the points for a customer, MySQL will use 0,

2:29:48 now we have a few other columns here which are not important at this stage,

2:29:52 you will learn about them later in this course.

2:29:55 So now you understand the attribute of each column,

2:29:58 let's go ahead and insert data into this table.

2:30:04 In this tutorial, you're going to learn how to insert a row into a table.

2:30:10 For that we're going to use the insert into statement.

2:30:13 Where are we going to insert this row?

2:30:17 Into the customers table,

2:30:18 so we type out the name of the table here followed by the values clause.

2:30:24 And here in parenthesis we supply the values for every column in this table,

2:30:29 so back to our table definition, these are all the columns,

2:30:33 first we need to supply a value for the customer id column.

2:30:36 However, in this column, the auto increment attribute is enabled,

2:30:40 and as I told you before,

2:30:42 if we don't supply a value, MySQL will generate a unique value for us.

2:30:48 So we can go back to our statement and either assign an explicit

2:30:52 value or use default to let MySQL take care of generating this value.

2:30:57 This is the preferred approach.

2:30:58 Because if we use this value an explicit value like 200 it

2:31:02 is possible that you might have another customer with the same id.

2:31:05 So when you execute the statement, you're going to get an error.

2:31:08 Because you cannot have duplicate values in this column.

2:31:11 Every value should be unique, so here we're going to use the default keyword

2:31:15 to let you generate a unique value for the customer id,

2:31:18 and after that we need to supply a value

2:31:21 or the first name and last name columns.

2:31:24 So, let's say Jon Smith.

2:31:28 Note that I have enclosed these values with quotes, because as I have told you,

2:31:32 string and for in SQL, we should always enclose string and values with quotes.

2:31:37 Either single or double quotes, okay?

2:31:40 Now, what else?

2:31:41 Back to our customer table, after the last name you have birthdate, however,

2:31:48 as we can see, this column is optional because this check box is not checked.

2:31:51 So here we can use null or an explicit value.

2:31:54 Null means the absence of a value.

2:31:57 So back t our statement, you can type out the birthdate,

2:32:01 like 1990 January 1st or you can use the null keyword to leave out this value.

2:32:08 Now in this demo, I'm going to use a value date,

2:32:12 now to make this code cleaner and more readable,

2:32:14 I'm going to break it up into multiple lines.

2:32:18 That's better, now back to our table,

2:32:21 next we have phone and phone is also optional,

2:32:24 because this checkboxes not checked,

2:32:26 and null is the default value for this column.

2:32:29 So here we can explicitly pass null,

2:32:32 or use the default keyword and then put null into this column.

2:32:36 It's exactly the same.

2:32:38 So, back to our statement, you can pass null or default.

2:32:44 Both these keywords will have the same result.

2:32:45 In this case I'm going to use the null keyword.

2:32:48 Alright, let's have one more look at our table,

2:32:52 next we have 4 more columns that are required,

2:32:56 so address, city, state, and points.

2:32:58 And note that points has the default value of 0,

2:33:01 so we can either use an explicit value like total value like 200,

2:33:05 or use the default keyword and let MySQL generate 0.

2:33:08 So, back to our statement, let's type out an address, it doesn't really matter.

2:33:16 Followed by a city, and a state, let's say california, and finally points,

2:33:21 again we use explicit value or default,

2:33:24 so this is how we can insert a row into a table.

2:33:29 However, in this example, you're only supplying values for first name,

2:33:34 last name, birthdate and these address fields.

2:33:37 So you're leaving out phone number the customer id and the points.

2:33:42 So there's another way to write the statement.

2:33:45 Let me show you.

2:33:46 So after the table name you can optionally supply

2:33:49 the stuff columns that you want to insert values into.

2:33:53 In this case first name, last name,

2:33:58 birthdate and once again I'm going to break up

2:34:02 this statement into multiple lines So 3 more columns, address, city, and state,

2:34:12 so these are the 6 columns that we're going to supply values for.

2:34:16 With this change, we don't have to use these default or null values,

2:34:21 we only supply values for these columns.

2:34:24 So I'm going to remove default from here,

2:34:26 and null, and finally this last default keyword.

2:34:32 So, the 6 values that you supplied here are used for the 6th column.

2:34:36 Now with this change, you can also reorder the columns.

2:34:40 We don't have to list them in the same order,

2:34:43 they were defined in the customers table,

2:34:45 for example you can put the last name first,

2:34:48 and then obviously we should also swap the order of these values,

2:34:52 so we can list them in any orders.

2:34:54 Now we can execute the statement,

2:34:56 now if you look at the output window down on the bottom you should see

2:35:02 the statement followed by one rows unfortunately I

2:35:04 cannot resize this window to show you this message,

2:35:06 but if you look down below you can see that one word was affected,

2:35:11 which basically means one record was inserted into this table.

2:35:15 Now, let's look at the data in the customers table

2:35:20 so the last row is the one that we inserted,

2:35:22 we can see that the MySQL automatically generated the value 11,

2:35:26 this is the effect of auto increment attribute.

2:35:29 So it takes the value of the last row, and incrememnts it by 1.

2:35:33 So here we have the first name.

2:35:35 Last name, birthdate, you didn't supply a value for the phone attribute,

2:35:39 so, that's why we have null here,

2:35:42 we also have address, city state, and the default value of 0 for the points.

2:35:52 In this tutorial, you're going to learn how to insert multiple rows in one go.

2:35:57 For this we're going to use the shippers table,

2:36:00 let's have a quick look at the table definition so here we have 2 columns,

2:36:05 shipper id and name.

2:36:07 Shipper id is primary key, it's not nullable,

2:36:10 and it's an auto incremented column.

2:36:12 So we're going to let MySQL generate values for this column.

2:36:15 Easy.

2:36:16 You only need to supply a value for the name column.

2:36:18 So back to our query editor window,

2:36:21 we type out insert into shippers in parenthesis

2:36:25 we specify the name of the column

2:36:28 we want to insert values into, in this case name followed by the values clause.

2:36:33 Here we add a pair of parenthesis with a value like shipper one.

2:36:39 Now insert multiple rows all you have to do

2:36:42 is insert a comma followed by another pair of parenthesis.

2:36:47 So, shipper 2, one more time, comma, shipper 3,

2:36:52 this is how you can insert multiple rows in 1 row,

2:36:56 now let's execute this statement, alright,

2:37:00 and then inspect the data in the shippers data.

2:37:04 There you go.

2:37:05 So initially we only had 5 shippers and here

2:37:09 are the 3 new shippers that we inserted.

2:37:11 Note that MySQL automatically generated the values for the shipper id column.

2:37:16 So we have 67 and 8.

2:37:20 Alright, here's your exercise for this tutorial.

2:37:25 Write a statement to insert three rows in the products table,

2:37:29 it's really easy you can knock it out in a minute, So here's our products table,

2:37:38 we only have 4 columns,

2:37:40 we're going to leave out the first column because it's an auto

2:37:43 increments column so we only have to supply values for name,

2:37:47 quality, unit price.

2:37:48 Back to our query editor window,

2:37:51 let's insert into products the columns are name,

2:37:56 quanitty in stock, and unit price.

2:38:00 And the values are going to be let's say product 1,

2:38:04 stock is going to be 10 and the unit price is going to be 1, .95.

2:38:08 Now I'm going to select these values.

2:38:12 copy, paste it, like this, change the values accordingly,

2:38:19 and finally the last row for the 3 there you go, let's execute this statement,

2:38:27 alright and then verify the result so in the products table,

2:38:33 now we should have 3 new records, now the id's you see here are 15, 16, and 17.

2:38:40 Because before recording this video I actually inserted

2:38:42 a few records in this table and then deleted them.

2:38:44 So I had product ID's 11, 12 13, and 14.

2:38:49 And even though they're actually deleted from this table,

2:38:52 MySQL remembers their id's so instead of incrementing 10 x 11,

2:38:57 it incremented 14 which is the last record from before.

2:39:00 And that's why you generated 15.

2:39:02 On your computer, the items are going to be 11, 12, 13.

2:39:12 So far you have only learned how to insert data into a singular table.

2:39:16 In this tutorial, I'm going to show you how to insert data into multiple tables.

2:39:19 Here's a really good example, look at the orders table, what columns do we have?

2:39:24 We have the order id, we have the customer id,

2:39:28 so we know who has placed the order, we know the date of the order,

2:39:32 you know the status, comments, as well as shipping information,

2:39:36 but the actual items for this order are not in this table,

2:39:39 they are in the order items table.

2:39:41 So in this table, we have 4 columns we have the order id,

2:39:45 so we know what order these items for.

2:39:48 We have the product id, so we know what product order,

2:39:52 in what quantity, and in what price.

2:39:54 So an actual order can have 1 or more order items.

2:40:00 This is what we call a parent child relationship.

2:40:03 So this relationship the orders table is the parent,

2:40:05 and the order items table is the child.

2:40:08 So one row in the orders table can have

2:40:10 1 or more children inside the order items table.

2:40:13 Now in this tutorial,

2:40:14 I'm going to show you how to insert an order and all it's items.

2:40:18 So you will learn how to insert data into multiple cables.

2:40:23 alright, back to our query editor window.

2:40:25 First we need to insert the order,

2:40:28 so insert, into, let me close the navigator panel,

2:40:33 we want to insert a record into the order table,

2:40:36 now what columns do we have here?

2:40:40 So we have these columns but only the first

2:40:43 four are the and actually the first one,

2:40:45 we don't want to worry about, because that's an auto increment column.

2:40:48 So we only want to supply values for customer id, order date, and status.

2:40:52 So back to our query, specifying those columns here,

2:40:57 customer id, order date, and status, now let's supply the value.

2:41:04 In the customer id column, we need to add a valid customer id.

2:41:09 So let's have a quick look at our customers table, there you go.

2:41:14 In this table, we have 11 records.

2:41:17 So these are the valid customer id's.

2:41:20 Now back to our query, let's use 1 for customer query and then 2019,

2:41:26 January the 2nd for the order date,

2:41:29 and one for the order status, once again in this column,

2:41:33 we need to insert a valid order status id.

2:41:36 If you don't supply a valid id, MySQL is going to get ahead of us.

2:41:41 So we insert an order here, the items,

2:41:44 now back to our order items table, in this table,

2:41:49 we have this order id column, so here's the as soon as we insert an order,

2:41:54 MySQL is going to generate an id for our new order now we

2:41:58 need to access that id in order to insert the items in this table.

2:42:02 How can we do that?

2:42:04 Well, back to our query editor window, in MySQL,

2:42:08 we have a bunch of built in functions,

2:42:11 and a function is basically a piece of code that we use ourself.

2:42:14 Just like a function is a feature in your tv.

2:42:17 Every tv comes with a bunch of functions like power on, power off,

2:42:21 change the volume, change the channel so on.

2:42:24 So MySQL and other database engines come with a bunch

2:42:26 of built in functions that we can use in our programs.

2:42:30 One of these funcitons is last insert id.

2:42:34 We can call or execute this function, by adding parenthesis here,

2:42:38 and this will return the ID that MySQL generates when we insert a new row.

2:42:44 So, before going any further,

2:42:46 let's just select these, and make sure you get the right result.

2:42:49 Now we have a syntax error here because we

2:42:51 didn't terminate the first statement with a semi colon,

2:42:54 alright, now let's execute this query, alright,

2:42:56 so the id of the new order is 12.

2:43:00 Let's verify that.

2:43:01 So back to the orders table let's look at the data.

2:43:06 On my machine I have 12 records here,

2:43:08 I actually created 1 just before recording this video,

2:43:11 so on your machine you're going to have 11 orders.

2:43:16 Now back to our query window now that we know

2:43:18 how to get the id of the newly inserted record,

2:43:20 we can use that id to insert the child records.

2:43:24 So, we're going to write another insert statement, insert into order items,

2:43:30 let's have another look at the columns in this table.

2:43:34 So we have 4 columns on all of them are required,

2:43:37 so there's really no need to specify the column name in our insert statement,

2:43:41 you simply supply values or order id, product id, quantity and unit price.

2:43:47 So, here in the values clause, we add parenthesis, what is our order id?

2:43:54 That is the value returned from calling this function.

2:43:57 So I'm going to cut this from here and next we need to supply a valid photo id,

2:44:04 but let's say product 1, quantity, let's say 1.

2:44:08 And the unit price 2.95 dollars.

2:44:12 Now let's delete this select we don't really

2:44:14 need it anymore let's add another set of values,

2:44:17 so once again, we're going to call last insert id, to get the id of a new order,

2:44:23 we're going to change the product to product number 2,

2:44:26 and add a different price.

2:44:28 That's it.

2:44:29 Now let's execute this statement and see what we get.

2:44:35 Alright, so, back to our orders table,

2:44:37 let's refresh the records here, alright so we have a new order,

2:44:42 order number 13, beautiful, now let's look at the orders items table,

2:44:47 here's the order items, let's open the table.

2:44:50 So we should have 2 items, or order number 13.

2:44:55 Beautiful.

2:44:56 So this is how you insert hierarchical data in SQL.

2:45:04 In this tutorial, I'm going to show you

2:45:06 how to copy data from one table to another.