SQL TUTORIALS

Answer Key

1. Query used:
   WITH columns_trimmed AS (
   SELECT customer_id, TRIM(first_name) AS first_name, TRIM(last_name) AS last_name,
      TRIM(street) AS street, TRIM(city) AS city, TRIM(state) AS state,
      TRIM(zip) AS zip
   FROM customers)

SELECT *
FROM columns_trimmed
WHERE customer_id > 1010
2. Query used:
   UPDATE orders.customers SET first_name = INITCAP(first_name), last_name = INITCAP(last_name), city = INITCAP(city), state = UPPER(state);
3. Query used:
   UPDATE orders.order_items SET item_name =
    CASE WHEN item_name LIKE '%Mens%' THEN REPLACE(item_name, 'Mens', 'Unisex')
    WHEN item_name LIKE '%Womens%' THEN REPALCE(item_name, 'Womens', 'Unisex')
    ELSE item_name
    END;
4. Query used:
   SELECT *
FROM orders.customers
WHERE SUBSTRING(first_name, 1, 3) = 'Sam' OR state IN('UT', 'CO');
5. Query used:
   ALTER TABLE orders.customers ADD COLUMN email VARCHAR(50);

UPDATE orders.customers SET email = LEFT(first_name, 4) || RIGHT(last_name, 3) || '@gmail.com';