1 00:00:00,590 --> 00:00:03,810 We know what tables we need, now it's time to create them. 2 00:00:03,810 --> 00:00:06,340 But first, a quick disclaimer. 3 00:00:06,340 --> 00:00:09,010 In this course, we will be using SQLite. 4 00:00:09,010 --> 00:00:12,167 But SQLite isn't the only implementation of SQL. 5 00:00:12,167 --> 00:00:15,052 So if you find yourself using a different version of SQL, 6 00:00:15,052 --> 00:00:18,580 just know that things might look a little different. 7 00:00:18,580 --> 00:00:21,300 Okay, let's get back to creating those tables and 8 00:00:21,300 --> 00:00:23,680 learning about data definition language. 9 00:00:23,680 --> 00:00:27,420 The first DDL statement we need to know about is the create statement, 10 00:00:27,420 --> 00:00:29,240 which is used to create tables. 11 00:00:30,550 --> 00:00:32,450 If you'd like to follow along, go ahead and 12 00:00:32,450 --> 00:00:34,940 click Launch SQL Playground in the bottom right. 13 00:00:36,210 --> 00:00:39,060 Let's start by first getting rid of this comment and 14 00:00:39,060 --> 00:00:40,570 then creating the concerts table. 15 00:00:41,690 --> 00:00:44,670 To create a table, you start by typing, 16 00:00:44,670 --> 00:00:50,030 CREATE TABLE, and then the name of the table, CONCERTS. 17 00:00:52,480 --> 00:00:59,850 Then you add parenthesis and inside you define your columns, separated by commas. 18 00:00:59,850 --> 00:01:04,331 So let's type ID, DATE, CITY, 19 00:01:04,331 --> 00:01:09,290 STATE, and VENUE. 20 00:01:09,290 --> 00:01:12,060 And it looks like DATE is a keyword. 21 00:01:12,060 --> 00:01:14,580 But that's okay, it can also be a column name. 22 00:01:16,360 --> 00:01:19,711 Finally, let's add a semicolon at the end and hit Run. 23 00:01:22,698 --> 00:01:26,560 Now, on the left, we can see a CONCERTS table. 24 00:01:26,560 --> 00:01:33,120 And if we click on it, we can see it's got the columns we specified earlier, awesome. 25 00:01:34,490 --> 00:01:35,990 Getting back to the CREATE statement. 26 00:01:37,450 --> 00:01:43,570 While this works fine for SQLite, it won't work so well in other versions of SQL. 27 00:01:43,570 --> 00:01:45,430 In most SQL implementations, 28 00:01:45,430 --> 00:01:50,800 when you declare a column, you also need to specify a data type for that column. 29 00:01:50,800 --> 00:01:54,580 However, SQLite doesn't really use data types. 30 00:01:54,580 --> 00:01:56,460 More on that later. 31 00:01:56,460 --> 00:02:01,270 But, to see how data types work, let's pretend that instead of SQLite, 32 00:02:01,270 --> 00:02:05,910 we're using a different implementation of SQL, called Microsoft SQL Server. 33 00:02:07,290 --> 00:02:11,760 To figure out which data types are available in Microsoft SQL Server, 34 00:02:11,760 --> 00:02:17,120 let's Google, SQL server data types. 35 00:02:17,120 --> 00:02:22,580 And let's pick this third result for technet.microsoft.com. 36 00:02:22,580 --> 00:02:24,470 Then if we scroll down, 37 00:02:24,470 --> 00:02:28,170 we can see all the different data types available in SQL server. 38 00:02:29,750 --> 00:02:32,590 Let's take a minute to figure out which types we should use for 39 00:02:32,590 --> 00:02:35,270 our concerts table, starting with the ID column. 40 00:02:36,660 --> 00:02:40,860 Almost always, you'll want your ID columns to be integers. 41 00:02:40,860 --> 00:02:44,480 So looking at this list it looks like we've got a few options, 42 00:02:44,480 --> 00:02:48,810 bigint, smallint, int, and tinyint. 43 00:02:48,810 --> 00:02:53,230 And if we click on one, we can see the different ranges for 44 00:02:53,230 --> 00:02:58,450 each integer type, and how much storage space it takes up. 45 00:02:58,450 --> 00:03:04,020 I think it's safe to say that there will be less than 32,000 concerts. 46 00:03:04,020 --> 00:03:07,590 So for ID, let's pick smallint as the data type. 47 00:03:09,650 --> 00:03:11,160 Next up is the date column. 48 00:03:12,400 --> 00:03:17,050 And unsurprisingly, we should use the date type for the date column. 49 00:03:18,065 --> 00:03:19,470 Though before we move on, 50 00:03:19,470 --> 00:03:23,690 lets make sure we know how we should be representing our dates. 51 00:03:23,690 --> 00:03:27,310 Different SQL implementations use different date formats, so 52 00:03:27,310 --> 00:03:30,230 we want to double check this before adding any dates. 53 00:03:31,420 --> 00:03:33,504 Let's click on date, and 54 00:03:33,504 --> 00:03:38,440 then if we scroll down a bit we can see the default date format. 55 00:03:38,440 --> 00:03:41,649 So when we're adding new data to our table, 56 00:03:41,649 --> 00:03:44,620 we should use this format for our dates. 57 00:03:44,620 --> 00:03:48,750 The last three columns on our concerts table all represent string data 58 00:03:48,750 --> 00:03:52,860 which is usually represented by the varchar data type. 59 00:03:52,860 --> 00:03:56,800 Varchar lets you set a maximum length for your string, but 60 00:03:56,800 --> 00:04:01,370 then only uses as much storage as ends up being required. 61 00:04:01,370 --> 00:04:03,610 So it's a really good choice for string data. 62 00:04:04,840 --> 00:04:09,720 Now that we know what data types we should use, let's check back in with our query. 63 00:04:09,720 --> 00:04:14,462 First, let's take a second to reformat our create statement to have each 64 00:04:14,462 --> 00:04:15,845 column on a new line. 65 00:04:22,263 --> 00:04:28,930 Then to add a data type to a column, just add a space and then add the data type. 66 00:04:28,930 --> 00:04:32,430 So for our ID, since we want this to be a small int, 67 00:04:32,430 --> 00:04:37,110 let's add space SMALLINT, perfect. 68 00:04:37,110 --> 00:04:39,780 Now let's add in the rest of those data types. 69 00:04:39,780 --> 00:04:42,600 Let's add DATE to our DATE column. 70 00:04:44,660 --> 00:04:48,350 Then for the CITY let's add VARCHAR, and then for 71 00:04:48,350 --> 00:04:54,250 the max length lets go with 255 to make sure we have plenty of room. 72 00:04:54,250 --> 00:04:58,638 Finally, lets copy that VARCHAR data type and the space and 73 00:04:58,638 --> 00:05:01,904 paste it in for our state and venue columns. 74 00:05:04,628 --> 00:05:06,050 Great job. 75 00:05:06,050 --> 00:05:08,761 Now that we've got our data types lets re-run the query. 76 00:05:10,819 --> 00:05:16,110 Ooh, looks like we're getting an error that the Concerts Table already exists. 77 00:05:16,110 --> 00:05:18,920 Let's find out how to fix this error in the next video.