Exam 1z0-051 topic 2 question 116 discussion

Actual exam question from Oracle's 1z0-051

Question #: 116
Topic #: 2

[All 1z0-051 Questions]

Which two statements are true regarding subqueries? (Choose two.)

A. A subquery can retrieve zero or more rows.
B. Only two subqueries can be placed at one level.
C. A subquery can be used only in SQL query statements.
D. A subquery can appear on either side of a comparison operator.
E. There is no limit on the number of subquery levels in the WHERE clause of a SELECT statement.

Show Suggested Answer

Suggested Answer: AD 🗳️
Using a Subquery to Solve a Problem
Suppose you want to write a query to find out who earns a salary greater than Abels salary.
To solve this problem, you need two queries: one to find how much Abel earns, and a second query to find who earns more than that amount.
You can solve this problem by combining the two queries, placing one query inside the other query. The inner query (or subquery) returns a value that is used by the outer query (or main query).
Using a subquery is equivalent to performing two sequential queries and using the result of the first query as the search value in the second query.

Subquery Syntax -
A subquery is a SELECT statement that is embedded in the clause of another SELECT statement. You can build powerful statements out of simple ones by using subqueries. They can be very useful when you need to select rows from a table with a condition that depends on the data in the table itself.
You can place the subquery in a number of SQL clauses, including the following:

WHERE clause -

HAVING clause -

FROM clause -
In the syntax:
operator includes a comparison condition such as >, =, or IN
Note: Comparison conditions fall into two classes: single-row operators (>, =, >=, <, <>, <=) and multiple-row operators (IN, ANY, ALL, EXISTS).
The subquery is often referred to as a nested SELECT, sub-SELECT, or inner SELECT statement. The subquery generally executes first, and its output is used to complete the query condition for the main (or outer) query.

Guidelines for Using Subqueries -
Enclose subqueries in parentheses. Place subqueries on the right side of the comparison condition for readability. (However, the subquery can appear on either side of the comparison operator.) Use single-row operators with single-row subqueries and multiple-row operators with multiple-row subqueries.
Subqueries can be nested to an unlimited depth in a FROM clause but to "only" 255 levels in a WHERE clause. They can be used in the SELECT list and in the
FROM, WHERE, and HAVING clauses of a query.

by thuthuysphn58 at April 4, 2025, 7:13 a.m.

Comments

Submit Cancel

Currently there are no comments in this discussion, be the first to comment!