Using DDL to Manage Tables and Constraints practice questions

Worked examples for Using DDL to Manage Tables and Constraints

1. Question 1

   You are preparing to add a CHECK constraint to the EMPLOYEES table: ``` ALTER TABLE employees ADD CONSTRAINT emp_comm_ck CHECK (commission BETWEEN 0.10 AND 0.20); ``` COMMISSION is a nullable NUMBER(4,2) column and some rows currently hold NULL. Adding the constraint validates every existing row, and the statement fails with ORA-02293 if any existing row would violate it. Before running the ALTER TABLE, you want a report of exactly which existing rows would make it fail. Which query returns the EMP_ID of every row — and only those rows — that would cause the ALTER TABLE to fail?

   1. A. SELECT emp_id FROM employees WHERE commission NOT BETWEEN 0.10 AND 0.20 ORDER BY emp_idCorrect answer

      NOT BETWEEN is TRUE only for a non-NULL commission outside the inclusive range 0.10..0.20 — exactly the rows for which the CHECK condition evaluates to FALSE. Rows with a NULL commission yield UNKNOWN for both the constraint and this predicate, so they are correctly left out.

   2. B. SELECT emp_id FROM employees WHERE commission NOT BETWEEN 0.10 AND 0.20 OR commission IS NULL ORDER BY emp_id

      Treats an UNKNOWN condition as a violation. A CHECK constraint rejects a row only when its condition evaluates to FALSE; with a NULL commission the condition is UNKNOWN, which satisfies the constraint, so these NULL rows are reported as offenders when they are not.

   3. C. SELECT emp_id FROM employees WHERE commission <= 0.10 OR commission >= 0.20 ORDER BY emp_id

      Reads BETWEEN as exclusive. BETWEEN 0.10 AND 0.20 expands to 0.10 <= commission AND commission <= 0.20, so the endpoint values 0.10 and 0.20 satisfy the constraint; this query wrongly flags rows sitting exactly on the boundaries.

   4. D. SELECT emp_id FROM employees WHERE commission IS NULL ORDER BY emp_id

      Confuses CHECK with NOT NULL. A CHECK constraint does not reject NULLs unless its condition explicitly says so (for example, IS NOT NULL); NULL commissions pass this constraint, while genuinely out-of-range values are missed entirely.

   Explanation

   When a CHECK constraint is added, Oracle validates existing rows and rejects only those for which the condition evaluates to FALSE — a condition that evaluates to UNKNOWN because of a NULL is treated as satisfied. BETWEEN is inclusive of both endpoints, expanding to lower <= expr AND expr <= upper, so boundary values comply. The pre-check query must therefore select non-NULL values outside the inclusive range and nothing else.

2. Question 2

   The `departments` table declares its optional column as `location VARCHAR2(30)`: ```sql CREATE TABLE departments ( dept_id NUMBER(4) PRIMARY KEY, dept_name VARCHAR2(30) NOT NULL, location VARCHAR2(30) ); ``` The table holds four rows. Three were loaded with a non-empty location string, and exactly one row was loaded with no location value at all. Which query returns exactly that one row's `dept_id`, and nothing else?

   1. A. SELECT dept_id FROM departments WHERE location IS NULLCorrect answer

      IS NULL is the only test that is TRUE for a column holding no value. Because a VARCHAR2 column with no value is NULL — whether it was never supplied or was supplied as '' — this returns exactly the single unlocated department's dept_id (SQL Language Reference, Nulls).

   2. B. SELECT dept_id FROM departments WHERE location = ''

      Treats '' as a zero-length string value distinct from NULL. Oracle stores a zero-length character value as NULL, so this predicate is `location = NULL`, which evaluates to UNKNOWN for every row and returns no rows at all.

   3. C. SELECT dept_id FROM departments WHERE location LIKE '%'

      Assumes the % wildcard, matching zero or more characters, also matches a column with no value. A LIKE comparison whose operand is NULL evaluates to UNKNOWN, so this returns the three located departments — the exact complement of what was asked.

   4. D. SELECT dept_id FROM departments WHERE LENGTH(location) = 0

      Assumes an absent VARCHAR2 value has length 0. LENGTH of a NULL argument returns NULL, not 0, so the comparison is UNKNOWN and no rows are returned.

   Explanation

   Oracle does not distinguish a zero-length character value from NULL: a VARCHAR2 column that holds no value is NULL, and every comparison operator applied to NULL — `=`, `<>`, LIKE — yields UNKNOWN rather than TRUE. Functions such as LENGTH also propagate NULL instead of returning 0. That makes the IS NULL predicate the only way to select rows whose character column holds no value, which is why an optional VARCHAR2 column can never be probed with an empty-string literal.

