SQL Task- Need it asap..

Anonymous
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