<style>
.markdown-body .alert {color: #070707!important;} body { overflow-x: hidden; color: #312928; background: #f5f3ed url(https://i.imgur.com/B8wy02N.png) top left repeat } :root { --color-primary: #004d40; --color-primary-dark: #1c1c1e; --color-primary-darker: #111; --color-primary-light: #39796b; --color-secondary: #00838f; --color-secondary-dark: #005662; --color-secondary-light: #4fb3bf; --color-accent: #9d76ff; --border-color: #080808; --text-color-light: #8effff; --text-color-bright: #fff; --text-color-dark: #333 } #doc { display: block; width: 100%; margin: 31px auto 31px; background: #f5f3ed url(https://i.imgur.com/S2fW9vg.jpg) top left repeat; border-image-width: 100px; border: 32px solid; border-image: url(https://i.imgur.com/XzF7kg2.png) 38 round round } .tool-bar>a.fa { color: var(--color-primary-light) !important; border: none } .tool-bar>a.fa:hover { color: var(--text-color-bright) !important } .ui-infobar__user-info>ul>li>div>.flex>span { color: var(--text-color-light) !important } .markdown-body h1, .markdown-body h2, .markdown-body h3, .markdown-body h4, .markdown-body h5, .markdown-body h6 { font-weight: 500; text-align: left; border: none; text-align: center; font-size: 80px; color: #424242; font-family: cursive } .markdown-body h1, .markdown-body h2, .markdown-body h3, .markdown-body h4 { padding-top: 35px; text-align: center }} .markdown-body h1 { font-size: 3.7em; text-align: center } .markdown-body h2 { font-size: 1.5em } .markdown-body h3 { font-size: 1.4em } .markdown-body h4 { font-size: 1.3em } .markdown-body h5 { font-size: 1.2em } .markdown-body h6 { font-size: .8em } .markdown-body hr { background-color: var(--color-secondary); margin: 1.25em 0; height: 1.1px } .markdown-body blockquote, .markdown-body blockquote blockquote { border-left-color: var(--color-secondary); color: #999; padding: 0 !important; padding-right: 5em !important } .markdown-body blockquote:before { color: var(--color-secondary) } .markdown-body blockquote small, .markdown-body blockquote i.fa { color: var(--color-accent); opacity: .8 } .markdown-body table { display: table; padding: 1em; margin-left: 1em; width: calc(100% - 2em) } .markdown-body table th, .markdown-body table td, .markdown-body table tr { border: none !important } .markdown-body table tr { background-color: transparent !important; border-bottom: 1px solid rgba(255, 255, 255, .3) !important } .markdown-body .sequence-diagram rect[stroke=none] { fill: #000; opacity: .2; filter: invert(1) } .markdown-body .graphviz polygon { fill: transparent } .markdown-body .vega svg { background-color: transparent !important } .markdown-body .fretboard .svg_wrapper { background-color: transparent } .markdown-body .alert { color: #fff; position: relative; z-index: 1; padding: 0; border: none; font-weight: 500 } .markdown-body p { padding: 0 1em } .markdown-body .alert-success:before, .markdown-body .alert-info:before, .markdown-body .alert-warning:before, .markdown-body .alert-danger:before { content: ' '; position: absolute; z-index: -1; width: 100%; height: 100% } h1:after, h2:after, h3:after, h4:after, h3.md-focus:before, h4.md-focus:before, h3.md-focus:after, h4.md-focus:after { content: ''; display: block; background-repeat: no-repeat; background-position: top center; background-size: auto 80%; width: 100%; z-index: 1; margin: 0; padding: 20px 30px; border: 0 } h1:after, h2:after, h3:after, h4:after { background-image: url("https://lh3.googleusercontent.com/wJsGW_MA3V4aC2clzv0Z0OAVWwdXE11CFoA2LjG-1AavRukDFaCBQU5HqJKe9oi57buAMrsCEkd9XJeIBfGjP4h7HzOKhIjANL94EnNrD8GADHG7I-3GFLdHVq_bF1DJSYCPj9tu5CPzv4Lb4an0Ef93_CRbZ--6QyqFrNWzHz5XL-iRrzbrrA") } table { width: 550px; margin: 24px auto; border-collapse: separate; border-spacing: 4px } tr:nth-child(odd) { background-color: #f0f8ff } tr:nth-child(even) { background-color: #ffb6c1 } td { vertical-align: top; padding: 8px 16px } td:first-child { text-align: center } td:not(:first-child) { text-align: left } #doc pre { background-color: #f4f3ee !important; border-image-width: 100px !important; border: 32px solid !important; border-image: url(https://i.imgur.com/XzF7kg2.png) 38 round round !important } .ui-infobar { margin: 0; max-width: none; width: 100%; height: 80px; display: flex; justify-content: flex-end; align-items: center; box-shadow: 0 1px 10px rgb(0 0 0/20%), 0 1px 1px rgb(0 0 0/20%); box-sizing: border-box; margin-bottom: 100px; position: relative; background: #f5f3ed url(https://i.imgur.com/D3JHgvv.png) top left repeat } .ui-infobar:after { content: ''; position: absolute; z-index: -1; bottom: -300px; display: block; width: 100%; height: 300px; background: url(https://i.imgur.com/kZDzETE.png) no-repeat, url(https://i.imgur.com/visfeOY.png) repeat-x }
#doc, .toc { z-index: 100; } .container-fluid { padding-right: 0; padding-left: 15px; margin-right: auto; margin-left: auto } #ui-toc-affix { z-index: 1000; font-size: 1.2em } .ui-comment-app { right: 100px !important; top: 100px } .ui-toc-dropdown .nav>li>a { font-size: 25px } .ui-comment-container { margin-left: 55px !important } .ribbon-container { background: 0 0 !important; position: relative; z-index: 1; top: -30px } .ribbon { background: #f6a655 url(http://portfolio.queenofwhisks.com/demo/overlay/noise.png); box-shadow: 2px 6px 12px rgba(0, 0, 0, .5); font-family: Charter, serif; font-size: 16px; line-height: 1; text-align: center; margin: 0 auto; padding: .25em 0; position: relative; width: 86%; top: 80px } .ribbon:before, .ribbon:after { border: 1.5em solid #ee9e4f; content: ''; display: block; position: absolute; bottom: -1em; z-index: -1 } .ribbon:before { left: -2em; border-right-width: 1.5em; border-left-color: transparent; box-shadow: 2px 2px 2px rgba(0, 0, 0, .5) } .ribbon:after { right: -2em; border-left-width: 1.5em; border-right-color: transparent; box-shadow: -2px 2px 2px rgba(0, 0, 0, .5) } .ribbon-content { border-top: .0625em dashed #d5700b; border-bottom: .0625em dashed #d5700b; box-shadow: inset 0px 1px 1px rgba(0, 0, 0, .08), inset 0px -1px 1px rgba(255, 255, 255, .08), 0px -1px 1px rgba(255, 255, 255, .2), 0px 1px 1px rgba(0, 0, 0, .08); display: block; padding: .6875em 0 } .ribbon-content:before, .ribbon-content:after { content: ''; display: block; position: absolute; border-style: solid; border-color: #b86614 transparent transparent; bottom: -1em } .ribbon-content:before { left: 0; border-width: 1em 0 0 1em } .ribbon-content:after { right: 0; border-width: 1em 1em 0 0 } .ribbon a { color: #c2660a; text-decoration: none; text-shadow: 1px 1px 1px rgba(255, 255, 255, .25) }
body { counter-reset: h1 } h1 { counter-reset: h2 } h2 { counter-reset: h3 } h3 { counter-reset: h4 } h2:before { counter-increment: h2; content: counter(h2) ". " } h3:before { counter-increment: h3; content: counter(h2) "." counter(h3) ". " } h4:before { counter-increment: h4; content: counter(h2) "." counter(h3) "." counter(h4) ". " }
</style>

<style type="text/css">
#wrap {
width:600px;
margin:0 auto;
}
.wrap {
display: -ms-flex;
display: -webkit-flex;
display: flex;
width: 100%;
}

.left_col {
min-width: 75px;
flex-grow: 0;
}

.right_col {
flex-grow: 1;
}
</style>

:::danger
# Переделать

