Aggregation: 1Z0-171 practice questions
Questions in the style of exam 1Z0-171 on this topic. Answer each one first, then open its answer and explanation.
What this topic covers
- Using group functions
- Creating groups of data
- Restricting group results
Practice questions
1. The five customers have the credit limits 5000, 3000, NULL, 2000 and 1000. What does this query return?
SELECT AVG(c.credit_limit), AVG(NVL(c.credit_limit, 0))
FROM customers c;- 2750 and 2200
- 2200 and 2750
- 2750 and 2750
- NULL and 2200
Show the answer
Answer: A
Group functions ignore NULLs: AVG(c.credit_limit) = (5000 + 3000 + 2000 + 1000) / 4 = 2750. With NVL the NULL becomes 0 and is included in the count: 11000 / 5 = 2200. A NULL input never makes AVG return NULL as long as at least one value is not NULL.
2. No employee earns more than 50000. What does this query return?
SELECT COUNT(*), MAX(e.salary)
FROM employees e
WHERE e.salary > 50000;- One row: 0 and NULL
- One row: 0 and 0
- One row: NULL and NULL
- No rows
Show the answer
Answer: A
WHERE removes every row, so the single group is empty. An aggregate query without GROUP BY still returns exactly one row, even when no rows reach it. COUNT never returns NULL, so it gives 0; SUM, AVG, MIN and MAX have no values to work on and return NULL. Wrap with NVL(MAX(...), 0) if you need 0.
3. What happens when you run SELECT SUM(e.last_name) FROM employees e?
- It fails: Oracle tries to read each last name as a number (ORA-01722).
- It fails: SUM refuses a text column before reading any value (ORA-00932).
- It returns NULL
- It returns all last names concatenated
Show the answer
Answer: A
SUM and AVG need numeric input, so Oracle implicitly converts each last_name to a number, and 'King' cannot be converted: ORA-01722. Concatenating strings is LISTAGG's job; MIN and MAX are the group functions that accept character data.
4. promotions has no rows. How many rows does this query return?
SELECT p.promo_name, COUNT(*)
FROM promotions p
GROUP BY p.promo_name;- 0
- 1
- An error, because there is nothing to group
Show the answer
Answer: A
With GROUP BY, one row is returned per group, and an empty table has no groups, so no rows come back. Without GROUP BY the same aggregate would return one row with COUNT(*) = 0.