Hello, thank you for you help. Sorry for the delay accepting your last answer. I have 3 more Oracle PL/SQL questions if you or anyone else you know are up to it. The tables needed can be created with the attached c4_BBcreate.sql

Question 1. Uses BB_SHOPPER and BB_BASKET tables

Write an anonymous PL/SQL block with a cursor to retrieve and display information about

shoppers based on their total purchases (For example, in BB_BASKET table, the total purchase for idShopper 21 should be 32.4+34.36):

Total purchases                Message

150 or more                        Valuable customer

Between 50 and 150         Regular customer

50 or less                             Needs promotion

Sample output:

anonymous block completed

Scott Savid is a regular customer.

…

Monica Cast needs promotion.

Camryn Sonnie is a regular customer.

Margaret Somner is a valuable customer.

 

Question 2.  Uses BB_SHOPPER and BB_BASKET tables
Write an anonymous PL/SQL block to count number of orders placed by a shopper. Raise a user-defined exception if a shopper hasn’t placed any orders.

Sample output for idshopper = 23
anonymous block completed
This shopper placed 3 orders.

Sample output for idshopper = 26
anonymous block completed
This shopper hasn't placed any orders.

Question 3. Uses EMPLOYEE table
 Create a stored procedure called INCREASE_SALARY to increase all employees’ salaries in a department. The procedure should receive the following two parameters: department number and salary increase amount. The new salary for an employee is calculated based on the following formula: New salary = Old salary + Increase amount.
Including the following two exceptions in the procedure:
a. If the department number provided is not in the DEPTNO column, generate a message “This department doesn’t exist.”
b. If the salary increase amount is less than 100 or greater than 1000, then generate a message “The specified amount is invalid.”

 

DROP VIEW bb_basketitem_vu;DROP VIEW bb_ship_vu;DROP TABLE bb_department CASCADE CONSTRAINTS;DROP TABLE bb_product CASCADE CONSTRAINTS;DROP TABLE bb_productoption CASCADE CONSTRAINTS;DROP TABLE bb_productoptiondetail CASCADE CONSTRAINTS;DROP TABLE bb_productoptioncategory CASCADE CONSTRAINTS;DROP TABLE bb_basketitem CASCADE CONSTRAINTS;DROP TABLE bb_basket CASCADE CONSTRAINTS;DROP TABLE bb_shopper CASCADE CONSTRAINTS;DROP TABLE bb_basketstatus CASCADE CONSTRAINTS;DROP TABLE bb_tax CASCADE CONSTRAINTS;DROP TABLE bb_shipping CASCADE CONSTRAINTS;DROP TABLE bb_promolist CASCADE CONSTRAINTS;DROP TABLE bb_promo CASCADE CONSTRAINTS;DROP TABLE bb_test1 CASCADE CONSTRAINTS;DROP TABLE bb_test2 CASCADE CONSTRAINTS;DROP TABLE bb_prod_sales CASCADE CONSTRAINTS;DROP TABLE bb_shop_sales CASCADE CONSTRAINTS;DROP TABLE bb_audit_logon CASCADE CONSTRAINTS;DROP TABLE bb_product_request CASCADE CONSTRAINTS;DROP TABLE bb_blocktest CASCADE CONSTRAINTS;DROP TABLE bb_trans_log CASCADE CONSTRAINTS;DROP SEQUENCE bb_prodid_seq;DROP SEQUENCE bb_shopper_seq;DROP SEQUENCE bb_poption_seq;DROP SEQUENCE bb_idbasket_seq;DROP SEQUENCE bb_idbasketitem_seq;DROP SEQUENCE bb_status_seq;DROP SEQUENCE bb_prodreq_seq;CREATE TABLE BB_Department (idDepartment number(2),DeptName varchar2(25) ,DeptDesc varchar2(100) ,DeptImage varchar2(25) ,CONSTRAINT dept_id_pk PRIMARY KEY(idDepartment) );insert into bb_departmentvalues(1,'Coffee','Many types of coffee beans','coffee.gif');insert into bb_departmentvalues(2,'Equipment and Supplies','Coffee makers to coffee filtersavailable','machines.gif');insert into bb_departmentvalues(3,'Coffee Club','Benefits of our club membership?','club.gif');CREATE TABLE BB_Product (idProduct number(2) ,ProductName varchar2(25) ,Description varchar2(100) ,ProductImage varchar2(25),Price number(6,2),SaleStart date,SaleEnd date,SalePrice number(6,2),Active number(1),Featured number(1),FeatureStart date,FeatureEnd date,Type char(1),idDepartment number(2),CONSTRAINT prod_id_pk PRIMARY KEY(idProduct),CONSTRAINT prod_idDept_fk FOREIGN KEY (idDepartment)REFERENCES BB_Department (idDepartment) );insert into bb_product(idProduct, type, ProductName, Description, ProductImage,Price, Active, idDepartment)values(1,'E','CapressoBar Model #351', 'A fully programmable pump espresso