Selection Techniques

Selecting columns/fields

Imagine you work for a regional planning agency and need to pull specific information from your database. The SELECT statement lets you choose exactly which columns you need.

Selection 1: Create a quick reference list of all town names

Scenario: You need a simple list of town names for a mail merge or dropdown menu.

Selection 2: List all counties in the Pacific Northwest

Scenario: You’re preparing a report header that needs to list every county in the region.

Selection 3: Export all town data for a comprehensive audit

Scenario: An auditor needs the complete towns dataset. Using * selects every column.

Selection 4: Pull county names and populations for a funding report

Scenario: Federal funding is allocated by population. You need county names paired with their 2022 population figures.

Aliasing

Aliases make your output more readable and your queries easier to write. They’re especially useful when column names are long or when you’re doing calculations.

Aliasing 1: Create a clean report with user-friendly column headers

Scenario: You’re sharing data with non-technical stakeholders who won’t understand land_area_sq_mi. Show each county’s county as county_name, its population_2022 as population, and its land_area_sq_mi as area_square_miles.

Aliasing 2: Use table aliases to simplify longer queries

Scenario: When writing complex queries with multiple tables, short table aliases save typing and improve readability. Select the county, county_seat, and year_established columns from the counties table.

Unique entries

DISTINCT removes duplicate values, helping you understand the unique categories in your data.

Unique 1: What states are represented in our towns database?

Scenario: Before running state-specific analyses, you need to know which states are in the dataset.

Unique 2: Which counties appear as a secondary county for any town?

Scenario: Some towns span county lines. List each distinct county that shows up in the secondary_county column.

Counting

COUNT helps you understand the size of your data and answer “how many” questions.

Counting 1: How many towns are in our database?

Scenario: A journalist asks how comprehensive your database is. You need a quick count, reported as total_towns.

Counting 2: How many states does our county data cover?

Scenario: You want to verify your data covers both Oregon and Washington (and only those states). Report the count of distinct states as number_of_states.

Counting 3: How many towns lie entirely within a single county?

Scenario: Most towns never cross a county line. Count the towns with no secondary county, reported as towns_single_county.