SQL

Definition
Creating
Filtering Data
Aggregating Data
Subqueries
Joins
Playground
Learning Resources

3 rectangles stacked on top of 1 another.

A beginner's guide to SQL.

Last Tended	Nov 2021
Planted	Oct 2021
Status	Seed
Tags	Data

Definition

SQL stands for Structured Query Language. Its purpose is to allow users to interact with relational databases. Data is structured in tables. Each table has:

1 column for each field &
1 row for each record.

An SQL statement is composed of:

an ordered list of clauses (example: SELECT, FROM, WHERE, …),
each clause has its own syntax &
each statement ends in ';'.

3 rectangles stacked on top of 1 another decorated with measurement guides.

Creating

schema.sql

output

CREATE TABLE fleet (
    /* PRIMARY KEY - values must be unique */
    id INTEGER PRIMARY KEY,
    name TEXT,
    class TEXT,
    length INTEGER
);

INSERT INTO fleet VALUES (1, 'a-wing', 'fighter', 7);
INSERT INTO fleet VALUES (2, 'cruiser', 'corvette', 127);
INSERT INTO fleet VALUES (3, 'x-wing', 'fighter', 13);
INSERT INTO fleet VALUES (4, 'y-wing', 'bomber', 23);

CREATE TABLE fleet (
    /* PRIMARY KEY - values must be unique */
    id INTEGER PRIMARY KEY,
    name TEXT,
    class TEXT,
    length INTEGER
);

INSERT INTO fleet VALUES (1, 'a-wing', 'fighter', 7);
INSERT INTO fleet VALUES (2, 'cruiser', 'corvette', 127);
INSERT INTO fleet VALUES (3, 'x-wing', 'fighter', 13);
INSERT INTO fleet VALUES (4, 'y-wing', 'bomber', 23);

schema.sql

fleet

id	name	class	length
1	a-wing	fighter	7
2	cruiser	corvette	127
3	x-wing	fighter	13
4	y-wing	bomber	23

Allow SQL to create IDs

schema.sql

output

CREATE TABLE fleet (
  id INTEGER PRIMARY KEY AUTO_INCREMENT,
  name TEXT,
  class TEXT,
  length INTEGER
);

INSERT INTO fleet(name, class, length) VALUES ('a-wing', 'fighter', 7);
INSERT INTO fleet(name, class, length) VALUES ('cruiser', 'corvette', 127);
INSERT INTO fleet(name, class, length) VALUES ('x-wing', 'fighter', 13);
INSERT INTO fleet(name, class, length) VALUES ('y-wing', 'bomber', 23);

CREATE TABLE fleet (
  id INTEGER PRIMARY KEY AUTO_INCREMENT,
  name TEXT,
  class TEXT,
  length INTEGER
);

INSERT INTO fleet(name, class, length) VALUES ('a-wing', 'fighter', 7);
INSERT INTO fleet(name, class, length) VALUES ('cruiser', 'corvette', 127);
INSERT INTO fleet(name, class, length) VALUES ('x-wing', 'fighter', 13);
INSERT INTO fleet(name, class, length) VALUES ('y-wing', 'bomber', 23);

schema.sql

fleet

id	name	class	length
1	a-wing	fighter	7
2	cruiser	corvette	127
3	x-wing	fighter	13
4	y-wing	bomber	23

Source

Squares, triangles & circles scattered on 1 side of a line, squares in a line on the other.

Filtering Data

query.sql

output

/* select all columns */
SELECT *
/* from table: 'fleet' */
FROM fleet;

/* select all columns */
SELECT *
/* from table: 'fleet' */
FROM fleet;

query.sql

fleet

id	name	class	length
1	a-wing	fighter	7
2	cruiser	corvette	127
3	x-wing	fighter	13
4	y-wing	bomber	23

query.sql

output

SELECT name, class
FROM fleet;

SELECT name, class
FROM fleet;

query.sql

fleet

id	name	class	length
1	a-wing	fighter	7
2	cruiser	corvette	127
3	x-wing	fighter	13
4	y-wing	bomber	23

query.sql

output

SELECT DISTINCT class
FROM fleet;

SELECT DISTINCT class
FROM fleet;

query.sql

fleet

id	name	class	length
1	a-wing	fighter	7
2	cruiser	corvette	127
3	x-wing	fighter	13
4	y-wing	bomber	23

