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

1. Read Oracle: Single-Row Functions

Multimedia

1. YouTube: Oracle SQL Tutorial - Querying Data - Part 9 - Intro to Single-Row Functions
2. YouTube: Oracle SQL Tutorial - Querying Data - Part 10(a) - Single-Row Character Functions
3. YouTube: Oracle SQL Tutorial - Querying data - Part 10(b) - Single-Row Character Functions
4. YouTube: Oracle SQL Tutorial - Querying a table - Part 10(c) Single-Row Numeric Functions
5. YouTube: Oracle SQL Tutorial - Querying a table - Part 10(d) - Single-Row Date Functions

Activities

1. Test case conversion functions using HR data.
   1. 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;
2. Test character manipulation functions using HR data.
   1. Run the following query to test LENGTH():
      SELECT '"' || COUNTRY_NAME || '" is ' || LENGTH(COUNTRY_NAME) || ' characters.' AS COUNTRY FROM COUNTRIES;
   2. Run the following query to test CONCAT() and LPAD()):
      SELECT CONCAT(LPAD(SALARY, 8, '*'), '.00') FROM EMPLOYEES;
   3. 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;
   4. Run the following query to test INSTR():
      SELECT TRIM(SUBSTR('Wales, Jimbo', INSTR('Wales, Jimbo', ',') + 1)) AS FIRST_NAME FROM DUAL;
   5. Run the following query to test REPLACE():
      SELECT REPLACE(PHONE_NUMBER, '.', '-') FROM EMPLOYEES;
3. Test numeric functions using HR data.
   1. 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;
   2. Run the following query to test MOD():
      SELECT MOD(365, 7) FROM DUAL;
4. Test date functions using HR data.
   1. Run the following query to test MONTHS_BETWEEN():
      SELECT LAST_NAME, TRUNC(MONTHS_BETWEEN(SYSDATE, HIRE_DATE)) AS MONTHS_SINCE_HIRE FROM EMPLOYEES;
   2. Run the following query to test MONTHS_ADD():
      SELECT ADD_MONTHS(SYSDATE, 60) AS "60 MONTHS" FROM DUAL;
   3. 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;
5. Test case conversion functions using OE data.
   1. Select customer email address as lower case.
   2. Select customer NLS territory as mixed case.
   3. Select customer status as upper case.
6. Test character manipulation functions using OE data.
   1. Select the total length of customer's first and last names.
   2. Select the customer's credit limit, displayed in a column 10 characters wide with leading asterisks.
   3. Trim any leading or trailing spaces from the customer's address.
   4. Select the domain name / company name from the customer's email address.
   5. Separate the country code and local phone number from the customer's full phone number.
   6. Select the customer's phone number, replacing spaces with hyphens.
7. Test numeric functions using OE data.
   1. Select the customer's credit limit, rounded to 24 payments and truncated to 24 payments.
   2. 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.
8. Test date functions using OE data.
   1. Select the number of months between each order's order date and the current date.
   2. Select 24 months after the order date to determine when payment is due.
   3. Select the calendar date of next Saturday based on SYSDATE.
   4. 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

References

← Sorting Data

Oracle SQL Fundamentals

Conversion Functions →