DAY1_MASTERS

----comment

/*
c
o
m
m
e
n
t
*/
---------------------------------------
-----------------1. DDL----------------
---------------------------------------

---------------------------------------
---------------table Students----------
---------------------------------------
CREATE DATABASE MY_FIRST_DB
---------------------------------------
USE  MY_FIRST_DB
---------------------------------------
CREATE TABLE STUDENTS
(
    FAC_NUM INT NOT NULL,
    NAME VARCHAR (50) NOT NULL,
    PHONE VARCHAR (15)
)
---------------------------------------
ALTER TABLE STUDENTS
ADD ADDRESS VARCHAR(50)
---------------------------------------
ALTER TABLE STUDENTS
ALTER COLUMN ADDRESS TEXT
---------------------------------------
ALTER TABLE STUDENTS
DROP COLUMN PHONE
---------------------------------------
DROP TABLE STUDENTS



---------------------------------------
-----------------2. DML----------------
---------------------------------------
INSERT INTO STUDENTS(FAC_NUM, NAME, PHONE )
             VALUES (19000, 'IVAN IVANOV', '08934566')
---------------------------------------
INSERT INTO STUDENTS(FAC_NUM, NAME, PHONE )
            VALUES  (19001, 'IVAN IVANOV', '08934566'),
                    (19002, 'IVA', '08934576'),
                    (19003, 'NOVA', '089367766')
---------------------------------------
INSERT INTO STUDENTS
              VALUES (19004, 'MIRA', NULL )


SELECT * FROM STUDENTS

---------------------------------------
UPDATE STUDENTS
SET NAME = 'RENI'
WHERE FAC_NUM = 19001
---------------------------------------
DELETE FROM STUDENTS
WHERE FAC_NUM = 19000



---------------------------------------
-------------3. table COUNTRIES-----------
---------------------------------------

CREATE DATABASE SEC_DB
---------------------------------------
USE SEC_DB
---------------------------------------
CREATE TABLE COUNTRIES
(
    COUNTRY_CODE CHAR(3) NOT NULL PRIMARY KEY,
    NAME VARCHAR(40) NOT NULL,
    POPULATION INT
)
---------------------------------------
ALTER TABLE COUNTRIES
ADD PHONE_CODE CHAR(3)
---------------------------------------
INSERT INTO COUNTRIES(COUNTRY_CODE, NAME, POPULATION, PHONE_CODE)
               VALUES('BGN', 'БЪЛГАРИЯ', 7500000, '359')

SELECT * FROM COUNTRIES
---------------------------------------
UPDATE COUNTRIES
SET POPULATION = 6000000
WHERE COUNTRY_CODE='BGR'
---------------------------------------
DELETE FROM COUNTRIES
WHERE COUNTRY_CODE='BGR'
---------------------------------------
DELETE FROM COUNTRIES
---------------------------------------
ALTER TABLE COUNTRIES
DROP COLUMN PHONE_CODE
---------------------------------------
DROP TABLE COUNTRIES
---------------------------------------
USE MASTER
DROP DATABASE SEC_DB


---------------------------------------
-----------4. TRADE_MASTERS---------------
---------------------------------------

CREATE DATABASE TRADE_MASTERS
---------------------------------------
USE TRADE_MASTERS
---------------------------------------
ALTER DATABASE CURRENT COLLATE CYRILLIC_GENERAL_CI_AI;
---------------------------------------
CREATE TABLE REGIONS
(
    REGION_ID SMALLINT NOT NULL PRIMARY KEY IDENTITY(1,1),
    NAME VARCHAR(25) NOT NULL UNIQUE
)


CREATE TABLE COUNTRIES
(
    COUNTRY_ID CHAR(2) NOT NULL,
    NAME VARCHAR(40) NOT NULL,
    REGION_ID SMALLINT NULL,

    CONSTRAINT PK_COUNTRIES PRIMARY KEY (COUNTRY_ID),
    CONSTRAINT FK_COUNTRIES_REGIONS FOREIGN KEY (REGION_ID)
    REFERENCES REGIONS (REGION_ID)
)

CREATE TABLE CUSTOMERS
(
    CUSTOMER_ID NUMERIC(6) NOT NULL,
    COUNTRY_ID CHAR(2) NOT NULL,
    FNAME VARCHAR(20) NOT NULL,
    LNAME VARCHAR(20) NOT NULL,
    ADDRESS TEXT NULL,
    EMAIL VARCHAR(30) NULL,
    GENDER CHAR(1) NULL DEFAULT 'M'
    CONSTRAINT CUST_GENDER CHECK( GENDER IS NULL OR (GENDER IN('M','F'))),
    CONSTRAINT OK_CUSTOMERS PRIMARY KEY(CUSTOMER_ID)
)

ALTER TABLE CUSTOMERS
ADD CONSTRAINT FK_CUSTOMERS_COUNTRIES FOREIGN KEY (COUNTRY_ID)
REFERENCES COUNTRIES(COUNTRY_ID)

CREATE TABLE JOBS
(
    JOB_ID VARCHAR(10) NOT NULL PRIMARY KEY,
    JOB_TITLE VARCHAR(35) NOT NULL,
    MIN_SALARY NUMERIC(6) NULL,
    MAX_SALARY NUMERIC(6) NULL
)

