1. Write SQL to Join 3 tables from your test database to select Total Sales Amou

Question

1. Write SQL to Join 3 tables from your test database to select Total Sales Amount per Agent and Product Family

Run this SQL in your database.

2. Write SQL to join tables from your database to select Market and number of the Customers belonged to it from Higher number to Lower

Run SQL in your database

Product Family Key Field Type Key Field Type Key Field Type ManagerName varchar(30) CommisionsPct float ManagerPhone char(12) AgentName varchar(30) AgentPhone char(12) PK AgentID FK ManagerlD int int ProductDescr varchar(100) Unit Price PK ProductID int ManagerAgent FK FK Key DeAssign date ManagerlD int AgentID int Sales Order OrderDate date Market Total Amt int PK MarketlD Type MarketDeahar50) FK CustID FK ProductID FK AgentlD PK OrderNo Customer Customer Market int PK Customer Name varchar(50) FK CustID int int FK MarketlD int Customer Phone char(12) achar(20) Customer Zip Customer ID

Explanation / Answer

1. Select Total Sales Amount per Agent and Product Family

SELECT
ProductFamily,
AgentName,
SUM(TotalAmt * Qty) [Sale Amount]
FROM [Sales Order] SO
INNER JOIN [Agent] A
ON A.AgentID = SO.AgentID
INNER JOIN [Product] P
ON P.ProductID = SO.ProductID
INNER JOIN [Product Family] PF
ON PF.ProductID = P.ProductID
GROUP BY ProductFamily,
AgentName

2. select Market and number of the Customers belonged to it from Higher number to Lower

SELECT
MarketDescr,
COUNT(DISTINCT CustID) [Number of Customer]
FROM Market M
INNER JOIN [Customer Market] CM
ON M.MarketID = CM.MarketID
GROUP BY MarketDescr
ORDER BY [Number of Customer] DESC

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.