1 00:00:00,450 --> 00:00:02,575 One table down, two to go. 2 00:00:02,575 --> 00:00:05,637 Now that we're done with our CONCERTS table, 3 00:00:05,637 --> 00:00:09,733 let's start by clicking reset to give us a nice clean canvas. 4 00:00:09,733 --> 00:00:11,866 And delete that comment again, 5 00:00:11,866 --> 00:00:15,743 then let's move onto creating our ticket holders table. 6 00:00:15,743 --> 00:00:18,848 If you'd like to take a stab at it yourself, pause me and 7 00:00:18,848 --> 00:00:20,314 see what you come up with. 8 00:00:20,314 --> 00:00:24,520 It should be mostly the same as what we did with the CONCERTS table. 9 00:00:24,520 --> 00:00:29,752 Okay, here's what I came up with, first we're going 10 00:00:29,752 --> 00:00:34,545 to create the table and name it Ticket_Holders. 11 00:00:37,812 --> 00:00:42,793 Then we're going to add the ID column, give it an INT data type, 12 00:00:42,793 --> 00:00:45,391 and then make it the PRIMARY KEY. 13 00:00:45,391 --> 00:00:49,749 Since we'll probably have more than 32,000 TICKET_HOLDERS, 14 00:00:49,749 --> 00:00:52,301 we can't use small INT for the data type. 15 00:00:52,301 --> 00:00:54,369 Instead, we should use INT, 16 00:00:54,369 --> 00:00:58,772 which will let us have just over two billion TICKET_HOLDERS. 17 00:00:58,772 --> 00:01:03,643 Next, let's declare the FIRST_NAME column as 18 00:01:03,643 --> 00:01:08,642 a VARCHAR with space for up to 255 characters. 19 00:01:08,642 --> 00:01:11,439 Then let's copy and paste that line twice. 20 00:01:17,182 --> 00:01:23,294 Rename FIRST_NAME to LAST_NAME and FIRST_NAME to EMAIL, 21 00:01:23,294 --> 00:01:27,650 get rid of this last comma, and there we go. 22 00:01:28,780 --> 00:01:29,580 Let's run it. 23 00:01:31,524 --> 00:01:34,951 And now we've got a TICKET_HOLDERS table, great work. 24 00:01:34,951 --> 00:01:36,782 Back in the query, let's copy and 25 00:01:36,782 --> 00:01:41,213 paste on the insert statements from the teacher's notes to give us some test data. 26 00:01:41,213 --> 00:01:47,104 Then let's run it and write, if we want to recreate the table on each run, 27 00:01:47,104 --> 00:01:50,252 we need to start by dropping the table. 28 00:01:50,252 --> 00:01:53,884 Let's add a line at the top and 29 00:01:53,884 --> 00:01:58,678 type, DROP TABLE TICKET_HOLDERS and 30 00:01:58,678 --> 00:02:04,204 a semi colon, and then try running it again. 31 00:02:04,204 --> 00:02:08,393 Perfect, also, if we'd like to check what's in the table, 32 00:02:08,393 --> 00:02:10,981 we can always use a select statement. 33 00:02:10,981 --> 00:02:16,183 Below our insert statements, let's select everything from 34 00:02:16,183 --> 00:02:21,792 the TICKET_HOLDERS table, SELECT star FROM TICKET_HOLDERS. 35 00:02:21,792 --> 00:02:23,405 Then if we run it again, 36 00:02:23,405 --> 00:02:28,501 we should be able to see what's in the table without needing to click on it. 37 00:02:28,501 --> 00:02:31,233 Perfect, we're almost done with this table, 38 00:02:31,233 --> 00:02:36,360 there's just one more thing we should talk about, and that's the ID column. 39 00:02:36,360 --> 00:02:40,871 Each time we insert a new row, we have to come up with a unique ID, 40 00:02:40,871 --> 00:02:42,974 which can be kind of a hassle. 41 00:02:42,974 --> 00:02:47,286 Luckily, SQL gives us an easy way to deal with this by using 42 00:02:47,286 --> 00:02:49,486 the AUTOINCREMENT keyword. 43 00:02:49,486 --> 00:02:54,446 If you had AUTOINCREMENT after an integer PRIMARY KEY, then anytime you try 44 00:02:54,446 --> 00:02:59,890 to insert null it will just give you the next number, let's try it out. 45 00:02:59,890 --> 00:03:06,707 Let's have a space after PRIMARY KEY, and then type AUTOINCREMENT. 46 00:03:06,707 --> 00:03:12,154 And then instead of using ID's one, two, 47 00:03:12,154 --> 00:03:19,584 three and four, let's go with one, two, null and null. 48 00:03:19,584 --> 00:03:22,585 And if we run it, we get an error that 49 00:03:22,585 --> 00:03:27,502 AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY. 50 00:03:27,502 --> 00:03:31,782 You know how I've mentioned that sequel it doesn't use data types? 51 00:03:31,782 --> 00:03:37,403 Well, there is one exception and it's the INTEGER PRIMARY KEY 52 00:03:37,403 --> 00:03:42,602 if you declare an INTEGER PRIMARY KEY sequel will actually 53 00:03:42,602 --> 00:03:47,285 make sure that whatever goes in there is an INTEGER. 54 00:03:47,285 --> 00:03:50,309 So it makes sense that AUTOINCREMENT, 55 00:03:50,309 --> 00:03:56,002 which requires integers would only be allowed on an INTEGER PRIMARU KEY. 56 00:03:56,002 --> 00:04:00,715 Okay, let's change INT to INTEGER, 57 00:04:00,715 --> 00:04:03,966 and try running it again. 58 00:04:06,432 --> 00:04:09,992 But now it's telling us that there's no TICKET_HOLDERS tables. 59 00:04:09,992 --> 00:04:14,984 Since last time we got an error in our CREATE TABLE statement, 60 00:04:14,984 --> 00:04:19,213 the table was dropped but it never got re-created. 61 00:04:19,213 --> 00:04:24,371 So now we're trying to drop a table that doesn't exist, lucky for 62 00:04:24,371 --> 00:04:29,542 us there's an easy solution that helps make testing a lot easier. 63 00:04:29,542 --> 00:04:34,074 And the DROP TABLE statement right after the word table, 64 00:04:34,074 --> 00:04:40,413 we just need to add IF EXISTS to make it only drop the table if it actually exists. 65 00:04:40,413 --> 00:04:46,532 Let's run it, and now we can finally see our AUTOINCREMENT in action. 66 00:04:46,532 --> 00:04:52,131 We didn't specify IDs for Dominik or Marcella but thanks to AUTOINCREMENT, 67 00:04:52,131 --> 00:04:57,574 the database was able to pick up where we left off and give us three and four. 68 00:04:57,574 --> 00:04:59,154 Pretty cool, right? 69 00:04:59,154 --> 00:05:03,371 Now that we know how AUTOINCREMENT works, let's go ahead and use null for 70 00:05:03,371 --> 00:05:05,828 all the IDs, instead of just the last two. 71 00:05:10,692 --> 00:05:13,041 Also while we're changing things, 72 00:05:13,041 --> 00:05:16,683 just like we added IF EXISTS to the DROP TABLE statement. 73 00:05:16,683 --> 00:05:20,416 We can do something similar with the CREATE TABLE statement, 74 00:05:20,416 --> 00:05:23,162 by adding IF NOT EXIST before the table name. 75 00:05:27,260 --> 00:05:32,261 This way, we won't try to create any already existing tables. 76 00:05:32,261 --> 00:05:36,135 All right, let's run this one more time to make sure everything works. 77 00:05:37,910 --> 00:05:39,166 And in the next video, 78 00:05:39,166 --> 00:05:42,350 we'll tie everything together with the tickets table.