3. Question 3

   The `EMPLOYEES` table holds exactly the eight rows below (only the relevant columns are shown): ``` EMP_ID SALARY DEPT_ID ------ ------ ------- 100 9000 10 101 6000 20 102 7500 20 103 4800 20 104 5200 30 105 3900 30 106 8100 30 107 6700 10 ``` A session with no prior uncommitted work runs the statements below in order. How many rows does the **final** `DELETE` statement remove? ```sql DELETE FROM employees WHERE dept_id = 20; SAVEPOINT sp1; DELETE FROM employees WHERE dept_id = 30; ROLLBACK TO SAVEPOINT sp1; DELETE FROM employees WHERE salary > 5000; ```

   1. A. 2

      Reflects the misconception that rows removed by DELETE are gone for good — treating DELETE like TRUNCATE, which is DDL, commits implicitly, and cannot be undone. Under that reading both departments 20 and 30 stay empty, leaving only employees 100 and 107 above 5000. DELETE is DML: everything it removes is undo-protected until the transaction ends.

   2. B. 6

      Treats `ROLLBACK TO SAVEPOINT sp1` as a full `ROLLBACK`, restoring department 20's rows as well and giving all eight rows back (100, 101, 102, 104, 106, 107 exceed 5000). Rolling back to a savepoint discards only the work done *after* that savepoint; the first DELETE precedes sp1 and survives.

   3. C. 4Correct answer

      `ROLLBACK TO SAVEPOINT sp1` undoes only the second DELETE, so employees 104, 105, and 106 are back while 101, 102, and 103 remain deleted. The surviving rows are 100 (9000), 104 (5200), 105 (3900), 106 (8100), and 107 (6700); of these, four have salary > 5000 — 100, 104, 106, and 107.

   4. D. 7

      Reports the transaction's running total of removed rows (3 from the first DELETE plus 4 from the last) instead of the count reported by the final statement alone. Each DML statement reports only the rows it itself changed; prior uncommitted deletes in the same transaction are not re-counted.

   Explanation

   DELETE is DML: every row it removes is protected by undo until the transaction ends, so a ROLLBACK — full or to a savepoint — puts those rows back and they become visible to later statements in the same transaction. Rolling back to a savepoint discards only the work performed after that savepoint, leaving earlier statements in the transaction intact, so exactly one of the two deletes is reversed here. TRUNCATE TABLE behaves nothing like this: it is DDL, issues an implicit commit, generates no per-row undo, and cannot be reversed by ROLLBACK or partially reversed by a savepoint. Each DML statement also reports only the rows it changed, not the transaction's cumulative total.

