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.
What is a database?
The first step is to understand what a database is: a tool that’s used to store and manage data.
What is SQL?
SQL stands for Structured Query Language and is the language used to interact with databases.
Basic Select Query
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
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 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
The HAVING clause allows you to filter data after a group has been applied.
A join is where you combine data from two or more tables into one query.
Learn more: SQL Joins
The Update statement allows you to make changes to data in a table.
Learn more: SQL Update Statement
The Delete statement allows you to remove records from a table.
Learn more: SQL Delete Statement
The Insert statement allows you to add new records to a table.
The Truncate Table statement lets you remove all data from a table.
Learn more: SQL Truncate Table
Working with Tables
Create new tables in your database with the Create Table statement.
Make changes to your table, such as adding or removing columns, with the Alter Table statement.
Remove a table from your database with the Drop Table command.
Learn more: SQL Drop Table
There are many data types available in each database, each of which have their pros and cons.
Improving Your Queries
Indexes are objects that help the performance of your queries.
Learn more: SQL Indexes
A primary key is a way of uniquely identifying a record in a table.
Learn more: Database Keys
A foreign key is how a record in one table is related to a record in another table.
Learn more: Database Keys
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
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
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.
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 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
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
A transaction is a single unit of work that can be used to improve data integrity in your database.
Learn more: SQL Transactions
A function is a predefined set of code that can be used in your SQL statements.
- 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
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
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
SQL allows you to limit the number of rows returned by your query, and this is done in several ways.
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)
A set operator, such as Union, allows you to combine two queries into one.
Learn more: SQL Set Operators
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
There are many ways to import data from external files (e.g. CSV, spreadsheets, log files) into a database.
JSON in 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.
Regular expressions are a programming concept that let you search and match text files based on specific structures.
- Definition (Wikipedia)
- RegExr – tool to build regular expressions
- Oracle REGEXP Functions
- SQL Server Regular Expressions
- MySQL Regular Expressions
- PostgreSQL Regular Expressions
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.
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, 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
A character set is a defined list of characters accepted by computers.
Learn more: Oracle Characters Sets
The Merge command lets you insert or update data in one query based on a condition.
An external table is the ability to create a table linked to an external file (e.g. a CSV file).
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 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
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.
SQL Server T-SQL
T-SQL is SQL Server’s procedural programming language.
MySQL Stored Procedures
Stored procedures can be created in MySQL using their procedural language.
PL/pgSQL is the PostgreSQL version of a procedural language.
Views are objects that store a saved SQL statement, and offer several benefits including query simplification, code reuse, and security.
Learn more: SQL Views
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
Privileges are different activities or functions that can be made available to users. They are added or removed from users or 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.
Creating and removing users is another required task on a database.
- Creating users in Oracle
- Creating users in SQL Server
- Creating users in MySQL
- Creating users in PostgreSQL
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.
- 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.