1 00:00:00,150 --> 00:00:00,540 All right. 2 00:00:00,540 --> 00:00:05,939 In this part of the solution, I want to walk through setting up our database and creating the table. 3 00:00:06,300 --> 00:00:10,020 So the first thing we'll do is make a new database called Shirts DB. 4 00:00:10,260 --> 00:00:15,240 Now, we don't actually have to do this all as one step at a time. 5 00:00:15,390 --> 00:00:19,380 You know, I don't have to create the the database and then use it and then create my table. 6 00:00:19,410 --> 00:00:25,200 If I want to work in a script, I can and I can do all these things at once and run the entire script. 7 00:00:25,200 --> 00:00:26,610 So I'll show that here. 8 00:00:26,610 --> 00:00:29,010 But it doesn't really make a difference. 9 00:00:29,250 --> 00:00:34,230 If you want to just run these in your shell one line at a time, that's also totally valid. 10 00:00:34,230 --> 00:00:40,350 But what I'm going to do is create database and I think I called it shirts underscore DB. 11 00:00:43,330 --> 00:00:44,110 Like that. 12 00:00:44,680 --> 00:00:47,470 And then I'm going to use that database. 13 00:00:47,470 --> 00:00:49,210 So I didn't even tell you about that part. 14 00:00:49,240 --> 00:00:50,830 You're supposed to remember that. 15 00:00:51,430 --> 00:00:56,530 Otherwise, we may not be creating the shirts table in the correct database. 16 00:00:56,650 --> 00:01:02,140 If I just created a table here, create table shirts. 17 00:01:02,890 --> 00:01:06,450 This table is not guaranteed to be in our shirts. 18 00:01:06,460 --> 00:01:07,180 DB. 19 00:01:07,450 --> 00:01:11,020 It just depends on whatever database I'm using at that time. 20 00:01:11,260 --> 00:01:14,880 We can create a database from anywhere, but we don't automatically use it. 21 00:01:14,890 --> 00:01:17,500 So I would then say use shirts. 22 00:01:17,530 --> 00:01:18,220 DB. 23 00:01:19,100 --> 00:01:25,070 Or of course, if I'm using the guy here, I could after I create the database, I could refresh my 24 00:01:25,070 --> 00:01:29,660 view and then double click and find that database and use it using the graphical way. 25 00:01:29,660 --> 00:01:31,760 Like I'm using chicken coop right now. 26 00:01:32,700 --> 00:01:39,180 I'm using pet shop right now, but this is a foolproof way and this is the the actual command that I 27 00:01:39,180 --> 00:01:39,980 want you to know. 28 00:01:39,990 --> 00:01:42,450 I don't want you to rely only on the interface. 29 00:01:43,050 --> 00:01:45,120 So create our table is the next step. 30 00:01:45,240 --> 00:01:49,830 The first thing we have is our shirt ID, which is the primary key. 31 00:01:49,830 --> 00:01:50,730 It's an integer. 32 00:01:50,730 --> 00:01:52,260 So let's call that shirt. 33 00:01:52,260 --> 00:01:56,040 ID or ID is fine too, but let's just follow the slides. 34 00:01:56,040 --> 00:02:04,950 Shirt ID is an int auto increment and I'll do primary key on the same line again. 35 00:02:04,950 --> 00:02:06,750 I can do that after the fact as well. 36 00:02:06,750 --> 00:02:09,000 I can add that constraint, but I'll leave it like this. 37 00:02:09,000 --> 00:02:13,620 Then we have article and I didn't specify if any of these are knowable or not. 38 00:02:14,070 --> 00:02:20,370 Let's just keep it simple and say that everything can be null of course, except the shirt id, so article 39 00:02:20,370 --> 00:02:29,490 will be a var article, var cha var car and that will be, I don't know, let's just say 50 characters 40 00:02:30,540 --> 00:02:32,910 and then we have color. 41 00:02:33,300 --> 00:02:37,020 I believe the order doesn't matter, but color is of our car shirt. 42 00:02:37,020 --> 00:02:38,340 Sizes of our car. 43 00:02:41,990 --> 00:02:43,760 Let's say, size. 44 00:02:45,760 --> 00:02:46,150 Is it? 45 00:02:46,150 --> 00:02:47,350 Shirt, underscore size. 46 00:02:47,380 --> 00:02:47,740 All right. 47 00:02:47,740 --> 00:02:49,240 Well, be specific. 48 00:02:50,600 --> 00:02:54,450 And we could limit this and say it's five characters at most, right? 49 00:02:54,470 --> 00:03:00,100 Because we would have small, medium, large, extra, extra large, extra, extra, extra, large. 50 00:03:00,110 --> 00:03:02,500 Do we want to support this many XL's? 51 00:03:02,510 --> 00:03:03,410 Probably not. 52 00:03:03,410 --> 00:03:06,440 But what about do we want to support the word medium? 53 00:03:06,440 --> 00:03:08,080 That's more than five characters. 54 00:03:08,090 --> 00:03:10,010 It just depends on how we structure things. 55 00:03:10,010 --> 00:03:11,660 So I'll keep it short here. 56 00:03:11,660 --> 00:03:12,380 Why not? 57 00:03:12,380 --> 00:03:16,160 And then we have last worn, which is an integer, right? 58 00:03:16,160 --> 00:03:19,600 It's a number of days ago we wore the shirt OC. 59 00:03:19,910 --> 00:03:27,620 So if we run this query or really queries the entire script, we're we're going to create an empty database, 60 00:03:27,620 --> 00:03:31,670 use that empty database, and then inside of there, create the shirts table. 61 00:03:31,670 --> 00:03:38,780 And why don't we do a describe shirts afterwards and just make sure we see something printed out here. 62 00:03:38,780 --> 00:03:39,920 We'll know that it worked. 63 00:03:40,070 --> 00:03:42,080 So I'm going to run the entire script. 64 00:03:42,080 --> 00:03:45,320 If you're in workbench, you do it using that lightning bolt here. 65 00:03:45,320 --> 00:03:50,300 This one to the right there, so tiny, but this one to the right only runs where your cursor is. 66 00:03:50,300 --> 00:03:52,010 This will run the entire file. 67 00:03:52,010 --> 00:03:55,970 But remember, you can do this one line at a time directly in the shell. 68 00:03:55,970 --> 00:03:56,870 It doesn't matter. 69 00:03:56,900 --> 00:03:58,820 Okay, Let's see if I have any errors. 70 00:03:59,570 --> 00:04:00,410 It looks good. 71 00:04:00,500 --> 00:04:04,760 And we get back our results from describe at the bottom and we have shirt. 72 00:04:04,760 --> 00:04:05,810 ID is an integer. 73 00:04:05,840 --> 00:04:06,590 Can't be null. 74 00:04:06,590 --> 00:04:07,730 It's a primary key. 75 00:04:07,730 --> 00:04:12,380 Let's go over here and remember how we can tell what database we're using. 76 00:04:12,380 --> 00:04:13,760 Select database. 77 00:04:13,760 --> 00:04:15,620 We're still using pet shop over here. 78 00:04:15,710 --> 00:04:20,089 If I do show databases, we should see shirt or shirts. 79 00:04:20,089 --> 00:04:21,920 DB Let's use it. 80 00:04:23,510 --> 00:04:27,170 And of course now if I select database, we see we're using shirts. 81 00:04:27,170 --> 00:04:30,890 DB Let's do a show tables Great. 82 00:04:30,890 --> 00:04:32,480 We see the shirts table. 83 00:04:32,510 --> 00:04:34,610 Let's describe the shirts table. 84 00:04:34,640 --> 00:04:35,480 There we are. 85 00:04:35,960 --> 00:04:36,560 All right. 86 00:04:36,560 --> 00:04:40,490 So the next thing I'd like to do is get our basic data in there. 87 00:04:40,490 --> 00:04:42,020 So I gave you this. 88 00:04:42,020 --> 00:04:47,750 If you copy it from the slides or from my code, what we want to do is take that and insert all of those 89 00:04:47,750 --> 00:04:49,130 rows at once. 90 00:04:49,460 --> 00:04:56,330 So I'm going to do this from a script again, but I don't want to put it in here and then run all of 91 00:04:56,330 --> 00:05:01,550 this again, because I would create the database again, I would use it, I'd create the table again. 92 00:05:01,550 --> 00:05:05,780 I'd run into errors if I try and do that, but I don't want to run all of that code again. 93 00:05:05,780 --> 00:05:09,170 So I'm going to select it for now and comment it out. 94 00:05:09,170 --> 00:05:12,170 So again, for me, that's command forward slash. 95 00:05:12,170 --> 00:05:13,310 So it's still here. 96 00:05:13,310 --> 00:05:19,490 So we have a record of it, but now if I run this entire file, this is not treated as code, it's completely 97 00:05:19,490 --> 00:05:20,210 ignored. 98 00:05:21,170 --> 00:05:23,450 So this is not valid code either, right? 99 00:05:23,450 --> 00:05:25,220 This is going to be our values list. 100 00:05:25,220 --> 00:05:32,090 But I need to do an insert into shirts and then I have to be clear on the order here. 101 00:05:32,090 --> 00:05:37,760 So we have the article first, then the color and then the size and then the last worn. 102 00:05:37,760 --> 00:05:41,600 So article, color, shirt to size. 103 00:05:41,600 --> 00:05:51,680 I have to match the column names that I created and last worn, and then I'll have my values just like 104 00:05:51,680 --> 00:05:52,130 that. 105 00:05:52,130 --> 00:05:55,220 And I could keep this on its own line if I wanted to. 106 00:05:55,220 --> 00:05:56,450 I could put that up there. 107 00:05:56,450 --> 00:05:57,860 I could indent it all. 108 00:05:58,040 --> 00:06:00,320 It looks a little janky as it is, but it's fine. 109 00:06:00,320 --> 00:06:03,980 I copied and pasted it, so it's fine, but I'm going to put my semicolon in there. 110 00:06:04,340 --> 00:06:06,770 Make sure this works just before I run it. 111 00:06:06,770 --> 00:06:08,660 Make sure it seems like it should work. 112 00:06:08,660 --> 00:06:10,580 Insert into shirts articles. 113 00:06:10,580 --> 00:06:14,510 First color is next, shirt size and then last worn. 114 00:06:14,510 --> 00:06:15,380 Let's try it. 115 00:06:15,380 --> 00:06:16,280 Run the script. 116 00:06:17,930 --> 00:06:23,930 Okay, let's head over here and let's just end the video with a simple select star from shirts. 117 00:06:23,930 --> 00:06:25,430 Do we see H shirts? 118 00:06:25,430 --> 00:06:26,420 Yes, we do. 119 00:06:26,420 --> 00:06:28,150 And does the data make sense? 120 00:06:28,160 --> 00:06:32,600 In other words, do we have colors showing up in shirt size or size? 121 00:06:32,600 --> 00:06:33,790 Is showing up in article? 122 00:06:33,800 --> 00:06:34,550 No, we don't. 123 00:06:34,550 --> 00:06:36,560 It's working the way we expected it. 124 00:06:36,860 --> 00:06:42,470 So now that we have our data in, the next thing we're going to do is insert a new shirt. 125 00:06:42,470 --> 00:06:47,330 So I guess this is really the last thing that I want to do in this video, because then we move on to 126 00:06:47,630 --> 00:06:49,190 reading and selecting. 127 00:06:49,190 --> 00:06:52,100 But we've got one more part of the creating part of CRUD. 128 00:06:52,100 --> 00:06:59,960 Add a new shirt, purple polo shirt, medium last worn 50 days ago, and maybe I'll do this one directly 129 00:06:59,960 --> 00:07:09,140 here in the shell insert into shirts and we're going to have article color, shirt size last worn. 130 00:07:10,480 --> 00:07:12,010 And then values. 131 00:07:12,400 --> 00:07:15,250 And it was polo shirt, I think. 132 00:07:16,840 --> 00:07:18,280 And then I don't remember. 133 00:07:18,280 --> 00:07:19,030 Is there a dash? 134 00:07:19,060 --> 00:07:19,520 No. 135 00:07:19,540 --> 00:07:21,790 For whatever reason, polo shirt has a space. 136 00:07:21,790 --> 00:07:26,500 If I'm just matching what I already have, which will be nice, because then I can select all polo shirts. 137 00:07:27,250 --> 00:07:36,010 We have purple as the colour medium and last worn 50 days ago, so that should do it. 138 00:07:36,010 --> 00:07:40,150 That's just double check polo shirt, purple m 50 polo shirts. 139 00:07:40,300 --> 00:07:41,200 Well, purple. 140 00:07:41,200 --> 00:07:41,770 There we are. 141 00:07:41,770 --> 00:07:44,200 M 50 OC. 142 00:07:44,650 --> 00:07:46,870 Let's select Star and make sure it's in there. 143 00:07:47,860 --> 00:07:48,940 And there it is. 144 00:07:49,180 --> 00:07:54,580 OC So all that I'm going to do is just copy that line that I ran and put it over here so that we have 145 00:07:54,580 --> 00:07:56,340 a full record of everything we've done. 146 00:07:56,350 --> 00:07:57,910 I have to get rid of this arrow though. 147 00:07:58,600 --> 00:07:59,890 But you don't have to do that, right? 148 00:07:59,890 --> 00:08:03,250 It's it's totally up to you where you run these individual queries. 149 00:08:03,250 --> 00:08:07,870 From next step, we'll move on to the selecting part of this exercise solution.