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.
name
---- |
dept_no
------- |
jobtitle
-------- |
salary
------ |
---|---|---|---|
Aguilar J | 600 | Manager | 45,000.00 |
Carter J | 500 | Engineer | 44,000.00 |
Omura H | 500 | Programmer | 40,000.00 |
Phan A | 300 | Vice President | 55,000.00 |
Regan R | 600 | Purchaser | 44,000.00 |
Russell S | 300 | President | 65,000.00 |
Smith T | 500 | Engineer | 42,000.00 |
Smith T | 700 | Manager | 45,000.00 |
Watson L | 500 | Vice President | 56,000.00 |
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:
item
---- |
profit
------ |
profitrank
---------- |
item
---- |
revenue
------- |
revenuerank
----------- |
---|---|---|---|---|---|
Dress slacks | 17804 | 74 | Dress slacks | 180211 | 56 |
Dress shirts | 16319 | 68 | ? | ? | ? |
Dresses | 55888 | 82 | Dresses | 652312 | 77 |
Blouses | 9849 | 48 | Blouses | 771849 | 92 |
? | ? | ? | Skirts | 817811 | 55 |