QUESTION 2. Write a PL/SQL PROCEDURE that can list the Top-N customers (id and name) and the amounts spent in a specified year together with their rank. The customer rank can be obtained from the function created in the previous question. The total purchase is calculated for each customer using the quantity and price columns. The total purchased is sorted from the highest to the lowest. The procedure will have two input parameters: year (NUMBER) and TOP_N (NUMBER). Hint: • use ONE EXPLICIT CURSOR (corresponding to one select statement). • Use the topN technique (from clause subquery) and rownum to limit the customers. Expected Output for Top 3 customers in 2018 using execute topSpenders (2018. 3 3 Gary jenkins s422.64 Excellent 45 Fred Fontain $359.21 Excellent 16 Ralph Foster $351.64 Excellent Expected Output for Top 10 customers in 2018 using begin topSpenders(2018.1or end: 3 Gary Jenkins $422.64 Excellent 45 Fred Fontain s359.21 Excellent 16 Ralph Foster $351.64 Excellent 81 Laurice Karl s326.32 Excellent
QUESTION 2. Write a PL/SQL PROCEDURE that can list the Top-N customers (id and name) and the amounts spent in a specified year together with their rank. The customer rank can be obtained from the function created in the previous question. The total purchase is calculated for each customer using the quantity and price columns. The total purchased is sorted from the highest to the lowest. The procedure will have two input parameters: year (NUMBER) and TOP_N (NUMBER). Hint: • use ONE EXPLICIT CURSOR (corresponding to one select statement). • Use the topN technique (from clause subquery) and rownum to limit the customers. Expected Output for Top 3 customers in 2018 using execute topSpenders (2018. 3 3 Gary jenkins s422.64 Excellent 45 Fred Fontain $359.21 Excellent 16 Ralph Foster $351.64 Excellent Expected Output for Top 10 customers in 2018 using begin topSpenders(2018.1or end: 3 Gary Jenkins $422.64 Excellent 45 Fred Fontain s359.21 Excellent 16 Ralph Foster $351.64 Excellent 81 Laurice Karl s326.32 Excellent
Computer Networking: A Top-Down Approach (7th Edition)
7th Edition
ISBN:9780133594140
Author:James Kurose, Keith Ross
Publisher:James Kurose, Keith Ross
Chapter1: Computer Networks And The Internet
Section: Chapter Questions
Problem R1RQ: What is the difference between a host and an end system? List several different types of end...
Related questions
Question
Transcribed Image Text:DATA
Sample data:
SQL> SELECT * FROM customers;
CUSTOMER ID CUST_NAME
EMAIL
Tammy Bryant tammy.bryant@internalmail
Roy White
roy.white@internalmail
SQL> SELECT * FROM orders:
3
Gary Jenkins garyjenkinseinternalmail
ORDER IDORDER DATECUSTOMER_IDORDER STATUSSTORE ID
Victor Morris victor.morris@internalmail
1
04-FEB-18
CANCELLED
1
Beverly Hughesbeverly.hughes@internalmail
2
08-FEB-18
4
COMPLETE
3
09-FEB-18
COMPLETE
SQL> SELECT * FROM stores;
STORE IDSTORE_NAME
4
10-FEB-18
COMPLETE
11-FEB-18
2
COMPLETE
1
Online
SQL> SELECT * FROM order_items:
San Francisco
ORDER IDITEM_IDPRODUCT_IDPRICEQUANTITY
Seattle
33
37 4
4
New York City
1
11
30.69 2
5.
Chicago
1
41
8.66 3
2
32
5.65 5
SQL> SELECT * FROM products:
3
41
8.66 5
1
20
28.21 2
PRODUCT_ID PRODUCT_NAME
4
2
38
22.98 4
1
Boy's Shirt (White)
4
46
39.16 4
2
Women's Shirt (Green)
3.
Boy's Sweater (Green)
4
Boy's Trousers (White)
Girl's Shorts (Red)
Transcribed Image Text:QUESTION
2. Write a PL/SQL PROCEDURE that can list the Top-N customers (id and name) and the amounts spent in a specified year together with their rank. The
customer rank can be obtained from the function created in the previous question. The total purchase is calculated for each customer using the quantity and
price columns. The total purchased is sorted from the highest to the lowest. The procedure will have two input parameters: year (NUMBER) and TOP_N (NUMBER).
Hint:
• use ONE EXPLICIT CURSOR (corresponding to one select statement).
• Us the topN technique (from clause subquery) and rownum to limit the customers.
Expected Output for Top 3 customers in 2018 using:
execute topSpenders (2018. 3)
3 Gary Jenkins s422.64 Excellent
45 Fred Fontain $359.21 Excellent
16 Ralph Foster $351.64 Excellent
Expected Output for Top 10 customers in 2018 using:
begin
topSpenders(2018,10
end:
3 Gary Jenkins s422.64 Excellent
45 Fred Fontain $359.21 Excellent
16 Ralph Foster $351.64 Excellent
81 Laurice Karl 5326.32 Excellent
39 Harry Powell $319.2 Excellent
49 Kaija Crawford s270.64 Good
80 Milo Manoni $245.68 Good
9 Norma Robinson $221.19 Good
71 Eduardo Flignia s212.26 Good
74 Alex Loera S199.48 Fair
Expert Solution
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
Step by step
Solved in 2 steps with 2 images
Recommended textbooks for you
Computer Networking: A Top-Down Approach (7th Edi…
Computer Engineering
ISBN:
9780133594140
Author:
James Kurose, Keith Ross
Publisher:
PEARSON
Computer Organization and Design MIPS Edition, Fi…
Computer Engineering
ISBN:
9780124077263
Author:
David A. Patterson, John L. Hennessy
Publisher:
Elsevier Science
Network+ Guide to Networks (MindTap Course List)
Computer Engineering
ISBN:
9781337569330
Author:
Jill West, Tamara Dean, Jean Andrews
Publisher:
Cengage Learning
Computer Networking: A Top-Down Approach (7th Edi…
Computer Engineering
ISBN:
9780133594140
Author:
James Kurose, Keith Ross
Publisher:
PEARSON
Computer Organization and Design MIPS Edition, Fi…
Computer Engineering
ISBN:
9780124077263
Author:
David A. Patterson, John L. Hennessy
Publisher:
Elsevier Science
Network+ Guide to Networks (MindTap Course List)
Computer Engineering
ISBN:
9781337569330
Author:
Jill West, Tamara Dean, Jean Andrews
Publisher:
Cengage Learning
Concepts of Database Management
Computer Engineering
ISBN:
9781337093422
Author:
Joy L. Starks, Philip J. Pratt, Mary Z. Last
Publisher:
Cengage Learning
Prelude to Programming
Computer Engineering
ISBN:
9780133750423
Author:
VENIT, Stewart
Publisher:
Pearson Education
Sc Business Data Communications and Networking, T…
Computer Engineering
ISBN:
9781119368830
Author:
FITZGERALD
Publisher:
WILEY