Relational Database Concepts practice questions

Worked examples for Relational Database Concepts

1. Question 1

   In `hr-mini`, `EMPLOYEES.MANAGER_ID` is a self-referencing foreign key: it holds the `EMP_ID` of that employee's manager, and is NULL for employees who report to no one. Which query returns the FIRST_NAME of every employee who is **not** the manager of any other employee — that is, no employee has that person's EMP_ID as their MANAGER_ID?

   1. A. SELECT first_name FROM employees WHERE emp_id NOT IN (SELECT manager_id FROM employees)

      Classic NOT IN with a NULL in the value list. Because some MANAGER_ID values are NULL, `emp_id NOT IN (…, NULL)` can never evaluate to TRUE — it is UNKNOWN for every row — so the query returns zero rows instead of the non-managers.

   2. B. SELECT first_name FROM employees e WHERE NOT EXISTS (SELECT 1 FROM employees m WHERE m.manager_id = e.emp_id)Correct answer

      NOT EXISTS is evaluated row by row and treats an unmatched correlated subquery as simply 'no matching child'. It correctly keeps every employee whose EMP_ID never appears as another row's MANAGER_ID, and the NULL manager_ids in the subquery do not sabotage it, so it returns exactly the non-managers.

   3. C. SELECT first_name FROM employees WHERE emp_id IN (SELECT manager_id FROM employees)

      Inverts the requirement. IN keeps employees whose EMP_ID DOES appear as a MANAGER_ID, so this returns the managers themselves rather than the employees who manage no one.

   4. D. SELECT first_name FROM employees WHERE manager_id IS NULL

      Confuses 'is not a manager' with 'has no manager'. MANAGER_ID IS NULL selects employees who report to no one (top of the hierarchy), which is a different set from employees whom no one reports to.

   Explanation

   Finding rows on one side of a self-referencing foreign key that are never pointed at from the other side is an anti-join, and the safe way to express it is NOT EXISTS (or an outer join with an IS NULL test). NOT IN is a trap whenever the subquery can yield NULL, because a single NULL in the list makes the NOT IN predicate UNKNOWN for every row and the query returns nothing. Selecting on MANAGER_ID directly answers the opposite question — who has no manager — rather than who is nobody's manager.

2. Question 2

   A session has an uncommitted `INSERT` still pending. Which one of the following statements, if run next, would leave that insert still pending (uncommitted) rather than forcing it to be committed?

   1. A. `TRUNCATE TABLE archive;`

      This is the 'TRUNCATE is just another DML that leaves work pending' misconception. TRUNCATE is a DDL statement and issues an implicit commit, so it would make the pending INSERT permanent instead of leaving it uncommitted.

   2. B. `ALTER TABLE orders ADD (note VARCHAR2(20));`

      This assumes ALTER TABLE does not commit. ALTER is a DDL statement carrying an implicit commit, so it would persist the pending INSERT rather than leaving it pending.

   3. C. `UPDATE orders SET status = 'X' WHERE id = 1;`Correct answer

      UPDATE is plain DML. It commits nothing on its own, so both it and the earlier INSERT remain pending and reversible until an explicit COMMIT (or a later implicit commit from DDL/DCL) occurs.

   4. D. `CREATE TABLE tmp (id NUMBER);`

      This assumes CREATE TABLE does not commit. CREATE is a DDL statement with an implicit commit, so running it would finalize the pending INSERT rather than leaving it uncommitted.

   Explanation

   Plain DML — INSERT, UPDATE, DELETE, MERGE — never commits itself; changes accumulate and stay reversible until an explicit COMMIT. DDL statements are the opposite: CREATE, ALTER, and TRUNCATE each fire an implicit commit that permanently saves whatever DML was pending. So the only statement here that preserves the uncommitted insert is another piece of ordinary DML, while each DDL option would silently commit it.

