1
00:00:00,150 --> 00:00:02,940
It's time to get some more practice with crud.

2
00:00:02,969 --> 00:00:08,910
The basics of inserting and selecting and deleting and updating, and then also creating tables and

3
00:00:08,910 --> 00:00:09,990
dropping tables.

4
00:00:09,990 --> 00:00:14,750
So this is a larger exercise that will be broken up into a couple of videos.

5
00:00:14,760 --> 00:00:16,950
The scenario here, it's still very simple.

6
00:00:16,950 --> 00:00:21,690
We're not working with some real world complex data, but it's still a little more complicated than

7
00:00:21,690 --> 00:00:22,500
cats.

8
00:00:23,130 --> 00:00:24,960
We are spring cleaning.

9
00:00:24,960 --> 00:00:26,670
We're cleaning out our closet.

10
00:00:27,330 --> 00:00:32,070
Let's say that we've got a closet with lots of shirts in there, which is actually not really true in

11
00:00:32,070 --> 00:00:32,460
my case.

12
00:00:32,460 --> 00:00:35,010
I in desperate need of new shirts at the moment.

13
00:00:35,010 --> 00:00:38,310
But let's say that I've got a surplus or you have a surplus.

14
00:00:38,310 --> 00:00:44,580
We're going to create a database to keep track of all of our shirts like insane tech person would just

15
00:00:44,760 --> 00:00:49,380
for whatever reason, we have to just put everything into tech and into a database.

16
00:00:49,380 --> 00:00:52,860
So we're going to make a new database, not the pets database, a whole new one.

17
00:00:52,860 --> 00:00:54,870
We need to create it and use it called shirts.

18
00:00:54,870 --> 00:00:59,370
DB Then create a new table called shirts.

19
00:00:59,370 --> 00:01:02,370
Now this shirts table will look something like this.

20
00:01:02,370 --> 00:01:07,470
Obviously it will be empty when you make it, but it's going to have these columns, shirt ID, article,

21
00:01:07,470 --> 00:01:10,260
color, shirt size last worn.

22
00:01:10,680 --> 00:01:13,140
So shirt ID should be a primary key.

23
00:01:13,140 --> 00:01:14,430
It should auto increment.

24
00:01:14,430 --> 00:01:15,000
Whoops.

25
00:01:15,000 --> 00:01:17,550
Article is the type of shirt.

26
00:01:17,580 --> 00:01:19,590
Let's just say there's a handful of options.

27
00:01:19,590 --> 00:01:21,660
Tank top polo shirt, t shirt.

28
00:01:21,660 --> 00:01:23,130
Technically there's a bunch of others.

29
00:01:23,130 --> 00:01:28,590
Right Button up long sleeves, Hawaiian shirts, just long sleeve t shirts.

30
00:01:28,590 --> 00:01:29,340
It doesn't matter.

31
00:01:29,340 --> 00:01:30,330
It's just text.

32
00:01:30,330 --> 00:01:31,770
So we don't care about that.

33
00:01:31,770 --> 00:01:37,200
When we're creating the actual column, we just need to say it's text and come up with some reasonable

34
00:01:37,200 --> 00:01:38,880
limit for the number of characters.

35
00:01:38,970 --> 00:01:40,170
Then we have color.

36
00:01:40,200 --> 00:01:44,100
Another piece of text shirt size is a piece of text.

37
00:01:44,100 --> 00:01:49,530
Now eventually we'll learn a better option for this shirt size where it's just a single character always,

38
00:01:49,560 --> 00:01:51,090
although that's actually not true.

39
00:01:51,090 --> 00:01:52,590
What about extra large or extra?

40
00:01:52,590 --> 00:01:53,430
Extra large?

41
00:01:53,430 --> 00:01:56,610
That's not exactly a single character, so it can vary in length.

42
00:01:56,610 --> 00:01:58,550
So we're going to do VAR.

43
00:01:58,770 --> 00:02:02,370
That's all we know so far, but there might be a better option later on.

44
00:02:02,370 --> 00:02:04,050
And then last worn is an integer.

45
00:02:04,050 --> 00:02:07,950
It's a number how many days ago we last wore this shirt.

46
00:02:08,190 --> 00:02:12,180
So that's all you need to do when you're creating the table.

47
00:02:12,180 --> 00:02:14,640
Just make sure you have the primary key set up as well.

48
00:02:14,880 --> 00:02:18,570
And then I'd like you to take this batch of data I've given you.

49
00:02:18,600 --> 00:02:26,550
Copy it from the slides or from the code following this video and insert all of these rows in one go.

50
00:02:26,700 --> 00:02:30,990
So don't copy this line and do an insert with this and then an insert with this.

51
00:02:30,990 --> 00:02:32,100
Just insert all of it.

