Filtering with WHERE

-- Products priced between $25 and $100 (inclusive)
SELECT name, price FROM products
WHERE price BETWEEN 25 AND 100
ORDER BY price;

-- Orders placed within a date range
SELECT id, customer_id, total_amount, order_date FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-06-30'
ORDER BY order_date;

-- Employees with salaries in a band
SELECT first_name, last_name, salary FROM employees
WHERE salary BETWEEN 50000 AND 80000
ORDER BY salary;

-- NOT BETWEEN — outside the range
SELECT name, price FROM products
WHERE price NOT BETWEEN 25 AND 100
ORDER BY price;

-- Customers from specific states
SELECT first_name, last_name, state FROM customers
WHERE state IN ('CA', 'NY', 'TX')
ORDER BY state, last_name;

-- Products in certain categories
SELECT name, category, price FROM products
WHERE category IN ('Electronics', 'Accessories')
ORDER BY category, price;

-- Orders with specific statuses
SELECT id, total_amount, status FROM orders
WHERE status IN ('pending', 'processing')
ORDER BY status;

-- NOT IN — exclude certain categories
SELECT name, category FROM products
WHERE category NOT IN ('Furniture', 'Electronics')
ORDER BY category;

-- Products starting with 'Wire'
SELECT name, price FROM products
WHERE name LIKE 'Wire%';

-- Products with 'desk' anywhere in the name (case-insensitive)
SELECT name, price FROM products
WHERE name ILIKE '%desk%';

-- Customers whose email ends with '@example.com'
SELECT first_name, last_name, email FROM customers
WHERE email LIKE '%@example.com';

-- Customers whose last name is exactly 5 characters
SELECT first_name, last_name FROM customers
WHERE last_name LIKE '_____';

-- Employees in departments starting with 'En'
SELECT first_name, department FROM employees
WHERE department LIKE 'En%';

-- NOT LIKE — names that do NOT start with 'W'
SELECT name FROM products WHERE name NOT LIKE 'W%';

-- Customers who have not provided a phone number
SELECT first_name, last_name, email FROM customers
WHERE phone IS NULL;

-- Orders that have no total amount recorded yet
SELECT id, customer_id, order_date FROM orders
WHERE total_amount IS NULL;

-- Employees who have a salary on record
SELECT first_name, last_name, salary FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC;

-- Products with no category assigned
SELECT id, name FROM products
WHERE category IS NULL;

-- WRONG way to check for NULL — this never returns results
SELECT * FROM customers WHERE phone = NULL;    -- always returns 0 rows
SELECT * FROM customers WHERE phone != NULL;   -- also always 0 rows

-- Electronics products priced under $50 with stock available
SELECT name, price, stock_qty FROM products
WHERE category = 'Electronics'
  AND price < 50
  AND stock_qty > 0;

-- Orders that are either high value or priority
SELECT id, total_amount, status FROM orders
WHERE total_amount > 300
   OR status = 'vip-pending';

-- The parentheses make OR logic clear and correct
-- Without them AND binds first, changing the meaning
SELECT name, price, category FROM products
WHERE (category = 'Electronics' OR category = 'Accessories')
  AND price < 50;

-- Negate an entire expression with NOT
SELECT first_name, state FROM customers
WHERE NOT (state = 'CA' OR state = 'NY')
ORDER BY state;

-- Orders placed today
SELECT id, customer_id, total_amount FROM orders
WHERE order_date = CURRENT_DATE;

-- Orders placed in the last 30 days
SELECT id, customer_id, total_amount, order_date FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY order_date DESC;

-- Employees hired in 2023
SELECT first_name, last_name, hire_date FROM employees
WHERE hire_date BETWEEN '2023-01-01' AND '2023-12-31'
ORDER BY hire_date;

-- Customers who joined this year
SELECT first_name, last_name, joined_date FROM customers
WHERE EXTRACT(YEAR FROM joined_date) = EXTRACT(YEAR FROM CURRENT_DATE)
ORDER BY joined_date;

-- Customers who have placed at least one order
SELECT first_name, last_name, email FROM customers
WHERE id IN (SELECT DISTINCT customer_id FROM orders);

-- Products that have never been ordered
SELECT name, price FROM products
WHERE id NOT IN (SELECT DISTINCT product_id FROM order_items);

-- Employees earning more than the average salary
SELECT first_name, last_name, salary FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;