1Z0-006 Basic SQL Statements 3 — Questions and Answers
Question 1: Which DELETE syntax correctly removes all rows from a table named EMPLOYEES?
- DELETE EMPLOYEES;
- DELETE FROM EMPLOYEES; (Correct answer)
- DROP EMPLOYEES;
- REMOVE FROM EMPLOYEES;
Correct answer: DELETE FROM EMPLOYEES;
DELETE FROM table_name without a WHERE clause removes all rows while keeping the table structure intact.
Question 2: What does the ORDER BY clause do by default when no ASC or DESC is specified?
- Sorts in descending order
- No sorting is applied
- Sorts in ascending order (Correct answer)
- Sorts NULLs first
Correct answer: Sorts in ascending order
If neither ASC nor DESC is specified, ORDER BY sorts in ascending (ASC) order by default.
Question 3: Which aggregate function returns the number of non-NULL values in a column?
- SUM
- AVG
- COUNT (Correct answer)
- MAX
Correct answer: COUNT
COUNT(column_name) counts only non-NULL values in that column; COUNT(*) counts all rows including NULLs.
Question 4: Which SQL clause is used to join data from a column with the rows of another table inline?
- CONNECT BY
- GROUP BY
- SUBQUERY in FROM (inline view) (Correct answer)
- HAVING
Correct answer: SUBQUERY in FROM (inline view)
A subquery in the FROM clause (inline view) acts as a derived table that can be queried like a regular table.
Question 5: Which wildcard character in a LIKE condition matches any single character?
- %
- *
- _ (Correct answer)
- ?
Correct answer: _
The underscore (_) wildcard matches exactly one character, while % matches zero or more characters.
Question 6: What is the purpose of the GROUP BY clause?
- Filters rows based on a condition
- Arranges rows into groups for aggregate functions (Correct answer)
- Combines rows from two tables
- Removes duplicate columns
Correct answer: Arranges rows into groups for aggregate functions
GROUP BY partitions rows into groups so aggregate functions like COUNT, SUM, or AVG can be applied to each group.
Question 7: Which statement about the WHERE clause is TRUE?
- It can reference column aliases defined in the SELECT list
- It is evaluated after GROUP BY
- It filters rows before any grouping occurs (Correct answer)
- It can contain aggregate functions
Correct answer: It filters rows before any grouping occurs
WHERE is evaluated before GROUP BY; it filters individual rows before aggregation takes place.
1Z0-006 Basic SQL Statements 3
Which DELETE syntax correctly removes all rows from a table named EMPLOYEES?