ASSIGNMENT: DATABASE SYSTEMS PROJECT

This project is about a company called the Merchandise Distribution Company or MDC. MDC buys merchandise from suppliers both American and foreign and sells the merchandise to stores. When the merchandise arrives MDC first stores the merchandise in warehouses and then the sales representatives sell the merchandise to the different stores. The following tables are used in this project.

The "ST" or STORE table has 6 columns and each row describes one of the stores to whom we sell merchandise. Each store is given a unique store number (STORENO) which is the PK of the table. NAME is the next column and is the name of the store. CITY and STATE are the next two columns and tells us the location of the store. MGR_NAME is the name of the manager of the store. COUNTRY is the country where the store is located.

The "I" or ITEM table has 6 columns. The first column is ITEMNO which is a unique number assigned to each item so we can identify it easily. It is the PK of the table. NAME is the next column and is the name of the item. COLOR is the color of the item. WEIGHT is the weight of the item. PRICE is the current price of the item that we sell to stores. DESC is the last column and it is a description of the item.

1. Find the name and number of all stores that have not sold any items that could be supplied by foreign suppliers.

2. Give the name and number of all stores along with the average amount of sales and the total amount of sales as long as there are at least 2 sales for the store.

3. Find the name and number of the sales rep who makes the smallest salary.

4. Find the number of items that we offer for sale in each color.

5. Find the name and number of all items that have no known weight.

6. Create a view that has for each Repno the number of stores in his territory (use table T). Then, using this view write a select statement that finds the Repno of the sales rep who has the most stores in his territory compared to the other sales reps.

7. Find the name and number of all foreign suppliers where the province is unknown and the language spoken has the letter "z" (could be upper or lower) and the name of the supplier does not have the letter "P" (upper) and does not have the letter "t" (lower).

8. Find the name and number of all items that are either supplied by a foreign supplier but not an American supplier or by an American supplier but not by a foreign supplier.

9. Which sales transaction had the most number of different items? Provide the sales transaction number.