1
00:00:00,210 --> 00:00:00,690
All righty.

2
00:00:00,690 --> 00:00:02,390
Let's go through the solution.

3
00:00:02,400 --> 00:00:03,510
Spring cleaning.

4
00:00:03,510 --> 00:00:09,210
We're doing our closet inventory, and the first step was to create a brand new database called Shirts

5
00:00:09,210 --> 00:00:09,930
DB.

6
00:00:10,020 --> 00:00:16,500
So if we hop over to cloud nine, if we just check what database we're using, remember, we can do

7
00:00:16,500 --> 00:00:18,300
select database.

8
00:00:19,650 --> 00:00:20,760
We're using cat app.

9
00:00:20,760 --> 00:00:28,110
So we're going to make a new database, create database, and we wanted to call it shirts, underscore

10
00:00:28,110 --> 00:00:30,060
DB semicolon.

11
00:00:30,940 --> 00:00:36,940
Now if we do show databases, you can see we have shared SDB down at the bottom.

12
00:00:37,570 --> 00:00:38,140
All right.

13
00:00:38,140 --> 00:00:40,990
So the next step, we need to use shirts.

14
00:00:40,990 --> 00:00:43,840
DB Just like that.

15
00:00:45,190 --> 00:00:50,440
And now if we run that same select database line, we're currently using short SDB.

16
00:00:52,210 --> 00:00:55,690
So now this is probably the longest thing you'll have to type.

17
00:00:55,720 --> 00:00:58,120
We need to create a new table.

18
00:00:58,480 --> 00:01:05,650
We'll call it shirts, and it needs to have these five columns, shirt ID, which can not be null and

19
00:01:05,650 --> 00:01:12,130
as a primary key, which means that we'll have it auto increment as well article which should be var

20
00:01:12,130 --> 00:01:19,240
char and let's just for simplicity say all of these have 100 character limit color var char 100 shirt

21
00:01:19,240 --> 00:01:19,850
size.

22
00:01:19,870 --> 00:01:27,190
Yes, we could have kept it short, but maybe you might have a shirt size that's small spelled spelled

23
00:01:27,190 --> 00:01:29,470
out the full word rather than just a letter s.

24
00:01:29,470 --> 00:01:31,450
So we're not going to limit that either for now.

25
00:01:31,600 --> 00:01:33,580
And then last worn is an int.

26
00:01:33,940 --> 00:01:35,020
So let's try it.

27
00:01:35,500 --> 00:01:42,940
We'll do a create table shirts and I'm going to do this on a new line here.

28
00:01:44,800 --> 00:01:52,300
And the first thing that we have, let's start with shirt ID, which should be an integer, not null.

29
00:01:53,080 --> 00:01:55,300
Auto increment.

30
00:01:56,740 --> 00:02:02,770
Next, we're going to move on to article, which is a var cha 100.

31
00:02:04,130 --> 00:02:10,970
And then after that we have color, which is also far 100.

32
00:02:12,230 --> 00:02:18,620
Then we have shirt size, which is also var CHA 100.

33
00:02:18,950 --> 00:02:23,390
And finally, we have last worn, which is an int.

34
00:02:23,960 --> 00:02:24,950
Just like that.

35
00:02:24,950 --> 00:02:28,400
Then we'll close our parentheses and then we'll add our semicolon.

36
00:02:29,660 --> 00:02:33,260
And I forgot something which I did not do on purpose.

37
00:02:33,260 --> 00:02:38,720
But if we look at the message says incorrect table definition, there can be only one auto column and

38
00:02:38,720 --> 00:02:40,310
it must be defined as a key.

39
00:02:40,530 --> 00:02:45,860
And so I'm missing the primary key definition at the end, which this is actually a helpful error.

40
00:02:45,890 --> 00:02:50,510
Oftentimes these error messages aren't that useful, but this one tells us exactly what we did wrong.

41
00:02:50,510 --> 00:02:52,640
So I'm going to recall that line.

42
00:02:53,120 --> 00:02:57,560
And then at the end I'm going to add comma primary key.

43
00:02:57,950 --> 00:03:01,730
And then our primary key is shirt ID, just like that.

