What is SELECT?

SELECT column1, column2
FROM table_name;

Select All Columns

SELECT * FROM customers;

Select Specific Columns

SELECT first_name, last_name, email
FROM customers;

-- SLOW: Returns all columns
SELECT * FROM customers WHERE city = 'New York';

-- FAST: Returns only needed columns
SELECT first_name, last_name, email
FROM customers
WHERE city = 'New York';

Expressions in SELECT

SELECT
    name,
    price,
    price * 1.08 AS price_with_tax,
    price * stock AS inventory_value,
    ROUND(price * 0.9, 2) AS discounted
FROM products;

Common Functions

-- String functions
SELECT
    UPPER(first_name) AS upper_name,
    LOWER(last_name) AS lower_name,
    LENGTH(email) AS email_length,
    CONCAT(first_name, ' ', last_name) AS full_name
FROM customers;

-- Math functions
SELECT
    price,
    ROUND(price, 0) AS rounded,
    CEIL(price) AS ceiling,
    FLOOR(price) AS floor_price,
    ABS(-price) AS absolute
FROM products;

-- Date functions
SELECT
    hire_date,
    YEAR(hire_date) AS hire_year,
    MONTH(hire_date) AS hire_month,
    DATEDIFF(CURRENT_DATE, hire_date) AS days_employed
FROM employees;

Column Aliases

SELECT
    first_name AS "First Name",
    last_name AS "Last Name",
    salary AS annual_salary,
    salary * 12 AS monthly_salary
FROM employees;

SELECT Without a Table

-- Perform calculations without any table
SELECT 1 + 1 AS result;                    -- 2
SELECT CURRENT_DATE AS today;              -- 2024-01-15
SELECT 'Hello, World!' AS greeting;        -- Hello, World!
SELECT RANDOM();                           -- Random number

SQL Execution Order

-- What you write:
SELECT first_name, salary
FROM employees
WHERE department = 'Engineering'
ORDER BY salary DESC
LIMIT 5;

-- How it executes:
-- 1. FROM employees        → All employee rows
-- 2. WHERE department='Engineering' → Only engineers
-- 3. SELECT first_name, salary → Just those two columns
-- 4. ORDER BY salary DESC  → Highest salary first
-- 5. LIMIT 5               → Top 5 only

Performance Tips

Common Mistakes

-- BAD: SELECT * in production
SELECT * FROM customers;

-- GOOD: Specific columns
SELECT id, first_name, last_name, email FROM customers;

-- BAD: Missing WHERE clause
SELECT * FROM customers;

-- GOOD: Filter with WHERE
SELECT * FROM customers WHERE is_active = TRUE;

-- BAD: No LIMIT for large tables
SELECT * FROM logs;

-- GOOD: Limit results
SELECT * FROM logs ORDER BY created_at DESC LIMIT 100;

Practice Exercises

Exercise 1: Write a query to select name and price from products, and calculate a 10% discount as discounted_price.

SELECT
    name,
    price,
    price * 0.9 AS discounted_price
FROM products;

Exercise 2: Select all employees with their annual salary (salary * 12).

SELECT
    first_name,
    last_name,
    salary,
    salary * 12 AS annual_salary
FROM employees;

Exercise 3: Select customers with formatted names and email length.

SELECT
    UPPER(first_name) AS first_name,
    UPPER(last_name) AS last_name,
    CONCAT(first_name, ' ', last_name) AS full_name,
    LENGTH(email) AS email_length
FROM customers;