SQL Cheatsheet

SELECT * FROM users;

SELECT name, email, created_at
FROM users;

SELECT DISTINCT country
FROM users;

SELECT
  first_name AS "First Name",
  last_name AS "Last Name",
  email AS "Email Address"
FROM users;

SELECT
  first_name || ' ' || last_name AS full_name,
  email
FROM users;

SELECT
  name,
  price,
  CASE
    WHEN price < 10 THEN 'Budget'
    WHEN price < 50 THEN 'Standard'
    ELSE 'Premium'
  END AS price_tier
FROM products;

SELECT
  name,
  COALESCE(nickname, name) AS display_name,
  COALESCE(phone, 'No phone') AS phone
FROM users;

SELECT
  name,
  NULLIF(status, 'inactive') AS active_status
FROM users;

SELECT * FROM users
WHERE status = 'active';

SELECT * FROM products
WHERE price >= 100
  AND price <= 500;

SELECT * FROM orders
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

SELECT * FROM users
WHERE country IN ('USA', 'Canada', 'UK');

-- % matches any sequence
SELECT * FROM users WHERE email LIKE '%@gmail.com';
 
-- _ matches single character
SELECT * FROM products WHERE sku LIKE 'ABC-___';

SELECT * FROM users
WHERE name ILIKE '%john%';

-- Find rows with NULL
SELECT * FROM users WHERE phone IS NULL;
 
-- Find rows without NULL
SELECT * FROM users WHERE phone IS NOT NULL;

SELECT * FROM users
WHERE (status = 'active' OR status = 'pending')
  AND created_at > '2024-01-01';

SELECT * FROM users
WHERE NOT (status = 'deleted' OR status = 'banned');

SELECT * FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id
);

-- Equal to any value
SELECT * FROM products
WHERE price = ANY(SELECT price FROM featured_products);
 
-- Greater than any (same as > MIN)
SELECT * FROM products
WHERE price > ANY(SELECT price FROM budget_products);

-- Greater than all values
SELECT * FROM products
WHERE price > ALL(SELECT price FROM budget_products);
 
-- Not equal to all (exclude all matches)
SELECT * FROM users
WHERE id <> ALL(SELECT user_id FROM banned_users);

SELECT * FROM users
ORDER BY created_at ASC;

SELECT * FROM products
ORDER BY price DESC;

SELECT * FROM users
ORDER BY country ASC, created_at DESC;

SELECT * FROM users
ORDER BY phone NULLS LAST;
 
-- Or put NULLs first
SELECT * FROM users
ORDER BY phone NULLS FIRST;

SELECT * FROM products
ORDER BY created_at DESC
LIMIT 10;

-- Page 1 (rows 1-10)
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 0;
 
-- Page 2 (rows 11-20)
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 10;
 
-- Page 3 (rows 21-30)
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 20;

SELECT * FROM products
ORDER BY price DESC
FETCH FIRST 5 ROWS ONLY;

SELECT COUNT(*) AS total_users
FROM users;

SELECT COUNT(DISTINCT country) AS countries
FROM users;

SELECT SUM(amount) AS total_revenue
FROM orders
WHERE status = 'completed';

SELECT AVG(price) AS average_price
FROM products;

SELECT
  MIN(price) AS cheapest,
  MAX(price) AS most_expensive
FROM products;

SELECT
  COUNT(*) AS total_orders,
  COUNT(*) FILTER (WHERE status = 'completed') AS completed,
  COUNT(*) FILTER (WHERE status = 'pending') AS pending
FROM orders;

SELECT country, COUNT(*) AS user_count
FROM users
GROUP BY country;

SELECT
  country,
  status,
  COUNT(*) AS user_count
FROM users
GROUP BY country, status;

SELECT country, COUNT(*) AS user_count
FROM users
GROUP BY country
HAVING COUNT(*) > 100;

SELECT
  country,
  city,
  COUNT(*) AS user_count
FROM users
GROUP BY ROLLUP(country, city);

SELECT
  country,
  status,
  COUNT(*) AS user_count
FROM users
GROUP BY CUBE(country, status);

SELECT
  users.name,
  orders.id AS order_id,
  orders.total
FROM users
INNER JOIN orders ON users.id = orders.user_id;

SELECT
  users.name,
  orders.id AS order_id
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

SELECT
  users.name,
  orders.id AS order_id
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;

SELECT
  users.name,
  orders.id AS order_id
FROM users
FULL OUTER JOIN orders ON users.id = orders.user_id;

SELECT
  colors.name AS color,
  sizes.name AS size
