Basic summary of orcla (2)


Set operator

Notes on using set operator

Column names and expressions in the select list should correspond to * * quantity and data type * *
  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

SELECT department_id, TO_NUMBER(null) location, hire_date FROM employees UNION SELECT department_id, location_id, TO_DATE(null) FROM departments;
SELECT employee_id, job_id,salary FROM employees UNION SELECT employee_id, job_id,0 FROM job_history;
Examples of using relative position sorting

COLUMN a_dummy NOPRINT SELECT 'sing' AS "My dream", 3 a_dummy FROM dual UNION SELECT 'I"d like to teach', 1 FROM dual UNION SELECT 'the world to', 2 FROM dual ORDER BY 2;


Single column subquery

The sub query (inner query) is executed once before the main query.

SELECT select_list FROM table WHERE expr operator (SELECT select_list FROM table);

Multiple-column subqueries

1. Multi column subquery
Pairwise comparison
Question: query the manager of employee 141 or 174_ ID and department_ Employees of other employees with the same ID_ id, manager_ id, department_ id
SELECT employee_id, manager_id, department_id FROM EMPLOYEES WHERE ( 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)

Unpaired comparison
SELECT employee_id, manager_id, department_id FROM employees WHERE 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 );

2. Using subquery in from clause
Question: return the last of the employee whose average salary is higher than that of the Department_ name, department_ ID, salary and average wage

Method 1
select last_name,department_id,salary, (select avg(salary)from employees e3 where e1.department_id = e3.department_id group by department_id) avg_salary from employees e1 where salary > (select avg(salary) from employees e2 where e1.department_id = e2.department_id group by department_id )
Method 2

SELECT a.last_name, a.salary, a.department_id, b.salavg FROM employees a, (SELECT department_id, AVG(salary) salavg FROM employees GROUP BY department_id) b WHERE a.department_id = b.department_id AND a.salary > b.salavg;

3. Use single column subquery in case expression
Question: explicit employee_ id,last_ Name and location. Among them, if the employee Department_ ID and location_ Department with ID 1800_ If the ID is the same, the location is’ Canada ‘and the rest is’ USA’.
SELECT employee_id, last_name, ( CASE WHEN department_id = ( SELECT department_id FROM departments WHERE location_id = 1800 ) THEN 'Canada' ELSE 'USA' END ) location FROM employees;

4. Using single column subquery in order by clause
Question: query employee_ id,last_ Name, according to the Department of the employee_ Name sort

SELECT employee_id, last_name FROM employeese ORDER BY ( SELECT department_name FROM departments d WHERE e.department_id = d.department_id );

5. Related subquery
Question: query the last of employees whose salary is greater than the average salary of the Department_ Name, salary and its Department_ id

Question: if the employee in the employees table_ ID and job_ Employee in history table_ The number of employees with the same ID is not less than 2, and the employees of these employees with the same ID are output_ id,last_ Name and its job_ id
SELECT e.employee_id, last_name,e.job_id FROM employees e WHERE 2 <= (SELECT COUNT(*) FROM job_history WHERE employee_id = e.employee_id);

6. Related updates

Related update: use related subqueries to update data from one table to another

7. Relevant deletion

Related deletion: use related subqueries to delete data from one table based on data from another

Problem: delete the table employees, which is related to EMP_ All data in history table
DELETE FROM employees E WHERE employee_id = (SELECT employee_id FROM emp_history WHERE employee_id = E.employee_id);

Exists / not exists operator

Question: query the company manager’s employee_ id,last_ name,job_ id,department_ ID information
SELECT employee_id, last_name, job_id, department_id FROM employees outer WHERE EXISTS ( SELECT 'X' FROM employees WHERE manager_id = outer.employee_id);
Problem: in the departments table, query the Department of the Department that does not exist in the employees table_ ID and department_ name
SELECT department_id, department_name FROM departments d WHERE NOT EXISTS (SELECT 'X' FROM employees WHERE department_id = d.department_id);

With Clause

1. Using the with clause can avoid writing the same block in the select statement repeatedly
2. With Clause executes the statement block in the clause once and stores it in the user’s temporary table space
3. Using with clause can improve query efficiency

Question: query the Department information that the total salary of each department in the company is greater than the average total salary of each department in the company