1 00:00:01,410 --> 00:00:08,190 Let's try a few more complex queries, so right now we have this animal table that has three rows in 2 00:00:08,190 --> 00:00:13,620 it and each row consists of two columns, ID, an animal name, and I'm going to get rid of this entirely. 3 00:00:13,650 --> 00:00:14,400 I'm going to do it. 4 00:00:14,640 --> 00:00:16,500 First of all, I'll show you how you can empty a table. 5 00:00:16,860 --> 00:00:24,870 I can just say delete from animals and hit return and then or hit and run your query. 6 00:00:25,950 --> 00:00:28,340 I'm about to execute a delete statement without a where clause. 7 00:00:28,350 --> 00:00:30,110 Yes, that's exactly what I want to do. 8 00:00:30,120 --> 00:00:31,790 I want to delete everything in that table. 9 00:00:31,800 --> 00:00:37,320 So if we look at the animals table now, select staff from animals, it'll be empty. 10 00:00:39,510 --> 00:00:44,690 And it is now you don't have to do that if you just want to get rid of the table, I can just say drop 11 00:00:44,700 --> 00:00:49,530 table animals and that will delete the table. 12 00:00:50,070 --> 00:00:52,870 And if there's anything in it, obviously that's gone as well. 13 00:00:52,890 --> 00:00:53,890 So it is gone. 14 00:00:54,150 --> 00:00:59,340 So now if I look at my my tables and refresh this. 15 00:01:00,980 --> 00:01:02,330 Right, click on it, there's nothing there. 16 00:01:02,930 --> 00:01:07,190 All right, so let's create some new tables just to show some more complex queries. 17 00:01:07,490 --> 00:01:13,370 So I'm going to create a table called People and I'll do it the using the graphical user interface here 18 00:01:13,370 --> 00:01:17,390 by saying create a new table and I will call that table people. 19 00:01:18,140 --> 00:01:19,510 So where is it here? 20 00:01:20,690 --> 00:01:21,610 New table. 21 00:01:22,040 --> 00:01:22,720 This is it. 22 00:01:22,970 --> 00:01:31,940 And we'll go to properties and I'll call this people and I'll add a column of typed serial, which I'll 23 00:01:31,940 --> 00:01:32,750 call ID. 24 00:01:34,960 --> 00:01:46,210 And the type will be cereal, so cereal there is and cereal there and we'll give it a length of 11:00 25 00:01:46,210 --> 00:01:54,190 once again and we'll make it not know, OK, and then I'll add another column called First Name. 26 00:01:57,800 --> 00:02:03,680 Verkerk or VirTra, I'll make the link to 55 and I'll make it not null. 27 00:02:06,610 --> 00:02:09,430 And I'll create a third column called Last Lasting. 28 00:02:14,460 --> 00:02:21,030 And again, I'll at length 255, which is long enough for our purposes, and click, OK, and I'll save 29 00:02:21,030 --> 00:02:22,160 this table safe. 30 00:02:22,770 --> 00:02:27,400 So I now have a table and you notice when you go to save it, we could have typed it out like this. 31 00:02:27,600 --> 00:02:30,000 That's the sequel syntax for creating a table. 32 00:02:30,030 --> 00:02:33,500 But we're just using a graphical user interface to make it a little simpler for us. 33 00:02:34,170 --> 00:02:35,200 So we now have this table. 34 00:02:37,710 --> 00:02:43,440 So now I'm going to create another table by clicking on the tables over here, create new table. 35 00:02:43,440 --> 00:02:50,370 And I will call this emails and I'll create a column in this table called Idy. 36 00:02:50,610 --> 00:02:52,500 And once again, that'll be of type serial cereal. 37 00:02:52,500 --> 00:02:59,400 So let's find Serial right there, their length 11 not. 38 00:03:00,390 --> 00:03:05,700 OK, and I will create another column called People, Heidi. 39 00:03:06,570 --> 00:03:09,300 And this will also this won't be a type serial. 40 00:03:09,310 --> 00:03:10,650 This will be a type integer. 41 00:03:14,330 --> 00:03:23,510 So we'll choose a eight, I'll make it length 11 and not know, OK, and then I'll create another third 42 00:03:23,510 --> 00:03:29,270 field called email address and that will be of type. 43 00:03:30,170 --> 00:03:36,230 I'll make it length 255, which should be long enough for email addresses, make it not null and click. 44 00:03:36,230 --> 00:03:39,020 OK, so let's save this one now. 45 00:03:39,950 --> 00:03:41,720 So now I have. 46 00:03:42,950 --> 00:03:50,930 Two tables, if I refresh this by clicking on it and using refresh, you'll see that I have emails and 47 00:03:50,930 --> 00:03:54,290 I have people, so let's go put some content into these. 48 00:03:54,290 --> 00:03:57,680 So we'll do it using the command line, using Sindhi, using direct SQL. 49 00:03:57,690 --> 00:04:05,450 First, let's insert a person and I will say, well, first of all, let's have a look at the structure 50 00:04:05,450 --> 00:04:05,990 of people. 51 00:04:05,990 --> 00:04:08,180 So People has columns. 52 00:04:08,180 --> 00:04:10,010 It has its first name. 53 00:04:10,010 --> 00:04:10,440 Last name. 54 00:04:10,460 --> 00:04:18,140 OK, so I want to say insert into people and then I'll put the columns I want to populate, which will 55 00:04:18,140 --> 00:04:24,530 be just first name and last name values. 56 00:04:24,980 --> 00:04:27,980 And then because this is text, I have to put single quotes around it. 57 00:04:28,170 --> 00:04:33,110 I'll put you on for the first name and Smith for the last name and I'll run that. 58 00:04:34,640 --> 00:04:37,060 So now I have some data in people. 59 00:04:37,640 --> 00:04:43,580 So if I look at the data here, right here, there's John Smith back to our Shell script or to our school 60 00:04:43,580 --> 00:04:44,030 editor. 61 00:04:44,330 --> 00:04:51,920 And let's do a second one and we'll call this one Mary Jones, and we'll save that or run that. 62 00:04:53,510 --> 00:04:57,650 And back under here we have our people. 63 00:04:58,010 --> 00:04:59,720 We should have them and refresh this. 64 00:05:00,080 --> 00:05:04,400 Undo when I refresh this. 65 00:05:09,820 --> 00:05:10,750 I have Mary Jones. 66 00:05:10,780 --> 00:05:17,350 OK, so now I have two people in my people table in our emails table, we currently have nothing. 67 00:05:17,350 --> 00:05:18,940 If we look at data, there's nothing in there. 68 00:05:18,950 --> 00:05:20,220 So let's go insert some data. 69 00:05:20,770 --> 00:05:25,240 Now, I have two people in my people table. 70 00:05:25,720 --> 00:05:28,990 We have John with an idea of one and Mary with an idea of two. 71 00:05:29,260 --> 00:05:35,200 I want to give John an email and I'm not going to put it in the people table because he might have more 72 00:05:35,200 --> 00:05:36,480 than one email address. 73 00:05:36,520 --> 00:05:39,460 So let's insert into emails. 74 00:05:39,790 --> 00:05:46,030 And now I want to put in people are dying and I want to put email address values. 75 00:05:46,240 --> 00:05:50,080 And because John is 81, I just put 81 and free email. 76 00:05:50,080 --> 00:05:56,470 I put John Smith, Daudzai and I will put a semicolon at the end and run that comment. 77 00:05:57,880 --> 00:06:04,290 Now, I want to give John a second email address, Gmail dot com, which obviously is not a real email. 78 00:06:04,480 --> 00:06:06,250 Somebody probably has that, but it doesn't matter. 79 00:06:06,250 --> 00:06:09,370 We're just using this for testing purposes now. 80 00:06:09,370 --> 00:06:12,210 He has to email address and let's give Mary one as well. 81 00:06:12,220 --> 00:06:21,090 So Mary is 82 and the ID for people ID matches the ID column in people, so we'll give her email address 82 00:06:21,100 --> 00:06:25,470 Mary at Jones dot com and run that. 83 00:06:27,580 --> 00:06:32,620 Now let's go look at our emails and refresh this. 84 00:06:34,100 --> 00:06:41,180 And there is so John has two male email addresses, and that's linked to him via this I.D., the ID 85 00:06:41,180 --> 00:06:47,990 and the People People I.D. column in the emails table matches the ID column in the people's table in 86 00:06:47,990 --> 00:06:49,910 the same way 82 is for Mary. 87 00:06:49,940 --> 00:06:57,970 And her ID here in the Lady column is two and that matches idea of two in the people's table. 88 00:06:57,980 --> 00:06:59,300 So let's see what we can do with that. 89 00:06:59,330 --> 00:07:06,020 Now what I want to do is I want to write a query that says, give me all of John's email addresses and 90 00:07:06,020 --> 00:07:06,650 that is enough. 91 00:07:06,980 --> 00:07:20,300 I can say select email address from emails where people ID equals one and that is John's ID. 92 00:07:20,300 --> 00:07:24,350 So when I run this, I should just get his two email addresses and they're there. 93 00:07:24,830 --> 00:07:30,140 And if I want to get Mary's email addresses, I can say I'd love to because that's her people. 94 00:07:30,440 --> 00:07:33,640 And if I run that, I should just get her email addresses. 95 00:07:33,830 --> 00:07:40,760 Well, what if I want to get John's first name, his last name and only one of his email addresses? 96 00:07:40,850 --> 00:07:41,710 I can do that, too. 97 00:07:41,930 --> 00:07:43,070 I can say select. 98 00:07:44,270 --> 00:07:47,210 And what I want to select is from the the people's table. 99 00:07:47,210 --> 00:07:50,950 So I'm going to use the prefix of P, which will be defining in a minute. 100 00:07:51,050 --> 00:08:03,650 Peter, first name, P Dot, last name, either email address from people P and that's where the P comes 101 00:08:03,650 --> 00:08:04,010 from. 102 00:08:04,400 --> 00:08:08,150 This is the table I want to select from and actually select for more than one table. 103 00:08:08,660 --> 00:08:12,820 But the people table has the prefix or the shorthand or the reference of P. 104 00:08:12,860 --> 00:08:16,190 That's why the P is here in the P is here and now. 105 00:08:16,190 --> 00:08:25,700 I need to join another table left, join emails E on how am I joining those tables. 106 00:08:25,910 --> 00:08:29,330 I want to join them on either email or people. 107 00:08:29,330 --> 00:08:36,520 ID is equal to the are p dot ID. 108 00:08:37,070 --> 00:08:39,350 Let's see what happens when we do this run. 109 00:08:41,830 --> 00:08:50,200 And what it says is John Smith has an email at John Smith, John Smith, John Smith also has an email 110 00:08:50,200 --> 00:08:53,700 of John at Gmail dot com, but Mary only gets one entry. 111 00:08:53,710 --> 00:09:00,490 And that's because when I join the people's table with the emails table, there is only one matching 112 00:09:00,490 --> 00:09:01,360 entry in emails. 113 00:09:01,360 --> 00:09:03,330 So she only gets it once. 114 00:09:03,730 --> 00:09:08,620 What if I want to limit this and just get the emails for John and I want his first name and last name 115 00:09:08,620 --> 00:09:09,250 at the same time. 116 00:09:09,490 --> 00:09:11,530 I can just anywhere close where? 117 00:09:13,930 --> 00:09:23,350 P ID is equal to one and run that, and now I just get on if I changes where to where p ID equals two, 118 00:09:23,950 --> 00:09:24,670 I can run that. 119 00:09:26,470 --> 00:09:27,790 I can even do it this way. 120 00:09:27,790 --> 00:09:28,420 Where. 121 00:09:33,030 --> 00:09:47,160 Peter, first name is equal to John and Peter last name is equal to Smith, and if I run that, there 122 00:09:47,160 --> 00:09:47,530 we are. 123 00:09:48,360 --> 00:09:50,220 So there is how you join tables now. 124 00:09:50,220 --> 00:09:51,960 We could do this with anything we wanted to. 125 00:09:51,960 --> 00:09:53,120 We could have another table. 126 00:09:53,130 --> 00:09:53,990 Let's create one. 127 00:09:54,000 --> 00:10:02,730 Let's go to tables, create a new table, and we'll call this one under properties, phone numbers for 128 00:10:02,730 --> 00:10:03,330 phones. 129 00:10:04,530 --> 00:10:06,810 And it's going to have a column create a new column. 130 00:10:06,960 --> 00:10:08,570 We'll call that column ID. 131 00:10:09,270 --> 00:10:10,890 The data type will be serial. 132 00:10:15,200 --> 00:10:22,640 The length, again, will be 11 and not know, which means you can't try to insert something into the 133 00:10:22,640 --> 00:10:28,220 table without populating that field and here it's being populated for us because it's a serial type 134 00:10:28,880 --> 00:10:30,190 and that will create a new column. 135 00:10:30,200 --> 00:10:33,110 Once again, we'll call it people ID. 136 00:10:34,160 --> 00:10:36,350 The type will be int eight. 137 00:10:36,920 --> 00:10:38,090 So let's find it eight. 138 00:10:41,190 --> 00:10:45,570 Indicate the length will be 11 and it will be not known. 139 00:10:46,650 --> 00:10:47,110 OK. 140 00:10:48,090 --> 00:10:51,420 And we actually want to create one more column and that will be phone number. 141 00:10:54,150 --> 00:10:55,380 And that'll just be a marker. 142 00:10:55,530 --> 00:10:56,080 Not no. 143 00:10:57,720 --> 00:11:02,790 OK, save this table and previews it for us. 144 00:11:02,820 --> 00:11:03,970 That's exactly what I want. 145 00:11:03,990 --> 00:11:08,040 So let's save that and go back to our school window. 146 00:11:08,130 --> 00:11:10,110 And this time we'll insert some phone numbers. 147 00:11:10,140 --> 00:11:12,590 So insert into phones. 148 00:11:13,200 --> 00:11:18,990 And the only popular columns I want to populate are people, ID and phone number. 149 00:11:21,240 --> 00:11:28,440 And the values will be people ID for John, his phone number is going to be 555, dash five five five, 150 00:11:28,440 --> 00:11:30,200 dash one, two, three, four. 151 00:11:31,530 --> 00:11:34,620 And we'll save that or run that. 152 00:11:36,300 --> 00:11:42,510 And then we'll do the same thing for Mary, but hers will be four, three, two, one and run that. 153 00:11:43,890 --> 00:11:49,690 And now when I refresh my tables, we should have phones. 154 00:11:49,690 --> 00:11:54,690 So let's look at phones and let's look at the data and they're there back to our school script. 155 00:11:54,690 --> 00:11:55,800 Let's modify this. 156 00:11:56,310 --> 00:11:58,350 So we're going to make this a little easier to read. 157 00:11:58,380 --> 00:12:02,400 I'll make it select these things and then I'll put Frum on. 158 00:12:02,400 --> 00:12:07,710 It's online and I want to select from people and I'm on a left. 159 00:12:07,710 --> 00:12:13,800 Join emails and I'll put we're way down here and we'll do another joint left. 160 00:12:13,890 --> 00:12:26,600 Join Fontes and we'll call it Pitou Osgoode on Peter ID for people ID is equal to Pitou Daudt people. 161 00:12:26,610 --> 00:12:32,570 Eddie and I will modify this to include the phone number, which will be Pitou phone number. 162 00:12:33,780 --> 00:12:36,000 So let's make sure we got the syntax right. 163 00:12:36,300 --> 00:12:42,300 We have Peeter first name for the people table paedo, last name for the people table Iida, email address 164 00:12:42,300 --> 00:12:45,840 for the emails table and paedo Pitou phone number. 165 00:12:45,840 --> 00:12:49,110 And you notice there's no comma after the last part of my select. 166 00:12:49,110 --> 00:12:49,860 That's important. 167 00:12:50,310 --> 00:12:51,870 We're selecting it from people. 168 00:12:52,080 --> 00:12:53,940 Good for making those two. 169 00:12:53,940 --> 00:12:54,920 Joynes good. 170 00:12:54,930 --> 00:12:56,400 Now let's fix up our workless. 171 00:12:56,400 --> 00:13:04,560 Let's get it just for John to start with where John are, where people dot ideas equals one that you 172 00:13:04,560 --> 00:13:05,720 get me to read. 173 00:13:08,420 --> 00:13:14,450 And it does John Smith with his two phone numbers, and if I change this to two to get married, we 174 00:13:14,450 --> 00:13:18,910 should get one row and there it is, Mary Jones. 175 00:13:19,220 --> 00:13:21,320 So that's how we can do more complex queries. 176 00:13:21,650 --> 00:13:22,850 We can even do it this way. 177 00:13:22,850 --> 00:13:28,910 We can again leave the where clause off entirely and run this. 178 00:13:30,670 --> 00:13:35,510 And we get John we get to John's in America, but what if I want these in a particular order? 179 00:13:35,830 --> 00:13:39,810 Well, after the where clause, if we have one, I'm going to leave it up right now. 180 00:13:40,210 --> 00:13:43,780 We can also have an order clause ordered by and what do you want to order. 181 00:13:43,790 --> 00:13:49,750 But I want to order by from the people table last name and run that. 182 00:13:50,230 --> 00:13:52,000 And now we'll sort on the last name. 183 00:13:52,390 --> 00:13:54,430 And therefore, Mary Jones comes first. 184 00:13:54,430 --> 00:13:54,940 Perfect. 185 00:13:55,450 --> 00:13:58,930 So that is how we can do a slightly more complex queries. 186 00:13:59,140 --> 00:14:04,680 Now, the best way to get used to writing SQL queries is to actually write them to do something useful. 187 00:14:05,080 --> 00:14:07,960 So we're going through an overview of the syntax right now. 188 00:14:08,080 --> 00:14:13,480 But when we start actually writing the application, the business logic for our bookings application, 189 00:14:13,840 --> 00:14:18,850 we're going to be writing a lot of queries and those queries will become increasingly complex as time 190 00:14:18,850 --> 00:14:19,360 goes on. 191 00:14:20,080 --> 00:14:24,530 But the basic syntax behind it all is exactly what we're covering right here. 192 00:14:24,550 --> 00:14:28,960 There are other other kinds of Joynes we can do an inner join an outer, join the left, join a right, 193 00:14:28,960 --> 00:14:30,130 join a natural join. 194 00:14:30,400 --> 00:14:31,540 But don't worry about those. 195 00:14:31,720 --> 00:14:37,390 As they come up, we'll go through them and learn the syntax and see how all of this works. 196 00:14:37,660 --> 00:14:38,110 All right. 197 00:14:38,140 --> 00:14:39,370 That's enough for this time around. 198 00:14:39,760 --> 00:14:43,570 It's just about time to try to connect our code to the database.