Relational Database Design and SQL Please answer as soon as possible For each qu
ID: 3849983 • Letter: R
Question
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)
Explanation / Answer
HI Please find the sqls below. Request you to kindly provide database schema in order to provide better help. If possible provide the complete schema with added data and the database taht is being used. Thanks
1: DELIMITER //
CREATE PROCEDURE store_information(IN priceofBook VARCHAR(255))
BEGIN
SELECT s.store_id,s.order_date,str.store_name,t.title_id,t.price,t.advance
FROM sales s, stores str,titles t
WHERE t.price >= priceofBook;
END //
DELIMITER ;
2: CREATE TABLE business_books (
title_id VARCHAR(5),
title VARCHAR(20),
price VARCHAR(5),
advance VARCHAR(10)
);
SELECT * INTO business_books
FROM titles
WHERE type = 'business';
3:DELIMITER //
CREATE PROCEDURE employee_information (IN first_name VARCHAR(255),IN last_name VARCHAR(255) )
BEGIN
SELECT employee_id, firstname, lastname, hiredate
FROM employee
WHERE firstname = first_name
AND lastname = last_name;
END //
DELIMITER ;
6: CREATE VIEW insert_stores_vw AS SELECT storeid, storename, and state from stores
7: INSERT INTO insert_stores_vw values ('9999','Sals Used Books','WA');