--%>
Assignment Help - homework help

Compute the Total Shopper Spending in SQL

Most of the reports produced from the system compute the total dollars in purchases for a shopper. Process the following steps to build a function named TOT_PURCH_SF which accepts a shopper id as input and returns the total dollars which the shopper has spent with company. Utilize the function in a SELECT statement which shows the shopper id and total purchases for each and every shopper in a database.
 
1. Make and run a CREATE FUNCTION statement to build the TOT_PURCH_SF function. The function code requires a formal parameter for the shopper id and to sum the total column from the BB_BASKET table.

2. Form a SELECT statement employing the BB_SHOPPER table to generate a list of each shopper in the database and his/her respective totals.

E

Expert

Verified

create or replace function "TOT_PURCH_SF" (shopper_id in NUMBER)
return NUMBER is TOTAMT NUMBER;

begin

SELECT SUM(TOTAL) INTO TOTAMT FROM BB_BASKET WHERE IDSHOPPER=SHOPPER_ID GROUP BY IDSHOPPER;

dbms_output.put_line (totamt);

RETURN TOTAMT;

end;

   Related Questions in Programming Languages

  • Q : Define Homology Modeling In Structural

    Homology Modelling is a process in which models are generated. The generated models may be conceptual or graphical or/and mathematical.So, we have different methods, tools and techniques for all kinds of modeling.Modelling methods are broadly classifi

    		•
  • Q : Define Context Normal 0 false false

    Normal 0 false false

  • Q : What is an Instance variable Instance

    Instance variable: It is a non-static field of a class. Each and every individual object of a class has its own copy of this field. This is in contrary to a class variable that is shared by all instances of class. Instance variables are employed to mo

    		•
  • Q : Define the term Heterogeneous collection

    Define the term Heterogeneous collection: It is a collection of objects with distinct dynamic types

    		•
  • Q : What is MIME MIME : Multipurpose

    MIME: Multipurpose Internet Mail Extensions (abbreviated as MIME) are rules which make it possible to utilize electronic mail to send content other than the simple text.

    		•
  • Q : Explain the relation between XHTML and

    Explain the relation between XHTML and CSS.

    		•
  • Q : What is Static initializer Static

    Static initializer: An initializer prefixed with ‘static’ reserved word. The static initializer is stated outside the methods of its enclosing class, and might access only the static fields and methods of its enclosing class.

    		•
  • Q : What is an Initializer Initializer : A

    Initializer: A block stated at the outermost level of a class - identical to a method devoid of a header. The initializer blocks are executed, in order, whenever an instance is formed. They are executed prior to the constructor of the defining class,

    		•
  • Q : Define the term Blank final variable

    Blank final variable: A final variable which is not initialized as portion of its declaration. This variable should be initialized in either an instance initialization block or every of the constructors for its class before it is employed. A static bl

    		•
  • Q : Define the term Garbage collector

    Garbage collector: It is a daemon thread which recycles objects to which there are no extant references in a program.

    		•