Shulou(Shulou.com)06/01 Report--

Subquery

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

Subqueries are divided into:

Single-row, single-column subquery: the result of the query has only one row and only one field

Multi-row single-column subquery: the result of the query has multiple rows, but only one column, multiple rows, and multiple columns.

Query multiple rows and multiple columns.

In general, single-row, single-column and multi-row, multi-column subqueries are used in the where clause, while multi-row, multi-column subqueries are used for

In the 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'

-- check which employees earn above average

SELECT ename,sal FROM emp WHERE sal > (SELECT AVG (sal) FROM emp)

-- title: view the information of employees in other positions in the same department as SALESMAN.

-- the first step:

SELECT ename,deptno FROM emp WHERE job='SALESMAN'

-second step:

SELECT ename,job,deptno FROM emp WHERE deptno IN (SELECT deptno FROM emp WHERE

Job='SALESMAN') AND job'SALESMAN'

-- look at other employees who earn more than all employees in department 20

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)

The role of EXISTS, when the subquery can return at least one record, then the expression returns true, the following example says: view contains employees

The department

SELECT deptno,dname FROM dept d WHERE EXISTS (SELECT * FROM emp e WHERE d.deptno = e.deptno)

-- check the departments where the minimum salary is higher than the minimum salary in Department 30.

SELECT deptno,MIN (sal) minimum wage FROM emp GROUP BY deptno HAVING MIN (sal) > (SELECT MIN (sal) FROM emp WHERE deptno=30)

-- check the departments where the minimum salary is higher than the minimum salary in Department 30.

SELECT deptno,MIN (sal) minimum wage FROM emp GROUP BY deptno HAVING MIN (sal) > (SELECT MIN (sal) FROM emp WHERE deptno=30)

-- remove duplicates

SELECT DISTINCT deptno FROM emp

-- search for information about employees who earn more than the average salary of their department.

The idea here is that we should first calculate the average salary of each department because the query result is multi-row and multi-column, so we will

-- treat it as a table, and then use the EMP table to associate the query with it. Therefore, multi-row and multi-column subqueries are generally used after the FROM clause.

Subqueries written in FROM, commonly referred to as inner views

SELECT e.enameree.salre 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'

-- check which employees earn above average

SELECT ename "name", sal "salary" FROM emp WHERE sal > (SELECT AVG (sal) FROM emp)

-- title: view the information of employees in other positions in the same department as SALESMAN.

-- the first step:

SELECT ename,deptno FROM emp WHERE job='SALESMAN'

-second step:

SELECT ename,job,deptno FROM emp WHERE deptno IN (SELECT deptno FROM emp WHERE

Job='SALESMAN') AND job'SALESMAN'

-- look at other employees who earn more than all employees in department 20

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)

-- the role of EXISTS, when at least one record can be returned in a subquery, then the expression returns true. The following example shows that the view contains employees.

-- Department of

SELECT deptno,dname FROM dept d WHERE EXISTS (SELECT * FROM emp e WHERE d.deptno=e.deptno)

-- check the departments where the minimum salary is higher than the minimum salary in Department 30.

SELECT deptno,MIN (sal) minimum wage FROM emp GROUP BY deptno HAVING MIN (sal) > (SELECT MIN (sal) FROM emp WHERE deptno=30)

-- remove duplicates

SELECT DISTINCT deptno FROM emp

-- search for information about employees who earn more than the average salary of their department.

The idea here is that we should first calculate the average salary of each department because the query result is multi-row and multi-column, so we will

-- treat it as a table, and then use the EMP table to associate the query with it. Therefore, multi-row and multi-column subqueries are generally used after the FROM clause.

Subqueries written in FROM, commonly referred to as inner views

SELECT e.enameree.salre 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

Subqueries can also appear in the SELECT clause. The effect usually achieved is the outer join effect, if the values of the deptno field in the emp table are associated.

When there is no query data in the query dept table, the value is displayed as null

SELECT e.enamejie.sal, (SELECT d.deptno FROM dept d WHERE d.deptno=e.deptno) deptno FROM emp e

-- pagination to obtain all records in batches. Purpose: to speed up the query and reduce the consumption of system resources

Paging at least requires numbering and sorting of records

-- number: pseudo column ROWNUM that has used ROWNUM in ORALCE is not in the table itself, use it as a column

-- the value is the number of the data queried from the table, and ORACLE automatically generates the value of this column

SELECT * FROM emp

SELECT * FROM (SELECT ROWNUM rn,e.ename "name", e.job "work", e.sal "salary" FROM emp e ORDER BY "wage" 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

Take the positions of MANAGER and ANALYST as one group and the other positions as another group, and count the total number of people in these two groups.

Idea: for those data that need to be seen as a group, but with different values, we can use DECODE to change them to the same values.

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)

-- grouping according to departments and according to the descending order of wages to produce continuous and unique figures in the group:

SELECT ename,deptno,sal,ROW_NUMBER () OVER

(PARTITION BY deptno ORDER BY sal DESC) the difference between the rank FROM emp;--rank function and ROW_NUMBER is that the fields that are sorted are of the same value

-- and when they are in the same group, then they get the same number, but there will be a jump in the following number, and RANK will generate discontiguous and not unique numbers within the group.

-- DENSE_RANK () produces a continuous and unique

SELECT ename,deptno,sal, DENSE_RANK () OVER (PARTITION BY deptno ORDER BY sal DESC) rank FROM emp

In the set operation: Union, all the elements in the two sets are gathered into a common union and a full union.

-- full union: repeating elements are generated, and elements that exist in both sets appear twice in the new collection after the merge.

Intersection: only elements that exist in both sets are retained in the new collection

Subtraction: only elements that I have and you don't have are saved in the new collection.

-- difference set

SELECT ename,job,sal FROM emp WHERE job = 'MANAGER' MINUS SELECT ename,job,sal FROM emp WHERE sal > 2500

-- ordinary union

SELECT ename,job,sal FROM emp WHERE job = 'MANAGER' UNION SELECT ename,job,sal FROM emp WHERE sal > 2500

-- full union

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.