[https://mukeshonplsql.blogspot.com/2015/10/hr-schema-queries.html](https://mukeshonplsql.blogspot.com/2015/10/hr-schema-queries.html)

## Задание

Найти коды и фамилии сотрудников, суммарный стаж работы которых в компании не менее 4 лет.

SELECT emp.empno, emp.empname FROM emp
JOIN career c ON emp.empno = c.empno
GROUP BY emp.empno, emp.empname
HAVING sum(MONTHS_BETWEEN(NVL(c.enddate, current_date), c.startdate)) >= 48;

## Задание

Найти всех сотрудников (коды и фамилии), ни разу не увольнявшихся из компании.

SELECT DISTINCT emp.empno, emp.empname FROM emp
JOIN career c ON c.empno = emp.empno
WHERE (SELECT count(*) FROM career
    WHERE c.empno = emp.empno AND enddate IS NOT NULL) = 0;

## Задание

Найти фамилии, коды должностей, названия должностей и периоды работы (даты приема и даты увольнения) для бухгалтеров (Accountant) и исполнительных директоров (Executive Director), работавших или работающих в компании. Для работающих дата увольнения для периода неопределена и при выводе либо отсутсвует, либо определяется как Null.

SELECT e.empname, j.jobno, j.jobname, career.startdate, career.enddate FROM career
JOIN emp e ON e.empno = career.empno
JOIN job j ON j.jobno = career.jobno
WHERE j.jobname in ('Accountant', 'Executive Director');

## Задание

Найти номера и названия отделов, в которых в период с 01.01.2015 по 31.12.2015 работало не более 7 сотрудников.

SELECT DISTINCT dept.deptid, dept.deptname FROM dept
JOIN career c ON c.deptid = dept.deptid
WHERE (SELECT count(*) FROM career
    WHERE (career.deptid = dept.deptid 
        AND career.startdate <= to_date('31.12.2015','dd-mm-yyyy') AND to_date('01.01.2015','dd-mm-yyyy') <= career.enddate)) <= 7;

## Задание

Найти фамилии работников, никогда не работавших ни в исследовательском (Research) отделе, ни в отделе поддержки (Support).

SELECT DISTINCT(emp.empname) FROM emp
JOIN career c ON c.empno = emp.empno
JOIN dept d ON d.deptid = c.deptid
WHERE (SELECT count(*) FROM career
    WHERE c.empno = emp.empno AND d.deptname IN ('Research', 'Support')) = 0;

## Задание

Найти количество сотрудников, работавших в двух и более отделах. Если сотрудник работает в настоящее время, то отдел также учитывается.

SELECT count(DISTINCT(emp.empno)) FROM emp
JOIN career c ON c.empno = emp.empno
WHERE (SELECT count(DISTINCT deptid) FROM career
    WHERE career.empno = emp.empno) >= 2;

## Задание

Найти коды и фамилии сотрудников, работавших только на одной должности. Если сотрудник работает в настоящее время, то должность также учитывается.

SELECT DISTINCT emp.empno, emp.empname FROM emp
JOIN career c ON c.empno = emp.empno
WHERE (SELECT count(DISTINCT jobno) FROM career
    WHERE career.empno = emp.empno) >= 2;

## Задание

Выполните выборку табельных номеров сотрудников, чьи отделы расположены в Сиэтле (Seattle).

SELECT e.emp_no
FROM employee e, department d
WHERE e.dept_no = d.dept_no AND d.location = 'Seattle';

SELECT emp_no
FROM employee
WHERE dept_no = (
	SELECT dept_no
	FROM department
	WHERE location = 'Seattle');

## Задание

Выполните выборку должностей, занимаемых больше, чем двумя сотрудниками.

SELECT job
FROM works_on
WHERE job IS NOT NULL
GROUP BY job
HAVING COUNT(*) > 2

</br>
:::

</br>

# Легкие задачи

## Задание

Вывести список сотрудников, получающих заработную плату большую чем у непосредственного руководителя

SELECT  *
FROM Employee AS employees, Employee AS chieves
WHERE chieves.id = employees.chief_id
AND employees.salary > chieves.salary;

## Задание

Вывести список сотрудников, получающих максимальную заработную плату в своем отделе

SELECT  *
FROM Employee AS employees
WHERE employees.salary = (
SELECT  MAX(salary)
FROM Employee AS max
WHERE max.department_id = employees.department_id);

## Задание

Вывести список ID отделов, количество сотрудников в которых не превышает 3 человек

SELECT  department_id
FROM Employee
GROUP BY  department_id
HAVING COUNT(*) <= 3;

## Задание

Вывести список сотрудников, не имеющих назначенного руководителя, работающего в том-же отделе

SELECT  *
FROM Employee AS employees
LEFT JOIN Employee AS chieves
ON (employees.chief_id = chieves.Id AND employees.department_id = chieves.department_id)
WHERE chieves.id IS NULL;

## Задание

Вывести список служащих, фактические объемы продаж которых не попадают в диапазон от 80 до 120 процентов плана.

SELECT  NAME
       ,SALES
       ,QUOTA
FROM salesreps
WHERE SALES not BETWEEN 0.8 * QUOTA AND 1.2 * QUOTA; 

## Задание

Сформируйте поле, которое указывает последние 4 цифры номера телефона сотрудника. Уберите в телефоне символ «.». Определите работников с самым длинным email адресом. Сформируйте поле с именем и фамилией сотрудника

**Решение:**

1) Сформируйте поле, которое указывает последние 4 цифры номера телефона сотрудника

SELECT  substr(PHONE_NUMBER,length(PHONE_NUMBER)-3 )
FROM hr.employees;

2) Уберите в телефоне символ «.»

SELECT REPLACE(phone_number, '.', '') from hr.employees;
-- в случае если подстрока меняется на пустую строку, второй параметр не обязателен
SELECT REPLACE(phone_number, '.') from hr.employees; 

3) Определите работников с самым длинным email адресом

SELECT  *
FROM hr.employees
WHERE length(email) = (
    SELECT  MAX(length(email))
    FROM hr.employees; 
)

4) Сформируйте поле с именем и фамилией сотрудника

SELECT  FIRST_NAME || ' ' || LAST_NAME FROM hr.employees;

## Задание

Напишите отчёт, в котором отражена структура отдела, которым руководит Mourgos.
Выведите фамилии, оклады и номер отдела сотрудников.

SELECT last_name, salary, department_id        
FROM employees        
START WITH last_name = 'Mourgos'        
CONNECT BY PRIOR employee_id = manager_id;

## Задание

Создайте отчёт, который показывает иерархию менеджеров,
которым подчиняется сотрудник Lorentz.
Выведите сначала менеджера, перед которым непосредственно отчитывается Lorentz.

SELECT last_name        
FROM employees        
WHERE last_name != 'Lorentz'        
START WITH last_name = 'Lorentz'        
CONNECT BY PRIOR manager_id = employee_id;

## Задание

Создайте отчёт с отступом, в котором отражается иерархия управления,
начиная с сотрудника по фамилии Kochhar.
Выведите фамилии, номера менеджеров и номера отделов сотрудников.
Назовите столбцы как показано в примере выходных результатов.

COLUMN name FORMAT A20
SELECT  LPAD(last_name,LENGTH(last_name)+(LEVEL*2)-2,'_') name
       ,manager_id mgr
       ,department_id deptno
FROM employees START
WITH last_name = 'Kochhar'
CONNECT BY PRIOR employee_id = manager_id / COLUMN name CLEAR

## Задание

Создайте отчёт, отражающий иерархию управления компанией.
Начните с сотрудника самого высокого уровня и исключите из выходных данных всех служащих с идентификатором должности IT_PROG, а также сотрудника De Haan и всех, кто перед ним отчитывается.

SELECT  last_name
       ,employee_id
       ,manager_id
FROM employees
WHERE job_id != 'IT_PROG' START 
WITH manager_id IS NULL 
CONNECT BY PRIOR 
    employee_id = manager_id 
    AND last_name != 'De Haan';

## Задание

Напишите запрос для вывода фамилий, названия отдела, идентификатора местоположения отдела и города, в котором он находится, для всех служащих, зарабатывающих комиссионные.

SELECT  a.last_name
       ,b.department_name
       ,c.location_id
       ,c.city
FROM employees a
INNER JOIN departments b
    ON a.department_id = b.department_id
INNER JOIN locations c
    ON b.location_id = c.location_id
WHERE a.commission_pct is not null

## Задание

Покажите номер отдела с наивысшей средней заработной платой и наименьший оклад работающего в нём сотрудника.

SELECT  department_id
       ,minsalary
FROM
(
	SELECT  department_id
	       ,AVG(salary)sal
	       ,MIN(salary)minsalary
	FROM employees
	GROUP BY  department_id
)
WHERE sal = (
    SELECT  MAX(AVG(salary))
    FROM employees
    GROUP BY  department_id
)

## Задание

Выведите номера , наименования и местоположения отделов, в которых не работают торговые представители (job_id = ‘SA_REP’).

SELECT  a.department_id
       ,a.department_name
       ,a.manager_id
       ,a.location_id
FROM departments a
WHERE a.department_id not IN (
    SELECT nvl(department_id, 0)
    FROM employees
    WHERE job_id = 'sa_rep'
);

## Задание

Создайте запрос для вывода фамилии, номера отдела и оклада всех служащих, чей номер отдела и оклад совпадают с номером отдела и окладом любого служащего, зарабатывающего комиссионные.

SELECT  last_name
       ,department_id
       ,salary
FROM employees
WHERE (salary, department_id) IN (
    SELECT salary, department_id
    FROM employees
    WHERE commission_pct IS NOT NULL
)

## Задание

Выведите фамилию, название отдела и оклад всех служащих, чей оклад и комиссионные совпадают с окладом и комиссионными любого служащего, работающего в отделе, идентификатор местоположения которого Location_ID = 1700.

SELECT
    e.last_name,
    d.department_name,
    e.salary
FROM
    employees e,
    departments d
WHERE
    e.department_id = d.department_id
    AND (salary, NVL(commission_pct, 0)) IN (
        SELECT
            salary,
            NVL(commission_pct, 0)
        FROM
            employees e,
            departments d
        WHERE
            e.department_id = d.department_id
            AND d.location_id = 1700
)

## Задание

Создайте запрос для вывода фамилии, даты найма и оклада всех служащих, которые получают такой же оклад и такие же комиссионные, как Kochhar. Не выводите данные о сотруднике Kochhar.

SELECT
    last_name,
    hire_date,
    salary
FROM
    employees
WHERE
    (salary, NVL(commission_pct, 0)) IN (
    SELECT
        salary,
        NVL(commission_pct, 0)
    FROM
        employees
    WHERE
        last_name = 'Kochhar'
)
AND last_name != 'Kochhar'

## Задание

Выведите фамилию, должность и оклад всех служащих, оклад которых превышает оклад каждого клерка торгового менеджера (JOB_ID = ‘SA_MAN’).
Отсортируйте результаты по убыванию окладов.

SELECT
    last_name,
    job_id,
    salary
FROM
    employees
WHERE
    salary > ALL (
        SELECT
            salary
        FROM
            employees
        WHERE
            job_id = 'SA_MAN'
    )
