T-SQL-7-1 Create a stored procedure that lists the customer id, last name and nu
ID: 3596164 • Letter: T
Question
T-SQL-7-1
Create a stored procedure that lists the customer id, last name and number of guests for all parties served by a server whose employee id is entered as a parameter. Print an error message if that server has not waited on any guests. Print a different error message if that server ID does not exist in the employee table.
DATABASE (key fields are underlined)
EMPLOYEE TABLE: EMP_ID, EMP_LNAME, EMP_STREET, EMP_CITY, EMP_STATE, EMP_ZIP, EMP_START_DATE
TABLES TABLE: TABLE_ID, AREA_ID, TABLE_SEATS
AREA TABLE: AREA_ID, AREA_NAME, AREA_SUPERVISOR_EMPLOYEE_ID
CUSTOMER TABLE: CUST_ID, CUST_LAST_NAME, CUST_NUMBER_OF_GUESTS
ASSIGNMENT TABLE: EMP_ID, TABLE_ID
SEATING TABLE: CUST_ID, TABLE_ID, SEATING_COST, SEATING_DATE, SEATING_TIP
Explanation / Answer
create or replace procedure CUSTOMER_GUESTS_PROC (
CUST_ID out number ,
CUST_LAST_NAME out varchar2 ,
CUST_NUMBER_OF_GUESTS out number ,
empId in out number
)
is
begin
SELECT CUST_ID, CUST_LAST_NAME, CUST_NUMBER_OF_GUESTS INTO CUST_ID, CUST_LAST_NAME, CUST_NUMBER_OF_GUESTS FROM CUSTOMER WHERE CUST_ID = (SELECT CUST_ID FROM SEATING WHERE TABLE_ID = (SELECT TABLE_ID FROM ASSIGNMENT WHERE EMP_ID = (SELECT EMP_ID FROM EMPLOYEE WHERE EMP_ID = empId)));
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('server ID does not exist in the employee table');
end CUSTOMER_GUESTS_PROC;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.