Oracle learning notes subquery

Subquery when one of our operations needs to be recorded based on another query, the query that is executed first is that subquery is divided into: Single Row, single column, subquery: query results only

Subquery when one of our operations needs to be recorded based on another query, the query that is executed first is that subquery is divided into: Single Row, single column, subquery: query results only

Subquery

When one of our operations needs to be recorded based on another query, the first query to be executed is the subquery.

Subqueries are divided:

Single-row, single-column, and subquery: Only one row is queried and only one field exists.

Multi-row, single-column subquery: the query results have multiple rows, but only one column, multiple rows, and multiple-column subqueries

Multiple columns in multiple rows are queried.

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 in

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 have higher salaries than average

SELECT ename, sal FROM emp WHERE sal> (select avg (sal) FROM emp );

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

-- Step 1:

SELECT ename, deptno FROM emp WHERE job = 'salesman ';

-- Step 2:

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

Job = 'salesman') AND job <> 'salesman ';

-- View other employees with higher salaries 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 );

EXISTS function. if at least one record can be returned in a subquery, the expression returns true. The following example shows that

Department

SELECT deptno, dname FROM dept d where exists (SELECT * FROM emp e WHERE d. deptno = e. deptno );

-- Check the department with a minimum salary higher than the minimum salary of Department 30

SELECT deptno, MIN (sal) "lowest salary" FROM emp group by deptno having min (sal)> (select min (sal) FROM emp WHERE deptno = 30 );

-- Check the department with a minimum salary higher than the minimum salary of Department 30

SELECT deptno, MIN (sal) "lowest salary" FROM emp group by deptno having min (sal)> (select min (sal) FROM emp WHERE deptno = 30 );

-- Remove duplicate items

Select distinct deptno FROM emp;

-- Query Information about employees with higher average salaries than the Department

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

-- It can be viewed as a table, and then associated queries can be performed using the EMP table. Therefore, multi-row and multi-column subqueries are generally used after the FROM clause.

-- Subquery written in FROM, generally called internal view

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 ';

-- Check which employees have higher salaries than average

SELECT ename "name", sal "salary" FROM emp WHERE sal> (select avg (sal) FROM emp );

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

-- Step 1:

SELECT ename, deptno FROM emp WHERE job = 'salesman ';

-- Step 2:

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

Job = 'salesman') AND job <> 'salesman ';

-- View other employees with higher salaries 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 );

-- EXISTS function. if at least one record can be returned in a subquery, the expression returns true. The following example shows that

-- Department

SELECT deptno, dname FROM dept d where exists (SELECT * FROM emp e WHERE d. deptno = e. deptno );

-- Check the department with a minimum salary higher than the minimum salary of Department 30

SELECT deptno, MIN (sal) "lowest salary" FROM emp group by deptno having min (sal)> (select min (sal) FROM emp WHERE deptno = 30 );

-- Remove duplicate items

Select distinct deptno FROM emp;

-- Query Information about employees with higher average salaries than the Department

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

-- It can be viewed as a table, and then associated queries can be performed using the EMP table. Therefore, multi-row and multi-column subqueries are generally used after the FROM clause.

-- Subqueries written in FROM are generally called internal 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;

-- Subqueries can also appear in the SELECT clause. Generally, the effect is the external join effect. If the value of the deptno field in the emp table is associated

-- When no data is queried in the dept table, the value is null.

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

-- Retrieve all records in batches by page. Objective: To accelerate query and reduce system resource consumption

-- At least the number and sorting of records are required for paging.

-- No.: In ORALCE, the pseudo-column ROWNUM that can use ROWNUM is not in the table and is used as a column.

-- The value is the number from the data queried in the table. Oracle automatically generates the value of this column.

SELECT * FROM emp;

SELECT * FROM (select rownum rn, e. ename "name", e. job "job", e. sal "salary" FROM emp e order by "salary" 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 positions are regarded as one group, the other positions are considered as another group, and the total number of the two groups is counted,

-- Idea: we can use DECODE to change the data that needs to be viewed as a group with different values to the same value.

Select decode (job, 'manager', 'vip ', 'analyst', 'vip', 'operation') NAME, COUNT (*) FROM emp

Group by decode (job, 'manager', 'vip ', 'analyst', 'vip', 'operation ');

SELECT deptno, dname FROM dept order by decode (dname, 'operations ', 1, 'access', 2, 'sales', 3 );

-- Group by department and generate a unique number in the group in descending order of salary:

SELECT ename, deptno, sal, ROW_NUMBER () OVER

(Partition by deptno order by sal DESC) rank FROM emp; -- the difference between the rank function and ROW_NUMBER is that if the values of the sorted fields are the same

-- When they are in the same group, the numbers they get are the same. However, the numbers below will jump and RANK will generate non-sequential and non-Unique Numbers in 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;

-- Set operation: Union, which combines all element sets in the two sets into a common Union and full Union.

-- Full Union: duplicate elements are generated. Elements of both sets are merged and appear twice in the new set.

-- Intersection: only elements of both sets are retained in the new set.

-- Difference set: only elements that you do not have are saved in the new set.

-- Difference set

SELECT ename, job, sal FROM emp WHERE job = 'manager' minus select ename, job, sal FROM emp WHERE sal> 2500;

-- Common 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

Grouping sets (year_id, month_id, day_id), (year_id, month_id ))

Order by year_id, month_id, day_id;

This article permanently updates the link address: