네이버의 yura000님 께서 훌륭하게 정리하신 글이 있기에 퍼왔습니다.
보는데 편하도록 글 배치와 색상은 약간 수정했습니다.
제 1 장 데이터의 검색
SQL 명령어는 다음과 같이 기술한다.
■ SQL 명령어는 한 줄 혹은 여러 줄에 기술한다.
■ 일반적으로 절들은 수정하기 쉽게 다른 줄에 기술한다.
■ TAB 을 사용할 수 있다.
■ SQL 명령어 단어는 생략하거나 분리할 수 없다.
■ SQL 명령어는 대소문자를 구분하지 않는다.
■ SQL 명령어는 ; 으로 종료한다.
■ SQL 명령어는 SQL BUFFER 에 저장된다.
■ SQL BUFFER 에 저장된 SQL 명령어는 / 혹은 RUN 으로 실행할 수 있다. SQL*PLUS 명령어는 다음과 같이 기술한다.
■ SQL*PLUS 명령어는 기본적으로 한 줄에 기술한다.
■ SQL*PLUS 명령어는 대소문자를 구별하지 않는다.
■ SQL*PLUS 명령어는 SQL BUFFER 에 저장되지 않는다.
■ SQL*PLUS 명령어는 다음과 같다.
• DESCRIBE table명 : TABLE 의 구조를 보여준다.
• SAVE file명 : SQL BUFFER 를 file 로 저장한다.
• START file명 : file 을 수행한다.
• @ file명 : file 을 수행한다.
• EDIT file명 : EDITOR 를 사용하여 file 을 편집한다.
• SPOOL file명 : QUERY 결과를 file 에 저장한다.
• SPOOL OFF : SPOOL FILE 을 닫는다.
• HOST : SQL*PLUS 를 떠나지 않고 HOST 상태로 간다.
• HELP 명령어 : SQL, SQL*PLUS, PL/SQL 에 대한 HELP 를 보내준다.
• EXIT : SQL*PLUS 를 종료한다.
1.SELECT 문장의 형식
SELECT 절에는 검색하고 싶은 COLUMN 명들을 기술한다..
FROM 절에는 SELECT 절에서 기술된 COLUMN 명들이 포함된 TABLE 명을 기술한다.
TABLE 의 모든 ROW 와 모든 COLUMN 을 검색한다.
SELECT *FROM table명 ;
[ 예제 ]S_DEPT TABLE 로부터 모든 ROW 와 COLUMN 을 검색하시오.
SELECT *FROM S_DEPT ;
특정 column의 검색SELECT 절에서 검색하고자 하는 COLUMN 명을 COMMA 를 사용하여 나열한다.
COLUMN 순서는 검색하고 싶은 순서대로 나열한다.
COLUMN HEADING 은 COLUMN 명이 대문자로 출력된다.
SELECT column명, column명, column명,..FROM table명 ;
[ 예제 ]S_EMP TABLE 로부터 ID, LAST_NAME, START_DATE 를 검색하시오.
SELECT ID, LAST_NAME, START_DATEFROM S_EMP ;
2.산술식을 사용한 검색산술 연산자를 사용하여 검색되는 데이타 값을 변경
산술 연산식은 COLUMN 명, 상수 값, 산술 연산자로 구성된다.
SELECT 산술연산식FROM table명 ;
[ 예제 ]S_EMP TABLE 로부터 ID, LAST_NAME, 연봉을 검색하시오.연봉은 SALARY * 12 로 계산한다.(+,-,*,/,())SELECT ID, LAST_NAME, SALARY * 12 FROM S_EMP ;
Column alias기본적으로 COLUMN HEADING 은 COLUMN 명이 대문자로 출력된다.
그러나 많은 경우 COLUMN 명이 이해하기 어렵거나 무의미하기 때문에 COLUMN ALIAS 를 사용하여 COLUMN HEADING 을 변경할 수 있다. ANSI SQL 92 와 호환을 위해 ALIAS 앞에 AS 를 붙일 수 있다.ALIAS 에 공백이나 특수문자가 포함되거나 대소문자를 구별하고 싶으면 " " 로 막아준다.COLUMN ALIAS 를 사용하여 COLUMN HEADING 을 변경할 수 있다.
SELECT column명 alias, column명 "alias", column명 as alias FROM table명 ;
[ 예제 ]S_EMP TABLE 에서 LAST_NAME, (SALARY + 100) * 12, DEPT_ID 를 검색하시오.단, COLUMN ALIAS 는 Employee, ANNUAL_SALARY, DEPARTMENT NO 로 정의하시오.
SELECT LAST_NAME "Employee", (SALARY + 100) * 12 AS ANNUAL_SALARY,DEPT_ID "DEPARTMENT NO" FROM S_EMP ;
Column의 결합COLUMN 과 다른 COLUMN, 산술연산식, 상수 값과 결합하여 하나의 COLUMN 으로 결합할 수 있다.
SELECT column명 column명FROM table명;
[ 예제 ]S_EMP TABLE 에서 FIRST_NAME 과 LAST_NAME 을 결합하여 ALIAS EMPLOYEE 로 검색하시오.
SELECT FIRST_NAME LAST_NAME EMPLOYEEFROM S_EMP ;
3. Null값 처리
특정 COLUMN 에 값이 입력되어 있지 않을 때, 그 값을 NULL 이라 부른다.
NULL 값은 0 이나 공백과 같지 않다. NULL 값이 산술 연산식에 포함되면 그 결과도 NULL 이다.
그러므로 NVL FUNCTION 을 사용하여 NULL 값을 다른 값으로 대체하여야 한다.
NULL 값을 다른 값으로 대체한다.
NVL (number_column, 9) NVL (date_column, '01-JAN-95') NVL (character_column, 'ABCDE')
[ 예제 ]S_EMP TABLE 에서 LAST_NAME, COMMISSION 값을 검색하시오.
COMMISSION 은 SALARY * COMMISSION_PCT /100 으로 계산하시오.
SELECT LAST_NAME, SALARY * NVL(COMMISSION_PCT,0) /100 COMMISSIONFROM S_EMP ;
4.중복 row의 제거
SELECT 결과 값에 중복된 값이 있을 때 중복을 피하고 UNIQUE 하게 검색한다.
중복된 ROW 를 제거한다.
SELECT DISTINCT column명, column명FROM table명;
[ 예제 ]S_DEPT TABLE 에서 NAME 이 중복되지 않게 검색하시오.
SELECT DISTINCT NAMEFROM S_DEPT ;
데이타의 정렬SELECT 되는 ROW 의 순서는 알 수 없다.
그러므로 ROW 를 SORT 하고 싶으면 ORDER BY 절을 사용하여야 한다.
DATA 의 DEFAULT SORT 순서는 ASCENDING 이며 다음과 같다.
• 숫자 : 1 에서 999 순으로 SORT 한다.
• 날짜 : 01-JAN-92 에서 01-JAN-95 순으로 SORT 한다.
• 문자 : A 에서 Z 순서로 SORT 한다.
• NULL : ASC 순에서는 뒤에, DESC 순에서는 앞에 나온다.
• 역순으로 SORT 하고 싶으면 COLUMN 명 뒤에 DESC 를 붙인다.
• COLUMN 명 대신에 ALIAS 혹은 SELECT 한 COLUMN 의 순서로 지정할 수도 있다.
SELECT exprFROM table명ORDER BY {column명, expr} [ASCDESC] ;
[ 예제 ]S_EMP TABLE 에서 LAST_NAME, DEPT_ID, START_DATE 를 LAST_NAME 순으로 검색하시오.
SELECT LAST_NAME, DEPT_ID, START_DATEFROM S_EMP ORDER BY LAST_NAME ;
5.특정 row의 검색
WHERE 절에서 조건식을 기술하여 조건을 만족하는 ROW 만 검색할 수 있다.
조건식은 COLUMN 명, COMPARISON OPERATOR, VALUE 로 구성되어 있다.
문자 값은 ' ' 으로 묶어주고 값의 대소문자를 구별하여 적어준다.
날짜 값은 ' ' 으로 묶어주고 지정된 날짜 형태로 적어준다. '01-MAR-97' 숫자값은 값만 적어준다.
특정 ROW 만 검색한다.
SELECT exprFROM table명WHERE expr operator value;
[ 예제 ]S_EMP TABLE 에서 LAST_NAME 이 Magee 인 사원의 FIRST_NAME, LAST_NAME,TITLE 을 검색하시오. (=,>,<,>=,<=,<>)
SELECT FIRST_NAME, LAST_NAME, TITLEFROM S_EMPWHERE LAST_NAME = 'Magee' ;
6.Between...and
BETWEEN OPERATOR 를 사용하여 범위를 지정할 수 있다.
범위를 지정할 때는 작은 값을 먼저 큰 값을 나중에 지정한다. 두 범위의 한계 값을 포함한다. BETWEEN...AND...
NOT BETWEEN...AND...
[ 예제 ]S_EMP TABLE 에서 START_DATE 가 09-MAY-91 에서 17-JUN-91 사이에 입사한 사원의 FIRST_NAME, LAST_NAME, START_DATE 를 검색하시오.
SELECT FIRST_NAME, LAST_NAME, START_DATE FROM S_EMP
WHERE START_DATE BETWEEN '09-MAY-91' AND '17-JUN-91' ;
7.In[list]
IN OPERATOR 를 사용하여 나열된 값들 중에서 값을 검사한다.
IN(LIST), NOT IN(LIST)
[ 예제 ]S_EMP TABLE에서 DEPT_ID 가 10 , 31, 41 혹은 50 인 사원의 FIRST_NAME,LAST_NAME, DEPT_ID 를 검색하시오.
SELECT FIRST_NAME, LAST_NAME, DEPT_IDFROM S_EMPWHERE DEPT_ID IN (10, 31, 41, 50) ;
8.like
찾고자 하는 값을 정확히 모를 때, LIKE OPERATOR 를 사용하여 문자형태가 같은 ROW 를 검색한다.
WILDCARD 를 사용하여 문자의 형태를 지정한다.
% : 여러 문자,
_ : 한문자
LIKE '형태', NOT LIKE '형태'
[ 예제 ]S_EMP TABLE에서 LAST_NAME 이 M 으로 시작하는 사원의 LAST_NAME 을 검색하시오.
SELECT LAST_NAME FROM S_EMPWHERE LAST_NAME LIKE 'M%' ;
SELECT LAST_NAME FROM S_EMPWHERE LAST_NAME LIKE '__M____' ;
9.is nullIS
NULL OPERATOR을 사용하여 값이 NULL 인 것을 찾을 수 있다.
NULL 값은 값이 정의되지 않은 것을 의미하기 때문에 = OPERATOR를 사용하여 어떤 값과 비교할 수 없기 때문에 사용한다.
IS NULL, IS NOT NULL
[ 예제 ]S_EMP TABLE에서 COMMISSION_PCT 가 NULL 인 사원의 LAST_NAME, SALARY,COMMISSION_PCT 를 검색하시오.
SELECT last_name, salary,commission_pct,last_name, salaryFROM s_empWHERE commission_pct is null;
10.And와 Or
조건식의 결합조건식을 기술할 때 AND 와 OR 를 사용하여 여러가지 조건을 결합할 수 있다.
AND 와 OR 가 같이 사용됐을 때 AND 가 먼저 수행되고 OR 가 나중에 수행된다.
그러므로 우선순위를 바꾸고자 하면 ( ) 를 사용한다.
WHERE 조건식 AND OR 조건식
[ 예제 ]S_EMP TABLE에서 DEPT_ID 가 41 이고 TITLE 이 Stock Clerk 인 사원의 LAST_NAME, SALARY, DEPT_ID, TITLE 을 검색하시오.
SELECT LAST_NAME, SALARY, DEPT_ID, TITLEFROM S_EMP WHERE DEPT_ID = 41AND TITLE = 'Stock Clerk' ;
11.Single Row Functions
1)먼저, LOWER를 알아보자.
소문자로 변환,모든 문자를 소문자로 변환시킨다.
LOWER(COLUMN명)
[ 예제 ]S_EMP TABLE 에서 LAST_NAME 이 Smith 인 사원의 FIRST_NAME, LAST_NAME 을 소문자로 출력시키시오.
SELECT LOWER(FIRST_NAME), LOWER(LAST_NAME) FROM S_EMP WHERE LOWER(LAST_NAME) = 'smith' ;
2)이번엔, 대문자로 변환인 UPPER를 보자.
모든 문자를 대문자로 변환시킨다.
UPPER(COLUMN명)
[ 예제 ]S_EMP TABLE 에서 LAST_NAME 이 Smith 인 사원의 TITLE 을 대문자로 출력하시오.
SELECT UPPER(TITLE) FROM S_EMP WHERE UPPER(LAST_NAME) = 'SMITH';
3)INITCAP
첫글자만 대문자로 변환, 단어의 첫글자는 대문자로, 나머지는 소문자로 변환시킨다.
INITCAP(COLUMN명)
[ 예제 ]S_EMP TABLE 에서 TITLE 을 단어의 첫글자만 대문자로 출력시키시오.
SELECT INITCAP(TITLE)FROM S_EMP ;
4)SUBSTR
문자의 부분을 자름문자를 시작위치(M)에서 자리수(N) 만큼 잘라준다.
자리수(N)이 생략되면 시작위치(M)에서 끝까지 잘라준다.
SUBSTR(COLUMN명, M, N)
[ 예제 ]S_PRODUCT TABLE 에서 NAME COLUMN 의 앞에서 부터 3글자가 Ace 인 제품의 NAME 을 출력하시오.
SELECT NAMEFROM S_PRODUCTWHERE SUBSTR(NAME, 1, 3) = 'Ace' ;
5)Length
문자의 길이를 계산문자의 길이를 RETURN 한다.
LENGTH(COLUMN명)
[ 예제 ]S_PRODUCT TABLE 에서 NAME, NAME 의 길이를 출력하시오.
SELECT NAME, LENGTH(NAME)FROM S_PRODUCT;
6)Round
숫자의 반올림지정된 자리수(M) 밑에서 반올림한다.
COLUMN 값이 1 2 3 4. 5 6 7 일 때 자리수(M)은 다음과 같다.
M : -3-2-1 0 1 2 3
ROUND(COLUMN명, M)
[ 예제 ]S_EMP TABLE 에서 LAST_NAME, SALARY/22 의 값을 소수 2째 자리까지 나타내고
소수 3째 자리에서 반올림하시오.
SELECT LAST_NAME, ROUND(SALARY/22, 2) FROM S_EMP ;
7)Trunc
숫자의 절사지정된 자리수(M) 까지 나타내고 그 밑은 잘라버린다.
COLUMN 값이 1 2 3 4. 5 6 7 일 때 자리수(M)은 다음과 같다.
M : -3-2-1 0 1 2 3
절사 값은 RETURN 한다.
TRUNC(COLUMN명, M)
[ 예제 ]S_EMP TABLE 에서 LAST_NAME, SALARY / 22 의 값을 십의 자리까지 나타내고 일의 자리는 버림SELECT LAST_NAME, TRUNC(SALARY/22, -1) FROM S_EMP ;
8)나누기의 나머지, MOD
M 을 N 으로 나누고 남은 나머지를 RETURN 한다.
MOD(M, N)
[ 예제 ]10 을 3 으로 나눈 나머지를 구하시오.
SELECT MOD(10, 3)FROM SYS.DUAL ;
9)날짜의 연산
DATABASE 안의 DATE 값은 다음과 같은 숫자로 저장되어 있다.
■ CENTURY, YEAR, MONTH, DAY, HOURS, MINUTES, SECONDS그러므로 산술 연산을 할 수 있다.
● DATE + NUMBER : 숫자만큼 일이 더해진 날짜가 RETURN 된다.
● DATE - NUMBER : 숫자만큼 일이 빼진 날짜가 RETURN 된다.
● DATE1 - DATE2 : 두 날짜 사이의 일수가 계산된다.날짜 계산을 한다.
DATE + NUMBERDATE - NUMBERDATE1 - DATE2
[ 예제 ]S_EMP TABLE 에서 LAST_NAME, 입사한지 90 일째 되는 날, 입사한지 며칠 됐는지 검색하시오.
SELECT LAST_NAME, START_DATE + 90, SYSDATE - START_DATE FROM S_EMP;
( 날짜에는 시간도 포함되어 있으므로 일수 계산의 결과가 소수로 나온다. )
날짜 사이의 개월 수두 날짜 사이의 개월 수를 RETURN 한다.
MONTHS_BETWEEN(DATE1, DATE2)
[ 예제 ]S_EMP TABLE 에서 LAST_NAME, 입사한지 몇 달이 됐는지 출력하시오.
SELECT LAST_NAME, MONTHS_BETWEEN(SYSDATE, START_DATE)FROM S_EMP ;
(일이 포함되어 있어서 소수로 출력된다.)
날짜에 달을 더함날짜에서 숫자(N) 개월만큼 더해진 날짜를 RETURN 한다.
ADD_MONTHS(DATE, N)
[ 예제 ]S_EMP TABLE 에서 LAST_NAME, START_DATE, 입사한지 3 개월되는 날짜를 출력하시오.
SELECT LAST_NAME, START_DATE, ADD_MONTHS(START_DATE, 3) FROM S_EMP ;
지정한 요일 날짜날짜에서 지정한 요일(CHAR)이 될 날짜를 RETURN 한다.
NEXT_DAY(DATE, 'CHAR')
[ 예제 ]오늘을 기준으로 돌아오는 금요일이 언제인지 출력하시오.
SELECT SYSDATE, NEXT_DAY(SYSDATE, 'FRIDAY') FROM SYS.DUAL ;
그 달의 마지막 날 날짜가 포함된 달의 마지막 날을 RETURN 한다.
LAST_DAY(DATE)
[ 예제 ]이번 달의 마지막 날은 언제인지 출력하시오.
SELECT SYSDATE, LAST_DAY(SYSDATE) FROM SYS.DUAL ;
날짜의 반올림형태에 따른 반올림 기준은 다음과 같다.
• YEAR : 6월 이후• MONTH : 15일 이후
• DAY : 12시 이후날짜 데이타를 지정된 형태까지 나타내고 그 이하에서 반올림한다.
ROUND(COLUMN명, '형태')
[ 예제 ]S_EMP TABLE 에서 ID, LAST_NAME, 입사 시작 달을 검색하시오.
단, 15일 이후는 다음달로 올리시오.
SELECT ID, LAST_NAME, ROUND(START_DATE, 'MONTH') FROM S_EMP ;
날짜의 절사날짜 데이타를 지정된 형태까지 나타내고 그 밑은 잘라낸다.
TRUNC(COLUMN명, '형태')
[ 예제 ]S_EMP TABLE 에서 ID, LAST_NAME, 입사 시작 달을 검색하시오.단, 일자는 잘라버리시오.
SELECT ID, LAST_NAME, TRUNC(START_DATE, 'MONTH') FROM S_EMP ;
문자를 날짜로 변환, CHARACTER TYPE 을 지정된 형태의 DATE TYPE 으로 변환한다.
TO_DATE(character_column명, '형태')
[ 예제 ]S_EMP TABLE 에서 LAST_NAME, START_DATE 를 검색하시오.
단, START_DATE 의 값이 92/02/07 인 사원을 검색하시오.
SELECT LAST_NAME, START_DATEFROM S_EMP WHERE START_DATE = TO_DATE('92/02/07', 'YY/MM/DD') ;
날짜를 문자로 변환DATE 값은 기본적으로 DD-MON-YY 형태로 출력된다.
이것을 TO_CHAR FUNCTION 을 사용하면 원하는 다른 형태로 변환할 수 있다.
■ 형태를 지정할 때 사용된 대소문자로 출력된다.
■ DAY 와 MONTH 형태는 공백을 포함한 9 자리로 출력된다.
■ TO_CHAR 의 결과는 80 자리로 출력된다.
DATE TYPE 을 지정된 형태의 CHARACTER TYPE 으로 변환한다.
TO_CHAR(date_column, '형태')
[ 예제 ]S_EMP TABLE 에서 LAST_NAME, START_DATE 를 검색하시오.
단, START_DATE 의 형태는 1991/06/17 14:20:00 와 같이 출력하시오.
SELECT LAST_NAME, TO_CHAR(START_DATE, 'YYYY/MM/DD HH24:MI:SS'), START_DATEFROM S_EMP ;
숫자를 문자로 변환, NUMBER TYPE 을 지정된 형태의 CHARACTER TYPE 으로 변환한다.
TO_CHAR(number_column명, '형태')
[ 예제 ]S_EMP TABLE 에서 LAST_NAME, SALARY 를 검색하시오.
단 SALARY 를 $1,450 와 같은 형태로 출력시키시오.
SELECT LAST_NAME, TO_CHAR(SALARY, '$999,999') FROM S_EMP ;
12.여러Table로부터 Data검색
1)Equijoin
SIMPLE JOIN (EQUI-JOIN)
여러개의 TABLE 들로부터 정보를 검색하려면, SELECT 문장의 FROM 절에 TABLE명들을 적고 WHERE 절에 각 TABLE의 ROW들을 연결시킬 조건식을 기술한다.
각 TABLE 의 COLUMN명이 중복될 때는 반드시 COLUMN명 앞에 TABLE명을 붙여야 한다.
(중복되지 않을 때는 붙이지 않아도 되지만 명확성을 위해서나 ACCESS 를 위해서 붙이는 것이 좋다.)
N 개의 TABLE 을 JOIN 할 때는 최소한 N-1 개의 조건식이 필요하다.
복합 COLUMN 으로 JOIN 할 때는 더 많은 조건식이 필요하다.
2개 이상의 TABLE 에서 = 조건식을 만족시키는 ROW 들을 연결하여 검색한다.
SELECT table명.column명, table명.column명...FROM table1명, table2명
WHERE table1명.column1명 = table2명.column명 ;
[ 예제 ]S_EMP TABLE 과 S_DEPT TABLE 을 사용하여 사원들의 LAST_NAME, DEPT_ID,NAME 을 검색하시오.
SELECT S_EMP.LAST_NAME, S_EMP.DEPT_ID, S_DEPT.NAME FROM S_EMP, S_DEPT
WHERE S_EMP.DEPT_ID = S_DEPT.ID ;
2)특정 row의 join
JOIN 문장을 기술할 때 JOIN 조건식 이외에 다른 조건식을 AND 로 연결할 수 있다.
SELECT table명.column명, table명.column명...FROM table1명, table2명
WHERE table1명.column1명 = table2명.column2명 AND condition ;
[ 예제 ]S_EMP TABLE과 S_DEPT TABLE 을 사용하여 LAST_NAME 이 Menchu 인 사원의 LAST_NAME, DEPT_ID, NAME 을 검색하시오.
SELECT S_EMP.LAST_NAME, S_EMP.DEPT_ID, S_DEPT.NAME FROM S_EMP, S_DEPT
WHERE S_EMP.DEPT_ID = S_DEPT.ID AND S_EMP.LAST_NAME = 'Smith' ;
3)Table alias
JOIN 문장에서 TABLE명이 긴 경우 TABLE명.COLUMN명 으로 적는 것이 매우 불편하다.
그런데 TABLE명 대신 ALIAS 를 사용하면 편하게 사용할 수 있다.
(SELECT 문장에서 TABLE명 대신 ALIAS 를 지정했다면 그 문장에서는 계속해서ALIAS 로 사용하여야 한다.)TABLE ALIAS를 사용하여 JOIN 문장을 간단하게 기술한다.
SELECT alias명.column명, alias명.column명 FROM table1명 alias1명, table2명 alias2명
WHERE alias1명.column1명 = alias2명.column2명 ;
[ 예제 ]S_CUSTOMER TABLE과 S_REGION TABLE 을 사용하여 고객 명,지역번호,지역 명을 검색하시오.
단, COLUMN ALIAS 와 TABLE ALIAS 를 사용하시오.
SELECT C.NAME "Customer Name", C.REGION_ID "Region Id",R.NAME "Region Name"
FROM S_CUSTOMER C, S_REGION R WHERE C.REGION_ID = R.ID ;
4)Non-Equijoin
NON-EQUIJOIN
JOIN 문장에서 두 TABLE 을 JOIN 하는 조건식에 = OPERATOR 가 사용되지 않고 다른 OPERATOR 가 사용되는 것을 말한다.
SELECT table명.column명, table명.column명...FROM table1명, table2명 WHERE 조인조건식 ;
[ 예제 ]EMP TABLE 과 SALGRADE TABLE 을 사용하여 사원의 ENAME, JOB, SAL,GRADE를 검색하시오.
SELECT E.ENAME, E.JOB, E.SAL, S.GRADE FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL ;
(BETWEEN OPERATOR 대신에 <= 와 >= 를 사용해도 되지만 BETWEEN 이 간편하다.)
5)Outer Join
두 TABLE 을 JOIN 할 때 JOIN 조건식을 만족시키지 못하는 ROW 는 검색에서 빠지게 된다.
그런데 이러한 ROW 들이 검색되도록 하는 것이 OUTER JOIN 이다.
(+),즉 OUTER JOIN OPERATOR 를 데이타가 없는 어느 한쪽의 COLUMN 쪽에 붙인다.
JOIN 결과, 데이타가 없는 쪽의 COLUMN 값은 NULL로 검색된다.
조건식을 만족시키지 못하는 데이타도 검색한다.
SELECT table명.column명, table명.column명
FROM table1명, table2명 WHERE table1명.column1명 = table2명.column2명(+)
[ 예제 ]S_EMP TABLE 과 S_CUSTOMER TABLE 을 사용하여 영업사원의 LAST_NAME,SALES_REP_ID, NAME 을 검색하시오.단, 영업사원이 정해져 있지 않은 고객의 이름도 검색하시오.
SELECT E.LAST_NAME, C.SALES_REP_ID,C.NAMEFROM S_EMP E, S_CUSTOMER CWHERE E.ID(+) = C.SALES_REP_ID ;
6)Self Join
TABLE 의 ALIAS 를 사용하여 마치 2 개의 TABLE 처럼 생각하여 자신의 TABLE 과 자신의 TABLE 을 JOIN 한다.
SELECT alias명.column명, alias명.column명...FROM table명 alias1명, table명 alias2명
WHERE alias1명.column1명 = alias2명.column2명 ;
[ 예제 ]S_EMP TABLE 에서 사원들의 LAST_NAME 과 그들의 상사 LAST_NAME 을 검색하시오.
SELECT W.LAST_NAME "Woker", M.LAST_NAME "Manager" FROM S_EMP W, S_EMP M
WHERE W.MANAGER_ID = M.ID ;
13.Group FunctionsGroup Function
각각의 FUNCTION 은 ARGUMENT 를 받는데 기능은 다음과 같다.
■ DISTINCT : 중복된 값은 제외한다.
■ ALL : DEFAULT 로써 모든 값을 포함한다.
■ COLUMN명 : NULL 값은 제외한다.
■ * : NULL 값도 포함한다.
1)TABLE 전체를 하나의 GROUP 으로 보고 GROUP FUNCTION 값을 RETURN 한다.
SELECT group_function(column명), group_function(column명)...FROM table명 ;
[ 예제 ]S_EMP TABLE 에서 회사 전체의 급여합계, 최고급여, 최소급여, 인원수를 검색하시오.
SELECT SUM(SALARY), MAX(SALARY), MIN(SALARY), COUNT(SALARY)FROM S_EMP ;
-COUNT(SALARY)는 급여를 받는 사원의 총 인원수고 COUNT(*) 는 급여를 받지 않는 사원의 인원수도 포함된다.)
2)소group으로 분리
기본적인 SELECT 절(그룹화 되지 않은 SELECT절)에는 COLUMN 명과 GROUP FUNCTION이 같이 기술될 수 없다.SELECT 절에 COLUMN 명이 기술되려면 GROUP BY 절이 반드시 기술되어야 한다.
SELECT 절에 기술된 COLUMN 명들은 전부 GROUP BY 절에 기술되어야 하며 GROUP BY 절에 기술된 COLUMN 명들은 SELECT 절에 기술되지 않아도 된다.(하지만 결과를 파악하기 위해서는 SELECT 절에 기술해주는 것이 좋다.)GROUP BY 절을 기술하면 GROUP BY 절에 기술된 COLUMN 값으로 1 개의 TABLE이 소 GROUP 으로 나눠진다. 결과는 COLUMN 값으로 SORT 되어서 출력된다.
1 개의 TABLE 을 소 GROUP 으로 나누어 GROUP FUNCTION 값을 구한다.
SELECT column1명[, column2명], group_function(column명)
FROM table명 GROUP BY column1명[, column2명] ;
[ 예제 ]S_EMP TABLE 에서 DEPT_ID, TITLE 별로, 최고급여, 최소급여, 인원수를 검색하시오.
SELECT DEPT_ID, TITLE, MAX(SALARY), MIN(SALARY), COUNT(SALARY)
FROM S_EMPGROUP BY DEPT_ID, TITLE;
3)특정 group의 선택
HAVING 절이 기술됐을 때 처리되는 순서는 다음과 같다.
① ROW 들이 GROUPing 된다.
② GROUP 에 대해 GROUP FUNCTION 이 적용된다.
③ HAVING 절을 만족하는 GROUP 을 선택한다.
그러므로 GROUP BY 절과 HAVING 절의 순서는 바뀌어도 되지만 의미상 GROUP BY절 다음에 HAVING 절을 기술하는 것이 좋다.HAVING 절에서는 GROUP FUNCTION 을 사용하여 GROUP 에 대한 조건식을 기술한다. SELECT column1명[, column2명], group_function(column명)
FROM table명GROUP BY column1명[, column2명] HAVING 그룹조건식 ;
[ 예제 ]S_EMP TABLE 에서 TITLE 별로 급여합계를 검색하시오.
단, 급여합계가 5000 이상인 GROUP 만 출력하시오.
SELECT TITLE, SUM(SALARY) PAYROLL FROM S_EMP GROUP BY TITLE HAVING SUM(SALARY) >= 5000 ;
4)Group의 정렬
기본적으로 GROUP BY 절에 기술된 COLUMN 값으로 SORT 된다.
이 순서를 바꾸고자 하면 ORDER BY 절을 기술하면 된다. DATA 의 SORT 순서를 정한다.
SELECT column1명[, column2명], group_function(column명)
FROM table명 GROUP BY column1명[, column2명] ORDER BY column명 group_function(column명) ;
[ 예제 ]S_EMP TABLE에서 DEPT_ID 별로 인원수를 검색하시오.
단, 인원수가 많은 부서부터 출력하시오.
SELECT DEPT_ID, COUNT(*)FROM S_EMPGROUP BY DEPT_IDORDER BY COUNT(*) DESC ;
14.Subquery
1)Single Row Subquery
SUBQUERY 의 결과가 1 개의 ROW 로 나오는 것을 SINGLE ROW SUBQUERY 라 하며 다음과 같은 OPERATOR 를 사용할 수 있다.=, >, >=, <, <=VALUE 값을 구하기 위해 SELECT 문장을 사용한다.
SELECT column명, column명...FROM table명 WHERE column명 operator
(SELECT column명 FROM table명 WHERE 조건식 );
[ 예제 ]S_EMP TABLE 에서 LAST_NAME 이 Smith 인 사원과 같은 업무(TITLE)를 하고 있는 사원의 LAST_NAME, TITLE 을 검색하시오.
SELECT LAST_NAME, TITLEFROM S_EMP
WHERE TITLE = ( SELECT TITLE FROM S_EMPWHERE LAST_NAME = 'Smith') ;
2)From절의 Subquery
FROM 절에 기술된 SUBQUERY 문은 VIEW 처럼 사용된다.
SELECT alias명.column명, alias명,column명...
FROM table1명 alias1명, (SELECT column2명 FROM table2명WHERE 조건식) alias2명
WHERE alias1명.column1명 OPERATOR alias2명.column2명 ;
[ 예제 ]S_EMP TABLE 에서 SALARY 가 회사평균급여 보다 적은 사원의 LAST_NAME,SALARY, 회사평균급여를 검색하시오.
SELECT E.LAST_NAME, E.SALARY, S.AVGSAL FROM S_EMP E,
(SELECT AVG(SALARY) AVGSAL FROM S_EMP) S WHERE E.SALARY <>
3)Multi Row Subquery
SUBQUERY 의 결과가 여러 ROW 일 때는 반드시 IN OPERATOR 를 사용하여야 한다.
SELECT column명, column명...FROM table명 WHERE column명
IN ( SELECT column명 FROM table명WHERE 조건식);
[ 예제 ]S_EMP TABLE 과 S_DEPT TABLE 에서 Operations 부서에서 근무하는 사원의 LAST_NAME, TITLE, DEPT_ID 를 검색하시오.
SELECT LAST_NAME, TITLE, DEPT_ID FROM S_EMP WHERE DEPT_ID
IN (SELECT IDFROM S_DEPTWHERE NAME = 'Operations') ;
4)Multi Column Subquery
SELECT 문장의 WHERE 절에서 여러개의 COLUMN 값을 비교하려면 LOGICALOPERATOR 를 사용하여 여러개의 조건식을 기술하여야 한다.그런데 MULTI COLUMN SUBQUERY 를 사용하면 이를 해결할 수 있다.
SELECT column명, column명,,,FROM table명 WHERE (column명, column명...)
IN (SELECT column명, column명...FROM table명 WHERE 조건식);
[ 예제 ]S_EMP TABLE 에서 LAST_NAME Patel 인 사원과 같은 부서, 같은 업무를맡고 있는 사원의 LAST_NAME, TITLE, DEPT_ID 를 검색하시오.
SELECT LAST_NAME, TITLE, DEPT_IDFROM S_EMP WHERE (DEPT_ID, TITLE)
IN(SELECT DEPT_ID, TITLE FROM S_EMP WHERE LAST_NAME = 'Patel') ;
SELECT LAST_NAME, TITLE, DEPT_IDFROM S_EMP WHERE (DEPT_ID)
IN(SELECT DEPT_IDFROM S_EMP WHERE LAST_NAME = 'Patel')
OR (TITLE) IN(SELECT TITLE FROM S_EMPWHERE LAST_NAME = 'Patel') ;
15.Table 생성이름 붙이는 법
이름은 다음의 규칙을 따라서 지정한다.
■ TABLE 명이나 COLUMN 명은 문자로 시작하고 30 자 이내로 지정한다.
■ A ~ Z, a ~ z, 0 ~ 9, _ , $ , # 을 사용할 수 있다.
■ 한 USER 내에서는 다른 OBJECT 의 이름과 동일하게 지정할 수 없다.
■ ORACLE7 SERVER 예약어를 사용할 수 없다.
■ 대소문자를 구별하지 않는다.
16.Oracle 7 datatype
COLUMN 의 DATATYPE 은 다음과 같다.
■ CHAR(size) : 고정된 size 의 문자 값, 최대 255 자까지 지정할 수 있다.
■ VARCHAR2(size) : size내에서의 가변길이 문자 값,최대 2000자까지 지정할 수 있다.
■ LONG : 가변길이 문자 값, 최대 2 기가 바이트까지 사용할 수 있다. TABLE 당 한 개의 COLUMN 에만 지정 가능하다.
■ NUMBER(p,s) : 정수, 소수 자리수의 합이 P, 소수 자리수가 s 인 숫자값, 최대 38 자리수까지 지정할 수 있다.
■ DATE : 날짜와 시간 값, B.C. 4712년 1월 1일부터 A.D. 4712년 12월 31일까지 입력할 수 있다.
■ RAW(size) : size 내에서의 가변길이 BINARY DATA
■ LONGRAW : 가변길이 BINARY DATA
17.다른 table로부터 table생성
기존하는 TABLE 로 부터 데이타와 구조를 복사하여 TABLE 을 생성한다.
CREATE TABLE table명 [(column명, column명...)]AS subquery ;
[ 예제 ]S_EMP TABLE 에서 DEPT_ID 가 41 인 사원들의 ID, LAST_NAME, USERID,START_DATE 만을 복사하여 EMP_41 TABLE 을 생성하시오.
CREATE TABLE EMP_41AS SELECT ID, LAST_NAME, USERID, START_DATEFROM S_EMPWHERE DEPT_ID = 41;(S_EMP TABLE 에서 COLUMN명, TYPE, SIZE, NOT NULL CONSTRAINT 가 복사되어 EMP_41 TABLE 이 생성되며, 데이타는 DEPT_ID = 41 인 ROW 만 복사된다.)
18.Constraint
CONSTRAINT의 종류는 다음과 같다.
■ NOT NULL
COLUMN 에 NULL 값이 입력되는 것을 허용하지 않는다.COLUMN-CONSTRAINT 로만 기술해야 한다.
■ UNIQUE
한 개의 COLUMN 혹은 복합 COLUMN 을 UNIQUE KEY 로 지정한다.UNIQUE KEY 에는 중복된 값을 허용하지 않는다.한개의 COLUMN 으로 구성된 UNIQUE KEY 는 NULL 값을 허용한다.COLUMN 이나 TABLE-CONSTRAINT 로 기술할 수 있다.복합 COLUMN 으로 구성된 UNIQUE KEY 는 TABLE-CONSTRAINT 로만 기술해야 한다.UNIQUE KEY COLUMN 의 UNIQUE INDEX FILE 이 자동 생성된다.
■ PRIMARY KEY
ROW 를 UNIQUE 하게 대표할 수 있는 한개의 COLUMN 혹은 복합 COLUMN 으로 지정한다.PRIMARY KEY 에는 중복된 값과 NULL 값을 허용하지 않는다.TABLE 당 한 개의 PRIMARY KEY 만 지정할 수 있다.COLUMN 이나 TABLE-CONSTRAINT 로 기술할 수 있다.복합 COLUMN 으로 구성된 PRIMARY KEY 는 TABLE-CONSTRAINT 로만 기술해야 한다.PRIMARY KEY COLUMN 의 UNIQUE INDEX FILE 이 자동 생성된다.
■ FOREIGN KEY
한개의 COLUMN 혹은 복합 COLUMN 으로 지정한다.
같은 TABLE 혹은 다른 TABLE의 PRIMARY KEY 나 UNIQUE KEY 값을 참조한다.
FOREIGN KEY 값은 모 TABLE 에 존재하는 데이타와 같던가 NULL 값을 허용한다.
COLUMN 이나 TABLE-CONSTRAINT 로 기술할 수 있다.
※ CHECK : 각각의 ROW 가 만족해야할 조건을 지정한다.조건식은 QUERY 조건식과 동일하게 지정한다.
단, 다음과 같은 것은 사용할 수 없다.
CURRVAL, NEXTVAL, LEVEL, ROWNUM, SYSDATE, USER COLUMN 이나TABLE-CONSTRAINT 로 기술할 수 있다.
CONSTRAINT 명은 다음과 같이 지정한다.
• CONSTRAINT 는 DICTIONARY 에 저장되므로 참조하기 쉽게 의미있게 붙여준다.
• 일반적으로 TABLE명_COLUMN명_CONSTRAINT종류와 같은 형태로 지정한다.
• 사용자가 CONSTRAINT 명을 지정하지 않으면 ORACLE7이 SYS_Cn의 형태로 붙인다.
• 동일한 USER 내에서 CONSTRAINT명은 UNIQUE해야 한다.
CONSTRAINT 는 다음과 같이 기술할 수 있다.
COLUMN-CONSTRAINT : column명 [CONSTRAINT constraint명] constraint종류
TABLE-CONSTRAINT : [CONSTRAINT constraint명] constraint종류(column명, column명..)
19.Table 생성
CREATE TABLE table명
(column명 type(size) [DEFAULT VALUE] [column constraint],
column명 type(size) [DEFAULT VALUE] [column constraint],.... ,
[table constraint] ,[table constraint] ,.... ) ;
[ 예제 ]
20.Data DICTIONARY
DICTIONARY
• DATABASE 가 만들어 졌을때 DICTIONARY TABLE 도 만들어 진다.
• DATABASE 가 사용중일때 DICTIONARY TABLE 은 ORACLE7 SERVER 에 의해 UPDATE 된다.
• 사용자들은 DICTIONARY TABLE 을 SELECT 할 수 있다.
• DICTIONARY TABLE 은 SYS USER 의 소유다.
• DICTIONARY TABLE 의 값은 대문자로 들어있다.
• DICTIONARY TABLE 의 종류는 다음과 같은 방법으로 알 수 있다.
SELECT * FROM DICTIONARY ;
DICTIONARY TABLE 의 종류는 다음과 같다.
• USER : USER 가 소유하고 있는 OBJECT 의 정보를 보여준다.
• ALL : USER 가 ACCESS 할 수 있는 OBJECT 의 정보를 보여준다.
• DBA : DBA USER 가 ACCESS 할 수 있는 OBJECT 의 정보를 보여준다.
활용예
DICTIONARY TABLE 의 검색예는 다음과 같다.
■ 자신이 갖고 있는 TABLE 의 이름을 검색한다.
SELECT OBJECT_NAME FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TABLE';
■ 자신이 갖고 있는 OBJECT 의 종류를 검색한다.SELECT DISTINCT OBJECT_TYPEFROM USER_OBJECTS;
■ GRANT 와 관련된 DICTIONARY TABLE 의 이름을 검색한다.SELECT TABLE_NAMEFROM DICTIONARYWHERE UPPER(COMMENTS) LIKE '%GRANT%';
■ S_EMP TABLE 의 CONSTRAINT 종류를 검색한다.SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, SEARCH_CONDITION,R_CONSTRAINT_NAMEFROM USER_CONSTRAINTSWHERE TABLE_NAME = 'S_EMP';
■ S_EMP TABLE 의 COLUMN CONSTRAINT 를 검색한다.SELECT CONSTRAINT_NAME, COLUMN_NAMEFROM USER_CONS_COLUMNSWHERE TABLE_NAME = 'S_EMP'