query.sql

output

SELECT *
FROM fleet
/* ascending is default */
ORDER BY length DESC;

SELECT *
FROM fleet
/* ascending is default */
ORDER BY length DESC;

query.sql

fleet

id	name	class	length
2	cruiser	corvette	127
4	y-wing	bomber	23
3	x-wing	fighter	13
1	a-wing	fighter	7

query.sql

output

SELECT *
FROM fleet
ORDER BY class, name;

SELECT *
FROM fleet
ORDER BY class, name;

query.sql

fleet

id	name	class	length
4	y-wing	bomber	23
2	cruiser	corvette	127
1	a-wing	fighter	7
3	x-wing	fighter	13

query.sql

output

SELECT *
FROM fleet
LIMIT 2;

SELECT *
FROM fleet
LIMIT 2;

query.sql

fleet

id	name	class	length
1	a-wing	fighter	7
2	cruiser	corvette	127
3	x-wing	fighter	13
4	y-wing	bomber	23

query.sql

output

SELECT *
FROM fleet
WHERE name LIKE "%wing%"

SELECT *
FROM fleet
WHERE name LIKE "%wing%"

query.sql

fleet

id	name	class	length
4	y-wing	bomber	23
2	cruiser	corvette	127
1	a-wing	fighter	7
3	x-wing	fighter	13

Conditions

Comparison Operators

=	equals
<	less-than
<=	less-than or equals
>	greater-than
>=	greater-than or equals
!=	does not equal

query.sql

output

SELECT *			
FROM fleet 
WHERE length > 80;

SELECT *			
FROM fleet 
WHERE length > 80;

query.sql

fleet

id	name	class	length
1	a-wing	fighter	7
2	cruiser	corvette	127
3	x-wing	fighter	13
4	y-wing	bomber	23

query.sql

output

SELECT *			
FROM fleet 
/* math operator can be used */
WHERE length > 80 - 60;

SELECT *			
FROM fleet 
/* math operator can be used */
WHERE length > 80 - 60;

query.sql

fleet

id	name	class	length
1	a-wing	fighter	7
2	cruiser	corvette	127
3	x-wing	fighter	13
4	y-wing	bomber	23

query.sql

output

SELECT *			
FROM fleet 
/* when comparing letters, uppercase are smaller (come before) lower case */
WHERE name < 'd';

SELECT *			
FROM fleet 
/* when comparing letters, uppercase are smaller (come before) lower case */
WHERE name < 'd';

query.sql

fleet

id	name	class	length
1	a-wing	fighter	7
2	cruiser	corvette	127
3	x-wing	fighter	13
4	y-wing	bomber	23

query.sql

output

SELECT *
FROM fleet
/* values are inclusive */
WHERE length BETWEEN 7 AND 14;

SELECT *
FROM fleet
/* values are inclusive */
WHERE length BETWEEN 7 AND 14;

query.sql

fleet

id	name	class	length
1	a-wing	fighter	7
2	cruiser	corvette	127
3	x-wing	fighter	13
4	y-wing	bomber	23

query.sql

output

SELECT *
FROM fleet
WHERE length NOT BETWEEN 7 AND 14;

SELECT *
FROM fleet
WHERE length NOT BETWEEN 7 AND 14;

query.sql

fleet

id	name	class	length
1	a-wing	fighter	7
2	cruiser	corvette	127
3	x-wing	fighter	13
4	y-wing	bomber	23

query.sql

output

SELECT *
FROM fleet
WHERE length IN (7, 13, 23);

SELECT *
FROM fleet
WHERE length IN (7, 13, 23);

query.sql

fleet

id	name	class	length
1	a-wing	fighter	7
2	cruiser	corvette	127
3	x-wing	fighter	13
4	y-wing	bomber	23

query.sql

output

SELECT *
FROM fleet
WHERE length NOT IN (7, 13, 23);

SELECT *
FROM fleet
WHERE length NOT IN (7, 13, 23);

query.sql

fleet

id	name	class	length
1	a-wing	fighter	7
2	cruiser	corvette	127
3	x-wing	fighter	13
4	y-wing	bomber	23

Multiple Conditions

query.sql

output

SELECT *			
FROM fleet
WHERE class = 'fighter' AND length < 10;

