1Z0-006 SQL Fundamentals & Data Manipulation 3 — Questions and Answers
Question 1: What does the TRUNCATE TABLE command do that differs from DELETE?
- TRUNCATE removes specific rows matching a condition
- TRUNCATE is a DDL statement that removes all rows and cannot be rolled back in the same way as DELETE (Correct answer)
- TRUNCATE drops and recreates the table
- TRUNCATE permanently removes the table and its indexes
Correct answer: TRUNCATE is a DDL statement that removes all rows and cannot be rolled back in the same way as DELETE
TRUNCATE is a DDL operation that deallocates table data quickly and, unlike DELETE, implicitly commits and cannot be rolled back.
Question 2: Which operator tests whether a value falls within an inclusive range in a WHERE clause?
- IN (low, high)
- BETWEEN low AND high (Correct answer)
- RANGE low TO high
- FROM low TO high
Correct answer: BETWEEN low AND high
BETWEEN low AND high is inclusive on both ends, equivalent to col >= low AND col <= high.
Question 3: Which SELECT statement retrieves distinct department IDs from the employees table?
- SELECT UNIQUE department_id FROM employees;
- SELECT DIFFERENT department_id FROM employees;
- SELECT DISTINCT department_id FROM employees; (Correct answer)
- SELECT department_id NODUPS FROM employees;
Correct answer: SELECT DISTINCT department_id FROM employees;
DISTINCT is the standard SQL keyword to eliminate duplicate rows from a result set.
Question 4: What is the result of: SELECT 100 + NULL FROM dual;
- 100
- NULL (Correct answer)
- 0
- An error is raised
Correct answer: NULL
Any arithmetic operation involving NULL propagates NULL as the result.
Question 5: Which SQL keyword is used to sort query results in descending order?
- SORT DESC
- ORDER DESC
- ORDER BY column DESC (Correct answer)
- ARRANGE BY column DESCENDING
Correct answer: ORDER BY column DESC
ORDER BY column DESC instructs Oracle to sort the specified column from highest to lowest.
Question 6: Which constraint ensures that a column value in one table matches a value in a referenced table's primary key?
- UNIQUE constraint
- CHECK constraint
- FOREIGN KEY constraint (Correct answer)
- NOT NULL constraint
Correct answer: FOREIGN KEY constraint
A FOREIGN KEY constraint enforces referential integrity by requiring that the column value exists in the parent table's primary key.
Question 7: What does the NVL(commission_pct, 0) function do when commission_pct is NULL?
- Raises an exception
- Returns the string 'NULL'
- Returns 0 (Correct answer)
- Leaves the value unchanged
Correct answer: Returns 0
NVL replaces a NULL value with the specified substitute, so NVL(commission_pct, 0) returns 0 when commission_pct is NULL.
1Z0-006 SQL Fundamentals & Data Manipulation 3
What does the TRUNCATE TABLE command do that differs from DELETE?