Introduction

Welcome to this database and SQL tutorial.

The Structured Query Language is somtimes seen as the most important invention; well, right after the washing machine;).

It is custom to begin with a “Hello World” example:

select "Hello World";

Now try it yourself and press Run Code. You can also Start Over or have a look at the proposed Solution.

select "Hello World"

Now try to display (with the keyword select) some basic calculations (1+2+3)*2.

select (1+2+3)*2

In SQLite You can concatenate strings and numbers by using ||. In MySQL you need to use the concat function. Concatenate “hello”, “world: 1+2+3 =” and 1+2+3.

select "hello" || " world: 1+2+3 = " || (1+2+3)

In MySQL the solution is: select concat("hello", " world: 1+2+3 = ", 1+2+3)

Now, after this little “ice-breaker”, let us start learning SQL via an example.

The aim is to build a car sales system, and gain business insights.

First, we will need customers to buy cars. Of course, we must have cars that can be sold to the customers. The sales transactions need to be recorded as well. Hence, we need three tables: customer, car and sale.

We will use the SQLite database system and assume that an empty database exists. Note, that this tutorial will work for almost all relational databases such as PostgreSQL and MySQL. I have also provided a similar tutorial for Access. If you are interested in the popularity of database engines go to db-engines.com.

Table operations

We begin by creating a customer table.

create table customer (info text)

Executing the above SQL command does not show any output. However, we can see the table using the sqlite_master table. The following select query displays the customer table name.

select name from sqlite_master

In MySQL use show tables.

We have not quite thought through this. Let us delete the table again.

drop table customer

What information do we need from a customer? Name, phone number, email and an address seem to be a reasonable starting point. How can we identify a customer? We could use the name as identifier. However, if there are two people with exactly the same name then there is an issue. A number to identify these individuals could resolve this. More generally using a unique integer number to identify a record is common practice. A unique identifier for a record is known as primary key. Note, it could be an integer number, but it could be any field (e.g. the customer’s name assuming its uniqueness). The following SQL statement puts this all together:

create table customer 
(
  id int primary key,
  name text,
  phone_number text, 
  email text, 
  address_building text,
  address_street text,
  postcode text
)

Here, the field names are id, name, phone_number, email, etcetera. These are also known as column names. On the right of each field name is a data type specifier. Here, we used int (representing whole numbers, integers) and text (representing characters). Right next to the field id we wrote primary key. This is a constraint on the field, which means when inserting a record a unique value needs to be provided. To summarise a field contains a field name, its data type and a constraint.

Again, we can see the table in the master overview.

select * from sqlite_master

Insert data

Now, let us insert a bit of test data.

insert into customer values
(1, 'Wolfgang','0779...','w.garn@surrey.ac.uk',
 '20MS02','UoS','GU2 7XH')

The above represents an entire record. A row in a table is known as record or tuple.

To verify that we have really inserted the above values we can execute the select query.

select * from customer

Let us insert the customers with names Dominic, Michael and spook. When inserting incomplete records (i.e. some field values are missing) the column names (especially required fields, such as primary key) have to be specified.

insert into customer (id, name) 
values (2,'Dominic'),(3,'Michael'),(4,'spook')

View the previously inserted records.

select * from customer

Changing data

Okay, we really don’t want to have a “spook” customer. So, let us remove that customer.

delete from customer where name = "spook";

In order to display only the column id and name we replace the asterisk.

select id, name from customer

Here, we see the “beauty” of SQL - it is intuitive and natural: “delete from my customer table where the name is spook” (at least somewhat natural). Important is that we used the where clause, otherwise all records would disappear.

Let us assume we need to “correct” (update) the customer with identifier 1 and add the surname “Garn”.

update customer 
set name = "Wolfgang Garn"
where id = 1

Observe the result of the update query.

select * from customer;

Now, assume we want a new field in the customer table, which identifies the status of being a current (someone in the middle of purchase), potential (showed some interest) or no-longer (moved on). This means we will alter (change) the table.

alter table customer
add status text

In MySQL all columns are displayed via describe customer.

select * from customer;

If we decide adding the field was not a good idea, it can be undone with alter table customer drop status.

update customer set name = 'Dominic Garn' where id=2
-- update customer set name = 'Michael Garn' where id=3

select name from customer

update customer set status='current'   where id = 1
-- update customer set status='no-longer' where id = 2
-- update customer set status='potential' where id = 3

select id, name, status from customer

Car Table

Now, that we have the customer table. Let us create a car table. So, that we can sell these type of cars to customers. What fields should we add? What car do you currently drive? How much was it? What fuel does it need? This gives us the field names: manufacturer, model, price and fuel type. We need to decide about the data types - text seems to be fine for all but the price column, where we will use float. Again, let us introduce the column id as primary key.

create table car (
  id int primary key,
  manufacturer text, -- Mazda
  model text, -- CX-5 
  price float, -- £27,000 
  fuel_type varchar(255) -- diesel
)

Show the car table using the sqlite_master.

select * from sqlite_master where name = "car"

Insert a few test-records.

