Stepwise-Lab

Subqueries: 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

  • Single-row subqueries
  • Multiple-row subqueries
  • Updating and deleting rows using correlated subqueries and joins

Practice questions

1. Department 40 has no employees, and EMPLOYEES has 9 rows. What value does this query return?

SELECT COUNT(*)
FROM   employees e
WHERE  e.salary > ALL (SELECT e2.salary FROM employees e2 WHERE e2.department_id = 40);
  1. 0
  2. 1
  3. 9
  4. It raises an error because the subquery returns no rows
Show the answer

Answer: C

> ALL over an empty set is TRUE for every row (there is nothing to fail against), so all 9 employees qualify. > ANY over an empty set is FALSE and would give 0. An empty subquery is not an error.

2. What happens when you run this query?

SELECT e.last_name
FROM   employees e
WHERE  e.salary > (SELECT e2.salary
                   FROM   employees e2
                   WHERE  e2.employee_id = 103
                   ORDER  BY e2.salary);
  1. It returns the employees who earn more than employee 103
  2. It raises ORA-00907: missing right parenthesis
  3. It raises ORA-01427: single-row subquery returns more than one row
  4. The ORDER BY is ignored and a warning is returned
Show the answer

Answer: B

A subquery used in a WHERE condition cannot have its own ORDER BY; the parser expects the closing parenthesis where ORDER BY appears and raises ORA-00907. ORDER BY is allowed in an inline view in the FROM clause (for example for top-N with ROWNUM). Without ORDER BY the query would return the employees who earn more than employee 103, and employee_id 103 matches at most one row, so ORA-01427 is impossible.

3. What is shown in the HEADCOUNT column for Research (department 40, no employees)?

SELECT d.department_name,
       (SELECT COUNT(*)
        FROM   employees e
        WHERE  e.department_id = d.department_id) AS headcount
FROM   departments d;
  1. 0
  2. NULL
  3. The Research row is not returned
  4. It raises ORA-01403: no data found
Show the answer

Answer: A

The subquery is an aggregate without GROUP BY, so it always returns exactly one row, and COUNT over no rows is 0. Compare with a scalar subquery that selects a plain column (or SUM/MAX): there an empty result gives NULL. A scalar subquery never removes the outer row.

4. Three employees earn less than 5000: Partners (department 20), Lorentz (department 30) and Grant, who has no department. So the subquery returns 20, 30 and NULL. Which statement about the result is true?

SELECT e.last_name
FROM   employees e
WHERE  e.department_id IN (SELECT e2.department_id
                           FROM   employees e2
                           WHERE  e2.salary < 5000);
  1. It returns the seven employees of departments 20 and 30; the NULL in the list has no effect
  2. It returns no rows, because the list contains a NULL
  3. It returns eight rows: Grant is added, because her NULL matches the NULL in the list
  4. It fails: a subquery for IN may not return a NULL
Show the answer

Answer: A

IN is an OR of equalities, so the NULL only adds an UNKNOWN branch; a row still qualifies when another value matches. Grant's NULL department never equals anything, not even the NULL in the list, so she is not returned. Only NOT IN is ruined by a NULL in the list, and a NULL in a subquery's result is never an error.