Oracle SQL Oracle SQL Joins and Subqueries 2 — Questions and Answers
Question 1: Which Oracle SQL operator checks if a subquery returns at least one row?
- IN
- ANY
- EXISTS (Correct answer)
- SOME
Correct answer: EXISTS
EXISTS returns TRUE if the subquery produces at least one row, and is often more efficient than IN for large datasets.
Question 2: What is an inline view in Oracle SQL?
- A view stored in the database
- A subquery used in the FROM clause (Correct answer)
- A view with no data
- A synonym for a table
Correct answer: A subquery used in the FROM clause
An inline view is a subquery placed in the FROM clause, treated like a temporary table within the query.
Question 3: What does a correlated subquery do in Oracle SQL?
- Runs once and returns a single value
- References a column from the outer query and executes once per outer row (Correct answer)
- Creates a permanent view
- Performs a full table scan only once
Correct answer: References a column from the outer query and executes once per outer row
A correlated subquery references columns from the outer query and is re-executed for each row processed by the outer query.
Question 4: Which keyword allows you to join tables using a column with the same name in both tables in Oracle SQL?
- ON
- USING (Correct answer)
- WHERE
- NATURAL JOIN
Correct answer: USING
The USING clause joins tables on a column that has the same name in both tables, and the column is not qualified with a table prefix in the result.
Question 5: What does the NATURAL JOIN keyword do in Oracle SQL?
- Joins tables based on all columns with the same name (Correct answer)
- Joins tables based on primary key relationships
- Returns only distinct rows from joined tables
- Performs a full outer join automatically
Correct answer: Joins tables based on all columns with the same name
NATURAL JOIN automatically joins tables on all columns that share the same name and data type in both tables.
Question 6: Which type of subquery is used with the ANY operator in Oracle SQL?
- Scalar subquery returning one value
- Multi-row subquery returning multiple values (Correct answer)
- Correlated subquery with no outer reference
- Inline view in FROM clause
Correct answer: Multi-row subquery returning multiple values
The ANY operator compares a value against each value returned by a multi-row subquery, returning TRUE if any comparison is satisfied.
Oracle SQL Oracle SQL Joins and Subqueries 2
Which Oracle SQL operator checks if a subquery returns at least one row?