1
00:00:00,480 --> 00:00:04,950
We just tried to recreate our CONCERTS
table with our new data types, but

2
00:00:04,950 --> 00:00:09,460
it turns out you can't create
a table that already exists.

3
00:00:09,460 --> 00:00:14,360
To fix this, we just need to remove
the table before we try and recreate it.

4
00:00:14,360 --> 00:00:18,160
Which in SQL,
is called dropping the table.

5
00:00:18,160 --> 00:00:21,970
Let's add a line above
our create statement and

6
00:00:21,970 --> 00:00:25,200
then type drop TABLE CONCERTS.

7
00:00:26,820 --> 00:00:33,270
And don't forget the semicolon, now if we
run these it will first drop the table,

8
00:00:33,270 --> 00:00:37,760
removing it from the data base and then
add it back in with the create statement.

9
00:00:39,620 --> 00:00:44,420
Lets click run and
then let's check out the CONCERTS table.

10
00:00:46,560 --> 00:00:50,090
Notice that this time,
our columns have data types.

11
00:00:50,090 --> 00:00:51,250
Awesome, right?

12
00:00:52,260 --> 00:00:54,660
Let's try inserting some values.

13
00:00:54,660 --> 00:01:00,620
Back in our query, below our CREATE TABLE
statement, let's add an insert statement.

14
00:01:01,620 --> 00:01:06,680
Let's type INSERT INTO and

15
00:01:06,680 --> 00:01:11,030
then the name of the table,
which is CONCERTS, followed by VALUES,

16
00:01:13,270 --> 00:01:16,480
and then in parenthesis,
values for each of the columns.

17
00:01:18,060 --> 00:01:19,760
Let's use 1 for our ID.

18
00:01:20,760 --> 00:01:26,700
And for the date, remember we need to pass
it in as a string using the right format.

19
00:01:26,700 --> 00:01:32,965
So just to make something up,
let's go with New Year's Day of 2018.

20
00:01:32,965 --> 00:01:37,722
2018-01-01.

21
00:01:39,050 --> 00:01:41,880
Finally, since the rest
of columns are varchars,

22
00:01:41,880 --> 00:01:47,104
let's just add three short strings like A,

23
00:01:47,104 --> 00:01:50,820
B, and C and then,
finish with a semicolon.

24
00:01:52,150 --> 00:01:56,180
Then, let's run it again and
check out the CONCERTS table.

25
00:01:58,300 --> 00:02:02,820
Looks good, but let's try it one more
time with some more extreme values.

26
00:02:04,080 --> 00:02:09,700
For the ID,
let's pass in 100,000 instead of 1.

27
00:02:09,700 --> 00:02:13,050
Remember that small INT only
goes up to about 32,000.

28
00:02:13,050 --> 00:02:17,497
So this number should be too big, but

29
00:02:17,497 --> 00:02:23,230
if we run it, it gets inserted just fine.

30
00:02:23,230 --> 00:02:27,680
Remember how I mentioned that SQLite
doesn't really use data types,

31
00:02:27,680 --> 00:02:29,320
that's what's going on here.

32
00:02:29,320 --> 00:02:34,370
In SQL Server these data types would
mean something, but here in SQLite

33
00:02:34,370 --> 00:02:39,260
they're basically just documentation and
don't have any functional purpose.

34
00:02:39,260 --> 00:02:45,030
In fact, in SQLite if you want to create
a column with the type of happy birthday,

35
00:02:47,120 --> 00:02:51,880
you'll get a column with
the happy birthday data type.

36
00:02:51,880 --> 00:02:58,220
However, even though SQLite doesn't use
data types, most SQL implementations do.

37
00:02:58,220 --> 00:03:00,830
To make sure you are well
prepared we'llcontinue

38
00:03:00,830 --> 00:03:02,980
to use the data types from SQL Server.

39
00:03:03,980 --> 00:03:11,640
Starting by changing happy birthday
back to small SMALLINT, awesome.

40
00:03:11,640 --> 00:03:15,480
It may seem weird to allow data types and
then not do anything with them.

41
00:03:15,480 --> 00:03:19,200
But it's really a compatibility
feature letting you reuse code

42
00:03:19,200 --> 00:03:21,600
between different types of SQL.

43
00:03:21,600 --> 00:03:25,190
If you've gotta CREATE statement
from another version of DSQL,

44
00:03:25,190 --> 00:03:30,407
regardless of what data types it uses,
SQLite will create the table just fine.

45
00:03:30,407 --> 00:03:33,990
Now before we move on to
the other two tables,

46
00:03:33,990 --> 00:03:37,860
there's one more thing we need
to do with the concert's table.

47
00:03:37,860 --> 00:03:43,990
For each column, after you specified a
data type, you can also add a constraint.

48
00:03:43,990 --> 00:03:48,300
One of the most common constraints is
to mark a column as a primary key.

49
00:03:48,300 --> 00:03:53,770
Letting SQL know that each value in this
column uniquely identifies a record.

50
00:03:53,770 --> 00:03:56,930
To add a primary key
constraint to the ID column,

51
00:03:56,930 --> 00:04:02,560
let's add a space after SMALLINT and
then type PRIMARY KEY.

52
00:04:02,560 --> 00:04:04,710
Then to make sure it works,

53
00:04:04,710 --> 00:04:10,490
let's copy our INSERT statement and
paste it on the next line.

54
00:04:11,870 --> 00:04:15,360
And if we run it, perfect!

55
00:04:15,360 --> 00:04:17,540
It won't let us insert a duplicate ID.

56
00:04:18,960 --> 00:04:21,440
That should do it for
the CONCERTS table, for

57
00:04:21,440 --> 00:04:24,910
our last step,
let's add some more realistic data.

58
00:04:24,910 --> 00:04:26,630
Let's delete the INSERT statements.

59
00:04:29,084 --> 00:04:33,680
And then copy and paste in some new
ones from the teacher's notes below.

60
00:04:33,680 --> 00:04:34,300
Then let's run it.

61
00:04:36,518 --> 00:04:39,650
And now we're ready to start
creating the ticket-holders table.