SQL Notes

Section 1 - Simple SQL statements

# Create table
CREATE TABLE cities (
  name VARCHAR(50),
  country VARCHAR(50),
  population INTEGER,
  area INTEGER
);

# Insert data into table
INSERT INTO cities ( name, country, population, area )
VALUES ('Tokyo', 'Japan', 38505000, 8223)
VALUES ('Delhi', 'India', 28125000, 2240)
VALUES ('Shanghai', 'China', 22125000, 4015)
VALUES ('Sao Paulo', 'Brazil', 20935000, 3043);
# or
INSERT INTO cities (username)
VALUES
  ('monahan93'),
  ('pferrer'),
  ('si93onis'),
  ('99stroman');

# Select all
SELECT * FROM cities;

# Select only name and country
SELECT name, country FROM cities;

# Select a column 3 times
SELECT name, name, name FROM cities;

# Calculated column
SELECT name, population / area AS population_density
FROM cities;

# Join strings
Select name || ', ' || country AS location FROM cities;
# or
Select CONCAT(name, ', ', country) AS location FROM cities;

# Uppercase
Select CONCAT(UPPER(name), ', ', UPPER(country)) AS location FROM cities;

# Losercase
LOWER()

# Length
LENGTH()

Section 2 - Filtering records

# Where
SELECT name, area FROM cities WHERE area > 4000;

Comparison math operators

=	equal	<=	less or equal
>	greater than	<>	not equal
<	less than	!=	not equal
>=	greater or equal	BETWEEN	between two values
IN	in a list	NOT IN	not in a list

# Compound where
SELECT name, area FROM cities WHERE area BETWEEN 2000 AND 4000;

# Calculation in where
SELECT name, population / area AS population_density FROM cities WHERE population / area > 6000;

# Updating rows
UPDATE cities SET population = 39505000 WHERE name = 'Tokyo';

# Delete rows
DELETE FROM cities WHERE name = 'Tokyo';

Section 3 - Working with tables

# Auto generated IDs
CREATE TABLE user (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50)
);

# Creating foreign key columns
CREATE TABLE photos (
  id SERIAL PRIMARY KEY,
  url VARCHAR(200),
  user_id INTEGER REFERENCES users(id)
);

Deletion contraints

Delete all photo records after deleting a user.

# On delete cascade
CREATE TABLE photos (
  id SERIAL PRIMARY KEY,
  url VARCHAR(200),
  user_id INTEGER REFERENCES users(id) ON DELETE CASCADE
);

Set foreign keys to NULL after deleting a user.

# On delete set null
CREATE TABLE photos (
  id SERIAL PRIMARY KEY,
  url VARCHAR(200),
  user_id INTEGER REFERENCES users(id) ON DELETE SET NULL
);

Section 4 - Relating records with joins

# Inner join
SELECT url, username
FROM photos
JOIN users ON users.id = photos.user_id;

# Left outer join
SELECT url, username
FROM photos
LEFT JOIN users ON users.id = photos.user_id;

# Right outer join
SELECT url, username
FROM photos
RIGHT JOIN users ON users.id = photos.user_id;

# Full join
SELECT url, username
FROM photos
FULL JOIN users ON users.id = photos.user_id;

# Where with join
SELECT url, contents
FROM comments
JOIN photos ON photos.id = comments.photo_id
WHERE comments.user_id = photos.user_id;

# Three way join
SELECT url, contents, username
FROM comments
JOIN photos ON photos.id = comments.photo_id
JOIN users ON users.id = comments.user_id AND users.id = photos.user_id;

Section 5 - Aggregation of records

# Group by
SELECT user_id
FROM comments
GROUP BY user_id;

# Group by and count
SELECT user_id, COUNT(id)
FROM comments
GROUP BY user_id;

# Group by and count with NULL values
SELECT user_id, COUNT(*)
FROM comments
GROUP BY user_id;

# Select photo id for first 2 photos and more than 2 comments
SELECT photo_id, COUNT(*)
FROM comments
WHERE photo_id < 3
GROUP BY photo_id
HAVING COUNT(*) > 2;

# Select user id for first 50 photos and more than 20 comments
SELECT user_id, COUNT(*)
FROM comments
WHERE photo_id < 50
GROUP BY user_id
HAVING COUNT(*) > 20;

Section 7 - Sorting records

# Sort ascending
SELECT *
FROM products
ORDER BY price;

# Sort descending
SELECT *
FROM products
ORDER BY price DESC;

# Sort 2 columns
SELECT *
FROM products
ORDER BY price, weight;

# Offset by skipping first 40 users
SELECT *
FROM users
OFFSET 40;

# Limit the number of users to 40
SELECT *
FROM users
LIMIT 40;

# Pagination: show third page
SELECT *
FROM users
LIMIT 20
OFFSET 40;

Section 8 - Unions and intersection with sets