ORDER BY
    salary DESC

## Задание

Выведите номера, фамилии и отделы служащих, живущих в городах, названия которых начинаются с буквы Т.

SELECT
    employee_id,
    last_name,
    department_id
FROM
    employees
WHERE
    department_id IN (
    SELECT
        department_id
    FROM
        departments
    WHERE
        location_id IN (
            SELECT
                location_id
            FROM
                locations
            WHERE
                city LIKE 'T%'
        )
)

## Задание

Напишите запрос для нахождения всех сотрудников, которые зарабатывают больше среднего оклада по их отделу.
Выведите фамилию, оклад, номер отдела и средний оклад по отделу.
Отсортируйте результаты по средней зарплате. Используйте псевдонимы для выбираемых столбцов.

SELECT
    ename,
    salary,
    deptno,
    dept_avg
FROM
    (
        SELECT
            e.last_name ename,
            e.salary salary,
            e.department_id deptno,
            round(AVG(a.salary)) dept_avg
        FROM
            employees e,
            employees a
        WHERE
            e.department_id = a.department_id
            AND e.salary > (
                SELECT
                    AVG(salary)
                FROM
                    employees
                WHERE
                    department_id = e.department_id
            )
        GROUP BY
            e.last_name,
            e.salary,
            e.department_id
    )
ORDER BY
    dept_avg

## Задание

Найдите всех сотрудников, не являющихся руководителями.
Выполните это с помощью оператора NOT EXIST.

SELECT   *
FROM     employees
ORDER BY manager_id
SELECT   a.last_name,
         a.employee_id,
         a.manager_id
FROM
         employees a
WHERE
    NOT EXISTS (
        SELECT  *
        FROM    employees b
        WHERE   b.manager_id = a.employee_id
    )

Может ли это же быть сделано с помощью оператора NOT IN?

SELECT
    a.last_name,
    a.employee_id,
    a.manager_id
FROM
    employees a
WHERE
    a.employee_id not IN (
        SELECT
            b.manager_id
        FROM
            employees b
        WHERE
            b.manager_id = a.employee_id
    )

## Задание

Выведите фамилии сотрудников, зарабатывающих меньше среднего оклада по их отделу.

SELECT  last_name
FROM employees a
WHERE a.salary < (
    SELECT  AVG(b.salary)
    FROM employees b
    WHERE b.department_id = a.department_id
)

## Задание

Выведите фамилии сотрудников, у которых есть коллеги по отделу,
которые были приняты на работу позже, но имеют более высокий оклад.

SELECT  last_name
FROM employees a
WHERE exists (
    SELECT  *
    FROM employees b
    WHERE b.department_id = a.department_id
    AND b.hire_date > a.hire_date
    AND b.salary > a.salary
) 

## Задание

Найти список ID отделов с максимальной суммарной зарплатой сотрудников

WITH dep_salary AS
(
	SELECT  department_id
	       ,SUM(salary) AS salary
	FROM employee
	GROUP BY  department_id
)
SELECT  department_id
FROM dep_salary
WHERE dep_salary.salary = (
    SELECT  MAX(salary)
    FROM dep_salary
);

## Задание

Вывести список служащих, фактические объемы продаж которых не попадают в диапазон от 80 до 120 процентов плана.

SELECT  NAME
       ,SALES
       ,QUOTA
FROM salesreps
WHERE SALES not BETWEEN 0.8 * QUOTA AND 1.2 * QUOTA; 

## Задание

Сформируйте поле, которое указывает последние 4 цифры номера телефона сотрудника. Уберите в телефоне символ «.». Определите работников с самым длинным email адресом. Сформируйте поле с именем и фамилией сотрудника

**Решение:**

1) Сформируйте поле, которое указывает последние 4 цифры номера телефона сотрудника

SELECT  substr(PHONE_NUMBER,length(PHONE_NUMBER)-3 )
FROM hr.employees;

2) Уберите в телефоне символ «.»

SELECT REPLACE(phone_number, '.', '') from hr.employees;
-- в случае если подстрока меняется на пустую строку, второй параметр не обязателен
SELECT REPLACE(phone_number, '.') from hr.employees; 

3) Определите работников с самым длинным email адресом

SELECT  *
FROM hr.employees
WHERE length(email) = (
    SELECT  MAX(length(email))
    FROM hr.employees; 
)

4) Сформируйте поле с именем и фамилией сотрудника

SELECT  FIRST_NAME || ' ' || LAST_NAME FROM hr.employees;

## Задание

Напишите отчёт, в котором отражена структура отдела, которым руководит Mourgos.
Выведите фамилии, оклады и номер отдела сотрудников.

SELECT last_name, salary, department_id        
FROM employees        
START WITH last_name = 'Mourgos'        
CONNECT BY PRIOR employee_id = manager_id;

## Задание

Создайте отчёт, который показывает иерархию менеджеров,
которым подчиняется сотрудник Lorentz.
Выведите сначала менеджера, перед которым непосредственно отчитывается Lorentz.

SELECT last_name        
FROM employees        
WHERE last_name != 'Lorentz'        
START WITH last_name = 'Lorentz'        
CONNECT BY PRIOR manager_id = employee_id;

## Задание

Создайте отчёт с отступом, в котором отражается иерархия управления,
начиная с сотрудника по фамилии Kochhar.
Выведите фамилии, номера менеджеров и номера отделов сотрудников.
Назовите столбцы как показано в примере выходных результатов.

COLUMN name FORMAT A20
SELECT  LPAD(last_name,LENGTH(last_name)+(LEVEL*2)-2,'_') name
       ,manager_id mgr
       ,department_id deptno
FROM employees START
WITH last_name = 'Kochhar'
CONNECT BY PRIOR employee_id = manager_id / COLUMN name CLEAR

## Задание

Создайте отчёт, отражающий иерархию управления компанией.
Начните с сотрудника самого высокого уровня и исключите из выходных данных всех служащих с идентификатором должности IT_PROG, а также сотрудника De Haan и всех, кто перед ним отчитывается.

SELECT  last_name
       ,employee_id
       ,manager_id
FROM employees
WHERE job_id != 'IT_PROG' START 
WITH manager_id IS NULL 
CONNECT BY PRIOR 
    employee_id = manager_id 
    AND last_name != 'De Haan';

## Задание

Напишите запрос для вывода фамилий, названия отдела, идентификатора местоположения отдела и города, в котором он находится, для всех служащих, зарабатывающих комиссионные.

SELECT  a.last_name
       ,b.department_name
       ,c.location_id
       ,c.city
FROM employees a
INNER JOIN departments b
    ON a.department_id = b.department_id
INNER JOIN locations c
    ON b.location_id = c.location_id
WHERE a.commission_pct is not null

## Задание

Покажите номер отдела с наивысшей средней заработной платой и наименьший оклад работающего в нём сотрудника.

SELECT  department_id
       ,minsalary
FROM
(
	SELECT  department_id
	       ,AVG(salary)sal
	       ,MIN(salary)minsalary
	FROM employees
	GROUP BY  department_id
)
WHERE sal = (
    SELECT  MAX(AVG(salary))
    FROM employees
    GROUP BY  department_id
)

## Задание

Выведите номера , наименования и местоположения отделов, в которых не работают торговые представители (job_id = ‘SA_REP’).

SELECT  a.department_id
       ,a.department_name
       ,a.manager_id
       ,a.location_id
FROM departments a
WHERE a.department_id not IN (
    SELECT nvl(department_id, 0)
    FROM employees
    WHERE job_id = 'sa_rep'
);

## Задание

Создайте запрос для вывода фамилии, номера отдела и оклада всех служащих, чей номер отдела и оклад совпадают с номером отдела и окладом любого служащего, зарабатывающего комиссионные.

SELECT  last_name
       ,department_id
       ,salary
FROM employees
WHERE (salary, department_id) IN (
    SELECT salary, department_id
    FROM employees
    WHERE commission_pct IS NOT NULL
)

## Задание

Выведите фамилию, название отдела и оклад всех служащих, чей оклад и комиссионные совпадают с окладом и комиссионными любого служащего, работающего в отделе, идентификатор местоположения которого Location_ID = 1700.

SELECT
    e.last_name,
    d.department_name,
    e.salary
FROM
    employees e,
    departments d
WHERE
    e.department_id = d.department_id
    AND (salary, NVL(commission_pct, 0)) IN (
        SELECT
            salary,
            NVL(commission_pct, 0)
        FROM
            employees e,
            departments d
        WHERE
            e.department_id = d.department_id
            AND d.location_id = 1700
)

## Задание

Создайте запрос для вывода фамилии, даты найма и оклада всех служащих, которые получают такой же оклад и такие же комиссионные, как Kochhar. Не выводите данные о сотруднике Kochhar.

SELECT
    last_name,
    hire_date,
    salary
FROM
    employees
WHERE
    (salary, NVL(commission_pct, 0)) IN (
    SELECT
        salary,
        NVL(commission_pct, 0)
    FROM
        employees
    WHERE
        last_name = 'Kochhar'
)
AND last_name != 'Kochhar'

## Задание

Выведите фамилию, должность и оклад всех служащих, оклад которых превышает оклад каждого клерка торгового менеджера (JOB_ID = ‘SA_MAN’).
Отсортируйте результаты по убыванию окладов.

SELECT
    last_name,
    job_id,
    salary
FROM
    employees
WHERE
    salary > ALL (
        SELECT
            salary
        FROM
            employees
        WHERE
            job_id = 'SA_MAN'
    )
ORDER BY
    salary DESC

## Задание

Выведите номера, фамилии и отделы служащих, живущих в городах, названия которых начинаются с буквы Т.

