1Z0-006 Basic SQL Statements 2 — Questions and Answers
Question 1: Which clause in a SELECT statement is used to filter rows AFTER grouping has been applied?
- WHERE
- HAVING (Correct answer)
- GROUP BY
- ORDER BY
Correct answer: HAVING
HAVING filters groups after GROUP BY aggregation, while WHERE filters individual rows before grouping.
Question 2: What does the DISTINCT keyword do in a SELECT statement?
- Sorts the result set in ascending order
- Removes duplicate rows from the result set (Correct answer)
- Filters NULL values from the result
- Limits the number of rows returned
Correct answer: Removes duplicate rows from the result set
SELECT DISTINCT eliminates duplicate rows so each unique combination of selected column values appears only once.
Question 3: Which SQL statement is used to add new rows to a table?
- UPDATE
- MERGE
- INSERT (Correct answer)
- CREATE
Correct answer: INSERT
INSERT INTO is the SQL statement used to add one or more new rows of data into a table.
Question 4: In a SELECT statement, what is the correct order of clauses?
- FROM, WHERE, SELECT, GROUP BY, HAVING, ORDER BY
- SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY (Correct answer)
- SELECT, WHERE, FROM, HAVING, GROUP BY, ORDER BY
- FROM, SELECT, WHERE, ORDER BY, GROUP BY, HAVING
Correct answer: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY
The standard clause order is SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY.
Question 5: What happens when you use UPDATE without a WHERE clause?
- Only the first row is updated
- The statement returns an error
- All rows in the table are updated (Correct answer)
- No rows are updated
Correct answer: All rows in the table are updated
An UPDATE without WHERE updates every row in the table with the specified new values.
Question 6: Which operator in SQL is used to compare a value against a list of values?
- BETWEEN
- LIKE
- IN (Correct answer)
- EXISTS
Correct answer: IN
The IN operator checks whether a value matches any value in a specified list, e.g., WHERE dept IN ('HR','IT').
Question 7: What is the result of: SELECT 10 + NULL FROM DUAL?
- 10
- NULL (Correct answer)
- 0
- Error
Correct answer: NULL
Any arithmetic operation involving NULL produces NULL because NULL represents an unknown value.
