Trail Blazed

This website will walk you through the roadmap of Cloud Data Engineer.

© 2022. All rights reserved.

SQL Practice - Employee Database SQL Queires 1-50

21 Jul 2021

The Jupyter notebook was used and SQLAlchemy library helped connecting with mySQL

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"

1. Display all the information of the EMP table?

%%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

2. Display unique Jobs from EMP table?

%%sql
select distinct(job) from emp;

 * mysql+mysqldb://root:***@localhost:3306/oracle_emp
5 rows affected.
job
CLERK
SALESMAN
MANAGER
ANALYST
PRESIDENT

3.List the emps in the asc order of their Salaries?

%%sql
select ename,sal from emp order by sal asc 

 * mysql+mysqldb://root:***@localhost:3306/oracle_emp
14 rows affected.
ename sal
SMITH 800.00
JAMES 950.00
ADAMS 1100.00
WARD 1250.00
MARTIN 1250.00
MILLER 1300.00
TURNER 1500.00
ALLEN 1600.00
CLARK 2450.00
BLAKE 2850.00
JONES 2975.00
SCOTT 3000.00
FORD 3000.00
KING 5000.00

4. List the details of the emps in asc order of the Dptnos and desc of Jobs?

%%sql
select ename,deptno,job from emp order by deptno asc,job desc;

 * mysql+mysqldb://root:***@localhost:3306/oracle_emp
14 rows affected.
ename deptno job
KING 10 PRESIDENT
CLARK 10 MANAGER
MILLER 10 CLERK
JONES 20 MANAGER
SMITH 20 CLERK
ADAMS 20 CLERK
SCOTT 20 ANALYST
FORD 20 ANALYST
ALLEN 30 SALESMAN
WARD 30 SALESMAN
MARTIN 30 SALESMAN
TURNER 30 SALESMAN
BLAKE 30 MANAGER
JAMES 30 CLERK

5.Display all the unique job groups in the descending order?

%%sql
select job from emp group by job order by job desc

 * mysql+mysqldb://root:***@localhost:3306/oracle_emp
5 rows affected.
job
SALESMAN
PRESIDENT
MANAGER
CLERK
ANALYST

6. Display all the details of all ‘Mgrs’

%%sql
select * from emp where job="Manager"

 * 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
7698 BLAKE MANAGER 7839 1981-05-01 2850.00 None 30
7782 CLARK MANAGER 7839 1981-06-09 2450.00 None 10

7.List the emps who joined before 1981.

%%sql
select * from emp where hiredate<"1981-01-01"

 * mysql+mysqldb://root:***@localhost:3306/oracle_emp
1 rows affected.
empno ename job mgr hiredate sal comm deptno
7369 SMITH CLERK 7902 1980-12-17 800.00 None 20

8. List the Empno, Ename, Sal, Daily sal of all emps in the asc order of Annsal

%%sql
select empno,ename,sal,sal/30 as dailysal,sal*12 as annualsal  from emp order by annualsal desc

 * mysql+mysqldb://root:***@localhost:3306/oracle_emp
14 rows affected.
empno ename sal dailysal annualsal
7839 KING 5000.00 166.666667 60000.00
7788 SCOTT 3000.00 100.000000 36000.00
7902 FORD 3000.00 100.000000 36000.00
7566 JONES 2975.00 99.166667 35700.00
7698 BLAKE 2850.00 95.000000 34200.00
7782 CLARK 2450.00 81.666667 29400.00
7499 ALLEN 1600.00 53.333333 19200.00
7844 TURNER 1500.00 50.000000 18000.00
7934 MILLER 1300.00 43.333333 15600.00
7521 WARD 1250.00 41.666667 15000.00
7654 MARTIN 1250.00 41.666667 15000.00
7876 ADAMS 1100.00 36.666667 13200.00
7900 JAMES 950.00 31.666667 11400.00
7369 SMITH 800.00 26.666667 9600.00

