Question 13

Explanation

Correct Answers:

1. Display all products whose minimum list price is more than the average list price of products having the status 'orderable'

   • You need to:

     • Compare each product’s min list price with

     • The average list price of products with status = 'orderable'

   • This requires a subquery to calculate the average list price.

   • Requires a subquery

2. Display the number of products whose list prices are more than the average list price

   • You're comparing list prices to the overall average

   • That average must be computed in a subquery

   • Requires a subquery

Incorrect Options:

Display the total number of products supplied by supplier 102 and have product status as 'OBSOLETE'

• Can be done with a single SELECT + WHERE clause + COUNT()

sql
SELECT COUNT(*) 
FROM products 
WHERE supplier_id = 102 AND product_status = 'OBSOLETE';

• Does not require a subquery

Display all products whose list price is more than 1000

• Simple WHERE condition:

sql
SELECT * FROM products WHERE list_price > 1000;

• Does not require a subquery

Display the minimum list price for each product status

• This can be done with a GROUP BY:

sql
SELECT product_status, MIN(list_price)
FROM products
GROUP BY product_status;

• Does not require a subquery

In Oracle, a subquery is a query within a query. You can create subqueries within your SQL statements. These subqueries can reside in the WHERE clause, the FROM clause, or the SELECT clause.

WHERE clause

Most often, the subquery will be found in the WHERE clause. These subqueries are also called nested subqueries.

For example:

SELECT * 
FROM all_tables tabs
WHERE tabs.table_name IN (SELECT cols.table_name
                          FROM all_tab_columns cols
                          WHERE cols.column_name = 'SUPPLIER_ID');

Limitation: Oracle allows up to 255 levels of subqueries in the WHERE clause.

FROM clause

A subquery can also be found in the FROM clause. These are called inline views.

For example:

SELECT suppliers.name, subquery1.total_amt
FROM suppliers,
 (SELECT supplier_id, SUM(orders.amount) AS total_amt
  FROM orders
  GROUP BY supplier_id) subquery1
WHERE subquery1.supplier_id = suppliers.supplier_id;

In this example, we've created a subquery in the FROM clause as follows:

(SELECT supplier_id, SUM(orders.amount) AS total_amt
 FROM orders
 GROUP BY supplier_id) subquery1

This subquery has been aliased with the name subquery1. This will be the name used to reference this subquery or any of its fields.

Limitations

Oracle allows an unlimited number of subqueries in the FROM clause.

SELECT clause

A subquery can also be found in the SELECT clause.

For example:

SELECT tbls.owner, tbls.table_name,
  (SELECT COUNT(column_name) AS total_columns
   FROM all_tab_columns cols
   WHERE cols.owner = tbls.owner
   AND cols.table_name = tbls.table_name) subquery2
FROM all_tables tbls;

In this example, we've created a subquery in the SELECT clause as follows:

(SELECT COUNT(column_name) AS total_columns
 FROM all_tab_columns cols
 WHERE cols.owner = tbls.owner
 AND cols.table_name = tbls.table_name) subquery2

The subquery has been aliased with the name subquery2. This will be the name used to reference this subquery or any of its fields.

The trick to placing a subquery in the select clause is that the subquery must return a single value. This is why an aggregate function such as SUM function, COUNT function, MIN function, or MAX function is commonly used in the subquery.