Relational Database Design and SQL

Please answer as soon as possible

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)