The tables in Table 4-17 reveal the structure of a database for the following queries.

Build the database and enter data from these tables in a relational DBMS package, build the database and import an expanded dataset formatted in Excel available at http://www.pearsonhighered.com/romney, or retrieve the database from http://www.pearsonhighered.com/romney (in Microsoft Access format).

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. For which items are there at least 100 units in Quantity On Hand?

b. What were the item numbers, price, and quantity of each item sold on invoice number 103?

c. Which customers made purchases from Martinez? Provide Customer Name and Sales Person. (Show customer names only once, even if they purchased from Martinez multiple times.)

d. List Invoice, Sale Date, Sales Person, Customer Name, and Amount for Invoices whose Amount is over $1,500. Sort the Amount from largest to smallest.

e. List Invoice, Item ID, Description, Sell Price, List Price, Quantity, and compute the Extension (Sell Price * Quantity) for all items sold where the Sell Price is different than the List Price. Sort by Item ID in descending order.

f. Compute the profit margin for each item sold. Calculate the profit margin as (SellPrice-StdCost)/SellPrice. List Invoice, SaleDate, ItemID, SellPrice, Quantity, and computed ProfitMargin. Sort by ProfitMargin in descending order.

g. Compute the profit for each line of the invoices. Calculate the profit as (SellPrice –
StdCost) * Quantity. List Invoice, SaleDate, ItemID, SellPrice, Quantity, and computed Profit. Sort by Invoice in ascending, then Profit in descending order.

h. How many sales transactions were made in October? Name your output column Num_Of_Sales.

i. How many customers live in Arizona? List the State and CountOfCustomers.

j. How many customers live in each state? List the State and CountOfCustomers. Sort the list alphabetically by State.

k. How much did each salesperson sell based on Sales.Amount? List SalesPerson and TotalAmount. Sort from largest TotalAmount to smallest.

l. How many units of each item were sold? List ItemID, Description, TotalSold. Sort on TotalSold in ascending order.

m. Compute a total for each invoice based on Quantity times SellPrice. Name your total InvoiceTotal. Display Invoices where InvoiceTotal does not equal Amount. List Invoice, SaleDate, SalesPerson, Amount, InvoiceTotal. If no rows meet your criteria, consider changing the value of Amount for Invoice 101 to a different value to test your query.

n. Show invoices for the customer(s) with the largest credit limit. List CreditLimit, CustomerName, Invoice, SaleDate, Amount.

o. Show invoices that are 2 times larger than the average invoice based on Amount. List Invoice, SaleDate, Amount.