There’s a lot to learn with SQL. Once you understand the basics, you’ll feel comfortable running queries on tables.
But what else is there to know? What other things do you need to know about, and which topics are more advanced?
I’ve broken down the many different topics of SQL and databases in this SQL Roadmap.
If you have any feedback or questions, feel free to add them in the comments at the bottom of the page.
Database Basics
What is a database?
The first step is to understand what a database is: a tool that’s used to store and manage data.
Learn more: Wikipedia, JavaTPoint
What is SQL?
SQL stands for Structured Query Language and is the language used to interact with databases.
Learn more: Wikipedia, Learn To Code With Me, SQL vs MySQL vs SQL Server: What’s the Difference?
Basic Select Query
SELECT Statement
Learn how to use the Select keyword to view data, including all columns or only some columns.
Learn more: SQL SELECT Statement, DUAL Table in Oracle
WHERE Clause
The WHERE clause lets you filter data out of your Select query, and also works with Update and Delete statements.
Learn more: SQL WHERE Clause
ORDER BY Clause
This clause allows you to order the results of your Select query,
Learn more: SQL ORDER BY
Aggregate Functions
Aggregate functions allow you to combine multiple rows into one, such as counting records or adding numbers.
Learn more: SQL Aggregate Functions
GROUP BY Clause
The GROUP BY clause allows you to group data by values in a column and use aggregate functions.
Learn more: SQL Group By Clause
HAVING Clause
The HAVING clause allows you to filter data after a group has been applied.
Learn More: SQL Having Clause, Difference Between Where and Having
Joins
A join is where you combine data from two or more tables into one query.
Learn more: SQL Joins
Modifying Data
UPDATE Statement
The Update statement allows you to make changes to data in a table.
Learn more: SQL Update Statement
DELETE Statement
The Delete statement allows you to remove records from a table.
Learn more: SQL Delete Statement
INSERT Statement
The Insert statement allows you to add new records to a table.
Learn more: SQL Insert Statement, Inserting Multiple Rows
Truncate Table
The Truncate Table statement lets you remove all data from a table.
Learn more: SQL Truncate Table
Working with Tables
Create Table
Create new tables in your database with the Create Table statement.
Learn more: SQL Create Table, Create a Database in SQL
Alter Table
Make changes to your table, such as adding or removing columns, with the Alter Table statement.
Learn more: SQL Alter Table, SQL Add Column,
Drop Table
Remove a table from your database with the Drop Table command.
Learn more: SQL Drop Table
Data Types
There are many data types available in each database, each of which have their pros and cons.
Learn more: SQL Data Types, SQL INT Data Types
Improving Your Queries
Indexes
Indexes are objects that help the performance of your queries.
Learn more: SQL Indexes
Primary Keys
A primary key is a way of uniquely identifying a record in a table.
Learn more: Database Keys
Foreign Keys
A foreign key is how a record in one table is related to a record in another table.
Learn more: Database Keys
Constraints
Constraints are rules that can be added to tables to improve the quality of data.
Learn more: Constraints
Column and Table Aliases
Aliases are names given to tables or columns in your query.
Learn more: Aliases in SQL
Database Design
Normalisation (First to Third)
Normalisation is the process of transforming a database design using a set of rules to improve its efficiency and quality.
Learn more: Database Normalisation: A Step-By-Step Guide
ERDs
ERD stands for Entity Relationship Diagram and it is a way that database designs are shown and created.
Learn more: A Guide to the ERD
Further Normal Forms (3.5 to 5)
There are more steps, or normal forms, that occur after Third Normal Form. They aren’t used very often but can be handy to know.
Learn more:
Data Warehouse
A data warehouse is a type of database that focuses on fast reading of data and is often used to generate reports.
Learn more: Ultimate Guide to Data Warehouses
Auto-Increment
Auto-increment is a feature that lets you automatically specify a value for a column to ensure it is unique.
Learn more: Auto-Increment in Different Databases
Intermediate SQL
Temporary Tables
A temporary table is an object that lets you store results for easier and faster processing of a larger query.
Learn more: SQL Temp Tables
Transactions
A transaction is a single unit of work that can be used to improve data integrity in your database.
Learn more: SQL Transactions
Using Functions
A function is a predefined set of code that can be used in your SQL statements.
Learn more:
- Oracle SQL Functions
- SQL Server Functions
- MySQL Functions
- PostgreSQL Functions
- Analytic Functions
- Aggregate Functions
Types of Commands
SQL statements fall into several different categories of commands, such as DML, DDL, and TCL.
Learn more: SQL Commands
Operators (IN, EXISTS)
There are several keywords that let you filter your data in other ways, such as IN and EXISTS.
Learn more: SQL Operators
Subqueries
A subquery is a query inside another query. They can help you solve problems that can’t otherwise be solved in a single query.
Learn more: SQL Subqueries
Bind Variables
A bind variable is a feature that lets you provide the values for a query when it is run, improving performance and security.
Learn more: Bind Variables in SQL
Limiting Rows
SQL allows you to limit the number of rows returned by your query, and this is done in several ways.
Learn more: SQL Limit: The Complete Guide to SQL Row Limiting and Top-N Queries
CTEs and With Clause
A Common Table Expression uses the With clause and can be used to simplify your queries.
Learn more: SQL CTE (WITH Clause)
Set Operators
A set operator, such as Union, allows you to combine two queries into one.
Learn more: SQL Set Operators
Execution Plans
Execution plans allow you to see how the database will run your query, and are a helpful step in improving the performance of your query.
Learn more: Ultimate Guide to Execution Plans
Comments on Objects
Databases allow you to add comments to different objects, which can help you and others understand more about them.
Learn more: SQL Comments
Importing Data
There are many ways to import data from external files (e.g. CSV, spreadsheets, log files) into a database.
Learn more:
JSON in SQL
JSON, pronounced like “jason”, stands for JavaScript Object Notation, and is a format for storing data. Recent versions of databases include features for storing JSON data and functions for working with it.
Learn More:
Advanced SQL
Pivot and Unpivot
Pivot and Unpivot are database features that let you transpose row data to columns, to analyse data in a different way. It’s common in Excel but can be done in SQL as well.
Learn More:
Regular Expressions
Regular expressions are a programming concept that let you search and match text files based on specific structures.
Learn More:
- Definition (Wikipedia)
- RegExr – tool to build regular expressions
- Oracle REGEXP Functions
- SQL Server Regular Expressions
- MySQL Regular Expressions
- PostgreSQL Regular Expressions
Isolation Levels
Isolation levels in databases allow you to define how and when data can be accessed in different situations.
Learn more: SQL Transactions and Isolation Levels
Continuous Integration and Delivery
Continuous Integration (CI) is the concept of regularly runnings tests on your code to ensure it still works. Continuous Delivery (CD) is the ability to always be able to deliver working software.
These techniques can be applied to databases as well.
Learn more: The Ultimate Guide to Database Version Control, CI/CD, and Deployment
Hierarchical Data in SQL
Hierarchical data is the ability to store multiple levels of data in a single table, such as employees and managers, or categories of products.
Learn more: Hierarchical Data in SQL: The Ultimate Guide
Analytic Functions
Analytic functions, or window functions, allow you to perform calculations on sets of rows within a result. They can be powerful and can simplify your queries.
Learn more: SQL Window Functions: The Ultimate Guide
Character Sets
A character set is a defined list of characters accepted by computers.
Learn more: Oracle Characters Sets
Merge Command
The Merge command lets you insert or update data in one query based on a condition.
Learn more:
External Tables
An external table is the ability to create a table linked to an external file (e.g. a CSV file).
Learn more:
Dynamic SQL
Dynamic SQL is a handy feature of a database that lets you construct SQL statements in a different way.
Learn more: Dynamic SQL: A Guide
SQL Injection
SQL Injection is a technique that allows users to run malicious commands on your database. Protecting against it is an important thing to do for developers.
Learn more: SQL Injection
Procedural Language
Oracle PL/SQL
PL/SQL stands for Procedural Language Structured Query Language, and is the language of the Oracle database that allows for procedural concepts (such as If statements and variables) to be used with SQL.
Learn More:
SQL Server T-SQL
T-SQL is SQL Server’s procedural programming language.
Learn More:
MySQL Stored Procedures
Stored procedures can be created in MySQL using their procedural language.
Learn More:
PostgreSQL pl/pgSQL
PL/pgSQL is the PostgreSQL version of a procedural language.
Learn More:
Database Objects
Views
Views are objects that store a saved SQL statement, and offer several benefits including query simplification, code reuse, and security.
Learn more: SQL Views
Oracle Synonyms
A synonym is an object that refers to another object. This is often done for security or to allow for easier database changes.
Learn more: Synonyms in Oracle
Database Administration
Privileges
Privileges are different activities or functions that can be made available to users. They are added or removed from users or roles.
Learn more:
Roles
A role is something that can be applied to a set of users that share the same characteristics. Privileges or permissions can be applied to the role, making it easier to work with.
Learn more:
User Management
Creating and removing users is another required task on a database.
Learn more:
- Creating users in Oracle
- Creating users in SQL Server
- Creating users in MySQL
- Creating users in PostgreSQL
Data Dictionary
The data dictionary is an area of the database that contains a range of tables and views. They can help you find out more information about the database.
Learn more:
- Oracle data dictionary
- SQL Server data dictionary
- MySQL Server data dictionary
- PostgreSQL system catalogs
Have I missed anything on this list? Let me know in the comments below.
Hi Ben, how are you?
First time here and I’m very happy to find a great SQL roadmap, thanks a lot.
Hi Diego, I’m well thanks! Glad you like the SQL roadmap!
Can I get a job with SQL?
yes.
I just stumpled across your website last week and let me tell you, this is by far the greatest tool I have ever seen on SQL as a whole! The layout of the website and the way you explain things with examples are just unmatched to anything I have personally seen! Thank you so much for this incredible resource of information and your work is treasure!! You’re helping a lot of people and we’re very grateful for this.
Sincerely,
Tom
Awesome, thanks Tom for the kind words! I’m glad you’ve found it useful and like it.
I sure which this site existed when I first started to work with SQL some 20+ yrs ago! My involvement/hands-on activity with SQL has diminished over that time and only recently has the need arising to re-engage. This site is excellent; explanations are clear and concise! So glad I stumbled across it!
Thanks for this!
Ed
hello bro,
can I get a job with only sql
It is amazing. I just go through the list but there are some missing topics in administration section like Backups, cloning, installition on different os, datagaurd etc so hopefully you will include them as well
I am very happy to start my SQL journey with your instructions. Thank you for informing us with these handy, clean explained informations. This type of summarized information is always useful on our learning journeys. Thank you for your efforts
Can you include some of the exotic stuff that most people do not use, with examples?
Thanks for explaining each topics. It is very useful for us and it will definitely help us to learn Database management…
hi ,ben. you have given very decent stuff of sql and its adavanced concepts in very easy format to learn.
I generally don’t comment on articles and tutorial ( I’m too lazy too :p ) but you deserve it. You’re doing an amazing work. I have struggled in sql and in normalization a lot and went through a lot of tutorials but none of them cleared my concepts. I loved your normalization tutorial and this roadmap too.
Keep up the great work!
Thank you for the quality work. You have made difficult concepts look pretty easy.
Thank you Ben.
you are a great man
My faith in humanity has been restored. Thank you is not enough. Would love to hang out with you wherever you live in the world.
This list is definitely a 5-star.
Looking forward to your web site and postgresql!