9. Display the Empno, Ename, job, Hiredate, Exp of all Mgrs

%%sql
select empno,ename,job,hiredate,floor(datediff(curdate(),hiredate)/365) as exp from emp where job="Manager"

 * mysql+mysqldb://root:***@localhost:3306/oracle_emp
3 rows affected.
empno ename job hiredate exp
7566 JONES MANAGER 1981-04-02 40
7698 BLAKE MANAGER 1981-05-01 40
7782 CLARK MANAGER 1981-06-09 40

10. List the Empno, Ename, Sal, Exp of all emps working for Mgr 7698.

%%sql
select empno,ename,sal,floor(datediff(curdate(),hiredate)/365) as exp,mgr from emp where mgr=7698;

 * mysql+mysqldb://root:***@localhost:3306/oracle_emp
5 rows affected.
empno ename sal exp mgr
7499 ALLEN 1600.00 40 7698
7521 WARD 1250.00 40 7698
7654 MARTIN 1250.00 39 7698
7844 TURNER 1500.00 39 7698
7900 JAMES 950.00 39 7698

11. Display all the details of the emps whose Comm. Is more than their Sal.

%%sql
select * from emp e where e.comm>e.sal

 * mysql+mysqldb://root:***@localhost:3306/oracle_emp
1 rows affected.
empno ename job mgr hiredate sal comm deptno
7654 MARTIN SALESMAN 7698 1981-09-28 1250.00 1400.00 30

12. List the emps in the asc order of Designations of those joined after the second half of 1981.

%%sql
select * from emp where hiredate between "1981-07-01"and "1981-12-31" order by job asc;

 * mysql+mysqldb://root:***@localhost:3306/oracle_emp
5 rows affected.
empno ename job mgr hiredate sal comm deptno
7902 FORD ANALYST 7566 1981-12-03 3000.00 None 20
7900 JAMES CLERK 7698 1981-12-03 950.00 None 30
7839 KING PRESIDENT None 1981-11-07 5000.00 None 10
7654 MARTIN SALESMAN 7698 1981-09-28 1250.00 1400.00 30
7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30

13. List the emps along with their Exp and Daily Sal is more than Rs.100.

%%sql 
select *,sal/30 as dailysal,floor(datediff(curdate(),hiredate)/365) as exp from emp where (sal/30)>100

 * mysql+mysqldb://root:***@localhost:3306/oracle_emp
1 rows affected.
empno ename job mgr hiredate sal comm deptno dailysal exp
7839 KING PRESIDENT None 1981-11-07 5000.00 None 10 166.666667 39

14. List the emps who are either ‘CLERK’ or ‘ANALYST’ in the Desc order.

%%sql
select * from emp where job like "clerk" or job like "analyst" order by job desc

 * 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
7876 ADAMS CLERK 7788 1987-07-13 1100.00 None 20
7900 JAMES CLERK 7698 1981-12-03 950.00 None 30
7934 MILLER CLERK 7782 1982-01-23 1300.00 None 10
7788 SCOTT ANALYST 7566 1987-07-13 3000.00 None 20
7902 FORD ANALYST 7566 1981-12-03 3000.00 None 20

15. List the emps who joined on 1-MAY-81,3-DEC-81,17-DEC-81,19-JAN-80 in asc order of seniority.

%%sql
select * from emp where hiredate in ("1981-05-01", "1981-12-03" ,"1981-12-17" , "1980-01-19")
order by datediff(curdate(),hiredate) desc

 * mysql+mysqldb://root:***@localhost:3306/oracle_emp
3 rows affected.
empno ename job mgr hiredate sal comm deptno
7698 BLAKE MANAGER 7839 1981-05-01 2850.00 None 30
7900 JAMES CLERK 7698 1981-12-03 950.00 None 30
7902 FORD ANALYST 7566 1981-12-03 3000.00 None 20

16.List the emp who are working for the Deptno 10 or 20.

