Enter the tables in Table 4-15 into a relational DBMS package. Write queries to
ID: 3749976 • Letter: E
Question
Enter the tables in Table 4-15 into a relational DBMS package. Write queries to answer the following questions. Note: For some questions, you may have to create two queries—one to calculate a 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 in voice 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?PLEASE SHOW ANWSER IN ACCESS, PLEASE LINK ACCESS FILE
TABLE 4-15 Problem 4.5 Tables customer Customer #. Customer Name. City Phoenix State Credit Limit. . Phoenix 1004 Lankford Record: H 9 of9H No Filter Search Invoice Number Item Number QuantityExtension Inventory Item # * Description * Unit Cost Unit Price . Quantity on Hand . 1015 Toaster 1030 Freezer 0 Record: 49 of9H No Filte Record::4 417 of 17. Invoice Number 06 10/16/2018 Martinez 107 10/29/2018 Mahomet No Filter SearchExplanation / Answer
If you have any doubts, please give me comment...
-- a. Which customers (show their names) made purchases from Martinez?
SELECT [Customer Name]
FROM Customer C, Sales S
WHERE C.[Customer #] = S.[Customer Number] AND Salesperson = 'Martinez';
-- b. Who has the largest credit limit?
SELECT [Customer Name]
FROM Customer
WHERE [Credit Limit] = (
SELECT MAX([Credit Limit])
FROM Customer;
);
-- c. How many sales were made in October?
SELECT COUNT(*)
FROM Sales
WHERE MonthName(Month(Date)) = 'October';
-- d. What were the item numbers, price, and quantity of each item sold on invoice number 103?
SELECT [Item Number], Extension, Quantity
FROM [Sales Inventory]
WHERE [Invoice Number] = 103;
-- e. How much did each salesperson sell?
SELECT Salesperson, SUM(Amount)
FROM Sales
GROUP BY Salesperson;
-- f. How many customers live in Arizona?
SELECT COUNT(*)
FROM Customer
WHERE City = 'Arizona';
-- g. How much credit does each customer still have available?
SELECT [Customer Name], [Credit Limit] - SUM(Amount)
FROM Customer C, Sales S
WHERE C.[Customer #] = S.[Customer Number]
GROUP BY [Customer Name], [Credit Limit];
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.