Oracle SQL Fundamentals/Single-Row Functions
This lesson introduces single-row functions.
Objectives and Skills
editObjectives and skills for the single-row functions portion of Oracle SQL Fundamentals I certification include:[1]
- 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
editMultimedia
edit- 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
edit- 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;
- Run the following query:
- 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;
- Run the following query to test LENGTH():
- 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;
- Run the following query to test ROUND() and TRUNC():
- 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;
- Run the following query to test MONTHS_BETWEEN():
- 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
edit- Single-row functions return a single result row for every row of a queried table or view.[2]
Case Conversion Functions
edit- UPPER(char) returns char, with all letters uppercase.[3]
- LOWER(char) returns char, with all letters lowercase.[4]
- INITCAP(char) returns char, with the first letter of each word in uppercase, all other letters in lowercase.[5]
Character Functions
edit- LENGTH(char) returns the length of the given character string.[6]
- CONCAT(char1, char2) returns char1 concatenated with char2.[7]
- 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.[8]
- INSTR(string, substring, [start,] [occurrence]) searches string for substring, optionally beginning at start and seeking the given occurrence.[9]
- LPAD(expr1, n, [expr2]) returns expr1, left-padded to length n characters with either the sequence of characters in expr2 or spaces.[10]
- RPAD(expr1, n, [expr2]) returns expr1, right-padded to length n characters with either the sequence of characters in expr2 or spaces.[11]
- TRIM([options,] string) trims leading and trailing spaces or other specified characters from a character string[12]
- 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.[13]
Number Functions
edit- 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.[14]
- 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.[15]
- MOD(n2, n1) returns the remainder of n2 divided by n1.[16]
Date Functions
edit- ADD_MONTHS(date, integer) returns the given date plus integer months.[17]
- LAST_DAY(date) returns the date of the last day of the month that contains date.[18]
- MONTHS_BETWEEN(date1, date2) returns the number of months between dates date1 and date2.[19]
- NEXT_DAY(date, char) returns the date of the first weekday named by char that is later than the given date.[20]
- SYSDATE returns the current date and time set for the operating system on which the database server resides.[21]
Assessments
edit- Flashcards: Quizlet: Oracle 1Z0-051 Exam - Date Functions
See Also
editReferences
edit- ↑ Oracle: Database 11g: SQL Fundamentals I Exam Topics
- ↑ Oracle: Single-Row Functions
- ↑ Oracle: UPPER
- ↑ Oracle: LOWER
- ↑ Oracle: INITCAP
- ↑ Oracle: LENGTH
- ↑ Oracle: CONCAT
- ↑ Oracle: SUBSTR
- ↑ Oracle: INSTR
- ↑ Oracle: LPAD
- ↑ Oracle: RPAD
- ↑ Oracle: TRIM
- ↑ Oracle: REPLACE
- ↑ Oracle: ROUND
- ↑ Oracle: TRUNC
- ↑ Oracle: MOD
- ↑ Oracle: ADD_MONTHS
- ↑ Oracle: LAST_DAY
- ↑ Oracle: MONTHS_BETWEEN
- ↑ Oracle: NEXT_DAY
- ↑ Oracle: SYSDATE