%%sql
select * from emp where deptno in (10,20)

 * mysql+mysqldb://root:***@localhost:3306/oracle_emp
8 rows affected.
empno ename job mgr hiredate sal comm deptno
7782 CLARK MANAGER 7839 1981-06-09 2450.00 None 10
7839 KING PRESIDENT None 1981-11-07 5000.00 None 10
7934 MILLER CLERK 7782 1982-01-23 1300.00 None 10
7369 SMITH CLERK 7902 1980-12-17 800.00 None 20
7566 JONES MANAGER 7839 1981-04-02 2975.00 None 20
7788 SCOTT ANALYST 7566 1987-07-13 3000.00 None 20
7876 ADAMS CLERK 7788 1987-07-13 1100.00 None 20
7902 FORD ANALYST 7566 1981-12-03 3000.00 None 20

17.List the emps who are joined in the year 81.

%%sql
select * from emp where 1981=extract(year from hiredate)

 * mysql+mysqldb://root:***@localhost:3306/oracle_emp
10 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
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
7839 KING PRESIDENT None 1981-11-07 5000.00 None 10
7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30
7900 JAMES CLERK 7698 1981-12-03 950.00 None 30
7902 FORD ANALYST 7566 1981-12-03 3000.00 None 20

18. List the emps who are joined in the month of Sep 1981.

%%sql
select * from emp where 09=extract(month from hiredate) and 1981=extract(year from hiredate)

 * mysql+mysqldb://root:***@localhost:3306/oracle_emp
2 rows affected.
empno ename job mgr hiredate sal comm deptno
7654 MARTIN SALESMAN 7698 1981-09-28 1250.00 1400.00 30
7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30

19. List the emps Who Annual sal ranging from 22000 and 45000.

%%sql
select *,sal*12 as annualsal from emp where sal*12  between 22000 and 45000 order by annualsal asc


 * mysql+mysqldb://root:***@localhost:3306/oracle_emp
5 rows affected.
empno ename job mgr hiredate sal comm deptno annualsal
7782 CLARK MANAGER 7839 1981-06-09 2450.00 None 10 29400.00
7698 BLAKE MANAGER 7839 1981-05-01 2850.00 None 30 34200.00
7566 JONES MANAGER 7839 1981-04-02 2975.00 None 20 35700.00
7788 SCOTT ANALYST 7566 1987-07-13 3000.00 None 20 36000.00
7902 FORD ANALYST 7566 1981-12-03 3000.00 None 20 36000.00

20.List the Enames those are having five characters in their Names.

Answer 1
%%sql
select ename from emp where ename like "_____"

 * mysql+mysqldb://root:***@localhost:3306/oracle_emp
8 rows affected.
ename
SMITH
ALLEN
JONES
BLAKE
CLARK
SCOTT
ADAMS
JAMES
Answer 2
%%sql
select ename from emp where length(ename)=5

 * mysql+mysqldb://root:***@localhost:3306/oracle_emp
8 rows affected.
ename
SMITH
ALLEN
JONES
BLAKE
CLARK
SCOTT
ADAMS
JAMES

21. List the Enames those are starting with ‘S’ and with five characters.

Answer 1
%%sql
select ename from emp where ename like "s%" and length(ename)=5

 * mysql+mysqldb://root:***@localhost:3306/oracle_emp
2 rows affected.
ename
SMITH
SCOTT
Answer 2
%%sql
select ename from emp where ename like "s____"

 * mysql+mysqldb://root:***@localhost:3306/oracle_emp
2 rows affected.
ename
SMITH
SCOTT

22. List the emps those are having four chars and third character must be ‘r’.

%%sql
select ename from emp where ename like "__r_";

 * mysql+mysqldb://root:***@localhost:3306/oracle_emp
2 rows affected.
ename
WARD
FORD

23. List the Five character names starting with ‘S’ and ending with ‘H’.