SELECT
    employee_id,
    last_name,
    department_id
FROM
    employees
WHERE
    department_id IN (
    SELECT
        department_id
    FROM
        departments
    WHERE
        location_id IN (
            SELECT
                location_id
            FROM
                locations
            WHERE
                city LIKE 'T%'
        )
)

## Задание

Напишите запрос для нахождения всех сотрудников, которые зарабатывают больше среднего оклада по их отделу.
Выведите фамилию, оклад, номер отдела и средний оклад по отделу.
Отсортируйте результаты по средней зарплате. Используйте псевдонимы для выбираемых столбцов.

SELECT
    ename,
    salary,
    deptno,
    dept_avg
FROM
    (
        SELECT
            e.last_name ename,
            e.salary salary,
            e.department_id deptno,
            round(AVG(a.salary)) dept_avg
        FROM
            employees e,
            employees a
        WHERE
            e.department_id = a.department_id
            AND e.salary > (
                SELECT
                    AVG(salary)
                FROM
                    employees
                WHERE
                    department_id = e.department_id
            )
        GROUP BY
            e.last_name,
            e.salary,
            e.department_id
    )
ORDER BY
    dept_avg

## Задание

Найдите всех сотрудников, не являющихся руководителями.
Выполните это с помощью оператора NOT EXIST.

SELECT   *
FROM     employees
ORDER BY manager_id
SELECT   a.last_name,
         a.employee_id,
         a.manager_id
FROM
         employees a
WHERE
    NOT EXISTS (
        SELECT  *
        FROM    employees b
        WHERE   b.manager_id = a.employee_id
    )

Может ли это же быть сделано с помощью оператора NOT IN?

SELECT
    a.last_name,
    a.employee_id,
    a.manager_id
FROM
    employees a
WHERE
    a.employee_id not IN (
        SELECT
            b.manager_id
        FROM
            employees b
        WHERE
            b.manager_id = a.employee_id
    )

## Задание

Выведите фамилии сотрудников, зарабатывающих меньше среднего оклада по их отделу.

SELECT  last_name
FROM employees a
WHERE a.salary < (
    SELECT  AVG(b.salary)
    FROM employees b
    WHERE b.department_id = a.department_id
)

## Задание

Выведите фамилии сотрудников, у которых есть коллеги по отделу,
которые были приняты на работу позже, но имеют более высокий оклад.

SELECT  last_name
FROM employees a
WHERE exists (
    SELECT  *
    FROM employees b
    WHERE b.department_id = a.department_id
    AND b.hire_date > a.hire_date
    AND b.salary > a.salary
) 

## Задание

Выведите номера, фамилии и наименования отделов всех сотрудников.
Используйте скалярный подзапрос в команде SELECT для вывода наименований отделов.

SELECT  a.employee_id
       ,a.last_name
       ,(
SELECT  b.department_name
FROM departments b
WHERE a.department_id = b.department_id) department
FROM employees a
ORDER BY department

# Нетривиальные задачи

## Поиск по шаблону
Из служащих отделов 10 и 20 требуется выбрать только тех, в имени которых встречается буква «A» или чье название должности заканчивается на «ER»

**Решение:**

<div class="wrap">
<div class="left_col">

SELECT
    first_name,
    job_id
FROM
     hr.employees
WHERE
    department_id IN (30, 50)
    AND (
        first_name like '%A%'
        or job_id like '%ER'
    )

</div>
<div class="right_col">

Результат:

<img src="https://i.imgur.com/MmXR5Sk.png">

</div>
</div>

## Сотрудники, работающие в компании дольше руководителя подразделения

Используются таблицы стандартной демо-схемы HR.

Одной командой SELECT вывести всех сотрудников, которые приняты на работу
раньше руководителя подразделения компании, к которому они приписаны в данный
момент.

В результате вывести следующие данные:
* Название подразделения компании
* Имя сотрудника
* Фамилия сотрудника
* На сколько суток раньше руководителя данный сотрудник был принят на работу

**Предлагаемые решения:**

* Решение через соединение таблиц

SELECT d.department_name, e1.first_name, e1.last_name, TO_NUMBER(e2.hire_date - e1.hire_date) days
FROM employees e1, departments d, employees e2
WHERE e1.department_id = d.department_id
  AND d.manager_id = e2.employee_id
  AND TO_NUMBER(e2.hire_date - e1.hire_date) > 0

## Чаще всего встречающиеся имена

Используются таблицы стандартной демо-схемы HR.

Одной командой SELECT вывести наиболее часто встречающтеся имена сотрудников.

Если несколько имен встречаются чаще всего, то вывести все эти имена.

**Предлагаемые решения:**
* Решение с использованием виртуальных таблиц и аналитических функций

WITH t AS (
  SELECT DISTINCT e.first_name, COUNT(1) OVER (PARTITION BY e.first_name) cnt
  FROM employees e
), t2 AS (
  SELECT first_name, cnt, MAX(cnt) OVER () max_cnt
  FROM t 
)
SELECT first_name 
FROM t2 
WHERE cnt = max_cnt

## Выбор сотрудников, менявших должность без записи в историю

Одной командой SELECT выбрать сотрудников, для которых
* в таблице истории занятия должностей зафиксированы переходы на другую должность
* в таблице истории занятия должностей отсутствуют сведения об их первой должности, которую они занимали при приеме на работу в компанию.

В результат вывести:
* идентификатор сотрудника,
* фамилию сотрудника,
* дата, когда сотрудник был принят на работу,
* идентификатор должности, которую занимает сотрудник,
* идентификатор подразделения, к которому в данный момент приписан сотрудник.

Результат отсортировать по возрастанию по полям:
* дата, когда сотрудник был принят на работу,
* фамилия сотрудника.

**Предлагаемые решения:**
* Решение с использованием групповых сравнений и вложенных подзапросов

SELECT e.employee_id, e.last_name, e.hire_date, e.job_id, e.department_id  
FROM hr.employees e
WHERE e.employee_id = ANY(SELECT j.employee_id FROM hr.job_history j)
AND (e.employee_id, e.hire_date) <> ALL(SELECT j.employee_id, j.start_date FROM hr.job_history j)
ORDER BY e.last_name ASC, e.hire_date ASC

* Решение с использованием виртуальной таблицы и соединением с историей занимаемых должностей

WITH emp AS (
  SELECT e.employee_id, e.hire_date, e.last_name, e.job_id, e.department_id 
  FROM employees e, job_history j
  WHERE j.employee_id = e.employee_id
)
SELECT distinct emp.employee_id, emp.last_name, emp.hire_date, emp.job_id, emp.department_id 
FROM emp, job_history j
WHERE emp.employee_id = j.employee_id (+)
AND emp.hire_date = j.start_date (+)
AND j.employee_id IS NULL
ORDER BY emp.last_name ASC, emp.hire_date ASC

* Решение с использованием EXISTS, функций аггрегирования и вложенного подзапроса

SELECT
    e.employee_id,
    e.last_name,
    e.hire_date,
    e.job_id,
    e.department_id
FROM
    employees e
WHERE
    EXISTS (
        SELECT
            1
        FROM
            job_history jj
        WHERE
            jj.employee_id = e.employee_id
        GROUP BY
            jj.employee_id
        HAVING
            MIN(start_date) <> e.hire_date
    )
ORDER BY
    e.last_name ASC,
    e.hire_date ASC

* Решение с использованием одностороннего соединения таблиц и аггрегирующих функций

SELECT
    jh.employee_id,
    e.last_name,
    e.hire_date,
    e.job_id,
    e.department_id
FROM
    job_history jh
    LEFT JOIN employees e ON jh.employee_id = e.employee_id
GROUP BY
    (
        jh.employee_id,
        e.last_name,
        e.hire_date,
        e.job_id,
        e.department_id
    )
HAVING
    MIN(start_date) <> hire_date
ORDER BY
    e.last_name ASC,
    e.hire_date ASC

## Оконная функция

Вывести должности и подсчитать количество сотрудников на каждой должности в каждом отделе. Столбик назвать «EmpCount».

<center>
Результат:

</center>

SELECT
    DISTINCT department_id,
    job_id,
    COUNT(employee_id) OVER(PARTITION BY department_id, job_id) AS "EmpCount"
FROM
    HR.employees
ORDER BY 1;

## Сбор информации из последующих строк

Требуется найти каждого служащего, который зарабатывает меньше, чем служащий, принятый на работу сразу после него. Исходя из следующего результирующего множества:

**Предлагаемые решения:**

1. С помощью подзапросов определите для каждого служащего:
- Дату приема на работу следующего служащего, имеющего более высокую заработную плату.
- Дату приема на работу следующего служащего.<br>
Если эти две даты совпадают, мы нашли то, что искали:

SELECT  last_name
       ,salary
       ,hire_date
FROM
(
  SELECT
      a.last_name,
      a.salary,
      a.hire_date,
      (
          SELECT MIN(hire_date)
          FROM   hr.employees b
          WHERE  b.hire_date > a.hire_date
                 AND b.salary > a.salary
      ) AS next_salary_grtr,
      (
          SELECT MIN(hire_date)
          FROM   hr.employees b
          WHERE  b.hire_date > a.hire_date
      ) AS next_hire
  FROM hr.employees a
) x
WHERE next_salary_grtr = next_hire

<center>

Результат:


</center>

Для каждого служащего скалярные подзапросы возвращают значение HIREDATE служащего, принятого на работу сразу после него, и HIREDATE первого служащего из тех, кто был принят на работу позже, но зарабатывает больше рассматриваемого служащего. Рассмотрим исходные данные:

SELECT
    a.last_name,
    a.salary,
    a.hire_date,
    (
        SELECT MIN(hire_date)
        FROM   hr.employees b
        WHERE  b.hire_date > a.hire_date
               AND b.salary > a.salary
    ) AS next_salary_grtr,
    (
        SELECT MIN(hire_date)
        FROM   hr.employees b
        WHERE  b.hire_date > a.hire_date
    ) AS next_hire
FROM hr.employees a

<center>

Результат:


</center>

Не обязательно, чтобы те, кто был принят на работу позже, был принят сразу после рассматриваемого служащего. Следующий (и последний) шаг –выбрать только те строки, в которых значение NEXT_SAL_GRTR (наименьшее значение HIREDATE среди служащих, зарабатывающих больше рассматриваемого служащего) равно NEXT_HIRE (ближайшее последующее значение HIREDATE относительно HIREDATE текущего служащего).

2. С помощью оконной функции LEAD OVER можно получить заработную плату служащего, принятого на работу следующим после рассматриваемого служащего. Затем остается просто сравнить их заработные платы:

SELECT
    last_name,
    salary,
    hire_date
FROM(
  SELECT
      last_name,
      salary,
      hire_date,
      lead(salary) over(
          order by
              hire_date
      ) next_salary
  FROM
      hr.employees
)
WHERE
    salary < next_salary

Оконная функция LEAD OVER идеально подходит для решения задач такого типа. Она принимает аргумент, определяющий, на сколько строк вперед она должна «заглянуть» (по умолчанию аргумент равен 1). Возможность «перепрыгивать» через несколько строк важна в случае присутствия дублирующихся значений в столбце, по которому осуществляется упорядочивание. Следующий пример показывает, как просто с помощью LEAD OVER получить заработную плату служащего, принятого на работу «следующим»:

**Предлагаемые решения:**

<div class="wrap">
<div class="left_col">

SELECT
    last_name,
    salary,
    hire_date,
    lead(salary) over(
        order by hire_date
    ) next_salary
FROM
  hr.employees

</div>
<div class="right_col">

Результат:

</div>
</div>

Заключительный шаг – выбрать только те строки, в которых значение SAL меньше NEXT_SAL. Поскольку по умолчанию областью действия LEAD OVER является одна строка, в случае присутствия дубликатов в таблице EMP, в частности, если несколько служащих были приняты на работу в один день, сравниваться будут их SAL. Это может не соответствовать требуемому поведению. Если стоит задача сравнивать SAL каждого служащего с SAL служащего, который был принят на работу следующим, но не в один день с рассматриваемым служащим, в качестве альтернативы можно использовать следующее решение:

SELECT
    last_name,
    salary,
    hire_date
FROM(
    SELECT
        last_name,
        salary,
        hire_date,
        lead(salary, cnt - rn + 1) over(
            order by hire_date
        ) next_salary
    FROM (
        SELECT
            last_name,
            salary,
            hire_date,
            COUNT(*) over(partition by hire_date) cnt,
            row_number() over(
                partition by hire_date
                ORDER BY employee_id
            ) rn
        FROM hr.employees
      )
) WHERE salary < next_salary

<center>
Результат:


</center>

Основная идея данного решения – найти, на сколько строк текущая строка отстоит от строки, с которой должна сравниваться. Например, если имеется пять дубликатов, первый из пяти должен сравниваться со строкой, отстоящей от него на пять строк. Значение CNT для каждого служащего с дублирующимся значением HIREDATE представляет общее количество дубликатов данного HIREDATE. Значение RN представляет ранг служащего 10!го отдела (DEPTNO 10). Ранги подразделяются по HIREDATE, поэтому только для служащих с дублирующимися HIREDATE это значение может быть больше 1. Сортируются
ранги по EMPNO (это выбрано произвольно). Теперь, когда известно общее количество дублирующихся значений и для каждого их них определен ранг, чтобы найти расстояние до следующего значения HIREDATE, необходимо просто из общего числа дубликатов вычесть ранг текущего и прибавить единицу (CNT – RN + 1).

## Ход конем

Требуется получить множество, содержащее имя каждого служащего, отдел, в котором он работает, его заработную плату, дату его приема на работу и заработную плату сотрудника, принятого на работу последним в отделе. Должно быть получено следующее результирующее множество:

<center>


</center>

**Предлагаемые решения:**

1) Чтобы найти для каждого отдела значение SAL служащего, принятого на работу последним, используем в подзапросе выражение CASE. Для всех остальных заработных плат возвращаем нуль. С помощью оконной функции MAX OVER во внешнем запросе получаем значения SAL, не равные нулю, для отдела каждого служащего:

SELECT  department_id
       ,last_name
       ,salary
       ,hire_date
       ,MAX(latest_salary)over(partition by department_id) latest_salary
FROM
(
	SELECT  department_id
	       ,last_name
	       ,salary
	       ,hire_date
	       ,CASE
                WHEN hire_date = MAX(hire_date)over(partition by department_id)
             THEN salary  ELSE 0
            END latest_salary
	FROM hr.employees
) x
ORDER BY 1, 4 DESC

Первый шаг – использовать оконную функцию MAX OVER в выражении CASE, чтобы найти в каждом отделе служащего, который был принят на работу самым последним или позже всех. Если значение HIREDATE служащего совпадает со значением, возвращенным MAX OVER, с помощью выражения CASE возвращаем значение SAL этого служащего; в противном случае возвращаем 0. Результаты показаны ниже:

SELECT  department_id
        ,last_name
        ,salary
        ,hire_date
        ,CASE WHEN hire_date = MAX(hire_date)over(partition by department_id) THEN salary  ELSE 0 END latest_salary
FROM hr.employees

<center>


</center>

Поскольку LATEST_SAL будет содержать 0 или SAL служащих, принятых на работу последними, приведенный выше запрос можно поместить во вложенный запрос и опять применить MAX OVER, но на этот раз, чтобы получить наибольшее отличное от нуля значение LATEST_SAL для каждого DEPTNO.

2) Используем скалярный подзапрос с двумя уровнями вложенности. Сначала находим для всех отделов HIREDATE служащего, принятого на работу последним. Затем используем агрегатную функцию MAX (на случай дублирования значений), чтобы выбрать значение SAL каждого такого служащего:

SELECT
    e.department_id,
    e.last_name,
    e.salary,
    e.hire_date,
(   SELECT
            MAX(d.salary)
        FROM
            hr.employees d
        WHERE
            d.department_id = e.department_id
            AND d.hire_date = (
                SELECT
                    MAX(f.hire_date)
                FROM
                    hr.employees f
                WHERE
                    f.department_id = e.department_id
            )
    ) AS latest_salary
FROM
    hr.employees e
ORDER BY 1, 4 desc

Первый шаг – использовать скалярный подзапрос, чтобы найти для каждого отдела HIREDATE служащего, принятого на работу самым последним:

SELECT
    e.department_id,
    e.last_name,
    e.salary,
    e.hire_date,
(   SELECT
        MAX(f.hire_date)
    FROM
        hr.employees f
    WHERE
        f.department_id = e.department_id
) AS last_hire
FROM
    hr.employees e
ORDER BY 1, 4 desc

<center>


</center>

Следующий шаг – найти в каждом отделе SAL служащих, дата приема на работу которых совпадает с датой LAST_HIRE. С помощью агрегатной функции MAX выбираем наибольшее значение SAL (если в один день было нанято несколько служащих).

## Вычисление простых подсумм

В данном рецепте под «простой подсуммой» подразумевается резуль!
тирующее множество, содержащее значения, полученные в результа!
те агрегации одного столбца, и общую сумму таблицы. В качестве при!
мера возьмем результирующее множество, содержащее суммы зара!
ботных плат таблицы EMP по должностям (JOB), а также сумму всех
заработных плат таблицы EMP. Суммы зарплат по JOB – это подсум!
мы, а сумма всех заработных плат таблицы EMP – это общая сумма.
Такое результирующее множество выглядело бы так

| JOB_TITLE | SALARYARY |
|--------------------------|-----------|
| Accountant | 39600 |
| Accounting Manager | 12008 |
| Administration Assistant | 4400 |
| Purchasing Manager | 11000 |
| ... | ... |
| Sales Manager | 61000 |
| TOTAL | 691416 |

**Предлагаемые решения:**

1) Используя агрегатную функцию SUM, просуммируйте заработные платы; с помощью расширения ROLLUP оператора GROUP BY организуйте результаты в подсуммы (по JOB) и найдите общую сумму (для всей таблицы):

SELECT
    case
        grouping(job_title)
        WHEN 0 THEN job_title else 'TOTAL'
    end job_title,
    SUM(salary) salary_sum
FROM hr.employees
JOIN hr.jobs USING (job_id)
GROUP BY rollup(job_title) 

2) Используя агрегатную функцию SUM, просуммируйте заработные платы по DEPTNO. Затем посредством оператора UNION ALL объедините этот запрос с запросом, вычисляющим сумму всех заработных плат таблицы:

SELECT
    job_title,
    SUM(salary) AS salary
FROM
    hr.employees
    JOIN hr.jobs USING (job_id)
GROUP BY
    job_title
UNION ALL
SELECT
    'TOTAL', SUM(salary)
FROM
    hr.employees
    JOIN hr.jobs USING (job_id)

## Изощренно отсортированный список сотрудников по подразделениям компании

Используются телицы стандартной демо-схемы HR.

Одной командой select вывести отсортированный список сотрудников компании.

В результат вывести 8 (восемь) столбцов:
1) идентификатор сотрудника
2) Фамилию сотрудника
3) имя сотрудника
4) название подразделения компании, к которому приписан сотрудник
5) Дату найма сотрудника
6) е-mail сотрудника
7) оклад, установленный сотруднику
8) номер телефона

