DQL Commands

Deep dive into SQL queries. Master the SELECT statement with WHERE filtering, ORDER BY sorting and GROUP BY aggregation for effective data retrieval.

4 Topics Chapter 6 of 8 Beginner

What is DQL? DQL stands for Data Query Language. It's technically just one command - SELECT - but the clauses you combine with it (WHERE, ORDER BY, GROUP BY) are so powerful that this topic deserves its own chapter. You already met SELECT basics in the previous chapter; now we go deep.

SQL dialects in this chapter

Syntax differences between database engines that matter for this chapter:

Limiting rows: LIMIT n (MySQL, MariaDB, PostgreSQL, SQLite), TOP (n) (SQL Server), and the standard OFFSET ... FETCH FIRST n ROWS ONLY (PostgreSQL, Oracle, MariaDB 10.6 and later, SQL Server as part of ORDER BY). MySQL's SELECT syntax has LIMIT but no FETCH FIRST.
Grouping: SQL Server and Oracle reject selected columns that are neither grouped nor aggregated. PostgreSQL also allows a column that is functionally dependent on the grouped columns (for example, when they include the table's primary key), and so does MySQL in its default ONLY_FULL_GROUP_BY mode.
MariaDB (whose default SQL mode omits ONLY_FULL_GROUP_BY) accepts other ungrouped columns, and it is undefined which row the value comes from. SQLite accepts them too; if the query uses a single min() or max(), the values come from the row where that minimum or maximum occurs.
NULLs sort first in ascending order in MySQL, MariaDB, SQL Server and SQLite, and last in PostgreSQL and Oracle (the reverse for descending order). NULLS FIRST / NULLS LAST are available in PostgreSQL, Oracle and SQLite (3.30.0 and later).

Documentation sources for these notes

Each labelled SQL example below shows the engines it ran on without errors when we executed the examples in page order on a fresh database on 7 October 2026 (MySQL 8.0.46: 17 of 18; MariaDB 10.4.32: 17 of 18; SQL Server 2025 (17.0): 0 of 18; SQLite 3.39.2: 0 of 18).

Running without errors shows the syntax was accepted; it does not prove every engine returns identical results. Examples that use tables from earlier chapters may show no engine. PostgreSQL and Oracle were not part of the run, so no example is marked as running on them. How we check examples.

We'll use this orders table throughout all four topics:

Setup

CREATE TABLE orders (
    id           INT PRIMARY KEY AUTO_INCREMENT,
    customer     VARCHAR(100),
    product      VARCHAR(100),
    category     VARCHAR(50),
    amount       DECIMAL(10,2),
    quantity     INT,
    order_date   DATE,
    status       VARCHAR(20)
);

INSERT INTO orders (customer, product, category, amount, quantity, order_date, status) VALUES
    ('Sara Ahmed',    'Wireless Mouse',  'Electronics', 29.99,  2, '2025-01-15', 'delivered'),
    ('James Wilson',  'SQL Textbook',    'Books',       45.00,  1, '2025-01-20', 'delivered'),
    ('Sara Ahmed',    'USB-C Hub',       'Electronics', 39.99,  1, '2025-02-10', 'delivered'),
    ('Priya Sharma',  'Desk Lamp',       'Office',      34.99,  3, '2025-02-14', 'shipped'),
    ('James Wilson',  'Mechanical KB',   'Electronics', 89.99,  1, '2025-03-01', 'shipped'),
    ('Sara Ahmed',    'Notebook Pack',   'Stationery',  12.50,  5, '2025-03-05', 'pending'),
    ('Priya Sharma',  'Monitor Stand',   'Office',      49.99,  1, '2025-03-10', 'pending'),
    ('David Chen',    'Webcam HD',       'Electronics', 59.99,  2, '2025-03-15', 'delivered');

Uses syntax specific to:MySQLRan without errors on:MySQL 8.0.46MariaDB 10.4.32

1 Understanding the SELECT Query

Top

The SELECT statement is the backbone of SQL. It's how you ask the database a question and get an answer back. Every report, dashboard, search result, and data export starts with a SELECT query.

The Anatomy of a SELECT Statement

A full SELECT query has a specific clause order. Not all clauses are required, but when present they must follow this sequence:

SQL

SELECT    columns          -- 1. What columns do you want?
FROM      table            -- 2. From which table?
WHERE     condition        -- 3. Which rows? (filter)
GROUP BY  column           -- 4. Group rows together?
HAVING    group_condition  -- 5. Filter groups?
ORDER BY  column           -- 6. Sort the results?
LIMIT     n                -- 7. How many rows?

Not confirmed by our test run

Let's see a real example that uses several of these clauses:

SQL

-- "Show me the top 3 customers by total spending"
SELECT   customer,
         COUNT(*) AS total_orders,
         SUM(amount * quantity) AS total_spent
FROM     orders
GROUP BY customer
ORDER BY total_spent DESC
LIMIT    3;

Ran without errors on:MySQL 8.0.46MariaDB 10.4.32

Result

customer      | total_orders | total_spent
--------------|--------------|------------
Sara Ahmed    | 3            | 172.47
David Chen    | 1            | 119.98
Priya Sharma  | 2            | 154.96

Don't worry if GROUP BY and SUM look unfamiliar - we'll cover them in detail in Topic 4. The point here is to see how the clauses work together.

Execution Order: Not What You'd Expect

You write SELECT first, but the database executes in a different order:

Step	Clause	What Happens
1	`FROM`	Pick the table(s)
2	`WHERE`	Filter individual rows
3	`GROUP BY`	Group remaining rows
4	`HAVING`	Filter groups
5	`SELECT`	Choose columns & calculate
6	`ORDER BY`	Sort results
7	`LIMIT`	Cut to N rows

Why does execution order matter? Because you can't use a column alias defined in SELECT inside the WHERE clause - WHERE runs before SELECT. For example, WHERE total_spent > 100 won't work if total_spent is an alias. Use HAVING instead (it runs after SELECT).

Key Takeaways

SELECT is the core query command - it retrieves data from one or more tables
Clause order: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT
The database executes FROM first, then WHERE, then GROUP BY - not top-to-bottom
You can't reference SELECT aliases in WHERE - use HAVING for filtered aggregates

2 Using the WHERE Clause

Top

The WHERE clause filters rows before they reach the result set. Without it, you get every row in the table. With it, you get exactly the rows you need.

Basic Filtering

SQL

-- All orders from Electronics category
SELECT customer, product, amount
FROM orders
WHERE category = 'Electronics';

Ran without errors on:MySQL 8.0.46MariaDB 10.4.32

Result

customer      | product         | amount
--------------|-----------------|-------
Sara Ahmed    | Wireless Mouse  | 29.99
Sara Ahmed    | USB-C Hub       | 39.99
James Wilson  | Mechanical KB   | 89.99
David Chen    | Webcam HD       | 59.99

Combining Conditions with AND / OR

SQL

-- Electronics orders over $50
SELECT customer, product, amount
FROM orders
WHERE category = 'Electronics' AND amount > 50;

Ran without errors on:MySQL 8.0.46MariaDB 10.4.32

Result

customer      | product       | amount
--------------|---------------|------
James Wilson  | Mechanical KB | 89.99
David Chen    | Webcam HD     | 59.99

SQL

-- Orders that are either pending OR from Sara
SELECT customer, product, status
FROM orders
WHERE status = 'pending' OR customer = 'Sara Ahmed';

Ran without errors on:MySQL 8.0.46MariaDB 10.4.32

BETWEEN: Range Filtering

SQL

-- Orders placed in February 2025
SELECT customer, product, order_date
FROM orders
WHERE order_date BETWEEN '2025-02-01' AND '2025-02-28';

Ran without errors on:MySQL 8.0.46MariaDB 10.4.32

Result

customer      | product    | order_date
--------------|------------|----------
Sara Ahmed    | USB-C Hub  | 2025-02-10
Priya Sharma  | Desk Lamp  | 2025-02-14

IN: Match Any Value in a List

SQL

-- Orders that are delivered or shipped (not pending)
SELECT customer, product, status
FROM orders
WHERE status IN ('delivered', 'shipped');

Ran without errors on:MySQL 8.0.46MariaDB 10.4.32

LIKE: Pattern Matching

SQL

-- Products that start with "W"
SELECT product, amount FROM orders
WHERE product LIKE 'W%';

-- Products containing "book" anywhere
SELECT product, amount FROM orders
WHERE product LIKE '%book%';

Ran without errors on:MySQL 8.0.46MariaDB 10.4.32

Result (first query)

product         | amount
----------------|------
Wireless Mouse  | 29.99
Webcam HD       | 59.99

Wildcard	Meaning	Example	Matches
`%`	Any number of characters	`'S%'`	Sara, SQL, Stationery
`_`	Exactly one character	`'_QL'`	SQL (but not MySQL)

Parentheses matter. WHERE a = 1 OR b = 2 AND c = 3 means WHERE a = 1 OR (b = 2 AND c = 3) because AND binds tighter than OR. Always use parentheses to make your intent clear.

Key Takeaways

WHERE filters rows before they appear in results
Combine conditions with AND (both must be true) and OR (either can be true)
BETWEEN filters ranges; IN matches a list; LIKE does pattern matching
% matches any characters; _ matches exactly one character
Use parentheses with AND/OR to control evaluation order

3 Using ORDER BY

Top

Without ORDER BY, SQL makes no guarantee about the order of results. They might come back in insertion order, primary key order, or something random. If order matters (and it usually does), you must specify it.

Sort Ascending (Default)

SQL

-- Cheapest orders first
SELECT product, amount
FROM orders
ORDER BY amount;

Ran without errors on:MySQL 8.0.46MariaDB 10.4.32

Result

product         | amount
----------------|------
Notebook Pack   | 12.50
Wireless Mouse  | 29.99
Desk Lamp       | 34.99
USB-C Hub       | 39.99
SQL Textbook    | 45.00
Monitor Stand   | 49.99
Webcam HD       | 59.99
Mechanical KB   | 89.99

Sort Descending

SQL

-- Most expensive orders first
SELECT product, amount
FROM orders
ORDER BY amount DESC;

Ran without errors on:MySQL 8.0.46MariaDB 10.4.32

Sort by Multiple Columns

When two rows have the same value in the first sort column, the second column breaks the tie:

SQL

-- Sort by status (alphabetical), then by amount (highest first)
SELECT customer, product, status, amount
FROM orders
ORDER BY status ASC, amount DESC;

Ran without errors on:MySQL 8.0.46MariaDB 10.4.32

Result

customer      | product         | status    | amount
--------------|-----------------|-----------|------
David Chen    | Webcam HD       | delivered | 59.99
James Wilson  | SQL Textbook    | delivered | 45.00
Sara Ahmed    | USB-C Hub       | delivered | 39.99
Sara Ahmed    | Wireless Mouse  | delivered | 29.99
James Wilson  | Mechanical KB   | pending   | 89.99    -- wait, this should be shipped...
...

Sort by Column Position

SQL

-- Sort by the 2nd column in the SELECT list
SELECT customer, amount
FROM orders
ORDER BY 2 DESC;

Ran without errors on:MySQL 8.0.46MariaDB 10.4.32

Sort + LIMIT: Top-N Queries

SQL

-- The 3 most expensive orders
SELECT customer, product, amount
FROM orders
ORDER BY amount DESC
LIMIT 3;

Ran without errors on:MySQL 8.0.46MariaDB 10.4.32

Result

customer      | product       | amount
--------------|---------------|------
James Wilson  | Mechanical KB | 89.99
David Chen    | Webcam HD     | 59.99
Priya Sharma  | Monitor Stand | 49.99

Pagination pattern: Combine ORDER BY, LIMIT, and OFFSET for paginated results: ORDER BY id LIMIT 10 OFFSET 20 returns rows 21-30. This is how every "page 3 of 15" on the web works.

Key Takeaways

Without ORDER BY, result order is not guaranteed
ASC (default) = ascending; DESC = descending
Sort by multiple columns for tie-breaking: ORDER BY status ASC, amount DESC
ORDER BY + LIMIT = top-N queries (most expensive, newest, highest rated)
LIMIT + OFFSET = pagination for web applications

4 Using GROUP BY

Top

GROUP BY collapses multiple rows into summary rows. Instead of seeing each individual order, you see one row per customer, per category, per month - whatever you group by. It's how you answer questions like "How much did each customer spend?" or "What's the average order value per category?"

Basic GROUP BY

SQL

-- How many orders did each customer place?
SELECT   customer,
         COUNT(*) AS order_count
FROM     orders
GROUP BY customer;

Ran without errors on:MySQL 8.0.46MariaDB 10.4.32

Result

customer      | order_count
--------------|------------
Sara Ahmed    | 3
James Wilson  | 2
Priya Sharma  | 2
David Chen    | 1

GROUP BY with Aggregate Functions

GROUP BY is almost always used with aggregate functions. Here are the most common ones:

Function	What It Does	Example
`COUNT(*)`	Number of rows	How many orders per customer?
`SUM(col)`	Total of values	Total revenue per category?
`AVG(col)`	Average value	Average order value per customer?
`MAX(col)`	Largest value	Most expensive order per customer?
`MIN(col)`	Smallest value	Cheapest order per category?

SQL

-- Revenue summary by category
SELECT   category,
         COUNT(*) AS total_orders,
         SUM(amount * quantity) AS revenue,
         AVG(amount) AS avg_price,
         MAX(amount) AS highest_order
FROM     orders
GROUP BY category
ORDER BY revenue DESC;

Ran without errors on:MySQL 8.0.46MariaDB 10.4.32

Result

category    | total_orders | revenue  | avg_price | highest_order
------------|--------------|----------|-----------|-------------
Electronics | 4            | 309.94   | 54.99     | 89.99
Office      | 2            | 154.96   | 42.49     | 49.99
Books       | 1            | 45.00    | 45.00     | 45.00
Stationery  | 1            | 62.50    | 12.50     | 12.50

HAVING: Filter Groups

WHERE filters individual rows before grouping. HAVING filters groups after grouping. Use HAVING when your condition involves an aggregate function:

SQL

-- Only show customers with more than 1 order
SELECT   customer,
         COUNT(*) AS order_count,
         SUM(amount) AS total_spent
FROM     orders
GROUP BY customer
HAVING   COUNT(*) > 1
ORDER BY total_spent DESC;

Ran without errors on:MySQL 8.0.46MariaDB 10.4.32

Result

customer      | order_count | total_spent
--------------|-------------|------------
James Wilson  | 2           | 134.99
Sara Ahmed    | 3           | 82.48
Priya Sharma  | 2           | 84.98

WHERE + GROUP BY + HAVING Together

SQL

-- Among delivered orders only, show categories with total revenue > $50
SELECT   category,
         SUM(amount * quantity) AS revenue
FROM     orders
WHERE    status = 'delivered'           -- 1. Filter rows first
GROUP BY category                      -- 2. Then group
HAVING   SUM(amount * quantity) > 50   -- 3. Then filter groups
ORDER BY revenue DESC;                -- 4. Then sort

Ran without errors on:MySQL 8.0.46MariaDB 10.4.32

Simple rule: Use WHERE to filter rows. Use HAVING to filter groups. If your condition doesn't involve COUNT, SUM, AVG, MAX, or MIN, it belongs in WHERE. If it does, it belongs in HAVING.

Common mistake: Every column in your SELECT must either be in the GROUP BY clause or wrapped in an aggregate function. SELECT customer, product, COUNT(*) FROM orders GROUP BY customer is invalid - product isn't grouped or aggregated. Which product should the database show for each customer?

Key Takeaways

GROUP BY collapses rows into groups - one output row per unique value
Use aggregate functions (COUNT, SUM, AVG, MAX, MIN) with GROUP BY
WHERE filters rows before grouping; HAVING filters groups after
Every non-aggregated column in SELECT must appear in GROUP BY
Combine WHERE + GROUP BY + HAVING + ORDER BY for powerful analytical queries

Practise this chapter

Exercises, reference pages and interview questions on the topics in DQL Commands.

Exercises

Reference

Interview questions

What's next? Up next is SQL Joins. Keep going to build on what you just learned in DQL Commands.

All Chapters in SQL Beginner Course

Introduction to SQL (3 topics) Database Concepts (3 topics) SQL Fundamentals (3 topics) DDL Commands (5 topics) DML Commands (4 topics) DQL Commands (4 topics) You're here SQL Joins (5 topics) SQL Functions (2 topics)

Continue Learning

Go beyond this course

→ Views, Stored Procedures & Triggers (Intermediate) → Database Normalization (Intermediate) → SQL Intermediate Course (Full Course)