# Oracle SQL Fundamentals/Single-Row Functions

This lesson introduces single-row functions.

## Objectives and Skills

Objectives and skills for the single-row functions portion of Oracle SQL Fundamentals I certification include:
* Using Single-Row Functions to Customize Output
** Describe various types of functions available in SQL
** Use character, number, and date functions in SELECT statements

## Readings

* Read Oracle: Single-Row Functions

## Multimedia

* YouTube: Oracle SQL Tutorial - Querying Data - Part 9 - Intro to Single-Row Functions
* YouTube: Oracle SQL Tutorial - Querying Data - Part 10(a) - Single-Row Character Functions
* YouTube: Oracle SQL Tutorial - Querying data - Part 10(b) - Single-Row Character Functions
* YouTube: Oracle SQL Tutorial - Querying a table - Part 10(c) Single-Row Numeric Functions
* YouTube: Oracle SQL Tutorial - Querying a table - Part 10(d) - Single-Row Date Functions

## Activities

* Test case conversion functions using HR data.
** Run the following query: SELECT COUNTRY_NAME AS ORIGINAL, UPPER(COUNTRY_NAME) AS UPPER, LOWER(COUNTRY_NAME) AS LOWER, INITCAP(COUNTRY_NAME) AS MIXED FROM COUNTRIES;
* Test character manipulation functions using HR data.
** Run the following query to test LENGTH: SELECT '"' || COUNTRY_NAME || '" is ' || LENGTH(COUNTRY_NAME) || ' characters.' AS COUNTRY FROM COUNTRIES;
** Run the following query to test CONCAT and LPAD): SELECT CONCAT(LPAD(SALARY, 8, '*'), '.00') FROM EMPLOYEES;
** Run the following query to test SUBSTR and TRIM: SELECT FIRST_NAME, LAST_NAME, CONCAT(LOWER(SUBSTR(TRIM(FIRST_NAME), 1, 1)), LOWER(SUBSTR(TRIM(LAST_NAME), 1, 7))) AS EMAIL FROM EMPLOYEES;
** Run the following query to test INSTR: SELECT TRIM(SUBSTR('Wales, Jimbo', INSTR('Wales, Jimbo', ',') + 1)) AS FIRST_NAME FROM DUAL;
** Run the following query to test REPLACE: SELECT REPLACE(PHONE_NUMBER, '.', '-') FROM EMPLOYEES;
* Test numeric functions using HR data.
** Run the following query to test ROUND and TRUNC: SELECT LAST_NAME, SALARY * 12 / 365 AS DAILY, ROUND(SALARY * 12 / 365, 2) AS ROUND, TRUNC(SALARY * 12 / 365, 2) AS TRUNC FROM EMPLOYEES;
** Run the following query to test MOD: SELECT MOD(365, 7) FROM DUAL;
* Test date functions using HR data.
** Run the following query to test MONTHS_BETWEEN: SELECT LAST_NAME, TRUNC(MONTHS_BETWEEN(SYSDATE, HIRE_DATE)) AS MONTHS_SINCE_HIRE FROM EMPLOYEES;
** Run the following query to test MONTHS_ADD: SELECT ADD_MONTHS(SYSDATE, 60) AS "60 MONTHS" FROM DUAL;
** Run the following query to test NEXT_DAY: SELECT NEXT_DAY(SYSDATE, 'MONDAY') AS NEXT_MONDAY, LAST_DAY(SYSDATE) AS LAST_DAY_OF_MONTH FROM DUAL;
* Test case conversion functions using OE data.
** Select customer email address as lower case.
** Select customer NLS territory as mixed case.
** Select customer status as upper case.
* Test character manipulation functions using OE data.
** Select the total length of customer's first and last names.
** Select the customer's credit limit, displayed in a column 10 characters wide with leading asterisks.
** Trim any leading or trailing spaces from the customer's address.
** Select the domain name / company name from the customer's email address.
** Separate the country code and local phone number from the customer's full phone number.
** Select the customer's phone number, replacing spaces with hyphens.
* Test numeric functions using OE data.
** Select the customer's credit limit, rounded to 24 payments and truncated to 24 payments.
** Select the number of cards left over in a 52-card deck if there are 5 players and they each receive an equal number of cards.
* Test date functions using OE data.
** Select the number of months between each order's order date and the current date.
** Select 24 months after the order date to determine when payment is due.
** Select the calendar date of next Saturday based on SYSDATE.
** Select the last day of the month for each order date.

## Lesson Summary

* Single-row functions return a single result row for every row of a queried table or view.

### Case Conversion Functions

* UPPER(char) returns char, with all letters uppercase.
* LOWER(char) returns char, with all letters lowercase.
* INITCAP(char) returns char, with the first letter of each word in uppercase, all other letters in lowercase.

### Character Functions

* LENGTH(char) returns the length of the given character string.
* CONCAT(char1, char2) returns char1 concatenated with char2.
* SUBSTR(char, start, [length]) returns a portion of char, beginning at character start, either length characters long or to the end of the string if length is omitted.
* INSTR(string, substring, [start,] [occurrence]) searches string for substring, optionally beginning at start and seeking the given occurrence.
* LPAD(expr1, n, [expr2]) returns expr1, left-padded to length n characters with either the sequence of characters in expr2 or spaces.
* RPAD(expr1, n, [expr2]) returns expr1, right-padded to length n characters with either the sequence of characters in expr2 or spaces.
* TRIM([options,] string) trims leading and trailing spaces or other specified characters from a character string
* REPLACE(char, search, [replacement]) returns char with every occurrence of search replaced with replacement. If replacement is omitted or null, then all occurrences of search are removed.

### Number Functions

* ROUND(number, [integer]) ROUND returns n rounded to integer places to the right of the decimal point. If you omit integer, then n is rounded to zero places. If integer is negative, then n is rounded off to the left of the decimal point.
* TRUNC(n1, n2) returns n1 truncated to n2 decimal places. If n2 is omitted, then n1 is truncated to 0 places. n2 can be negative to truncate (make zero) n2 digits left of the decimal point.
* MOD(n2, n1) returns the remainder of n2 divided by n1.

### Date Functions

* ADD_MONTHS(date, integer) returns the given date plus integer months.
* LAST_DAY(date) returns the date of the last day of the month that contains date.
* MONTHS_BETWEEN(date1, date2) returns the number of months between dates date1 and date2.
* NEXT_DAY(date, char) returns the date of the first weekday named by char that is later than the given date.
* SYSDATE returns the current date and time set for the operating system on which the database server resides.

## Assessments

* Flashcards: Quizlet: Oracle 1Z0-051 Exam - Date Functions

## See Also

* Oracle: Database 2 Day Developers Guide - Selecting Table Data