Question

I need oracle SQL queries：

1. Find the first name and last name of all staff who are supervised by Wilma Smith and worked a breakfast meeting.

2. Find the number of staff who worked at morning events. Hints: use start_hour. Also, keep in mind that a staff member may have worked more than 1 morning event. We are looking for a count of distinct staff.

3. List the total staff cost of each event. Your query should display the location as well as the total staff cost. Staff cost can be calculated by multiplying the duration by the hourly_rate. Assume that each staff member did work the entire time of the event. Do not worry about overtime.

4. For each dish, list the description and the total number of staff who served the dish.

Richard's Catering Case Study Map staff event_staff course_item menu EVENT_ID SSN MAINCOURSE.ID DISH.ID MENU_ID MAINCOURSE.ID DESSERT ID STYLE event SSN LNAME FNAME ADDRESS ПcITY STATE ZIPCODE TELEPHONE HOURLY RATE SUPERVISOR SHIFT dish dessert EVENT_ID DEVENT_DATE EVENT TIME START_HOUR LOCATION EVENT_TYPE CUSTOMER_ID MENU_ID PLATES DURATION DESSERT_ID DESCRIPTION DISHID DESCRIPTION PREPTIME SERVICETYPE maincourse OMAINCOURSE.ID DESCRIPTION

Answer 1

Short Summary:

• Provided all DDL and DML queries.

Table Creation:

create table staff
(ssn varchar2(11) primary key,
lname varchar2(25),
fname varchar2(25),
address varchar2(30),
city varchar2(20),
state varchar2(2),
zipcode varchar2(10),
telephone varchar2(14),
hourly_rate number(4,2),
supervisor varchar2(11) references staff(ssn) null,
shift number(1));

create table maincourse
(maincourse_id varchar2(2) primary key,
description varchar2(40));

create table dessert
(dessert_id varchar2(2) primary key,
description varchar2(40));

create table menu
(menu_id varchar2(2) primary key,
maincourse_id varchar2(2) references maincourse(maincourse_id),
dessert_id varchar2(2) references dessert(dessert_id),
style number(1));

create table dish
(dish_id varchar2(5) primary key,
description varchar2(40),
preptime number(4,2),
servicetype number(2));

create table event
(event_id varchar2(6) primary key,
event_date date,
event_time varchar2(10),
start_hour number(2),
location varchar2(40),
event_type varchar2(20),
customer_id varchar2(4),
menu_id varchar2(2) references menu(menu_id),
plates number(5),
duration number(4,2));

create table event_staff
(event_id varchar2(6) references event(event_id),
ssn varchar2(11) references staff(ssn),
primary key (event_id,ssn));

create table course_item
(maincourse_id varchar2(2) references maincourse(maincourse_id),
dish_id varchar2(5) references dish(dish_id),
primary key (maincourse_id,dish_id));

Insertion to Tables:

//Insert IntoTables//

insert into staff
values('013-23-2121','Smith','Wilma','1023 High St','Boston','MA','02115','(617) 289-3212',26.00,'',1);
insert into staff
values('014-21-2331','Williams','Warren','213 West St','Boston','MA','02120','(617) 292-1212',20.00,'013-23-2121',1);
insert into staff
values('015-22-1212','Roth','Earl','542 Chestnut St','Natick','MA','01721','(781) 231-1211',18.50,'013-23-2121',2);
insert into staff
values('982-12-1212','Yolenda','Meredith','4832 Pleasant St','Boston','MA','02132','(617) 292-8432',19.00,'013-23-2121',2);
insert into staff
values('374-32-2121','Taylor','Ursula','352 Pike St','Framingham','MA','01701','(781) 212-2123',17.34,'982-12-1212',1);
insert into staff
values('014-54-4412','Lo','Ili','56 Harrison Ave.','Boston','MA','02121','(617) 867-1721',21.00,'013-23-2121',1);
insert into staff
values ('022-21-2352','Himenda','Herbert','1298 South Bay Road','Natick','MA','01721','(781) 231-2322',15.00,'982-12-1212',2);
insert into staff
values ('986-21-2721','Well','F.D.','863A Main St','Fitchburg','MA','01420','(978) 345-2392',19.00,'982-12-1212',1);

insert into maincourse
values('10','Chicken Picatta with Rice');
insert into maincourse
values('11','Chicken Picatta with Potato');
insert into maincourse
values('20','Pasta Primavera');
insert into maincourse
values('21','Baked Ziti');
insert into maincourse
values('30','Beef au Jous');
insert into maincourse
values('40','Omlet with Homefries');
insert into maincourse
values('41','French Toast');
insert into maincourse
values('42','Eggs Benedict');
insert into maincourse
values('89','Party Platter');

