DATA WAREHOUSINGTARLETON STATE UNIVERSITYMATH 586

INSTRUCTORS: KEITH EMMERT, SEAN PERRY, MICHA ROBERSON, AND

MICHAEL SCHUCKING

COURSE MATERIALS: ORACLE SQL BY EXAMPLE, 4TH EDITION BY ALICE RISCHERTSOFTWARE: ORACLE 11GTEXTBOOK RESOURCE WEBSITE:

HTTP://WWW.ORACLESQLBYEXAMPLE.COM/COURSE OBJECTIVES:UNDERSTAND AND APPLY FUNDAMENTAL SQL FUNCTIONS, EXPRESSIONS, COMMANDS ETC.

A. CREATE/DROP TABLES/DATABASEB. QUERY TABLESC. USE AGGREGATE FUNCTIONSD. USE INNER/OUTER/LEFT/RIGHT JOINSE. WRITE QUERIES WITH STRING, NUMERIC,

CONVERSION, AND DATE/TIME FUNCTIONS. CONDUCT QA AND INTEGRITY CHECKS.

CHAPTER 4 CHARACTER, NUMBER, AND MISCELLANEOUS FUNCTIONS A function is a type of formula whose result is one of

two things: either a transformation or information. Syntax SymbolsSYMBOL USAGE[] Square brackets enclose syntax options{} Braces enclose items of which only one is required| A vertical bar denotes options… Three dots indicate that the preceding expression

can be repeatedDelimiters Delimiters must be entered exactly as shown in

the syntax. Commas, parentheses, etc.CAPS Words in all capital letters indicate the Oracle

keywords that identify the individual elements of the SQL command or the name of the function.

UNDERLINE

Default values are underlined

LAB 4.1: CHARACTER FUNCTIONSLOWER The LOWER function transforms data into lowercase.

SELECT state, LOWER(state), LOWER('STATE'), LOWER('State') FROM zipcode;

STATE LOWER(STATE) LOWER('STATE') LOWER('STATE') ----- ------------ -------------- -------------- CT ct state state FL fl state state OH oh state state MI mi state state GA ga state state NY ny state state MA ma state state NJ nj state state WV wv state state PR pr state state

10 rows selected

UPPER AND INITCAP The UPPER function transforms data into UPPERCASE. The INITCAP functions capitalizes the first letter and lowercases the

rest of the word. SELECT DISTINCT state, UPPER(state), INITCAP(state) FROM zipcode;

STATE UPPER(STATE) INITCAP(STATE) ----- ------------ -------------- NJ NJ Nj MA MA Ma CT CT Ct GA GA Ga WV WV Wv FL FL Fl PR PR Pr NY NY Ny OH OH Oh MI MI Mi

10 rows selected

THE LPAD AND RPAD FUNCTIONS Transforms data by padding strings to the

left(LPAD) or to the right(RPAD). There is a third, optional input parameter to

specify which character the string should be padded with. Strings are padded with spaces by default. Remember that the second input is the maximum TOTAL character length.

SELECT RPAD('yeah', 9, '!'), LPAD('no', 4, '?') from dual;

RPAD('YEAH',9,'!') LPAD('NO',4,'?') ------------------ ---------------- yeah!!!!! ??no

THE DUAL TABLE DUAL is a table unique to Oracle. It

contains a single row and a single column called DUMMY.

Used to call functions with string literals as input that does not exist in a table.

See example on previous slide.

THE LTRIM, RTRIM, AND TRIM FUNCTIONS LTRIM and RTRIM remove all unwanted

characters in a string. Blanks spaced are removed by default.

The second input to the function is the character to be removed.

SELECT LTRIM('000012340', '0'), RTRIM('0123400000', '0'),LTRIM(RTRIM('000012340000','0'), '0')FROM dual;

LTRIM('000012340','0') RTRIM('0123400000','0') LTRIM(RTRIM('000012340000','0'), '0')---------------------- ----------------------- ---------------------------- 12340 01234 1234

TRIM FUNCTION Instead of nesting the LTRIM and RTRIM

functions, we can use TRIM to achieve the same results. Keywords are LEADING, TRAILING, and the default BOTH.

SELECT TRIM(LEADING '0' FROM '000012340'), TRIM(TRAILING '0' FROM '0123400000'), TRIM('0' FROM '000012340000')FROM dual;

TRIM(LEADING'0'FROM'000012340') TRIM(TRAILING'0'FROM'0123400000') TRIM('0'FROM'000012340000') ------------------------------- --------------------------------- --------------------------- 12340 01234 1234

