SQL Basics

SQL cheat sheet using starcraft relational data model based on races, planets and heroes.

Table and associations

• Database: Starcraft
• Tables: Races, Planets, Planets_Races (join table), Heroes
• Associations:
  • Races many-to-many Planets
    • 1 planet can be controlled by many races and similarly 1 race can control many planets)
  • Races one-to-many Heroes
    • 1 race has many heroes but 1 hero can only belong to a single race (no biracial)
  • Planets one-to-many Heroes
    • 1 planet can give birth to many heroes but 1 hero can only be born in a single homeworld

Postgresql commands

Create database. Ensure postgresql application is running

# run postgresql in terminal
psql
# create database
CREATE DATABASE {database_name};
# connect to database
\c {database_name}

# other useful terminal commands
\c {database_name}# connect to [database] 
\l # list all databases
\dt #list all tables in current database
\d {table_name} #list schema of table
\q # quit

Create Tables

CREATE TABLE Races (
    id serial PRIMARY KEY,
    name varchar(225),
    description varchar(225)
);

CREATE TABLE Planets (
    id serial PRIMARY KEY,
    name varchar(225),
    moons int
);

CREATE TABLE Heroes (
    id serial PRIMARY KEY,
    planet_id int REFERENCES planets ON DELETE SET NULL,
    race_id int REFERENCES races ON DELETE SET NULL,
    alias varchar(225),
    level int
);

CREATE TABLE Planets_Races (
    planet_id int REFERENCES planets ON DELETE SET NULL,
    race_id int REFERENCES races ON DELETE SET NULL
);

Drop tables

DROP TABLE Heroes CASCADE;
DROP TABLE Races CASCADE;
DROP TABLE Planets CASCADE;
DROP TABLE Planets_Races CASCADE;

Seed Database

Seed races

-- Delete existing data
DELETE FROM Races;
-- RESET SERIAL PRIMARY KEY
ALTER SEQUENCE races_id_seq RESTART WITH 1;
-- SEED NEW DATA
INSERT INTO Races (id, name, description) VALUES
(1, 'Zerg', 'Eats humans'),
(2, 'Terran', 'Marine hoorah'),
(3, 'Protoss', 'For Aiur'),
(4, 'Xel Naga', '...');

Seed planets

-- Delete existing data
DELETE FROM Planets;
-- RESET SERIAL PRIMARY KEY
ALTER SEQUENCE planets_id_seq RESTART WITH 1;
-- SEED NEW DATA
INSERT INTO Planets (id, name, moons) VALUES
(1, 'Char', 0),
(2, 'Earth', 1),
(3, 'Aiur', 6),
(4, 'Korhal', 3);

Associate planets to races. Zerg controls Char (1,1) and shares control of Earth (2,1). Terran controls Korhal (4,2) and shares control of Earth (2,2). Protoss control Aiur (3,3).

-- Delete existing data
DELETE FROM Planets_Races;
-- SEED NEW DATA
INSERT INTO Planets_Races (planet_id, race_id) VALUES
(1, 1),
(2, 1),
(4, 2),
(2, 2),
(3, 3);

Seed heroes and associate them with planets and races

-- Delete existing data
DELETE FROM heroes;
-- RESET SERIAL PRIMARY KEY
ALTER SEQUENCE heroes_id_seq RESTART WITH 1;
-- SEED NEW DATA
INSERT INTO Heroes (id, planet_id, race_id, alias, level) VALUES
(1, 2, 2, 'Jim Raynor', 5),
(2, 1, 1, 'Queen of Blades', 20),
(3, 3, 3, 'Zeratul', 15),
(4, 4, 2, 'Arcturus Mensk', 5);

QUERIES

Basic Queries

-- Find all planets
SELECT * FROM Planets;
-- id |  name  | moons
-- ----+--------+-------
--  1 | Char   |     0
--  2 | Earth  |     1
--  3 | Aiur   |     6
--  4 | Korhal |     3

