ACME Accounts
Details of the database.
Some items from the database.
| 1a |
Identify customers 'C001', 'C005'
SELECT * FROM badguy WHERE id IN ('C001', 'C005')
|
|
| 1b |
Identify products 'P001', 'P007'
SELECT * FROM product WHERE id IN ('P001', 'P007')
|
|
Invoices and receipts
| 2a |
View the payments made by customer 'C001'
SELECT * FROM receipt WHERE badguy='C001'
|
|
| 2b |
View the shipments made to customer 'C001' by date
order
SELECT * FROM shipped WHERE badguy='C001'
ORDER BY sdate
|
|
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).
SELECT sdate, description, price, quantity, quantity*price
FROM
shipped, product
WHERE badguy='C001'
AND product=product.id
|
|
| 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'
SELECT SUM(quantity*price)
FROM shipped, product
WHERE product=product.id
AND badguy='C001'
AND sdate=DATE '1998-07-23'
|
|
| 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.
SELECT sdate, 'Delivery', SUM(quantity*price)
FROM shipped, product
WHERE product=product.id
AND badguy='C001'
GROUP BY sdate
|
|
| 3d |
Prepare a receipts statement for customer 'C001' it
should show the date, the notes and the amount received.
SELECT rdate, notes, amount
FROM receipt
WHERE badguy='C001'
|
|
| 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.
SELECT sdate, 'Delivery', -SUM(quantity*price)
FROM shipped, product
WHERE product=product.id
AND badguy='C001'
GROUP BY sdate
UNION
SELECT rdate, notes, amount
FROM receipt
WHERE badguy='C001'
|
|
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
The following
VIEW
, accountline has been created (if not create it yourself). It
shows one line for each payment or shipment, money out is
negative (a debit), money in positive (a credit).
CREATE VIEW accountline AS
SELECT shipped.badguy AS badguy, shipped.sdate AS LineDate,
'Delivery' AS Legend, -[price]*[quantity] AS amount
FROM product, shipped
WHERE product.id = shipped.product
UNION
SELECT receipt.badguy, receipt.rdate,notes, amount
FROM receipt
ORDER BY linedate;
| 4a |
Issue the command to see the accountline.
SELECT * FROM accountline","",tru
|
|
| 4b |
Create a list showing the outstanding balance for
each customer.
SELECT badguy, SUM(amount) FROM accountline
GROUP BY badguy
|
|
What is wrong with this database.
- There is no mechanism for recording returns. Suggest what
action should be taken when faulty goods are returned and money
refunded.
- 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.
- 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.
- 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