create JSON file for this code. 

DROP DATABASE pizza;

 

CREATE DATABASE pizza;

 

CREATE TABLE Customers (

    CustomerId  INTEGER       PRIMARY KEY AUTO_INCREMENT

                               NOT NULL,

    FirstName   NVARCHAR (40) NOT NULL,

    LastName    NVARCHAR (20) NOT NULL,

    Address     NVARCHAR (70),

    City        NVARCHAR (40),

    State       NVARCHAR (2),

    ZipCode  NVARCHAR (10),

    Phone       NVARCHAR (24),

    Cell        NVARCHAR (24),

    Email       NVARCHAR (60) NOT NULL

);

INSERT INTO Customers(FirstName, LastName, Address, City, State, ZipCode, Phone,Cell, Email) VALUES('Kaladin','King','4 Radiant Rd','Columbus','OH','43222','614-222-1771', '..d@mydomain.com');

INSERT INTO Customers(FirstName, LastName, Address, City, State, ZipCode, Phone,Cell, Email) VALUES('Biggs','Darklighter','43 Yavin Blvd','Reynoldsburg','OH','12567','330-124-4533', '..g@mydomain.com');

INSERT INTO Customers(FirstName, LastName, Address, City, State, ZipCode, Phone,Cell, Email) VALUES('Arya','Stark','10 Direwolf Lane','Columbus','OH','43222','614-342-5643', '..l@mydomain.com');

INSERT INTO Customers(FirstName, LastName, Address, City, State, ZipCode, Phone,Cell, Email) VALUES('Sarah','Connor','1984 Terminal Rd','Chicago','IL','12578','222-343-8786', '..l@mydomain.com');

INSERT INTO Customers(FirstName, LastName, Address, City, State, ZipCode, Phone,Cell, Email) VALUES('Malcolm,','Reynolds','22 Serenity Blvd','Hilliard','OH','44587','', '..y@mydomain.com');

select * from customers;

 

 

 

 

CREATE TABLE Customer_payments (

        PaymentId   INTEGER       PRIMARY KEY AUTO_INCREMENT

                                NOT NULL,

    CustomerId  INTEGER       NOT NULL,

    CardType   NVARCHAR (20) NOT NULL,

    CardNumber     NVARCHAR (20) NOT NULL,

    CCV    NVARCHAR (70),

    ExpDate       DATETIME        NOT NULL,

        FOREIGN KEY (

        CustomerId

    )

    REFERENCES Customers (CustomerId) ON DELETE NO ACTION

                                      ON UPDATE NO ACTION

 

);

 

INSERT INTO Customer_payments(CustomerID, CardType, CardNumber, CCV, ExpDate) VALUES(1, 'Visa', '1111 2222 3333 4444', '123','2022-01-00');

INSERT INTO Customer_payments(CustomerID, CardType, CardNumber, CCV, ExpDate) VALUES(1, 'Discover', '4444 5555 6666 7777', '321','2022-02-00');

INSERT INTO Customer_payments(CustomerID, CardType, CardNumber, CCV, ExpDate) VALUES(2, 'Visa', '2345 6789 1234 5678', '111','2023-07-00');

INSERT INTO Customer_payments(CustomerID, CardType, CardNumber, CCV, ExpDate) VALUES(3, 'Amex', '9999 8888 7777 6666', '888','2023-03-00');

INSERT INTO Customer_payments(CustomerID, CardType, CardNumber, CCV, ExpDate) VALUES(4, 'Visa', '3456 2222 9876 1234', '444','2022-01-00');

INSERT INTO Customer_payments(CustomerID, CardType, CardNumber, CCV, ExpDate) VALUES(4, 'Discover', '5678 1234 3456 4477', '777','2023-05-00');

INSERT INTO Customer_payments(CustomerID, CardType, CardNumber, CCV, ExpDate) VALUES(4, 'MC', '1122 2233 3344 4455', '333','2022-03-00');

INSERT INTO Customer_payments(CustomerID, CardType, CardNumber, CCV, ExpDate) VALUES(5, 'Visa', '4322 5433 6544 7655', '222','2022-02-00');