-- Find all races only display id and name
SELECT id, name FROM Races;
-- id |   name
-- ----+----------
--  1 | Zerg
--  2 | Terran
--  3 | Protoss
--  4 | Xel Naga

-- Find all heroes
SELECT * FROM Heroes;
-- id | planet_id | race_id |      alias      | level
-- ----+-----------+---------+-----------------+-------
--  1 |         2 |       2 | Jim Raynor      |     5
--  2 |         1 |       1 | Queen of Blades |    20
--  3 |         3 |       3 | Zeratul         |    15
--  4 |         4 |       2 | Arcturus Mensk  |     5

-- Find all Heroes level 10 and above only displaying alias as "Boss Hero"
SELECT alias as "Boss Hero" , level FROM Heroes WHERE level > 10;
--    Boss Hero    | level
-- -----------------+-------
-- Queen of Blades |    20
-- Zeratul         |    15

IMPORT & EXPORT

IMPORT csv file into table

\copy <table_name> FROM '<file_path>' CSV HEADER

IMPORT CSV file into table, only specific columns

\copy <table_name>(<column_1>,<column_1>,<column_1>) FROM '<file_path>' CSV HEADER

EXPORT table into csv file

\copy <table_name> TO '<file_path>' CSV

EXPORT table into csv file, only specific columns

\copy <table_name>(<column_1>,<column_1>,<column_1>) TO '<file_path>' CSV

JOINS

INNER JOIN query: Race has_many Heroes

-- Find all heroes belonging to race:Terran
SELECT * 
FROM Heroes
INNER JOIN Races
ON Heroes.race_id = Races.id
WHERE Races.name = 'Terran';
-- id | planet_id | race_id |     alias      | level | id |  name  |  description
-- ----+-----------+---------+----------------+-------+----+--------+---------------
--  1 |         2 |       2 | Jim Raynor     |     5 |  2 | Terran | Marine hoorah
--  4 |         4 |       2 | Arcturus Mensk |     5 |  2 | Terran | Marine hoorah

LEFT JOIN query: Planets has_many Races

-- Find all planets including their races (if any)
SELECT planets.name as planet_name, races.name as race_name
FROM planets
LEFT JOIN planets_races
ON planets_races.planet_id = planets.id
LEFT JOIN races
ON races.id = planets_races.race_id
ORDER BY planet_name;
--  planet_name | race_name 
-- -------------+-----------
--  Aiur        | Protoss
--  Braxis      | Terran
--  Char        | Zerg
--  Earth       | Terran
--  Earth       | Zerg
--  Kaldir      | Zerg
--  Korhal      | Terran
--  Mar Sara    | Terran
--  Moria       | Terran
--  New Folsom  | Terran
--  Phaeton     | Zerg
--  Shiloh      | Terran
--  Tarsonis    | 
--  Umoja       | Terran
--  Xt39323     | 
-- (15 rows)

FULL OUTER JOIN query: Planets has_many Races

-- Find all planets and races including combinations
SELECT planets.name as planet_name, races.name as race_name
FROM planets
FULL OUTER JOIN planets_races
ON planets_races.planet_id = planets.id
FULL OUTER JOIN races
ON races.id = planets_races.race_id
ORDER BY planet_name;
--  planet_name | race_name 
-- -------------+-----------
--  Aiur        | Protoss
--  Braxis      | Terran
--  Char        | Zerg
--  Earth       | Terran
--  Earth       | Zerg
--  Kaldir      | Zerg
--  Korhal      | Terran
--  Mar Sara    | Terran
--  Moria       | Terran
--  New Folsom  | Terran
--  Phaeton     | Zerg
--  Shiloh      | Terran
--  Tarsonis    | 
--  Umoja       | Terran
--  Xt39323     | 
--              | Xel Naga
-- (16 rows)

INNER JOIN query: Planets has_many Races

