Product_Management_Project .sql

-- Create database 
CREATE DATABASE group2_db;

-- Set as Default
USE group2_db;

-- Create Products table
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(200),
    category VARCHAR(100),
    price DECIMAL(10,2),
    quantity INT,
    supplier VARCHAR(200)
);

-- Insert at least 20 records
INSERT INTO products (product_id, product_name, category, price, quantity, supplier) 
VALUES
(1, 'Wireless Gaming Mouse X1', 'Electronics', 78.91, 25, 'TechCorp Solutions'),
(2, 'Bamboo Fountain Pen Elite', 'Stationery', 129.50, 8, 'WriteWell Industries'),
(3, 'Smart Coffee Maker Pro', 'Appliances', 199.99, 15, 'HomeLogic Ltd'),
(4, 'Ergonomic Office Chair', 'Furniture', 299.75, 12, 'ComfortSeating Co'),
(5, 'LED Desk Lamp Touch', 'Electronics', 45.25, 30, 'BrightTech Supply'),
(6, 'Gel Pen Set Professional', 'Stationery', 12.99, 45, 'WriteWell Industries'),
(7, 'Bluetooth Speaker Mini', 'Electronics', 89.99, 0, 'SoundWave Corp'),
(8, 'Stainless Steel Blender', 'Appliances', 156.80, 7, 'KitchenMaster Inc'),
(9, 'Memory Foam Pillow', 'Home', 39.95, 22, 'SleepComfort LLC'),
(10, 'Mechanical Keyboard RGB', 'Electronics', 134.99, 18, 'TechCorp Solutions'),
(11, 'Digital Pen Tablet', 'Electronics', 249.99, 6, 'CreativeTools Ltd'),
(12, 'Air Fryer Deluxe', 'Appliances', 179.99, 14, 'KitchenMaster Inc'),
(13, 'Wooden Desk Organizer', 'Furniture', 28.50, 35, 'WoodCraft Studios'),
(14, 'Noise Cancelling Headphones', 'Electronics', 279.99, 9, 'SoundWave Corp'),
(15, 'Ceramic Pen Holder', 'Stationery', 15.75, 50, 'ArtisanCraft Co'),
(16, 'Smart Thermostat V2', 'Electronics', 189.99, 11, 'HomeLogic Ltd'),
(17, 'Vacuum Cleaner Compact', 'Appliances', 129.99, 0, 'CleanTech Systems'),
(18, 'Adjustable Monitor Stand', 'Furniture', 67.50, 20, 'ComfortSeating Co'),
(19, 'Fountain Pen Ink Cartridges', 'Stationery', 8.99, 75, 'WriteWell Industries'),
(20, 'Wireless Charging Pad', 'Electronics', 34.99, 28, 'TechCorp Solutions'),
(21, 'Electric Kettle Rapid', 'Appliances', 54.99, 16, 'KitchenMaster Inc'),
(22, 'Calligraphy Pen Set', 'Stationery', 42.25, 13, 'ArtisanCraft Co');


-- 1. List all product names, ordered by price
SELECT product_name 
FROM products 
ORDER BY price;

-- 2. Show products with quantity less than 10 AND price above 50
SELECT * 
FROM products 
WHERE quantity < 10 AND price > 50;

-- 3. List all unique suppliers
SELECT DISTINCT supplier 
FROM products;

-- 4. Find products in the category “Electronics” or “Appliances”
SELECT * 
FROM products 
WHERE category IN ('Electronics', 'Appliances');

-- 5. Show products where name contains the word “Pen”
SELECT * 
FROM products 
WHERE product_name LIKE '%Pen%';

-- 6. Display top 5 most expensive products
SELECT * 
FROM products 
ORDER BY price DESC 
LIMIT 5;

-- 7. Count how many products are available for each category
SELECT category,
COUNT(*) AS product_count 
FROM products 
GROUP BY category;

-- 8. Find the cheapest product
SELECT * 
FROM products 
WHERE price = (SELECT MIN(price) FROM products);

-- 9. Update the quantity of a product
UPDATE products 
SET quantity = 35 
WHERE product_name = 'Wireless Mouse';

-- 10. Delete all products out of stock
DELETE FROM products 
WHERE quantity = 0;