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

-- Полезные функции и операторы в SQL
-- https://youtu.be/SglQ7CCkkTw

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


CREATE TABLE KMV_TEST_COLUMNS AS
SELECT * FROM course_program
;


SELECT * FROM KMV_TEST_COLUMNS;


ALTER TABLE KMV_TEST_COLUMNS ADD NEW_COLUMN varchar2(3);
commit;


SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = 'KMV_TEST_COLUMNS'
;


CREATE TABLE KMV_TEST_COLUMNS_2 AS
SELECT NUM, NEW_COLUMN, THEME FROM KMV_TEST_COLUMNS;
;


ALTER TABLE KMV_TEST_COLUMNS MODIFY NEW_COLUMN NUMBER;
commit;


ALTER TABLE KMV_TEST_COLUMNS RENAME COLUMN NEW_COLUMN TO NEW_COLUMN_2;
commit;


ALTER TABLE KMV_TEST_COLUMNS DROP COLUMN NEW_COLUMN_2;
commit;


SELECT * FROM KMV_NUMBER;
SELECT RND_NUM, to_char(RND_NUM,'999G999G999G999G999') AS RND_NUM_NEW FROM KMV_NUMBER;
SELECT RND_NUM, to_char(RND_NUM,'999G999G999G999G999D00') AS RND_NUM_NEW FROM KMV_NUMBER;
SELECT RND_NUM, to_char(RND_NUM,'999G999G999G999G999D00', 'NLS_NUMERIC_CHARACTERS = ,`') AS RND_NUM_NEW FROM KMV_NUMBER;
SELECT DISTINCT HIRE_DATE, to_char(HIRE_DATE, 'IW', 'nls_date_language = russian') AS WEEK_NUMBER FROM HR.EMPLOYEES;


SELECT DISTINCT DEPARTMENT_ID, DEPARTMENT_NAME
FROM HR.DEPARTMENTS
WHERE DEPARTMENT_ID BETWEEN 90 AND 150
;

SELECT DISTINCT FIRST_NAME, LAST_NAME, DEPARTMENT_ID
FROM HR.EMPLOYEES
WHERE DEPARTMENT_ID BETWEEN 90 AND 150
;


WITH
TEMP_1 AS
  (
    SELECT DISTINCT DEPARTMENT_ID, DEPARTMENT_NAME
    FROM HR.DEPARTMENTS
    WHERE DEPARTMENT_ID BETWEEN 90 AND 150
  ),
TEMP_2 AS
  (
    SELECT DISTINCT FIRST_NAME, LAST_NAME, DEPARTMENT_ID
    FROM HR.EMPLOYEES
    WHERE DEPARTMENT_ID BETWEEN 90 AND 150
  )
--select * from TEMP_1
--select * from TEMP_2
SELECT a.*, b.DEPARTMENT_NAME
FROM
   TEMP_2 a
   LEFT JOIN TEMP_1 b ON a.DEPARTMENT_ID = b.DEPARTMENT_ID
;