The SQL syntax that cracks 90% of interviews

SELECT DISTINCT column1, column2 AS alias_name
FROM table_name
WHERE condition
ORDER BY column1 DESC, column2 ASC
LIMIT 10 OFFSET 20;

-- Find active engineers over 30, ordered by hire date
SELECT id, name, hire_date, salary
FROM employees
WHERE dept = 'Engineering'
  AND age > 30
  AND manager_id IS NOT NULL
ORDER BY hire_date DESC, name ASC
LIMIT 20;

-- INNER: only matching rows
SELECT e.name, d.name AS dept
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

-- LEFT: every employee, dept name if it exists (else NULL)
SELECT e.name, d.name AS dept
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

-- RIGHT: every department, employee if exists
SELECT e.name, d.name AS dept
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;

-- FULL OUTER: everything from both sides
SELECT e.name, d.name AS dept
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.id;

-- CROSS: every employee paired with every department
SELECT e.name, d.name AS dept
FROM employees e
CROSS JOIN departments d;

-- SELF JOIN: employee and their manager from same table
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- Orders → customers → employees (sales rep)
SELECT o.id, c.name AS customer, e.name AS sales_rep, o.amount
FROM orders o
JOIN customers c   ON o.customer_id = c.id
JOIN employees e   ON o.sales_rep_id = e.id
WHERE o.amount > 1000
ORDER BY o.amount DESC;

SELECT dept, COUNT(*) AS headcount, AVG(salary) AS avg_salary
FROM employees
WHERE active = true              -- filter rows BEFORE grouping
GROUP BY dept
HAVING COUNT(*) > 5              -- filter groups AFTER grouping
ORDER BY avg_salary DESC;

-- Average salary by department AND job title, only groups with 3+ people
SELECT dept, title, AVG(salary) AS avg_sal, COUNT(*) AS n
FROM employees
GROUP BY dept, title
HAVING COUNT(*) >= 3
ORDER BY dept, avg_sal DESC;

-- Find duplicate emails
SELECT email, COUNT(*) AS occurrences
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- 1. SCALAR subquery: employees earning more than company average
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- 2. IN subquery: employees in departments with budget over 1M
SELECT name
FROM employees
WHERE dept_id IN (
  SELECT id FROM departments WHERE budget > 1000000
);

-- 3. CORRELATED subquery: employees earning more than their dept average
SELECT e1.name, e1.salary, e1.dept
FROM employees e1
WHERE e1.salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.dept = e1.dept        -- correlated to outer row
);

-- 4. DERIVED TABLE: pre-aggregate, then filter
SELECT dept, headcount
FROM (
  SELECT dept, COUNT(*) AS headcount
  FROM employees
  GROUP BY dept
) t
WHERE headcount > 10;

-- 5. EXISTS: customers who placed at least one order
SELECT c.name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id
);

FUNCTION_NAME() OVER (
  PARTITION BY column1, column2     -- optional: restart per group
  ORDER BY column3                  -- optional: defines row order
  ROWS BETWEEN ... AND ...          -- optional: frame definition
) AS alias

-- Ranking: top 3 earners per department
SELECT dept, name, salary,
       DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk
FROM employees;

-- ROW_NUMBER: assign sequential row ids per group
SELECT name, dept,
       ROW_NUMBER() OVER (PARTITION BY dept ORDER BY hire_date) AS seq
FROM employees;

-- LAG / LEAD: compare row to previous/next (e.g., MoM growth)
SELECT month, revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_month,
       revenue - LAG(revenue) OVER (ORDER BY month) AS growth
FROM monthly_sales;

-- Running total (cumulative sum)
SELECT order_date, amount,
       SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

-- Average within department (no ORDER BY = whole partition as frame)
SELECT name, dept, salary,
       AVG(salary) OVER (PARTITION BY dept) AS dept_avg
FROM employees;

-- NTILE: split into 4 quartiles by salary
SELECT name, salary,
       NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;

-- FIRST_VALUE / LAST_VALUE in a partition
SELECT name, dept, salary,
       FIRST_VALUE(name) OVER (PARTITION BY dept ORDER BY salary DESC) AS top_earner
FROM employees;

-- ROWS: physical row count. RANGE: logical value range.
SUM(amount) OVER (
  ORDER BY date
  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW       -- 3-row rolling sum
)

SUM(amount) OVER (
  ORDER BY date
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  -- running total
)

SUM(amount) OVER (
  ORDER BY date
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  -- grand total per row
)

