Monday, October 7, 2019

SQL From video on Data And Entities

Create table topping
(
 toppingID serial primary key,
    toppingName text,
 toppingPrice decimal(5,2)
);

Create table crust
(
 crustID serial primary key,
 crustName text,
    crustPrice Decimal(5,2)
);

Create table pizzasize
(
 sizeID serial primary key,
    sizeName text,
    sizeBasePrice decimal(5,2)
);

Create table pizza
(
 pizzaID serial primary key,
 crustID integer references crust(crustID),
 sizeID integer references pizzasize(sizeID)
);

create table pizzatopping
(
 pizzaID integer references pizza(pizzaID),
 toppingID integer references topping(toppingID),
 primary key (pizzaID, toppingID)
);

create table "order"
(
 orderID serial primary key,
 orderdate timestamp default current_timestamp
);

Create table orderPizza
(
   orderID integer references "order"(orderID),
   pizzaID integer references pizza(pizzaID),
   primary key (orderID, pizzaID)
 
);

Insert into topping(toppingname, toppingprice)
Values('pepperoni', .50),
('olives', .50),
('pinapple', .50),
('tomatoes', .30),
('sausage', .50);

Insert into pizzasize(sizename, sizebaseprice)
values('large', 15.00),
('medium', 13.00),
('small', 10.00),
('personal', 9.00);

Insert into crust(crustname, crustprice)
values('regular', 0.00),
('deep dish', 1.00),
('cheese stuffed', 2.00);

Insert into Pizza(crustID, sizeID)
Values(1,2),
(2,2);

Insert into pizzatopping(pizzaid, toppingid)
values(1, 1),
(1,5),
(2, 2),
(2,3),
(2,4);

Insert into "order"(orderdate)
values('2019-10-07');

Insert into OrderPizza(orderID, PizzaID)
Values(1, 1),
(1,2);

Select * from Topping;
Select * from Crust;
Select * from PizzaSize;
Select * from Pizza;
Select * from PizzaTopping;
Select * from "order";
Select * from Orderpizza;

No comments:

Post a Comment