--2.1

/*
1. Выполнить запрос, который получает:
- фамилии сотрудников
- E-mail адреса сотрудников в полном формате: значение атрибута E-mail +
"@Netcracker.com"
*/

SELECT last_name, email || '@Netcracker.com' FROM employees;

/*
LAST_NAME                 EMAIL||'@NETCRACKER.COM'
------------------------- ------------------------
King                      SKING@Netcracker.com
Kochhar                   NKOCHHAR@Netcracker.com
De Haan                   LDEHAAN@Netcracker.com
Hunold                    AHUNOLD@Netcracker.com
Ernst                     BERNST@Netcracker.com
Austin                    DAUSTIN@Netcracker.com
Pataballa                 VPATABAL@Netcracker.com
Lorentz                   DLORENTZ@Netcracker.com
*/

/*
2.1. Построить логический план запроса, который получает:
- фамилию сотрудника;
- E-mail сотрудника;
- зарплату сотрудника за последние три месяца с учетом комиссионных.
*/
select last_name, email, (salary + nvl(commission_pct,0))*3 as salary from employees;
/*
LAST_NAME                 EMAIL                         SALARY
------------------------- ------------------------- ----------
King                      SKING                          72000
Kochhar                   NKOCHHAR                       51000
De Haan                   LDEHAAN                        51000
Hunold                    AHUNOLD                        27000
Ernst                     BERNST                         18000
Austin                    DAUSTIN                        14400
Pataballa                 VPATABAL                       14400
Lorentz                   DLORENTZ                       12600
Greenberg                 NGREENBE                       36000
Faviet                    DFAVIET                        27000
Chen                      JCHEN                          24600
*/

/*
2.2. Построить логический план запроса, который получает:
- название подразделения;
- подразделением управляет менеджер с идентификатором = 114
*/
select department_name from departments where manager_id=114;
/*
DEPARTMENT_NAME
------------------------------
Purchasing
*/

/*
2.3. Построить логический план запроса, который получает:
- фамилию сотрудников;
- выплаченную зарплату сотрудников за весь период работы (для расчета месяцев работы
использовать функцию округления дробных чисел в меньшую сторону - round);
- выплаченная зарплата сотрудников за весь период работы должна быть в диапазоне от
50000$ до 100000$.
*/
select last_name, (salary + nvl(commission_pct,0))*round((current_date-hire_date)/30) as salary from employees
where (salary + nvl(commission_pct,0))*round((current_date-hire_date)/30) BETWEEN 50000 and 100000;
/*
no rows selected
*/

/*
2.4. Построить логический план запроса, который получает:
- фамилию сотрудников;
- сотрудники работают в подразделении с идентификатором = 90;
- зарплата находится в диапазоне от 5000$ до 10000$
*/
select last_name from employees where department_id=90 and salary BETWEEN 5000 and 10000;
/*
no rows selected
*/

--2.2

/*
1. Выполнить запрос, который получает для каждого сотрудника cтроку в формате
'Dear '+A+ ' ' + B + ’! ' + ‘ Your salary = ‘ + C,
где A = {‘Mr.’,’Mrs.’} – сокращенный вариант обращения к мужчине или женщине
(предположить, что женщиной являются все сотрудницы, имя которых заканчивается на букву
‘a’ или ‘e’)
B – фамилия сотрудника;
C – годовая зарплата с учетом комиссионных сотрудника
*/

SELECT ('Dear Mrs.' || ' ' || last_name || '!') as name, ('Your salary=' || (salary + nvl(commission_pct,0))*12) as salary FROM employees where first_name LIKE '%a' OR first_name LIKE '%e'
UNION
SELECT ('Dear Mr.' || ' ' || last_name || '!') as name, ('Your salary=' || (salary + nvl(commission_pct,0))*12) as salary FROM employees where first_name NOT LIKE '%a' OR first_name NOT LIKE '%e';

/*
NAME                                 SALARY
------------------------------------ ----------------------------------------------------
Dear Mr. Abel!                       Your salary=132003,6
Dear Mr. Ande!                       Your salary=76801,2
Dear Mr. Atkinson!                   Your salary=33600
Dear Mr. Austin!                     Your salary=57600
Dear Mr. Baer!                       Your salary=120000
Dear Mr. Baida!                      Your salary=34800
Dear Mr. Banda!                      Your salary=74401,2
Dear Mr. Bates!                      Your salary=87601,8
Dear Mr. Bell!                       Your salary=48000
Dear Mr. Bernstein!                  Your salary=114003
Dear Mr. Bissot!                     Your salary=39600
*/


--2.3

/*
1. Построить логический план запроса, который получает:
- фамилию сотрудников;
- названия подразделения, в котором работает сотрудник;
- название города, в котором работает сотрудник.
*/
select last_name, department_name, city from employees inner join
departments on employees.department_id = departments.department_id inner join
locations on departments.location_id = locations.location_id;
/*
LAST_NAME                 DEPARTMENT_NAME                CITY
------------------------- ------------------------------ ------------------------------
King                      Executive                      Seattle
Kochhar                   Executive                      Seattle
De Haan                   Executive                      Seattle
Hunold                    IT                             Southlake
Ernst                     IT                             Southlake
Austin                    IT                             Southlake
Pataballa                 IT                             Southlake
Lorentz                   IT                             Southlake
Greenberg                 Finance                        Seattle
Faviet                    Finance                        Seattle
Chen                      Finance                        Seattle
*/

