Oracle 1Z0-071: DDL Tables and Relationships

Topic snapshot

Sample questions

These questions are original IT Mastery practice items aligned to this topic area. They are designed for self-assessment and are not official exam questions.

Question 1

Topic: Manage Tables and Relationships by Using DDL

Examine the exhibit:

CREATE TABLE students (
  student_id NUMBER PRIMARY KEY
);

CREATE TABLE sections (
  section_id NUMBER PRIMARY KEY
);

CREATE TABLE enrollments (
  student_id NUMBER NOT NULL,
  section_id NUMBER NOT NULL,
  enrolled_on DATE DEFAULT SYSDATE
);

Business rule: each enrollment must reference one existing student and one existing section, and the same student cannot enroll in the same section more than once.

Which constraint definition best models this rule for ENROLLMENTS?

Options:

• A. CONSTRAINT enroll_pk PRIMARY KEY (section_id), CONSTRAINT enroll_student_fk FOREIGN KEY (student_id) REFERENCES students(student_id), CONSTRAINT enroll_section_fk FOREIGN KEY (section_id) REFERENCES sections(section_id)

• B. CONSTRAINT enroll_pk PRIMARY KEY (student_id, section_id), CONSTRAINT enroll_parent_fk FOREIGN KEY (student_id, section_id) REFERENCES students(student_id, section_id)

• C. CONSTRAINT enroll_pk PRIMARY KEY (student_id, section_id), CONSTRAINT enroll_student_fk FOREIGN KEY (student_id) REFERENCES students(student_id), CONSTRAINT enroll_section_fk FOREIGN KEY (section_id) REFERENCES sections(section_id)

• D. CONSTRAINT enroll_pk PRIMARY KEY (student_id), CONSTRAINT enroll_student_fk FOREIGN KEY (student_id) REFERENCES students(student_id), CONSTRAINT enroll_section_fk FOREIGN KEY (section_id) REFERENCES sections(section_id)

Best answer: C

Explanation: This is a classic intersection table. The pair (student_id, section_id) must be unique for each enrollment, and each column must also reference its own parent table through a foreign key.

The correct design uses a composite primary key on (student_id, section_id) plus two foreign keys, one to STUDENTS and one to SECTIONS. In an intersection table, the combined key represents one relationship instance, so it naturally prevents the same student from being enrolled in the same section more than once.

• PRIMARY KEY (student_id, section_id) enforces uniqueness of each student-section pair.
• FOREIGN KEY (student_id) ensures the student exists.
• FOREIGN KEY (section_id) ensures the section exists.

A primary key on only one of those columns would incorrectly limit the table to one row per student or one row per section. The key takeaway is that many-to-many relationships are typically modeled with a bridge table whose primary key is the combination of both parent keys.

• Single student key fails because PRIMARY KEY (student_id) would allow each student only one row in ENROLLMENTS.
• Single section key fails because PRIMARY KEY (section_id) would allow each section only one row in ENROLLMENTS.
• Wrong foreign key target fails because a composite foreign key to students(student_id, section_id) does not match a key in STUDENTS and does not reference SECTIONS.

Question 2

Topic: Manage Tables and Relationships by Using DDL

An application no longer uses the promo_code column in the SALES table. You must make that column unavailable to SQL statements immediately, keep all rows in SALES, and defer the physical column removal to a later maintenance window. Which statement best meets this requirement?

Options:

• A. ALTER TABLE sales SET UNUSED (promo_code);

• B. TRUNCATE TABLE sales;

• C. ALTER TABLE sales DROP UNUSED COLUMNS;

• D. ALTER TABLE sales DROP COLUMN promo_code;

Best answer: A

Explanation: The correct choice is the ALTER TABLE ... SET UNUSED statement. In Oracle, marking a column unused makes it inaccessible right away without removing table rows, and the actual storage cleanup can be performed later.

This requirement matches Oracle’s UNUSED column feature. ALTER TABLE ... SET UNUSED is used when you want a column to disappear from normal SQL access immediately, but you do not want to spend the time to physically remove it at that moment. The table remains available, and all existing rows stay in place.

