pizza_updated

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

create database PIZZARIA

use  PIZZARIA

---------------------------------------------------------
-- 1.   Създаване на обектите от схемата.
---------------------------------------------------------
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,
    PHONE varchar(15) 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 check(SIZE='S' or SIZE='B'),
    DATETIME datetime not null

    constraint PK_PIZZA_ORDERS primary key(PIZZA_ID, CLIENT_ID),
    constraint FK_PO_PIZZAS foreign key (PIZZA_ID) references PIZZAS(PIZZA_ID),
    constraint FK_PO_CLIENTS 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)


---------------------------------------------------------
--3.    Изтрий атрибут PHONE от таблица CLIENTS.
---------------------------------------------------------
alter table CLIENTS
drop column PHONE


---------------------------------------------------------
--4.    Добавяне по един запис с произволни данни във всяка таблица.
-- (по-добре повече от 1)
---------------------------------------------------------
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-01-2020',105), 4.00),
                        (2, 100, 1, 'S', convert(datetime,'20-01-2020',105), 3.00),
                        (3, 101, 5, 'B', convert(datetime,'20-02-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', convert(datetime,'10-02-2020',105), 5.00)

select * from PIZZAS
select * from CLIENTS
select * from PIZZA_ORDERS

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

--проверка:
select * from PIZZA_ORDERS


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

-- (трием първо в таблицата, където последно е използван PIZZA_ID = 5
delete from PIZZA_ORDERS
      where PIZZA_ID = 5


--  и след това в таблицата, в която е съзаден PIZZA_ID = 5)
delete from PIZZAS
      where PIZZA_ID = 5

--проверка:
select * from PIZZA_ORDERS
select * from PIZZAS

---------------------------------------------------------
-- 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(107, 'Mira Ivanova')

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

--  излиза, че нямаме пица, която да са поръчвали
--  двама клиента, тоест да е участвала в две поръчки

-- затова ще проверим дали заявката ни работи като добавим
-- още една поръчка с пица, която е поръчвана, но този път от различен клиент:
insert into PIZZA_ORDERS(PIZZA_ID, CLIENT_ID, QUANTITY, SIZE, DATETIME, price)
                  values(1, 103, 3, 'S', convert(datetime,'20-01-2020',105), 4.10)


---------------------------------------------------------
--11.   Създай изглед за типа на пиците и сумарната им стойност в поръчка.
---------------------------------------------------------
create view MyFirstView
as
  select p.PIZZA_TYPE,
         sum(po.QUANTITY*po.PRICE) as Summary
    from PIZZAS p join PIZZA_ORDERS po
      on p.PIZZA_ID=po.PIZZA_ID
group by p.PIZZA_TYPE