select * from Customer_payments;

 

 

CREATE TABLE Employees (

    EmployeeId INTEGER       PRIMARY KEY AUTO_INCREMENT

                             NOT NULL,

    LastName  NVARCHAR (20) NOT NULL,

    FirstName NVARCHAR (20) NOT NULL,

    BirthDate DATETIME,

    HireDate  DATETIME,

    Address   NVARCHAR (70),

    City      NVARCHAR (40),

    State     NVARCHAR (40),

    ZipCode NVARCHAR (10),

    Phone     NVARCHAR (24),

    Email     NVARCHAR (60)

);

 

 

INSERT INTO Employees(LastName, FirstName, BirthDate, HireDate, Address, City, State, ZipCode, Phone, Email) VALUES('Cooper','Gina','1991-07-05', '2020-01-01','2187 SQL Lane', 'Columbus','OH','12345', '..r@fakeemail.com');

INSERT INTO Employees(LastName, FirstName, BirthDate, HireDate, Address, City, State, ZipCode, Phone, Email) VALUES('Hill','Ed','1990-02-07', '2020-01-05','314 Pi Rd', 'Reynoldsburg','OH','12356', '..l@fakeemail.com');

INSERT INTO Employees(LastName, FirstName, BirthDate, HireDate, Address, City, State, ZipCode, Phone, Email) VALUES('Walker','Russ','1989-03-05', '2020-02-02','345 Excel Rd', 'Columbus','OH','12344', '..r@fakeemail.com');

INSERT INTO Employees(LastName, FirstName, BirthDate, HireDate, Address, City, State, ZipCode, Phone, Email) VALUES('Bird','Rick','1990-07-15', '2020-05-01','123 Sharp Blvd', 'Pickerington','OH','12346', '..d@fakeemail.com');

 

 

CREATE TABLE Sides (

    SideId INTEGER       PRIMARY KEY AUTO_INCREMENT

                             NOT NULL,

    SideName  NVARCHAR (40) NOT NULL,

    SideDescription  NVARCHAR (70) NOT NULL,

    BasePrice NUMERIC (10, 2) NOT NULL

 

 

);

 

 

INSERT INTO Sides(SideName, SideDescription, BasePrice) VALUES( 'Breadsticks', 'Breadsticks with Pizza dipping sauce', 4.50);

INSERT INTO Sides(SideName, SideDescription, BasePrice) VALUES( 'Fries', 'Hand cut french fries with ketchup', 4.00);

INSERT INTO Sides(SideName, SideDescription, BasePrice) VALUES( 'Wings', '6 Boneless chicken wings with bbq sauce', 6.50);

INSERT INTO Sides(SideName, SideDescription, BasePrice) VALUES( 'Calzone', 'Pepperoni and Sausage', 8.00);

INSERT INTO Sides(SideName, SideDescription, BasePrice) VALUES( 'Salad', 'Garden salad with Italian dressing', 5.50);

INSERT INTO Sides(SideName, SideDescription, BasePrice) VALUES( 'Medium Fountain drink', '16 ounce fountain drink', 2.50);

INSERT INTO Sides(SideName, SideDescription, BasePrice) VALUES( 'Large Fountain drink', '24 ounce fountain drink', 3.50);

INSERT INTO Sides(SideName, SideDescription, BasePrice) VALUES( 'Cookie', 'Large chocolate chunk cookie', 1.50);

INSERT INTO Sides(SideName, SideDescription, BasePrice) VALUES( 'Brownie', 'Triple chocolate brownie', 2.00);

select * from Sides;

 

CREATE TABLE Topping (

    ToppingId INTEGER       PRIMARY KEY AUTO_INCREMENT

                             NOT NULL,

    ToppingName NVARCHAR (20) NOT NULL,

    ToppingPrice  NUMERIC (10, 2) NOT NULL

);

 

INSERT INTO Topping(ToppingName, ToppingPrice) VALUES( 'Salami',1.00);

INSERT INTO Topping(ToppingName, ToppingPrice) VALUES( 'Pepperoni',1.00);

