Расширенные возможности SQL - Команд SQL в рамках урока #10

-- Оконные функции в SQL (Агрегатные функции)
-- https://youtu.be/9nN_g4eIEgo

-------------------------------------------
-- Расширенные возможности SQL. Примеры команд SQL в рамках урока #10.


SELECT FIRST_NAME, LAST_NAME, JOB_ID, SALARY
FROM employees;


SELECT a.FIRST_NAME, a.LAST_NAME, a.JOB_ID, a.SALARY, b.MAX_SALARY
FROM
    employees a
    LEFT JOIN
        (
            SELECT JOB_ID, MAX(SALARY) AS MAX_SALARY FROM employees
            GROUP BY JOB_ID
        ) b ON a.JOB_ID = b.JOB_ID
;


SELECT
    FIRST_NAME, LAST_NAME, JOB_ID, SALARY,
    MAX(SALARY) OVER (PARTITION BY JOB_ID) AS MAX_SALARY
FROM employees
;


SELECT
    FIRST_NAME, LAST_NAME, JOB_ID, SALARY,
    MAX(SALARY) OVER () AS MAX_SALARY
FROM employees
;


SELECT FIRST_NAME, LAST_NAME, JOB_ID, SALARY, MAX(SALARY)
FROM employees
GROUP BY FIRST_NAME, LAST_NAME, JOB_ID, SALARY
;


SELECT
    a.FIRST_NAME, a.LAST_NAME, b.DEPARTMENT_NAME,
    a.SALARY,
    round(a.SALARY / SUM(SALARY) OVER (PARTITION BY b.DEPARTMENT_NAME),2) AS PR_SALARY,
    SUM(SALARY) OVER (PARTITION BY b.DEPARTMENT_NAME) AS SUM_DEPARTMENT_SALARY
FROM
    employees a
    LEFT JOIN departments b ON a.DEPARTMENT_ID = b.DEPARTMENT_ID
;