SELECT & Filtering

-- Select specific columns
SELECT name, email FROM users;

-- Select all columns
SELECT * FROM users;

-- Select with aliases
SELECT name AS customer_name,
       email AS contact_email
FROM users;

-- Select distinct values
SELECT DISTINCT country FROM users;

-- Comparison operators
SELECT * FROM products WHERE price > 50;
SELECT * FROM products WHERE price >= 50;
SELECT * FROM products WHERE category != 'Electronics';

-- Logical operators
SELECT * FROM products
WHERE price > 50 AND category = 'Electronics';

SELECT * FROM products
WHERE category = 'Books' OR category = 'Music';

SELECT * FROM products
WHERE NOT category = 'Electronics';

-- LIKE for pattern matching
SELECT * FROM users WHERE name LIKE 'A%';       -- Starts with A
SELECT * FROM users WHERE email LIKE '%@gmail.com'; -- Ends with @gmail.com
SELECT * FROM users WHERE name LIKE '_o%';      -- Second char is 'o'

-- IN for matching a list of values
SELECT * FROM products
WHERE category IN ('Electronics', 'Books', 'Clothing');

-- BETWEEN for ranges (inclusive)
SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

-- IS NULL / IS NOT NULL
SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;

-- Sort ascending (default)
SELECT * FROM products ORDER BY price ASC;

-- Sort descending
SELECT * FROM products ORDER BY price DESC;

-- Multiple sort columns
SELECT * FROM products
ORDER BY category ASC, price DESC;

-- Get top 10 most expensive products
SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 10;

-- Pagination: skip first 20, get next 10
SELECT * FROM products
ORDER BY id
LIMIT 10 OFFSET 20;

SELECT name,
       price,
       quantity,
       price * quantity AS total_value,
       ROUND(price * 0.9, 2) AS discounted_price,
       CASE
         WHEN price > 100 THEN 'Premium'
         WHEN price > 50 THEN 'Standard'
         ELSE 'Budget'
       END AS price_tier
FROM products;