Exercises

Objectives

By the end of this lesson, you should be able to:

• Combine arithmetic, string, numeric, date, and aggregate functions in real queries
• Use CAST()/:: where a type conversion is needed
• Read a query using multiple functions and predict its result

⚠️ A note on verification: every query and result in this lesson was run against a real, live PostgreSQL 18 database. Results involving CURRENT_DATE will differ depending on the day you run them, everything else is fixed.

Setup

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    department VARCHAR(50),
    salary NUMERIC(10,2) NOT NULL,
    hire_date DATE NOT NULL
);

INSERT INTO employees (first_name, last_name, email, department, salary, hire_date) VALUES
  ('Erin', 'Castillo', 'erin.castillo@example.com', 'Engineering', 74000, '2021-03-15'),
  ('Jordan', 'Blake', 'jordan.blake@example.com', 'Sales', 61000, '2022-06-01'),
  ('Maya', 'Fischer', 'maya.fischer@example.com', 'Marketing', 67000, '2020-11-20'),
  ('Priya', 'Desai', 'priya.desai@example.com', 'Engineering', 89000, '2019-01-10'),
  ('Sam', 'Whitfield', 'sam.whitfield@example.com', 'Sales', 63000, '2023-02-14'),
  ('Devon', 'Ellery', 'devon.ellery@example.com', NULL, 58000, '2023-08-30'),
  ('Taylor', 'Nakamura', 'taylor.nakamura@example.com', 'Engineering', 95000, '2018-05-05'),
  ('Alexis', 'Moreno', 'alexis.moreno@example.com', 'Marketing', 71000, '2021-09-12');

Employee id values follow insert order: Erin is 1, Jordan is 2, Maya is 3, Priya is 4, Sam is 5, Devon is 6, Taylor is 7, Alexis is 8.

Exercise 1: Calculations

a) Write a query that shows every employee’s first_name and their salary after an 8% raise, rounded to 2 decimal places, aliased as new_salary. Expected result for a few: Erin 79920.00, Taylor 102600.00, Devon 62640.00.

b) Write a query for Maya Fischer’s weekly pay, dividing her salary by 52 and rounding to 2 decimal places. Expected result: 1288.46.

c) Write a query showing CEIL() and FLOOR() of Priya Desai’s monthly salary (salary / 12). Expected result: 7417 and 7416, since her exact monthly amount is 7416.67, CEIL rounds up, FLOOR rounds down.

d) Write a query for the absolute difference between Taylor Nakamura’s salary (95000) and Priya Desai’s salary (89000), using ABS(). Expected result: 6000.

Exercise 2: Formatting Names and Text

a) Write a query that shows Priya Desai’s name as "DESAI, PRIYA", combining || and UPPER().

b) Write a query that extracts the username portion of Sam Whitfield’s email (everything before the @), using SUBSTRING() and POSITION(). Expected result: sam.whitfield.

c) Write a query that shows Alexis Moreno’s first_name, last_name, and the combined character length of first_name || last_name (no space). Expected result: 12.

Exercise 3: Working with Dates

a) Write a query for Jordan Blake’s hire month, using EXTRACT(MONTH FROM hire_date). Expected result: 6 (hired 2022-06-01).

b) Write a query for Devon Ellery’s hire_date plus 180 days, their approximate six-month mark. Expected result: 2024-02-26.

c) Write a query for how many days after Taylor Nakamura’s hire date (2018-05-05) Erin Castillo was hired (2021-03-15), using date subtraction. Expected result: 1045 days.

d) Write a query showing your own current tenure calculation for Priya Desai using AGE(CURRENT_DATE, hire_date). Since this depends on today’s date, describe in words what changes about the result if you run it a year from now.

Exercise 4: Aggregates and Casting

a) Write a query for how many employees are in the Engineering department, using COUNT(*) with a WHERE clause. Expected result: 3.

b) Write a query for the total salary of everyone in the Sales department, using SUM(). Expected result: 124000.00.

c) Write a query that produces the label "7 - Taylor" for Taylor Nakamura, casting id to TEXT and concatenating it with ' - ' and first_name.

d) Explain, in your own words, why CAST('not a number' AS INTEGER) raises an error instead of returning NULL or 0.

Recap

This module covered every core built-in function category: arithmetic expressions, string functions, numeric functions, date and time functions, a first look at aggregate functions, and type casting. Each one transforms or computes a value directly inside a query, no need to pull raw data out and process it elsewhere.

Next module: GROUP BY and HAVING, putting the aggregate functions from this module to work across groups of rows instead of the whole table at once.