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

-- Подзапросы в SQL
-- https://youtu.be/fm434u-FKd0

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


SELECT DISTINCT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER
FROM EMPLOYEES
WHERE MANAGER_ID IN
      (
        SELECT DISTINCT MANAGER_ID
        FROM DEPARTMENTS
        WHERE DEPARTMENT_NAME IN ('Marketing', 'Sales', 'Finance')
      )
;


SELECT DISTINCT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER
FROM EMPLOYEES
WHERE MANAGER_ID IN
      (
        SELECT DISTINCT MANAGER_ID FROM DEPARTMENTS
        WHERE DEPARTMENT_NAME = 'IT'
              AND LOCATION_ID IN
                (
                  SELECT LOCATION_ID FROM LOCATIONS
                  WHERE CITY IN ('Toronto', 'Southlake')
                )
      )
;


SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, DEPARTMENT_ID
FROM EMPLOYEES
WHERE SALARY = (SELECT MAX(SALARY) FROM EMPLOYEES)
;


SELECT b.COUNTRY_NAME, a.REGION_NAME
FROM
   REGIONS a,
   (
      SELECT COUNTRY_NAME, REGION_ID
      FROM COUNTRIES
      WHERE REGION_ID > 2
   ) b
WHERE
   a.REGION_ID = b.REGION_ID
;


SELECT b.COUNTRY_NAME, a.REGION_NAME
FROM
   REGIONS a
   INNER JOIN
     (
        SELECT COUNTRY_NAME, REGION_ID
        FROM COUNTRIES
        WHERE REGION_ID > 2
     ) b
    ON a.REGION_ID = b.REGION_ID
;


SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN
  (
    SELECT DEPARTMENT_ID FROM DEPARTMENTS WHERE LOCATION_ID BETWEEN 1700 AND 2000
    UNION
    SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE LAST_NAME LIKE '%ing%'
  )
ORDER BY EMPLOYEE_ID
;


SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN
  (
    SELECT DEPARTMENT_ID
    FROM EMPLOYEES
    WHERE LAST_NAME LIKE '%ing%'
    ORDER BY DEPARTMENT_ID
  )
;


SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, DEPARTMENT_ID
FROM EMPLOYEES
WHERE EXISTS
      (
        SELECT DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID
        FROM DEPARTMENTS
        WHERE EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID
      )
;


SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, DEPARTMENT_ID
FROM EMPLOYEES
WHERE NOT EXISTS
      (
        SELECT DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID
        FROM DEPARTMENTS
        WHERE EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID
      )
;