26.11

--create database Pizzaria
--use Pizzaria

--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) NULL
)

CREATE TABLE PIZZA_ORDERS
(
    PIZZA_ID INT NOT NULL ,
    CLIENT_ID INT NOT NULL,
    QUANTITY INT NOT NULL,
    SIZE CHAR(1) NOT NULL CHECK (SIZE IN('B','S')),
    DATETIME DATETIME NOT NULL

    CONSTRAINT PK_PO PRIMARY KEY (PIZZA_ID, CLIENT_ID),
    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
/*CREATE TABLE PIZZA_ORDERS
(
    PIZZA_ID INT NOT NULL REFERENCES PIZZAS,
    CLIENT_ID INT NOT NULL REFERENCES CLIENTS,
    QUANTITY INT NOT NULL,
    SIZE CHAR(1) NOT NULL CHECK (SIZE IN('B','S')),
    DATETIME DATETIME NOT NULL,

    PRIMARY KEY (PIZZA_ID, CLIENT_ID)
)*/

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

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

--4.    Добавяне по един запис с произволни данни във всяка таблица
INSERT INTO PIZZAS(PIZZA_ID,PIZZA_TYPE)
            VALUES(1, 'QUATRO FORMAJI'),
                  (2, 'AMERICANA'),
                  (3, 'MARGERITHA'),
                  (4, 'BROCCOLI PIZZA'),
                  (5, 'BULGARIAN PIZZA')

INSERT INTO CLIENTS(CLIENT_ID,NAME)
          VALUES(100, 'PAMELA ANDERSON'),
             (101, 'MILA KUNIS'),
             (102, 'VENETA RAYKOVA'),
             (103, 'VASIL NAIDENOV'),
             (104, 'PETAR STOYANOV'),
             (105, 'JUSTIN TIMBERLAKE')

INSERT INTO PIZZA_ORDERS(PIZZA_ID, CLIENT_ID, QUANTITY, SIZE, DATETIME, PRICE)
            VALUES(1, 100, 3, 'S', CONVERT(DATETIME,'20-11-2020',105), 4.00),
                  (2, 100, 1, 'S', CONVERT(DATETIME,'20-01-2020',105), 3.00),
                  (3, 101, 5, 'B', CONVERT(DATETIME,'27-10-2020',105), 2.50),
                  (4, 104, 1, 'B', CONVERT(DATETIME,'01-03-2020',105), 2.60),
                  (5, 105, 10,'S', GETDATE(), 5.00),
                  (5, 104, 1, 'S', '2020-01-01', 5.00)

select GETDATE()

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

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

DELETE FROM PIZZA_ORDERS
WHERE PIZZA_ID = 5

DELETE FROM PIZZAS
WHERE PIZZA_ID = 5

--7.    Изведи от името на кои клиенти и кога са направени поръчки на  малки пици.
--Подреди ги по име на клиент в низходящ ред.
SELECT C.NAME,
       PO.DATETIME
  FROM PIZZA_ORDERS PO JOIN CLIENTS C
    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, 'MARIYA ILIEVA')

--9.    Изведи имената на клиентите и колко пъти са поръчвали пица.
--В резултатния набор да се покажат и клиентите без поръчка.
  SELECT C.NAME,
         COUNT(PO.PIZZA_ID) AS [PIZZA ORDS]
    FROM CLIENTS C LEFT  JOIN PIZZA_ORDERS PO
      ON C.CLIENT_ID = PO.CLIENT_ID
GROUP BY C.NAME

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


--излиза, че нямаме пица, която да са поръчвали 2 клиента, тоест да е участвала в две поръчки
--затова ще проверим дали заявката ни работи като добавим  още една поръчка с пица, която е поръчвана,
--но този път от различен клиент:
INSERT INTO PIZZA_ORDERS(PIZZA_ID, CLIENT_ID, QUANTITY, SIZE, DATETIME, PRICE)
                  VALUES(1, 103, 3, 'S', CONVERT(DATETIME,'01-01-2020',105), 4.10)

--11.   Създай изглед за типа на пиците и сумарната им стойност в поръчка.
CREATE VIEW PizzaView
AS
  SELECT P.PIZZA_TYPE,
      SUM(PO.PRICE*PO.QUANTITY) as [Total SUMMARY]
    FROM PIZZAS P JOIN PIZZA_ORDERS PO
      ON P.PIZZA_ID=PO.PIZZA_ID
GROUP BY P.PIZZA_TYPE