SELECT Statements

-- Select all columns (avoid in production)
SELECT * FROM products;

-- Select only needed columns
SELECT id, name, price FROM products;

-- Use aliases for readability
SELECT 
    id AS product_id,
    name AS product_name,
    price AS unit_price,
    price * 1.1 AS price_with_tax
FROM products;

-- Exact match
SELECT * FROM products WHERE category_id = 2;

-- Not equal
SELECT * FROM products WHERE category_id <> 2;
-- OR
SELECT * FROM products WHERE category_id != 2;

-- Greater than
SELECT * FROM products WHERE price > 1000;

-- Less than or equal
SELECT * FROM products WHERE stock <= 100;

-- Multiple conditions
SELECT * FROM products 
WHERE price > 500 AND stock > 0;

-- BETWEEN (inclusive)
SELECT * FROM products 
WHERE price BETWEEN 500 AND 1500;

-- Equivalent to:
SELECT * FROM products 
WHERE price >= 500 AND price <= 1500;

-- Date ranges
SELECT * FROM orders 
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

-- IN operator (match any in list)
SELECT * FROM products 
WHERE category_id IN (2, 3);

-- NOT IN
SELECT * FROM products 
WHERE category_id NOT IN (4, 5);

-- IN with subquery
SELECT * FROM products 
WHERE category_id IN (
    SELECT id FROM categories WHERE name LIKE 'Electronics%'
);

-- Starts with 'Mac'
SELECT * FROM products WHERE name LIKE 'Mac%';
-- Matches: MacBook Pro, Mac Mini, MacBook Air

-- Ends with 'Pro'
SELECT * FROM products WHERE name LIKE '%Pro';
-- Matches: MacBook Pro, iPad Pro

-- Contains 'Phone'
SELECT * FROM products WHERE name LIKE '%Phone%';
-- Matches: iPhone, Phone Case, Smartphone

-- Exactly 3 characters starting with 'a'
SELECT * FROM products WHERE name LIKE 'a__';
-- Matches: abc, app, air

-- Second character is 'a'
SELECT * FROM products WHERE name LIKE '_a%';
-- Matches: MacBook, Samsung, iPad

-- Case-sensitive by default in PostgreSQL
SELECT * FROM products WHERE name LIKE 'mac%';
-- Won't match 'MacBook'

-- Case-insensitive with ILIKE (PostgreSQL)
SELECT * FROM products WHERE name ILIKE 'mac%';
-- Matches: MacBook, macbook, MACBOOK

-- Case-insensitive in MySQL (by default)
SELECT * FROM products WHERE name LIKE 'mac%';
-- Matches: MacBook, macbook (depends on collation)

-- SQL Server
SELECT * FROM products WHERE name LIKE 'mac%';
-- Depends on collation (CI = case-insensitive)

-- Find rows with NULL values
SELECT * FROM customers WHERE phone IS NULL;

-- This does NOT work!
SELECT * FROM customers WHERE phone = NULL;
-- Always returns empty result

-- Find rows with non-NULL values
SELECT * FROM customers WHERE phone IS NOT NULL;

-- Combine with other conditions
SELECT * FROM customers 
WHERE phone IS NOT NULL AND status = 'active';

-- Replace NULL with a default value
SELECT 
    name,
    COALESCE(phone, 'No phone') AS phone,
    COALESCE(email, 'No email') AS email
FROM customers;

-- COALESCE returns first non-NULL value
SELECT COALESCE(NULL, NULL, 'default');
-- Returns: 'default'

SELECT COALESCE(phone, mobile, work_phone, 'N/A') AS contact_number
FROM contacts;

-- Both conditions must be true
SELECT * FROM products 
WHERE price > 500 AND stock > 0;

-- Multiple ANDs
SELECT * FROM orders 
WHERE status = 'pending'
  AND total > 100
  AND created_at > '2024-01-01';

-- Either condition can be true
SELECT * FROM products 
WHERE category_id = 2 OR category_id = 3;

-- Better with IN
SELECT * FROM products 
WHERE category_id IN (2, 3);

-- Use parentheses for clarity and correctness
SELECT * FROM products 
WHERE (category_id = 2 OR category_id = 3)
  AND price > 500
  AND stock > 0;

-- Without parentheses, this is different!
SELECT * FROM products 
WHERE category_id = 2 OR category_id = 3
  AND price > 500;
-- Equivalent to: category_id = 2 OR (category_id = 3 AND price > 500)

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

-- Descending
SELECT * FROM products ORDER BY price DESC;

-- Alphabetical
SELECT * FROM products ORDER BY name;

-- Sort by multiple columns
SELECT * FROM products 
ORDER BY category_id ASC, price DESC;

