MIDTERM
CS122, Spring 2004
Please print out your answer sheet and turn it in
at
the end of the exam. Do not forget to put your name on the answer
sheet.
[Database Description] A small online computer store uses a MS Access
database
to keep track of product, customer, and sales information. The database
consists of four tables, Products, Customers, Orders, and
Order_details. The table
design
of each table is as follows, where each field of the table is shown in
the format <field name :
field type - description of the field>.
Products
- pid : TEXT - product id.
The product id for each product is unique, and a product id consists of
three letters followed by a three digit number, e.g. CPU010 or
HDD079. The three letters in a product id represent the category of the
product, e.g. the product id of all computer processors start with CPU,
and the product id of all hard drives start with HDD.
- description : TEXT -
description of the product.
- quantity : NUMBER -
the quantity of the product currently in stock. If the value of
this field is 0, it means the product is currently out of stock.
- price : CURRENCY - price
of the product.
Customers
- cid : NUMBER - customer
id. Note that two customers may have the same last name and first name,
but the customer id for each customer is unique.
- lname : TEXT - customer's
last name.
- fname : TEXT - customer's
first name.
- address : TEXT -
customer's address.
Orders
- oid : NUMBER - order id.
The order id for each order is unique.
- cid : NUMBER - customer
id of the customer who placed the order.
- odate : DATE - order
date, which is the date when the order was placed.
- sdate : DATE - shipping
date, which is the date when the order was shipped. Note that the value
of this field could be NULL, which means the order has not been shipped
yet.
Order_details
- oid : NUMBER - order id.
Note that there could be duplicated order id's in this table. For
example, a row of <1002, CPU010, 1> and a row of <1002,
HDD079, 2> mean the order 1002 includes one computer processor and two
hard drives.
- pid : TEXT - the product
id of the product ordered.
- quantity : NUMBER - the
quantity of the product ordered.
[Queries] Based on the database
given above, please complete the queries in the answer sheet.