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
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)
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started