WEBVTT

00:00.160 --> 00:02.380
All right let's go through the solution.

00:02.580 --> 00:08.640
Spring cleaning or doing our cause inventory and the first step was to create a brand new database called

00:08.700 --> 00:10.180
Schertz the B.

00:10.200 --> 00:18.030
So if we hop over to cloud 9 if we just check what database we're using remember we can do select database

00:19.660 --> 00:27.300
we're using cat app so we're going to make a new database create a database and we want to call it Schertz

00:27.540 --> 00:31.040
underscored TB semi-colon.

00:31.060 --> 00:37.620
Now if we do show databases you can see we have Schertz DBM at the bottom.

00:37.630 --> 00:38.120
All right.

00:38.170 --> 00:45.000
So the next step we need to use Schertz DB Just like that.

00:45.190 --> 00:49.510
And now if we run that same select database line we're currently using.

00:49.510 --> 00:50.760
Sure Stevie.

00:51.360 --> 00:55.900
OK so now this is probably the longest thing you'll have to type.

00:55.900 --> 01:03.790
We need to create a new table we'll call it shirts and it needs to have these five columns shirt ID

01:03.880 --> 01:09.860
which cannot be no and is a primary key which means that we'll have it autoincrement as well.

01:10.270 --> 01:12.670
Article which should be far Chaar.

01:12.760 --> 01:19.810
And let's just for simplicity say all of these have 100 character limit color Vardar 100 shirt size.

01:20.050 --> 01:27.160
Yes we could you kept it short but maybe you might have a shirt size that's small spell that spelled

01:27.160 --> 01:29.500
out the full word rather than just the letter S.

01:29.590 --> 01:31.450
So we're not going to limit that either for now.

01:31.780 --> 01:34.060
And then last worn isn't it.

01:34.060 --> 01:35.390
So let's try it.

01:35.530 --> 01:42.920
We'll do a create table shirts and I'm going to do this on line here.

01:44.910 --> 01:55.170
And the first thing that we have let's start with shirt ID which should be an integer not all auto increment.

01:56.830 --> 02:04.010
Next we're going to move on to article which is a Vardar 100.

02:04.200 --> 02:12.320
And then after that we have color which is also for our char 100.

02:12.330 --> 02:18.950
Then we have shirt size which is also Vardar 100.

02:18.990 --> 02:27.250
And finally we have last worne which isn't just like that and we'll close our parentheses and then we'll

02:27.240 --> 02:28.810
add our semi-colon.

02:29.730 --> 02:35.700
And I forgot something which I did not do on purpose but if you look at the message says incorrect table

02:35.700 --> 02:40.450
definition there can be only one autocall column and it must be defined as a key.

02:40.490 --> 02:45.870
And so I'm missing the primary key definition at the end which this is actually a helpful error.

02:45.870 --> 02:50.520
Often times these error messages aren't that useful but this one tells us exactly what we did wrong.

02:50.550 --> 02:59.100
So I'm going to recall that line and then at the end to add comma primary key then our primary key is

02:59.120 --> 03:01.950
shirt Heidi just like that.

03:01.950 --> 03:07.760
Now if I hit enter and that left off the last print the see again.

03:08.070 --> 03:09.420
So we have another error.

03:09.720 --> 03:10.100
OK.

03:10.140 --> 03:12.770
Now if we hit enter here it worked.

03:12.960 --> 03:18.410
If you'd like to see a nice clean up version what this actually looks like here I have it here.

03:18.480 --> 03:25.190
So create table shirts shirt ID and not know autoincrement article Vardar colored Vardar shirts which

03:25.210 --> 03:31.950
are last worn int primary key shirt ID and the semi-colon and the correct parentheses.

03:31.950 --> 03:32.980
Closing it out.

03:33.360 --> 03:35.090
And that should do it.

03:35.090 --> 03:37.770
All right if we wanted to double check that things worked.

03:37.890 --> 03:43.900
We can do our describe this shirt and you can see we have what we expect.

03:43.920 --> 03:48.450
So the next thing is to get all of this data in our database.

03:48.450 --> 03:53.820
So I gave it to you as this comma separated list where things are all in the same format where we have

03:53.950 --> 03:58.890
you know print the seeds for one shirt and then inside of that we have article first and then color

03:59.380 --> 04:00.040
size.

04:00.090 --> 04:04.780
And then last one so we can insert using that format.

04:04.920 --> 04:12.810
So we'll do an insert into shirts and we have to specify we're doing article first then we're doing

04:12.810 --> 04:15.610
color and we're doing shirt size.

04:15.780 --> 04:20.610
And then finally we're doing last worn and then we need our values.

04:21.210 --> 04:25.900
And I'm just going to hit enter for now and I'm going to paste that giant list.

04:26.310 --> 04:33.930
And we need a semi-colon at the end and we hit Enter good no mistakes this time says records eight eight

04:33.990 --> 04:35.210
rows affected.

04:35.560 --> 04:35.990
OK.

04:36.060 --> 04:42.930
So if we wanted just to double check I can do our select star from shirts and we get the data in there

04:43.020 --> 04:44.260
that we should have.

04:44.940 --> 04:51.870
And then the last thing we need to do is to add a new shirt so manually one shirt rather than bulk inserting

04:52.050 --> 04:54.580
it should be a purple polo shirt that medium.

04:54.650 --> 04:56.980
That was last worn 50 days ago.

04:57.630 --> 05:01.380
So I didn't specify what order you need it to insert into.

05:01.380 --> 05:02.660
So I don't really care.

05:02.780 --> 05:10.410
But do insert into shirts and we'll do color because that's how we specified here color an article then

05:10.410 --> 05:26.460
size and then must warn color article shirt size last worn like that values and it's purple polo shirt

05:28.700 --> 05:36.850
Khama medium Khama 50 just like that OK.

05:37.020 --> 05:41.490
And if we do show like Star we should see that polo shirt down there it's purple medium.

05:41.490 --> 05:43.120
50 days ago we were in.

05:43.560 --> 05:44.190
All right.

05:44.490 --> 05:47.230
So we've now wrapped up the Create part of crud.

05:47.430 --> 05:53.310
We saw how to create the database we inserted created a table we inserted data into the table.

05:53.310 --> 05:54.630
Now we're moving on to reading.
