--%>
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 : C-C++ interfaces into WLS JMS Normal 0

    Normal 0 false false

  • Q : Explain the way to handle the mapping

    Explain the way to handle the mapping form.

    		•
  • Q : Describe Method result Method result :

    Method result: The value returned from a method through a return statement. The kind of expression in the return statement should match the return type declared in method header.

    		•
  • Q : Define the Binding of Instructions and

    Define the Binding of Instructions and Data to Memory?

    		•
  • Q : What is a Port Port: It is a number

    Port: It is a number employed by a procedure to communicate with the other procedure across a network, employing the Transmission Control Protocol (abbreviated as TCP) or User Datagram Protocol (abbreviated as UDP), for instance.

    		•
  • Q : Automaton distributed in the class

    Write a code in a c++/java  for the automaton distributed in the class which accepts keywords(cat,bat,cab). Create an input file with these words may be two or three copies of these words scattered in a paragraph and show that your program does accept these words and gives an output to that

    		•
  • Q : What do you mean by ActiveX control

    What do you mean by ActiveX control? Describe in brief.

    		•
  • Q : What is an Operator Operator : It is a

    Operator: It is a symbol, like -, = or ?: taking one, two or three operands and yielding an outcome. The operators are employed in both arithmetic and Boolean expressions.

    		•
  • Q : What is Avoid Redundancy Avoid

    Avoid Redundancy: While not every form of redundancy is as bad from a verification perspective as it is from a maintenance point of view, behavioral redundancy to re-create (local) state can impose problems because the model checker does not distingui

    		•
  • Q : State Sub type Sub type: It is a type

    Sub type: It is a type with a parent super type. The sub-type or super-type relationship is more common than the sub-class or super-class relationship. A class which implements an interface is a sub type of interface. An interface which expands the ot

    		•