Below is a comprehensive, industry-aligned SQL syllabus for a Data Analyst, structured in macro (high-level) and micro (granular) topics. This list is designed to cover interview readiness, real-world project work, and day-to-day analyst responsibilities.
I am intentionally being exhaustive so that nothing important is left out.
.png)
Complete Macro & Micro Topic Map
1. SQL Fundamentals (Non-Negotiable Core)
Macro Topics
- Relational Databases & SQL Basics
- Data Types and Table Structures
Micro Topics
- What is a database, table, row, column
- RDBMS concepts (MySQL, PostgreSQL, SQL Server)
- Primary key vs foreign key
- NULL values and NULL handling
- Data types:
-
- Numeric (INT, FLOAT, DECIMAL)
- String (VARCHAR, CHAR, TEXT)
- Date & Time (DATE, TIME, TIMESTAMP)
- Boolean
- Schema and database structure
- Case sensitivity in SQL
- SQL keywords vs identifiers
- SQL execution order (conceptual overview)
2. Data Retrieval with SELECT (Most Tested Area)
Macro Topics
- Querying data
- Filtering, sorting, limiting
Micro Topics
SELECT *vs selecting specific columns- Column alias (
AS) - DISTINCT
WHEREclause- Comparison operators (
=,!=,<,>,<=,>=) - Logical operators (
AND,OR,NOT) IN,BETWEENLIKE, wildcard patterns (%,_)IS NULL,IS NOT NULL- ORDER BY
LIMIT/OFFSETTOP(SQL Server)- Writing readable SQL (formatting & indentation)
3. Aggregate Functions & Grouping (Analyst Bread & Butter)
Macro Topics
- Aggregation and summarization
Micro Topics
- Aggregate functions:
- COUNT
- SUM
- AVG
MIN,MAX- GROUP BY
HAVINGvsWHERE- Grouping by multiple columns
- Aggregation on calculated fields
- Common aggregation mistakes
- Handling NULLs in aggregates
4. Joins & Table Relationships (Most Important Interview Topic)
Macro Topics
- Combining data from multiple tables
Micro Topics
- Types of joins:
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
FULL OUTER JOIN- Join conditions (
ON) - Joining on multiple keys
- Self joins
- Cross joins
- Many-to-one and many-to-many relationships
- Join order and readability
- Common join pitfalls (duplicate rows, fan-out effect)
- Identifying missing matches using joins
5. Subqueries & Nested Queries
Macro Topics
- Querying within queries
Micro Topics
- Subqueries in:
- SELECT
- FROM
WHERE- Scalar subqueries
- Correlated vs non-correlated subqueries
INvsEXISTS- Performance implications
- When to use subqueries vs joins
6. Common Table Expressions (CTEs)
Macro Topics
- Query modularization
Micro Topics
WITHclause- Single CTE
- Multiple CTEs
- Referencing CTEs
- Recursive CTEs (basic understanding)
- CTE vs subquery
- Improving query readability using CTEs
7. Window Functions (High-Value Skill)
Macro Topics
- Analytical functions without collapsing rows
Micro Topics
OVER()clause- PARTITION BY
ORDER BYin window functions- Common window functions:
ROW_NUMBERRANKDENSE_RANKLEAD,LAGNTILERunning totalsMoving averagesWindow functions vsGROUP BY- Interview-level use cases (top-N per group, trend analysis)
8. Conditional Logic in SQL
Macro Topics
- Business logic implementation
Micro Topics
- CASE WHEN
- Simple vs searched CASE
- Nested CASE statements
- Handling NULLs in CASE
-
Using CASE in:
- SELECT
- WHERE
- ORDER BY
- GROUP BY
- Practical business scenarios (classification, flags)
-
9. Date & Time Functions (Frequently Tested)
Macro Topics
- Time-based analysis
Micro Topics
- Current date/time functions
- Date arithmetic
-
Extracting:
- Year, month, day
- Week, quarter
- DATEDIFF
- DATEADD
- Formatting dates
- Handling time zones (conceptual)
- Month-over-month, year-over-year analysis
- Cohort-style date analysis
10. String Functions & Text Processing
Macro Topics
- Text manipulation
Micro Topics
CONCATUPPER,LOWERTRIM,LTRIM,RTRIM- SUBSTRING
- REPLACE
- LENGTH
- Pattern matching
- Cleaning dirty text data
11. Data Cleaning & Transformation in SQL
Macro Topics
- Preparing data for analysis
Micro Topics
- Handling missing values
- Removing duplicates
- De-duplication strategies
- Data type casting (
CAST,CONVERT) - Standardizing formats
- Creating derived columns
- Validating data consistency
12. Database Design Basics (Analyst-Level)
Macro Topics
- Understanding data structure
Micro Topics
- Normalization (1NF, 2NF, 3NF)
- Denormalization (why it exists)
- Fact tables & dimension tables
- Star schema
- Snowflake schema
- Surrogate vs natural keys
13. SQL Performance & Optimization (Interview Differentiator)
Macro Topics
- Writing efficient queries
Micro Topics
- Indexes (basic understanding)
- Query execution order
- Avoiding unnecessary columns
- Avoiding SELECT *
- Join optimization concepts
- Subquery vs join performance
- Using
EXPLAIN(conceptual)
14. SQL Constraints & Integrity (Awareness Level)
Macro Topics
- Data quality enforcement
Micro Topics
PRIMARY KEY- FOREIGN KEY
- UNIQUE
- NOT NULL
- CHECK
- Referential integrity
15. SQL for Analytics Use Cases (Project-Critical)
Macro Topics
- Business problem solving
Micro Topics
- Funnel analysis
- Retention analysis
- Churn analysis (basic)
- KPI calculation
- Customer segmentation
- Sales & revenue analysis
- Cohort analysis (introductory)
- Dashboard-ready queries
16. SQL Interview Patterns (Must Practice Separately)
Macro Topics
- Interview problem types
Micro Topics
- Top-N problems
- Duplicate detection
- Consecutive records
- Gap analysis
- Ranking problems
- Percentages & ratios
- Case-based business questions
- Real-world datasets (orders, customers, products)
17. SQL Environment & Tooling (Awareness)
Macro Topics
- Working in real systems
Micro Topics
- Writing SQL in:
- MySQL
- PostgreSQL
- SQL Server
- SQL in BI tools (Power BI, Tableau)
- SQL in Python (basic exposure)
- Version control awareness (Git, optional)
Final Reality Check (Important)
If you master topics 1–11 deeply, you are interview-ready for:
- Junior Data Analyst
- Entry-level Analyst
- Internship roles
If you also understand 12–17, you move into:
- Strong Data Analyst
- Project-ready candidate
- High-confidence interview performer
