SQL Task- Need it asap..

timer Asked: Mar 4th, 2016

Question description

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.”

Tutor Answer

(Top Tutor) Studypool Tutor
School: New York University
Studypool has helped 1,244,100 students
flag Report DMCA
Similar Questions
Hot Questions
Related Tags

Brown University

1271 Tutors

California Institute of Technology

2131 Tutors

Carnegie Mellon University

982 Tutors

Columbia University

1256 Tutors

Dartmouth University

2113 Tutors

Emory University

2279 Tutors

Harvard University

599 Tutors

Massachusetts Institute of Technology

2319 Tutors

New York University

1645 Tutors

Notre Dam University

1911 Tutors

Oklahoma University

2122 Tutors

Pennsylvania State University

932 Tutors

Princeton University

1211 Tutors

Stanford University

983 Tutors

University of California

1282 Tutors

Oxford University

123 Tutors

Yale University

2325 Tutors