Example : Simple Subquery Using the IN Logical Predicate
This example selects the names and department locations of those employees who report to manager Aguilar (whose employee number is 10007).
SELECT name, loc
FROM employee, department
WHERE employee.dept_no = department.dept_no
AND employee.dept_no IN (SELECT dept_no
FROM department
WHERE mgr_no = 10007);
Example : Simple Subquery Using an AVG Aggregate Expression
The following SELECT statement finds every employee in the employee table with a salary that is greater than the average salary of all employees in the table.
SELECT name, dept_no, jobtitle, salary
FROM employee
WHERE salary > (SELECT AVG(salary)
FROM employee)
ORDER BY name;
The statement returns this results table.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Vice President |
|
Example : Simple Subquery Using the ALL Logical Predicate
The following SELECT statement retrieves the employees with the highest salary and the most years of experience:
SELECT emp_no, name, job_title, salary, yrs_exp
FROM employee
WHERE (salary,yrs_exp) >= ALL (SELECT salary,yrs_exp
FROM employee);
The query returns this many rows … |
WHEN this condition evaluates to TRUE … |
0 |
employees with the highest salary do not also have the most years experience. |
1 |
there is only one employee with the highest salary and years experience. |
multiple |
there is more than one employee with the highest salary and years experience. |
For the current data in the employee table, the result looks like this:
EmpNo Name JobTitle Salary YrsExp
----- --------- --------- --------- -------
10018 Russell S President 65,000.00 25
The result shows that one employee has both the highest salary and the most years of experience.
Example : Subquery Using the SUM and COUNT Aggregate Functions
The following statement uses a nested ordered analytical function and a HAVING clause to find those items that appear in the top 1 percent of profitability in more than 20 stores:
SELECT item, COUNT(store)
FROM (SELECT store,item,profit,QUANTILE(100,profit) AS percentile
FROM (SELECT ds.store, it.item, SUM(sales)-
COUNT(sales) * it.item_cost AS profit
FROM daily_sales AS ds, items AS it
WHERE ds.item = it.item
GROUP BY ds.store, it.item, it.item_cost) AS item_profit
GROUP BY store, item, profit
QUALIFY percentile = 0) AS top_one_percent
GROUP BY item
HAVING COUNT(store) >= 20;
The results of this query might look like this:
Item Count(Store)
---------------- ------------
Chilean sea bass 99
Mackerel 149
Tako 121
Herring 120
Salmon 143
Example : Subqueries Using the RANK Functions and QUALIFY Clauses
The following example uses nested RANK functions and QUALIFY clauses to report the top 100 items by profitability and the top items by revenue, matching those that appear in both lists using an OUTER JOIN:
SELECT *
FROM (SELECT item, profit, RANK(profit) AS profit_rank
FROM item, sales
QUALIFY profit_rank <= 100 AS p)
FULL OUTER JOIN
(SELECT item, revenue, RANK(revenue) AS revenue_rank
FROM item, sales
QUALIFY revenue_rank <= 100 AS r)
ON p.item = r.item;
The results of this query might look something like the following table:
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
? |
? |
? |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
For more information related to simple subqueries, see: