1 00:00:00,000 --> 00:00:09,192 [MUSIC] 2 00:00:09,192 --> 00:00:13,800 Hi, I'm Ben and in this course, we're going to talk about how to create and 3 00:00:13,800 --> 00:00:16,160 manage tables in a database. 4 00:00:16,160 --> 00:00:20,370 We've already seen how to query tables and how to add new rows. 5 00:00:20,370 --> 00:00:22,210 Now we're gonna take a step back and 6 00:00:22,210 --> 00:00:25,460 see how to create those tables in the first place. 7 00:00:25,460 --> 00:00:29,920 To create tables in SQL, we'll need to used data definition language, or 8 00:00:29,920 --> 00:00:31,670 DDL for short. 9 00:00:31,670 --> 00:00:32,890 As its name implies, 10 00:00:32,890 --> 00:00:37,960 data definition language is what's used to define the structure of our database. 11 00:00:37,960 --> 00:00:42,930 If you need to create or delete a table, you'll use data definition language. 12 00:00:42,930 --> 00:00:47,510 To help us learn about data definition language let's work through an example. 13 00:00:47,510 --> 00:00:52,360 Let's say we're in a band and we've just had our big break, a stadium tour. 14 00:00:52,360 --> 00:00:56,820 We've picked the dates, booked the stadiums, and rented a tour bus. 15 00:00:56,820 --> 00:01:00,050 So now it's time to start selling tickets. 16 00:01:00,050 --> 00:01:01,880 Since we're a pretty big deal, 17 00:01:01,880 --> 00:01:05,480 the only place we're going to sell tickets is on our website, 18 00:01:05,480 --> 00:01:10,090 which means we'll be responsible for keeping track of all the ticket sales. 19 00:01:10,090 --> 00:01:15,210 Luckily, we know a bit about databases so this shouldn't be too hard. 20 00:01:15,210 --> 00:01:19,160 But before we dive into creating tables, let's start by taking a minute to 21 00:01:19,160 --> 00:01:21,960 think about how we should structure our database. 22 00:01:21,960 --> 00:01:25,000 First, we'll need a Tickets Table to store details about 23 00:01:25,000 --> 00:01:26,960 each of the tickets we've sold. 24 00:01:26,960 --> 00:01:30,310 Then, inside that table we'll need a unique ID for 25 00:01:30,310 --> 00:01:35,200 each ticket, followed by information about which concert the ticket is for. 26 00:01:35,200 --> 00:01:40,890 We'll need the date, city, state, and venue for each concert. 27 00:01:40,890 --> 00:01:44,620 After that we need to know who the ticket is for. 28 00:01:44,620 --> 00:01:47,910 So let's add FIRST_NAME and LAST_NAME columns and 29 00:01:47,910 --> 00:01:52,540 also an email address in case we need to send any updates about the concert. 30 00:01:52,540 --> 00:01:53,910 That looks pretty good. 31 00:01:53,910 --> 00:01:57,420 Let's see what it would look like once we've inserted some data. 32 00:01:57,420 --> 00:01:59,590 There's nothing inherently wrong with this. 33 00:01:59,590 --> 00:02:04,040 We could do everything with one table, and probably make it work. 34 00:02:04,040 --> 00:02:08,080 But down the line, this approach ends up being a lot more complicated. 35 00:02:08,080 --> 00:02:12,490 For example, if we wanted to add information about a new concert but 36 00:02:12,490 --> 00:02:16,040 weren't selling tickets yet, we'd have no way of doing it. 37 00:02:16,040 --> 00:02:20,300 To fix this we should pull out all of the concert data into its own table. 38 00:02:21,300 --> 00:02:24,610 Let's put the concert data in a new Concerts Table. 39 00:02:24,610 --> 00:02:28,370 And in the Tickets Table, let's replace it with a Concert ID column. 40 00:02:29,580 --> 00:02:33,365 We can also have repeated data about who's buying the tickets. 41 00:02:33,365 --> 00:02:37,600 Maybe they're going to multiple concerts or just buying tickets for a group. 42 00:02:37,600 --> 00:02:41,010 Either way, we can save some more space by making a table for 43 00:02:41,010 --> 00:02:42,290 our ticket holders as well. 44 00:02:43,600 --> 00:02:48,295 This process of removing repeated data is called normalization and 45 00:02:48,295 --> 00:02:52,840 it's one of the first things you should be looking for when creating a new database. 46 00:02:52,840 --> 00:02:55,850 If you've got a group of columns that keeps repeating, 47 00:02:55,850 --> 00:02:57,710 then it's usually time to create a new table. 48 00:02:58,750 --> 00:03:01,460 Now that we've decided on the structure of our database, 49 00:03:01,460 --> 00:03:03,470 in the next video we'll start creating it.