3 rectangles stacked on top of 1 another.

SQL - Create / Read Data

Tended: Dec 2021

Status: decay

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

Creating

/schema.sql

result

-- 'serial' is a pseudo-type that generates a sequence of integers (often used as the primary key column in a table).

CREATE TABLE IF NOT EXISTS fleet (
    id serial PRIMARY KEY,
    name VARCHAR (50) NOT NULL,
    class VARCHAR (50) NOT NULL,
    length INTEGER NOT NULL
);

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

SELECT *
FROM fleet;

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

Filtering Data

result

CREATE TABLE IF NOT EXISTS fleet (
    id serial PRIMARY KEY,
    name VARCHAR (50) NOT NULL,
    class VARCHAR (50) NOT NULL,
    length INTEGER NOT NULL
);

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

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

result

CREATE TABLE IF NOT EXISTS fleet (
    id serial PRIMARY KEY,
    name VARCHAR (50) NOT NULL,
    class VARCHAR (50) NOT NULL,
    length INTEGER NOT NULL
);

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

SELECT distinct(class)
FROM fleet;

result

CREATE TABLE IF NOT EXISTS fleet (
    id serial PRIMARY KEY,
    name VARCHAR (50) NOT NULL,
    class VARCHAR (50) NOT NULL,
    length INTEGER NOT NULL
);

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

SELECT *
FROM fleet
-- ASC is default
ORDER BY length DESC;

result

CREATE TABLE IF NOT EXISTS fleet (
    id serial PRIMARY KEY,
    name VARCHAR (50) NOT NULL,
    class VARCHAR (50) NOT NULL,
    length INTEGER NOT NULL
);

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

SELECT *
FROM fleet
ORDER BY class, name;

result

CREATE TABLE IF NOT EXISTS fleet (
    id serial PRIMARY KEY,
    name VARCHAR (50) NOT NULL,
    class VARCHAR (50) NOT NULL,
    length INTEGER NOT NULL
);

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

SELECT *
FROM fleet
LIMIT 2;

result

CREATE TABLE IF NOT EXISTS fleet (
    id serial PRIMARY KEY,
    name VARCHAR (50) NOT NULL,
    class VARCHAR (50) NOT NULL,
    length INTEGER NOT NULL
);

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

SELECT *
FROM fleet
WHERE name LIKE '%wing%';

Conditions

Comparison Operators

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

result

CREATE TABLE IF NOT EXISTS fleet (
    id serial PRIMARY KEY,
    name VARCHAR (50) NOT NULL,
    class VARCHAR (50) NOT NULL,
    length INTEGER NOT NULL
);

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

SELECT *	
FROM fleet
WHERE length > 80;

result

CREATE TABLE IF NOT EXISTS fleet (
    id serial PRIMARY KEY,
    name VARCHAR (50) NOT NULL,
    class VARCHAR (50) NOT NULL,
    length INTEGER NOT NULL
);

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

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

result

CREATE TABLE IF NOT EXISTS fleet (
    id serial PRIMARY KEY,
    name VARCHAR (50) NOT NULL,
    class VARCHAR (50) NOT NULL,
    length INTEGER NOT NULL
);

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

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

result

CREATE TABLE IF NOT EXISTS fleet (
    id serial PRIMARY KEY,
    name VARCHAR (50) NOT NULL,
    class VARCHAR (50) NOT NULL,
    length INTEGER NOT NULL
);

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

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

result

CREATE TABLE IF NOT EXISTS fleet (
    id serial PRIMARY KEY,
    name VARCHAR (50) NOT NULL,
    class VARCHAR (50) NOT NULL,
    length INTEGER NOT NULL
);

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

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

result

CREATE TABLE IF NOT EXISTS fleet (
    id serial PRIMARY KEY,
    name VARCHAR (50) NOT NULL,
    class VARCHAR (50) NOT NULL,
    length INTEGER NOT NULL
);

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

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

result

CREATE TABLE IF NOT EXISTS fleet (
    id serial PRIMARY KEY,
    name VARCHAR (50) NOT NULL,
    class VARCHAR (50) NOT NULL,
    length INTEGER NOT NULL
);

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

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

Multiple Conditions

result

CREATE TABLE IF NOT EXISTS fleet (
    id serial PRIMARY KEY,
    name VARCHAR (50) NOT NULL,
    class VARCHAR (50) NOT NULL,
    length INTEGER NOT NULL
);

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

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

result

