Oracle SQL Oracle SQL SELECT Statements 1 — Questions and Answers
Question 1: Which clause is used to filter rows AFTER grouping in Oracle SQL?
- WHERE
- HAVING (Correct answer)
- FILTER
- GROUP FILTER
Correct answer: HAVING
The HAVING clause filters rows after the GROUP BY aggregation is applied, unlike WHERE which filters before grouping.
Question 2: What does SELECT DISTINCT do in Oracle SQL?
- Returns only the first row of each group
- Removes duplicate rows from the result set (Correct answer)
- Sorts the result set
- Selects a random row
Correct answer: Removes duplicate rows from the result set
SELECT DISTINCT eliminates duplicate rows from the query result, returning only unique combinations of the selected columns.
Question 3: Which Oracle SQL keyword limits the number of rows returned using a row-limiting clause (Oracle 12c+)?
- TOP
- LIMIT
- FETCH FIRST (Correct answer)
- ROWNUM
Correct answer: FETCH FIRST
Oracle 12c introduced the FETCH FIRST n ROWS ONLY syntax as the standard row-limiting clause.
Question 4: What is the purpose of the ORDER BY clause in a SELECT statement?
- Filters rows based on a condition
- Groups rows with the same value
- Sorts the result set by one or more columns (Correct answer)
- Joins two tables together
Correct answer: Sorts the result set by one or more columns
ORDER BY sorts the final result set in ascending (ASC) or descending (DESC) order based on specified columns.
Question 5: In Oracle SQL, which pseudo-column assigns a sequential number to each row in a result set before ORDER BY is applied?
- ROW_NUMBER()
- ROWID
- ROWNUM (Correct answer)
- SEQUENCE
Correct answer: ROWNUM
ROWNUM is a pseudo-column assigned by Oracle before sorting, so filtering with ROWNUM must be done in a subquery to work correctly with ORDER BY.
Question 6: Which Oracle SQL operator is used to compare a value against a list of values in a WHERE clause?
- BETWEEN
- LIKE
- IN (Correct answer)
- EXISTS
Correct answer: IN
The IN operator tests whether a value matches any value in a specified list, equivalent to multiple OR conditions.
Oracle SQL Oracle SQL SELECT Statements 1
Which clause is used to filter rows AFTER grouping in Oracle SQL?