SELECT title, price, stock_quantity
FROM books
WHERE price > 15;

-- Books published in or after 1950
SELECT title, publication_year
FROM books
WHERE publication_year >= 1950;
 
-- Orders that aren't pending
SELECT order_id, status
FROM orders
WHERE status <> 'pending';

-- Authors whose last name comes before 'M' alphabetically
SELECT first_name, last_name
FROM authors
WHERE last_name < 'M';

-- Orders placed in January 2024
SELECT order_id, order_date
FROM orders
WHERE order_date >= '2024-01-01'
  AND order_date < '2024-02-01';

-- Books priced between $14 and $17
SELECT title, price
FROM books
WHERE price BETWEEN 14 AND 17;

WHERE price >= 14 AND price <= 17

-- Books NOT in the $14-17 range
SELECT title, price
FROM books
WHERE price NOT BETWEEN 14 AND 17;

-- Books by authors 1 or 4
SELECT title, author_id
FROM books
WHERE author_id IN (1, 4);

WHERE author_id = 1 OR author_id = 4

-- Orders that aren't pending or cancelled
SELECT order_id, status
FROM orders
WHERE status NOT IN ('pending', 'cancelled');

-- Books with 'Wood' in the title
SELECT title FROM books WHERE title LIKE '%Wood%';

-- Books starting with 'A'
SELECT title FROM books WHERE title LIKE 'A%';

-- Email addresses from email.com domain
SELECT email FROM customers WHERE email LIKE '%@email.com';

-- Starts with 'John'
WHERE name LIKE 'John%'
 
-- Ends with 'son'
WHERE name LIKE '%son'
 
-- Contains 'mark'
WHERE name LIKE '%mark%'
 
-- Second character is 'a'
WHERE name LIKE '_a%'
 
-- Exactly 5 characters
WHERE code LIKE '_____'

-- PostgreSQL case-insensitive search
SELECT title FROM books WHERE title ILIKE '%the%';

-- Find values containing a literal %
SELECT * FROM products WHERE name LIKE '%\%%' ESCAPE '\';

-- This finds nothing, even if birth_date is NULL
SELECT * FROM authors WHERE birth_date = NULL;  -- WRONG!
 
-- Correct way to find NULLs
SELECT * FROM authors WHERE birth_date IS NULL;
 
-- Find non-NULL values
SELECT * FROM authors WHERE birth_date IS NOT NULL;

-- Expensive books with good stock
SELECT title, price, stock_quantity
FROM books
WHERE price > 15 AND stock_quantity > 25;

-- Books that are cheap OR well-stocked
SELECT title, price, stock_quantity
FROM books
WHERE price < 14 OR stock_quantity > 30;

-- Books NOT in the expensive range
SELECT title, price
FROM books
WHERE NOT (price > 16);
 
-- Equivalent to:
WHERE price <= 16

-- What does this return?
SELECT * FROM books
WHERE author_id = 1 OR author_id = 4 AND price > 16;
 
-- AND binds tighter, so it's interpreted as:
WHERE author_id = 1 OR (author_id = 4 AND price > 16)

-- Clear intent with parentheses
SELECT * FROM books
WHERE (author_id = 1 OR author_id = 4) AND price > 16;

-- Find books that meet these criteria:
-- 1. Published after 1950
-- 2. Either by author 1 or 4
-- 3. Priced between $12 and $18
-- 4. With at least 20 in stock
 
SELECT title, author_id, publication_year, price, stock_quantity
FROM books
WHERE publication_year > 1950
  AND author_id IN (1, 4)
  AND price BETWEEN 12 AND 18
  AND stock_quantity >= 20;

-- Books with inventory value over $500
SELECT
    title,
    price,
    stock_quantity,
    price * stock_quantity AS inventory_value
FROM books
WHERE price * stock_quantity > 500;

-- This won't work:
WHERE inventory_value > 500  -- ERROR: unknown column
 
-- Either repeat the expression:
WHERE price * stock_quantity > 500
 
-- Or use a subquery (covered later):
SELECT * FROM (
    SELECT *, price * stock_quantity AS inventory_value FROM books
) AS subq
WHERE inventory_value > 500;

WHERE customer_id = 5           -- Exact match
WHERE price > 100               -- Range on indexed column
WHERE email LIKE 'john%'        -- Prefix match

WHERE UPPER(email) = 'JOHN@EXAMPLE.COM'  -- Function on column
WHERE email LIKE '%@gmail.com'            -- Suffix/contains match
WHERE price + tax > 100                   -- Expression on column

-- Find customers matching a search term
SELECT customer_id, first_name, last_name, email
FROM customers
WHERE first_name LIKE 'A%'
   OR last_name LIKE 'A%'
   OR email LIKE 'a%';

-- Find high-value completed orders from this month
SELECT order_id, customer_id, total_amount, order_date
FROM orders
WHERE status = 'completed'
  AND total_amount >= 30
  AND order_date >= '2024-01-01'
  AND order_date < '2024-02-01'
ORDER BY total_amount DESC;

-- Find books that need reordering
SELECT title, stock_quantity, price
FROM books
WHERE stock_quantity < 25
  AND stock_quantity > 0
ORDER BY stock_quantity ASC;