Basic SQL commands

Learning Objectives

Understand how to construct and execute SQL statements to create, read, update and delete data held in a relational database.

Pre-requisites

Sign up for a (free) Repl account

Lesson

A Relational Database Management System (RDBMS) refers to a database that stores data using rows and columns. Structured Query Language (SQL) is used to create, read, update, and delete data in RDMSs.

Relational Databases stores data in tables. Here is an example table called COMPANIES, which stores data about software companies:

Tables are made up of columns, each which have a specific Data Type.
The Data Types for different SQL databases vary slightly, for SQLite they are as follows:

The SQL command to create the COMPANIES table would look as follows:

CREATE TABLE COMPANIES(id INTEGER, 
                          name TEXT, 
                          location TEXT,
                          numEmployees INTEGER);

A column (or set of columns) marked as a PRIMARY KEY is a column (or set of columns) that has to be unique i.e. you cannot insert rows with the same value in this column and the value cannot be null. It is good practice to have a PRIMARY KEY on each table. The syntax for adding a PRIMARY KEY is as follows:

CREATE TABLE companies(id INTEGER PRIMARY KEY, 
                          name TEXT, 
                          location TEXT,
                          numEmployees INTEGER);

Here, we are specifying that the id column is the PRIMARY KEY i.e. has to have unique values. You can also use this idempotent command which is very useful if you use an in memory database, or when someone runs your code for the first time.

CREATE TABLE IF NOT EXISTS companies(id INTEGER PRIMARY KEY, 
                          name TEXT, 
                          location TEXT,
                          numEmployees INTEGER);

An alternative to creating your own unique identifier to populate an id column is to use an AUTO INCREMENTing number. SQL provides a way to to do this. Or use a Universally Unique Identifier, or UUID, which are 128 bit numbers, composed of 16 octets and represented as 32 base-16 characters that looks something like 27dce754-7a31-4411-bbc0-383c731b34f8.

CREATE

Create new rows in the database with the following;

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

You can try...

INSERT INTO companies (name, location, numEmployees)
VALUES ("Google", "USA", 135000);

READ

To read values out of the database you can use the following:

SELECT column1, column2, ...
FROM table_name;

Try it

SELECT name, numEmployees
FROM companies;

This statement can be extended with the WHERE keyword to be more selective what is read from the database. The WHERE clause can filter records for you. An example is below.

SELECT * FROM companies
WHERE numEmployees > 100000;

In the statement above we return all the columns, the * is a short hand for all the columns which is equivalent to asking for the row. The WHERE clause identifies the column numEmployees and the condition is to only return rows where the value for this field is greater than 100000.

There is lots you can do with SELECT and WHERE.

UPDATE

What to change a value or set of values? You need the UPDATE statement.

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Lets update the location of Google HQ.

UPDATE TABLE companies SET location="California" WHERE name="Google";

DELETE

Had enough? Remove data like this.

DELETE FROM table_name WHERE condition;

This would delete all the companies who's location field has the value of "California".

DELETE FROM companies WHERE location="California";

You can also delete entire tables;

DROP TABLE companies;

And yes you can really throw your toys out the pram.

DROP DATABASE main;

Assignment

Here is a UML class diagram representing a model for a restaurant ordering app.

Imagine we write some code to create instances of these classes with real Restaurant and Menu data. The problem is all the data is lost when your app stops. The data that makes the app persists only in memory, so when the process dies, so does the data. Your challenge in this assignment is to provide a persistent way of storing this data.

1. Using a SQLite Repl, compose the SQL queries to do the following:
   • Implement the Restaurants, Menus and MenuItems tables - make the id of each a PRIMARY KEY (this has to be unique) and make this AUTO INCREMENTing
   • Insert rows into each table using "INSERT"
   • Read back your row data using the SELECT command
   • Update your rows using the UPDATE command
   • Delete your rows using DELETE command
   • Use the WHERE clause to filter results
2. Share your Repl with your coach for review

You should also be aware of 2 SQLite specific SQL statements which will help you.
The PRAGMA command describes a table by listing its columns and indicating which column is the primary key.

PRAGMA table_info(companies);

The following command lists all the tables in the database:

SELECT name FROM sqlite_master WHERE type='table'

Assignment extension tasks

Explore the SQL documentation for the SUM, COUNT and GROUP BY aggregate functions

Research how you would modify a table's structure after it has been created.

Additional resources

NoSQL versus SQL databases