oracle-Sub-query

One, a subquery in the Where condition

1. What are the employees with a higher salary than black?

Select Ename

From EMP

where sal> (select Sal from emp where ename= ' BLAKE ');

2. What are the employees who have higher than 30 of the highest wages?

Select Ename,sal

From EMP

Where sal> (select Max (SAL) from EMP where deptno=30);

Select Ename,sal

From EMP

where Sal > All (select Sal from EMP where deptno=10); -Any

3. When all is followed by a subquery

"x = All (...)": The value must match all of the values in the list to evaluate to TRUE.

"X! = All (...)": The value must not match any of the values in the list to evaluate to TRUE. At least one value does not match

"X > All (...)": The value must is greater than the biggest value in the list to evaluate TRUE. Greater than the maximum value

"X < All (...)": The value must is smaller than the smallest value in the list to evaluate TRUE. Less than the minimum value

"X >= All (...)": The value must is greater than or equal to the biggest value in the list to evaluate to TRUE. Greater than or equal to the maximum value

"X <= All (...)": The value must is smaller than or equal to the smallest value in the list to evaluate to TRUE. Less than or equal to the smallest Value

4. What are the employees with a minimum wage greater than 10?

Select Ename,sal

From EMP

Where sal> (select min (sal) from EMP where deptno=10);

Select Ename,sal

From EMP

where Sal > any (select Sal from EMP where deptno=10); --any is bigger than any one, isn't that the smallest?? , any one

5. When any is followed by a subquery

"x = any (...)": The value must match one or more values from the list to evaluate TRUE. Match at least a single value

"X! = Any (...)": The value must not match one or more values from the list to evaluate to TRUE. None of the values match

"x > Any (...)": The value must is greater than the smallest value in the list to evaluate TRUE. Greater than the minimum value

"X < any (...)": The value must is smaller than the biggest value in the list to evaluate TRUE. Less than the maximum value

"x >= any (...)": The value must is greater than or equal to the smallest value in the list to evaluate to TRUE. Greater than or equal to the minimum value

"x <= any (...)": The value must is smaller than or equal to the biggest value in the list to evaluate to TRUE. Less than or equal to the maximum value

6. Who is the highest-paid person?

Select ename from emp

Where sal= (select Max (SAL) from EMP);

7. What are the employees who pay more than Martin in the same department as Allen?

Select ename from emp

where deptno= (select Deptno from emp where ename= ' ALLEN ')

and sal> (select Sal from emp where ename= ' MARTIN ');

8. What are the employees who pay more than James for the same work and department as Smith?

Select ename from emp

WHERE (Job,deptno) = (select Job,deptno from emp where ename= ' SMITH ')

and sal> (select Sal from emp where ename= ' JAMES ');

Sub-query in the FROM clause

1. What are the people who pay more than the average wage in the sector (people with upstream wages)?

① to find out the average wage for each department and use this as a table

② is associated with the EMP table and the average salary table,

Select Ename,sal,avgsal,e.deptno

From EMP E,

(select AVG (SAL) Avgsal,deptno

From EMP

Group BY Deptno) b

where E.deptno=b.deptno

and e.sal>b.avgsal;

Three, pseudo-column: rownum

Features: First the result set in the RowNum, is a number of the result set

1. Five people in the first place? (Top-n analysis)

① first to sort the wages

② using the rownum limit result set (why not use the rownum to qualify the result set in the first step?). The problem of execution order, where is executed before the order by, the time to get rownum<6 is not yet sorted out from EMP

Select Ename,sal

From EMP

where Sal in

(Select Sal

From (SELECT DISTINCT Sal from emp order by sal Desc)

where rownum<6)

ORDER BY Sal Desc;

3. Wages 6~10 people?

① first to sort the wages

② the wages in the 6~10 out, because can not use rownum>6 and xxx<10 so, so to add a step, the rownum into an ID column, and then constructed into a result set

③ take out the previous result set ID 6~10 entry

④ and EMP Associations

Select Ename,sal from emp

where Sal in

(Select Sal from

(Select RowNum rn,sal

From (SELECT DISTINCT Sal

From emp ORDER BY sal Desc))

where RN between 6 and 10)

ORDER BY Sal Desc;

This article is from "Rookie Talent" blog, please be sure to keep this source http://omphy.blog.51cto.com/7921842/1895372

oracle-Sub-query