A database has two tables.\n\nProducts(PID, Category, Price) - 200 rows. The 200 products are evenly distributed among 5 categories, and 120 products have $Price 50$. The average $Price$ of these 120 products is $70$.\n\nSales(SID, PID, Quantity) - 800 rows. Each product appears in the Sales table exactly 4 times on average.\n\nConsider the query:\n\nSELECT Category, SUM(Price * Quantity) AS Revenue\nFROM Products NATURAL JOIN Sales\nWHERE Price 50\nGROUP BY Category;\n\n(a) How many rows are produced by the join before the GROUP BY clause is applied?\n(b) How many rows are returned by the query (i.e., number of distinct categories in the result)?\n(c) What is the total Revenue for a particular category, say Category C1, assuming the average Quantity per sale for that category is 3?

Question

A database has two tables.\n\nProducts(PID, Category, Price) - 200 rows. The 200 products are evenly distributed among 5 categories, and 120 products have $Price > 50$. The average $Price$ of these 120 products is $70$.\n\nSales(SID, PID, Quantity) - 800 rows. Each product appears in the Sales table exactly 4 times on average.\n\nConsider the query:\n\nSELECT Category, SUM(Price * Quantity) AS Revenue\nFROM Products NATURAL JOIN Sales\nWHERE Price > 50\nGROUP BY Category;\n\n(a) How many rows are produced by the join before the GROUP BY clause is applied?\n(b) How many rows are returned by the query (i.e., number of distinct categories in the result)?\n(c) What is the total Revenue for a particular category, say Category C1, assuming the average Quantity per sale for that category is 3?

Accepted Answer

Join produces 480 rows; the query returns 5 rows; Revenue for Category C1 is 20,160.

Neco 2023 Data Processing Question 40

Answer Options

About This Question

Master the Exam!