%%sql
select ename from emp where ename like "s___h";

 * mysql+mysqldb://root:***@localhost:3306/oracle_emp
1 rows affected.
ename
SMITH

24. List the emps who joined in January.

%%sql
select ename,hiredate from emp where 01=extract(month from hiredate);

 * mysql+mysqldb://root:***@localhost:3306/oracle_emp
1 rows affected.
ename hiredate
MILLER 1982-01-23

25. List the emps who joined in the month of which second character is ‘a’.

%%sql
select *, monthname(hiredate) from emp where monthname(hiredate) like "_a%";

 * mysql+mysqldb://root:***@localhost:3306/oracle_emp
2 rows affected.
empno ename job mgr hiredate sal comm deptno monthname(hiredate)
7698 BLAKE MANAGER 7839 1981-05-01 2850.00 None 30 May
7934 MILLER CLERK 7782 1982-01-23 1300.00 None 10 January

26. List the emps whose Sal is four digit number ending with Zero.

%%sql
select * from emp where length(floor(sal))=4 and mod(floor(sal),10)=0;

 * mysql+mysqldb://root:***@localhost:3306/oracle_emp
11 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
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
7902 FORD ANALYST 7566 1981-12-03 3000.00 None 20
7934 MILLER CLERK 7782 1982-01-23 1300.00 None 10

27. List the emps whose names having a character set ‘ll’ together.

%%sql
select * from emp where ename like "%ll%";

 * 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
7934 MILLER CLERK 7782 1982-01-23 1300.00 None 10

28. List the emps those who joined in 80’s.

%%sql
select * from emp where

29. List the emps who does not belong to Deptno 20.

%%sql
select * from emp where deptno<>20

 * mysql+mysqldb://root:***@localhost:3306/oracle_emp
9 rows affected.
empno ename job mgr hiredate sal comm deptno
7782 CLARK MANAGER 7839 1981-06-09 2450.00 None 10
7839 KING PRESIDENT None 1981-11-07 5000.00 None 10
7934 MILLER CLERK 7782 1982-01-23 1300.00 None 10
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
7698 BLAKE MANAGER 7839 1981-05-01 2850.00 None 30
7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30
7900 JAMES CLERK 7698 1981-12-03 950.00 None 30

30. List all the emps except ‘PRESIDENT’ & ‘MGR” in asc order of Salaries.

%%sql
select * from emp where job not like "president" and job not like "Manager"  order by sal asc

 * 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
7900 JAMES CLERK 7698 1981-12-03 950.00 None 30
7876 ADAMS CLERK 7788 1987-07-13 1100.00 None 20
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
7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30
7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 30
7788 SCOTT ANALYST 7566 1987-07-13 3000.00 None 20
7902 FORD ANALYST 7566 1981-12-03 3000.00 None 20

31. List all the emps who joined before or after 1981.

%%sql
select * from emp where extract(year from hiredate)>1981 or extract(year from hiredate)<1981 

 * mysql+mysqldb://root:***@localhost:3306/oracle_emp
4 rows affected.
empno ename job mgr hiredate sal comm deptno
7369 SMITH CLERK 7902 1980-12-17 800.00 None 20
7788 SCOTT ANALYST 7566 1987-07-13 3000.00 None 20
7876 ADAMS CLERK 7788 1987-07-13 1100.00 None 20
7934 MILLER CLERK 7782 1982-01-23 1300.00 None 10

32.List the emps whose Empno not starting with digit 78.

%%sql
select * from emp where empno not like "78%"

 * mysql+mysqldb://root:***@localhost:3306/oracle_emp
11 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
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

33. List the emps who are working under ‘MGR’.

%%sql
select e.ename from emp e where e.mgr in (select empno from emp where job="Manager")

 * mysql+mysqldb://root:***@localhost:3306/oracle_emp
8 rows affected.
ename
ALLEN
WARD
MARTIN
SCOTT
TURNER
JAMES
FORD
MILLER

