Using DDL to Manage Tables and Constraints practice questions

Worked examples for Using DDL to Manage Tables and Constraints

1. Question 1

   The ORDERS table has an `AFTER DELETE ... FOR EACH ROW` trigger that inserts one row into ORDER_AUDIT for every order removed. A maintenance job empties the table with `TRUNCATE TABLE orders;` while ORDERS holds 400 rows. How many rows does the trigger write to ORDER_AUDIT?

   1. A. Zero — TRUNCATE is DDL and does not fire DML (DELETE) triggers.Correct answer

      TRUNCATE removes rows as a DDL operation, not row-by-row DML, so no DELETE trigger — row-level or statement-level — fires. The reference notes TRUNCATE does not fire the table's DELETE triggers, so ORDER_AUDIT gains no rows.

   2. B. 400 — TRUNCATE fires the AFTER DELETE row trigger once for every row it removes.

      This is the 'TRUNCATE fires DELETE triggers' misconception. Because TRUNCATE does not remove rows via DML, it fires no DELETE triggers at all; only an actual DELETE statement would trigger the per-row audit.

   3. C. 1 — TRUNCATE fires the trigger once at the statement level rather than per row.

      This is a variant of the same misconception (that TRUNCATE fires DELETE triggers, just once). TRUNCATE fires no DML triggers whatsoever, so it produces neither per-row nor statement-level audit rows.

   4. D. It raises an error because the presence of an AFTER DELETE trigger blocks TRUNCATE.

      This assumes a DELETE trigger prevents TRUNCATE. The reference imposes no such restriction; a DELETE trigger is simply bypassed, not an obstacle, so the TRUNCATE succeeds and writes zero audit rows.

   Explanation

   TRUNCATE clears a table as a DDL operation and never processes rows through the DML path, so DELETE triggers — whether row-level or statement-level — do not fire. A DELETE statement, by contrast, removes rows as DML and fires those triggers for each affected row. Any audit or cascade logic built on DELETE triggers is therefore silently skipped when a table is truncated.

2. Question 2

   A single session runs `DELETE FROM temp_stage;` and then, as a separate operation, `TRUNCATE TABLE temp_load;`. Each of the two statements is immediately followed by its own `ROLLBACK;`. No COMMIT is issued by the user. Which TWO statements are true about the outcome?

   1. A. The ROLLBACK after the TRUNCATE restores the rows of TEMP_LOAD.

      This is the 'TRUNCATE can be rolled back' misconception. TRUNCATE is DDL and issues an implicit COMMIT, so its ROLLBACK is a no-op and TEMP_LOAD's rows are gone permanently.

   2. B. The ROLLBACK after the DELETE restores the rows of TEMP_STAGE.Correct answer

      DELETE is DML: its changes live inside the transaction until committed, so an uncommitted DELETE followed by ROLLBACK restores every removed row, as the reference describes for transaction control.

   3. C. TEMP_LOAD remains empty because TRUNCATE issued an implicit COMMIT and cannot be rolled back.Correct answer

      TRUNCATE is DDL and commits implicitly before and after running; there is nothing for the following ROLLBACK to reverse, so TEMP_LOAD stays empty.

   4. D. Both tables are restored, because ROLLBACK reverses the most recently executed statement in either case.

      This misconception treats ROLLBACK as a universal 'undo last statement'. ROLLBACK only reverses uncommitted DML; it cannot touch the already-committed DDL TRUNCATE, so TEMP_LOAD is not restored.

   Explanation

   DELETE is DML whose effect is provisional until the transaction commits, so rolling back an uncommitted DELETE fully restores the removed rows. TRUNCATE is DDL that commits implicitly and is therefore irreversible — a following ROLLBACK does nothing. The rollback-ability of the two operations is the sharpest practical contrast between them.

