Stepwise-Lab

Tables and constraints: 1Z0-171 practice questions

Questions in the style of exam 1Z0-171 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, including BOOLEAN, VECTOR and SQL domains
  • 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 do these two distances return?

SELECT VECTOR_DISTANCE(VECTOR('[1, 0]'), VECTOR('[0, 1]'), COSINE),
       VECTOR_DISTANCE(VECTOR('[0, 0]'), VECTOR('[3, 4]'), EUCLIDEAN);
  1. 1 and 5
  2. 0 and 5
  3. 1 and 7
  4. 0 and 25
Show the answer

Answer: A

Cosine distance is 1 - cosine similarity; perpendicular vectors have similarity 0, so the distance is 1 (identical directions give 0). Euclidean distance is the straight-line length sqrt(3*3 + 4*4) = 5. 7 is MANHATTAN distance and 25 is EUCLIDEAN_SQUARED. The results are BINARY_DOUBLE values.

4. 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 ...