Trail Blazed

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

© 2022. All rights reserved.

Oracle Emp Table

12 Aug 2021

Employee Database Queries 51-100

23 Jul 2021

SQLAlchemy 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

Create and Insert Employee Database

23 Jul 2021 
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"

%%sql
use oracle_emp;
create table dept(
  deptno int2,
  dname  varchar(14),
  loc    varchar(13),
  constraint pk_dept primary key (deptno)
);
 
create table emp(
  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),
  constraint fk_deptno foreign key (deptno) references dept (deptno)
);
 

create table bonus(
  ename varchar(10),
  job   varchar(9),
  sal   int4,
  comm  int4
);
 
create table salgrade(
  grade integer,
  losal integer,
  hisal integer
);




INSERT INTO EMP VALUES
        (7369, 'SMITH',  'CLERK',     7902,
        TO_DATE('17-DEC-1980', 'DD-MON-YYYY'),  800, NULL, 20);
INSERT INTO EMP VALUES
        (7499, 'ALLEN',  'SALESMAN',  7698,
        TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600,  300, 30);
INSERT INTO EMP VALUES
        (7521, 'WARD',   'SALESMAN',  7698,
        TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250,  500, 30);
INSERT INTO EMP VALUES
        (7566, 'JONES',  'MANAGER',   7839,
        TO_DATE('2-APR-1981', 'DD-MON-YYYY'),  2975, NULL, 20);
INSERT INTO EMP VALUES
        (7654, 'MARTIN', 'SALESMAN',  7698,
        TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMP VALUES
        (7698, 'BLAKE',  'MANAGER',   7839,
        TO_DATE('1-MAY-1981', 'DD-MON-YYYY'),  2850, NULL, 30);
INSERT INTO EMP VALUES
        (7782, 'CLARK',  'MANAGER',   7839,
        TO_DATE('9-JUN-1981', 'DD-MON-YYYY'),  2450, NULL, 10);
INSERT INTO EMP VALUES
        (7788, 'SCOTT',  'ANALYST',   7566,
        TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES
        (7839, 'KING',   'PRESIDENT', NULL,
        TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES
        (7844, 'TURNER', 'SALESMAN',  7698,
        TO_DATE('8-SEP-1981', 'DD-MON-YYYY'),  1500,    0, 30);
INSERT INTO EMP VALUES
        (7876, 'ADAMS',  'CLERK',     7788,
        TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES
        (7900, 'JAMES',  'CLERK',     7698,
        TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),   950, NULL, 30);
INSERT INTO EMP VALUES
        (7902, 'FORD',   'ANALYST',   7566,
        TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),  3000, NULL, 20);
INSERT INTO EMP VALUES
        (7934, 'MILLER', 'CLERK',     7782,
        TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);


INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH',   'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES',      'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');



INSERT INTO SALGRADE VALUES (1,  700, 1200);
INSERT INTO SALGRADE VALUES (2, 1201, 1400);
INSERT INTO SALGRADE VALUES (3, 1401, 2000);
INSERT INTO SALGRADE VALUES (4, 2001, 3000);
INSERT INTO SALGRADE VALUES (5, 3001, 9999);
commit;

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

Azure DP-900 Course 4

20 Jul 2021

Modern Data Warehouse Analytics in Microsoft Azure

Module 1

  • Data Ingestion, Storage, and Processing in Microsoft Azure

  • Explore the Components of a Modern Data Warehouse and Data Ingestion.

  • Describe modern data warehousing and get some practice on creating a data warehouse.
  • Explore Azure data ingestion components

  • Azure data analytical components for modern data warehousing.

  • Data ingestion in Microsoft Azure.
  • Explore ingesting data using Azure Data Factory
  • Describe common practices for data ingestion.
  • Load data into Azure Synapse Analytics.
  • Explore data storage and processing in Microsoft Azure
  • Azure Synapse Analytics Pools
  • Azure Synapse Analytics Components.

Module 2

  • Start building with Power BI
  • Explore its building blocks
  • Use the Power BI service.