Cведения о сотрудникам, для которых неизвестно к какому подразделению компании они приписаны, выводить не нужно.
Результат отсортировать:

1) по названию подразделения компании (по возрастанию)
2) внутри каждого подразделения сотрудников вывести в порядке:
1) с окладом менее 4000, отсортированных по дате найма (.по возрастанию),
2) с окладом не менее 4000, но менее 8000, отсортированных по e-mail (по убыванию)
3) с окладом не менее 8000, но менее 12000, отсортированных по номеру телефона (по убыванию, NULL-значения в конце)
4) с окладом не менее 12000 или с неизвестным окладом, отсортированных по величине оклада (по возрастанию, NULL-значения в конце)

3) по идентификатору сотрудника (по возрастанию)

**Предлагаемые решения:**

* Решение с использованием оператора CASE

SELECT employee_id, last_name, first_name, department_name, hire_date, email, salary, phone_number
FROM t1
ORDER BY department_name, 
CASE
  WHEN salary < 4000 THEN sort1
  WHEN salary >= 4000 AND salary < 8000 THEN sort2
  WHEN salary >= 8000 AND salary < 12000 THEN sort3
  WHEN salary >= 12000 THEN sort4          
END

## Выбор сотрудников с минимальным окладом

Выбрать сотрудников с минимальным окладом по подразделению,
* сотрудников не приписанных к подразделению выводить не нужно,
* нельзя использовать:
* подзапросы, в том числе во фразе FROM
* конструкции CONNECT BY, START WITH, GROUP BY, WITH
* аналитические функции

**Предлагаемые решения:**
* Решение через одностороннее соединение таблиц

SELECT e1.department_id, e1.last_name,e1.salary 
FROM employees e1, employees e2
WHERE e1.salary > e2.salary (+)
  AND e1.department_id = e2.department_id (+) 
  AND e2.employee_id IS NULL 
  AND e1.department_id IS NOT NULL
ORDER BY e1.department_id, e1.last_name, e1.salary, e1.employee_id

## Вычисление моды

Требуется найти моду (мода - это наиболее часто встречающийся элемент рассматриваемого множества данных) столбца значений. Например, поставлена задача найти моду заработных плат 60го отдела (DEPTNO 60). Для следующего набора заработных плат:

<div class="wrap">
<div class="left_col">

SELECT salary
FROM   hr.employees
WHERE  department_id = 60;

</div>
<div class="right_col">

Результат:

</div>
</div>

**Предлагаемые решения:**

<div class="wrap">
<div class="left_col">

SELECT  MAX(salary) keep(
                    dense_rank first 
        ORDER BY cnt desc) salary
FROM
(
	SELECT  salary
	       ,COUNT(*) cnt
	FROM hr.employees
	WHERE department_id = 20
	GROUP BY  salary
)

</div>
<div class="right_col">

Результат:

</div>
</div>

Вложенное представление возвращает каждое значение столбца SAL
и число раз, сколько это значение встречается в столбце, как показано
ниже:

<div class="wrap">
<div class="left_col">

SELECT  salary
       ,COUNT(*) cnt
FROM hr.employees
WHERE department_id = 60
GROUP BY  salary

</div>
<div class="right_col">

Результат:

</div>
</div>

Следующий шаг – использовать расширение KEEP агрегатной функции MAX для поиска моды. Если проанализировать приведенный ниже оператор KEEP, можно заметить три подоператора, DENSE_RANK, FIRST и ORDER BY CNT DESC:
keep(dense_rank first order by cnt desc)

С их помощью очень удобно искать моду. Оператор KEEP определяет, какое значение SAL будет возвращено функцией MAX, по значению CNT, возвращенному вложенным представлением. Выражение выполняется справа налево. Сначала значения для CNT выстраиваются поубыванию, затем выбирается первое из них и возвращается в порядке,
установленном функцией DENSE_RANK.

## Вычисление моды

Требуется найти медиану столбца числовых значений (медиана (median) – это значение среднего члена множества упорядоченных элементов). Например, поставлена задача найти медиану заработных плат служащих 60го отдела (DEPTNO 60). Для следующего набора заработных плат:

<div class="wrap">
<div class="left_col">

SELECT salary
FROM   hr.employees
WHERE  department_id = 60;

</div>
<div class="right_col">

Результат:

</div>
</div>

**Предлагаемые решения:**

<div class="wrap">
<div class="left_col">

SELECT
    median(salary)
FROM hr.employees
WHERE
    department_id = 60;
------------ OR ------------
SELECT
    percentile_cont(0.5) within group(
        order by salary
    )
FROM  hr.employees
WHERE department_id = 60;

</div>
<div class="right_col">

Результат:

</div>
</div>

Очевидно, что функция MEDIAN вычисляет медиану, тогда как с функцией PERCENTILE_CONT все не так явно, хотя она делает то же самое.
Передаваемый в PERCENTILE_CONT аргумент, 0,5 – это процентиль.
Конструкция WITHIN GROUP (ORDER BY SAL) определяет множество сортированных строк, с которым будет работать PERCENTILE_CONT (помним, что медиана – это середина множества упорядоченных значений). Возвращаемое значение соответствует заданной процентилив сортированном множестве строк (в данном случае, это 0,5; т. е. середина множества, поскольку граничными значениями являются 0 и 1).

## Вычисление среднего без учета наибольшего и наименьшего значений

Требуется вычислить среднее, но без учета наибольшего и наименьшего значений, чтобы уменьшить асимметрию распределения. Например, необходимо найти среднюю заработную плату служащих, исключая из расчета наибольшую и наименьшую из них.

**Предлагаемые решения:**

1. Исключаем наибольшее и наименьшее значения с помощью подзапросов:

<div class="wrap">
<div class="left_col">

SELECT TRUNC(AVG(salary),3) AS avg
FROM hr.employees
WHERE
    salary not IN (
        (
            SELECT MIN(salary)
            FROM hr.employees
        ),
        (
            SELECT MAX(salary)
            FROM hr.employees
        )
    );

</div>
<div class="right_col">

Результат:

</div>
</div>

Решение 2: С помощью вложенного запроса с оконными функциями MAX OVER и MIN OVER формируем результирующее множество, из которого можно без труда исключить наибольшее и наименьшее значения:

SELECT TRUNC(AVG(salary),3) AS avg
FROM
    (
        SELECT salary,
            MIN(salary) over() min_salary,
            MAX(salary) over() max_salary
        FROM hr.employees
    ) x
WHERE
    salary not IN (min_salary, max_salary);

Вложенный запрос Х возвращает все заработные платы, а также наибольшую и наименьшую из них:

<div class="wrap">
<div class="left_col">

 SELECT
    salary,
    MIN(salary) over() min_salary,
    MAX(salary) over() max_salary
FROM hr.employees

</div>
<div class="right_col">

Результат:

</div>
</div>

Обратиться к наибольшей и наименьшей заработной плате можно в любой строке, поэтому доступ к ним не составляет труда. Внешний запрос фильтрует строки, возвращенные вложенным запросом Х, так, чтобы все заработные платы, соответствующие MIN_SAL или MAX_SAL, были исключены из вычисления среднего.

## Вычисление доли от целого в процентном выражении

Требуется определить, какую долю от целого в процентном выражении для определенного столбца составляет та или иная группа значений.
Например, стоит задача вычислить, какой процент от всех заработных плат составляют заработные платы служащих 50го отдела (процентный вклад зарплат DEPTNO 50 в общую сумму заработных плат).

**Предлагаемые решения:**

1.<div class="wrap">
<div class="left_col">

SELECT
    TRUNC(
    (
     SUM(case WHEN  department_id = 50 THEN salary end)
     / SUM(salary)
    ) * 100
    ,2) || '%' AS pct
FROM
    hr.employees

</div>
<div class="right_col">

Результат:

</div>
</div>

2.<div class="wrap">

SELECT
    distinct TRUNC((d50 / total) * 100, 2) || '%' AS pct
FROM
    (
        SELECT
            department_id,
            SUM(salary) over() total,
            SUM(salary) over(partition by department_id) d50
        FROM hr.employees
    ) x
WHERE
    department_id = 50

</div>

В качестве альтернативы традиционному подходу второе решение для вычисления процента от целого использует оконные функции. Важно помнить, что оконные функции обрабатываются после предиката WHERE, по-этому значение столбца TOTAL представляет сумму всех зарплат только 50го отдела. Но для решения задачи TOTAL должен представлять сумму всех заработных плат. Вот почему фильтр по DEPTNO должен располагаться вне вложенного запроса Х.

## Определить, является ли год високосным

Требуется определить, является ли текущий год високосным.

**Предлагаемые решения:**

<div class="wrap">
<div class="left_col">

select 
  to_char(sysdate, 'yyyy') as Year,
  CASE WHEN
    to_char(
      last_day(add_months(trunc(sysdate,'y'),1)),
    'DD') = 29 then 'Yes'  
  ELSE 'No' 
  END AS IsLeap
from dual

</div>
<div class="right_col">

Результат:

Проверяется последний день февраля;
если он является 29м днем,
то текущий год – високосный.
</div>
</div>

## Выбор всех дат года, выпадающих на определенный день недели

Требуется найти все даты года, соответствующие заданному дню недели.
Например, стоит задача сформировать список пятниц текущего года.

**Предлагаемые решения:**

<div class="wrap">
<div class="left_col">

WITH x AS (
    SELECT
        trunc(sysdate, 'y') + level - 1 AS dy
    FROM
        dual
    connect by level <= add_months (trunc(sysdate, 'y'), 12) - trunc(sysdate, 'y')
)
SELECT *
FROM x
WHERE to_char(dy, 'dy') = 'fri'

<
</div>
<div class="right_col">

Результат:


</div>
</div>

