Implement the set of relational tables shown in Table 4-15 in a relational DBMS package to which you have access. Write queries to answer the following questions. Note: For some of these questions you may have to create two queries—one to calculate a total such as invoice total and the second to answer the question asked.

a. Which customers (show their names) made purchases from Martinez?

b. Who has the largest credit limit?

c. How many sales were made in October?

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

e. How much did each salesperson sell?

f. How many customers live in Arizona?

g. How much credit does each customer still have available?

h. How much of each item was sold? (Include the description of each item in your answer.)

i. Which customers still have more than $1,000 in available credit?

j. For which items are there at least 100 units on hand?

Transcribed Image Text:

Table 4-15 Customer Table Customer Numb Name City State Credit Lim 1000 Smith Phoenix AZ 2500 1001 Jones St Louis MO 1500 1002 Jeffries Atlanta GA 4000 1003 Glkey Phoenix AZ 5000 1004 Lankford Phoenix AZ 2000 1006 Zele Chicago IL 2000 1006 Pagnazzi 1007 Amchal Salt Lake City UT Chicago LL 3000 1000 Record 14 of wentory: Table Rem Number Desorption 1010 Blender 1015 Toaster 1020 Moxer 1025 Television 1030 Freezer 1035 Refrigerator Record 1040 Radio 1045 Clock Sales Inventory: Table woice Number Item Number Quantity Extension 1011 1025 699 95 101 1035 1 849 95 102 1045 3 299 86 Unt Cast Unit Price Quantity on Hand 103 1010 1 29.95 14 29.96 200 103 1015 3 19.95 12 19.95 300 100 1025 2 1399.90 23 33.96 250 104 1025 1 659 95 499 699 95 74 104 1045 1 99.95 799 999 95 32 105 1035 1 849 95 599 849 95 25 106 1045 1 99.95 45 79 95 100 107 1030 1 999.95 79 99.96 300 107 1035 1 849.95 107 1040 2 159 90 of 9 107 1045 2 199 90 108- 1025 699 95 108 1045 99.95 Record 14 17+170 17 Sales Table Invoice Number Date Salesperson Customer Number Amount 101. 10/3/2008 Wilson 1000 1549.90 102 10/5/2008 Mahomet 1003 299.95 103 10/5/2008 Jackson 1002 1449 50 104 10/15/2008 Drezen 1000 799.00 106 10/15/2008 Martinez 1005 849.96 10 10/16/2008 Martinez 1007 99.96 107 10/29/2008 Mahomet 1002 2209.70 108 11/3/2003 Martinez 1000 779.90 Record: 14