Functions ① — Arithmetic, String Concatenation, and Date Functions

Data used in this article — the staff table

Starting from this article, the next three articles cover functions that are built into SQL. The first one focuses on three categories: arithmetic operations, string concatenation, and date functions — all expressions you'll usually drop into the column list of a SELECT.

The dataset is the staff table (10 rows: id / name / birthday / city / salary), auto-loaded from a CSV. Across four practice exercises, you'll do money math with salary, build labels from name and city, and format dates from birthday.

Before diving into the exercises, let's check the column definitions and sample data of the staff table.

① Run PRAGMA table_info(staff); to see the column names, types, and primary key.

② Run SELECT * FROM staff LIMIT 5; to preview the first 5 rows.

Arithmetic operators — +, -, *, /, %

For calculations on numeric columns, you have the 5 arithmetic operators + - * / %. SQL lets you write expressions directly in the SELECT column list, so you can take a value out of a table and return its calculated result as a separate column. Adding AS alias gives the result column a name, which makes it easier to display or consume in downstream code.

Division / behaves differently across databases: in integer-only divisions, some databases (PostgreSQL / SQL Server) return an integer result, while others (MySQL / this course's console = SQLite) automatically return a decimal.

-- 1) Simple calculations without a table
SELECT 1 + 1;       -- 2
SELECT 10 - 3;      -- 7
SELECT 3 * 4;       -- 12
SELECT 10.0 / 3;    -- 3.333...
SELECT 10 % 3;      -- 1

-- 2) Calculation using a column (annual salary after a 10% raise)
SELECT name, salary, salary * 1.1 AS next_year_salary
FROM staff;

-- 3) Compute monthly salary (annual / 12)
SELECT name, salary / 12 AS monthly_salary FROM staff;

Imagine running a salary simulation: show "the annual salary of every employee if they received a 10% raise." (If you run it correctly, an explanation will appear.)

① From the staff table, pull name, salary, and salary * 1.1 aliased as next_year_salary — 3 columns total.

② Order by next_year_salary descending.

③ Verify that the result has 10 rows and starts with Frank Tanaka 7,200,000 / 7,920,000.

String concatenation — `||` and `CONCAT()`

Joining multiple strings into one is called concatenation. There are two ways to write it: this course's console (SQLite), PostgreSQL, Oracle, and SQL Server use the `||` operator (two pipes), while MySQL uses the `CONCAT()` function. SQLite actually supports both || and CONCAT(), so this article leans on the more portable CONCAT().

By slipping a separator (': ' or ' / ') between values, you can collapse name and city into a single readable column like "Alice Tanaka / Tokyo." It's a workhorse for reports and CSV exports where you want "multiple columns merged into one."

-- 1) The || operator (SQLite / PostgreSQL / Oracle)
SELECT name || ' (' || city || ')' AS label FROM staff;

-- 2) The CONCAT function (MySQL / SQLite 3.40+ / PostgreSQL)
SELECT CONCAT(name, ' (', city, ')') AS label FROM staff;

-- Both return the same result
-- → Alice Tanaka (Tokyo) / Bob Suzuki (Osaka) / ...

`||` vs. `CONCAT()` — which to pick

For portability, `CONCAT()` is the safer bet. It runs on SQLite, MySQL, PostgreSQL, SQL Server (2012+), and Oracle. || is the SQL standard, but in MySQL it defaults to logical OR, so if production might be MySQL, picking CONCAT() means you don't need to rewrite anything.

NULL handling differs too: NULL || 'A' returns NULL with the || operator. CONCAT(NULL, 'A') returns an empty string in MySQL but NULL in PostgreSQL — behavior varies. When NULL might sneak into your inputs, wrap them with COALESCE(column, '') to convert NULL to an empty string before concatenating (COALESCE is covered in the third article of this series).

Imagine building a label for employee cards: "name followed by space and parenthesized city."

① From the staff table, pull the original name and city columns plus `CONCAT(name, ' (', city, ')')` aliased as `label`.

② Verify that the result has 10 rows and the label column contains strings like "Alice Tanaka (Tokyo)" / "Bob Suzuki (Osaka)."

Date functions — current timestamp and formatting

For things like "stamp the current time on a new record" or "pull just the year out of a birthday," you use date functions. Date functions are one of the categories where function names and syntax vary the most across databases — this course's console (SQLite) and MySQL call them quite differently.

This course uses SQLite's datetime('now') / date('now') / strftime('format', value) for the exercises, with MySQL's NOW() / CURDATE() / DATE_FORMAT(value, 'format') shown side-by-side in the comparison table below. The format strings ('%Y' for year, '%m' for month, '%d' for day, etc.) are the same in both databases, so at least the format specifiers transfer directly.

-- How to write it in this course's console (SQLite)

-- 1) Current timestamp / current date
SELECT datetime('now') AS current_dt, date('now') AS current_d;

-- 2) Format specifier (year only / year-month only)
SELECT strftime('%Y', '1990-04-15') AS year_only;       -- '1990'
SELECT strftime('%Y-%m', '1990-04-15') AS year_month;   -- '1990-04'

-- 3) Format applied to a column
SELECT name, strftime('%Y', birthday) AS birth_year FROM staff;

-- Reference: same thing in MySQL
--   SELECT NOW(), CURDATE();
--   SELECT DATE_FORMAT(birthday, '%Y') FROM staff;

Let's pull the current timestamp and current date from this course's console. Running it is enough — no exact-match check.

① Pull datetime('now') as current_dt and date('now') as current_d.

② Verify that the result is 1 row × 2 columns, with current_dt showing the current timestamp in "YYYY-MM-DD HH:MM:SS" format and current_d showing the current date in "YYYY-MM-DD" format.

Imagine an employee list that needs a separate "birth year" column.

① From the staff table, pull name, birthday, and `strftime('%Y', birthday)` aliased as `birth_year` — 3 columns total.

② Verify that the result has 10 rows. For Alice Tanaka, you should see birthday=1990-04-15 and birth_year='1990' — just the year ends up in the new column.

Imagine a request: "show the younger employees (born in the 1990s) in birth order."

① From the staff table, pull name, birthday, and strftime('%Y', birthday) AS birth_year.

② Filter to rows where birth_year is between '1990' and '1999' inclusive (use BETWEEN).

③ Order by birthday ascending (earliest first) and keep only the top 3 rows.

④ Verify that the result is 3 rows: Alice 1990-04-15 / Grace 1991-05-25 / Carol 1992-11-03.