3. Question 3

   The MANAGER_ID column in EMPLOYEES is a self-referencing foreign key: it references EMP_ID in the same table, allowing each employee to record an optional manager who is also an employee. Which query returns the EMP_ID of every employee who manages at least one other employee?

   1. A. SELECT DISTINCT manager_id FROM employees WHERE manager_id IS NOT NULLCorrect answer

      Returns the distinct EMP_ID values that appear in another employee's MANAGER_ID column, which is exactly the set of employees who manage at least one other employee. DISTINCT prevents duplicates when one manager oversees multiple employees, and IS NOT NULL excludes the sentinel meaning 'no manager assigned'.

   2. B. SELECT emp_id FROM employees WHERE manager_id IS NOT NULL

      Confuses 'has a manager' with 'is a manager'. Filtering on an employee's own MANAGER_ID column returns the employees who have a manager on record — the subordinates — not the employees whose EMP_ID appears as someone else's manager.

   3. C. SELECT emp_id FROM employees WHERE manager_id IS NULL

      Returns employees at the top of the reporting hierarchy (those with no manager assigned), not those who manage others. A NULL MANAGER_ID means the employee has no manager recorded, not that the employee manages others.

   4. D. SELECT emp_id FROM employees WHERE emp_id NOT IN (SELECT manager_id FROM employees)

      Because MANAGER_ID contains NULL values, NOT IN evaluates to UNKNOWN for every candidate row under three-valued logic — x NOT IN (a set containing NULL) is never TRUE — so this query returns zero rows instead of any intended complement.

   Explanation

   A self-referencing foreign key links a column back to the primary key of the same table — here MANAGER_ID references EMP_ID within EMPLOYEES. To find which employees ARE managers, look for EMP_ID values that appear in the MANAGER_ID column of other rows, filtering out NULL because NULL signals 'no manager assigned'. Querying the MANAGER_ID of an employee's own row retrieves the inverse relationship: employees who have a manager. Applying NOT IN against any subquery that may return NULL silently produces zero rows because NULL propagates UNKNOWN in Oracle's three-valued comparison logic.

4. Question 4

   The DEPARTMENTS table contains the four rows shown below, and EMPLOYEES.DEPT_ID is defined as a foreign key referencing DEPARTMENTS(DEPT_ID). Which Oracle error code is raised when the following INSERT statement executes? | DEPT_ID | DEPT_NAME | LOCATION | |---------|----------------|----------| | 10 | Administration | New York | | 20 | Engineering | San Jose | | 30 | Sales | Chicago | | 40 | Research | NULL | ```sql INSERT INTO employees (emp_id, first_name, last_name, salary, dept_id) VALUES (200, 'Ian', 'Smith', 5500, 99) ```

   1. A. ORA-01400

      ORA-01400 ('cannot insert NULL into column') fires when a NOT NULL column receives a NULL value. All NOT NULL columns in EMPLOYEES (EMP_ID, FIRST_NAME, LAST_NAME) receive non-NULL values in this statement, so this error cannot occur.

   2. B. ORA-02292

      ORA-02292 ('integrity constraint violated — child record found') is raised when a DELETE or UPDATE on a parent table row is blocked by existing child rows. This INSERT operates on the child table EMPLOYEES, not on DEPARTMENTS, so ORA-02292 is never triggered by inserting into the child table.

   3. C. ORA-00001

      ORA-00001 signals a UNIQUE constraint violation — a duplicate value was supplied for a primary key or unique-indexed column. EMP_ID 200 is not already present in EMPLOYEES, so no uniqueness rule is broken; the failure here is referential, not a duplicate key.

   4. D. ORA-02291Correct answer

      ORA-02291 ('integrity constraint violated — parent key not found') is raised when a child row's foreign key value has no matching primary key in the parent table. DEPT_ID 99 does not appear in DEPARTMENTS, so Oracle blocks the INSERT with this error to preserve referential integrity.

   Explanation

   A foreign key constraint enforces referential integrity: every non-NULL value placed in the child column must match an existing primary key value in the referenced parent table. When the INSERT supplies a DEPT_ID that is absent from DEPARTMENTS, Oracle raises ORA-02291 to signal that the required parent row does not exist and rejects the statement. The related error ORA-02292 applies in the opposite direction — it blocks a DELETE or UPDATE that would orphan existing child rows by removing their referenced parent.

