Reporting Aggregated Data with Group Functions practice questions

Worked examples for Reporting Aggregated Data with Group Functions

1. Question 1

   Each employee's manager_id is stored in the EMPLOYEES table; some employees have no manager, and several employees share the same manager. Which query returns the number of distinct managers that employees report to?

   1. A. SELECT COUNT(manager_id) FROM employees;

      COUNT(manager_id) counts every row whose manager_id is not null but keeps duplicates, so employees who share a manager are each counted, overstating the number of distinct managers.

   2. B. SELECT COUNT(*) FROM employees;

      COUNT(*) counts all rows, including the employees who report to no one, so it counts people rather than distinct managers.

   3. C. SELECT COUNT(DISTINCT manager_id) FROM employees;Correct answer

      COUNT(DISTINCT manager_id) discards the null manager_ids and collapses the repeated ids, leaving only the separate managers actually reported to (100, 101 and 106).

   4. D. SELECT COUNT(DISTINCT NVL(manager_id, 0)) FROM employees;

      Wrapping manager_id in NVL(...,0) converts the null manager_ids into a single value 0, which DISTINCT then counts as an extra 'manager', inflating the true distinct count by one.

   Explanation

   COUNT behaves differently according to its argument: COUNT(*) counts every row, COUNT(column) counts only rows where that column is not null, and COUNT(DISTINCT column) counts the distinct non-null values. Because some employees have no manager and several share a manager, only the distinct-non-null form yields the number of separate managers reported to.

2. Question 2

   Which query correctly counts all rows in the EMPLOYEES table, regardless of whether any column value is NULL?

   1. A. SELECT COUNT(salary + commission) FROM employees

      Arithmetic with a NULL operand propagates NULL: SALARY + NULL = NULL for each of the four employees with no commission. COUNT then skips those NULL expression results, returning 4 rather than 8. The non-nullable SALARY column does not immunise the expression against NULL propagation from COMMISSION.

   2. B. SELECT COUNT(*) FROM employeesCorrect answer

      COUNT(*) counts every row returned by the query, including rows where one or more columns hold NULLs. It is the only form of COUNT that never examines individual column values and therefore cannot be reduced by NULLs in any column.

   3. C. SELECT COUNT(commission) FROM employees

      COUNT(expr) counts only the rows where the expression evaluates to a non-NULL value. Four employees have a NULL COMMISSION (Alice, Dave, Frank, Heidi), so this returns 4, not 8 — the classic COUNT(*) vs. COUNT(column) confusion.

   4. D. SELECT COUNT(manager_id) FROM employees

      COUNT(manager_id) skips the two employees whose MANAGER_ID is NULL (Alice and Grace), returning 6. This confirms the rule: COUNT(column) excludes NULLs and therefore cannot be used to count all rows when the column is nullable.

   Explanation

   COUNT(*) is the only aggregate that counts every row regardless of NULLs; COUNT(expr) counts only the non-NULL results of that expression. When a column itself is nullable, COUNT(column) silently under-counts. When an arithmetic expression involves a nullable operand, NULL propagation further reduces the count before the function even sees the values. Only COUNT(*) is immune to both effects.

3. Question 3

   Which query correctly returns the DEPT_ID for each department in which every employee earns more than 5,000?

   1. A. SELECT dept_id FROM employees WHERE salary > 5000 GROUP BY dept_id

      WHERE salary > 5000 filters out individual low-earning employees before grouping but still allows their department to appear in the result if at least one remaining employee satisfies the predicate. A department that contains both high- and low-salary employees passes this query because the low-salary rows are silently discarded rather than disqualifying the group.

   2. B. SELECT dept_id FROM employees GROUP BY dept_id HAVING MIN(salary) <= 5000

      MIN(salary) <= 5000 retains groups whose lowest salary is at most 5000, which identifies departments where at least one employee earns 5000 or less — the logical complement of the required condition. Reversing the comparison operator inverts the filter and returns exactly the departments that should be excluded.

   3. C. SELECT dept_id FROM employees GROUP BY dept_id HAVING AVG(salary) > 6000

      AVG(salary) > 6000 tests whether the mean salary in the group exceeds 6000. A department clears this threshold even when some individual salaries fall below 5000, because high-earning employees can pull the average up while low-earning outliers remain in the group undetected. Checking the average does not guarantee that every employee earns more than 5000; only MIN provides that guarantee.

   4. D. SELECT dept_id FROM employees GROUP BY dept_id HAVING MIN(salary) > 5000Correct answer

      MIN(salary) > 5000 in HAVING ensures that the lowest salary across the entire group exceeds 5000, which is logically equivalent to asserting that every individual salary in the department exceeds 5000. No row in a qualifying group can fall at or below the threshold, because MIN would expose it.

   Explanation

   To assert that a condition holds for every row in a group, the correct aggregate is MIN: if MIN(salary) > 5000 then no salary in the group can be at or below 5000, confirming the universal claim. Filtering with WHERE before grouping silently removes low-salary employees without disqualifying their department, so departments with a mix of salaries still appear in the result. Reversing the comparison produces the logical complement, and substituting AVG for MIN tests a different condition that admits groups containing individual salaries below the threshold.

