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.

 

 

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

 

Table 4-17 Problem 4.5 Tables Customer CustomerID CustomerName ........ Inventory 1000 Smith 1001 Jones 1002 Jeffries 1003 Gilkey 1004 Lankford 1005 Zeile Sales 00000 1006 Pagnozzi 1007 Arocha ItemID. City Description StdCost 1010 Blender 1015 Toaster 1020 Mixer 1025 Television 1030 Freezer 1035 Refrigerator 1040 Radio 1045 Clock Invoice. SaleDate 101 102 Phoenix St. Louis Atlanta Phoenix Phoenix Chicago Salt Lake City Chicago 14 12 23 499 799 699 45 79 SalesPerson 10/3/2018 Wilson 10/5/2018 Mahomet 103 10/5/2018 Jackson 104 10/15/2018 Drezen 105 10/15/2018 Martinez 106 10/16/2018 Martinez 107 10/29/2018 Mahomet 108 11/3/2018 Martinez State- 22322 5: AZ MO GA AZ AZ IL UT IL 849.95 79.97 99.95 CreditLimit. Customer D 2500 1500 4000 5000 2000 2000 ListPrice. QuantityOnHand. 29.95 19.95 33.95 699.95 999.95 5000 1000 200 300 250 74 32 25 100 300 Amount 1000 1,549.90 1003 299.95 1002 1,449.80 1000 799.90 1005 849.95 1007 99.95 1002 2,209.70 1000 779.90 Sales Inventory Invoice. ItemID. 101 101 102 103 103 103 104 104 105 106 107 107 107 107 108 108 1025 1035 1045 1010 1015 1025 1025 1045 1035 1045 1030 1035 1040 1045 1025 1045 Quantity. 1 1 3 1 1 2 1 1 1 1 1 1 2 2 1 1 SellPrice 699.95 849.95 99.95 29.95 19.95 699.95 699.95 99.95 849.95 99.95 999.95 849.95 79.97 99.95 689.95 89.95