SQL-Basics: Create – Read – Update – Delete

This episode is about the basic statements needed to create, read, update and delete data in a database system. It is part of my SQL-Tutorial

Motivation

Let’s assume we work as a data scientist for Knight Industries.  We want to help the Foundation of Law and Government to keep track of our operatives.

We decide to use a classic relational database management system or RDBMS. In order to explore Database Management Systems we can either install one locally or we can use an online tool like SQLFiddle.

To interact with RDBMS we use SQL – the Structured Query Language.

As the name says SQL (speak either S-Q-L or Sequel) is used to write structured queries. Think of “conversations” when You think of “queries”.

So, let’s fire up SQLFiddle.

Creating the table

At first we have an empty database, so we create a table named operatives with the following columns: id, name and birthday

CREATE TABLE ki_operatives (id INTEGER, name TEXT, birthday DATE, PRIMARY KEY(id));

Type that into the left column of the SQLfiddle window and press “Build Scheme”.  Everything green? If not, please check for typos. Please also make sure that MySQL 5.6 is selected in the menu bar.

So we learned our first SQL statement: CREATE TABLE. It does exactly what its name suggest: creating a table in a database.

In SQL it does not matter if you write the statements in all lower or upper case, we could have written as well: “create table”, but as good practice let’s write SQL keywords always in all uppercase letters.

INTEGER, TEXT and DATE are built-in data types of SQL. PRIMARY KEY (id) tells the DBRMS that the id column is a special one: it may not contain duplicates and must always have a value assigned.

With that kind of unique identifier we can always reference an entry in a table.

To check if the table was created we can type

SHOW TABLES;

into the right column and press “Run SQL”.

All we have by now is an empty table. We can check that by typing the following statement into the right column as well and press “Run SQL” again.

SELECT * FROM ki_operatives;

The Output should read like “Record Count: 0;”

SELECT is SQLs way of getting data out of the database. The * means “all columns”, so you don’t need to know all the column names of a table beforehand.

Adding some data

As most important operative we add Michael Arthur Long to our operatives table:

INSERT INTO ki_operatives (id, name, birthday) VALUES (1, "Michael Arthur Long", "1949-01-09");

Good practice: Although You could insert the first record into the database without repeating the column names it is considered a good practice to state the names of the columns you want to insert your data into.

We add three more of our employees to the operatives table.

INSERT INTO ki_operatives (id, name, birthday)VALUES 
(2, "Devon Miles", "1942-07-12");
INSERT INTO ki_operatives (id, name, birthday)VALUES 
(3, "Dr. Bonnie Barstow", "1954-11-24");
INSERT INTO ki_operatives (id, name, birthday)VALUES 
(4, "Reginald Cornelius III", "1952-05-04");

Times they are a changing

Michael Arthur Long gets his new name: Michael Knight. We update his record in our database.

UPDATE ki_operatives SET name = "Michael Knight" WHERE id = 1;

The Update keyword does again exactly what its name suggests: updating a database record. With SET You select a specific column and the WHERE clause specifies the row of the table.

We want to keep track of the different occupations of our team members and add a new column to operatives table.

ALTER TABLE ki_operatives ADD COLUMN occupation TEXT;

With the ALTER TABLE clause we can add or delete columns or change the data type of a column as well.

Let’s update the occupation of all operatives:

UPDATE ki_operatives SET occupation = "Field Agent" WHERE id = 1; 
UPDATE ki_operatives SET occupation = "Head of Operations" WHERE id = 2; 
UPDATE ki_operatives SET occupation = "Technician" WHERE id = 3; 
UPDATE ki_operatives SET occupation = "Truck Driver" WHERE id = 4;

Bonnie left in 1983 to pursue her graduate studies. We remove her from our operatives database.

DELETE FROM ki_operatives WHERE id = 3;

DELETE is the clause for -you guessed it- deleting records from the database. Be careful: Don’t forget the WHERE clause or you delete all columns from the table at once!

Because FLAG needs a lead technician, we add April Curtis as Head of Design Team.

INSERT INTO ki_operatives VALUES (5, "April Curtis", "1952-05-04", "Senior Design Officer");

To evaluate each step of our implementation we can always run

SELECT * FROM operatives;

in the right window by clicking “Run SQL”

Today we learned

• creating a table
• inserting some data into it
• reading data from the table
• adding more columns
• deleting data

In the next article we will deal with the “relational” in the “relational database management system”.

For a relation we need at least to parties / tables that know each other.

Read the next chapter:  SQL-Basics: Relations