Module 8 — SELECT

1. SELECT — Reading Data

What it is: SELECT is a DML command that retrieves data from one or more tables. It's the most frequently used SQL command — every time your app displays data, it's running a SELECT query behind the scenes.

Why we use it: Applications need to read data — user profiles, product listings, reports, dashboards, search results. SELECT is the tool that makes this possible.

When we use it: Every time data needs to be displayed, exported, analyzed, or processed. It's used in APIs, dashboards, reports, and data analysis.

Select all columns

-- The "all columns" shortcut
SELECT * FROM users;

Select specific columns (Best Practice)

SELECT username, email FROM users;

2. Column Aliases (AS)

What it is: Column aliases rename the output columns in your query results using the AS keyword. The rename is temporary — it only affects the query output, not the actual table structure.

Why we use it: Aliases make output more readable (user_handle instead of username), provide meaningful names for computed columns (price_in_dollars instead of ?column?), and match API response field names.

When we use it: Every time you want cleaner output, when using computed columns, or when building API responses that need specific field names.

SELECT
    username AS user_handle,
    email AS contact_info
FROM users;

You can alias computed columns too:

SELECT
    name,
    price / 100 AS price_in_dollars
FROM products;

3. Expressions in SELECT

What it is: SQL expressions let you transform data as it's being retrieved — arithmetic, string operations, function calls, and more. The transformation happens in the database, not in your application code.

Why we use it: Transforming data at the database level is more efficient than fetching raw data and transforming it in application code. It also ensures consistent formatting across all consumers of the data.

When we use it: When displaying formatted data (currency, dates), combining fields (full names), or computing derived values (totals, averages).

Arithmetic

What it is: SQL supports standard arithmetic operators (+, -, *, /) on numeric columns. You can perform calculations directly in your SELECT statement.

Why we use it: To display computed values without storing them — converting cents to dollars, calculating discounts, computing totals, etc.

When we use it: When displaying prices, calculating metrics, or deriving new values from existing data.

-- Convert cents to dollars
SELECT name, price / 100.0 AS price_in_dollars
FROM products;

String concatenation

What it is: The || operator concatenates (joins) strings together. You can combine multiple columns or add literal text between them.

Why we use it: To create display-friendly values — full names from first/last, formatted addresses, labeled values, etc.

When we use it: When building display strings, creating search-friendly text, or formatting data for reports.

-- Combine first and last names
SELECT first_name || ' ' || last_name AS full_name
FROM users;

Conditional expressions

What it is: The CASE expression (covered in detail in Module 21) lets you add if/else logic to your SELECT statement. It evaluates conditions and returns different values based on the result.

Why we use it: To display human-readable labels, categorize data, or handle different display logic without modifying the underlying data.

When we use it: When displaying status labels, categorizing data, or applying conditional formatting to output.

-- Label prices
SELECT
    name,
    CASE
        WHEN price > 1000 THEN 'Premium'
        ELSE 'Affordable'
    END AS price_label
FROM products;

4. DISTINCT — Unique Values

What it is: DISTINCT removes duplicate rows from the query result, returning only unique combinations of values. It operates on the entire row (all selected columns), not just one column.

Why we use it: Without DISTINCT, queries can return duplicate rows — especially when joining tables or selecting from columns with repeated values. DISTINCT ensures you see each unique value only once.

When we use it: When listing unique categories, finding all distinct authors, counting unique values, or cleaning up results from joins that produce duplicates.

-- See every unique category once
SELECT DISTINCT category FROM products;
 
-- Count unique categories
SELECT COUNT(DISTINCT category) FROM products;
 
-- Unique combinations
SELECT DISTINCT category, brand FROM products;

5. Constants and Literals

What it is: You can include constant (literal) values in your SELECT statement — strings, numbers, booleans. These appear as the same value in every row of the result.

Why we use it: To add context to results (like a "source" column), provide default labels, or create union-compatible queries that need the same number of columns.

When we use it: When adding metadata columns, creating labeled datasets, or building union queries.

-- Add a constant column
SELECT username, 'active' AS status FROM users;
 
-- Add a computed label
SELECT name, price, '$' AS currency FROM products;

Common Mistakes to Avoid

1. Using SELECT * in production — Always specify columns for performance and stability
2. Forgetting aliases on computed columns — Without AS, the column name is something like ?column?
3. Not using DISTINCT when needed — Duplicate rows can skew reports and counts

Practice Exercise

Using your courses table:

1. Select all columns from courses
2. Select only title and price
3. Write a query that returns the course title and the price divided by 100, renamed as price_in_rupees
4. Use string concatenation to create a label like "SQL Basics by John Doe" (aliased as course_label)
5. Find all unique instructor names using DISTINCT