السلام عليكم
انا ادرس IT وتخصصيي BIT
وعندي كم سؤال في PL\SQL
وياريت اتساعدوني قبل يوم الاحد 31\5
1- Create a PL\SQL block that displays the book and publisher details of all books with a cost price of more than $20.00
2- Create a PL\SQL stored procedure that returns the Profit (profit = retail - cost) on the sale of each book based on the input book ISBN. Test the procedure using the PL\SQL block using the book ISBN"4981341710".
When the book ISBN entered dose not returen any record then the PL\SQL procedure should handle the exception with an appropriate exception handler and display a message "No record for book with the given ISBN exists".
3- write a PL\SQL stored procedure that processes an update to the Retail Price of a book. The procedure should take a BOOK ISBN and the required PROFIT PERCENTAGE as input and perform an update to the RETAIL PRICE of the book basedon the following criteria:
a. The procedure should be to perform the above mentioned update only if the book's existing profit % is less than the required PROFIT PERCENTAGE.
b. Display a suitable message if the existing profit % is equal to or more than the required profit percentage.
c. when the book ISBN entered dose not return any record, then your PL\SQL PROCEDURE SHOULD HANDLE THE EXCEPTION with an appropriate exception handler "No BOOK with ISBN : XXXXXXXXX exits"
4- Write a PL\SQL function to accept an order Number as an input and determine the total value of the order. The function should return -1 if NO DATA FOUND exception occurs.
5- Write a PL\SQL procedure to accept the customer as an input parameter and display the number of orders for that customer. the procedure should display a message based on the following requirements:
-if there are no orders for the input customer number-
'there is no order for customer#: xxx'
- if there is only one order for the input customer number-
'there is 1 order for customer <customer name>'
-if there are more than one orders for the input customer number-
'there are xx orders for customer <customer name>'
6- Write a PL\SQL trigger to execute all BEFORE STATEMENT (insert, update and delete)
TRIGGERS on the ORDERS table to implement company policies:
a. Orders cannot be update or deleted on Friday or Saturday.
b. Orders cannot be deleted between 16:00 and 08:00 hours.
c. Orders cannot be updated on Sunday and Tuesday between 22:00 and 02:00 hours on all days.
d. New orders cannot be inserted Sun -Thu between 08:00 and 10:00 hours
. 