Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Sep 23, 2024

1. Write the query that returns the member's names and guest's names with the dish they selected for dinner. (Hint: UNION) 2. Include the query

1. Write the query that returns the member's names and guest's names with the dish they selected for dinner. (Hint: UNION)

2. Include the query that returns all the information from above and includes sides and entree for each person present in the event. (Hint: It is ok to have multiple rows for each person present in the event.)

1. Write the query that returns the member's names that did not attend the event. (Hint: OUTER JOIN)

2. Adding to the current answer, identify the members who RSVP and said they were not attending. Please identify the people that RSVP and the people that did not RSVP.

Let me know if there is anything wrong with the database that makes these question impossible to answer. Thanks!

Database below for reference...

CREATE TABLE IF NOT EXISTS Entree (

Entree_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

Entree_Name VARCHAR(50) NOT NULL,

Entree_Description TEXT NOT NULL,

Entree_Wine VARCHAR(20) NOT NULL,

Entree_IngredCost DECIMAL(5,2) NOT NULL,

Entree_PrepCost DECIMAL(5,2) NOT NULL,

CONSTRAINT ck_entreename

UNIQUE KEY (Entree_Name)

);

ALTER TABLE Entree AUTO_INCREMENT = 4;

INSERT INTO Entree (Entree_Name, Entree_Description, Entree_Wine, Entree_IngredCost, Entree_PrepCost)

VALUES ('Home Style Sliders', 'Your choice of chicken or turkey and toppings', 'Red', '5.99', '2.99');

INSERT INTO Entree (Entree_Name, Entree_Description, Entree_Wine, Entree_IngredCost, Entree_PrepCost)

VALUES ('Salad', 'Your choice of garden or caesar salad', 'Red', '7.99', '1.99');

INSERT INTO Entree (Entree_Name, Entree_Description, Entree_Wine, Entree_IngredCost, Entree_PrepCost)

VALUES ('Steak', '16oz New York Strip Steak', 'White', '12.99', '3.99');

INSERT INTO Entree (Entree_Name, Entree_Description, Entree_Wine, Entree_IngredCost, Entree_PrepCost)

VALUES ('Ribs', 'Your choice of full or half slab of St. Louis style ribs', 'Sparkly', '7.99', '2.99');

INSERT INTO Entree (Entree_Name, Entree_Description, Entree_Wine, Entree_IngredCost, Entree_PrepCost)

VALUES ('Spaghetti', 'Your choice of tomato or meat sauce', 'Sparkly', '4.99', '1.99');

CREATE TABLE IF NOT EXISTS Side (

Side_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

Side_Name VARCHAR(50) NOT NULL,

Side_Description TEXT NOT NULL,

Side_isSweet BOOLEAN NOT NULL DEFAULT 1,

Side_IngredCost DECIMAL(5,2) NOT NULL,

Side_PrepCost DECIMAL(5,2) NOT NULL,

CONSTRAINT ck_sidename

UNIQUE KEY (Side_Name)

);

ALTER TABLE Side AUTO_INCREMENT = 9;

INSERT INTO Side (Side_Name, Side_Description, Side_isSweet, Side_IngredCost, Side_PrepCost)

VALUES ('Baked Beans', 'Beans cooked in our homemade sauce', '1', '2.99', '0.30');

INSERT INTO Side (Side_Name, Side_Description, Side_isSweet, Side_IngredCost, Side_PrepCost)

VALUES ('Baked Potato', 'A baked potato served with a side of butter', '1', '1.99', '0.20');

INSERT INTO Side (Side_Name, Side_Description, Side_isSweet, Side_IngredCost, Side_PrepCost)

VALUES ('Coleslaw', 'Our sweet, homemade coleslaw', '1', '2.99', '0.30');

INSERT INTO Side (Side_Name, Side_Description, Side_isSweet, Side_IngredCost, Side_PrepCost)

VALUES ('Macaroni Salad', 'Made with onion, celery, green pepper, carrot, and pimentos', '1', '3.99', '0.40');

INSERT INTO Side (Side_Name, Side_Description, Side_isSweet, Side_IngredCost, Side_PrepCost)

VALUES ('Potato Salad', 'Made with hard-boiled eggs, sweet onion, relish, and celery', '1', '3.99', '0.25');

INSERT INTO Side (Side_Name, Side_Description, Side_isSweet, Side_IngredCost, Side_PrepCost)

VALUES ('BBQ Chips', 'Southern style, roasted BBQ chips', '0', '1.99', '0.67');

INSERT INTO Side (Side_Name, Side_Description, Side_isSweet, Side_IngredCost, Side_PrepCost)

