Advanced sub-query "Weber products must be a boutique"

1. Dolez Query

   Multiple columns in the Where condition are compared to sub-queries

   Dolez query is divided into: paired comparison and non-paired comparison

   Paired comparisons:

   Select  from where inch (Selectfromwhere ename='SCOTT'); ename    SAL JOB--------------------FORD      Analystscott    4000 ANALYST

   Non-paired comparisons:

   SelectEname,sal,job fromEMPwhereDeptno= (SelectDeptno fromEmpwhereEname='SCOTT') and Job= (SelectJob fromEmpwhereEname='SCOTT') ename SAL JOB------ ----- ---------SCOTT4000Analystford theANALYST

2. Scalar-scalar query expression

   A scalar subquery is a subquery that returns a column of values from a row

   Scalar quantum queries can be used to:

   1. Conditions and Expressions section of DECODE and case

   2. All SELECT statements except GROUP BY

   Using a scalar subquery in a CASE expression: a case expression can implement the If--then--else function

   Sql>SelectEmployee_id,last_name, (
    　　      Case 
   When Department_id= (Selectdepartment_id fromDepartmentswhereLocation_id=1800)
   Then'Canda' Else'USA'End) Location
    fromemployees; employee_id last_name Locat----------- ------------------------- -----198OConnell USA199Grant USA $Whalen USA201Hartstein Canda202Fay Canda203Mavris USA204Baer USA205Higgins USA206Gietz USA -King USA101Kochhar USA        .......................
   107Rows selected.

3. Using a scalar subquery in an ORDER by clause

   Sql>SelectEname,sal,deptno fromEMP ORDER BY (SelectDname fromDeptwhereemp.deptno=Dept.deptno); ename SAL DEPTNO---------- ---------- ----------CLARK2450         TenKING the         TenMILLER1300         TenJONES2975          -FORD the          -ADAMS1100          -SMITH -          -SCOTT the          -WARD1250          -TURNER the          -ALLEN the          -JAMES950          -BLAKE2850          -MARTIN1250          - -Rows selected.

4. Inter-related subqueries

   Inter-correlated subqueries are used for row-to-row processing for each row of the primary query that is executed once: in Layman's words, the main query---> subquery---> main query. This is the order of execution.

   ---from the EMP table, the user name, department number, and salary that are more than the average salary of the department will not be displayed.
   SelectEname,deptno,sal fromEMP OuterwhereSal> (SelectAVG (SAL) fromEMP Innerwhereouter.deptno=Inner.deptno); ename DEPTNO SAL------ ------ -----ALLEN -   theJONES -  2975BLAKE -  2850SCOTT -  4000KINGTen   theFORD -   the

6. To cite an interrelated example

   ---Query employees who have at least two jobs---
   Select  from where 2<= (selectfromwhere e.employee_id=j.employee_id); employee_id last_name                 job_id----------------------------------------------          Whalen                    ad_asst        101  Kochhar                   ad_vp        176 Taylor                    Sa_ REP

7. Using exists

   The existence of the row in the EXISTS detection sub-query

   1. If the value exists in the subquery:
   Terminating a lookup in a subquery
   Condition returns True

   2. If the value does not exist in the subquery:
   Conditional return False
   Continue to find in sub-query

   ---Example: Find employees who are leaders
   Sql>SelectEmpno,ename fromEMP OuterwhereExistsSelectMgr fromEMP Innerwhereouter.empno=inner.mgr); EMPNO ename---------- ----------7566JONES7698BLAKE7782CLARK7788SCOTT7839KING7902FORD6Rows selected.

   Another way to do this:

    sql> select  empno, ename from  EMP where  empno in  ( select  Mgr from       EMP); EMPNO ename --------------------7566   7698   BLAKE  7782   CLARK  7788   SCOTT  7839   KING  7902   FORD  6  rows selected. 

8. Let's do another experiment: find people who are not leaders, that is, the most basic staff.

   Sql>SelectEmpno,ename fromEMP OuterwhereNot EXISTS (Select 1  fromEMP Innerwhereouter.empno=inner.mgr); EMPNO ename---------- ----------7369SMITH7499ALLEN7521WARD7654MARTIN7844TURNER7876ADAMS7900JAMES7934MILLER8Rows selected.
   ---Explain here: the function of the integer 1 in not exists, as long as it is an integer, the ' abc ' string is also possible.