решения ден 1 зад

--коментар

/*
1
2
*/

----------------------------------
------------DDL-------------------
----------------------------------
CREATE DATABASE MY_DB
USE MY_DB

CREATE TABLE STUDENTS
(
    F_NUM VARCHAR(10) NOT NULL,
    NAME VARCHAR(50) NOT NULL,
    PHONE VARCHAR(15) NULL           --null is by DEFAULT
)

--добави нова колона в таблица:
ALTER TABLE STUDENTS
ADD ADDRESS VARCHAR(50)

--промени типа на колона в таблица:
ALTER TABLE STUDENTS
ALTER COLUMN ADDRESS TEXT

--изтрий коона от таблица:
ALTER TABLE STUDENTS
DROP COLUMN PHONE

--изтрий таблица:
DROP TABLE STUDENTS

--изтрий база данни:
--DROP DATABASE MY_DB


----------------------------------
------------DML-------------------
----------------------------------
CREATE TABLE STUDENTS
(
    F_NUM VARCHAR(10) NOT NULL,
    NAME VARCHAR(50) NOT NULL,
    PHONE VARCHAR(15) NULL,
    GENDER CHAR(1) DEFAULT 'M'
        CHECK(GENDER IS NULL OR (GENDER IN ('M','F')))
)

---въведи записи:
INSERT INTO STUDENTS(F_NUM,  NAME, PHONE, GENDER)
VALUES (1, 'IVAN IVANOV', '089898989', NULL)

INSERT INTO STUDENTS(F_NUM,  NAME)
VALUES (1, 'MIRO ILIEV')

INSERT INTO STUDENTS
VALUES (2, 'MIRA ILIEVA', '08956365', 'F')

INSERT INTO STUDENTS
VALUES (3, 'ILIYA ILIEV', '089563658', 'M'),
       (4, 'INA STOEVA', '089563658', 'F'),
       (5, 'NORA IVANOVA', NULL, 'F')

SELECT * FROM STUDENTS

--промени пола при ф.номер 1
UPDATE STUDENTS
SET GENDER = 'M'
WHERE F_NUM = 1

--изтрий студент с ф.номер 1
DELETE FROM STUDENTS
WHERE F_NUM = 1

--добави първичен ключ към таблица (за предпочитане се добавя още при създаването на таблицата)
ALTER TABLE STUDENTS
ADD CONSTRAINT PK_STUDENTS PRIMARY KEY (F_NUM)

--вече таблицата има

----------------------------------
-------TRADECOMPANY DATABASE------
----------------------------------
---СЪЗДАВАМЕ БАЗА ДАННИ:
CREATE DATABASE TRADECOMPANY

--ДОСТЪПВАМЕ Я:
USE TRADECOMPANY

--ПРОМЕНЯМЕ КОЛАЦИЯТА И (АКО ДАННИТЕ, КОИТО ЩЕ ВЪВЕЖДАМЕ ЩЕ БЪДАТ НА КИРИЛИЦА)
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 PRIMARY KEY,
    NAME VARCHAR(40) NOT NULL,
    REGION_ID SMALLINT NULL,
    CONSTRAINT FK_COUNTRIES_REGIONS FOREIGN KEY (REGION_ID)
        REFERENCES REGIONS(REGION_ID)
)

CREATE TABLE CUSTOMERS
(
    CUSTOMER_ID NUMERIC(6) NOT NULL PRIMARY KEY,
    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' CHECK (GENDER IS NULL OR (GENDER IN('M','F')))
)

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 PRIMARY KEY,
    FNAME VARCHAR(20) NOT NULL,
    LNAME VARCHAR(25) NOT NULL,
    EMAIL VARCHAR(40)  NOT NULL UNIQUE,
    PHONE VARCHAR(20)  NULL,
    HIRE_DATE DATETIME  NOT NULL,
    SALARY NUMERIC(8,2) NOT NULL CHECK(SALARY >0),
    JOB_ID VARCHAR(10) NOT NULL,
    MANAGER_ID INT NULL,
    DEPARTMENT_ID INT NULL,

    CONSTRAINT FK_EMPL_JOB FOREIGN KEY (JOB_ID)
        REFERENCES JOBS(JOB_ID),
    CONSTRAINT FK_EMPL_MNG 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_DEPT PRIMARY KEY (DEPARTMENT_ID),
    CONSTRAINT FK_DEPT_COUNTRY FOREIGN KEY (COUNTRY_ID)
        REFERENCES COUNTRIES(COUNTRY_ID),
    CONSTRAINT FK_DEPT_MNG 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 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 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) NULL,

    --CONSTRAINT FK_ORD_CUST FOREIGN KEY (CUSTOMER_ID)
    --REFERENCES CUSTOMERS(CUSTOMER_ID),
    --CONSTRAINT FK_ORD_EMP FOREIGN KEY (EMPLOYEE_ID)
    --REFERENCES EMPLOYEES(EMPLOYEE_ID)
)

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

    CONSTRAINT FK_OI_PRODUCT FOREIGN KEY (PRODUCT_ID)
        REFERENCES PRODUCTS(PRODUCT_ID),
    CONSTRAINT FK_OI_ORDERS FOREIGN KEY (ORDER_ID)
        REFERENCES ORDERS(ORDER_ID)
            ON DELETE CASCADE ON UPDATE CASCADE
)