SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE Customers ( CustomerID varchar(64), CustomerName varchar(64) ); INSERT INTO Customers (CustomerID, CustomerName) VALUES ('1', 'Anny'), ('2', 'Maria'), ('3', 'Martin'), ('4', 'Hanna'); -- CREATE TABLE Orders ( OrderID varchar(64), CustomerID varchar(64) ); INSERT INTO Orders (OrderID, CustomerID) VALUES ('1','1'), ('2','1'), ('3','1'), ('4','1'), ('5','2'), ('6','2'), ('7','2'), ('8','2'), ('9','3'), ('10','3'), ('11','3'), ('12','3'), ('13','4'), ('14','4'), ('15','4'), ('16','4'), ('17','4'); -- CREATE TABLE OrderDetails ( OrderID varchar(64), Quantity varchar(64), ProductID varchar(64) ); INSERT INTO OrderDetails (OrderID, Quantity, ProductID) VALUES ('1','20','1'), ('2','12','2'), ('3','43','3'), ('4','23','4'), ('5','32','5'), ('6','12','6'), ('7','32','7'), ('8','43','8'), ('9','54','9'), ('10','3','10'), ('11','43','11'), ('12','43','12'), ('13','32','13'), ('14','12','14'), ('15','32','15'), ('16','43','16'), ('17','32','17'); -- CREATE TABLE Products ( ProductID varchar(64), CategoryID varchar(64) ); INSERT INTO Products (ProductID, CategoryID) VALUES ('1','1'), ('2','2'), ('3','2'), ('4','1'), ('5','3'), ('6','3'), ('7','4'), ('8','4'), ('9','5'), ('10','5'), ('11','6'), ('12','6'), ('13','7'), ('14','7'), ('15','8'), ('16','8'), ('17','8'); -- CREATE TABLE Categories ( CategoryID varchar(64), CategoryName varchar(64) ); INSERT INTO Categories (CategoryID, CategoryName) VALUES ('1','Tables'), ('2','tables'), ('3','cars'), ('4','microphones'), ('5','cabinets'), ('6','showers'), ('7','monitors'), ('8','books'); -- -- Задача вывести по каждому покупателю Имя категории с самым большим количеством товаров SELECT DISTINCT CustomerName,CategoryName,SUM(Quantity) FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID LEFT JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID LEFT JOIN Products ON OrderDetails.ProductID = Products.ProductID LEFT JOIN Categories ON Products.CategoryID = Categories.CategoryID GROUP BY CustomerName, CategoryName;