THE SUBSTR FUNCTION SUBSTR transforms a string, returning a

substring or a subset of a string, based on its input parameters.

Takes two input parameters and an optional third. SUBSTR(input, starting_postition, ending_position)

SELECT substr('I love MATH!', 1,1), substr('I love MATH!', 2,6),substr('I love MATH!', 7)FROM dual;

SUBSTR('ILOVEMATH!',1,1) SUBSTR('ILOVEMATH!',2,6) SUBSTR('ILOVEMATH!',7) ------------------------ ------------------------ ---------------------- I love MATH!

THE INSTR FUNCTION INSTR, meaning in string, looks for the

occurrence of a string inside another string, returning the starting position of the search string within the target string.

SELECT INSTR('linear', 'er'),INSTR('hyperbolic', 'er')FROM dual;

INSTR('LINEAR','ER') INSTR('HYPERBOLIC','ER') ---------------------- ------------------------ 0 4

THE LENGTH FUNCTION The LENGTH function determines the

length of a string, expressed as a number.

SELECT length('linear')FROM dual;

LENGTH('LINEAR') ---------------------- 6

FUNCTIONS IN WHERE AND ORDER BY CLAUSES Functions can be used anywhere an

expression can be used.

SELECT first_name, last_nameFROM sharma.studentWHERE instr(first_name, '.')>0ORDER BY LENGTH(last_name);

FIRST_NAME LAST_NAME ------------------------- ------------------------- Suzanne M. Abid J. Dalvi Austin V. Cadet D. Orent

NESTED FUNCTIONS As we saw in the earlier example using

LTRIM(RTRIM(in)), functions can be nested.

The are evaluated starting from the inner function and working outward.

SELECT first_name, instr(SUBSTR(first_name, 1,5), 'rr'), last_nameFROM student;

FIRST_NAME INNERSTRING LAST_NAME ------------------------- ---------------------- ------------------------- Pierre 4 Radicola Derrick 3 Baltazar Gerry 3 Tripp

CONCATENATION Concatenation connects strings together

to become one. Two different concatenation methods The CONCAT function:

Select CONCAT(‘me’, ‘ too!’) from DUAL;

‘me too!’ The || symbol.

Select ‘me’ || ‘ too!’ from DUAL;‘me too!’

THE REPLACE FUNCTION The REPLACE function replaces one

string with another string.SELECT REPLACE('My hand is asleep', 'hand', 'foot') FROM DUAL;

REPLACE('MYHANDISASLEEP','HAND','FOOT') --------------------------------------- My foot is asleep

THE TRANSLATE FUNCTION The TRANSLATE function provides a one-

for-one character substitution. SELECT TRANSLATE('My hand is asleep', 'ae', 'io') FROM DUAL;

TRANSLATE('MYHANDISASLEEP','AE','IO') ------------------------------------- My hind is isloop

THE SOUNDEX FUNCTION The SOUNDEX functions allows you to

compare differently spelled words that phonetically sound alike.

SELECT student_id, last_nameFROM sharma.studentWHERE soundex(last_name) = soundex('MARTIN');

STUDENT_ID LAST_NAME ---------------------- ------------------------- 110 Martin 324 Marten 393 Martin

SQL DEVELOPER SNIPPETS SQL functions or syntax examples that

are frequently used are stored and called snippets.

They can be accessed by going to ViewSnippets in SQL Developer

LAB 4.1: EXERCISES Go to page 147 in book Answer and discuss answers in class.

LAB 4.2: NUMBER FUNCTIONSABS AND SIGN The ABS Function

The ABS function computes the absolute value of a number, measuring its magnitude.

SELECT 'The absolute value of -25 is ' || ABS(-25)FROM DUAL;

'THEABSOLUTEVALUEOF-25IS'||ABS(-25) ----------------------------------- The absolute value of -25 is 25

The SIGN Function The SIGN function tells you the sign of a value, returning a

number 1 for positive numbers, -1 for negative numbers, and 0 for zero.SELECT -14, SIGN(-14), SIGN(14), SIGN(0) FROM DUAL;

-14 SIGN(-14) SIGN(14) SIGN(0) ---------------------- ---------------------- ---------------------- ---------------------- -14 -1 1 0

ROUND AND TRUNC FUNCTIONS ROUND rounds a value to a given number of digits

of precision. TRUNC cuts off a value to a given number of digits of precisions.