SELECT *			
FROM fleet
WHERE class = 'fighter' AND length < 10;

query.sql

fleet

id	name	class	length
1	a-wing	fighter	7
2	cruiser	corvette	127
3	x-wing	fighter	13
4	y-wing	bomber	23

query.sql

output

SELECT *
FROM fleet
WHERE length < 10 OR class = 'corvette';

SELECT *
FROM fleet
WHERE length < 10 OR class = 'corvette';

query.sql

fleet

id	name	class	length
1	a-wing	fighter	7
2	cruiser	corvette	127
3	x-wing	fighter	13
4	y-wing	bomber	23

query.sql

output

SELECT *
FROM fleet
/* 'AND' is evaluated before 'OR' (brackets can be used to negate this) */
WHERE name = 'a-wing' OR class = 'fighter' AND length > 10;

SELECT *
FROM fleet
/* 'AND' is evaluated before 'OR' (brackets can be used to negate this) */
WHERE name = 'a-wing' OR class = 'fighter' AND length > 10;

query.sql

fleet

id	name	class	length
1	a-wing	fighter	7
2	cruiser	corvette	127
3	x-wing	fighter	13
4	y-wing	bomber	23

query.sql

output

SELECT *
FROM fleet
WHERE (name = 'a-wing' OR class = 'fighter') AND length > 10;

SELECT *
FROM fleet
WHERE (name = 'a-wing' OR class = 'fighter') AND length > 10;

query.sql

fleet

id	name	class	length
1	a-wing	fighter	7
2	cruiser	corvette	127
3	x-wing	fighter	13
4	y-wing	bomber	23

Squares, triangles & circles scattered on 1 side of a line, all shapes in oredered lines on the other.

Aggregating Data

Aggregate functions

COUNT	number of rows in a column
SUM	add values in a column
MIN	lowest value in a column
MAX	highest value in a column
AVG	average of a group of selected values

query.sql

output

SELECT SUM(length) 
FROM fleet;

SELECT SUM(length) 
FROM fleet;

query.sql

Query Result

SUM(length)
170

/* In previous examples, the original table with highlighted cells was displayed. In this example, the table resulting from the query is displayed. */

query.sql

output

SELECT class, SUM(length) 
FROM fleet
GROUP BY class;

SELECT class, SUM(length) 
FROM fleet
GROUP BY class;

query.sql

Query Result

class	SUM(length)
cruiser	170
fighter	20
bomber	23

query.sql

output

/* renaming column */
SELECT class, SUM(length) AS combined_length
FROM fleet
GROUP BY class;

/* renaming column */
SELECT class, SUM(length) AS combined_length
FROM fleet
GROUP BY class;

query.sql

Query Result

class	combined_length
cruiser	170
fighter	20
bomber	23

query.sql

output

SELECT class, SUM(length) AS combined_length
FROM fleet
GROUP BY class
/* a conditional on an aggregate (use HAVING instead of WHERE) */
HAVING combined_length > 21;

SELECT class, SUM(length) AS combined_length
FROM fleet
GROUP BY class
/* a conditional on an aggregate (use HAVING instead of WHERE) */
HAVING combined_length > 21;

query.sql

fleet

class	combined_length
cruiser	170
fighter	20
bomber	23

query.sql

output

SELECT *,
	CASE
    	WHEN length > 100 THEN "large"
        WHEN length > 10 THEN "medium"
        ELSE "small"
    END as "length_category"
FROM fleet

SELECT *,
	CASE
    	WHEN length > 100 THEN "large"
        WHEN length > 10 THEN "medium"
        ELSE "small"
    END as "length_category"
FROM fleet

query.sql

Query Result

id	name	class	length	length_category
1	a-wing	fighter	7	small
2	cruiser	corvette	127	large
3	x-wing	fighter	13	medium
4	y-wing	bomber	23	medium

Source

A line from 1 square to another, then to 3 smaller squares.

Subqueries

index.sql

output

/* schema */
CREATE TABLE fleet (
  id INTEGER PRIMARY KEY AUTO_INCREMENT,
  name TEXT,
  class TEXT,
  length INTEGER
);

