SQLize | PHPize | SQLtest

CREATE TABLE Customers ( CustomerID INT PRIMARY KEY, CustomerName VARCHAR(50), City VARCHAR(50) ); INSERT INTO Customers (CustomerID, CustomerName, City) VALUES (1, 'John Doe', 'New York'), (2, 'Jane Smith', 'Los Angeles'), (3, 'Michael Johnson', 'Chicago'), (4, 'Emily Davis', 'Houston'), (5, 'Chris Brown', 'New York'); CREATE TABLE Orders ( OrderID INT PRIMARY KEY, CustomerID INT, OrderAmount DECIMAL(10, 2), OrderDate DATE ); INSERT INTO Orders (OrderID, CustomerID, OrderAmount, OrderDate) VALUES (1, 1, 150.00, '2023-01-05'), (2, 2, 200.00, '2023-02-10'), (3, 3, 100.00, '2023-02-15'), (4, 4, 300.00, '2023-03-20'), (5, 5, 180.00, '2023-04-25'); CREATE TABLE Products ( ProductID INT PRIMARY KEY, ProductName VARCHAR(50), Category VARCHAR(50), Price DECIMAL(10, 2), SupplierCountry VARCHAR(50) ); INSERT INTO Products (ProductID, ProductName, Category, Price, SupplierCountry) VALUES (1, 'Product A', 'Electronics', 500.00, 'USA'), (2, 'Product B', 'Clothing', 50.00, 'China'), (3, 'Product C', 'Electronics', 700.00, 'Japan'), (4, 'Product D', 'Furniture', 300.00, 'USA'), (5, 'Product E', 'Clothing', 80.00, 'China'); CREATE TABLE OrderDetails ( OrderDetailID INT PRIMARY KEY, OrderID INT, ProductID INT, Quantity INT ); INSERT INTO OrderDetails (OrderDetailID, OrderID, ProductID, Quantity) VALUES (1, 1, 1, 2), (2, 2, 2, 3), (3, 3, 3, 1), (4, 4, 4, 4), (5, 5, 5, 2); SELECT City, COUNT(*) AS TotalCustomers FROM Customers GROUP BY City; SELECT CustomerID, AVG(OrderAmount) AS AvgOrderAmount FROM Orders GROUP BY CustomerID; SELECT MONTH(OrderDate) AS Month, COUNT(*) AS OrdersCount FROM Orders WHERE YEAR(OrderDate) = 2023 GROUP BY MONTH(OrderDate); SELECT CustomerID, MAX(OrderAmount) AS MaxOrderAmount, MIN(OrderAmount) AS MinOrderAmount FROM Orders GROUP BY CustomerID; SELECT DATEPART(WEEKDAY, OrderDate) AS DayOfWeek, COUNT(*) AS OrdersCount FROM Orders GROUP BY DATEPART(WEEKDAY, OrderDate); SELECT YEAR(OrderDate) AS OrderYear, SUM(OrderAmount) AS TotalSales FROM Orders GROUP BY YEAR(OrderDate); SELECT Category, AVG(Quantity) AS AvgQuantity FROM OrderDetails od JOIN Products p ON od.ProductID = p.ProductID GROUP BY Category;