SQL/HW_3.sql at main · VysotskiySS/SQL · GitHub

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
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
-- 1. Вывести всех работников чьи зарплаты есть в базе, вместе с зарплатами.;
select employee_name, monthly_salary
from employees join employee_salary
on employees.id = employee_salary.employee_id
join salary on employee_salary.salary_id = salary.id;

 -- 2. Вывести всех работников у которых ЗП меньше 2000.;
select employee_name, monthly_salary
from employees join employee_salary
on employees.id = employee_salary.employee_id
join salary on employee_salary.salary_id = salary.id
where monthly_salary < '2000';

-- 3. Вывести все зарплатные позиции, но работник по ним не назначен. (ЗП есть, но не понятно кто её получает.);
select employee_name, monthly_salary
from employees left join employee_salary
on employees.id = employee_salary.employee_id
right join salary
on employee_salary.salary_id = salary.id
where employee_name is null;

-- 4. Вывести все зарплатные позиции  меньше 2000 но работник по ним не назначен. (ЗП есть, но не понятно кто её получает.);
select employee_name, monthly_salary
from employees left join employee_salary
on employees.id = employee_salary.employee_id
right join salary
on employee_salary.salary_id = salary.id
where employee_name is null and monthly_salary < 2000;

-- 5. Найти всех работников кому не начислена ЗП.;
select employee_name
from employees left join employee_salary
on employees.id = employee_salary.employee_id
left join salary
on employee_salary.salary_id = salary.id
where monthly_salary is null;

-- 6. Вывести всех работников с названиями их должности.;
select employee_name, role_name
from roles join roles_employee
on roles.id = roles_employee.role_id
join employees
on roles_employee.employee_id  = employees.id;

-- 7. Вывести имена и должность только Java разработчиков.;
select employee_name, monthly_salary
from roles join roles_employee
on roles.id = roles_employee.role_id
join employees
on roles_employee.employee_id  = employees.id
join employee_salary
on employees.id = employee_salary.employee_id
join salary on employee_salary.salary_id = salary.id
where role_name like '%Java%' and role_name not like '%Sctipt%';

-- 8. Вывести имена и должность только Python разработчиков.;
select employee_name, monthly_salary
from roles join roles_employee
on roles.id = roles_employee.role_id
join employees
on roles_employee.employee_id  = employees.id
join employee_salary
on employees.id = employee_salary.employee_id
join salary on employee_salary.salary_id = salary.id
where role_name like '%Python%';

-- 9. Вывести имена и должность всех QA инженеров.;
select employee_name, monthly_salary
from roles join roles_employee
on roles.id = roles_employee.role_id
join employees
on roles_employee.employee_id  = employees.id
join employee_salary
on employees.id = employee_salary.employee_id
join salary on employee_salary.salary_id = salary.id
where role_name like '%QA engineer%';

-- 10. Вывести имена и должность ручных QA инженеров.;
select employee_name, monthly_salary
from roles join roles_employee
on roles.id = roles_employee.role_id
join employees
on roles_employee.employee_id  = employees.id
join employee_salary
on employees.id = employee_salary.employee_id
join salary on employee_salary.salary_id = salary.id
where role_name like '%Manual QA engineer%';

-- 11. Вывести имена и должность автоматизаторов QA;
select employee_name, monthly_salary
from roles join roles_employee
on roles.id = roles_employee.role_id
join employees
on roles_employee.employee_id  = employees.id
join employee_salary
on employees.id = employee_salary.employee_id
join salary on employee_salary.salary_id = salary.id
where role_name like '%Automation QA engineer%';

-- 12. Вывести имена и зарплаты Junior специалистов;
select employee_name, monthly_salary
from roles join roles_employee
on roles.id = roles_employee.role_id
join employees
on roles_employee.employee_id  = employees.id
join employee_salary
on employees.id = employee_salary.employee_id
join salary on employee_salary.salary_id = salary.id
where role_name like '%Junior%';

-- 13. Вывести имена и зарплаты Middle специалистов;
select employee_name, monthly_salary
from roles join roles_employee
on roles.id = roles_employee.role_id
join employees
on roles_employee.employee_id  = employees.id
join employee_salary
on employees.id = employee_salary.employee_id
join salary on employee_salary.salary_id = salary.id
where role_name like '%Middle%';

-- 14. Вывести имена и зарплаты Senior специалистов;
select employee_name, monthly_salary
from roles join roles_employee
on roles.id = roles_employee.role_id
join employees
on roles_employee.employee_id  = employees.id
join employee_salary
on employees.id = employee_salary.employee_id
join salary on employee_salary.salary_id = salary.id
where role_name like '%Senior%';

-- 15. Вывести зарплаты Java разработчиков;
select monthly_salary
from roles join roles_employee
on roles.id = roles_employee.role_id
join employees
on roles_employee.employee_id  = employees.id
join employee_salary
on employees.id = employee_salary.employee_id
join salary on employee_salary.salary_id = salary.id
where role_name like '%Java%' and role_name not like '%Sctipt%';

-- 16. Вывести зарплаты Python разработчиков;
select monthly_salary
from roles join roles_employee
on roles.id = roles_employee.role_id
join employees
on roles_employee.employee_id  = employees.id
join employee_salary
on employees.id = employee_salary.employee_id
join salary on employee_salary.salary_id = salary.id
where role_name like '%Python%';

-- 17. Вывести имена и зарплаты Junior Python разработчиков;
select employee_name, monthly_salary
from roles join roles_employee
on roles.id = roles_employee.role_id
join employees
on roles_employee.employee_id  = employees.id
join employee_salary
on employees.id = employee_salary.employee_id
join salary on employee_salary.salary_id = salary.id
where role_name like '%Junior%' and role_name like '%Python%';

