Quick Ref.

ACME Accounts

Details of the database.

Some items from the database.

1a. Identify customers 'C001', 'C005'

Results
1b. Identify products 'P001', 'P007'

Results

Invoices and receipts

2a. View the payments made by customer 'C001'

Results
2b. View the shipments made to customer 'C001' by date order

Results

Preparing accounts

3a. Prepare a list of all items purchased by customer 'C001', show the date, the product description, the unit price, the quantity shipped and the total value (quantity * price).

Results
3b. Calculate the total value of all items shipped to customer 'C001' on 23rd July 1998. The SQL standard way to write this date is DATE '1998-07-23'

Results
3c. Prepare a shipping statement for customer 'C001' it should show the date, the legend 'Delivery' and the total value of the products shipped on each day.

Results
3d. Prepare a receipts statement for customer 'C001' it should show the date, the notes and the amount received.

Results
3e. Use the UNION command to prepare a full statement for customer 'C001' - it should be laid out as follows. (Note that the values shown below are not correct.) You may be able to use '' or NULL for blank values - if necessary use 0.

Results
  10-JUL-1998 Delivery       100.00
  15-JUL-1998 Cheque                       100.00
  22-JUL-1998 Delivery       210.00
  23-JUL-1998 Delivery        45.00
  23-JUL-1998 Cash                         255.00
 

You can reuse a useful "phrase" many times. For example the following query produces a useful summary of incoming and outgoing money. This can be helpful in displaying customer accounts.

  SELECT shipped.badguy AS badguy, shipped.sdate AS LineDate,
        'Delivery' AS Legend, -price*quantity AS amount
    FROM product JOIN shipped ON (product.id = shipped.product)
UNION
  SELECT receipt.badguy, receipt.rdate,notes, amount
  FROM receipt

You can use this as a subquery in other queries. Better yet you can create a VIEW and name the query.

4a. Issue the command to see the accoutnline, you should return the rows in date order.

Results
4b. Create a list showing the outstanding balance for each customer.

Results

What is wrong with this database.

  1. There is no mechanism for recording returns. Suggest what action should be taken when faulty goods are returned and money refunded.
  2. The price of item P001 is to be increased. What effect will this have on the calculated balance for customers who previously purchased the item? Suggest how the structure of the database should be amended to prevent this problem.
  3. All goods must be purchased at list price. In practice discounts may be given on an ad-hoc basis. Suggest how such discounts might be recorded.
  4. As it stands the data will just grow and grow. It may be desirable to have a consolidation routine which removes old records and leaves an outstanding balance for each customer. This is unlikely to be worth doing for technical reasons. You could store hundreds of years worth of data for a small company at only a marginal cost of processing time or disk space.

Tutorial Six: Edinburgh Buses concerns self joins and includes some ridiculously complex SQL queries