Relational Database Concepts practice questions

Worked examples for Relational Database Concepts

1. Question 1

   The COMMISSION column of EMPLOYEES is optional: an employee with no commission plan stores no value at all in that column. ``` LAST_NAME COMMISSION --------- ---------- King (null) Chen 0.1 Diaz 0.15 Novak (null) Osei 0.05 Petrov (null) Quinn 0.2 Rossi (null) ``` How many rows does the following query return? ```sql SELECT last_name FROM employees WHERE commission <> 0.10; ```

   1. A. 7

      Treats the absent value as a value that is trivially different from 0.10, adding the four commission-less rows to the three real matches. A comparison against NULL is UNKNOWN, never TRUE.

   2. B. 4

      Confuses "not equal to 0.10" with "has a commission at all", keeping every row that stores a value including Chen's 0.10. The inequality still excludes the row whose value equals the literal.

   3. C. 3Correct answer

      Only the four rows that hold a value can be compared: 0.15, 0.05 and 0.2 satisfy the inequality and 0.1 does not, while each NULL row evaluates to UNKNOWN and is discarded — three rows.

   4. D. 0

      Assumes that a NULL anywhere in the column makes the predicate UNKNOWN for the whole table. The condition is evaluated independently per row, so rows holding real values are unaffected by the NULLs in other rows.

   Explanation

   In the relational model a column that holds no value stores NULL, which represents an absent or unknown value rather than a particular one. Any comparison operator applied to NULL yields UNKNOWN, and a WHERE clause returns a row only when its condition evaluates to TRUE, so rows with no value are dropped by an inequality just as they are by an equality test. Retrieving them requires the IS NULL operator.

2. Question 2

   An ERD models a single entity, EMPLOYEES, with a recursive one-to-many relationship named *manages*: each employee is managed by **at most one** other employee (attribute `MANAGER_ID` references `EMP_ID`), and each employee may manage **zero or more** employees. Participation is optional on both ends — some employees are managed by no one, and some manage no one. Which query returns exactly one row per employee, showing that employee's first name and the first name of the employee who manages them, with a NULL manager name for an employee who is managed by no one?

   1. A. SELECT e.first_name AS employee, m.first_name AS manager FROM employees e JOIN employees m ON e.manager_id = m.emp_id

      Treats optional participation as mandatory. An inner join keeps only rows that satisfy the join condition, and `NULL = m.emp_id` is UNKNOWN for every candidate row, so employees whose MANAGER_ID is NULL are dropped entirely instead of appearing with a NULL manager name.

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

      Preserves the wrong side of the optional relationship. RIGHT OUTER JOIN null-extends the left (child) side, so it emits an extra row for every employee who manages nobody — a row with a NULL *employee* name and a non-NULL manager name — instead of one row per employee.

   3. C. SELECT e.first_name AS employee, m.first_name AS manager FROM employees e LEFT OUTER JOIN employees m ON e.manager_id = m.emp_idCorrect answer

      The child end of the relationship (the employee holding MANAGER_ID) is the preserved table, so LEFT OUTER JOIN returns every row of `e` and supplies NULLs for `m`'s columns when the join condition finds no match — exactly one row per employee, NULL manager name when MANAGER_ID is NULL.

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

      Traverses the foreign key backwards: the condition matches rows where `m` reports to `e`, so the aliased `manager` column actually holds subordinates. An employee who manages several people yields several rows, breaking the one-row-per-employee requirement.

   Explanation

   In an ERD, a relationship line becomes a join condition between the foreign-key attribute and the primary-key attribute it references, and the *direction* of that reference decides which alias plays the child role. Optional participation on the child end means the FK may be NULL, and because a NULL never satisfies an equality predicate, only an outer join that preserves the child table keeps those instances; the preserved table must be the one whose rows must all appear. A recursive relationship is handled identically — the same table is aliased twice so that one alias is the child and the other is the parent.

3. Question 3

   The ERD models a recursive MANAGES relationship on EMPLOYEES: MANAGER_ID points at EMP_ID in the same entity, and participation is optional — a top-level employee has no manager, so the attribute holds no value. Which query lists the LAST_NAME of exactly those employees that do not participate as a child in that relationship?

   1. A. SELECT last_name FROM employees WHERE manager_id IS NULLCorrect answer

      An optional relationship is recorded as a NULL foreign key, and IS NULL is the only condition that tests for the absence of a value, so this returns precisely the employees with no manager.

   2. B. SELECT last_name FROM employees WHERE manager_id = NULL

      Assumes = NULL tests for absence of a value. Comparing anything with NULL using an equality operator yields UNKNOWN, never TRUE, so the WHERE clause rejects every row and no rows are returned.

   3. C. SELECT e.last_name FROM employees e JOIN employees m ON e.manager_id = m.emp_id WHERE m.manager_id IS NULL

      Applies the IS NULL test one level up the recursive relationship: it returns the employees whose manager has no manager, not the employees who have no manager themselves.

   4. D. SELECT last_name FROM employees WHERE manager_id IS NOT NULL

      Reads the optional relationship backwards, returning the employees that DO participate as a child — the complement of the requested set.

   Explanation

   Optional participation in a relationship is represented by a foreign key column that holds no value, and in SQL the absence of a value is NULL. Because NULL is unknown rather than a value, it cannot be matched with an equality comparison; only the IS NULL condition detects it. Testing the wrong end of a recursive relationship, or testing for a value that is present, answers a different question entirely.