4. Question 4

   You are preparing to add a self-referencing foreign key to EMPLOYEES: ``` ALTER TABLE employees ADD CONSTRAINT emp_mgr_fk FOREIGN KEY (manager_id) REFERENCES employees (emp_id); ``` EMP_ID is the primary key of EMPLOYEES, so it is unique and can never be NULL. MANAGER_ID is a nullable NUMBER(6) column, and several rows currently hold NULL in it. Oracle validates every existing row when the constraint is created and raises ORA-02298 if any row fails validation. Before running the ALTER TABLE you want a report of exactly which existing rows would make it fail. Which query returns the EMP_ID of every row that would cause the ALTER TABLE to fail, and only those rows?

   1. A. SELECT e.emp_id FROM employees e WHERE NOT EXISTS (SELECT 1 FROM employees m WHERE m.emp_id = e.manager_id)

      Treats NOT EXISTS as interchangeable with NOT IN. For a row whose MANAGER_ID is NULL the correlated predicate m.emp_id = NULL is UNKNOWN for every candidate row, so the subquery returns no rows and NOT EXISTS is TRUE. This query therefore reports every NULL-manager row as an offender, even though a NULL foreign key value satisfies the constraint.

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

      Applies the IS NOT NULL filter to the wrong side. The NULL problem lives in the child column MANAGER_ID, not in the parent list, so this filter instead shrinks the set of valid parent keys down to employees who themselves have a manager. Legitimate parent keys are dropped from the list and rows that reference them are wrongly reported as violations.

   3. C. SELECT emp_id FROM employees WHERE manager_id NOT IN (SELECT emp_id FROM employees)Correct answer

      Correct on both counts. The subquery selects the primary key EMP_ID, which can never be NULL, so NOT IN is safe here and is FALSE exactly when a matching parent key exists. For a row whose MANAGER_ID is NULL the comparison is UNKNOWN, so that row is not returned — matching Oracle's rule that a NULL foreign key value always satisfies a FOREIGN KEY constraint.

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

      Assumes a FOREIGN KEY column implicitly requires a value. FOREIGN KEY does not imply NOT NULL: a row whose foreign key is NULL satisfies the referential constraint, so adding the IS NULL branch reports rows that would validate cleanly.

   Explanation

   A FOREIGN KEY constraint is satisfied by any row whose foreign key value is NULL — referential integrity is only enforced for non-NULL values — so a pre-validation query must exclude NULL foreign keys from its violation report. NOT IN does that for free, because a NULL left operand makes the comparison UNKNOWN and the row is filtered out, and NOT IN is safe against a parent list built from a primary key column, which can contain no NULLs. NOT EXISTS behaves differently: a NULL correlation value matches nothing, so NOT EXISTS is TRUE and the NULL-foreign-key row is wrongly flagged.

5. Question 5

   The schema below is in place, `emp_dept_fk` is ENABLED, and no other user is holding a lock on either table: ```sql CREATE TABLE departments ( dept_id NUMBER(4) PRIMARY KEY, dept_name VARCHAR2(30) NOT NULL, location VARCHAR2(30) ); CREATE TABLE employees ( emp_id NUMBER(6) PRIMARY KEY, first_name VARCHAR2(30) NOT NULL, last_name VARCHAR2(30) NOT NULL, salary NUMBER(8, 2), commission NUMBER(4, 2), manager_id NUMBER(6), dept_id NUMBER(4), hire_date DATE, CONSTRAINT emp_dept_fk FOREIGN KEY (dept_id) REFERENCES departments (dept_id) ); ``` What is the result of executing the following statement? ```sql ALTER TABLE departments DROP COLUMN dept_id ```

   1. A. The statement fails with ORA-12992: cannot drop parent key column.Correct answer

      DEPT_ID is the parent key of the enabled foreign key EMP_DEPT_FK in EMPLOYEES. Dropping (or setting unused) a parent key column without CASCADE CONSTRAINTS raises ORA-12992; `ALTER TABLE departments DROP COLUMN dept_id CASCADE CONSTRAINTS` would succeed by dropping the dependent foreign key too.

   2. B. The column is dropped and `emp_dept_fk` is dropped automatically along with the primary key it depends on.

      Assumes DROP COLUMN implicitly cascades to dependent constraints. ALTER TABLE ... DROP COLUMN drops only constraints defined *on the dropped column itself*; a referential constraint in another table that depends on it is not removed unless CASCADE CONSTRAINTS is specified, and without that clause the statement is rejected rather than silently cascading.

   3. C. The statement fails with ORA-02449: unique/primary keys in table referenced by foreign keys.

      Conflates the DROP TABLE error with the DROP COLUMN error. ORA-02449 is raised by DROP TABLE (and by dropping the constraint) when a table's key is referenced by a foreign key; dropping the parent key *column* raises the column-specific ORA-12992 instead.

   4. D. The statement fails with ORA-12991: column is referenced in a multi-column constraint.

      Assumes any constraint involvement produces ORA-12991. That error is reserved for a column that participates in a *multi-column* constraint; DEPT_ID's primary key is single-column, so the parent-key rule applies instead.

   Explanation

   ALTER TABLE ... DROP COLUMN removes only the constraints defined on the column being dropped. When the column is the parent key of an enabled referential constraint in another table, Oracle refuses the drop because honouring it would leave a foreign key with no parent, and CASCADE CONSTRAINTS is the clause that authorises dropping those dependent constraints. The same parent-key restriction applies to SET UNUSED, since an unused column is logically gone even though its data still occupies space.

