Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Preview
Start a free Courses trial
to watch this video
In this video we'll talk about Data Definition Language (DDL) and make a plan for structuring our database!
Related Links
Related Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign upRelated Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign up
[MUSIC]
0:00
Hi, I'm Ben and in this course, we're
going to talk about how to create and
0:09
manage tables in a database.
0:13
We've already seen how to query tables and
how to add new rows.
0:16
Now we're gonna take a step back and
0:20
see how to create those
tables in the first place.
0:22
To create tables in SQL, we'll need
to used data definition language, or
0:25
DDL for short.
0:29
As its name implies,
0:31
data definition language is what's used
to define the structure of our database.
0:32
If you need to create or delete a table,
you'll use data definition language.
0:37
To help us learn about data definition
language let's work through an example.
0:42
Let's say we're in a band and we've
just had our big break, a stadium tour.
0:47
We've picked the dates, booked
the stadiums, and rented a tour bus.
0:52
So now it's time to start selling tickets.
0:56
Since we're a pretty big deal,
1:00
the only place we're going to
sell tickets is on our website,
1:01
which means we'll be responsible for
keeping track of all the ticket sales.
1:05
Luckily, we know a bit about databases so
this shouldn't be too hard.
1:10
But before we dive into creating tables,
let's start by taking a minute to
1:15
think about how we should
structure our database.
1:19
First, we'll need a Tickets Table
to store details about
1:21
each of the tickets we've sold.
1:25
Then, inside that table
we'll need a unique ID for
1:26
each ticket, followed by information
about which concert the ticket is for.
1:30
We'll need the date, city, state,
and venue for each concert.
1:35
After that we need to know
who the ticket is for.
1:40
So let's add FIRST_NAME and
LAST_NAME columns and
1:44
also an email address in case we need
to send any updates about the concert.
1:47
That looks pretty good.
1:52
Let's see what it would look like
once we've inserted some data.
1:53
There's nothing inherently
wrong with this.
1:57
We could do everything with one table,
and probably make it work.
1:59
But down the line, this approach ends
up being a lot more complicated.
2:04
For example, if we wanted to add
information about a new concert but
2:08
weren't selling tickets yet,
we'd have no way of doing it.
2:12
To fix this we should pull out all of
the concert data into its own table.
2:16
Let's put the concert data
in a new Concerts Table.
2:21
And in the Tickets Table,
let's replace it with a Concert ID column.
2:24
We can also have repeated data
about who's buying the tickets.
2:29
Maybe they're going to multiple concerts
or just buying tickets for a group.
2:33
Either way, we can save some
more space by making a table for
2:37
our ticket holders as well.
2:41
This process of removing repeated
data is called normalization and
2:43
it's one of the first things you should be
looking for when creating a new database.
2:48
If you've got a group of
columns that keeps repeating,
2:52
then it's usually time
to create a new table.
2:55
Now that we've decided on
the structure of our database,
2:58
in the next video we'll start creating it.
3:01
You need to sign up for Treehouse in order to download course files.
Sign upYou need to sign up for Treehouse in order to set up Workspace
Sign up