-- Incorrect Formulation
SELECT * FROM orders 
WHERE order_date > '2026-06-03' AND amount > 100 AND status != 'cancelled';

-- Missed: status could be NULL

-- Correct Way: Handle NULL
SELECT * FROM orders 
WHERE order_date > DATE_SUB(CURDATE(), INTERVAL 30 DAY)
  AND amount > 100
  AND (status != 'cancelled' OR status IS NULL);

-- Calculate the total order amount
SELECT 
    product_name,
    price,
    quantity,
    price * quantity AS total
FROM order_items;

-- Calculate the discounted price
SELECT 
    product_name,
    original_price,
    discount_rate,
    original_price * (1 - discount_rate / 100) AS sale_price
FROM products;

+--------------+-------+----------+-------+
| product_name | price | quantity | total |
+--------------+-------+----------+-------+
| iPhone       | 999   |        2 | 1998  |
| MacBook      | 1999  |        1 | 1999  |
+--------------+-------+----------+-------+

-- Check prices between 100 and 500
SELECT * FROM products WHERE price BETWEEN 100 AND 500;

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

-- Query orders within a specific date range
SELECT * FROM orders WHERE order_date BETWEEN '2026-01-01' AND '2026-06-30';

-- Query Orders with a Specific Status
SELECT * FROM orders WHERE status IN ('pending', 'paid', 'shipped');

-- Equivalent to
SELECT * FROM orders WHERE status = 'pending' OR status = 'paid' OR status = 'shipped';

-- Search for employees in a specific department
SELECT * FROM employees WHERE department_id IN (1, 3, 5, 7);

-- % Match any number of characters
SELECT * FROM users WHERE name LIKE 'J%';       -- Starts with J
SELECT * FROM users WHERE name LIKE '%son';     -- Ends with son
SELECT * FROM users WHERE name LIKE '%john%';   -- Contains john

-- _ Match a single character
SELECT * FROM users WHERE phone LIKE '138____1111'; -- Positions 4-7 can be any digit

-- ESCAPE Escape
SELECT * FROM files WHERE name LIKE '%\%%' ESCAPE '\\'; -- Contains %

-- NULL cannot be compared with =
SELECT * FROM users WHERE email = NULL;    -- Wrong, returns null
SELECT * FROM users WHERE email IS NULL;   -- Correct

-- NULL cannot be compared with !=
SELECT * FROM users WHERE email != NULL;   -- Wrong
SELECT * FROM users WHERE email IS NOT NULL; -- Correct

-- Safe equals <=>
SELECT NULL = NULL;    -- NULL (Uncertain)
SELECT NULL <=> NULL;  -- 1 (Determines if equal)

-- AND: Both conditions must be met
SELECT * FROM employees 
WHERE department = 'Engineering' AND salary > 8000;

-- OR: Meets any one of the conditions
SELECT * FROM employees 
WHERE department = 'Sales' OR department = 'Marketing';

-- Combination: Note priority (AND > OR)
SELECT * FROM employees 
WHERE (department = 'Sales' OR department = 'Marketing')
  AND salary > 5000;

-- NOT IN
SELECT * FROM products WHERE category_id NOT IN (1, 2, 3);

-- NOT BETWEEN
SELECT * FROM orders WHERE amount NOT BETWEEN 100 AND 500;

-- NOT LIKE
SELECT * FROM users WHERE email NOT LIKE '%@test.com';

-- NOT EXISTS
SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- Permission system (Bit mask)
-- Read=1, Write=2, Execute=4
CREATE TABLE permissions (
    user_id INT,
    permission TINYINT
);

-- User has Read+Write permissions = 1|2 = 3
INSERT INTO permissions VALUES (1, 3);

-- Check for write permission (3 & 2 = 2, non-zero means "yes")
SELECT * FROM permissions WHERE permission & 2;