19.01.22

CREATE TABLE DEPARTMENTS
(
    DEPARTMENT_ID INT NOT NULL,
    NAME VARCHAR(30) NOT NULL,
    MANAGER_ID INT,
    COUNTRY_ID CHAR(2) NOT NULL,
    CITY VARCHAR(30) NOT NULL,
    STATE VARCHAR(25),
    ADDRESS VARCHAR(40),
    POSTAL_CODE VARCHAR(12),

    CONSTRAINT PK_DEPARTMENTS PRIMARY KEY (DEPARTMENT_ID),
    CONSTRAINT FK_DEPT_COUNTRIES FOREIGN KEY (COUNTRY_ID)REFERENCES COUNTRIES(COUNTRY_ID),
    CONSTRAINT FK_DEPT_MANGR FOREIGN KEY (MANAGER_ID)REFERENCES EMPLOYEES(EMPLOYEE_ID)
)

ALTER TABLE EMPLOYEES
ADD CONSTRAINT FK_EMPL_DEPT FOREIGN KEY (DEPARTMENT_ID)
REFERENCES DEPARTMENTS(DEPARTMENT_ID)

CREATE TABLE ORDERS
(
    ORDER_ID INT NOT NULL PRIMARY KEY,
    ORDER_DATE DATETIME NOT NULL,
    CUSTOMER_ID NUMERIC(6) NOT NULL REFERENCES CUSTOMERS,
    EMPLOYEE_ID INT NOT NULL REFERENCES EMPLOYEES,
    SHIP_ADDRESS VARCHAR(150)
)

CREATE TABLE PRODUCTS
(
    PRODUCT_ID INT NOT NULL PRIMARY KEY,
    NAME VARCHAR(70) NOT NULL,
    PRICE NUMERIC(8,2) NOT NULL,
    DESCR VARCHAR(2000)
)

CREATE TABLE ORDER_ITEMS
(
    ORDER_ID INT NOT NULL,
    PRODUCT_ID INTEGER NOT NULL,
    UNIT_PRICE NUMERIC(8,2) NOT NULL,
    QUANTITY NUMERIC(8) NOT NULL,

    CONSTRAINT PK_OI PRIMARY KEY(ORDER_ID, PRODUCT_ID),

    CONSTRAINT FK_OI_O FOREIGN KEY (ORDER_ID)REFERENCES ORDERS (ORDER_ID)
            ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT FK_OI_P FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCTS(PRODUCT_ID)
)

---------------------------------------------------------------
-----------------------TRADECOMPANY ---------------------------
---------------------------------------------------------------

--Демо:
    --Въведи по един запис във всяка от таблиците

    --INSERT INTO TABLE (COLUMN1, COLUM2)
    --VALUES ('', 4)

    INSERT INTO REGIONS (NAME)
    VALUES('Източна Европа')

    SELECT * FROM REGIONS

    INSERT INTO COUNTRIES(COUNTRY_ID, NAME, REGION_ID)
    VALUES('BG', 'България', 1)

    SELECT * FROM COUNTRIES

    INSERT INTO CUSTOMERS(CUSTOMER_ID, COUNTRY_ID, FNAME, LNAME, EMAIL, ADDRESS, GENDER)
    VALUES(1001, 'BG', 'Мария', 'Милева', 'mm@abv.bg', 'бул. България 100, гр.Пловдив', 'F')

    SELECT * FROM CUSTOMERS

    INSERT INTO JOBS(JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY)
    VALUES('SA_rep', 'Търговски Представител', 9000, 10000)

    SELECT * FROM JOBS

    INSERT INTO DEPARTMENTS(DEPARTMENT_ID, NAME, COUNTRY_ID, CITY, STATE, ADDRESS, POSTAL_CODE)
    VALUES(80, 'Продажби', 'BG', 'Пловдив', 'Пловдив', 'бул. Марица 10, гр.Пловдив','4000')

    SELECT * FROM DEPARTMENTS

    INSERT INTO EMPLOYEES(EMPLOYEE_ID, FNAME, LNAME, EMAIL, PHONE, HIRE_DATE, SALARY, JOB_ID, DEPARTMENT_ID)
    VALUES(1501, 'Петър', 'Петров', 'peter@gmail.com', '0892 345 3456', CONVERT(DATE, '19-01-2022', 105), 9000, 'SA_rep', 80)

    SELECT * FROM EMPLOYEES

    INSERT INTO PRODUCTS(PRODUCT_ID, NAME, PRICE, DESCR)
    VALUES(20001, 'FLASH ADATA', 30, 'FLASH ADATA 2GB')

    SELECT * FROM PRODUCTS

    INSERT INTO ORDERS(ORDER_ID, ORDER_DATE , CUSTOMER_ID, EMPLOYEE_ID, SHIP_ADDRESS)
    VALUES(1, CONVERT(DATETIME, '20-01-2022 10:00', 105), 1001, 1501, 'бул. Марица 10, гр.Пловдив')

    SELECT * FROM ORDERS

    INSERT INTO ORDER_ITEMS(PRODUCT_ID, ORDER_ID, UNIT_PRICE, QUANTITY)
    VALUES(20001, 1, 29.98, 20)

    SELECT * FROM ORDER_ITEMS

