-
Notifications
You must be signed in to change notification settings - Fork 0
/
employee.sql
176 lines (122 loc) · 8.12 KB
/
employee.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
create table employee(EMPNO int primary key, ENAME varchar(25), JOB varchar(30), MGR int, HIREDATE date, SAL int, COMM int, DEPTNO int);
desc employee;
alter table employee modify ENAME varchar(25) not null;
insert into employee(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO) values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, 20);
insert into employee(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)
values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 30),
(7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 30),
(7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, 20),
(7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 30),
(7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, 30),
(7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, 10),
(7788, 'SCOTT', 'ANALYST', 7566, '1982-12-09', 3000, 20),
(7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, 10),
(7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 30),
(7876, 'ADAMS', 'CLERK', 7788, '1983-01-12', 1100, 20),
(7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, 30),
(7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, 20),
(7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, 10);
update employee set COMM=300 where EMPNO=7499;
update employee set COMM=500 where EMPNO=7521;
update employee set COMM=1400 where EMPNO=7654;
select * from employee;
-- 1. WAQTD details of employee who earns salary greater than 1250 and less than 2000.
select * from employee where SAL>1250 and SAL<2000;
-- 2. WAQTD details of employee where salary should be less than 1500 or should be working as team leader.
select * from employee where SAL<1500 or JOB='TEAMLEADER';
-- 3. WAQTD details of employee working as clerk and earning less than 1300.
select * from employee where JOB='CLERK' and SAL<1300;
-- 4. WAQTD name and hiredate of employee working as manager in dept 30.
select ENAME, HIREDATE from employee where JOB='MANAGER' and DEPTNO=30;
-- 5. WAQTD details of emp along with annual salary if they are working in dept 30 as salesman and their annual salary has to greater than 1500.
select * from employee where DEPTNO=30 and JOB='SALESMAN' and SAL>1500;
-- 6. WAQTD all the details of employee working in dept 30 or as supervisior.
select * from employee where DEPTNO=30 or JOB='SUPERVISOR';
-- 7. WAQTD name of the employee who's salary is less than 1100 and their designation is clerk.
select ENAME from employee where SAL<1100 and JOB='CLERK';
-- 8. WAQTD name and salary annual salary and dept no if dept no is 20 earning more than 2300 and annual salary exceeds 3000.
select ENAME, SAL, DEPTNO from employee where DEPTNO=20 and SAL>2300;
-- 9. WAQTD empno and name of the employee working as manager in dept 20.
select EMPNO, ENAME from employee where JOB='MANAGER' and DEPTNO=20;
-- 10. WAQTD details of employee working in dept 20 or 30.
select * from employee where DEPTNO in (20, 30);
-- 11. WAQTD details of employee working as engineer in dept 10.
select * from employee where JOB='ENGINEER' and DEPTNO=10;
-- 12. WAQTD details of employee working as doctor with salary 2980.
select * from employee where JOB='DOCTOR' and SAL=2980;
-- 13. WAQTD name and dept no of employee working in dept no 20 or 30.
select ENAME, DEPTNO from employee where DEPTNO in (20, 30);
-- 14. WAQTD details of employee who is not working as clerk or manager.
select * from employee where JOB not in('CLERK', 'MANAGER');
-- 15. WAQTD details of employee hiredate after 2020 and before 2021.
select * from employee where HIREDATE between '2020-01-01' and '2021-01-01';
-- select * from employee where HIREDATE between '1982-01-01' and '1983-01-01';
-- 16. WAQTD details of employee hiredate during the year 2021.
select * from employee where HIREDATE between '2021-01-01' and '2021-12-31';
-- select * from employee where HIREDATE between '1982-01-01' and '1982-12-31';
-- 17. WAQTD details of employee who are not hired during the year 2022.
select * from employee where HIREDATE not between '2022-01-01' and '2022-12-31';
-- 18. WAQTD emp names and commission of employee who does not earn commission.
select ENAME, COMM from employee where COMM is null;
-- 19. WAQTD details of employee who doesn't report to any manager.
select * from employee where MGR is null;
-- 20. WAQTD details of employee who earns commission.
select * from employee where COMM is not null;
-- 21. WAQTD name of the employee whose name starts with char 'A'.
select * from employee where ENAME like 'a%';
-- 22. WAQTD name of employee whose name ends with the char 'K'.
select * from employee where ENAME like '%k';
-- 23. WAQTD ename whose name has char 'A' in it.
select * from employee where ENAME like '%a%';
-- 24. WAQTD ename whose name has char 'A' as the 3rd letter.
select * from employee where ENAME like '__a%';
-- 25. WAQTD ename whose name has char 'R' as the 3rd letter and the total char's should be '6' in their name.
select * from employee where ENAME like '__r___';
-- 26. WAQTD name and dept no, job of employee working as clerk in dept no 10 or 20.
select ENAME, DEPTNO, JOB from employee where JOB='CLERK' and DEPTNO in (10, 20);
-- 27. WAQTD details of employee working as clerk or manger in dept10.
select * from employee where JOB in ('CLERK', 'MANAGER');
-- 28. WAQTD name of employee working in dept 10,20,30,40.
select ENAME from employee where DEPTNO in (10, 20, 30, 40);
-- 29. WAQTD details of employee with empno 7902,7839.
select * from employee where EMPNO in (7902, 7839);
-- 30. WAQTD details of employee working as manager or salesman or clerk.
select * from employee where JOB in ('MANAGER', 'SALESMAN', 'CLERK');
-- 31. WAQTD name of employee hired after 20 and before 22.
select ENAME from employee where HIREDATE between '2020-01-01' and '2022-12-31';
-- select ENAME from employee where HIREDATE between '1981-01-01' and '1982-12-31';
-- 32. WAQTD details of employee earning more than 1250 but less than 2000.
select * from employee where SAL between 1250 and 2000;
-- 33. WAQTD name of employee hired after 21 into dept 10 or 30.
select ENAME from employee where HIREDATE > '2021-12-31' and (DEPTNO=10 or DEPTNO=30);
-- select ENAME from employee where HIREDATE > '1981-12-31' and (DEPTNO=10 or DEPTNO=30);
-- 34. WAQTD name of employee along with annual salary for the employee working as manager or clerk and should earn commission.
select ENAME, SAL from employee where JOB in ('MANAGER', 'CLERK') and COMM is not null;
-- 35. WAQTD all the details along with A-salary if salary is between 1000 and 2500 annual salary more than 3000.
select * from employee where SAL between 1000 and 2500;
select * from employee;
-- 1. WAQTD number of employee getting salary less than 2000 in dept 10.
select * from employee where SAL<2000 and DEPTNO=10;
-- 2. WAQTD total salary needed to pay employee working as clerk.\
select sum(SAL) from employee where JOB='CLERK';
-- 3. WAQTD average salary needed to pay all employees.
select sum(SAL) from employee;
-- 4. WAQTD no of employee having 'A' as their first character.
select count(ENAME) from employee where ENAME like 'a%';
-- 5. WAQTD number of employees working as clerk or manager.
select count(ENAME) from employee where JOB in ('CLERK', 'MANAGER');
-- 6. WAQTD total salary need to pay employee hired in feb.
select sum(SAL) from employee where HIREDATE like "_____02___";
-- select sum(SAL) from employee where HIREDATE between '' and '';
-- 7. WAQTD number of employeea reporting to 7839(MGR).
select count(ENAME) from employee where MGR=7839;
-- 8. WAQTD number of employee getting commission in dept no 30.
select count(ENAME) from employee where DEPTNO=30 and COMM is not null;
-- 9. WAQTD avg salary,total salary, number of employees and maximum salary given to employees workings as manager.
select avg(SAL), sum(SAL), count(ENAME), max(SAL) from employee where JOB='MANAGER';
-- 10. WAQTD number of employee having 'A' in their names.
select count(ENAME) from employee where ENAME like '%a%';
-- 11. WAQTD no. of employee and total salary needed to pay the employee who have 2 consicutive 'L' in their names.
select count(ENAME), sum(SAL) from employee where ENAME like '%ll%';
-- 12. WAQTD number of departments present in empty table.
select count(distinct DEPTNO) from employee;