Oracle SQL Subquery Mastery

What is a scalar subquery in Oracle SQL?

• A scalar subquery in Oracle SQL is a subquery that returns a single row.

• A scalar subquery in Oracle SQL is a subquery that returns a single value (one row and one column). It can be used in places where an expression is expected, such as SELECT list, WHERE clause, or HAVING clause.

• A scalar subquery in Oracle SQL is a subquery that returns no values.

• A scalar subquery in Oracle SQL is a subquery that returns multiple values.

Explain the concept of a correlated subquery with an example.

• Selecting all employees whose salary is below the average salary of their department

• Using a subquery to calculate the total salary for each department and comparing it to the salary of each employee

• Filtering employees based on their job title rather than salary

• An example of a correlated subquery is selecting all employees whose salary is above the average salary of their department. This can be achieved by using a subquery to calculate the average salary for each department and then comparing it to the salary of each employee in the outer query.

How is an inline view used in Oracle SQL subqueries?

• By placing the subquery within the FROM clause and giving it an alias.

• By placing the subquery within the GROUP BY clause and giving it an alias.

• By placing the subquery within the WHERE clause and giving it an alias.

• By placing the subquery within the SELECT clause and giving it an alias.

Differentiate between nested subquery and correlated subquery.

• Nested subquery is executed after the outer query, while correlated subquery is executed before the outer query.

• Nested subquery is not dependent on the outer query, while correlated subquery is dependent on the outer query.

• Nested subquery is executed first before the outer query, while correlated subquery is executed for each row processed by the outer query.

• Nested subquery can only return a single value, while correlated subquery can return multiple values.

When would you use an EXISTS subquery in Oracle SQL?

• When you want to retrieve all columns from a table

• When you want to check for the existence of rows in a subquery result set.

• When you want to update multiple rows in a table

• When you want to sort the result set in descending order

Provide an example of a NOT EXISTS subquery in Oracle SQL.

• SELECT * FROM table1 WHERE NOT EXISTS (SELECT * FROM table2);

• SELECT * FROM table1 WHERE NOT EXISTS (SELECT * FROM table2 WHERE table1.id = table2.id);

• SELECT * FROM table1 WHERE EXISTS (SELECT * FROM table2);

• SELECT * FROM table1 WHERE EXISTS (SELECT * FROM table2 WHERE table1.id = table2.id);

What is a multi-column subquery and how is it useful?

• A multi-column subquery is a subquery that returns multiple columns of data. It is useful when you need to retrieve related data from different tables in a single query.

• A multi-column subquery is a subquery that returns only one column of data.

• A multi-column subquery is a subquery that returns data from a single table.

• A multi-column subquery is a subquery that returns data in random order.

Illustrate a scenario where a multi-row subquery is necessary.

• Illustrate a scenario where a multi-row subquery is necessary.

• Multi-row subqueries can only be used in specific database systems

• Using a single-row subquery would achieve the same result

• Multi-row subqueries are never necessary

How can you optimize performance when using subqueries in Oracle SQL?

• Increase the number of subqueries

• Avoid using WHERE clause

• Use GROUP BY clause

• Use EXISTS or IN clauses, avoid correlated subqueries, ensure proper indexing, and limit the number of rows returned.

Explain the difference between a subquery and a join in Oracle SQL.

• In Oracle SQL, a subquery is a query nested within another query, returning a single value or set of values to be used by the outer query. A join is used to combine rows from two or more tables based on a related column between them.

• A join is a query nested within another query, returning a single value or set of values to be used by the outer query.

• A subquery is used to combine rows from two or more tables based on a related column between them.

• A subquery is used to return all columns from a single table, while a join is used to filter specific columns from multiple tables.