Filtering Data with WHERE

-- Create expense categories table
CREATE TABLE categories (
    category_id INTEGER PRIMARY KEY,
    category_name TEXT NOT NULL,
    budget_limit DECIMAL(10,2),
    category_type TEXT CHECK(category_type IN ('Essential', 'Discretionary'))
);

-- Create employees table
CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    department TEXT,
    email TEXT UNIQUE,
    monthly_budget DECIMAL(10,2) DEFAULT 1000.00
);

-- Create expenses table
CREATE TABLE expenses (
    expense_id INTEGER PRIMARY KEY,
    employee_id INTEGER,
    category_id INTEGER,
    expense_date DATE NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    description TEXT,
    vendor TEXT,
    payment_method TEXT CHECK(payment_method IN ('Credit Card', 'Cash', 'Bank Transfer', 'Company Card')),
    status TEXT DEFAULT 'Pending' CHECK(status IN ('Pending', 'Approved', 'Rejected', 'Reimbursed')),
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id),
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

-- Insert expense categories
INSERT INTO categories (category_name, budget_limit, category_type) VALUES
('Travel', 2000.00, 'Discretionary'),
('Meals', 500.00, 'Discretionary'),
('Office Supplies', 300.00, 'Essential'),
('Software', 1000.00, 'Essential'),
('Training', 1500.00, 'Discretionary'),
('Transportation', 400.00, 'Essential'),
('Entertainment', 200.00, 'Discretionary');

-- Insert employees
INSERT INTO employees (first_name, last_name, department, email) VALUES
('Sarah', 'Johnson', 'Marketing', 'sarah.j@company.com'),
('Mike', 'Chen', 'IT', 'mike.c@company.com'),
('Emily', 'Davis', 'Sales', 'emily.d@company.com'),
('David', 'Wilson', 'Finance', 'david.w@company.com'),
('Lisa', 'Anderson', 'HR', 'lisa.a@company.com');

-- Insert sample expenses
INSERT INTO expenses (employee_id, category_id, expense_date, amount, description, vendor, payment_method, status) VALUES
(1, 1, '2024-01-15', 450.00, 'Client meeting in NYC', 'Delta Airlines', 'Company Card', 'Approved'),
(1, 2, '2024-01-15', 85.50, 'Business lunch with client', 'The Capital Grille', 'Company Card', 'Approved'),
(2, 4, '2024-01-10', 129.99, 'IntelliJ IDEA license', 'JetBrains', 'Credit Card', 'Reimbursed'),
(3, 1, '2024-01-20', 325.00, 'Sales conference travel', 'Southwest Airlines', 'Company Card', 'Approved'),
(2, 3, '2024-01-12', 67.45, 'Ergonomic keyboard', 'Amazon', 'Credit Card', 'Pending'),
(4, 3, '2024-01-08', 234.00, 'Office furniture', 'Office Depot', 'Company Card', 'Approved'),
(5, 5, '2024-01-22', 899.00, 'HR certification course', 'SHRM', 'Bank Transfer', 'Approved'),
(3, 2, '2024-01-18', 156.75, 'Team dinner', 'Olive Garden', 'Cash', 'Pending'),
(1, 6, '2024-01-16', 45.00, 'Uber to client office', 'Uber', 'Credit Card', 'Reimbursed'),
(2, 4, '2024-01-25', 49.99, 'GitHub subscription', 'GitHub', 'Credit Card', 'Approved');

SELECT column1, column2, ...  -- Choose what to see
FROM table_name              -- Choose where to look
WHERE condition             -- Filter what you find
ORDER BY column            -- Sort your results
LIMIT number;             -- Control how much you see

SELECT * FROM expenses;

-- Find expenses exactly $100
SELECT * FROM expenses WHERE amount = 100.00;

-- Find expenses over $200
SELECT * FROM expenses WHERE amount > 200;

-- Find expenses $100 or less
SELECT * FROM expenses WHERE amount <= 100;

-- Find expenses between $50 and $150
SELECT * FROM expenses WHERE amount BETWEEN 50 AND 150;

-- Find specific payment methods
SELECT * FROM expenses WHERE payment_method IN ('Company Card', 'Credit Card');

-- Exclude certain statuses
SELECT * FROM expenses WHERE status NOT IN ('Rejected', 'Pending');

-- Your query here!

-- Your query here!

-- Your query here!

-- Find large approved travel expenses
SELECT * FROM expenses
WHERE category_id = 1  -- Travel category
  AND amount > 300
  AND status = 'Approved';

-- Find IT department software expenses
SELECT e.first_name, e.last_name, ex.amount, ex.description
FROM expenses ex
JOIN employees e ON ex.employee_id = e.employee_id
WHERE e.department = 'IT'
  AND ex.category_id = 4  -- Software category
  AND ex.amount > 50;

-- Find expenses paid by company card OR bank transfer
SELECT * FROM expenses
WHERE payment_method = 'Company Card'
   OR payment_method = 'Bank Transfer';

