*UNION Operator

* UNION ALL Operator

* INTERSECT Operator
* MINUS Operator

SQL> select * from a;


SQL> select * from b;


SQL> select id  from a
      union all
      select id  from b;


SQL> select id from a
select id from b;

SQL> select id from a
select id from b;

SQL> select id from a
select id from b;

SQL> select id from b
select id from a;

Set Operator Guidelines
• The expressions in the SELECT lists must match in number and data type.
• Parentheses can be used to alter the sequence of execution.
• The ORDER BY clause:
– Can appear only at the very end of the statement
– Will accept the column name, aliases from the first SELECT statement, or the positional notation

The Oracle Server and Set Operators
• Duplicate rows are automatically eliminated except in UNION ALL.
• Column names from the first query appear in the result.
• The output is sorted in ascending order by default except in UNION ALL.

The iSQL*Plus COLUMN Command
You can use the iSQL*Plus COLUMN command to customize column headings.
      COL[UMN] [{column|alias} [option]]
Where OPTION is:       
      CLE[AR]: Clears any column formats       
      HEA[DING] text: Sets the column heading
      FOR[MAT] format: Changes the display of the column using a format model       
      NOPRINT | PRINT: Suppresses or displays the column heading and data
The following statement suppresses the column data and title heading for the column named
A_DUMMY. Notice that the first SELECT clause in the previous slide creates a dummy column
named A_DUMMY.
      COLUMN a_dummy NOPRINT

• DataBases Objects
TableBasic unit of storage; composed of rows
ViewLogically represents subsets of data from one or more tables
SequenceGenerates numeric values
IndexImproves the performance of some queries
Sysnonymgives alternative names to objects

Including Constraints
• Constraints enforce rules at the table level.
• Constraints prevent the deletion of a table if there are dependencies.
• The following constraint types are valid:
     – NOT NULL
     – UNIQUE
     – CHECK

'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


*databases select Sorting

lll. Using Sigle-Row Functions to Customize Output

* Character Functions (continued)
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
Concatenates the first character value to the second
character value; equivalent to concatenation operator (||)
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:
CONCAT('Hello', 'World')HelloWorld
SUBSTR('HelloWorld', 1, 5)
SUBSTR('HelloWorld', -5, 3)
SUBSTR('HelloWorld', 3)
INSTR('HelloWorld', 'W')      // 몇번째에 있느냐6
LPAD(salary, 10, '*')*****24000
RPAD(salary, 10, '*')24000*****
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
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,
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


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 : 

* : 필수
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 - 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


티스토리 툴바