44
00:03:01,760 --> 00:03:07,640
Now, if I hit enter and you look at that, I left off the last parentheses again.

45
00:03:08,000 --> 00:03:09,320
So we have another error.

46
00:03:10,070 --> 00:03:12,110
So now if we hit enter here, it worked.

47
00:03:12,970 --> 00:03:17,800
If you'd like to see a nice cleaned up version of what this actually looks like here I have it here.

48
00:03:18,310 --> 00:03:22,900
So create table shirts, shirt ID, and not an old auto increment article.

49
00:03:22,960 --> 00:03:31,120
Cha cha cha cha cha cha shirt sized cha cha last worn int primary key shirt ID and the semicolon and

50
00:03:31,120 --> 00:03:32,980
the correct parentheses closing it out.

51
00:03:33,160 --> 00:03:34,600
And that should do it.

52
00:03:35,050 --> 00:03:35,740
All right.

53
00:03:35,740 --> 00:03:41,800
And if we wanted to double check that things worked, we can do our describe shirts and you can see

54
00:03:41,800 --> 00:03:43,150
we have what we expect.

55
00:03:43,780 --> 00:03:48,190
So the next thing is to get all of this data in our database.

56
00:03:48,310 --> 00:03:53,530
So I gave it to you as this comma separated list where things are all in the same format, where we

57
00:03:53,530 --> 00:03:59,470
have parentheses for one shirt, and then inside of that we have article first and then color and then

58
00:03:59,470 --> 00:04:02,200
size and then last worn.

59
00:04:02,380 --> 00:04:04,780
So we can insert using that format.

60
00:04:04,780 --> 00:04:12,850
So we'll do an insert into shirts and we have to specify we're doing article first, then we're doing

61
00:04:12,850 --> 00:04:20,500
color, then we're doing shirt size, and then finally we're doing last worn and then we need our values.

62
00:04:21,070 --> 00:04:27,310
And I'm just going to hit enter for now and I'm going to paste that giant list and we need a semicolon

63
00:04:27,310 --> 00:04:29,260
at the end and we hit enter.

64
00:04:30,420 --> 00:04:31,040
Good.

65
00:04:31,050 --> 00:04:33,160
No mistakes this time says records.

66
00:04:33,180 --> 00:04:35,070
Eight, eight rows affected.

67
00:04:35,590 --> 00:04:42,690
Okay so if we want to just to double check can do our select star from shirts and we get the data in

68
00:04:42,720 --> 00:04:43,980
there that we should have.

69
00:04:44,790 --> 00:04:48,030
And then the last thing we need to do is to add a new shirt.

70
00:04:48,030 --> 00:04:53,580
So manually, one shirt, rather than bulk inserting, it should be a purple polo shirt.

71
00:04:53,580 --> 00:04:56,790
That's medium that was last worn 50 days ago.

72
00:04:57,510 --> 00:05:02,610
So I didn't specify what order you needed to insert into, so I don't really care.

73
00:05:02,610 --> 00:05:09,570
But we'll do insert into shirts and we'll do color because that's how I specified here color, then

74
00:05:09,570 --> 00:05:12,180
article, then size and then last worn.

75
00:05:12,180 --> 00:05:21,480
So color article shirt size last worn like that values.

76
00:05:21,930 --> 00:05:23,670
And it's purple.

77
00:05:25,640 --> 00:05:26,690
Polo shirt.

78
00:05:28,700 --> 00:05:33,590
Comma, medium, comma, 50.

79
00:05:34,340 --> 00:05:35,330
Just like that.

80
00:05:36,250 --> 00:05:36,850
Okay.

81
00:05:36,920 --> 00:05:40,370
And if we do select star, we should see that new polo shirt down there.

82
00:05:40,370 --> 00:05:41,460
It's Purple Medium.

83
00:05:41,480 --> 00:05:42,950
50 days ago, we wore it.

84
00:05:43,520 --> 00:05:47,150
All right, so we've now wrapped up the create part of crud.

85
00:05:47,240 --> 00:05:51,470
We saw how to create the database we inserted, created a new table.

86
00:05:51,470 --> 00:05:53,140
We inserted data into the table.

87
00:05:53,150 --> 00:05:54,860
Now we're moving on to reading.