Left Arrow.Back
Left Arrow.Back

Notes / SQL / Create, Read Data

Create, Read Data

3 rectangles stacked on top of 1 another.

SQL - Create / Read Data

Last Tended

Status: seed

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

2 circles overlapping.

/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

2 circles overlapping.

/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

2 circles overlapping.

/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

2 circles overlapping.

/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

2 circles overlapping.

/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

2 circles overlapping.

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)

2 circles overlapping.

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