-- First sorts by category, then by price within each category

-- PostgreSQL: Control NULL position
SELECT * FROM customers ORDER BY phone NULLS LAST;
SELECT * FROM customers ORDER BY phone NULLS FIRST;

-- MySQL/SQL Server: NULLs typically sort first in ASC
-- Use COALESCE for control
SELECT * FROM customers 
ORDER BY COALESCE(phone, 'ZZZZ');  -- NULLs sort last

-- Or use CASE
SELECT * FROM customers 
ORDER BY CASE WHEN phone IS NULL THEN 1 ELSE 0 END, phone;

-- Sort by calculated value
SELECT name, price, stock, price * stock AS inventory_value
FROM products
ORDER BY price * stock DESC;

-- Sort by alias (supported in most databases)
SELECT name, price * stock AS inventory_value
FROM products
ORDER BY inventory_value DESC;

-- Sort by column position (not recommended)
SELECT name, price FROM products ORDER BY 2 DESC;
-- Sorts by second column (price)

-- Get first 10 rows
SELECT * FROM products ORDER BY price DESC LIMIT 10;

-- Skip first 20 rows, get next 10
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 20;

-- Get first 10 rows
SELECT TOP 10 * FROM products ORDER BY price DESC;

-- With OFFSET (SQL Server 2012+)
SELECT * FROM products
ORDER BY id
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

-- Page calculation
-- Page 1: OFFSET 0, LIMIT 10
-- Page 2: OFFSET 10, LIMIT 10
-- Page 3: OFFSET 20, LIMIT 10
-- Formula: OFFSET = (page - 1) * page_size

-- PostgreSQL/MySQL
SELECT * FROM products
ORDER BY id
LIMIT 10 OFFSET 20;  -- Page 3

-- With total count
SELECT 
    *,
    COUNT(*) OVER() AS total_count
FROM products
ORDER BY id
LIMIT 10 OFFSET 20;

-- Keyset pagination (more efficient)
-- Instead of OFFSET, use WHERE with last seen value
SELECT * FROM products
WHERE id > 1000  -- Last ID from previous page
ORDER BY id
LIMIT 10;

-- Get unique categories
SELECT DISTINCT category_id FROM products;

-- Get unique statuses
SELECT DISTINCT status FROM orders;

-- Unique combinations
SELECT DISTINCT category_id, status FROM products;

-- Count unique values
SELECT COUNT(DISTINCT category_id) AS unique_categories
FROM products;

-- Count unique vs total
SELECT 
    COUNT(*) AS total_products,
    COUNT(DISTINCT category_id) AS unique_categories
FROM products;

SELECT 
    name,
    price,
    stock,
    price * stock AS inventory_value,
    price * 0.1 AS discount_amount,
    price * 0.9 AS discounted_price,
    ROUND(price * 1.08, 2) AS price_with_tax
FROM products;

-- PostgreSQL
SELECT 
    UPPER(name) AS upper_name,
    LOWER(name) AS lower_name,
    LENGTH(name) AS name_length,
    CONCAT(name, ' - $', price) AS display_name,
    LEFT(name, 10) AS short_name,
    SUBSTRING(name, 1, 5) AS prefix
FROM products;

-- MySQL
SELECT 
    UPPER(name),
    LOWER(name),
    CHAR_LENGTH(name),
    CONCAT(name, ' - $', price),
    LEFT(name, 10),
    SUBSTRING(name, 1, 5)
FROM products;

-- PostgreSQL
SELECT 
    created_at,
    DATE(created_at) AS date_only,
    EXTRACT(YEAR FROM created_at) AS year,
    EXTRACT(MONTH FROM created_at) AS month,
    DATE_TRUNC('month', created_at) AS month_start,
    created_at + INTERVAL '7 days' AS week_later,
    NOW() - created_at AS age
FROM orders;

-- MySQL
SELECT 
    created_at,
    DATE(created_at),
    YEAR(created_at),
    MONTH(created_at),
    DATE_FORMAT(created_at, '%Y-%m'),
    DATE_ADD(created_at, INTERVAL 7 DAY),
    DATEDIFF(NOW(), created_at) AS days_old
FROM orders;

-- CASE expression
SELECT 
    name,
    price,
    CASE 
        WHEN price < 100 THEN 'Budget'
        WHEN price < 500 THEN 'Mid-range'
        WHEN price < 1500 THEN 'Premium'
        ELSE 'Luxury'
    END AS price_tier,
    CASE status
        WHEN 'pending' THEN 'Awaiting Processing'
        WHEN 'shipped' THEN 'On the Way'
        WHEN 'delivered' THEN 'Completed'
        ELSE 'Unknown'
    END AS status_label
FROM products;