*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:
Functionresult
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.














'STUDY > OCP' 카테고리의 다른 글

OCP license.E05.130628  (0) 2013.07.06
OCP license.E04.130627  (0) 2013.07.06
OCP license.E03.130626  (0) 2013.07.06
OCP license.E02.130625  (0) 2013.07.06
OCP license.E01.130624  (0) 2013.07.06
oracle TRUNCATE  (0) 2013.06.26

Comment