Oracle Database Exercises

The following exercises have the default table in the Oracle database as the Principal

1. List all departments that have at least one employee.

SELECT DISTINCT d.dname from EMP e,dept D WHERE e.deptno=d.deptno;

SELECT * from DEPT WHERE DEPTNO in (SELECT DISTINCT DEPTNO from EMP);

SELECT DISTINCT d.dname from EMP E JOIN DEPT D USING (DEPTNO);

2. List all employees who pay more than "SMITH". (Greater than the maximum salary of Smith staff)

Select ename from emp where sal> (SELECT SAL from emp where ename = ' SMITH ');

3. List the names of all employees and their immediate superiors.
SELECT e.ename,m.ename from EMP e,emp M WHERE e.mgr=m.empno;

SELECT E.ename,m.ename from the EMP E JOIN emp M on (e.mgr=m.empno);

4. List all employees whose employment date is earlier than their immediate superiors.

SELECT e.ename from EMP e,emp M WHERE e.mgr=m.empno and e.hiredate<m.hiredate;
SELECT E.ename from the EMP E JOIN emp M on (E.mgr=m.empno and E.hiredate<m.hiredate);

5. List department names and employee information for these departments, including those with no employees.

SELECT D.dname,e.ename from EMP E full OUTER JOIN DEPT D USING (DEPTNO);

6. List the names of all job "clerk" (clerks) and their department names.

SELECT E.ename,d.dname from EMP E full OUTER JOIN DEPT D USING (DEPTNO) WHERE job= ' clerk ';

SELECT e.ename,d.dname from EMP e,dept D WHERE e.deptno=d.deptno and JOB = ' clerk ';

7. List all kinds of jobs with minimum salary of more than 1500.

SELECT Job,min (sal) from EMP GROUP by JOB have MIN (SAL) >1500;

8. List the names of employees who work in the department "sales", assuming they do not know the department number of the sales department.

SELECT * from EMP E JOIN DEPT D USING (DEPTNO) WHERE d.dname= ' SALES ';

9. List all employees who pay more than the company's average salary.
Select Ename,sal from emp WHERE sal> (select AVG (SAL) from EMP);

10. List all employees who work in the same way as "SCOTT".

SELECT JOB from EMP WHERE ename= ' SOCTT ';

Select ename from emp where ename<> ' Scott ' and job= (select JOB from emp where ename= ' Scott ');

11. Name and salary of all employees whose salaries are equal to the salaries of the employees in the Department 30.

Select Ename,sal from emp where SAL in (SELECT SAL from EMP where deptno=30) and deptno<>30;

12. The names and salaries of employees who pay more than the salaries of all employees working in department 30.

Select Ename,sal from emp where Sal>all (select SAL from emp where deptno=30);

13. List the number of employees working in each department, the average salary and service duration.
SELECT D.dname,count (E.ename), avg (SAL), Avg (Months_between (sysdate,hiredate)/12) from EMP e,dept D WHERE e.deptno=d. DEPTNO GROUP by D.dname;

14. List all employees ' names, department names and wages.

SELECT e.ename,d.dname,sal from EMP e,dept D WHERE e.deptno=d.deptno;

15. List A combination of employees who work in the same job but belong to different departments.

SELECT DISTINCT m.ename from emp e,emp M where E.job=m.job and e.deptno<>m.deptno;

16. List details of all departments and number of departments.

SELECT D.dname,count (e.ename) from EMP E, DEPT D WHERE e.deptno=d.deptno GROUP by D.dname;

17. List the minimum wage for each job.
SELECT job,min (SAL) from EMP e,dept D WHERE e.deptno=d.deptno GROUP by E.job;

SELECT job,min (SAL) from EMP E joins DEPT D on (e.deptno=d.deptno) GROUP by E.job;

18, list the manager (manager) minimum salary for each department (job is manager).

SELECT deptno,min (SAL) from the EMP WHERE job= ' MANAGER ' GROUP by DEPTNO;

19, List all employees annual salary, according to the annual salary from low to high ranking.

SELECT ename, (SAL*12+NVL (comm,0) *12) annual salary from the EMP ORDER by annual salary;

SELECT ename, (SAL*12+NVL (comm,0) *12) annual salary from EMP ORDER by 1;

Oracle Database Exercises