S_Madanska

19.01.22

Jan 19th, 2022 (edited)
154
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 7.76 KB | None
  1. CREATE TABLE DEPARTMENTS
  2. (
  3.     DEPARTMENT_ID INT NOT NULL,
  4.     NAME VARCHAR(30) NOT NULL,
  5.     MANAGER_ID INT,
  6.     COUNTRY_ID CHAR(2) NOT NULL,
  7.     CITY VARCHAR(30) NOT NULL,
  8.     STATE VARCHAR(25),
  9.     ADDRESS VARCHAR(40),
  10.     POSTAL_CODE VARCHAR(12),
  11.  
  12.     CONSTRAINT PK_DEPARTMENTS PRIMARY KEY (DEPARTMENT_ID),
  13.     CONSTRAINT FK_DEPT_COUNTRIES FOREIGN KEY (COUNTRY_ID)REFERENCES COUNTRIES(COUNTRY_ID),
  14.     CONSTRAINT FK_DEPT_MANGR FOREIGN KEY (MANAGER_ID)REFERENCES EMPLOYEES(EMPLOYEE_ID)
  15. )
  16.  
  17. ALTER TABLE EMPLOYEES
  18. ADD CONSTRAINT FK_EMPL_DEPT FOREIGN KEY (DEPARTMENT_ID)
  19. REFERENCES DEPARTMENTS(DEPARTMENT_ID)
  20.  
  21. CREATE TABLE ORDERS
  22. (
  23.     ORDER_ID INT NOT NULL PRIMARY KEY,
  24.     ORDER_DATE DATETIME NOT NULL,
  25.     CUSTOMER_ID NUMERIC(6) NOT NULL REFERENCES CUSTOMERS,
  26.     EMPLOYEE_ID INT NOT NULL REFERENCES EMPLOYEES,
  27.     SHIP_ADDRESS VARCHAR(150)
  28. )
  29.  
  30. CREATE TABLE PRODUCTS
  31. (
  32.     PRODUCT_ID INT NOT NULL PRIMARY KEY,
  33.     NAME VARCHAR(70) NOT NULL,
  34.     PRICE NUMERIC(8,2) NOT NULL,
  35.     DESCR VARCHAR(2000)
  36. )
  37.  
  38. CREATE TABLE ORDER_ITEMS
  39. (
  40.     ORDER_ID INT NOT NULL,
  41.     PRODUCT_ID INTEGER NOT NULL,
  42.     UNIT_PRICE NUMERIC(8,2) NOT NULL,
  43.     QUANTITY NUMERIC(8) NOT NULL,
  44.  
  45.     CONSTRAINT PK_OI PRIMARY KEY(ORDER_ID, PRODUCT_ID),
  46.  
  47.     CONSTRAINT FK_OI_O FOREIGN KEY (ORDER_ID)REFERENCES ORDERS (ORDER_ID)
  48.             ON UPDATE CASCADE ON DELETE CASCADE,
  49.  
  50.     CONSTRAINT FK_OI_P FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCTS(PRODUCT_ID)
  51. )
  52.  
  53. ---------------------------------------------------------------
  54. -----------------------TRADECOMPANY ---------------------------
  55. ---------------------------------------------------------------
  56.  
  57. --Демо:
  58.     --Въведи по един запис във всяка от таблиците
  59.  
  60.     --INSERT INTO TABLE (COLUMN1, COLUM2)
  61.     --VALUES ('', 4)
  62.  
  63.     INSERT INTO REGIONS (NAME)
  64.     VALUES('Източна Европа')
  65.  
  66.     SELECT * FROM REGIONS
  67.  
  68.     INSERT INTO COUNTRIES(COUNTRY_ID, NAME, REGION_ID)
  69.     VALUES('BG', 'България', 1)
  70.  
  71.     SELECT * FROM COUNTRIES
  72.  
  73.     INSERT INTO CUSTOMERS(CUSTOMER_ID, COUNTRY_ID, FNAME, LNAME, EMAIL, ADDRESS, GENDER)
  74.     VALUES(1001, 'BG', 'Мария', 'Милева', 'mm@abv.bg', 'бул. България 100, гр.Пловдив', 'F')
  75.  
  76.     SELECT * FROM CUSTOMERS
  77.  
  78.     INSERT INTO JOBS(JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY)
  79.     VALUES('SA_rep', 'Търговски Представител', 9000, 10000)
  80.  
  81.     SELECT * FROM JOBS
  82.  
  83.     INSERT INTO DEPARTMENTS(DEPARTMENT_ID, NAME, COUNTRY_ID, CITY, STATE, ADDRESS, POSTAL_CODE)
  84.     VALUES(80, 'Продажби', 'BG', 'Пловдив', 'Пловдив', 'бул. Марица 10, гр.Пловдив','4000')
  85.  
  86.     SELECT * FROM DEPARTMENTS
  87.  
  88.     INSERT INTO EMPLOYEES(EMPLOYEE_ID, FNAME, LNAME, EMAIL, PHONE, HIRE_DATE, SALARY, JOB_ID, DEPARTMENT_ID)
  89.     VALUES(1501, 'Петър', 'Петров', 'peter@gmail.com', '0892 345 3456', CONVERT(DATE, '19-01-2022', 105), 9000, 'SA_rep', 80)
  90.  
  91.     SELECT * FROM EMPLOYEES
  92.  
  93.     INSERT INTO PRODUCTS(PRODUCT_ID, NAME, PRICE, DESCR)
  94.     VALUES(20001, 'FLASH ADATA', 30, 'FLASH ADATA 2GB')
  95.    
  96.     SELECT * FROM PRODUCTS
  97.  
  98.     INSERT INTO ORDERS(ORDER_ID, ORDER_DATE , CUSTOMER_ID, EMPLOYEE_ID, SHIP_ADDRESS)
  99.     VALUES(1, CONVERT(DATETIME, '20-01-2022 10:00', 105), 1001, 1501, 'бул. Марица 10, гр.Пловдив')
  100.  
  101.     SELECT * FROM ORDERS
  102.  
  103.     INSERT INTO ORDER_ITEMS(PRODUCT_ID, ORDER_ID, UNIT_PRICE, QUANTITY)
  104.     VALUES(20001, 1, 29.98, 20)
  105.  
  106.     SELECT * FROM ORDER_ITEMS
  107.  
  108. --Демо:
  109.     --Промяна на заплатата на служител с даден идентификатор:
  110.     UPDATE EMPLOYEES
  111.     SET SALARY = 9500
  112.     WHERE EMPLOYEE_ID = 1501
  113.  
  114.     SELECT * FROM EMPLOYEES
  115.  
  116.  
  117. --Демо:
  118.     --Поръчката е анулирана -  изтрий реда и от таблицата ORDERS:
  119.  
  120.     --БЕЗ ----> ON DELETE CASCADE
  121.     --DELETE FROM ORDER_ITEMS ; DELETE FROM ORDERS
  122.  
  123.     --СЪЗДАДОХМЕ----> ON DELETE CASCADE ....(PARENT TABLE --> CHILD TABLES)
  124.     DELETE FROM ORDERS
  125.     WHERE ORDER_ID = 1
  126.  
  127.     SELECT * FROM ORDER_ITEMS
  128.     SELECT * FROM ORDERS
  129.  
  130.  
  131. /* Задачи:
  132. Задача 3-1.
  133.     Да се изтрият всички редове от всички таблици в базата от данни, след което да
  134.     се въведат данните чрез командите от inserts_mssql.sql файла. */
  135.  
  136.     DELETE FROM PRODUCTS
  137.     DELETE FROM EMPLOYEES
  138.     DELETE FROM DEPARTMENTS
  139.     DELETE FROM JOBS
  140.     DELETE FROM CUSTOMERS
  141.     DELETE FROM COUNTRIES
  142.     DELETE FROM REGIONS
  143.  
  144.     SELECT * FROM PRODUCTS
  145.     SELECT * FROM ORDER_ITEMS
  146.     SELECT * FROM ORDERS
  147.     SELECT * FROM EMPLOYEES
  148.     SELECT * FROM DEPARTMENTS
  149.     SELECT * FROM JOBS
  150.     SELECT * FROM CUSTOMERS
  151.     SELECT * FROM COUNTRIES
  152.     SELECT * FROM REGIONS
  153.  
  154. /*Задача 3-2.
  155.     Да се увеличи количеството с 2 броя и да се намали единичната цена с 5% на
  156.     продукт с идентификатор 2254 в поръчка с идентификатор 2354.*/
  157.     SELECT *
  158.     FROM ORDER_ITEMS
  159.     WHERE PRODUCT_ID = 2254 AND ORDER_ID = 2354
  160.  
  161.     UPDATE ORDER_ITEMS
  162.     SET QUANTITY += 2, UNIT_PRICE *= 0.95
  163.     WHERE PRODUCT_ID = 2254 AND ORDER_ID = 2354
  164.  
  165.  
  166. --Задача 3-3.
  167.     --Да се изтрие служител с идентификатор 183.
  168.  
  169.     DELETE FROM EMPLOYEES
  170.     WHERE EMPLOYEE_ID = 183
  171.  
  172.     SELECT * FROM EMPLOYEES
  173.     WHERE EMPLOYEE_ID = 183
  174.  
  175. -------------------------------------------------------------------------------------------
  176. --Пример 4-1. -- Д.Р.
  177.     --Да се изведат имената, датите на назначаване и заплатите на всички служители.
  178.  
  179. /*Пример 4-2. -- Д.Р.
  180.     Да се изведат всички данни за продуктите, с цена по-голяма от 2000. Резултатът
  181.     нека бъде подреден по цена на продукт възходящо.*/
  182.  
  183. --Пример 4-3.
  184.     --Да се изведе броя на всички служители.
  185.     SELECT * FROM EMPLOYEES
  186.  
  187.     --1,2,3 .. N ROWS ---> 1 VALUE
  188.     --AGGREG: COUNT(*),  COUNT(COLUMN), SUM(), MAX(), MIN(), AVG()
  189.  
  190.     SELECT COUNT(EMPLOYEE_ID) AS  [броя на всички служители.]
  191.     FROM EMPLOYEES
  192.  
  193. --Пример 4-4.  -- Д.Р
  194.     --Да се изведе броя служители, групирани по отдела, в който работят.
  195.  
  196. -------------------------------------------------------------------------------------------
  197. --4.2.1. Задачи
  198. --Задача 4-1. -- Д.Р
  199.     --Да се изведат имената, заплатите и идентификаторите на длъжностите на
  200.     --служителите, работещи в отдели 50 и 80. Резултатът да е подреден по фамилия на служител във възходящ ред.
  201.  
  202. --Задача 4-2.
  203.     --Да се изведат общата сума на заплатите и броя служители в отдел 60.
  204.     SELECT COUNT(EMPLOYEE_ID) AS [ броя служители в отдел 60]
  205.            , SUM(SALARY) AS 'общата сума на заплатитев отдел 60'
  206.     FROM EMPLOYEES
  207.     WHERE DEPARTMENT_ID = 60
  208.  
  209.     --SELECT COLUMN1, COLUMN2 , AGG_FUNCTION(COLUMN3)
  210.     --FROM TABLE_NAME
  211.     --WHERE CONDITION1 AND  CONDITION2
  212.     --GROUP BY COLUMN1, COLUMN2
  213.     --ORDER BY COLUMN1 ASC/DESC
  214.  
  215. --Задача 4-3.
  216.     --За всички поръчки да се изведат идентификатор на поръчка и обща стойност на поръчката. (ЕД.ЦЕНА*КОЛИЧЕСТВО)
  217.     -- Резултатът да е подреден по стойност на поръчката в низходящ ред.
  218.  
  219.     SELECT ORDER_ID, SUM(UNIT_PRICE*QUANTITY) AS [обща стойност на поръчката]
  220.     FROM ORDER_ITEMS
  221.     GROUP BY ORDER_ID
  222.     --ORDER BY [обща стойност на поръчката] DESC
  223.     ORDER BY 2 DESC
  224.  
RAW Paste Data Copied