SQL Advanced Section One (set operator && advanced subquery)

One, set operator

To concatenate multiple queries into a new query with the SET operator

Select employee_id, Department_idfrom emp01union all – equivalent to two aggregates, union A and B, intersect, a intersection b,minus difference set, A-bselect employee_i D, Department_idfrom EMP02

Considerations for Set operations

column names and expressions in the select list should correspond to the number and data types
Parentheses can change the order of execution
ORDER BY clause:
Can only appear at the end of a statement
You can use the column name, alias, or relative position in the first query

Note:
The system automatically deletes duplicate records except UNION all
The system displays the column name of the first query in the output
In addition to UNION all, the system automatically follows the first column in the first query in ascending order

SELECT department_id, to_number (null) location, Hire_datefrom   employeesunionselect department_id, location_id,  to_date (NULL) from   departments;

The columns to be queried must correspond to each other, and nothing can be replaced with null.

SELECT employee_id, Job_id,salaryfrom   employeesunionselect employee_id, job_id,0--salary can replace from Job_ with 0   History

Specifies that the column does not display the name of the column specified noprint;

Implementation by
I ' d like to teach
The world to
Sing
The SQL statement that is displayed

Select ' Sing '  "My Dream", 3 a_nufrom dualunionselect ' I ' d like to teach ', 1FROM dualunion SELECT ' The World to ', 1FROM D Ualorder by 2

sql> column A_nu noprint;--Specifies that a_nu columns are not displayed

Second, advanced sub-query
Dolez Query
The main query is compared to multiple columns returned by the subquery
Example:
Find other employees with the same manager_id and department_id as employee No. 141th or 174th
employee_id, manager_id, department_id
1) Examples of paired comparisons

Select manager_id, Employee_id,department_idfrom employeeswhere (manager_id,department_id) in (                                   select manager_id , department_id from                                   employees                                   where employee_id in (141,174)                                    ) and employee_id not in (141,174)

2) unpaired Comparisons

Select  employee_id, manager_id, Department_idfrom    employeeswhere   manager_id in  (select  manager_id                 from    employees                WHERE   employee_id in (174,141)) and   department_id in  ( SELECT  department_id                        from    employees        WHERE   employee_id in (174,141)) and employee_id not in ( 174,141);

Note that the columns of the main query and the columns within the query must correspond to each




Using subqueries in the FROM clause
Question: last_name, department_id, salary and average salary of employees who return higher average wages than the department

1) has learned to solve the knowledge:

Select Last_name,department_id,salary, (select AVG (Salary) from employees e3 where e1.department_id = e3.department_id Group by DEPARTMENT_ID) Avg_salaryfrom Employees E1where Salary >     (select AVG (Salary) from          employees E2          where e1.department_id = e2.department_id          GROUP by department_id          )

This method has a repetitive operation

2) FROM clause

Select employee_id, e1.department_id, salary, E2.avg_salfrom employees E1, (select DEPARTMENT_ID, avg (Salary) avg_sal< C5/>from Employees                    GROUP by department_id                    ) E2--in the form of a subquery, a new table is formed where e1.department_id = E2.department_idand E1.salary > E2.avg_sal

Example of single row subquery application
Use a single-column subquery in a CASE expression
Problem: Explicit employee's employee_id,last_name and location. Among them, if the employee department_id and location_id 1800 department_id the same, then location for ' Canada ', the rest is ' USA '.

/*select employee_id, last_name, (case                                 department_id if                                  (select department_id from                                       departments                                       where location_id = 1800                                       ) then                                 ' Canada ' Else ' Usa '                                 end                                 ) locationfrom Employees*/select employee_id, last_name,       (case is        department_id =                (select department_id from                 departments                where Location_ id = 1800                 ) then        ' Canada ' ELSE ' USA ' END ' locationfrom   Employees

Using a single-column subquery in an ORDER by clause
Question: Query employee's employee_id,last_name, ask to sort according to employee's Department_name

Select   employee_id, last_namefrom     Employees Eorder by (select Department_name from Departments D WHERE e.department_id = d.department_id);

third, related sub-query
Correlated subqueries are executed in a row-by-row order, and each row of the main query executes a subquery once
The outer table is used in the inner layer, which is the correlated subquery

SELECT column1, Column2, ... From   table1 outerwhere  column1 operator (SELECT  colum1, Column2                      from    table2                       WHERE   EXPR1 = OUTER.EXPR2);

Using columns from the main query in a subquery
Problem: If the employee_id in the Employees table is not less than 2 employee_id the same number as the Job_history table,
The Employee_id,last_name and its job_id of employees who output these same IDs

Select employee_id, last_name, job_id from Employees ewhere 2 <= (               select COUNT (*) from               job_history J               whe Re e.employee_id = j.employee_id            )

The EXISTS operator checks if there are rows in the subquery that meet the criteria
If there are rows in the subquery that meet the criteria:
Do not continue lookup in subquery, condition returns TRUE
If there are no rows in the subquery that meet the criteria:
The condition returns FALSE and continues to look in the subquery

Question: Query the company manager's employee_id,last_name,job_id,department_id information

/*select employee_id, last_name, Salaryfrom employees e1where e1.employee_id in (                   select manager_id from                   Employe ES E2                   ) *//*select distinct e1.employee_id, E1.last_name, e1.salaryfrom employees e1,employees E2where e1.employee_i D = E2.manager_id*/select employee_id, last_name, job_id, department_idfrom   employees outerwhere EXISTS  ( SELECT ' X '                 from   employees                 WHERE  manager_id = outer.employee_id     );

Problem: Querying the Departments table, the department_id and department_name of departments that do not exist in the Employees table

/*select d1.department_idfrom Departments d1minusselect department_id from   employees*/select department_id, Department_namefrom Departments Dwhere not EXISTS (SELECT ' X '                  from   employees                  WHERE  department_id  = d.department_id);  

SQL Advanced Section One (set operator && advanced subquery)