Several Oracle Exercises

--1. Query which department's average salary is the highest, listing departmental codes and average wages.
Select Deptno,avg (SAL) from the EMP GROUP by DEPTNO have avg (sal) = (select Max (SAL) from the EMP Group by DEPTNO);
--2. Lists information about the highest-paid employees in each department: name, department number, salary.
Select Emp.ename,emp.deptno,emp.sal
From EMP, (select Deptno T_dept,max (SAL) t_maxsal from EMP Group by DEPTNO) TAB1
where Emp.deptno=tab1.t_dept and emp.sal=tab1.t_maxsal;
--3. Query the superior is the employee name (ename) and salary (SAL) of "KING".
Select Ename,sal from emp where mgr= (select empno from emp where ename= ' KING ');
--4. The name (ename), department name (Dname), and job name (job) of the employee of the department whose location (Loc) is "NEW YORK".
Select Ename,deptno,job from emp where deptno= (select Deptno from dept where loc= ' NEW YORK ');
--5. Check the employee number (empno), name (ename), and salary (SAL) for all employees with a higher salary than the company's average wage.
Select Empno,ename,sal from emp where sal> (select AVG (SAL) from EMP);
--6. Query the employee number (EMPNO) and name (ename) of the employee in the same department whose name contains the letter "U".
Select Empno,ename from emp where deptno= (select Deptno from emp where ename like '%u% ');
--7. Query which employees are paid less than the average salary of the department.
Select emp.*
From EMP, (select Deptno,avg (SAL) avg_sal from EMP Group by deptno) tab
where Emp.deptno=tab.deptno and emp.sal<tab.avg_sal;
--8. Querying the first 5 records of the EMP table
SELECT * FROM (select Emp.*,rownum rn from EMP) TAB1 where tab1.rn<=5;
--9. Querying 第3-5条 Records, no sorting required
SELECT * FROM (select Emp.*,rownum rn from EMP) TAB1 where tab1.rn between 3 and 5;
--10. Inquire about the three highest-paid people in the company
SELECT * FROM (
Select Tab1.*,rownum rn from (select emp.* from emp order BY sal Desc) tab1) tab2 where tab2.rn<=3;
--11. Check the company's lowest paid five people
SELECT * FROM (
Select Tab1.*,rownum rn from (select emp.* from emp ORDER by sal ASC) TAB1) TAB2 where tab2.rn<=5;

Several Oracle Exercises