FROM colors
CROSS JOIN sizes;

SELECT
  e.name AS employee,
  m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

SELECT
  users.name,
  orders.id AS order_id,
  products.name AS product
FROM users
INNER JOIN orders ON users.id = orders.user_id
INNER JOIN order_items ON orders.id = order_items.order_id
INNER JOIN products ON order_items.product_id = products.id;

SELECT users.name, orders.total
FROM users
INNER JOIN orders USING (user_id);

-- Joins on all columns with same name
SELECT * FROM orders
NATURAL JOIN order_items;
 
-- Be careful: joins on ALL matching names
-- Prefer explicit JOIN conditions

SELECT * FROM products
WHERE price > (
  SELECT AVG(price) FROM products
);

SELECT * FROM users
WHERE id IN (
  SELECT user_id FROM orders
  WHERE total > 1000
);

SELECT
  country,
  avg_order
FROM (
  SELECT
    u.country,
    AVG(o.total) AS avg_order
  FROM users u
  JOIN orders o ON u.id = o.user_id
  GROUP BY u.country
) AS country_stats
WHERE avg_order > 500;

SELECT
  name,
  price,
  price - (SELECT AVG(price) FROM products) AS diff_from_avg
FROM products;

SELECT * FROM products p
WHERE price > (
  SELECT AVG(price)
  FROM products
  WHERE category_id = p.category_id
);

SELECT
  u.name,
  recent.order_date,
  recent.total
FROM users u
CROSS JOIN LATERAL (
  SELECT order_date, total
  FROM orders
  WHERE user_id = u.id
  ORDER BY order_date DESC
  LIMIT 3
) AS recent;

-- Users with no orders
SELECT * FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id
);

WITH active_users AS (
  SELECT * FROM users
  WHERE status = 'active'
)
SELECT * FROM active_users
WHERE created_at > '2024-01-01';

WITH
  active_users AS (
    SELECT * FROM users WHERE status = 'active'
  ),
  user_orders AS (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY user_id
  )
SELECT
  u.name,
  COALESCE(o.order_count, 0) AS orders
FROM active_users u
LEFT JOIN user_orders o ON u.id = o.user_id;

WITH RECURSIVE org_chart AS (
  -- Base case: top-level (no manager)
  SELECT id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL
 
  UNION ALL
 
  -- Recursive case
  SELECT e.id, e.name, e.manager_id, oc.level + 1
  FROM employees e
  INNER JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY level, name;

WITH RECURSIVE numbers AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM numbers WHERE n < 100
)
SELECT * FROM numbers;

SELECT
  name,
  price,
  ROW_NUMBER() OVER (ORDER BY price DESC) AS rank
FROM products;

SELECT
  name,
  price,
  RANK() OVER (ORDER BY price DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY price DESC) AS dense_rank
FROM products;

SELECT
  category,
  name,
  price,
  RANK() OVER (PARTITION BY category ORDER BY price DESC) AS category_rank
FROM products;

SELECT
  order_date,
  total,
  LAG(total) OVER (ORDER BY order_date) AS prev_total,
  LEAD(total) OVER (ORDER BY order_date) AS next_total
FROM orders;

SELECT
  order_date,
  total,
  SUM(total) OVER (ORDER BY order_date) AS running_total
FROM orders;