Later, during a maintenance window, you can physically remove the unused columns with ALTER TABLE ... DROP UNUSED COLUMNS.

That makes SET UNUSED the best fit when the goal is immediate logical removal now and deferred physical cleanup later. By contrast, dropping the column removes it now, and truncating the table removes rows rather than just changing the table structure.

• Immediate drop fails because dropping the column removes it physically now instead of deferring that work.
• Drop unused columns fails because it only removes columns that were already marked unused earlier.
• Truncate confusion fails because TRUNCATE TABLE removes all rows from the table, not just one column.

Question 3

Topic: Manage Tables and Relationships by Using DDL

A developer gets ORA-02291: integrity constraint violated - parent key not found when running the insert below.

CREATE TABLE departments (
  department_id NUMBER CONSTRAINT dept_pk PRIMARY KEY
);

CREATE TABLE employees (
  employee_id   NUMBER CONSTRAINT emp_pk PRIMARY KEY,
  department_id NUMBER CONSTRAINT emp_dept_fk
                 REFERENCES departments(department_id),
  salary        NUMBER CONSTRAINT emp_sal_ck CHECK (salary > 0)
);

INSERT INTO employees VALUES (101, 50, 4000);

What is the best fix?

Options:

• A. Raise salary above 0 before inserting the row

• B. Insert department 50 first or use an existing department_id

• C. Create a unique index on employees(department_id)

• D. Drop the primary key on departments and retry

Best answer: B

Explanation: This error shows a foreign key violation. The DDL makes employees.department_id depend on an existing departments.department_id, so the inserted department value must already be present in the parent table.

The key concept is foreign-key enforcement. In this DDL, emp_dept_fk creates a parent-child relationship from employees.department_id to departments.department_id. When the insert uses 50, Oracle checks whether a parent row with department_id = 50 exists in departments. If it does not, Oracle raises ORA-02291.

• The PRIMARY KEY on departments defines valid parent key values.
• The foreign key in employees requires matching parent values.
• The CHECK (salary > 0) only verifies that salary is positive.

So the correct next step is to insert the missing parent department or use a department ID that already exists. Changing indexing or salary does not resolve a missing parent row.

• Raising the salary fails because 4000 already satisfies CHECK (salary > 0).
• Dropping the parent primary key does not solve the missing parent-row problem and would break the referenced key definition.
• Creating a unique index on the child foreign-key column is unrelated to ORA-02291; the issue is referential integrity.

Question 4

Topic: Manage Tables and Relationships by Using DDL

A developer is creating the projects table. The project_id column must identify each row, must never be NULL, and must be declared with a single PRIMARY KEY constraint in the CREATE TABLE statement. Which column definition best meets the requirement?

Options:

• A. project_id NUMBER CONSTRAINT projects_nn NOT NULL

• B. project_id NUMBER CONSTRAINT projects_fk REFERENCES projects(project_id)

• C. project_id NUMBER CONSTRAINT projects_pk PRIMARY KEY

• D. project_id NUMBER CONSTRAINT projects_uk UNIQUE NOT NULL

Best answer: C

Explanation: A PRIMARY KEY is the Oracle constraint used to define a table’s row identifier. It automatically enforces both uniqueness and non-nullability, so a single PRIMARY KEY clause satisfies all stated requirements.

In Oracle, a PRIMARY KEY constraint is the standard way to declare the column or columns that uniquely identify each row in a table. It combines two behaviors: values must be unique, and they cannot be NULL. That matches the stem exactly because project_id must serve as the row identifier and be enforced by one named constraint.

Using UNIQUE with NOT NULL can mimic the data rules, but it still does not define the table’s primary key. A plain NOT NULL constraint prevents missing values but allows duplicates. A REFERENCES clause defines a foreign key relationship, not the table’s own row identity.

The key takeaway is that when Oracle asks for row identity with uniqueness and non-null enforcement, use PRIMARY KEY.

