Question 14

Explanation

True statements about multiple-row subqueries:

1. They can contain a subquery within a subquery. (Nested subqueries are a common feature.)

2. They can contain group functions and GROUP BY and HAVING clauses. (Subqueries are often used to aggregate data and filter based on those aggregations.)

3. They can return multiple columns as well as rows. (While single-column, multiple-row results are common for IN or ANY/ALL operators, subqueries in the FROM clause or for EXISTS can return multiple columns.)

Incorrect options:

• "They cannot contain a subquery within a subquery."

  • Why it's incorrect: This directly contradicts the true statement that "They can contain a subquery within a subquery." Nested subqueries are a powerful and common feature in SQL, allowing for complex queries where the result of one subquery feeds into another.

• "They can return only one column but multiple rows."

  • Why it's incorrect: While it's true that multiple-row subqueries often return a single column when used with operators like IN, ANY, or ALL, they are not limited to this.

    • Subqueries in the FROM clause (derived tables): These can return multiple columns and multiple rows, effectively acting like a temporary table.

    • Correlated subqueries (especially with EXISTS): While EXISTS itself doesn't return data, the subquery within it might logically be thought of as processing multiple columns internally to determine existence.

    • The statement implies a strict limitation that isn't true for all contexts where multiple-row subqueries are used.

• "They can contain group functions and the GROUP BY clause, but not the HAVING clause."

  • Why it's incorrect: Subqueries frequently use HAVING clauses, especially when performing aggregations. The HAVING clause is used to filter the groups created by the GROUP BY clause. If a subquery contains GROUP BY and a group function, it can (and often does) contain HAVING to further refine the aggregated results.

Oracle SubQueries

1. A Subquery or Nested query is a query within another SQL query and embedded within the WHERE clause. A subquery is a query within a query

2. A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.

3. Subqueries answer the queries that have multiple parts. The parent query answers a part and the sub query answers other part

Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN etc.

Using subqueries in a FROM clause is known as an inline view.

Using subqueries in the WHERE clause is called a nested subquery. Up to 255 nested queries are allowed.

Guidelines for Sql SubQueries

1. We need to put sub queries in parenthesis always

2. We need to place subqueries on the right side of the comparison operator

3. Use single row operator with single row subqueries and multiple row operator with multiple row subqueries

General Syntax

SELECT col1, col2
FROM   table1
WHERE  col1  OPERATOR
(SELECT col1
FROM table2
[WHERE])

Single Row Subquery

It returns only one row of results and uses a single row operator (most common is the equal operator (=)). The other operators are > ,< ,>= ,=<.

Single row subqueries can select data from the same table or from another table.

SELECT ENAME FROM EMP WHERE SAL = (
    SELECT MIN(SAL) FROM EMP);
 
SELECT ENAME FROM EMP WHERE DEPTNO = (
    SELECT DEPTNO FROM DEPT WHERE DNAME = 'RESEARCH');

√  They can return multiple columns as well as rows.

Multiple Row Subquery

It returns several rows of results from the subquery, uses the IN operator. In the previous query, if there was more than one research department, the query would have failed. Example of returning more than one row in the subquery.

The other operator which are used is any and all.

SELECT ENAME, DEPTNO FROM EMP
WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE DNAME LIKE 'R%');

Oracle evaluates the whole query above in two steps:

First, execute the subquery.

Second, use the result of the subquery in the outer query.

A subquery which is nested within the FROM clause of the SELECT statement is called an inline view. Note that other RDBMS such as MySQL and PostgreSQL use the term derived table instead of the inline view.

A subquery nested in the WHERE clause of the SELECT statement is called a nested subquery.

A subquery can contain another subquery. Oracle allows you to have an unlimited number of subquery levels in the FROM clause of the top-level query and up to 255 subquery levels in the WHERE clause.

√  They can contain a subquery within a subquery.

Oracle subquery with comparison operators

The subqueries that use comparison operators e..g, >, >=, <, <=, <>, = often include aggregate functions, because an aggregate function returns a single value that can be used for comparison in the WHERE clause of the outer query.

SELECT
    employee_id, first_name, last_name
FROM
    employees
WHERE
    employee_id IN(
        SELECT salesman_id
        FROM orders
        INNER JOIN order_items USING(order_id)
        WHERE status = 'Shipped'
        GROUP BY salesman_id,
            EXTRACT(
                    YEAR
                    FROM order_date)
        HAVING SUM( quantity * unit_price )  >= 1000000  
        AND EXTRACT(
                    YEAR
                    FROM order_date) = 2017
        AND salesman_id IS NOT NULL)
ORDER BY
    first_name, last_name;

√ They can contain group functions and GROUP BY and HAVING clauses.