Displaying Data from Multiple Tables with Joins practice questions

Worked examples for Displaying Data from Multiple Tables with Joins

1. Question 1

   The `EMPLOYEES` and `DEPARTMENTS` tables have exactly one column name in common: `DEPT_ID`. `LAST_NAME` exists only in `EMPLOYEES`, and `DEPT_NAME` and `LOCATION` only in `DEPARTMENTS`. Which query executes successfully and lists the last name of every employee assigned to department 20 together with that department's name, returning exactly one row per such employee?

   1. A. SELECT e.last_name, d.dept_name FROM employees e NATURAL JOIN departments d WHERE d.dept_id = 20

      Assumes a table alias may be used on a NATURAL JOIN's common column. After NATURAL JOIN, DEPT_ID becomes a single coalesced column that must be referenced without any qualifier anywhere in the statement, so `d.dept_id` in the WHERE clause raises ORA-25155 (column used in NATURAL join cannot have qualifier).

   2. B. SELECT e.last_name, d.dept_name FROM employees e, departments d WHERE e.dept_id = 20

      Treats a filter on one table as if it were also the join condition. The comma join supplies no equijoin predicate, so every department row is paired with every qualifying employee row — a Cartesian product that repeats each employee once per department instead of one row per employee.

   3. C. SELECT last_name, dept_name FROM employees JOIN departments USING (dept_id) WHERE dept_id = 20Correct answer

      USING (dept_id) equijoins on the shared column and coalesces it into one unqualified column that is legal to reference bare in the WHERE clause; LAST_NAME and DEPT_NAME are unique to one table each, so the unqualified select list is unambiguous. One row per matching employee is returned.

   4. D. SELECT e.last_name, d.dept_name FROM employees e JOIN departments d USING (dept_id) WHERE e.dept_id = 20

      Assumes a USING column can still be qualified by the table it came from. A column named in USING has no table prefix anywhere in the query, so `e.dept_id` raises ORA-25154 (column part of USING clause cannot have qualifier), even though the same predicate written unqualified would be valid.

   Explanation

   Both USING and NATURAL JOIN merge the shared column into a single coalesced join column, and that column must then be referenced without a table name or alias anywhere in the statement — select list, WHERE clause, or ORDER BY — otherwise Oracle rejects the statement. Columns that are not part of the join key are unaffected and may be qualified freely. A comma join, by contrast, coalesces nothing and joins nothing unless an explicit equijoin predicate is written, so omitting it yields a Cartesian product rather than a filtered inner join.

2. Question 2

   In `employees`, the `manager_id` column holds the `emp_id` of that employee's manager, and some employees have a NULL `manager_id`. Which query lists each employee's own `last_name` in the first column and that employee's manager's `last_name` in the second column, returning exactly one row per employee who has a manager?

   1. A. SELECT e.last_name AS employee, m.last_name AS manager FROM employees e JOIN employees m ON e.manager_id = m.emp_idCorrect answer

      A self join gives the table two aliases and states the relationship explicitly in ON: `e.manager_id = m.emp_id` resolves each employee's manager_id to the manager's row. Because it is an inner join, employees whose `manager_id` is NULL match nothing (NULL = anything is UNKNOWN) and are dropped, giving exactly one row per employee who has a manager.

   2. B. SELECT e.last_name AS employee, m.last_name AS manager FROM employees e JOIN employees m ON e.emp_id = m.manager_id

      Reverses the direction of the self-join key: it matches rows where the *other* row reports to `e`, so the alias `e` ends up holding the manager and `m` the subordinate. The two output columns are therefore swapped relative to what is asked, and the row set is one row per manager/subordinate pair viewed from the manager's side.

   3. C. SELECT e.last_name AS employee, m.last_name AS manager FROM employees e JOIN employees m USING (manager_id)

      USING equates a column to the column of the SAME name in the other table, so this joins `e.manager_id = m.manager_id` — it pairs employees who share a manager (including each employee with themself), never an employee with their manager. A hierarchy self-join relates two DIFFERENT column names (`manager_id` to `emp_id`) and so cannot be written with USING or NATURAL JOIN.

   4. D. SELECT e.last_name AS employee, m.last_name AS manager FROM employees e NATURAL JOIN employees m

      NATURAL JOIN joins on EVERY column the two sides share; joining `employees` to itself makes all eight columns join columns, so it can only pair a row with an identical row, not with its manager. It also fails outright: `last_name` is a natural-join column and qualifying it with an alias raises ORA-25155 (column used in NATURAL join cannot have qualifier).

   Explanation

   Relating a table to itself requires two aliases plus an ON condition that names the two different columns being equated — here the child's `manager_id` and the parent's `emp_id`. NATURAL JOIN and USING both match columns by identical name, so neither can express a hierarchy whose two ends have different column names; a self NATURAL JOIN matches on all columns at once, and a self USING (manager_id) groups peers who share a manager. Because the join is inner, rows whose `manager_id` is NULL find no partner and disappear from the result.