• UNIQUE NOT NULL enforces similar data rules, but it does not declare the column as the table’s PRIMARY KEY.
• NOT NULL only prevents missing values, but duplicate project_id values would still be allowed.
• REFERENCES clause creates a foreign key relationship and does not define row uniqueness for this table.

Question 5

Topic: Manage Tables and Relationships by Using DDL

You are reviewing this table design.

CREATE TABLE invoices (
  invoice_id   NUMBER(8),
  invoice_code NUMBER(8),
  amount_due   NUMBER(9,2),
  due_date     VARCHAR2(20)
);

Business notes:

• invoice_code stores values like A7, A0105, and B12001.
• due_date will be compared to SYSDATE and used in date arithmetic.

Which change best aligns the column data types with these requirements?

Options:

• A. Use VARCHAR2 for invoice_code and DATE for due_date.

• B. Use VARCHAR2 for amount_due and keep due_date VARCHAR2.

• C. Keep invoice_code NUMBER and use CHAR for due_date.

• D. Use CHAR for invoice_code and TIMESTAMP for due_date.

Best answer: A

Explanation: invoice_code contains letters and varying lengths, so it should be stored as character data, not as a number. due_date participates in date comparisons and arithmetic, so it should be stored as the Oracle DATE data type rather than text.

When defining table columns, choose the data type based on how the data is represented and how SQL will use it. An invoice code such as A7 or B12001 is not a numeric value for calculation; it is an identifier, so a character type like VARCHAR2 is appropriate. Because the values vary in length, VARCHAR2 is a better fit than fixed-length CHAR.

A due date should use DATE because Oracle date comparisons and date arithmetic work directly with date-oriented data types. Storing dates in VARCHAR2 forces conversions and can cause sorting and comparison problems. NUMBER(9,2) is already suitable for amount_due because it stores numeric amounts with two decimal places.

The key takeaway is to match identifiers to character types, amounts to number types, and calendar dates to date types.

• The option using CHAR and TIMESTAMP is less suitable because the code values are variable length and the requirement does not need time-of-day precision.
• The option changing amount_due to VARCHAR2 fails because monetary values should remain numeric for calculations and comparisons.
• The option keeping the code as NUMBER fails because alphanumeric identifiers cannot be reliably stored as numeric data.
• The option using CHAR for the due date fails because date comparisons and arithmetic should use a date-oriented type, not text.

Question 6

Topic: Manage Tables and Relationships by Using DDL

Exhibit:

SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 50;

A developer has confirmed that this query returns the exact rows needed. The table DEPT50_EMP does not exist yet. Which statement should be used to create that table from this query result in one step?

Options:

• A. CREATE TABLE dept50_emp (employee_id, last_name, salary) SELECT employee_id, last_name, salary FROM employees WHERE department_id = 50;

• B. CREATE VIEW dept50_emp AS SELECT employee_id, last_name, salary FROM employees WHERE department_id = 50;

• C. CREATE TABLE dept50_emp AS SELECT employee_id, last_name, salary FROM employees WHERE department_id = 50;

• D. INSERT INTO dept50_emp SELECT employee_id, last_name, salary FROM employees WHERE department_id = 50;

Best answer: C

Explanation: When a table does not yet exist and must be created from a query result, Oracle uses CREATE TABLE ... AS SELECT (CTAS). This statement both creates the table and inserts the selected rows in one operation.

The core concept is CTAS: CREATE TABLE ... AS SELECT. In Oracle, this is the correct choice when you want a brand-new table whose initial data comes directly from a query result.

In this scenario, the query is already validated, and DEPT50_EMP does not exist. That means the needed action is to create the table and load it immediately from the SELECT statement. CTAS does exactly that in one SQL statement.

INSERT INTO ... SELECT is only appropriate after the target table already exists. CREATE VIEW ... AS SELECT creates a view, not a physical table. A CREATE TABLE statement with a column list followed directly by a query is not valid CTAS syntax.