Основой решения являются возвращение всех дней текущего года и выбор из них только тех, которые соответствуют интересующему дню недели. Рекурсивным оператором CONNECT BY возвращаем все дни текущего года. Затем с помощью функции TO_CHAR выбираем только пятницы.

:::danger
## Разворачивание ранжированного результирующего множества

Требуется ранжировать значения таблицы, затем развернуть результирующее множество и получить три столбца. Идея в том, чтобы показать в разных столбцах три наибольших значения, три следующих значения и все остальные. Например, необходимо ранжировать служащих в таблице EMP по значениям SAL и затем развернуть результаты в три столбца. Должно быть получено следующее результирующее множество:

TOP_3 NEXT_3 REST













































KING (5000) BLAKE (2850) TURNER (1500)
FORD (3000) CLARK (2450) MILLER (1300)
SCOTT (3000) ALLEN (1600) MARTIN (1250)
JONES (2975) WARD (1250)
ADAMS (1100)
JAMES (950)
SMITH (800)
<br>
:::

## Сравнение строк по определенной части даты

Требуется найти служащих, которые были приняты на работу в один месяц и день недели. Например, если один служащий был нанят в понедельник 10 марта 1988 года, а другой служащий – в понедельник 2 марта 2001, они должны быть включены в результирующее множество, поскольку день недели и месяц их приема на работу совпадают.В таблице EMP только трое служащих отвечают этому требованию.
Должно быть получено следующее результирующее множество:

<center>


</center>

**Предлагаемые решения:**

Поскольку требуется сравнивать значение HIREDATE одного служащего со значениями HIREDATE других служащих, понадобится провести рефлексивное объединение таблицы EMP. Тем самым мы получим все возможные сочетания HIREDATE. После этого для каждой строки мы просто будем извлекать день недели и месяц из значений HIREDATE и сравнивать их у разных строк.

SELECT
    a.first_name 
    || ' was hired on the same month and weekday as ' 
    || b.first_name AS msg
FROM
    hr.employees a,
    hr.employees b
WHERE
    to_char(a.hire_date, 'DMON') = to_char(b.hire_date, 'DMON')
    AND a.employee_id < b.employee_id
ORDER BY
    a.first_name

Благодаря рефлексивному объединению таблицы EMP мы можем сравнивать значение HIREDATE служащего SCOTT со значениями HIREDATE всех остальных служащих. Для EMPNO задан фильтр, который обеспечивает, что значение HIREDATE служащего SCOTT не будет включено в столбец OTHER_HDS:

<div class="wrap">
<div class="left_col">

--- ...
WHERE
    to_char(a.hire_date, 'DMON') = to_char(b.hire_date, 'DMON')
    AND a.employee_id != b.employee_id
ORDER BY
    a.first_name

</div>
<div class="right_col">

Результат:


</div>
</div>

Причина вывода лишних строк кроется в фильтре по EMPNO. Использование оператора «не равно» не обеспечивает отсеивание обратных равенств. Например, в третьей строке со поставляются BRITNEY и CHRISTOPHER, а в пятой строке – CHRISTOPHER и BRITNEY.
Строки результирующего множества, с технической точки зрения, возвращены правильно, но они дублируют друг друга. Избавиться от дублирования позволит оператор «<»

:::danger
## Преобразование целых чисел в их двоичное

Требуется преобразовать целое число в его двоичное представление. Например, необходимо получить все заработные платы таблицы EMP в двоичном виде.

**Предлагаемые решения:**

SELECT
    last_name,
    salary,
(
    SELECT
        bin
    FROM
        dual model dimension by (0 attr) measures (
            salary num,
            cast(null AS varchar2(30)) bin,
            '0123456789ABCDEF' hex
        ) rules iterate (10000) until (num [0] <= 0) (
            bin [0] = substr(hex [cv()], mod(num [cv()], 2) + 1, 1) || bin [cv()],
            num [0] = trunc(num [cv()] / 2)
        )
    ) salary_binary
FROM
    hr.employees

<center>
Результат:

</center>

В этом решении используется оператор MODEL. MODEL обладает возможностью выполнять итерации и обеспечивать доступ к значениям строк как к элементам массива. Чтобы упростить объяснение, я собираюсь показать небольшие вариа!
ции подзапроса, содержащего оператор MODEL. Приведенный далее
код является кодом подзапроса из решения за исключением того, что
здесь жестко запрограммировано возвратить двоичное представление
значения 2:

select bin
from dual
model
dimension by ( 0 attr )
measures ( 2 num,
cast(null as varchar2(30)) bin,
'0123456789ABCDEF' hex
)
rules iterate (10000) until (num[0] <= 0) (
bin[0] = substr (hex[cv()],mod(num[cv()],2)+1,1)||bin[cv()],
num[0] = trunc(num[cv()]/2)
)
BIN











10

Следующий запрос выводит значения, возвращенные в результате од ной итерации конструкции RULES из запроса выше:

select 2 start_val,
'0123456789ABCDEF' hex,
substr('0123456789ABCDEF',mod(2,2)+1,1) ||
cast(null as varchar2(30)) bin,
trunc(2/2) num
from dual
START_VAL HEX BIN NUM







































2 0123456789ABCDEF 0 1

Поле START_VAL содержит число, для которого должно быть получе!
но двоичное представление, в данном случае это 2. Значение столбца
BIN – результат операции SUBSTR над строкой ‘0123456789ABCDEF’
(в оригинальном решении HEX). Значение NUM – это признак выхода
из цикла.
Как видно из предыдущего результирующего множества, после пер!
вой итерации цикла BIN равен 0, и NUM равен 1. Поскольку значение
NUM не меньше или не равно 0, выполняется следующая итерация,
результаты которой представляет приведенное ниже выражение SQL:

select num start_val,
substr('0123456789ABCDEF',mod(1,2)+1,1) || bin bin,

trunc(1/2) num
from (
select 2 start_val,
'0123456789ABCDEF' hex,
substr('0123456789ABCDEF',mod(2,2)+1,1) ||
cast(null as varchar2(30)) bin,
trunc(2/2) num
from dual
)
START_VAL BIN NUM























1 10 0

После следущей итерации операция SUBSTR над HEX возвращает 1, к которой присоединяется предыдущее значение BIN, 0. Проверочное
значение, NUM, теперь равно 0; таким образом, это последняя итера!
ция, а возвращенное значение «10» – двоичное представление числа 2.
Разобравшись с тем, что происходит, можно убрать итерацию из опе!
ратора MODEL и строка за строкой проследить, как применяются пра!
вила для получения окончательного результирующего множества, что
показано ниже:

select 2 orig_val, num, bin
from dual
model
dimension by ( 0 attr )
measures ( 2 num,
cast(null as varchar2(30)) bin,
'0123456789ABCDEF' hex
)
rules (
bin[0] = substr (hex[cv()],mod(num[cv()],2)+1,1)||bin[cv()],
num[0] = trunc(num[cv()]/2),
bin[1] = substr (hex[0],mod(num[0],2)+1,1)||bin[0],
num[1] = trunc(num[0]/2)
)
ORIG_VAL NUM BIN





















2 1 0
2 0 10

<br>
:::

## Создание календаря

Требуется создать календарь на текущий месяц. Он должен быть отформатирован, как обычный календарь: семь столбцов в ширинуи (как правило) пять строк вниз.

**Предлагаемые решения:**

WITH x AS
(
	SELECT  *
	FROM
	(
		SELECT  to_char(trunc(sysdate,'mm')+level-1,'iw') wk
		       ,to_char(trunc(sysdate,'mm')+level-1,'dd') dm
		       ,to_number(to_char(trunc(sysdate,'mm')+level-1,'d')) dw
		       ,to_char(trunc(sysdate,'mm')+level-1,'mm') curr_mth
		       ,to_char(sysdate,'mm') mth
		FROM dual connect by level <= 31
	)
	WHERE curr_mth = mth 
)
SELECT  MAX(case dw WHEN 2 THEN dm end) Mo
       ,MAX(case dw WHEN 3 THEN dm end) Tu
       ,MAX(case dw WHEN 4 THEN dm end) We
       ,MAX(case dw WHEN 5 THEN dm end) Th
       ,MAX(case dw WHEN 6 THEN dm end) Fr
       ,MAX(case dw WHEN 7 THEN dm end) Sa
       ,MAX(case dw WHEN 1 THEN dm end) Su
FROM x
GROUP BY  wk
ORDER BY wk

<center>
Результат:


</center>

Чтобы возвратить все дни текущего месяца, используйте рекурсивный оператор CONNECT BY. Затем разбейте месяц на недели по выбранному дню с помощью выражений CASE и функций MAX:

начинаем с использования рекурсивного оператора CONNECT BY, с помощью которого получаем столько строк, сколько дней в заданном месяце. Для каждого дня месяца понадобится дополнительно получить следующие данные: порядковый номер дня месяца (под псевдонимом DM), день недели (под псевдонимом DW), текущий месяц (под псевдонимом MTH) и ISOномер недели для каждого дня месяца (под псевдонимом WK). Результаты, возвращаемые представлением Х оператора WITH для первого дня текущего месяца, показаны ниже:

<div class="wrap">
<div class="left_col">

SELECT  trunc(sysdate,'mm') dy
       ,to_char(trunc(sysdate,'mm'),'dd') dm
       ,to_char(sysdate,'mm') mth
       ,to_number(to_char(trunc(sysdate,'mm'),'d')) dw
       ,to_char(trunc(sysdate,'mm'),'iw') wk
FROM dual

</div>
<div class="right_col">

Результат:


</div>
</div>

