SQL roadmap

Relational databases store information in tables made up of rows and columns, much like a spreadsheet. Every row represents a single record, like a customer, while columns hold specific details about them, such as names or emails.

Keys are unique identifiers that link these tables together safely. For instance, a customer table connects to an orders table through a shared customer ID number, keeping your records tidy and preventing messy duplicates.

Understanding this structure matters because it forms the foundation of all SQL queries. Knowing how rows and columns relate helps you find the exact information you need without getting lost in the data.

• Tables hold data in rows and columns
• Columns define the type of information stored
• Rows represent individual items or records
• Primary keys uniquely identify each row

The SELECT statement is your main tool for asking questions of a database. It lets you pick precisely which columns you want to view, saving you from wading through clutter.

Adding a WHERE clause filters your results so you only see rows matching your specific rules. If you run a coffee shop, you might use it to list just the drinks priced under three pounds.

This command is essential because most database tasks begin with retrieving a specific subset of data. Mastering SELECT and WHERE lets you quickly find answers in daily work.

• SELECT chooses which columns to display
• FROM specifies the table to search
• WHERE filters rows based on set conditions
• Combine rules using basic comparison symbols

Once you have your data, you often want it presented in a logical order. The ORDER BY clause lets you sort results alphabetically, chronologically, or numerically in either ascending or descending direction.

Limiting results keeps your screens readable and your queries fast. If you only want the top ten biggest sales, a LIMIT clause chops off the rest. The DISTINCT keyword removes duplicate entries so you see unique values only.

These tools matter because raw data is rarely neat. Sorting and limiting help turn a chaotic list of search results into a clear, useful report.

• ORDER BY sorts results up or down
• LIMIT restricts the number of returned rows
• DISTINCT hides duplicate values in outputs
• Sorting helps present data clearly

In databases, missing information is stored as a special marker called NULL, which is different from zero or an empty space. Handling it correctly prevents unexpected blank spots in your final reports.

Combining conditions with AND and OR lets you build precise filters, while CASE expressions let you create custom labels on the fly, like grouping ages into bands.

This matters because real-world data is messy and incomplete. Knowing how to manage missing values and complex logic ensures your reports remain accurate and trustworthy.

• NULL represents missing or unknown values
• Use IS NULL to find missing entries
• AND and OR combine multiple conditions
• CASE creates conditional values in results

Aggregate functions let you calculate totals, averages, and counts across multiple rows rather than looking at items individually. Grouping these results by categories gives you quick summaries, like total sales per month.

The HAVING clause filters these grouped results afterwards, which is useful if you only want to see categories with more than ten sales.

This skill matters because stakeholders rarely want raw rows; they want high-level summaries to spot trends, track performance, and make informed business decisions.

• SUM, AVG, and COUNT calculate group metrics
• GROUP BY organises rows into categories
• HAVING filters groups after aggregation
• Aggregates turn raw data into insights

Joins let you combine data from two or more related tables into a single output. An inner join only returns matching records, while left joins keep everything from the first table even if there is no match.

Imagine combining a customer table with an orders table so you can see who bought what. The join matches the customer ID in both places.

Joins are vital because relational databases split information across multiple tables to stay organised. You need joins to rebuild that information into a complete picture.

• Inner joins return only matching records
• Left joins keep all rows from the left table
• Joins rely on shared key columns
• Combining tables provides complete context

A subquery is a query nested inside another query, helping you solve multi-step questions in one go. Common Table Expressions, or CTEs, make this cleaner by letting you name a subquery using the WITH keyword beforehand.

Think of finding products priced above the overall company average. You calculate the average in one step, then use that result to filter your main product list.

This matters because complex data questions can get messy quickly. Subqueries and CTEs keep your code readable, maintainable, and easy for colleagues to review.

• Subqueries nest one query inside another
• CTEs use WITH to name temporary result sets
• They break complex problems into steps
• Improves readability of long queries

Window functions perform calculations across a set of table rows that are somehow related to the current row. Unlike group functions, they do not collapse your rows; every original record remains visible.

They let you calculate running totals, rank items within categories, or compare a row against the previous one, such as tracking daily temperature changes.

This matters for advanced reporting because it allows you to see both individual details and contextual comparisons at the exact same time without complex workarounds.

• Window functions keep all original rows
• OVER defines the window of related rows
• Useful for running totals and rankings
• Compares rows without collapsing data

Data Manipulation Language commands let you add, change, and remove information inside your tables using INSERT, UPDATE, and DELETE. Transactions wrap these changes together so you can safely undo mistakes if something goes wrong.

If you update prices across a menu, a transaction ensures that if the power cuts halfway through, the database rolls back to the safe original state rather than leaving corrupted data.

This matters because managing data safely protects your business from accidental loss and keeps your records accurate and reliable over time.

• INSERT adds new rows of data
• UPDATE modifies existing records carefully
• DELETE removes unwanted rows safely
• Transactions let you undo mistakes

Data Definition Language commands let you build and alter the structure of your database itself. You use CREATE TABLE to set up new tables, choosing data types like text, numbers, or dates for each column.

Constraints like primary keys and foreign keys act as rules, stopping invalid entries like text in a date column or orders assigned to non-existent customers.

This matters because a well-designed database structure prevents data errors right from the start, making future queries faster and much more reliable.

• CREATE TABLE builds new database structures
• Data types define what kind of data fits
• Constraints enforce rules on columns
• Foreign keys maintain table relationships

Normalisation is the process of organising database tables to reduce duplicate data and prevent update anomalies. The goal is to ensure each distinct fact is stored in just one logical place.

If a customer changes their phone number, normalisation ensures you only need to update it in the customer table rather than hunting through past orders.

This matters because clean database design saves storage space, prevents conflicting information, and keeps your applications running smoothly as your organisation grows.

• Organises tables to remove duplication
• Stores each fact in one place
• Prevents update and delete errors
• Improves overall database efficiency

Indexes are special lookup structures that help the database find rows much faster, much like an index at the back of a textbook. Without them, the database must scan every single row to find a match.

While indexes speed up searches, adding too many can slow down inserts and updates because the database has to update the indexes every time.

This matters because slow queries frustrate users and bog down systems. Knowing when to add an index keeps your applications snappy and efficient.

• Indexes speed up data retrieval
• Works like a book index for fast lookups
• Too many indexes slow down writes
• Essential for large datasets and performance