Basics of selection I · Topic 2 Free
Literals and expressions
Data model — the tables you will query
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.
A query does not have to return raw column values. You can compute things on the way out.
Literals
A literal is a fixed value written directly in the query:
- Numbers:
42,3.14 - Text (always single quotes):
'hello','Data Analyst' - Booleans:
true,false
You can select literals without any table at all — handy as a calculator and for testing:
SELECT 2 + 2 AS answer, 'hello' AS greeting;Note: double quotes in SQL mean an identifier (a column or table name), not text. 'Vancouver' is a string; "Vancouver" would be a column named Vancouver.
Arithmetic
Standard operators work on numeric columns: +, -, *, /, % (remainder).
SELECT title,
hours,
hours * 60 AS minutes
FROM courses;One trap: dividing two integers in Postgres gives an integer. 5 / 2 is 2, not 2.5. To get a decimal, make one side a decimal: 5 / 2.0.
String concatenation
The || operator glues text together:
SELECT name || ' from ' || country AS intro
FROM students;If any part is NULL, the whole result is NULL. (More about NULL in a later topic.)
Always alias computed columns
Without an alias, a computed column gets a meaningless name like ?column?. Give every expression a clear name with AS — your future self and your teammates will thank you.
Practice
A real Postgres database runs in your browser. Solve the tasks, or just experiment.