Personnel System - in Oracle Query Assignment with Answer

/***************************************************************************/
Personnel System
/***************************************************************************/

(A) Create the above mentioned table with constraints

1) Emp_ Master
Ans: create table Emp_Master
(
empno number(4) primary key,
ename varchar2(35) not null,
hiredate date,
mgrno number(4) references Emp_master(empno),
salary number(5) check(salary>0),
comm number(3),
deptno number(2) references dept_master(deptno),
job varchar2(15)
);

2) Dept_master
Ans: create table dept_master
(
deptno number(2) primary key,
deptname varchar2(35) not null,
location varchar2(15)
);

(B) Insert records into respected tables.

1) Emp_master
Ans: insert into emp_master
values(&empno,'&ename','&hiredate',&mgrno,&salary,&comm,
&deptno,'&job');

EMPNO ENAME HIREDATE MGRNO SALARY COMM DEPTNO JOB
----- ------- --------- ----- ------ ----- ------ ----------
1001 Jayesh 01-APR-00 1001 20000 200 20 Executive
1002 Goldie 01-MAY-00 1002 15000 150 30 Clerk
1003 Sandy 01-JUN-00 1002 14000 140 30 Executive
1004 Tanu 01-JUN-00 1003 12000 120 40 Worker
1005 Palak 01-JUN-00 1004 11000 110 10 Clerk
1006 Falu 01-JUL-00 1002 10500 105 40 Worker
1007 Jatin 01-JUN-00 1006 11500 115 50 Accountant
1008 Ankee 01-MAY-00 1005 15000 150 30 Executive
1009 Deepak 01-JAN-00 1009 50000 500 20 Executive

2) Dept_master
Ans: insert into dept_master
values(&deptno,'&deptname','&location');

DEPTNO DEPTNAME LOCATION
---------- --------------------- -----------
10 Finance Shahibaug
20 Marketing Ahmedabad
30 Sales Gandhinagar
40 Purchase Mehasana
50 Accounts Narol

(B) Perform the given queries. Write each query using Joins, Sub-query
and co-related Sub-Query, wherever possible, with all possible
combinations.

1. To display details of employees drawing salary greater than all
employees of deptno=30.
Ans: select e2.empno,e2.ename from emp_master e2
where e2.salary >
(select max(e1.salary) from emp_master e1
where deptno=30);

EMPNO ENAME
------ -------
1001 Jayesh
1009 Deepak

2. To display details of employees of deptno=10
and having same job as that of employee of deptno=30.
Ans: select distinct e1.empno,e1.ename from emp_master e1 ,emp_master e2
where e1.deptno=10 and e1.empno <> e2.empno and
e1.job in
(select job from emp_master where deptno=30);

select distinct e1.empno,e1.ename from emp_master e1 ,emp_master e2
where e1.deptno=10 and e1.job=e2.job and e2.deptno=30;

select e1.empno,e1.ename from emp_master e1
where e1.job IN
(select job from emp_master
where e1.empno<>empno and deptno=30)
and deptno=10;

EMPNO ENAME
----- ------
1005 Palak

3. Employees having same job and salary as that of 'Palak'.
Ans: select e.empno,e.ename from emp_master e
where e.job IN
(select distinct e1.job from emp_master e1
where e1.ename like 'Palak' and e1.salary = 11000)
and e.salary = (select e1.salary from emp_master e1
where e1.ename like 'Palak' and e1.salary = 11000)
and e.empno <> (select e1.empno from emp_master e1
where e1.ename like 'Palak' and e1.salary = 11000);

select e1.empno,e1.ename from emp_master e1,emp_master e2
where e1.job=e2.job and e2.ename='Palak' and e1.salary=e2.salary
and e1.empno<>e2.empno;

select e1.empno,e1.ename from emp_master e1
where e1.job IN
(select job from emp_master
where ename='Palak' and e1.salary=salary and e1.empno<>empno);

no rows selected

4. Employees of deptno=10 and job same as that of employees of
'Sales' dept.
Ans: select distinct e1.empno,e1.ename from emp_master e1
where e1.deptno=10 and e1.job IN
(select e2.job from emp_master e2,dept_master d1
where e2.deptno=d1.deptno and d1.deptname like 'Sales');

select distinct e1.empno,e1.ename from emp_master e1,dept_master d1
where e1.deptno=10 and e1.job IN
(select e2.job from emp_master e2
where e2.deptno=d1.deptno and d1.deptname like 'Sales');

select e1.empno,e1.ename from emp_master e1,emp_master e2,dept_master d1
where e1.deptno=10 and e1.job=e2.job and e2.deptno=d1.deptno and
d1.deptname like 'Sales';

EMPNO ENAME
----- ------
1005 Palak

5. Employees having salary greater than average salary of their
respective depts.
Ans: select * from emp_master e1
where salary >
(select avg(e2.salary) from emp_master e2
where e1.deptno=e2.deptno
group by e2.deptno);

select e1.empno,e1.ename from emp_master e1,emp_master e2
where e1.deptno=e2.deptno
group by e1.empno,e1.ename
having e1.salary > avg(e2.salary);

