Displaying Data from Multiple Tables with Joins practice questions

Worked examples for Displaying Data from Multiple Tables with Joins

1. Question 1

   Given the hr-mini data below: ``` DEPARTMENTS EMPLOYEES (salary) 10 Administration Alice 9000, Heidi 6700 20 Engineering Bob 6000, Carol 7500, Dave 4800 30 Sales Eve 5200, Frank 3900, Grace 8100 40 Research (no employees) ``` How many rows does the following query return? ```sql SELECT d.dept_name, e.first_name FROM departments d LEFT JOIN employees e ON d.dept_id = e.dept_id WHERE e.salary > 6000 ```

   1. A. 4Correct answer

      The WHERE predicate sits on the null-extended employees table and is applied after the outer join. Only Alice, Carol, Grace and Heidi earn more than 6000; the Research row and every employee at or below 6000 are removed, leaving 4 rows.

   2. B. 5

      Assumes a LEFT join always keeps the department row, so it counts Research as well (4 + 1). It overlooks that Research's employee salary is NULL, making NULL > 6000 UNKNOWN, so the WHERE clause discards that row.

   3. C. 8

      Recognizes that the WHERE on the outer table collapses the join to inner semantics (dropping Research) but forgets to also apply salary > 6000, counting all eight matched employees instead of only those above 6000.

   4. D. 9

      Treats the predicate as if it lived in the ON clause (or as non-filtering), leaving every row of the LEFT join in place: the eight employees plus the NULL-extended Research row.

   Explanation

   A join's ON clause decides which rows pair up; a WHERE clause filters the already-joined result. When a predicate on the null-extended (optional) table sits in WHERE, it is evaluated after the outer join, and for an unmatched row that column is NULL, so the comparison is UNKNOWN and the row is discarded — collapsing the outer join toward an inner join. Only genuinely matched rows that also satisfy the predicate remain.

2. Question 2

   Both tables have a column named dept_id. What happens when the following statement is executed? ```sql SELECT dept_id, first_name, dept_name FROM employees JOIN departments ON employees.dept_id = departments.dept_id ```

   1. A. ORA-00904: invalid identifier

      ORA-00904 is raised when a referenced name does not exist as a column of any table in scope. Every name here (dept_id, first_name, dept_name) exists, so the problem is not a missing identifier but an unqualified name that exists in two tables.

   2. B. ORA-00918: column ambiguously definedCorrect answer

      dept_id exists in both employees and departments, and the ON clause (unlike USING) keeps both copies visible. The unqualified dept_id in the select list cannot be resolved to a single table, so Oracle raises ORA-00918; qualifying it as employees.dept_id or departments.dept_id fixes it.

   3. C. ORA-00957: duplicate column name

      ORA-00957 concerns duplicate column names in a DDL definition or in the output list of a CREATE TABLE AS SELECT, not an ambiguous reference in an ordinary query. This SELECT projects distinct column names; the fault is which table the ambiguous dept_id comes from.

   4. D. ORA-25154: column part of USING clause cannot have qualifier

      ORA-25154 fires only when a column named in a USING clause is written with a table qualifier. This query joins with ON, not USING, so that rule does not apply; the actual failure is the ambiguous unqualified dept_id.

   Explanation

   An ON-based join keeps a separate copy of each shared column in the query's namespace, so a column name that occurs in more than one joined table must be qualified wherever it is referenced. Leaving dept_id unqualified in the select list gives Oracle no way to choose between the two tables' copies, and it raises ORA-00918 at parse time. This differs from a USING or NATURAL join, which coalesce the join column into one unqualified reference instead.

3. Question 3

   Three of the four queries below return exactly the same rows. Which query returns a DIFFERENT result from the other three?

   1. A. SELECT e.first_name FROM employees e JOIN departments d ON e.dept_id = d.dept_id AND d.dept_name = 'SALES';Correct answer

      Character literal comparison is case-sensitive: 'SALES' matches no stored department name ('Sales'), so this returns no rows while the other three return the department's employees.

   2. B. SELECT e.first_name FROM employees e JOIN departments d ON e.dept_id = d.dept_id AND d.dept_name = 'Sales';

      For an inner join a predicate in the ON clause filters exactly as it would in WHERE, so this returns the Sales employees — identical to the WHERE and comma-join forms.

   3. C. SELECT e.first_name FROM employees e JOIN departments d ON e.dept_id = d.dept_id WHERE d.dept_name = 'Sales';

      In an inner join the WHERE filter is equivalent to placing the same condition in ON, so this produces the same rows as the ON-clause and comma-join forms.

   4. D. SELECT e.first_name FROM employees e, departments d WHERE e.dept_id = d.dept_id AND d.dept_name = 'Sales';

      The old-style comma join with both the join and filter predicates in WHERE is equivalent to the ANSI inner-join forms, returning the same Sales employees.

   Explanation

   For an inner join a predicate is evaluated identically whether it sits in the ON clause, the WHERE clause, or an old-style comma-join WHERE, so those three forms are guaranteed to produce the same rows for any data. Character string comparison in Oracle is case-sensitive, so altering only a literal's case can eliminate every match, making that query the odd one out.

