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;- 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 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 and 5
- 0 and 5
- 1 and 7
- 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;- 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 ...