Oracle's SQL statement advanced exercises and Answers (ii)

--1, List all departments that have at least one employee

SELECT DISTINCT dname from dept where Deptno in (select DISTINCT deptno from EMP);

　　--2, List all employees with more than "SMITH" salary

Select Ename,sal from emp where sal> (the Select Sal from emp where Ename=upper (' Smith '));

　　--3, lists the names of all employees and their immediate superiors

　　Select E.ename,m.ename from emp e,emp m where e.mgr=m.empno (+);

　　--4, List all employees whose entry date is earlier than their immediate superiors

　　Select ename from emp e where hiredate< (select HireDate from emp where empno=e.mgr);

　　--5, List department names and employees of these departments , and list the departments with no employees

　　Select Dname,ename from Dept D left JOIN EMP E on D.deptno=e.deptno;

　　--6, lists the namesof all "clerk" (clerks) and their department names

　　Select Ename,dname from emp e left joins dept D on E.deptno=d.deptno where Job=upper (' clerk ');

　　--7, listing minimum salaries for various job categories, showing records with minimum salary greater than 1500

　　Select Job,min (SAL) from EMP Group by Job have min (sal) >1500;

　　--8 the name of the employee who is engaged in thesales job, assuming that the department number of the sales department is not known

　　Select ename from emp where deptno = (select Deptno from dept where Dname=uppder (' SALES '))

　　--9, list all employees with salary above the company's average level

　　Select ename from emp where sal> (select AVG (SAL) from EMP);

　　--10, Listall employees who work in the same way as "SCOTT"

　　Select ename from emp where job= (the Select job from emp where Ename=upper (' Scott '));

　　--11, list the names and salaries of certain employees, provided that their salaries are equal to the salary of any employee in department 30

　　Select Ename,sal from emp where Sal in (select Sal from EMP where deptno=30);

　　--12, list the names and salaries of certain employees, provided that their salaries are higher than the salaries of all employees in department 30

　　Select Ename, sal from EMP where sal> (select Max (SAL) from EMP where deptno=30);

　　--13, list information for each department and number of employees in the department

　　Select D.deptno,dname,count (ename) from Dept D left JOIN EMP E on (D.DEPTNO=E.DEPTNO)

　　GROUP BY D.deptno,dname

　　--14, list employee names, department names, and salaries for all employees

　　Select e.ename,d.dname,e.sal from EMP e left joins dept D on (D.DEPTNO=E.DEPTNO)

　　--15, list of different combinations of employees engaged in the same job but belonging to different departments

　　Select Tba.ename,tbb.ename,tba.job,tbb.job,tba.deptno,tba.deptno

　　From EMP tba,emp TBB

　　Where Tba.job=tbb.job and Tba.deptno<>tbb.deptno

　　--16, listing details of all departments assigned the number of employees, even if 0 employees are assigned

　　Select Dept.deptno,dname,loc,count (empno)

　　From Dept,emp

　　Where Dept.deptno=emp.deptno (+)

　　Group by Dept.deptno,dname,loc

　　--17, list minimum wage for various categories of work

　　Select min (sal) from the EMP group by job

　　--18, list the manager 's minimum salary for each department

　　Select Deptno,min (SAL) from EMP where Job=upper ('manager ') GROUP by Deptno

　　--19, list the annual salary of all employees sorted by annual salary

　　Select (SAL+NVL (comm,0)) *12 as AVN from EMP Order by AVN

　　--20, employees who list salary levels in fourth place

　　SELECT * FROM (select Ename,sal, Rank () over (order by Sal Desc) as grade from EMP) where grade=4

Oracle's SQL statement advanced exercises and Answers (ii)