INSERT INTO fleet(name, class, length) VALUES ('a-wing', 'fighter', 7);
INSERT INTO fleet(name, class, length) VALUES ('cruiser', 'corvette', 127);
INSERT INTO fleet(name, class, length) VALUES ('x-wing', 'fighter', 13);
INSERT INTO fleet(name, class, length) VALUES ('y-wing', 'bomber', 23);
<br/>
CREATE TABLE personnel (
  id INTEGER PRIMARY KEY AUTO_INCREMENT,
  name TEXT,
  starship TEXT,
  related_to INTEGER
);
<br/>
INSERT INTO personnel(name, starship, related_to) VALUES ('Luke', 3, 3);
INSERT INTO personnel(name, starship, related_to) VALUES ('Obi-Wan', 1, null);
INSERT INTO personnel(name, starship, related_to) VALUES ('Leia', null, 1);
<br/>
/* query */
SELECT *
FROM fleet
WHERE id IN (
  SELECT starship 
  FROM personnel
);

/* schema */
CREATE TABLE fleet (
  id INTEGER PRIMARY KEY AUTO_INCREMENT,
  name TEXT,
  class TEXT,
  length INTEGER
);

INSERT INTO fleet(name, class, length) VALUES ('a-wing', 'fighter', 7);
INSERT INTO fleet(name, class, length) VALUES ('cruiser', 'corvette', 127);
INSERT INTO fleet(name, class, length) VALUES ('x-wing', 'fighter', 13);
INSERT INTO fleet(name, class, length) VALUES ('y-wing', 'bomber', 23);
<br/>
CREATE TABLE personnel (
  id INTEGER PRIMARY KEY AUTO_INCREMENT,
  name TEXT,
  starship TEXT,
  related_to INTEGER
);
<br/>
INSERT INTO personnel(name, starship, related_to) VALUES ('Luke', 3, 3);
INSERT INTO personnel(name, starship, related_to) VALUES ('Obi-Wan', 1, null);
INSERT INTO personnel(name, starship, related_to) VALUES ('Leia', null, 1);
<br/>
/* query */
SELECT *
FROM fleet
WHERE id IN (
  SELECT starship 
  FROM personnel
);

index.sql

fleet

id	name	class	length
1	a-wing	fighter	7
2	cruiser	corvette	127
3	x-wing	fighter	13
4	y-wing	bomber	23

personnel

id	name	starship	related_to
1	Luke	3	3
2	Obi-Wan	1	null
3	Leia	null	1

A line connecting to squares, then leading to 3 smaller squares.

Joins

An SQL database commonly contains data that has been split up into multiple related tables. You can bring that data back together using joins.

The following examples will be referring to this data:

schema.sql

output

CREATE TABLE fleet (
  id INTEGER PRIMARY KEY AUTO_INCREMENT,
  name TEXT,
  class TEXT,
  length INTEGER
);

INSERT INTO fleet(name, class, length) VALUES ('a-wing', 'fighter', 7);
INSERT INTO fleet(name, class, length) VALUES ('cruiser', 'corvette', 127);
INSERT INTO fleet(name, class, length) VALUES ('x-wing', 'fighter', 13);
INSERT INTO fleet(name, class, length) VALUES ('y-wing', 'bomber', 23);
<br/>
CREATE TABLE personnel (
  id INTEGER PRIMARY KEY AUTO_INCREMENT,
  name TEXT,
  starship TEXT,
  related_to INTEGER
);
<br/>
INSERT INTO personnel(name, starship, related_to) VALUES ('Luke', 3, 3);
INSERT INTO personnel(name, starship, related_to) VALUES ('Obi-Wan', 1, null);
INSERT INTO personnel(name, starship, related_to) VALUES ('Leia', null, 1);
<br/>
CREATE TABLE teams (
  id INTEGER PRIMARY KEY AUTO_INCREMENT,
  member_a INTEGER,
  member_b INTEGER
);
<br/>
INSERT INTO teams(member_a, member_b) VALUES (1, 3);

CREATE TABLE fleet (
  id INTEGER PRIMARY KEY AUTO_INCREMENT,
  name TEXT,
  class TEXT,
  length INTEGER
);

