Advertisement
S_Madanska

DAY1_MASTERS

Feb 13th, 2021
171
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 8.08 KB | None
  1. ----comment
  2.  
  3. /*
  4. c
  5. o
  6. m
  7. m
  8. e
  9. n
  10. t
  11. */
  12. ---------------------------------------
  13. -----------------1. DDL----------------
  14. ---------------------------------------
  15.  
  16. ---------------------------------------
  17. ---------------table Students----------
  18. ---------------------------------------
  19. CREATE DATABASE MY_FIRST_DB
  20. ---------------------------------------
  21. USE  MY_FIRST_DB
  22. ---------------------------------------
  23. CREATE TABLE STUDENTS
  24. (
  25.     FAC_NUM INT NOT NULL,
  26.     NAME VARCHAR (50) NOT NULL,
  27.     PHONE VARCHAR (15)
  28. )
  29. ---------------------------------------
  30. ALTER TABLE STUDENTS
  31. ADD ADDRESS VARCHAR(50)
  32. ---------------------------------------
  33. ALTER TABLE STUDENTS
  34. ALTER COLUMN ADDRESS TEXT
  35. ---------------------------------------
  36. ALTER TABLE STUDENTS
  37. DROP COLUMN PHONE
  38. ---------------------------------------
  39. DROP TABLE STUDENTS
  40.  
  41.  
  42.  
  43. ---------------------------------------
  44. -----------------2. DML----------------
  45. ---------------------------------------
  46. INSERT INTO STUDENTS(FAC_NUM, NAME, PHONE )
  47.              VALUES (19000, 'IVAN IVANOV', '08934566')
  48. ---------------------------------------
  49. INSERT INTO STUDENTS(FAC_NUM, NAME, PHONE )
  50.             VALUES  (19001, 'IVAN IVANOV', '08934566'),
  51.                     (19002, 'IVA', '08934576'),
  52.                     (19003, 'NOVA', '089367766')
  53. ---------------------------------------
  54. INSERT INTO STUDENTS
  55.               VALUES (19004, 'MIRA', NULL )
  56.  
  57.  
  58. SELECT * FROM STUDENTS
  59.  
  60. ---------------------------------------
  61. UPDATE STUDENTS
  62. SET NAME = 'RENI'
  63. WHERE FAC_NUM = 19001
  64. ---------------------------------------
  65. DELETE FROM STUDENTS
  66. WHERE FAC_NUM = 19000
  67.  
  68.  
  69.  
  70. ---------------------------------------
  71. -------------3. table COUNTRIES-----------
  72. ---------------------------------------
  73.  
  74. CREATE DATABASE SEC_DB
  75. ---------------------------------------
  76. USE SEC_DB
  77. ---------------------------------------
  78. CREATE TABLE COUNTRIES
  79. (
  80.     COUNTRY_CODE CHAR(3) NOT NULL PRIMARY KEY,
  81.     NAME VARCHAR(40) NOT NULL,
  82.     POPULATION INT
  83. )
  84. ---------------------------------------
  85. ALTER TABLE COUNTRIES
  86. ADD PHONE_CODE CHAR(3)
  87. ---------------------------------------
  88. INSERT INTO COUNTRIES(COUNTRY_CODE, NAME, POPULATION, PHONE_CODE)
  89.                VALUES('BGN', 'БЪЛГАРИЯ', 7500000, '359')
  90.  
  91. SELECT * FROM COUNTRIES
  92. ---------------------------------------
  93. UPDATE COUNTRIES
  94. SET POPULATION = 6000000
  95. WHERE COUNTRY_CODE='BGR'
  96. ---------------------------------------
  97. DELETE FROM COUNTRIES
  98. WHERE COUNTRY_CODE='BGR'
  99. ---------------------------------------
  100. DELETE FROM COUNTRIES
  101. ---------------------------------------
  102. ALTER TABLE COUNTRIES
  103. DROP COLUMN PHONE_CODE
  104. ---------------------------------------
  105. DROP TABLE COUNTRIES
  106. ---------------------------------------
  107. USE MASTER
  108. DROP DATABASE SEC_DB
  109.  
  110.  
  111. ---------------------------------------
  112. -----------4. TRADE_MASTERS---------------
  113. ---------------------------------------
  114.  
  115. CREATE DATABASE TRADE_MASTERS
  116. ---------------------------------------
  117. USE TRADE_MASTERS
  118. ---------------------------------------
  119. ALTER DATABASE CURRENT COLLATE CYRILLIC_GENERAL_CI_AI;
  120. ---------------------------------------
  121. CREATE TABLE REGIONS
  122. (
  123.     REGION_ID SMALLINT NOT NULL PRIMARY KEY IDENTITY(1,1),
  124.     NAME VARCHAR(25) NOT NULL UNIQUE
  125. )
  126.  
  127.  
  128. CREATE TABLE COUNTRIES
  129. (
  130.     COUNTRY_ID CHAR(2) NOT NULL,
  131.     NAME VARCHAR(40) NOT NULL,
  132.     REGION_ID SMALLINT NULL,
  133.  
  134.     CONSTRAINT PK_COUNTRIES PRIMARY KEY (COUNTRY_ID),
  135.     CONSTRAINT FK_COUNTRIES_REGIONS FOREIGN KEY (REGION_ID)
  136.     REFERENCES REGIONS (REGION_ID)
  137. )
  138.  
  139. CREATE TABLE CUSTOMERS
  140. (
  141.     CUSTOMER_ID NUMERIC(6) NOT NULL,
  142.     COUNTRY_ID CHAR(2) NOT NULL,
  143.     FNAME VARCHAR(20) NOT NULL,
  144.     LNAME VARCHAR(20) NOT NULL,
  145.     ADDRESS TEXT NULL,
  146.     EMAIL VARCHAR(30) NULL,
  147.     GENDER CHAR(1) NULL DEFAULT 'M'
  148.     CONSTRAINT CUST_GENDER CHECK( GENDER IS NULL OR (GENDER IN('M','F'))),
  149.     CONSTRAINT OK_CUSTOMERS PRIMARY KEY(CUSTOMER_ID)
  150. )
  151.  
  152. ALTER TABLE CUSTOMERS
  153. ADD CONSTRAINT FK_CUSTOMERS_COUNTRIES FOREIGN KEY (COUNTRY_ID)
  154. REFERENCES COUNTRIES(COUNTRY_ID)
  155.  
  156. CREATE TABLE JOBS
  157. (
  158.     JOB_ID VARCHAR(10) NOT NULL PRIMARY KEY,
  159.     JOB_TITLE VARCHAR(35) NOT NULL,
  160.     MIN_SALARY NUMERIC(6) NULL,
  161.     MAX_SALARY NUMERIC(6) NULL
  162. )
  163.  
  164. CREATE TABLE EMPLOYEES
  165. (
  166.     EMPLOYEE_ID INT NOT NULL,
  167.     FNAME VARCHAR(20) NOT NULL,
  168.     LNAME VARCHAR(25) NOT NULL,
  169.     EMAIL VARCHAR(40) NOT NULL ,
  170.     PHONE VARCHAR(20) NULL,
  171.     HIRE_DATE DATETIME NOT NULL,
  172.     SALARY NUMERIC(8,2) NOT NULL CONSTRAINT SALARY_CHECK CHECK(SALARY>0),
  173.     JOB_ID VARCHAR(10) NOT NULL,
  174.     MANAGER_ID INT NULL,
  175.     DEPARTMENT_ID INT NULL,
  176.     CONSTRAINT PK_EMPLOYEES PRIMARY KEY(EMPLOYEE_ID),
  177.     CONSTRAINT UK_EMAIL UNIQUE(EMAIL),
  178.     CONSTRAINT FK_EMPLOYEES_JOBS FOREIGN KEY (JOB_ID)
  179.         REFERENCES JOBS(JOB_ID),
  180.     CONSTRAINT FK_EMPLOYEES_MANAGERS FOREIGN KEY (MANAGER_ID)
  181.         REFERENCES EMPLOYEES(EMPLOYEE_ID)
  182. )
  183.  
  184. CREATE TABLE DEPARTMENTS
  185. (
  186.     DEPARTMENT_ID INT NOT NULL,
  187.     NAME VARCHAR(30) NOT NULL,
  188.     MANAGER_ID INT NULL,
  189.     COUNTRY_ID CHAR(2) NOT NULL,
  190.     CITY VARCHAR(30) NOT NULL,
  191.     STATE VARCHAR(25),
  192.     ADDRESS VARCHAR(40),
  193.     POSTAL_CODE VARCHAR(12),
  194.     CONSTRAINT PK_DEPARTMENTS PRIMARY KEY(DEPARTMENT_ID),
  195.     CONSTRAINT FK_DEPT_MGR FOREIGN KEY (MANAGER_ID)
  196.         REFERENCES EMPLOYEES(EMPLOYEE_ID),
  197.     CONSTRAINT FK_DEPT_COUNTRIES FOREIGN KEY (COUNTRY_ID)
  198.         REFERENCES COUNTRIES(COUNTRY_ID)
  199. )
  200.  
  201. ALTER TABLE EMPLOYEES
  202. ADD CONSTRAINT FK_EMPLOYEE_DEPARTMENTS FOREIGN KEY (DEPARTMENT_ID)
  203. REFERENCES DEPARTMENTS
  204.  
  205.  
  206. CREATE TABLE ORDERS
  207. (
  208.     ORDER_ID INT NOT NULL PRIMARY KEY,
  209.     ORDER_DATE DATETIME NOT NULL,
  210.     CUSTOMER_ID NUMERIC(6) NOT NULL FOREIGN KEY REFERENCES CUSTOMERS,
  211.     EMPLOYEE_ID INT NOT NULL FOREIGN KEY REFERENCES EMPLOYEES,
  212.     SHIP_ADDRESS VARCHAR(150)
  213. )
  214.  
  215. CREATE TABLE PRODUCTS
  216. (
  217.     PRODUCT_ID INT NOT NULL PRIMARY KEY,
  218.     NAME VARCHAR(70) NOT NULL,
  219.     PRICE NUMERIC(8,2) NOT NULL,
  220.     DESCR VARCHAR(2000) NULL
  221. )
  222.  
  223. CREATE TABLE ORDER_ITEMS
  224. (
  225.     ORDER_ID INT NOT NULL,
  226.     PRODUCT_ID INTEGER NOT NULL,
  227.     UNIT_PRICE NUMERIC(8,2) NOT NULL,
  228.     QUANTITY NUMERIC(8) NOT NULL,
  229.  
  230.     CONSTRAINT PK_ORDER_ITEMS PRIMARY KEY(ORDER_ID, PRODUCT_ID),
  231.     CONSTRAINT FK_OI_ORDERS FOREIGN KEY(ORDER_ID)
  232.         REFERENCES ORDERS(ORDER_ID) ON UPDATE CASCADE ON DELETE CASCADE,
  233.     CONSTRAINT FK_OI_PRODUCTS FOREIGN KEY(PRODUCT_ID)
  234.         REFERENCES PRODUCTS(PRODUCT_ID)
  235. )
  236.  
  237.  
  238. ---------------------------------------
  239. --5. INSERTS into TRADE_MASTERS tables-
  240. ---------------------------------------
  241. INSERT INTO REGIONS(NAME) VALUES('ИЗТОЧНА ЕВРОПА')
  242.  
  243. INSERT INTO COUNTRIES(COUNTRY_ID, NAME, REGION_ID)
  244. VALUES ('BG', 'БЪЛГАРИЯ', 1)
  245.  
  246. INSERT INTO CUSTOMERS(CUSTOMER_ID, COUNTRY_ID, FNAME,LNAME, ADDRESS, EMAIL,GENDER)
  247. VALUES(1001, 'BG', 'ИВАН','ГЕНЕВ', 'БУЛ. БЪЛГАРИЯ 100', 'gg@gmail.com', 'M')
  248.  
  249. INSERT INTO JOBS(JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY)
  250. VALUES('SA_REP', 'ТЪРГОВСКИ ПРЕДСТАВИТЕЛ', 9000, 17000)
  251.  
  252. INSERT INTO DEPARTMENTS(DEPARTMENT_ID, NAME, COUNTRY_ID, CITY, STATE, ADDRESS, POSTAL_CODE)
  253. VALUES (80, 'ПРОДАЖБИ', 'BG', 'ПЛОВДИВ', 'ПЛОВДИВ','БУЛ.МАРИЦА 10', '4000')
  254.  
  255. INSERT INTO EMPLOYEES(EMPLOYEE_ID, FNAME, LNAME, EMAIL, PHONE, HIRE_DATE,JOB_ID, SALARY, DEPARTMENT_ID)
  256. VALUES(1501, 'ПЕТЪР','ТОДОРОВ', 'pt@abv.bg','08956778', CONVERT(DATE, '13-02-2021', 105), 'SA_REP', 1900, 80)
  257.  
  258. INSERT INTO PRODUCTS(PRODUCT_ID, NAME, DESCR, PRICE)
  259. VALUES(20001, 'SAMSUNG GALAXY', 'BLACK 128GB', 1400)
  260.  
  261. INSERT INTO ORDERS(ORDER_ID, CUSTOMER_ID, EMPLOYEE_ID, ORDER_DATE)
  262. VALUES(1, 1001, 1501, CONVERT(DATETIME, '12-02-2021 10:30', 105))
  263.  
  264. INSERT INTO ORDER_ITEMS(ORDER_ID, PRODUCT_ID, UNIT_PRICE, QUANTITY)
  265. VALUES(1, 20001, 1500, 2)
  266.  
  267. SELECT * FROM EMPLOYEES
  268. ---------------------------------------
  269. UPDATE EMPLOYEES
  270. SET SALARY = 2000
  271. WHERE EMPLOYEE_ID = 1501
  272. ---------------------------------------
  273. --DELETE FROM ORDER_ITEMS
  274. --WHERE
  275.  
  276. DELETE FROM ORDERS
  277. WHERE ORDER_ID = 1
  278. ---------------------------------------
  279. DELETE FROM PRODUCTS
  280. DELETE FROM EMPLOYEES
  281. DELETE FROM DEPARTMENTS
  282. DELETE FROM CUSTOMERS
  283. DELETE FROM COUNTRIES
  284. DELETE FROM REGIONS
  285. DELETE FROM JOBS
  286.  
  287. SELECT * FROM EMPLOYEES
  288.  
  289. -- след това достъпихме линка с готовите
  290. --  инсърти отворихме го в ново query и execute
Advertisement
RAW Paste Data Copied
Advertisement