Surfalytics
SQL course overview

Basics of selection I · Topic 1 Free

Basic SQL query syntax: SELECT

Data model — the tables you will query
N 1 N 1 N 1 N 1 cohorts id PK INT name TEXT start_date DATE format TEXT students id PK INT name TEXT city TEXT country TEXT cohort_id FK INT background TEXT joined_at DATE courses id PK INT title TEXT category TEXT difficulty TEXT hours INT enrollments id PK INT student_id FK INT course_id FK INT enrolled_at DATE completed_at DATE score INT job_offers id PK INT student_id FK INT company TEXT role TEXT city TEXT salary_usd INT remote BOOLEAN offer_date DATE

How the tables join

Relationship Cardinality Join condition
students.cohort_id → cohorts.id many students → one cohort JOIN cohorts ON students.cohort_id = cohorts.id
enrollments.student_id → students.id many enrollments → one student JOIN students ON enrollments.student_id = students.id
enrollments.course_id → courses.id many enrollments → one course JOIN courses ON enrollments.course_id = courses.id
job_offers.student_id → students.id many job offers → one student JOIN students ON job_offers.student_id = students.id

In short: each student belongs to one cohort. Students enroll in courses through enrollments, and their job offers land in job_offers.

Every report, dashboard, and data pipeline starts with the same statement: SELECT. It reads data from a table and returns it as rows and columns.

The shape of a query

SELECT column1, column2
FROM table_name;
  • SELECT lists the columns you want.
  • FROM names the table to read from.
  • The semicolon ; ends the statement.

For example, to get every student’s name and country:

SELECT name, country
FROM students;

Select everything: *

The star means “all columns”. It is great for exploring a table you don’t know yet:

SELECT * FROM courses;

In production code, prefer naming the columns. SELECT * breaks when the table changes and reads more data than you need.

Rename columns: aliases with AS

An alias gives a column a different name in the result. This matters for reports and for calculated columns (you will see those in the next topic):

SELECT title, hours AS duration_hours
FROM courses;

The keyword AS is optional in Postgres (hours duration_hours also works), but writing it makes queries easier to read.

A few rules to remember

  • SQL keywords are not case-sensitive: select and SELECT are the same. The common style is UPPERCASE keywords, lowercase table and column names.
  • Whitespace and line breaks don’t matter. Format for readability.
  • Column order in the result follows the order in your SELECT list.

Now try it yourself in the playground below. You can always press Schema to see the tables, and Reset database if you break something — this database is yours alone.

Practice

A real Postgres database runs in your browser. Solve the tasks, or just experiment.

Sign in to track progress
Next Literals and expressions