INSERT INTO fleet(name, class, length) VALUES ('a-wing', 'fighter', 7);
INSERT INTO fleet(name, class, length) VALUES ('cruiser', 'corvette', 127);
INSERT INTO fleet(name, class, length) VALUES ('x-wing', 'fighter', 13);
INSERT INTO fleet(name, class, length) VALUES ('y-wing', 'bomber', 23);
<br/>
CREATE TABLE personnel (
  id INTEGER PRIMARY KEY AUTO_INCREMENT,
  name TEXT,
  starship TEXT,
  related_to INTEGER
);
<br/>
INSERT INTO personnel(name, starship, related_to) VALUES ('Luke', 3, 3);
INSERT INTO personnel(name, starship, related_to) VALUES ('Obi-Wan', 1, null);
INSERT INTO personnel(name, starship, related_to) VALUES ('Leia', null, 1);
<br/>
CREATE TABLE teams (
  id INTEGER PRIMARY KEY AUTO_INCREMENT,
  member_a INTEGER,
  member_b INTEGER
);
<br/>
INSERT INTO teams(member_a, member_b) VALUES (1, 3);

schema.sql

fleet

id	name	class	length
1	a-wing	fighter	7
2	cruiser	corvette	127
3	x-wing	fighter	13
4	y-wing	bomber	23

personnel

id	name	starship	related_to
1	Luke	3	3
2	Obi-Wan	1	null
3	Leia	null	1

teams

id	member_a	member_b
1	1	3

Types of Joins

Explicit Inner Join

query.sql

output

SELECT *
FROM fleet
JOIN personnel
ON fleet.id = personnel.starship

SELECT *
FROM fleet
JOIN personnel
ON fleet.id = personnel.starship

query.sql

Query Result

id	name	class	length	name	starship	related_to
3	x-wing	fighter	13	Luke	3	3
1	a-wing	fighter	7	Obi-Wan	1	null

Explicit Inner Join (with other filtering techniques)

Because the fleet & personnel tables both have a column with heading 'name', we need to specify which table we referring to these columns.

query.sql

output

SELECT fleet.name, class, personnel.name as pilot
FROM fleet
JOIN personnel
ON fleet.id = personnel.starship
WHERE fleet.name = 'a-wing'

SELECT fleet.name, class, personnel.name as pilot
FROM fleet
JOIN personnel
ON fleet.id = personnel.starship
WHERE fleet.name = 'a-wing'

query.sql

Query Result

name	class	pilot
a-wing	fighter	Obi-Wan

Outer Join

This join will list all rows from the primary table, even if there is no data for that row in the secondary table.

query.sql

output

SELECT fleet.name, personnel.name as pilot
FROM fleet
LEFT OUTER JOIN personnel
ON fleet.id = personnel.starship

SELECT fleet.name, personnel.name as pilot
FROM fleet
LEFT OUTER JOIN personnel
ON fleet.id = personnel.starship

query.sql

Query Result

name	pilot
x-wing	Luke
a-wing	Obi-Wan
cruiser	null
y-wing	null

Self Join

A table joined to itself.

query.sql

output

SELECT personnel.name, relations.name as related_to
FROM personnel
/* Using an alias 'relations' to represent the 'personnel' table in the join */
JOIN personnel relations
ON personnel.related_to = relations.id

SELECT personnel.name, relations.name as related_to
FROM personnel
/* Using an alias 'relations' to represent the 'personnel' table in the join */
JOIN personnel relations
ON personnel.related_to = relations.id

query.sql

Query Result

name	related_to
Luke	Leia
Leia	Luke

Multiple Joins

query.sql

output

SELECT a.name, b.name 
FROM teams
JOIN personnel a
ON teams.member_a = a.id
JOIN personnel b
ON teams.member_b = b.id;

SELECT a.name, b.name 
FROM teams
JOIN personnel a
ON teams.member_a = a.id
JOIN personnel b
ON teams.member_b = b.id;

query.sql

Query Result

name	name
Luke	Leia

A computer screen with a blinking cursor.

Playground

DB Fiddle - SQL Database Playground

A library with book cases 30 meters tall and a globe in the middle of them.

Learning Resources

SQL Police Department - an SQL game

Table Of Contents

Definition

Creating

Allow SQL to create IDs

Filtering Data

Conditions

Comparison Operators

Multiple Conditions

Aggregating Data

Aggregate functions

Subqueries

Joins

Types of Joins

Explicit Inner Join

Explicit Inner Join (with other filtering techniques)

Outer Join

Self Join

Multiple Joins

Playground

Learning Resources