As many as possible Example: SELECT c.first_name, p.name, po.quantity, p.sell_pr
ID: 3864631 • Letter: A
Question
As many as possible
Example:
SELECT c.first_name, p.name, po.quantity, p.sell_price
FROM Customer c, Product p, Product_Order po, Orders o
WHERE p.product_id=po.product_id AND
po.order_id =o.order_id AND
c.customer_id=o.customer_id AND
c.last_name='Huang';
the tables are from dreamhome database.
L 2017s CPS3740 Google Dri x THE VAMPIRE NETWORK De X YouTube C Secure https:/ drive.google.com CPS3740/ Spring 2017 Homework #2 Page 1 of 2 Name: Due date: by 11:59pm on April 18, 2017 Please write stored procedures and stored functions based on the dreamhome database You must create and test your programs on database CPS3740 2017S at server imc.kean.edu. Your program names must EXACTLY follow each question format. The program name is case sensitive. XXXx means your email ID in the test case examples. The test cases in each question provide as the testing SQL queries with the corresponding results. You should test your programs with different input values and make sure the results are comect. The title of your output should also match the test cases 1. Please write a stored function named fHW2 i XXXX based on the Staff table. It should take one argument branchno and it should meet the following 2 test cases. 10 points) If the given branch is 1B. no (10 points) If the given branchno in the Staff table, please return the average salary of s not in the Staff table, please return a NULL value. the staff with the given branchno. mysql select fHw2 i XXXX('B009') as output; mysql select fHw2 i XXXX('B003') as output; output l output I NULL 18000 I 2. (20 points Please write a s tored procedure named pHW2 2 XXXX Booking tables. Your program should take one argument hotelname and it should meet the following 2 test Q Page 1 2 ecuarcoExplanation / Answer
solution1)The solution would be similar to this:
CREATE OR REPLACE FUNCTION fHW2_1_XXX(i_branchno IN VARCHAR2)
RETURN VARCHAR2
IS
l_branchno varchar2(1000);
l_avg_sal varchar2(1000);
Begin
For i IN(select branchno from staff where branchno=i_branchno)
LOOP
if(i is null)
then
l_avg_sal=null;
else
select avg(salary) into l_avg_sal from staff where branchno=i.branchno;
end if;
end loop;
RETURN l_avg_sal;
end;
end;
solution 2)
CREATE OR REPLACE procedure pHW2_2_XXX(i_hname IN VARCHAR2)
IS
l_room varchar2(1000);
l_count varchar2(1000);
Begin
For i IN(select hotel_name from hotel h , room r , booking b WHERE h.hotel_id= r.hotel_Id and r.room_id=b.room_id and hotel_name=i_hname)
LOOP
if(i is not null)
then
select room_typ into l_room,count(room_type) into l_count
from hotel h , room r , booking b
WHERE h.hotel_id= r.hotel_Id and r.room_id=b.room_id and hotel_name=i.hotel_name;
else
RAISERROR ('Please input a valid hotel name',1,1);
end if;
end loop;
end;
end;
solution 3)
CREATE OR REPLACE procedure pHW2_3_XXX(i_city IN VARCHAR2)
IS
l_gname varchar2(1000);
l_address varchar2(1000);
l_ message varchar2(1000)
Begin
For i IN(select city from guest WHERE city= i_city)
LOOP
if(i is not null)
then
select guest_name into l_gname,address into l_address
from guest WHERE city= i.city;
else if (i is null or empty )
l_message="Please input a valide city name";
select message
else
DBMS_OUTPUT.PUT_LINE("Empty set(0.00sec)");
end loop;
end;
end;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.