Basic SQL 1

October 19 · Why databases, then your first query

MaDS Databases & SQL

Real data rarely arrives ready for analysis

Statistics classes often begin with a tidy CSV. In practice, someone must:

• extract records from several sources,
• transform them into consistent data,
• load and maintain a system others can reuse.

That end-to-end system is the data pipeline.

Adapted from Alex Reinhart, The Data Pipeline.

Our weather data will not stand still

• Observations are revised and hurricane tracks grow.
• Agencies publish on different schedules and in different formats.
• People and programs need the same current records.

A folder of copied CSV files cannot reliably be the shared source of truth.

A database keeps one managed version

A relational database gives us:

• defined tables, columns, and rules,
• one canonical version that can be updated,
• simultaneous access from many clients,
• SQL for requesting the records we need.

Adapted from Reinhart, Database Fundamentals.

Your laptop is the client

You set up

• Visual Studio Code
• Microsoft’s PostgreSQL extension

Your instructor provides

• server and database address
• username and initial password
• account access

Open the setup primer: VS Code + PostgreSQL + Azure

One question will organize today

Which Pennsylvania weather stations sit at the highest elevations—and which records are not usable yet?

We will build the answer one clause at a time.

Course source and adaptation

This course follows and adapts Alex Reinhart’s MADS Computing course. Today’s concepts follow his SQL Basics chapter; the tooling, database, examples, and checkpoints have been updated for Fall 2026.

By the end of class

You should be able to:

• select specific columns from one table,
• rename output columns with aliases,
• filter rows with WHERE,
• handle missing values deliberately,
• sort and limit results,
• translate a plain-language question into a working query.

Start with the table

Our first table is stations:

Column	Meaning	Example
station_id	stable station identifier	USW00094823
name	station name	PITTSBURGH ASOS
state	two-letter state code	PA
elevation	meters above sea level	366.7
lat, lon	station coordinates	40.48, -80.21

Every query makes two choices

SELECT station_id, name, state, elevation
FROM stations;

• FROM identifies the table.
• SELECT identifies the columns or expressions returned.
• The semicolon ends the statement.

SELECT * is useful—but temporary

SELECT *
FROM stations;

* means every column.

Good for a first look. Less good for a saved analysis because:

• the result may be wide,
• the schema may change,
• readers cannot see which fields matter.

Checkpoint 1: control the output

Run the query. Then change it so the result contains only name, state, and elevation, in that order.

SELECT *
FROM stations
LIMIT 8;

Before running your revision, predict what the first column will be.

Checkpoint 1: one valid solution

SELECT name, state, elevation
FROM stations
LIMIT 8;

The order of expressions in SELECT controls the order of columns in the result.

Different row order is possible unless a query includes ORDER BY.

Aliases make results readable

SELECT
  station_id,
  name AS station_name,
  elevation AS elevation_m
FROM stations
LIMIT 8;

An alias changes the output label. It does not rename the stored column.

Expressions create columns

SELECT
  name,
  elevation AS elevation_m,
  ROUND(elevation * 3.28084) AS elevation_ft
FROM stations
LIMIT 8;

SQL evaluates the expression for each selected row.

WHERE decides which rows survive

SELECT station_id, name, state, elevation
FROM stations
WHERE state = 'PA';

• Text values use single quotes.
• = compares values; it does not assign a value.
• Rows where the condition is not true are removed.

Conditions can be combined

Intent	SQL
equal	state = 'PA'
not equal	state <> 'PA'
compare numbers	elevation >= 500
both conditions	... AND ...
either condition	... OR ...
one of several values	state IN ('PA', 'OH', 'WV')

Use parentheses when AND and OR appear together.

Checkpoint 2: translate

Return the ID, name, and elevation of stations in Pennsylvania with elevation at least 500 meters.

Start here:

SELECT
FROM stations
WHERE ;

Compare with a neighbor before running it.

Checkpoint 2: one valid solution

