1 00:00:02,190 --> 00:00:10,920 So the code begins with the two SQL keywords create and table that together the name of teachers, the 2 00:00:10,920 --> 00:00:18,690 signal PostgreSQL that the next bit of code describes the table to add to the database following an 3 00:00:18,690 --> 00:00:20,070 opening parentheses. 4 00:00:20,070 --> 00:00:28,110 The statement includes a comma separated list of column names along with their data types for style 5 00:00:28,110 --> 00:00:28,620 purposes. 6 00:00:28,620 --> 00:00:37,710 Each new line of code is on its own line and in indented four spaces so isn't required but makes the 7 00:00:37,710 --> 00:00:39,390 code more readable. 8 00:00:39,390 --> 00:00:40,590 So each column. 9 00:00:40,590 --> 00:00:42,930 So you can also write this like that. 10 00:00:43,260 --> 00:00:46,260 And here like that. 11 00:00:47,480 --> 00:00:53,200 So it but it making writing the codes like that in SQL makes it more readable. 12 00:00:53,210 --> 00:00:59,450 So each column name represents one discrete data element defined by a data type. 13 00:00:59,450 --> 00:01:02,030 So the ID column here. 14 00:01:02,210 --> 00:01:03,590 ID column here. 15 00:01:04,250 --> 00:01:09,020 Uh, two is of the data type of big cereal, right? 16 00:01:09,020 --> 00:01:18,230 So here a spatial integer type that auto increments every time you add a new row title. 17 00:01:18,230 --> 00:01:24,920 So in first of its the big big ID is going to one in second row is going to be three or in second, 18 00:01:24,920 --> 00:01:30,920 one is going to be two, three, four, five, six and so on. 19 00:01:30,920 --> 00:01:31,760 So. 20 00:01:32,730 --> 00:01:34,630 Uh, we also have the force. 21 00:01:35,650 --> 00:01:41,650 As I said, the first row receives the value of one in the ID column and the second row two and so on. 22 00:01:41,650 --> 00:01:49,030 So the big serial data type and other serial types are PostgreSQL specific implementations, but most 23 00:01:49,030 --> 00:01:52,090 database systems have similar feature. 24 00:01:52,090 --> 00:01:57,910 So next we create columns for teachers first name and last name, and for the school where they are. 25 00:01:57,910 --> 00:02:06,070 Teach each year of the data type var car a text column with a maximum length specified by the number 26 00:02:06,070 --> 00:02:10,630 in parentheses, in this case 25, 50 and 50. 27 00:02:11,350 --> 00:02:12,260 Uh, so. 28 00:02:13,610 --> 00:02:19,100 Now, although this is a safe assumption, you will discover over time that exceptions will always surprise 29 00:02:19,100 --> 00:02:19,500 you. 30 00:02:19,520 --> 00:02:22,730 So the teacher's hire date here. 31 00:02:22,730 --> 00:02:29,960 As you can see, our date here, teacher's hire date is set to the data type date. 32 00:02:30,430 --> 00:02:35,080 And the salary column said to the data type of numeric. 33 00:02:35,290 --> 00:02:41,860 So I will cover these data types more truly in next lectures, but this table shows some common examples 34 00:02:41,860 --> 00:02:42,910 for the data types. 35 00:02:42,910 --> 00:02:47,880 So the code block wraps up six with a closing parenthesis here. 36 00:02:48,490 --> 00:02:54,610 Here This is the last the eighth line of code with the close parenthesis and a semicolon. 37 00:02:54,610 --> 00:02:57,430 So now that you have a sense of how SQL works. 38 00:02:58,390 --> 00:03:02,500 Let's run this code in Pgadmin. 39 00:03:02,500 --> 00:03:05,200 So here I will also need to delete. 40 00:03:05,230 --> 00:03:06,040 Oops, sorry. 41 00:03:07,210 --> 00:03:07,600 Yeah. 42 00:03:08,200 --> 00:03:08,910 Like this. 43 00:03:08,950 --> 00:03:09,730 That's it. 44 00:03:11,270 --> 00:03:11,780 Here. 45 00:03:12,830 --> 00:03:13,460 Yes. 46 00:03:13,490 --> 00:03:18,920 Now you have your code and you're connected to the database so you can make the table using the some 47 00:03:18,920 --> 00:03:19,390 steps. 48 00:03:19,400 --> 00:03:23,660 Same steps we did when we created this database. 49 00:03:23,660 --> 00:03:29,600 So open the Pgadmin query tool, as I said, and just click on execute here. 50 00:03:32,630 --> 00:03:34,070 Syntax error hiredate. 51 00:03:34,100 --> 00:03:38,150 We have some errors with higher data and as you can see, we don't have comma here. 52 00:03:38,180 --> 00:03:38,960 We will need that. 53 00:03:38,960 --> 00:03:41,090 And here now let's run it again. 54 00:03:41,740 --> 00:03:42,530 And that's it. 55 00:03:42,550 --> 00:03:46,770 Query turned successfully in, uh, and milliseconds. 56 00:03:46,800 --> 00:03:50,140 Write this here and we will refresh the data here. 57 00:03:50,140 --> 00:03:52,750 And as you can see here we have here. 58 00:03:53,930 --> 00:03:55,370 Foreign data wrappers. 59 00:03:56,020 --> 00:03:57,340 Oxalic has. 60 00:03:57,350 --> 00:03:58,460 We have here. 61 00:04:01,230 --> 00:04:03,360 And we will have themes. 62 00:04:03,360 --> 00:04:13,230 And as you can see here, we have five EFTs aggregative collections and domains and under that. 63 00:04:13,990 --> 00:04:16,090 There is something called tables. 64 00:04:16,570 --> 00:04:20,890 And as you can see here, we add our tables here. 65 00:04:22,340 --> 00:04:23,600 Let's actually view this. 66 00:04:24,570 --> 00:04:32,670 So you can also expand the teachers table node by clicking the arrow to the left like this of its name. 67 00:04:32,730 --> 00:04:40,670 And this reveals more details about the table, including the column names here. 68 00:04:40,680 --> 00:04:46,140 Other information appears as well, such as indexes, triggers and constraints. 69 00:04:46,140 --> 00:04:51,510 So but I will cover those in later lectures and clicking on the table name and selecting the SQL menu 70 00:04:51,540 --> 00:04:52,320 here. 71 00:04:54,140 --> 00:04:55,070 Concrete. 72 00:04:55,280 --> 00:04:57,680 As you can see, there is so much options here. 73 00:04:57,770 --> 00:05:04,280 So click on the table and selecting the SQL menu in Pgadmin workspace will display a SQL statements 74 00:05:04,280 --> 00:05:07,550 that will be used to recreate the teachers table. 75 00:05:07,910 --> 00:05:11,000 And here the congratulations so far. 76 00:05:11,000 --> 00:05:14,240 You built the database and added a table to it. 77 00:05:14,240 --> 00:05:18,980 And the next step is to add data to the table so you can write your first query.