FREE AI-Enhanced Online SQL Server Compiler

Untitled*

You must execute the code again to save those values in the database.

CREATE TABLE Categories (
    CategoryId INT PRIMARY KEY,
    CategoryName VARCHAR(100)
);

INSERT INTO Categories (CategoryId, CategoryName) VALUES 
(1, 'Electronics'),
(2, 'Clothing'),
(3, 'Groceries'),
(4, 'Furniture');

CREATE TABLE Products (
    ProductId INT PRIMARY KEY,
    ProductName VARCHAR(100),
    CategoryId INT,
    UnitPrice DECIMAL(10,2),
    FOREIGN KEY (CategoryId) REFERENCES Categories(CategoryId)
);

INSERT INTO Products (ProductId, ProductName, CategoryId, UnitPrice) VALUES 
(1, 'Laptop', 1, 1200.00),
(2, 'Smartphone', 1, 800.00),
(3, 'Headphones', 1, 150.00),
(4, 'T-Shirt', 2, 25.00),
(5, 'Jeans', 2, 50.00),
(6, 'Jacket', 2, 120.00),
(7, 'Milk', 3, 2.50),
(8, 'Bread', 3, 1.50),
(9, 'Cheese', 3, 5.00),
(10, 'Sofa', 4, 900.00),
(11, 'Dining Table', 4, 1500.00),
(12, 'Chair', 4, 250.00);

/*SELECT c.CategoryName, 
       (SELECT MAX(UnitPrice) 
        FROM Products p 
        WHERE p.CategoryId = c.CategoryId) AS MaxPrice
FROM Categories c;*/

WITH MaxPrices AS (
    SELECT CategoryId, MAX(UnitPrice) AS MaxPrice
    FROM Products
    GROUP BY CategoryId
)
SELECT c.CategoryName, m.MaxPrice
FROM Categories c
LEFT JOIN MaxPrices m ON c.CategoryId = m.CategoryId;

CategoryName	MaxPrice
Electronics	1200.00
Clothing	120.00
Groceries	5.00
Furniture	1500.00

(4 rows affected)

(12 rows affected)

(4 rows affected)