VALUES ('French Fries', 'Homemade french fries', '0', '1.99', '0.18');

CREATE TABLE IF NOT EXISTS Member (

Mem_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

Mem_Name VARCHAR(20) NOT NULL,

Mem_Phone VARCHAR(10) NOT NULL,

Mem_Active BOOLEAN NOT NULL DEFAULT 1,

Add_ID INT NOT NULL,

CONSTRAINT fk_member_address

FOREIGN KEY (Add_ID) REFERENCES Address(Add_ID) ON UPDATE CASCADE

);

ALTER TABLE Member AUTO_INCREMENT = 31;

INSERT INTO Member (Mem_Name, Mem_Phone, Mem_Active, Add_ID)

VALUES ('John R. Smith', '9992315461', '1', 19);

INSERT INTO Member (Mem_Name, Mem_Phone, Mem_Active, Add_ID)

VALUES ('Bob L. Jones', '9995462584', '1', 20);

INSERT INTO Member (Mem_Name, Mem_Phone, Mem_Active, Add_ID)

VALUES ('Sandy E. Gordon', '9996365698', '1', 21);

INSERT INTO Member (Mem_Name, Mem_Phone, Mem_Active, Add_ID)

VALUES ('Larry P. Adams', '9992548763', '1', 22);

INSERT INTO Member (Mem_Name, Mem_Phone, Mem_Active, Add_ID)

VALUES ('Jason V. Alen', '9992513654', '1', 23);

INSERT INTO Member (Mem_Name, Mem_Phone, Mem_Active, Add_ID)

VALUES ('Mary T. Andrews', '9991254774', '1', 24);

INSERT INTO Member (Mem_Name, Mem_Phone, Mem_Active, Add_ID)

VALUES ('Sarah S. Blackmon', '9994005656', '1', 25);

INSERT INTO Member (Mem_Name, Mem_Phone, Mem_Active, Add_ID)

VALUES ('Rachel R. Gates', '9998785321', '1', 26);

INSERT INTO Member (Mem_Name, Mem_Phone, Mem_Active, Add_ID)

VALUES ('Michael A. Williams', '9995541242', '1', 27);

INSERT INTO Member (Mem_Name, Mem_Phone, Mem_Active, Add_ID)

VALUES ('Randy G. Jackson', '9997784321', '1', 28);

INSERT INTO Member (Mem_Name, Mem_Phone, Mem_Active, Add_ID)

VALUES ('Adam C. Finn', '9996672581', '1', 29);

INSERT INTO Member (Mem_Name, Mem_Phone, Mem_Active, Add_ID)

VALUES ('Luke H. James', '9993312159', '1', 30);

CREATE TABLE IF NOT EXISTS Invitation (

Invite_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

Invite_PrintDate DATETIME NOT NULL,

ClubEvent_ID INT NOT NULL,

Mem_ID INT NOT NULL,

CONSTRAINT fk_club_event_invitation

FOREIGN KEY (ClubEvent_ID) REFERENCES ClubEvent(ClubEvent_ID) ON UPDATE CASCADE,

CONSTRAINT fk_member_invitation

FOREIGN KEY (Mem_ID) REFERENCES Member(Mem_ID) ON UPDATE CASCADE,

CONSTRAINT ck_ClubEventID_MemID

UNIQUE KEY (ClubEvent_ID, Mem_ID)

);

ALTER TABLE Invitation AUTO_INCREMENT = 58;

CREATE TABLE IF NOT EXISTS RSVP (

RSVP_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

RSVP_isAttending BOOLEAN NOT NULL DEFAULT 1,

RSVP_ReceiveDate DATETIME NOT NULL,

Invite_ID INT NOT NULL,

CONSTRAINT fk_rsvp_invite

FOREIGN KEY (Invite_ID) REFERENCES Invitation(Invite_ID) ON UPDATE CASCADE,

CONSTRAINT ck_inviteID

UNIQUE KEY (Invite_ID)

);

ALTER TABLE RSVP AUTO_INCREMENT = 70;

CREATE TABLE IF NOT EXISTS PositiveRSVP (

PosRSVP_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

RSVP_ID INT NOT NULL,

CONSTRAINT fk_positive_rsvp

FOREIGN KEY (RSVP_ID) REFERENCES RSVP(RSVP_ID) ON UPDATE CASCADE

);

ALTER TABLE PositiveRSVP AUTO_INCREMENT = 80;

