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

-- Условные операторы и функции в SQL (NULLIF, NVL, NVL2, DECODE, CASE)
-- https://youtu.be/Gl2o4d6kqSM

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


SELECT NULLIF(5, 2) AS RESULT FROM DUAL;
SELECT NULLIF(5, 5) AS RESULT FROM DUAL;


SELECT NULLIF('март', 'апрель') AS RESULT FROM DUAL;
SELECT NULLIF('апрель', 'апрель') AS RESULT FROM DUAL;


SELECT nvl(NULL, 'ваше значение') AS RESULT FROM DUAL;
SELECT nvl('тест', 'ваше значение') AS RESULT FROM DUAL;


SELECT LOCATION_ID, CITY, STATE_PROVINCE, nvl(STATE_PROVINCE, 'штат не определен') AS CHECK_NULL
FROM LOCATIONS
WHERE LOCATION_ID <= 1500
;


SELECT a.EMPLOYEE_ID, a.DEPARTMENT_ID, b.DEPARTMENT_NAME, nvl(b.DEPARTMENT_NAME, 'Administration')
FROM
   EMPLOYEES a
   LEFT OUTER JOIN DEPARTMENTS b ON a.DEPARTMENT_ID = b.DEPARTMENT_ID
WHERE a.EMPLOYEE_ID IN (108, 178, 205, 206)
;


SELECT a.EMPLOYEE_ID, a.DEPARTMENT_ID, b.DEPARTMENT_NAME, nvl(b.DEPARTMENT_NAME, c.DEPARTMENT_NAME)
FROM
   EMPLOYEES a
   LEFT OUTER JOIN DEPARTMENTS b ON a.DEPARTMENT_ID = b.DEPARTMENT_ID
   LEFT OUTER JOIN KMV_EMPLOYEES c ON a.EMPLOYEE_ID = c.EMPLOYEE_ID
WHERE a.EMPLOYEE_ID IN (108, 178, 205, 206)
;


SELECT nvl2(NULL, 'значение известно', 'значение не определено') AS RESULT FROM DUAL;
SELECT nvl2('тест', 'значение известно', 'значение не определено') AS RESULT FROM DUAL;


SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, COMMISSION_PCT, nvl2(COMMISSION_PCT, SALARY, SALARY * 2) AS RESULT
FROM EMPLOYEES
WHERE EMPLOYEE_ID BETWEEN 177 AND 183
;


SELECT
    DEPARTMENT_ID,
    decode(DEPARTMENT_ID, 10, 'Administration') AS DEP_NAME
FROM DEPARTMENTS
WHERE DEPARTMENT_ID <= 50
;


SELECT
    DEPARTMENT_ID,
    decode(DEPARTMENT_ID, 10, 'Administration', 'Marketing') AS DEP_NAME
FROM DEPARTMENTS
WHERE DEPARTMENT_ID <= 50
;


SELECT
    DEPARTMENT_ID,
    decode(DEPARTMENT_ID,
        10, 'Administration',
        20, 'Marketing',
        30, 'Purchasing',
        40, 'Human Resources',
        50, 'Shipping'
        ) AS DEP_NAME
FROM DEPARTMENTS
WHERE DEPARTMENT_ID <= 50
;


SELECT
    DEPARTMENT_ID,
    decode(DEPARTMENT_ID, 10, 'Administration', 20, 'Marketing', 30, 'Purchasing', 40, 'Human Resources', 50, 'Shipping') AS DEP_NAME
FROM DEPARTMENTS
WHERE DEPARTMENT_ID <= 50
;


SELECT
    DEPARTMENT_ID,
    decode(DEPARTMENT_ID,
        10, 'Administration',
        20, 'Marketing',
        30, 'Purchasing',
        40, 'Human Resources',
        50, 'Shipping',
        'Marketing'
        ) AS DEP_NAME
FROM DEPARTMENTS
WHERE DEPARTMENT_ID <= 80
;


SELECT
    DEPARTMENT_ID,
    decode(DEPARTMENT_ID,
        10, 'Administration',
        20, 'Marketing',
        30, 'Purchasing',
        40, 'Human Resources',
        50, 'Shipping',
        'Marketing'
        ) AS DEP_NAME,
    CASE DEPARTMENT_ID
        WHEN 10 THEN 'Administration'
        WHEN 20 THEN 'Marketing'
        WHEN 30 THEN 'Purchasing'
        WHEN 40 THEN 'Human Resources'
        WHEN 50 THEN 'Shipping'
        ELSE 'Marketing'
    END AS DEP_NAME_2
FROM DEPARTMENTS
WHERE DEPARTMENT_ID <= 80
;


SELECT
    DEPARTMENT_ID,
    CASE
        WHEN DEPARTMENT_ID = 10 THEN 'Administration'
        WHEN DEPARTMENT_ID = 20 THEN 'Marketing'
        WHEN DEPARTMENT_ID = 30 THEN 'Purchasing'
        WHEN DEPARTMENT_ID = 40 THEN 'Human Resources'
        WHEN DEPARTMENT_ID = 50 THEN 'Shipping'
        ELSE 'Marketing'
    END AS DEP_NAME
FROM DEPARTMENTS
WHERE DEPARTMENT_ID <= 80
;


SELECT
    DEPARTMENT_ID,
    CASE
        WHEN DEPARTMENT_ID = 10 THEN 'Administration'
        WHEN DEPARTMENT_ID BETWEEN 20 AND 29 THEN 'Marketing'
        WHEN DEPARTMENT_ID LIKE '%30%' THEN 'Purchasing'
        WHEN DEPARTMENT_ID IN (40, 50) THEN 'Human Resources'
        ELSE 'Marketing'
    END AS DEP_NAME
FROM DEPARTMENTS
WHERE DEPARTMENT_ID <= 80
;


SELECT
    DEPARTMENT_ID,
    CASE
        WHEN DEPARTMENT_ID = 10 AND MANAGER_ID <= 200 THEN 'Administration'
        WHEN DEPARTMENT_ID BETWEEN 20 AND 29 THEN 'Marketing'
        WHEN DEPARTMENT_ID LIKE '%30%' OR LOCATION_ID > 1500 THEN 'Purchasing'
        WHEN DEPARTMENT_ID IN (40, 50) THEN 'Human Resources'
        ELSE 'Marketing'
    END AS DEP_NAME
FROM DEPARTMENTS
WHERE DEPARTMENT_ID <= 80
;


SELECT
    DEPARTMENT_ID,
    CASE WHEN DEPARTMENT_ID = 10 THEN 'Administration' ELSE 'Marketing' END AS DEP_NAME
FROM DEPARTMENTS
WHERE DEPARTMENT_ID <= 80
;


SELECT
    DEPARTMENT_ID,
    CASE
        WHEN DEPARTMENT_ID = 10 THEN
             CASE WHEN MANAGER_ID < 500 THEN 'Administration' ELSE 'Marketing' END
        ELSE 'Marketing'
    END AS DEP_NAME
FROM DEPARTMENTS
WHERE DEPARTMENT_ID <= 80
;