1 00:00:00,930 --> 00:00:04,170 The PostgreSQL program you installed in previous lectures. 2 00:00:04,200 --> 00:00:09,420 The database management system, a software package that allows you to define, manage and query data 3 00:00:09,420 --> 00:00:10,710 stored in databases. 4 00:00:11,370 --> 00:00:16,410 A database, as I said, is a collection of objects that includes tables, functions and much more. 5 00:00:16,410 --> 00:00:22,920 So when you install PostgreSQL, it created a database server, an instance of the application running 6 00:00:22,920 --> 00:00:28,450 on your computer that includes the default database called PostgreSQL. 7 00:00:28,470 --> 00:00:32,160 As you can see here, this is our default database that we. 8 00:00:33,150 --> 00:00:35,820 Created in our installation process. 9 00:00:36,030 --> 00:00:42,450 So we will create a new database to use for the examples on this course rather than use the default 10 00:00:42,450 --> 00:00:48,600 so we can keep objects related to the particular topic or application organized together. 11 00:00:48,600 --> 00:00:49,860 So this is a good practice. 12 00:00:49,890 --> 00:00:56,910 It helps avoid a pileup of tables in a single database that have no relation to each other, and it 13 00:00:56,910 --> 00:01:03,150 ensures that if your data will be used to power an application such as mobile application, then the 14 00:01:03,150 --> 00:01:07,140 application database will contain only relevant information. 15 00:01:07,140 --> 00:01:12,810 So to create a database, you just need one line of SQL code which. 16 00:01:13,710 --> 00:01:19,500 And also you can find this code on our downloadable, on our lectures, downloadable section. 17 00:01:19,500 --> 00:01:22,850 You can download the code here in the text format here. 18 00:01:22,860 --> 00:01:30,900 So now we will go to servers and we will click on Object, Create or here tools. 19 00:01:31,230 --> 00:01:33,240 We will open the query tool here. 20 00:01:33,330 --> 00:01:35,700 So we'll go to Databases tool. 21 00:01:35,700 --> 00:01:37,410 Just one click on databases. 22 00:01:38,580 --> 00:01:39,630 And query tool. 23 00:01:39,810 --> 00:01:41,100 So now. 24 00:01:42,720 --> 00:01:43,950 We will use the code. 25 00:01:43,980 --> 00:01:45,810 Create database. 26 00:01:45,840 --> 00:01:46,560 I'm sorry. 27 00:01:47,070 --> 00:01:47,250 Yeah. 28 00:01:47,400 --> 00:01:49,980 So create database. 29 00:01:51,190 --> 00:01:54,430 Database and name this database, for example. 30 00:01:54,430 --> 00:01:56,460 In this case, let's actually name it Oxley. 31 00:01:58,470 --> 00:01:59,700 And here. 32 00:01:59,970 --> 00:02:10,260 So this statement creates a in this case, the statement creates a database named Oxley on our server 33 00:02:10,260 --> 00:02:12,180 using the PostgreSQL settings. 34 00:02:12,950 --> 00:02:17,540 So I want to also note that the code consists of two keywords. 35 00:02:17,540 --> 00:02:23,660 In this case, it's create and database, followed by the name of new database. 36 00:02:23,660 --> 00:02:24,830 In this case it's Oxley. 37 00:02:25,280 --> 00:02:28,010 So you end the statement with a semicolon here. 38 00:02:29,360 --> 00:02:31,580 Which signals the end of the command. 39 00:02:31,580 --> 00:02:38,090 So you must end all PostgreSQL statements with a semicolon as part of the ANSI SQL standard. 40 00:02:38,210 --> 00:02:42,950 In some circumstance, your queries will work even if you omit the semicolon. 41 00:02:42,950 --> 00:02:44,120 But not always. 42 00:02:44,120 --> 00:02:45,770 So use the semicolon. 43 00:02:45,800 --> 00:02:48,820 Using the semicolon is a good habit to form. 44 00:02:48,830 --> 00:02:52,700 So now let's execute the SQL in pgadmin. 45 00:02:52,700 --> 00:02:55,820 So you install the graphical administrative tool Pgadmin. 46 00:02:55,850 --> 00:03:01,340 If you didn't, go ahead and do that now in and I explained that in previous lectures. 47 00:03:01,340 --> 00:03:07,280 So for much of your work you will use Pgadmin to run the SQL statements you write knowing as executing 48 00:03:07,280 --> 00:03:07,820 the code. 49 00:03:07,850 --> 00:03:13,280 Later in this course, I will show you how to run SQL statements in a terminal window using the PostgreSQL 50 00:03:13,280 --> 00:03:16,820 command line using the SQL here SQL. 51 00:03:18,110 --> 00:03:19,120 Uh, here. 52 00:03:20,010 --> 00:03:20,570 Here. 53 00:03:23,420 --> 00:03:24,220 Ksql. 54 00:03:27,050 --> 00:03:33,460 So buy things, get things, starting with it easier with a graphical interface, of course. 55 00:03:33,470 --> 00:03:38,780 So here we will use the Pgadmin to run the SQL statement that creates database. 56 00:03:38,780 --> 00:03:41,990 Then we will connect to the new database and create a table. 57 00:03:41,990 --> 00:03:45,200 So now let's run a PostgreSQL. 58 00:03:45,200 --> 00:03:51,640 So if you are using the Windows installer says PostgreSQL launch, every time you boot up on macOS, 59 00:03:51,650 --> 00:03:55,100 you must double click PostgreSQL application in your application folder. 60 00:03:55,100 --> 00:03:56,750 So now. 61 00:03:57,750 --> 00:04:01,860 Let's click on this run button or F5. 62 00:04:01,860 --> 00:04:06,150 And as you can see here, our database is query returned successfully. 63 00:04:06,150 --> 00:04:11,610 And here, if you are not seeing any database here, just click on right click on the databases or your 64 00:04:11,610 --> 00:04:13,470 server and click on refresh. 65 00:04:13,470 --> 00:04:16,830 And here, as you can see here, our database is come up here. 66 00:04:16,830 --> 00:04:18,180 So now let's click on that. 67 00:04:18,180 --> 00:04:19,530 And here that's it. 68 00:04:19,530 --> 00:04:21,540 This is our auxiliary database. 69 00:04:22,790 --> 00:04:23,390 So. 70 00:04:24,590 --> 00:04:25,280 Now. 71 00:04:27,840 --> 00:04:28,860 What are we going to do here? 72 00:04:28,860 --> 00:04:31,110 As you can see, we have databases here. 73 00:04:31,260 --> 00:04:35,250 We have Oxley and PostgreSQL inside this PostgreSQL server. 74 00:04:36,110 --> 00:04:36,860 So now. 75 00:04:38,130 --> 00:04:43,200 We will open up Oxley here and now we will use the create. 76 00:04:43,200 --> 00:04:45,840 As you can see, there's a several options we can do with this. 77 00:04:45,840 --> 00:04:50,280 And after that, after right clicking on Oxley, we will select the query tool. 78 00:04:50,850 --> 00:04:55,570 And as you can see here, our database name is changed. 79 00:04:55,590 --> 00:05:04,170 As you can see here in previous here, we had something named Postgres here, which is this is associated 80 00:05:04,170 --> 00:05:08,880 that every command we type in here goes to here Postgres. 81 00:05:08,880 --> 00:05:13,500 But in this case, as you can see, the name changed and it actually is obsolete. 82 00:05:14,100 --> 00:05:15,870 So that's it. 83 00:05:16,170 --> 00:05:18,420 And let's connect to the database now. 84 00:05:18,660 --> 00:05:19,440 Here. 85 00:05:20,780 --> 00:05:24,860 So before you create a table, we also need to create a table, right? 86 00:05:24,980 --> 00:05:32,810 Because, uh, and we created our database firstly, you know, have a database called Oxalate, which 87 00:05:32,810 --> 00:05:36,950 you can use for the majority of the exercises in this course in your own work. 88 00:05:36,950 --> 00:05:42,230 It's generally best practice to create a new database for each project to keep tables with a related 89 00:05:42,230 --> 00:05:43,190 data together. 90 00:05:43,190 --> 00:05:49,640 And before you create a table, you must ensure that the PGADMIN is connected to the Oxley database, 91 00:05:49,640 --> 00:05:55,490 as I showed you, rather than to the default Postgres database so we can actually close this. 92 00:05:55,700 --> 00:05:59,870 Don't say Yeah, this is now we are connected to Oxley database. 93 00:06:00,530 --> 00:06:01,370 That's it. 94 00:06:01,370 --> 00:06:02,150 So. 95 00:06:03,200 --> 00:06:10,730 Now, let's as I mentioned, tables are where data lives and it's a relationship with are defined. 96 00:06:11,090 --> 00:06:17,630 So when you create a table, you assign a name to each column, sometimes referred to as a field or 97 00:06:17,630 --> 00:06:21,470 attribute, and it's assign each column a data type. 98 00:06:22,940 --> 00:06:29,420 These are the values with the columns we accept, such as text, integers, decimals and dates. 99 00:06:30,150 --> 00:06:32,420 And the definition of the data type is one way. 100 00:06:32,480 --> 00:06:35,030 SQL enforces the integrity of data. 101 00:06:35,770 --> 00:06:44,590 For example, a column defined as a date will accept data in only one of several standards formats such 102 00:06:44,590 --> 00:06:48,300 as years and months and days. 103 00:06:48,310 --> 00:06:48,700 Right? 104 00:06:48,700 --> 00:06:53,740 So if you try to enter characters not in a date format, for instance, the word. 105 00:06:55,100 --> 00:06:58,310 The word apple, you will receive an error. 106 00:06:58,310 --> 00:07:02,730 So data stored in a table can be accessed and analyzed or queried. 107 00:07:02,750 --> 00:07:04,330 So which SQL statements? 108 00:07:04,340 --> 00:07:11,480 Of course you can sort, edit and view data as well as easily alter the table later if your needs change. 109 00:07:11,480 --> 00:07:15,610 So now let's make a table in our database. 110 00:07:15,620 --> 00:07:20,090 So now we will use the create the Create table for this exercise. 111 00:07:20,090 --> 00:07:24,350 We will often discuss a piece of data and teacher salaries. 112 00:07:24,940 --> 00:07:27,850 And here we will create a create table. 113 00:07:28,850 --> 00:07:29,270 Great. 114 00:07:29,870 --> 00:07:33,890 Actually, if we would increase this font size a little bit, it will be awesome. 115 00:07:33,890 --> 00:07:35,450 So you can see more. 116 00:07:36,200 --> 00:07:36,860 And here. 117 00:07:36,860 --> 00:07:37,250 Object. 118 00:07:37,280 --> 00:07:38,360 No file. 119 00:07:39,510 --> 00:07:40,490 References. 120 00:07:41,780 --> 00:07:44,740 And here it has somewhere here. 121 00:07:44,840 --> 00:07:46,330 Debugger area graphs. 122 00:07:46,340 --> 00:07:49,540 MySQL stream user language is here. 123 00:07:49,550 --> 00:07:51,200 Query tool display. 124 00:07:52,180 --> 00:07:52,720 Editor. 125 00:07:53,990 --> 00:07:56,450 Font size to maybe. 126 00:08:00,910 --> 00:08:01,570 Oh, it's so big. 127 00:08:01,600 --> 00:08:04,120 It's too big here. 128 00:08:04,420 --> 00:08:07,330 Maybe we will put one and a three. 129 00:08:11,030 --> 00:08:11,250 Are. 130 00:08:14,450 --> 00:08:14,980 Browser. 131 00:08:14,990 --> 00:08:22,250 This dashboard graphs Miscellaneous path Query Tool and Editor one and three. 132 00:08:23,960 --> 00:08:24,500 Perfect. 133 00:08:29,490 --> 00:08:31,260 Now we will create a table. 134 00:08:31,260 --> 00:08:33,900 We will write this code and I will explain all of this code. 135 00:08:33,930 --> 00:08:40,140 Now, in this lecture, we will create a table table and we will name this table. 136 00:08:40,170 --> 00:08:40,920 Teachers. 137 00:08:41,160 --> 00:08:42,090 Teachers. 138 00:08:42,180 --> 00:08:44,670 And here we will have a columns here. 139 00:08:44,670 --> 00: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. 140 00:08:51,660 --> 00:08:59,070 And we will also do, of course, the teacher also has an first name and last name. 141 00:09:02,390 --> 00:09:03,440 We'll write that now. 142 00:09:05,270 --> 00:09:07,700 I the big serial. 143 00:09:09,050 --> 00:09:09,950 First name. 144 00:09:10,430 --> 00:09:13,760 First name is going to be our character. 145 00:09:15,950 --> 00:09:17,120 Which is going to be 25. 146 00:09:20,520 --> 00:09:21,840 And the last name. 147 00:09:23,290 --> 00:09:24,050 Last name. 148 00:09:24,970 --> 00:09:27,060 Let's actually make it our character as well. 149 00:09:27,070 --> 00:09:31,150 50 plus names might be longer in some cases. 150 00:09:31,900 --> 00:09:32,710 Schools. 151 00:09:34,210 --> 00:09:34,540 Cool. 152 00:09:34,690 --> 00:09:40,330 And our character is also the character type and it's going to be 50. 153 00:09:40,630 --> 00:09:47,980 The school name might even longer than last name, and we will also set a higher date, that higher 154 00:09:47,980 --> 00:09:55,360 date that our teacher started his or her job and what teacher has. 155 00:09:56,270 --> 00:09:57,110 His salary. 156 00:09:57,830 --> 00:10:00,740 We will also add the salary, which is numeric. 157 00:10:01,220 --> 00:10:02,060 Numeric here. 158 00:10:02,330 --> 00:10:03,050 That's it. 159 00:10:03,350 --> 00:10:05,330 And after that, we will close that. 160 00:10:05,450 --> 00:10:07,970 As you can see here, you can also close that. 161 00:10:08,120 --> 00:10:11,960 Right here or here, it doesn't matter in some cases. 162 00:10:11,960 --> 00:10:12,740 And that's it. 163 00:10:12,740 --> 00:10:15,860 And I'll add the semicolon here. 164 00:10:15,860 --> 00:10:19,190 So the table definition is a far from comprehensive. 165 00:10:19,190 --> 00:10:23,720 For example, it's missing several constraints that would ensure that the columns that must be filled 166 00:10:24,440 --> 00:10:30,110 do indeed have data or that are not inadvertently entering duplicate values. 167 00:10:30,110 --> 00:10:37,880 So I covered constraint in detail in next lectures, but these early lectures, I'm omitting them to 168 00:10:37,880 --> 00:10:41,300 focus on getting you started on exploring data.