4. Question 4

   Which Oracle-proprietary WHERE-clause query produces exactly the same result as the following ANSI SQL outer join against the hr-mini schema? ```sql SELECT d.dept_name, e.first_name FROM departments d LEFT JOIN employees e ON d.dept_id = e.dept_id ```

   1. A. SELECT d.dept_name, e.first_name FROM departments d, employees e WHERE d.dept_id = e.dept_id(+)Correct answer

      Oracle's (+) operator marks the OPTIONAL (deficient) side — the table whose rows may be NULL-extended. Placing (+) on e.dept_id makes employees the optional side and preserves every department row, exactly mirroring LEFT JOIN departments → employees. The result is 9 rows: 8 matched pairs plus Research with NULL employee columns.

   2. B. SELECT d.dept_name, e.first_name FROM departments d, employees e WHERE d.dept_id(+) = e.dept_id

      Placing (+) on d.dept_id makes departments the optional (NULL-extended) side, preserving every employee row instead — the reversed direction (equivalent to employees LEFT JOIN departments). All 8 employees have valid dept_ids so all 8 match; no Research row with NULL employee columns appears.

   3. C. SELECT d.dept_name, e.first_name FROM departments d, employees e WHERE d.dept_id = e.dept_id(+) AND e.dept_id IS NOT NULL

      The (+) placement is correct, but the conjunct e.dept_id IS NOT NULL in the WHERE clause is evaluated after the outer join and eliminates every NULL-extended row. The result is indistinguishable from a plain INNER JOIN — the Research row with NULL employee columns never appears.

   4. D. SELECT d.dept_name, e.first_name FROM departments d, employees e WHERE d.dept_id = e.dept_id

      Omitting (+) entirely produces an implicit INNER JOIN (SQL-89 comma syntax). Only rows with matching dept_id values in both tables are returned; Research (dept_id 40) has no employees and is excluded from the result.

   Explanation

   Oracle's traditional outer-join notation places (+) on the column belonging to the OPTIONAL table — the side whose rows may be NULL-extended when no match is found. A LEFT JOIN preserves the left table and NULL-fills the right, so (+) must appear on the right table's join column. Reversing the (+) placement reverses the preserved side. Any WHERE-clause predicate that tests the outer-joined table's column without its own (+) — such as IS NOT NULL — is applied after the join and silently removes the NULL-extended rows, converting the outer join into an inner join.

5. Question 5

   The hr-mini schema has four departments (10 Administration, 20 Engineering, 30 Sales, 40 Research); Research currently has no employees. You must produce a report that lists **all four** department names — Research included, shown once with a NULL first name — and beside each department only the first names of its employees who earn **more than 6000**. An employee earning 6000 or less must not appear anywhere in the result. Which query does exactly this?

   1. A. SELECT d.dept_name, e.first_name FROM departments d LEFT JOIN employees e ON d.dept_id = e.dept_id WHERE e.salary > 6000;

      Moving the salary predicate to WHERE filters AFTER the outer join. The Research row is NULL-extended, so e.salary is NULL and NULL > 6000 is UNKNOWN, so WHERE discards it — Research is lost and the query collapses to the high earners only.

   2. B. SELECT d.dept_name, e.first_name FROM departments d LEFT JOIN employees e ON d.dept_id = e.dept_id AND e.salary > 6000;Correct answer

      A LEFT JOIN keeps every departments row; putting the salary test in the ON clause decides which employees match WITHOUT dropping any department, so Research survives as a NULL-extended row and only >6000 earners are attached. Employees earning 6000 or less never match, so they never appear.

   3. C. SELECT d.dept_name, e.first_name FROM departments d RIGHT JOIN employees e ON d.dept_id = e.dept_id AND e.salary > 6000;

      RIGHT JOIN preserves the employees side, not departments. Every employee appears — low earners simply get a NULL department name — which violates 'earning 6000 or less must not appear', and Research never shows up because no employee anchors it.

   4. D. SELECT d.dept_name, e.first_name FROM departments d FULL OUTER JOIN employees e ON d.dept_id = e.dept_id AND e.salary > 6000;

      FULL OUTER JOIN preserves both sides, so in addition to the departments it emits a NULL-department row for each low earner (Bob, Dave, Eve, Frank). Those employees must not appear in the wanted result.

   Explanation

   In an outer join a predicate in the ON clause participates in matching and is applied before unmatched rows are NULL-extended and preserved, so filtering the optional table there keeps every preserved-side row. The same predicate in WHERE runs after the join and silently discards the NULL-extended rows. Which of LEFT, RIGHT, or FULL is used then decides which side's unmatched rows are kept at all.