insert into dessert
values('1','Cheesecake w/ Strawberries');
insert into dessert
values('2','Chocolate Cake');
insert into dessert
values('3','Ice Cream');
insert into dessert
values('4','Fruit Torte');
insert into dessert
values('5','Chocolate Mousse');
insert into dessert
values('6','Cannoli');

insert into menu
values('01','10','1',2);
insert into menu
values('02','20','2',2);
insert into menu
values('03','30','1',2);
insert into menu
values('04','41','',2);
insert into menu
values('05','40','',2);
insert into menu
values('06','42','',2);
insert into menu
values('10','89','2',3);

insert into dish
values('AC-1','Rice Pilaf',0.4,3);
insert into dish
values('AC-4','Baked Potato',0.5,3);
insert into dish
values('AC-7','Scalloped Potato',0.7,3);
insert into dish
values('AP-1','Mixed Veggies',1,2);
insert into dish
values('AP-2','Stuffed Mushrooms',0.7,2);
insert into dish
values('AP-3','Fruit Cup',0.5,1);
insert into dish
values('AP-5','Cheese Tray',0.3,2);
insert into dish
values('AP-6','Cracker Tray',0.3,2);
insert into dish
values('BF-1','Steak',1,3);
insert into dish
values('BF-2','Steak Tips',1.05,3);
insert into dish
values('BF-3','Beef',1.8,3);
insert into dish
values('CX-1','Chicken Picatta',1.2,3);
insert into dish
values('CX-2','Chicken Cordon Bleu',2,3);
insert into dish
values('DK-1','Punch Bowl',0.2,2);
insert into dish
values('PA-1','Pasta Primavera',0.5,3);
insert into dish
values('PA-2','Lasagna',1.5,3);
insert into dish
values('PA-3','Baked Ziti',1.25,3);
insert into dish
values('PA-4','Spaghetti',0.5,3);
insert into dish
values('PA-5','Manicotti',1.25,3);
insert into dish
values('SA-1','Salad Bar',1,2);
insert into dish
values('SA-2','Tossed Salad',0.5,3);
insert into dish
values('SA-3','Ceaser Salad',0.5,3);
insert into dish
values('SP-1','Minestrone',0.3,1);
insert into dish
values('SP-2','Chicken Noodle',0.3,2);
insert into dish
values('VG-1','Vegetable Medley',0.4,3);
insert into dish
values('VG-2','Brocolli',0.3,3);
insert into dish
values('VG-3','Green Beans',0.2,3);
insert into dish
values('VG-4','Sweet Peas',0.3,3);
insert into dish
values('VM-1','Veggie Stir Fry',0.75,3);
insert into dish
values('BK-1','Omlet',0.01,4);
insert into dish
values('BK-2','French Toast',0.1,4);
insert into dish
values('BK-3','Eggs Benedict',0.2,4);
insert into dish
values('BK-4','Homefries',0.1,4);
insert into dish
values('BK-5','Sausage',0.1,4);
insert into dish
values('BK-6','Bacon',0.1,4);
insert into dish
values('BK-7','Canadian Bacon',0.2,4);

insert into event
values('99-001','23-JAN-1999','12:00 PM',12,'Boston Associates','Business Dinner','1290','02',400,3.5);
insert into event
values('99-002','15-FEB-1999','06:00 PM',18,'Worcester Lions Club','Wedding Reception','2120','03',275,4);
insert into event
values('99-003','14-MAY-1999','07:00 AM',7,'Westboro Lodge of Elks','Breakfast Meeting','1290','01',90,1);
insert into event
values('00-001','18-SEP-2000','06:00 AM',6,'Boston Elks','Breakfast Meeting','2501','06',100,2);
insert into event
values('01-001','20-OCT-2001','05:00 PM',17,'Framingham MEMA','Dinner','3212','02',400,5);
insert into event
values('01-002','24-DEC-2001','06:00 PM',18,'Westboro Lodge','Christmas Party','5463','10',1000,4);
insert into event
values('04-001','10-DEC-2004','05:00 PM',17,'Odd Fellows Hall','Dinner Meeting','5420','01',100,3);
insert into event
values('05-001','18-JAN-2005','07:00 PM',19,'Sportsman Club','Birthday Party','0125','03',125,4);

