CIS 3010: Assignment 1

Due date: 25 September 2015
Value: 15%

Please submit the CODE and OUTPUT for all programming questions. You will submit this assignment electronically in the ASSESSMENT area of the Study Desk.
For this assignment we will use the Brewers database that comes with the textbook. The script to create the databases is included as a file in the assignment 1 section of the Moodle course web site. You will need to run the scripts to create the needed database tables. If you are using you own Oracle insulation the script should work as normal.
Please Read Carefully.
There are 4 question valued a total of 100 marks. You need to provide a well-structured and commented solution to the problems. With your code solution you also need to provide a screen shot of the solution output on your screen. You can use the screen capture features of the OS you are using. For Windows you can use the ‘print current window capture’ key stroke ‘Alt-Shift-Print Screen’. Depending on your keyboard you may have to use the function key as well. For Apple Mac use ‘Command (?)-Shift-4’ and select screen to capture.
The structures and information about the tables is located in Appendix A (page 423) of the Casteel textbook. The structure of the tables used in this assignment questions is reproduced below.
BB_SHOPER

BB_BASKET

Question 1 (20 marks)
Write an anonymous block that places a substitution variable (&) into a local variable of type character. You should check the value entered in the local variable and output different messages depending on the value provided.
• If value is ‘A’, output: ‘Please proceed to Level 1’
• If value is ‘B’ or ‘C’, output: ‘Please proceed to Level 2’
• If value is ‘D’, output: ‘Please proceed to Level 3’
• In all other entries, output: ‘Invalid Entry’
Question 2 (20 marks)
We need a block to categorise the customers according to their total purchases using the BB_BASKET table. For each customer we determine if they are a of ‘HIGH’, ‘MEDIUM’ or ‘LOW’ category depending on their total purchases. Using a host variable to store the customer number the code needs to use an implicit cursor to sum up the total amount of purchases for that customer. Use another implicit cursor to count the number of purchases for that customer. Using a conditional block write the code to categorise the customer according to total phrases. For a ‘HIGH’ category the purchase is more than $150. For ‘MEDIUM’ category total purchase is more than $100. Anything less than $100 is categorised as ‘LOW’.
The block should display the following example output after execution
‘Shopper ID 22 is rated as HIGH with total spend of $XXX.XX and YY number of orders.’
Question 3 (30 marks)
Write an anonymous block that meets the following specification:
1. Using an explicit cursor, retrieve each customer row from the BB_SHOPPER table.
2. Create a local variable that inherits the structure of the BB_SHOPPER table (using %ROWTYPE attribute). Place each row returned into this variable.
3. Create variable to keep track of number of customers’ processed and total number of orders processed.
4. For each customer using an implicit cursor, calculate the number of orders that belong to each customer.
5. Display the customer ID, first name, last name and the total number of orders.
6. After processing all the customers display the following line of information.
‘Processed xx number of shoppers with a total of yy number of orders recorded.’
Where xx is the total number of shoppers and yy is the total number of orders placed.
Question 4 (30 marks)
Write a PL/SQL that displays the largest total order amount, the highest number of quantity ordered and the average amount spent on orders in BB_BASKET. Your program must use only explicit cursor/s with basic looping and must NOT use any aggregate functions such as SUM, MAX, MIN, COUNT and AVG.
(Average is total amount divided by the number of customers)
The output should be formatted as follow.
‘The highest total order is $xxx.xx’
‘The largest number of items on an order is customer yy.’
‘The average amount for all customers is $xxx.xx’
(Hint:BB_BASKET table has the field QUATITY and TOTAL which contain the information you need)
MARKING CRITERIA
1. The code executes without error messages.
2. The solution is well documented formatted
3. The code produces the required output.
4. The code addresses the specification and provides a solution to every element in the specification.
5. The code is well structured and, where applicable, adopts an optimal and sophisticated approach to PL/SQL.