Assignment 4 (Single Row Functions)

Single Row Functions in SQL: Character, Number, Date and Conversion

So far in this series we've created tables, manipulated data, and enforced constraints. Now we get into one of the most practically useful parts of SQL — single row functions. These are built-in functions that let you transform, format, and compute values directly inside your queries, without needing any extra code.

If you're new here, it's worth going through Assignment 1 and Assignment 2 first, since the queries below use an employees table with similar structure to what we've been working with.

What Are Single Row Functions?

Single row functions operate on each row individually and return exactly one result per row. That's what sets them apart from aggregate functions like SUM or COUNT, which collapse multiple rows into one result.

Here's a quick overview of the four categories:

A few things worth knowing about single row functions before diving into the queries:

• They work on each row independently

• They return one result per row

• They can change the datatype of a value

• They accept multiple arguments

• They can be used in SELECT, WHERE, and ORDER BY

• They can be nested inside each other

Assignment Queries with Expected Output

All queries below use Oracle SQL syntax and an employees table. These are real SQL functions you'll use constantly in practice — not just in exams.

1. Display the current date

SELECT SYSDATE AS "Date"
FROM dual;

SYSDATE returns the current system date and time. dual is a special one-row, one-column table in Oracle used when you need to run a function without querying an actual table.

Expected output:

Date
31-OCT-25

2. Employee ID, name, salary, and salary after 15% increment

SELECT employee_id,
       last_name,
       salary,
       TRUNC(salary * 1.15) AS "New Salary"
FROM employees;

TRUNC cuts off the decimal part without rounding. So if the new salary computes to 4600.75, you get 4600 — a clean whole number.

Expected output format:

EMPLOYEE_ID | LAST_NAME | SALARY | New Salary
101         | Smith     | 4000   | 4600

3. Names starting with J, A, or M — formatted and sorted

SELECT INITCAP(last_name) AS "Name",
       LENGTH(last_name) AS "Length"
FROM employees
WHERE UPPER(last_name) LIKE 'J%'
   OR UPPER(last_name) LIKE 'A%'
   OR UPPER(last_name) LIKE 'M%'
ORDER BY last_name;

Three functions working together here. UPPER normalizes the case before the LIKE comparison so it works regardless of how data was entered. INITCAP then formats the output with proper title case. LENGTH counts the characters.

Expected output format:

Name   | Length
James  | 5
Miller | 6

4. Last name and months worked since hire date

SELECT last_name,
       MONTHS_BETWEEN(SYSDATE, hire_date) AS "MONTHS_WORKED"
FROM employees
ORDER BY MONTHS_BETWEEN(SYSDATE, hire_date);

MONTHS_BETWEEN calculates the number of months between two dates. The result can be a decimal — so someone hired 3 months and 15 days ago would show something like 3.5. Results are sorted in ascending order, so the newest hires appear first.

Expected output format:

LAST_NAME | MONTHS_WORKED
Brown     | 122.5

5. Build a sentence using string concatenation

SELECT last_name || ' earns ' || salary ||
       ' monthly but wants ' || (salary * 3) AS "Dream Salaries"
FROM employees;

The || operator concatenates strings in Oracle SQL. This query builds a readable sentence from column values — useful for generating formatted reports or messages directly from SQL.

Expected output format:

Dream Salaries
Smith earns 4000 monthly but wants 12000

6. Salary padded with dollar signs

SELECT last_name,
       LPAD(salary, 15, '$') AS "SALARY"
FROM employees;

LPAD pads the left side of a value to reach a total width of 15 characters, filling the gap with $. So a salary of 4000 (4 digits) gets 11 dollar signs prepended. This is often used to align output in fixed-width reports.

Expected output format:

LAST_NAME | SALARY
Smith     | $$$$$$$$$$$4000

7. First Monday after 6 months from hire date

SELECT last_name,
       hire_date,
       TO_CHAR(NEXT_DAY(ADD_MONTHS(hire_date, 6), 'MONDAY'),
               'FMDay, "the" DDth "of" Month, YYYY') AS "REVIEW"
FROM employees;

This one chains three functions together. ADD_MONTHS adds 6 months to the hire date. NEXT_DAY finds the next Monday after that date. TO_CHAR formats the final date into a readable string. The FM prefix in the format string removes the extra padding Oracle adds by default.

Expected output format:

LAST_NAME | HIRE_DATE | REVIEW
Smith     | 12-JAN-24 | Monday, the 15th of July, 2024

8. Commission — show "No Commission" if null

SELECT last_name,
       NVL(TO_CHAR(commission_pct), 'No Commission') AS "COMM"
FROM employees;

NVL handles NULL values by substituting a default. Here, if commission_pct is NULL, the output shows 'No Commission' instead of a blank. TO_CHAR converts the number to a string first so that NVL can compare both values as the same datatype — you can't mix NUMBER and VARCHAR2 in NVL.

Expected output format:

LAST_NAME | COMM
Smith     | No Commission
Miller    | 0.2

Also Explore These Topics

• SQL Assignment 1 — create tables and insert data

• SQL Assignment 2 — UPDATE, DELETE, WHERE, ORDER BY

• SQL Assignment 3 — PRIMARY KEY, FOREIGN KEY, CHECK and other constraints

• SQL Assignment 5 — next in the series

• SQL Quick Recap — fast revision of all core SQL concepts

• Triggers in SQL — automate actions when data changes

• Views in SQL — virtual tables built from your queries

• PL/SQL Assignment — add procedural logic on top of SQL

The complete reference for all Oracle single row functions is in the Oracle SQL Functions documentation. For interactive practice without any setup, W3Schools SQL editor is a good starting point — though note it uses a different SQL dialect, so some Oracle-specific functions like NVL or SYSDATE may not work there.