insert into event_staff
values('99-001','013-23-2121');
insert into event_staff
values('99-001','014-21-2331');
insert into event_staff
values('99-001','015-22-1212');
insert into event_staff
values('99-001','374-32-2121');
insert into event_staff
values('99-001','982-12-1212');
insert into event_staff
values('99-002','013-23-2121');
insert into event_staff
values('99-002','014-21-2331');
insert into event_staff
values('99-002','015-22-1212');
insert into event_staff
values('99-003','013-23-2121');
insert into event_staff
values('99-003','015-22-1212');
insert into event_staff
values('99-003','374-32-2121');
insert into event_staff
values('00-001','013-23-2121');
insert into event_staff
values('00-001','014-54-4412');
insert into event_staff
values('00-001','986-21-2721');
insert into event_staff
values('01-001','013-23-2121');
insert into event_staff
values('01-001','014-54-4412');
insert into event_staff
values('01-001','982-12-1212');
insert into event_staff
values('01-002','013-23-2121');
insert into event_staff
values('01-002','014-54-4412');
insert into event_staff
values('01-002','015-22-1212');
insert into event_staff
values('01-002','022-21-2352');
insert into event_staff
values('01-002','374-32-2121');
insert into event_staff
values('04-001','013-23-2121');
insert into event_staff
values('04-001','374-32-2121');
insert into event_staff
values('04-001','986-21-2721');
insert into event_staff
values('05-001','014-54-4412');
insert into event_staff
values('05-001','982-12-1212');
insert into event_staff
values('05-001','986-21-2721');

insert into course_item
values('10','AC-1');
insert into course_item
values('10','CX-1');
insert into course_item
values('10','SA-2');
insert into course_item
values('10','VG-2');
insert into course_item
values('11','AC-4');
insert into course_item
values('11','CX-1');
insert into course_item
values('11','SA-2');
insert into course_item
values('11','VG-2');
insert into course_item
values('20','PA-1');
insert into course_item
values('20','SA-2');
insert into course_item
values('20','SP-1');
insert into course_item
values('30','AC-4');
insert into course_item
values('30','BF-3');
insert into course_item
values('30','SA-2');
insert into course_item
values('30','VG-3');
insert into course_item
values('21','PA-3');
insert into course_item
values('21','SA-2');
insert into course_item
values('40','BK-1');
insert into course_item
values('40','BK-4');
insert into course_item
values('40','BK-5');
insert into course_item
values('41','BK-2');
insert into course_item
values('41','BK-5');
insert into course_item
values('42','BK-3');
insert into course_item
values('42','BK-6');
insert into course_item
values('89','AP-1');
insert into course_item
values('89','AP-2');
insert into course_item
values('89','AP-5');
insert into course_item
values('89','AP-6');
insert into course_item
values('89','DK-1');

SELECT * FROM staff;

SELECT * FROM staff; 20 21 SSN LNAME FNAME ADDRESS CITY STATE ZIPCODE TELEPHONE HOURLY_RATE SUPERVISOR SHIFT 013-23-2121 Smith Wilma 1023 High St Boston MA 02115 (617) 289-3212 26 1 014-21-2331 Williams Warren 213 West St Boston MA 02120 (617) 292-1212 20 013-23-2121 1 015-22-1212 Roth Earl 542 Chestnut St Natick MA 01721 (781) 231-1211 18.5 013-23-2121 2 982-12-1212 Yolenda Meredith 4832 Pleasant St Boston MA 02132 (617) 292-8432 19 013-23-2121 2 374-32-2121 Taylor Ursula 352 Pike St Framingham MA 01701 (781) 212-2123 17.34 982-12-1212 1 014-54-4412 Lo Ili 56 Harrison Ave. Boston MA 02121 (617) 867-1721 21 013-23-2121 1 022-21-2352 Himenda Herbert 1298 South Bay Road Natick MA 01721 (781) 231-2322 15 982-12-1212 2 986-21-2721 Well F.D. 863A Main St Fitchburg MA 01420 (978) 345-2392 19 982-12-1212 1

SELECT * FROM maincourse;

20 21 SELECT * FROM maincourse; MAINCOURSE_ID DESCRIPTION 10 Chicken Picatta with Rice 11 Chicken Picatta with Potato 20 Pasta Primavera 21 Baked Ziti 30 Beef au Jous 40 Omlet with Homefries 41 French Toast 42 Eggs Benedict Party Platter 89

SELECT * FROM dessert;

54 SELECT * FROM dessert; 55 1 DESSERT_ID DESCRIPTION 1 Cheesecake w/ Strawberries 2 Chocolate Cake 3 Ice Cream 4 Fruit Torte 5 Chocolate Mousse 6 Cannoli

