Q1. CREATE TABLES BELOW: -- Reservation CREATE TABLE Reservation (Res_ID INT (9)

Question

Q1. CREATE TABLES BELOW:

-- Reservation

CREATE TABLE Reservation (Res_ID INT (9) NOT NULL, Time TIME NOT NULL, Date DATE NOT NULL, Num_ppl INT(3) NOT NULL, cust_firstname VARCHAR(30), cust_lastname VARCHAR(30) NOT NULL, Phone INT(10) NOT NULL, Res_type VARCHAR(10) NOT NULL, Seat_pref VARCHAR(30) NOT NULL, Smoke_pref VARCHAR(30) NOT NULL, Group_name VARCHAR(30) NOT NULL, Payment VARCHAR(10) NOT NULL, CONSTRAINT Res_PK PRIMARY KEY (Res_ID));

--Table

CREATE TABLE Cust_Table (Table_ID INT(9) NOT NULL, Description VARCHAR(30) NOT NULL, Res_ID INT(9) NOT NULL, CONSTRAINT Table_PK PRIMARY KEY (Table_ID), CONSTRAINT Res_FK FOREIGN KEY (Res_ID) REFERENCES Reservation (Res_ID));

--Employee

CREATE TABLE Employee (Employee_ID INT(9) NOT NULL, Dateofbirth DATE NOT NULL, Emp_firstname VARCHAR(30) NOT NULL, Emp_lastname VARCHAR(30) NOT NULL, DriverLicNumber INT(10) NOT NULL, Title VARCHAR(10) NOT NULL, Manager_name VARCHAR(10) NOT NULL, CONSTRAINT Employee_PK PRIMARY KEY (Employee_ID));

--Menu

CREATE TABLE Menu (Item_ID INT(10) NOT NULL, Description VARCHAR(50) NOT NULL, Preptime VARCHAR(20) NOT NULL, Price VARCHAR(9) NOT NULL, CONSTRAINT Item_PK PRIMARY KEY (Item_ID));

--Order

CREATE TABLE Cust_Order (Order_ID INT(10) NOT NULL, Res_ID INT(10) NOT NULL, Item_ID INT(10) NOT NULL, Quantity INT(10) NOT NULL, Date DATE NOT NULL, Time TIME NOT NULL, CONSTRAINT Order_PK PRIMARY KEY (Order_ID), CONSTRAINT Res_FK FOREIGN KEY (Res_ID) REFERENCES Reservation (Res_ID), CONSTRAINT Item_FK FOREIGN KEY (Item_ID) REFERENCES Menu (Item_ID));

Q2. INSERT THE VALUES TO THE TABLES CREATED:

---Reservation Table Data

INSERT INTO Reservation VALUES ('0001', '11:30', '2-NOV-16', '2', 'John', 'Smith', '9875652245', 'Indi', 'Next to Window', 'No', 'No Group', 'card');

INSERT INTO Reservation VALUES ('0002', '13:30', '7-NOV-16', '5', 'Ben', 'Wild', '2349763859', 'Indi', 'close to the salad bar', 'No', 'No Group', 'cash');

INSERT INTO Reservation VALUES ('0003', '19:00', '10-NOV-16', '12', 'Janna', 'Emily', '3365562876', 'group', 'table with flowers', 'yes', 'Party Group', 'card');

INSERT INTO Reservation VALUES ('0004', '17:15', '15-NOV-16', '15', 'Andrew', 'Kane', '7042276749', 'Group', 'Right side of the bar', 'No', 'Biker Boyz', 'cash');

---Cust Table Data

INSERT INTO Cust_Table VALUES ('025', 'Quiet Zone', '0001');

INSERT INTO Cust_Table VALUES ('076', 'Bar Time', '0004');

INSERT INTO Cust_Table VALUES ('102', 'Birth Party', '0002');

INSERT INTO Cust_Table VALUES ('567', 'Smoking Zone', '0003');

--Employee Table Data

INSERT INTO Employee VALUES ('00345', 'Hanna', 'Jones', 'MaitreD', '05-20-1980', '33456832', 'No Manager');

INSERT INTO Employee VALUES ('29865', 'Leo', 'Smith', 'Dish Washer', '06-12-1983', '23758392', 'Cary Russell');

INSERT INTO Employee VALUES ('12846', 'David', 'Young', 'Waiter', '11-15-1991', '48577584', 'Hanna Jones');

INSERT INTO Employee VALUES ('12375', 'Scott', 'Brown', 'Cook', '11-21-1988', '25357156', 'Cary Russell');

INSERT INTO Employee VALUES ('87952', 'Cary', 'Russell', 'Chef', '07-07-1964', '23574512', 'No Manager');

--Menu Table Data

INSERT INTO Menu VALUES ('045', 'Appetizer', '8-10 min', '9.95');

INSERT INTO Menu VALUES ('176', 'Entree', '15-20 min', '14.45');

INSERT INTO Menu VALUES ('390', 'Desert', '5-10 min', '5.95');

--Cust_Order Table Data

INSERT INTO Cust_Order VALUES ('012', '0001', '045', '2', '2-NOV-16', '12:00');

INSERT INTO Cust_Order VALUES ('020', '0002', '176', '3', '7-NOV-16', '14:00');

INSERT INTO Cust_Order VALUES ('033', '0003', '390', '1', '10-NOV-16', '19:15');

INSERT INTO Cust_Order VALUES ('055', '0004', '045', '7', '15-NOV-16', '17:35');

ANSWER THESE QUESTIONS:

1. List in alphabetical order the names of all chefs and cooks, their date of birth, and the names of their mangers.
2. List all banquet reservations in chronological order together with the group name, name of the person making the reservation, time, date, and number of people.
3. For a specific date e.g., November 24, 2016, list all menu items that have been ordered, their names, and their prices.
4. For a specific date, e.g., November 24, 2016 and for each table, list the table description, its reservations’ information including time and the name of the person making the reservation.
5. Provide the count of employees by type (i.e., manager, Maitre’D, waiter, etc.)

Explanation / Answer

1)

SELECT Emp_firstname,Emp_lastname,Manager_name,dateof_birth FROM Employee
WHERE Title IN ('cook','chef')
ORDERED BY Emp-firstname|ASC;

--------------------------------------------------------------------------------
5)

SELECT count(Title) From Employee
GROUP BY Title;

----------------------------------------------------------------------------------
3)

SELECT * from Menu m INNER JOIN Order o
ON m.Item_ID=o.Item_ID
WHERE o.Date='24-NOV-2016';

-----------------------------------------------------------------------------------

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.