The most complete Oracle exercises in history ____oracle

1. Name of person who obtains the highest salary in each department

First step: Get the highest salary in each department

Select Max (SAL) from the EMP group by DEPTNO;

The second step: according to the results of the first step and the employee table to associate, get the name of the person

Select A.empno,a.ename,a.sal,a.deptno from emp a join (select Deptno, max (SAL) max_sal from EMP Group by Deptno) b on A.D eptno= B.deptno and a.sal = b.max_sal;

2. Which person's salary is above the department's average salary

First step: Get the average salary per department

Select deptno, avg (SAL) avgsal from EMP Group Bydeptno

Step two: Correlate the results of the first step with the employee table. Get person Name

3. Obtain the average salary level of the department (Everyone)

The first step: Get everyone's pay grade

Select E.empno,e.ename,e.deptno,g.grade from emp e join Salgrade G on e.sal between G.losal and G.hisal

Step two: The results of the first step are grouped by department. Then get the average of the level

Select S.deptno, AVG (grade) from (select E.empno,e.ename,e.deptno,g.grade to emp e join Salgrade G on e.sal between G.L Osal and G.hisal) s GROUP by S.deptno

4. Do not use Group function (MAX), get the highest salary

The first method:

Step one: Move employee pay in descending order

Select Empno,ename,sal from emp order BY sal Desc

Step two: Get the first piece of data

Select A.empno,a.ename,a.sal from (select Empno,ename,sal from emp order BY Sal Desc) A WHERE rownum < = 1;

The second method:

The first step is to treat the EMP table as 2 sheets. The method of using the Cartesian product is compared. Gets a value other than the maximum value.
SELECT DISTINCT a.sal from emp a join EMP B on a.sal < b.sal
Step two: Get the maximum value
Select A.sal,a.empno,a.ename from emp A where Sal isn't in (select distinct a.sal from emp a join EMP B on a.sal < b.sal );
The third method:
The first step is to treat the EMP table as 2 sheets. The method of using the Cartesian product is compared. Sort from big to small
SELECT DISTINCT a.sal from emp a join EMP B on A.sal > B.sal
Step two: Get the first piece of data
Select Sal from (SELECT distinct a.sal to emp a join EMP B on a.sal > B.sal) where rownum <= 1