4. Question 4

   In the ERD, DEPARTMENTS and EMPLOYEES are joined by the foreign key EMPLOYEES.DEPT_ID → DEPARTMENTS.DEPT_ID. A department may exist with no employees, so participation on the DEPARTMENTS side is optional. Assume every EMPLOYEES row has a non-null DEPT_ID. Which TWO queries return the DEPT_NAME of exactly those departments that currently have no employees assigned?

   1. A. SELECT d.dept_name FROM departments d JOIN employees e ON d.dept_id = e.dept_id WHERE e.emp_id IS NULL

      Assumes an inner join preserves parent rows that have no matching child. An inner join returns only matched pairs, so no row can survive with a NULL EMP_ID and the query returns nothing.

   2. B. SELECT d.dept_name FROM departments d LEFT JOIN employees e ON d.dept_id = e.dept_id WHERE e.emp_id IS NULLCorrect answer

      A LEFT OUTER JOIN preserves every DEPARTMENTS row and null-extends the EMPLOYEES columns when no match exists; testing the child's NOT NULL primary key with IS NULL isolates exactly those unmatched parents.

   3. C. SELECT d.dept_name FROM departments d LEFT JOIN employees e ON d.dept_id = e.dept_id WHERE e.dept_id IS NOT NULL

      Inverts the anti-join test: it keeps only the matched rows and discards the null-extended ones, so it returns the departments that DO have employees, once per employee.

   4. D. SELECT dept_name FROM departments WHERE dept_id NOT IN (SELECT dept_id FROM employees)Correct answer

      NOT IN is a valid anti-join here because the stem guarantees the subquery returns no NULL DEPT_ID; with a NULL-free list it evaluates TRUE exactly for departments absent from EMPLOYEES.

   Explanation

   Optional participation on the parent side of an ERD relationship is expressed in SQL as an anti-join: keep every parent row, then retain only those with no matching child. An outer join followed by an IS NULL test on a NOT NULL child column does this, and a NULL-free NOT IN subquery expresses the same set. An inner join can never surface an unmatched parent, and testing the child key for NOT NULL after an outer join asks the opposite question.

5. Question 5

   `departments.dept_id` is the primary key, and `employees.dept_id` is a foreign key declared as `CONSTRAINT emp_dept_fk FOREIGN KEY (dept_id) REFERENCES departments (dept_id)` — no delete rule and no other options are specified. The current data is: ``` DEPARTMENTS EMPLOYEES (dept_id per row) dept_id dept_name location 10, 20, 20, 20, 30, 30, 30, 10 ------- --------------- ---------- 10 Administration New York -> 2 employees reference dept 10 20 Engineering San Jose -> 3 employees reference dept 20 30 Sales Chicago -> 3 employees reference dept 30 40 Research (null) -> 0 employees reference dept 40 ``` No value 50 exists in either table. What happens when the following statement is executed? ```sql UPDATE departments SET dept_id = 50 WHERE dept_id = 30 ```

   1. A. ORA-00001: unique constraint violated

      Assumes a primary key value can never be changed once rows exist. The primary key's unique index only rejects a duplicate, and 50 is not already present, so uniqueness is not what stops this statement.

   2. B. 1 row is updated, and the three employees rows in that department are automatically changed to dept_id 50

      Assumes ON UPDATE CASCADE semantics. Oracle's FOREIGN KEY clause supports referential actions only on delete (ON DELETE CASCADE / SET NULL); there is no ON UPDATE action, so a parent key change is never propagated to children.

   3. C. ORA-02292: integrity constraint (EMP_DEPT_FK) violated - child record foundCorrect answer

      Changing the parent key would orphan the existing child rows that reference dept 30. Oracle has no ON UPDATE referential action at all — only ON DELETE CASCADE/SET NULL exist — so updating a parent key that dependent rows still reference is always rejected regardless of any delete rule, and Oracle raises ORA-02292.

   4. D. ORA-02291: integrity constraint (EMP_DEPT_FK) violated - parent key not found

      Reverses which side of the relationship is being violated. ORA-02291 is raised when a child row supplies a foreign key value that has no matching parent; here the failing statement modifies the parent table while children still point at the old value.

   Explanation

   A foreign key requires every non-null referencing value to match an existing parent key at statement end. Changing a parent key value is therefore constrained exactly like deleting the parent row: with no referential action declared, the constraint behaves restrictively and the operation is rejected while dependent rows still reference the old value. Oracle supports referential actions only for deletes, so a parent-key update is never cascaded down to the children, and the two integrity errors are distinguished by which table the offending statement modifies.