CREATE TABLE IF NOT EXISTS fleet (
    id serial PRIMARY KEY,
    name VARCHAR (50) NOT NULL,
    class VARCHAR (50) NOT NULL,
    length INTEGER NOT NULL
);

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

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

result

CREATE TABLE IF NOT EXISTS fleet (
    id serial PRIMARY KEY,
    name VARCHAR (50) NOT NULL,
    class VARCHAR (50) NOT NULL,
    length INTEGER NOT NULL
);

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

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;

result

CREATE TABLE IF NOT EXISTS fleet (
    id serial PRIMARY KEY,
    name VARCHAR (50) NOT NULL,
    class VARCHAR (50) NOT NULL,
    length INTEGER NOT NULL
);

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

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

Squares, triangles and 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

result

CREATE TABLE IF NOT EXISTS fleet (
    id serial PRIMARY KEY,
    name VARCHAR (50) NOT NULL,
    class VARCHAR (50) NOT NULL,
    length INTEGER NOT NULL
);

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

SELECT SUM(length) 
FROM fleet;

result

CREATE TABLE IF NOT EXISTS fleet (
    id serial PRIMARY KEY,
    name VARCHAR (50) NOT NULL,
    class VARCHAR (50) NOT NULL,
    length INTEGER NOT NULL
);

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

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

result

CREATE TABLE IF NOT EXISTS fleet (
    id serial PRIMARY KEY,
    name VARCHAR (50) NOT NULL,
    class VARCHAR (50) NOT NULL,
    length INTEGER NOT NULL
);

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

-- Same as above but using a column alias.
-- Note: column alias are not supported in WHERE or HAVING clauses.
SELECT class, SUM(length) AS combined_length
FROM fleet
GROUP BY class;

This query isn't working in this garden. For a working example, see DB Fiddle.

result

CREATE TABLE IF NOT EXISTS fleet (
    id serial PRIMARY KEY,
    name VARCHAR (50) NOT NULL,
    class VARCHAR (50) NOT NULL,
    length INTEGER NOT NULL
);

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

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

result

CREATE TABLE IF NOT EXISTS fleet (
    id serial PRIMARY KEY,
    name VARCHAR (50) NOT NULL,
    class VARCHAR (50) NOT NULL,
    length INTEGER NOT NULL
);

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

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

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

Subqueries

result

CREATE TABLE IF NOT EXISTS fleet (
    id serial PRIMARY KEY,
    name VARCHAR (50) NOT NULL,
    class VARCHAR (50) NOT NULL,
    length INTEGER NOT NULL
);

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

CREATE TABLE IF NOT EXISTS personnel (
  id serial PRIMARY KEY,
  name VARCHAR (50) NOT NULL,
  starship INTEGER,
  related_to INTEGER
);

INSERT INTO personnel(name, starship, related_to)
VALUES ('Luke', 3, 3),
       ('Obi-Wan', 1, null),
       ('Leia', null, 1);

SELECT *
FROM fleet
WHERE id IN (
  SELECT starship 
  FROM personnel
);

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. Types of joins:

Inner Join

result

CREATE TABLE IF NOT EXISTS fleet (
    id serial PRIMARY KEY,
    name VARCHAR (50) NOT NULL,
    class VARCHAR (50) NOT NULL,
    length INTEGER NOT NULL
);

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

CREATE TABLE IF NOT EXISTS personnel (
  id serial PRIMARY KEY,
  name VARCHAR (50) NOT NULL,
  starship INTEGER,
  related_to INTEGER
);

INSERT INTO personnel(name, starship, related_to)
VALUES ('Luke', 3, 3),
       ('Obi-Wan', 1, null),
       ('Leia', null, 1);

SELECT fleet.name AS fleet_name, personnel.name AS personnel_name
FROM fleet
INNER JOIN personnel
    ON fleet.id = personnel.starship;

Left Join

result

CREATE TABLE IF NOT EXISTS fleet (
    id serial PRIMARY KEY,
    name VARCHAR (50) NOT NULL,
    class VARCHAR (50) NOT NULL,
    length INTEGER NOT NULL
);

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

CREATE TABLE IF NOT EXISTS personnel (
  id serial PRIMARY KEY,
  name VARCHAR (50) NOT NULL,
  starship INTEGER,
  related_to INTEGER
);

INSERT INTO personnel(name, starship, related_to)
VALUES ('Luke', 3, 3),
       ('Obi-Wan', 1, null),
       ('Leia', null, 1);

