Database and SQL Roadmap: Everything You Need to Know About SQL

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:

• BCNF (Boyce-Codd Normal Form), or 3.5 NF
• Fourth Normal Form
• Fifth Normal Form

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:

• MySQL Workbench Import CSV
• SSMS Import
• SQL Developer Import

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:

• JSON in Oracle
• JSON in SQL Server
• JSON in MySQL
• JSON in PostgreSQL

 

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:

• Oracle PIVOT and UNPIVOT
• SQL Server Pivot
• MySQL Pivot
• PostgreSQL Pivot
• Modern SQL Pivot article

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:

• Oracle Merge
• SQL Server Merge
• MySQL Insert On Duplicate Key
• PostgreSQL Upsert

External Tables

An external table is the ability to create a table linked to an external file (e.g. a CSV file).

Learn more:

• Oracle external tables
• SQL Server external tables
• MySQL external tables
• PostgreSQL foreign tables

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:

• PL/SQL Tutorial
• A Beginner’s Guide to Cursors
• Oracle Triggers
• Stored Procedures

SQL Server T-SQL

T-SQL is SQL Server’s procedural programming language.

Learn More:

• T-SQL Tutorial and Stored Procedures
• A Beginner’s Guide to Cursors
• Stored Procedures

MySQL Stored Procedures

Stored procedures can be created in MySQL using their procedural language.

Learn More:

• A Beginner’s Guide to Cursors
• Stored Procedures

PostgreSQL pl/pgSQL

PL/pgSQL is the PostgreSQL version of a procedural language.

Learn More:

• pgSQL Tutorial
• A Beginner’s Guide to Cursors
• Stored Procedures

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:

• Privileges in Oracle
• Permissions in SQL Server
• Privileges in MySQL
• Privileges in PostgreSQL

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:

• Roles in Oracle
• Roles in SQL Server
• Roles in MySQL
• Roles in PostgreSQL

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.