# SQL Exercises

## Exercise 1: Basic Queries

**Task:** Given `products` (id, name, price, category), write queries to:
1. Get all products in category 'Electronics'
2. Get product names and prices, sorted by price descending
3. Get the count of products per category

**Validation:**
- [ ] WHERE filters correctly
- [ ] ORDER BY produces correct sort
- [ ] GROUP BY with COUNT gives per-category counts

**Hints:**
1. SELECT name, price FROM products WHERE category = 'Electronics'
2. ORDER BY price DESC
3. SELECT category, COUNT(*) FROM products GROUP BY category

---

## Exercise 2: INNER vs LEFT JOIN

**Task:** Tables: `customers` (id, name), `orders` (id, customer_id, total). Write:
1. INNER JOIN: orders with customer names (exclude orders with invalid customer_id)
2. LEFT JOIN from orders to customers: all orders with customer name, or NULL if customer missing

**Validation:**
- [ ] INNER returns only orders with valid customer
- [ ] LEFT returns all orders; NULL for orphaned orders
- [ ] Correct ON condition (orders.customer_id = customers.id)

**Hints:**
1. JOIN customers ON orders.customer_id = customers.id
2. LEFT JOIN keeps all rows from left (orders)
3. Test with an order whose customer_id doesn't exist

---

## Exercise 3: Aggregation and HAVING

**Task:** From `orders` (id, customer_id, total, status), find:
1. Total sales per customer
2. Customers with total sales > 1000
3. Count of orders per status

**Validation:**
- [ ] SUM(total) grouped by customer_id
- [ ] HAVING SUM(total) > 1000
- [ ] GROUP BY status with COUNT

**Hints:**
1. GROUP BY customer_id, SUM(total)
2. HAVING goes after GROUP BY
3. GROUP BY status

---

## Exercise 4: Subquery

**Task:** Find all products (id, name, price) that have been ordered (table `order_items`: order_id, product_id, quantity). Use a subquery with IN or EXISTS.

**Validation:**
- [ ] Only products that appear in order_items
- [ ] Subquery correctly correlates or filters
- [ ] No duplicate products (DISTINCT if using IN)

**Hints:**
1. SELECT * FROM products WHERE id IN (SELECT DISTINCT product_id FROM order_items)
2. Or: WHERE EXISTS (SELECT 1 FROM order_items WHERE order_items.product_id = products.id)

---

## Exercise 5: Normalization

**Task:** This table has redundancy: `sales` (id, product_name, product_price, category, quantity, sale_date). Split into `products` and `sales` to satisfy 2NF. Define the new tables and relationship.

**Validation:**
- [ ] products: id, name, price, category
- [ ] sales: id, product_id (FK), quantity, sale_date
- [ ] No repeated product info in sales
- [ ] Can join to get product details for a sale

**Hints:**
1. products holds product_name, product_price, category once
2. sales references product_id
3. JOIN sales ON products.id = sales.product_id for full info