INSERT INTO Topping(ToppingName, ToppingPrice) VALUES( 'Ham',1.00);

INSERT INTO Topping(ToppingName, ToppingPrice) VALUES( 'Sausage',1.00);

INSERT INTO Topping(ToppingName, ToppingPrice) VALUES( 'Green peppers',1.00);

INSERT INTO Topping(ToppingName, ToppingPrice) VALUES( 'Onions',1.00);

INSERT INTO Topping(ToppingName, ToppingPrice) VALUES( 'Mushrooms',1.00);

INSERT INTO Topping(ToppingName, ToppingPrice) VALUES( 'Anchovies',2.00);

INSERT INTO Topping(ToppingName, ToppingPrice) VALUES( 'Red peppers',1.00);

INSERT INTO Topping(ToppingName, ToppingPrice) VALUES( 'Zucchini',1.00);

INSERT INTO Topping(ToppingName, ToppingPrice) VALUES( 'Basil',0.50);

INSERT INTO Topping(ToppingName, ToppingPrice) VALUES( 'Banana peppers',1.00);

INSERT INTO Topping(ToppingName, ToppingPrice) VALUES( 'Fresh Mozzarella',1.50);

INSERT INTO Topping(ToppingName, ToppingPrice) VALUES( 'Goat Cheese',1.50);

INSERT INTO Topping(ToppingName, ToppingPrice) VALUES( 'Parmesan',1.00);

INSERT INTO Topping(ToppingName, ToppingPrice) VALUES( 'Bacon',2.00);

INSERT INTO Topping(ToppingName, ToppingPrice) VALUES( 'BBQ Chicken',1.50);

select * from Topping;

 

 

 

 

CREATE TABLE Pizza (

    PizzaId INTEGER       PRIMARY KEY AUTO_INCREMENT

                             NOT NULL,

    PizzaName  NVARCHAR (40) NOT NULL,

    PizzaDescription  NVARCHAR (70) NOT NULL,

    BasePrice NUMERIC (10, 2) NOT NULL

 

 

);

 

INSERT INTO Pizza(PizzaName, PizzaDescription, BasePrice) VALUES( 'Small  Pizza', '10" hand tossed cheese pizza with your choice of toppings', 10.00);

INSERT INTO Pizza(PizzaName, PizzaDescription, BasePrice) VALUES( 'Kids Pizza', '6" cheese pizza with your choice of toppings', 5.00);

INSERT INTO Pizza(PizzaName, PizzaDescription, BasePrice) VALUES( 'Medium Pizza', '12" and tossed cheese pizza with your choice of toppings', 12.00);

INSERT INTO Pizza(PizzaName, PizzaDescription, BasePrice) VALUES( 'Large Pizza', '14" hand tossed cheese pizza with your choice of toppings', 14.00);

INSERT INTO Pizza(PizzaName, PizzaDescription, BasePrice) VALUES( 'Gluten Free Pizza', '12" hand tossed cheese pizza with your choice of toppings', 15.00);

select * from Pizza;

 

 

CREATE TABLE Pizza_Topping (

    PizzaToppingId INTEGER       PRIMARY KEY AUTO_INCREMENT

                             NOT NULL,

    PizzaID  INTEGER,

    ToppingID INTEGER,

    Placement  NVARCHAR (1),   

 

    FOREIGN KEY (

        PizzaId

    )

    REFERENCES Pizza (PizzaId) ON DELETE NO ACTION

                                      ON UPDATE NO ACTION,

    FOREIGN KEY (

        ToppingId

    )

    REFERENCES Topping(ToppingId) ON DELETE NO ACTION

                                      ON UPDATE NO ACTION

 

);

 

INSERT INTO Pizza_Topping(PizzaID, ToppingID, Placement) VALUES(1,2, 'F'  );

INSERT INTO Pizza_Topping(PizzaID, ToppingID, Placement) VALUES(4,2, 'R'  );

INSERT INTO Pizza_Topping(PizzaID, ToppingID, Placement) VALUES(4,4, 'L'  );

INSERT INTO Pizza_Topping(PizzaID, ToppingID, Placement) VALUES(4,6, 'F'  );

