1
00:00:00,180 --> 00:00:00,750
All right.

2
00:00:00,750 --> 00:00:05,430
So we've seen the basic components of crud creating, reading, updating and deleting.

3
00:00:05,430 --> 00:00:10,890
You've learned how to do all of those operations and SQL, and now it's time for the super ultra hyper

4
00:00:10,890 --> 00:00:12,570
mega crud exercise.

5
00:00:13,040 --> 00:00:14,850
Actually, let me try that one more time.

6
00:00:14,940 --> 00:00:18,130
It's time for the super duper ultra high five.

7
00:00:18,410 --> 00:00:19,170
Meghan, Meghan, Meghan.

8
00:00:21,330 --> 00:00:24,240
It's only been half a second, but I already regret that.

9
00:00:24,390 --> 00:00:25,470
So let's move on.

10
00:00:25,680 --> 00:00:30,390
So the idea here is that you're going to get to put all the different things you've learned to use.

11
00:00:30,390 --> 00:00:33,090
So this will be one exercise in this case.

12
00:00:33,090 --> 00:00:35,910
We're working on closet inventory.

13
00:00:36,000 --> 00:00:36,840
Super exciting.

14
00:00:36,840 --> 00:00:37,380
I know.

15
00:00:38,160 --> 00:00:45,060
So basically the idea is that I or you or somebody has a bunch of clothing in their closet that they

16
00:00:45,060 --> 00:00:48,240
want to go through in inventory and figure out sizes and colors.

17
00:00:48,240 --> 00:00:49,710
And we're just focusing on shirts.

18
00:00:49,710 --> 00:00:54,180
So different types of shirts, tank tops, polo shirts, t shirts and so on.

19
00:00:54,180 --> 00:00:58,170
And we can go through and we'll just perform some basic operations on them.

20
00:00:58,440 --> 00:01:04,800
I do want to add a note that I know that up until now our data has been sort of trivial and this this

21
00:01:04,800 --> 00:01:08,850
included t shirts is not that exciting or that realistic really.

22
00:01:08,850 --> 00:01:14,880
But we still don't have the pieces to go into complex real world data that you would see in a web app.

23
00:01:15,270 --> 00:01:16,560
But we will get there.

24
00:01:16,560 --> 00:01:18,180
And so hold tight.

25
00:01:18,510 --> 00:01:23,550
I promise we'll be seeing a lot of real world potentially more exciting, definitely more challenging

26
00:01:23,550 --> 00:01:24,120
data.

27
00:01:24,120 --> 00:01:28,710
So we're working with spring cleaning and you're going to have a bunch of operations to do, and the

28
00:01:28,710 --> 00:01:31,680
first one is to create a brand new database.

29
00:01:31,680 --> 00:01:33,810
So think back to how we did that.

30
00:01:33,810 --> 00:01:35,580
No more working in the CAT database.

31
00:01:35,580 --> 00:01:37,770
We need a new one called Shirts DB.

32
00:01:38,570 --> 00:01:39,800
So create that.

33
00:01:40,100 --> 00:01:44,240
And then the next step is to create a new table called shirts.

34
00:01:44,480 --> 00:01:47,090
And to do that, you need a little more information.

35
00:01:47,090 --> 00:01:49,250
You need to know what does that table look like?

36
00:01:49,250 --> 00:01:50,300
So here you go.

37
00:01:50,810 --> 00:01:52,880
Here's a graphic representation.

38
00:01:53,120 --> 00:01:58,850
We've got a shirt ID which can not be null and is a primary key.

39
00:01:59,360 --> 00:02:04,220
We have article which should be something like t shirt or polo shirt or tank top.

40
00:02:04,310 --> 00:02:05,210
It's text.

41
00:02:05,480 --> 00:02:10,669
Then we have the color of the shirt, which is also text white, green, black, blue, and so on.

42
00:02:11,090 --> 00:02:16,100
We have the shirt size in this case, smalls and mediums text.

43
00:02:16,250 --> 00:02:17,600
And then we have last.

