Database and SQL Roadmap

There's a lot you can learn when it comes to databases and SQL. Once you learn the basics, you can write some SQL queries on a database. But what else is there to know? Which topics should you learn next?

I've broken down the different areas of databases and SQL in this roadmap.

It's based on a tree structure: start at the top with the basics, and move down the tree to move advanced topics. This is just based on my own opinions and experience, so you may want to learn things in a different order. Each of the sections contain links to guides I've written on the topics, if applicable.

Let me know if you have any feedback.

Enjoy!

Database Basics

Understand what a database is and how it fits into an application.

Understand what tables, columns, and rows are.

SQL Level 1

Use the SELECT statement to display all data, and specific columns from a table: SQL Select Statement, 59 Best Resources for Learning SQL

Use the WHERE clause to filter data: SQL WHERE Clause: Guide and Examples

Order results with the ORDER BY clause: SQL ORDER BY - The Complete Guide

Update data with the UPDATE statement: SQL UPDATE Statement: The Complete Guide

Insert data with the INSERT statement: SQL INSERT: The Complete Guide

Delete data with the DELETE statement: SQL DELETE: The Complete Guide

Create tables: SQL CREATE TABLE Syntax and Examples - The Complete Guide

Alter tables: SQL ALTER TABLE: A Complete Guide

Drop tables: SQL DROP TABLE: The Complete Guide

SQL Level 2

Using SQL functions: Oracle SQL Functions, MySQL Functions, SQL Server Functions, PostgreSQL Functions

Simple aggregate functions: SQL Aggregate Functions

Grouping data with GROUP BY: SQL GROUP BY and HAVING: The Complete Guide

Limiting grouped data with HAVING: The Difference Between WHERE and HAVING Clause

Joins: inner, left, right: SQL Joins: The Complete Guide

A basic understanding of indexes: Clustered and Non-Clustered Index in SQL Server, Oracle SQL Indexes - The Definitive Guide

Understand table constraints: Constraints: The Complete Guide

Understand the purpose of DML and DDL: DML, DDL, and TCL Commands in SQL

Understand the data types available for columns: SQL Data Types

Operators such as IN and EXISTS: SQL Operators: The Complete Guide

Delete data with the TRUNCATE statement: TRUNCATE TABLE Guide

Modifying columns in a table: SQL ALTER TABLE: A Complete Guide

Table and column aliases: SQL Alias: A Guide

Procedural Language Level 1

Procedural language basics (PL/SQL, T-SQL, pgSQL): PL/SQL Tutorial

Output to console or screen

If, Then, Else

Variables

Procedural Language Level 2

Triggers: Oracle Triggers - The Complete Guide

Cursors: A Beginner’s Guide to SQL Cursors

Stored procedures and functions: SQL Stored Procedures: The Complete Guide

SQL Level 3

Temporary tables: SQL Temp Tables: The Ultimate Guide

Transactions, locking, isolation levels: SQL Transactions and Isolation Levels: The Ultimate Guide

Subqueries in the FROM clause: SQL Subqueries: The Complete Guide

Self joins: SQL Joins: The Complete Guide

Complex results using GROUP BY and aggregate functions

Auto increment features (e.g. Identity, Sequence): SQL Auto Increment: How to Use It in Different Databases

Views and Materialised Views: SQL Views: The Complete Guide

Analytic functions, Partition By, window functions: SQL Analytic Functions, SQL Window Functions

Row limiting: SQL Limit: The Complete Guide to SQL Row Limiting and Top-N Queries

WITH clause, CTEs: SQL CTE (WITH Clause): The Ultimate Guide

Set operators: SQL Set Operators: The Complete Guide

Generate an execution plan and understand the basics: Ultimate Guide to the SQL Execution Plan

Comments on objects: SQL Comments

DBA Level 1

What privileges are

System vs object privileges

Privileges vs roles

Granting and revoking privileges and roles

Basic data dictionary views

Creating and removing users

DBA Level 2

Basic profiling for monitoring and debugging

Generate profiles and run traces for executions of queries

Understand how data is structured and stored on disk

SQL Level 4

Pivot and Unpivot: SQL PIVOT and UNPIVOT: The Complete Guide

Subqueries in different places (SELECT, WHERE): SQL Subqueries: The Complete Guide

Correlated subqueries

Nested transactions: SQL Transactions and Isolation Levels: The Ultimate Guide

Character sets: Overview of Oracle Character Sets

Bind variables

CI/CD, automated deployment: Ultimate Guide

SQL Level 5

Flashback operations in Oracle

External tables

Merge rows: Merge: The Complete Guide

Distributed systems

Database Design Level 1

Understand what an ERD (Entity Relationship Diagram) is: A Guide to the Entity Relationship Diagram (ERD)

Create an ERD

Understand primary keys and foreign keys: Database Keys: The Complete Guide

Database normalisation (first to third normal form): Database Normalization: A Step-By-Step-Guide With Examples

Database Design Level 2

Further normal forms (3.5NF, 4NF. BCNF)

Denormalising a database

Designing a data warehouse: Ultimate Guide to Data Warehouses

Understanding solutions to common design issues