Creating a Table5:19 with Ben Deitch
In this video we'll learn about the CREATE statement as well as shine some light on how datatypes are used in SQLite.
We know what tables we need, now it's time to create them. 0:00 But first, a quick disclaimer. 0:03 In this course, we will be using SQLite. 0:06 But SQLite isn't the only implementation of SQL. 0:09 So if you find yourself using a different version of SQL, 0:12 just know that things might look a little different. 0:15 Okay, let's get back to creating those tables and 0:18 learning about data definition language. 0:21 The first DDL statement we need to know about is the create statement, 0:23 which is used to create tables. 0:27 If you'd like to follow along, go ahead and 0:30 click Launch SQL Playground in the bottom right. 0:32 Let's start by first getting rid of this comment and 0:36 then creating the concerts table. 0:39 To create a table, you start by typing, 0:41 CREATE TABLE, and then the name of the table, CONCERTS. 0:44 Then you add parenthesis and inside you define your columns, separated by commas. 0:52 So let's type ID, DATE, CITY, 0:59 STATE, and VENUE. 1:04 And it looks like DATE is a keyword. 1:09 But that's okay, it can also be a column name. 1:12 Finally, let's add a semicolon at the end and hit Run. 1:16 Now, on the left, we can see a CONCERTS table. 1:22 And if we click on it, we can see it's got the columns we specified earlier, awesome. 1:26 Getting back to the CREATE statement. 1:34 While this works fine for SQLite, it won't work so well in other versions of SQL. 1:37 In most SQL implementations, 1:43 when you declare a column, you also need to specify a data type for that column. 1:45 However, SQLite doesn't really use data types. 1:50 More on that later. 1:54 But, to see how data types work, let's pretend that instead of SQLite, 1:56 we're using a different implementation of SQL, called Microsoft SQL Server. 2:01 To figure out which data types are available in Microsoft SQL Server, 2:07 let's Google, SQL server data types. 2:11 And let's pick this third result for technet.microsoft.com. 2:17 Then if we scroll down, 2:22 we can see all the different data types available in SQL server. 2:24 Let's take a minute to figure out which types we should use for 2:29 our concerts table, starting with the ID column. 2:32 Almost always, you'll want your ID columns to be integers. 2:36 So looking at this list it looks like we've got a few options, 2:40 bigint, smallint, int, and tinyint. 2:44 And if we click on one, we can see the different ranges for 2:48 each integer type, and how much storage space it takes up. 2:53 I think it's safe to say that there will be less than 32,000 concerts. 2:58 So for ID, let's pick smallint as the data type. 3:04 Next up is the date column. 3:09 And unsurprisingly, we should use the date type for the date column. 3:12 Though before we move on, 3:18 lets make sure we know how we should be representing our dates. 3:19 Different SQL implementations use different date formats, so 3:23 we want to double check this before adding any dates. 3:27 Let's click on date, and 3:31 then if we scroll down a bit we can see the default date format. 3:33 So when we're adding new data to our table, 3:38 we should use this format for our dates. 3:41 The last three columns on our concerts table all represent string data 3:44 which is usually represented by the varchar data type. 3:48 Varchar lets you set a maximum length for your string, but 3:52 then only uses as much storage as ends up being required. 3:56 So it's a really good choice for string data. 4:01 Now that we know what data types we should use, let's check back in with our query. 4:04 First, let's take a second to reformat our create statement to have each 4:09 column on a new line. 4:14 Then to add a data type to a column, just add a space and then add the data type. 4:22 So for our ID, since we want this to be a small int, 4:28 let's add space SMALLINT, perfect. 4:32 Now let's add in the rest of those data types. 4:37 Let's add DATE to our DATE column. 4:39 Then for the CITY let's add VARCHAR, and then for 4:44 the max length lets go with 255 to make sure we have plenty of room. 4:48 Finally, lets copy that VARCHAR data type and the space and 4:54 paste it in for our state and venue columns. 4:58 Great job. 5:04 Now that we've got our data types lets re-run the query. 5:06 Ooh, looks like we're getting an error that the Concerts Table already exists. 5:10 Let's find out how to fix this error in the next video. 5:16
You need to sign up for Treehouse in order to download course files.Sign up