-- 18. Вывести имена и зарплаты Middle JS разработчиков;
select employee_name, monthly_salary
from roles join roles_employee
on roles.id = roles_employee.role_id
join employees
on roles_employee.employee_id  = employees.id
join employee_salary
on employees.id = employee_salary.employee_id
join salary on employee_salary.salary_id = salary.id
where role_name like '%Middle%' and role_name like '%JavaScript%';

-- 19. Вывести имена и зарплаты Senior Java разработчиков;
select employee_name, monthly_salary
from roles join roles_employee
on roles.id = roles_employee.role_id
join employees
on roles_employee.employee_id  = employees.id
join employee_salary
on employees.id = employee_salary.employee_id
join salary on employee_salary.salary_id = salary.id
where role_name like '%Senior%' and role_name like '%Java developer';

-- 20. Вывести зарплаты Junior QA инженеров;
select monthly_salary
from roles join roles_employee
on roles.id = roles_employee.role_id
join employees
on roles_employee.employee_id  = employees.id
join employee_salary
on employees.id = employee_salary.employee_id
join salary on employee_salary.salary_id = salary.id
where role_name like '%Junior%' or role_name like '%junior%';

-- 21. Вывести среднюю зарплату всех Junior специалистов;
select avg(monthly_salary)
from roles join roles_employee
on roles.id = roles_employee.role_id
join employees
on roles_employee.employee_id  = employees.id
join employee_salary
on employees.id = employee_salary.employee_id
join salary on employee_salary.salary_id = salary.id
where role_name like '%Junior%' or role_name like '%junior%';

-- 22. Вывести сумму зарплат JS разработчиков;
select sum(monthly_salary)
from roles join roles_employee
on roles.id = roles_employee.role_id
join employees
on roles_employee.employee_id  = employees.id
join employee_salary
on employees.id = employee_salary.employee_id
join salary on employee_salary.salary_id = salary.id
where role_name like '%JavaScript%';

-- 23. Вывести минимальную ЗП QA инженеров;
select min(monthly_salary)
from roles join roles_employee
on roles.id = roles_employee.role_id
join employees
on roles_employee.employee_id  = employees.id
join employee_salary
on employees.id = employee_salary.employee_id
join salary on employee_salary.salary_id = salary.id
where role_name like '%QA%' and role_name like '%engineer%' or role_name like '%Engineer%';

-- 24. Вывести максимальную ЗП QA инженеров;
select max(monthly_salary)
from roles join roles_employee
on roles.id = roles_employee.role_id
join employees
on roles_employee.employee_id  = employees.id
join employee_salary
on employees.id = employee_salary.employee_id
join salary on employee_salary.salary_id = salary.id
where role_name like '%QA%' and role_name like '%engineer%' or role_name like '%Engineer%';

-- 25. Вывести количество QA инженеров;
select count(employee_name)
from roles join roles_employee
on roles.id = roles_employee.role_id
join employees
on roles_employee.employee_id  = employees.id
where role_name like '%QA%' and role_name like '%engineer%' or role_name like '%Engineer%';

-- 26. Вывести количество Middle специалистов.;
select count(employee_name)
from roles join roles_employee
on roles.id = roles_employee.role_id
join employees
on roles_employee.employee_id  = employees.id
where role_name like '%Middle%' or role_name like '%middle%';

-- 27. Вывести количество разработчиков;
select count(employee_name)
from roles join roles_employee
on roles.id = roles_employee.role_id
join employees
on roles_employee.employee_id  = employees.id
where role_name like '%Developer%' or role_name like '%developer%';;

-- 28. Вывести фонд (сумму) зарплаты разработчиков.;
select sum(monthly_salary)
from roles join roles_employee
on roles.id = roles_employee.role_id
join employees
on roles_employee.employee_id  = employees.id
join employee_salary
on employees.id = employee_salary.employee_id
join salary on employee_salary.salary_id = salary.id
where role_name like '%developer%';

-- 29. Вывести имена, должности и ЗП всех специалистов по возрастанию;
select employee_name, role_name, monthly_salary
from roles join roles_employee
on roles.id = roles_employee.role_id
join employees
on roles_employee.employee_id  = employees.id
join employee_salary
on employees.id = employee_salary.employee_id
join salary on employee_salary.salary_id = salary.id
order by monthly_salary;

-- 30. Вывести имена, должности и ЗП всех специалистов по возрастанию у специалистов у которых ЗП от 1700 до 2300;
select employee_name, role_name, monthly_salary
from roles join roles_employee
on roles.id = roles_employee.role_id
join employees
on roles_employee.employee_id  = employees.id
join employee_salary
on employees.id = employee_salary.employee_id
join salary on employee_salary.salary_id = salary.id
where monthly_salary between 1700 and 2300
order by monthly_salary;

-- 31. Вывести имена, должности и ЗП всех специалистов по возрастанию у специалистов у которых ЗП меньше 2300;
select employee_name, role_name, monthly_salary
from roles join roles_employee
on roles.id = roles_employee.role_id
join employees
on roles_employee.employee_id  = employees.id
join employee_salary
on employees.id = employee_salary.employee_id
join salary on employee_salary.salary_id = salary.id
where monthly_salary < 2300
order by monthly_salary;

-- 32. Вывести имена, должности и ЗП всех специалистов по возрастанию у специалистов у которых ЗП равна 1100, 1500, 2000;
select employee_name, role_name, monthly_salary
from roles join roles_employee
on roles.id = roles_employee.role_id
join employees
on roles_employee.employee_id  = employees.id
join employee_salary
on employees.id = employee_salary.employee_id
join salary on employee_salary.salary_id = salary.id
where monthly_salary in (1100, 1500, 2000)
order by monthly_salary;