Stepwise-Lab

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;
  1. The value is truncated to 'US'.
  2. It fails with ORA-12899: value too large for column.
  3. It fails with ORA-01438.
  4. 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?

  1. UPDATE employees SET salary = NULL WHERE employee_id = 108;
  2. UPDATE employees SET salary = 0 WHERE employee_id = 108;
  3. UPDATE employees SET salary = -1 WHERE employee_id = 108;
  4. 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;
  1. The table is created with columns EMPLOYEE_ID and "E.SALARY*12".
  2. It fails: the expression has no valid column name (ORA-00998).
  3. The table is created with columns EMPLOYEE_ID and COL2.
  4. 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)?

  1. CREATE TABLE t (room_id NUMBER PRIMARY KEY, booked_on DATE PRIMARY KEY);
  2. CREATE TABLE t (room_id NUMBER, booked_on DATE, CONSTRAINT t_pk PRIMARY KEY (room_id, booked_on));
  3. CREATE TABLE t (room_id NUMBER, booked_on DATE) PRIMARY KEY (room_id, booked_on);
  4. 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).