INSERT INTO Pizza_Topping(PizzaID, ToppingID, Placement) VALUES(4,7, 'F'  );

INSERT INTO Pizza_Topping(PizzaID, ToppingID, Placement) VALUES(3,10, 'F'  );

INSERT INTO Pizza_Topping(PizzaID, ToppingID, Placement) VALUES(3,12, 'F'  );

INSERT INTO Pizza_Topping(PizzaID, ToppingID, Placement) VALUES(3,2, 'R'  );

INSERT INTO Pizza_Topping(PizzaID, ToppingID, Placement) VALUES(1,2, 'F'  );

INSERT INTO Pizza_Topping(PizzaID, ToppingID, Placement) VALUES(5,NULL, 'F'  );

INSERT INTO Pizza_Topping(PizzaID, ToppingID, Placement) VALUES(2,NULL, 'F'  );

INSERT INTO Pizza_Topping(PizzaID, ToppingID, Placement) VALUES(1,7, 'F'  );

INSERT INTO Pizza_Topping(PizzaID, ToppingID, Placement) VALUES(1,5, 'F'  );

INSERT INTO Pizza_Topping(PizzaID, ToppingID, Placement) VALUES(4,2, 'F'  );

INSERT INTO Pizza_Topping(PizzaID, ToppingID, Placement) VALUES(4,4, 'F'  );

select * from Pizza_Topping;

 

CREATE TABLE Orders(

    OrderId         INTEGER         PRIMARY KEY AUTO_INCREMENT

                                      NOT NULL,

    CustomerId        INTEGER         NOT NULL,

     EmployeeId        INTEGER         NOT NULL,

    PaymentID     INTEGER,

    OrderDate       DATETIME        NOT NULL,

 

   

    FOREIGN KEY (

        CustomerId

    )

    REFERENCES Customers (CustomerId) ON DELETE NO ACTION

                                      ON UPDATE NO ACTION,

    FOREIGN KEY (

        PaymentId

    )

    REFERENCES Customer_payments(PaymentId) ON DELETE NO ACTION

                                      ON UPDATE NO ACTION,

    FOREIGN KEY (

        EmployeeId

    )

    REFERENCES Employees(EmployeeId) ON DELETE NO ACTION

                                      ON UPDATE NO ACTION

);

 

 

INSERT INTO Orders(CustomerID, EmployeeID, PaymentID, OrderDate) VALUES(1, 1,1, '2020-09-09');

INSERT INTO Orders(CustomerID, EmployeeID, PaymentID, OrderDate) VALUES(1, 3,2, '2020-09-10');

INSERT INTO Orders(CustomerID, EmployeeID, PaymentID, OrderDate) VALUES(2, 2,1, '2020-09-15');

INSERT INTO Orders(CustomerID, EmployeeID, PaymentID, OrderDate) VALUES(3, 3,1, '2020-09-22');

INSERT INTO Orders(CustomerID, EmployeeID, PaymentID, OrderDate) VALUES(3, 4,1, '2020-10-03');

INSERT INTO Orders(CustomerID, EmployeeID, PaymentID, OrderDate) VALUES(4, 3,1, '2020-10-04');

INSERT INTO Orders(CustomerID, EmployeeID, PaymentID, OrderDate) VALUES(5, 2,1, '2020-10-09');

INSERT INTO Orders(CustomerID, EmployeeID, PaymentID, OrderDate) VALUES(3, 4,1, '2020-10-10');

INSERT INTO Orders(CustomerID, EmployeeID, PaymentID, OrderDate) VALUES(4, 4,2, '2020-10-15');

INSERT INTO Orders(CustomerID, EmployeeID, PaymentID, OrderDate) VALUES(4, 1,3, '2020-10-16');

select * from Orders;

 