CREATE TABLE EMPLOYEES
(
    EMPLOYEE_ID INT NOT NULL,
    FNAME VARCHAR(20) NOT NULL,
    LNAME VARCHAR(25) NOT NULL,
    EMAIL VARCHAR(40) NOT NULL ,
    PHONE VARCHAR(20) NULL,
    HIRE_DATE DATETIME NOT NULL,
    SALARY NUMERIC(8,2) NOT NULL CONSTRAINT SALARY_CHECK CHECK(SALARY>0),
    JOB_ID VARCHAR(10) NOT NULL,
    MANAGER_ID INT NULL,
    DEPARTMENT_ID INT NULL,
    CONSTRAINT PK_EMPLOYEES PRIMARY KEY(EMPLOYEE_ID),
    CONSTRAINT UK_EMAIL UNIQUE(EMAIL),
    CONSTRAINT FK_EMPLOYEES_JOBS FOREIGN KEY (JOB_ID)
        REFERENCES JOBS(JOB_ID),
    CONSTRAINT FK_EMPLOYEES_MANAGERS FOREIGN KEY (MANAGER_ID)
        REFERENCES EMPLOYEES(EMPLOYEE_ID)
)

CREATE TABLE DEPARTMENTS
(
    DEPARTMENT_ID INT NOT NULL,
    NAME VARCHAR(30) NOT NULL,
    MANAGER_ID INT NULL,
    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_MGR FOREIGN KEY (MANAGER_ID)
        REFERENCES EMPLOYEES(EMPLOYEE_ID),
    CONSTRAINT FK_DEPT_COUNTRIES FOREIGN KEY (COUNTRY_ID)
        REFERENCES COUNTRIES(COUNTRY_ID)
)

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


CREATE TABLE ORDERS
(
    ORDER_ID INT NOT NULL PRIMARY KEY,
    ORDER_DATE DATETIME NOT NULL,
    CUSTOMER_ID NUMERIC(6) NOT NULL FOREIGN KEY REFERENCES CUSTOMERS,
    EMPLOYEE_ID INT NOT NULL FOREIGN KEY 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) NULL
)

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_ORDER_ITEMS PRIMARY KEY(ORDER_ID, PRODUCT_ID),
    CONSTRAINT FK_OI_ORDERS FOREIGN KEY(ORDER_ID)
        REFERENCES ORDERS(ORDER_ID) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT FK_OI_PRODUCTS FOREIGN KEY(PRODUCT_ID)
        REFERENCES PRODUCTS(PRODUCT_ID)
)


---------------------------------------
--5. INSERTS into TRADE_MASTERS tables-
---------------------------------------
INSERT INTO REGIONS(NAME) VALUES('ИЗТОЧНА ЕВРОПА')

INSERT INTO COUNTRIES(COUNTRY_ID, NAME, REGION_ID)
VALUES ('BG', 'БЪЛГАРИЯ', 1)

INSERT INTO CUSTOMERS(CUSTOMER_ID, COUNTRY_ID, FNAME,LNAME, ADDRESS, EMAIL,GENDER)
VALUES(1001, 'BG', 'ИВАН','ГЕНЕВ', 'БУЛ. БЪЛГАРИЯ 100', '[email protected]', 'M')

INSERT INTO JOBS(JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY)
VALUES('SA_REP', 'ТЪРГОВСКИ ПРЕДСТАВИТЕЛ', 9000, 17000)

INSERT INTO DEPARTMENTS(DEPARTMENT_ID, NAME, COUNTRY_ID, CITY, STATE, ADDRESS, POSTAL_CODE)
VALUES (80, 'ПРОДАЖБИ', 'BG', 'ПЛОВДИВ', 'ПЛОВДИВ','БУЛ.МАРИЦА 10', '4000')

INSERT INTO EMPLOYEES(EMPLOYEE_ID, FNAME, LNAME, EMAIL, PHONE, HIRE_DATE,JOB_ID, SALARY, DEPARTMENT_ID)
VALUES(1501, 'ПЕТЪР','ТОДОРОВ', '[email protected]','08956778', CONVERT(DATE, '13-02-2021', 105), 'SA_REP', 1900, 80)

INSERT INTO PRODUCTS(PRODUCT_ID, NAME, DESCR, PRICE)
VALUES(20001, 'SAMSUNG GALAXY', 'BLACK 128GB', 1400)

INSERT INTO ORDERS(ORDER_ID, CUSTOMER_ID, EMPLOYEE_ID, ORDER_DATE)
VALUES(1, 1001, 1501, CONVERT(DATETIME, '12-02-2021 10:30', 105))

INSERT INTO ORDER_ITEMS(ORDER_ID, PRODUCT_ID, UNIT_PRICE, QUANTITY)
VALUES(1, 20001, 1500, 2)

SELECT * FROM EMPLOYEES
---------------------------------------
UPDATE EMPLOYEES
SET SALARY = 2000
WHERE EMPLOYEE_ID = 1501
---------------------------------------
--DELETE FROM ORDER_ITEMS
--WHERE

DELETE FROM ORDERS
WHERE ORDER_ID = 1
---------------------------------------
DELETE FROM PRODUCTS
DELETE FROM EMPLOYEES
DELETE FROM DEPARTMENTS
DELETE FROM CUSTOMERS
DELETE FROM COUNTRIES
DELETE FROM REGIONS
DELETE FROM JOBS

SELECT * FROM EMPLOYEES

-- след това достъпихме линка с готовите
--  инсърти отворихме го в ново query и execute