Oracle Complex Query

1: List the names, department names, and salaries of all employees.

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

2: List detailed information about all departments and number of departments

Select a2.deptno, a2.dname, a2.loc, count (a1.empno) from 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 names of the departments are listed in ascending order of annual salary.

Select a1.sal * 12, a2.dname from emp a1, dept a2 where a1.deptno = a2.deptno order by a1.sal * 12;

4: Find the Superior Supervisor and department name of each employee, and ask the supervisor to have a salary of over 3000

Select employee. ename, boss. ename, a1.dname from emp employee, emp boss, dept a1 where employee. mgr = boss. empno and boss. deptno = a1.deptno and boss. sal> 3000;

5: Find the total salary of the Department employees whose names contain the characters 'S '.

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 the employee information (quantity, average salary) of these departments, and list the departments without employees

Select d. dname, avg (e. sal), count (e. empno) from emp e, dept d where e. deptno (+) = d. deptno group by d. dname;

7: list the names, basic salary, employment date, and department names of employees working in the Department "SALES". If you do not know the Department ID of the SALES department

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 and average salary of employees of each wage level of the company.

Select grade, count (*), avg (sal) from emp, salgrade where sal between losal and hisal group by grade;

9: Name and salary of all employees whose salaries are higher than 30 in the Department, Department name

Select a1.ename, a1.sal, a2.dname from emp a1, dept a2 where a1.deptno = a2.deptno and sal> all (select sal from emp where deptno = 30 );

10: list the number, name, Department name, Department location, and number of employees whose employment date is 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 the names of all "clerk" and their department names, number of departments, and salary levels.

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 = 'cler'

AND e. deptno = d. deptno

AND d. deptno = temp. dno

AND e. sal BETWEEN s. losal AND s. hisal;

1 2 Next Page