5, get the department number of the department with the highest average salary.
The first method:
First step: Get the average salary of each department. Sort by Desc. Get maximum Value
Select E.deptno, Avg (e.sal) avg_sal from EMP e GROUP by E.deptno ORDER by avg_sal Desc;
Step two: Get the first piece of data
Select A.deptno, A.avg_sal from (select E.deptno, avg (e.sal) avg_sal from EMP e GROUP by E.deptno ORDER BY avg_sal Desc) A WHERE rownum < = 1;
The second method:
Step one: Get the average salary of each department
Select AVG (SAL) Avg_sal,e.deptno from emp e Group by DEPTNO;
Step Two: Use aggregate function to get maximum average salary
Select Max (avg_sal) from (select AVG (SAL) Avg_sal,e.deptno from emp e GROUP by Deptno)
Step three: Correlate the results obtained in the first and second steps
Select M.deptno from
(select AVG (SAL) Avg_sal,e.deptno from emp e Group by Deptno) m
Right Join
(select Max (avg_sal) max_sal from (select AVG (SAL) avg_sal,e.deptno to EMP e Group by deptno)) n
On m.avg_sal = N.max_sal
The third method:
Step one: Get the maximum value of each department
Select Max ((SAL) from EMP M Group by Deptno;
Step two: Use having filtration. Get maximum Value
Select A.deptno,avg (SAL) avg_sal from emp A GROUP by DEPTNO has avg (sal) = (select Max (SAL) from EMP M GROUP BY DEPTNO);
6. Department name of the department with the highest average salary

The first method:

Step one: Get the average salary of each department

Select E.deptno, Avg (SAL) avg_sal from the EMP e GROUP by the Deptno order by avg_sal Desc;

Step two: Get the maximum average salary of each department

Select M.deptno, Avg_sal from (select E.deptno, avg (SAL) avg_sal from emp e GROUP by Deptno ORDER by avg_sal Desc) m where RowNum <=1;

Step three: Associate with the departmental table. Get Department Name

Select T.deptno,t.dname from Dept t

Right Join
(select M.deptno, avg_sal from (select E.deptno, avg (SAL) avg_sal from emp e GROUP by Deptno ORDER by avg_sal Desc) m wher e rownum <=1) n
on t.deptno = N.deptno;

The second method:

First step: Get the highest department number. and mean maximum value

Select Max ((SAL)) from EMP e Group by DEPTNO;

Step two: Related Department table

Select T.dname,a.sal from dept t right join EMP A on t.deptno = A.deptno where a.sal = (select Max (SAL) Max_sal from EMP e GROUP by DEPTNO);

The third method:

First step: Get the department number with the highest average salary

Select E.deptno from EMP e GROUP BY DEPTNO has avg (sal) = (select Max (SAL) from EMP Group by DEPTNO);

Step Two: Associate with the departmental table

Select T.dname,t.deptno from dept t where T.deptno = (select E.deptno from emp e GROUP BY DEPTNO have avg (sal) = (select Max (SAL) from the EMP Group by Deptno));

7. department name of the lowest rank of average salary.

The first method:

Step one: Get the lowest average salary department number in ascending order

Select M.deptno from (select E.deptno, avg (SAL) avg_sal from emp e GROUP by Deptno ORDER by avg_sal ASC) m where rownum <= 1;

Step two: Get the department number with the lowest average salary level and the average salary and grade

Select S.grade,m.deptno,avg_sal from Salgrade s

Right Join

(select E.deptno, avg (SAL) avg_sal from emp e GROUP by DEPTNO and avg_sal ASC) m on Avg_sal

Between S.losal and S.hisal

where RowNum < = 1

Step three: Associate with the departmental table. Get Department Name

Select T.dname, T.deptno, avg_sal from dept t
Right Join
(select S.grade,m.deptno,avg_sal from Salgrade s right join (select E.deptno, avg (SAL) avg_sal from emp e GROUP by DEPTN o ORDER by avg_sal ASC) m on Avg_sal

Between S.losal and s.hisal where rownum < = 1) n on t.deptno = N.deptno;

8. Get the name of the manager who has the highest salary than the average employee (employee code does not appear on the Mgr field)

First step: Get the manager number

SELECT distinct MGR from EMP where MGR isn't null;

Step two: The average employee salary is not in the Mgr field.

Select Max (SAL) max_sal from emp e where e.empno isn't in (select distinct MGR from EMP where MGR is not NULL);

Step three: Get a higher salary manager's name

Select Sal, ename from emp s where s.sal > (select max (SAL) max_sal the from EMP e where e.empno not in (select distinct MG R from EMP where Mgr are NOT NULL)

9. Get the top five employees with the highest salary

First method: Use RowNum to sort

SELECT * FROM (

Select RowNum R, t.*

From

(

Select Ename, Sal from emp order BY sal Desc

) T

where rownum <=5) wherer> 0

The second method:

The first step: get the highest salary of employees

Select Sal from emp order by Sal Desc;

Step two: Get the top 5 salaries

Select Sal from (select Sal from emp desc) where rownum <= 5;

Step three: Get name from association with employee table

Select E.ename,e.sal from emp e where e.sal into (select Sal from (select Sal from emp desc) where RowNum < = 5 ORDER BY sal Desc;

10. Get the highest paid sixth to tenth employees

Step one: Get the full salary

Select Sal,ename from emp order BY sal Desc

Step two: Get paid 1th to 10th employee salary and name

Select RowNum R, ename, Sal from
(select Sal,ename from emp order BY sal Desc)
where RowNum <= 10

Step three: Excluding the top 5

Select t.* from
(
Select RowNum R, ename, Sal from
(select Sal,ename from emp order BY sal Desc)
where RowNum <= 10
) T where r>5

11.5 employees who have been admitted to the final position

The first step: descending sort last entry

Select ename,hiredate from EMP hiredate desc;

Step two: Get the last 5 employees

Select Ename,hiredate from (select ename,hiredate from EMP hiredate desc) where rownum <= 5;

12. How many employees do you get per salary level?

Step one: Get the salary level of each employee

Select S.grade,t.ename,t.sal from Salgrade s join (select Ename,sal to EMP) T on t.sal between S.losal and S.hisal

Step two: Get the number of employees per salary level

Select COUNT (*), M.grade from (select S.grade,t.ename,t.sal to Salgrade s join (select Ename,sal from emp) T on T.sal Bet Ween S.losal and S.hisal
) m GROUP by M.grade

13. List the names of all employees and direct superiors
Select E.ename, NVL (M.ename, ' no superiors ') Mname from EMP e-left join emp m on e.mgr = M.empno


14. List the number, name and department name of all employees whose employment date is earlier than their immediate superiors
Select E.empno,e.ename,t.dname from emp e join EMP A in e.hiredate < a.hiredate and e.mgr = a.empno Join dept T on E.D Eptno = T.deptno;


15. List the department name and the employee information of these departments, and list the departments that do not have employees.
Select A.*,t.dname from EMP a right join dept t on t.deptno = A.deptno;


16. List all departments that have at least one employee
Select dname, COUNT (*) from EMP e join Dept D to E.deptno = D.deptno GROUP BY dname


17. List all employee information with a salary ratio of "SMITH".
SELECT * from emp where sal > (select sal from emp where ename = ' SMITH ');


18. List the names of all "clerk" (clerks) and their departmental names, and the number of departments.
1. First obtain the name and department number of the "clerk" (clerk)
Select E.deptno,e.ename,e.job from emp e where e.job = ' clerk ';
2. Second, to obtain the corresponding department name
Select T.deptno,t.dname,n.job,n.ename from dept t right join (select E.deptno,e.ename,e.job from emp e where e.job = ' Cler K ') n on n.deptno = T.deptno
3. Secondly, the number of departments
Select COUNT (*) TT, j.dname from EMP v join Dept J in V.deptno = J.deptno GROUP BY j.dname
4. The final query results
Select T1.ename,t1.deptno,t1.dname, t2.tt from
(select T.deptno,t.dname,n.job,n.ename from dept t join (select E.deptno,e.ename,e.job from emp e where e.job = ' clerk ') n On n.deptno = t.deptno) t1
Join
(SELECT COUNT (*) TT, j.dname from EMP v join Dept J on v.deptno = J.deptno GROUP by j.dname) T2
On
T1.dname = T2.dname;


19. List of jobs with a minimum salary greater than 1500 and the total number of employees engaged in this work.
1. Get the job name with a salary greater than 1500
Select E.job from EMP e GROUP by E.job has min (sal) > 1500
2. Access to the number of employees per employee
Select COUNT (*) cc from EMP GROUP by job
3. Access to Results
Select t1.job,t2.cc from (select E.job from emp e GROUP by E.job have min (sal) > 1500) T1 join (SELECT COUNT (*) CC, J OB from EMP GROUP by job
) t2 on t1.job = T2.job


20. List the names of employees in the department "sales" < sales > work, assuming you do not know the department number of the sales department.
1. First get the department number of the sales department
Select Deptno from dept where dname = ' SALES '
2. Root Employee Table Association. Get the employee's name
SELECT * from emp where deptno = (select Deptno from dept where dname = ' SALES ')


21. List the names of all employees and departments that are engaged in the same work as "SCOTT".
The first method: Stepwise analysis
1. List all "SCOTT" department numbers and work performed
Select Job from emp where ename = ' SCOTT '
2. List of employees engaged in ' SCOTT ' work
SELECT * from emp where job = (select Job from emp where ename = ' Scott ') and ename!= ' Scott '
3. Related Department table. Get department name. and exclude Scott.
Select dname,t1.* from dept t right join (SELECT * from emp where job = (select Job from emp where ename = ' SCOTT ') and ename!= ' SCOTT ') t1 on t.deptno = T1.deptno


The second method: relatively concise (recommended)
Select E.ename, d.dname from EMP E, dept d where E.deptno = D.deptno and E.job = (select Job from emp where ename = ' SC