Working with Single-Table Data

From Schema to Query

Learning Objectives

You can choose the right table and columns for a simple query.
You can write basic SELECT statements that are clear and readable.
You can move from a schema or table snapshot to a concrete query question.

Structured Query Language (SQL) is a language used to ask a relational database for data and, later, to change stored data. In a relational database, those questions are usually asked from tables. In this chapter, we begin with the simplest kind of SQL question: which values should be returned from one table?

Suppose a database contains a table called courses.

id	code	title
1	CS-A1150	Introduction to Databases
2	CS-C3170	Web Software Development

If we want to list course codes and titles, the needed data clearly lives in courses.

Start with the Question and Read the Schema

Before writing SQL, it helps to do two small things in order:

Ask what question you want to answer.
Read the table or schema to find where that fact is stored.

For example, we might ask:

What courses exist?
What is the code of each course?
What is the title of each course?

Those are question-first ways of thinking. The next step is to read the schema and look for the table and columns that hold the needed fact.

A useful habit is to pause and look for:

which table contains the fact you need,
which columns represent that fact,
and which columns are only identifiers or support fields.

Figure 1 — A familiar schema view helps us decide which single table contains the fact we want.

For example, this habit can guide us like this:

Question: “What course codes and titles exist?” Table: courses. Columns: code, title.
Question: “What exercise titles and deadlines are stored?” Table: exercises. Columns: title, deadline_at.
Question: “What submission times and scores are stored?” Table: submissions. Columns: submitted_at, score.

That small pause often prevents many beginner mistakes. Once the question, table, and columns are clear, the query often becomes much easier to write.

Loading Exercise...

From Table to Question

At this point, it is useful to pause before writing SQL and practice the question-design step directly. If you can propose one clear, one-table question from a table definition, the later SELECT statement often becomes much easier to write.

The exercise below asks you to write one concrete question that can be answered from one table only and to name the columns you would need.

Loading Exercise...

Basic Shape of a Query

Once we know which table and columns we need, SQL gives us a simple way to ask for them.

In SQL, the simplest query pattern is:

SELECT column_name
FROM table_name;

For example:

SELECT title
FROM courses;

This asks the database to return the values from the title column of the courses table. In the result, each row represents one stored course. At this stage, it is enough to read this as: take the data from courses, and show the title column.

A good habit for learning: before writing a query, say out loud what you want from the database. Then, after writing a query, say out loud what the query does. This helps you connect the question, the query, and the result.

If we want several columns, we separate them with commas. For example:

SELECT
  id,
  code,
  title
FROM courses;

This asks the database to return the values from the id, code, and title columns of the courses table.

Formatting habit

At this stage, a good SQL formatting habit is simple:

put major clauses such as SELECT and FROM on separate lines
if several values are selected, put them on separate lines and indent them by two spaces
keep the layout consistent from one query to the next
prefer readable multi-line queries over trying to fit everything onto one line

Loading Exercise...

Specific Columns vs. All Columns

It is common to see SELECT *, which means “all columns”.

SELECT *
FROM courses;

This is convenient when exploring, but in most cases it is better to name the columns you actually need. Named columns make queries easier to read and often easier to maintain.

A page that only shows course codes and titles usually does not need every column from the table.

Common mistake

SELECT * is not automatically wrong, but beginners often keep using it after the exploration stage. When you already know what the page or query needs, naming the columns is usually clearer.

If you want a compact reminder of statement shapes while practicing, the SQL Quick Reference page is a useful companion.

Loading Exercise...

Check Your Understanding

Why is it important to pick the table first before writing the rest of the query?
What is the difference between selecting every column and selecting only the columns you need?
Why is SELECT * useful for exploration but often less clear for teaching and maintenance?

SQL Practice

Complete the SQL sequence below to practice choosing the right table and columns and writing basic SELECT ... FROM ... queries.

Loading Exercise...

← Overview

Filtering with WHERE →