34. List the emps who joined in any year but not belongs to the month of December.

%%sql
select * from emp where monthname(hiredate) not like "December"

 * mysql+mysqldb://root:***@localhost:3306/oracle_emp
11 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
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
7934 MILLER CLERK 7782 1982-01-23 1300.00 None 10

35. List all the Clerks of Deptno 20.

%%sql
select * from emp where job="clerk" and deptno=20

 * mysql+mysqldb://root:***@localhost:3306/oracle_emp
2 rows affected.
empno ename job mgr hiredate sal comm deptno
7369 SMITH CLERK 7902 1980-12-17 800.00 None 20
7876 ADAMS CLERK 7788 1987-07-13 1100.00 None 20

36.List the emps of Deptno 30 or 10 joined in the year 1981.

%%sql
select * from emp where deptno=30 or deptno=10 and 1981=extract(year from hiredate)

 * mysql+mysqldb://root:***@localhost:3306/oracle_emp
8 rows affected.
empno ename job mgr hiredate sal comm deptno
7782 CLARK MANAGER 7839 1981-06-09 2450.00 None 10
7839 KING PRESIDENT None 1981-11-07 5000.00 None 10
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
7698 BLAKE MANAGER 7839 1981-05-01 2850.00 None 30
7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30
7900 JAMES CLERK 7698 1981-12-03 950.00 None 30

37. Display the details of SMITH.

%%sql
select * from emp where ename like "smith"

 * mysql+mysqldb://root:***@localhost:3306/oracle_emp
1 rows affected.
empno ename job mgr hiredate sal comm deptno
7369 SMITH CLERK 7902 1980-12-17 800.00 None 20

38. Display the location of SMITH.

%%sql
select e.ename,d.deptno,d.loc from emp e,dept d where e.deptno=d.deptno and e.ename="smith"

 * mysql+mysqldb://root:***@localhost:3306/oracle_emp
1 rows affected.
ename deptno loc
SMITH 20 DALLAS

39. List the total information of EMP table along with DNAME and Loc of all the emps Working Under ‘ACCOUNTING’ & ‘RESEARCH’ in the asc Deptno.

%%sql
select  * from emp e,dept d where e.deptno=d.deptno and d.dname in ("Accounting","research") order by e.deptno asc;

 * mysql+mysqldb://root:***@localhost:3306/oracle_emp
8 rows affected.
empno ename job mgr hiredate sal comm deptno deptno_1 dname loc
7782 CLARK MANAGER 7839 1981-06-09 2450.00 None 10 10 ACCOUNTING NEW YORK
7839 KING PRESIDENT None 1981-11-07 5000.00 None 10 10 ACCOUNTING NEW YORK
7934 MILLER CLERK 7782 1982-01-23 1300.00 None 10 10 ACCOUNTING NEW YORK
7369 SMITH CLERK 7902 1980-12-17 800.00 None 20 20 RESEARCH DALLAS
7566 JONES MANAGER 7839 1981-04-02 2975.00 None 20 20 RESEARCH DALLAS
7788 SCOTT ANALYST 7566 1987-07-13 3000.00 None 20 20 RESEARCH DALLAS
7876 ADAMS CLERK 7788 1987-07-13 1100.00 None 20 20 RESEARCH DALLAS
7902 FORD ANALYST 7566 1981-12-03 3000.00 None 20 20 RESEARCH DALLAS

40. List the Empno, Ename, Sal, Dname of all the ‘MGRS’ and ‘ANALYST’ working in New York, Dallas

with an exp more than 37 years without receiving the Comm asc order of Loc.

%%sql
select e.hiredate,e.empno,e.ename,e.sal,e.job,floor(datediff(curdate(),e.hiredate)/365) as yearsExp,d.dname,d.loc from emp e,dept d where e.job in("Manager","Analyst") and e.deptno=d.deptno and d.loc in("New York","Dallas")
and e.comm is null and 37<floor(datediff(curdate(),e.hiredate)/365)

 * mysql+mysqldb://root:***@localhost:3306/oracle_emp
