SQL Revision

Overview

This checklist covers relational database fundamentals, complex query authoring, window functions, and schema design principles essential for backend engineering.

Tip: Practice writing window functions (ROW_NUMBER, DENSE_RANK, LEAD/LAG) and CTEs (WITH) on sample datasets to ensure quick problem solving during SQL interviews.

DDL & DML Refresh

• CREATE TABLE, ALTER TABLE, DROP TABLE
• Data types — INT, VARCHAR, TEXT, DATE, TIMESTAMP, BOOLEAN, DECIMAL
• INSERT, SELECT, UPDATE, DELETE
• WHERE, AND/OR, IN, BETWEEN, LIKE, IS NULL
• ORDER BY, LIMIT, OFFSET, DISTINCT

Aggregation & Grouping

• COUNT, SUM, AVG, MIN, MAX
• GROUP BY, HAVING
• Window functions — ROW_NUMBER, RANK, DENSE_RANK, LEAD, LAG, OVER(PARTITION BY)

Joins & Subqueries

• INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN
• CROSS JOIN, SELF JOIN
• Subqueries — correlated & non-correlated
• EXISTS, IN with subqueries
• UNION vs UNION ALL

Database Design Refresh

• Constraints — PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, DEFAULT
• Normalization — 1NF, 2NF, 3NF, BCNF
• Indexing — B-tree, composite, unique, when to index & when not to
• Transactions — BEGIN, COMMIT, ROLLBACK, ACID properties
• Isolation levels — Read Uncommitted, Read Committed, Repeatable Read, Serializable

Advanced SQL

• Views — CREATE VIEW
• Stored procedures & functions (basics)
• Triggers (basics)
• EXPLAIN / EXPLAIN ANALYZE — query optimization
• Common Table Expressions (CTEs) — WITH