madanska

26.11

Nov 26th, 2020 (edited)
597
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. --create database Pizzaria
  2. --use Pizzaria
  3.  
  4. --1. Създаване на обектите от схемата.
  5. CREATE TABLE PIZZAS
  6. (
  7.     PIZZA_ID INT NOT NULL PRIMARY KEY,
  8.     PIZZA_TYPE VARCHAR(20) NOT NULL
  9. )
  10.  
  11. CREATE TABLE CLIENTS
  12. (
  13.     CLIENT_ID INT NOT NULL PRIMARY KEY ,
  14.     NAME VARCHAR(30) NOT NULL,
  15.     PHONE VARCHAR(10) NULL
  16. )
  17.  
  18. CREATE TABLE PIZZA_ORDERS
  19. (
  20.     PIZZA_ID INT NOT NULL ,
  21.     CLIENT_ID INT NOT NULL,
  22.     QUANTITY INT NOT NULL,
  23.     SIZE CHAR(1) NOT NULL CHECK (SIZE IN('B','S')),
  24.     DATETIME DATETIME NOT NULL
  25.  
  26.     CONSTRAINT PK_PO PRIMARY KEY (PIZZA_ID, CLIENT_ID),
  27.     CONSTRAINT FK_PO_P FOREIGN KEY (PIZZA_ID) REFERENCES PIZZAS(PIZZA_ID),
  28.     CONSTRAINT FK_PO_C FOREIGN KEY (CLIENT_ID) REFERENCES CLIENTS(CLIENT_ID)
  29. )
  30.  
  31. --вариант 2
  32. /*CREATE TABLE PIZZA_ORDERS
  33. (
  34.     PIZZA_ID INT NOT NULL REFERENCES PIZZAS,
  35.     CLIENT_ID INT NOT NULL REFERENCES CLIENTS,
  36.     QUANTITY INT NOT NULL,
  37.     SIZE CHAR(1) NOT NULL CHECK (SIZE IN('B','S')),
  38.     DATETIME DATETIME NOT NULL,
  39.  
  40.     PRIMARY KEY (PIZZA_ID, CLIENT_ID)
  41. )*/
  42.  
  43. --2.    Добавяне на атрибут PRICE за цена с ограничение на стойностите само положителни
  44. --числа в таблица PIZZA_ORDERS.
  45. ALTER TABLE PIZZA_ORDERS
  46. ADD PRICE DECIMAL(5,2) NOT NULL CHECK (PRICE> 0)
  47.  
  48. --3.    Изтрий атрибут PHONE от таблица CLIENTS.
  49. ALTER TABLE CLIENTS
  50. DROP COLUMN PHONE
  51.  
  52. --4.    Добавяне по един запис с произволни данни във всяка таблица
  53. INSERT INTO PIZZAS(PIZZA_ID,PIZZA_TYPE)
  54.             VALUES(1, 'QUATRO FORMAJI'),
  55.                   (2, 'AMERICANA'),
  56.                   (3, 'MARGERITHA'),
  57.                   (4, 'BROCCOLI PIZZA'),
  58.                   (5, 'BULGARIAN PIZZA')
  59.  
  60. INSERT INTO CLIENTS(CLIENT_ID,NAME)
  61.           VALUES(100, 'PAMELA ANDERSON'),
  62.              (101, 'MILA KUNIS'),
  63.              (102, 'VENETA RAYKOVA'),
  64.              (103, 'VASIL NAIDENOV'),
  65.              (104, 'PETAR STOYANOV'),
  66.              (105, 'JUSTIN TIMBERLAKE')
  67.  
  68. INSERT INTO PIZZA_ORDERS(PIZZA_ID, CLIENT_ID, QUANTITY, SIZE, DATETIME, PRICE)
  69.             VALUES(1, 100, 3, 'S', CONVERT(DATETIME,'20-11-2020',105), 4.00),
  70.                   (2, 100, 1, 'S', CONVERT(DATETIME,'20-01-2020',105), 3.00),
  71.                   (3, 101, 5, 'B', CONVERT(DATETIME,'27-10-2020',105), 2.50),
  72.                   (4, 104, 1, 'B', CONVERT(DATETIME,'01-03-2020',105), 2.60),
  73.                   (5, 105, 10,'S', GETDATE(), 5.00),
  74.                   (5, 104, 1, 'S', '2020-01-01', 5.00)
  75.  
  76. select GETDATE()
  77.  
  78. --5.    Промени количеството поръчана пица на 5 и размера на малка за първият въведен идентификатор на пица.
  79. UPDATE PIZZA_ORDERS
  80.    SET QUANTITY = 5, SIZE = 'S'
  81.  WHERE PIZZA_ID = 1
  82.  
  83. --6.    Вече не се предлага пица с последният идентификатор, който сте попълнили – изтрийте я.
  84.  
  85. DELETE FROM PIZZA_ORDERS
  86. WHERE PIZZA_ID = 5
  87.  
  88. DELETE FROM PIZZAS
  89. WHERE PIZZA_ID = 5
  90.  
  91. --7.    Изведи от името на кои клиенти и кога са направени поръчки на  малки пици.
  92. --Подреди ги по име на клиент в низходящ ред.
  93. SELECT C.NAME,
  94.        PO.DATETIME
  95.   FROM PIZZA_ORDERS PO JOIN CLIENTS C
  96.     ON PO.CLIENT_ID = C.CLIENT_ID
  97.  WHERE PO.SIZE = 'S'
  98. ORDER BY C.NAME DESC
  99.  
  100. --8.    Добави нов клиент, без да добавяш поръчка за него.  
  101. INSERT INTO CLIENTS(CLIENT_ID,NAME)
  102.              VALUES(106, 'MARIYA ILIEVA')
  103.  
  104. --9.    Изведи имената на клиентите и колко пъти са поръчвали пица.
  105. --В резултатния набор да се покажат и клиентите без поръчка.
  106.   SELECT C.NAME,
  107.          COUNT(PO.PIZZA_ID) AS [PIZZA ORDS]
  108.     FROM CLIENTS C LEFT  JOIN PIZZA_ORDERS PO
  109.       ON C.CLIENT_ID = PO.CLIENT_ID
  110. GROUP BY C.NAME
  111.  
  112. --10.   Изведи типа и броя на поръчвани пици. Само ако са поръчвани повече от веднъж.
  113.    SELECT P.PIZZA_TYPE,
  114.           COUNT(PO.PIZZA_ID) AS [ORDERED PIZZA COUNT]
  115.      FROM PIZZAS P JOIN PIZZA_ORDERS PO
  116.        ON P.PIZZA_ID=PO.PIZZA_ID
  117.  GROUP BY P.PIZZA_TYPE
  118.    HAVING COUNT(PO.PIZZA_ID) > 1
  119.  
  120.  
  121. --излиза, че нямаме пица, която да са поръчвали 2 клиента, тоест да е участвала в две поръчки
  122. --затова ще проверим дали заявката ни работи като добавим  още една поръчка с пица, която е поръчвана,
  123. --но този път от различен клиент:
  124. INSERT INTO PIZZA_ORDERS(PIZZA_ID, CLIENT_ID, QUANTITY, SIZE, DATETIME, PRICE)
  125.                   VALUES(1, 103, 3, 'S', CONVERT(DATETIME,'01-01-2020',105), 4.10)
  126.  
  127. --11.   Създай изглед за типа на пиците и сумарната им стойност в поръчка.
  128. CREATE VIEW PizzaView
  129. AS
  130.   SELECT P.PIZZA_TYPE,
  131.       SUM(PO.PRICE*PO.QUANTITY) as [Total SUMMARY]
  132.     FROM PIZZAS P JOIN PIZZA_ORDERS PO
  133.       ON P.PIZZA_ID=PO.PIZZA_ID
  134. GROUP BY P.PIZZA_TYPE
RAW Paste Data