Oracle Advanced Query 2 sub-query

Tag: Group does not produce order by STS first syntax value Ora

Sub-query:

A definition is a query that is nested within a query.
Subqueries can be present in many places, such as columns, tables, conditions, etc.

Grammar:
SELECT (sub-query)
From (sub-query)
WHERE (sub-query)
GROUP BY clause
Having (sub-query)
ORDER BY clause

Note: Sub-queries are enclosed in parentheses.

Such as:
-Find employees in the same department as Ben
First step: Find out where Ben is.
Select dept_id from s_emp where first_name = ' Ben ';
--Step Two: Find out all the employees in the department
Select First_Name from s_emp where dept_id = numeric
--Step Three:
Select First_Name from s_emp where dept_id = (select dept_id from s_emp where first_name = ' Ben ') and first_name! = ' Ben ';

Sub-query classification
1. Unrelated subqueries
Refers to a variable/alias that is not defined in the subquery using an external query.

2. Related sub-query
Refers to a variable/alias that is defined in a subquery to use an external query.

RowNum

It always starts at 1, increments in turn, never produces gap "interval"

Such as:
Select D.*,rownum from S_dept d where rownum = 1;
Select D.*,rownum from S_dept d where rownum = 5;
Select D.*,rownum from S_dept D where rownum > 5;
Select D.*,rownum from S_dept D where rownum < 5;

--The role of rownum

2. Paging Query problems

--Check out the data in the Employee table on page 2nd [line 7th to 12th].
Error
SELECT * from S_emp where rownum between 7 and 12;
SELECT * from S_emp where rownum >=7 and rownum<= 12;
Correct: Method One
SELECT * FROM (
--the middle layer, is responsible for the upper line to take out
Select V1_.*,rownum Rw_ from (
--The core statement of the business
Select Id,first_name,salary from S_emp
) v1_
where RowNum <= 12
) v2_
where V2_.rw_ >= 7;

Method Two:
SELECT * FROM
(SELECT * from s_emp where rownum <=12)
where ID not in (SELECT ID from s_emp where rownum <7);

About the exists and not EXISTS operators
exists is used to determine if the subquery has a result, if any, returns true, otherwise, returns false

About collection operations for subqueries
UNION returns the set of two subqueries with no duplicate records
UNION all returns the set of two subqueries that contain the duplicate records

Such as:
Select Id,first_name from s_emp where salary > 1200
Union
Select Id,first_name from S_emp where salary between 850 and 1400;

INTERSECT return intersection
Minus two sub-query subtraction

Oracle Advanced Query 2 sub-query