5. Question 5

   In Oracle Database, a developer runs `UPDATE employees SET salary = salary * 1.10 WHERE dept_id = 20;` in one session and does **not** issue a `COMMIT`. Still in the same session, the developer next runs `GRANT SELECT ON employees TO auditor;`. A moment later, realizing the raise was applied to the wrong department, the developer runs `ROLLBACK;` and nothing else. Under the four-category classification of SQL statements (DML, DDL, DCL, TCL), what is the state of the salary data afterward?

   1. A. `ROLLBACK` restores the original salaries, because `GRANT` only manages privileges and leaves an open DML transaction untouched — only DDL such as `CREATE` or `DROP` forces an implicit commit.

      This is the 'only DDL implicitly commits' misconception. DCL statements (GRANT, REVOKE) carry the same implicit COMMIT as DDL; the privilege change is not isolated from pending DML, so the UPDATE is already committed and ROLLBACK cannot restore the old salaries.

   2. B. The 10% raise is permanent: `GRANT` is a Data Control Language (DCL) statement, and like DDL it forces an implicit `COMMIT`, so the pending `UPDATE` was committed the instant the `GRANT` ran; the later `ROLLBACK` finds nothing to undo.Correct answer

      GRANT and REVOKE (DCL) trigger an implicit COMMIT before and after they execute, exactly as DDL does. That implicit commit made the uncommitted UPDATE permanent the moment the GRANT ran, so by the time ROLLBACK is issued there is no open transaction left to reverse.

   3. C. `ROLLBACK` undoes both the `UPDATE` and the `GRANT`, restoring the salaries and revoking the privilege, because everything since the last commit is a single transaction.

      This is the 'DCL is transactional and rollbackable' misconception. A GRANT is not part of the surrounding DML transaction; its own implicit commit closes that transaction, and the privilege grant itself is permanent and cannot be undone by ROLLBACK.

   4. D. The `GRANT` fails with an error because a DML transaction is still open, so the salaries stay pending and the following `ROLLBACK` then undoes the `UPDATE`.

      This is the 'a pending transaction blocks DCL' misconception. An open, uncommitted transaction does not prevent a GRANT from running; the GRANT succeeds and its implicit commit finalizes the pending UPDATE rather than raising an error.

   Explanation

   Whether a change can still be taken back depends on the category of every statement run in between, not just on the reversal at the end. Data-control statements such as GRANT and REVOKE carry an implicit commit just as data-definition statements do, so running one silently finalizes any uncommitted data-manipulation work that preceded it. Once that implicit commit fires there is no open transaction left, and a subsequent ROLLBACK has nothing to reverse — making the earlier DML permanent even though it was never explicitly committed.

6. Question 6

   In Oracle Database, a session with no transaction currently open runs a single `MERGE INTO accounts ...` that **updates** 12 existing rows and **inserts** 4 new rows, then does nothing else — no `COMMIT`, no `SAVEPOINT`. Realizing the source data was wrong, the developer next runs `ROLLBACK;`. Based on the category `MERGE` belongs to, what is the state of the `accounts` table afterward?

   1. A. Nothing is undone, because `MERGE` adds brand-new rows to the table and is therefore a Data Definition Language (DDL) operation that takes effect immediately and sits outside the transaction that `ROLLBACK` can reach.

      This is the 'MERGE inserts rows so it must be DDL' misconception. Creating rows is not schema definition; DDL acts on schema objects (tables, indexes), whereas MERGE manipulates row data and is documented as a DML statement, so its uncommitted effect is fully reversible.

   2. B. The table returns to exactly its pre-`MERGE` state: both the 4 inserted rows and the 12 updated rows are undone, because `MERGE` is a Data Manipulation Language (DML) statement whose entire effect stays uncommitted until a `COMMIT`, and `ROLLBACK` reverses all of it as one unit.Correct answer

      MERGE is classified as DML alongside INSERT, UPDATE, and DELETE. A single DML statement's row changes are atomic and remain uncommitted, so ROLLBACK reverses the complete statement — every inserted and every updated row — returning the table to its prior state.

   3. C. Only the 12 updated rows revert to their previous values; the 4 inserted rows remain, because an `INSERT` writes new rows into the table that `ROLLBACK` cannot remove.

      This is the 'inserted rows are permanent / MERGE's parts commit independently' misconception. A MERGE is one atomic DML statement, not a committed insert plus a separate update; all its uncommitted row effects — inserts included — are reversed together by ROLLBACK.

   4. D. `ROLLBACK` undoes the changes only if a `SAVEPOINT` had been set before the `MERGE`; with no savepoint in place it has nothing to reverse, so all 16 row changes stay.

      This is the 'ROLLBACK needs a SAVEPOINT to work' misconception. A bare ROLLBACK ends the current transaction and undoes every uncommitted change since the last COMMIT; a SAVEPOINT only lets you roll back to an intermediate point, it is never a prerequisite for rolling back at all.

   Explanation

   Whether a mistaken change can be taken back depends first on correctly categorizing the statement that made it. Even though a MERGE can create new rows, it manipulates data rather than defining schema objects, so it belongs to the data-manipulation category and behaves like INSERT, UPDATE, and DELETE: its full effect stays uncommitted and reversible until an explicit COMMIT. A plain ROLLBACK therefore reverses the entire statement as one unit — every row it inserted and every row it updated — with no savepoint required.

