Activity 3: Querying Data with SELECT

-- Create the products table
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock_quantity INT NOT NULL,
    brand VARCHAR(50) NOT NULL,
    rating DECIMAL(2,1) DEFAULT 0.0,
    release_date DATE NOT NULL,
    is_active BOOLEAN DEFAULT TRUE
);

-- Insert sample data
INSERT INTO products (name, category, price, stock_quantity, brand, rating, release_date) VALUES
('iPhone 15 Pro', 'Smartphones', 999.99, 45, 'Apple', 4.8, '2023-09-12'),
('Galaxy S24', 'Smartphones', 899.99, 32, 'Samsung', 4.7, '2024-01-17'),
('MacBook Air M2', 'Laptops', 1199.99, 28, 'Apple', 4.9, '2023-06-05'),
('ThinkPad X1', 'Laptops', 1399.99, 15, 'Lenovo', 4.6, '2023-11-20'),
('AirPods Pro', 'Audio', 249.99, 120, 'Apple', 4.7, '2023-09-07'),
('Surface Laptop', 'Laptops', 999.99, 22, 'Microsoft', 4.5, '2023-08-15'),
('Galaxy Buds Pro', 'Audio', 199.99, 85, 'Samsung', 4.4, '2023-10-01'),
('iPad Air', 'Tablets', 599.99, 40, 'Apple', 4.8, '2023-09-20'),
('Pixel 8', 'Smartphones', 699.99, 18, 'Google', 4.6, '2023-11-30'),
('Dell XPS 13', 'Laptops', 1099.99, 12, 'Dell', 4.5, '2023-07-10');

-- Create essential indexes for performance
CREATE INDEX idx_products_category ON products(category);
CREATE INDEX idx_products_brand ON products(brand);
CREATE INDEX idx_products_price ON products(price);
CREATE INDEX idx_products_rating ON products(rating);

-- Check your data loaded correctly
SELECT 
    COUNT(*) as total_products,
    COUNT(DISTINCT category) as categories,
    COUNT(DISTINCT brand) as brands,
    AVG(price) as avg_price,
    AVG(rating) as avg_rating
FROM products;

-- Essential syntax: SELECT columns FROM table WHERE conditions ORDER BY columns LIMIT number

-- Pattern 1: Simple filtering
SELECT * FROM products WHERE category = 'Smartphones';

-- Pattern 2: Specific columns (more efficient)
SELECT name, price, brand FROM products WHERE price < 500;

-- Pattern 3: Multiple conditions
SELECT name, brand, price, rating 
FROM products 
WHERE rating >= 4.7 AND price > 1000;

-- Pattern 4: Sorting results
SELECT * FROM products 
WHERE brand = 'Apple' 
ORDER BY price DESC;

-- IN operator for multiple values
SELECT * FROM products 
WHERE category IN ('Laptops', 'Tablets');

-- BETWEEN for ranges
SELECT name, price, brand 
FROM products 
WHERE price BETWEEN 200 AND 1000;

-- LIKE for pattern matching
SELECT * FROM products WHERE name LIKE '%Pro%';

-- NOT operator for exclusion
SELECT * FROM products 
WHERE category NOT IN ('Smartphones') 
AND stock_quantity > 20;

-- Multi-criteria search with ranking
SELECT 
    name,
    brand,
    price,
    rating,
    stock_quantity,
    CASE 
        WHEN stock_quantity > 50 THEN 'In Stock'
        WHEN stock_quantity > 10 THEN 'Limited Stock'
        ELSE 'Low Stock'
    END as availability,
    CASE 
        WHEN rating >= 4.7 THEN 'Premium'
        WHEN rating >= 4.0 THEN 'Good'
        ELSE 'Budget'
    END as quality_tier
FROM products
WHERE 
    category IN ('Smartphones', 'Laptops')
    AND price BETWEEN 500 AND 1500
    AND stock_quantity > 0
    AND is_active = true
ORDER BY 
    rating DESC,
    price ASC
LIMIT 10;

-- Category analysis
SELECT 
    category,
    COUNT(*) as product_count,
    AVG(price) as avg_price,
    MIN(price) as min_price,
    MAX(price) as max_price,
    AVG(rating) as avg_rating
FROM products
GROUP BY category
ORDER BY avg_rating DESC;

-- Brand performance metrics
SELECT 
    brand,
    COUNT(*) as products,
    ROUND(AVG(price), 2) as avg_price,
    ROUND(AVG(rating), 2) as avg_rating,
    SUM(stock_quantity) as total_inventory
FROM products
WHERE is_active = true
GROUP BY brand
HAVING COUNT(*) >= 2  -- Only brands with 2+ products
ORDER BY avg_rating DESC, avg_price ASC;

-- Find all products under $500, sorted by best rating first
SELECT name, price, brand, rating, category
FROM products 
WHERE price < 500 
ORDER BY rating DESC, price ASC;

-- Find highly-rated products (>= 4.7) over $1000
SELECT name, brand, price, rating, stock_quantity
FROM products 
WHERE rating >= 4.7 AND price > 1000
ORDER BY price DESC;

-- Show all Apple products with availability status
SELECT 
    name, 
    price, 
    stock_quantity,
    CASE 
        WHEN stock_quantity > 50 THEN 'Plenty Available'
        WHEN stock_quantity > 20 THEN 'In Stock'
        WHEN stock_quantity > 0 THEN 'Limited Stock' 
        ELSE 'Out of Stock'
    END as availability
FROM products 
WHERE brand = 'Apple' 
ORDER BY price DESC;

-- Find products running low on stock (< 20 units)
SELECT 
    name, 
    category, 
    brand,
    stock_quantity,
    price * stock_quantity as inventory_value
FROM products 
WHERE stock_quantity < 20
ORDER BY stock_quantity ASC;

-- Products released in 2024, sorted by date
SELECT 
    name, 
    brand, 
    category, 
    price, 
    rating,
    release_date
FROM products 
WHERE release_date >= '2024-01-01' 
ORDER BY release_date DESC;

-- Customer interested in Laptops OR Tablets
SELECT * FROM products 
WHERE category IN ('Laptops', 'Tablets')
AND stock_quantity > 0
ORDER BY rating DESC, price ASC;

-- Products between $600-$1200 with good ratings
SELECT 
    name, 
    price, 
    brand, 
    rating,
    category
FROM products 
WHERE price BETWEEN 600 AND 1200
AND rating >= 4.0
ORDER BY rating DESC, price ASC;

-- Show top 5 highest-rated products
SELECT 
    name, 
    brand, 
    category,
    price, 
    rating,
    stock_quantity
FROM products 
WHERE rating IS NOT NULL
ORDER BY rating DESC, price ASC 
LIMIT 5;

-- 1. Test query correctness
SELECT 'Total products' as metric, COUNT(*) as value FROM products
UNION ALL
SELECT 'Active products', COUNT(*) FROM products WHERE is_active = true
UNION ALL  
SELECT 'In-stock products', COUNT(*) FROM products WHERE stock_quantity > 0
UNION ALL
SELECT 'Premium products (>$1000)', COUNT(*) FROM products WHERE price > 1000;

-- 2. Verify data consistency
SELECT 
    category,
    COUNT(*) as count,
    MIN(price) as min_price,
    MAX(price) as max_price
FROM products
GROUP BY category
ORDER BY category;