*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

OCP license

class : .00 - .45
SQL Fundamental l
SQL Fundamental ll -> additional practices(x)


administration workshop l
administration workshop ll

sql -> 5 day kkkkkkk
week1. sql, ....... ... .. . . . .. .  . .. . arcitecture
week2. 파라미터, 테이블, 사용자 관리, 데이터베이스 설치, 보안 감사 , 성능 모니터링, 백업 및 복구 기본적인것. load, unload
week3. backup, recobery, 자동화 데이터베이스, 암호화.....



데이터베이스가 꺼지면..
  1. $ lsnrctl start
  2. $ sqlplus / as sysdba
     SQL> startup
  3. $ emctl start dbconsole


Database : mysql //  mssql // oracle // cubrid // db2 ...




RDBMS -> Relational Database Management Systems
Database object : 
scala datatype : number, character, etc...
Datamodeling !! 

데이터 베이스 장점 : 일관성(consistency), 정확성(accuracy), 공유 ..
Realtion database concept : 
1. 


* : 필수
o : 선택
# : primary key 식별키(=고유키) : auto_increment


1. 필드 : 한개의 값
2. 튜플 : select data
3. 컬럼 : 한 ㅣ.. 











* Basic select statme 

SELECT * | { [DISTINCT] column | expression [alias], ...} FROM table;
                                                                           ㄴ 별칭
* : can display all columns of data in a  table

 - oracle database start ..
[oracle@ocpdba sysconfig]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jun 24 13:39:53 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup

Writing SQL StateMents
 - SQL statements are note case-sensitive.
 - SQL statement can be on one or more lines.
 - Keywords cannot be abbreviated or split across lines.
 - Clauses are usually placed on separate lines.
 - Indents are used to enhance readability.
 - In iSQL*Plus can optionally be terminated by a semicolon(;). But SQL*plus, you are required to end each SQL statement with a semicolon(;).

Column Heading Defaults
  iSQL*Plus :
    - Default heading alignment : center
    - Default Heading display : Uppercase
  SQL*Plus :
    - Cahracter and Date Column hadings are left-aligned
    - Number column hading are right-aligned
    - Default heading display : Uppercase

Defining a Null value
  - A null is value that is unvailable, unassigned, unknowmm or inapplication.
  - A null is not the same as a zero or a blank space.


* Arithmetic expressions containing a null value evaluate to null.

Using column Aliases
  - if you use to space of column aliases space, then you can ".
  example)   select last_name as name, commission_pct comm from employees;
  example2) select last_name as "Name", commission_pct "Annual Salary" from employees;

Concatenation Operator
  - Links columns or character strings to other columns
  - ls represented by two vertical bars (ll)
  - Creates a resultant column that is a character expression.
  - You can use to using literal character strings 
      :: example) select last_name || ' is a ' || job_id as "employ details" from employees;

What is "Dummy table" ??
  - you can calcurate use to dual table.
    example )   select 4 + 2 from dual.
    example2 ) select 'It''s real database class'  from dual;

select 'It;s real database class'  from dual;

Duplicate Rows
  - The default display of queries is all rows, including duplicate rows.


* Using the WHERE clause



* Using the BETWEEN condition

*Using the IN Conditon

*Using the LIKE condition

If you do can NUll , then...
  example ) select * from employees where manager_id is NULL;

* AND Truth Table


*  OR Truth Table

* Not Truth Table


... WHERE job_id                  NOT IN ('AC_ACCOUNT', 'AD_VP')
... WHERE salary                  NOT BETWEEN 10000 AND 15000
... WHERE last_name           NOT LIKE '%A%'
... WHERE commission_pct  IS NOT NULL

* Rules of Precedence ( ★ precedence NOT >> AND >> OR ★ )
















'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



티스토리 툴바