SELECT * FROM menu;

54 SELECT * FROM menu; 55 | MENU_ID MAINCOURSE_ID DESSERT_ID STYLE 01 10 1 N 02 20 2 2 03 30 1 2 04 41 2 05 40 2 06 42 2 10 89 2 3

SELECT * FROM dish;

DISH_ID DESCRIPTION PREPTIME SERVICETYPE AC-1 Rice Pilaf .4 3 AC-4 Baked Potato .5 3 AC-7 Scalloped Potato .7 3 AP-1 Mixed Veggies 1 2 AP-2 Stuffed Mushrooms .7 2 AP-3 Fruit Cup .5 1 AP-5 Cheese Tray .3 2 AP-6 Cracker Tray .3 2 BF-1 Steak 1 3 BF-2 Steak Tips 1.05 3 BF-3 Beef 1.8 3 CX-1 Chicken Picatta 1.2 3 CX-2 Chicken Cordon Bleu 2 3 DK-1 Punch Bowl .2 2 PA-1 Pasta Primavera .5 3 PA-2 Lasagna 1.5 3 PA-3 Baked Ziti 1.25 3 PA-4 Spaghetti .5 3 PA-5 Manicotti 1.25 3 SA-1 Salad Bar 1 2 SA-2 Tossed Salad .5 3 SA-3 Ceaser Salad .5 3 SP-1 Minestrone 3 1 SP-2 Chicken Noodle .3 2 VG-1 Vegetable Medley .4 3 VG-2 Brocolli .3 3

VG-3 Green Beans .2 3 VG-4 Sweet Peas .3 3 VM-1 Veggie Stir Fry .75 3 BK-1 Omlet .01 4 BK-2 French Toast .1 4 BK-3 Eggs Benedict .2 4 BK-4 Homefries .1 4 BK-5 Sausage .1 4 BK-6 Bacon .1 4 BK-7 Canadian Bacon .2 4

SELECT * FROM event;

SELECT FROM event; 146 147 EVENT_ID EVENT_DATE EVENT_TIME START_HOUR LOCATION EVENT_TYPE CUSTOMER_ID MENU_ID PLATES DURATION 99-001 23-JAN-99 12:00 PM 12 Boston Associates Business Dinner 1290 02 400 3.5 99-002 15-FEB-99 06:00 PM 18 Worcester Lions Club Wedding Reception 2120 03 275 4 99-003 14-MAY-99 07:00 AM 7 Westboro Lodge of Elks Breakfast Meeting 1290 01 90 1 00-001 18-SEP-00 06:00 AM 6 Boston Elks Breakfast Meeting 2501 06 100 2 01-001 20-OCT-01 05:00 PM 17 Framingham MEMA Dinner 3212 02 400 5 01-002 24-DEC-01 06:00 PM 18 Westboro Lodge Christmas Party 5463 10 1000 4 04-001 10-DEC-04 05:00 PM 17 Odd Fellows Hall Dinner Meeting 5420 01 100 3 05-001 18-JAN-05 07:00 PM 19 Sportsman Club Birthday Party 0125 03 125 4

SELECT * FROM event_staff;

EVENT_ID SSN 00-001 013-23-2121 00-001 014-54-4412 00-001 986-21-2721 01-001 013-23-2121 01-001 014-54-4412 01-001 982-12-1212 01-002 013-23-2121 01-002 014-54-4412 01-002 015-22-1212 01-002 022-21-2352 01-002 374-32-2121 04-001 013-23-2121 04-001 374-32-2121 04-001 986-21-2721 05-001 014-54-4412 05-001 982-12-1212

select * from course_item;

MAINCOURSE_ID DISH_ID 10 AC-1 10 CX-1 10 SA-2 10 VG-2 11 AC-4 11 CX-1 11 SA-2 11 VG-2 20 PA-1 20 SA-2 20 SP-1 21 PA-3 21 SA-2 30 AC-4 30 BF-3 30 SA-2 30 VG-3 40 BK-1 40 BK-4 40 BK-5 41 BK-2 41 BK-5 42 BK-3 42 BK-6 89 AP-1 89 AP-2

SQL QUERIES:

1) Find the first name and last name of all staff who are supervised by Wilma Smith and worked a breakfast meeting.

Answer:
SELECT fname, lname FROM staff WHERE supervisor = (SELECT SSN FROM Staff WHERE fname = 'Wilma' and lname = 'Smith') AND shift = 1;

