PIZZARIA/22/

--1. Създаване на обектите от схемата.
CREATE TABLE PIZZAS
(
    PIZZA_ID INT NOT NULL PRIMARY KEY,
    PIZZA_TYPE VARCHAR(20) NOT NULL
)

CREATE TABLE CLIENTS
(
    CLIENT_ID INT NOT NULL PRIMARY KEY,
    NAME VARCHAR(30) NOT NULL,
    PHONE VARCHAR(10) NOT NULL
)

CREATE TABLE PIZZA_ORDERS
(
    PIZZA_ID INT NOT NULL REFERENCES PIZZAS,
    CLIENT_ID INT NOT NULL REFERENCES CLIENTS,
    PRIMARY KEY(PIZZA_ID, CLIENT_ID),
    QUNATITY INT NOT NULL,
    SIZE CHAR(1) NOT NULL CHECK(SIZE IN ('S','B')),
    DATETME DATETIME NOT NULL

    --CONSTRAINT FK_PO_P FOREIGN KEY (PIZZA_ID)
    --  REFERENCES PIZZAS(PIZZA_ID),
    --CONSTRAINT FK_PO_C FOREIGN KEY (CLIENT_ID)
    --  REFERENCES CLIENTS (CLIENT_ID)
)


--2. Добавяне на атрибут PRICE от тип decimal(5,2)
--за цена в таблица PIZZA_ORDERS с ограничение на
--стойностите само положителни числа.
ALTER TABLE PIZZA_ORDERS
ADD PRICE decimal(5,2) NOT NULL
CHECK(PRICE > 0)

SELECT * FROM PIZZA_ORDERS

--3. Изтрий атрибут PHONE от таблица CLIENTS.
ALTER TABLE CLIENTS
DROP COLUMN PHONE

SELECT * FROM CLIENTS

--4. Добавяне по един запис с произволни данни във всяка таблица.
--(или повече)
INSERT INTO PIZZAS(PIZZA_ID, PIZZA_TYPE)
VALUES(1, 'Quatro Formaji'),
      (2, 'Americana'),
      (3, 'Bulgarian pizza'),
      (4, 'Margeritha'),
      (5, 'Vegan pizza')

INSERT INTO CLIENTS (CLIENT_ID, NAME)
VALUES(100, 'Pamela Anderson'),
       (101, 'Justin Timberlake'),
       (102, 'Mila Kunis'),
       (103, 'J Lo'),
       (104, 'Vasil Ivanov'),
       (105, 'Borovinka Malinkova')

INSERT INTO PIZZA_ORDERS
(PIZZA_ID, CLIENT_ID, QUNATITY, SIZE, DATETME, PRICE)
VALUES(1, 100, 1, 'S', GETDATE(), 2.50),
      (1, 101, 5, 'B', CONVERT(DATETIME, '02-03-2022', 105), 5.50),
      (3, 101, 2, 'S', CONVERT(DATETIME, '01-03-2022', 105), 2.60),
      (2, 102, 5, 'B', CONVERT(DATETIME, '02-03-2022', 105), 2.50),
      (4, 103, 1, 'S', CONVERT(DATETIME, '02-01-2022', 105), 1.50),
      (5, 105, 1, 'B', CONVERT(DATETIME, '02-02-2022', 105), 3.50)

SELECT * FROM PIZZA_ORDERS

--5. Промени количеството поръчана пица на 5
--и размера на малка за първият въведен идентификатор на пица.
UPDATE PIZZA_ORDERS
SET QUNATITY = 5, SIZE = 'S'
WHERE PIZZA_ID = 1

SELECT * FROM PIZZA_ORDERS

--6. Вече не се предлага пица с последният идентификатор,
--който сте попълнили – изтрийте я.
DELETE FROM PIZZA_ORDERS
WHERE PIZZA_ID = 5

DELETE FROM PIZZAS
WHERE PIZZA_ID = 5

--7. Изведи от името на кои клиенти и кога са направени поръчки
--на малки пици. Подреди ги по име на клиент в низходящ ред.
SELECT C.NAME, PO.DATETME
FROM CLIENTS C JOIN PIZZA_ORDERS PO
ON PO.CLIENT_ID = C.CLIENT_ID
WHERE PO.SIZE = 'S'
ORDER BY C.NAME DESC

--8. Добави нов клиент, без да добавяш поръчка за него.
INSERT INTO CLIENTS (CLIENT_ID, NAME)
VALUES(106, 'Test Client')


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

--/AGG FUNCTIONS: SUM, COUNT, MIN, MAX, AVG/

SELECT C.NAME, COUNT(PO.PIZZA_ID) ORDER_COUNT
FROM CLIENTS C LEFT JOIN PIZZA_ORDERS PO
ON PO.CLIENT_ID = C.CLIENT_ID
GROUP BY C.NAME, C.CLIENT_ID

--10. Изведи типа и броя на поръчвани пици.
--Само ако са поръчвани в повече от една поръчки.
SELECT P.PIZZA_TYPE, COUNT(PO.PIZZA_ID) PIZZA_COUNT
FROM PIZZAS P JOIN PIZZA_ORDERS PO
ON PO.PIZZA_ID = P.PIZZA_ID
GROUP BY P.PIZZA_TYPE, P.PIZZA_ID
HAVING COUNT(PO.PIZZA_ID) > 1

--11. Създай изглед за типа на пиците и сумарната  им стойност в поръчка.

--/!ALL COOLUMNS HAVE NAMES/

CREATE VIEW TEST_VIEW
AS
SELECT P.PIZZA_TYPE, SUM(PO.QUNATITY*PO.PRICE) TOTAL
FROM PIZZAS P JOIN PIZZA_ORDERS PO
ON P.PIZZA_ID = PO.PIZZA_ID
GROUP BY P.PIZZA_TYPE, PO.PIZZA_ID