SELECT
  order_date,
  total,
  AVG(total) OVER (
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7day
FROM orders;

SELECT
  name,
  price,
  FIRST_VALUE(name) OVER (ORDER BY price DESC) AS most_expensive,
  LAST_VALUE(name) OVER (
    ORDER BY price DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS cheapest
FROM products;

SELECT
  name,
  price,
  NTILE(4) OVER (ORDER BY price) AS quartile
FROM products;

SELECT
  CONCAT(first_name, ' ', last_name) AS full_name,
  first_name || ' ' || last_name AS full_name_alt
FROM users;

SELECT
  UPPER(name) AS uppercase,
  LOWER(email) AS lowercase,
  INITCAP(name) AS titlecase
FROM users;

SELECT
  SUBSTRING(phone FROM 1 FOR 3) AS area_code,
  SUBSTRING(email FROM '@(.+)$') AS domain
FROM users;

SELECT
  TRIM('  hello  ') AS trimmed,
  LTRIM('  hello  ') AS left_trimmed,
  RTRIM('  hello  ') AS right_trimmed,
  TRIM(BOTH '-' FROM '--hello--') AS custom_trim
FROM users;

SELECT
  REPLACE(phone, '-', '') AS phone_digits,
  REPLACE(name, 'Mr. ', '') AS name_no_title
FROM users;

SELECT
  email,
  SPLIT_PART(email, '@', 1) AS username,
  SPLIT_PART(email, '@', 2) AS domain
FROM users;

SELECT
  name,
  LENGTH(name) AS name_length,
  CHAR_LENGTH(name) AS char_count
FROM users;

SELECT
  email,
  POSITION('@' IN email) AS at_position,
  STRPOS(email, '@') AS at_position_alt
FROM users;

SELECT
  CURRENT_DATE AS today,
  CURRENT_TIME AS now_time,
  CURRENT_TIMESTAMP AS now_full,
  NOW() AS now_func;

SELECT
  created_at,
  EXTRACT(YEAR FROM created_at) AS year,
  EXTRACT(MONTH FROM created_at) AS month,
  EXTRACT(DAY FROM created_at) AS day,
  EXTRACT(HOUR FROM created_at) AS hour
FROM orders;

SELECT
  created_at,
  DATE_TRUNC('month', created_at) AS month_start,
  DATE_TRUNC('year', created_at) AS year_start,
  DATE_TRUNC('hour', created_at) AS hour_start
FROM orders;

SELECT
  NOW() AS now,
  NOW() + INTERVAL '1 day' AS tomorrow,
  NOW() - INTERVAL '1 week' AS last_week,
  NOW() + INTERVAL '3 months' AS three_months
FROM orders;

SELECT
  name,
  birth_date,
  AGE(birth_date) AS age,
  AGE(NOW(), hire_date) AS tenure
FROM employees;

SELECT
  created_at,
  TO_CHAR(created_at, 'YYYY-MM-DD') AS iso_date,
  TO_CHAR(created_at, 'Mon DD, YYYY') AS pretty_date,
  TO_CHAR(created_at, 'HH24:MI:SS') AS time_24h
FROM orders;

SELECT date::date
FROM generate_series(
  '2024-01-01'::date,
  '2024-12-31'::date,
  '1 month'::interval
) AS date;

SELECT
  data->'name' AS name_json,          -- returns JSON
  data->>'name' AS name_text,         -- returns text
  data->'address'->>'city' AS city    -- nested access
FROM users;

SELECT
  data #> '{address,city}' AS city_json,
  data #>> '{address,city}' AS city_text,
  data #>> '{tags,0}' AS first_tag
FROM users;

-- Contains this JSON?
SELECT * FROM products
WHERE data @> '{"status": "active"}';
 
-- Is contained by?
SELECT * FROM products
WHERE '{"status": "active", "type": "book"}' @> data;

SELECT
  json_build_object(
    'id', id,
    'name', name,
    'email', email
  ) AS user_json
FROM users;

SELECT
  category,
  json_agg(name) AS product_names,
  json_agg(json_build_object('name', name, 'price', price)) AS products
FROM products
GROUP BY category;

UPDATE users
SET data = jsonb_set(
  data,
  '{address,city}',
  '"New York"'
)
WHERE id = 1;

INSERT INTO users (name, email, status)
VALUES ('John Doe', 'john@example.com', 'active');

INSERT INTO users (name, email)
VALUES
  ('Alice', 'alice@example.com'),
  ('Bob', 'bob@example.com'),
  ('Carol', 'carol@example.com');

INSERT INTO users (name, email)
VALUES ('John', 'john@example.com')
RETURNING id, name, created_at;

INSERT INTO users_archive (name, email)
SELECT name, email
FROM users
WHERE status = 'inactive';

INSERT INTO users (id, name, email)
VALUES (1, 'John', 'john@example.com')
ON CONFLICT (id) DO UPDATE
SET name = EXCLUDED.name,
    email = EXCLUDED.email;

INSERT INTO users (email, name)
VALUES ('john@example.com', 'John')
ON CONFLICT (email) DO NOTHING;

UPDATE users
SET status = 'inactive',
    updated_at = NOW()
WHERE last_login < '2024-01-01';

UPDATE products
SET price = price * 1.10
WHERE category = 'electronics'
RETURNING id, name, price;

UPDATE orders
SET status = 'shipped'
FROM shipments
WHERE orders.id = shipments.order_id
  AND shipments.shipped_at IS NOT NULL;

DELETE FROM users
WHERE status = 'deleted'
  AND updated_at < NOW() - INTERVAL '30 days';

DELETE FROM sessions
WHERE expires_at < NOW()
RETURNING id, user_id;

-- Fast delete all rows (resets sequences)
TRUNCATE TABLE logs;
 
-- Truncate multiple tables
TRUNCATE TABLE orders, order_items
RESTART IDENTITY CASCADE;

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  status VARCHAR(20) DEFAULT 'active',
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS logs (
  id SERIAL PRIMARY KEY,
  message TEXT,
  level VARCHAR(20),
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE active_users AS
SELECT id, name, email
FROM users
WHERE status = 'active';

ALTER TABLE users
ADD COLUMN phone VARCHAR(20);
 
-- With default value
ALTER TABLE users
ADD COLUMN is_verified BOOLEAN DEFAULT false;

ALTER TABLE users
DROP COLUMN phone;
 
-- Drop if exists
ALTER TABLE users
DROP COLUMN IF EXISTS temp_field;

-- Rename column
ALTER TABLE users
RENAME COLUMN name TO full_name;
 
-- Rename table
ALTER TABLE users
RENAME TO customers;

ALTER TABLE products
ALTER COLUMN price TYPE NUMERIC(10,2);
 
-- With conversion
ALTER TABLE users
ALTER COLUMN age TYPE INTEGER
USING age::INTEGER;

-- Set default
ALTER TABLE users
ALTER COLUMN status SET DEFAULT 'pending';
 
-- Remove default
ALTER TABLE users
ALTER COLUMN status DROP DEFAULT;

DROP TABLE users;
 
-- Drop if exists
DROP TABLE IF EXISTS temp_users;
 
-- Drop with dependent objects
DROP TABLE orders CASCADE;

-- Basic index
CREATE INDEX idx_users_email ON users(email);
 
-- Unique index
CREATE UNIQUE INDEX idx_users_username ON users(username);
 
-- Multi-column index
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

DROP INDEX idx_users_email;
 
DROP INDEX IF EXISTS idx_temp;

CREATE VIEW active_orders AS
SELECT o.id, u.name, o.total, o.created_at
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'active';
 
-- Then query like a table
SELECT * FROM active_orders;

-- Inline definition
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100)
);
 
-- Named constraint
CREATE TABLE products (
  id INTEGER,
  sku VARCHAR(50),
  CONSTRAINT pk_products PRIMARY KEY (id)
);

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id),
  total DECIMAL(10,2)
);
 
