Surfalytics
SQL course overview

Basics of selection I · Topic 3 Free

Using functions

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.

Functions take a value, transform it, and return the result. You call them inside SELECT (and later inside WHERE, ORDER BY — anywhere an expression fits).

Text functions

SELECT UPPER(name)        AS shouting,     -- MARIA PETROVA
       LOWER(country)     AS quiet,        -- canada
       LENGTH(name)       AS chars,        -- 13
       TRIM('  hi  ')     AS trimmed       -- 'hi'
FROM students;

Other useful ones: SUBSTRING(text FROM 1 FOR 3) cuts a piece, REPLACE(text, 'a', 'b') swaps characters, INITCAP('hello world') → Hello World.

Number functions

SELECT ROUND(4.7)        AS rounded,   -- 5
       ROUND(4.678, 2)   AS two_dp,    -- 4.68
       CEIL(4.1)         AS up,        -- 5
       FLOOR(4.9)        AS down,      -- 4
       ABS(-10)          AS positive;  -- 10

Date functions

Dates are everywhere in analytics. Two you will use constantly:

SELECT CURRENT_DATE;                        -- today

SELECT name,
       joined_at,
       EXTRACT(YEAR FROM joined_at)  AS join_year,
       EXTRACT(MONTH FROM joined_at) AS join_month
FROM students;

AGE(date1, date2) returns the interval between two dates, and date1 - date2 on two dates gives the number of days.

Functions compose

The output of one function can feed another:

SELECT UPPER(SUBSTRING(name FROM 1 FOR 1)) AS first_initial
FROM students;

Read nested calls from the inside out.

Practice

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

Sign in to track progress
Previous Literals and expressions Next Removing duplicates: DISTINCT