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.
 * 2) Run the following query:
 * 3) Test character manipulation functions using HR data.
 * 4) Run the following query to test LENGTH:
 * 5) Run the following query to test CONCAT and LPAD):
 * 6) Run the following query to test SUBSTR and TRIM:
 * 7) Run the following query to test INSTR:
 * 8) Run the following query to test REPLACE:
 * 9) Test numeric functions using HR data.
 * 10) Run the following query to test ROUND and TRUNC:
 * 11) Run the following query to test MOD:
 * 12) Test date functions using HR data.
 * 13) Run the following query to test MONTHS_BETWEEN:
 * 14) Run the following query to test MONTHS_ADD:
 * 15) Run the following query to test NEXT_DAY:
 * 16) Test case conversion functions using OE data.
 * 17) Select customer email address as lower case.
 * 18) Select customer NLS territory as mixed case.
 * 19) Select customer status as upper case.
 * 20) Test character manipulation functions using OE data.
 * 21) Select the total length of customer's first and last names.
 * 22) Select the customer's credit limit, displayed in a column 10 characters wide with leading asterisks.
 * 23) Trim any leading or trailing spaces from the customer's address.
 * 24) Select the domain name / company name from the customer's email address.
 * 25) Separate the country code and local phone number from the customer's full phone number.
 * 26) Select the customer's phone number, replacing spaces with hyphens.
 * 27) Test numeric functions using OE data.
 * 28) Select the customer's credit limit, rounded to 24 payments and truncated to 24 payments.
 * 29) 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.
 * 30) Test date functions using OE data.
 * 31) Select the number of months between each order's order date and the current date.
 * 32) Select 24 months after the order date to determine when payment is due.
 * 33) Select the calendar date of next Saturday based on SYSDATE.
 * 34) 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