/*
2. Построить логический план запроса, который получает:
- фамилию сотрудников,
- названия подразделений;
- подразделения расположены в городе = Seattle.
*/
select last_name, department_name from employees inner join
departments on employees.department_id = departments.department_id inner join
locations on departments.location_id = locations.location_id
where city = 'Seattle';
/*
LAST_NAME                 DEPARTMENT_NAME
------------------------- ------------------------------
Whalen                    Administration
Colmenares                Purchasing
Himuro                    Purchasing
Tobias                    Purchasing
Baida                     Purchasing
Khoo                      Purchasing
Raphaely                  Purchasing
De Haan                   Executive
Kochhar                   Executive
King                      Executive
Popp                      Finance
*/

/*
3. Построить логический план запроса, который получает:
- фамилию сотрудников;
- название должности сотрудников;
- сотрудники работают в городе = Toronto.
*/
select last_name, job_title from employees inner join
jobs on employees.job_id = jobs.job_id inner join
departments on employees.department_id = departments.department_id inner join
locations on departments.location_id = locations.location_id
where city = 'Toronto';
/*
LAST_NAME                 JOB_TITLE
------------------------- -----------------------------------
Hartstein                 Marketing Manager
Fay                       Marketing Representative
*/

/*
4. Построить логический план запроса, который:
- получает фамилию, название должности сотрудников;
- зарплата сотрудника должна попадать в диапазон минимальной и максимальной зарплат,
установленных в справочнике должностей.
*/
select last_name, job_title from employees inner join
jobs on employees.job_id = jobs.job_id
where salary between min_salary and max_salary;
/*
LAST_NAME                 JOB_TITLE
------------------------- -----------------------------------
Hartstein                 Marketing Manager
Fay                       Marketing Representative
King                      President
Kochhar                   Administration Vice President
De Haan                   Administration Vice President
Hunold                    Programmer
Ernst                     Programmer
Austin                    Programmer
*/

/*
5. Построить логический план запроса, который получает номер и фамилию сотрудника,
номер и фамилию его менеджера.
*/
Select a.employee_id, a.last_name, a.manager_id, b.last_name
from Employees A
inner join Employees B
on a.manager_id = b.employee_id
/*
EMPLOYEE_ID LAST_NAME                 MANAGER_ID LAST_NAME
----------- ------------------------- ---------- -------------------------
        201 Hartstein                        100 King
        149 Zlotkey                          100 King
        148 Cambrault                        100 King
        147 Errazuriz                        100 King
        146 Partners                         100 King
        145 Russell                          100 King
        124 Mourgos                          100 King
        123 Vollman                          100 King
        122 Kaufling                         100 King
        121 Fripp                            100 King
        120 Weiss                            100 King
*/

/*
6. Построить логический план запроса, который:
- получает номер и название подразделений;
- подразделения расположены в стране = Denmark;
- в подразделениях не должно быть сотрудников.
*/
select departments.department_id, department_name from departments inner join
locations on departments.location_id = locations.location_id inner join
countries on countries.country_id = locations.country_id inner join
employees on employees.department_id = departments.department_id
where countries.country_name='Denmark'
GROUP BY department_name, departments.department_id
HAVING COUNT(employees.employee_id)=0;
/*
no rows selected
*/

/*
7. Построить логический план запроса, который:
- получает названия стран;
- страны расположены в регионе = Europe;
- в указанных странах не должно быть сотрудников.
*/
select countries.country_name from countries inner join
regions on regions.region_id = countries.region_id inner join
locations on locations.country_id = countries.country_id inner join
departments on departments.location_id = locations.location_id inner join
employees on employees.department_id = departments.department_id
where regions.region_name = 'Europe'
group by countries.country_name
HAVING COUNT(employees.employee_id) = 0;
/*
no rows selected
*/


--2.4

/*
1. Выполнить запрос, который получает:
- кол-во сотрудников в каждом подразделении;
- кол-во сотрудников не должно быть меньше 2;
*/
SELECT COUNT(*) FROM employees GROUP BY department_id having COUNT(department_id) > 2;
/*
  COUNT(*)
----------
         6
         6
         3
        45
        34
         5
*/

/*
2. Выполнить запрос, который получает:
- названия должностей;
- среднюю зарплату по должности;
- должность должна быть связана с управлением, т.е. содержать слово Manager;
- средняя зарплата не должна быть менее 10 тысяч.
*/
select job_title, avg((max_salary+min_salary)/2) from jobs where job_title like '%Manager%' group by job_title HAVING avg((max_salary+min_salary)/2) > 10000;
/*
JOB_TITLE                           AVG((MAX_SALARY+MIN_SALARY)/2)
----------------------------------- ------------------------------
Accounting Manager                                           12100
Finance Manager                                              12100
Purchasing Manager                                           11500
Sales Manager                                                15000
Marketing Manager                                            12000
*/

/*
3. Выполнить запрос, который получает:
- кол-во сотрудников в каждом подразделении;
- последней строкой ответа на запрос должно быть общее кол-во сотрудников.
*/
SELECT COUNT(*) FROM employees GROUP BY department_id
UNION all
SELECT COUNT(*) FROM employees
/*
  COUNT(*)
----------
         6
         6
         1
         3
         2
         1
         2
        45
        34
         1
         5
         1
       107
*/