SQL: Relational Data & SELECT

Chapter roadmap

Understand tables, rows, columns, keys, and types, then retrieve and shape data with clear SELECT statements. Work through the concepts in order, type every example yourself, and keep a short debugging log that records predictions, errors, fixes, and what each fix taught you.

Learning objectives

Explain the relational model
Identify primary and foreign keys
Select columns and expressions
Use aliases and remove duplicates

01.1

Tables, rows, columns, and keys

A relational database stores facts in tables. Each row represents one record and each column represents one attribute with a declared type. A primary key uniquely identifies a row; a foreign key refers to a row in another table and records a relationship.

Before running the example, trace each expression and write down the expected state or result. Then run it, compare the actual result, and change one input or rule to confirm that you understand which part controls the behavior.

Example

CREATE TABLE students (
    student_id INTEGER PRIMARY KEY,
    full_name  VARCHAR(100) NOT NULL,
    grade_level INTEGER,
    active BOOLEAN DEFAULT TRUE
);

Result

A students table with a unique student_id and required full_name.

Check your understanding

Explain the example in plain language, identify one boundary or failure case, and revise it so the output changes in a predictable way.

01.2

Basic SELECT and projection

SELECT chooses expressions and FROM chooses their source table. Listing only needed columns makes results easier to understand and can reduce work. SELECT * is useful while exploring but should not be the default in saved queries.

Example

SELECT student_id, full_name, grade_level
FROM students;

Result

student_id | full_name     | grade_level
-----------+---------------+------------
101        | Ana Ruiz      | 11
102        | Marcus Chen   | 12

Check your understanding

Explain the example in plain language, identify one boundary or failure case, and revise it so the output changes in a predictable way.

01.3

Expressions and aliases

A result column can be calculated from stored values. AS assigns a readable name to an expression without changing the table. String, date, and arithmetic functions differ slightly among database systems, so the course uses portable SQL where possible.

Example

SELECT
    product_name,
    unit_price,
    quantity,
    unit_price * quantity AS line_total
FROM order_items;

Result

product_name | unit_price | quantity | line_total
Notebook     | 4.50       | 3        | 13.50

Check your understanding

Explain the example in plain language, identify one boundary or failure case, and revise it so the output changes in a predictable way.

01.4

DISTINCT and result sets

Queries return result sets rather than modifying source data. DISTINCT removes duplicate combinations from selected columns, which is useful for discovering categories but can hide a poor query if used automatically. Always decide whether duplicates carry meaning before removing them.

Example

SELECT DISTINCT city, state_code
FROM customers
ORDER BY state_code, city;

Result

city       | state_code
Albany     | NY
Brooklyn   | NY
Newark     | NJ

Check your understanding

Explain the example in plain language, identify one boundary or failure case, and revise it so the output changes in a predictable way.

Chapter assignments

Complete these in order. Later assignments assume that earlier skills are working. Do not only test the sample values; design tests that challenge boundaries, missing values, incorrect types, empty collections, and other likely failures.

A1.1
Given a school schema, identify entities, candidate primary keys, and relationships, explaining each choice.
Submit readable source code, a brief design note, and evidence from at least three tests including one boundary or invalid case.
A1.2
Write ten SELECT queries that retrieve specific columns and create readable aliases for calculated expressions.
Submit readable source code, a brief design note, and evidence from at least three tests including one boundary or invalid case.
A1.3
Create a products table with sensible types, a primary key, required fields, and a default availability value.
Submit readable source code, a brief design note, and evidence from at least three tests including one boundary or invalid case.
A1.4
Explore a provided table and produce a data dictionary describing every column and possible data-quality concern.
Submit readable source code, a brief design note, and evidence from at least three tests including one boundary or invalid case.

Chapter project

Community Events Database

Community Events Database — Design and create tables for events, venues, organizers, and categories. Insert a small realistic dataset and write a report query that presents event name, date, venue, capacity, and remaining seats with readable aliases.

Required process

Write a short specification listing inputs, outputs, rules, and failure cases.
Break the work into small functions, queries, modules, or classes appropriate to the language.
Build the smallest working version before adding optional features.
Test normal, boundary, empty, and invalid cases and record the results.
Refactor names and duplication, then write a concise user guide.

Key terms

relation
table
row
column
schema
primary key
foreign key
projection
result set