WEBVTT 00:07.550 --> 00:10.370 I will call you back again for another video lecture in PostgreSQL. 00:10.370 --> 00:15.680 And in this video lecture we go ahead and learn how to create a table in PostgreSQL. 00:16.220 --> 00:20.900 Alright, so in the previous lecture I was able to teach you how to create a database, and I was able 00:20.900 --> 00:25.430 to teach you how to drop a database and also taught you how to connect to your database. 00:25.460 --> 00:29.000 Now let's go ahead and learn how to create a table inside our database. 00:29.000 --> 00:32.150 So we are going to have a quick assignment before this. 00:32.150 --> 00:33.980 So it just depends on you. 00:33.980 --> 00:37.010 And that is washing over this. 00:37.010 --> 00:40.340 You can see I have uh some of the database here. 00:40.370 --> 00:42.980 I have a Postgres and school. 00:43.010 --> 00:48.290 I have dropped the student database and I created a new database called school. 00:48.290 --> 00:56.060 So you can inaugurate a new database of any name of your choice, or you go ahead and use the student 00:56.060 --> 01:02.840 database, or you go ahead and drop the student database and create a database called school. 01:02.870 --> 01:09.670 Then we are going to use the student's name as a symbol inside the school. 01:09.700 --> 01:12.280 Because students have to be inside the school. 01:12.280 --> 01:18.130 So that is a quick assignment for you to walk ahead and drop the student's database and create a school 01:18.130 --> 01:18.700 database. 01:18.700 --> 01:25.450 And then we join me while we create the student table inside the school database. 01:25.840 --> 01:26.350 How? 01:26.350 --> 01:29.500 Right now you can see that everything is still about Postgres. 01:29.500 --> 01:32.530 And then I have to first of all connect to the SQL database. 01:32.530 --> 01:40.240 And for me to do that, go ahead and put the backward slash C or connect and then school and hit enter. 01:40.240 --> 01:45.730 And now you are now connected to the database school as user Postgres. 01:46.840 --> 01:56.020 Well the next thing is you can see that everything right in here is actually looking so so so confusing 01:56.290 --> 01:58.420 because we have so many things in here. 01:58.450 --> 02:07.000 Now I just want us to use those school database I just entered, and for that I have to play everything 02:07.000 --> 02:07.540 I have here. 02:07.540 --> 02:15.690 So in order to play everything on this SQL show or you need to do is backward slash that exclamation 02:15.690 --> 02:19.440 mark, then C, r, s and hit enter. 02:19.440 --> 02:23.250 And now I'm dealing with just the SQL database. 02:23.250 --> 02:28.320 So inside this code base I will go ahead and create a table. 02:28.320 --> 02:32.820 And to create a table I need to do is I'll go ahead and say create table. 02:32.820 --> 02:35.040 And then I'm going to give the table name. 02:35.040 --> 02:37.020 Let me say is students. 02:37.020 --> 02:38.970 So the table name is going to be student. 02:38.970 --> 02:43.410 And then I'll go ahead and uh open up this. 02:43.410 --> 02:51.510 And then I'm going to add in the student, uh, ID the student first name the student last name the 02:51.510 --> 02:52.050 GP. 02:52.140 --> 02:56.340 And in row means date, the date the student enrolled. 02:56.370 --> 02:58.800 Go ahead and click enter. 02:58.830 --> 03:01.470 I can actually continue this in one single line. 03:01.470 --> 03:02.700 What if you hit enter? 03:02.700 --> 03:06.510 It's not going to close because you've not put a semicolon at the end. 03:06.540 --> 03:13.140 So whenever you put a semicolon at the end of this, that is when this database has been finished and 03:13.440 --> 03:14.810 you move to the next lane. 03:14.840 --> 03:15.350 What? 03:15.380 --> 03:15.950 As follows. 03:15.980 --> 03:18.740 I have not put a semicolon at the end. 03:18.770 --> 03:23.240 I could actually go ahead and hit enter and keep moving while creating this table. 03:23.780 --> 03:30.320 Now the very first one is the students underscore ID right. 03:30.320 --> 03:36.710 So strings underscore id and the data type is going to be integer rank with a comma. 03:36.740 --> 03:41.060 Now you can go over to PostgreSQL documentation. 03:41.090 --> 03:45.800 And under chapter we have on the chapter eight the data types. 03:45.800 --> 03:48.050 You can go ahead and see some of the data types. 03:48.050 --> 03:51.110 We have the integer because I used integer just now. 03:51.110 --> 03:53.930 And most of us will be like what is int? 03:53.930 --> 03:58.340 So int is a force sign byte integer and that is the int. 03:58.340 --> 04:01.550 And now we're also going to use something like character. 04:01.550 --> 04:04.730 And now we have a fixed length character. 04:04.730 --> 04:06.560 We're not going to use that for single one. 04:06.590 --> 04:09.890 We're going to use variable length character for the string. 04:09.890 --> 04:13.820 So I'm going to always use varchar for names and text. 04:13.820 --> 04:17.030 And I'm going to use int for numbers. 04:17.030 --> 04:19.350 And I'm going to use decimal. 04:19.380 --> 04:27.960 I'm going to use that, uh, for the decimal numbers and for floats you can also use for floats, but 04:27.990 --> 04:30.990 for integer decimal our range is a decimal. 04:31.020 --> 04:31.680 Right. 04:31.680 --> 04:34.380 So now I'll go ahead and minimize this. 04:34.410 --> 04:36.450 Let's go back to the table. 04:36.450 --> 04:38.610 And now I've added the students ID. 04:38.640 --> 04:41.880 What I need again is the first name and the last name. 04:41.910 --> 04:44.760 Go ahead and see first okay. 04:44.790 --> 04:50.310 This is going to be all lowercase first underscore name. 04:50.310 --> 04:57.090 And just like I told you the data type is going to be var because that is the variable length we are 04:57.090 --> 04:57.900 going to use. 04:57.900 --> 05:02.280 And then I'm going to make that to be like 50 characters and put a comma. 05:02.280 --> 05:07.470 And the next one is going to be the last underscore name. 05:07.470 --> 05:10.890 And the data type is going to be varchar. 05:11.460 --> 05:16.200 And the character is going to be like 50 and click comma. 05:16.200 --> 05:18.810 And the next one is the GP. 05:18.960 --> 05:24.950 And GP is going to be a decimal because it's going to be like 3.5, 4.7 and so on, and it's going to 05:24.950 --> 05:26.330 be just one decimal place. 05:26.330 --> 05:34.010 So I'll go ahead and say, um, this is going to be decimal, and then this is going to be one and then 05:34.010 --> 05:37.130 by one decimal place and then go ahead and put a comma. 05:37.160 --> 05:40.190 Then we'll go ahead and add the enroll dates. 05:40.220 --> 05:40.940 Right. 05:40.940 --> 05:48.410 So this is going to be the in row underscore date. 05:48.440 --> 05:53.180 Then I went ahead and close this and then put a semicolon at the end. 05:53.180 --> 05:56.870 And now that I've added a semicolon I've come to the final end. 05:56.870 --> 06:04.220 And if I should go ahead and hit on enter you can see that my table is now created. 06:04.310 --> 06:11.870 So if you want to see the table you've just created, just go ahead and put backward slash D and hit 06:11.870 --> 06:12.440 enter. 06:12.440 --> 06:20.900 And you can see I have uh under my schema I have public uh students table for uh Postgres. 06:20.900 --> 06:27.230 And if I should go over to Pgadmin, let's go ahead and, uh, refresh this. 06:27.260 --> 06:33.220 Go ahead and refresh and then go under table under school and then move under tables. 06:33.220 --> 06:35.860 You can see I have students right in here. 06:35.860 --> 06:39.400 So a table called students has been created under here. 06:39.400 --> 06:43.000 And remanding this under the SQL show. 06:43.030 --> 06:43.840 All right. 06:44.320 --> 06:51.490 Now if you want to select the that's for you to see whatever you created the column names you want to 06:51.520 --> 06:54.760 check the columns under the school table. 06:54.790 --> 07:02.650 All I need to do is I'll go right in here and I'm going to say backward slash D. 07:02.770 --> 07:09.130 And then the table name is A students. 07:09.130 --> 07:12.190 And I'm going to hit enter. 07:12.190 --> 07:20.560 And you can see I have a students ID integer first name and character varying and last name character 07:20.560 --> 07:21.070 varying. 07:21.070 --> 07:23.290 And GP is a numeric. 07:23.290 --> 07:26.680 And one by one the error dates is date. 07:26.710 --> 07:33.720 Now let's go over here and we can go under this place and take on query two. 07:33.750 --> 07:36.570 And there we click on query two. 07:36.600 --> 07:43.530 We can actually create these I can go ahead and say uh select uh all. 07:43.680 --> 07:45.570 And then from. 07:48.210 --> 07:49.230 Students. 07:50.130 --> 07:51.720 So select all from students. 07:51.720 --> 07:54.270 Put a semicolon at the end of this and hit enter. 07:54.540 --> 07:56.010 Now you go ahead and run. 07:56.010 --> 07:57.870 This so is the cutest query. 07:57.870 --> 08:05.040 And uh you can see under here I have a students ID, first name, last name, GPA and enroll date. 08:05.070 --> 08:12.000 Okay, uh, whatever I have on that SQL show, if I get over to Pgadmin, I can actually do that. 08:12.030 --> 08:13.620 All right, so our application will boot up. 08:13.620 --> 08:18.270 Then whatever I will do here, I'll go over to Pgadmin and I'm going to show you that. 08:18.270 --> 08:21.000 So go ahead and practice with this. 08:21.000 --> 08:24.300 And if you have any questions please go ahead and use the question and answer section. 08:24.300 --> 08:26.610 And I'm going to get back to you as soon as possible. 08:26.640 --> 08:27.540 Thank you so much. 08:27.540 --> 08:30.480 And I'm going to see you in the next video lecture.