Table of Contents

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
/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 columnsSELECT *-- 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 defaultORDER 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 fleetORDER 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 fleetLIMIT 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 fleetWHERE 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 fleetWHERE 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 usedWHERE 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 caseWHERE 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 inclusiveWHERE 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 fleetWHERE 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 fleetWHERE 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 fleetWHERE 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 fleetWHERE 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 fleetWHERE 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 fleetWHERE (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 |
/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 fleetGROUP 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_lengthFROM fleetGROUP 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 fleetGROUP 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 *,CASEWHEN length > 100 THEN 'large'WHEN length > 10 THEN 'medium'ELSE 'small'END length_categoryFROM fleet;
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 fleetWHERE id IN (SELECT starshipFROM 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
/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_nameFROM fleetINNER JOIN personnelON 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_nameFROM fleetLEFT JOIN personnelON 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_nameFROM fleetLEFT JOIN personnelON fleet.id = personnel.starshipWHERE 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_nameFROM fleetRIGHT JOIN personnelON 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_nameFROM fleetRIGHT JOIN personnelON fleet.id = personnel.starshipWHERE 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_nameFROM fleetFULL OUTER JOIN personnelON 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_nameFROM fleetFULL OUTER JOIN personnelON fleet.id = personnel.starshipWHERE personnel.starship IS NOT NULL;