1Z0-006 Basic SQL Statements 5 — Questions and Answers
Question 1: Which of the following SQL statements modifies existing data in a table?
- ALTER
- INSERT
- UPDATE (Correct answer)
- MERGE
Correct answer: UPDATE
UPDATE changes the values of existing rows in a table based on the specified SET and optional WHERE conditions.
Question 2: What is the effect of using ORDER BY SALARY DESC in a SELECT statement?
- Rows are sorted by SALARY from lowest to highest
- Rows are sorted by SALARY from highest to lowest (Correct answer)
- NULLs are excluded from the result
- The query returns only distinct salaries
Correct answer: Rows are sorted by SALARY from highest to lowest
DESC (descending) orders values from the largest to the smallest, so the highest salary appears first.
Question 3: In Oracle, which character is used to concatenate two strings in SQL?
- +
- &
- || (Correct answer)
- ::
Correct answer: ||
Oracle uses the || (double pipe) operator to concatenate strings, e.g., first_name || ' ' || last_name.
Question 4: Which of the following WHERE conditions correctly uses the LIKE operator to find names starting with 'J'?
- WHERE name LIKE 'J'
- WHERE name LIKE 'J_'
- WHERE name LIKE 'J%' (Correct answer)
- WHERE name LIKE '%J'
Correct answer: WHERE name LIKE 'J%'
J% matches any string that starts with 'J' followed by zero or more characters.
Question 5: What does the AVG aggregate function do when NULL values are present in the column?
- Treats NULLs as zero in the calculation
- Returns NULL if any value is NULL
- Ignores NULL values in the calculation (Correct answer)
- Returns an error
Correct answer: Ignores NULL values in the calculation
AVG (like most aggregate functions) ignores NULL values and calculates the average only from non-NULL values.
Question 6: Which of the following is a valid use of a table alias in SQL?
- SELECT e.name FROM employees AS e WHERE e.dept = 'HR' (Correct answer)
- SELECT employees.name AS e WHERE dept = 'HR'
- SELECT name FROM e AS employees WHERE dept = 'HR'
- SELECT e.name employees FROM WHERE dept = 'HR'
Correct answer: SELECT e.name FROM employees AS e WHERE e.dept = 'HR'
A table alias is assigned in the FROM clause (e.g., employees AS e or employees e) and then used to qualify column references.
Question 7: What is the difference between DELETE and TRUNCATE in Oracle?
- DELETE keeps the table; TRUNCATE drops the table entirely
- DELETE is DDL; TRUNCATE is DML
- TRUNCATE cannot be rolled back; DELETE can be rolled back before COMMIT (Correct answer)
- TRUNCATE allows a WHERE clause; DELETE does not
Correct answer: TRUNCATE cannot be rolled back; DELETE can be rolled back before COMMIT
TRUNCATE is a DDL operation that cannot be rolled back; DELETE is DML and can be undone with ROLLBACK before committing.
1Z0-006 Basic SQL Statements 5
Which of the following SQL statements modifies existing data in a table?