insert into car values
(1,'Mazda'     ,'CX-5'  ,27000, 'diesel'),
(2,'Mazda'     ,'MX-30' ,35000, 'electric'),
(3,'BMW'       ,'i3'    ,35000, 'electric'),
(4,'BMW'       ,'2'     ,27000, 'petrol'),
(5,'Volkswagen','e-golf',27000, 'electric')

select * from car

Insert two more records a Volkswagen - GTI with petrol, which costs £33k; and Ford - Fiesta Van with LPG, which costs £35k.

insert into car values
(6,'Volkswagen','GTI'   ,33000, 'petrol'),
(7,'Ford','Fiesta Van'  ,35000, 'LPG')

Display all car entries:

select * from car

Lookup Tables

In the previous table we use fuel type with several repetition. In order to avoid inconsistent spelling (e.g. electric, electrical or Electric). We could introduce a lookup table.

  create table fuel (type varchar(255) primary key)

A lookup table only has one field, which is a primary key. Let us insert the lookup values:

insert into fuel
values ('diesel'),('electric'),('petrol'),('LPG')

Display the table:

select * from fuel

PRAGMA foreign_keys = ON;

PRAGMA foreign_keys;

drop table car

create table car (
  id int primary key,
  manufacturer text, model text, price float,
  fuel_type varchar(255) REFERENCES fuel(type) ON UPDATE CASCADE  
)

alter table car add constraint constraint_fuel_type 
foreign key (fuel_type) references fuel(type) on update cascade;

create table car (
  id int primary key,
  manufacturer text, model text, price float,
  fuel_type varchar(255),
  foreign key (fuel_type) REFERENCES fuel(type) ON UPDATE CASCADE  
); 

insert into car values
(1,'Mazda'     ,'CX-5'  ,27000, 'diesel'),
(2,'Mazda'     ,'MX-30' ,35000, 'electric'),
(3,'BMW'       ,'i3'    ,35000, 'electric'),
(4,'BMW'       ,'2'     ,27000, 'petrol'),
(5,'Volkswagen','e-golf',27000, 'electric'),
(6,'Volkswagen','GTI'   ,33000, 'petrol'),
(7,'Fiesta Van','Ford'  ,35000, 'LPG')

update fuel set type='Diesel' where type = 'diesel'

select * from fuel

select * from car

create table customer_status 
 (status varchar(255) primary key)

insert into customer_status 
values ('current'), ('potential'), ('no-longer')

drop table customer

create table customer 
(
  id int primary key,
  name text,
  phone_number text, 
  email text, 
  address_building text,
  address_street text,
  postcode text,
  status text REFERENCES customer_status(status) ON UPDATE CASCADE  
)

insert into customer values
(1, 'Wolfgang','0779...','w.garn@surrey.ac.uk','20MS02','UoS','GU2 7XH','current'),
(2, 'Dominic','','','','','','no-longer'),
(3, 'Michael','','','','','','potential')

select id, name, status from customer

Sale Table

Next, we’d like to create a table for the sales. This table will need a foreign key to the customer table and another one to link to the car table. What other sale’s information do we need. The date of the sale would be definitely good. A quantity field would be good assuming a car record represents “unlimited” supply of this type of car. Let us assume - for simplicity - that the price in the car table is a fixed retail price.

create table sale (
  id int, -- sale id
  customer_id int REFERENCES customer(id) ON UPDATE CASCADE,
  car_id int REFERENCES car(id) ON UPDATE CASCADE,
  sale_date text,
  quantity int
)

Note, SQLite does not have a specific data type for date/time, but text, real and int can be used (see sqlitetutorial.net/sqlite-date for examples).

Let us add three records to the sale-table. Let us say say Wolfgang purchased a BMW i3 on the 2nd of March 2021, and bought a Mazda CX-5 on 17th November 2014. Dominic bought a GTI on the 15th of August 2020.

 insert into sale values
 (1, 1, 1, '2014-11-17 10:00:00.0',1),
 (2, 1, 3, '2021-03-02 11:00:00.0',1),
 (3, 2, 6, '2020-08-15 17:00:00.0',1)

 select * from sale

Queries

Now, it would be great to display the following information: all the sale records but with customer name, manufacturer, model and sale’s date. This means we have to collect the information from the customer, car and sale table.

select name, manufacturer, model, sale_date
from customer, car, sale 
where sale.customer_id = customer.id and 
      sale.car_id = car.id

The function strftime('%Y-%m-%d %H:%M:%S', ...) can be used to extract any specific date/time information. date and time are two more useful functions.

Now, that we have built a basic car-sales-system it is time to operate it. Okay, some time has passed and the tables have been filled with some data.

select name, email, phone_number from customer
where name like '%Wolf%'

select price, quantity
from car, sale
where sale.car_id = car.id

select sum(price* quantity) as revenue
from car, sale
where sale.car_id = car.id

select manufacturer, model, sum(quantity) as nb
from car, sale
where sale.car_id = car.id
group by manufacturer, model
order by nb desc

Summary

We introduced fundamental SQL statements by building a car-sales-system and showing how to use it. Concepts such as creating a table, inserting and updating its data were introduced. Simple queries and aggregates were mentioned.

Resources

• w3schools.com is a great systematic introduction into SQL.
• Shah (2020) is a hands-on introduction to data science (Chapter 7 introduces MySQL).

References