Relational Database Design and SQL For each question, write the SQL statements r
ID: 3849982 • Letter: R
Question
Relational Database Design and SQL
For each question, write the SQL statements required to generate the required results.
1. Create a stored procedure called store_information which takes one input parameter for the price of a book. The stored procedure should list the store id and order date from the sales table, the store name from the stores table, and the title id, price and advance from the titles table where the price is greater than or equal to the input variable of the price. If the price is not entered, display the message ‘Enter valid price’ indicating value was missing. Format the order date as YYYY.MM.DD. Order the result set by the store name. Use the following code to test your stored procedure to produce the result set listed below.
EXECUTE store_information; -- Price not entered; display message
EXECUTE store_information 20.00; -- Valid price
stor_id stor_name ord_date title_id price advance
--------- --------------------------------------------------- ----------------- ----------- ------ -----------
7066 Barnum's 1993.05.24 PC8888 20.00 8000.00
8042 Bookbeat 1993.05.22 PC1035 22.95 7000.00
7131 Doc-U-Mat: Quality Laundry and Books 1993.05.29 PS1372 21.59 7000.00
7067 News & Brews 1992.06.15 TC3218 20.95 7000.00
(4 row(s) affected)
2. Using the INTO command, create a new table called business_books containing the title id, title, price, and advance columns with the data from the titles table where the type is business. There should be 4 rows inserted into the new table.
3. Create a stored procedure called employee_information which takes two input parameter for the first name and last name of an employee. The stored procedure should list the employee id, first name, last name, and hire date from the employee table. Format the name of the employee as the first name followed by a space followed by the last name. Display the hire date in the format of YYYY.MM.DD. Use the following code to test your stored procedure to produce the result set listed below.
EXECUTE employee_information 'Janine', 'Labrune';
emp_id name hire_date
------------- -------------------- ----------------
JYL26161F Janine Labrune 1991.05.26
(1 row(s) affected)
4. Create a stored procedure called author_information which takes one input parameter of an author id, and returns two output parameters of the name and the phone number. Format the name as the first name followed by a space followed by the last name.
5. Run the stored procedure author_information for a value of 672-71-3249 for the author id. Display the output values from the stored procedure for the first name, the last name on one line, and the phone number on the second line. The stored procedure should produce the result set listed below.
Author Name: Akiko Yokomoto
phone: 415 935-4228
6. Create a view called insert_stores_vw to display the store id, store name, and state from the stores table.
7. Using the view insert_stores_vw, insert a row in the stores table with at store id of 9999, a store name of Sals Used Books, and a state of WA.
8. List the store id and the sum of the quantity from the sales table, and the store name from the stores tables by store id and store name. Order the result set by the store id. Use a CASE statement to change the sum of the quantity as follows:
Sum of the quantity value Change to
Less than 50 Low Value
Between 50 and 100 Medium Value
Greater than 100 High Value
ELSE Unknown Value
The query should produce the result set listed below.
stor_id stor_name total_quantity
---------- ------------------------------------------------------- ----------------------
6380 Eric the Read Books Low Value
7066 Barnum's High Value
7067 News & Brews Medium Value
7131 Doc-U-Mat: Quality Laundry and Books High Value
7896 Fricative Bookshop Medium Value
8042 Bookbeat Medium Value
(6 row(s) affected)
Explanation / Answer
since I dont have tables and their schema... I have assumed the colum names and written the queries
--1.--------------------
CREATE PROCEDURE store_information
@price float
AS
BEGIN
if(@price<>0)
SELECT sa.stor_id,CONVERT(char(10),sa.ord_date,102) ord_date,st.stor_name,t.title_id,t.price,t.advance
from Sales sa
join store st on sa.stor_id=st.stor_id
join titles t on t.title_id=sa.title_id
where t.price>=@price
order by st.stor_name
else
RAISERROR ('Enter valid price')
END
EXECUTE store_information; -- Price not entered; display message
EXECUTE store_information 20.00; -- Valid price
--2.--------------------
select title id, title, price, advance into business_books from titles where type='business'
--3.-------------------
CREATE PROCEDURE employee_information
@firstname nvarchar(32),
@lastname nvarchar(32)
as
begin
select emp_id, first_name+' '+ last_name name, CONVERT(char(10),hire_date,102) from Employee
end
EXECUTE employee_information 'Janine', 'Labrune';
--4.--------------------
CREATE PROCEDURE author_information
@author_id nvarchar(32),
@author_name nvarchar(32) OUTPUT,
@phone_num nvarchar(32) OUTPUT
as
begin
select @author_name=first_name+' '+ last_name ,@phone_num=Phone_num from Authors where author_id=@author_id
print @author_name +' '+@phone_num
end
--5.---------------------
EXECUTE author_information '672-71-3249'
--6.--------------------
create view insert_stores_vw
as
select stor_id, stor_name, state from stores
--7.---------------------
create view insert_stores_vw
as
SELECT stor_id,stor_name,state from store
INSERT INTO insert_stores_vw
SELECT 9999,'Sals Used Books','WA'
--8.------------------------
SELECT sa.stor_id,
st.stor_name
case when sum(sa.Quantity)<50 then 'Low Value'
when sum(sa.Quantity)>50 and sum(sa.Quantity)<100 then 'Medium Value'
when sum(sa.Quantity)>100 then 'High Value'
else 'Unknown Value'
end total_quantity,
from Sales sa
join store st on sa.stor_id=st.stor_id
group by sa.stor_id,st.stor_name
order by sa.stor_id