WEBVTT 00:00.930 --> 00:04.170 The PostgreSQL program you installed in previous lectures. 00:04.200 --> 00:09.420 The database management system, a software package that allows you to define, manage and query data 00:09.420 --> 00:10.710 stored in databases. 00:11.370 --> 00:16.410 A database, as I said, is a collection of objects that includes tables, functions and much more. 00:16.410 --> 00:22.920 So when you install PostgreSQL, it created a database server, an instance of the application running 00:22.920 --> 00:28.450 on your computer that includes the default database called PostgreSQL. 00:28.470 --> 00:32.160 As you can see here, this is our default database that we. 00:33.150 --> 00:35.820 Created in our installation process. 00:36.030 --> 00:42.450 So we will create a new database to use for the examples on this course rather than use the default 00:42.450 --> 00:48.600 so we can keep objects related to the particular topic or application organized together. 00:48.600 --> 00:49.860 So this is a good practice. 00:49.890 --> 00:56.910 It helps avoid a pileup of tables in a single database that have no relation to each other, and it 00:56.910 --> 01:03.150 ensures that if your data will be used to power an application such as mobile application, then the 01:03.150 --> 01:07.140 application database will contain only relevant information. 01:07.140 --> 01:12.810 So to create a database, you just need one line of SQL code which. 01:13.710 --> 01:19.500 And also you can find this code on our downloadable, on our lectures, downloadable section. 01:19.500 --> 01:22.850 You can download the code here in the text format here. 01:22.860 --> 01:30.900 So now we will go to servers and we will click on Object, Create or here tools. 01:31.230 --> 01:33.240 We will open the query tool here. 01:33.330 --> 01:35.700 So we'll go to Databases tool. 01:35.700 --> 01:37.410 Just one click on databases. 01:38.580 --> 01:39.630 And query tool. 01:39.810 --> 01:41.100 So now. 01:42.720 --> 01:43.950 We will use the code. 01:43.980 --> 01:45.810 Create database. 01:45.840 --> 01:46.560 I'm sorry. 01:47.070 --> 01:47.250 Yeah. 01:47.400 --> 01:49.980 So create database. 01:51.190 --> 01:54.430 Database and name this database, for example. 01:54.430 --> 01:56.460 In this case, let's actually name it Oxley. 01:58.470 --> 01:59.700 And here. 01:59.970 --> 02:10.260 So this statement creates a in this case, the statement creates a database named Oxley on our server 02:10.260 --> 02:12.180 using the PostgreSQL settings. 02:12.950 --> 02:17.540 So I want to also note that the code consists of two keywords. 02:17.540 --> 02:23.660 In this case, it's create and database, followed by the name of new database. 02:23.660 --> 02:24.830 In this case it's Oxley. 02:25.280 --> 02:28.010 So you end the statement with a semicolon here. 02:29.360 --> 02:31.580 Which signals the end of the command. 02:31.580 --> 02:38.090 So you must end all PostgreSQL statements with a semicolon as part of the ANSI SQL standard. 02:38.210 --> 02:42.950 In some circumstance, your queries will work even if you omit the semicolon. 02:42.950 --> 02:44.120 But not always. 02:44.120 --> 02:45.770 So use the semicolon. 02:45.800 --> 02:48.820 Using the semicolon is a good habit to form. 02:48.830 --> 02:52.700 So now let's execute the SQL in pgadmin. 02:52.700 --> 02:55.820 So you install the graphical administrative tool Pgadmin. 02:55.850 --> 03:01.340 If you didn't, go ahead and do that now in and I explained that in previous lectures. 03:01.340 --> 03:07.280 So for much of your work you will use Pgadmin to run the SQL statements you write knowing as executing 03:07.280 --> 03:07.820 the code. 03:07.850 --> 03:13.280 Later in this course, I will show you how to run SQL statements in a terminal window using the PostgreSQL 03:13.280 --> 03:16.820 command line using the SQL here SQL. 03:18.110 --> 03:19.120 Uh, here. 03:20.010 --> 03:20.570 Here. 03:23.420 --> 03:24.220 Ksql. 03:27.050 --> 03:33.460 So buy things, get things, starting with it easier with a graphical interface, of course. 03:33.470 --> 03:38.780 So here we will use the Pgadmin to run the SQL statement that creates database. 03:38.780 --> 03:41.990 Then we will connect to the new database and create a table. 03:41.990 --> 03:45.200 So now let's run a PostgreSQL. 03:45.200 --> 03:51.640 So if you are using the Windows installer says PostgreSQL launch, every time you boot up on macOS, 03:51.650 --> 03:55.100 you must double click PostgreSQL application in your application folder. 03:55.100 --> 03:56.750 So now. 03:57.750 --> 04:01.860 Let's click on this run button or F5. 04:01.860 --> 04:06.150 And as you can see here, our database is query returned successfully. 04:06.150 --> 04:11.610 And here, if you are not seeing any database here, just click on right click on the databases or your 04:11.610 --> 04:13.470 server and click on refresh. 04:13.470 --> 04:16.830 And here, as you can see here, our database is come up here. 04:16.830 --> 04:18.180 So now let's click on that. 04:18.180 --> 04:19.530 And here that's it. 04:19.530 --> 04:21.540 This is our auxiliary database. 04:22.790 --> 04:23.390 So. 04:24.590 --> 04:25.280 Now. 04:27.840 --> 04:28.860 What are we going to do here? 04:28.860 --> 04:31.110 As you can see, we have databases here. 04:31.260 --> 04:35.250 We have Oxley and PostgreSQL inside this PostgreSQL server. 04:36.110 --> 04:36.860 So now. 04:38.130 --> 04:43.200 We will open up Oxley here and now we will use the create. 04:43.200 --> 04:45.840 As you can see, there's a several options we can do with this. 04:45.840 --> 04:50.280 And after that, after right clicking on Oxley, we will select the query tool. 04:50.850 --> 04:55.570 And as you can see here, our database name is changed. 04:55.590 --> 05:04.170 As you can see here in previous here, we had something named Postgres here, which is this is associated 05:04.170 --> 05:08.880 that every command we type in here goes to here Postgres. 05:08.880 --> 05:13.500 But in this case, as you can see, the name changed and it actually is obsolete. 05:14.100 --> 05:15.870 So that's it. 05:16.170 --> 05:18.420 And let's connect to the database now. 05:18.660 --> 05:19.440 Here. 05:20.780 --> 05:24.860 So before you create a table, we also need to create a table, right? 05:24.980 --> 05:32.810 Because, uh, and we created our database firstly, you know, have a database called Oxalate, which 05:32.810 --> 05:36.950 you can use for the majority of the exercises in this course in your own work. 05:36.950 --> 05:42.230 It's generally best practice to create a new database for each project to keep tables with a related 05:42.230 --> 05:43.190 data together. 05:43.190 --> 05:49.640 And before you create a table, you must ensure that the PGADMIN is connected to the Oxley database, 05:49.640 --> 05:55.490 as I showed you, rather than to the default Postgres database so we can actually close this. 05:55.700 --> 05:59.870 Don't say Yeah, this is now we are connected to Oxley database. 06:00.530 --> 06:01.370 That's it. 06:01.370 --> 06:02.150 So. 06:03.200 --> 06:10.730 Now, let's as I mentioned, tables are where data lives and it's a relationship with are defined. 06:11.090 --> 06:17.630 So when you create a table, you assign a name to each column, sometimes referred to as a field or 06:17.630 --> 06:21.470 attribute, and it's assign each column a data type. 06:22.940 --> 06:29.420 These are the values with the columns we accept, such as text, integers, decimals and dates. 06:30.150 --> 06:32.420 And the definition of the data type is one way. 06:32.480 --> 06:35.030 SQL enforces the integrity of data. 06:35.770 --> 06:44.590 For example, a column defined as a date will accept data in only one of several standards formats such 06:44.590 --> 06:48.300 as years and months and days. 06:48.310 --> 06:48.700 Right? 06:48.700 --> 06:53.740 So if you try to enter characters not in a date format, for instance, the word. 06:55.100 --> 06:58.310 The word apple, you will receive an error. 06:58.310 --> 07:02.730 So data stored in a table can be accessed and analyzed or queried. 07:02.750 --> 07:04.330 So which SQL statements? 07:04.340 --> 07:11.480 Of course you can sort, edit and view data as well as easily alter the table later if your needs change. 07:11.480 --> 07:15.610 So now let's make a table in our database. 07:15.620 --> 07:20.090 So now we will use the create the Create table for this exercise. 07:20.090 --> 07:24.350 We will often discuss a piece of data and teacher salaries. 07:24.940 --> 07:27.850 And here we will create a create table. 07:28.850 --> 07:29.270 Great. 07:29.870 --> 07:33.890 Actually, if we would increase this font size a little bit, it will be awesome. 07:33.890 --> 07:35.450 So you can see more. 07:36.200 --> 07:36.860 And here. 07:36.860 --> 07:37.250 Object. 07:37.280 --> 07:38.360 No file. 07:39.510 --> 07:40.490 References. 07:41.780 --> 07:44.740 And here it has somewhere here. 07:44.840 --> 07:46.330 Debugger area graphs. 07:46.340 --> 07:49.540 MySQL stream user language is here. 07:49.550 --> 07:51.200 Query tool display. 07:52.180 --> 07:52.720 Editor. 07:53.990 --> 07:56.450 Font size to maybe. 08:00.910 --> 08:01.570 Oh, it's so big. 08:01.600 --> 08:04.120 It's too big here. 08:04.420 --> 08:07.330 Maybe we will put one and a three. 08:11.030 --> 08:11.250 Are. 08:14.450 --> 08:14.980 Browser. 08:14.990 --> 08:22.250 This dashboard graphs Miscellaneous path Query Tool and Editor one and three. 08:23.960 --> 08:24.500 Perfect. 08:29.490 --> 08:31.260 Now we will create a table. 08:31.260 --> 08:33.900 We will write this code and I will explain all of this code. 08:33.930 --> 08:40.140 Now, in this lecture, we will create a table table and we will name this table. 08:40.170 --> 08:40.920 Teachers. 08:41.160 --> 08:42.090 Teachers. 08:42.180 --> 08:44.670 And here we will have a columns here. 08:44.670 --> 08:51.660 And now we will ID, we will give it an ID, which is this ID is going to be datatype of big cereal. 08:51.660 --> 08:59.070 And we will also do, of course, the teacher also has an first name and last name. 09:02.390 --> 09:03.440 We'll write that now. 09:05.270 --> 09:07.700 I the big serial. 09:09.050 --> 09:09.950 First name. 09:10.430 --> 09:13.760 First name is going to be our character. 09:15.950 --> 09:17.120 Which is going to be 25. 09:20.520 --> 09:21.840 And the last name. 09:23.290 --> 09:24.050 Last name. 09:24.970 --> 09:27.060 Let's actually make it our character as well. 09:27.070 --> 09:31.150 50 plus names might be longer in some cases. 09:31.900 --> 09:32.710 Schools. 09:34.210 --> 09:34.540 Cool. 09:34.690 --> 09:40.330 And our character is also the character type and it's going to be 50. 09:40.630 --> 09:47.980 The school name might even longer than last name, and we will also set a higher date, that higher 09:47.980 --> 09:55.360 date that our teacher started his or her job and what teacher has. 09:56.270 --> 09:57.110 His salary. 09:57.830 --> 10:00.740 We will also add the salary, which is numeric. 10:01.220 --> 10:02.060 Numeric here. 10:02.330 --> 10:03.050 That's it. 10:03.350 --> 10:05.330 And after that, we will close that. 10:05.450 --> 10:07.970 As you can see here, you can also close that. 10:08.120 --> 10:11.960 Right here or here, it doesn't matter in some cases. 10:11.960 --> 10:12.740 And that's it. 10:12.740 --> 10:15.860 And I'll add the semicolon here. 10:15.860 --> 10:19.190 So the table definition is a far from comprehensive. 10:19.190 --> 10:23.720 For example, it's missing several constraints that would ensure that the columns that must be filled 10:24.440 --> 10:30.110 do indeed have data or that are not inadvertently entering duplicate values. 10:30.110 --> 10:37.880 So I covered constraint in detail in next lectures, but these early lectures, I'm omitting them to 10:37.880 --> 10:41.300 focus on getting you started on exploring data.