CREATE TABLE IF NOT EXISTS Dish (

Dish_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

Entree_ID INT NOT NULL,

Dish_Name VARCHAR(20) NOT NULL,

CONSTRAINT fk_dish_entree

FOREIGN KEY (Entree_ID) REFERENCES Entree(Entree_ID),

CONSTRAINT ck_dishname

UNIQUE KEY (Dish_Name)

);

ALTER TABLE Dish AUTO_INCREMENT = 43;

INSERT INTO Dish (Entree_ID, Dish_Name)

VALUES(4, 'Sliders');

INSERT INTO Dish (Entree_ID, Dish_Name)

VALUES(5, 'Salad');

INSERT INTO Dish (Entree_ID, Dish_Name)

VALUES(6, 'New York Strip');

INSERT INTO Dish (Entree_ID, Dish_Name)

VALUES(7, 'St. Louis Style Ribs');

INSERT INTO Dish (Entree_ID, Dish_Name)

VALUES(8, 'Spaghetti');

CREATE TABLE IF NOT EXISTS SideList (

SideList_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

Dish_ID INT NOT NULL,

Side_ID INT NOT NULL,

CONSTRAINT fk_sidelist_dish

FOREIGN KEY (Dish_ID) REFERENCES Dish(Dish_ID) ON UPDATE CASCADE,

CONSTRAINT fk_sidelist_side

FOREIGN KEY (Side_ID) REFERENCES Side(Side_ID) ON UPDATE CASCADE,

CONSTRAINT ck_dishID_sideID

UNIQUE KEY (Dish_ID, Side_ID)

);

ALTER TABLE SideList AUTO_INCREMENT = 48;

INSERT INTO SideList (Dish_ID, Side_ID)

VALUES (43, 9);

INSERT INTO SideList (Dish_ID, Side_ID)

VALUES (44, 10);

INSERT INTO SideList (Dish_ID, Side_ID)

VALUES (45, 11);

INSERT INTO SideList (Dish_ID, Side_ID)

VALUES (46, 12);

INSERT INTO SideList (Dish_ID, Side_ID)

VALUES (47, 13);

CREATE TABLE IF NOT EXISTS MenuDish (

MenuDish_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

Menu_ID INT NOT NULL,

Dish_ID INT NOT NULL,

CONSTRAINT fk_menudish_menuID

FOREIGN KEY (Menu_ID) REFERENCES Menu(Menu_ID) ON UPDATE CASCADE,

CONSTRAINT fk_menudish_dishID

FOREIGN KEY (Dish_ID) REFERENCES Dish(Dish_ID) ON UPDATE CASCADE,

CONSTRAINT ck_menuID_dishID

UNIQUE KEY (Menu_ID, Dish_ID)

);

ALTER TABLE MenuDish AUTO_INCREMENT = 53;

INSERT INTO MenuDish (Menu_ID, Dish_ID)

VALUES (1, 43);

INSERT INTO MenuDish (Menu_ID, Dish_ID)

VALUES (1, 44);

INSERT INTO MenuDish (Menu_ID, Dish_ID)

VALUES (1, 45);

INSERT INTO MenuDish (Menu_ID, Dish_ID)

VALUES (1, 46);

INSERT INTO MenuDish (Menu_ID, Dish_ID)

VALUES (1, 47);

CREATE TABLE IF NOT EXISTS MemberAttend (

Attend_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

GUEST BOOLEAN NOT NULL DEFAULT 1,

Invite_ID INT NOT NULL,

MenuDish_ID INT NOT NULL,

CONSTRAINT fk_memberattend_inviteID

FOREIGN KEY (Invite_ID) REFERENCES Invitation(Invite_ID) ON UPDATE CASCADE,

CONSTRAINT fk_memberattend_menudishID

FOREIGN KEY (MenuDish_ID) REFERENCES MenuDish(MenuDish_ID) ON UPDATE CASCADE

);

ALTER TABLE MemberAttend AUTO_INCREMENT = 90;

CREATE TABLE IF NOT EXISTS Guest (

Guest_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

G_FName VARCHAR(20) NOT NULL,

G_LName VARCHAR(20) NOT NULL,

Attend_ID INT NOT NULL,

MenuDish_ID INT NOT NULL,

CONSTRAINT fk_guest_attendID

FOREIGN KEY (Attend_ID) REFERENCES MemberAttend(Attend_ID) ON UPDATE CASCADE,

CONSTRAINT fk_guest_menudishID

FOREIGN KEY (MenuDish_ID) REFERENCES MenuDish(MenuDish_ID) ON UPDATE CASCADE

);

ALTER TABLE Guest AUTO_INCREMENT = 98;