Preview
Video Player
00:00
00:00
Creating a Table
5:19In 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