02-filtering-with-where.sql

-- ============================================================
-- SQL Masterclass — Chapter 02: Filtering with WHERE
-- ============================================================
-- 🟢 BEGINNER
--
-- In this chapter you will learn:
--   • Filtering rows with WHERE
--   • Comparison operators: =, !=, <>, <, >, <=, >=
--   • Logical operators: AND, OR, NOT
--   • IN and NOT IN for lists of values
--   • BETWEEN for ranges
--   • LIKE and wildcards for pattern matching
--   • IS NULL and IS NOT NULL for missing data
-- ============================================================


-- ============================================================
-- 2.1  BASIC EQUALITY FILTER
-- ============================================================

-- Find all orders that have been delivered
SELECT *
FROM orders
WHERE order_status = 'delivered'
LIMIT 10;


-- Find all sellers from the state of São Paulo (SP)
SELECT *
FROM sellers
WHERE seller_state = 'SP'
LIMIT 10;


-- ============================================================
-- 2.2  COMPARISON OPERATORS
-- ============================================================

-- Find expensive items (price > 1000 BRL)
SELECT
    order_id,
    product_id,
    price,
    freight_value
FROM order_items
WHERE price > 1000
LIMIT 10;


-- Find cheap items (price <= 20 BRL)
SELECT
    order_id,
    product_id,
    price
FROM order_items
WHERE price <= 20
LIMIT 10;


-- Find items where freight is more expensive than the product
SELECT
    order_id,
    price,
    freight_value
FROM order_items
WHERE freight_value > price
LIMIT 10;


-- ============================================================
-- 2.3  NOT EQUAL: != or <>
-- ============================================================
-- Both work the same way. <> is the ANSI standard.

-- Find orders that are NOT delivered
SELECT
    order_id,
    order_status
FROM orders
WHERE order_status <> 'delivered'
LIMIT 10;


-- ============================================================
-- 2.4  AND — Multiple conditions (ALL must be true)
-- ============================================================

-- Find delivered orders from customer state RJ (Rio de Janeiro)
SELECT
    o.order_id,
    o.order_status,
    c.customer_state
FROM orders o, customers c
WHERE o.customer_id = c.customer_id
  AND o.order_status = 'delivered'
  AND c.customer_state = 'RJ'
LIMIT 10;


-- ============================================================
-- 2.5  OR — At least ONE condition must be true
-- ============================================================

-- Find orders that are either shipped or delivered
SELECT
    order_id,
    order_status
FROM orders
WHERE order_status = 'shipped'
   OR order_status = 'delivered'
LIMIT 10;


-- ============================================================
-- 2.6  IN — Shorthand for multiple OR conditions
-- ============================================================

-- Same as above but cleaner:
SELECT
    order_id,
    order_status
FROM orders
WHERE order_status IN ('shipped', 'delivered', 'invoiced')
LIMIT 10;


-- NOT IN — exclude specific values
SELECT
    order_id,
    order_status
FROM orders
WHERE order_status NOT IN ('delivered', 'canceled')
LIMIT 10;


-- Find sellers from the top 3 states
SELECT
    seller_id,
    seller_city,
    seller_state
FROM sellers
WHERE seller_state IN ('SP', 'RJ', 'MG')
LIMIT 15;


-- ============================================================
-- 2.7  BETWEEN — Range filtering (inclusive)
-- ============================================================

-- Find items priced between 100 and 500 BRL
SELECT
    order_id,
    product_id,
    price
FROM order_items
WHERE price BETWEEN 100 AND 500
LIMIT 10;

-- Note: BETWEEN is INCLUSIVE on both ends.
-- price BETWEEN 100 AND 500
-- is the same as: price >= 100 AND price <= 500


-- Find orders placed in January 2018
SELECT
    order_id,
    order_purchase_timestamp
FROM orders
WHERE order_purchase_timestamp BETWEEN '2018-01-01' AND '2018-01-31 23:59:59'
LIMIT 10;


-- ============================================================
-- 2.8  LIKE — Pattern matching
-- ============================================================
-- % matches any sequence of characters
-- _ matches exactly one character

-- Find cities starting with 'rio'
SELECT DISTINCT customer_city
FROM customers
WHERE customer_city LIKE 'rio%'
LIMIT 10;


-- Find cities containing 'paulo'
SELECT DISTINCT customer_city
FROM customers
WHERE customer_city LIKE '%paulo%'
LIMIT 10;


-- Find cities with exactly 3 characters
SELECT DISTINCT customer_city
FROM customers
WHERE customer_city LIKE '___'
LIMIT 10;


-- ============================================================
-- 2.9  IS NULL / IS NOT NULL
-- ============================================================
-- NULL means "no value" or "unknown". You CANNOT use = to check for NULL!

-- Find orders that have NOT been delivered yet
SELECT
    order_id,
    order_status,
    order_delivered_customer_date
FROM orders
WHERE order_delivered_customer_date IS NULL
LIMIT 10;


-- Find orders that HAVE been delivered
SELECT
    order_id,
    order_status,
    order_delivered_customer_date
FROM orders
WHERE order_delivered_customer_date IS NOT NULL
LIMIT 10;


-- Find reviews without any comment
SELECT
    review_id,
    review_score,
    review_comment_message
FROM order_reviews
WHERE review_comment_message IS NULL
   OR review_comment_message = ''
LIMIT 10;


-- ============================================================
-- 2.10  COMBINING EVERYTHING
-- ============================================================

-- Find high-value delivered orders placed in 2018 from São Paulo
SELECT
    oi.order_id,
    oi.price,
    oi.freight_value
FROM order_items oi
JOIN orders o ON oi.order_id = o.order_id
JOIN customers c ON o.customer_id = c.customer_id
WHERE oi.price > 500
  AND o.order_status = 'delivered'
  AND c.customer_state = 'SP'
  AND o.order_purchase_timestamp BETWEEN '2018-01-01' AND '2018-12-31 23:59:59'
LIMIT 10;


-- ============================================================
-- EXERCISES
-- ============================================================

-- Exercise 1: Find all orders with status 'canceled'.
--             How many are there? (use COUNT)

-- Exercise 2: Find all products where product_weight_g > 10000
--             (heavier than 10kg). Show product_id and weight.

-- Exercise 3: Find all payments made via 'boleto' (a Brazilian 
--             payment method) with payment_value > 200.

-- Exercise 4: Find all customers from states 'SP', 'RJ', or 'MG'.
--             Count how many there are.

-- Exercise 5: Find all reviews with review_score of 1
--             that also have a review_comment_message (not NULL).

-- Exercise 6: Find all seller cities that start with 'sao'.


-- ============================================================
-- SOLUTIONS
-- ============================================================

-- Exercise 1
SELECT COUNT(*) AS canceled_orders
FROM orders
WHERE order_status = 'canceled';

-- Exercise 2
SELECT product_id, product_weight_g
FROM products
WHERE product_weight_g > 10000
LIMIT 15;

-- Exercise 3
SELECT order_id, payment_type, payment_value
FROM order_payments
WHERE payment_type = 'boleto'
  AND payment_value > 200
LIMIT 15;

-- Exercise 4
SELECT COUNT(*) AS top3_state_customers
FROM customers
WHERE customer_state IN ('SP', 'RJ', 'MG');

-- Exercise 5
SELECT
    review_id,
    review_score,
    review_comment_message
FROM order_reviews
WHERE review_score = 1
  AND review_comment_message IS NOT NULL
  AND review_comment_message != ''
LIMIT 15;

-- Exercise 6
SELECT DISTINCT seller_city
FROM sellers
WHERE seller_city LIKE 'sao%'
LIMIT 15;