Retrieve the Chapter 4 Relational Database (in Microsoft Access format) from http://www.pearsonhighered.com/romney or create the tables in Table 4-5 in a relational DBMS product.

REQUIRED

Write queries to answer the following questions. Your instructor may specify the method of acquiring the database noted above and whether to use Design view or SQL view.

a. List Customers that live in the city of Winslow. Provide Customer ID, Customer Name and Street.

b. List all sales for which Sales Person “B. Green” was responsible. Provide Sales InvoiceID, Sale Date, Customer ID and Customer Name.

c. List all inventory items sold to “Lola Doyle”. Provide Sale Date, Item ID, Description and Quantity.

Queries with Aggregate functions

d. How many different kinds of inventory items does S&S sell? Use Num Of Items as the name of the output column.

e. How many sales were made during October? Use Num Of Sales as the name of your output column.

f. How many sales were made to each customer? List Customer ID, Customer Name and Num Of Sales. Sort the list by Num Of Sales in descending order.

g. How many units of each product were sold? Provide Item ID, Description, Color and Sum Of Quantity, Sort the list by Item ID in ascending order.

h. List the total of each invoice. Computed this by summing Quantity times Sold Price for all the items on each invoice. Name the total Invoice Total. Provide Sales Invoice ID, Sale Date, Sales Person, Invoice Total. Sort the list by Invoice Total in descending order.

i. What were total sales in October? Provide Total Sales. You may reuse your query from question h.

j. What was the average amount of a sales transaction? Provide Avg Sales. You may reuse your query from question h.

k. Which product sold the most units? Provide Item ID, Description, Color and Sum Of Quantity. You may reuse your query from question g.

l. Which salesperson made the largest sale? Provide the Sales Persons name and the amount of the sale. You may reuse your query from question h.