WITH dept_stats AS (
  SELECT dept, AVG(salary) AS avg_sal, COUNT(*) AS headcount
  FROM employees
  GROUP BY dept
),
high_earners AS (
  SELECT name, dept, salary
  FROM employees
  WHERE salary > 100000
)
SELECT h.name, h.dept, h.salary, d.avg_sal, d.headcount
FROM high_earners h
JOIN dept_stats d ON h.dept = d.dept
ORDER BY h.salary DESC;

WITH RECURSIVE org_tree AS (
  -- Anchor: top-level (no manager)
  SELECT id, name, manager_id, 1 AS depth,
         CAST(name AS VARCHAR(1000)) AS path
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive: find direct reports of previously found employees
  SELECT e.id, e.name, e.manager_id, o.depth + 1,
         CAST(o.path || ' > ' || e.name AS VARCHAR(1000))
  FROM employees e
  JOIN org_tree o ON e.manager_id = o.id
)
SELECT id, name, depth, path
FROM org_tree
ORDER BY path;

-- Generate a sequence of numbers 1..10
WITH RECURSIVE nums(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM nums WHERE n < 10
)
SELECT n FROM nums;

-- UNION ALL: combine customer + employee names (keep duplicates)
SELECT name, email FROM customers
UNION ALL
SELECT name, email FROM employees;

-- INTERSECT: people who are both customers AND employees
SELECT name FROM customers
INTERSECT
SELECT name FROM employees;

-- EXCEPT: customers who are NOT employees
SELECT name FROM customers
EXCEPT
SELECT name FROM employees;

-- Searched CASE (more flexible, use this)
SELECT name, salary,
  CASE
    WHEN salary >= 150000 THEN 'Staff+'
    WHEN salary >= 100000 THEN 'Senior'
    WHEN salary >= 70000  THEN 'Mid'
    ELSE 'Junior'
  END AS level
FROM employees;

-- Simple CASE (compares single value for equality)
SELECT name,
  CASE dept
    WHEN 'Eng' THEN 'Engineering'
    WHEN 'Sales' THEN 'Sales'
    ELSE 'Other'
  END AS dept_name
FROM employees;

-- Count employees by dept in one row per department
SELECT
  COUNT(CASE WHEN dept = 'Eng'   THEN 1 END) AS eng_count,
  COUNT(CASE WHEN dept = 'Sales' THEN 1 END) AS sales_count,
  COUNT(CASE WHEN dept = 'Ops'   THEN 1 END) AS ops_count
FROM employees;

-- Pivot: revenue per quarter, one row per dept
SELECT dept,
  SUM(CASE WHEN quarter = 1 THEN revenue ELSE 0 END) AS q1,
  SUM(CASE WHEN quarter = 2 THEN revenue ELSE 0 END) AS q2,
  SUM(CASE WHEN quarter = 3 THEN revenue ELSE 0 END) AS q3,
  SUM(CASE WHEN quarter = 4 THEN revenue ELSE 0 END) AS q4
FROM quarterly_revenue
GROUP BY dept;

-- Safe percentage: divide but never by zero
SELECT
  dept,
  SUM(CASE WHEN active THEN 1 ELSE 0 END) AS active_count,
  COUNT(*) AS total,
  ROUND(
    SUM(CASE WHEN active THEN 1 ELSE 0 END) * 100.0 /
    NULLIF(COUNT(*), 0),
    2
  ) AS pct_active
FROM employees
GROUP BY dept;

-- Clean and normalize names
SELECT
  TRIM(name) AS clean_name,
  UPPER(TRIM(name)) AS upper_name,
  INITCAP(name) AS proper_name             -- PostgreSQL
FROM users;

-- Extract domain from email
SELECT email,
  SUBSTRING(email FROM POSITION('@' IN email) + 1) AS domain
FROM users;

-- Mask all but last 4 digits of SSN
SELECT
  CONCAT(REPEAT('•', LENGTH(ssn) - 4), RIGHT(ssn, 4)) AS masked
FROM users;

-- Combine rows into a comma-separated list
SELECT dept,
  STRING_AGG(name, ', ' ORDER BY name) AS all_names
FROM employees
GROUP BY dept;

-- Pattern matching with regex (PostgreSQL)
SELECT name FROM users
WHERE name ~ '^[A-Z][a-z]+$';              -- starts capital, rest lowercase

-- Users who signed up in the last 30 days
SELECT name, created_at
FROM users
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days';

-- Orders grouped by month
SELECT
  DATE_TRUNC('month', order_date) AS month,
  COUNT(*) AS order_count,
  SUM(amount) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

