Product (ProdID, Name, Price)
и Sales(Date, ProdID, Sum)
. Какой запрос возвращает сумму продаж самого дорогого товара в июне 2019 года. Запрос должен возвращать Name
и Sum
.SELECT Product.Name AS Наименование, Sales.Sum AS Сумма
FROM Product, Sales
WHERE Product.ProdID = Sales.ProdID AND YEAR(DATE) = 2019 AND MONTH(DATE) = 07 AND Price = max(Price);
GROUP BY Product.Name
SELECT * FROM (
-- SELECT MOST EXPENSIVE PRODUCT
SELECT TOP(1) ProdID, Name
FROM Product
ORDER BY Price DESC
) ExpensiveProd
JOIN (
-- CALCULATE SALES BY PRODUCT IN DATE INTERVAL
SELECT ProdID, SUM(Sum) ProdSales
FROM Sales
WHERE Date BETWEEN '2019-07-01' AND '2019-07-31' GROUP BY ProdID
) ProdSales ON ProdSales.ProdID = ExpensiveProd.ProdID;