-- With ON DELETE action
CREATE TABLE comments (
  id SERIAL PRIMARY KEY,
  post_id INTEGER REFERENCES posts(id)
    ON DELETE CASCADE,
  content TEXT
);

-- Single column
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE
);
 
-- Multiple columns
CREATE TABLE memberships (
  user_id INTEGER,
  group_id INTEGER,
  UNIQUE (user_id, group_id)
);

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) NOT NULL
);

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  price DECIMAL(10,2) CHECK (price > 0),
  quantity INTEGER CHECK (quantity >= 0)
);
 
-- Named check
CREATE TABLE users (
  age INTEGER,
  CONSTRAINT valid_age CHECK (age >= 0 AND age < 150)
);

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title VARCHAR(200),
  status VARCHAR(20) DEFAULT 'draft',
  views INTEGER DEFAULT 0,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Add foreign key
ALTER TABLE orders
ADD CONSTRAINT fk_orders_user
FOREIGN KEY (user_id) REFERENCES users(id);
 
-- Add unique constraint
ALTER TABLE users
ADD CONSTRAINT unique_email UNIQUE (email);
 
-- Add check constraint
ALTER TABLE products
ADD CONSTRAINT positive_price CHECK (price > 0);

ALTER TABLE orders
DROP CONSTRAINT fk_orders_user;
 
ALTER TABLE users
DROP CONSTRAINT IF EXISTS unique_email;

SELECT name, email FROM customers
UNION
SELECT name, email FROM suppliers;

SELECT product_id, 'sale' AS type FROM sales
UNION ALL
SELECT product_id, 'return' AS type FROM returns;

SELECT user_id FROM premium_users
INTERSECT
SELECT user_id FROM active_users;

SELECT user_id FROM all_users
EXCEPT
SELECT user_id FROM banned_users;

(SELECT name, 'customer' AS type FROM customers)
UNION
(SELECT name, 'supplier' AS type FROM suppliers)
ORDER BY name;

SELECT
  '123'::INTEGER AS num,
  '2024-01-15'::DATE AS date,
  123::TEXT AS str,
  '3.14'::NUMERIC AS decimal;

SELECT
  CAST('123' AS INTEGER) AS num,
  CAST('2024-01-15' AS DATE) AS date,
  CAST(price AS INTEGER) AS rounded
FROM products;

