PIZZARIA

-----------------------------------------------------------
---------------Създай база данни за Пицария----------------
-----------------------------------------------------------

-- 1.   Създаване на обектите от схемата.;
CREATE DATABASE PIZZARIA
USE PIZZARIA

CREATE TABLE PIZZAS
(
    PIZZA_ID int not null,
    PIZZA_TYPE varchar(20) not null

    CONSTRAINT PK_PIZZAS PRIMARY KEY (PIZZA_ID)
)

CREATE TABLE CLIENTS
(
    CLIENT_ID int not null,
    NAME varchar(30) not null

    CONSTRAINT PK_CLIENTS PRIMARY KEY (CLIENT_ID)
)

CREATE TABLE PIZZA_ORDERS
(
    PIZZA_ID int not null ,
    CLIENT_ID int not null,
    QUANTITY int not null,
    SIZE char(1) not null CONSTRAINT CH_SIZE CHECK (SIZE IN('B','S')),
    DATETIME datetime not null

    CONSTRAINT PK_ORDS PRIMARY KEY (PIZZA_ID, CLIENT_ID),
    CONSTRAINT FK_ORD_PIZZA FOREIGN KEY (PIZZA_ID)
        REFERENCES PIZZAS(PIZZA_ID),
    CONSTRAINT FK_ORD_CLIENT FOREIGN KEY (CLIENT_ID)
        REFERENCES CLIENTS(CLIENT_ID)
)


-- 2.   Добавяне на атрибут PRICE за цена с ограничение на
--      стойностите cамо положителни числа в таблица PIZZA_ORDERS.;

ALTER TABLE PIZZA_ORDERS
ADD PRICE DECIMAL(6,2) NOT NULL Check (PRICE> 0)


--3.    Добавяне по един запис с произволни данни във всяка таблица.;

insert into PIZZAS(PIZZA_ID, PIZZA_TYPE)
           values (101, 'Neapolitan'),
                  (102, 'New York-Style Pizza'),
                  (103, '4 Cheese')

insert into CLIENTS(CLIENT_ID,NAME)
            values (1, 'Justin Timberlake'),
                   (2, 'Pamela Anderson'),
                   (3, 'Veneta Raykova')


insert into PIZZA_ORDERS(PIZZA_ID,CLIENT_ID, QUANTITY, SIZE, DATETIME, PRICE)
                 values (101, 1, 10, 'B', GETDATE(), 80.90),
                        (102, 2, 50, 'S', convert(datetime,'24-12-2019',105), 245),
                        (103, 3, 10, 'B', convert(datetime,'25-11-2019',105), 80.90),
                        (102, 1, 1, 'S', convert(datetime,'26-11-2019',105), 4.90),
                        (103, 2, 2, 'B', '01-01-2019', 9.80)

                        SELECT * FROM PIZZA_ORDERS

-- 4.   Промени количеството поръчана пица на 5 и размера и на малка.
--      Там където имаш идентификатор 102 и идентификатор на клиент 1.;
UPDATE PIZZA_ORDERS
   SET QUANTITY = 5, SIZE = 'S'
 WHERE PIZZA_ID = 102 AND CLIENT_ID = 1

-- 5.   Вече не се предлага пица с първия идентификатор – изтрийте я.;

DELETE FROM PIZZA_ORDERS
      WHERE PIZZA_ID = 101

DELETE FROM PIZZAS
      WHERE PIZZA_ID = 101


--6.    Изведи час на поръчка и име на клиент, само за тези клиенти,
--      които са поръчвали малка пица. Подреди ги по име на клиент в низходящ ред.;

  SELECT PO.DATETIME, C.NAME
    FROM PIZZA_ORDERS PO JOIN CLIENTS C
      ON PO.CLIENT_ID = C.CLIENT_ID
   WHERE PO.SIZE = 'S'
ORDER BY C.NAME DESC


-- 7.   Изтрий поръчка на клиент с идентификатор 1 от таблицата с поръчките.;
DELETE FROM PIZZA_ORDERS
      WHERE CLIENT_ID = 1

-- 8.   Изведи имената на клиентите, колко пъти е поръчвана пица и кога.
--      В резултатния набор да се покажат и клиентите без поръчка.;

  SELECT C.NAME,
         PO.DATETIME,
         COUNT(PO.PIZZA_ID) [Number of Pizzas]
    FROM PIZZA_ORDERS PO RIGHT JOIN CLIENTS C
      ON PO.CLIENT_ID = C.CLIENT_ID
GROUP BY C.NAME, PO.SIZE, PO.DATETIME

-- В условието нямам предвид в резултатния набор да се включи и самият тип на пиците, но ако се
-- изискваше да се покаже и типа на пицата - това става с Multiple Joins, защото ще свързваме 3 таблици:
--  SELECT C.NAME,
--       PO.DATETIME,
--         COUNT(PO.PIZZA_ID) [Number of Pizzas],
--       p.PIZZA_TYPE
--    FROM PIZZA_ORDERS PO RIGHT JOIN CLIENTS C   ON PO.CLIENT_ID = C.CLIENT_ID
--                        left join PIZZAS p    on p.PIZZA_ID = po.PIZZA_ID
--GROUP BY C.NAME, PO.SIZE, PO.DATETIME , p.PIZZA_TYPE


-- 9.   Изведи типа на пиците, сумарната им стойност в поръчка, броя на поръчвани пици.
--      Но само на тези пици, които са поръчвани повече от веднъж.;
  SELECT P.PIZZA_TYPE,
         COUNT(PO.PIZZA_ID) [Number of Pizzas],
         SUM(PO.QUANTITY * PO.PRICE) [Total Price]
    FROM PIZZAS P JOIN PIZZA_ORDERS PO
      ON PO.PIZZA_ID = P.PIZZA_ID
GROUP BY P.PIZZA_TYPE
  HAVING COUNT(PO.PIZZA_ID) > 1