SQL Commands Cheat Sheet for Interviews and Daily Work

Quick Answer

SQL splits into DQL for querying with SELECT, DML for changing data with INSERT, UPDATE and DELETE, DDL for structure with CREATE, ALTER and DROP, and DCL for permissions. The single most useful thing to memorise is the logical execution order — FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT — because it explains why aliases work in ORDER BY but not in WHERE, and why aggregates belong in HAVING.

Querying Data

-- Basics
SELECT col1, col2 FROM table;
SELECT * FROM table;                        -- avoid in production code
SELECT DISTINCT city FROM users;
SELECT name AS full_name FROM users;

-- Filtering
WHERE age > 18
WHERE city IN ('Pune', 'Mumbai')
WHERE age BETWEEN 18 AND 25                 -- inclusive on both ends
WHERE name LIKE 'A%'                        -- starts with A
WHERE name LIKE '%an%'                      -- contains 'an'
WHERE email IS NULL                         -- never use = NULL
WHERE active = 1 AND (city = 'Pune' OR city = 'Nagpur')

-- Sorting and limiting
ORDER BY salary DESC, name ASC
LIMIT 10
LIMIT 10 OFFSET 20                          -- rows 21-30

-- MySQL/PostgreSQL use LIMIT; SQL Server uses TOP or OFFSET/FETCH

The NULL rule that catches everyone: NULL means unknown, so it is never equal to anything, including another NULL. WHERE col = NULL returns no rows even when nulls exist. Always use IS NULL and IS NOT NULL.

The same logic means WHERE city != 'Pune' excludes rows where city is NULL, because unknown is not provably different either. Add OR city IS NULL if you want them.

Joins and Aggregation

-- Joins
SELECT u.name, d.title
FROM users u
INNER JOIN departments d ON u.dept_id = d.id;    -- only matches

LEFT JOIN  departments d ON u.dept_id = d.id     -- all users, NULL if no dept
RIGHT JOIN ...                                    -- all departments
FULL OUTER JOIN ...                               -- all of both
CROSS JOIN ...                                    -- every combination

-- Self join: employees and their managers
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- Find rows with NO match (the LEFT JOIN idiom)
SELECT u.name FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;                               -- users who never ordered

-- Aggregation
SELECT dept_id,
       COUNT(*)        AS headcount,
       COUNT(email)    AS with_email,             -- ignores NULLs
       AVG(salary)     AS avg_salary,
       SUM(salary)     AS total,
       MIN(salary), MAX(salary)
FROM employees
WHERE active = 1              -- filters ROWS, before grouping
GROUP BY dept_id
HAVING COUNT(*) > 5           -- filters GROUPS, after aggregation
ORDER BY avg_salary DESC;

Two traps. Putting a condition on the right table in WHERE silently converts a LEFT JOIN into an INNER JOIN, because NULL fails the comparison — put it in the ON clause instead. And aggregates ignore NULLs, so AVG(salary) over ten rows with two nulls divides by eight.

The Execution Order That Explains Everything

SQL is written in one order and evaluated in another. Memorising the logical order resolves a whole class of confusing errors.

WRITTEN:   SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT

EVALUATED: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

Why a SELECT alias fails in WHERE:

SELECT salary * 12 AS annual
FROM employees
WHERE annual > 500000;        -- ERROR: annual does not exist yet

WHERE salary * 12 > 500000;   -- correct: repeat the expression
ORDER BY annual;              -- fine — ORDER BY runs after SELECT

Why aggregates cannot appear in WHERE: WHERE runs before grouping, so COUNT(*) does not exist yet. That is exactly what HAVING is for.

Why DISTINCT and ORDER BY sometimes conflict: you cannot order by a column that DISTINCT removed from the result, because the sort happens after the projection.

This ordering also explains a performance principle: filtering in WHERE is cheaper than in HAVING, because it removes rows before the expensive grouping step rather than after.

Modifying Data and Structure

-- Insert
INSERT INTO users (name, email) VALUES ('Riya', 'riya@example.com');
INSERT INTO users (name, email) VALUES ('A', 'a@x.com'), ('B', 'b@x.com');
INSERT INTO archive SELECT * FROM users WHERE active = 0;

-- Update  -- ALWAYS write the WHERE first
UPDATE users SET city = 'Pune' WHERE id = 5;
UPDATE users SET salary = salary * 1.1 WHERE dept_id = 3;

-- Delete
DELETE FROM users WHERE id = 5;

-- Tables
CREATE TABLE users (
  id       INT PRIMARY KEY AUTO_INCREMENT,
  name     VARCHAR(100) NOT NULL,
  email    VARCHAR(255) UNIQUE,
  age      INT CHECK (age >= 18),
  dept_id  INT,
  created  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (dept_id) REFERENCES departments(id) ON DELETE SET NULL
);

ALTER TABLE users ADD COLUMN phone VARCHAR(15);
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users MODIFY name VARCHAR(150);

CREATE INDEX idx_users_email ON users(email);
DROP INDEX idx_users_email ON users;

TRUNCATE TABLE logs;        -- removes all rows, keeps the table, fast
DROP TABLE logs;            -- removes the table entirely

The habit that prevents disasters: write SELECT * FROM users WHERE ... first and check the rows, then change SELECT * to UPDATE ... SET or DELETE. An UPDATE without a WHERE updates every row, and there is no undo outside a transaction.

Subqueries, Window Functions and Transactions

-- Subqueries
SELECT name FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Correlated: runs per row
SELECT e.name FROM employees e
WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id);

-- EXISTS is often faster than IN for large sets
SELECT name FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- CTE — more readable than nested subqueries
WITH high_earners AS (
  SELECT * FROM employees WHERE salary > 100000
)
SELECT dept_id, COUNT(*) FROM high_earners GROUP BY dept_id;

-- Window functions: aggregate WITHOUT collapsing rows
SELECT name, dept_id, salary,
       ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn,
       RANK()       OVER (ORDER BY salary DESC) AS overall_rank,
       AVG(salary)  OVER (PARTITION BY dept_id) AS dept_avg
FROM employees;

-- Top earner per department
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) rn
  FROM employees
) t WHERE rn = 1;

-- Transactions
BEGIN;
  UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
  UPDATE accounts SET balance = balance + 1000 WHERE id = 2;
COMMIT;    -- or ROLLBACK to undo both

RANK vs DENSE_RANK vs ROW_NUMBER: with a tie, ROW_NUMBER gives 1,2,3 arbitrarily; RANK gives 1,1,3 leaving a gap; DENSE_RANK gives 1,1,2 with no gap. Interviewers ask this directly.

Frequently Asked Questions