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.
We'll use this orders table throughout all four topics:
SetupCREATE 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');
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:
SQLSELECT columns
FROM table
WHERE condition
GROUP BY column
HAVING group_condition
ORDER BY column
LIMIT n
Let's see a real example that uses several of these clauses:
SQL
SELECT customer,
COUNT(*) AS total_orders,
SUM(amount * quantity) AS total_spent
FROM orders
GROUP BY customer
ORDER BY total_spent DESC
LIMIT 3;
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
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
SELECT customer, product, amount
FROM orders
WHERE category = 'Electronics';
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
SELECT customer, product, amount
FROM orders
WHERE category = 'Electronics' AND amount > 50;
Result
customer | product | amount
--------------|---------------|------
James Wilson | Mechanical KB | 89.99
David Chen | Webcam HD | 59.99
SQL
SELECT customer, product, status
FROM orders
WHERE status = 'pending' OR customer = 'Sara Ahmed';
BETWEEN: Range Filtering
SQL
SELECT customer, product, order_date
FROM orders
WHERE order_date BETWEEN '2025-02-01' AND '2025-02-28';
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
SELECT customer, product, status
FROM orders
WHERE status IN ('delivered', 'shipped');
LIKE: Pattern Matching
SQL
SELECT product, amount FROM orders
WHERE product LIKE 'W%';
SELECT product, amount FROM orders
WHERE product LIKE '%book%';
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
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
SELECT product, amount
FROM orders
ORDER BY amount;
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
SELECT product, amount
FROM orders
ORDER BY amount DESC;
Sort by Multiple Columns
When two rows have the same value in the first sort column, the second column breaks the tie:
SQL
SELECT customer, product, status, amount
FROM orders
ORDER BY status ASC, amount DESC;
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
SELECT customer, amount
FROM orders
ORDER BY 2 DESC;
Sort + LIMIT: Top-N Queries
SQL
SELECT customer, product, amount
FROM orders
ORDER BY amount DESC
LIMIT 3;
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
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
SELECT customer,
COUNT(*) AS order_count
FROM orders
GROUP BY customer;
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
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;
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
SELECT customer,
COUNT(*) AS order_count,
SUM(amount) AS total_spent
FROM orders
GROUP BY customer
HAVING COUNT(*) > 1
ORDER BY total_spent DESC;
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
SELECT category,
SUM(amount * quantity) AS revenue
FROM orders
WHERE status = 'delivered'
GROUP BY category
HAVING SUM(amount * quantity) > 50
ORDER BY revenue DESC;
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
What's next? You can now filter, sort, and summarize data from a single table. The next chapter, SQL Joins, teaches you how to combine data from multiple tables — INNER JOIN, LEFT JOIN, RIGHT JOIN, CROSS JOIN, and SELF JOIN.
All Chapters in SQL Beginner Course
