Calculated Columns - Interview Prep Notes

Calculated (computed) columns are derived values produced by expressions in a SELECT statement. They don’t exist in the table — they’re computed on the fly at query time.

Sample data

CREATE TABLE products (
  id         SERIAL PRIMARY KEY,
  name       VARCHAR(100),
  price      NUMERIC(10, 2),
  cost       NUMERIC(10, 2),
  quantity   INT,
  weight_kg  NUMERIC(6, 2),
  category   VARCHAR(50),
  created_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO products (name, price, cost, quantity, weight_kg, category)
VALUES
  ('Laptop',       999.99, 650.00, 50,  2.10, 'Electronics'),
  ('Keyboard',      79.99,  35.00, 200, 0.80, 'Electronics'),
  ('Desk Chair',   249.99, 120.00, 75,  12.50, 'Furniture'),
  ('Monitor',      399.99, 220.00, 100, 5.40, 'Electronics'),
  ('Mouse',         29.99,  10.00, 500, 0.12, 'Electronics'),
  ('Standing Desk', 549.99, 300.00, 30,  35.00, 'Furniture'),
  ('Headphones',   149.99,  60.00, 150, 0.25, 'Electronics'),
  ('Webcam',        89.99,  40.00, 120, 0.18, 'Electronics');

Arithmetic operators

Operator	Description	Example
`+`	Addition	`price + 10`
`-`	Subtraction	`price - cost`
`*`	Multiplication	`price * quantity`
`/`	Division	`price / 2`
`%`	Modulo (remainder)	`quantity % 3`
`^`	Exponentiation (PG-specific)	`2 ^ 3` → `8`
`\|/`	Square root (PG-specific)	`\|/ 16` → `4`

Basic calculated columns

Profit margin

SELECT
  name,
  price,
  cost,
  price - cost AS profit,
  ROUND((price - cost) / price * 100, 1) AS profit_margin_pct
FROM products;

     name      | price  |  cost  | profit | profit_margin_pct
---------------+--------+--------+--------+-------------------
 Laptop        | 999.99 | 650.00 | 349.99 |              35.0
 Keyboard      |  79.99 |  35.00 |  44.99 |              56.2
 Desk Chair    | 249.99 | 120.00 | 129.99 |              52.0
 Monitor       | 399.99 | 220.00 | 179.99 |              45.0
 Mouse         |  29.99 |  10.00 |  19.99 |              66.7

Inventory value

SELECT
  name,
  price,
  quantity,
  price * quantity AS inventory_value
FROM products
ORDER BY inventory_value DESC;

Tax calculation

SELECT
  name,
  price,
  ROUND(price * 0.18, 2) AS gst,
  ROUND(price * 1.18, 2) AS price_with_gst
FROM products;

Mathematical functions

Function	Description	Example	Result
`ROUND(n, d)`	Round to `d` decimal places	`ROUND(3.14159, 2)`	`3.14`
`CEIL(n)`	Round up to nearest integer	`CEIL(4.2)`	`5`
`FLOOR(n)`	Round down to nearest integer	`FLOOR(4.9)`	`4`
`ABS(n)`	Absolute value	`ABS(-42)`	`42`
`MOD(a, b)`	Modulo (same as `%`)	`MOD(10, 3)`	`1`
`POWER(a, b)`	Raise `a` to the power of `b`	`POWER(2, 3)`	`8`
`SQRT(n)`	Square root	`SQRT(16)`	`4`
`GREATEST(a,b,..)`	Largest value from a list	`GREATEST(10, 20, 5)`	`20`
`LEAST(a,b,..)`	Smallest value from a list	`LEAST(10, 20, 5)`	`5`

SELECT
  name,
  price,
  CEIL(price) AS rounded_up,
  FLOOR(price) AS rounded_down,
  ROUND(price, 0) AS rounded
FROM products;

Type casting

Convert one data type to another using CAST or PostgreSQL’s :: shorthand.

-- CAST syntax (SQL standard)
SELECT CAST(price AS INT) FROM products;

-- :: syntax (PostgreSQL shorthand)
SELECT price::INT FROM products;

-- Numeric to text
SELECT 'Price: ' || price::TEXT FROM products;

-- Text to numeric
SELECT CAST('123.45' AS NUMERIC(10, 2));

-- Date casting
SELECT CAST('2024-01-15' AS DATE);
SELECT '2024-01-15'::DATE;

-- Integer division vs decimal division
SELECT 10 / 3;         -- 3   (integer division)
SELECT 10.0 / 3;       -- 3.333...  (decimal division)
SELECT 10::NUMERIC / 3; -- 3.333...  (cast to force decimal)

◆ Integer division trap

Dividing two integers in PostgreSQL gives an integer result (truncated, not rounded). SELECT 7 / 2 returns 3, not 3.5. Cast at least one operand to NUMERIC or FLOAT to get a decimal result: SELECT 7::NUMERIC / 2 returns 3.5.

CASE — conditional expressions

The CASE expression adds if/else logic directly in SQL queries.

Simple CASE

SELECT
  name,
  price,
  CASE category
    WHEN 'Electronics' THEN 'Tech'
    WHEN 'Furniture' THEN 'Office'
    ELSE 'Other'
  END AS category_label
FROM products;

Searched CASE (with conditions)

SELECT
  name,
  price,
  CASE
    WHEN price > 500 THEN 'Premium'
    WHEN price > 100 THEN 'Mid-Range'
    ELSE 'Budget'
  END AS price_tier
FROM products;

CASE in ORDER BY

-- Custom sort order
SELECT name, category
FROM products
ORDER BY
  CASE category
    WHEN 'Electronics' THEN 1
    WHEN 'Furniture' THEN 2
    ELSE 3
  END;

CASE with aggregates

-- Count products per price tier
SELECT
  COUNT(CASE WHEN price > 500 THEN 1 END) AS premium,
  COUNT(CASE WHEN price BETWEEN 100 AND 500 THEN 1 END) AS mid_range,
  COUNT(CASE WHEN price < 100 THEN 1 END) AS budget
FROM products;

 premium | mid_range | budget
---------+-----------+--------
       2 |         3 |      3

CASE for conditional updates

UPDATE products
SET price = CASE
  WHEN category = 'Electronics' THEN price * 0.9   -- 10% discount
  WHEN category = 'Furniture' THEN price * 0.85     -- 15% discount
  ELSE price
END;

COALESCE, NULLIF, and NULL handling

COALESCE

Returns the first non-NULL value from the argument list.

-- Replace NULL department with 'Unassigned'
SELECT
  first_name,
  COALESCE(department, 'Unassigned') AS department
FROM employees;

-- Chain multiple fallbacks
SELECT COALESCE(phone, mobile, email, 'No contact') AS contact
FROM users;

NULLIF

Returns NULL if two values are equal, otherwise returns the first value. Useful to prevent division by zero.

-- Avoid division by zero
SELECT
  name,
  revenue / NULLIF(cost, 0) AS roi
FROM projects;
-- If cost = 0, NULLIF returns NULL, and revenue / NULL = NULL (no error)

Date/time calculated columns

SELECT
  first_name,
  hire_date,
  CURRENT_DATE - hire_date AS days_employed,
  AGE(hire_date) AS tenure,
  EXTRACT(YEAR FROM hire_date) AS hire_year,
  EXTRACT(MONTH FROM hire_date) AS hire_month,
  DATE_PART('quarter', hire_date) AS hire_quarter,
  TO_CHAR(hire_date, 'DD Mon YYYY') AS formatted_date
FROM employees;

Function	Description	Example
`AGE(date)`	Interval from date to now	`AGE('2020-01-01')` → `5 years 1 mon...`
`EXTRACT(field FROM date)`	Extract part of date	`EXTRACT(YEAR FROM hire_date)`
`DATE_PART('field', date)`	Same as EXTRACT (older syntax)	`DATE_PART('month', hire_date)`
`TO_CHAR(date, fmt)`	Format date as string	`TO_CHAR(NOW(), 'YYYY-MM-DD')`
`NOW()`	Current timestamp	`NOW()`
`CURRENT_DATE`	Current date (no time)	`CURRENT_DATE`

Common interview questions

How do you handle division by zero in SQL?

Use NULLIF(denominator, 0) to convert zero to NULL before dividing. Division by NULL returns NULL instead of raising an error: SELECT revenue / NULLIF(expenses, 0) AS ratio.

What is the difference between COALESCE and IFNULL?

COALESCE is SQL-standard and accepts multiple arguments, returning the first non-NULL value. IFNULL is MySQL-specific and accepts only two arguments. PostgreSQL doesn’t have IFNULL — use COALESCE instead.

Can you use a calculated column in WHERE?

No, not by its alias. The WHERE clause executes before SELECT, so aliases aren’t available. You must repeat the expression: WHERE price * quantity > 10000 — not WHERE inventory_value > 10000. Alternatively, use a subquery or CTE to name the calculation first, then filter on it.

What is a generated/stored computed column in PostgreSQL?

PostgreSQL 12+ supports generated columns — columns whose value is automatically computed from other columns and stored on disk:

CREATE TABLE products (
  price  NUMERIC,
  tax    NUMERIC GENERATED ALWAYS AS (price * 0.18) STORED
);

Unlike a calculated column in SELECT (computed at query time), a generated column is persisted and can be indexed.

PostgreSQL

​Sample data

​Arithmetic operators

​Basic calculated columns

​Profit margin

​Inventory value

​Tax calculation

​Mathematical functions

​Type casting

​CASE — conditional expressions

​Simple CASE

​Searched CASE (with conditions)

​CASE in ORDER BY

​CASE with aggregates

​CASE for conditional updates

​COALESCE, NULLIF, and NULL handling

​COALESCE

​NULLIF

​Date/time calculated columns

​Common interview questions