The key takeaway is that a new table from query output calls for CREATE TABLE table_name AS subquery.

• Insert instead fails because INSERT INTO ... SELECT needs DEPT50_EMP to exist first.
• Create a view fails because a view stores a query definition, not a new table of copied rows.
• Mixing syntaxes fails because CTAS uses AS SELECT, not a plain column list followed by SELECT.

Question 7

Topic: Manage Tables and Relationships by Using DDL

A nightly load refreshes the SALES_STG table before new rows are inserted. All existing rows must be removed as quickly as possible, the table definition must remain, and row-by-row DELETE processing is specifically not allowed. The team does not need to roll back this step. Which SQL statement should be used?

Options:

• A. ALTER TABLE sales_stg REMOVE ALL ROWS;

• B. DELETE FROM sales_stg;

• C. DROP TABLE sales_stg;

• D. TRUNCATE TABLE sales_stg;

Best answer: D

Explanation: Use TRUNCATE TABLE when every row must be removed and the table itself must stay in place. In Oracle, TRUNCATE is a DDL operation, so it avoids row-by-row DELETE processing and is appropriate when rollback is not required.

The core rule is that TRUNCATE TABLE removes all rows from a table while preserving the table definition for future use. In this scenario, that matches all stated requirements: remove every row, do it without row-by-row DELETE processing, and keep the table available for the next load.

TRUNCATE is a DDL statement in Oracle, so it performs an implicit commit and cannot be rolled back in the way a DELETE can. By contrast, DELETE FROM sales_stg is DML and processes deleted rows individually, which the stem explicitly says to avoid. The key takeaway is: if you must empty the whole table quickly and keep the object, use TRUNCATE TABLE.

• Using DELETE fails because it removes rows through DML processing rather than the all-rows TRUNCATE operation.
• Dropping the table fails because it removes the table object itself, not just its rows.
• The ALTER TABLE choice fails because REMOVE ALL ROWS is not valid Oracle SQL syntax for emptying a table.

Question 8

Topic: Manage Tables and Relationships by Using DDL

Review the exhibit:

DESC stage_orders
Name      Null? Type
--------- ----- -----------------
ORDER_ID        NUMBER
LOAD_TS         TIMESTAMP
STATUS          VARCHAR2(20)

SELECT COUNT(*) FROM stage_orders;
-- 1250000 rows

-- No enabled foreign keys reference STAGE_ORDERS.

A nightly load must remove every row from stage_orders before reloading it. The table definition must remain, and Oracle should avoid row-by-row DELETE processing. Which statement best meets the requirement?

Options:

• A. DELETE FROM stage_orders;

• B. DROP TABLE stage_orders;

• C. TRUNCATE TABLE stage_orders;

• D. ALTER TABLE stage_orders MOVE;

Best answer: C

Explanation: TRUNCATE TABLE is the correct choice when all rows must be removed and the table itself must stay in place. It clears the table without row-by-row DELETE processing, which is exactly what the requirement states.

The core concept is choosing TRUNCATE when you need to remove all rows from a table efficiently but keep the table definition. In Oracle, TRUNCATE TABLE is a DDL operation that removes all rows without processing each row as a DELETE would. That makes it the appropriate command for a staging table that is fully emptied and reloaded.

In this case:

• all rows must be removed
• the table structure must remain
• row-by-row DELETE processing should be avoided
• no enabled foreign keys reference the table

Those facts align directly with TRUNCATE TABLE stage_orders;. A close distractor is plain DELETE, but that still performs row-by-row deletion logic rather than the faster table-level truncation behavior.

• Using DELETE fails because it removes rows through row-by-row processing, which the requirement explicitly wants to avoid.
• Dropping the table fails because it removes the table object itself, not just its data.
• Moving the table fails because ALTER TABLE ... MOVE changes storage characteristics and does not clear rows.

Question 9

Topic: Manage Tables and Relationships by Using DDL

