Re: Help is SQL query for Cursor

Charlie - Melbourne wrote:
> Hi there,
>
> I am getting confused here with my sql code.
>
> I am using Oracle PL/SQL Gen8i & have two tables. (See atttachment,
> SQL create tables plus data) One customer table and one orders table
>
> Where i am getting confused is with the query: "The PL/SQL block to
> raise each customers' credit limit by the equal value to the average
> ammount of his or her orders." My issue is particularly with the SQL
> (select) in the declaration statement. (You don't need to know PL/SQL
> to answer this)
> .
> As I understand it, this means finding the total nos of orders per
> customer and the total ammount (from balance) of the orders, finding
> the average and adding this average to the credit limit.
>
> The problem is that I am not sure how to count the nos of customer
> orders in SQL across the two tables in order to calculate the average
> ammount per order. Not every customer has made an order. (this
> exception will be handled by PLSQL later). I think i should use joins
> but they don't seem to work.
>
> I only need to display the follow in PLSQL via DBMS_OUTPUT_LINE is the
> following
> ----------------------------------------------------------------------
> custnumb custname oldcredlim newcredlim
>
> This translates in SQL to from a SQL select query, in incrememtal
> steps
>
> 1) SELECT current creditlimit and orders for all customers from TABLES
> customer & orders.
> 2) COUNT the nos of orders per customer
> 3) SUM the total balances of orders
> 4) Calculate new credit limit = Total Balance / Nos of Orders
>
> If you know how a cursor for the PLSQL block would work for this that
> would be a bonus, if not just stick to the SQL query part of the
> problem.
>
> I know how it works at the conceptual level but get bogged down when i
> try the SQL script ... am working alone so in need of some support.
>
> yours fustrated,
> Charlie
>
> charlie_at_rahinston.com
>
> Data Below:
>
> CREATE TABLE customer (
>
> custnumb NUMBER(8) NOT NULL,
> custname VARCHAR2(25),
> custaddr VARCHAR2(30),
> balance decimal(6,2),
> credlim NUMBER(4),
> slsrnumb NUMBER(4) REFERENCES slsrep(slsrnumb),
>
> PRIMARY KEY(custnumb));
>
> -- 3) Create Table for Orders
> CREATE TABLE orders (
>
> ordnumb NUMBER(8),
> orddte DATE,
> custnumb NUMBER(8) REFERENCES customer(custnumb),
>
> PRIMARY KEY(ordnumb));
>
> -- Data then inserted.
>
>
> SHOW TAB customer gives (sample data)
> customer
> --------
> custnumb custname custaddr balance credlim slsrnumb
> 124 Adams, Sally 481 Oak, Sandringham 418.75 500 3
> 256 Samuels, Ann 215 Pete, Glenroy 10.75 800 6
> 311 Charles, Don 48 College, Caulfield 200.10 300 12
> 315 Daniels, Tom 914 Cherry, Kilsyth 320.75 300 6
> 405 Williams, Al 519 Watson, Glenroy 201.75 800 12
> 412 Adams, Sara 16 Elm, Lalor 908.75 1000 3
> 522 Nelson, Mary 108 Pine, Avondale Hts 49.50 800 12
> 567 Baker, Joe 808 Ridge, Hawthorn 201.20 300 6
> 587 Roberts, Judy 512 Pine, Avondale Hts 57.75 500 6
> 622 Martin, Dan 419 Chip, Glenroy 575.50 500 3
>
> order
> -----
> ordnum orddte custnumb
> 12489 2/09/95 124
> 12491 2/09/95 311
> 12494 4/09/95 315
> 12495 4/09/95 256
> 12498 5/09/95 522
> 12500 5/09/95 124
> 12504 5/09/95 522

So where do you go to school & what is the email address of the instructor? Received on Sat Apr 12 2003 - 15:37:19 CEST