S..Q..L.. “Structured Query Language”. SQL is a language for handling database CRUD:
Create, Read, Update and Delete. With SQL, you can create databases and tables... select
specific pieces of data... make changes, remove data, and more. Much, much more… So pour
yourself a pan galactic gargle blaster, because we are going on a scenic tour of SQL Space.
Future users of large data banks must be protected from having to know how the data is organized
in the machine… E. F. Codd... This is the opening line from the paper “A
Relational Model of Data for Large Shared Data Banks”
In 11 pages Ted Codd laid the foundation for relational databases.
A relational database consists of a collection of tables.
Each table has a defined set of columns and rows containing data.
Each row is called a record. The data between tables can be related to
each other. This is why they are called “relational”
databases. Taken together, the collection of tables in
a database is called the schema. So when you are designing a database, you
could say that you are … scheming.
In the beginning there was nothing. A digital void.
To begin our SQL Tour, let us turn on our server.
It is running a database management system - D.B.M.S. for short.
First, we connect to the DBMS. Then we create a database.
You create a database using the bizarre command “CREATE DATABASE”.
After the command, specify the database name. This will create a database called “social_network.”
Because this is precisely what the world needs - another social network…
By the way, do not worry about which DBMS is being used.
Focus on the SQL… Focus on the SQL... Next, we need to fill this database with some
tables. To make a table, you use the … wait for
it ... “CREATE TABLE” command. You specify the name of the table, and then
type an open parentheses. Inside you specify the columns this table
will have. This table will be called “users” and
have 4 columns: user_id... first name... last name... and
email address. After the name of each column, you enter the
datatype. Once we have listed all of the columns, we
close the parentheses and enter a semicolon. SQL commands generally end in a semicolon.
Execute… This creates a new table called “users”
in our “social_network” database. But wait, what if you are a typical human
and forgot a column in the “users” table. You can change an existing table by using
the “ALTER TABLE” command. You first specify the table you would like
to change. then enter the “ADD” command. Next, you provide the name and type of the
new column. This command will add a new column called
“encrypted_password” You do not want to store a user’s password
in plain text… As you can hear, I am using my serious voice.
We will hake a serious discussion about the seriousness of security with my serious voice
another time. Execute…
This SQL command added a new column to our “users” table.
If there is a part of a database you no longer want or need, you can cancel its existence
with the DROP command.
To eliminate the “email” column, you alter the table and DROP the email column;
To delete the “users” table altogether, you execute DROP table users;
And to wipe the slate completely clean, you can even drop the database.
Here we are.. back to where we started.
Now that you have seen the essentials for creating, altering, and deleting databases,
let us visit the various ways you can interact with the data.
Suppose we have a database with three tables: users… movies… and purchases…
These tables can be created using the CREATE DATABASE and CREATE TABLE commands.
In this diagram, we display the name of the table at the top, and the columns in the table
below. You can insert data into the database using
the INSERT INTO command. Let us add a record to the “movies” table.
You first type “INSERT INTO” followed by the table name.
Next, inside parentheses, specify the columns you will be providing data for.
Then the command “VALUES” followed by the corresponding data in parentheses.
And do not forget the semicolon. Execute…
Our data has been inserted into the movies table.
But how can we be sure? To look at data, you use the SELECT statement.
After SELECT, you specify the columns of data you would like.
If you enter an asterisk, it will return ALL columns.
The FROM command is used to specify the table name.
Execute… Our one movie is there.
Notice in the “users” and “movies” tables there are columns with names ending
in “ID”. These are numbers which uniquely identify
each row of data. Such values are called “primary keys.”
We provided the value for “movie_id”. But most respectable database software will
create the IDs for you. So going forward, we will not specify the
“user id” or “movie id”. If you only want to view the movie titles,
you can perform a SELECT with just the “titles” column.
As before, you use FROM to identify the table to select from.
Execute… Let us now populate our database with a lot
more data… Generally you will use software to insert
data.
Let us now select all the movies and their prices.
Notice they are not in any particular order. You can order by PRICE with the ORDER BY command.
By default, the records are sorted in ascending order.
You can sort in descending order with the DESC keyword;
You can change existing data with the UPDATE statement.
If you want to update the price of Jaws, you specify the table to update…
Use the “SET” statement to specify the new value.
And then use a WHERE statement to restrict the update to specific records.
Execute.... And verify the change...
To get rid of data, use the DELETE FROM statement. After “DELETE FROM” enter the table name.
Next, enter a WHERE statement to specify which records to delete.
This will delete the movie “Star Wars” from the database.
… A quick SELECT will confirm its disappearance.
Much like the original scene where Han shot first.
But we are just getting started.
What if the data you need is scattered across multiple tables?
The SELECT statement gives you the ability to choose data from multiple tables in a process
called “Joins”. A related solution would be to create a virtual
table called a “View”. This table-like object collects matching data
from multiple tables and makes it easily accessible as if you were working with a single table.
And what about speed? When you execute a SELECT statement with a
WHERE clause, the database may have to look at every single record in the table to find
matching data. If there are billions of records, this can
be slow. An Index can be created to ensure your queries
are fast and efficient. Transactions provide you the ability to make
several changes and ensure that your data is safe if there is a problem part way through
the process. The list of features is long...
Today we have seen the essential SQL statements. The ability to manage a database schema with
the CREATE, ALTER, and DROP commands. The statements used when working with data: SELECT,
INSERT, UPDATE, and DELETE. But now it is time to take that can of air and clean out
your keyboard. Grab a towel, don’t panic, and click subscribe. Because you will want
to hitch a ride on this ship.
Subscribe to see the next exciting episode of SQL.