-- Find only planets that have races
SELECT planets.name as planet_name, races.name as race_name
FROM planets
INNER JOIN planets_races
ON planets_races.planet_id = planets.id
INNER JOIN races
ON races.id = planets_races.race_id
ORDER BY planet_name;
-- planet_name | race_name 
-- -------------+-----------
--  Aiur        | Protoss
--  Braxis      | Terran
--  Char        | Zerg
--  Earth       | Zerg
--  Earth       | Terran
--  Kaldir      | Zerg
--  Korhal      | Terran
--  Mar Sara    | Terran
--  Moria       | Terran
--  New Folsom  | Terran
--  Phaeton     | Zerg
--  Shiloh      | Terran
--  Umoja       | Terran
-- (13 rows)

INNER JOIN query: Race has_and_belongs_to_many Planets

-- Find all planets controlled by race: Zerg
SELECT *
FROM Planets
INNER JOIN Planets_Races
ON Planets.id = Planets_Races.planet_id
INNER JOIN Races
ON Planets_Races.race_id = Races.id
WHERE Races.name = 'Zerg';
-- id | name  | moons | planet_id | race_id | id | name | description
-- ----+-------+-------+-----------+---------+----+------+-------------
--  1 | Char  |     0 |         1 |       1 |  1 | Zerg | Eats humans
--   2 | Earth |     1 |         2 |       1 |  1 | Zerg | Eats humans

-- Find all races that control planet: Earth 
SELECT Races.id, Races.name as race_alias
FROM Races
INNER JOIN Planets_Races
ON Races.id = Planets_Races.race_id
INNER JOIN Planets
ON Planets_Races.planet_id = Planets.id
WHERE Planets.name = 'Earth';
-- id | race_alias
-- ----+------------
--  1 | Zerg
--  2 | Terran

AGGREGATE FUNCTIONS

COUNT function: Heroes

-- Count how many heroes there are with alias'
SELECT count(Heroes.alias) from Heroes;
-- 4

INNER JOIN & GROUP BY query: Race has_many Heroes

-- Count all heroes by race order by hero count descending
SELECT Races.name, count(Heroes.alias) as "hero_count"
FROM Races
FULL OUTER JOIN Heroes
ON Races.id = Heroes.race_id
GROUP BY Races.name
ORDER BY hero_count DESC;

   name   | hero_count
----------+------------
 Terran   |          2
 Protoss  |          1
 Zerg     |          1
 Xel Naga |          0

ARRAY_AGG function

-- Expand query above and include comma separated string list of hero alias names
SELECT Races.name, count(Heroes.alias) as "hero_count",
ARRAY_TO_STRING(ARRAY_AGG(Heroes.alias), ', ') as "hero_list"
FROM Races
FULL OUTER JOIN Heroes
ON Races.id = Heroes.race_id
GROUP BY Races.name
ORDER BY hero_count DESC;

   name   | hero_count |      hero_list
----------+------------+----------------------------
 Terran   |          2 | Jim Raynor, Arcturus Mensk
 Protoss  |          1 | Zeratul
 Zerg     |          1 | Queen of Blades
 Xel Naga |          0 |

LEFT OUTER JOIN: Find all unique planets and include their race count and list of race names

SELECT planets.name, count(races.name) as race_count, ARRAY_TO_STRING(ARRAY_AGG(races.name), ', ') as race_list
FROM planets
LEFT OUTER JOIN planets_races
ON planets.id = planets_races.planet_id
LEFT OUTER JOIN races
ON races.id = planets_races.race_id
GROUP BY planets.name
ORDER BY race_count DESC;
--     name    | race_count |  race_list   
-- ------------+------------+--------------
--  Earth      |          2 | Zerg, Terran
--  Umoja      |          1 | Terran
--  Moria      |          1 | Terran
--  Mar Sara   |          1 | Terran
--  Shiloh     |          1 | Terran
--  Aiur       |          1 | Protoss
--  New Folsom |          1 | Terran
--  Phaeton    |          1 | Zerg
--  Kaldir     |          1 | Zerg
--  Korhal     |          1 | Terran
--  Braxis     |          1 | Terran
--  Char       |          1 | Zerg
--  Tarsonis   |          0 | 
--  Xt39323    |          0 |