Advanced query and paging query of OracleSQL, and advanced query of oraclesql

Advanced query and paging query of OracleSQL, and advanced query of oraclesql

1. subquery

A subquery is a SELECT statement, but it is nested in other SQL statements to provide data for this SQL statement to support its operations.

Check who has higher salary than CLARK? Select ename, sal from emp where sal> (select sal from emp where ename = 'clark ')

View employees in the same position as CLARK? Select ename, job from emp where job = (select job from emp where ename = 'clark ')

View employees in the same department as CLARK? Select ename, deptno from emp where deptno = (select deptno from emp where ename = 'clark ')

Create a table "employee" with the following fields:

Empno, ename, job, sal, deptno, dname, loc

The data is the data corresponding to emp and dept in the existing table.

create table employee asselect e.empno,e.ename,e.job,e.sal,e.deptno,d.dname,d.locfrom emp e,dept dwhere e.deptno=d.deptno(+)

 

When creating a table, if the field in the subquery has an alias, the field corresponding to the table uses this alias as its field name. When a field in the subquery contains a function or expression, this field must be an alias.

create table employee asselect e.empno id,e.ename name,e.job,e.sal,e.deptno,d.dname,d.locfrom emp e,dept dwhere e.deptno=d.deptno(+)

Delete all employees of CLARK's Department

delete from employee where deptno=(select deptnp from employee where name=’clark’)

2. subquery type

Subqueries are often used in SELECT statements. subqueries are divided into different query result sets:

Single-row Single-Column subquery: Used in filtering conditions. It can be used with =,>, >=, <, <=

Multi-row, single-column subquery: It is often used IN filtering conditions. Because multiple values are queried, IN is used for determining =,

Judge>,> =, and other operations must be combined with ANY, AOR

Multi-row and multi-column subqueries are often treated as a table.

Query employees in other positions in the same department as salesman:

select ename,job,deptno from emp where deptno in(select deptno from emp where job=’salesman’) and job<>’salesman’

Check the employees who have higher salaries than the employees in the positions clerk and salesman.

select ename,sal from emp where sal >ALL(select sal from emp where job in(‘clerk’,’salesman’))

3. EXISTS keywords

Exists is followed by a subquery. When this subquery can query a record, the exists expression is true.

select deptno,dname from dept d where exists(select *from emp e where  d.deptno=e.deptno)

Check the minimum salary for each department? The premise is that the minimum salary for this department is higher than the minimum salary for department 30.

Old method:

(select min(sal),deptno from emp group by deptno )(having min(sal>950))

New approaches:

select min(sal),deptno from emp group by deptno having min(select min(sal) from emp group by deptno)

View employees with higher average salaries than their own departments?

Old method:

（select avg(sal),deptno from emp group by deptno）(select e.ename,e.sal,e.deptno from emp e,ttt t where e.deptno=t.deptno and e.sal>t.avg(sal))

New approaches:

select e.ename,e.sal,e.deptno from emp e,（select avg(sal) avg_sal,deptno from emp group by deptno） t where e.deptno=t.deptno and e.sal>t.avg_sal

Use a subquery in the select clause to display the query result as a field value in the outer query record.

select e.ename,e.sal,(select d.dname from dept d where d.deptno=e.deptno) dep from emp e

4. Paging Query

Paging queries query data segments in a table, rather than querying all data at once. Sometimes the amount of data queried is very large, which leads to high system resource consumption, long response speed, and serious data redundancy. In this case, paging queries are generally used. Databases support paging, but different database syntaxes (dialects) are different ).

Pagination in ORACLE is implemented based on the pseudo-column ROWNUM.

ROWNUM does not exist in any table, but all tables can query this field. The value of this field is automatically generated with the query, and the value of this field is the row number of this record, start from 1 and increase one by one.

When you use ROWNUM to encode a result set, you cannot use ROWNUM to determine the number greater than 1. Otherwise, no number can be found.

select * from(select rownum rn,empno,ename,sal,job from emp) where rn between 6 and 10.

Note: Use rownum to compile all numbers as a table.

Example:View the salary ranking 6-10 (nested two subqueries: sorting, number, and query)

select *from( select rownum rn, t.*from (select empno,ename,sal from emp order by sal desc）t)where rn between 6 and 10

Improve Efficiency

select *from(select rownum rn,t.*from(select empno,ename,sal from emp order by sal desc) twhere rownum<=10)where rn>=6

From (select rownum rn, t .*

From (select empno, ename, sal from emp order by sal desc) t

Where rownum <= 10)

Where rn> = 6

Calculation interval:

PageSize: Number of IDE entries per page

Page: page number

Star :( page-1) * pageSize + 1

End: pageSize * page;

5. DECODE Function

select ename,job,sal,decode(job,’MANAGER’,sal*1.2,‘ANALYST’,sal*1.1,‘SALESMAN’,sal*1.15,sal) bonus        from emp;

CASE statements are similar to DECODE functions.

select ename,job,sal,CASE job WHEN ’MANAGER’ THEN sal*1.2WHEN‘ANALYST’THEN sal*1.1,WHEN‘SALESMAN’THEN sal*1.15,ELSE sal END bonus        from emp;

DEOCE's Application in group by groups can regard records with different field values as a GROUP.

Count the number of people. The positions are "MANAGER" and "ANALYST" as a group, and the remaining occupations are regarded as the number of people in another group.

selectcount(*),decode(job,'MANAGER','VIP','AVALYST','VIP','OTHER') from emp group by decode(job,'MANAGER','VIP','AVALYST','VIP','OTHER')

6. sorting functions

The sorting function allows the result set to be grouped by specified fields, and then sorted by specified fields in the group to generate the group number.

The ROW_NUMBER function generates consecutive and unique numbers in the group:

View the salary ranking of each department?

select ename,sal,deptno,row_number() over(PARTITION BY deptnoORDER BY sal DESC  )rankFROM emp

RANK function, which generates non-consecutive numbers in the group. records with the same sorting Field Values in the group are the same as those generated.

View the salary ranking of each department?

select ename,sal,deptno,RANK() over(PARTITION BY deptnoORDER BY sal DESC)  )rankFROM emp 

The DENSE_RANK function generates consecutive but not unique numbers in the group.

select ename,sal,deptnoDENSE_RANK() OVER(PARTITION BY deptnoORDER BY sal DESC)rankfrom emp

7. Advanced grouping Functions

The combined job is a set of 'manager' employees and employees with salaries greater than 2500. view the differences between the two methods: select ename, job, sal from emp where job = 'manager' UNIONselect ename, job, sal from empwhere sal> 2500;

select ename,job,sal from empwhere job='MANAGER'UNION ALLselect ename,job,sal from empwhere sal>2500;

Intersection SELECT ename, job, sal from empwhere job = 'manager' INTERSECTSELECT ename, job, sal, from empwhere sal> 2500;

Difference set SELECT ename, job, sal, from empwhere job = 'manager' MINUSSELECT ename, job, sal from empwhere sal >=2500;

The advanced grouping function is used in the group by clause. Each advanced grouping function has a set of grouping policies.

ROLLUP (): grouping principle. The parameters are progressively decreasing until all parameters are specified. Each group counts the results and displays them in a result set.

CUBE (): Each combination is divided into one group. Number of groups: the power of the number of 2 parameters