1 00:00:00,730 --> 00:00:05,380 So let's get our feet wet with Postgres, so on my Mac, I have it running on windows, you need to 2 00:00:05,380 --> 00:00:10,150 have it running as well and you can see that it's running on a Mac by looking at this window, if you 3 00:00:10,150 --> 00:00:11,590 see it, or coming up here. 4 00:00:11,590 --> 00:00:13,650 And if it says stop, then it's running. 5 00:00:13,660 --> 00:00:20,590 Otherwise get it going, then start Dvor and we'll connect to our localhost connection, which is called 6 00:00:20,590 --> 00:00:21,090 postgrads. 7 00:00:21,100 --> 00:00:25,360 In my case, you may have me may need your something else, but I'm going to connect and I'm going to 8 00:00:25,360 --> 00:00:26,620 look at our bookings database. 9 00:00:26,620 --> 00:00:29,040 We'll double click on that to make that our active database. 10 00:00:29,200 --> 00:00:30,030 So I'm now connected. 11 00:00:30,760 --> 00:00:33,550 So there is nothing in this database right now. 12 00:00:33,550 --> 00:00:38,810 We have under our schemas for public, we have no tables and that's fine. 13 00:00:39,130 --> 00:00:42,100 What I want to do is start playing with some sequel. 14 00:00:42,100 --> 00:00:48,880 So I'm going to come up here and choose a sequel, Ed, and that will pop up a little window that allows 15 00:00:48,880 --> 00:00:50,770 me to start typing some commands. 16 00:00:51,460 --> 00:00:56,200 Now, when you connect to a database and it doesn't matter if it's postgrads, if it's a relational 17 00:00:56,200 --> 00:01:03,940 database like Postgrads or MySQL or Marea DB, you have to interact with it using a language just as 18 00:01:03,940 --> 00:01:06,130 go as a language or as a language. 19 00:01:06,370 --> 00:01:12,130 There's a language to talk to database, and it's called sequel, which is spelled QOL and it stands 20 00:01:12,130 --> 00:01:14,080 for structured query language. 21 00:01:14,590 --> 00:01:17,620 So we don't have a database, we have a database, we have nothing in it right now. 22 00:01:17,620 --> 00:01:19,540 But we can still play with a little bit. 23 00:01:20,110 --> 00:01:25,090 When you want to get information from a database, you're going to select information. 24 00:01:25,630 --> 00:01:31,900 So I'm going to type the command select and I'll just select in single quotes fish and end my command 25 00:01:31,900 --> 00:01:32,710 with a semicolon. 26 00:01:33,100 --> 00:01:37,870 And I can run that by clicking the little button right here and down at the bottom. 27 00:01:37,930 --> 00:01:42,820 It shows me that I selected fish and it says column questionmark, column questionmark because there's 28 00:01:42,820 --> 00:01:43,840 no actual column there. 29 00:01:44,110 --> 00:01:49,550 If we are selecting from a table, there might be or there would be names for things. 30 00:01:49,580 --> 00:01:51,880 Now I can actually name this fish. 31 00:01:51,880 --> 00:01:56,800 I can say select fish as and I would just call it animal with no quotes. 32 00:01:56,800 --> 00:02:00,280 So I'm naming this select and I run it again. 33 00:02:00,790 --> 00:02:04,270 It says now you've selected fish from the column animal. 34 00:02:04,630 --> 00:02:05,890 But what if you wanted more than that? 35 00:02:06,100 --> 00:02:13,750 I can say select fish as animal, ten as a page and then one hundred point one as weight. 36 00:02:15,790 --> 00:02:23,770 So again, I have select fish as animal comma, ten as age, comma and one hundred point one as weight. 37 00:02:23,770 --> 00:02:26,350 And if I run that I will get three columns. 38 00:02:26,890 --> 00:02:29,650 So I have an animal, an age and weight. 39 00:02:30,100 --> 00:02:34,480 And you notice that each of these looks different is what I what I actually entered fish. 40 00:02:34,480 --> 00:02:36,940 I had to put it in quotes because that's character data. 41 00:02:36,940 --> 00:02:37,960 It's string's. 42 00:02:39,190 --> 00:02:44,460 Ten is an integer, one hundred point one is a float, sometimes called a double. 43 00:02:45,100 --> 00:02:46,300 Now this is really helpful. 44 00:02:46,450 --> 00:02:50,050 I can select nothing from nothing or select something from nothing. 45 00:02:50,200 --> 00:02:52,990 It'd be far more useful if we actually had a table. 46 00:02:53,110 --> 00:02:56,350 So what I'm going to do now is create a sample table. 47 00:02:56,350 --> 00:03:01,600 So back over on the left hand side where it says tables, I'm going to create a new table and I will 48 00:03:01,600 --> 00:03:02,590 call my table. 49 00:03:03,400 --> 00:03:08,590 I will call it animal animals, plural, OK? 50 00:03:09,010 --> 00:03:10,570 And it has no columns at the moment. 51 00:03:11,170 --> 00:03:12,880 I need to give it some columns. 52 00:03:12,880 --> 00:03:16,480 So I've I've created I'm creating a table that has not saved yet, so it doesn't exist. 53 00:03:16,750 --> 00:03:20,830 And you can see where it is by looking at this little path at the top. 54 00:03:21,250 --> 00:03:27,700 It's on the server called postgrads in the database called bookings on the schema called public. 55 00:03:27,940 --> 00:03:31,390 And then there's tables and I'm on new table, which will be called animals. 56 00:03:31,390 --> 00:03:31,540 What? 57 00:03:31,540 --> 00:03:32,110 I'm done with it. 58 00:03:32,230 --> 00:03:33,550 So I'm going to give it that name. 59 00:03:34,060 --> 00:03:36,010 And now I want to give it a column. 60 00:03:38,640 --> 00:03:42,270 So I will right click in this area and say create a new column. 61 00:03:43,440 --> 00:03:52,200 Now we are going to use a pattern where every row in a table in our database has a unique I.D., a number. 62 00:03:52,530 --> 00:03:55,820 And the easiest way to do that is by creating a column called ID. 63 00:03:55,830 --> 00:03:59,310 And this is a kind of a pattern will be using throughout this course. 64 00:03:59,880 --> 00:04:05,850 The data type is not VirTra, which would be character data there is called character bearing or voiture. 65 00:04:06,150 --> 00:04:08,330 Instead, I'm going to change it to serial. 66 00:04:08,850 --> 00:04:15,370 So I click click on this, click on the little arrow that shows up here and just look for serial. 67 00:04:15,420 --> 00:04:21,630 So somewhere under here I'm going to have serial serial not serial two but serial. 68 00:04:21,810 --> 00:04:24,750 OK, so that's what I'm gonna choose for its data type length. 69 00:04:24,750 --> 00:04:34,170 I'll leave at 11, which makes I can have billions of records in this table and I will just say not 70 00:04:34,170 --> 00:04:35,790 no, I have to have not known. 71 00:04:35,790 --> 00:04:39,630 And the funny thing about Dvir is when you click on it, you actually have to click on it twice to get 72 00:04:39,630 --> 00:04:47,000 that checkmark and click, OK, so I've now given it one column of type ID length eleven. 73 00:04:47,490 --> 00:04:49,570 It's not know and it's a serial. 74 00:04:49,600 --> 00:04:52,770 Now the serial type will actually do some magic for us. 75 00:04:52,770 --> 00:04:58,590 Every time we insert a record into this row, we insert one line of data, one row of data, which we 76 00:04:58,590 --> 00:04:59,250 call a record. 77 00:05:00,000 --> 00:05:03,360 It will automatically increment the ID by one for us. 78 00:05:03,540 --> 00:05:09,600 OK, now I'm going to create a second column, so create a new column and I will just call this name. 79 00:05:10,450 --> 00:05:14,670 I'll call it animal name, animal name. 80 00:05:14,850 --> 00:05:20,040 And again, I'm going to make that not null because every animal has to have a name and I'll click, 81 00:05:20,040 --> 00:05:22,410 OK, and I'll just save this. 82 00:05:22,500 --> 00:05:26,460 So I click save and it will ask me if I want to create it. 83 00:05:26,460 --> 00:05:27,450 Are persistent in. 84 00:05:27,450 --> 00:05:30,360 Yes, I do want to persistance now I have a table. 85 00:05:30,730 --> 00:05:38,790 OK, so if I look over here on the left hand side I have one under my server postgrads, my database 86 00:05:38,790 --> 00:05:46,040 bookings, my schema public under my tables I have one table called animals and it's currently nothing. 87 00:05:46,050 --> 00:05:51,960 So if I go back to my school by Sehgal editor here now, I can make my select statement a little more 88 00:05:51,960 --> 00:05:52,380 useful. 89 00:05:52,380 --> 00:05:59,610 I can say select star, which means get everything from animals and hit return and I'll get nothing 90 00:05:59,610 --> 00:06:01,440 when I run this because there's nothing in there right now. 91 00:06:01,980 --> 00:06:03,570 So it shows me you have nothing. 92 00:06:03,570 --> 00:06:07,860 You have do have a column ID and a column animal name, but there's nothing in there. 93 00:06:07,890 --> 00:06:11,550 So let's go back to animals and this time let's click on data. 94 00:06:12,120 --> 00:06:16,380 And I want to add a row of data to this table. 95 00:06:17,610 --> 00:06:19,710 And I do that by coming down here. 96 00:06:20,040 --> 00:06:21,030 It's not intuitive. 97 00:06:21,930 --> 00:06:28,050 This is this is a nice free ed for databases or visual GUI tool to connect to databases. 98 00:06:28,380 --> 00:06:33,150 But you could absolutely tell it was designed by programmers with no thought for interface design. 99 00:06:33,150 --> 00:06:34,140 So it's a little awkward. 100 00:06:34,140 --> 00:06:36,300 There are better ones out there, but you have to pay for them. 101 00:06:37,380 --> 00:06:39,390 So I'm going to click on this little button right here. 102 00:06:39,390 --> 00:06:40,890 I'm going to add a new row. 103 00:06:40,890 --> 00:06:45,000 So I click that and it says ID is no, I'm going to leave that. 104 00:06:45,000 --> 00:06:50,010 No, because I want my serial data type to put the number one in there, because this will be my first 105 00:06:50,010 --> 00:06:50,400 record. 106 00:06:50,640 --> 00:06:55,530 But I'll put in dog as an animal name and then hit enter. 107 00:06:55,980 --> 00:07:00,510 And when I when I'm done with that, I come down to the bottom left hand corner here and say safe. 108 00:07:01,260 --> 00:07:02,730 So now I have one row of data. 109 00:07:02,820 --> 00:07:07,260 Let's go back to our SQL editor and run the same command again. 110 00:07:08,070 --> 00:07:09,840 And now it says, I have a dog. 111 00:07:09,960 --> 00:07:12,450 All right, let's go back to animals and add another type. 112 00:07:12,450 --> 00:07:19,080 So I'll add another row here, which I'll call Cat and I'll save that. 113 00:07:20,100 --> 00:07:24,030 And I'll add another row here by clicking on this little plus icon again. 114 00:07:24,900 --> 00:07:28,140 And I'll call this one a fish and I'll save it. 115 00:07:29,190 --> 00:07:29,640 All right. 116 00:07:30,000 --> 00:07:34,170 Now let's go back to our school editor and run this script again. 117 00:07:34,350 --> 00:07:35,760 Run this little command again. 118 00:07:35,770 --> 00:07:36,690 That's a bit of sequel. 119 00:07:37,320 --> 00:07:38,490 And now I have three rows. 120 00:07:38,760 --> 00:07:41,580 Now, I can actually change this a little bit. 121 00:07:41,760 --> 00:07:47,100 I can say select start from animals where the idea is greater than one. 122 00:07:47,850 --> 00:07:51,210 So this actually filters are select statement. 123 00:07:51,220 --> 00:07:54,720 So now I can select everything from animals where the idea is greater than one. 124 00:07:54,720 --> 00:07:57,180 So what should happen is they should give me cat and fish. 125 00:07:58,970 --> 00:08:07,880 And it does or I can say where ID equals to one and run it again and I should just get a dog and I do 126 00:08:08,360 --> 00:08:19,730 or I can say where name equals animal name is not a name, animal name equals and then add single quotes. 127 00:08:20,520 --> 00:08:22,930 I'm going to put lowercase cat see what happens. 128 00:08:26,380 --> 00:08:32,380 I get nothing, but if I go uppercase cat, I can run it and now I get Cat. 129 00:08:33,920 --> 00:08:35,990 So there is a way of selecting data. 130 00:08:36,110 --> 00:08:43,010 Now, we can also insert data into a table by saying insert into it, and then you give your table name, 131 00:08:43,010 --> 00:08:46,100 animal name or animals. 132 00:08:47,450 --> 00:08:53,720 And then in parentheses, I'm going to say animal name, which is the one row that I want to affect. 133 00:08:53,720 --> 00:09:00,440 I don't want to manually insert a value into it because I want my serial data type to handle that for 134 00:09:00,440 --> 00:09:00,650 me. 135 00:09:01,580 --> 00:09:09,980 Then I type values and then in parentheses I will put porc and in my command with the semicolon and 136 00:09:09,980 --> 00:09:10,400 run it. 137 00:09:12,370 --> 00:09:15,100 And it says query finish time in certain animals. 138 00:09:15,220 --> 00:09:21,210 Now let's go back up to here and change our query to select Star from Animals and run. 139 00:09:22,930 --> 00:09:27,310 And now I have a horse in there, so there's a way of inserting data into it. 140 00:09:27,320 --> 00:09:29,410 I can also delete information from it. 141 00:09:29,560 --> 00:09:30,910 I can say or update it. 142 00:09:30,910 --> 00:09:31,690 Lets them do the update. 143 00:09:31,690 --> 00:09:46,810 First update animals set animal name equal to German ship bird where I equals one. 144 00:09:47,200 --> 00:09:49,180 And you can see it's very similar to selected. 145 00:09:49,190 --> 00:09:53,710 You can have this where clause that allows you to specify what you want to effect. 146 00:09:54,010 --> 00:09:59,380 I only want to effect ID one which currently is set to dog under animal name. 147 00:09:59,620 --> 00:10:04,780 I want to change its name animal name from what it is to German Shepherd. 148 00:10:04,900 --> 00:10:05,620 Let's run that. 149 00:10:07,790 --> 00:10:14,790 And now let's go back and select a star from Animals and enter that and there we are. 150 00:10:14,900 --> 00:10:18,970 So German Shepherd has been updated, dog has been changed to German Shepherd. 151 00:10:19,520 --> 00:10:29,420 I can also change the I can delete an individual record, but by saying delete from the table name animals 152 00:10:29,990 --> 00:10:35,360 where ID equals four and that will delete the harsh record. 153 00:10:35,360 --> 00:10:41,510 So I run that and go back and select star again by clicking in that line. 154 00:10:41,510 --> 00:10:44,350 I want to run and there are horses gone. 155 00:10:44,750 --> 00:10:50,840 So now we can create a table, we can insert information, we can update information, we can delete 156 00:10:50,840 --> 00:10:53,540 information and we can select information. 157 00:10:53,810 --> 00:10:58,460 And that is the basic principle for all of the skill commands we're going to be doing. 158 00:10:58,490 --> 00:11:02,480 Now, some of our queries will get more and a query is just selecting information. 159 00:11:02,660 --> 00:11:07,400 Some of our queries will get more sophisticated as time goes on because we'll be able to select information 160 00:11:07,400 --> 00:11:12,110 from multiple tables or a subset of information from a given table. 161 00:11:12,560 --> 00:11:14,360 But this is the basic principle. 162 00:11:14,360 --> 00:11:19,640 It's called CRUD, which is a rather unfortunate acronym, but it stands for Create, Read, Update 163 00:11:19,640 --> 00:11:20,210 and delete. 164 00:11:20,690 --> 00:11:25,670 We can create information, read information, update information and delete information. 165 00:11:26,030 --> 00:11:28,400 And that is really not that difficult. 166 00:11:28,400 --> 00:11:35,510 Now, SQL is an extremely powerful language and it would be a course in itself to go through it in incredible 167 00:11:35,510 --> 00:11:35,990 detail. 168 00:11:36,230 --> 00:11:41,060 But we're going to learn enough to do everything we need to do to make our Web application work. 169 00:11:41,360 --> 00:11:47,180 So when you think about it, we're going to have to have a table for, say, reservations to persist 170 00:11:47,180 --> 00:11:48,920 information to the database. 171 00:11:48,920 --> 00:11:54,320 When people fill out the reservation form, we're going to have to have some way in the database of 172 00:11:54,320 --> 00:11:57,920 storing information for individual reservations by date. 173 00:11:58,220 --> 00:12:05,450 So we'll be able to ask the database, hey, is the date, say, January the 1st to January the 3rd? 174 00:12:05,570 --> 00:12:08,090 Is that available for the general quarters room? 175 00:12:08,420 --> 00:12:10,160 And that's really not that difficult. 176 00:12:10,310 --> 00:12:12,860 So we'll start this process in a little while. 177 00:12:12,860 --> 00:12:18,050 But right now I want to get out, get used to using a database, and then we'll have to go through the 178 00:12:18,050 --> 00:12:23,870 process of connecting it to a Web application and writing all the procedures to manipulate the data 179 00:12:23,900 --> 00:12:24,860 as required. 180 00:12:25,970 --> 00:12:26,330 All right. 181 00:12:26,330 --> 00:12:27,680 That's enough for this time around. 182 00:12:27,920 --> 00:12:28,700 Let's move on.