Oracle Notes subquery

Sub-query

When one of our operations needs to be based on another query demerit, then the query that is executed first is the subquery

Sub-queries are divided into:

Single-row subquery: The result is only one row, and there is only a single field

Multiline single-row subquery: Query results have multiple rows, but only one column multi-row multi-column subquery

Query out more than one row of columns.

Typically, single-row and multiline multi-column subqueries are used in a WHERE clause and multiple rows in a multicolumn subquery are used to

FROM clause.

--View other employees in the same position as Scott

Select Ename,sal,job from emp where job= (select Job from emp where ename= ' SCOTT ')

and ename <> ' SCOTT ';

--See which employees pay above average

Select Ename,sal from emp WHERE sal> (select AVG (SAL) from EMP);

--Title: View information about other positions in the company and salesman in the same department

--The first step:

SELECT Ename,deptno from emp WHERE job= ' salesman ';

--The second step:

Select Ename,job,deptno from emp where Deptno in (SELECT deptno from EMP where

job= ' salesman ') and job<> ' salesman ';

--View other employees who have a higher salary than all 20 departments

SELECT Ename,sal,deptno from emp WHERE deptno = 20;

Select Ename,sal,deptno from emp where Sal>all (select Sal from emp where Deptno =20);

exists function, when a subquery can return at least one record, then the expression returns True, the following example shows: View the employee

The Department

Select Deptno,dname from Dept D where EXISTS (SELECT * from emp e where d.deptno = E.deptno);

--See the department with minimum salary above the minimum wage of unit 30th

Select Deptno,min (SAL) "minimum wage" from the EMP GROUP by Deptno have MIN (sal) > (SELECT min (sal) from EMP WHERE deptno=30);

--See the department with minimum salary above the minimum wage of unit 30th

Select Deptno,min (SAL) "minimum wage" from the EMP GROUP by Deptno have MIN (sal) > (SELECT min (sal) from EMP WHERE deptno=30);

--Remove Duplicates

SELECT DISTINCT deptno from EMP;

--Query for information on employees with an average salary higher than the department

The idea here is that we should first count the average wage for each department because the result of this query is more than one row and many columns, so we will

Look at it as a table, and then use the EMP table with its associated query. Therefore, multi-row multi-column subqueries are generally used after the FROM clause.

Subqueries written in--from, commonly referred to as inner views

Select E.ename,e.sal,e.deptno from emp E, (select AVG (SAL) Avg_sal,deptno from EMP

GROUP by Deptno) x WHERE e.deptno = X.deptno and e.sal>x.avg_sal;

--View other employees in the same position as Scott

Select Ename,sal,job from emp where job= (select Job from emp where ename= ' SCOTT ')

and ename <> ' SCOTT ';

--See which employees pay above average

Select Ename "Name", Sal "Payroll" from the EMP WHERE sal> (SELECT AVG (SAL) from EMP);

--Title: View information about other positions in the company and salesman in the same department

--The first step:

SELECT Ename,deptno from emp WHERE job= ' salesman ';

--The second step:

Select Ename,job,deptno from emp where Deptno in (SELECT deptno from EMP where

job= ' salesman ') and job<> ' salesman ';

--View other employees who have a higher salary than all 20 departments

SELECT Ename,sal,deptno from emp WHERE deptno = 20;

Select Ename,sal,deptno from emp where Sal>all (select Sal from emp where Deptno =20);

--exists function, when a subquery can return at least one record, then the expression returns True, the following example shows: View the employee

--The Department

Select Deptno,dname from Dept D where EXISTS (SELECT * from emp e where d.deptno=e.deptno);

--See the department with minimum salary above the minimum wage of unit 30th

Select Deptno,min (SAL) "minimum wage" from the EMP GROUP by Deptno have MIN (sal) > (SELECT min (sal) from EMP WHERE deptno=30);

--Remove Duplicates

SELECT DISTINCT deptno from EMP;

--Query for information on employees with an average salary higher than the department

The idea here is that we should first count the average wage for each department because the result of this query is more than one row and many columns, so we will