3 rows affected.
hiredate empno ename sal job yearsExp dname loc
1981-04-02 7566 JONES 2975.00 MANAGER 40 RESEARCH DALLAS
1981-06-09 7782 CLARK 2450.00 MANAGER 40 ACCOUNTING NEW YORK
1981-12-03 7902 FORD 3000.00 ANALYST 39 RESEARCH DALLAS

41. Display the Empno, Ename, Sal, Dname, Loc, Deptno, Job of all emps working at CHICAGO or working for ACCOUNTING dept with Ann Sal>28000, but the Sal should not be=3000 or 2800 who doesn’t belongs to the Mgr and whose no is having a digit ‘7’ or ‘8’ in 3rd position in the asc order of Deptno and desc order of job.

%%sql
select e.empno,e.ename,e.sal,d.dname,d.loc,e.deptno,e.job from emp e,dept d where e.deptno=d.deptno
and d.loc="chicago" or d.dname="accounting" and sal*12 >28000 and sal not in(3000,2800) and e.job not like "manager" 
and (e.empno like '__7%' or '__8%' ) order by e.deptno asc,e.job desc

 * mysql+mysqldb://root:***@localhost:3306/oracle_emp
6 rows affected.
empno ename sal dname loc deptno job
7499 ALLEN 1600.00 SALES CHICAGO 30 SALESMAN
7521 WARD 1250.00 SALES CHICAGO 30 SALESMAN
7654 MARTIN 1250.00 SALES CHICAGO 30 SALESMAN
7844 TURNER 1500.00 SALES CHICAGO 30 SALESMAN
7698 BLAKE 2850.00 SALES CHICAGO 30 MANAGER
7900 JAMES 950.00 SALES CHICAGO 30 CLERK

42. Display the total information of the emps along with Grades in the asc order.

%%sql
select * from emp e, salgrade s,bonus b where

43. List all the Grade 2 and Grade 3 emps.

%%sql
select e.ename,e.sal,g.grade from emp e,salgrade g where e.sal between g.losal and g.hisal and g.grade in (2,3)

 * mysql+mysqldb://root:***@localhost:3306/oracle_emp
5 rows affected.
ename sal grade
ALLEN 1600.00 3
WARD 1250.00 2
MARTIN 1250.00 2
TURNER 1500.00 3
MILLER 1300.00 2

44. Display all Grade 4,5 Analyst and Mgr.

%%sql
select e.ename,e.job,g.grade from emp e, salgrade g where e.sal between g.losal and g.hisal and e.job in ("Analyst","Manager")

 * mysql+mysqldb://root:***@localhost:3306/oracle_emp
5 rows affected.
ename job grade
FORD ANALYST 4
SCOTT ANALYST 4
CLARK MANAGER 4
BLAKE MANAGER 4
JONES MANAGER 4

45. List the Empno, Ename, Sal, Dname, Grade, Exp, and Ann Sal of emps working for Dept10 or20.

%%sql
select e.empno,e.ename,e.sal,d.deptno,d.dname,g.grade,floor((datediff(curdate(),e.hiredate))/365) as exp,sal*12 as annulaSal 
from emp e,dept d,salgrade g where e.deptno in(10,20) and e.deptno=d.deptno and e.sal between g.losal and g.hisal 

 * mysql+mysqldb://root:***@localhost:3306/oracle_emp
8 rows affected.
empno ename sal deptno dname grade exp annulaSal
7876 ADAMS 1100.00 20 RESEARCH 1 34 13200.00
7369 SMITH 800.00 20 RESEARCH 1 40 9600.00
7934 MILLER 1300.00 10 ACCOUNTING 2 39 15600.00
7902 FORD 3000.00 20 RESEARCH 4 39 36000.00
7788 SCOTT 3000.00 20 RESEARCH 4 34 36000.00
7566 JONES 2975.00 20 RESEARCH 4 40 35700.00
7782 CLARK 2450.00 10 ACCOUNTING 4 40 29400.00
7839 KING 5000.00 10 ACCOUNTING 5 39 60000.00