# Combine rows and remove duplicates
(
  SELECT *
  FROM products
  ORDER BY price DESC
  LIMIT 4
)
UNION
(
  SELECT *
  FROM products
  ORDER BY price / weight DESC
  LIMIT 4
);

# Combine rows and show duplicates
(
  SELECT *
  FROM products
  ORDER BY price DESC
  LIMIT 4
)
UNION ALL
(
  SELECT *
  FROM products
  ORDER BY price / weight DESC
  LIMIT 4
);

# Show only duplicates
(
  SELECT *
  FROM products
  ORDER BY price DESC
  LIMIT 4
)
INTERSECT
(
  SELECT *
  FROM products
  ORDER BY price / weight DESC
  LIMIT 4
);

# Show rows only present in first query
(
  SELECT *
  FROM products
  ORDER BY price DESC
  LIMIT 4
)
INTERSECT
(
  SELECT *
  FROM products
  ORDER BY price / weight DESC
  LIMIT 4
);

Section 9 - Assembling queries with subqueries

# Subquery
SELECT price
FROM products
WHERE price > (
  SELECT MAX(price) FROM products WHERE departments = 'Toys'
);

# Subquery in SELECT clause
SELECT name, price, price / (SELECT MAX(price) FROM phones) AS price_ratio
FROM phones;

# Subquery in FROM clause
SELECT AVG(order_count)
FROM (
  SELECT user_id, COUNT(*) AS order_count
  FROM orders
  GROUP BY user_id
) AS p;

# Subquery in JOIN clause
SELECT first_name
FROM users
JOIN (
  SELECT user_id FROM orders WHERE product_id = 3
) AS o
ON o.user_id = users.id;

# Subquery in WHERE clause
SELECT id
FROM orders
WHERE product_id IN (
  SELECT id FROM products WHERE price / weight > 50
);

# Subquery in WHERE clause using ALL
SELECT name, department, price
FROM products
WHERE price > ALL (
  SELECT price FROM products WHERE department = 'Industrials'
);

# Subquery in WHERE clause using ANY/SOME
SELECT name, department, price
FROM products
WHERE price > ANY (
  SELECT price FROM products WHERE department = 'Industrials'
);

# Select without a FROM
SELECT (
  SELECT MAX(price) FROM products
), (
  SELECT AVG(price) FROM products
);

Section 10 - Selecting distinct records

# Selecting distinct values
SELECT DISTINCT department
FROM products;

Section 11 - Utility operators, keywords, and functions

# Greatest value in a list
SELECT name, weight, GREATEST(30, 2 * weight)
FROM products;

# Least value in a list
SELECT name, weight, LEAST(price * 0.5, 400)
FROM products;

# Case
SELECT
  name,
  price,
  CASE
    WHEN price > 600 THEN 'high'
    WHEN price > 300 THEN 'medium'
    ELSE 'cheap'
  END
FROM products;

Section 14 - Datbase-side validation and constraints

# Apply a NULL constraint
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50),
  department VARCHAR(50),
  price INTEGER NOT NULL,
  weight INTEGER
);
# (After the table was created)
ALTER TABLE products
ALTER COLUMN price
SET NOT NULL;

# Updating a NULL value
UPDATE products
SET price = 9999
WHERE price IS NULL;

# Default column value
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  department VARCHAR(50) NOT NULL,
  price INTEGER DEFAULT 999,
  weight INTEGER
);
# (After the table was created)
ALTER TABLE products
ALTER COLUMN price
SET DEFAULT 999;

# Applying a unique constraint to one column
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) UNIQUE,
  department VARCHAR(50),
  price INTEGER,
  weight INTEGER
);
# (After the table was created)
ALTER TABLE products
ADD UNIQUE (name);

# Multi-column uniqueness
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50),
  department VARCHAR(50),
  price INTEGER,
  weight INTEGER,
  UNIQUE(name, department)
);
# (After the table was created)
ALTER TABLE products
ADD UNIQUE(name, department);

# Validation check
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50),
  department VARCHAR(50),
  price INTEGER CHECK (price > 0),
  weight INTEGER,
);
# (After the table was created)
ALTER TABLE products
ADD CHECK(price > 0);

# Checks over multiple columns
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  created_at TIMESTAMP NOT NULL,
  est_delivery TIMESTAMP NOT NULL,
  CHECK (created_at < est_delivery)
);

Mirakurun/sql.md

SQL Notes

Section 1 - Simple SQL statements

Section 2 - Filtering records

Comparison math operators

Section 3 - Working with tables

Deletion contraints

Section 4 - Relating records with joins

Section 5 - Aggregation of records

Section 7 - Sorting records

Section 8 - Unions and intersection with sets

Section 9 - Assembling queries with subqueries

Section 10 - Selecting distinct records

Section 11 - Utility operators, keywords, and functions

Section 14 - Datbase-side validation and constraints