3. Question 3

   In `employees`, `emp_id` is the primary key and `manager_id` is a self-reference to it. The table holds exactly these rows in the columns this query touches: ``` EMP_ID LAST_NAME MANAGER_ID COMMISSION ------ --------- ---------- ---------- 100 King (null) (null) 101 Chen 100 0.10 102 Diaz 100 0.15 103 Novak 101 (null) 104 Osei 106 0.05 105 Petrov 106 (null) 106 Quinn (null) 0.20 107 Rossi 100 (null) ``` What value does the following query return? ```sql SELECT COUNT(*), COUNT(m.emp_id), COUNT(e.commission) FROM employees m RIGHT OUTER JOIN employees e ON e.manager_id = m.emp_id ```

   1. A. 11, 11, 3

      Reverses which side keeps unmatched rows — reads the table written first (`m`) as the preserved one. That would return the 6 manager/employee pairs plus 5 NULL-extended rows for the employees who manage nobody, giving 11 rows, an always-populated m.emp_id, and only 3 non-NULL commissions among the matched employees.

   2. B. 6, 6, 4

      Expects the unmatched rows to vanish, i.e. treats the outer join as an inner join. That drops King and Quinn because their NULL manager_id matches no emp_id, but an outer join keeps them and NULL-extends the other side instead.

   3. C. 8, 8, 4

      Treats COUNT(m.emp_id) as equivalent to COUNT(*) — counting every returned row. COUNT(expr) counts only rows where expr is not NULL, and the two NULL-extended rows carry NULL in every column of the deficient table, including its primary key.

   4. D. 8, 6, 4Correct answer

      RIGHT preserves the table to the right of the keyword, `e`, so all 8 employees are returned (emp_id is unique, so no row multiplies). King and Quinn have a NULL manager_id, and NULL = emp_id is UNKNOWN, so those two rows are NULL-extended on `m` — COUNT(m.emp_id) sees only the 6 matched rows. COUNT ignores NULLs of any origin, so COUNT(e.commission) counts the 4 stored non-NULL commissions.

   Explanation

   A RIGHT OUTER JOIN preserves every row of the table named to the right of the join keyword and supplies NULLs for all columns of the other table when the join condition is not met. Because a comparison with NULL is UNKNOWN rather than TRUE, a preserved row whose join column is NULL can never match and is always NULL-extended. COUNT(*) then counts every preserved row, while COUNT(column) skips NULLs — both the NULLs manufactured by the outer join and the NULLs stored in the table — so the three counts differ.

4. Question 4

   The `employees` table holds exactly these eight rows (only the columns that matter are shown); `commission` is declared `NUMBER(4,2)`: ``` LAST_NAME COMMISSION King (null) Chen 0.10 Diaz 0.15 Novak (null) Osei 0.05 Petrov (null) Quinn 0.20 Rossi (null) ``` The three tiers below do not overlap and together span 0.00 through 0.29. How many rows does the following query return? ```sql WITH tiers AS ( SELECT 'Low' AS tier_name, 0.00 AS lo, 0.09 AS hi FROM dual UNION ALL SELECT 'Mid', 0.10, 0.19 FROM dual UNION ALL SELECT 'High', 0.20, 0.29 FROM dual ) SELECT e.last_name, t.tier_name FROM employees e JOIN tiers t ON e.commission BETWEEN t.lo AND t.hi; ```

   1. A. 2

      Reads BETWEEN as excluding its endpoints, which would drop the two employees sitting exactly on a tier's lower bound (0.10 and 0.20) and keep only 0.15 and 0.05. BETWEEN is inclusive on both ends.

   2. B. 4Correct answer

      The four employees with a commission each match exactly one tier (0.05 → Low; 0.10 and 0.15 → Mid; 0.20 → High), endpoints included. The four NULL-commission rows make the BETWEEN condition UNKNOWN, and an inner join keeps only rows whose condition is TRUE, so they contribute nothing.

   3. C. 8

      Treats a NULL commission as 0, which would place the four NULL rows in the Low tier and add four more rows. NULL is not zero: comparing it to anything yields UNKNOWN, never TRUE.

   4. D. 24

      Assumes a non-equijoin ON clause does not actually restrict rows, so the query degenerates to the 8 × 3 Cartesian product. A range condition is evaluated for every candidate pair just as an equality condition is.

   Explanation

   A range (non-equi) join with BETWEEN is evaluated pair by pair exactly like an equijoin: it is TRUE when the left value is greater than or equal to the lower bound and less than or equal to the upper bound, so values landing on a bound do qualify. When the joined column is NULL the comparison evaluates to UNKNOWN rather than TRUE or FALSE, and an inner join emits a row only for TRUE, so NULL-valued rows silently disappear from the result. With non-overlapping tiers, each remaining row matches exactly one tier, so the row count equals the number of non-NULL values that fall inside the spanned range.

