Henry Books Use the Henry Books database (see Figures 1-4 through 1-7 in Chapter

Question

Henry Books Use the Henry Books database (see Figures 1-4 through 1-7 in Chapter 1) to complete the following exercises. If directed to do so by your instructor, use the information provided with the Chapter 3 Exercises to print your output. 1. List the author number, first name, and last name for all authors. The first name should appear in lowercase letters and the last name should appear in uppercase letters. 2. List the publisher code and name for all publishers located in the city of NewYork.Your query should ignore case. For example, a customer with the city New York should be included as should customers whose city is NEW YORK, New york, NeW yOrK, and so on. 3. List the book code, title, and price for all books. The price should be rounded to the nearest dollar. 4. Write PL/SQL or T-SQL procedures to accomplish the following tasks: a. Obtain the first name and last name of the author whose number currently is stored in I_AUTHOR_NUM. Place these values in the variables I_AUTHOR_FIRST and I_AUTHOR_LAST. Output the contents of I_AUTHOR_NUM, I_AUTHOR_FIRST, and I_AUTHOR_LAST. Chapter 8 270 b. Obtain the book title, publisher code, and publisher name for every book whose code currently is stored in I_BOOK_CODE. Place these values in the variables I_TITLE, I_PUBLISHER_CODE, and I_PUBLISHER_NAME, respectively. Output the contents of I_TITLE, I_PUBLISHER_CODE, and I_PUBLISHER_NAME. c. Add a row to the AUTHOR table. d. Change the last name of the author whose number is stored in I_AUTHOR_NUM to the value currently found in I_AUTHOR_LAST. e. Delete the author whose number is stored in I_AUTHOR_NUM.

Explanation / Answer

Please rate it as LifeSaver

1)

SELECT AUTHOR_NUM,UPPER(AUTHOR_LAST),LOWER(AUTHOR_FIRST) FROM AUTHOR

2)

SELECT PUBLISHER_CODE,PUBLISHER_NAME FROM PUBLISHER WHERE UPPER(CITY)='NEWYORK'

3)

SELECT BOOK_CODE,TITLE,ROUND(PRICE) FROM BOOK

a)

CREATE PROCEDURE sCustDetails
@I_CUSTOMER_NUM int
AS
BEGIN
DECLARE @I_CUSTOMER_NAME varchar(20)
DECLARE @I_CREDIT_LIMIT varchar(20)
SET @I_CUSTOMER_NAME= SELECT FIRST_NAME FROM CUSTOMER WHERE CUSTOMER_NUM =@I_CUSTOMER_NUM
SET @I_CREDIT_LIMIT =SELECT LAST_NAME FROM CUSTOMER WHERE CUSTOMER_NUM=@I_CUSTOMER_NUM
SELECT @I_CUSTOMER_NAME,@I_CREDIT_LIMIT
END
GO

Sample Test Case:

exec sCustDetails 1234


b)

CREATE PROCEDURE sOrderDetails
@I_ORDER_NUM int
AS
BEGIN
DECLARE @I_ORDER_DATE varchar(20)
DECLARE @I_CUSTOMER_NUM varchar(20)
DECLARE @I_CUSTOMER_NAME int
SET @I_ORDER_DATE = SELECT ORDER_DATE FROM ORDERS WHERE CUSTOMER_NUM =@I_ORDER_NUM
SET @I_CUSTOMER_NUM = SELECT CUSTOMER_NUM FROM ORDERS WHERE CUSTOMER_NUM =@I_ORDER_NUM
SET @I_CUSTOMER_NAME= SELECT CUSTOMER_NAME FROM ORDERS WHERE CUSTOMER_NUM =@I_ORDER_NUM
SELECT @I_ORDER_DATE,@I_CUSTOMER_NUM,@I_CUSTOMER_NAME
END
GO

Sample Test Case:

exec sOrderDetails 1234

c)

CREATE PROCEDURE sInsertOrders
As
BEGIN
INSERT INTO ORDERS VALUES(5,'11/12/2012',12,'DEO')
END

d)

UPDATE ORDERS
SET ORDER_DATE=@I_ORDER_DATE
WHERE ORDER_NUM=@I_ORDER_NUM

e)
DELETE FROM ORDERS WHERE ORDER_NUM=@I_ORDER_NUM

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.