Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Well done!
You have completed Creating and Modifying Database Tables!
You have completed Creating and Modifying Database Tables!
Preview
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