44
00:02:17,600 --> 00:02:22,220
Oh, actually, before I move on, I do want to call your attention to the fact that I did shirt underscore

45
00:02:22,250 --> 00:02:24,290
size rather than just size.

46
00:02:24,470 --> 00:02:28,460
And that's because size is a bit of a reserved word.

47
00:02:28,730 --> 00:02:35,390
So you can still use it, but it's a word that my school uses internally size and so it's better to

48
00:02:35,390 --> 00:02:37,220
avoid any conflicts there.

49
00:02:37,220 --> 00:02:38,750
So I just want my shirt size.

50
00:02:39,290 --> 00:02:46,220
And then last worn is a number and that refers to roughly how many days ago the shirt was last worn.

51
00:02:46,400 --> 00:02:49,280
So the idea is that we would inventory our closet.

52
00:02:49,280 --> 00:02:53,780
Maybe you do this, maybe you don't, but you go through and you figure out what you don't wear all

53
00:02:53,780 --> 00:03:00,710
that often and then either sell it or donate it or, I don't know, make tie dye with it, whatever

54
00:03:00,710 --> 00:03:01,250
you do.

55
00:03:01,370 --> 00:03:05,300
So that's what we're trying to calculate or what we're trying to store here.

56
00:03:05,300 --> 00:03:06,830
And it's just the number for now.

57
00:03:06,950 --> 00:03:08,450
So create that table.

58
00:03:08,450 --> 00:03:13,130
It should be called shirts, and once you do that, you can move on to the next step, which is getting

59
00:03:13,130 --> 00:03:14,510
the starter data in there.

60
00:03:14,750 --> 00:03:20,270
So I want all this data in the table, but I don't want you to have to type it up yourself.

61
00:03:20,270 --> 00:03:23,090
It can be obnoxious to insert that manually.

62
00:03:23,630 --> 00:03:30,080
So I've given you a basically a starter seed data that contains the exact same thing.

63
00:03:30,080 --> 00:03:33,350
So a white t shirt that's small with ten days ago.

64
00:03:33,350 --> 00:03:39,050
So you can see it's the same as this first shirt here and I've done it for all eight so you could copy

65
00:03:39,050 --> 00:03:39,590
this.

66
00:03:39,590 --> 00:03:41,960
It's in the text of this video as well.

67
00:03:41,960 --> 00:03:45,200
And then what you could do is just insert it all at once.

68
00:03:45,200 --> 00:03:47,170
So remember how to do a multi insert.

69
00:03:47,360 --> 00:03:49,820
Get all that data in there with a single line.

70
00:03:50,950 --> 00:03:54,990
And then the next step is to add a single new shirt.

71
00:03:55,000 --> 00:03:59,870
So insert something manually, not using a multi insert, but just one at a time.

72
00:03:59,890 --> 00:04:04,630
A purple polo shirt that's medium that was last worn 50 days ago.

73
00:04:05,860 --> 00:04:06,520
All right.

74
00:04:06,610 --> 00:04:10,900
So then once we have those nine shirts in there, we can now start to do some stuff with them.

75
00:04:12,110 --> 00:04:18,560
First thing we'll do is select all shirts, but only print out article and color.

76
00:04:18,560 --> 00:04:22,390
So we should only see t shirt purple, polo shirt, black.

77
00:04:22,400 --> 00:04:26,690
We don't want to see ID, we don't want to see size or last worn.

78
00:04:27,110 --> 00:04:31,910
Then once you do that, the next step is to select all medium shirts.

79
00:04:31,910 --> 00:04:37,850
So all shirts that have size of medium and print out everything but the shirt ID.

80
00:04:38,060 --> 00:04:46,430
So we want all of this here article color shirt size, last one, but not this, but only for the mediums.

81
00:04:48,590 --> 00:04:51,650
Then next up, we're moving on to update.

82
00:04:51,650 --> 00:04:56,930
So we've done the create and the read we're working on update now, which is update all polo shirts

83
00:04:56,930 --> 00:04:59,210
to be size large maybe.

84
00:04:59,210 --> 00:04:59,630
I don't know.