6. Question 6

   The employees table holds 8 rows, and every employee's salary is distinct from all the others. How many rows does the following query return? ```sql SELECT e.first_name, h.first_name FROM employees e JOIN employees h ON e.salary > h.salary ```

   1. A. 64

      64 is 8 x 8, the full Cartesian product of the table with itself. That count would require a predicate that is TRUE for every pair, including each row against itself; a strict greater-than can never be TRUE when a row is compared to itself, so the diagonal is excluded.

   2. B. 56

      56 is 8 x 7, which removes only the 8 self-pairs where e and h are the same row. It still counts both (higher, lower) and (lower, higher) orderings of each distinct pair, but a strict > is TRUE for exactly one of those two directions, so this double-counts.

   3. C. 28Correct answer

      For each of the C(8,2) = 28 unordered pairs of distinct salaries, exactly one ordering satisfies e.salary > h.salary. Because all 8 salaries are distinct, no pair is tied and no row matches itself, so the join yields precisely 28 rows.

   4. D. 8

      8 assumes the join pairs each employee with a single counterpart, as an equijoin on a unique key would. A non-equijoin on an inequality has no such one-to-one structure: a row matches every other row whose salary is strictly lower, so most rows contribute several output rows, not one.

   Explanation

   A join condition may use any predicate, not just equality; an inequality such as e.salary > h.salary is a non-equijoin. Over n distinct values a strict greater-than is satisfied by exactly one ordering of each of the C(n,2) unordered pairs, giving n(n-1)/2 rows. With 8 distinct salaries that is 28, with no self-matches because a value is never strictly greater than itself.

7. Question 7

   A developer needs a query that lists every employee's first name and salary alongside the first name of that employee's direct manager. Employees who report to no one — those whose `manager_id` is NULL — must also appear in the result, with NULL in the manager column. Which query correctly satisfies this requirement?

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

      LEFT JOIN keeps every row from the left table (e, all 8 employees). When e.manager_id IS NULL or matches no emp_id in m, the right-side columns are NULL-filled. Alice and Grace — whose manager_id is NULL — appear with NULL in the manager column, satisfying the requirement.

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

      INNER JOIN requires a matching row on both sides of the predicate. Employees whose manager_id IS NULL — Alice and Grace — find no matching emp_id in m and are silently excluded from the result, violating the requirement that all employees appear.

   3. C. SELECT e.first_name AS employee, e.salary, m.first_name AS manager FROM employees e RIGHT JOIN employees m ON e.manager_id = m.emp_id

      RIGHT JOIN preserves every row in the right table (m), making every employee appear in the manager role. The result shows who reports to each person rather than what manager each person reports to — the relationship is inverted — and returns 11 rows with a completely different meaning from the requirement.

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

      The ON clause is reversed: m.manager_id = e.emp_id matches rows where m reports to e — each employee's direct subordinates — not the manager of e. LEFT JOIN is syntactically present, but the inverted predicate makes the 'manager' column show each employee's direct reports rather than their own manager.

   Explanation

   In a self-join that looks up a parent row from the same table, the ON predicate must link the child's foreign key to the parent's primary key (child.manager_id = parent.emp_id). LEFT JOIN is required — not INNER JOIN — because employees at the top of the hierarchy carry a NULL manager_id that matches nothing, causing those rows to be silently dropped by an inner join. Reversing the join direction swaps which side is preserved; reversing the ON predicate retrieves each employee's subordinates instead of their manager.

8. Question 8

   Each employee belongs to exactly one department. The relevant columns of `employees` are shown below: | emp_id | first_name | dept_id | |--------|------------|---------| | 100 | Alice | 10 | | 101 | Bob | 20 | | 102 | Carol | 20 | | 103 | Dave | 20 | | 104 | Eve | 30 | | 105 | Frank | 30 | | 106 | Grace | 30 | | 107 | Heidi | 10 | How many rows does the following self-join return? ```sql SELECT a.first_name, b.first_name FROM employees a JOIN employees b ON a.dept_id = b.dept_id AND a.emp_id < b.emp_id; ```

   1. A. 14

      Represents reading the predicate as a.emp_id <> b.emp_id, which counts every pair twice (both orderings): dept 10 gives 2, dept 20 gives 6, dept 30 gives 6.

   2. B. 7Correct answer

      The join pairs two employees in the same department once, ordered by emp_id (a.emp_id < b.emp_id): dept 10 gives 1 pair (100-107), dept 20 gives C(3,2)=3, dept 30 gives 3, for 1+3+3 = 7.

   3. C. 15

      Represents reading the predicate as a.emp_id <= b.emp_id, which adds the 8 self-pairs (each employee joined to itself) to the 7 genuine pairs.

   4. D. 22

      Represents dropping the emp_id comparison entirely and joining on a.dept_id = b.dept_id alone: every ordered pair within a department, self-pairs included, is 2^2 + 3^2 + 3^2 = 22.

   Explanation

   A self-join joins employees to a second alias of the same table; the ON condition decides which pairs survive. Equating dept_id groups colleagues, and the a.emp_id < b.emp_id half-open condition keeps each unordered colleague pair exactly once while excluding an employee paired with itself. Relaxing that comparison to <>, <=, or removing it changes which combinations qualify and therefore the count.