20 Points

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).

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 QuantityOnHand?

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 CustomerName and SalesPerson. (Show customer names only once, even if they purchased from Martinez multiple times.)

d. List Invoice, SaleDate, SalesPerson, CustomerName, and Amount for Invoices whose Amount is more than $1,500. Sort the Amount from largest to smallest.

e. List Invoice, ItemID, Description, SellPrice, ListPrice, Quantity, and compute the Extension (SellPrice * Quantity) for all items sold where the SellPrice is different than the ListPrice. Sort by ItemID 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.

Queries with aggregate functions

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, and 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, and InvoiceTotal. If no rows meet your criteria, consider changing the value of Amount for Invoice 101 to a different value to test your query.

Avanced queries that may require nesting or reference to earlier queries.

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

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