They each have two inputs, the number to be rounded/truncated and the number of places to round/truncate to. A negative value in the second input will round/truncate to n digits to the left of the decimal point.

SELECT 22.0051, ROUND(22.0051, 2), ROUND(22.0051, -2) FROM DUAL;22.0051 ROUND(22.0051,2) ROUND(22.0051,-2) ---------------------- ---------------------- ---------------------- 22.0051 22.01 0

SELECT 22.0051, TRUNC(22.0051, 2), TRUNC(22.0051, -2) FROM DUAL;

22.0051 TRUNC(22.0051,2) TRUNC(22.0051,-2) ---------------------- ---------------------- ---------------------- 22.0051 22 0

THE FLOOR AND CEIL FUNCTIONS The CEIL function returns the smallest

integer greater than or equal to a value; the FLOOR function returns the largest integer equal to or less than a value.

SELECT FLOOR(22.5), CEIL(22.5) FROM DUAL;

FLOOR(22.5) CEIL(22.5) ---------------------- ---------------------- 22 23

THE MOD FUNCTION MOD is a function that returns the

modulus, or the remainder of a value divided by another value.

It takes two inputs, the number to be divided and the value to divide it by. MOD uses FLOOR in its computations.SELECT 25/6, MOD(25,6), 28/5, MOD(28,5) FROM DUAL;

25/6 MOD(25,6) 28/5 MOD(28,5) ---------------------- ---------------------- ---------------------- ---------------------- 4.16666667 1 5.6 3

25 – 6*(FLOOR(4.16)) =25-24 =1 28-5*(FLOOR(5.6)) = 28-25=3

FLOATING-POINT NUMBERS Floating-point numbers support the IEEE standard for

binary floating-point arithmetic. Computations can sometimes be in the order of 5-10x faster than NUMBER because floating-point data types use the native instruction set supplied by the hardware vendor.

BINARY_DOUBLE supports a wider range of values than does BINARY_FLOAT but they both consist of three components: a sign, the signed exponent, and significand.

The ROUND function takes on a different behavior if the input is BINARY_FLOAT or BINARY_DOUBLE, rounding to the nearest EVEN value.

select ROUND(3.5), ROUND(3.5f), ROUND(4.5), ROUND(4.5f) from DUAL;

ROUND(3.5) ROUND(3.5F) ROUND(4.5) ROUND(4.5F) ------------------ ----------- ---------------------- ----------- 4 4.0 5 4.0

THE REMAINDER FUNCTION The REMAINDER function calculates the

REMAINDER according to the IEEE specification.

It is similar to MOD, but uses ROUND instead of FLOOR in its calculations.

SELECT 25/6, REMAINDER(25,6), 28/5, REMAINDER(28,5) FROM DUAL;

25/6 REMAINDER(25,6) 28/5 REMAINDER(28,5) ---------------------- ---------------------- ---------------------- ---------------------- 4.166666666667 1 5.6 -2

25 – 6*(ROUND(4.16)) =25-24 =1 28-5*(ROUND(5.6)) = 28-30=-2

ARITHMETIC OPERATORS The four mathematical operators

( addition, subtraction, multiplication, and division) can used in a SQL statement and can be combined.

Parentheses are used to group computations, indicating precedence of the operators.

LAB 4.2: EXERCISES Go to page 163 in book Answer and discuss answers in class.

LAB 4.3: MISCELLANEOUS SINGLE-ROW FUNCTIONSTHE NVL FUNCTION The NVL function replaces a NULL value

with a default value. Since a computation with an unknown

value yields an unknown value, the NVL function can be used to replace the unknown value with a valid value.

SELECT NVL(1,2) + NVL(NULL, 2) FROM DUAL;

NVL(1,2)+NVL(NULL,2) ---------------------- 3

THE COALESCE FUNCTION Similar to the NVL function except instead of specifying one

substitution expression for a null value, you can optionally evaluate multiple substitution columns or substitution expressions.

If the first value is not null then the first value is returned else if the second value is not null then the second value is returned else if the third value is not null then the third value is returned, etc.

SELECT student_id, midterm_grade, finalexam_grade, quiz_grade,COALESCE(midterm_grade, finalexam_grade, quiz_grade) as "Coalesce"from grade_summary;

STUDENT_ID MIDTERM_GRADE FINALEXAM_GRADE QUIZ_GRADE Coalesce -------------- ---------------------- ---------------------- --------------- -------------- 123 90 50 100 90 456 80 95 80 678 98 98 789 78 85 78

THE NVL2 FUNCTION This function checks for both not null