6. Question 6

   In the HR schema, `EMPLOYEES.DEPT_ID` carries an **enabled** foreign key (`emp_dept_fk`) that references `DEPARTMENTS.DEPT_ID`, and department 20 currently has three employee rows referencing it. A DBA who owns both tables runs the statement below in SQL*Plus. What is the result? ```sql TRUNCATE TABLE departments WHERE dept_id = 20; ```

   1. A. ORA-02266: unique/primary keys in table referenced by enabled foreign keys

      This is the error a *bare* `TRUNCATE TABLE departments` would raise, and it is a real rule — TRUNCATE is blocked while a child table has an enabled referential constraint pointing at the table. But that check happens at execution time; this statement never reaches execution because the parser rejects the WHERE clause first. Choosing it means assuming a filtered TRUNCATE is valid syntax.

   2. B. ORA-02292: integrity constraint (HR.EMP_DEPT_FK) violated - child record found

      This is the row-level error `DELETE FROM departments WHERE dept_id = 20` would raise. Picking it means treating TRUNCATE as a filtered DELETE that enforces the foreign key row by row; TRUNCATE never evaluates individual child rows — it refuses outright while the constraint is enabled — and here the statement fails to parse in any case.

   3. C. ORA-00933: SQL command not properly ended

      ORA-00933 is the trailing-token error raised by a completed DROP TABLE or a DML statement with stray text after it. TRUNCATE behaves differently: after the table name it expects only its documented options, so it parses the unexpected WHERE as a truncate option and, finding it invalid, raises ORA-03291 rather than ORA-00933.

   4. D. ORA-03291: invalid TRUNCATE optionCorrect answer

      After the table name, TRUNCATE TABLE admits only its documented options — {PRESERVE|PURGE} MATERIALIZED VIEW LOG, {DROP [ALL]|REUSE} STORAGE, and CASCADE. Oracle parses the unexpected WHERE as a truncate option and, finding it invalid, raises ORA-03291 (invalid TRUNCATE option). Nothing is removed; TRUNCATE cannot filter rows — that is DELETE's job, and its parse-time rejection precedes any foreign-key check.

   Explanation

   TRUNCATE TABLE is a DDL statement whose grammar has no filtering predicate: it removes all rows or nothing at all, which is why it can reset the high-water mark and reclaim storage without generating per-row undo. After the table name it admits only the materialized-view-log, storage, and CASCADE options, so an appended WHERE is parsed as an invalid truncate option and rejected with ORA-03291 — before the enabled foreign key or any child row is ever consulted. Selective removal is the job of DELETE, which is DML, evaluates a predicate row by row, enforces referential integrity per row, and can be rolled back.

