SQL Functions Practice

Practise COUNT, SUM, AVG, MIN and MAX, string functions like UPPER and LENGTH, and filtering by date ranges.

Beginner8 problemsUpdated October 23, 2026

Review first: COUNT, SUM, AVG, MIN, MAX, String Functions, Date Functions

All problems use this orders table:

order_id	customer	city	amount	order_date	coupon
101	Asha	Mumbai	1200	2026-01-05	NEW10
102	Ravi	Delhi	450	2026-01-18	NULL
103	Asha	Mumbai	800	2026-02-02	NULL
104	Meera	Pune	2300	2026-02-14	SAVE20
105	John	Chennai	150	2026-03-01	NULL
106	Ravi	Delhi	990	2026-03-09	NEW10
107	Meera	Pune	640	2026-03-21	NULL

Try your answers on these tables in the SQL Playground

1.One-row summary

Beginner

In a single row, return the number of orders as orders, the total amount as revenue, the smallest order as smallest and the largest as largest.

Show hint

Aggregate functions without GROUP BY summarise the whole table into one row.

Show solution

SQLRun

SELECT COUNT(*)    AS orders,
       SUM(amount) AS revenue,
       MIN(amount) AS smallest,
       MAX(amount) AS largest
FROM orders;

Output

orders | revenue | smallest | largest
-------+---------+----------+--------
7      | 6530    | 150      | 2300

2.Average order value
Beginner
Return the average order amount, rounded to 2 decimal places, as avg_order.
Show hint
Wrap AVG() in ROUND(value, 2).
Show solution
SQLRun
SELECT ROUND(AVG(amount), 2) AS avg_order FROM orders;
Output
avg_order --------- 932.86
The exact average is 6530 / 7 = 932.857…, which rounds to 932.86. Some databases display the result with trailing zeros (for example 932.86 or 932.8600), but the value is the same.
3.Counting with NULLs
Beginner
Return three numbers:
- with_coupon: orders that used a coupon
- coupon_codes: how many different coupon codes were used
- without_coupon: orders with no coupon
Show hint
COUNT(column) skips NULLs, COUNT(DISTINCT column) counts unique non-NULL values, and COUNT(*) counts every row.
Show solution
SQLRun
SELECT COUNT(coupon) AS with_coupon, COUNT(DISTINCT coupon) AS coupon_codes, COUNT(*) - COUNT(coupon) AS without_coupon FROM orders;
Output
with_coupon | coupon_codes | without_coupon ------------+--------------+--------------- 3 | 2 | 4
4.Unique customers
Beginner
How many different customers have placed an order? Name the column customers.
Show hint
Some customers ordered more than once, so COUNT(*) is too high.
Show solution
SQLRun
SELECT COUNT(DISTINCT customer) AS customers FROM orders;
Output
customers --------- 4
5.Build a label
Beginner
Return one row per customer with a label like Asha - MUMBAI (the city in capital letters), sorted by label.
Show hint
UPPER() changes the case and CONCAT() joins strings. DISTINCT removes repeat customers.
Show solution
SQLRun
SELECT DISTINCT CONCAT(customer, ' - ', UPPER(city)) AS label FROM orders ORDER BY label;
Output
label -------------- Asha - MUMBAI John - CHENNAI Meera - PUNE Ravi - DELHI
CONCAT with several arguments works in MySQL, PostgreSQL, SQL Server and SQLite 3.44+. Oracle's CONCAT takes only two arguments, so there you would write customer || ' - ' || UPPER(city). The || operator also works in PostgreSQL and SQLite.
6.Clean up coupon codes
Beginner
For orders that used a coupon, return the order_id, the code in lower case as code, and the number of characters in the code as code_length.
Show hint
LOWER() and LENGTH(). Filter out the NULL coupons first.
Show solution
SQLRun
SELECT order_id, LOWER(coupon) AS code, LENGTH(coupon) AS code_length FROM orders WHERE coupon IS NOT NULL ORDER BY order_id;
Output
order_id | code | code_length ---------+--------+------------ 101 | new10 | 5 104 | save20 | 6 106 | new10 | 5
SQL Server names the length function LEN(); most other databases use LENGTH().
7.Orders in a date range
Beginner
Return the order_id, customer and order_date of every order placed in February 2026.
Show hint
Use a half-open range: on or after the 1st of February, and before the 1st of March.
Show solution
SQLRun
SELECT order_id, customer, order_date FROM orders WHERE order_date >= '2026-02-01' AND order_date < '2026-03-01' ORDER BY order_date;
Output
order_id | customer | order_date ---------+----------+----------- 103 | Asha | 2026-02-02 104 | Meera | 2026-02-14
The pattern >= first day AND < first day of next month is correct for every month length, and it still works when the column stores a date and time (an order at 2026-02-28 18:30 is included). BETWEEN '2026-02-01' AND '2026-02-28' would miss that order, because a time later than midnight on the 28th is greater than '2026-02-28'.
8.First and last order dates
Beginner
Return the date of the first order as first_order and the date of the most recent order as last_order.
Show hint
MIN and MAX work on dates as well as numbers.
Show solution
SQLRun
SELECT MIN(order_date) AS first_order, MAX(order_date) AS last_order FROM orders;
Output
first_order | last_order ------------+----------- 2026-01-05 | 2026-03-21

← All practice sets Learn SQL course →