Tables and constraints: 1Z0-071 practice questions
Questions in the style of exam 1Z0-071 on this topic. Answer each one first, then open its answer and explanation.
What this topic covers
- Describing and working with tables
- Columns and data types
- Creating tables
- Dropping columns and setting a column UNUSED
- Temporary tables
- External tables
- Managing constraints
Practice questions
1. customers.country_code is CHAR(2). What happens?
UPDATE customers SET country_code = 'USA' WHERE customer_id = 1;- The value is truncated to 'US'.
- It fails with ORA-12899: value too large for column.
- It fails with ORA-01438.
- The column grows to CHAR(3) automatically.
Show the answer
Answer: B
Oracle never truncates character data silently: a value longer than the column raises ORA-12899, and the message shows the actual and maximum length. ORA-01438 is the NUMBER counterpart for too many integer digits.
2. employees.salary has CHECK (salary > 0) and no NOT NULL constraint. Employee 108 exists and has a salary. Which statement succeeds?
UPDATE employees SET salary = NULL WHERE employee_id = 108;UPDATE employees SET salary = 0 WHERE employee_id = 108;UPDATE employees SET salary = -1 WHERE employee_id = 108;UPDATE employees SET salary = salary - salary WHERE employee_id = 108;
Show the answer
Answer: A
A CHECK constraint rejects a row only when its condition is FALSE. NULL > 0 is UNKNOWN, so NULL passes; add NOT NULL to forbid it. 0, -1, and salary - salary = 0 all make the condition FALSE (ORA-02290).
3. What happens when this statement runs?
CREATE TABLE emp_pay AS
SELECT e.employee_id, e.salary * 12
FROM employees e;- The table is created with columns EMPLOYEE_ID and "E.SALARY*12".
- It fails: the expression has no valid column name (ORA-00998).
- The table is created with columns EMPLOYEE_ID and COL2.
- The table is created, but empty.
Show the answer
Answer: B
Every column of a new table needs a valid name. A plain column reference supplies its own name, but an expression needs an alias in the SELECT (salary * 12 AS annual_pay) or a column list after the table name: CREATE TABLE emp_pay (id, annual_pay) AS SELECT ...
4. Which CREATE TABLE statement defines a primary key on (room_id, booked_on)?
CREATE TABLE t (room_id NUMBER PRIMARY KEY, booked_on DATE PRIMARY KEY);CREATE TABLE t (room_id NUMBER, booked_on DATE, CONSTRAINT t_pk PRIMARY KEY (room_id, booked_on));CREATE TABLE t (room_id NUMBER, booked_on DATE) PRIMARY KEY (room_id, booked_on);CREATE TABLE t (room_id NUMBER CONSTRAINT t_pk PRIMARY KEY (room_id, booked_on), booked_on DATE);
Show the answer
Answer: B
A constraint on more than one column must be written out of line, as a table-level constraint inside the parentheses. Two inline PRIMARY KEY clauses raise ORA-02260 (table can have only one primary key): Oracle reads two keys, not one key over two columns. The clause cannot follow the closing parenthesis (ORA-00922), and a column-level constraint cannot name a column list (ORA-00907).