Welcome back, we're about ready to start coding,

but before we do so, I'd like to touch on an acronym.

This is programming, after all.

You didn't think you'd get through a course without more acronyms, did you?

The one I want to mention is RDBMS, and it stands for

relational database management system.

What this is is the actual tool or vendor used as a database provider.

Now, there are a lot of big names out there such as MySQL and

PostgreSQL in the open source realm.

There's Oracle and Microsoft SQL Server in the proprietary realm.

And there are a couple lightweight options with file-based modes,

such as SQLite and H2, both of which we'll use in this course.

It's worth mentioning here that there are some non-relational

database management systems out there, most notably, MongoDB.

We'll assume that in this course we're working with relational data.

That is, we'll assume here that we have a SQL based database that contains a set of

related tables.

Let's now hop over to the SQL playground for a review of how to create and

interact with data using SQL.

I've created a SQL playground for our use in the first part of this course, and

that's what I'm in right now.

People often use the term schema to refer to a database, all its tables, and

all the columns of those tables.

In general, schema refers to the structure of the database without regard to

the actual data that's stored.

Currently, our database has an empty schema.

We can see that by clicking on Database Schema and

noticing the message that says no tables.

Let's add a table to our currently empty database.

I'll click on Add a contacts table.

In here we'll write the SQL to add a contacts table with all the columns that I

mentioned earlier.

So we'll start that using the create table syntax.

I'll call the table contacts and just for

good formatting purposes, I'm going to list each column on a line of its own.

The first column is going to be named id, and

that's going to be our integer primary key.

And then we have a first name, which is a string, last name,

which is a string, email, which is a string.

And then we'll have a phone number,

I'll just call that phone, that will be an integer.

And I will only need ten digits to store that,

this is will be a United States phone number.

After clicking Run, you should see the contacts table show up in the sidebar.

And upon clicking on it,

we'll see the structure that we just created with all five columns.

Now that we have a table, let's go to the Create new contacts query.

And in here, I'll add a couple insert statements, so

that we can get a couple contacts into the contacts table.

So I'll use my INSERT INTO syntax.

And specify the name of the table I'd like to insert into,

which is contacts in this case.

And then in parentheses I'll list the column names for

which I will be providing values.

That'll be (firstname, lastname, email, phone).

Next I'll specify the values that I'd like to insert.

And again, for formatting purposes, I'll list the values on lines of their own.

Remember, if you have a string value,

don't forget to put that value in single quotes.

So I'll put my first and last name in here, my email address.

And then just some old phone number,

773, we'll go 5554535.

Excellent, now, so that we have a couple rows to work with,

I'm gonna add a semicolon here and include a second insert statement.

I'm simply going to copy and

paste this statement that I've already typed, and then change the values.

Let's go, James Gosling.

And I'll say James@java.com.

I'm sure that's not his actual email address.

5555557788.

Excellent, now we have two insert statements.

Let's go ahead and click Run and

then you should see this message that says Query returned no results.

Now, that's okay because we weren't asking for any data from the table but

rather, storing data into the table.

Let's create another query to read,

or fetch, all the rows from the contacts table.

So I'm gonna click on this Read all contacts query.

And into it I'm going to select all columns,

remember that's what this asterisk means, from the contacts table.

Let's click Run.

Excellent, so what you should see down below

are the data rows returned from the query in this bottom panel.

And in this case, there are the two rows that we just inserted with our

insert statements in the other query.

Now, let's review the update operation in SQL.

I'm gonna click on the Update a contact query, and

add an update statement that will update one of the row's first names.

So here we go, I'll start with the UPDATE syntax UPDATE contacts.

I'm gonna SET the firstname equal to,

let's expand my first name to Christopher from Chris.

And I need to specify some unique identifier for

the row that I'd like to update, otherwise it's going to update all rows so

that all first names are going to be Christopher.

So I will specify which row I'd like to update by including

a WHERE clause where the id=1.

So let's run this query, and again,

we should see this message that says Query returned no results.

To verify that the update happened successfully,

I'm gonna go back to the Read all contacts query and run that one again.

And here in this first column, the firstname column,

I can verify that my first name was indeed changed to Christopher.

Okay, we have one final operation to review and that's the delete operation.

If we want to delete a row form contacts, we simply use the following syntax.

DELETE FROM, specify the name of the table from which we'd like to delete a row or

multiple rows.

And again, make sure you use that WHERE clause so

you specify which row or rows that you precisely want to delete.

If you exclude this WHERE clause right here,

you'll delete all rows from the contacts table.

So let's go ahead and run this query.

And see, by going back to the Read all contacts query,

if I indeed deleted myself.

Perfect, I'm gone!

Now what we've just reviewed are called the CRUD actions that every application

will need in order to expose a full interactive interface to users.

What I mean by CRUD is create, read, update, and delete.

Now that databases in SQL are fresh in your minds,

we'll turn to how to interact with one using Java.

But first, why don't you answer a couple questions about SQL?