SQL Queries Every Developer Should Know

Why SQL Still Matters

Despite the popularity of Object Relational Mappers (ORMs), SQL remains one of the most important skills for developers. Understanding SQL allows you to troubleshoot performance issues, create efficient reports, validate application behaviour, and gain deeper insight into how your data is stored and accessed.

Most database platforms, including Microsoft SQL Server, MySQL, PostgreSQL, and SQLite, share a common SQL foundation, making these skills transferable across projects and technologies.

Retrieving Data with SELECT

The SELECT statement is the foundation of almost every SQL query. It allows you to retrieve information from one or more tables.

SELECT FirstName, LastName, Email
FROM Customers;

Although simple, it is good practice to avoid using SELECT * in production code unless you genuinely need every column. Retrieving only the data you require reduces network traffic and can improve performance.

Filtering Results with WHERE

Most applications rarely need every record in a table. The WHERE clause enables you to filter data based on specific conditions.

SELECT *
FROM Orders
WHERE OrderDate >= '2025-01-01';

You can combine conditions using AND and OR operators.

SELECT *
FROM Products
WHERE Category = 'Hardware'
AND StockQuantity > 0;

Effective filtering is one of the most important aspects of writing efficient queries.

Sorting Data with ORDER BY

Users generally expect data to appear in a meaningful order. The ORDER BY clause makes this possible.

SELECT ProductName, Price
FROM Products
ORDER BY Price DESC;

Multiple columns can be used for sorting.

SELECT LastName, FirstName
FROM Employees
ORDER BY LastName, FirstName;

Sorting should be used carefully on large datasets as it can impact query performance.

Limiting Results

When working with large tables, it is often useful to return only a subset of records.

In SQL Server:

SELECT TOP 10 *
FROM Orders
ORDER BY OrderDate DESC;

In MySQL and PostgreSQL:

SELECT *
FROM Orders
ORDER BY OrderDate DESC
LIMIT 10;

This is particularly useful when displaying recent activity or implementing pagination.

Joining Tables

Relational databases are built around relationships between tables. JOIN statements allow related data to be combined into a single result set.

SELECT
    o.OrderID,
    c.CustomerName,
    o.OrderDate
FROM Orders o
INNER JOIN Customers c
    ON o.CustomerID = c.CustomerID;

An INNER JOIN returns only matching records from both tables.

A LEFT JOIN returns all records from the first table, even if no matching record exists in the second table.

SELECT
    c.CustomerName,
    o.OrderID
FROM Customers c
LEFT JOIN Orders o
    ON c.CustomerID = o.CustomerID;

Understanding joins is essential for working with real-world databases.

Aggregating Data with COUNT, SUM and AVG

Aggregate functions help summarise large amounts of information.

To count records:

SELECT COUNT(*)
FROM Customers;

To calculate a total:

SELECT SUM(TotalAmount)
FROM Orders;

To find an average value:

SELECT AVG(Price)
FROM Products;

These functions are frequently used in dashboards, reports, and analytics systems.

Grouping Results with GROUP BY

GROUP BY allows aggregate calculations to be performed on subsets of data.

SELECT
    Category,
    COUNT(*) AS ProductCount
FROM Products
GROUP BY Category;

This query returns the number of products in each category.

You can combine GROUP BY with aggregate functions to create powerful reporting queries.

SELECT
    CustomerID,
    SUM(TotalAmount) AS TotalSpent
FROM Orders
GROUP BY CustomerID;

Filtering Groups with HAVING

The HAVING clause filters grouped results after aggregation has taken place.

SELECT
    CustomerID,
    SUM(TotalAmount) AS TotalSpent
FROM Orders
GROUP BY CustomerID
HAVING SUM(TotalAmount) > 1000;

This query returns only customers who have spent more than £1,000.

Developers often confuse WHERE and HAVING. WHERE filters rows before grouping, while HAVING filters groups after aggregation.

Finding Missing Data with NULL Checks

NULL values represent missing or unknown information. They require special handling.

SELECT *
FROM Customers
WHERE Email IS NULL;

Likewise, to find records where a value exists:

SELECT *
FROM Customers
WHERE Email IS NOT NULL;

Using standard equality operators with NULL will not produce the expected results.

Using EXISTS for Efficient Checks

When you only need to know whether related data exists, EXISTS can be more efficient than joins.

SELECT *
FROM Customers c
WHERE EXISTS
(
    SELECT 1
    FROM Orders o
    WHERE o.CustomerID = c.CustomerID
);

This query returns customers who have placed at least one order.

EXISTS is particularly useful when dealing with large datasets.

Updating Records Safely

Updating data is a common task, but it should always be performed carefully.

UPDATE Products
SET Price = Price * 1.10
WHERE Category = 'Hardware';

Always verify your WHERE clause before running an UPDATE statement. Omitting it can update every record in the table.

Many developers first execute the equivalent SELECT query to confirm the records that will be affected.

Deleting Records Responsibly

DELETE statements should be used with similar caution.

DELETE FROM Orders
WHERE OrderDate < '2020-01-01';

Without a WHERE clause, every row in the table will be removed.

For critical systems, it is often wise to perform a backup before executing large delete operations.

Using Common Table Expressions

Common Table Expressions (CTEs) make complex queries easier to read and maintain.

WITH CustomerTotals AS
(
    SELECT
        CustomerID,
        SUM(TotalAmount) AS TotalSpent
    FROM Orders
    GROUP BY CustomerID
)
SELECT *
FROM CustomerTotals
WHERE TotalSpent > 5000;

CTEs improve query readability and are especially useful when working with complex reporting requirements.

Window Functions for Advanced Analysis

Window functions provide powerful analytical capabilities without collapsing rows.

SELECT
    EmployeeID,
    Salary,
    RANK() OVER (ORDER BY Salary DESC) AS SalaryRank
FROM Employees;

This query ranks employees by salary while still displaying every individual record.

Window functions are increasingly important for reporting and business intelligence applications.

Conclusion

While modern frameworks and ORMs abstract much of the database layer, SQL remains an essential skill for every developer. Understanding how to retrieve, filter, join, aggregate, and analyse data directly within the database can lead to better performance, easier troubleshooting, and more maintainable applications. Mastering these core queries will provide a strong foundation that can be applied across virtually any relational database platform.