Aliases, DISTINCT, and NULL Handling

-- Column alias: rename output columns
SELECT
  first_name AS name,
  email AS contact_email,
  created_at AS registered_on
FROM users;

-- Table alias: shorter names for JOINs (you will use this a lot)
SELECT u.first_name, o.order_id, o.total_amount
FROM users u
JOIN orders o ON u.id = o.user_id;

-- Calculated column with alias
SELECT
  first_name,
  last_name,
  CONCAT(first_name, ' ', last_name) AS full_name
FROM users;

-- What statuses exist in the orders table?
SELECT DISTINCT status FROM orders;

-- How many unique cities do users come from?
SELECT COUNT(DISTINCT city) AS unique_cities FROM users;

-- QA check: Are there duplicate emails? (should be 0 if UNIQUE constraint exists)
SELECT email, COUNT(*) AS occurrences
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- Common NULL trap: This finds NOTHING even if nulls exist
SELECT * FROM users WHERE phone = NULL;         -- WRONG!
SELECT * FROM users WHERE phone IS NULL;         -- CORRECT!

-- COALESCE: Replace NULL with a default value
SELECT
  first_name,
  COALESCE(phone, 'No phone') AS phone,
  COALESCE(last_login, 'Never') AS last_login
FROM users;

-- IFNULL (MySQL) / COALESCE (standard) in calculations
SELECT
  product_name,
  price,
  COALESCE(discount, 0) AS discount,
  price - COALESCE(discount, 0) AS final_price
FROM products;

-- QA check: Find columns with unexpected NULLs
SELECT
  COUNT(*) AS total_rows,
  COUNT(phone) AS rows_with_phone,
  COUNT(*) - COUNT(phone) AS rows_without_phone
FROM users;