Using scalar functions

In addition to aggregate functions we have scalar functions that allow us to work with a field column.

UCASE() and LCASE()

The UCASE() and LCASE() functions return the results in either uppercase or lowercase characters. Below is an example of using both UCASE() and LCASE().

SELECT student_id, LCASE(first_name), LCASE(last_name), UCASE(state)
FROM student;

LENGTH()

The LENGTH() function returns the total number of characters in each field. Spaces between words also count as characters. Below is an example of the LENGTH() function.

SELECT student_id, LENGTH(first_name)
FROM student;

ROUND()

The ROUND() function is used on a numeric field to round the value of the number. It takes two arguments, the field and the number of decimal places to round. The basic syntax for the ROUND() function is:

SELECT ROUND(field_name, number of decimals)
FROM table_name;

Rounding may be different in your database system than taught as a child. Most people round the .5 value and higher in a decimal number to the next whole number. This places a bias on rounding with 4 digits rounding down (.1, .2, .3, and .4) and 5 digits rounding up (.5, .6, .7, .8, and .9). To correct this some database systems use the IEEE 754 standard which rounds the .5 to the nearest even number. So 5.5 would round to 6, 4.5 would round to 4.

Here is an example of rounding book prices to the nearest dollar.

SELECT book_name, ROUND(price,0) AS "Rounded Price"
FROM books;

Lesson Activities

  1. List the titles of the books using all uppercase letters
    Click to see code
    SELECT UCASE(title)
    FROM books;
  2. Round the price of the books to the nearest whole number. Did the books that were 8.50 round up to 9 or down to 8?
    Click to see code
    SELECT ROUND(price,0)
    FROM books;
  3. Using a calculated field, increase the price field by 10% and round the values to the nearest penny.
    Click to see code
    SELECT price, ROUND(price * 1.1, 2)
    FROM books;
  4. Determine the longest book title length.
    Click to see code
    SELECT title, LENGTH(title)
    FROM books;

Questions