4. Question 4

   Which query returns exactly one row for every distinct combination of DEPT_ID and MANAGER_ID that occurs in EMPLOYEES, together with the number of employees sharing that combination — including combinations whose MANAGER_ID is NULL?

   1. A. SELECT dept_id, manager_id, COUNT(*) FROM employees GROUP BY dept_id, manager_idCorrect answer

      Grouping by both DEPT_ID and MANAGER_ID forms one group per distinct pair of those two values, and Oracle treats NULL as its own distinct group value, so employees with no manager are counted in their own (DEPT_ID, NULL) group. Both selected columns appear in GROUP BY, so the statement is valid.

   2. B. SELECT dept_id, manager_id, COUNT(*) FROM employees GROUP BY dept_id

      MANAGER_ID is in the SELECT list but not in GROUP BY. Every non-aggregate selected column must appear in GROUP BY, so this raises ORA-00979: not a GROUP BY expression.

   3. C. SELECT dept_id, manager_id, COUNT(*) FROM employees GROUP BY dept_id, manager_id, salary

      Adding SALARY to GROUP BY makes the grouping finer: two employees who share a DEPT_ID and MANAGER_ID but earn different salaries land in separate groups, so the result has more than one row per (DEPT_ID, MANAGER_ID) pair rather than exactly one.

   4. D. SELECT dept_id, manager_id, COUNT(*) FROM employees GROUP BY dept_id, manager_id HAVING manager_id IS NOT NULL

      GROUP BY does form a group for the NULL manager values, and this HAVING clause then discards those (DEPT_ID, NULL) groups, so it drops exactly the NULL-manager combinations the question requires.

   Explanation

   GROUP BY on several columns produces one group per distinct combination of all of them, and NULL is treated as a single distinct grouping value, so rows with a NULL manager are grouped together rather than discarded. Any column selected alongside an aggregate must itself be in GROUP BY, and adding an extra grouping column that is not selected still splits the groups, changing how many rows come back.

5. Question 5

   The table below shows the COMMISSION column for every row in the EMPLOYEES table. ``` | COMMISSION | |------------| | NULL | | 0.10 | | 0.15 | | NULL | | 0.05 | | NULL | | 0.20 | | NULL | ``` What value does the following query return? ```sql SELECT AVG(commission) FROM employees ```

   1. A. NULL

      Scalar NULL propagation (e.g. NULL + x = NULL) does not apply to aggregate functions. AVG has explicit NULL-ignoring semantics; it returns NULL only when every input row is NULL, which is not the case here.

   2. B. 0.0625

      This result would follow from summing all four non-NULL commissions (0.50) and dividing by the total row count of 8, as though NULLs were treated as zeros in the denominator. AVG ignores NULLs in both the sum and the divisor, so 8 is never used.

   3. C. 0.125Correct answer

      AVG excludes NULL inputs from both the numerator and the denominator. The four non-NULL commission values (0.10 + 0.15 + 0.05 + 0.20 = 0.50) are divided by 4, yielding 0.125.

   4. D. 4

      4 is the count of non-NULL commission values, which AVG uses internally as its divisor, but AVG returns the computed mean — not the count.

   Explanation

   The AVG aggregate function excludes NULL values from both the sum and the row count used as the divisor, so it computes the arithmetic mean over non-NULL rows only. With four non-NULL commission values summing to 0.50, AVG divides by 4 rather than by the full table row count of 8. The scalar NULL-propagation rule — where arithmetic on a NULL yields NULL — does not apply to aggregate functions, which have their own explicit NULL-ignoring semantics defined in the SQL standard and Oracle's implementation.

