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.