SQL Tutorial
Learn SQL from your first SELECT to window functions, query tuning and transactions. Every topic has a clear explanation, runnable MySQL code, the points to remember and interview questions.
52 topics · Beginner → Expert · concept, code, interview questions and a test in every topic
Module 02 – Database & Table Management (DDL)
0/13 · 0%
- 2CREATE TABLEDefine a new table: its name, columns, data types and constraints.Detailed · live SQL
- 3ALTER TABLE – ADD COLUMNAdd a new column to a table that already has data.Detailed · live SQL
- 4ALTER TABLE – RENAME COLUMNRename a column without touching its data or type.Detailed · live SQL
- 5ALTER TABLE – DROP COLUMNPermanently remove a column and all its values.Detailed · live SQL
- 6RENAME TABLE / ALTER TABLE RENAME TOGive an existing table a new name, keeping its data.Detailed · live SQL
- 7DROP TABLEDelete a table completely: structure, data, indexes and permissions.Detailed · live SQL
- 8TRUNCATE TABLERemove every row instantly while keeping the table structure.Detailed · live SQL
- 9DELETE vs TRUNCATE vs DROPThe three ways to remove data — and when each one is right.Detailed · live SQL
- 10CREATE TABLE vs CREATE TABLE AS SELECTBuild a new table directly from a query's result.Detailed · live SQL
- 11ALTER TABLE – MODIFY & CHANGE COLUMNChange a column's data type, size, NULL rule or default — and optionally its name.Detailed · live SQL
- 12ALTER TABLE – Add & Drop ConstraintsAdd or remove PRIMARY KEY, UNIQUE, CHECK, FOREIGN KEY and DEFAULT rules on an existing table.Detailed · live SQL
- 13Database Commands – CREATE, USE, SHOW, DROP DATABASECreate, list, select and delete whole databases (schemas).Detailed · live SQL
- 14DESCRIBE & SHOW – Inspecting StructureLook at tables, columns, indexes and full definitions without reading data.Detailed · live SQL
Beginner lessons
0/5 · 0%
- 1Databases, Tables & SQLWhat a relational database is and how SQL talks to it.
- 15CREATE TABLE & Data TypesDesign tables with the right column types.
- 16Keys & ConstraintsPrimary keys, foreign keys, UNIQUE, NOT NULL, CHECK and DEFAULT.
- 17INSERT, UPDATE, DELETEAdd, change and remove rows safely.
- 18SELECT, WHERE & SortingFilter rows, handle NULLs, sort and limit results.
Module — SQL Functions, Methods & Commands (Basics → Advanced)
0/11 · 0%
- 30Functions Module: Roadmap & How Functions WorkWhat a function is, the categories of SQL commands and functions, and the order to learn them.
- 31Commands Reference: DDL (CREATE, ALTER, DROP, TRUNCATE)Every Data Definition Language command and its common options, with examples.
- 32Commands Reference: DML & DQL (INSERT, UPDATE, DELETE, SELECT)Every way to add, change, remove and read rows — including upserts and multi-table updates.
- 33Commands Reference: DCL & TCL (GRANT, REVOKE, COMMIT, ROLLBACK)Users, roles and permissions, plus transaction control with savepoints and isolation levels.
- 34Functions Reference: String & NumericEvery common MySQL string and math function, what it does and an example.
- 35Functions Reference: Date, NULL & AggregateDate/time, NULL-handling, conditional, aggregate and window functions in one place.
- 36Functions Reference: Date & Time Deep DiveFormatting codes, conversions, time zones, week/quarter maths and common date patterns.
- 37Functions Reference: Conditional, Comparison & ConversionCASE, IF, NULL handling, comparison operators and type conversion with their traps.
- 38Functions Reference: Aggregate Functions Deep DiveCOUNT variants, conditional aggregation, GROUP_CONCAT options, ROLLUP and statistics.
- 39Functions Reference: Advanced, Window & JSONRanking, offset and value window functions plus MySQL 8 JSON helpers, with frames explained.
- 40Functions Reference: System, Info & SecuritySession and metadata functions, IDs, hashing/encryption helpers, locks and stored routines.
Intermediate lessons
0/4 · 0%
- 19Aggregates, GROUP BY & HAVINGSummarise data with COUNT, SUM, AVG, MIN and MAX.
- 20JOINsCombine tables with INNER, LEFT, RIGHT, CROSS and SELF joins.
- 21Subqueries & EXISTSQueries inside queries: scalar, IN, EXISTS and correlated.
- 22CASE, Functions & UNIONConditional logic, string/date functions and set operators.
Advanced lessons
0/4 · 0%
- 23CTEs & Recursive QueriesReadable step-by-step queries and hierarchy walks with WITH.
- 24Window FunctionsROW_NUMBER, RANK, LAG/LEAD and running totals without collapsing rows.
- 25Normalization & Schema Design1NF, 2NF, 3NF, BCNF and when to denormalise.
- 26Indexes & EXPLAINHow B-tree indexes speed up queries and how to read a plan.
Expert lessons
0/3 · 0%
Interview preparation
Module — Core Technical Interview Q&A (Top Questions & Answers)
6 questions · 0 completed · 0%
Module — Core Technical Interview Q&A (Top Questions & Answers)
6 questions · 0 completed · 0%
Beginner · Fundamentals
Why does a comparison silently exclude NULL rows?
NULL represents an unknown or missing value; comparisons such as NULL = 4 evaluate to UNKNOWN, not FALSE or TRUE. WHERE keeps only TRUE rows.
Basic · Aggregation
When should a filter use WHERE instead of HAVING?
WHERE filters input rows before grouping; HAVING filters groups after aggregate values have been calculated. They answer different questions and are not interchangeable.
Intermediate · Joins
Why can joining two valid tables double the reported revenue?
A join emits every matching pair. Unique business entities in one table do not imply a unique join key in both tables; two child rows multiply one parent amount twice.
Advanced · Windows
How do ties and window frames change a top-earner report?
ROW_NUMBER selects a fixed number of rows, RANK leaves gaps after ties, and DENSE_RANK selects distinct value tiers without gaps. Top three employees and top three salary levels are different requirements.
Expert · Production concurrency
How do you safely retry a transaction after a deadlock?
A deadlock is a cycle of transactions waiting for locks. The database aborts a victim so other transactions can proceed. Retry the whole logical transaction, not merely the last failed statement.
Architect / Specialist · Warehouse architecture
How would you reduce the cost of a daily billion-row warehouse query?
Start with measured bytes scanned, slot usage or warehouse time, shuffle volume and output grain. BigQuery bills on bytes under on-demand pricing; capacity pricing and Snowflake credits have different cost models.
Module — Coding Interview Problems & Solutions (Hands-on)
6 questions · 0 completed · 0%
Module — Coding Interview Problems & Solutions (Hands-on)
6 questions · 0 completed · 0%
Beginner · Filtering
Filter employees without losing missing salaries
Return employees whose salary is below 90 or unknown, ordered by ID.
Basic · Aggregation
Count known salaries by department
Return total employees and known salaries for each department.
Intermediate · Ranking
Find the second distinct highest salary
Return one row containing the second distinct highest non-null salary, or NULL if absent.
Advanced · Windows
Select top two salary tiers per department
Return every employee in the two highest distinct salary tiers in each department, excluding NULL.
Expert · Gaps and islands
Find consecutive-day login islands
Return each maximal streak as user_id, start_day, end_day, length after deduplicating same-day logins.
Architect / Specialist · Incremental data quality
Reconcile latest versioned payments before aggregation
For each payment ID keep the highest version, then sum amount per customer.
A–Z index
- Aggregates, GROUP BY & HAVING
- ALTER TABLE – Add & Drop Constraints
- ALTER TABLE – ADD COLUMN
- ALTER TABLE – DROP COLUMN
- ALTER TABLE – MODIFY & CHANGE COLUMN
- ALTER TABLE – RENAME COLUMN
- CASE, Functions & UNION
- Commands Reference: DCL & TCL (GRANT, REVOKE, COMMIT, ROLLBACK)
- Commands Reference: DDL (CREATE, ALTER, DROP, TRUNCATE)
- Commands Reference: DML & DQL (INSERT, UPDATE, DELETE, SELECT)
- Count known salaries by department
- CREATE TABLE
- CREATE TABLE & Data Types
- CREATE TABLE vs CREATE TABLE AS SELECT
- CTEs & Recursive Queries
- Database Commands – CREATE, USE, SHOW, DROP DATABASE
- Databases, Tables & SQL
- DELETE vs TRUNCATE vs DROP
- DESCRIBE & SHOW – Inspecting Structure
- DROP TABLE
- Expert Interview Patterns
- Filter employees without losing missing salaries
- Find consecutive-day login islands
- Find the second distinct highest salary
- Functions Module: Roadmap & How Functions Work
- Functions Reference: Advanced, Window & JSON
- Functions Reference: Aggregate Functions Deep Dive
- Functions Reference: Conditional, Comparison & Conversion
- Functions Reference: Date & Time Deep Dive
- Functions Reference: Date, NULL & Aggregate
- Functions Reference: String & Numeric
- Functions Reference: System, Info & Security
- How do ties and window frames change a top-earner report?
- How do you safely retry a transaction after a deadlock?
- How would you reduce the cost of a daily billion-row warehouse query?
- Indexes & EXPLAIN
- INSERT, UPDATE, DELETE
- JOINs
- Keys & Constraints
- Normalization & Schema Design
- Reconcile latest versioned payments before aggregation
- RENAME TABLE / ALTER TABLE RENAME TO
- Select top two salary tiers per department
- SELECT, WHERE & Sorting
- Subqueries & EXISTS
- Transactions & ACID
- TRUNCATE TABLE
- Views, Procedures & Triggers
- When should a filter use WHERE instead of HAVING?
- Why can joining two valid tables double the reported revenue?
- Why does a comparison silently exclude NULL rows?
- Window Functions
Curriculum roadmap (20 modules)
- 01 Database Fundamentals — partial · Covered in the existing intro lesson
- 02 Database & Table Management (DDL) — done · 13 detailed lessons: CREATE/ALTER/MODIFY/CHANGE, constraints via ALTER, DROP/TRUNCATE, CTAS, database commands, DESCRIBE/SHOW
- 03 Data Types — coming soon
- 04 Constraints — partial · Existing constraints lesson
- 05 DML: INSERT / UPDATE / DELETE — partial · Existing DML lesson
- 06–07 SELECT, filtering & sorting — partial · Existing lessons
- 08 Functions reference — partial · 3 reference lessons
- 09 Joins — partial · Existing lesson
- 10 Subqueries & CTEs — partial · Existing lesson
- 11–14 Set ops, views, transactions, DCL — partial · Existing lessons
- 15–16 Database objects, indexing & optimisation — partial · Existing lessons
- 17 Design & normalisation — coming soon
- 18 SQL for Data Engineering — coming soon
- 19 Dialects — coming soon
- 20 Real-world projects — coming soon
Can't find a SQL topic you need for exams or placements? Request it and we'll add it.