A table ORDERS has an obsolete column PROMO_CODE. The application must stop referencing that column immediately, but the DBA wants to postpone the physical column removal and space reclamation until a later maintenance window. Which statement best meets this requirement?

Options:

• A. TRUNCATE TABLE orders;

• B. ALTER TABLE orders SET UNUSED (promo_code);

• C. ALTER TABLE orders DROP UNUSED COLUMNS;

• D. ALTER TABLE orders DROP COLUMN promo_code;

Best answer: B

Explanation: Oracle uses ALTER TABLE ... SET UNUSED when a column should disappear from use immediately but its physical removal can wait. The column becomes unavailable to SQL right away, and the actual storage cleanup can be done later with DROP UNUSED COLUMNS.

The key Oracle SQL rule is that SET UNUSED is a two-step approach to column removal. It marks the column as unused so applications can no longer reference it, while delaying the more expensive physical cleanup.

• SET UNUSED hides the column immediately.
• The column data remains in the table segment for now.
• Later, ALTER TABLE ... DROP UNUSED COLUMNS permanently removes those unused columns.

By contrast, DROP COLUMN performs the actual column drop now, DROP UNUSED COLUMNS only affects columns already marked unused, and TRUNCATE TABLE removes rows rather than a column. The best match is the statement that separates logical removal from physical cleanup.

• Immediate full drop fails because DROP COLUMN removes the column now instead of deferring the physical cleanup.
• Later cleanup only fails because DROP UNUSED COLUMNS works only after a column has already been marked unused.
• Remove rows instead fails because TRUNCATE TABLE deletes all table data, not a single obsolete column.

Question 10

Topic: Manage Tables and Relationships by Using DDL

A developer currently reloads a daily CSV file into a regular table named STAGE_ORDERS before running reports. After the file is replaced, queries still show the old data until the table is reloaded again. The new requirement is read-only SQL access to the file contents without importing the rows into a database table each day.

What is the best next step?

Options:

• A. Create an external table that maps columns to the file.

• B. Convert STAGE_ORDERS to a global temporary table.

• C. Create a view on STAGE_ORDERS to read the file indirectly.

• D. Create a synonym for the CSV file name.

Best answer: A

Explanation: The requirement is to query file-based data directly with SQL, without loading it into a normal table first. In Oracle, that is the role of an external table: the table definition is in the database, but the data remains in the external file.

An external table is designed for SQL query access to data stored outside the database, such as a CSV or other text file. Oracle stores the column definitions and access parameters in the data dictionary, but it does not store the file rows in database blocks like a regular or temporary table.

In this case, the symptom is that STAGE_ORDERS shows old data until it is reloaded, because it is a normal table that contains copied rows. To meet the new requirement, the developer should define an external table over the file and query that object directly.

A view or synonym only points to an existing object, and a global temporary table still requires loading data into the database session or transaction scope.

• Temporary table confusion fails because a global temporary table still holds rows loaded into Oracle; it does not query an external file by itself.
• View misconception fails because a view only stores a query on existing objects and cannot make Oracle read a CSV file directly.
• Synonym misconception fails because a synonym is just an alternate name for an existing object, not a file-access mechanism.

Continue with full practice

Use the Oracle 1Z0-071 Practice Test page for the full IT Mastery route, mixed-topic practice, timed mock exams, explanations, and web/mobile app access.

Try Oracle 1Z0-071 on Web View Oracle 1Z0-071 Practice Test

Related focused pages

• Free Oracle 1Z0-071 Full-Length Practice Exam
• Relational Database Concepts
• SQL SELECT
• Restrict and Sort Data
• Single-Row Functions
• Conversion and CASE
• Group Functions
• Display Data from Multiple Tables
• Use Subqueries to Solve Queries
• Use Set Operators
• Manage Tables by Using DML Statements
• Indexes and Sequences
• Manage Views
• Control User Access
• Data Dictionary Views
• Manage Data in Different Time Zones

Free review resource

Read the Oracle 1Z0-071 Cheat Sheet on Tech Exam Lexicon, then return to IT Mastery for timed practice.