OCP license.E02.130625

*databases select Sorting

lll. Using Sigle-Row Functions to Customize Output

* Character Functions (continued)

Function	Purpose
LOWER(column|expression )	Converts alpha character values to lowercase
UPPER(column|expression )	Converts alpha character values to uppercase
INITCAP(column|expression )	Converts alpha character values to uppercase for the first letter of each word; all other letters in lowercase
CONCAT(column1|expression1, column2|expression2)	Concatenates the first character value to the second character value; equivalent to concatenation operator (||)
SUBSTR(column|expression,m[ ,n])	Returns specified characters from character value starting at character position m, n characters long (If m is negative, the count starts from the end of the character value. If n is omitted, all characters to the end of the string are returned.)

* These functions manipulate charcter strings:

Function	result
CONCAT('Hello', 'World')	HelloWorld
SUBSTR('HelloWorld', 1, 5) SUBSTR('HelloWorld', -5, 3) SUBSTR('HelloWorld', 3)	Hello Wor lloWorld
LENGTH('HelloWorld')	10
INSTR('HelloWorld', 'W')      // 몇번째에 있느냐	6
LPAD(salary, 10, '*')	*****24000
RPAD(salary, 10, '*')	24000*****
REPLACE ('JACK and JUE', 'J', 'BL')	BLACK and BLUE
TRIM('H' FROM 'HelloWorld')	elloWorld

Number Functions

• ROUND: Rounds value to specified decimal

• TRUNC: Truncates value to specified decimal

• MOD: Returns remainder of division

Using the COALESCE Function

  - In the example shown in the slide, if the MANAGER_ID value is not null, it is displayed. If the

MANAGER_ID value is null, then the COMMISSION_PCT is displayed. If the MANAGER_ID

and COMMISSION_PCT values are null, then the value –1 is displayed.

SELECT last_name, COALESCE(manager_id,commission_pct, -1) comm FROM employees where last_name='King' ORDER BY commission_pct;

Using the CASE Function

SELECT last_name, job_id, salary,       CASE job_id WHEN 'IT_PROG' THEN 1.10*salary                         WHEN 'ST_CLERK' THEN 1.15*salary                         WHEN 'SA_REP' THEN 1.20*salary        ELSE salary END "REVISED_SALARY" FROM employees;

 - In the SQL statement in the slide, the value of JOB_ID is decoded. If JOB_ID is IT_PROG,

the salary increase is 10%; if JOB_ID is ST_CLERK , the salary increase is 15%; if JOB_ID is

SA_REP, the salary increase is 20%. For all other job roles, there is no increase in salary.

The same statement can be written with the DECODE function.

Using the DECODE Function

SELECT last_name, job_id, salary,      DECODE(job_id, 'IT_PROG', 1.10*salary,                'ST_CLERK', 1.15*salary,                'SA_REP', 1.20*salary,           salary)      REVISED_SALARY FROM employees;

 - The DECODE function decodes an expression in a way similar to the IF-THEN-ELSE logic that

is used in various languages. The DECODE function decodes expression after comparing it

to each search value. If the expression is the same as search , result is returned.

If the default value is omitted, a null value is returned where a search value does not match any

of the result values.

Using Group Functions 

• You cannot use the WHERE clause to restrict groups.

• You use the HAVING clause to restrict groups.

• You cannot use group functions in the WHERE clause.

Types of Joins

Joins that are compliant with the SQL:1999 standard
include the following:
• Cross joins
• Natural joins
• USING clause
• Full (or two-sided) outer joins
• Arbitrary join conditions for outer joins

Creating Natural Joins
. The NATURAL JOIN clause is based on all columns
in the two tables that have the same name.
. It selects rows from the two tables that have equal
values in all matched columns.
. If the columns having the same names have
different data types, an error is returned.