Creating the Ticket Holders Table

One table down, two to go. 0:00 Now that we're done with our CONCERTS table, 0:02 let's start by clicking reset to give us a nice clean canvas. 0:05 And delete that comment again, 0:09 then let's move onto creating our ticket holders table. 0:11 If you'd like to take a stab at it yourself, pause me and 0:15 see what you come up with. 0:18 It should be mostly the same as what we did with the CONCERTS table. 0:20 Okay, here's what I came up with, first we're going 0:24 to create the table and name it Ticket_Holders. 0:29 Then we're going to add the ID column, give it an INT data type, 0:37 and then make it the PRIMARY KEY. 0:42 Since we'll probably have more than 32,000 TICKET_HOLDERS, 0:45 we can't use small INT for the data type. 0:49 Instead, we should use INT, 0:52 which will let us have just over two billion TICKET_HOLDERS. 0:54 Next, let's declare the FIRST_NAME column as 0:58 a VARCHAR with space for up to 255 characters. 1:03 Then let's copy and paste that line twice. 1:08 Rename FIRST_NAME to LAST_NAME and FIRST_NAME to EMAIL, 1:17 get rid of this last comma, and there we go. 1:23 Let's run it. 1:28 And now we've got a TICKET_HOLDERS table, great work. 1:31 Back in the query, let's copy and 1:34 paste on the insert statements from the teacher's notes to give us some test data. 1:36 Then let's run it and write, if we want to recreate the table on each run, 1:41 we need to start by dropping the table. 1:47 Let's add a line at the top and 1:50 type, DROP TABLE TICKET_HOLDERS and 1:53 a semi colon, and then try running it again. 1:58 Perfect, also, if we'd like to check what's in the table, 2:04 we can always use a select statement. 2:08 Below our insert statements, let's select everything from 2:10 the TICKET_HOLDERS table, SELECT star FROM TICKET_HOLDERS. 2:16 Then if we run it again, 2:21 we should be able to see what's in the table without needing to click on it. 2:23 Perfect, we're almost done with this table, 2:28 there's just one more thing we should talk about, and that's the ID column. 2:31 Each time we insert a new row, we have to come up with a unique ID, 2:36 which can be kind of a hassle. 2:40 Luckily, SQL gives us an easy way to deal with this by using 2:42 the AUTOINCREMENT keyword. 2:47 If you had AUTOINCREMENT after an integer PRIMARY KEY, then anytime you try 2:49 to insert null it will just give you the next number, let's try it out. 2:54 Let's have a space after PRIMARY KEY, and then type AUTOINCREMENT. 2:59 And then instead of using ID's one, two, 3:06 three and four, let's go with one, two, null and null. 3:12 And if we run it, we get an error that 3:19 AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY. 3:22 You know how I've mentioned that sequel it doesn't use data types? 3:27 Well, there is one exception and it's the INTEGER PRIMARY KEY 3:31 if you declare an INTEGER PRIMARY KEY sequel will actually 3:37 make sure that whatever goes in there is an INTEGER. 3:42 So it makes sense that AUTOINCREMENT, 3:47 which requires integers would only be allowed on an INTEGER PRIMARU KEY. 3:50 Okay, let's change INT to INTEGER, 3:56 and try running it again. 4:00 But now it's telling us that there's no TICKET_HOLDERS tables. 4:06 Since last time we got an error in our CREATE TABLE statement, 4:09 the table was dropped but it never got re-created. 4:14 So now we're trying to drop a table that doesn't exist, lucky for 4:19 us there's an easy solution that helps make testing a lot easier. 4:24 And the DROP TABLE statement right after the word table, 4:29 we just need to add IF EXISTS to make it only drop the table if it actually exists. 4:34 Let's run it, and now we can finally see our AUTOINCREMENT in action. 4:40 We didn't specify IDs for Dominik or Marcella but thanks to AUTOINCREMENT, 4:46 the database was able to pick up where we left off and give us three and four. 4:52 Pretty cool, right? 4:57 Now that we know how AUTOINCREMENT works, let's go ahead and use null for 4:59 all the IDs, instead of just the last two. 5:03 Also while we're changing things, 5:10 just like we added IF EXISTS to the DROP TABLE statement. 5:13 We can do something similar with the CREATE TABLE statement, 5:16 by adding IF NOT EXIST before the table name. 5:20 This way, we won't try to create any already existing tables. 5:27 All right, let's run this one more time to make sure everything works. 5:32 And in the next video, 5:37 we'll tie everything together with the tickets table. 5:39