-- Find high-priority expenses (Travel OR Training over $500)
SELECT * FROM expenses
WHERE (category_id = 1 OR category_id = 5)
  AND amount > 500;

-- Find all non-pending expenses
SELECT * FROM expenses
WHERE NOT status = 'Pending';

-- Find expenses NOT in Travel or Entertainment
SELECT * FROM expenses
WHERE NOT (category_id = 1 OR category_id = 7);

-- Build your query here!
SELECT * FROM expenses
WHERE (amount > 200 AND status = 'Pending')
   OR status = 'Rejected';

-- Find all airline expenses
SELECT * FROM expenses
WHERE vendor LIKE '%Airlines%';

-- Find Uber or Lyft rides
SELECT * FROM expenses
WHERE vendor LIKE 'Uber%' OR vendor LIKE 'Lyft%';

-- Find expenses with "client" in description
SELECT * FROM expenses
WHERE description LIKE '%client%';

-- Find Amazon purchases
SELECT * FROM expenses
WHERE vendor LIKE 'Amazon%';

-- Find employees with company email
SELECT * FROM employees
WHERE email LIKE '%@company.com';

-- Find 3-letter department codes
SELECT * FROM employees
WHERE department LIKE '___';

-- Find suspiciously round amounts (like $100.00, $200.00)
SELECT * FROM expenses
WHERE CAST(amount AS TEXT) LIKE '%.00';

-- Find weekend expenses (potential personal use)
SELECT * FROM expenses
WHERE strftime('%w', expense_date) IN ('0', '6');  -- 0=Sunday, 6=Saturday

-- Find expenses with generic descriptions
SELECT * FROM expenses
WHERE description LIKE '%misc%'
   OR description LIKE '%other%'
   OR description LIKE '%various%';

-- Find expenses at restaurants (hint: look for "Grill", "Cafe", "Restaurant", etc.)
-- Your query here!

-- Find expenses missing descriptions
SELECT * FROM expenses
WHERE description IS NULL;

-- Find expenses WITH descriptions
SELECT * FROM expenses
WHERE description IS NOT NULL;

-- ⚠️ Common Mistake - These DON'T work!
SELECT * FROM expenses WHERE description = NULL;     -- Wrong!
SELECT * FROM expenses WHERE description != NULL;    -- Wrong!

-- Show "No description" for NULL descriptions
SELECT expense_id,
       COALESCE(description, 'No description provided') as description,
       amount
FROM expenses;

-- Calculate totals, treating NULL as 0
SELECT employee_id,
       SUM(COALESCE(amount, 0)) as total_spent
FROM expenses
GROUP BY employee_id;

-- Find incomplete expense records
SELECT * FROM expenses
WHERE description IS NULL
   OR vendor IS NULL;

-- Your query here!

-- Comparison Operators
WHERE amount = 100.00              -- Exact match
WHERE amount != 50.00              -- Not equal
WHERE amount > 200                 -- Greater than
WHERE amount >= 100                -- Greater than or equal
WHERE amount < 500                 -- Less than
WHERE amount <= 300                -- Less than or equal

-- Range and List Operators
WHERE amount BETWEEN 100 AND 500   -- Range (inclusive)
WHERE status IN ('Pending', 'Approved')  -- List match
WHERE status NOT IN ('Rejected')   -- Exclude from list

-- Pattern Matching
WHERE vendor LIKE 'Amazon%'        -- Starts with
WHERE vendor LIKE '%Airlines%'     -- Contains
WHERE vendor LIKE '%Store'         -- Ends with
WHERE phone LIKE '555-____'        -- Pattern with length

-- NULL Handling
WHERE description IS NULL          -- Missing values
WHERE description IS NOT NULL      -- Has values
WHERE COALESCE(vendor, '') != ''   -- Handle NULLs

-- Combining Conditions
WHERE amount > 100 AND status = 'Pending'        -- Both must be true
WHERE category = 'Travel' OR category = 'Meals'  -- Either can be true
WHERE NOT status = 'Rejected'                    -- Negation

-- Complex Logic with Parentheses
WHERE (amount > 500 AND status = 'Pending')
   OR (amount > 1000 AND status = 'Approved');

-- Mixed Logic
WHERE employee_id IN (1, 2, 3)
  AND expense_date >= '2024-01-01'
  AND (amount > 200 OR category = 'Travel');

-- Date Comparisons
WHERE expense_date = '2024-01-15'           -- Specific date
WHERE expense_date >= DATE('now', '-30 days')  -- Last 30 days
WHERE expense_date BETWEEN '2024-01-01' AND '2024-01-31'  -- Date range

-- Date Functions
WHERE strftime('%Y', expense_date) = '2024'     -- Specific year
WHERE strftime('%w', expense_date) IN ('0', '6')  -- Weekends
WHERE DATE(expense_date) = CURRENT_DATE         -- Today only