21 22 SELECT fname, Iname FROM staff WHERE supervisor = (SELECT SSN FROM Staff WHERE fname = 'Wilma' and Iname = 'Smith') AND shift = 1; FNAME LNAME Warren Williams Ili Lo

2) Find the number of staff who worked at morning events. Hints: use start_hour. Also, keep in mind that a staff member may have worked more than 1 morning event. We are looking for a count of distinct staff.

Answer:

Assuming that statrt_hour less than 9 are morning events

SELECT COUNT(DISTINCT st.SSN) FROM staff st
INNER JOIN event_staff es ON es.SSN = st.SSN
INNER JOIN event ev ON ev.event_id = es.event_id
WHERE ev.start_hour < 9

1 2 3 4 5 SELECT COUNT(DISTINCT st.SSN) FROM staff st INNER JOIN event_staff es ON es. SSN = st.SSN INNER JOIN event ev ON ev.event_id es.event_id WHERE ev.start_hour < 9 COUNT (DISTINCTST.SSN) 5 Download CSV

3) List the total staff cost of each event. Your query should display the location as well as the total staff cost. Staff cost can be calculated by multiplying the duration by the hourly_rate. Assume that each staff member did work the entire time of the event. Do not worry about overtime.

Answer:

SELECT ev.event_id, ev.Location, SUM(ev.Duration * st.Hourly_Rate) Staff_Cost FROM staff st
INNER JOIN event_staff es ON es.SSN = st.SSN
INNER JOIN event ev ON ev.event_id = es.event_id
GROUP BY ev.event_id, ev.Location

1 2 3 4 5 SELECT ev.event_id, ev.Location, SUM(ev.Duration * st.Hourly_Rate) Staff_Cost FROM staff st INNER JOIN event_staff es ON es. SSN = st. SSN INNER JOIN event ev ON ev.event_id = es.event_id GROUP BY ev.event_id, ev.Location EVENT_ID LOCATION STAFF_COST 00-001 Boston Elks 132 01-002 Westboro Lodge 391.36 04-001 Odd Fellows Hall 187.02 99-001 Boston Associates 352.94 99-002 Worcester Lions Club 258 99-003 61.84 Westboro Lodge of Elks Sportsman Club 05-001 236 330 01-001 Framingham MEMA Download CSV 8 rows selected.

4)For each dish, list the description and the total number of staff who served the dish.

Answer:

SELECT di.Dish_ID, di.Description, COUNT(DISTINCT es.SSN) AS NoOfStaffs
FROM dish di
INNER JOIN Course_Item ci ON ci.Dish_ID = di.Dish_ID
INNER JOIN MainCourse mc ON mc.MainCourse_ID = ci.MainCourse_ID
INNER JOIN Menu me ON me.MainCourse_ID = mc.MainCourse_ID
INNER JOIN Event ev ON ev.Menu_ID = me.Menu_ID
INNER JOIN Event_Staff es ON es.Event_ID = ev.Event_ID
GROUP BY di.Dish_ID, di.Description

1 SELECT di .Dish_ID, di .Description, COUNT(DISTINCT es.SSN) AS NoOfStaffs 2 FROM dish di 3 INNER JOIN Course_Item ci ON ci.Dish_ID = di .Dish_ID 4 INNER JOIN Main Course mc ON mc. MainCourse_ID = ci.MainCourse_ID 5 INNER JOIN Menu me ON me. MainCourse_ID = mc. MainCourse_ID 6 INNER JOIN Event ev ON ev.Menu_ID = me. Menu_ID 7 INNER JOIN Event_Staff es ON es. Event_ID = ev. Event_ID 8 GROUP BY di.Dish_ID, di . Description a DISH_ID DESCRIPTION NOOFSTAFFS AP-6 Cracker Tray 5 DK-1 Punch Bowl 5 VG-2 Brocolli 4 BF-3 Beef 6 AC-4 Baked Potato 6 SP-1 Minestrone 6 AP-2 Stuffed Mushrooms 5 VG-3 Green Beans 6 AC-1 Rice Pilaf 4 BK-3 Eggs Benedict 3 SA-2 Tossed Salad 7 AP-1 Mixed Veggies 5 BK-6 Bacon 3 CX-1 Chicken Picatta 4 PA-1 Pasta Primavera 6 5 AP-5 Cheese Tray Download CSV 16 rows selected.

**************************************************************************************

Feel free to rate the answer and comment your questions, if you have any.

Please upvote the answer and appreciate our time.

Happy Studying!!!

**************************************************************************************