3. Question 3

   Which query correctly returns only the name of the column that constitutes the PRIMARY KEY of the EMPLOYEES table, using Oracle's built-in data dictionary views?

   1. A. SELECT column_name FROM user_tab_columns WHERE table_name = 'EMPLOYEES' AND nullable = 'N'

      USER_TAB_COLUMNS.nullable = 'N' marks every column that disallows NULL—both the primary-key column and every explicitly declared NOT NULL column. This returns EMP_ID, FIRST_NAME, and LAST_NAME (three rows), conflating primary-key identity with null-disallowed status.

   2. B. SELECT ucc.column_name FROM user_cons_columns ucc JOIN user_constraints uc ON ucc.constraint_name = uc.constraint_name WHERE uc.table_name = 'EMPLOYEES' AND uc.constraint_type = 'P'Correct answer

      Joining USER_CONS_COLUMNS to USER_CONSTRAINTS and filtering on constraint_type = 'P' isolates exactly the column(s) that form the primary key. For EMPLOYEES this returns only EMP_ID; no other constraint type shares the 'P' code, so no spurious columns appear.

   3. C. SELECT column_name FROM user_cons_columns WHERE table_name = 'EMPLOYEES'

      USER_CONS_COLUMNS has one row for every column that participates in any constraint—including the NOT NULL constraints on FIRST_NAME and LAST_NAME (stored as system-generated type 'C') and the FOREIGN KEY on DEPT_ID. Without a constraint_type filter this returns EMP_ID, FIRST_NAME, LAST_NAME, and DEPT_ID (four rows).

   4. D. SELECT ucc.column_name FROM user_cons_columns ucc JOIN user_constraints uc ON ucc.constraint_name = uc.constraint_name WHERE uc.table_name = 'EMPLOYEES' AND uc.constraint_type = 'C'

      Constraint_type 'C' covers CHECK constraints, which in Oracle also backs inline NOT NULL declarations. This query returns FIRST_NAME and LAST_NAME—the two NOT NULL columns—not EMP_ID, because the primary-key constraint carries type 'P', not 'C'.

   Explanation

   Oracle splits constraint metadata between USER_CONSTRAINTS (one row per constraint, carrying constraint_type: 'P' for primary key, 'R' for foreign key, 'C' for check including NOT NULL) and USER_CONS_COLUMNS (one row per column participating in any constraint). Filtering USER_TAB_COLUMNS on nullable = 'N' is too broad because it conflates primary-key and NOT NULL columns. Querying USER_CONS_COLUMNS alone without a constraint_type join pulls in every constrained column across all constraint types. Only the join between both views filtered on constraint_type = 'P' precisely isolates primary-key columns.

4. Question 4

   A team empties a 5-million-row LOG table with `DELETE FROM log;` followed by `COMMIT;`, but a subsequent `SELECT COUNT(*) FROM log;` full scan is still slow even though the table is empty. Which single operation, used instead, removes all rows AND resets the high-water mark so that a later full scan is fast again?

   1. A. `DELETE FROM log;` — once every row is gone, DELETE resets the table's high-water mark.

      This is the 'DELETE resets the high-water mark' misconception. DELETE leaves the high-water mark where it was, so a full scan still reads all the formerly used blocks — which is exactly why the scan stayed slow in the scenario.

   2. B. `DELETE FROM log WHERE 1=1;` then `COMMIT;` — the COMMIT lowers the high-water mark and releases the blocks.

      This misconception credits COMMIT with reclaiming space. COMMIT only makes the DELETE durable; it does not move the high-water mark or deallocate segment space, so the scan cost is unchanged.

   3. C. Neither statement can do it; only dropping and re-creating the table resets the high-water mark.

      This denies that TRUNCATE resets the high-water mark. TRUNCATE resets the high-water mark and (by default) deallocates space without needing to DROP the table, so recreation is unnecessary.

   4. D. `TRUNCATE TABLE log;` — it resets the high-water mark and, by default, deallocates the freed space.Correct answer

      TRUNCATE resets the table's high-water mark and, under the default DROP STORAGE, deallocates space back to MINEXTENTS. With the high-water mark lowered, a subsequent full scan reads far fewer blocks, so it runs fast.

   Explanation

   DELETE removes rows but leaves the segment's high-water mark unchanged, so a full scan still reads every block that was ever used — emptiness does not make the scan cheap. TRUNCATE resets the high-water mark and by default (DROP STORAGE) returns space to the segment, so later full scans read only the remaining allocated blocks. This space-and-high-water-mark difference is a core reason TRUNCATE is preferred for a complete table wipe.