-- Average days between a user's first and second order
WITH order_seq AS (
  SELECT user_id, order_date,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date) AS n
  FROM orders
)
SELECT AVG(second_date - first_date) AS avg_days_between
FROM (
  SELECT user_id,
    MAX(CASE WHEN n = 1 THEN order_date END) AS first_date,
    MAX(CASE WHEN n = 2 THEN order_date END) AS second_date
  FROM order_seq
  WHERE n <= 2
  GROUP BY user_id
) t
WHERE second_date IS NOT NULL;

-- Age from birthdate
SELECT name,
  EXTRACT(YEAR FROM AGE(CURRENT_DATE, birthdate))::int AS age
FROM users;

-- Single row
INSERT INTO employees (name, dept, salary)
VALUES ('Alice', 'Eng', 120000);

-- Multiple rows
INSERT INTO employees (name, dept, salary)
VALUES
  ('Bob',   'Eng',   95000),
  ('Carol', 'Sales', 85000),
  ('Dave',  'Eng',  110000);

-- INSERT from SELECT (load from another table)
INSERT INTO archive_employees (id, name, retired_on)
SELECT id, name, CURRENT_DATE
FROM employees
WHERE active = false;

-- UPDATE with condition
UPDATE employees
SET salary = salary * 1.1, updated_at = NOW()
WHERE dept = 'Eng' AND performance = 'exceeds';

-- UPDATE from join (PostgreSQL syntax)
UPDATE employees e
SET salary = e.salary * 1.05
FROM departments d
WHERE e.dept_id = d.id AND d.budget > 1000000;

-- DELETE with condition
DELETE FROM employees
WHERE active = false AND last_login < CURRENT_DATE - INTERVAL '1 year';

-- DELETE using subquery
DELETE FROM orders
WHERE customer_id IN (
  SELECT id FROM customers WHERE deleted = true
);

-- DELETE duplicates keeping the lowest id
DELETE FROM users
WHERE id NOT IN (
  SELECT MIN(id) FROM users GROUP BY email
);

-- Insert, or update on conflict
INSERT INTO employees (id, name, salary)
VALUES (1, 'Alice', 120000)
ON CONFLICT (id)
DO UPDATE SET salary = EXCLUDED.salary, name = EXCLUDED.name;

-- Insert, or do nothing
INSERT INTO subscriptions (user_id, plan)
VALUES (42, 'pro')
ON CONFLICT (user_id) DO NOTHING;

INSERT INTO employees (id, name, salary)
VALUES (1, 'Alice', 120000)
ON DUPLICATE KEY UPDATE salary = VALUES(salary);

CREATE TABLE employees (
  id           SERIAL PRIMARY KEY,                    -- auto-incrementing PK
  email        VARCHAR(255) NOT NULL UNIQUE,          -- no duplicates
  name         VARCHAR(100) NOT NULL,
  dept_id      INTEGER REFERENCES departments(id),    -- foreign key
  salary       NUMERIC(10, 2) CHECK (salary >= 0),    -- constraint
  hire_date    DATE DEFAULT CURRENT_DATE,             -- default value
  manager_id   INTEGER REFERENCES employees(id),      -- self-reference
  created_at   TIMESTAMP DEFAULT NOW(),

  -- Table-level constraints
  CONSTRAINT valid_salary CHECK (salary < 1000000),
  CONSTRAINT unique_dept_role UNIQUE (dept_id, email)
);

-- Add / drop column
ALTER TABLE employees ADD COLUMN phone VARCHAR(20);
ALTER TABLE employees DROP COLUMN phone;

-- Add constraint
ALTER TABLE employees
  ADD CONSTRAINT positive_salary CHECK (salary >= 0);

-- Create index (for query speed)
CREATE INDEX idx_employees_dept     ON employees(dept_id);
CREATE UNIQUE INDEX idx_email       ON employees(email);
CREATE INDEX idx_orders_date_amount ON orders(order_date, amount);  -- composite

-- Drop & truncate (TRUNCATE is faster, can't be rolled back in some DBs)
DROP TABLE employees;          -- removes structure + data
TRUNCATE TABLE employees;      -- removes data, keeps structure

-- View: a saved query
CREATE VIEW active_employees AS
SELECT id, name, dept
FROM employees
WHERE active = true;

-- View the query plan (PostgreSQL)
EXPLAIN SELECT * FROM employees WHERE dept_id = 5;

-- View plan + actually run it (shows real timing)
EXPLAIN ANALYZE SELECT * FROM employees WHERE dept_id = 5;

-- MySQL equivalent
EXPLAIN SELECT * FROM employees WHERE dept_id = 5;