This evening, I studied the example database Acme, reviewed SQL statements, and conducted SQL exercises. These questions mainly involve multi-table queries. Using the creation of views, querying on the views should be a good solution! But there are also some problems. We will study it tomorrow!
Acme database description
- About Acme: it is a typical simple account system. Acme sells products to a certain number of customers. These customers regularly order products. They do not need to pay the cash immediately, but may be temporary debts. They may even have credit, but this is not very common ).
- Badguy customer information
- Product cargo information
- Receipt information
- Shipped shipping records
The database relationship diagram is as follows:
Precautions for financial data
- Currencies should be precisely stored, usually stored as decimal (10, 2) decimal places-generally never use floating point numbers to record currencies.
- We must keep the account clues clear. We need to know not only who owes the money, but also why.
- All transactions should be recorded and kept. Transaction refers to (a) what do you have to do for someone giving you money (B) (here the goods are sent ), do not confuse the database during transaction execution.
- The current balance does not need to be stored. We can calculate it if necessary. We can "consolidate" the account, and put all the transaction information of a customer into a file, and replace all the original transactions with a balance.
- Each transaction should be dated.
- Transactions should never be changed. If the input of a record is incorrect, an additional record should be used for correction. (This database does not support reverse operations)
- We do not try to check the shipping and shipping information.
Exercise questions
-
- Compile a list to list all the items purchased by the customer 'c001', display the date, product description, unit price, quantity, shipment and total value (quantity * price ).
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
-
- Prepare a shipping record for the customer 'c001' and Mark 'delivery' and total value by date
Create view accountline
As select shipped. badguy as badguy, shipped. sdate as linedate,
'Delimitery' as legend,-[price] * [quantity] As amount
From product, shipped
Where product. ID = shipped. Product
Select *
From accountline
Where badguy = 'c001'
Order by linedate
-
- List all products purchased by a customer based on the customer name, including the date of purchase, goods name, quantity, and total value (quantity × price)
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
-
- List the currently settled accounts of a customer based on the name of the customer. The accounts that have not been settled (may be negative)
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
The last one does not count the total number of accounts closed or the total number of accounts not closed, but only lists the accounts. A positive number indicates the accounts settled, A negative number indicates an outstanding account. I don't know if there is any better way to write it!
What are the shortcomings of this database?
- It does not record the architecture design of rollback, and it is recommended that the problematic product be returned and refunded.
- P001 is about to increase the price of goods. Does this affect the balance calculated by customers who have purchased such goods before? We recommend that you revise the database structure to prevent this situation.
- Currently, all goods must be purchased at the price listed in the List. However, discounts may be available in certain situations. We recommend that you record the discount information.
- Data will grow constantly ......