85
00:04:59,630 --> 00:05:04,760
You realize that the company that makes your polo shirts, it might say medium on them, but in reality

86
00:05:04,760 --> 00:05:05,780
they're pretty large.

87
00:05:05,780 --> 00:05:07,730
And so you want to update your database.

88
00:05:07,880 --> 00:05:08,870
It's a dumb story.

89
00:05:08,870 --> 00:05:10,840
Just let's just go with it.

90
00:05:10,850 --> 00:05:13,190
So change their size to large.

91
00:05:13,190 --> 00:05:15,950
So that's where article equals polo shirt.

92
00:05:16,970 --> 00:05:24,020
Next up, update the shirt that was last worn 15 days ago and changed last 1 to 0.

93
00:05:24,620 --> 00:05:30,440
So this would mean we there's a shirt that was worn 15 days ago that we decided to wear today.

94
00:05:30,440 --> 00:05:34,850
So we're going to change the database to say last worn is now zero.

95
00:05:35,530 --> 00:05:39,100
So once you do that, now our final update.

96
00:05:39,580 --> 00:05:41,190
We had a bit of a mishap.

97
00:05:41,200 --> 00:05:42,460
We were doing laundry.

98
00:05:42,640 --> 00:05:49,690
We took all the white shirts and we watched them with, I don't know, some colored stuff that we shouldn't

99
00:05:49,690 --> 00:05:52,600
have, and we shrank them at the same time.

100
00:05:52,780 --> 00:05:55,060
So we have a double update here.

101
00:05:55,330 --> 00:06:01,750
You need to update all the white shirts to now be an extra small size and their color is no longer white,

102
00:06:01,750 --> 00:06:08,290
but it should be off white, so you'd need to update both of those at once with one line, just to be

103
00:06:08,290 --> 00:06:08,770
clear.

104
00:06:09,280 --> 00:06:09,910
All right.

105
00:06:10,300 --> 00:06:11,980
Next, we're deleting.

106
00:06:12,190 --> 00:06:14,140
So delete all old shirts.

107
00:06:14,140 --> 00:06:21,370
And in our case, all just means if it was worn 200 days ago or last one, 200 days ago, we will see

108
00:06:21,370 --> 00:06:27,580
very shortly how you can do things like delete all old shirts that have been last worn greater than

109
00:06:27,580 --> 00:06:28,660
50 days ago.

110
00:06:28,660 --> 00:06:31,660
But for now, we're doing exactly 200 days.

111
00:06:32,590 --> 00:06:35,110
Next up, our taste has changed.

112
00:06:35,110 --> 00:06:38,100
We've decided we no longer like wearing tank tops.

113
00:06:38,110 --> 00:06:40,570
We don't like the person that it turns us into.

114
00:06:40,600 --> 00:06:43,540
So we're going to delete all tank tops from our database.

115
00:06:43,810 --> 00:06:47,800
So delete all data, delete all shirts where article is tank top.

116
00:06:49,360 --> 00:06:52,360
Then finally, catastrophe strikes.

117
00:06:52,360 --> 00:06:54,250
We have to delete all shirts.

118
00:06:54,340 --> 00:06:58,680
I'll let you decide why, but we need to delete all the shirts.

119
00:06:58,690 --> 00:07:01,420
That doesn't mean delete the table or the database.

120
00:07:01,420 --> 00:07:04,660
Just delete the shirts in the table so it should be an empty table.

121
00:07:05,290 --> 00:07:08,680
Then finally drop the entire shirts table.

122
00:07:08,710 --> 00:07:10,270
Another catastrophe.

123
00:07:10,510 --> 00:07:12,850
So it should be an empty table at this point.

124
00:07:12,880 --> 00:07:15,340
Drop it entirely and that's it.

125
00:07:15,730 --> 00:07:17,530
So go through all those steps.

126
00:07:17,530 --> 00:07:22,540
I recommend just pausing the video as you go, make sure you do them and then if you'd like, watch

127
00:07:22,540 --> 00:07:25,540
the solution to see how to do it or how I did it.

128
00:07:25,930 --> 00:07:26,470
Okay.