Customer's Credit Limit

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@rahinston.com