Question

There are five categories of data to be stored in the database: Ingredients Ingredient Name Amount in Stock Menu Items Menu Item Name Ingredients Regular Price Sale Price Customers Customer Name Customer Address Ingredients Allergic To Employees Social Security Number Employee Name Employee Address Hourly Wage Sales Data Transaction Date and Time Server (i.e. Employee) ID Customer ID Menu Items Sold Price Each Item Sold At

Answer 1

Short Summary:

• Have created the given tables and formed the sql queries as per the requirement

**************Please do upvote to appreciate our time. Thank you!******************

Solution:

1. Query 1:

select menuItemName from MenuItems where Ingredients not in (select IngredientsAllergicTo from Customer where customerID=100)

2. Query 2:

select menuItemName from MenuItems where saleprice is not null and saleprice < regularPrice

3. Query 3:

select sum(priceSoldAt) As 'Revenue' from salesData
where MenuItemsSold in (select menuID from MenuItems where menuItemName='Fish Fry')
group by MenuItemsSold

Detailed Queries and output:

CREATE TABLES:

CREATE DATABASE MenuIT
USE MenuIT
CREATE TABLE Employees(
SSN INT PRIMARY KEY,
employeeName VARCHAR(20),
employeeAddress VARCHAR(100),
HourlyWage INT
)


CREATE TABLE Customer (
CustomerID INT PRIMARY KEY,
CustomerName varchar(255),
CustomerAddress varchar(255),
IngredientsAllergicTo varchar(255)
);

CREATE TABLE MenuItems (
menuID INT PRIMARY KEY,
menuItemName varchar(255),
Ingredients varchar(255),
regularPrice decimal(18,2),
saleprice decimal(18,2)
);

CREATE TABLE SalesData (
transactionDate DATETIME,
ServerID INT,
customerID decimal(18,2),
MenuItemsSold INT,
priceSoldAt decimal(18,2),
FOREIGN KEY(ServerID) REFERENCES Employees(SSN),
FOREIGN KEY(MenuItemsSold) REFERENCES MenuItems(menuID),
);

Test Data:

INSERT INTO MenuItems VALUES (1, 'Mushroom Pepper Fry', 'Mushroom', 10, 5)
INSERT INTO MenuItems VALUES (2, 'Avacado Sandwich', 'Avacado', 13.50, 17.50)
INSERT INTO MenuItems VALUES (3, 'Fish Fry', 'Fish', 20.50, 15.50)

INSERT INTO Customer VALUES(100, 'Kevin peter', 'Atlanta, GA', 'Avacado')
INSERT INTO Employees VALUES(123456789, 'Mai', 'Dunwoody, GA', 14.50)

INSERT INTO SalesData VALUES(GETDATE(), 123456789, 100, 3, 15)
INSERT INTO SalesData VALUES(GETDATE(), 123456789, 100, 2, 10)
INSERT INTO SalesData VALUES(GETDATE(), 123456789, 100, 3, 15)

Query 1:

-- For customer with id = 100
select menuItemName from MenuItems where Ingredients not in (select IngredientsAllergicTo from Customer where customerID=100)

Query 2:

Query 3:
select sum(priceSoldAt) As 'Revenue' from salesData
where MenuItemsSold in (select menuID from MenuItems where menuItemName='Fish Fry')
group by MenuItemsSold

**************Please do upvote to appreciate our time. Thank you!******************