Oracle Common Query statements

1: List all employees ' names, department names, and wages

Select A1.ename,a1.sal,a2.dname from emp a1,dept A2 where A1.deptno = A2.deptno;

2: List details of all departments and number of departments

Select A2.deptno,a2.dname,a2.loc,count (a1.empno) from the EMP a1,dept A2 where A1.deptno (+) = A2.deptno GROUP by A2.DEPTNO,A2. Dname,a2.loc;

3: List The annual salary of all employees, the department name, according to the annual salary in ascending order

Select A1.sal*12, A2.dname from EMP a1,dept A2 where A1.deptno = A2.deptno order by a1.sal*12;

4: Identify the supervisor of each employee and the name of the department, and ask those supervisors to pay more than 3000

Select Employee.ename,boss.ename, a1.dname from emp employee,emp boss,dept A1 where employee.mgr = Boss.empno and BOSS.DEP TNO = A1.deptno and Boss.sal >3000;

5: Find out the department's name with the ' S ' character of the Department employees Total payroll, Department number

SELECT D.deptno,nvl (SUM (SAL), 0), COUNT (empno) from EMP e,dept D WHERE e.deptno (+) =d.deptno and d.dname like '%s% ' GROUP by D . Deptno;

6: List the department name and employee information (quantity, average salary) of these departments, and list the departments with no employees

Select D.dname,avg (e.sal), COUNT (E.empno) from the EMP e,dept D where E.deptno (+) =d.deptno GROUP by D.dname;

7: List the employee name, base salary, hire date, department name in the department "sales", assuming that the department number of the sales department is not known

Select A1.ename,a1.sal,a1.hiredate,a2.dname from emp a1,dept A2 where A1.deptno = A2.deptno and a2.dname = ' SALES ';

8: List the number of employees at each wage level in the company, average wage

Select Grade,count (*), AVG (SAL) from EMP, Salgrade where Sal between Losal and Hisal group by grade;

9: List employee name and salary, department name and salary above salary of all employees in department 30 work

Select A1.ename,a1.sal,a2.dname from EMP A1, dept a2 where A1.deptno = A2.deptno and sal > All (select Sal from emp wher E deptno = 30);

10: Lists the number, name, department name, department position, department number of all employees who have been employed earlier than the direct superior.

SELECT E.empno,e.ename,d.dname,d.loc,temp.count

From EMP e,emp m,dept D, (

SELECT Deptno DNO, Count (empno) Count

From EMP

GROUP by Deptno) Temp

WHERE e.mgr = m.empno (+) and E.hiredate < m.hiredate

and E.deptno = D.deptno

and E.deptno = Temp.dno;

11: List All "clerk" names and their department names, number of departments, salary level

SELECT E.ename, D.dname, Temp.count,s.grade

From EMP E, Dept D, (

SELECT deptno dno,count (empno) COUNT

From EMP

GROUP by Deptno) Temp,salgrade s

WHERE job= ' clerk '

and E.deptno = D.deptno

and D.deptno = Temp.dno

and e.sal between S.losal and S.hisal;

12: List of jobs with minimum salary greater than 1500 and total number of employees and department name, location, average salary

SELECT T.job,t.count,d.dname,e.ename,reg.avg

From Dept D, (

SELECT E.job,count (e.empno) COUNT

From EMP E

GROUP by E.job

Having MIN (e.sal) >1500

) T,emp E, (

SELECT deptno dno,avg (SAL) AVG

From EMP

GROUP by Deptno

) reg

WHERE E.deptno = d.deptno and e.job = T.job

and E.deptno = Reg.dno;

13: List all employees who pay more than the average salary of the company, the department, the superior, the company's salary level

Select A1.ename,a2.dname,a1.mgr,a3.grade from emp a1,dept a2,salgrade A3, (select AVG (SAL) mysal from emp) A4 where a1.dept No = A2.deptno and Sal between A3.losal and A3.hisal and A1.sal > a4.mysal;

14: List all employees and department names in the same job as Scott, number of departments

Select E.empno,e.ename,e.job,d.dname,temp.count from emp e,dept D, (select Deptno dno,count (empno) count from emp GROUP by DEPTNO) Temp

where e.job= (SELECT job from emp WHERE ename= ' Scott ') and E.ename<> ' Scott ' and E.deptno=d.deptno and temp.dno=e.dept No

15: List the number of employees working in each department, average salary, and mean length of service

SELECT D.dname,count (e.empno), avg (e.sal), round (avg (sysdate-e.hiredate)/365) from EMP e,dept D where e.deptno (+) = D.deptno GROUP by D.dname;

16: List the minimum wage for each job and the employee's name

Select A1.ename,a1.job,a1.sal from EMP A1, (Select Job,min (SAL) min_sal from EMP Group by job) A2 where A1.job=a2.job and a 1.sal=a2.min_sal;

17: List the manager's minimum salary, name, department name, number of departments in each department

Select E.sal,e.ename,d.dname, Count from (select Job,min (SAL) Sal,ename,deptno from emp where job= ' MANAGER ' GROUP by Jo B,ename,deptno) E,

(Select D.deptno,d.dname,count (e.empno) Count fromemp e,dept D wheree.deptno=d.deptno GROUP by D.deptno,d.dname) d

where E.deptno=d.deptno;

Oracle pagination (based on ROWNUM pagination)

SELECT * FROM (select A1.*,rownum rn from (select Ename,sal to emp Order by Sal) A1 where rownum<=10) where RN >=6 ;

Oracle Common Query statements