5. Question 5

   In this schema `employees.dept_id` is a foreign key to `departments.dept_id`, and `dept_id` is the only column name the two tables have in common. The following statement fails. Which error does Oracle report? ```sql SELECT e.last_name, d.dept_name, e.dept_id FROM employees e JOIN departments d USING (dept_id) WHERE d.dept_name = 'Engineering' ```

   1. A. ORA-00918: column ambiguously defined

      This inverts the USING rule: it assumes the shared column stays ambiguous after the join and therefore *must* be qualified. USING already merges the two dept_id columns into one, so a bare `dept_id` in the select list is legal here — it is the qualified `e.dept_id` that is rejected.

   2. B. ORA-25155: column used in NATURAL join cannot have qualifier

      ORA-25155 is the NATURAL JOIN counterpart of the same restriction. This join names its join column explicitly with USING rather than deriving it from NATURAL, so Oracle raises the USING-specific code, not the NATURAL one.

   3. C. ORA-25154: column part of USING clause cannot have qualifierCorrect answer

      A column named in USING is coalesced into a single join column that belongs to the join itself, not to either source table, so it may not carry a table name or alias anywhere in the statement — select list included. `e.dept_id` violates that, giving ORA-25154 (Oracle SQL Language Reference, Joins: "do not qualify the column named in the USING clause").

   4. D. ORA-00904: "E"."DEPT_ID": invalid identifier

      This treats the USING column as having disappeared from the projection list of both tables. The identifier is perfectly valid and resolvable; the statement fails only because of the prohibition on qualifying it, which Oracle signals with its own dedicated code rather than ORA-00904.

   Explanation

   The USING clause replaces the two equally named columns with one coalesced join column owned by the join, not by either table. Because that column has no owning table, every reference to it in the statement — SELECT list, WHERE clause, ORDER BY — must be written unqualified; adding a table name or alias raises ORA-25154. Removing the alias (selecting plain `dept_id`) makes the statement run, while non-join columns such as `dept_name` may still be qualified normally.

6. Question 6

   The `employees` table has a self-referencing `manager_id` column that holds the `emp_id` of the employee's manager, or NULL when the employee has no manager. Some employees manage nobody. Which query returns **exactly one row per employee and no additional rows**, pairing each employee's last name with their manager's last name and showing NULL in the manager column for an employee who has no manager?

   1. A. SELECT e.last_name AS employee, m.last_name AS manager FROM employees e LEFT OUTER JOIN employees m ON e.manager_id = m.emp_idCorrect answer

      LEFT OUTER JOIN preserves every row of the left table (the employee-role copy) and NULL-extends the right side when the join condition finds no match, so an employee with a NULL manager_id still yields exactly one row with a NULL manager name. Because manager_id is a primary-key reference, no employee matches more than one manager row, so the count stays at one row per employee.

   2. B. SELECT e.last_name AS employee, m.last_name AS manager FROM employees e RIGHT OUTER JOIN employees m ON e.manager_id = m.emp_id

      Reverses the direction of preservation: RIGHT OUTER JOIN keeps every row of the right (manager-role) copy of the table, so employees whose manager_id is NULL never appear in the employee column, and every person who manages nobody contributes an extra row with a NULL employee.

   3. C. SELECT e.last_name AS employee, m.last_name AS manager FROM employees e FULL OUTER JOIN employees m ON e.manager_id = m.emp_id

      Treats FULL OUTER JOIN as 'the safe choice that keeps every employee once'. It does preserve the unmatched left rows, but it also NULL-extends the unmatched right rows, so people who manage nobody add rows whose employee column is NULL — more than one row per employee.

   4. D. SELECT e.last_name AS employee, m.last_name AS manager FROM employees e JOIN employees m ON e.manager_id = m.emp_id

      Expects an unmatched row to survive an inner join. A NULL manager_id satisfies no equality condition, so employees without a manager are silently dropped rather than NULL-extended.

   Explanation

   In an outer join the keyword names the side whose rows are preserved: LEFT keeps every row of the left table, RIGHT keeps every row of the right table, and FULL keeps both, filling the non-preserved side with NULLs wherever the join condition finds no match. A self-join that must list every employee once, manager or not, therefore has to preserve the employee-role copy and let the manager-role copy be NULL-extended. Preserving the manager-role copy instead loses the managerless employees and manufactures rows for people who manage nobody, while preserving both sides adds those manufactured rows on top of the desired ones.

