Microsoft Access Query 3 (2 pts) For each maker that produces PCs, find the aver
ID: 3741189 • Letter: M
Question
Microsoft Access
Query 3 (2 pts)
For each maker that produces PCs, find the average price of the models of PCs produced by this maker. Round the price up to the nearest integer. Output a table consisting of two columns: (maker, average price).
Product maker model type A 1001 pc A 1002 pc A 1003 pc B 1004 pc B 1005 pc B 1006 pc C 1007 pc D 1008 pc D 1009 pc D 1010 pc E 1011 pc E 1012 pc E 1013 pc E 2001 laptop E 2002 laptop E 2003 laptop A 2004 laptop A 2005 laptop A 2006 laptop B 2007 laptop F 2008 laptop F 2009 laptop G 2010 laptop E 3001 printer E 3002 printer E 3003 printer D 3004 printer D 3005 printer H 3006 printer H 3007 printerExplanation / Answer
For this both the table product and pc are joined. Aggregate function AVG() is used to find the average price of pc's along with the group by clause. A subquery is used in the where clause to filter only those maker who produces pc.
Query-
SELECT product.maker, AVG(price)
FROM product
INNER JOIN pc
ON pc.model = product.model
WHERE maker IN (SELECT maker FROM product WHERE type = 'pc')
GROUP BY product.maker;