5. Question 5

   Which Oracle error does the following CREATE TABLE statement raise? ```sql CREATE TABLE project ( proj_id NUMBER(6), budget MONEY ) ```

   1. A. ORA-00902Correct answer

      MONEY is not an Oracle built-in datatype (it belongs to other database products); Oracle rejects the column definition at parse time with ORA-00902: invalid datatype. Monetary amounts are modelled with NUMBER.

   2. B. ORA-00907

      ORA-00907 (missing right parenthesis) is raised when the parentheses enclosing the column list are unbalanced. Here every parenthesis is matched, so the statement fails on the datatype, not the punctuation.

   3. C. ORA-00904

      ORA-00904 (invalid identifier) is raised when a column or object name cannot be resolved. PROJ_ID and BUDGET are syntactically valid new column names; the error is the unrecognised datatype keyword, which Oracle reports as ORA-00902.

   4. D. ORA-00922

      ORA-00922 (missing or invalid option) covers malformed storage or column options such as an illegal precision. The token that Oracle cannot accept here is the type name itself, which is classified as an invalid datatype (ORA-00902), not an invalid option.

   Explanation

   Choosing an appropriate datatype means using one of Oracle's built-in types. When a column names a type Oracle does not recognise, the CREATE TABLE fails at parse time with ORA-00902: invalid datatype, and no table is created. MONEY, BOOLEAN-as-a-column-type in older releases, and STRING are common examples of names candidates expect but Oracle does not define; the correct substitute for a monetary value is NUMBER with a precision and scale.