Далее пошагово увеличиваем значение DM (перебираем дни месяца) до тех пор, пока не будут получены все дни текущего месяца. Для каждого дня месяца также возвращаем соответствующий ему день недели и ISOномер недели, в которую попадает данный день. Результаты частично показаны ниже (полная дата для каждого дня приведена для ясности):

<div class="wrap">
<div class="left_col">

WITH x AS
(
	SELECT  *
	FROM
	(
		SELECT  to_char(trunc(sysdate,'mm')+level-1,'iw') wk
		       ,to_char(trunc(sysdate,'mm')+level-1,'dd') dm
		       ,to_number(to_char(trunc(sysdate,'mm')+level-1,'d')) dw
		       ,to_char(trunc(sysdate,'mm')+level-1,'mm') curr_mth
		       ,to_char(sysdate,'mm') mth
		FROM dual connect by level <= 31
	)
	WHERE curr_mth = mth 
)
SELECT *
FROM x

</div>
<div class="right_col">

Результат:


</div>
</div>

Здесь мы получаем все дни текущего месяца, каждый из которых выведен в отдельной строке. В каждой строке представлены следующие данные: двузначный номер дня месяца, двузначный номер месяца, однозначный номер дня недели (1–7 для Вс.–Сб.) и двузначный ISOномер недели. Имея в своем распоряжении всю эту информацию, с помощью выражения CASE можно определить, на какой день недели выпадает каждое из значений столбца DM (каждый день месяца). Результаты частично показаны ниже:

<div class="wrap">
<div class="left_col">

WITH x AS
(
	SELECT  *
	FROM
	(
    SELECT  to_char(trunc(sysdate,'mm')+level-1,'iw') wk
           ,to_char(trunc(sysdate,'mm')+level-1,'dd') dm
           ,to_number(to_char(trunc(sysdate,'mm')+level-1,'d')) dw
           ,to_char(trunc(sysdate,'mm')+level-1,'mm') curr_mth
           ,to_char(sysdate,'mm') mth
    FROM dual connect by level <= 31
	)
	WHERE curr_mth = mth 
)
SELECT case dw when 2 then dm end as Mo,
       case dw when 3 then dm end as Tu,
       case dw when 4 then dm end as We,
       case dw when 5 then dm end as Th,
       case dw when 6 then dm end as Fr,
       case dw when 7 then dm end as Sa,
       case dw when 1 then dm end as Su
FROM x

</div>
<div class="right_col">

Результат:


</div>
</div>

Как видно из частично представленного результата, каждый день каждой недели возвращается в отдельной строке, но номер дня располагается в одном из семи столбцов в соответствии с днем недели. Теперь остается только собрать все дни одной недели в одну строку. Чтобы возвратить все дни недели в одной строке, используем агрегатную функцию MAX и группировку по значениям столбца WK (ISOномеру недели).

## Сведения о сотрудниках по регионам и трем цифрам

Используются таблицы стандартной демо-схемы HR.
Одной командой SELECT собрать информацию по сотрудникам, отделам, местам расположения, странам и регионам в единую таблицу. Сгруппировать сведения:
* По названию региона,
* По первым трем цифрам номера телефона сотрудника.

Вывести следующую информацию по полученным группам (семь столбцов):
* Название региона,
* Первые три цифры телефонного номера сотрудников,
* Количество сотрудников в данной группе,
* Средний оклад сотрудников группы (округлить до двух знаков после запятой),
* Количество мест расположения, где работают сотрудники данной группы,
* Количество подразделений, к которым приписаны сотрудники данной группы,
* Список названий городов, в которых работают сотрудники данной группы.

Названия городов в списке должны быть:
* Упорядочены по возрастанию
* Разделены символами ', ' ("запятая" и "пробел")
* Перед первым названием города не должно быть символов-разделителей
* После последнего названия города символов-разделителей быть не должно.

Результат должен быть отсортирован:
* По названию региона (по возрастанию),
* По первым трем цифрам номера телефона сотрудника (по возрастанию).

**Предлагаемые решения:**
* 1

WITH t1 AS (
SELECT r.region_name r_name, SUBSTR(e.phone_number, 1,3) ph_num,
e.employee_id, e.salary, l.location_id, d.department_id, l.city,
ROW_NUMBER() OVER (PARTITION BY r.region_name, SUBSTR(e.phone_number, 1,3) ORDER BY r.region_name) in_gr_num,
DENSE_RANK() OVER (ORDER BY r.region_name, SUBSTR(e.phone_number, 1,3)) gr_num,
DENSE_RANK() OVER (PARTITION BY r.region_name, SUBSTR(e.phone_number, 1,3) ORDER BY l.city) city_num 
FROM regions r, countries c, locations l, departments d, employees e
WHERE r.region_id = c.region_id
AND c.country_id = l.country_id
AND l.location_id = d.location_id
AND d.department_id = e.department_id)
--
SELECT DISTINCT r_name, ph_num,
(SELECT MAX(in_gr_num) FROM t1 WHERE gr_num = t.gr_num) c1,
(SELECT round(AVG(salary), 2) FROM t1 WHERE gr_num = t.gr_num) c2,
(SELECT count(DISTINCT location_id) FROM t1 WHERE gr_num = t.gr_num) c3, 
(SELECT count(DISTINCT department_id) FROM t1 WHERE gr_num = t.gr_num) c4,
LISTAGG(city, ', ') WITHIN GROUP (ORDER BY city) OVER (PARTITION BY gr_num) c5
FROM t1 t

* 2

SELECT r_name, ph_num, ce,ss cl,cd, LISTAGG(city,', ') WITHIN GROUP (ORDER BY city) OVER (PARTITION BY r_name,ph_num) c5 FROM (
  
  
  SELECT r.region_name r_name, SUBSTR(e.phone_number, 1,3) ph_num,
  COUNT(e.employee_id) ce, SUM(e.salary) ss, COUNT(l.location_id) cl, COUNT(d.department_id) cd, 
  LISTAGG(l.city,', ') WITHIN GROUP (ORDER BY l.city) OVER (PARTITION BY r.region_name, SUBSTR(e.phone_number, 1,3))
  FROM regions r, countries c, locations l, departments d, employees e
  WHERE r.region_id = c.region_id
  AND c.country_id = l.country_id
  AND l.location_id = d.location_id
  AND d.department_id = e.department_id
  GROUP BY (r.region_name, SUBSTR(e.phone_number, 1,3)), l.city
)

* 3

WITH t2 AS (
  SELECT DISTINCT r.region_name r_name, SUBSTR(e.phone_number, 1,3) ph_num,
  LISTAGG(l.city, ', ') WITHIN GROUP (ORDER BY l.city) OVER (PARTITION BY r.region_name, SUBSTR(e.phone_number, 1,3)) city
  FROM regions r, countries c, locations l, departments d, employees e
  WHERE r.region_id = c.region_id
  AND c.country_id = l.country_id
  AND l.location_id = d.location_id
  AND d.department_id = e.department_id
  GROUP BY r.region_name, SUBSTR(e.phone_number, 1,3), l.city),
t1 AS ( SELECT r.region_name r_name, SUBSTR(e.phone_number, 1,3) ph_num,
  COUNT(e.employee_id) ce, SUM(e.salary) ss, COUNT(l.location_id) cl, COUNT(d.department_id) cd 
  FROM regions r, countries c, locations l, departments d, employees e
  WHERE r.region_id = c.region_id
  AND c.country_id = l.country_id
  AND l.location_id = d.location_id
  AND d.department_id = e.department_id
  GROUP BY r.region_name, SUBSTR(e.phone_number, 1,3))  
--
SELECT tt1.r_name, tt1.ph_num, tt1.ce, tt1.ss, tt1.cl, tt1.cd, tt2.city
FROM t1 tt1, t2 tt2
WHERE tt1.r_name = tt2.r_name AND tt1.ph_num = tt2.ph_num

* 4

WITH t1 AS (
  SELECT r.region_name r_name, SUBSTR(e.phone_number, 1,3) ph_num,
  e.employee_id, e.salary, l.location_id, d.department_id, l.city,
  ROW_NUMBER() OVER (PARTITION BY r.region_name, SUBSTR(e.phone_number, 1,3) ORDER BY r.region_name) in_gr_num,
  DENSE_RANK() OVER (ORDER BY r.region_name, SUBSTR(e.phone_number, 1,3)) gr_num
  FROM regions r, countries c, locations l, departments d, employees e
  WHERE r.region_id = c.region_id
  AND c.country_id = l.country_id
  AND l.location_id = d.location_id
  AND d.department_id = e.department_id),  
t3 AS (SELECT DISTINCT gr_num, city FROM t1),
t4 AS (SELECT DISTINCT gr_num, city, ROW_NUMBER() OVER(PARTITION BY gr_num ORDER BY city) in_gr_num  FROM t3), 
t2 AS (
  SELECT DISTINCT r_name, ph_num,
  (SELECT MAX(in_gr_num) FROM t1 WHERE gr_num = t.gr_num) c1,
  (SELECT round(AVG(salary) ,2) FROM t1 WHERE gr_num = t.gr_num) c2,
  (SELECT count(DISTINCT location_id) FROM t1 WHERE gr_num = t.gr_num) c3, 
  (SELECT count(DISTINCT department_id) FROM t1 WHERE gr_num = t.gr_num) c4
  ,(SELECT SUBSTR(SYS_CONNECT_BY_PATH(city,', '), 3)
        FROM t4 WHERE CONNECT_BY_ISLEAF = 1 AND gr_num = t.gr_num AND ROWNUM = 1
        CONNECT BY  in_gr_num = PRIOR in_gr_num+1
        AND gr_num = PRIOR gr_num
        START WITH in_gr_num = 1) c5
  FROM t1 t)
--
SELECT * FROM t2
ORDER BY r_name, ph_num