52
00:02:32,100 --> 00:02:37,530
I formatted it for you so that it could work, assuming that you write the correct insert into query.

53
00:02:38,290 --> 00:02:40,960
So once you've done that, we have a little bit of data in there.

54
00:02:40,990 --> 00:02:45,040
I'd like you to get a little more add in a new shirt that is purple.

55
00:02:45,070 --> 00:02:48,310
It's a polo shirt, size em or medium.

56
00:02:48,310 --> 00:02:51,010
And it was last worn 50 days ago.

57
00:02:52,130 --> 00:02:57,290
And then select all of the shirts from the entire database, or rather from the entire shirts table,

58
00:02:57,290 --> 00:03:00,650
but only print out their article and their color.

59
00:03:01,840 --> 00:03:06,280
A little bit more selecting Select all medium shirts, but don't print everything out.

60
00:03:06,280 --> 00:03:07,720
So we're not selecting star.

61
00:03:07,750 --> 00:03:11,350
Print out everything except the shirt ID.

62
00:03:11,470 --> 00:03:14,140
So almost everything, but not exactly.

63
00:03:14,710 --> 00:03:17,290
Then I'd like you to work on updating things.

64
00:03:17,320 --> 00:03:19,120
Update all polo shirts.

65
00:03:19,120 --> 00:03:21,940
For whatever reason, their sizing is wrong in our database.

66
00:03:21,940 --> 00:03:24,250
All of them need to be sized large.

67
00:03:25,120 --> 00:03:32,320
And then update the shirt that was last worn 15 days ago and change its last 1 to 0, meaning we just

68
00:03:32,320 --> 00:03:33,070
wore it today.

69
00:03:33,070 --> 00:03:35,680
So last worn is zero days ago.

70
00:03:36,650 --> 00:03:42,980
And then update all the shirts that have a color of white and change their size to be extra small and

71
00:03:42,980 --> 00:03:45,320
the color to be off white.

72
00:03:46,250 --> 00:03:47,390
Very important stuff.

73
00:03:47,480 --> 00:03:53,720
Now we move on to deletion, delete all the old shirts, which will say is any shirt that was last worn

74
00:03:53,720 --> 00:03:58,310
exactly 200 days ago and then delete all the tank tops.

75
00:03:58,340 --> 00:03:59,740
Our tastes have changed.

76
00:03:59,750 --> 00:04:02,340
We're now a 50 year old man.

77
00:04:02,360 --> 00:04:08,300
It's not a good look to be wearing a tank top around town, which if you do, I'm sure you pull it off.

78
00:04:08,300 --> 00:04:11,600
But in my hypothetical 50 year old man does not pull it off.

79
00:04:11,600 --> 00:04:17,779
So we're deleting all tank tops and then we're deleting all shirts, regardless of type or color or

80
00:04:17,779 --> 00:04:18,380
size.

81
00:04:18,380 --> 00:04:24,800
Just delete all of them, empty the table out and then finally drop the entire shirts table.

82
00:04:24,830 --> 00:04:25,670
Oh, no.

83
00:04:25,700 --> 00:04:26,660
Maybe it was an accident.

84
00:04:26,660 --> 00:04:27,230
Maybe not.

85
00:04:27,230 --> 00:04:30,080
But let's say we want to drop the entire search table.

86
00:04:30,080 --> 00:04:31,130
It's part of the exercise.

87
00:04:31,130 --> 00:04:32,210
Just follow the instructions.

88
00:04:32,210 --> 00:04:36,350
There's no rational explanation why, and that is it.

89
00:04:36,350 --> 00:04:38,060
So let's go ahead and.

90
00:04:38,300 --> 00:04:41,450
Well, I hope you pause the video or open up the slides.

91
00:04:41,480 --> 00:04:42,980
There's a lot of pieces to this.

92
00:04:42,980 --> 00:04:45,860
So go through each one of these one step at a time.

93
00:04:46,220 --> 00:04:48,650
First, of course, create the table.

94
00:04:48,920 --> 00:04:49,910
Actually, that's a lie.

95
00:04:49,910 --> 00:04:54,320
First, create the database and then work with that database and create the table.

96
00:04:54,350 --> 00:04:55,790
And I'll be back with the solution.

97
00:04:55,790 --> 00:05:00,500
I'm going to break it up into a couple of videos just so I don't have like a ten, 15 minute solution

98
00:05:00,500 --> 00:05:01,070
walk through.

99
00:05:01,070 --> 00:05:08,960
But I, I pray, I plead with you that you go through and do this just to get some practice and it will

100
00:05:08,960 --> 00:05:09,590
be quick.

101
00:05:09,830 --> 00:05:10,490
All right.

102
00:05:10,490 --> 00:05:11,630
I'll see you on the other side.