Sixteen advanced subqueries of Oracle9i Study Notes

1. subquery Review
 
Select select_list
From table
Where expr operator (select select_list
From table );

Example 1: query the EMP table to display information about employees whose salaries are higher than the average salaries.

Select *
From EMP
Where SAL> (select AVG (SAL)
From EMP );

Example 2: display detailed information about an employee, requiring the manager and Department of the employee to be the same as employee number 7521 or 7698, excluding information about the employee whose empno is 7521 or 7698

Select *
From EMP
Where (MGR, deptno) in (select Mgr, deptno
From EMP
Where empno in (7521,7698 ))
And empno not in (7521,7698 );

Example 3: display other employees with the same department and type of work as employees with salaries of more than 2900 yuan

Select *
From EMP
Where (deptno, job) in (select deptno, job
From EMP
Where SAL> 2900)
And Sal <= 2900;

Example 4: show other employees with the same department and the same job type as employees with a salary of more than 2900 yuan (use subquery in the from clause)

Select *
From emp e, (select deptno, job, Sal
From EMP
Where SAL> 2900) S
Where E. deptno = S. deptno
And E. Job = S. Job
And E. Sal! = S. Sal;

2. Related subqueries
1) A correlated subquery is a method that reads each row of a table and compares the values of each row based on the relevant data. The correlated subquery is used for Row-by-row processing. Each row of an external query is executed once.
2) nested subqueries and related subqueries
(1) Execution of nested subqueries
-The internal query is executed first and the value is queried.
-The value of an in-use query for an external query is executed once.
(2) Execution of related subqueries
-Obtain candidate rows from external queries
-Use the value of the candidate row to perform the internal query.
-Use the value from the internal query to confirm or cancel the candidate row.
-Repeat until there are no remaining candidate rows

Select column1, column2 ,...
From Table1 outer
Where column1 operator (select column1, column2
From Table2
Where expr1 = outer. expr2)

Example: Find all employees who earn a higher salary than the average salary of the Department

Select ename, deptno, Sal
From EMP outer
Where SAL> (select AVG (SAL)
From EMP
Where deptno = outer. deptno );
Result:
Ename deptno Sal
Allen 30 1600
Jones 20 2975
Blake 30 2850
Scott 20 3000
King 10 5000
Ford 20 3000
Wang 10 3000

3. exists operation
The exists operation checks the rows that exist in the subquery result set:
1) if a subquery row value is found:
-The search in the internal query does not continue
-The condition is marked as true.
2) If the row value of a subquery is not found
-The condition is marked as false.
-Search continues in the internal Query

For example, find the manager information of at least one employee.

Select *
From EMP outer
Where exists (select 'x'
From EMP
Where Mgr = outer. empno );
Result:
Empno ename job Mgr hiredate Sal comm deptno
7566 Jones manager 7839 2975-81 20
7698 Blake manager 7839 01-5 months-81 2850 30
7782 Clark manager 7839-81 2450 10
7788 Scott analyst 7566-87 3000 20
7839 King President 17-11 month-81 5000 10
7902 Ford analyst 7566 03-12 months-81 3000 20

4. Not exists operation
For example, find the department information where the Department number is not in the employee table.

Select *
From Dept
Where not exists (select *
From EMP
Where EMP. deptno = Dept. deptno );

5. Related update
-Use a subquery to update rows in a table. The rows in the table are based on the rows in another table.

Update Table1 alias1
Set column = (select expression
From Table2 alias2
Where alias1.column = alias2.column );

Example: 1. Add a department name column in the employee table

ALTER TABLE EMP
Add (dname varchar2 (14 ));

2. Use the Department name in the department table to update the Department name in the employee table

Update EMP E
Set dname = (select dname
From dept d
Where E. deptno = D. deptno );

6. Related Delete
-Use a subquery to delete rows in a table. The rows in the table are based on the rows in the other table.

Delete from Table1 alias1
Where column operator (select expression
From Table2 alias2
Where alias1.column = alias2.column );

For example, you can use a related subquery to delete data in the EMP table. The deleted data is data with the same empno column value in the EMP table and job_history table.

Delete from EMP E
Where empno in (select empno
From job_history
Where empno = E. empno );

7. With clause
1) when a query block appears multiple times in a complex query, the with clause can be used in the SELECT statement to use the same query block.
2) The with clause retrieves the query block result and stores it in the user's temporary tablespace.
3) The with clause can improve the performance.

For example, use the with clause to write a query to display the Department name and the total salary of the Department. The total salary of those persons is higher than the average salary of each department.

With
Dept_costs (
Select D. dname, sum (E. Sal) as dept_total
From emp e, DEPT d
Where E. deptno = D. deptno
Group by D. dname ),
Avg_costs (
Select sum (dept_total)/count (*) as dept_avg
From dept_costs)
Select *
From dept_costs
Where dept_total> (select dept_avg
From avg_costs)
Order by dname;
Result:
Dname dept_total
Accounting 11750
Research 14075

Exercise
1. display the details of the employees who have changed at least two jobs (EMP and job_history)

 

2. Write a subquery to show the name, Department number, and salary of any employee whose department number and salary match with the department number and salary of any employee who earned the Commission.

Select ename, deptno, Sal
From EMP
Where (deptno, Sal) in (select deptno, Sal
From EMP
Where comm is not null );

3. display the name, Department name, and salary of any employee whose salary and Commission match that of any employee whose LOC is Chicago

Select ename, dname, Sal
From emp e, DEPT d
Where E. deptno = D. deptno
And (SAL, nvl (Comm, 0) in (select Sal, nvl (Comm, 0)
From emp e, DEPT d
Where E. deptno = D. deptno
And D. Loc = 'Chicago ');

4. Create a query to display the names, dates, and salaries of all employees whose salaries and commissions are the same as Scott (Scott's information is not displayed)

Select ename, hiredate, Sal
From EMP
Where (SAL, nvl (Comm, 0) in (select Sal, nvl (Comm, 0)
From EMP
Where ename = 'Scott ')
And ename! = 'Scott ';

5. Create a query to display the names, jobs, and salaries of employees whose salaries are higher than those of all managers (job = 'manager. Result set sorted by salary from highest to lowest

Select ename, job, Sal
From EMP
Where SAL> All (select Sal
From EMP
Where job = 'manager ')
Order by Sal DESC;

6. display the IDs, names, and department numbers of employees in cities whose names start with N.

Select empno, ename, deptno
From EMP
Where deptno in (select deptno
From Dept
Where loc like 'n' % ');

7. Write a query to find all employees whose salaries are higher than the average salaries of their respective departments, and display the average salaries of their names, Department numbers, and departments, sorted by average salaries.

Select ename, E. deptno, Sal, A. AVG
From emp e, (select deptno, AVG (SAL) AVG
From EMP
Group by deptno)
Where E. deptno = A. deptno
And E. SAL> A. AVG
Order by A. AVG;

8. Write a query to show the names of employees whose salaries are lower than the average salaries of their respective departments

Select ename
From EMP outer
Where Sal <(select AVG (SAL)
From EMP inner
Where deptno = outer. deptno );
Or:
Select ename
From EMP outer
Where outer. Sal <(select AVG (inner. Sal)
From EMP inner
Where inner. deptno = outer. deptno );

9. Find all employees who are not management personnel (employees whose employee numbers are not in the Manager Number Column)

Select *
From EMP
Where empno not in (select Mgr
From EMP
Where MGR is not null );
Or:
Select *
From EMP outer
Where not exists (select 'x'
From EMP
Where Mgr = outer. empno );
Or:
Select *
From EMP
Where empno not in (select nvl (MGR, 0)
From EMP );