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'); --Orders Table: 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'); --Products Table: 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'); --OrderDetails Table: 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; SELECT City, COUNT(*) AS TotalCustomers FROM Customers GROUP BY City HAVING COUNT(*) > 2; SELECT CustomerID, COUNT(*) AS OrderCount FROM Orders GROUP BY CustomerID HAVING COUNT(*) > 2; SELECT o.OrderID, o.OrderAmount, c.CustomerName, c.City FROM Orders o JOIN Customers c ON o.CustomerID = c.CustomerID; SELECT p.ProductID, p.ProductName, p.Price, p.Category FROM Products p JOIN Categories c ON p.CategoryID = c.CategoryID; CREATE TABLE Customers ( CustomerID INT PRIMARY KEY, CustomerName VARCHAR(50), City VARCHAR(50) ); INSERT INTO Customers (CustomerID, CustomerName, City) VALUES (1, 'Alice', 'New York'), (2, 'Bob', 'Los Angeles'), (3, 'Charlie', 'Chicago'), (4, 'David', 'Houston'), (5, 'Emma', 'San Francisco'); --Orders Table: CREATE TABLE Orders ( OrderID INT PRIMARY KEY, CustomerID INT, OrderAmount DECIMAL(10, 2) ); INSERT INTO Orders (OrderID, CustomerID, OrderAmount) VALUES (101, 1, 150.00), (102, 2, 200.00), (103, 3, 100.00), (104, 4, 300.00), (105, 1, 180.00); CREATE TABLE Suppliers ( SupplierID INT PRIMARY KEY, SupplierName VARCHAR(50), SupplierCountry VARCHAR(50) ); INSERT INTO Suppliers (SupplierID, SupplierName, SupplierCountry) VALUES (1, 'Supplier A', 'USA'), (2, 'Supplier B', 'China'), (3, 'Supplier C', 'Japan'); CREATE TABLE Products ( ProductID INT PRIMARY KEY, ProductName VARCHAR(50), Price DECIMAL(10, 2), SupplierID INT, CategoryID INT ); INSERT INTO Products (ProductID, ProductName, Price, SupplierID, CategoryID) VALUES (101, 'Product 1', 500.00, 1, 1), (102, 'Product 2', 50.00, 2, 2), (103, 'Product 3', 700.00, 3, 1), (104, 'Product 4', 300.00, 1, 3), (105, 'Product 5', 80.00, 2, 2); CREATE TABLE Departments ( DepartmentID INT PRIMARY KEY, DepartmentName VARCHAR(50) ); INSERT INTO Departments (DepartmentID, DepartmentName) VALUES (1, 'Sales'), (2, 'Marketing'), (3, 'Operations'); --Employees Table: CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, EmployeeName VARCHAR(50), DepartmentID INT ); INSERT INTO Employees (EmployeeID, EmployeeName, DepartmentID) VALUES (1, 'John Doe', 1), (2, 'Jane Smith', 2), (3, 'David Johnson', 1), (4, 'Emma Brown', 3), (5, 'Michael Davis', 2); SELECT o.OrderID, o.OrderAmount, c.CustomerName, c.City FROM Orders o JOIN Customers c ON o.CustomerID = c.CustomerID; SELECT p.ProductID, p.ProductName, p.Price, s.SupplierName, s.SupplierCountry FROM Products p JOIN Suppliers s ON p.SupplierID = s.SupplierID; SELECT c.CustomerID, c.CustomerName, o.OrderID, o.OrderAmount FROM Customers c LEFT JOIN Orders o ON c.CustomerID = o.CustomerID; SELECT e.EmployeeID, e.EmployeeName, d.DepartmentName FROM Employees e LEFT JOIN Departments d ON e.DepartmentID = d.DepartmentID; SELECT City FROM Customers UNION SELECT City FROM Suppliers; SELECT City FROM Customers UNION ALL SELECT City FROM Suppliers; SELECT ProductName FROM Products UNION SELECT ProductName FROM Inventory; SELECT ProductName FROM Products UNION ALL SELECT ProductName FROM Inventory; SELECT City FROM Customers INTERSECT SELECT City FROM Suppliers; SELECT EmployeeID, EmployeeName FROM Employees INTERSECT SELECT ManagerID, ManagerName FROM Managers; SELECT p.ProductName, c.CustomerName FROM Products p CROSS JOIN Customers c; SELECT c.CategoryName, s.SubcategoryName FROM Categories c CROSS JOIN Subcategories s;