EMPNO ENAME HIREDATE MGRNO SALARY COMM DEPTNO JOB
----- -------- --------- ----- ------ ---- ------ ---------
1002 Goldie 01-MAY-00 1002 15000 150 30 Clerk
1004 Tanu 01-JUN-00 1003 12000 120 40 Worker
1008 Ankee 01-MAY-00 1005 15000 150 30 Executive
1009 Deepak 01-JAN-00 1009 50000 500 20 Executive

6. Employees having salary greater than their manager's salary.
Ans: select * from emp_master e1
where e1.salary > (select e.salary from emp_master e
where e.empno=e1.mgrno);

select e.empno,e.ename,e.hiredate,e.mgrno,e.salary,e.comm,
e.deptno,e.job
from emp_master e,emp_master e1
where e.mgrno=e1.empno and e.salary>e1.salary

select * from emp_master
where empno IN
(select e.empno from emp_master e,emp_master e1
where e.mgrno=e1.empno and e.salary>e1.salary);

EMPNO ENAME HIREDATE MGRNO SALARY COMM DEPTNO JOB
----- -------- --------- ----- ------ ---- ------ ---
1007 Jatin 01-JUN-00 1006 11500 115 50 Accountant
1008 Ankee 01-MAY-00 1005 15000 150 30 Executive

7. To display details of employees drawing salary greater than any employee
of deptno=30.
Ans: select * from emp_master e1
where e1.salary > any (select e2.salary from emp_master e2
where e2.deptno=30 and e1.empno <> e2.empno);

select * from emp_master
where empno IN
(select distinct e1.empno from emp_master e1,emp_master e2
where e1.salary>e2.salary and e2.deptno=30);

select * from emp_master
where empno IN
(select distinct e1.empno from emp_master e1,emp_master e2
where e1.salary>ANY
(select salary from emp_master
where deptno=30));

EMPNO ENAME HIREDATE MGRNO SALARY COMM DEPTNO JOB
----- ------- --------- ----- ------ ----- ------ ----------
1001 Jayesh 01-APR-00 1001 20000 200 20 Executive
1002 Goldie 01-MAY-00 1002 15000 150 30 Clerk
1008 Ankee 01-MAY-00 1005 15000 150 30 Executive
1009 Deepak 01-JAN-00 1009 50000 500 20 Executive

8. Employee who is a manager of more than two employees.
Ans: select * from emp_master
where empno IN
(select e1.mgrno from emp_master e1
where e1.mgrno <> e1.empno
group by e1.mgrno
having count(e1.mgrno)>=2);

select e1.mgrno from emp_master e1
where e1.mgrno <> e1.empno
group by e1.mgrno
having count(e1.mgrno)>=2;

select * from emp_master e
where e.empno IN
(select e1.mgrno from emp_master e1
where e.mgrno=e1.mgrno
group by e1.mgrno
having count(e1.mgrno)>2);

EMPNO
-----
1002

9. Employee drawing the highest salary.
Ans: select * from emp_master
where salary =
(select max(salary) from emp_master);

EMPNO ENAME HIREDATE MGRNO SALARY COMM DEPTNO JOB
----- ------- --------- ----- ------ ----- ------ ----------
1009 Deepak 01-JAN-00 1009 50000 500 20 Executive

10. Employees drawing highest salary in their respective depts.
Ans: select empno,deptno,salary from emp_master
where salary IN
(select max(salary) from emp_master
group by deptno);

select e.empno,e.deptno,e.salary from emp_master e
group by e.empno,e.deptno,e.salary
having e.salary IN
(select max(e1.salary) from emp_master e1
where e1.deptno=e.deptno
group by e1.deptno);

EMPNO DEPTNO SALARY
------ --------- ---------
1005 10 11000
1007 50 11500
1004 40 12000
1002 30 15000
1008 30 15000
1009 20 50000

11. Department details of department having highest total salary.
Ans: select d1.deptno,d1.deptname,sum(salary) from emp_master e1,
dept_master d1
where d1.deptno=e1.deptno
group by d1.deptno,d1.deptname
having sum(salary) in
(select max(sum(salary)) from emp_master
group by deptno);

DEPTNO DEPTNAME SUM(SALARY)
------ ----------------------------------- -----------
20 Marketing 70000

12. Department details of department having highest no of employees.
Ans: select d1.deptno,d1.deptname,count(e1.empno) from emp_master e1,
dept_master d1
where d1.deptno=e1.deptno
group by d1.deptno,d1.deptname
having count(empno) in
(select max(count(empno)) from emp_master
group by deptno);

DEPTNO DEPTNAME COUNT(E1.EMPNO)
------ ----------------------------------- ---------------
30 Sales 3

13. To show records having duplicate names in a file.
Ans: select * from emp_master e1
where e1.ename IN (select e2.ename from emp_master e2
where e1.empno <> e2.empno);

select * from emp_master e1,emp_master e2
where e1.ename =e2.ename and e1.empno<>e2.empno;

no rows selected.