10 Aug 2001

Due:  17 Aug 2001
				Assignment 3
                                ~~~~~~~~~~~~
Consider the following schema (the schema is
the same as the one in last weeks assignment)

        CLIENT (client_id, name, city);
        PRODUCT(product_id, description, cost);
        SALESMAN(salesman_id, name, target, city);
        SALES_ORDER(order_id, date, client_id, salesman_id);
        ORDER_DETAILS(order_id, product_id, quantity);

For each relation, the fields with _id suffix together form the primary key 
except in the case of SALES_ORDER where order_id is the primary key.

Implement the queries listed below in SQL.
As before, make sure to eliminate duplicates where they may be generated.

IMPORTANT: This time you must also create test data to check if the queries 
generate the required results.  Part of the marks are for creating a 
good data set for testing your queries.  

---------------------------

Queries:

1. List all salesmen along with the total cost of all items sold by them,
   in decreasing order of total cost. 

2. As in part 1, but grouped by city, with salesmen within the city 
   listed in decreasing order.

3. As in part 2, but grouped by product.  That is, for each product, 
   list all salesmen who sold the product, in decreasing order of
   quantity of product they sold.    Sort products in alphabetical order.

4. List the top city by total sales to all clients based in each city, 
   as well as all cities with total sales within 50 percent of the top 
   city's sales.  Again, sort in decreasing order of total sales.

5. Find the weighted average cost of all products sold (i.e., weighted by 
   sales, so if you sell 2 units of cost 1 and 1 of cost 4, the average is 2)

6. Find the fraction of all sales due to products with cost less than
   the weighted average cost.

7. List all salesmen who sold more (by total cost) to clients outside their
   city than to clients in their city.  Print out the total sales in
   each of these categries for each of these salesmen.