CREATE TABLE Categories (
    category_id INT AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(50) NOT NULL
);



CREATE TABLE Products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    category_id INT,
    stock_quantity INT,
    FOREIGN KEY (category_id) REFERENCES Categories(category_id)
);

INSERT INTO Categories (category_name) VALUES
('Electronics'),
('Clothing'),
('Home and Kitchen');


INSERT INTO Products(product_name,price,category_id,stock_quantity) VALUES
('Smartphone',599.99,1,50),
('Laptop',1299.99,1,20),
('T-Shirt',19.99,2,100),
('Cookware Set',89.99,3,30);

Select *from Products;
Select *from Categories;

-- Query 1: Retrieve products with low stock levels (less than 50)

SELECT product_name, stock_quantity
FROM Products WHERE stock_quantity <50;

-- Query 2 Retrieve products and their categories, handling NULL values

SELECT product_name, price 
IF NULL (category_name,'Uncategorized') AS category_name 
FROM Products LEFT JOIN Categories ON Products.category_id=Categories.category_id,

-- Query 3: Create a view for frequently accessed data (product information)

CREATE VIEW ProductVIEW AS SELECT product_id, product_name, price, category_name, stock_quantity
FROM Products 
LEFT JOIN Categories ON Products.category_id=Categories.category_id;
Select data from the created view 
SELECT *FROM ProductView;