SQL lessons guide

SELECT TRIM('   Hello World   ') AS clean_text;
-- result: 'Hello world'

SELECT company, TRIM(company) AS clean_company
FROM customers;

-- Removing dashes from phone numbers
SELECT REPLACE(business_phone, '-', '') AS clean_phone
FROM customers;

-- replacing UK with United Kingdom
SELECT REPLACE(country_region, 'UK', 'United Kingdom')
FROM customers;

-- first 3 letters of the company name
SELECT company, LEFT(company, 3) AS short_code
FROM customers;

-- last 4 digits of the phone
SELECT RIGHT(business_phone, 4) AS phone_end
FROM customers;

-- 5 characters starting from character 2
SELECT SUBSTRING(product_name, 2, 5) AS middle_text
FROM products;

SELECT email_address,
       LENGTH(email_address) AS email_length,
       INSTR(email_address, '@') AS at_position
FROM employees;

-- full address with commas (skips NULL)
SELECT CONCAT_WS(', ', city, state_province, zip_postal_code) AS full_address
FROM customers;

-- price with a $ sign
SELECT CONCAT('$', ROUND(list_price, 2)) AS formatted_price
FROM products;

SELECT UPPER(company) AS company_caps
FROM suppliers;

SELECT LOWER(email_address) AS standard_email
FROM employees;

-- products priced above 50
SELECT product_name, list_price
FROM products
WHERE list_price > 50;

-- Employees who are not Sales Representative
SELECT first_name, job_title
FROM employees
WHERE job_title != 'Sales Representative';

-- AND: customers from USA and New York
SELECT * FROM customers
WHERE country_region = 'USA'
  AND city = 'New York';

-- OR: products from Beverages or Condiments
SELECT product_name FROM products
WHERE category = 'Beverages'
   OR category = 'Condiments';

-- Suppliers from 3 cities (shorthand for OR)
SELECT company, city
FROM suppliers
WHERE city IN ('Tokyo', 'London', 'New York');

-- NOT IN: not from these cities
SELECT * FROM customers
WHERE country_region NOT IN ('USA', 'UK');

-- Orders with shipping fees between 20 and 50
SELECT id, shipping_fee
FROM orders
WHERE shipping_fee BETWEEN 20 AND 50;

-- emails ending in northwindtraders.com
SELECT * FROM employees
WHERE email_address LIKE '%northwindtraders.com';

-- names that start with A
SELECT first_name FROM employees
WHERE first_name LIKE 'A%';

-- orders not yet shipped
SELECT id, order_date
FROM orders
WHERE shipped_date IS NULL;

-- Customers with a website
SELECT company, web_page
FROM customers
WHERE web_page IS NOT NULL;

-- statistics on the products table
SELECT
  COUNT(*) AS total_products,
  AVG(list_price) AS avg_price,
  MIN(list_price) AS min_price,
  MAX(list_price) AS max_price
FROM products;

-- how many products are in each category?
SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category;

-- average price per category
SELECT category, AVG(list_price) AS avg_price
FROM products
GROUP BY category
ORDER BY avg_price DESC;

-- Categories with more than 5 products
SELECT category, COUNT(*) AS cnt
FROM products
GROUP BY category
HAVING COUNT(*) > 5;

-- WHERE (before) + HAVING (after)
SELECT category, AVG(list_price) AS avg
FROM products
WHERE list_price > 10        -- filter before grouping
GROUP BY category
HAVING AVG(list_price) > 30; -- filter after grouping

-- count by category + total
SELECT
  COALESCE(category, '--- Total ---') AS category,
  COUNT(*) AS total
FROM products
GROUP BY category WITH ROLLUP;

-- a combined list of cities from customers and suppliers
SELECT city, 'customer' AS type FROM customers
UNION
SELECT city, 'supplier' AS type FROM suppliers
ORDER BY city;