46. List all the information of emp with Loc and the Grade of all the emps belong to the Grade range from 2 to 4 working at the Dept those are not starting with char set ‘OP’ and not ending with ‘S’ with the designation having a char ‘a’ any where joined in the year 1981 but not in the month of Mar or Sep and Sal not end with ‘00’ in the asc order of Grades

%%sql
select e.empno,d.loc,g.grade,d.dname,e.hiredate,e.sal from emp e,dept d,salgrade g where e.sal between g.losal and g.hisal and g.grade between 2 and 4 and e.deptno=d.deptno
and d.dname not like "op%" and d.dname not like "%s" and e.job like "%a%" and 1981=extract(year from e.hiredate) and monthname(e.hiredate)<>"March" and monthname(e.hiredate)<>"September"
and e.sal%100<>00 order by g.grade asc

 * mysql+mysqldb://root:***@localhost:3306/oracle_emp
2 rows affected.
empno loc grade dname hiredate sal
7566 DALLAS 4 RESEARCH 1981-04-02 2975.00
7782 NEW YORK 4 ACCOUNTING 1981-06-09 2450.00

47. List the details of the Depts along with Empno, Ename without using joins

%%sql
SELECT e.empno,e.ename, (SELECT dname FROM dept d WHERE d.deptno=e.deptno)dname
FROM emp e

 * mysql+mysqldb://root:***@localhost:3306/oracle_emp
14 rows affected.
empno ename dname
7369 SMITH RESEARCH
7499 ALLEN SALES
7521 WARD SALES
7566 JONES RESEARCH
7654 MARTIN SALES
7698 BLAKE SALES
7782 CLARK ACCOUNTING
7788 SCOTT RESEARCH
7839 KING ACCOUNTING
7844 TURNER SALES
7876 ADAMS RESEARCH
7900 JAMES SALES
7902 FORD RESEARCH
7934 MILLER ACCOUNTING

48. List the details of the emps whose Salaries more than the employee BLAKE.

%%sql
select * from emp e where e.sal>(select b.sal from emp b where b.ename="blake")


 * mysql+mysqldb://root:***@localhost:3306/oracle_emp
4 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
7839 KING PRESIDENT None 1981-11-07 5000.00 None 10
7902 FORD ANALYST 7566 1981-12-03 3000.00 None 20

49. List the emps whose Jobs are same as ALLEN and donot display ALLEN details.

%%sql
select * from emp e where e.job=(select a.job from emp a where a.ename="Allen") and e.ename not like "Allen"

 * 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
7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30

50.List the emps who are senior to King.

%%sql
select *,(datediff(curdate(),hiredate)/365) as exp from emp e where (datediff(curdate(),hiredate)/365)>(select (datediff(curdate(),hiredate)/365) from emp k where k.ename like "King")

 * mysql+mysqldb://root:***@localhost:3306/oracle_emp
8 rows affected.
empno ename job mgr hiredate sal comm deptno exp
7369 SMITH CLERK 7902 1980-12-17 800.00 None 20 40.6192
7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 30 40.4411
7521 WARD SALESMAN 7698 1981-02-22 1250.00 500.00 30 40.4356
7566 JONES MANAGER 7839 1981-04-02 2975.00 None 20 40.3288
7654 MARTIN SALESMAN 7698 1981-09-28 1250.00 1400.00 30 39.8384
7698 BLAKE MANAGER 7839 1981-05-01 2850.00 None 30 40.2493
7782 CLARK MANAGER 7839 1981-06-09 2450.00 None 10 40.1425
7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30 39.8932

Related Posts