Oracle Query Exercises

EMP Employee table (EMPNO employee number/ename Employee Name/job work/mgr Superior number/hiredate Employment Date/sal Salary/comm Commission/deptno department number)

Dept Department Table (DEPTNO department number/dname department name/loc location)

Salary = salary + commission

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

2. List all employees who pay more than "SMITH".

3. Lists the names of all employees and their immediate superiors.

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

5. Lists the department names and employee information for those departments, and lists the departments with no employees

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

7. Lists various jobs with a minimum salary of more than 1500.

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

9. List all employees who pay more than the company's average salary.

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

11. Lists the names and salaries of all employees whose salaries are equal to the salaries of employees in department 30.

12. Lists the employee's name and salary for salaries higher than the salary of all employees who work in department 30.

13. Lists the number of employees, average wages, and average service duration for each department.

14. Lists the names, department names, and wages of all employees.

15. Lists detailed information and number of departments for all departments.

16. List the minimum wage for each job.

17. Lists the manager (manager) minimum salary for each department.

18. Lists the annual salary of all employees, sorted from lowest to highest annual salary.

--------1----------

Select Dname from Dept

where Deptno in (select Deptno from EMP);

-------2----------

select* from EMP

where sal> (select Sal from emp where ename= ' SMITH ');

--------3----------

Select A.ename, (select ename from emp b where b.empno=a.mgr) as bossname from EMP A;

--------4----------
Selecta.ename from EMP A where a.hiredate< (select HireDate from emp b where b.empno=a.mgr);

--------5----------
Selecta.dname,b.empno,b.ename,b.job,b.mgr,b.hiredate,b.sal,b.comm,b.deptno from Dept a LEFT join EMP B on a.deptno= B.deptno;

--------6----------
Select A.ename,b.dname from emp a joins Dept B on A.deptno=b.deptno and a.job= ' clerk ';

--------7----------
SELECT distinct job as highsaljob from EMP Group by Job have min (sal) >1500;
--------8----------
Select E.name from emp where deptno= (select Deptno from dept where Dname= ' SALES ');

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

-----------------
Select ename from emp where job= (select Job from emp where ename= ' SCOTT ');

-----------------
Select A.ename,a.sal from emp a where a.sal in (selectb.sal from EMP b where b.deptno=30) and a.deptno<>30;

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

-----------------
Select
(Selectb.dname from dept b where A.deptno=b.deptno) as Deptname,
Count (Deptno) as Deptcount,
AVG (SAL) as Deptavgsal
From EMP a group by DEPTNO;

----------------
Select
A.ename,
(select B.dname from dept b where B.deptno=a.deptno) as Deptname
, Sal

from EMP A;

-----------------
Select
A.deptno,
A.dname,
A.loc,
(select COUNT (deptno) from EMP b where B.deptno=a.deptno GROUP by B.deptno) as Deptcount
From dept A;

-----------------
Select Job,avg (SAL) from the EMP group by job;
-----------------
Select Deptno,min (SAL) from the EMP where job= ' MANAGER ' GROUP by DEPTNO;

-----------------
Select Ename, (SAL+NVL (comm,0)) *12 as salpersal from emp order by Salpersal;

Oracle Query Exercises