Look at it as a table, and then use the EMP table with its associated query. Therefore, multi-row multi-column subqueries are generally used after the FROM clause.

Subqueries written in--from, commonly referred to as inner views

Select E.ename,e.sal,e.deptno from emp E, (select AVG (SAL) Avg_sal,deptno from EMP

GROUP by Deptno) x WHERE e.deptno = X.deptno and e.sal>x.avg_sal;

--The subquery can also appear in the SELECT clause, and the effect is usually an outer join effect, if the value of the Deptno field in the EMP table is associated

--The value is displayed as NULL when query data is not queried in the Dept table

Select E.ename,e.sal, (select D.deptno from dept d WHERE D.deptno=e.deptno) Deptno from EMP e;

--Paging, get all the records in batches, purpose: Speed up the query, reduce system resource consumption

--minimum paging required, for record numbering, and sorting

--Number: Pseudo-column that can be used in Oralce rownum rownum itself is not in the table, use him as a column

--The value is the number derived from the query data in the table, and Oracle automatically generates the value of the column

SELECT * from EMP;

SELECT * FROM (select ROWNUM rn,e.ename "name", e.job "work", e.sal "payroll" from emp e ORDER by "payroll" DESC) WHERE

RN between 5 and 10;

SELECT Ename,job,sal,decode (Job,

' MANAGER ', sal*1.2,

' ANALYST ', sal*1.1,

' Salesman ', sal*1.05,sal

) Bouns

from EMP;

--the manager and analyst two positions as a group, other positions as another group, statistics of the total number of these two groups,

--thinking: the data that needs to be seen as a group, but with different values, we use decode to change them to the same value.

SELECT DECODE (Job, ' MANAGER ', ' VIP ', ' ANALYST ', ' VIP ', ' OPERATIONS ') NAME, COUNT (*) from EMP

GROUP by DECODE (Job, ' MANAGER ', ' VIP ', ' ANALYST ', ' VIP ', ' OPERATIONS ');

SELECT deptno,dname from Dept ORDER by DECODE (dname, ' OPERATIONS ', 1, ' accpounting ', 2, ' SALES ', 3);

--the number of consecutive unique numbers in a group, in descending order of wages, in accordance with departmental groupings:

SELECT Ename,deptno,sal,row_number () over

(PARTITION by Deptno Order by Sal DESC) rank from the Emp;--rank function differs from row_number in that the sort field is the same value

--and when they're in the same group, the numbers they get are the same, but in the following numbers there will be jumps, and rank will generate discontinuous and not unique numbers within the group.

--dense_rank () produces a continuous, unique

SELECT Ename,deptno,sal, Dense_rank () over (PARTITION by Deptno ORDER by Sal DESC) RANK from EMP;

--Collection operation: The set, combining all the elements of the two set into a collection of common and full-set.

--Full union: will produce duplicate elements, two elements of the set, will appear in the new collection after merging two times.

--Intersection: The new collection retains only the elements of the two set

--Difference set: The new collection only saves I have elements that you do not have.

--Difference Set

Select Ename,job,sal from emp where job = ' MANAGER ' minus SELECT ename,job,sal from EMP where sal>2500;

--General and set

Select Ename,job,sal from emp where job = ' MANAGER ' UNION SELECT ename,job,sal from EMP where sal>2500;

--Full Set

Select Ename,job,sal from emp where job = ' MANAGER ' UNION all SELECT ename,job,sal from EMP where sal>2500;

--Intersection

Select Ename,job,sal from emp where job = ' MANAGER ' INTERSECT SELECT ename,job,sal from EMP where sal>2500;

SELECT * from Sales_tab;

SELECT Year_id,month_id,day_id,sum (sales_value) from Sales_tab GROUP by

GROUPING Sets ((year_id,month_id,day_id), (year_id,month_id))

ORDER by year_id,month_id,day_id;

This article is from "Fang Wenjun's It Technology blog" blog, make sure to keep this source http://fangwenjun.blog.51cto.com/7806977/1689583

Oracle Notes subquery