6. Question 6

   The following statement is executed against an Oracle database. What is the outcome? ```sql SELECT MAX(SUM(salary)) FROM employees ```

   1. A. The statement raises ORA-00978: nested group function without GROUP BY.Correct answer

      Nesting one aggregate inside another (MAX of SUM) collapses the rows to a single value in two stages, which is only meaningful if an inner grouping level exists. Without a GROUP BY clause there is no group for the inner SUM to produce multiple values for the outer MAX to range over, so Oracle rejects the query with ORA-00978.

   2. B. The query returns 51200.

      51200 is the total of all salaries — the value of a plain SUM(salary). MAX(SUM(salary)) is not the same as SUM(salary); the nested form is illegal without GROUP BY and never executes to produce this total.

   3. C. The query returns 9000.

      9000 is the largest single salary — MAX(salary). MAX(SUM(salary)) does not reduce to MAX(salary); nesting aggregates without a GROUP BY is a syntax error (ORA-00978), not a shortcut to the maximum individual value.

   4. D. The statement raises ORA-00937: not a single-group group function.

      ORA-00937 is raised when a SELECT mixes a bare column with an aggregate and no GROUP BY. Here the problem is a nested aggregate, which Oracle reports specifically as ORA-00978, not ORA-00937.

   Explanation

   A nested group function such as MAX(SUM(salary)) is only valid when a GROUP BY clause defines the inner grouping level: SUM aggregates within each group, and MAX then ranges over those per-group sums. Without a GROUP BY there is no set of group-level sums for the outer aggregate to consume, so Oracle raises ORA-00978 (nested group function without GROUP BY). This is distinct from ORA-00937, which concerns mixing a non-aggregated column with an aggregate.

7. Question 7

   Which of the following queries returns exactly the count of EMPLOYEES rows where the COMMISSION column is not NULL?

   1. A. SELECT COUNT(manager_id) FROM employees

      COUNT(manager_id) counts non-NULL MANAGER_ID values. Two employees have a NULL manager, so this returns 6 — a different count than the four non-NULL commission rows. Applying COUNT to the wrong column measures that column's non-NULL presence, which has no guaranteed relationship to COMMISSION's.

   2. B. SELECT COUNT(DISTINCT dept_id) FROM employees

      COUNT(DISTINCT dept_id) counts the number of distinct non-NULL department values among employees — departments 10, 20, and 30 — returning 3. It measures departmental variety, not the number of employees with a commission on record.

   3. C. SELECT COUNT(*) FROM employees

      COUNT(*) counts every row in the table unconditionally, including rows where COMMISSION is NULL. It returns the full table row count regardless of any column's NULL status.

   4. D. SELECT COUNT(commission) FROM employeesCorrect answer

      COUNT applied to a column name counts only the rows where that column is not NULL. Because COMMISSION is NULL for four employees, the function counts the remaining four non-NULL rows, which is exactly the target measure.

   Explanation

   COUNT applied to a column name — as opposed to COUNT(*) — counts only the non-NULL values in that column, making it the direct way to count rows where a particular column is populated. COUNT(*) disregards all column values and always returns the full row count. Applying COUNT to a different column measures that column's non-NULL presence, which has no guaranteed relationship to the target column's non-NULL count; the results will differ whenever the two columns have NULLs in different rows.

8. Question 8

   In the EMPLOYEES table the COMMISSION column is NULL for every employee who earns no commission. Which query returns each DEPT_ID together with the number of its employees who actually earn a commission, listing only departments in which more than one employee earns a commission?

   1. A. SELECT dept_id, COUNT(*) FROM employees GROUP BY dept_id HAVING COUNT(*) > 1

      COUNT(*) counts every row in the group regardless of NULLs, so it reports total headcount, not commissioned employees, and filters on that total. This confuses COUNT(*) with COUNT(commission); a department whose employees mostly have no commission can still pass and its reported number is the full headcount.

   2. B. SELECT dept_id, COUNT(commission) FROM employees GROUP BY dept_id HAVING COUNT(*) > 1

      The projection COUNT(commission) is correct, but the HAVING tests COUNT(*) > 1 — total headcount — so it keeps departments that have more than one employee even when only one (or none) of them earns a commission. Mixing COUNT(*) in the filter with COUNT(commission) in the select list lets through a department whose commissioned count is not above one.

   3. C. SELECT dept_id, COUNT(commission) FROM employees WHERE COUNT(commission) > 1 GROUP BY dept_id

      COUNT(commission) is a group function and WHERE is applied per row before grouping, so a group function cannot appear there and Oracle raises ORA-00934: group function is not allowed here. The per-group count condition must go in HAVING, not WHERE.

   4. D. SELECT dept_id, COUNT(commission) FROM employees GROUP BY dept_id HAVING COUNT(commission) > 1Correct answer

      COUNT(commission) tallies only the rows whose COMMISSION is non-null, so it counts the commissioned employees per department, and HAVING COUNT(commission) > 1 keeps the departments where more than one of them exists. Both the projected count and the group filter ignore the NULL-commission rows, which is exactly what the question asks.

   Explanation

   COUNT(expr) counts only the rows where expr is non-null, whereas COUNT(*) counts every row in the group including those with NULLs, so 'how many employees earn a commission' must use COUNT(commission) in both the projection and the group filter. Substituting COUNT(*) in either place changes the question to one about total headcount, and placing a group function in WHERE — which runs before groups are formed — raises ORA-00934.