Topics
What is a database? Tables, rows, columns
Understand DBMS, tables and why not just Excel.
SELECT and WHERE
Fetch rows and filter them.
ORDER BY, LIMIT, DISTINCT, LIKE
Sort, cap and de-duplicate results; pattern match.
COUNT, SUM, AVG, MIN, MAX
Summarise data with aggregate functions.
GROUP BY and HAVING
Aggregate per group and filter groups.
INSERT, UPDATE, DELETE
Change data safely.
CREATE, ALTER, constraints
Design tables with NOT NULL, UNIQUE, CHECK, DEFAULT.
Primary and foreign keys
Link tables with keys and keep data consistent.
INNER JOIN
Combine rows from two tables on a matching key.
LEFT, RIGHT and FULL joins
Keep unmatched rows and see NULLs.
Self join
Join a table with itself.
Subqueries
Use a query inside another query.
Nth highest salary and classic interview queries
Solve the most-asked SQL interview problems.
Window functions: ROW_NUMBER, RANK, running totals
Compute rankings and running sums without collapsing rows.
CTEs and recursive queries
Write readable multi-step queries with WITH.
ER diagrams
Model entities, attributes and relationships.
Normalisation: 1NF, 2NF, 3NF, BCNF
Remove redundancy by splitting tables correctly.
Transactions and ACID
Understand commit, rollback and ACID properties.
Concurrency, locks and isolation levels
See dirty reads and lost updates and how locks prevent them.
Indexes
Speed up lookups with B-tree indexes and know the trade-offs.
Views and stored procedures
Save queries as views and reusable procedures.
Project: e-commerce database
Design and query a complete small schema.