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
Now that you have been reminded of a few basic database concepts, it is time to move into a review of the language you will use to interact directly with a database. In this lesson, you will practice a few fundamental SQL operations.
This video doesn't have any notes.
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
Welcome back,
we're about ready to start coding,
0:00
but before we do so,
I'd like to touch on an acronym.
0:03
This is programming, after all.
0:06
You didn't think you'd get through
a course without more acronyms, did you?
0:08
The one I want to mention is RDBMS,
and it stands for
0:11
relational database management system.
0:15
What this is is the actual tool or
vendor used as a database provider.
0:18
Now, there are a lot of big names
out there such as MySQL and
0:23
PostgreSQL in the open source realm.
0:27
There's Oracle and Microsoft SQL Server
in the proprietary realm.
0:30
And there are a couple lightweight
options with file-based modes,
0:33
such as SQLite and H2,
both of which we'll use in this course.
0:37
It's worth mentioning here that
there are some non-relational
0:42
database management systems out there,
most notably, MongoDB.
0:45
We'll assume that in this course
we're working with relational data.
0:49
That is, we'll assume here that we have a
SQL based database that contains a set of
0:53
related tables.
0:59
Let's now hop over to the SQL playground
for a review of how to create and
1:01
interact with data using SQL.
1:05
I've created a SQL playground for our use
in the first part of this course, and
1:09
that's what I'm in right now.
1:12
People often use the term schema to
refer to a database, all its tables, and
1:14
all the columns of those tables.
1:19
In general, schema refers to the structure
of the database without regard to
1:21
the actual data that's stored.
1:24
Currently, our database
has an empty schema.
1:27
We can see that by clicking
on Database Schema and
1:29
noticing the message that says no tables.
1:33
Let's add a table to our
currently empty database.
1:36
I'll click on Add a contacts table.
1:39
In here we'll write the SQL to add a
contacts table with all the columns that I
1:42
mentioned earlier.
1:46
So we'll start that using
the create table syntax.
1:47
I'll call the table contacts and just for
1:51
good formatting purposes, I'm going to
list each column on a line of its own.
1:54
The first column is going to be named id,
and
1:59
that's going to be our
integer primary key.
2:02
And then we have a first name,
which is a string, last name,
2:07
which is a string, email,
which is a string.
2:11
And then we'll have a phone number,
2:15
I'll just call that phone,
that will be an integer.
2:17
And I will only need ten
digits to store that,
2:20
this is will be
a United States phone number.
2:23
After clicking Run, you should see
the contacts table show up in the sidebar.
2:27
And upon clicking on it,
2:32
we'll see the structure that we
just created with all five columns.
2:33
Now that we have a table,
let's go to the Create new contacts query.
2:37
And in here,
I'll add a couple insert statements, so
2:42
that we can get a couple
contacts into the contacts table.
2:44
So I'll use my INSERT INTO syntax.
2:48
And specify the name of the table
I'd like to insert into,
2:52
which is contacts in this case.
2:54
And then in parentheses I'll
list the column names for
2:56
which I will be providing values.
2:59
That'll be (firstname,
lastname, email, phone).
3:01
Next I'll specify the values
that I'd like to insert.
3:07
And again, for formatting purposes, I'll
list the values on lines of their own.
3:11
Remember, if you have a string value,
3:17
don't forget to put that
value in single quotes.
3:18
So I'll put my first and
last name in here, my email address.
3:24
And then just some old phone number,
3:30
773, we'll go 5554535.
3:35
Excellent, now, so
that we have a couple rows to work with,
3:40
I'm gonna add a semicolon here and
include a second insert statement.
3:44
I'm simply going to copy and
3:49
paste this statement that I've already
typed, and then change the values.
3:51
Let's go, James Gosling.
3:56
And I'll say James@java.com.
4:03
I'm sure that's not his
actual email address.
4:06
5555557788.
4:09
Excellent, now we have
two insert statements.
4:14
Let's go ahead and click Run and
4:17
then you should see this message
that says Query returned no results.
4:19
Now, that's okay because we weren't
asking for any data from the table but
4:24
rather, storing data into the table.
4:28
Let's create another query to read,
4:31
or fetch,
all the rows from the contacts table.
4:33
So I'm gonna click on this
Read all contacts query.
4:36
And into it I'm going
to select all columns,
4:39
remember that's what this asterisk means,
from the contacts table.
4:43
Let's click Run.
4:47
Excellent, so
what you should see down below
4:50
are the data rows returned from
the query in this bottom panel.
4:52
And in this case, there are the two
rows that we just inserted with our
4:56
insert statements in the other query.
4:59
Now, let's review the update
operation in SQL.
5:02
I'm gonna click on the Update
a contact query, and
5:05
add an update statement that will
update one of the row's first names.
5:08
So here we go, I'll start with
the UPDATE syntax UPDATE contacts.
5:14
I'm gonna SET the firstname equal to,
5:20
let's expand my first name
to Christopher from Chris.
5:23
And I need to specify some
unique identifier for
5:27
the row that I'd like to update,
otherwise it's going to update all rows so
5:32
that all first names
are going to be Christopher.
5:36
So I will specify which row I'd
like to update by including
5:39
a WHERE clause where the id=1.
5:44
So let's run this query, and again,
5:48
we should see this message that
says Query returned no results.
5:50
To verify that the update
happened successfully,
5:54
I'm gonna go back to the Read all
contacts query and run that one again.
5:57
And here in this first column,
the firstname column,
6:02
I can verify that my first name
was indeed changed to Christopher.
6:04
Okay, we have one final operation to
review and that's the delete operation.
6:10
If we want to delete a row form contacts,
we simply use the following syntax.
6:15
DELETE FROM, specify the name of the table
from which we'd like to delete a row or
6:20
multiple rows.
6:26
And again,
make sure you use that WHERE clause so
6:28
you specify which row or
rows that you precisely want to delete.
6:31
If you exclude this
WHERE clause right here,
6:36
you'll delete all rows
from the contacts table.
6:38
So let's go ahead and run this query.
6:41
And see, by going back to
the Read all contacts query,
6:44
if I indeed deleted myself.
6:49
Perfect, I'm gone!
6:51
Now what we've just reviewed are called
the CRUD actions that every application
6:54
will need in order to expose a full
interactive interface to users.
6:59
What I mean by CRUD is create,
read, update, and delete.
7:03
Now that databases in SQL
are fresh in your minds,
7:10
we'll turn to how to interact
with one using Java.
7:12
But first, why don't you answer
a couple questions about SQL?
7:16
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