SELECT
  1234.567::INTEGER AS truncated,    -- 1234
  1234.567::NUMERIC(10,1) AS rounded, -- 1234.6
  '99.99'::DECIMAL(5,2) AS decimal;

SELECT
  NOW()::DATE AS today,
  NOW()::TIME AS current_time,
  '2024-01-15 10:30:00'::TIMESTAMP AS ts,
  EXTRACT(EPOCH FROM NOW())::INTEGER AS unix;

SELECT
  'true'::BOOLEAN,
  'yes'::BOOLEAN,
  '1'::BOOLEAN,
  1::BOOLEAN,
  true::INTEGER;  -- 1

SELECT
  '{"name": "John"}'::JSON,
  '{"name": "John"}'::JSONB,
  row_to_json(users.*) FROM users;

SELECT
  ROUND(3.14159) AS whole,         -- 3
  ROUND(3.14159, 2) AS two_dec,    -- 3.14
  ROUND(1234.5, -2) AS hundreds;   -- 1200

SELECT
  CEIL(4.2) AS ceiling,   -- 5
  FLOOR(4.8) AS floor,    -- 4
  CEIL(-4.2) AS neg_ceil, -- -4
  FLOOR(-4.8) AS neg_fl;  -- -5

SELECT
  TRUNC(4.9) AS truncated,     -- 4
  TRUNC(-4.9) AS neg_trunc,    -- -4
  TRUNC(123.456, 1) AS one_dec; -- 123.4

SELECT
  ABS(-5) AS positive,     -- 5
  ABS(5) AS still_pos,     -- 5
  ABS(price - 100) AS diff
FROM products;

SELECT
  POWER(2, 3) AS eight,       -- 8
  POWER(10, 2) AS hundred,    -- 100
  SQRT(16) AS four,           -- 4
  SQRT(2) AS root2;           -- 1.414...

SELECT
  MOD(10, 3) AS remainder,    -- 1
  17 % 5 AS mod_operator,     -- 2
  MOD(id, 2) AS is_odd
FROM users;

SELECT
  RANDOM() AS zero_to_one,
  FLOOR(RANDOM() * 100) AS zero_to_99,
  FLOOR(RANDOM() * (max - min + 1) + min) AS range;
 
-- Random row
SELECT * FROM users
ORDER BY RANDOM()
LIMIT 1;

SELECT
  SIGN(-5) AS negative,   -- -1
  SIGN(0) AS zero,        -- 0
  SIGN(5) AS positive,    -- 1
  DIV(17, 5) AS int_div;  -- 3

SELECT
  ARRAY[1, 2, 3] AS int_array,
  ARRAY['a', 'b', 'c'] AS text_array,
  '{1,2,3}'::INTEGER[] AS cast_array;

SELECT
  (ARRAY['a','b','c'])[1] AS first,   -- 'a'
  (ARRAY['a','b','c'])[2:3] AS slice, -- {'b','c'}
  tags[1] AS first_tag
FROM posts;

-- Match any value in array
SELECT * FROM users
WHERE status = ANY(ARRAY['active', 'pending']);
 
-- Match all values
SELECT * FROM products
WHERE price > ALL(ARRAY[10, 20, 30]);

-- Contains all elements
SELECT * FROM posts
WHERE tags @> ARRAY['sql', 'database'];
 
-- Is contained by
SELECT * FROM posts
WHERE tags <@ ARRAY['sql', 'database', 'postgresql'];
 
-- Overlaps (has common elements)
SELECT * FROM posts
WHERE tags && ARRAY['sql', 'nosql'];

SELECT
  category,
  ARRAY_AGG(name) AS product_names,
  ARRAY_AGG(DISTINCT status) AS statuses
FROM products
GROUP BY category;

SELECT UNNEST(ARRAY[1, 2, 3]) AS num;
 
-- Expand column array
SELECT id, UNNEST(tags) AS tag
FROM posts;

SELECT
  ARRAY_LENGTH(ARRAY[1,2,3], 1) AS len,    -- 3
  ARRAY_CAT(ARRAY[1,2], ARRAY[3,4]) AS concat, -- {1,2,3,4}
  ARRAY_APPEND(ARRAY[1,2], 3) AS append,   -- {1,2,3}
  ARRAY_REMOVE(ARRAY[1,2,3], 2) AS removed; -- {1,3}

SELECT
  ARRAY_POSITION(ARRAY['a','b','c'], 'b') AS pos, -- 2
  'b' = ANY(ARRAY['a','b','c']) AS exists,        -- true
  CARDINALITY(ARRAY[1,2,3]) AS size;              -- 3