SELECT station_id, name, elevation
FROM stations
WHERE state = 'PA'
  AND elevation >= 500;

Read it in plain English: choose these columns, from stations, but keep only rows satisfying both conditions.

Use DISTINCT to remove repeats

SELECT DISTINCT state
FROM stations;

Without DISTINCT, one state code appears once for every station in that state.

DISTINCT applies to the complete selected row:

SELECT DISTINCT state, elevation
FROM stations;

Missing is not the same as zero

NULL means a value is missing or unknown.

SELECT station_id, name, elevation
FROM stations
WHERE elevation IS NULL;

Do not write elevation = NULL.

Filter to usable elevation records

SELECT station_id, name, elevation
FROM stations
WHERE state = 'PA'
  AND elevation IS NOT NULL;

This is a data-quality decision, not merely syntax.

The query now says which records count as usable for this question.

ORDER BY makes rank meaningful

SELECT station_id, name, elevation
FROM stations
WHERE state = 'PA'
  AND elevation IS NOT NULL
ORDER BY elevation DESC;

• DESC: largest to smallest
• ASC: smallest to largest; this is the default

Multiple sort columns break ties

SELECT station_id, name, elevation
FROM stations
WHERE state = 'PA'
  AND elevation IS NOT NULL
ORDER BY elevation DESC, name ASC;

First sort by elevation. If elevations tie, sort those rows by name.

LIMIT is applied after sorting

SELECT station_id, name, elevation
FROM stations
WHERE state = 'PA'
  AND elevation IS NOT NULL
ORDER BY elevation DESC
LIMIT 10;

Without ORDER BY, “the first 10” has no analytical meaning.

Checkpoint 3: repair the query

This query is supposed to return the ten highest-elevation Pennsylvania stations. Find and repair every problem.

SELECT station_id, name, elevation
FROM stations
WHERE state = "PA" AND elevation = NULL
LIMIT 10
ORDER BY elevation;

Checkpoint 3: repaired

SELECT station_id, name, elevation
FROM stations
WHERE state = 'PA'
  AND elevation IS NOT NULL
ORDER BY elevation DESC
LIMIT 10;

Correct clause order: SELECT → FROM → WHERE → ORDER BY → LIMIT.

Types control valid operations

PostgreSQL columns have types:

• text: name, state
• numeric: elevation, lat, lon
• dates and timestamps: observation and storm times
• Boolean: true/false flags

Types prevent nonsensical operations and determine which functions are available.

Functions transform values

SELECT
  UPPER(name) AS station_name,
  ROUND(elevation * 3.28084) AS elevation_ft
FROM stations
WHERE elevation IS NOT NULL
LIMIT 10;

Functions can appear anywhere SQL expects a value or expression.

The opening question, answered

SELECT
  station_id,
  name AS station_name,
  elevation AS elevation_m,
  ROUND(elevation * 3.28084) AS elevation_ft
FROM stations
WHERE state = 'PA'
  AND elevation IS NOT NULL
ORDER BY elevation DESC, station_name ASC
LIMIT 10;

We can now inspect the highest stations—and explain exactly which records were excluded.

Project transfer

Choose one project table and ask a question one table can answer.

Use:

• named columns,
• two filter conditions,
• an explicit sort and meaningful LIMIT.

Homework starts here

Save today’s transfer query. For homework:

1. revise it so the output answers a real project question,
2. add a comment stating what one row represents,
3. explain one way the result could mislead a client,
4. submit the query and a small result sample.

What you wrote in class is the first draft—not disposable practice.

Submit one file

Your SQL file should contain:

-- Checkpoint 1

-- Checkpoint 2

-- Checkpoint 3

-- Project transfer

Participation credit requires a genuine attempt at at least two checkpoints.

The pattern to keep

question
  → one-row meaning
  → SELECT what
  → FROM where
  → WHERE which rows
  → ORDER BY how
  → LIMIT how many
  → interpret carefully

Next: summarize many rows with aggregate functions and GROUP BY.