The icon of the Adeptus Mechanicus. A cog with a skull at the center.

3 rectangles stacked on top of 1 another.

SQL - Create / Read Data

Last 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 & circles scattered on 1 side of a line, squares in a line on the other.

Filtering Data

/query.sql

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;

/query.sql

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;

/query.sql

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;

/query.sql

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;

/query.sql

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;

/query.sql

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

/query.sql

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;

/query.sql

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;

/query.sql

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';

/query.sql

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;

/query.sql

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;

/query.sql

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);

/query.sql

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

/query.sql

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;

/query.sql

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';

/query.sql

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;

/query.sql

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 & 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

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;

/query.sql

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;

/query.sql

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.

/query.sql

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;

/query.sql

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

/query.sql

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

/query.sql

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

/query.sql

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

/query.sql

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

/query.sql

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

/query.sql

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.

/query.sql

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.

/query.sql

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;

Playground

DB fiddle - SQL Database Playground

Where to Next?

A sci-fi robot taxi driver with no lower body

Notes
      │
  └── SQL
        
YOU ARE HERE
          │
  ├── Create Read
          │
  ├── Performance
          │
  └── Update Delete

Table of Contents

SQL - Create / Read Data

Creating

Filtering Data

Conditions

Comparison Operators

Multiple Conditions

Aggregating Data

Aggregate functions

Subqueries

Joins

Inner Join

Left Join

Left Outer Join

Right Join

Right Outer Join

Full Outer Join

Full Outer Join (only rows unique in both tables)

Playground

Where to Next?