SELECT fleet.name AS fleet_name, personnel.name AS personnel_name
FROM fleet
LEFT JOIN personnel
    ON fleet.id = personnel.starship;

Left Outer Join

result

CREATE TABLE IF NOT EXISTS fleet (
    id serial PRIMARY KEY,
    name VARCHAR (50) NOT NULL,
    class VARCHAR (50) NOT NULL,
    length INTEGER NOT NULL
);

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

CREATE TABLE IF NOT EXISTS personnel (
  id serial PRIMARY KEY,
  name VARCHAR (50) NOT NULL,
  starship INTEGER,
  related_to INTEGER
);

INSERT INTO personnel(name, starship, related_to)
VALUES ('Luke', 3, 3),
       ('Obi-Wan', 1, null),
       ('Leia', null, 1);

SELECT fleet.name AS fleet_name, personnel.name AS personnel_name
FROM fleet
LEFT JOIN personnel
    ON fleet.id = personnel.starship
WHERE personnel.starship IS NOT NULL;

Right Join

result

CREATE TABLE IF NOT EXISTS fleet (
    id serial PRIMARY KEY,
    name VARCHAR (50) NOT NULL,
    class VARCHAR (50) NOT NULL,
    length INTEGER NOT NULL
);

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

CREATE TABLE IF NOT EXISTS personnel (
  id serial PRIMARY KEY,
  name VARCHAR (50) NOT NULL,
  starship INTEGER,
  related_to INTEGER
);

INSERT INTO personnel(name, starship, related_to)
VALUES ('Luke', 3, 3),
       ('Obi-Wan', 1, null),
       ('Leia', null, 1);

SELECT fleet.name AS fleet_name, personnel.name AS personnel_name
FROM fleet
RIGHT JOIN personnel
    ON fleet.id = personnel.starship;

Right Outer Join

result

CREATE TABLE IF NOT EXISTS fleet (
    id serial PRIMARY KEY,
    name VARCHAR (50) NOT NULL,
    class VARCHAR (50) NOT NULL,
    length INTEGER NOT NULL
);

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

CREATE TABLE IF NOT EXISTS personnel (
  id serial PRIMARY KEY,
  name VARCHAR (50) NOT NULL,
  starship INTEGER,
  related_to INTEGER
);

INSERT INTO personnel(name, starship, related_to)
VALUES ('Luke', 3, 3),
       ('Obi-Wan', 1, null),
       ('Leia', null, 1);

SELECT fleet.name AS fleet_name, personnel.name AS personnel_name
FROM fleet
RIGHT JOIN personnel
    ON fleet.id = personnel.starship
	WHERE personnel.starship IS NOT NULL;

Full Outer Join

This query isn't working in this garden. For a working example, see DB Fiddle.

result

CREATE TABLE IF NOT EXISTS fleet (
    id serial PRIMARY KEY,
    name VARCHAR (50) NOT NULL,
    class VARCHAR (50) NOT NULL,
    length INTEGER NOT NULL
);

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

CREATE TABLE IF NOT EXISTS personnel (
  id serial PRIMARY KEY,
  name VARCHAR (50) NOT NULL,
  starship INTEGER,
  related_to INTEGER
);

INSERT INTO personnel(name, starship, related_to)
VALUES ('Luke', 3, 3),
       ('Obi-Wan', 1, null),
       ('Leia', null, 1);

SELECT fleet.name AS fleet_name, personnel.name AS personnel_name
FROM fleet
FULL OUTER JOIN personnel
    ON fleet.id = personnel.starship;

Full Outer Join (only rows unique in both tables)

This query isn't working in this garden. For a working example, see DB Fiddle.

result

CREATE TABLE IF NOT EXISTS fleet (
    id serial PRIMARY KEY,
    name VARCHAR (50) NOT NULL,
    class VARCHAR (50) NOT NULL,
    length INTEGER NOT NULL
);

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

CREATE TABLE IF NOT EXISTS personnel (
  id serial PRIMARY KEY,
  name VARCHAR (50) NOT NULL,
  starship INTEGER,
  related_to INTEGER
);

INSERT INTO personnel(name, starship, related_to)
VALUES ('Luke', 3, 3),
       ('Obi-Wan', 1, null),
       ('Leia', null, 1);

SELECT fleet.name AS fleet_name, personnel.name AS personnel_name
FROM fleet
FULL OUTER JOIN personnel
    ON fleet.id = personnel.starship
WHERE personnel.starship IS NOT NULL;