7. Question 7

   The `employees` table holds one row per employee with columns `emp_id`, `first_name`, `last_name`, `salary`, `commission`, `manager_id`, `dept_id`, `hire_date`, where `manager_id` holds the `emp_id` of that employee's manager and is NULL for employees who have no manager. Which query returns exactly one row for each employee who has a manager, with the employee's own last name in the first column and that employee's manager's last name in the second column?

   1. A. SELECT w.last_name AS employee, m.last_name AS manager FROM employees w JOIN employees m ON w.emp_id = m.manager_id;

      Reverses the direction of the self-join: it matches rows where the first alias is the manager of the second, so it returns one row per manager/subordinate pair with the manager's name first and a subordinate's name second — and managers with several reports appear repeatedly while employees whose reports are none do not appear at all.

   2. B. SELECT w.last_name AS employee, m.last_name AS manager FROM employees w JOIN employees m ON w.manager_id = m.emp_id;Correct answer

      The correct self-equijoin: the child alias supplies `manager_id`, which is equated to the parent alias's primary key `emp_id`, so each row pairs an employee with the single row describing their manager. Rows whose `manager_id` is NULL match nothing (NULL = anything is UNKNOWN), so managerless employees are dropped exactly as required.

   3. C. SELECT w.last_name AS employee, m.last_name AS manager FROM employees w JOIN employees m USING (manager_id);

      Reads USING as if it linked `manager_id` to the other row's key. USING (col) equates the *same* column in both tables — `w.manager_id = m.manager_id` — so it pairs each employee with every colleague who reports to the same manager, including the employee with itself, instead of with the manager.

   4. D. SELECT w.last_name AS employee, m.last_name AS manager FROM employees w JOIN employees m ON manager_id = emp_id;

      Omits the alias qualifiers in the ON clause of a self-join. Both `manager_id` and `emp_id` exist in both copies of the table, so neither reference can be resolved and the statement fails to parse with ORA-00918 (column ambiguously defined).

   Explanation

   Joining a table to itself requires two different aliases so each copy can be referenced independently; the join condition then walks the foreign key from the child row to the parent row, equating the child's `manager_id` with the parent's `emp_id`. Writing that condition in the opposite direction produces the manager-to-subordinate listing instead, and every column reference in a self-join's ON clause must be alias-qualified because every column name occurs twice. USING is not a substitute here: it can only equate columns that have the same name in both tables, which for a self-join means matching employees to their peers rather than to their manager. Because the join is an inner equijoin, rows whose `manager_id` is NULL are eliminated automatically.

8. Question 8

   `employees.dept_id` is a foreign key to `departments.dept_id`. The relevant rows are: ``` DEPARTMENTS EMPLOYEES DEPT_ID DEPT_NAME EMP_ID LAST_NAME DEPT_ID COMMISSION ------- -------------- ------ --------- ------- ---------- 10 Administration 100 King 10 (null) 20 Engineering 101 Chen 20 0.10 30 Sales 102 Diaz 20 0.15 40 Research 103 Novak 20 (null) 104 Osei 30 0.05 105 Petrov 30 (null) 106 Quinn 30 0.20 107 Rossi 10 (null) ``` How many rows does the following query return? ```sql SELECT e.last_name, d.dept_name FROM employees e JOIN departments d ON e.dept_id = d.dept_id AND e.commission <> 0.10 ```

   1. A. 8

      Assumes only the equijoin `e.dept_id = d.dept_id` restricts the result and that the extra predicate in ON merely describes the match. In an inner join every conjunct of ON must be TRUE for a row to survive, so the commission test filters exactly as a WHERE clause would.

   2. B. 3Correct answer

      Both conjuncts of ON must evaluate to TRUE. Every employee has a matching department, so the deciding test is `commission <> 0.10`: it is TRUE for Diaz (0.15), Osei (0.05) and Quinn (0.20), FALSE for Chen (0.10), and UNKNOWN — hence not TRUE — for the four employees whose commission is NULL. That leaves 3 rows.

   3. C. 7

      Treats `commission <> 0.10` as TRUE when commission is NULL, i.e. reads 'unknown value' as 'a value different from 0.10'. Any comparison operator applied to NULL yields UNKNOWN, never TRUE, so the four NULL-commission employees are excluded rather than kept, and the count is not 8 minus Chen.

   4. D. 4

      Adds a row for the Research department, treating ANSI `JOIN ... ON` as if it preserved unmatched rows from the right table. `JOIN` without LEFT/RIGHT/FULL is an inner join, so a department with no qualifying employee contributes no row; preserving it would require an outer join.

   Explanation

   In an inner join the ON clause is a plain filter: a row appears only when the whole condition evaluates to TRUE, so an extra conjunct alongside the equijoin removes rows exactly the way a WHERE predicate would. Comparisons against NULL evaluate to UNKNOWN rather than TRUE or FALSE, so rows whose commission is NULL fail the inequality test and vanish. And because the join is inner, no unmatched department is padded back in.