0:01 Hey guys, welcome to the free SQL 0:03 course. In this course, we're going to 0:06 focus on the actual structured query 0:08 language syntax. So, we're going to put 0:10 away the fancy graphical user 0:12 interfaces, and we're going to get into 0:14 the actual programming. So, we'll be 0:17 using PHP MyAdmin, which is a graphical 0:19 tool, but we're going to be using the 0:21 SQL shell to do pretty much everything 0:24 uh except look at the actual rows that 0:26 are being returned. All 0:28 right. So, before we get started, I just 0:30 want to remind you to subscribe with us, 0:32 follow us on Twitter and Facebook, and 0:35 if you're feeling really generous, 0:36 donate a couple bucks so that we can 0:38 keep making these free courses. All 0:40 right, so let's take a look at what 0:43 we'll be doing, what you'll learn. So, 0:45 we'll take a look at kind of an overview 0:48 of SQL, talk about what it is, uh what 0:51 it's used for, and then we'll start to 0:53 look at how to create, alter, and delete 0:56 databases and tables, um how to query a 0:59 database using multiple 1:01 operators. We'll take a look at some of 1:04 the the various data types that are 1:06 available. Um I'll show you how to 1:08 insert, update, and delete records, how 1:10 to join tables together, use aliases. 1:14 We'll look at primary keys and foreign 1:16 key constraints and also indexes. All 1:19 right, among some other stuff that'll 1:21 get thrown in the 1:22 mix. So what is SQL? It stands for 1:25 structured query language and it's a 1:27 specialurpose programming language that 1:30 was created to work with relational 1:33 databases. All right, SQL has been 1:35 around for a very long time, but it's 1:38 still relevant. It's the main uh it's 1:41 the main method of interaction between 1:43 an application and a relational 1:46 database. All right. And SQL is used 1:49 anywhere from um just small individual 1:53 blogs to you know enterprise corporation 1:56 level. All right. So it's very very 1:59 flexible and also very scalable. 2:04 So, some of the popular databases that 2:06 use SQL. This is by no means the entire 2:09 list. There's a lot more, but these are 2:11 some of the more popular ones that you 2:13 may have heard of. All right. First, we 2:15 have MySQL, which is extremely popular. 2:18 That's what we'll be working with in 2:19 this particular course. Um but you also 2:22 have Postgres which is another really 2:24 popular uh relational database, Oracle, 2:27 Microsoft SQL Server, SQL Lite and then 2:31 you have uh DBase, Hadoop, Max DB, Maria 2:35 DB which is uh drop in for MySQL and 2:39 open base. Okay, so these are just some 2:41 of the the many relational databases 2:44 that use 2:45 SQL. All right. So when we talk about 2:48 SQL tools, there are dozens and dozens 2:50 of tools. Some are dedicated to one 2:54 specific uh relational database system. 2:57 Others are 2:59 cross-platform. So these are just a few 3:01 of the many. Um so the command line 3:03 client when you download let's say 3:05 MySQL, you get um you get a shell 3:09 program that you can log into through 3:10 the command line and you can start to 3:13 create your tables and and do all that. 3:15 All right. Then you have graphical tools 3:18 like admin which we'll be using that um 3:22 give you uh an easier way through gra a 3:25 graphical user interface to to do that 3:27 stuff but also give you the option to 3:30 use uh some kind of SQL shell. All 3:32 right. Um and did I say we're using 3:34 that? We're not using that. We're using 3:35 PHP MyAdmin which I'll get to in a 3:38 second. Um Adminer and Firebird the next 3:41 one are both crossplatform. So you can 3:43 use these with MySQL, Postgres and um a 3:48 bunch of other ones as well. MySQL 3:50 workbench is very popular. That's it's 3:53 actually offered free from the official 3:55 MySQL website. Um you can do diagrams 3:58 and and some really nice graphical stuff 4:00 with that. PHP MyAdmin is a web tool 4:04 that is offered on on many C panel 4:07 hosts. So if you if you get a hosting 4:09 account with HostGator or Bluehost or 4:12 something like that, uh they usually 4:13 offer PHP MyAdmin with that with their 4:17 packages. All right, PG Admin 3 is a 4:20 really nice desktop tool for Postgres. 4:23 Um I do have a video on that if you want 4:25 to check that out. SQL Pro is a Mac tool 4:29 I don't know too much about. And then 4:31 HeidiSQL I've used a couple times but 4:34 never really got got too much into it. 4:37 All right, so these are just some of the 4:39 the many many tools that are 4:41 available. All right, so before we can 4:44 actually dive in and start looking at 4:46 SQL, you need to have some kind of SQL 4:49 database installed on your server or on 4:52 your local machine, whatever it may be. 4:54 Now, what I would suggest is if you're 4:56 just using, let's say, Windows or or or 4:59 Mac and you don't have a server that you 5:02 actually want to set up, um, an easy 5:04 thing to do is to use something like 5:06 Zamp or X AMP. Um, what that does is it 5:09 gives you an Apache server, MySQL, PHP, 5:12 and a bunch of other tools available on 5:15 your local machine. All right? So, you 5:17 don't have to connect to a special 5:19 server or anything like that. All right, 5:21 that's what we'll be doing. If you want 5:22 to download ZAMP, you can go to 5:25 apachefriends.org. And it's it's 5:27 crossplatform. It's available for 5:28 Windows, Linux, and Mac. So, I already 5:31 have it installed. I'm not going to go 5:32 through it, but I do have a video on how 5:35 to get ZAMP set up and running on 5:38 Windows. All right. It's actually very, 5:40 very easy. So once you do that, you'll 5:43 be able to uh you'll be able to open up 5:46 PHP MyAdmin, which is a graphical uh 5:49 tool to manage your server, your 5:52 databases. So you can get to that with 5:56 localhost/phpmyadmin. Now, when you 5:58 first install ZAMP, you probably won't 6:01 have uh a password for your your root 6:04 user, and you want to fix that. So, what 6:08 you can do is in PHP MyAdmin, if you 6:10 click on user accounts and you look at 6:13 where it says root for the username and 6:16 local host for the host name, you 6:18 probably have a no right here. If you if 6:21 you have a yes, then it's it should be 6:23 fine. Then you know you have a password. 6:25 If you have a no, then let's click edit 6:27 privileges. And then you want to click 6:29 change password and then just put in a 6:31 password here and click go. All right. 6:34 Now, if you get booted out of PHP 6:36 MyAdmin because you're not logged in, 6:38 then what you need to do is go to your 6:42 uh your ZAMP 6:44 folder. That's not it. You want to go to 6:47 ZAMP and then go to PHP 6:50 MyAdmin. And then this config file right 6:52 here, config 6:54 inph. You want to open that up and then 6:58 just Whoops. 7:00 Just put the password that you chose in 7:03 this field right here. This should be 7:04 empty. Okay, you want to put it in there 7:06 and save it and close it. Go back and 7:09 you should be 7:13 okay. So, creating a database is 7:16 extremely easy. Okay, so what we're 7:18 going to do is we're in PHP MyAdmin and 7:21 we could simply create a database 7:22 through here. Um, but that's not what we 7:26 want to do. We want to focus on the SQL 7:28 programming language. So we'll click on 7:31 the SQL tab up here. And this is where 7:33 we can actually run queries. All right, 7:36 let me make this a little bigger. Okay, 7:38 so what we're going to do is say create 7:41 database. And then we want to name it. 7:44 Okay, I'm just going to say test for 7:46 now. And we're going to end all of our 7:48 statements with all of our lines with a 7:49 semicolon. So let's click go. And you 7:53 can say, see, we got an okay message. 7:55 But just to check, we can click on 7:57 databases. And there's our test 7:59 database. All right. And while we're at 8:01 it, I'll show you how to delete a 8:03 database. To do that, you want to say 8:06 drop database and then whatever the 8:08 name. Okay. If we run that, we get an 8:10 okay message. And if we look, test is 8:13 now gone. All right. So, that's how you 8:15 can create and drop a database. Now, I'm 8:17 going to create one just to work with 8:19 throughout this course. So again, let's 8:22 say create database. And this is going 8:25 to be just a fictional company. I'm just 8:27 going to call 8:28 Acme. All right. So we'll click go. Now 8:31 we have our Acme 8:35 database. Okay. So we have an an empty 8:38 database now, which isn't very useful 8:40 because we have no tables. All right. So 8:42 let's create our first table. To do 8:44 that, we're going to say create table. 8:48 And this I need to make this a little 8:52 bigger. All right, that should be good. 8:54 So, we want to say create table. And 8:56 then we want to name it. So, let's 8:58 create one for customers. All right. And 9:01 then what we want to do is in here we 9:03 want to specify the fields that we want 9:06 or the columns. So, I'm just going to 9:08 space this out a little bit. And uh we 9:11 know that we want an ID. So, to to 9:14 create a column, you want to put the 9:16 column name and then the data type. In 9:17 this case, it's going to be an integer. 9:19 So, we want int. All right. And then we 9:22 also want to make sure that the ID 9:23 cannot be null. Okay. Now, we want to 9:27 separate these with a 9:29 comma. And the next field that I want is 9:32 first name. Okay. Now, as far as the 9:35 naming conventions for columns, this 9:37 here I like to use camelc case, which 9:40 means that the first letter is lowercase 9:42 and then every word after that, the 9:44 first letter will be uppercase. Okay. 9:46 Often you'll also see something like 9:48 this. You'll see that we um separate the 9:52 words with an underscore. All right? So, 9:54 it's all preference. You could you could 9:55 do whatever you'd like. Uh some people 9:58 use partial case, which the the first 10:00 letter would be capital as well, but I'm 10:03 going to stick to camel case. Okay. So, 10:05 we have a first name. Now, for the data 10:06 type, that's going to be what's called a 10:08 varchar or a var. And that is a just a 10:12 string of characters. because it could 10:14 be letters, numbers, characters, 10:16 whatever. Um, and then we have to 10:18 specify the max length, which is going 10:20 to be 255. All right? And you could do 10:23 lower. Obviously, a first name isn't 10:25 going to be 255 characters, but um, just 10:29 to be safe, I use 255 for pretty much 10:32 everything. Okay. Next, we're going to 10:34 have a last name. Same 10:37 thing. Uh, I believe that the rest of 10:39 our fields are going to be var. Whoops. 10:45 Okay. Then we'll have an 10:51 email. I might as well just copy this. 10:54 Okay. So, 11:01 email. Then we'll have address. 11:06 And let's do 11:09 city, 11:12 state, and let's do zip 11:18 code. Okay, so this here will create the 11:21 customer's table for us. But I want to 11:23 do a couple things. One, we need to 11:25 specify a primary key. Okay, a primary 11:28 key is 11:29 unique and is usually attached to the 11:32 ID. 11:34 So all we need to do to to create this 11:36 is just add on to the end 11:38 here primary 11:41 key and then we want to specify the uh 11:45 which column we want and we just want to 11:47 put that in parenthesis. So it's going 11:48 to be the ID. All right. Now there's one 11:51 more thing I want to do and that's 11:52 setting the ID to auto increment. Okay. 11:55 And what that means is that let's say we 11:58 enter um we enter a row and it has the 12:02 ID of five. Well, the next time we go 12:05 and create a a new row, a new record, 12:07 the ID will be six. It's going to 12:09 increment by one by default. All right, 12:12 you can change it to something else. Uh 12:14 but one is the default and that's what's 12:16 usually used. So what we want to do is 12:19 just add on to this. We want to say auto 12:22 increment. Just like that. All right. 12:24 So, we're ready to create our table. 12:26 Let's go ahead and click 12:31 go. Uh, what's this? No database. Oh, 12:35 all right. So, we're actually not inside 12:38 of the Acme database. So, what I'm going 12:40 to do is just I'm going to copy this 12:42 with control C and then go to databases, 12:46 click on Acme, and then click on SQL. 12:49 And now you can see we're in the Acme 12:51 database. Okay. So, let's go ahead and 12:53 do that. 12:54 Okay. So now if we go to our Acme 12:58 database, you can see now we have a 13:00 customers table. Okay. If we click on 13:02 that, we're not going to see anything 13:03 here because we have no records in here, 13:05 no rows. Uh but if we click on 13:07 structure, you can see everything that 13:09 we just created. And you see the little 13:12 key icon next to the ID because that's 13:14 an a primary key. All right. It's also 13:17 set to auto increment. And you can see 13:19 all the data all the um data types here 13:22 as well. So we created our first table. 13:26 Now I'm going to show you how to insert 13:28 data into the 13:32 table. All right guys, so now we're 13:34 ready to insert some data. And you can 13:37 see here that we're in the Acme 13:39 customers table. Uh all I did was went 13:42 to this screen and then click SQL. Okay. 13:47 Um actually let's click on customers and 13:50 then SQL. And when we're in this view 13:52 you can see we have all the columns over 13:54 here that we can use for reference. 13:57 Okay. So let's go ahead and insert some 13:59 data. So to do that we're going to say 14:01 insert 14:02 into 14:04 customers. All right. And we want to 14:07 specify the fields that we want to 14:09 enter. So let's say first name. 14:12 Basically this is going to be everything 14:13 except the ID. The ID is auto increment 14:16 and will create on its 14:26 own. Okay. So then what we want to do is 14:29 say values and then some more 14:31 parentheses and we want to put in our 14:34 values. Okay. So we have let's say for 14:37 first name we'll do 14:38 John last name 14:42 do 14:45 email we'll say jd do 14:48 at 14:51 gmail address we'll say 55 Main 14:56 Street and we'll say 15:10 Austin. Okay. So, this should insert the 15:14 customer. Let's go ahead and click 15:19 go. Okay. So, notice it says one row 15:23 inserted. If we click on browse, there 15:26 you go. We have our John Doe customer. 15:29 Okay. So, what I want to do is insert uh 15:32 a few more. So, I'm just going to paste 15:34 this in. All right. So, this is how we 15:37 can insert multiple records at once. So, 15:40 we say insert into customers and then we 15:43 specify all of the columns, but we only 15:46 specify these once. Okay? And then we 15:48 can add multiple records. And we're just 15:51 separating them with a comma. And the 15:53 last one gets a semicolon. All right. 15:55 So, let's run go. Okay. So you can see 15:58 it says five rows inserted. And if I 16:00 click browse, you can see we now have a 16:03 bunch of 16:07 customers. So now I'm going to show you 16:09 how to update a record. Okay. So let's 16:13 say that one of our users wants to 16:16 change their email address. So what we 16:18 can do is say update and then whatever 16:21 the table name update customers and then 16:25 we want to use set actually I'm going to 16:27 put these on their own lines just to 16:29 make it a little more readable. So, we 16:32 want to set 16:34 email equal to whatever we want to set 16:37 it to. Let's say um we'll just say test 16:42 atgmail.com. Okay. So, we want to set 16:44 email to that. 16:47 And then we want to say where which this 16:50 part is very very important because if 16:52 you don't include the where then all of 16:55 the customers emails are going to change 16:57 to this. So you want to specify this. 16:59 We're going to say where 17:01 id uh where ID is equal to 17:05 three. Okay. So let's go ahead and run 17:09 that. Okay. So we get one row affected. 17:12 If we go to browse, you can see the the 17:15 customer with the ID of three, which is 17:17 Kathy. Her email is now 17:24 test@gmail.com. All right, guys. Looking 17:26 at our table, I want to show you how to 17:29 delete a customer. All right, so let's 17:32 say that Derek is not a customer anymore 17:34 and he has the ID of six. So, what we 17:37 want to do is go to our SQL and we're 17:40 going to say delete 17:44 from customers. Again, the where part is 17:48 extremely important. If you don't have 17:50 this, it's going to delete all your 17:52 customers. So, we'll say where ID is 17:55 equal to three. All right. So, let's go 17:58 ahead and click go. Okay. We get one row 18:01 affected. If we look now, you'll see Oh, 18:05 did I say three? Oh, okay. I deleted the 18:07 ID of three. I meant six, but that's 18:09 fine. Same thing. Um, so you can see 18:12 that we deleted a 18:16 customer. All right, guys. So, before we 18:19 get into selecting data and fetching 18:21 data, I want to show you alter table. 18:25 Alter table is used to do things like 18:27 add columns to uh to a table, uh delete 18:32 columns, change the data type, things 18:35 like that. All right, so let's go to our 18:37 s well actually let's figure out what we 18:39 want to do first. So first thing I want 18:41 to do is show you how to add a column. 18:42 So we're just going to add one called 18:45 test or test call. So let's go to our 18:47 SQL and what we want to do is say uh 18:51 alter 18:53 table 18:55 customers and we want to say 18:59 add test call. Okay. Uh and we also want 19:04 to specify the data type. Let's just say 19:09 varchchar 19:12 255. All right. So let's go ahead and 19:14 click go. Okay, so we got an okay 19:17 message here. Let's click browse. And 19:20 you'll see we have test call. Now 19:22 everything is null because we haven't 19:24 done any inserts with the test call 19:27 included. Okay. And if we click 19:29 structure, you'll see that test call is 19:31 a 19:32 varchar. Okay. So the next thing I want 19:34 to show you is how we can change the 19:36 data type for a column. So let's go and 19:42 say alter 19:45 table 19:46 customers. All right. Now the syntax 19:49 varies depending on what database you're 19:52 using. We're using MySQL. So what we'll 19:55 do is say modify. All right. But with 19:59 other databases you might see alter 20:03 column. 20:05 Okay. So let's say modify 20:10 uh modify 20:13 column. What were we changing? Uh test 20:17 call. And we want to change that to an 20:19 int. And let's just give it a max of 11 20:24 characters. Okay. Put a semicolon there 20:28 and 20:29 go. All right. Now if we go to our table 20:33 and test call, if we click structure, 20:37 you'll see that it's now an integer uh 20:39 with 11 max 20:41 characters. Okay. So last thing with 20:44 alter table is I want to show you how to 20:47 actually delete a column. So, we're 20:49 going to say again alter table 20:53 uh 20:58 customers and we're going to say 21:03 drop column test 21:09 call. Okay. And now if we go back now 21:12 you'll see test call is 21:17 gone. All right guys. Okay, so now we're 21:19 going to get into selecting data and 21:22 there's a lot to it. There's a lot of 21:24 different types of queries, select 21:26 queries that we can make. So we're going 21:28 to start simple and then just move up. 21:32 Okay, so first thing to do is the 21:35 easiest select uh statement would be 21:38 select all 21:41 from customers. Okay, the asterisk is 21:44 used is a placeholder for all um all 21:48 columns. Okay, so let's click 21:51 go and you can see it just gives us all 21:54 the all of the columns. Okay, now if we 21:57 want to limit that, we can say let's 22:00 select first 22:02 name and last name from customers and we 22:07 click go. And that just gives us the 22:10 first name and the last name. 22:13 Now let's say we want to select a 22:15 specific customer. So to do 22:19 that let's say select all from customers 22:25 and then we just want to use the wear 22:26 clause. So we'll say where ID is equal 22:30 to three. Okay. Actually I don't think 22:32 we have a three 22:34 anymore. All right. Now, we're using the 22:37 ID because that's the primary key and 22:40 because it's unique. Okay? So, if we 22:42 were to say select from where name or 22:45 first name equals Bob or something like 22:48 that, then it's going to find all of the 22:51 Bobs. So, if you have two customers 22:53 named Bob, then you have a problem. All 22:55 right? So, you want to use something 22:56 unique in your wear clauses. So, let's 22:59 go ahead and run that. And you can see 23:02 that that gives us Mike. He has the ID 23:04 of 23:05 two. All right. So that's how we can 23:08 select a specific customer. Now what if 23:12 we want to uh sort it? Okay. If we want 23:16 a certain order. So what we can do is 23:18 let's say select all from customers and 23:21 then we're going to say order by and 23:24 let's do last 23:27 name. Okay. We run that and you can see 23:30 we get all rows and it's by last name. 23:33 Okay, it starts with the D and goes down 23:35 to W. We can also change the sorting 23:38 order. Okay, if we want, we can say 23:40 descending D S 23:43 C and click go. And now you can see it 23:46 starts with 23:48 Williams. Okay. And same thing, you can 23:50 also do 23:52 ascending, which does the same thing as 23:54 the default. Okay. 24:00 Now, let's say that we want to return 24:04 um all of the the different states. All 24:06 right? So, we could do 24:12 select state from 24:17 customers. Okay, we click that go. And 24:20 now you can see it gives us the states. 24:22 But what if we don't want duplicates? 24:24 Okay, what if we just want one of each 24:27 state? Well, for that we could use 24:29 distinct. Okay, so we just put right 24:33 here select distinct. Okay, we click go. 24:37 And now you can see it just gives us one 24:39 of 24:40 each. Now there's some other select 24:43 statements that I want to show you. Um, 24:45 but some of them have to do with 24:48 integers with numbers. So what I'm going 24:50 to do is I'm going to add I'm going to 24:54 alter the table and add an age column. 24:57 All right. So we'll go to SQL and let's 25:00 say 25:02 alter table 25:05 customers. Uh and then we want 25:11 to add column age which will be 25:17 uh an 25:20 int. Okay. So if we go to browse now we 25:24 have age. And just really quickly, I'm 25:25 going to add their ages through here. 25:28 Okay, we'll say 25:30 23, 45, 25:35 um, 25:39 33, 25:42 20, and let's say 25:45 64. Okay, so now our customers have 25:49 ages. So let's go back to the SQL window 25:52 and let's say select all from customers 25:55 where 25:57 um age 26:02 is is less than let's say 30. Okay, so 26:08 this is an operator the less than and 26:11 I'll get to some of the other ones in a 26:12 minute. So if we click go, you can see 26:14 it's returning us John and Lillian which 26:17 are aged 23 and 20. So these are the 26:19 customers that are under the age of of 26:22 what was it? 26:26 30. Okay. So there's different operators 26:29 that we can use as I just showed you. We 26:32 have we used the less than and of course 26:34 we've used equal to. But there's 26:36 actually a whole bunch. Okay. So you can 26:38 see we have equal to. This here is not 26:41 equal to which you can also use in many 26:45 different database systems. You can use 26:47 exclamation equals which means not equal 26:49 to. All right, we have greater than and 26:51 less than. We have greater than or 26:54 equal, less than or equal between, which 26:57 will give us a range which I'll show you 26:59 in a minute. Like, which will match a 27:02 certain pattern. This is often used for 27:04 searches. Okay, if you have a website 27:06 and you want to be able to search 27:08 articles or something like that, in will 27:11 be equal to one of multiple possible 27:14 values. We can use is or is not to 27:18 compared to null. Is not distinct from 27:21 which is is equal to value or both are 27:24 nulls. And then we have as used to 27:27 change a field name when viewing 27:29 results. All right, we have some 27:31 examples over here, but I'm going to 27:33 show you some 27:34 examples. All right, so let's say we 27:37 want the customers that are an age 27:41 between, let's say, 22 and 40. All 27:46 right. So what we can 27:47 do is say select 27:51 uh we want to say 27:53 select all from 27:58 customers where 28:03 whoops 28:05 age. Um, and then we want to 28:10 say 28:12 between what I say 22 28:15 and 30. All right. So, let's go ahead 28:18 and run 28:19 that. And looks like the only one we 28:22 have is John who's 23. Um, let's let's 28:27 actually do 28:29 40. Okay, we click go and we get Stephen 28:33 who is age 33. Okay. So that's how we 28:36 can use 28:37 between. So another one we can use is 28:40 like. Okay. Like will match some kind of 28:42 pattern. So what we're going to do here 28:45 is let's say 28:47 select all from 28:50 customers and then we want to say 28:54 where 28:56 city like and I forget what we have. I 29:00 know we have Boston. So, let's do 29:05 um percent sign n and let's see what 29:09 that gives 29:11 us. Okay, so what that's giving us is 29:16 all of the customers where their city 29:18 ends with n. Okay, because if we look at 29:22 it, the percent 29:24 sign, this is a wild card. Okay, so that 29:28 means that anything could be here. And 29:30 then we need it to end with N. Okay, 29:33 which Brooklyn and Boston obviously that 29:36 makes sense. Okay, if we put uh 29:40 O and click go. Now we're not getting 29:43 Brooklyn anymore because it doesn't end 29:45 with 29:46 O. 29:48 Okay. Now let's say that instead of just 29:52 percent N, let's do percent N and then 29:54 another percent and let's see what that 29:57 gives us. 30:01 Okay, so that's giving us three records. 30:03 Boston, Brooklyn, and Yonkers. And the 30:05 reason for that is that now we're 30:07 putting a wild card on this side of it. 30:10 So basically, this is getting any city 30:12 that has the letter N in it. And you'll 30:14 notice Yonkers has it. It's not at the 30:16 end, but it's still showing up because 30:19 we have a wild card on on both 30:22 sides. All right. And then at the same 30:24 time, we can also use not like. Okay. So 30:27 if we put here instead of like we put 30:30 not like 30:32 uh and then click 30:35 go. Okay. Now it's giving us Amesberry 30:38 and exit because these cities don't have 30:41 the letter N in 30:43 them. Okay. So that's it for like. Now I 30:46 want to show you in. Okay. In is also 30:50 very helpful. What that does is it 30:53 allows us to specify multiple values in 30:55 a wear clause which can really shorten 30:58 it up. All right, so let's say 31:01 select all 31:03 from 31:05 customers and then we're going to say 31:09 where 31:11 state in and then we want to open up 31:14 parentheses. Let's say 31:19 um New York 31:25 and New 31:27 Hampshire. Okay. And we'll click 31:31 go. And you'll see that we're getting 31:33 all of the uh customers that are from 31:37 either New York or New Hampshire. Okay. 31:39 The Massachusetts customers aren't 31:41 coming 31:42 back. All right. Now imagine if you if 31:45 you wanted to specify you know nine or 31:47 10 states if you were to use just a wear 31:50 you would be it would be a long long 31:53 query and u but using in we can just 31:56 specify them all inside of our 32:02 parentheses. All right so we're going to 32:04 talk a little bit about indexes. All 32:06 right indexes are used to speed up 32:09 queries. 32:11 All right. It is uh it's a pointer to 32:14 data in a table. So an index in a 32:17 database is very similar to an index in 32:20 the back of a book. Okay. It's it's used 32:23 to find data more quickly and 32:25 efficiently. All right. Users do not see 32:28 indexes. They are just used to speed up 32:31 search queries or searches. 32:34 If in your website you want to be able 32:37 to look up uh let's say users by an ID, 32:41 you would put an index on that ID field. 32:44 It would speed up things. All right? You 32:46 only want to create indexes on columns 32:48 and tables that will frequently uh will 32:51 be frequently searched against. Okay? 32:53 So, you don't want to put them on every 32:55 single column or 32:57 table. Okay? So let's say that in our 33:00 application we're going to want people 33:02 to search uh customers or you or admins 33:05 to search customers by city. So we want 33:08 to add an index on the city column. So 33:11 let's go to our SQL 33:14 here. So let's 33:16 say create 33:20 index uh and then we want to give a name 33:22 to it. Let's just call it C 33:26 index. And then we want to say 33:29 on 33:30 customers and let's say 33:37 city. Now we're going to ignore that X 33:40 for now. So let's click go. And now we 33:44 should have an index on the city column. 33:49 So now if we go to structure and we look 33:52 at city, you'll see we have this little 33:54 key icon which means that we have an 33:57 index on 33:58 it. All right, let's make sure we can 34:01 still uh select data from it. Okay, so 34:05 if we say 34:06 select city 34:11 um 34:13 from 34:15 customers. All right. and the search is 34:17 going to be uh much more efficient and 34:20 faster. All right, to remove an index is 34:23 very easy as well. So let's say 34:26 drop index C 34:30 index 34:34 on customers. Okay, if we go ahead and 34:37 run that and we go back to structure, 34:40 you'll see that city no longer has an 34:43 index. 34:48 All right. So, we've gone over quite a 34:50 bit pretty much all of the fundamentals 34:52 of uh selecting data uh from one table. 34:56 Okay. So, what we want to do now is 34:59 create some other tables and create a 35:02 relationship between them and then I'll 35:05 show you how we can actually join tables 35:07 together for select queries. All right. 35:10 So, what we'll do is I'm going to paste 35:12 this stuff in because it's stuff that 35:13 we've already done. So what this is 35:15 going to do is it's going to create a 35:17 table called products. We're going to 35:18 have an ID which will be auto increment. 35:21 It has a name and a price and then a 35:23 primary key of ID. Okay. So let's go 35:27 ahead and run 35:29 that. Okay. So now you can see we have a 35:31 products table. So we're going to create 35:33 another one called 35:35 orders. So the orders table is going to 35:40 have an ID, primary key auto increment. 35:42 And then you'll see we're also going to 35:44 have an order number which is just going 35:46 to be uh just a madeup number that we 35:49 create. And then product ID and custom 35:52 ID. These are going to be foreign key 35:54 fields. All right. Um what that means is 35:57 that this product ID column is going to 36:00 match up to the products table ID and 36:03 then this one will match up to the 36:05 customers table ID. Okay. Now, we 36:08 haven't defined our foreign keys yet, 36:10 but I'll do that in a second. All right. 36:12 Right. And then you'll see we just have 36:13 age. We don't need I don't know why 36:15 that's there. Order date which is going 36:17 to be set to the date time data type. 36:20 And then we're just saying the primary 36:22 key is going to be ID. So let's go ahead 36:24 and add the foreign key 36:26 restraints. Okay. So what we want to do 36:28 is say foreign 36:31 key let's say customer ID. And then we 36:35 want to specify its reference. 36:39 So we want to say 36:42 references will be to the customers 36:44 table and then to the ID 36:47 field. Okay. And then what we can do is 36:50 copy that. We want to do the same thing 36:52 for products. So this will be foreign 36:55 key on product ID references is going to 36:59 be to the products 37:01 table to the ID field. 37:05 Now, before we run this, there's one 37:07 more thing I want to do for the date. 37:09 The order date. I don't want to have to 37:11 manually put that in. So, we're going to 37:13 set a default. So, what we can do is 37:15 just say 37:17 default. And we want to say current 37:21 timestamp. Okay. So, that'll that'll 37:23 happen for us. We don't have to uh 37:25 manually include the date and time. All 37:28 right. So, let's go ahead and run this. 37:31 We'll click go. 37:34 Okay, looks like it went. Okay, we'll 37:36 click structure. And you can see we now 37:38 have orders. Okay, if we click in orders 37:42 and go to structure, notice that the 37:45 product ID and the customer ID have a 37:47 key icon that's telling us that these 37:50 are foreign key fields. Now the purpose 37:53 of a foreign key field is so that 37:57 um we can prevent data corruption within 38:00 our database or just create an unstable 38:04 database. So let's say we have this 38:07 orders table and we have uh we have an 38:10 order that has a product ID of three. 38:12 Okay. Well, we don't want to have this 38:15 product ID of three and then in the 38:17 products table not have that specific 38:20 product with the ID of three. All right. 38:22 So, it it won't let us delete certain 38:26 records that are needed for other 38:28 records, if that makes sense, 38:32 hopefully. All right. So, that's why we 38:34 need foreign key 38:39 constraints. All right. So, now we're 38:41 going to take a look at another very 38:42 important part of of using relational 38:45 databases, and that is table joins. All 38:48 right. Joins are used to combine rows 38:50 from two or more tables based on a 38:52 common field between them. All right. 38:54 And there's different types of joins. We 38:56 have an inner join, left and right 38:58 joins, and a full join. All right. So, 39:01 let's take a look. All right. So, before 39:03 we can do any joins, we need to actually 39:06 have some data in our orders and 39:08 products table. So, let's go ahead and 39:10 go to products, and we're going to just 39:13 add some data 39:15 here. All right. So we're inserting some 39:18 products here. Okay, we have a name and 39:20 a price. And I just call them product 1 39:23 2 3 4 and five. And then just some 39:25 integers for price. All right. So let's 39:28 go ahead and run that. Okay. And we look 39:30 at the structure. Um actually want to go 39:33 to browse. And you can see we have our 39:36 products. All right. So now what we want 39:37 to do is we want to go 39:41 into orders. And I'm going to create 39:44 some orders. 39:49 All right. So, let's take a look at 39:50 this. We're saying insert into orders 39:53 and we have three things we want to put 39:54 in. Order number, product ID, customer 39:57 ID. Okay. Now, notice we don't have the 39:59 order date because that has a default of 40:02 the current timestamp. All right. And if 40:04 we look at this, we're just giving the 40:05 order numbers 001 and then just going up 40:09 by one. All right. And then we have our 40:11 product IDs. You want to make sure these 40:14 actually exist. Same thing with the 40:16 customer ids. All right. So, let's go 40:18 ahead and click 40:20 go. All right. And if we go to our 40:24 orders table, now you can see we have 40:26 all of these 40:28 orders. Now, if you want to fetch an 40:31 order along with its information, if we 40:34 were to just get from the orders table, 40:36 this is all we could get. And this this 40:39 obviously means nothing to us um as far 40:42 as readability. So what we need to do is 40:45 do uh some table 40:47 joins. So let's go to 40:53 SQL and we're going to start with an 40:55 inner join which is probably the most 40:58 popular. And what that does is it'll 41:00 return all rows when there is at least 41:02 one match in both tables. All right. So 41:05 let me give you an example. 41:08 So we're going to say 41:12 select. Now we're using two different 41:15 tables. So we need to specify the table 41:17 name along with the column name. So 41:19 let's say we want to select 41:22 um 41:25 customers 41:27 dot first 41:29 name. Okay, we want to get the 41:32 customers 41:34 dot last name. And then we also want the 41:39 orders. We'll say orders dot 41:46 id and let's also get orders 41:49 dot order number. Okay. So the goal is 41:53 to get the customer info along with the 41:55 order 41:56 number. Okay. So we want to say 42:01 from 42:03 customers. All right. But we want to 42:05 join in the orders table. So we're going 42:08 to say inner 42:10 join 42:12 orders. Okay. Then we want to say on and 42:16 then this is where we want to match them 42:17 together. So we want 42:20 customers do ID to be equal to 42:24 orders dot customer ID. Okay, these 42:29 should match. And then let's just do 42:31 order by 42:35 uh let's see we'll order by 42:38 customers 42:39 dot last name. All right. So let's see 42:42 what that gives 42:44 us. All right. So let's look down here. 42:46 And you can see we have our first name, 42:49 last name. We also have the ID of the um 42:54 order and the order number. All right. 42:56 Actually, you know what? We probably 42:57 don't want the 42:59 ID. So, let's take that 43:04 out and let's run go. And now we're just 43:08 getting the actual order numbers. All 43:10 right. And you may want to order it by 43:13 the order 43:20 number. Okay. So, if we run that now, 43:23 you can see it's ordered by the order 43:25 number. 43:27 Now for a left join, that's going to 43:30 return rows from the left table with the 43:32 matching rows on the right table. So 43:35 let's say 43:40 select first 43:51 name. And we want orders dot order 43:58 number. All right. Actually, you know 43:59 what? Let's also get the date. So, 44:03 orders 44:05 dot order 44:07 date. All right. And then we're going to 44:09 say 44:12 from 44:13 customers and then we want to left 44:17 join 44:21 orders. Customers do ID should be equal 44:26 to orders 44:28 dot customer 44:32 id. Okay. And then let's just 44:37 do order 44:42 by last name. 44:45 Okay, let's go ahead and run 44:48 that. Okay, and now you can see same 44:51 thing. We're getting the first and last 44:53 name with the order number and we're 44:55 also getting the order date and 44:59 time. So now let's do a right join. All 45:02 right. 45:03 So, actually what we'll do is let's 45:10 specify orders 45:15 dot order number. We want customers. 45:29 And let's see this time we're going to 45:32 say from 45:34 orders. Okay. And then this will be a 45:37 right 45:38 join. We'll say right join 45:46 customers 45:49 on. I'm just going to switch 45:57 these. And then let's do order 46:00 by orders 46:04 dot order 46:11 number. Okay. So now you can see we have 46:14 our order numbers and then the first and 46:17 last name. And this is probably how you 46:18 would want to do it. Now another thing 46:21 you might want to do is bring in the 46:23 product. Okay, we might want the product 46:25 name. So that means that we have to join 46:27 in products and orders. So let's go back 46:30 to our 46:32 SQL. We want to 46:35 select orders dot order 46:39 number. We want customers 46:44 dot first 46:47 name customers.last 46:51 name 46:53 products.name. Okay. Okay, so that's 46:55 what we 46:56 want. Uh, and then we're going to say 47:00 from 47:02 orders. Okay, then we want 47:05 to 47:08 interjoin 47:12 products 47:15 on orders. 47:18 product 47:20 ID and products do 47:24 ID. Okay. Then we want to also 47:28 join 47:31 customers. Okay. For that we're going to 47:33 say on 47:35 orders dot customer 47:38 id equals 47:41 customers. 47:43 ID. All right. And then we're just going 47:45 to say order 47:51 by order 47:57 number. Orders dot order number. All 48:00 right. So let's go ahead and run 48:03 that. And there we go. So now our result 48:06 here is pulling the data from three 48:09 separate tables. Okay. We have the order 48:11 number which is coming from the orders 48:13 table. First name and last name is 48:16 coming from the customers table and name 48:18 is coming from the products 48:22 table. So that's how you can join three 48:24 tables. And of course you could do more 48:26 than three if you 48:29 wanted. All right. So now we're going to 48:31 take a quick look at aliases. Okay. 48:34 Aliases are used to give tables or 48:36 columns a temporary name and make them 48:39 more readable. All right, so the column 48:42 names for this table aren't too bad, but 48:44 we may want first and last name to be 48:46 formatted a little differently. All 48:48 right, so to do that, let's go to our 48:51 SQL and we're going to select, let's 48:55 say, first name. And what you want to do 48:58 is use the as keyword. So we'll say 49:00 first name as. Now, if you're going to 49:03 use two two or more separate words, then 49:05 you need to have quotes around them. So, 49:07 we want first space name. Okay? So, 49:11 that's that. And then we also want last 49:14 name 49:18 as last name. Okay? And then we'll say 49:21 from 49:23 customers. So, let's go ahead and run 49:26 that. 49:28 And now you can see that the column 49:29 headings here have changed to a more re 49:32 uh readable 49:33 format. Now another thing you may want 49:36 to do is you may want to combine 49:39 columns. All right? For instance, first 49:41 and last name. You may want that to be 49:43 returned in one column instead of two 49:45 separate ones. So what I'm going to do 49:47 here is I'm going to say 49:51 uh let's just get rid of all this. Now 49:53 this is where we need to use concat. 49:56 Okay? So we need to say 49:58 select concat and then in here we're 50:02 going to 50:04 specify first name and then comma then 50:08 we want a space in between them and then 50:12 last name. All right. Then we can do 50:18 as actually let's just say name as name. 50:22 And then we also want to grab the 50:25 address, 50:27 city, and 50:29 state 50:31 from customers. All right. So, let's run 50:36 that. Okay. So, now you can see we have 50:39 a name column and it has their first and 50:42 last name. All right. Now, we may want 50:44 to also join the address fields together 50:46 and put them in a single column called 50:48 address. So, let's go back up here and 50:53 we're going to 50:55 do 50:57 concat and then we'll wrap these 51:00 up. Okay. Between each one, let's 51:07 do 51:14 that. Okay, let's try that. 51:21 Oh, okay. So, the heading isn't correct. 51:25 Oh, I didn't do 51:28 as do 51:32 as 51:35 address. There we go. Okay. So, we've 51:38 turned it into just two columns, name 51:40 and 51:41 address. So, you can see this can be 51:44 very helpful. 51:46 Now we can also use this for tables. So 51:48 let me give you an example of 51:50 that. Okay. So let's do select 51:56 uh we'll select O. 52:00 ID and then let's do O 52:06 dot actually let's do order 52:09 date. Okay. So this will be from the 52:12 orders table. You'll see why I'm doing 52:15 this in a second. And then let's do C 52:18 dot first name C 52:22 dot last name. And then we're going to 52:25 say 52:26 from 52:28 customers as C and then also 52:34 orders as O. So let's go ahead and run 52:39 that. And there we go. we get the order 52:42 ID and the date and then the first and 52:45 last 52:47 name. Okay, so we can use aliases for 52:50 tables and 52:54 columns. All right, so now we're going 52:56 to look at aggregate functions in SQL. 52:59 Okay, so there's a few of these. The 53:02 first one I'm going to show you is 53:03 average or AVG. Okay, so for this we can 53:07 take uh a column and we can get the 53:09 average. So let's do it with the age in 53:13 the customers table actually. Do we 53:15 still have an 53:17 age? Yes, we do. Okay. So let's go to 53:20 SQL and we're going to say 53:24 select AVG and then we need the column 53:28 name. In this case it's going to be 53:30 age 53:31 from 53:33 customers. Okay. And if we run that, you 53:37 can see that the average age is 53:40 37. So that's average or AVG. Now let's 53:44 take a look at 53:45 count. So what we'll do here is we'll 53:48 say 53:50 select 53:52 count and let's say 53:56 age 53:59 from 54:02 customers. Okay. Okay, if we run that, 54:05 it gives us five. Okay, there's five 54:08 customers with an age in the customers 54:11 uh 54:12 table. Okay, we also have max. So if we 54:16 want to find the maximum 54:18 age, we can do that which is 54:21 64. And we also obviously can do min for 54:27 the minimum age which is 20. 54:32 Another one is sum. So if we want to get 54:34 the sum of all ages, we can do that, 54:38 which is 185. Not sure what um what that 54:43 could possibly do for us, but you can do 54:46 it. Now, another thing we could do is we 54:49 can use the group by statement. And what 54:52 that does is it's used in con 54:54 conjunction with an aggregate function 54:56 to group the result set by one or more 54:59 columns. So, what I'm going to do is 55:03 just very 55:04 quickly, I'm going to just uh make one 55:07 of these ages the same as another. So, 55:09 let's say 33. Uh we'll go ahead and 55:13 change 45 to 33. All right. And then 55:18 let's go to our SQL. And what we're 55:20 going to do is say 55:22 select 55:24 age and let's use 55:28 count age 55:31 age from 55:34 customers. And we'll say where 55:39 whoops where age is greater than 55:44 30. And then let's do group 55:49 by age. Okay. So if we run 55:53 that, what it's doing is it's finding 55:55 all the ages above 30 and then it's 55:58 giving us the count of each one. So 56:01 remember we have two with 33. So we get 56:03 two here. Okay. And one for 56:07 64. And we can also we could take out 56:10 that wear if we wanted 56:11 to. And that gives us everybody's all 56:14 the ages and the amount of records with 56:17 that age. Okay, so that's group 56:21 by. And another thing we could do is 56:23 let's say we only want the ages that 56:26 have 56:28 uh 56:29 two or more 56:32 uh results. Okay. So what we can do is 56:35 take the same thing but just add on to 56:38 it having. Okay. So we'll say having 56:42 uh a count that account for 56:47 age that is greater than or equal to 56:51 two. All right. So if we run that, you 56:55 can see we only get 33 because that's 56:57 the only age that's seen two or more 57:02 times. Okay. So back to some of the more 57:05 simple aggregate functions we have 57:08 ukase. So let's say 57:11 uh we'll say 57:13 select 57:15 ukase and let's pass in 57:18 here first 57:21 name 57:23 from customers. And you probably know 57:26 what this is going to 57:27 do. Okay, it's going to put all of the 57:30 names in uppercase. And of course, we 57:33 could select other things here as well. 57:36 Uh if we wanted to 57:37 say last 57:39 name. Okay, it's going to give us the 57:41 last name, too, but only the first name 57:44 is 57:45 uppercase. All right. Now, we also have 57:48 lowercase. So, we could do LC case. And 57:51 let's put this one as 57:58 UK. And there we go. So, this is L case. 58:01 This is UK case. All right. So, I think 58:04 that that's going to be it, guys. I 58:06 mean, that is that's the fundamentals of 58:10 SQL. Now, there are there's going to be 58:12 some stuff that I didn't cover, some 58:14 more advanced stuff, but um I really 58:17 wanted to keep this video under an hour 58:20 and basically just give you uh what you 58:23 need to be able to uh you know, 58:26 administer a database. 58:29 So, if you like this video, please leave 58:31 a like. Please subscribe if you're not 58:33 already subscribed. And uh thanks again 58:36 and I'll see you next time.
