How to Write a SQL Query: Tutorial with Examples

How to Write a SQL Query: A Practical Tutorial with Examples

How to Write a SQL Query: A Practical Tutorial with Examples

Structured Query Language (SQL) is the universal method for communicating with relational databases, yet writing an effective query is more than just stringing together a few keywords. It's a process that combines logic, a clear understanding of your data, and an appreciation for how databases function. This tutorial will equip you with the knowledge to write SQL queries that are not only correct but also efficient, readable, and maintainable, regardless of your database system.

What You'll Learn

By the end of this guide, you'll be able to construct SQL queries from the ground up, filtering, sorting, and aggregating data with precision. You'll understand the importance of the query execution order and walk away with a clear plan for writing complex SQL statements that are a professional asset. Writing a SQL query is a structured process where clauses are written in a specific order that differs from how the database engine executes them.

The Foundation: The SELECT Statement

At the heart of data retrieval is the SELECT statement. It tells the database exactly what information you want to see and where to find it. The most basic form of a query consists of SELECT and FROM clauses .

SELECT column1, column2
FROM table_name;

For instance, to retrieve employee keys and last names from a dimension table, you would use:

SELECT EmployeeKey, LastName
FROM DimEmployee;

While SELECT * might be tempting for quickly viewing all columns, it is widely considered bad practice in production code because it can return unnecessary data, impact performance, and break applications if the table schema changes . Always specify the columns you need.

Building Logic with the WHERE Clause

To filter data and retrieve only relevant rows, you use the WHERE clause. It specifies the search condition that rows must meet to be included in the results . The WHERE clause comes after the FROM clause in your query's structure.

You can use various operators to build powerful search conditions. For example, to find a specific employee named "Smith," you can use a simple equality comparison :

SELECT EmployeeKey, LastName
FROM DimEmployee
WHERE LastName = 'Smith';

For more complex filters, you can use the LIKE operator for pattern matching. The following query finds all employees whose last name contains the pattern "Smi" :

SELECT EmployeeKey, LastName
FROM DimEmployee
WHERE LastName LIKE '%Smi%';

Logical operators like AND and OR allow you to combine multiple conditions. You can use AND to ensure all conditions must be met and OR when any condition can be true . For instance, to find rows that meet several criteria, you might write:

SELECT EmployeeKey, LastName
FROM DimEmployee
WHERE EmployeeKey <= 500
      AND LastName LIKE '%Smi%';

It's important to note that the WHERE clause filters individual rows and cannot filter aggregate results. The IN operator is a concise way to check if a value matches any in a list, and BETWEEN is used to find values within a range .

-- Using IN
SELECT EmployeeKey, LastName
FROM DimEmployee
WHERE LastName IN ('Smith', 'Godfrey', 'Johnson');

-- Using BETWEEN
SELECT EmployeeKey, LastName
FROM DimEmployee
WHERE EmployeeKey BETWEEN 100 AND 200;

Based on the Microsoft documentation, there is no limit to the number of predicates you can include in a search condition, allowing for highly specific and complex filtering logic .

Shaping and Organizing Your Results

After filtering your data, you often want to organize it for better readability or perform calculations. The ORDER BY clause sorts the result set based on one or more columns. It follows the SELECT statement and can specify ascending (ASC, the default) or descending (DESC) order . To remove duplicate rows from your result set, you use the DISTINCT keyword right after SELECT .

SELECT DISTINCT JobTitle
FROM HumanResources.Employee
ORDER BY JobTitle;

The GROUP BY clause is a powerful tool for data aggregation. It groups rows that have the same values in specified columns, allowing you to perform summary operations like SUM, AVG, COUNT, MAX, and MIN . For example, to find the total of each sales order, you could group by SalesOrderID and sum the line totals :

SELECT SalesOrderID,
    SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
ORDER BY SalesOrderID;

When you want to filter the results of a GROUP BY, you must use the HAVING clause. Unlike the WHERE clause, which filters individual rows before grouping, HAVING filters groups after the aggregation has occurred .

SELECT ProductID
FROM Sales.SalesOrderDetail
GROUP BY ProductID
HAVING AVG(OrderQty) > 5;

The order in which you write these clauses is crucial. A standard SQL query follows this structure: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY .

Advanced Techniques: Joins and Subqueries

Data is often stored across multiple tables, and you bring it together using JOIN clauses. An INNER JOIN retrieves records from two tables where the join condition is met . For instance, you can combine the Product table with SalesOrderDetail to get product information alongside sales data .

Subqueries, or nested queries, are queries within another query. They are useful for data manipulation and analysis that depends on the results of another operation. A correlated subquery is a particularly complex type where the inner query depends on the outer query for its values, executing repeatedly for each row processed . The EXISTS keyword is often used with correlated subqueries to test for the existence of rows in a subquery .

Writing Readable SQL

Writing SQL that works is only half the battle; writing SQL that is maintainable is a professional necessity . Follow these best practices to ensure your queries are a tool for collaboration, not a source of confusion:

• Capitalize Keywords: Use uppercase for SQL keywords like SELECT, FROM, WHERE, and JOIN. This separates them from column and table names, making the query easier to scan .
• Use Line Breaks and Indentation: Put each major clause on a new line and use indentation to show subquery or join structure. This visual hierarchy clarifies complex logic .
• Add Meaningful Comments: Use -- to add comments that explain the why behind your query, not the what. This is invaluable for your future self and your colleagues .
• Use Descriptive Aliases: When renaming columns with AS or shortening table names, choose aliases that are meaningful. Avoid vague names like t1 or a .

Sources

1. WHERE (Transact-SQL) - SQL Server
2. T-SQL Tutorial: Write Transact-SQL Statements
3. SELECT Examples (Transact-SQL)
4. PostgreSQL: Documentation: 17: 2.5. Querying a Table
5. Data Management with SQL for Ecologists: Accessing Data With Queries
6. EXISTS (Transact-SQL) - SQL Server
7. Transact-SQL Syntax Conventions (Transact-SQL)
8. How to Write SQL Queries Anyone Can Understand

— Editorial Team