1. List the author number, first name, and last name for all authors. The first

Question

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. 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. 5. Write a PL/SQL or T-SQL procedure to retrieve and output the book code, title, book type, and price for every book whose publisher code is stored in I_PUBLISHER_CODE. 6. Write Access functions to accomplish the following tasks: a. Delete the author whose number is stored in I_AUTHOR_NUM. b. Change the last name of the author whose number is stored in I_AUTHOR_NUM to the value currently found in I_AUTHOR_LAST. c. Retrieve and output the book code, title, book type, and price for every book whose publisher code is stored in I_PUBLISHER_CODE. 7. Write a stored procedure in PL/SQL or T-SQL that will change the price of a book with a given book code. How would you use this stored procedure to change the price of book 0189 to $8.49? 8. Assume the BOOK table contains a column called TOTAL_ON_HAND that represents the total units on hand in all branches for that book. Following the style shown in the text, write the code in PL/SQL or T-SQL for the following triggers: a. When inserting a row in the INVENTORY table, add the ON_HAND value to the TOTAL_ON_HAND value for the appropriate book. b. When updating a row in the INVENTORY table, add the difference between the new ON_HAND value and the old ON_HAND value to the TOTAL_ON_HAND value for the appropriate book. c. When deleting a row in the INVENTORY table, subtract the ON_HAND value from the TOTAL_ON_HAND value for the appropriate book.

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 1
• Subjects

---

---

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