Employee Database Queries 51-100
23 Jul 2021SQLAlchemy library with jupyter notebook ide is used
import sqlalchemy
Replace * with your Mysql Password
sqlalchemy.create_engine("mysql+mysqldb://root:*****@localhost:3306/oracle_emp")
Engine(mysql+mysqldb://root:***@localhost:3306/oracle_emp)
%load_ext sql
C:\Users\tskir\Anaconda3\lib\site-packages\numpy\_distributor_init.py:32: UserWarning: loaded more than 1 DLL from .libs:
C:\Users\tskir\Anaconda3\lib\site-packages\numpy\.libs\libopenblas.PYQHXLVVQ7VESDPUVUADXEVJOBGHJPAY.gfortran-win_amd64.dll
C:\Users\tskir\Anaconda3\lib\site-packages\numpy\.libs\libopenblas.QVLO2T66WEPI7JZ63PS3HMOHFEY472BC.gfortran-win_amd64.dll
stacklevel=1)
Replace * with your Mysql Password
%sql "mysql+mysqldb://root:*****@localhost:3306/oracle_emp"
51. List the Emps who are senior to their own MGRS.
%%sql
select * from emp e where (datediff(curdate(),hiredate)/365)>(select (datediff(curdate(),hiredate)/365) from emp f where f.empno=e.mgr )
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
6 rows affected.
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
|---|---|---|---|---|---|---|---|
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | None | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | None | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | None | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | None | 10 |
52. List the Emps of Deptno 20 whose Jobs are same as Deptno10.
%%sql
select * from emp e where e.deptno=20 and e.job in (select f.job from emp f where f.deptno=10) ;
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
3 rows affected.
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
|---|---|---|---|---|---|---|---|
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | None | 20 |
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | None | 20 |
| 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100.00 | None | 20 |
53.List the Emps whose Sal is same as FORD or SMITH in desc order of Sal.
%%sql
select * from emp e where e.sal in(select f.sal from emp f where f.ename like "FORD" or f.ename like "SMITH") and e.ename not like "FORD" and e.ename not like "Smith" order by sal desc
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
1 rows affected.
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
|---|---|---|---|---|---|---|---|
| 7788 | SCOTT | ANALYST | 7566 | 1987-07-13 | 3000.00 | None | 20 |
54. List the emps Whose Jobs are same as MILLER or Sal is more than ALLEN.
%%sql
select * from emp e where e.job like (select f.job from emp f where f.ename like "Miller")
or e.sal >(select g.sal from emp g where g.ename="Allen")
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
10 rows affected.
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
|---|---|---|---|---|---|---|---|
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | None | 20 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | None | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | None | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | None | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-07-13 | 3000.00 | None | 20 |
| 7839 | KING | PRESIDENT | None | 1981-11-07 | 5000.00 | None | 10 |
| 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100.00 | None | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | None | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | None | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | None | 10 |
55. List the Emps whose Sal is > the maximum remuneration of the SALESMAN.
%%sql
select * from emp e where e.sal > (select max(f.sal+f.comm) from emp f where f.job like "salesman")
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
5 rows affected.
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
|---|---|---|---|---|---|---|---|
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | None | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | None | 30 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-07-13 | 3000.00 | None | 20 |
| 7839 | KING | PRESIDENT | None | 1981-11-07 | 5000.00 | None | 10 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | None | 20 |
56. List the emps who are senior to BLAKE working at CHICAGO & BOSTON.
%%sql
select e.* from emp e,dept d where (datediff(curdate(),e.hiredate)/365) >(select (datediff(curdate(),f.hiredate)/365)
from emp f where f.ename like "Blake")
and e.deptno=d.deptno and d.loc like "chicago"
union
select e.* from emp e,dept d where (datediff(curdate(),e.hiredate)/365) >(select (datediff(curdate(),f.hiredate)/365)
from emp f where f.ename like "Blake")
and e.deptno=d.deptno and d.loc like "boston"
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
2 rows affected.
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
|---|---|---|---|---|---|---|---|
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
57. List the Emps of Grade 3,4 belongs to the dept ACCOUNTING and RESEARCH whose Sal is more than ALLEN and exp more than Blake in the asc order of EXP.
%%sql
select * from emp e,salgrade g,dept d where e.sal between g.losal and g.hisal and g.grade in(3,4) and e.deptno=d.deptno
and d.dname in("Accounting","Research") and e.sal>(select f.sal from emp f where f.ename="Allen") and (datediff(curdate(),e.hiredate)/365)>
(select (datediff(curdate(),f.hiredate)/365)
from emp f where f.ename like "Blake") order by e.hiredate
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
1 rows affected.
| empno | ename | job | mgr | hiredate | sal | comm | deptno | grade | losal | hisal | deptno_1 | dname | loc |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | None | 20 | 4 | 2001 | 3000 | 20 | RESEARCH | DALLAS |
58. List the emps whose jobs same as SMITH or ALLEN.
%%sql
select * from emp
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
14 rows affected.
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
|---|---|---|---|---|---|---|---|
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | None | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | None | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | None | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | None | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-07-13 | 3000.00 | None | 20 |
| 7839 | KING | PRESIDENT | None | 1981-11-07 | 5000.00 | None | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100.00 | None | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | None | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | None | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | None | 10 |
59. Write a Query to display the details of emps whose Sal is same as of
a) Employee Sal of EMP1 table.
b) ¾ Sal of any Mgr of EMP2 table.
c) The sal of any person with exp of 38 years belongs to the sales dept of emp3 table.
d) Any grade 2 employee of emp4 table.
e) Any grade 2 and 3 employee working fro sales dept or operations dept joined in 1989.
Answers to 59(a)-(e)
59(a) Employee Sal of EMP1 table.
%%sql
create table emp1 (sal int4)
%%sql
insert into emp1 select sal from emp;
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
14 rows affected.
[]
%%sql
select * from emp1
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
14 rows affected.
| sal |
|---|
| 800 |
| 1600 |
| 1250 |
| 2975 |
| 1250 |
| 2850 |
| 2450 |
| 3000 |
| 5000 |
| 1500 |
| 1100 |
| 950 |
| 3000 |
| 1300 |
59(b) ¾ Sal of any Mgr of EMP2 table.
%%sql
CREATE TABLE emp2 (sal int4)
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
0 rows affected.
[]
%%sql
insert into emp2 select (3/4)*sal from emp e where e.job like "Manager"
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
3 rows affected.
[]
%%sql
select * from emp2
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
3 rows affected.
| sal |
|---|
| 2231 |
| 2138 |
| 1838 |
59(c) The sal of any person with exp of 38 years belongs to the sales dept of emp3 table.
%%sql
create table emp3 (sal int4)
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
0 rows affected.
[]
%%sql
insert into emp3 select e.sal from emp e,dept d where (datediff(curdate(),e.hiredate)/365) >38 and e.deptno=d.deptno and d.dname like "Sales"
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
6 rows affected.
[]
%%sql
select * from emp3
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
6 rows affected.
| sal |
|---|
| 1600 |
| 1250 |
| 1250 |
| 2850 |
| 1500 |
| 950 |
59(d) Any grade 2 employee of emp4 table.
%%sql
create table emp4(
empno int4,
ename varchar(10),
job varchar(9),
mgr int4,
hiredate date,
sal decimal(7,2),
comm decimal(7,2),
deptno int2,
constraint pk_emp primary key (empno)
)
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
0 rows affected.
[]
%%sql
insert into emp4 select e.* from emp e,salgrade g
where e.sal between g.losal and g.hisal and g.grade=2
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
3 rows affected.
[]
%%sql
select * from emp4
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
3 rows affected.
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
|---|---|---|---|---|---|---|---|
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | None | 10 |
59 e) Any grade 2 and 3 employee working from sales dept or operations dept joined in 1989.
%%sql
create table emp5 (empno int4,
ename varchar(10),
job varchar(9),
mgr int4,
hiredate date,
sal decimal(7,2),
comm decimal(7,2),
deptno int2,
constraint pk_emp primary key (empno))
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
0 rows affected.
[]
%%sql
insert into emp5 select e.* from emp e,salgrade g,dept d where 1989=extract(year from e.hiredate)
and e.sal between g.losal and g.hisal and g.grade in(2,3) and e.deptno=d.deptno and d.dname in ("Sales","Operations")
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
0 rows affected.
[]
%%sql
select * from emp5
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
0 rows affected.
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
|---|
60. Any jobs of deptno 10 those that are not found in deptno 20.
%%sql
select distinct(e1.job) from emp e1,emp e2 where e1.deptno=10 and e2.deptno=20 and e1.job<>e2.job
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
3 rows affected.
| job |
|---|
| MANAGER |
| PRESIDENT |
| CLERK |
61. List of emps of emp4 who are not found in emp5.
%%sql
select e1.ename from emp4 e1,emp5 e2 where e1.ename<>e2.ename
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
0 rows affected.
| ename |
|---|
62. Find the highest sal of EMP table.
%%sql
select max(sal) from emp
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
1 rows affected.
| max(sal) |
|---|
| 5000.00 |
63. Find details of highest paid employee.
%%sql
select * from emp where sal in(select max(e.sal) from emp e)
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
1 rows affected.
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
|---|---|---|---|---|---|---|---|
| 7839 | KING | PRESIDENT | None | 1981-11-07 | 5000.00 | None | 10 |
64. Find the highest paid employee of sales department.
%%sql
select max(e.sal) from emp e,dept d where e.deptno=d.deptno and d.dname="Sales"
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
1 rows affected.
| max(e.sal) |
|---|
| 2850.00 |
65. List the most recently hired emp of grade3 belongs to location CHICAGO.
%%sql
select * from emp e,salgrade g,dept d where e.sal between g.losal and g.hisal and g.grade=3 and e.deptno=d.deptno and d.loc="chicago"
order by e.hiredate desc limit 1
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
1 rows affected.
| empno | ename | job | mgr | hiredate | sal | comm | deptno | grade | losal | hisal | deptno_1 | dname | loc |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | 3 | 1401 | 2000 | 30 | SALES | CHICAGO |
66. List the employees who are senior to most recently hired employee working under king.
%%sql
select * from emp e1 where e1.hiredate<(select max(e.hiredate) from emp e where e.mgr in
(select e2.empno from emp e2 where e2.ename="King"))
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
5 rows affected.
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
|---|---|---|---|---|---|---|---|
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | None | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | None | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | None | 30 |
67. List the details of the employee belongs to newyork with grade 3 to 5 except ‘PRESIDENT’ whose sal> the highest paid employee of Chicago in a group where there is manager and salesman not working under king
%%sql
select e.* from emp e,dept d,salgrade g where e.deptno=d.deptno and d.loc="new york"
and
e.sal between g.losal and g.hisal and g.grade between 3 and 5 and
e.job<>"President" and e.sal >(select max(e1.sal) from emp e1,dept d1 where e1.deptno=d1.deptno and
d1.loc="Chicago" and e1.mgr<>(select e3.empno from emp e3 where e3.job="President" ) group by e1.deptno )
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
1 rows affected.
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
|---|---|---|---|---|---|---|---|
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | None | 10 |
68. List the details of the senior employee belongs to 1981.
%%sql
select * from emp e where 1981=extract( year from e.hiredate) order by e.hiredate asc limit 1
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
1 rows affected.
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
|---|---|---|---|---|---|---|---|
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
69. List the employees who joined in 1981 with the job same as the most senior person of the year 1981.
%%sql
select * from emp e where 1981=extract(year from e.hiredate) and e.job = (select e1.job from emp e1 where 1981=extract( year from e1.hiredate) order by e1.hiredate asc limit 1)
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
4 rows affected.
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
|---|---|---|---|---|---|---|---|
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
70. List the most senior employee working under the king and grade is more than 3.
%%sql
select * from emp e where e.hiredate =(select min(e1.hiredate) from emp e1,salgrade g1 where
e1.sal between g1.losal and g1.hisal and g1.grade > 3
and e1.mgr =(select e2.empno from emp e2 where e2.ename="King"))
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
1 rows affected.
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
|---|---|---|---|---|---|---|---|
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | None | 20 |
71. Find the cumulative sal given to the MGR.
%%sql
select sum(e.sal) from emp e where e.job="Manager"
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
1 rows affected.
| sum(e.sal) |
|---|
| 8275.00 |
72. Find the total annual sal to distribute job wise in the year 81.
%%sql
select e.job,sum(e.sal)*12 as AnnualSal from emp e where 1981=extract(year from e.hiredate) group by e.job
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
5 rows affected.
| job | AnnualSal |
|---|---|
| SALESMAN | 67200.00 |
| MANAGER | 99300.00 |
| PRESIDENT | 60000.00 |
| CLERK | 11400.00 |
| ANALYST | 36000.00 |
73. Display total sal employee belonging to grade 3.
%%sql
select sum(e.sal) from emp e,salgrade g where e.sal between g.losal and g.hisal and g.grade=3
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
1 rows affected.
| sum(e.sal) |
|---|
| 3100.00 |
74. Display the average salaries of all the clerks.
%%sql
select avg(e.sal) from emp e where e.job="clerk"
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
1 rows affected.
| avg(e.sal) |
|---|
| 1037.500000 |
75. List the employee in dept 20 whose sal is >the average sal 0f dept 10 emps.
%%sql
select * from emp e where e.deptno=20 and e.sal>(select avg(e1.sal) from emp e1 where e1.deptno=10)
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
3 rows affected.
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
|---|---|---|---|---|---|---|---|
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | None | 20 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-07-13 | 3000.00 | None | 20 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | None | 20 |
76. Display the number of employee for each job group deptno wise.
%%sql
select e.deptno,e.job,count(*) from emp e group by e.job,e.deptno
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
9 rows affected.
| deptno | job | count(*) |
|---|---|---|
| 20 | CLERK | 2 |
| 30 | SALESMAN | 4 |
| 20 | MANAGER | 1 |
| 30 | MANAGER | 1 |
| 10 | MANAGER | 1 |
| 20 | ANALYST | 2 |
| 10 | PRESIDENT | 1 |
| 30 | CLERK | 1 |
| 10 | CLERK | 1 |
77. List the manager’s deptno and the number of employees working for those managers in the ascending deptno.
%%sql
select e.deptno,e.ename,e.job,count(*) from emp e ,emp e1
where e.job="Manager" and e.empno=e1.mgr group by e.deptno
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
3 rows affected.
| deptno | ename | job | count(*) |
|---|---|---|---|
| 30 | BLAKE | MANAGER | 5 |
| 20 | JONES | MANAGER | 2 |
| 10 | CLARK | MANAGER | 1 |
78. List the department details where at least two emps are working
%%sql
select d.*,count(*) as CountOfEmployees from emp e,dept d where d.deptno=e.deptno and 1<(select count(*) from emp e1) group by d.deptno
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
3 rows affected.
| deptno | dname | loc | CountOfEmployees |
|---|---|---|---|
| 10 | ACCOUNTING | NEW YORK | 3 |
| 20 | RESEARCH | DALLAS | 5 |
| 30 | SALES | CHICAGO | 6 |
79. Display the Grade, Number of emps, and max sal of each grade.
%%sql
select g.grade,max(e.sal),count(*) from salgrade g ,emp e
where e.sal between g.losal and g.hisal group by g.grade order by g.grade
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
5 rows affected.
| grade | max(e.sal) | count(*) |
|---|---|---|
| 1 | 1100.00 | 3 |
| 2 | 1300.00 | 3 |
| 3 | 1600.00 | 2 |
| 4 | 3000.00 | 5 |
| 5 | 5000.00 | 1 |
80. Display dname, grade, No. of emps where at least two emps are clerks.
%%sql
select d.deptno,d.dname,g.grade,count(*) as EmpCountInDept from emp e,dept d,salgrade g where
e.sal between g.losal and g.hisal and e.deptno=d.deptno group by e.deptno
having e.deptno=(select e2.deptno from emp e2 where e2.job="clerk" group by e2.deptno having 1< count(*)) order by d.deptno
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
1 rows affected.
| deptno | dname | grade | EmpCountInDept |
|---|---|---|---|
| 20 | RESEARCH | 1 | 5 |
81. List the details of the department where maximum number of emps are working.
%%sql
select d.*,count(*) from dept d,emp e where d.deptno=e.deptno group by e.deptno order by count(*) desc limit 1
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
1 rows affected.
| deptno | dname | loc | count(*) |
|---|---|---|---|
| 30 | SALES | CHICAGO | 6 |
82.Display the emps whose manager name is jones.
%%sql
select * from emp e where e.mgr=(select e1.empno from emp e1 where e1.ename="jones")
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
2 rows affected.
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
|---|---|---|---|---|---|---|---|
| 7788 | SCOTT | ANALYST | 7566 | 1987-07-13 | 3000.00 | None | 20 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | None | 20 |
83. List the employees whose salary is more than 3000 after giving 20% increment.
%%sql
select * from emp e where e.sal+(e.sal*20/100)>3000
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
5 rows affected.
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
|---|---|---|---|---|---|---|---|
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | None | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | None | 30 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-07-13 | 3000.00 | None | 20 |
| 7839 | KING | PRESIDENT | None | 1981-11-07 | 5000.00 | None | 10 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | None | 20 |
84. List the emps with dept names.
%%sql
select e.*,d.dname from emp e,dept d where e.deptno=d.deptno
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
14 rows affected.
| empno | ename | job | mgr | hiredate | sal | comm | deptno | dname |
|---|---|---|---|---|---|---|---|---|
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | None | 10 | ACCOUNTING |
| 7839 | KING | PRESIDENT | None | 1981-11-07 | 5000.00 | None | 10 | ACCOUNTING |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | None | 10 | ACCOUNTING |
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | None | 20 | RESEARCH |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | None | 20 | RESEARCH |
| 7788 | SCOTT | ANALYST | 7566 | 1987-07-13 | 3000.00 | None | 20 | RESEARCH |
| 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100.00 | None | 20 | RESEARCH |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | None | 20 | RESEARCH |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | SALES |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | SALES |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | SALES |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | None | 30 | SALES |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | SALES |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | None | 30 | SALES |
85. List the emps who are not working in sales dept.
%%sql
select e.*,d.dname from emp e,dept d where e.deptno=d.deptno and d.dname<>"Sales"
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
8 rows affected.
| empno | ename | job | mgr | hiredate | sal | comm | deptno | dname |
|---|---|---|---|---|---|---|---|---|
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | None | 10 | ACCOUNTING |
| 7839 | KING | PRESIDENT | None | 1981-11-07 | 5000.00 | None | 10 | ACCOUNTING |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | None | 10 | ACCOUNTING |
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | None | 20 | RESEARCH |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | None | 20 | RESEARCH |
| 7788 | SCOTT | ANALYST | 7566 | 1987-07-13 | 3000.00 | None | 20 | RESEARCH |
| 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100.00 | None | 20 | RESEARCH |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | None | 20 | RESEARCH |
86. List the emps name ,dept, sal and comm. For those whose salary is between 2000 and 5000 while loc is Chicago.
%%sql
select e.ename,e.deptno,e.sal,e.comm,d.loc from emp e,dept d where e.sal between 2000 and 5000 and e.deptno=d.deptno and d.loc="Chicago"
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
1 rows affected.
| ename | deptno | sal | comm | loc |
|---|---|---|---|---|
| BLAKE | 30 | 2850.00 | None | CHICAGO |
87. List the emps whose sal is greater than his managers salary
%%sql
select * from emp e where e.sal>(select e2.sal from emp e2,emp e1 where e2.mgr=e1.empno and e2.job="Manager" group by e2.mgr )
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
3 rows affected.
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
|---|---|---|---|---|---|---|---|
| 7788 | SCOTT | ANALYST | 7566 | 1987-07-13 | 3000.00 | None | 20 |
| 7839 | KING | PRESIDENT | None | 1981-11-07 | 5000.00 | None | 10 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | None | 20 |
88. List the grade, EMP name for the deptno 10 or deptno 30 but sal grade is not 4 while they joined the company before ’31-dec-82’.
%%sql
select e.ename,g.grade,e.hiredate from emp e,salgrade g,dept d where e.deptno=d.deptno and d.deptno in (10,30)
and e.sal between g.losal and g.hisal and g.grade not in (4)
and e.hiredate>1982-12-31
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
7 rows affected.
| ename | grade | hiredate |
|---|---|---|
| JAMES | 1 | 1981-12-03 |
| MARTIN | 2 | 1981-09-28 |
| WARD | 2 | 1981-02-22 |
| MILLER | 2 | 1982-01-23 |
| TURNER | 3 | 1981-09-08 |
| ALLEN | 3 | 1981-02-20 |
| KING | 5 | 1981-11-07 |
89. List the name ,job, dname, location for those who are working as MGRS.
%%sql
select e.ename,e.job,d.dname,d.loc from emp e,dept d where e.job="Manager" and e.deptno=d.deptno
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
3 rows affected.
| ename | job | dname | loc |
|---|---|---|---|
| JONES | MANAGER | RESEARCH | DALLAS |
| BLAKE | MANAGER | SALES | CHICAGO |
| CLARK | MANAGER | ACCOUNTING | NEW YORK |
90. List the emps whose mgr name is jones and also list their manager name.
%%sql
select e.ename,e2.ename as MgrName from emp e, emp e2 where
e.mgr=(select e1.empno from emp e1 where e1.ename="Jones")
and e.mgr=e2.empno and e2.ename="jones"
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
2 rows affected.
| ename | MgrName |
|---|---|
| SCOTT | JONES |
| FORD | JONES |
91. List the name and salary of ford if his salary is equal to hisal of his grade.
%%sql
select * from emp
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
14 rows affected.
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
|---|---|---|---|---|---|---|---|
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | None | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | None | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | None | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | None | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-07-13 | 3000.00 | None | 20 |
| 7839 | KING | PRESIDENT | None | 1981-11-07 | 5000.00 | None | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100.00 | None | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | None | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | None | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | None | 10 |
92. List the name, job, dname ,sal, grade dept wise
%%sql
select e.ename,d.deptno,d.dname,e.sal,g.grade from emp e,dept d,salgrade g
where e.sal between g.losal and g.hisal and e.deptno=d.deptno order by e.deptno
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
14 rows affected.
| ename | deptno | dname | sal | grade |
|---|---|---|---|---|
| CLARK | 10 | ACCOUNTING | 2450.00 | 4 |
| KING | 10 | ACCOUNTING | 5000.00 | 5 |
| MILLER | 10 | ACCOUNTING | 1300.00 | 2 |
| SMITH | 20 | RESEARCH | 800.00 | 1 |
| JONES | 20 | RESEARCH | 2975.00 | 4 |
| SCOTT | 20 | RESEARCH | 3000.00 | 4 |
| ADAMS | 20 | RESEARCH | 1100.00 | 1 |
| FORD | 20 | RESEARCH | 3000.00 | 4 |
| ALLEN | 30 | SALES | 1600.00 | 3 |
| WARD | 30 | SALES | 1250.00 | 2 |
| MARTIN | 30 | SALES | 1250.00 | 2 |
| BLAKE | 30 | SALES | 2850.00 | 4 |
| TURNER | 30 | SALES | 1500.00 | 3 |
| JAMES | 30 | SALES | 950.00 | 1 |
93. List the emp name, job, sal, grade and dname except clerks and sort on the basis of highest sal.
%%sql
select e.ename,e.job,e.sal,g.grade,d.dname from emp e,dept d,salgrade g where e.deptno=d.deptno and e.sal between g.losal and g.hisal
and e.job not in ("clerk") order by e.sal desc
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
10 rows affected.
| ename | job | sal | grade | dname |
|---|---|---|---|---|
| KING | PRESIDENT | 5000.00 | 5 | ACCOUNTING |
| SCOTT | ANALYST | 3000.00 | 4 | RESEARCH |
| FORD | ANALYST | 3000.00 | 4 | RESEARCH |
| JONES | MANAGER | 2975.00 | 4 | RESEARCH |
| BLAKE | MANAGER | 2850.00 | 4 | SALES |
| CLARK | MANAGER | 2450.00 | 4 | ACCOUNTING |
| ALLEN | SALESMAN | 1600.00 | 3 | SALES |
| TURNER | SALESMAN | 1500.00 | 3 | SALES |
| WARD | SALESMAN | 1250.00 | 2 | SALES |
| MARTIN | SALESMAN | 1250.00 | 2 | SALES |
94. List the emps name, job who are with out manager.
%%sql
select * from emp e where e.mgr is null
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
1 rows affected.
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
|---|---|---|---|---|---|---|---|
| 7839 | KING | PRESIDENT | None | 1981-11-07 | 5000.00 | None | 10 |
95. List the names of the emps who are getting the highest sal dept wise.
%%sql
select e1.ename,e1.deptno,max(e1.sal)as MaxSal from emp e1 group by e1.deptno
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
3 rows affected.
| ename | deptno | MaxSal |
|---|---|---|
| CLARK | 10 | 5000.00 |
| SMITH | 20 | 3000.00 |
| ALLEN | 30 | 2850.00 |
96. List the emps whose sal is equal to the average of max and minimum
%%sql
select * from emp e where e.sal=(select (max(e.sal)+min(e.sal))/2 from emp e)
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
0 rows affected.
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
|---|
97. List the no. of emps in each department where the no. is more than 3.
%%sql
select e.deptno,count(*) from emp e group by e.deptno having 3<(count(*))
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
2 rows affected.
| deptno | count(*) |
|---|---|
| 20 | 5 |
| 30 | 6 |
98. List the names of depts. Where atleast 3 are working in that department.
%%sql
select d.dname,count(*) from dept d,emp e where d.deptno=e.deptno group by e.deptno having 3<=(select count(*) from emp e)
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
3 rows affected.
| dname | count(*) |
|---|---|
| ACCOUNTING | 3 |
| RESEARCH | 5 |
| SALES | 6 |
99. List the managers whose sal is more than his employess avg salary.
%%sql
select * from emp e where e.job="manager" and e.sal > (select avg( e1.sal) from emp e1 where e1.mgr=e.empno)group by e.deptno
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
2 rows affected.
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
|---|---|---|---|---|---|---|---|
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | None | 10 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | None | 30 |
100. List the name,salary,comm. For those employees whose net pay is greater than or equal to any other employee salary of the company.
%%sql
select e.ename,e.sal,e.comm from emp e where (e.sal+e.comm)>=any(select e1.sal from emp e1)
* mysql+mysqldb://root:***@localhost:3306/oracle_emp
4 rows affected.
| ename | sal | comm |
|---|---|---|
| ALLEN | 1600.00 | 300.00 |
| WARD | 1250.00 | 500.00 |
| MARTIN | 1250.00 | 1400.00 |
| TURNER | 1500.00 | 0.00 |