SQL cheatsheet
A one-page reference for SQL. For indexing internals and interview Q&A, see the complete guide.
📖 Full guide: SQL →Core DML
SELECT name, age FROM users WHERE age > 18 ORDER BY name;
INSERT INTO users (name, age) VALUES ('Abhishek', 30);
UPDATE users SET age = 31 WHERE name = 'Abhishek';
DELETE FROM users WHERE age < 0;
Joins
SELECT o.id, u.name
FROM orders o
INNER JOIN users u ON o.user_id = u.id; -- only matching rows
SELECT u.name, o.id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id; -- all users, matched orders or NULL
Aggregates & grouping
SELECT user_id, COUNT(*) AS orders, SUM(total) AS spend
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 5;
WHERE filters rows before grouping; HAVING filters groups after.
Subqueries vs CTEs vs window functions
-- CTE: named, readable, reusable within the query
WITH big_spenders AS (
SELECT user_id FROM orders GROUP BY user_id HAVING SUM(total) > 1000
)
SELECT * FROM users WHERE id IN (SELECT user_id FROM big_spenders);
-- Window function: per-row ranking without collapsing rows
SELECT id, total, RANK() OVER (ORDER BY total DESC) AS rank FROM orders;
Indexes & performance
CREATE INDEX idx_orders_user_id ON orders(user_id);
EXPLAIN SELECT * FROM orders WHERE user_id = 42;
An index speeds up lookups/joins on that column but slows down writes — don't index everything.
Transactions & ACID
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- or ROLLBACK on failure
Atomicity, Consistency, Isolation, Durability — the guarantees that make this transfer safe under concurrent access/crashes.
How SDETs actually use SQL
- Verify backend state directly instead of trusting only the UI (faster, more precise test setup/teardown).
- Seed test data via
INSERTinstead of clicking through the UI. - Debug flaky tests by querying what the app actually persisted.