-- Create Products table
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(50),
    Price DECIMAL(10, 2),
    Stock INT
);

-- Create Orders table
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    ProductID INT,
    Quantity INT,
    OrderDate DATE,
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

-- Insert data into Products
INSERT INTO Products VALUES 
(1, 'Laptop', 75000.00, 10),
(2, 'Mouse', 500.00, 100),
(3, 'Keyboard', 1000.00, 50);

-- Insert data into Orders
INSERT INTO Orders VALUES 
(1, 1, 2, '2024-12-01'),
(2, 2, 5, '2024-12-02'),
(3, 3, 1, '2024-12-02');

-- LEFT JOIN: Products with Orders
SELECT 
    p.ProductName, 
    o.Quantity, 
    o.OrderDate
FROM 
    Products p
LEFT JOIN 
    Orders o ON p.ProductID = o.ProductID;

-- INNER JOIN: Orders with Product details and TotalPrice
SELECT 
    o.OrderID,
    p.ProductName,
    p.Price,
    o.Quantity,
    (p.Price * o.Quantity) AS TotalPrice,
    o.OrderDate
FROM 
    Orders o
JOIN 
    Products p ON o.ProductID = p.ProductID;

-- UNION: Products and Order Total
SELECT 
    ProductName AS Item, 
    Price AS Value 
FROM 
    Products
UNION
SELECT 
    'OrderTotal', 
    SUM(o.Quantity * p.Price) 
FROM 
    Orders o
JOIN 
    Products p ON o.ProductID = p.ProductID;

-- View: ProductDetails
CREATE VIEW ProductDetails AS
SELECT ProductID, ProductName, Price, Stock
FROM Products;

-- View: OrderSummary
CREATE VIEW OrderSummary AS
SELECT o.OrderID, p.ProductID, p.ProductName, o.Quantity, o.OrderDate
FROM Orders o
JOIN Products p ON o.ProductID = p.ProductID;