1Z0-071 Data Definition Language (DDL) 2 — Questions and Answers
Question 1: What is a FOREIGN KEY constraint used for?
- Ensuring uniqueness within a column
- Enforcing referential integrity between two tables (Correct answer)
- Preventing NULL values in a column
- Limiting the range of values in a column
Correct answer: Enforcing referential integrity between two tables
A FOREIGN KEY constraint ensures that values in a column match values in a referenced primary key column of another table.
Question 2: Which data type stores variable-length character strings up to 4000 bytes in Oracle?
- CHAR
- VARCHAR2 (Correct answer)
- NCHAR
- LONG
Correct answer: VARCHAR2
VARCHAR2 stores variable-length character data up to 4000 bytes (or 32767 in extended mode) and is Oracle's recommended string type.
Question 3: What does a CHECK constraint do?
- Ensures a column references a valid primary key
- Verifies that column values satisfy a specific condition (Correct answer)
- Prevents duplicate values in a column
- Guarantees a column is never NULL
Correct answer: Verifies that column values satisfy a specific condition
A CHECK constraint enforces a business rule by validating that column values meet a specified Boolean condition.
Question 4: Which Oracle statement adds a constraint to an existing table?
- INSERT CONSTRAINT INTO table
- ALTER TABLE table ADD CONSTRAINT ... (Correct answer)
- CREATE CONSTRAINT ON table
- MODIFY TABLE table ADD CONSTRAINT ...
Correct answer: ALTER TABLE table ADD CONSTRAINT ...
ALTER TABLE with ADD CONSTRAINT allows you to add a new constraint to an existing table.
Question 5: What is the CHAR data type's behavior regarding storage length?
- It stores variable-length strings
- It pads values with spaces to the defined fixed length (Correct answer)
- It stores only single characters
- It allows Unicode characters only
Correct answer: It pads values with spaces to the defined fixed length
CHAR stores fixed-length character data by right-padding shorter values with spaces to reach the declared length.
Question 6: Which of the following correctly creates a table with a primary key constraint?
- CREATE TABLE t (id NUMBER PRIMARY KEY, name VARCHAR2(50)) (Correct answer)
- CREATE TABLE t (id NUMBER UNIQUE NOT NULL, name VARCHAR2(50))
- CREATE TABLE t (id NUMBER KEY, name VARCHAR2(50))
- CREATE TABLE t (id NUMBER CONSTRAINT pk, name VARCHAR2(50))
Correct answer: CREATE TABLE t (id NUMBER PRIMARY KEY, name VARCHAR2(50))
Placing PRIMARY KEY after the column definition creates an inline primary key constraint for that column.
1Z0-071 Data Definition Language (DDL) 2
What is a FOREIGN KEY constraint used for?