---
title: "Basic SQL 1"
subtitle: "October 19 · Why databases, then your first query"
author: "MaDS Databases & SQL"
format:
  revealjs:
    theme: [default, mads-sql-reveal.scss]
    slide-number: c/t
    chalkboard: true
    code-line-numbers: true
    transition: fade
    footer: "Adapted from Alex Reinhart · MADS Computing"
---

## 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**.

::: {.source-line}
Adapted from Alex Reinhart, [The Data Pipeline](https://www.refsmmat.com/courses/msp-computing/data-engineering/data-pipeline.html).
:::

## 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.

::: {.source-line}
Adapted from Reinhart, [Database Fundamentals](https://www.refsmmat.com/courses/msp-computing/data-engineering/database-fundamentals.html).
:::

## Your laptop is the client

::: {.columns}
::: {.column width="48%"}
**You set up**

- Visual Studio Code
- Microsoft's PostgreSQL extension
:::

::: {.column width="48%"}
**Your instructor provides**

- server and database address
- username and initial password
- account access
:::
:::

::: {.setup-link}
[Open the setup primer: VS Code + PostgreSQL + Azure](../../computing-setup.html)
:::

## One question will organize today

::: {.question}
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

::: {.source-note}
This course follows and adapts [Alex Reinhart's MADS Computing course](https://www.refsmmat.com/courses/msp-computing/). Today's concepts follow his [SQL Basics chapter](https://www.refsmmat.com/courses/msp-computing/data-engineering/sql.html); 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

```sql
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

```sql
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

::: {.checkpoint}
Run the query. Then change it so the result contains only `name`, `state`, and `elevation`, in that order.
:::

```sql
SELECT *
FROM stations
LIMIT 8;
```

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

## Checkpoint 1: one valid solution

```sql
SELECT name, state, elevation
FROM stations
LIMIT 8;
```

::: {.answer}
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

```sql
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

```sql
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

```sql
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

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

Start here:

```sql
SELECT
FROM stations
WHERE ;
```

Compare with a neighbor before running it.

## Checkpoint 2: one valid solution

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

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

## Use `DISTINCT` to remove repeats

```sql
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:

```sql
SELECT DISTINCT state, elevation
FROM stations;
```

## Missing is not the same as zero

`NULL` means a value is missing or unknown.

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

Do not write `elevation = NULL`.

## Filter to usable elevation records

```sql
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

```sql
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

```sql
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

```sql
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

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

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

## Checkpoint 3: repaired

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

::: {.answer}
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

```sql
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

```sql
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

::: {.project-prompt}
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:

```sql
-- Checkpoint 1

-- Checkpoint 2

-- Checkpoint 3

-- Project transfer
```

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

## The pattern to keep

```text
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`.