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

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    ProductID INT,
    Quantity INT,
    OrderDate DATE,
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);
INSERT INTO Products VALUES (1, 'Laptop', 75000.00, 10),(2, 'Mouse', 500.00, 100),(3, 'Keyboard', 1000.00, 50);
INSERT INTO Orders VALUES (1, 1, 2, '2024-12-01'),(2, 2, 5, '2024-12-02'),(3, 3, 1, '2024-12-02');

select * from Products;
select * from Orders;

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

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


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

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

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