1 00:00:00,570 --> 00:00:01,620 Hello, my name is Typhoon. 2 00:00:01,620 --> 00:00:08,040 And in this lecture of our course, we will add rows into a table and we will use also insert statement 3 00:00:08,040 --> 00:00:09,620 and other statements as well. 4 00:00:09,630 --> 00:00:14,760 So to insert some data into the table, you first need to urge the create table statements. 5 00:00:14,940 --> 00:00:16,710 Just run in previous lectures. 6 00:00:16,710 --> 00:00:19,470 So firstly we will start our. 7 00:00:20,580 --> 00:00:23,330 Sqlplus or Pgadmin. 8 00:00:23,360 --> 00:00:23,930 Of course. 9 00:00:23,960 --> 00:00:28,720 Pgadmin for this is the latest version of Pgadmin. 10 00:00:28,730 --> 00:00:31,370 And here you will see. 11 00:00:32,580 --> 00:00:35,070 Our database that we created in previous lecture. 12 00:00:39,760 --> 00:00:43,390 And here we're going to enter our master password. 13 00:00:43,390 --> 00:00:46,690 In this case, my password is one, two, three and click on. 14 00:00:46,690 --> 00:00:47,110 Okay. 15 00:00:47,110 --> 00:00:49,210 And we are seeing our database is in it. 16 00:00:49,870 --> 00:00:50,770 So here. 17 00:00:53,900 --> 00:00:54,680 Password. 18 00:00:54,920 --> 00:00:59,300 And here we have we have com and servers and. 19 00:01:01,400 --> 00:01:02,060 Databases. 20 00:01:02,600 --> 00:01:03,890 Database as you can. 21 00:01:07,970 --> 00:01:09,220 As catalogues. 22 00:01:10,590 --> 00:01:10,950 Faces. 23 00:01:10,950 --> 00:01:13,560 And here we create some Postgres database. 24 00:01:17,320 --> 00:01:18,760 And you will learn all of this. 25 00:01:19,360 --> 00:01:20,260 What do you mean? 26 00:01:24,780 --> 00:01:25,350 Database. 27 00:01:25,950 --> 00:01:26,340 This is. 28 00:01:28,190 --> 00:01:36,200 Bruce lecture and here publications schemes and tied this we have tables and here as you can see we 29 00:01:36,200 --> 00:01:36,650 have. 30 00:01:37,830 --> 00:01:38,610 Cables here. 31 00:01:38,730 --> 00:01:41,160 So now. 32 00:01:42,510 --> 00:01:42,960 Um. 33 00:01:43,500 --> 00:01:44,100 We will. 34 00:01:45,010 --> 00:01:48,130 Following the same steps you did in previous lectures. 35 00:01:48,130 --> 00:01:52,900 You did create a database and tables and here. 36 00:01:52,900 --> 00:02:03,910 So I'm going to first fix that because I see that our contrasts and saturation of the video is much 37 00:02:03,910 --> 00:02:05,980 high and I will make it lower. 38 00:02:06,340 --> 00:02:07,390 So I think it's. 39 00:02:09,050 --> 00:02:09,710 Yes. 40 00:02:09,860 --> 00:02:10,640 So. 41 00:02:11,610 --> 00:02:12,360 In previous lectures. 42 00:02:12,360 --> 00:02:16,320 As I said, you did create the database and table here. 43 00:02:17,160 --> 00:02:21,390 So with into your Pgadmin query tool, as you can see here. 44 00:02:21,420 --> 00:02:23,670 So now. 45 00:02:24,520 --> 00:02:26,500 We will add some values in it, right? 46 00:02:26,620 --> 00:02:30,880 Firstly, we're going to go to Pgadmin query tool here. 47 00:02:31,590 --> 00:02:33,270 And here we go from. 48 00:02:34,600 --> 00:02:38,010 And truncate we have we have several options here. 49 00:02:38,530 --> 00:02:42,280 And here you go to tools and here query tool. 50 00:02:44,290 --> 00:02:47,800 And make sure you are using on Oxley Postgres that we just created. 51 00:02:49,860 --> 00:02:51,300 On in Oxley. 52 00:02:53,610 --> 00:02:56,700 If you click on this Postgres and click on Tools. 53 00:02:57,980 --> 00:02:58,630 For it all. 54 00:02:58,640 --> 00:03:01,700 As you can see here, we are seeing different here, right? 55 00:03:01,730 --> 00:03:02,450 Postgres. 56 00:03:03,020 --> 00:03:04,930 These are two different databases. 57 00:03:04,940 --> 00:03:10,990 Anything you write here will affect anything you write here will affect Postgres. 58 00:03:11,000 --> 00:03:12,410 So now. 59 00:03:13,380 --> 00:03:16,140 We will write our code insert. 60 00:03:16,410 --> 00:03:18,760 Insert into teachers. 61 00:03:18,790 --> 00:03:19,770 Teachers. 62 00:03:19,980 --> 00:03:23,340 And we will add the first name. 63 00:03:23,940 --> 00:03:24,960 As you can see here, we. 64 00:03:25,440 --> 00:03:27,240 Because we have. 65 00:03:28,890 --> 00:03:33,620 Because alarms, as you can see, we have several options here that we created. 66 00:03:34,980 --> 00:03:43,770 And here we have first name and we will also inside this here, we will also add the first name last. 67 00:03:45,360 --> 00:03:45,990 Name. 68 00:03:47,440 --> 00:03:47,710 Cool. 69 00:03:52,560 --> 00:03:53,540 Air date. 70 00:03:55,380 --> 00:03:57,420 Rate and salary. 71 00:03:59,310 --> 00:04:01,370 Audit and salary. 72 00:04:01,770 --> 00:04:04,650 After that, we will add some information in it. 73 00:04:04,800 --> 00:04:12,420 So in this case, we will add into and we will syntax here add into values. 74 00:04:13,250 --> 00:04:14,540 Alias here. 75 00:04:14,840 --> 00:04:22,040 Now, the first name in the phone here and the phone first name. 76 00:04:22,040 --> 00:04:25,280 Let's be a myth here. 77 00:04:25,950 --> 00:04:26,790 And. 78 00:04:27,460 --> 00:04:27,940 The. 79 00:04:28,300 --> 00:04:28,950 Yes. 80 00:04:28,960 --> 00:04:31,270 First we have first name, last name school. 81 00:04:31,270 --> 00:04:36,220 Here we have the alias date. 82 00:04:38,000 --> 00:04:39,140 University. 83 00:04:40,890 --> 00:04:45,900 We have the after school, we have hire date and salary. 84 00:04:46,050 --> 00:04:46,620 Right. 85 00:04:46,970 --> 00:04:54,210 In a statement versus hire date, let's make it 2015 and here hire that. 86 00:04:54,450 --> 00:05:00,960 So we will have to right hire that the dates in SQL in format here. 87 00:05:00,960 --> 00:05:02,850 So firstly year. 88 00:05:04,390 --> 00:05:09,790 2015, and after that we will enter the month. 89 00:05:09,940 --> 00:05:16,840 So it's going to zero nine and after that we will light the day in this case, 25. 90 00:05:17,800 --> 00:05:24,980 After that we have after that we will also have salary and in this case we will add salary for. 91 00:05:25,390 --> 00:05:30,340 We can also make it measure is yearly, monthly, weekly, whatever you want. 92 00:05:30,370 --> 00:05:33,270 In this case, we will make it monthly. 93 00:05:33,280 --> 00:05:37,030 We will see it monthly, but we will just add some value in it. 94 00:05:37,030 --> 00:05:37,570 Right? 95 00:05:38,500 --> 00:05:42,370 So SQL doesn't know if it's monthly or weekly, right. 96 00:05:42,370 --> 00:05:43,530 Or yearly. 97 00:05:43,540 --> 00:05:44,230 So. 98 00:05:45,260 --> 00:05:47,510 Here we had 5000. 99 00:05:47,750 --> 00:05:51,050 And after that, let's add another with comma. 100 00:05:51,080 --> 00:05:51,890 Let's add comma. 101 00:05:51,890 --> 00:05:58,260 And after that we will add a new here, parenthesis here, and we will add sample. 102 00:05:59,840 --> 00:06:01,520 Let's make. 103 00:06:03,130 --> 00:06:03,580 Gone. 104 00:06:05,230 --> 00:06:06,430 And here. 105 00:06:07,160 --> 00:06:07,730 After John. 106 00:06:07,760 --> 00:06:11,510 The short name John Smith. 107 00:06:13,260 --> 00:06:14,640 And here? 108 00:06:14,640 --> 00:06:15,480 Cambridge. 109 00:06:20,120 --> 00:06:21,390 Cambridge State University? 110 00:06:21,400 --> 00:06:21,880 I don't know. 111 00:06:23,020 --> 00:06:27,690 University is correct, but it's just a drink here that we are using. 112 00:06:27,700 --> 00:06:28,960 So now. 113 00:06:30,510 --> 00:06:31,110 First name. 114 00:06:31,110 --> 00:06:32,400 Last name here. 115 00:06:33,200 --> 00:06:33,650 First name? 116 00:06:33,650 --> 00:06:33,980 Don. 117 00:06:33,980 --> 00:06:35,000 Last name Don. 118 00:06:35,000 --> 00:06:36,560 We have school, Don. 119 00:06:36,560 --> 00:06:44,660 And we also we need to just add hire date and salary so we can also use some random name generator, 120 00:06:44,660 --> 00:06:45,200 right? 121 00:06:45,200 --> 00:06:50,960 So here we will add random name generator. 122 00:06:51,930 --> 00:06:54,960 Writer random name generator. 123 00:06:55,140 --> 00:06:56,580 Also known name. 124 00:06:57,300 --> 00:06:59,220 Surname generator. 125 00:06:59,220 --> 00:07:00,030 And. 126 00:07:01,440 --> 00:07:02,430 The name generator. 127 00:07:04,060 --> 00:07:04,540 Here. 128 00:07:04,900 --> 00:07:07,540 So we have this year, right? 129 00:07:08,660 --> 00:07:09,980 Bill will be president. 130 00:07:11,440 --> 00:07:13,390 Fake identity. 131 00:07:14,600 --> 00:07:15,500 Generator. 132 00:07:20,360 --> 00:07:22,930 You know the vision here. 133 00:07:22,930 --> 00:07:25,050 As you can see here, we will regenerate. 134 00:07:25,060 --> 00:07:29,820 So now the run, the message has to run the bases. 135 00:07:29,830 --> 00:07:36,130 So let's first complete the John Smith and here we will add doesn't hire. 136 00:07:36,190 --> 00:07:37,990 It is going to be 2002. 137 00:07:39,650 --> 00:07:40,670 Uh, here. 138 00:07:42,870 --> 00:07:43,410 12. 139 00:07:44,360 --> 00:07:46,430 And salary is going to be. 140 00:07:47,870 --> 00:07:48,980 It's a 9000. 141 00:07:50,140 --> 00:07:51,810 And after that we will add here. 142 00:07:51,820 --> 00:07:53,710 So during the missions. 143 00:07:53,740 --> 00:07:55,360 During the missions. 144 00:07:58,650 --> 00:07:59,400 Green. 145 00:08:00,960 --> 00:08:03,450 Here to the US. 146 00:08:04,320 --> 00:08:12,840 And here, 27 years old, we have Guzman online finance and physical characteristics, tracking numbers. 147 00:08:12,840 --> 00:08:16,170 As you can see here, this is actually pretty nice, right? 148 00:08:16,410 --> 00:08:20,490 Just created a fake identity for us here. 149 00:08:20,490 --> 00:08:21,180 We will. 150 00:08:21,330 --> 00:08:22,440 And we will also. 151 00:08:22,440 --> 00:08:26,550 I think it made us the university here. 152 00:08:26,550 --> 00:08:28,020 Let's go to advanced options. 153 00:08:28,020 --> 00:08:30,840 So make sure you make it. 154 00:08:31,690 --> 00:08:33,370 Employment finance. 155 00:08:35,440 --> 00:08:36,340 Email. 156 00:08:37,050 --> 00:08:41,160 Birthday on mother's maiden name and so on. 157 00:08:42,160 --> 00:08:42,880 And. 158 00:08:44,390 --> 00:08:45,740 Physical characteristics. 159 00:08:45,740 --> 00:08:46,220 Employment. 160 00:08:46,220 --> 00:08:46,910 So. 161 00:08:49,040 --> 00:08:51,750 Yes it didn't gives gave us the. 162 00:08:53,020 --> 00:08:54,310 They were here. 163 00:08:54,490 --> 00:08:56,830 The Messiahs and here. 164 00:08:57,430 --> 00:08:59,800 After last name, we will add school. 165 00:09:00,780 --> 00:09:01,620 Oxford. 166 00:09:03,650 --> 00:09:05,630 Oxford and. 167 00:09:06,850 --> 00:09:08,020 University. 168 00:09:08,050 --> 00:09:09,460 University. 169 00:09:11,570 --> 00:09:13,460 And after that, we'll add date. 170 00:09:15,180 --> 00:09:15,750 It doesn't. 171 00:09:15,750 --> 00:09:16,440 12. 172 00:09:17,990 --> 00:09:18,500 To. 173 00:09:21,150 --> 00:09:23,910 Four zero, five and ten. 174 00:09:24,880 --> 00:09:28,330 And after that we will add the salary. 175 00:09:28,360 --> 00:09:30,340 Salary is 15,000. 176 00:09:32,340 --> 00:09:33,270 Come again. 177 00:09:33,270 --> 00:09:40,410 And we we will also need to add like, let's add two more person and that's okay for us enough. 178 00:09:40,410 --> 00:09:42,210 And here we will add. 179 00:09:45,060 --> 00:09:47,310 Ahmet and. 180 00:09:48,780 --> 00:09:49,310 Ahmet. 181 00:09:50,540 --> 00:09:55,610 Till this year and after we have Istanbul. 182 00:09:55,790 --> 00:09:57,350 Istanbul. 183 00:09:58,320 --> 00:10:01,830 University or Istanbul Technical University. 184 00:10:02,730 --> 00:10:05,220 Technical University. 185 00:10:07,110 --> 00:10:10,380 And after that we will add the higher rate. 186 00:10:11,340 --> 00:10:14,090 Ah, this is going to be 1820. 187 00:10:16,720 --> 00:10:17,320 Then. 188 00:10:22,420 --> 00:10:22,780 Of. 189 00:10:22,780 --> 00:10:24,700 And after that we will add salary. 190 00:10:29,800 --> 00:10:32,140 Or let's make it 9000. 191 00:10:33,030 --> 00:10:33,420 That's it. 192 00:10:34,630 --> 00:10:39,910 And after that we'll it the for entity for variable. 193 00:10:40,120 --> 00:10:42,160 Okay so now. 194 00:10:43,270 --> 00:10:51,070 Add this code block inserts names and data for one, two, three, four for teachers. 195 00:10:51,070 --> 00:10:51,650 Right. 196 00:10:51,670 --> 00:10:57,910 So here the post cure PostgreSQL syntax follows the ANSI SQL standard. 197 00:10:57,910 --> 00:11:02,240 So after insert into let's actually let me get my pen. 198 00:11:02,260 --> 00:11:04,990 So after insert into. 199 00:11:05,140 --> 00:11:05,710 Right. 200 00:11:06,860 --> 00:11:07,280 Um. 201 00:11:07,280 --> 00:11:13,700 This after inserting the keywords is the name of the table which in this case teachers. 202 00:11:13,700 --> 00:11:15,230 This is the name of the table. 203 00:11:17,960 --> 00:11:25,640 So and the parentheses here, the parentheses are the columns to be filled. 204 00:11:25,680 --> 00:11:26,230 Right. 205 00:11:26,240 --> 00:11:26,840 First name. 206 00:11:27,080 --> 00:11:27,920 Last name. 207 00:11:27,920 --> 00:11:28,940 School hire. 208 00:11:28,940 --> 00:11:30,020 Date salary. 209 00:11:30,640 --> 00:11:31,420 And. 210 00:11:32,380 --> 00:11:35,470 In the next row are the values. 211 00:11:36,220 --> 00:11:42,220 This is values, keyword and the data to insert each column in each row here. 212 00:11:42,220 --> 00:11:42,610 So. 213 00:11:42,610 --> 00:11:44,530 One, two, three. 214 00:11:46,640 --> 00:11:47,810 Or that's it. 215 00:11:48,690 --> 00:11:54,090 So you need to enclose the data for each row in a set of parentheses, as we did here. 216 00:11:54,390 --> 00:12:02,310 And inside each set of parentheses, use a comma to separate each value. 217 00:12:02,340 --> 00:12:03,180 Like this. 218 00:12:04,610 --> 00:12:10,430 And the order of the values must also match the order of the columns specified after the table name. 219 00:12:10,430 --> 00:12:15,260 So each row of the data ends with a comma, except the last row. 220 00:12:15,290 --> 00:12:22,550 As you can see here, which ends the entire statement with the semicolon and ends with the semicolon 221 00:12:22,550 --> 00:12:23,030 here. 222 00:12:23,240 --> 00:12:29,690 So notice that the certain values that we are inserting are enclosed in a single quotes, but some are 223 00:12:29,690 --> 00:12:30,230 not. 224 00:12:31,200 --> 00:12:32,790 Right, as you can see here. 225 00:12:33,000 --> 00:12:33,930 So. 226 00:12:35,120 --> 00:12:36,440 Yeah, that's because. 227 00:12:37,310 --> 00:12:38,040 That that. 228 00:12:38,440 --> 00:12:42,640 So some are not, but this is the standard requirement, right? 229 00:12:42,640 --> 00:12:46,570 So text and datas require ports. 230 00:12:48,870 --> 00:12:54,300 So numbers, including integers and decimals, don't require codes. 231 00:12:54,300 --> 00:12:59,730 As you can see, we didn't write the codes here, so I will highlight this requirement as it comes up 232 00:12:59,730 --> 00:13:00,780 in examples. 233 00:13:00,780 --> 00:13:09,900 So also note that the date format we are using a four digit year is followed by the month and date and 234 00:13:09,900 --> 00:13:11,970 each part is joined by a hyphen. 235 00:13:11,970 --> 00:13:14,580 So this is the international standard for data formats. 236 00:13:14,640 --> 00:13:18,000 Now using it will help you avoid confusion. 237 00:13:18,000 --> 00:13:18,260 Right? 238 00:13:18,270 --> 00:13:23,430 Why is the best to use format year, month and then day. 239 00:13:24,030 --> 00:13:33,330 So here we will we can check the this website to I think this was a 11 nine. 240 00:13:33,510 --> 00:13:34,110 Yeah. 241 00:13:34,110 --> 00:13:40,680 So there's a here there's a public service announcement joke about the comic about this. 242 00:13:40,680 --> 00:13:47,040 So public service announcement are different ways of writing days as a numbers can lead to online confusion. 243 00:13:47,040 --> 00:13:53,230 That's why in nine 1988 ISO set global standard numeric date format. 244 00:13:53,230 --> 00:14:02,290 So this is the correct way to write numeric dates and the following formats are therefore discouraged. 245 00:14:02,290 --> 00:14:02,860 Here. 246 00:14:02,860 --> 00:14:03,220 Here. 247 00:14:03,220 --> 00:14:06,550 This is the normal dates, normal dates with words and so on. 248 00:14:06,550 --> 00:14:09,190 And here we have cats cat here. 249 00:14:09,640 --> 00:14:14,740 So this was a comic about that made in early days of internet. 250 00:14:16,930 --> 00:14:21,010 Here, close this and you might be wondering about the ID column here. 251 00:14:21,640 --> 00:14:22,110 You see? 252 00:14:22,140 --> 00:14:25,270 See ID, ID column, which is the first column in the table. 253 00:14:25,270 --> 00:14:28,900 So when you created the table, your script specified that the. 254 00:14:29,970 --> 00:14:31,380 Bigger serial data types. 255 00:14:31,380 --> 00:14:39,530 So as the PostgreSQL inserts row, it automatically fills the ID column with auto incrementing integer, 256 00:14:39,540 --> 00:14:40,260 remember? 257 00:14:41,360 --> 00:14:42,950 Arthur Incrementing Integer. 258 00:14:43,070 --> 00:14:52,030 So here we have ID properties, definition, distinct constraints, variables, security, SQL updates 259 00:14:52,070 --> 00:14:54,980 for now, but it will update now. 260 00:14:54,980 --> 00:15:01,940 So now we will execute that command with F5 or clicking on this execute refresh button. 261 00:15:01,940 --> 00:15:04,580 And as you can see, religion teachers does not exist. 262 00:15:07,040 --> 00:15:07,910 Features. 263 00:15:09,610 --> 00:15:11,980 Because we write down PostgreSQL. 264 00:15:11,980 --> 00:15:12,670 Let's copy this. 265 00:15:13,000 --> 00:15:17,020 I always say that make sure you're writing in a correct database. 266 00:15:17,030 --> 00:15:22,630 So here we will run this and as you can see here, Query returned successfully. 267 00:15:22,630 --> 00:15:24,520 And here we will. 268 00:15:25,450 --> 00:15:28,480 Uh, can we here so you can take a. 269 00:15:29,570 --> 00:15:32,210 Also the last of the two numbers I want to. 270 00:15:33,280 --> 00:15:33,770 Here. 271 00:15:33,790 --> 00:15:42,550 So the last of the two numbers are the insert keyword reports to how many rows are affected inserted. 272 00:15:42,550 --> 00:15:45,790 And in this case, as you can see here, it's 404. 273 00:15:45,940 --> 00:15:53,020 So the first number you might you might be wondering what this first number is, is an unused legacy 274 00:15:53,020 --> 00:15:54,310 PostgreSQL. 275 00:15:54,310 --> 00:16:00,040 So this is the value that is returned only to maintain wire protocol so you can safely ignore it and 276 00:16:00,040 --> 00:16:06,580 you can make attention to second number in this case four, which means the inserted four rows. 277 00:16:07,060 --> 00:16:12,700 You can also take a quick look at the data you just loaded into the features table using the pgadmin 278 00:16:12,700 --> 00:16:13,210 also. 279 00:16:13,210 --> 00:16:18,610 So in the object browser, locate the table and right click. 280 00:16:19,370 --> 00:16:29,090 So in the pop up menu here, you will see the view edit data and after that you will select the all 281 00:16:29,090 --> 00:16:29,960 rows. 282 00:16:32,010 --> 00:16:32,540 Here. 283 00:16:32,670 --> 00:16:33,960 Now let's come to here. 284 00:16:33,960 --> 00:16:36,180 Teachers inside, no tables and teachers. 285 00:16:36,180 --> 00:16:41,610 And here we will click on right, right click on the table name. 286 00:16:41,610 --> 00:16:47,190 And after that we will use the view edit data, all roles. 287 00:16:52,130 --> 00:16:52,400 Here. 288 00:16:52,400 --> 00:16:54,530 As you can see here, this is our database. 289 00:16:54,530 --> 00:16:57,410 It is the big, big integer. 290 00:16:57,560 --> 00:17:00,100 As I said, it will increment one by one. 291 00:17:00,110 --> 00:17:04,980 We have a first name, Typhoon Joan, Doreen Ahmed and Smith. 292 00:17:05,000 --> 00:17:09,380 And we have a last name, Smith Smith, that the Messiah's illness. 293 00:17:09,380 --> 00:17:10,910 And we have schools here. 294 00:17:10,910 --> 00:17:15,320 And as you can see, Ilia State University, Cambridge State University, Oxford University, Istanbul 295 00:17:15,320 --> 00:17:16,730 Technical University as well. 296 00:17:16,730 --> 00:17:21,170 And we also have higher dates and salary numeric. 297 00:17:22,180 --> 00:17:28,960 So notice that even though you didn't insert a value for the ID column, each teacher has an assigned, 298 00:17:29,110 --> 00:17:30,700 has an ID number assigned. 299 00:17:30,730 --> 00:17:36,790 Also, each column either displays the data type you defined when creating the table. 300 00:17:36,790 --> 00:17:45,450 And in this example varchar character Varang character Varang character Varang actually means varchar 301 00:17:45,460 --> 00:17:46,240 so. 302 00:17:47,210 --> 00:17:49,700 Character varying. 303 00:17:51,200 --> 00:17:53,030 So actually, let me take one here. 304 00:17:53,030 --> 00:17:57,890 So this character varying means actually means the. 305 00:17:59,510 --> 00:17:59,900 Here. 306 00:17:59,900 --> 00:18:02,840 So this means for. 307 00:18:03,650 --> 00:18:09,710 Car that we defined in previous lectures when we created our tables and database. 308 00:18:10,070 --> 00:18:13,610 So seeing the data type in the results will help later. 309 00:18:13,610 --> 00:18:19,580 So when you decide how to write queries that handle different depending on its type, so you can view 310 00:18:19,580 --> 00:18:26,030 the data using the Pgadmin interface in a few ways, but we will focus on writing the SQL handle use 311 00:18:26,030 --> 00:18:26,780 tasks.