7. Question 7

   EMPLOYEES was created with exactly these constraint clauses — MANAGER_ID carries no constraint of any kind, and no table anywhere references EMPLOYEES: ``` emp_id NUMBER(6) PRIMARY KEY first_name VARCHAR2(30) NOT NULL last_name VARCHAR2(30) NOT NULL manager_id NUMBER(6) dept_id NUMBER(4) CONSTRAINT emp_dept_fk FOREIGN KEY (dept_id) REFERENCES departments (dept_id) ``` The reporting chain stored in the table: ``` EMP_ID LAST_NAME MANAGER_ID ------ --------- ---------- 100 King NULL 101 Chen 100 102 Diaz 100 103 Novak 101 104 Osei 106 105 Petrov 106 106 Quinn NULL 107 Rossi 100 ``` How many rows does the following statement delete? ```sql DELETE FROM employees WHERE emp_id = 100 ```

   1. A. 0

      Assumes MANAGER_ID is a self-referencing foreign key with the default (restrict) delete rule, so the three rows carrying MANAGER_ID 100 would raise ORA-02292. No such constraint is declared, so nothing blocks the delete.

   2. B. 4

      Assumes a self-referencing foreign key declared ON DELETE CASCADE that removes only the direct reports (101, 102, 107) along with row 100. Cascading is recursive, not one level deep — and here no self-referencing constraint exists at all.

   3. C. 5

      Applies ON DELETE CASCADE correctly down the full chain (100, then 101, 102, 107, then 103 under 101) but to a constraint that was never declared; storing a manager's number in a plain NUMBER column creates no referential relationship.

   4. D. 1Correct answer

      Referential integrity applies only to columns named in a declared FOREIGN KEY clause. The one declared foreign key points from EMPLOYEES.DEPT_ID to DEPARTMENTS, and no constraint references EMPLOYEES, so the primary-key predicate removes exactly the single row for EMP_ID 100.

   Explanation

   A column is subject only to the constraints actually declared on it: MANAGER_ID here is an ordinary nullable NUMBER column, so the values it holds create no referential link back to EMP_ID and impose no restriction on deleting a row that other rows happen to name. The single declared foreign key runs from EMPLOYEES.DEPT_ID to DEPARTMENTS, which constrains deletes on the parent table rather than on EMPLOYEES, and no table references EMPLOYEES at all. Deleting by primary key therefore removes one row. Had a self-referencing foreign key been declared, omitting ON DELETE would block the delete with ORA-02292, while ON DELETE CASCADE would propagate through the entire chain of dependent rows rather than stopping at the direct reports.

8. Question 8

   EMPLOYEES was created with EMP_ID as its primary key, declared inline as `emp_id NUMBER(6) PRIMARY KEY`. No NOT NULL constraint was ever written for EMP_ID, and no other table references EMPLOYEES.EMP_ID. A row with EMP_ID 107 exists, and no other row currently holds a NULL EMP_ID. What is the result of executing the following statement? ```sql UPDATE employees SET emp_id = NULL WHERE emp_id = 107 ```

   1. A. It fails with ORA-00001: unique constraint violated

      Assumes every primary key breach reports the same code. ORA-00001 is raised only when a unique or primary key index would receive a duplicate key value; setting the column to NULL creates no duplicate, so the mandatory-column rule fires instead.

   2. B. It fails with ORA-01407: cannot update ("HR"."EMPLOYEES"."EMP_ID") to NULLCorrect answer

      A primary key forbids NULLs in its columns, so EMP_ID is mandatory even without an explicit NOT NULL clause; an UPDATE that would store NULL in a mandatory column is rejected with ORA-01407 and the statement is rolled back.

   3. C. It fails with ORA-01400: cannot insert NULL into ("HR"."EMPLOYEES"."EMP_ID")

      Assumes the NULL-into-a-mandatory-column error carries one code for all DML. ORA-01400 is raised by INSERT; an UPDATE that would place a NULL in a NOT NULL column raises the distinct code ORA-01407.

   4. D. It succeeds and updates 1 row, because EMP_ID was never declared NOT NULL

      Assumes NOT NULL must be spelled out to apply. A PRIMARY KEY constraint implicitly makes every one of its columns mandatory — Oracle marks EMP_ID as nullable = 'N' — so the update is rejected even though no NOT NULL clause was written.

   Explanation

   Declaring a column as PRIMARY KEY does two things at once: it enforces uniqueness through a unique index and it makes every key column mandatory, so an explicit NOT NULL clause is redundant rather than required. Attempting to store a NULL in such a column is a mandatory-column violation, not a uniqueness violation, and Oracle distinguishes the DML that caused it — INSERT reports ORA-01400 while UPDATE reports ORA-01407.