6. Question 6

   The ERD shows one DEPARTMENTS row related to many EMPLOYEES rows through the foreign key EMPLOYEES.DEPT_ID. The EMPLOYEES table holds 8 rows and the DEPARTMENTS table holds 4 rows. A developer names both entities in the FROM clause but writes no join condition: How many rows does this query return? ```sql SELECT e.last_name, d.dept_name FROM employees e, departments d ```

   1. A. 4

      Assumes the cardinality of the result follows the "one" side of the ERD relationship. Result cardinality comes from the join actually written in the query, not from the relationship drawn in the diagram.

   2. B. 8

      Assumes the declared foreign key implicitly supplies the join condition, giving one row per employee. A foreign key constrains the data; it never adds a join predicate to a query.

   3. C. 12

      Adds the two row counts, treating a comma in the FROM clause as a set union of the two entities rather than a product of them.

   4. D. 32Correct answer

      With no join condition the query produces a Cartesian product: every row of one table is paired with every row of the other, so 8 × 4 = 32 rows are returned.

   Explanation

   A relationship on an ERD is enforced by a foreign key constraint, but it does not travel into a query on its own — SQL only combines rows the way the statement says to. When the FROM clause lists two tables and nothing relates them, the result is a Cartesian product whose cardinality is the product of the two row counts. The join predicate must be written explicitly for the diagram's relationship to shape the result.

7. Question 7

   DEPARTMENTS is the parent table (`dept_id` is its primary key) and EMPLOYEES is the child table (`dept_id` is its foreign key). Which query lists the name of every department that no employee currently references?

   1. A. SELECT d.dept_name FROM departments d JOIN employees e ON d.dept_id = e.dept_id WHERE e.emp_id IS NULL

      Expects an inner join to keep unmatched parent rows. An inner join returns only rows that satisfy the join condition, so no NULL-extended employee row is ever produced and the IS NULL test can never be true.

   2. B. SELECT DISTINCT d.dept_name FROM departments d LEFT JOIN employees e ON d.dept_id = e.dept_id

      Assumes an outer join by itself filters down to the unmatched rows. A left outer join preserves *all* left-table rows — matched and unmatched alike — so this returns every department, not just the unreferenced one.

   3. C. SELECT dept_name FROM departments d WHERE EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id)

      Inverts the existence test. EXISTS keeps a department precisely when at least one employee references it, which is the complement of the departments being asked for.

   4. D. SELECT d.dept_name FROM departments d LEFT JOIN employees e ON d.dept_id = e.dept_id WHERE e.emp_id IS NULLCorrect answer

      The left outer join keeps every department and fills the employee columns with NULLs where no child row matches; testing a NOT NULL child column for IS NULL therefore isolates exactly the departments with no referencing employee.

   Explanation

   A foreign key permits a parent row to exist with no children, so finding unreferenced parents means finding rows that an inner join would silently discard. The standard anti-join preserves all parent rows with an outer join and then keeps only those whose child-side columns came back NULL-extended; the column tested must be one that is never NULL in a real child row, such as the child's primary key.

8. Question 8

   The two entities of the schema hold exactly the rows shown below. ``` EMPLOYEES DEPARTMENTS EMP_ID DEPT_ID DEPT_ID DEPT_NAME ------ ------- ------- -------------- 100 10 10 Administration 101 20 20 Engineering 102 20 30 Sales 103 20 40 Research 104 30 105 30 106 30 107 10 ``` The SELECT list projects an attribute of the EMPLOYEES entity. How many rows does the following query return? ```sql SELECT dept_id FROM employees ```

   1. A. 3

      Treats SQL projection as the relational-algebra operator π, which is defined on sets and eliminates duplicates. A SQL result is a multiset: duplicate rows are retained unless DISTINCT is written explicitly, so collapsing 10, 20 and 30 to one row each is wrong.

   2. B. 8Correct answer

      The FROM clause supplies the rows and the SELECT list only chooses which attributes of each row to project, so one row of EMPLOYEES yields one row of output — eight rows, including the repeated DEPT_ID values.

   3. C. 4

      Assumes that projecting a foreign-key column returns one row per instance of the referenced parent entity, i.e. one row per department. The FROM clause names EMPLOYEES, so DEPARTMENTS never determines the cardinality of the result.

   4. D. 9

      Reads a foreign-key column in the SELECT list as though it implicitly traversed the ERD relationship like an outer join, adding a row for the department (40, Research) that no employee references. Naming a foreign-key column performs no join; only the FROM clause can bring DEPARTMENTS into the query.

   Explanation

   SQL maps ERD components onto clauses: the FROM clause names the entity whose instances become candidate rows, the WHERE clause restricts which instances survive, and the SELECT list projects the attributes of each surviving row. Because projection selects columns and never rows, the row count of a query over a single table with no filter equals that table's row count, duplicates included, since SQL results are multisets and only DISTINCT removes duplicates. Mentioning a foreign-key column does not join to the parent entity — a join requires the parent table in FROM.