1. Mapping the Data Items Required to the Column Names in the DB

[QueryID:59]

For Colorado customers, compute the number of unique products ordered. If a product is purchased on multiple orders, it should be counted only one time. The result should include the customer number, the customer last name, and the number of unique products ordered.

The following steps may be helpful in formulating the query for the problem:

1. Mapping the data items required to the column names in the DB

Customer number Þ CustNo

Customer last name Þ CustLastName

Number of unique products ordered Þ one customer (CustNo) may put in one or more orders (OrdNo) and one order may contain one or more products (ProdNo). Each customer has a grouop of products ordered. There may be duplicated product names in the group. Eliminate the duplicates using DISTINCT. The number of unique products ordered for each customer can be calculated by COUNT (DISTINCT ProdNo)

2. Interpret the row selection condition

n Colorado customer Þ CustState = ¢CO¢

3. Identify the table(s) needed, which contain(s) all the data items used in 1) and 2)

customer table, orderTbl table, ordLine table, product table

4. How to join these tables

Joining condition — orderTbl.CustNo =customer.CustNo and orderTbl.OrdNo = ordLine.OrdNo and ordLine.ProdNo = product.ProdNo

Joining styles

– in FROM, customer c join orderTbl o on c.CustNo = o.CustNo join ordLine ol on o.OrdNo = ol.OrdNo join product p on ol.ProdNo =p.ProdNo or

-- in FROM, customer c, orderTbl o,ordLine ol, product p and in WHERE, c.CustNo = o.CustNo and o.OrdNo = ol.OrdNo = ol.ProdNo = p.ProdNo or

-- in FROM, customer c natural join orderTbl o natural join ordLine ol natural join product p

5. Group result or individual result

An aggregate function COUNT() is used for the each customer group to get the number of products ordered Þ group result, Group By is needed. The Grouping Bu columns are CustNo, CustLastName . Note the column CustLastName is redundant but required by SQL engine, and must be specified in Group By clause as it appears in Select clause and is not an aggregate column.

All the groups are considered, so Having clause is not needed.

6. Using the template:

SELECT <list of columns or expressions >

FROM <list of tables or join operations>

WHERE <row selection condition >

GROUP BY < list of grouping columns >