7. Question 7

   Which query correctly returns one row per employee showing the employee's FIRST_NAME and the DEPT_NAME of their department, by navigating the foreign-key relationship between EMPLOYEES and DEPARTMENTS?

   1. A. SELECT e.first_name, d.dept_name FROM employees e JOIN departments d ON e.dept_id = d.dept_idCorrect answer

      Joins EMPLOYEES to DEPARTMENTS on the column pair that defines the foreign-key relationship (EMPLOYEES.DEPT_ID → DEPARTMENTS.DEPT_ID), producing exactly one output row for each employee row that has a matching department.

   2. B. SELECT e.first_name, d.dept_name FROM employees e, departments d

      Listing two tables with a comma and no WHERE join condition produces a Cartesian product: every employee row is paired with every department row, yielding far more rows than there are employees.

   3. C. SELECT e.first_name, d.dept_name FROM employees e JOIN departments d ON e.emp_id = d.dept_id

      Joins EMPLOYEES.EMP_ID to DEPARTMENTS.DEPT_ID — columns from entirely different domains whose value ranges never overlap — so the join predicate is never satisfied and zero rows are returned.

   4. D. SELECT e.first_name, d.dept_name FROM employees e RIGHT JOIN departments d ON e.dept_id = d.dept_id

      A RIGHT OUTER JOIN includes every department row whether or not any employee references it; the Research department (DEPT_ID 40) has no employees in this table, so an extra row with NULL in FIRST_NAME is added, producing more output rows than there are employees.

   Explanation

   A foreign key declares that a column in the child table (EMPLOYEES.DEPT_ID) references the primary key of the parent table (DEPARTMENTS.DEPT_ID). The standard relational mechanism for retrieving data across that relationship is an inner join on that exact column pair, which produces one row for each matched child row. Omitting the join condition creates a Cartesian product. Joining on columns from different domains (employee surrogate keys versus department codes) produces no rows because no values coincide. A right outer join adds extra rows for parent rows that have no matching children, so the row count exceeds the number of employees.

8. Question 8

   In the DEPARTMENTS table, DEPT_ID is defined as `NUMBER(4) PRIMARY KEY`. What is the result of executing the following statement? ```sql INSERT INTO departments (dept_id, dept_name, location) VALUES (NULL, 'Marketing', 'Boston'); ```

   1. A. ORA-01400: cannot insert NULL into the DEPT_ID columnCorrect answer

      A PRIMARY KEY constraint implies NOT NULL on every key column, so supplying NULL for DEPT_ID is rejected at insert time with ORA-01400 — the row is never stored.

   2. B. ORA-00001: unique constraint violated

      Misconception: that a NULL key trips the uniqueness half of the primary key. ORA-00001 fires only when a duplicate non-NULL key collides with an existing row; here the value is missing, so the NOT NULL rule (ORA-01400) is what fails first.

   3. C. ORA-02291: integrity constraint violated - parent key not found

      Misconception: treating the primary key as a referential (foreign key) constraint. ORA-02291 is raised for a child FK value with no matching parent; DEPT_ID is the parent key itself, not a foreign key, so this code does not apply.

   4. D. The statement succeeds and inserts one new row.

      Misconception: that a primary key column may hold NULL. Because PRIMARY KEY carries an implicit NOT NULL constraint, a NULL key value can never be inserted, so the statement fails rather than adding a row.

   Explanation

   A PRIMARY KEY constraint enforces two things at once: values must be unique AND not null. When only the not-null half is broken — a NULL supplied for a key column — Oracle rejects the row with ORA-01400 (cannot insert NULL), which is distinct from the ORA-00001 raised for a duplicate value and from the ORA-02291 raised for a foreign-key parent that is missing.