Lab 08 — String & Date Functions

Manipulate text and dates inside SQL. Format employee names, extract hire years, calculate tenure, and build conditional labels with CASE WHEN.

⏳ Loading SQL engine...

📋 AVAILABLE TABLES: departments, employees, projects, assignmentsdepartments(id, name, budget, location) employees(id, name, dept_id, salary, years, manager_id, hire_date) -- hire_date format: 'YYYY-MM-DD' e.g. '2019-03-15' projects(id, name, dept_id, budget, status, start_date) assignments(employee_id, project_id, hours_per_week, role)

📖 Concept Recap: String & Date Functions in SQLite

String functions:

UPPER(str) / LOWER(str) — change case
LENGTH(str) — number of characters
SUBSTR(str, start, len) — extract substring (1-indexed)
TRIM(str) — remove leading/trailing whitespace
REPLACE(str, find, replace) — substitute text
INSTR(str, substr) — position of first occurrence (0 = not found)
str1 || str2 — concatenate strings with the || operator

Date functions:

strftime('%Y', date) — extract year; '%m' = month, '%d' = day
julianday(date) — convert to Julian day number for arithmetic
CAST((julianday('now') - julianday(hire_date)) / 365 AS INTEGER) — years of service
CASE WHEN condition THEN value ELSE other END — conditional labels

👀 Worked Example (read-only)

Split employee names into first and last name parts using SUBSTR and INSTR.

SELECT name,
       UPPER(name) AS upper_name,
       LENGTH(name) AS name_len,
       SUBSTR(name, 1, INSTR(name, ' ') - 1) AS first_name,
       SUBSTR(name, INSTR(name, ' ') + 1) AS last_name
FROM employees;

✏ Guided

Exercise 1 — Name Formatting: "Last, First"

Extract first and last names, format as "Last, First" using concatenation (||), and derive a company email address. Fill in the blank for the last_name expression.

💡 Hint: last_name = SUBSTR(name, INSTR(name,' ')+1). The space is at position INSTR(name,' ').

SELECT name,
       SUBSTR(name, 1, INSTR(name, ' ') - 1) AS first_name,
       ___ AS last_name,
       SUBSTR(name, INSTR(name,' ')+1) || ', ' ||
           SUBSTR(name, 1, INSTR(name,' ')-1) AS formal_name,
       LOWER(SUBSTR(name, 1, INSTR(name,' ')-1)) || '@company.com' AS work_email
FROM employees
ORDER BY last_name;

💪 Independent

Exercise 2 — Hire Year, Tenure, and Tenure Category

Extract the hire year using strftime, calculate years of service using julianday, and assign a tenure category with CASE WHEN: 'New' (less than 2 years), 'Established' (2–5 years), 'Senior' (more than 5 years).

💡 Hint: CAST((julianday('2026-04-02') - julianday(hire_date)) / 365 AS INTEGER) gives years. Use this in CASE WHEN conditions.

SELECT name,
       hire_date,
       strftime('%Y', hire_date) AS hire_year,
       CAST((julianday('2026-04-02') - julianday(hire_date)) / 365 AS INTEGER) AS years_at_company,
       CASE
           WHEN CAST((julianday('2026-04-02') - julianday(hire_date)) / 365 AS INTEGER) < 2 THEN 'New'
           WHEN CAST((julianday('2026-04-02') - julianday(hire_date)) / 365 AS INTEGER) <= 5 THEN 'Established'
           ELSE 'Senior'
       END AS tenure_category
FROM employees
ORDER BY years_at_company DESC;

🔥 Challenge

Exercise 3 — Department Summary with Formatted Strings

For each department show: name in UPPER case, location in LOWER case, headcount, budget formatted as '$' + value, average salary, and the salary range as a single string like '69000 - 115000'.

💡 Hint: Concatenate MIN and MAX with ' - ': CAST(MIN(e.salary) AS TEXT) || ' - ' || CAST(MAX(e.salary) AS TEXT)

SELECT UPPER(d.name) AS dept_upper,
       LOWER(d.location) AS location_lower,
       COUNT(e.id) AS headcount,
       '$' || CAST(CAST(d.budget AS INTEGER) AS TEXT) AS budget_formatted,
       ROUND(AVG(e.salary), 0) AS avg_salary,
       CAST(CAST(MIN(e.salary) AS INTEGER) AS TEXT) || ' - ' ||
           CAST(CAST(MAX(e.salary) AS INTEGER) AS TEXT) AS salary_range
FROM departments d
LEFT JOIN employees e ON d.id = e.dept_id
GROUP BY d.id, d.name, d.location, d.budget
ORDER BY headcount DESC;

🏆 Mini Project

Mini Project — Employee Anniversary Report

Build a work anniversary report for every employee showing: formal name (Last, First), hire date, hire year, years of service, next anniversary date in 2026, and a congratulations message using CASE WHEN.

Message rules: less than 1 year = 'Welcome!', exactly 2 years = '2-Year Anniversary!', exactly 5 years = '5-Year Anniversary!', exactly 10 years = '10-Year Anniversary!', otherwise = 'Milestone: X years'.

SELECT SUBSTR(name,INSTR(name,' ')+1) || ', ' || SUBSTR(name,1,INSTR(name,' ')-1) AS formal_name,
       hire_date,
       strftime('%Y', hire_date) AS hire_year,
       CAST((julianday('2026-04-02') - julianday(hire_date)) / 365 AS INTEGER) AS years_of_service,
       '2026-' || strftime('%m-%d', hire_date) AS anniversary_2026,
       CASE
           WHEN CAST((julianday('2026-04-02') - julianday(hire_date)) / 365 AS INTEGER) < 1
               THEN 'Welcome!'
           WHEN CAST((julianday('2026-04-02') - julianday(hire_date)) / 365 AS INTEGER) = 2
               THEN '2-Year Anniversary!'
           WHEN CAST((julianday('2026-04-02') - julianday(hire_date)) / 365 AS INTEGER) = 5
               THEN '5-Year Anniversary!'
           WHEN CAST((julianday('2026-04-02') - julianday(hire_date)) / 365 AS INTEGER) = 10
               THEN '10-Year Anniversary!'
           ELSE 'Milestone: ' ||
               CAST(CAST((julianday('2026-04-02') - julianday(hire_date)) / 365 AS INTEGER) AS TEXT) ||
               ' years'
       END AS message
FROM employees
ORDER BY years_of_service DESC;

🏁 Lab 08 Complete!

You can now manipulate text with UPPER, LOWER, SUBSTR, INSTR, and string concatenation, extract date parts with strftime, calculate tenure with julianday, and build conditional labels with CASE WHEN.

Next: stop just reading data — start writing it.

Continue to Lab 09 — INSERT, UPDATE, DELETE & CREATE →