and null values and has three parameters.

If the first input is not null then the second parameter is returned, else the third parameter is returned.

SELECT NVL2(1,'Not Null', 'Null'), NVL2(NULL, 'Not Null', 'Null') FROM dual;

NVL2(1,'NOTNULL','NULL') NVL2(NULL,'NOTNULL','NULL') ------------------------ --------------------------- Not Null Null

THE LNNVL FUNCTION The LNNVL can ONLY be used in the

WHERE clause. It returns either true of false. It returns

true and therefore a result if the condition is either false or unknown.

SELECT course_no, cost FROM course WHERE LNNVL(cost<1500)

COURSE_NO COST ---------------------- ---------------------- 80 1595 450

THE NULLIF FUNCTION The NULLIF function is unique in that it

generates null values. The function compares two functions; if

the values are equal, the function returns a null; otherwise, the function returns the first expression.

SELECT NULLIF('1','1'), NULLIF('1','2') FROM dual;

NULLIF('1','1') NULLIF('1','2') --------------- --------------- 1

THE NANVL FUNCTION This function is used only for the

BINARY_FLOAT and BINARY_DOUBLE floating-point data types.

The function returns a substitution value in case the input is NAN(“not a number”).

SELECT NANVL(BINARY_FLOAT_NAN,1), NANVL(1,2) FROM dual;

NANVL(BINARY_FLOAT_NAN,1) NANVL(1,2) ------------------------- ---------------------- 1.0 1

THE DECODE FUNCTION The DECODE function substitutes values based on a condition, using if-

then-else logic. If the input value is equal to the second parameter then the third value

is returned else the fourth value is returned. The search and result values can be repeated.

SELECT DISTINCT state, DECODE(state, 'NY', 'New York', 'NJ', 'New Jersey', 'OTHER')FROM zipcode;

STATE DECODE(STATE,'NY','NEWYORK','NJ','NEWJERSEY','OTHER') ----- ----------------------------------------------------- MA OTHER WV OTHER OH OTHER MI OTHER NJ New Jersey FL OTHER CT OTHER GA OTHER PR OTHER NY New York

THE DECODE FUNCTION AND NULLS AND COMPARISONS A NULL value can be explicitly passed to the DECODE function.

SELECT 'NY',DECODE('NY', 'NY', 'New York', 'NJ', 'New Jersey', NULL, 'OTHER'), NULL, DECODE(NULL, 'NY', 'New York', 'NJ', 'New Jersey', NULL, 'OTHER')FROM dual;'NY' DECODE('NY',…) NULL DECODE(NULL,…) --- --------------------- ------------ ------------------NY New York OTHER

The DECODE function does not allow greater than or less than comparison; however, combining it with the SIGN function overcomes this shortcoming.

SELECT course_no, cost, DECODE(SIGN(cost-1195), -1, 500, cost) newcostFROM courseWHERE course_no IN (80, 20, 135, 450)ORDER BY 2;

COURSE_NO COST NEWCOST ----------------- ----------- ---------------135 1095 500 20 1195 1195 80 1595 1595

THE SEARCHED CASE EXPRESSION Each CASE expression starts with keyword CASE and end with the

keyword END; the ELSE clause is optional. CASE expressions can be nested within each other and used in the

where clause. You must be careful that each THEN keyword returns a value of the same data type.

SELECT course_no, cost, CASE WHEN cost<1100 THEN 1000 WHEN cost>=1100 AND cost <1500 THEN cost*1.1 WHEN cost IS NULL THEN 0 ELSE cost END "test case"FROM courseWHERE course_no IN (80, 20, 135, 450)ORDER BY 2;

COURSE_NO COST test case ---------------------- ---------------------- ---------------------- 135 1095 1000 20 1195 1314.5 80 1595 1595 450 0

THE SIMPLE CASE EXPRESSION If your conditions are testing for equality only, you can use a simple

CASE expression. The simple CASE expression has an additional input which the

WHEN statements are tested against.

SELECT course_no, cost, CASE cost WHEN 1095 THEN cost/2 WHEN 1195 THEN cost*1.1 WHEN 1595 THEN cost ELSE cost*.5 END "simple case"FROM courseWHERE course_no IN (80, 20, 135, 450)ORDER BY 2;

COURSE_NO COST simple case ---------------------- ---------------------- ---------------------- 135 1095 547.5 20 1195 1314.5 80 1595 1595 450

CHAPTER 4 COMPLETE! Quiz will be given at the beginning of

our next class