CREATE TABLE Order_details_pizza (

    OrderDetailsPizzaId INTEGER         PRIMARY KEY AUTO_INCREMENT

                                  NOT NULL,

    OrderId    INTEGER         NOT NULL,

    PizzaToppingId     INTEGER,

    Quantity INTEGER ,

    FOREIGN KEY (

        OrderId

    )

    REFERENCES Orders (OrderId) ON DELETE NO ACTION

                                    ON UPDATE NO ACTION,

   

    FOREIGN KEY (

       PizzaToppingId

    )

    REFERENCES Pizza_Topping(PizzaToppingId) ON DELETE NO ACTION

                                ON UPDATE NO ACTION

);

 

 

INSERT INTO Order_details_pizza(OrderId, PizzaToppingID, Quantity) VALUES(1, 4,1 );

INSERT INTO Order_details_pizza(OrderId, PizzaToppingID, Quantity) VALUES(2, 2,1 );

INSERT INTO Order_details_pizza(OrderId, PizzaToppingID, Quantity) VALUES(2, 3,1 );

INSERT INTO Order_details_pizza(OrderId, PizzaToppingID, Quantity) VALUES(3, 4,2 );

INSERT INTO Order_details_pizza(OrderId, PizzaToppingID, Quantity) VALUES(4, 14,3 );

INSERT INTO Order_details_pizza(OrderId, PizzaToppingID, Quantity) VALUES(4, 12,1 );

INSERT INTO Order_details_pizza(OrderId, PizzaToppingID, Quantity) VALUES(5, 11,1 );

INSERT INTO Order_details_pizza(OrderId, PizzaToppingID, Quantity) VALUES(6, 5,1 );

INSERT INTO Order_details_pizza(OrderId, PizzaToppingID, Quantity) VALUES(6, 6,1 );

INSERT INTO Order_details_pizza(OrderId, PizzaToppingID, Quantity) VALUES(7, 14,2 );

INSERT INTO Order_details_pizza(OrderId, PizzaToppingID, Quantity) VALUES(7, 10,1 );

INSERT INTO Order_details_pizza(OrderId, PizzaToppingID, Quantity) VALUES(8, 14,1 );

INSERT INTO Order_details_pizza(OrderId, PizzaToppingID, Quantity) VALUES(8, 13,1 );

INSERT INTO Order_details_pizza(OrderId, PizzaToppingID, Quantity) VALUES(9, 7,1 );

INSERT INTO Order_details_pizza(OrderId, PizzaToppingID, Quantity) VALUES(10, 8,1 );

INSERT INTO Order_details_pizza(OrderId, PizzaToppingID, Quantity) VALUES(10, 14,5 );

INSERT INTO Order_details_pizza(OrderId, PizzaToppingID, Quantity) VALUES(10, 9,1 );

select * from Order_details_pizza;

 

 

 

 

 

CREATE TABLE Order_details_sides (

    OrderDetailsSidesId INTEGER         PRIMARY KEY AUTO_INCREMENT

                                  NOT NULL,

    OrderId    INTEGER         ,

    SideId    INTEGER,

    Quantity  INTEGER , 

    FOREIGN KEY (

        OrderId

    )

    REFERENCES Orders (OrderId) ON DELETE NO ACTION

                                    ON UPDATE NO ACTION,

    FOREIGN KEY (

        SideID

    )

    REFERENCES Sides(SideId) ON DELETE NO ACTION

                                ON UPDATE NO ACTION

);

 

 

INSERT INTO Order_details_sides(OrderId, SideID, Quantity) VALUES(1, 1,1 );

INSERT INTO Order_details_sides(OrderId, SideID, Quantity) VALUES(1, 3,2 );

INSERT INTO Order_details_sides(OrderId, SideID, Quantity) VALUES(10, 7,10 );

INSERT INTO Order_details_sides(OrderId, SideID, Quantity) VALUES(3, 1,2 );

INSERT INTO Order_details_sides(OrderId, SideID, Quantity) VALUES(7, 1,1 );

INSERT INTO Order_details_sides(OrderId, SideID, Quantity) VALUES(2, 5,2 );

INSERT INTO Order_details_sides(OrderId, SideID, Quantity) VALUES(2, 1,3 );

INSERT INTO Order_details_sides(OrderId, SideID, Quantity) VALUES(2, 8,1 );

select * from Order_details_sides