SQL ACME数据库的学习

今天晚上，研究了下示例数据库ACME,复习了一下SQL语句,做了下SQL练习。这些题目主要涉及到多表的查询，利用创建视图，在视图上面进行查询应该是个不错的解决之道！但是也存在一些问题，明天进一步研究！

ACME 数据库说明

• 关于ACME:是典型的一个简单的帐目系统。ACME把产品销售给一定数量的客户。这些客户的定期订购产品。他们无须立即缴付现款,有可能是临时的债务。他们甚至可能会信贷，但这是不太常见的） 。
• badguy 顾客信息
• product 货物信息
• receipt 收款信息
• shipped 发货记录

数据库关系图如下所示：

金融数据需要注意的问题

• 货币应精确存储，通常作为小数点DECIMAL（ 10,2 ）存储 -- 一般从来不使用浮点数记录货币。
• 必须维持账目线索清楚，我们不仅需要了解谁欠谁多少钱，更要说明为什么。
• 所有交易都应该被记录和保存，交易指(A)有人给了你钱（B）你得为此作些什么（这里是发送货物），在数据库执行交易期间不要把它们搞混淆了
• 目前的余额是不用储存的，如有需要我们可以计算。我们可以“巩固”帐户，我们可以把一个顾客所有的交易信息放到一个档案文件中，并用一个余额转结替换掉原来的所有交易。
• 每一笔交易应当注明日期。
• 交易绝对不应改变。如果一个纪录输入有误,应该是由一个额外的记录来进行修正。 （这个数据库不支持的逆向操作）
• 我们不试图检查收货和发货信息。

练习题

• 编写一份名单，列出所有由客户'c001'购买的物品 ，显示日期，产品描述，单位价格，数量，发运和总价值（数量*价格） 。

Create View List
as
Select badguy,sdate,product,description,price,quantity,price*quantity as total
from shipped join product
on shipped.product=product.id

Select *
From List

• 为顾客'c001'准备一份发货记录，需要按日期显示标记'Delivery'和总价值

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

Select *
From accountline
where badguy='C001'
Order By LineDate

• 列出一份清单，根据某个顾客名称，显示出所有由他购买的产品，包括购买日期、货物名、数量 和 总价值（数量×价格）

Select name,sdate,description,quantity,[price]*[quantity] as total
From badguy join shipped
on badguy.id=shipped.badguy
join product
on product.id=shipped.product

• 根据某个顾客名称，列出他的当前已经结清的帐，还没有结的帐（可能为负数）

Select badguy,sum(-[price]*[quantity]) as account
From product join shipped
on product.id=shipped.product
Group By badguy
union
Select badguy,sum(amount)
From receipt
Group By badguy

最后一个没有统计出结清帐的总和，也没有统计出还没有结清帐的总和，只是列出了帐目清单，其中正数表示结清了的帐，负数表示未结清的帐，不知道大家还有没有更好的写法！

这个数据库有什么不足？

1. 它没有记录回滚的架构设计，建议能对有问题的产品采取退货和退款服务。
2. 编号为P001的货品即将涨价，这对以前购买过这种货物的顾客计算余额时有什么影响吗？建议对数据库的结构进行修订，以防止这个情况
3. 当前所有的货物都必须按清单上的价格购买，但基于某些特定的情况可能会有折扣，建议记录折扣信息
4. 数据会不断的增长……