6. Question 6

   EMPLOYEES has a foreign key on DEPT_ID that references DEPARTMENTS. Using only the data dictionary, you want a query that returns the name of the parent (referenced) table for that foreign key — the single value DEPARTMENTS. In USER_CONSTRAINTS a foreign key is a CONSTRAINT_TYPE = 'R' row whose R_CONSTRAINT_NAME points at the referenced constraint. Which query returns exactly DEPARTMENTS?

   1. A. SELECT fk.table_name FROM user_constraints fk WHERE fk.table_name = 'EMPLOYEES' AND fk.constraint_type = 'R'

      Reads the foreign-key row's own TABLE_NAME as the parent. That column is always the child table on which the FK is defined, so this returns EMPLOYEES, not the referenced table.

   2. B. SELECT fk.r_constraint_name FROM user_constraints fk WHERE fk.table_name = 'EMPLOYEES' AND fk.constraint_type = 'R'

      Confuses R_CONSTRAINT_NAME with a table name. It returns the name of the referenced constraint (the parent's primary-key constraint), not the table that constraint belongs to.

   3. C. SELECT rc.table_name FROM user_constraints fk JOIN user_constraints rc ON fk.r_constraint_name = rc.constraint_name WHERE fk.table_name = 'EMPLOYEES' AND fk.constraint_type = 'R'Correct answer

      The 'R' row's R_CONSTRAINT_NAME names the referenced (parent) constraint; joining it back to USER_CONSTRAINTS by CONSTRAINT_NAME yields that parent constraint's row, whose TABLE_NAME is the referenced table — DEPARTMENTS.

   4. D. SELECT rc.table_name FROM user_constraints fk JOIN user_constraints rc ON fk.r_constraint_name = rc.constraint_name WHERE fk.table_name = 'EMPLOYEES' AND fk.constraint_type = 'P'

      Filters on CONSTRAINT_TYPE = 'P' (the primary key of EMPLOYEES). A primary-key row has a NULL R_CONSTRAINT_NAME, so the join matches nothing and the query returns no rows.

   Explanation

   In USER_CONSTRAINTS a foreign key is a CONSTRAINT_TYPE = 'R' row whose R_CONSTRAINT_NAME points at the referenced (parent) constraint; joining that back to USER_CONSTRAINTS on CONSTRAINT_NAME yields the parent constraint's row, whose TABLE_NAME is the referenced table. The FK row's own TABLE_NAME is the child (EMPLOYEES), R_CONSTRAINT_NAME is a constraint name rather than a table, and primary-key ('P') rows carry no referenced link to join on.

7. Question 7

   The hr-mini schema currently contains only the DEPARTMENTS and EMPLOYEES tables. A developer submits the statement below to create a new table. What is the result? ```sql CREATE TABLE payroll ( emp_id NUMBER(6), pay_period VARCHAR2(20), gross_pay MONEY, paid_on DATE ); ```

   1. A. ORA-00902: invalid datatypeCorrect answer

      MONEY is not a built-in Oracle datatype (it belongs to SQL Server, not Oracle). Oracle's parser rejects the unknown type name while checking the column definition, so the statement raises ORA-00902 at parse time and no table is ever created — the correct Oracle type for currency is NUMBER(p,s).

   2. B. The statement succeeds and the PAYROLL table is created

      Assumes Oracle silently accepts MONEY, or maps it to NUMBER the way some other databases do. Oracle has no MONEY type and does not alias it, so the CREATE TABLE fails outright rather than creating the table.

   3. C. ORA-00907: missing right parenthesis

      Misreads an unrecognized datatype keyword as a punctuation/syntax fault. The parentheses here are balanced; the parser gets past them and fails specifically on the invalid type name, which is ORA-00902, not ORA-00907.

   4. D. ORA-00955: name is already used by an existing object

      Assumes a naming collision, but PAYROLL does not exist in hr-mini (only DEPARTMENTS and EMPLOYEES do), so there is no duplicate name. The definition is rejected for its invalid column datatype, which is detected before any existing-object check would matter.

   Explanation

   CREATE TABLE is validated at parse time before any object is created, and each column's datatype must be one recognized by Oracle. MONEY is a SQL Server type with no Oracle equivalent, so naming it makes the column definition invalid and Oracle raises ORA-00902 (invalid datatype); the currency use case is served by NUMBER(p,s) instead. Because the error is a definition-time failure, nothing is created and there is no partial DDL to roll back.

8. Question 8

   The EMPLOYEES table is defined with the columns shown below and currently holds 8 rows. Which Oracle error does the following ALTER TABLE statement raise? ``` | Column | Type | |------------|---------------| | EMP_ID | NUMBER(6) PK | | FIRST_NAME | VARCHAR2(30) | | LAST_NAME | VARCHAR2(30) | | SALARY | NUMBER(8,2) | | COMMISSION | NUMBER(4,2) | | MANAGER_ID | NUMBER(6) | | DEPT_ID | NUMBER(4) | | HIRE_DATE | DATE | ``` ```sql ALTER TABLE employees ADD (salary NUMBER(8, 2)) ```

   1. A. ORA-00957

      ORA-00957 (duplicate column name) is raised when a single statement lists the same column name more than once. This ADD names SALARY only once; the conflict is with a column that already exists in the table, which Oracle reports as ORA-01430.

   2. B. ORA-00904

      ORA-00904 (invalid identifier) is raised when a referenced name cannot be resolved — for example renaming or modifying a column that does not exist. Here the problem is the opposite: SALARY already exists, so adding it again is ORA-01430.

   3. C. ORA-01430Correct answer

      EMPLOYEES already has a SALARY column, and ALTER TABLE ... ADD cannot introduce a column whose name is already present. Oracle rejects the statement at definition time with ORA-01430: column being added already exists in table.

   4. D. ORA-01442

      ORA-01442 (column to be modified to NOT NULL is already NOT NULL) arises from ALTER TABLE ... MODIFY on an existing column. This statement uses ADD to introduce a new column, so ORA-01442 does not apply.

   Explanation

   ALTER TABLE ... ADD introduces a new column and therefore requires a name not already used in the table. Because EMPLOYEES already defines SALARY, the request is a definition-time conflict and Oracle raises ORA-01430 without changing the table. To change an existing column's datatype or size you use MODIFY, not ADD; to give it a different name you use RENAME COLUMN.