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