--Демо:
    --Промяна на заплатата на служител с даден идентификатор:
    UPDATE EMPLOYEES
    SET SALARY = 9500
    WHERE EMPLOYEE_ID = 1501

    SELECT * FROM EMPLOYEES


--Демо:
    --Поръчката е анулирана -  изтрий реда и от таблицата ORDERS:

    --БЕЗ ----> ON DELETE CASCADE
    --DELETE FROM ORDER_ITEMS ; DELETE FROM ORDERS

    --СЪЗДАДОХМЕ----> ON DELETE CASCADE ....(PARENT TABLE --> CHILD TABLES)
    DELETE FROM ORDERS
    WHERE ORDER_ID = 1

    SELECT * FROM ORDER_ITEMS
    SELECT * FROM ORDERS


/* Задачи:
Задача 3-1.
    Да се изтрият всички редове от всички таблици в базата от данни, след което да
    се въведат данните чрез командите от inserts_mssql.sql файла. */

    DELETE FROM PRODUCTS
    DELETE FROM EMPLOYEES
    DELETE FROM DEPARTMENTS
    DELETE FROM JOBS
    DELETE FROM CUSTOMERS
    DELETE FROM COUNTRIES
    DELETE FROM REGIONS

    SELECT * FROM PRODUCTS
    SELECT * FROM ORDER_ITEMS
    SELECT * FROM ORDERS
    SELECT * FROM EMPLOYEES
    SELECT * FROM DEPARTMENTS
    SELECT * FROM JOBS
    SELECT * FROM CUSTOMERS
    SELECT * FROM COUNTRIES
    SELECT * FROM REGIONS

/*Задача 3-2.
    Да се увеличи количеството с 2 броя и да се намали единичната цена с 5% на
    продукт с идентификатор 2254 в поръчка с идентификатор 2354.*/
    SELECT *
    FROM ORDER_ITEMS
    WHERE PRODUCT_ID = 2254 AND ORDER_ID = 2354

    UPDATE ORDER_ITEMS
    SET QUANTITY += 2, UNIT_PRICE *= 0.95
    WHERE PRODUCT_ID = 2254 AND ORDER_ID = 2354


--Задача 3-3.
    --Да се изтрие служител с идентификатор 183.

    DELETE FROM EMPLOYEES
    WHERE EMPLOYEE_ID = 183

    SELECT * FROM EMPLOYEES
    WHERE EMPLOYEE_ID = 183

-------------------------------------------------------------------------------------------
--Пример 4-1. -- Д.Р.
    --Да се изведат имената, датите на назначаване и заплатите на всички служители.

/*Пример 4-2. -- Д.Р.
    Да се изведат всички данни за продуктите, с цена по-голяма от 2000. Резултатът
    нека бъде подреден по цена на продукт възходящо.*/

--Пример 4-3.
    --Да се изведе броя на всички служители.
    SELECT * FROM EMPLOYEES

    --1,2,3 .. N ROWS ---> 1 VALUE
    --AGGREG: COUNT(*),  COUNT(COLUMN), SUM(), MAX(), MIN(), AVG()

    SELECT COUNT(EMPLOYEE_ID) AS  [броя на всички служители.]
    FROM EMPLOYEES

--Пример 4-4.  -- Д.Р
    --Да се изведе броя служители, групирани по отдела, в който работят.

-------------------------------------------------------------------------------------------
--4.2.1. Задачи
--Задача 4-1. -- Д.Р
    --Да се изведат имената, заплатите и идентификаторите на длъжностите на
    --служителите, работещи в отдели 50 и 80. Резултатът да е подреден по фамилия на служител във възходящ ред.

--Задача 4-2.
    --Да се изведат общата сума на заплатите и броя служители в отдел 60.
    SELECT COUNT(EMPLOYEE_ID) AS [ броя служители в отдел 60]
           , SUM(SALARY) AS 'общата сума на заплатитев отдел 60'
    FROM EMPLOYEES
    WHERE DEPARTMENT_ID = 60

    --SELECT COLUMN1, COLUMN2 , AGG_FUNCTION(COLUMN3)
    --FROM TABLE_NAME
    --WHERE CONDITION1 AND  CONDITION2
    --GROUP BY COLUMN1, COLUMN2
    --ORDER BY COLUMN1 ASC/DESC

--Задача 4-3.
    --За всички поръчки да се изведат идентификатор на поръчка и обща стойност на поръчката. (ЕД.ЦЕНА*КОЛИЧЕСТВО)
    -- Резултатът да е подреден по стойност на поръчката в низходящ ред.

    SELECT ORDER_ID, SUM(UNIT_PRICE*QUANTITY) AS [обща стойност на поръчката]
    FROM ORDER_ITEMS
    GROUP BY ORDER_ID
    --ORDER BY [обща стойност на поръчката] DESC
    ORDER BY 2 DESC