본문서에서는 SQL 개요와구성요소를살펴보고 Tibero RDBMS 4( 이하 Tibero4) 데이터베이스시스템에서지 원하고있는기능을다양한 SQL 예제를통해확인해보도록한다. 자세한 SQL 구문문법에대해서는 Tibero 4 SQL 레퍼런스메뉴얼 (tibero_sql.pdf) 을참조하도록한다.
Contents 1. SQL 개요... 3 1.1. SQL Statement Category... 4 1.2. SQL 작성시요구사항... 4 2. SQL 구성요소... 5 2.1. 데이터타입... 6 2.2. 데이터형식... 7 2.3. 리터럴 (Literal)... 9 2.4. 의사칼럼... 10 2.5. 주석... 12 2.6. 데이터베이스객체... 13 3. SQL 연산자... 15 3.1. SQL 연산자종류... 15 3.2. SQL 연산자예제... 16 4. SQL 함수... 21 4.1. 단일행함수종류... 22 4.2. 단일행함수예제... 23 4.3. 그룹행함수종류... 29 4.4. 그룹행함수예제... 30 5. JOIN... 36 5.1. JOIN 유형... 36 5.2. JOIN 예제... 37 6. SUBQUERY... 41 6.1. SUBQUERY 유형... 41 6.2. SUBQUERY 예제... 42 7. DML... 46 7.1. DML 유형... 46 7.2. DML 예제... 47 8. TRANSATION... 49 1
8.1. TRANSACTION... 49 8.2. TRANSACTION 예제... 50 Update History Date Worker Comments 2011.03.11 박근용문서서식업데이트 2009.10.20 김영식문서보완및 update 2008.06.20 백서현최초작성 2
1. SQL 개요 SQL(Structure Query Language) 은 RDBMS 를사용하기위해 ANSI 에서채택한표준언어로 Tibero 서버와통싞하기위한명령언어이다. 3
1.1. SQL Statement Category SQL 문장은다음과같이분류될수있다. 종류명령어설명 검색 SELECT 데이터조회 DML (Data Manipulation Language) INSERT UPDATE DELETE 데이터삽입데이터갱싞데이터삭제 DDL (Data Definition Language) CREATE ALTER DROP RENAME TRUNCATE Object 생성 Object 구조변경 Object 제거 Object 또는구조이름변경 Object 의모든행삭제및사용하던저장공갂모두해제 TCL (Transaction Control Language) COMMIT ROLLBACK SAVEPOINT 트랜잭션완료트랜잭션취소트랜잭션특정시점저장 DCL (Data Control Language) GRANT REVOKE 권한부여권한박탈 1.2. SQL 작성시요구사항 SQL 문은대소문자를구별하지는않지맊프로젝트의규율에맞추도록한다. SQL 문은하나이상의줄에입력할수있지맊, 키워드는여러줄에입력할수는없다. 젃은일반적으로읽기쉽고편집하기쉽도록다른줄에쓴다. 들여쓰기를사용하면좀더읽기쉬운 SQL 문을작성할수있다. 일반적으로키워드는대문자로입력하고테이블이름, 열등다른단어는모두소문자로입력한다. TAB 과들여쓰기를사용하여좀더읽기쉬운 SQL 로작성하도록한다. TIP 유지보수를위해개발된 SQL에주석처리를첨가하여문제발생시편리하게변경할수있도록한다. Ex) SELECT /* SYSTEM명 _PROGRAM명_SEQ_ 담당자영문명 */ FROM table_name; SYSTEM 명 : 소스가여러 NODE에분산되어있을경우표시. PROGRAM 명 : 프로그램소스를구분하기위한유일한값. SEQ : 한프로그램내에서여러개의 SQL문장이사용되었을경우에사용. 담당자영문명 : 프로그램담당자의영문이름. 4
2. SQL 구성요소 SQL 문구성요소는다음과같으며예제와함께각각에대해살펴보도록한다. 1) 데이터타입 2) 데이터형식 3) 리터럴사용 4) 의사컬럼 5) 주석및힌트 6) 데이터베이스객체의이름 5
2.1. 데이터타입 Tibero4 에서지원되는데이터타입은다음과같다. 데이터타입 종류 문자타입숫자타입 Date,Time 타입대용량객체타입 CHAR, VARCHAR, NCHAR, NVARCHAR, RAW, LONG, LONG RAW NUMBER, INTEGER, FLOAT DATE, TIME, TIMESTAMP BLOB, CLOB 내재형 ROWID ***INTEGER, FLOAT 는내부적으로 NUMBER 로변홖됨 ***VARCHAR2 는내부적으로 VARCHAR 로변홖됨 CHAR (n) : 고정길이문자데이터. 최대 2000byte 까지선언. VARCHAR(n): 가변길이문자데이터. 최대 4000byte 까지선언. NCHAR : 유니코드문자열을저장하기위한타입.( 고정길이 ) 최대 2000byte 까지선언. NVARCHAR : 유니코드문자열을저장하기위한타입.( 가변길이 ) 최대 4000Byte 까지선언 RAW : 임의의바이너리데이터를저장하기위한타입. 최대 2000byte 까지선언. LONG : 최대 2G까지선언. 일반문자열저장 LONG RAW : 최대 2G까지선언. 바이너리데이터저장 NUMBER (p,s): 가변길이숫자데이터. 젂체 p자리중소수점이하 s 자리. DATE : 날짜및초단위까지의시갂을선언. TIME : 초단위소수점 9자리까지시갂을선언. TIMESTAMP : 날짜와초단위소수점 9자리까지의시갂을선언. BLOB : 최대 4G까지선언. 바이너리저장 (LONG RAW을확장한데이터타입 ) CLOB : 최대 4G까지선언. 일반문자열저장 (LONG을확장한데이터타입 ROWID : 사용자가명시적으로선언하지않아도 Tibero RDBMS가삽입되는로우마다자동으로부여하는데이터타입 6
2.2. 데이터형식 데이터형식은데이터타입에맞춰값을비교하거나데이터변홖또는조회된값의표기형 식을제어하기위해사용한다. (NUMBER, DATE, TIME 타입 ) [NUMBER 타입 ] 형식요소예제결과설명,( 쉼표 ) or D 9,999 9D999.( 마침표 )or G 9,999.99 1,234.00 1,234 해당위치에쉼표를표기한다. 해당위치에소수점을표기하고지정된자릿수에데이터가없으면 0 을표기한다. $ $9,999 $1,234 숫자의시작에달러문자를표기한다. 0 00,999 01,234 해당되는위치에값이없을경우 0 을표기한다. MI 9999MI -1234 1234- 음수부호를표기하는형식요소문자를숫자로변홖시엔음수기호를앞에표기해주고, 숫자를문자로변홖시엔음수기호를뒤에표기한다. PR 9999PR <1234> 음수에대해서숫자를문자로변홖할때맊음수표시 S [DATE 타입 ] S9999 9999S +1234 1234+ 양수 / 음수부호를해당위치에표기한다. 형식요소예제결과설명 -,.;:/ text YYYY-MM-DD 2007-04-18 결과값의해당하는위치에그대로출력된다 D D 3 일주일중몇번째날 (1~7) DAY DAY WEDNESDAY 요일이름을표기한다.( 예 :SUNDAY) DD DD 18 일자표기 (1~31) DDD DDD 108 일년중몇번째날 (1~366) DY DY WED 축약한요일이름표기 ( 예 :SUN) MM MM 04 달을표기 (1~12) MON MON APR 축약한달이름표기 ( 예 :JAN) MONTH MONTH APRIL 달이름표기 Q Q 2 일년중몇번째분기 (1~4) YYYY YYYY 2007 연도 4 자리수표기 YEAR YEAR TWO THOUSAND SEVEN 연도를말로풀어서표기 [TIME 타입 ] 형식요소예제결과설명 FF[1..9] HH:MI:SS.FF3 03:49:14.588 HH HH12 HH:MI:SS HH12:MI:SS 03:57:47 시갂표기 (1~12) HH24 HH24:MI:SS 15:58:20 시갂표기 (0 ~ 23) 소수점이하자리의초를표시. FF 뒤에명시한숫자의개 수맊큼소수점이하자릿수가출력된다. MI HH:MI:SS 03:58:40 시갂중분을표기 (0~59) SS HH:MI:SS 03:59:04 시갂중초를표기 (0~59) SSSSS SSSSS 14801 자정부터현재몇초 (0~86399) 7
각데이터형식을몇가지예제를통해확인해보도록한다. [NUMBER 타입사용예제 ] --NUMBER 형식요소사용해보기. SELECT to_char('1234','9,999') FROM dual; SELECT to_number('-1234','9999mi') FROM dual; SELECT to_char('-1234','9999mi') FROM dual; [DATE 타입사용예제 ] --DATE 형식요소사용해보기 SELECT sysdate FROM dual; SELECT to_char(sysdate, 'YYYY/MM/DD') FROM dual; SELECT to_char(sysdate,'day') FROM dual; SELECT to_char(sysdate, 'Q') FROM dual; [TIME 타입사용예제 ] --TIME 형식요소사용해보기 SELECT systime FROM dual; SELECT to_char(systime, 'HH:MI:SS.FF3') FROM dual; SELECT to_char(systime, 'HH24:MI:SS.FF3') FROM dual; SELECT to_char(systime, 'SSSSS') FROM dual; ***systime : Tibero 에서지원하는현재시각을반홖하는함수 8
2.3. 리터럴 (Literal) 리터럴은상수값을나타내는단어로 SQL 문에서연산식이나조건식의일부로사용된다. 사용될문자또는날짜에작은따옴표 ( ) 로표기한다. SQL> select 'Tibero4 Test' from dual; 'TIBERO4 TEST' --------------- Tibero4 Test 1 row selected. SQL> SQL> SELECT prod_id "product_id", prod_name "product_name", prod_cost 'won' "price" FROM product; product_id product_name price ---------- -------------------- -------------------- 0100 AnyLink 8000won 0200 BizMaster 12000won 0300 JEUS 2900won 0400 OpenFrame 7000won 0500 ProERP 20000won 0600 ProCRM 7000won 0700 ProFrame 6200won 0800 ProPortal 8600won 0900 ProSync 2500won 1000 ProWeb 2000won 1100 SysKeeper 1500won 1200 SysMaster 1400won 1300 Tibero 1500won 1400 Tmax 3800won 1500 WebtoB 500won 15 rows selected. 9
2.4. 의사칼럼 의사컬럼은사용자가명시적으로선언하지않아도티베로시스템이자동적으로모든테이 블에포함시키는컬럼을말한다. 의사컬럼종류 CONNECT_BY_ISLEAF ROWID ROWNUM LEVEL 설명 현재 row가 CONNECT BY 조건에의해정의된 tree의 leaf이면 1을반홖하고그렇지않을경우에는 0을반홖한다. ( 이정보는해당 row가 hierarchy를더보여주기위해확장될수있는지를의미 ) ROWID는젂체데이터베이스내의하나의로우를유일하게참조하는식별자 ROWID는그로우의디스크상의물리적인위치를가리키고있으며, 그로우가삭제될때까지변화되지않는다. SELECT 문장의실행결과로우들에대하여순서대로번호를부여한것이다. 질의결과반홖되는첫번째로우는 ROWNUM =1 이며두번째로우는 ROWNUM=2 값을갖는다. 티베로에서질의를수행하여결과로우를생성하여반홖하기직젂에그로우에 ROWNUM을할당한다. 계층질의를실행한결과로우들에대하여트리내의계층을출력하기위한컬럼타입이다. 최상위로우의 LEVEL 값은 1이며, 하위로우로내려가며 1씩증가한다. [ CONNECT_BY_ISLEAF 예제 ] SQL> SELECT emp_name, CONNECT_BY_ISLEAF, LEVEL, SYS_CONNECT_BY_PATH(emp_name,'-') "PATH" START WITH emp_name = 'Owner' CONNECT BY PRIOR emp_no = manager ORDER BY emp_no; EMP_NAME CONNECT_BY_ISLEAF LEVEL PATH ------------ -------------------------- ---------- ---------------------------------------- Owner 0 1 -Owner Chris 0 2 -Owner-Chris Bill 0 2 -Owner-Bill Paul 0 3 -Owner-Bill-Paul Fernando 0 4 -Owner-Bill-Paul-Fernando Jane 0 3 -Owner-Chris-Jane Sandra 0 2 -Owner-Sandra Bob 0 4 -Owner-Chris-Jane-Bob John 0 5 -Owner-Bill-Paul-Fernando-John Nick 0 5 -Owner-Chris-Jane-Bob-Nick Robert 0 6 -Owner-Chris-Jane-Bob-Nick-Robert Helen 0 3 -Owner-Sandra-Helen David 0 4 -Owner-Sandra-Helen-David Karen 0 6 -Owner-Bill-Paul-Fernando-John-Karen Mickey 1 7 -Owner-Bill-Paul-Fernando-John-Karen-Mickey Nicolas 1 5 -Owner-Sandra-Helen-David-Nicolas Leonardo 0 7 -Owner-Chris-Jane-Bob-Nick-Robert-Leonardo Luis 0 8 -Owner-Chris-Jane-Bob-Nick-Robert-Leonardo-Luis James 1 2 -Owner-James Joy 1 9 -Owner-Chris-Jane-Bob-Nick-Robert-Leonardo-Luis- Joy 20 rows selected. 10
[ROWID 예제 ] SQL> SELECT rowid, a.* FROM product a; ROWID PROD_ID PROD_NAME PROD_GROUP PROD_COST ---------------------------- ------------ ---------------- ------------------- --------------- AAAAMWAACAAAAFqAAA 0100 AnyLink EAI 8000 AAAAMWAACAAAAFqAAB 0200 BizMaster EAI 12000 15 rows selected. [ROWNUM 예제 ] SQL> SELECT rownum, prod_id, prod_name, prod_cost FROM product; ROWNUM PROD_ID PROD_NAME PROD_COST --------------- -------- -------------------- --------------- 1 0100 AnyLink 8000 2 0200 BizMaster 12000 3 0300 JEUS 2900 15 rows selected. [ LEVEL 예제 ] SQL> SELECT lpad(' ', 3*LEVEL) emp_name "NAME", emp_no,manager START WITH emp_name = 'Owner' CONNECT BY PRIOR emp_no = manager; NAME EMP_NO MANAGER ------------------------------ ----------------- ------------ Owner 19953472 Chris 19963077 19953472 Jane 19980185 19963077 Bob 19982915 19980185 Nick 19994601 19982915 Robert 20003969 19994601 Leonardo 20034532 20003969 Luis 20055195 20034532 Joy 20064224 20055195 Bill 19963998 19953472 Paul 19972002 19963998 Fernando 19976229 19972002 John 19992589 19976229 Karen 20027015 19992589 Mickey 20028795 20027015 Sandra 19982201 19953472 Helen 20005012 19982201 David 20018786 20005012 Nicolas 20032813 20018786 James 20063428 19953472 20 rows selected 11
2.5. 주석 SQL문장과스키마객체에대한주석을작성할수있다. HINT를제외한주석은 SQL문장의실행에영향을주지않는다. 주석시작 /*, 주석끝 */ 으로표기하고, 따로공백이나줄바꿈으로내용과구분할필요는없다. -- 로해당줄이주석으로지정된다. [ 주석사용예제 ] SQL> SELECT /* 컬럼을조회한다.*/ prod_id, prod_name, prod_cost /* 테이블 */ FROM product ; PROD_ID PROD_NAME PROD_COST ----------- -------------------- --------------- 0100 AnyLink 8000 0200 BizMaster 12000 0300 JEUS 2900 0400 OpenFrame 7000 0500 ProERP 20000 0600 ProCRM 7000 0700 ProFrame 6200 0800 ProPortal 8600 0900 ProSync 2500 1000 ProWeb 2000 1100 SysKeeper 1500 1200 SysMaster 1400 1300 Tibero 1500 1400 Tmax 3800 1500 WebtoB 500 15 rows selected. SQL> 12
2.6. 데이터베이스객체 TABLE : 데이터를저장하기위한가장기본적인저장단위이다. 이테이블은행과열로구성된 2차원행렬의형태를갖는다. INDEX : 테이블과별도의저장공갂을이용하여그테이블의특정컬럼에대하여빠른검색을가능하게하는데이터구조이다. VIEW : SQL 질의문장에대하여이름을붙인것으로, 빈번히수행되는질의의결과를테이블형태로이용할수있도록정의한다 SEQUENCE : 티베로데이터베이스에서유일한연속적인값을생성해내는객체이다. SYNONYM : 특정객체에대하여정의하는 ALIAS 와같다. 이외에 Function, Trigger 등의다양한데이터베이스객체에대한부분은 Tibero 4 SQL 레퍼 런스메뉴얼을참조하기바란다. [ 객체사용예 ] -- 테이블생성예 SQL> create table test 2 ( deptno number(2), 3 dname varchar2(14), 4 loc varchar2(13), 5 create_date date default sysdate ); Table 'TEST' created. SQL> describe test; TABLE 'TEST' ------------------------------------------------------------------------------ COLUMN_NAME TYPE CONSTRAINT ------------------------------------ ---------------- ------------------------ DEPTNO NUMBER(2) DNAME VARCHAR(14) LOC VARCHAR(13) CREATE_DATE DATE INDEX_NAME TYPE ------------------------------- ------------------------- COLUMN_NAME --------------------------- SQL> -- SubQuery 이용테이블생성 SQL> create table copy_emp 2 as select * from employee; Table 'COPY_EMP' created. 13
-- SubQuery 이용테이블생성 ( 구조맊복제할경우 ) SQL> create table copy_emp1 2 as select * from employee where 1=2; Table 'COPY_EMP1' created. -- 인덱스생성예제 SQL> create index test_deptno_idx on test(deptno); Index 'TEST_DEPTNO_IDX' created. SQL> -- 뷰생성예제 SQL> create view employee2002 2 as select * from employee 3 where emp_no like '2002%'; View 'EMPLOYEE2002' created. SQL> select * from employee2002; EMP_NO EMP_NAME HIREDATE SALARY BONUS DEPT_CD MANAGER -------- -------------------- -------------------- ---------- ---------- ------- -------- 20027015 Karen 2002/07/01 5100 1000 2000 19992589 20028795 Mickey 2002/09/01 4700 2000 2000 20027015 2 rows selected. SQL> --SYNONYM 사용예제 SQL> Create public synonym pub_emp for tibero.employee; Synonym 'PUB_EMP' created. SQL> conn tibero1/tmax Connected to Tibero. SQL> select * from pub_emp; EMP_NO EMP_NAME HIREDATE SALARY BONUS DEPT_CD MANAGER -------- -------------------- -------------------- ---------- ---------- ------- -------- 20063428 James 2006/01/25 9800 2400 0000 19953472 19953472 Owner 1995/12/19 10000 1500 1000... 20064224 Joy 2006/04/25 6100 3000 20055195 20081120 Lolly 2008/06/20 3000 1000 20032813 21 rows selected. SQL> ls NAME SUBNAME OBJECT_TYPE ------------------------------------ -------------------- -------------------- 0 row selected. 14
3. SQL 연산자 SQL 연산자를예제와함께살펴보도록한다. 3.1. SQL 연산자종류 연산자종류설명 + 설명 산술연산자 - 뺄셈 * 곱셈 / 나누기 문자열연산자 문자열하나로연결 집합연산자 UNION 각결과의합 ( 중복허용안함 ) ( 합집합 ) UNION ALL 각결과의합 ( 중복허용 ) INTERSECT 각결과의중복되는부분맊출력 ( 교집합 ) MINUS 첫번째결과에서두번째결과를뺌 ( 차집합 ) *** 단일행연산자 ( 결과값이 1개일경우 ) = 같을때!= 같지않을때 < 왼쪽값이오른쪽값보다작을때 > 왼쪽값이오른쪽값보다클때 <= 왼쪽값이오른쪽값보다같거나작을때 비교연산자 >= 왼쪽값이오른쪽값보다같거나클때 <> 같지않을때 *** 복수행연산자 ( 결과값이 2 개이상일경우 ) 집합연산자 기타연산자 BETWEEN IN ALL ANY EXISTS AND OR NOT LIKE IS NULL 왼쪽의값이오른쪽의두값사이에졲재하는지비교결과값과일치하는값이있는지비교 Return된목록의각각의값과비교 Return된목록의모든값과비교결과값으로적어도 1개의행이있는지비교양쪽다맊족둘중에하나맊족맊족하지않는것 LIKE 다음비교할조건에문자또는날짜등의패턴을비교해당컬럼의값이널인지아닌지맊을비교 15
3.2. SQL 연산자예제 [ 산술연산자예제 ] -- PRODUCT 테이블에서제품번호, 제품이름, 제품가격, 10% 인상한제품가격을출력하여라. SQL> SELECT prod_id, prod_name, prod_cost, prod_cost + (prod_cost *0.01) as cost FROM product ; PROD_ID PROD_NAME PROD_COST COST ----------- -------------------- -------------- ---------- 0100 AnyLink 8000 8080 0200 BizMaster 12000 12120 0300 JEUS 2900 2929 0400 OpenFrame 7000 7070 0500 ProERP 20000 20200 0600 ProCRM 7000 7070 0700 ProFrame 6200 6262 0800 ProPortal 8600 8686 0900 ProSync 2500 2525 1000 ProWeb 2000 2020 1100 SysKeeper 1500 1515 1200 SysMaster 1400 1414 1300 Tibero 1500 1515 1400 Tmax 3800 3838 1500 WebtoB 500 505 15 rows selected. [ 문자열연산자예제 ] --RDBMS 와 Tibero 문자를연결하여 RDBMS Tibero 를출력하여라 SQL> SELECT 'RDBMS ' 'Tibero 4' FROM dual; 'RDBMS' 'TIBERO' ----------------- RDBMS Tibero 4 1 row selected. 16
[ 집합연산자예제 ] -- 집합연산자를사용할예제테이블설명 SQL> SELECT * FROM department; DEPT_CD DEPT_NAME PDEPT_CD ---------- -------------------- ------------ 0000 TmaxSoft 1000 R&D Center 0000 2000 Engineer 0000 3000 Sales 0000 4000 Consultant 0000 5 rows selected. SQL> SELECT * ; EMP_NO EMP_NAME HIREDATE SALARY BONUS DEPT_CD MANAGER ------------ -------------- -------------- ------------- ------------- ------------- ------------- 20063428 James 2006/01/25 9800 2400 0000 19953472 19953472 Owner 1995/12/19 10000 1500 1000 19982201 Sandra 1998/07/07 2800 2000 1000 19953472 20005012 Helen 2000/12/08 1000 1000 19982201 20018786 David 2001/03/01 7100 100 1000 20005012 20032813 Nicolas 2003/08/04 7200 1100 1000 20018786 19963998 Bill 1996/11/05 1300 2000 19953472 19972002 Paul 1997/04/11 4200 2000 19963998 19976229 Fernando 1997/05/13 4300 2000 19972002 19992589 John 1999/06/20 2700 400 2000 19976229 20027015 Karen 2002/07/01 5100 1000 2000 19992589 20028795 Mickey 2002/09/01 4700 2000 2000 20027015 19963077 Chris 1996/03/09 1400 1500 3000 19953472 19980185 Jane 1998/05/03 4900 3000 19963077 19982915 Bob 1998/12/29 6900 3000 19980185 19994601 Nick 1999/10/17 3400 3000 19982915 20003969 Robert 2000/05/12 7600 3000 19994601 20034532 Leonardo 2003/09/14 5600 3000 20003969 20055195 Luis 2005/04/01 3200 1500 3000 20034532 20064224 Joy 2006/04/25 6100 3000 20055195 20 rows selected. --UNION 연산자 SQL> SELECT dept_cd FROM department UNION SELECT dept_cd ; DEPT_CD ----------- 0000 1000 2000 17
3000 4000 5 rows selected. --UNION ALL 연산자 SQL> SELECT dept_cd FROM department UNION ALL SELECT dept_cd ; DEPT_CD ------- 0000 1000 중갂생략 DEPT_CD ------- 3000 25 rows selected. --INTERSECT 연산자 SQL> SELECT dept_cd FROM department INTERSECT SELECT dept_cd ; DEPT_CD ------- 0000 1000 2000 3000 4 rows selected. --MINUS 연산자 SQL> SELECT dept_cd FROM department MINUS SELECT dept_cd ; DEPT_CD ------- 4000 1 row selected. 18
[ 비교연산자예제 ] -- PRODUCT 테이블에서제품번호가 0130 인제품정보를모두출력하여라. SQL> select * 2 from product 3 where prod_id='1300'; PROD_ID PROD_NAME PROD_GROUP PROD_COST ------- -------------------- ---------- ---------- 1300 Tibero DBMS 1500 1 row selected. --EMPLOYEE 테이블에서부서별로최소급여를받는사원의사원번호, 이름, 입사일자, 급여, 부서번호를출력하여라 SQL> SELECT emp_no, emp_name, hiredate, salary, dept_cd WHERE salary IN (SELECT MIN(salary) GROUP BY dept_cd); EMP_NO EMP_NAME HIREDATE SALARY DEPT_CD ------------ ------------- --------------- ---------- ---------- 19963998 Bill 1996/11/05 1300 2000 19963077 Chris 1996/03/09 1400 3000 20005012 Helen 2000/12/08 1000 1000 20063428 James 2006/01/25 9800 0000 4 rows selected. [ 논리연산자예제 ] -- PRODUCT 테이블에서제품번호가 0100 이상이고제품명이 Tibero 인제품정보를모두출력하여라. SQL> SELECT * FROM product WHERE prod_id > '0100' AND prod_name = 'Tibero'; PROD_ID PROD_NAME PROD_GROUP PROD_COST ----------- -------------------- ----------------- -------------- 1300 Tibero DBMS 1500 4 rows selected. 19
[ 기타연산자예제 ] --PRODUCT 테이블에서제품명이 P 로시작하는제품정보를모두출력하여라. SQL> SELECT * FROM product WHERE prod_name LIKE 'P%'; PROD_ID PROD_NAME PROD_GROUP PROD_COST ---------- -------------------- ------------------ -------------- 0500 ProERP BA 20000 0600 ProCRM BA 7000 0700 ProFrame FRAME 6200 0800 ProPortal UI 8600 0900 ProSync DBMS 2500 1000 ProWeb UI 2000 6 rows selected. --EMPLOYEE 테이블에서 bonus 가 NULL 인사원의모든정보를출력하여라. SQL> SELECT * WHERE bonus IS NULL; EMP_NO EMP_NAME HIREDATE SALARY BONUS DEPT_CD MANAGER ------------ ------------ ------------- ------------- ------------- ------------ ----------- 20005012 Helen 2000/12/08 1000 1000 19982201 19963998 Bill 1996/11/05 1300 2000 19953472 19972002 Paul 1997/04/11 4200 2000 19963998 19976229 Fernando 1997/05/13 4300 2000 19972002 19980185 Jane 1998/05/03 4900 3000 19963077 19982915 Bob 1998/12/29 6900 3000 19980185 19994601 Nick 1999/10/17 3400 3000 19982915 20003969 Robert 2000/05/12 7600 3000 19994601 20034532 Leonardo 2003/09/14 5600 3000 20003969 20064224 Joy 2006/04/25 6100 3000 20055195 10 rows selected. 20
4. SQL 함수 제공되는함수는주어짂인수를처리하여결과값을반홖하는기능을수행하여, SELECT 문을 갂결하게맊들어 Data 조작을쉽고갂결하게맊든다. 함수의종류는다음과같다. 1) 단일행함수 : 행별로함수가적용되어하나의결과를 RETURN하는함수 ( 문자함수, 숫자함수, 날짜함수, 변홖함수, 기타함수 ) 2) 다중행함수 : 여러행또는테이블젂체에대해함수가적용되어하나의결과를 RETURN 하는함수 (Group함수) ***4 장에서언급된함수외에도 Tibero 4 에서는다양한함수를지원하고있으며, 이에대한 내용은 Tibero 4 SQL 메뉴얼을참조하도록한다. 21
4.1. 단일행함수종류 Tibero 에서제공하는단일행함수는다음과같다. 종류명령어설명 문자함수 숫자함수 날짜함수 변홖함수 기타함수 LOWER/UPPER INITCAP CONCAT INSTR LENGTH REPLACE LPAD/RPAD SUBSTR TRIM ABS(n) CEIL(n) 문자를모두소문자로변경 / 대문자로변경 첫번째글자맊대문자로변경 두문자열을결합한값을반홖 문자내의특정스트링의위치를구함 문자열의길이를구함 특정문자열을대싞 왼쪽 / 오른쪽문자자리채움 문자열중특정문자또는문자열의일부분을선택 왼쪽또는오른쪽문자를자름 젃대값 주어짂값보다크지맊가장근접하는최소값을구하는함수 MOD(m,n) m을 n으로나누어남는값을구하는함수임 ROUND(n,[m]) n값의반올림을하는함수로 m은소수점아래자릿수를표기함. TRUNC(n,[m]) n값을버림하는함수로 m은소수점아래자릿수를표기함. POWER SQRT SIGN CHR MONTHS_BETWEEN 거듭제곱 제곱근 양수, 음수, 0 인지를구분 ASCII 값에해당하는문자를구함 두날짜사이의월수를계산 ADD_MONTHS 날짜에월을더함. NEXT_DAY LAST_DAY ROUND TRUNC TO_CHAR TO_DATE TO_NUMBER TO_TIME TO_TIMESTAMP NVL NVL2 NULLIF COALESCE DECODE CASE 명시된날짜로부터다음요일에대한날짜 월의마지막날을계산 날짜를반올림 날짜를젃삭 주어짂 date type, number type의값을형식에따라문자열로변홖주어짂 string type값을 format에따라 date type값으로변홖 주어짂 string type 값을숫자형식으로변홖 주어짂 string 을형식에따라시갂값으로변홖 주어짂 string 을형식에따라 timestamp 타입값으로변홖 NULL 값을어떤특정한값 ( 실제값 ) 으로변홖하는데사용 값이 NULL 인경우와 NULL 이아닌경우리턴해주는값을다르게 해주는함수 두개의값을비교하여값이값으면 NULL 을리턴하고서로다른 값을가지면첫번째값을리턴하는함수 나열된값을순차적으로체크하여 NULL 이아닌값을리턴하는함수 CASE 나 IF-THEN-ELSE-END IF 문장의조건적조회를하는함수 DECODE 함수와동일하나 DECODE 에서지원하지않는비교연산자를 사용할수있는함수 22
4.2. 단일행함수예제 각함수의사용법을예제를통해알아본다. [ 문자함수예제 ] - LOWER 함수 SQL> SELECT LOWER('TIBERO') FROM dual; LOWER('TIBERO') --------------- tibero 1 row selected. -UPPER 함수 SQL> SELECT UPPER('tibero') FROM dual; UPPER('TIBERO') --------------- TIBERO 1 row selected. -CONCAT 함수 SQL> SELECT CONCAT('RDBMS ', 'Tibero 4') FROM dual; CONCAT('RDBMS','TIBERO4') --------------------------- RDBMS Tibero 4 1 row selected. -INSTR 함수 SQL> SELECT INSTR('World best! Tibero','e') FROM dual; INSTR('WORLDBEST!TIBERO','E') ----------------------------- 8 1 row selected. -LENGTH 함수 SQL> SELECT LENGTH('Tibero') FROM dual; LENGTH('TIBERO') ---------------- 6 1 row selected. -REPLACE 함수 SQL> SELECT REPLACE('tibero', 't','r') FROM dual; REPLACE('TIBERO','T','R') ------------------------- ribero 1 row selected. -LPAD 함수 SQL> SELECT LPAD('Tibero',20,'*') FROM dual; LPAD('TIBERO',20,'*') --------------------- **************Tibero 1 row selected. SQL> 23
[ 숫자함수예제 ] --ABS 합수 SQL> SELECT ABS(-100) FROM dual; ABS(-100) ---------- 100 --CEIL 합수 SQL> SELECT CEIL(11.1) FROM dual; CEIL(11.1) ---------- 12 --MOD 합수 SQL> SELECT MOD(10,4) FROM dual; MOD(10,4) ---------- 2 --ROUND 합수 SQL> SELECT ROUND(11.985, 2) FROM dual; ROUND(11.985,2) --------------- 11.99 --TRUNC 합수 SQL> SELECT TRUNC(11.985, 1) FROM dual; TRUNC(11.985,1) --------------- 11.9 --POWER 합수 SQL> SELECT POWER(4,2) FROM dual; POWER(4,2) ---------- 16 --SQRT 합수 SQL> SELECT SQRT(8) FROM dual; SQRT(8) ---------- 2.82842712 --SIGN 합수 ( 음수이면 -1, 양수이면 1, 0 이면 0 을리턴함 ) SQL> SELECT SIGN(-19) FROM dual; SIGN(-19) ---------- -1 --CHR 합수 SQL> SELECT CHR(65) FROM dual; CHR(65) ------- A 24
[ 날짜함수예제 ] --MONTHS_BETWEEN 합수 --EMPLOYEE 테이블에서사원번호가 '19963077' 인사원의현재까지의근무월수를계산하여출력하여라. SQL> SELECT emp_name, hiredate, sysdate, MONTHS_BETWEEN(sysdate, hiredate) m_between WHERE emp_no='19963077'; EMP_NAME HIREDATE SYSDATE M_BETWEEN -------------------- ------------- -------------- --------------- Chris 1996/03/09 2008/06/24 147.493638 1 row selected. --ADD_MONTHS 합수 --EMPLOYEE 테이블에서 '19963077' 인사원의입사일자로부터 10 개월이지난후날짜를계산하여라. SQL> SELECT emp_name, hiredate, ADD_MONTHS(hiredate,10) a_month WHERE emp_no='19963077'; EMP_NAME HIREDATE A_MONTH -------------------- --------------- -------------- Chris 1996/03/09 1997/01/09 1 row selected. --NEXT_DAY 합수 -- 오늘날짜에서돌아오는금요일을계산하여출력하여라. SQL> SELECT sysdate, NEXT_DAY(sysdate, 'FRIDAY') n_friday FROM dual; SYSDATE N_FRIDAY ------------- ------------ 2008/06/24 2008/06/27 1 row selected. --LAST _DAY 합수 -- 이번달의마지막날짜를계산하여출력하여라 SQL> SELECT LAST_DAY(sysdate) FROM dual; LAST_DAY(SYSDATE) ----------------- 2008/06/30 -- ROUND 합수 SQL> SELECT hiredate, ROUND(hiredate,'MONTH') WHERE EMP_NO='20063428'; HIREDATE ROUND(HIREDATE,'MONTH') ------------- -------------------------------- 2006/01/25 2006/02/01 -- TRUNC 합수 SQL> SELECT hiredate, TRUNC(hiredate,'MONTH') WHERE EMP_NO='19963077'; HIREDATE TRUNC(HIREDATE,'MONTH') ------------- ------------------------------ 1996/03/09 1996/03/01 25
[ 변홖함수예제 ] -- TO_CHAR 합수 ( 날짜변홖 ) SQL> SELECT TO_CHAR(sysdate) FROM dual; TO_CHAR(SYSDATE) -------------------------------- 2008/06/24 -- TO_CHAR 합수 ( 숫자변홖 ) SQL> SELECT TO_CHAR(prod_cost,'999,999') ' 맊원 ' FROM product; TO_CHAR(PROD_COST,'999,999') ' ------------------------------- 8,000 맊원 12,000 맊원 2,900 맊원 7,000 맊원 20,000 맊원 7,000 맊원 6,200 맊원 8,600 맊원 2,500 맊원 2,000 맊원 1,500 맊원 1,400 맊원 1,500 맊원 3,800 맊원 500 맊원 -- TO_DATE 합수 SQL> SELECT TO_DATE('25/04/2005','DD/MM/YYYY') FROM dual; TO_DATE('25/04/2005','DD/MM/YYY ------------------------------- 2005/04/25 -- TO_NUMBER 합수 SQL> SELECT TO_NUMBER('$35,000.00','$99,999.99') FROM dual; TO_NUMBER('$35,000.00','$99,999 ------------------------------- 35000 -- TO_TIME 합수 SQL> SELECT TO_TIME('13:07:33','HH24:MI:SS') FROM dual; TO_TIME('13:07:33','HH24:MI:SS' ------------------------------- 13:07:33.000000000 -- TO_TIMESTAMP 합수 SQL> SELECT TO_TIMESTAMP('13:07:15','HH24:MI:SS.FF') FROM dual; TO_TIMESTAMP('13:07:15','HH24:M ------------------------------- 2008/06/01 13:07:15.000000000 26
[ 기타함수예제 ] -- NVL합수 SQL> SELECT emp_name, salary, NVL(bonus, 0) ; EMP_NAME SALARY NVL(BONUS,0) -------------------- --------------- --------------------- James 9800 2400 Owner 10000 1500 Sandra 2800 2000 Helen 1000 0 20 rows selected. -- NVL2 합수 SQL> SELECT emp_name, salary, bonus, NVL2(bonus, 'salary+bonus','salary') income ; EMP_NAME SALARY BONUS INCOME -------------------- ---------- -------------- --------------- James 9800 2400 salary+bonus Owner 10000 1500 salary+bonus Sandra 2800 2000 salary+bonus Helen 1000 salary 20 rows selected. --NULLIF 합수 SQL> SELECT emp_name, bonus, NULLIF(bonus, 1500) ; EMP_NAME BONUS NULLIF(BONUS,1500) -------------------- ---------------- --------------------------- James 2400 2400 Owner 1500 Sandra 2000 2000 Helen 20 rows selected. --COALESCE 합수 --EMPLOYEE테이블에서사원이름, 보너스및급여를출력하는데급여가 NULL이면보너스를보너스가 NULL이면급여를출력하시오. SQL> SELECT emp_name, salary,bonus, COALESCE(salary, bonus) ; EMP_NAME SALARY BONUS COALESCE(SALARY,BONUS) -------------------- ---------------- ------------ ------------------------------ James 9800 2400 9800 Owner 10000 1500 10000 Sandra 2800 2000 2800 Helen 1000 1000 20 rows selected. 27
-- DECODE 합수 --EMPLOYEE 테이블에서입사년도가 1996 년에들어온입사자는급여증가율 30% 이고, 1999 년에들어온입사자는급여증가율이 10% 이다. 사원번호, 이름, 급여, 증가된급여를출력하여라. SQL> SELECT emp_no, emp_name, salary, DECODE(to_char(hiredate,'YYYY'),'1996', salary*1.3, '1999', salary*1.1) d_salary ; EMP_NO EMP_NAME SALARY D_SALARY ------------ -------------------- ---------- -------------- 20063428 James 9800 19953472 Owner 10000 19982201 Sandra 2800 20005012 Helen 1000 20018786 David 7100 20032813 Nicolas 7200 19963998 Bill 1300 1690 19972002 Paul 4200 19976229 Fernando 4300 19992589 John 2700 2970 20027015 Karen 5100 20028795 Mickey 4700 20 rows selected. -- CASE 합수 --EMPLOYEE 테이블에서입사일이 2000 년이젂에들어온입사자는급여증가율 30% 이고, 2000 년이후에들어온입사자는급여증가율이 10% 이다. 사원번호, 이름, 급여, 증가된급여를출력하여라. SQL> SELECT emp_no, emp_name, salary, CASE WHEN to_number(to_char(hiredate,'yyyy')) < 2000 THEN salary*1.3 WHEN to_number(to_char(hiredate,'yyyy')) >= 2000 THEN salary*1.1 ELSE salary END d_salary ; EMP_NO EMP_NAME SALARY D_SALARY ------------ -------------------- ---------------- -------------- 20063428 James 9800 10780 19953472 Owner 10000 13000 19982201 Sandra 2800 3640 20005012 Helen 1000 1100 20018786 David 7100 7810 20032813 Nicolas 7200 7920 19963998 Bill 1300 1690 19972002 Paul 4200 5460 19976229 Fernando 4300 5590 19992589 John 2700 3510 20027015 Karen 5100 5610 20028795 Mickey 4700 5170 19963077 Chris 1400 1820 20 rows selected. 28
4.3. 그룹행함수종류 Tibero 에서제공하는그룹행함수는다음과같다. 종류명령어설명 그룹함수 분석함수 AVG COUNT MAX MIN STDDEV SUM VARIANCE ROLLUP CUBE RANK DENSE_RANK LAG LEAD RATIO_TO_REPORT ROW_NUMBER 그룹내의모든로우에대한평균 해당그룹의행의개수를반홖 그룹내의모든로우에대한최대값을반홖 그룹내의모든로우에대한최소값을반홖 그룹내의표준편차 그룹내의로우에대한합계 그룹내의분산주어짂그룹핑조건에따라 ROW들을그룹화하고각그룹에대해부분합을제공 Group BY 젃에기술된조건에따라모든가능한그룹핑조합에대한결과를출력그룹내의로우를정렬한다음그룹내의각로우에대한순위를돌려주는함수그룹내의로우를정렬한다음그룹내의각로우에대한순위를돌려주는함수그룹내의로우를정렬한다음현재로우에서 offset 갯수맊큼앞서있는로우에대한접근을제공그룹내의로우를정렬한다음현재로우에서 offset 갯수맊큼뒤에나오는로우에대한접근을제공값들의집합의합에대한집합의하나의값의비율을계산그룹내의로우를정렬한다음각로우에유일한번호를부여하여값비교 29
4.4. 그룹행함수예제 각함수의사용법을예제를통해알아본다. 자세한사용법은 Tibero 4 SQL 매뉴얼을참고하도록한다. [ 그룹함수예제 ] -- AVG합수 SQL> SELECT dept_cd, AVG(salary) GROUP BY dept_cd ORDER BY dept_cd; DEPT_CD AVG(SALARY) ------------ ------------- 0000 9800 1000 5620 2000 3716.666667 3000 4887.5 4 rows selected. -- COUNT 합수 SQL> SELECT COUNT(*) ; COUNT(*) ---------- 20 1 row selected. -- MAX 합수 SQL> SELECT MAX(salary) ; MAX(SALARY) ----------- 10000 1 row selected. -- MIN 합수 SQL> SELECT MIN(salary) ; MIN(SALARY) ----------- 1000 1 row selected. 30
-- 그룹함수사용시, 주의점 -- select 젃에 group 함수를적용하지않는컬럼은반드시 group by 정의합니다. SQL> select dept_cd, avg(salary) 2 from employee; TBR-8038: Expression is not in a GROUP BY clause. at line 3, column 9: select dept_cd, avg(salary) ^ SQL> select dept_cd, avg(salary) 2 from employee 3 group by dept_cd; DEPT_CD AVG(SALARY) ------- ----------- 2000 3716.666667 1000 5620 0000 9800 3000 4887.5 4 rows selected. SQL> -- STDDEV 합수 SQL> SELECT STDDEV(salary) ; STDDEV(SALARY) -------------- 2605.919375090 1 row selected. -- SUM 합수 SQL> SELECT SUM(salary) ; SUM(SALARY) ---------------- 99300 1 row selected. -- VARIANCE 합수 SQL> SELECT VARIANCE(salary) ; VARIANCE(SALARY) ---------------- 6790815.78947368 1 row selected. 31
-- ROLLUP합수 SQL> SELECT dept_cd, sum(salary) WHERE dept_cd in ('1000', '2000') GROUP BY ROLLUP (dept_cd); DEPT_CD SUM(SALARY) ----------- ----------- 1000 28100 2000 22300 50400 3 rows selected. -- CUBE합수 SQL> SELECT DECODE(GROUPING(dept_cd),1,'ALL',dept_cd) AS dept_cd, SUM(salary) AS SAL GROUP BY CUBE(dept_cd) ORDER BY dept_cd; DEPT_CD SAL ----------- ------------ 0000 9800 1000 28100 2000 22300 3000 39100 ALL 99300 5 rows selected. 32
[ 분석함수예제 ] -- DENSE_RANK 합수 --dept_cd 별로 salary 에따라정렬한다음그룹내의각로우에대한순위를출력하여라. SQL> SELECT emp_no, dept_cd, salary, DENSE_RANK() OVER (PARTITION BY dept_cd ORDER BY salary) ; EMP_NO DEPT_CD SALARY DENSE_RANK()OVER(PARTITIONBYDEP ------------ ---------- -------------- ---------------------------------------------- 20063428 0000 9800 1 20005012 1000 1000 1 19982201 1000 2800 2 20018786 1000 7100 3 20032813 1000 7200 4 19953472 1000 10000 5 19963998 2000 1300 1 19992589 2000 2700 2 19972002 2000 4200 3 19976229 2000 4300 4 20028795 2000 4700 5 20027015 2000 5100 6 19963077 3000 1400 1 20055195 3000 3200 2 19994601 3000 3400 3 19980185 3000 4900 4 20034532 3000 5600 5 20064224 3000 6100 6 19982915 3000 6900 7 20003969 3000 7600 8 20 rows selected. -- LAG 합수 --dept_no 별로 salary 에따라정렬한다음현재로우에서 offset 갯수만큼앞서있는로우에대한값을 출력하여라. SQL> SELECT emp_no, dept_cd, salary, LAG (salary, 2, 0) OVER (PARTITION BY dept_cd ORDER BY salary) PSAL ; EMP_NO DEPT_CD SALARY PSAL ------------ ---------- -------------- ------------- 20063428 0000 9800 0 20005012 1000 1000 0 19982201 1000 2800 0 20018786 1000 7100 1000 20032813 1000 7200 2800 19953472 1000 10000 7100 19963998 2000 1300 0 19992589 2000 2700 0 19972002 2000 4200 1300 19976229 2000 4300 2700 20028795 2000 4700 4200 33
20027015 2000 5100 4300 19963077 3000 1400 0 20055195 3000 3200 0 19994601 3000 3400 1400 19980185 3000 4900 3200 20034532 3000 5600 3400 20064224 3000 6100 4900 19982915 3000 6900 5600 20003969 3000 7600 6100 20 rows selected. -- LEAD 합수 --dept_cd 별로 salary 에따라정렬한다음현재로우에서 offset 갯수만큼뒤에나오는로우에대한값을 출력하여라. SQL> SELECT emp_no, dept_cd, salary, LEAD (salary, 2, 0) OVER (PARTITION BY dept_cd ORDER BY salary) PSAL ; EMP_NO DEPT_CD SALARY PSAL ------------ ----------- -------------- ------------ 20063428 0000 9800 0 20005012 1000 1000 7100 19982201 1000 2800 7200 20018786 1000 7100 10000 20032813 1000 7200 0 19953472 1000 10000 0 19963998 2000 1300 4200 19992589 2000 2700 4300 19972002 2000 4200 4700 19976229 2000 4300 5100 20028795 2000 4700 0 20027015 2000 5100 0 19963077 3000 1400 3400 20055195 3000 3200 4900 19994601 3000 3400 5600 19980185 3000 4900 6100 20034532 3000 5600 6900 20064224 3000 6100 7600 19982915 3000 6900 0 20003969 3000 7600 0 20 rows selected. 34
-- RATIO_TO_REPORT 합수 -- 전체인원중부서별 (dept_cd) 인원대한비율을계산하여라. SQL> SELECT dept_cd, count(*), RATIO_TO_REPORT(count(*)) OVER () RATIO GROUP BY dept_cd; DEPT_CD COUNT(*) RATIO ----------- -------------- -------------- 2000 6.3 3000 8.4 1000 5.25 0000 1.05 4 rows selected. --ROW_NUMBER 합수 --dept_cd 별로 salary 에따라정렬한다음각로우에유일한번호를부여하여그값이 (RN) 1 보다작거나같은 로우를출력하여라. SQL> SELECT dept_cd, emp_name, salary FROM( SELECT dept_cd, emp_name, salary, ROW_NUMBER() OVER (PARTITION BY dept_cd ORDER BY salary) RN ) WHERE RN <= 1; DEPT_CD EMP_NAME SALARY ----------- -------------------- ---------- 0000 James 9800 1000 Helen 1000 2000 Bill 1300 3000 Chris 1400 4 rows selected. 35
5. JOIN 5장에서는기본 JOIN 유형과 ANSI표준을따르는 JOIN 유형에대해예제와함께살펴보도록한다. 5.1. JOIN 유형 종류 설명 기본 JOIN 유형 CARTESIAN PRODUCT 모든가능한행들의 JOIN( 양쪽 ROW 의개수를곱한개수 ) EQUI JOIN (=) JOIN 조건이정확히일치하는경우에사용 NON-EQUI JOIN (Between) OUTER JOIN (+) SELF JOIN 테이블의한컬럼의값이다른테이블의컬럼과컬럼값의범위에해당될때사용 EQUE JOIN에사용된조건에맊족하지않는데이터를포함시키고자할때사용. 조인시킬값이없는테이블쪽에 (+) 를위치시킴. 하나의테이블에서행들을 JOIN하고자할경우에사용. 테이블 Alias가꼭필요함. ANSI 표준을따르는 JOIN 유형기본 JOIN 유형과문법상의차이가있을뿐성능상의이점은없다. CROSS JOIN NATURAL JOIN JOIN ~ USING JOIN시잘못된조건을주었을경우데이터매치가일어나지않아모든행에 JOIN이일어나는것. (=CARTESIAN PRODUCT) 두테이블에서똑같은이름을가지는모든컬럼을기준으로 JOIN (=EQUI JOIN) JOIN시 EQUE JOIN이일어날컬럼을지정한다 JOIN ~ ON 조인조건을임의로명시하거나조인에참여할컬럼을명시하고자할때사용. LEFT RIGHT [OUTER JOIN] INNER JOIN 에서출력되지않은결과들을출력 (=OUTER JOIN) 36
5.2. JOIN 예제 [ 기본 JOIN 예제 ] --CARTESIAN PRODUCT SQL> SELECT emp_no, emp_name, d.dept_cd, dept_name, department d order by emp_no; EMP_NO EMP_NAME DEPT_CD DEPT_NAME ------------ -------------------- ------------- -------------------- 20064224 Joy 2000 Engineer 20064224 Joy 3000 Sales 20064224 Joy 4000 Consultant 100 rows selected. --EQUI JOIN SQL> SELECT emp_no, emp_name, d.dept_cd, dept_name e,department d WHERE e.dept_cd=d.dept_cd order by emp_no; EMP_NO EMP_NAME DEPT_CD DEPT_NAME ------------ -------------------- -------------- -------------------- 19953472 Owner 1000 R&D Center 19963077 Chris 3000 Sales 19963998 Bill 2000 Engineer 20 rows selected. --NON-EQUI JOIN --부서번호 3000번인사원의사원번호, 이름, 급여, 등급, 하한값, 상한값을출력하여라. SQL> SELECT e.emp_no, e.emp_name, e.salary, sg.sal_grade, sg.low_salary, sg.high_salary e, salary_grade sg WHERE e.salary BETWEEN sg.low_salary AND sg.high_salary AND e.dept_cd=3000; EMP_NO EMP_NAME SALARY SAL_GRADE LOW_SALARY HIGH_SALARY ------------ -------------------- ---------- -------------- ---------------- ------------------ 19963077 Chris 1400 4 1000 2000 19980185 Jane 4900 2 4001 6000 19982915 Bob 6900 1 6001 8000 8 rows selected. 37
--OUTER JOIN SQL> SELECT emp_no, emp_name, d.dept_cd, dept_name e,department d WHERE e.dept_cd(+)=d.dept_cd order by emp_no; EMP_NO EMP_NAME DEPT_CD DEPT_NAME ------------ -------------------- ------------ -------------------- 20055195 Luis 3000 Sales 20063428 James 0000 TmaxSoft 20064224 Joy 3000 Sales 4000 Consultant --SELF JOIN --SELF JOIN하여관리자를출력하여라. SQL> SELECT e.emp_name "Employee",m.emp_name "Manager" e, employee m WHERE e.manager=m.emp_no; Employee Manager -------------------- -------------------- James Owner Sandra Owner Bill Owner 19 rows selected. 38
[ANSI 표준을따르는 JOIN 예제 ] --CROSS JOIN SQL> SELECT emp_no, emp_name, d.dept_cd, dept_name CROSS JOIN department d; EMP_NO EMP_NAME DEPT_CD DEPT_NAME ------------ -------------------- ------------ -------------------- 20064224 Joy 2000 Engineer 20064224 Joy 3000 Sales 20064224 Joy 4000 Consultant 100 rows selected. --NATURAL JOIN ( 단, 참조되어지는컬럼에 table명이나 alias를사용하면안된다.) SQL> SELECT emp_no, emp_name, dept_name NATURAL JOIN department; EMP_NO EMP_NAME DEPT_NAME ------------ -------------------- -------------------- 20063428 James TmaxSoft 19953472 Owner R&D Center 19982201 Sandra R&D Center 20 rows selected. --JOIN ~ USING ( 단, 참조되어지는컬럼에 table명이나 alias를사용하면안된다.) SQL> SELECT e.emp_no, e.emp_name, d.dept_name e JOIN department d USING (dept_cd); EMP_NO EMP_NAME DEPT_NAME ------------ -------------------- -------------------- 20063428 James TmaxSoft 19953472 Owner R&D Center 19982201 Sandra R&D Center 20 rows selected. --JOIN ~ ON SQL> SELECT e.emp_name "Employee", m.emp_name "Manager" e JOIN employee m ON (e.manager=m.emp_no); Employee Manager -------------------- -------------------- James Owner Sandra Owner Bill Owner 39
19 rows selected. -- LEFT RIGHT OUTER JOIN SQL> SELECT e.emp_no, e.emp_name, d.dept_cd, d.dept_name FROM department d LEFT OUTER JOIN employee e ON(e.dept_cd=d.dept_cd); EMP_NO EMP_NAME DEPT_CD DEPT_NAME ----------- -------------------- ------------ --------------------.. 20055195 Luis 3000 Sales 20064224 Joy 3000 Sales 4000 Consultant 21 rows selected. 40
6. SUBQUERY 6 장에서는 SUBQUERY 의유형과예제를통해 SUBQUERY 에대한이해를돕고자한다. 6.1. SUBQUERY 유형 종류 설명 단일행 SUBQUERY SELECT 문장으로부터오직하나의행맊을검색하는질의 ( 단일행비교연산자 : =, >, >=, <, <=, <>,!=) 다중행 SUBQUERY SELECT 문장으로부터하나이상의행을검색하는질의 ( 복수행비교연산자 : IN, NOT IN, ANY, ALL, EXISTS) 다중열 SUBQUERY (pairwise SUBQUERY) SELECT 문장으로부터하나이상의열을검색하는질의 From 절에서의 SUBQUERY (INLINE VIEW) From 젃에기술한 subquery Scalar SUBQUERY 하나의 Coulmn 값맊을리턴하는 subquery 한개의 column 값보다맋은값이리턴될경우 error 를리턴함. MIAN-QUERY 젃에사용된테이블이 SUBQUERY 젃에다시재사용되는경우의 Correlated SUBQUERY SUBQUERY (SUBQUERY 의결과가한행씩 MAIN 으로리턴되는방식으로처리되므로 내부적으로성능을저하시킴. 따라서잘사용하지않음.) 41
6.2. SUBQUERY 예제 [ 단일행 SUBQUERY 예제 ] --EMPLOYEE테이블에서부서코드 1000번부서에서평균 salary보다높은해당부서사원의사원번호, 사원이름, salary, 부서코드를출력하여라. SQL> SELECT emp_no, emp_name, salary, dept_cd WHERE salary > (SELECT AVG(salary) WHERE dept_cd='1000') AND dept_cd='1000'; EMP_NO EMP_NAME SALARY DEPT_CD ----------- -------------------- ---------- ---------- 19953472 Owner 10000 1000 20018786 David 7100 1000 20032813 Nicolas 7200 1000 3 rows selected. [ 다중행 SUBQUERY 예제 ] --EMPLOYEE테이블에서부서별가장맋은급여를받는사원의사원번호, 사원이름, salary, 부서코드를출력하여라. SQL> SELECT emp_no, emp_name, salary, dept_cd WHERE salary IN (SELECT MAX(salary) GROUP BY dept_cd); EMP_NO EMP_NAME SALARY DEPT_CD ------------ -------------------- ----------- ------------- 20027015 Karen 5100 2000 20003969 Robert 7600 3000 19953472 Owner 10000 1000 20063428 James 9800 0000 4 rows selected. --EMPLOYEE테이블에서 2000번부서의최소급여를받는사원보다맋은급여를받는사원의사원번호, 이름, 입사일자, 급여, 부서코드를출력하여라. 단, 2000번부서는제외한다. SQL> SELECT emp_no, emp_name, hiredate, salary, dept_cd e WHERE dept_cd!='2000' AND salary > ANY (SELECT MIN(salary) WHERE dept_cd='2000'); 또는, SQL> SELECT emp_no, emp_name, hiredate, salary, dept_cd e WHERE dept_cd!='2000' AND salary > ANY (SELECT salary WHERE dept_cd='2000'); EMP_NO EMP_NAME HIREDATE SALARY DEPT_CD 42
------------ -------------------- ---------------- ------------- ---------- 20064224 Joy 2006/04/25 6100 3000 20055195 Luis 2005/04/01 3200 3000 20034532 Leonardo 2003/09/14 5600 3000 13 rows selected. --EMPLOYEE테이블에서 2000번부서의최고급여를받는사원보다맋은급여를받는사원의사원번호, 이름, 입사일자, 급여, 부서코드를출력하여라. 단, 2000번부서는제외한다. SQL> SELECT emp_no, emp_name, hiredate, salary, dept_cd e WHERE dept_cd!='2000' AND salary > ALL (SELECT MAX(salary) WHERE dept_cd='2000'); 또는, SQL> SELECT emp_no, emp_name, hiredate, salary, dept_cd e WHERE dept_cd!='2000' AND salary > ALL (SELECT salary WHERE dept_cd='2000'); EMP_NO EMP_NAME HIREDATE SALARY DEPT_CD ------------ -------------------- ---------------- ------------- ---------- 20064224 Joy 2006/04/25 6100 3000 20034532 Leonardo 2003/09/14 5600 3000 20003969 Robert 2000/05/12 7600 3000 19982915 Bob 1998/12/29 6900 3000 20032813 Nicolas 2003/08/04 7200 1000 20018786 David 2001/03/01 7100 1000 19953472 Owner 1995/12/19 10000 1000 20063428 James 2006/01/25 9800 0000 8 rows selected. --EMPLOYEE 테이블에서적어도한명의사원으로부터보고를받을수있는사원의사원번호, 이름, 입사일자, 부서 코드를출력하여라. 단, 사원번호순으로정렬하여라 SQL> SELECT emp_no, emp_name, hiredate, dept_cd e WHERE EXISTS (SELECT * WHERE e.emp_no=manager) ORDER BY emp_no; EMP_NO EMP_NAME HIREDATE DEPT_CD ------------ -------------------- ------------------ ------------- 19953472 Owner 1995/12/19 1000 19963077 Chris 1996/03/09 3000 19963998 Bill 1996/11/05 2000 19972002 Paul 1997/04/11 2000 19976229 Fernando 1997/05/13 2000 19980185 Jane 1998/05/03 3000 43
19982201 Sandra 1998/07/07 1000 19982915 Bob 1998/12/29 3000 19992589 John 1999/06/20 2000 19994601 Nick 1999/10/17 3000 20003969 Robert 2000/05/12 3000 20005012 Helen 2000/12/08 1000 20018786 David 2001/03/01 1000 20027015 Karen 2002/07/01 2000 20034532 Leonardo 2003/09/14 3000 20055195 Luis 2005/04/01 3000 16 rows selected. [ 다중열 SUBQUERY 예제 (=(=PAIRWISE SUBQUERY)] --EMPLOYEE테이블에서부서별로최소급여를받는사원의정보를사원번호, 이름, 급여, 부서코드를출력하여라. 단, 부서코드별로정렬한다. SQL> SELECT emp_no, emp_name, salary, dept_cd e WHERE (dept_cd, salary) IN (SELECT dept_cd, MIN(salary) GROUP BY dept_cd) ORDER BY dept_cd; EMP_NO EMP_NAME SALARY DEPT_CD ------------ -------------------- ----------- ----------- 20063428 James 9800 0000 20005012 Helen 1000 1000 19963998 Bill 1300 2000 19963077 Chris 1400 3000 4 rows selected. [FROM젃의 SUBQUERY 예제 (=INLINE VIEW)] --EMPLOYEE테이블과 DEPARTMENT테이블에서업무가 Sales인사원의이름, 부서명을출력하여라. SQL> SELECT e.emp_name, d.dept_name FROM (SELECT dept_cd, dept_name FROM department WHERE dept_name='sales') d, employee e WHERE d.dept_cd=e.dept_cd; EMP_NAME DEPT_NAME -------------------- -------------------- Chris Sales Jane Sales Bob Sales Nick Sales Robert Sales Leonardo Sales Luis Sales Joy Sales 8 rows selected. 44
[Scalar SUBQUERY 예제 ] --Scalar SUBQUERY를이용하여부서이름에따라사원번호와이름을정렬하시오. SQL> SELECT emp_no, emp_name e ORDER BY (SELECT dept_name FROM department d WHERE e.dept_cd=d.dept_cd); EMP_NO EMP_NAME ------------ -------------------- 19963998 Bill 19972002 Paul 19976229 Fernando 19992589 John 20027015 Karen 20028795 Mickey 19953472 Owner 19982201 Sandra 20 rows selected. [Correlated SUBQUERY 예제 ] --각부서의평균급여보다맋이받는사원정보를출력하여라. SQL> SELECT emp_name, salary, dept_cd e WHERE salary > (SELECT avg(salary) WHERE dept_cd=e.dept_cd); EMP_NAME SALARY DEPT_CD -------------------- ---------- ------------ Owner 10000 1000 David 7100 1000 Nicolas 7200 1000 Jane 4900 3000 Bob 6900 3000 Robert 7600 3000 Leonardo 5600 3000 Joy 6100 3000 Paul 4200 2000 Fernando 4300 2000 Karen 5100 2000 Mickey 4700 2000 12 rows selected. 45
7. DML DML(Data Manipulation Language) 명령은데이터를입력, 수정, 삭제하는 SQL 명령어이다. 7.1. DML 유형 DML 의유형과각구문문법다음과같다 종류 INSERT UPDATE DELETE 설명테이블에새로운행추가테이블의행내용을변경테이블의행삭제 [INSERT 구문 Syntax] INSERT INTO table_name [(column1[.cloumn2,...])] VALUES (value1[,value2,...]); [UPDATE 구문 Syntax] UPDATE table_name SET column1=value1 [, column2= value2,... ] [WHERE condition]; [DELETE 구문 Syntax] DELETE [FROM] table_name [WHERE condition]; 46
7.2. DML 예제 [INSERT 예제 ] -- 모든 column 에대해값을갖는새로운행을삽입. SQL> DESC employee TABLE INFO COLUMN_NAME TYPE CONSTRAINT ------------------------------ ------------------ -------------------------------- EMP_NO VARCHAR(8) NOT NULL PRIMARY KEY EMP_NAME VARCHAR(20) HIREDATE DATE SALARY NUMBER(8,3) BONUS NUMBER(8,3) DEPT_CD VARCHAR(4) MANAGER VARCHAR(8) INDEX INFO INDEX_NAME TYPE COLUMN_NAME ------------------------------ ----------------- ------------------------------ EMPLOYEE_PK NORMAL EMP_NO SQL> INSERT INTO employee VALUES ('20081120','Lolly',TO_DATE('2008-06-20', 'YYYY-MM-DD'),3000,'','1000','20032813'); 1 row inserted. --특정 column에맊값을넣어새로운행을삽입. ( 이경우라도. NOT NULL제약조건이있는열은반드시포함되어야합 ) SQL> INSERT INTO employee (emp_no, emp_name, hiredate, dept_cd) VALUES ('20083311','Angel',TO_DATE('2008-07-13', 'YYYY-MM-DD'), '0000'); 1 row inserted. -- 치홖변수 (&, &&) 를사용하여값을추가하는방법 SQL> INSERT INTO department (dept_cd, dept_name) VALUES (&department_id, &department_name); Input value for department_id: 5000 Input value for department_name: 'Marketing' 1 row inserted. --기졲의테이블로부터값을가져와테이블에추가 (VALUES 젃에 Subquery를사용하여행을삽입 ) SQL> CREATE TABLE employee_0000(emp_no, emp_name, hiredate) AS SELECT emp_no, emp_name, hiredate WHERE 1=2; Table created. 47
SQL> INSERT INTO employee_0000 SELECT emp_no, emp_name, hiredate WHERE dept_cd=0000; 2 rows inserted. [UPDATE 예제 ] --Insert예제에서삽입한행 emp_no가 20083311 인사원의부서코드를 0000에서 1000으로변경해보자. SQL> UPDATE employee SET dept_cd = '1000' WHERE emp_no='20083311'; 1 row updated. --Merge를이용한 Row 삽입및갱싞. 즉, Merge하고자하는 Source테이블의 row를읽어 target테이블에 match되는행이있을경우새로운값으로 UPDATE를수행하고, 없을경우새로운행으로 INSERT를수행. --사원번호가 20081120 인사원의보너스금액이졲재하면 update, 없으면 insert SQL> MERGE INTO employee d USING(SELECT emp_name,salary,dept_cd WHERE emp_no='20081120') s ON(d.emp_name=s.emp_name) WHEN MATCHED THEN UPDATE SET d.bonus= d.bonus+s.salary*0.1 WHEN NOT MATCHED THEN INSERT (d.emp_name,d.bonus) VALUES(s.emp_name,s.salary*0.1); ***Merge 문장 : 여러 Data Source 로부터다양한 Data 를수집해서작업을수행하는 Data Warehousing 작업에적합함. [DELETE 예제 ] --Insert 예제에서삽입한행 emp_no 가 20083311 인사원과 20081120 사원의정보를삭제하여라. SQL> DELETE employee WHERE emp_no='20083311' OR emp_no='20081120'; 2 rows deleted. 48
8. TRANSATION Tibero 는 TRANSACTION 이라고하는논리적인작업단위를나누어데이터의일관성을보장 한다. 8.1. TRANSACTION TRANSACTION 은첫번째 DML 문을실행하였을때시작된다. 트랜잭션정상종료 : COMMIT/ROLLBACK 의명령어를사용했을때, DDL 이나 DCL 문장사용했을때 트랜잭션비정상종료 : 사용자에의한 tbsql 의비정상종료또는시스템의비정상적인종료. ROLLBACK 처리 Transaction 제어명령어의종류는다음과같다. 종류 설명 COMMIT 데이터변경내용을영구히저장하고현재트랜잭션을종료. SAVEPOINT 트랜잭션내에저장위치를표시 ROLLBACK 데이터변경내용을버리고트랜잭션을종료. ROLLBACK TO SAVEPOINT_NAME 현재트랜잭션을지정된 (SAVEPOINT_NAME) 저장위치로돌아가, 그이젂의데이터변경내용을버리고트랜잭션을종료 49
8.2. TRANSACTION 예제 [TRANSACTION 예제 ] SQL> SELECT * FROM product; PROD_ID PROD_NAME PROD_GROUP PROD_COST ----------- -------------------- ----------------- -------------- 0100 AnyLink EAI 8000 0200 BizMaster EAI 12000 0300 JEUS MW 2900 0400 OpenFrame FRAME 7000 0500 ProERP BA 20000 0600 ProCRM BA 7000 0700 ProFrame FRAME 6200 0800 ProPortal UI 8600 0900 ProSync DBMS 2500 1000 ProWeb UI 2000 1100 SysKeeper ETC 1500 1200 SysMaster ETC 1400 1300 Tibero DBMS 1500 1400 Tmax MW 3800 1500 WebtoB MW 500 15 rows selected. SQL> UPDATE product SET prod_cost =2000 WHERE prod_name = 'Tibero'; 1 row updated. 트랜잭션시작 SQL> SAVEPOINT SV1; savepoint created. 트랜잭션저장 SV1 SQL> INSERT INTO product (prod_id) VALUES ('1600'); 1 row inserted. SQL> SAVEPOINT SV2; savepoint created. 트랜잭션저장 SV2 SQL> INSERT INTO product (prod_id) VALUES ('1700'); 1 row inserted. SQL> rollback to SV2; rollback succeeded. SV2 이후작업무시 SQL> rollback to SV1; SV1 이후작업무시 SQL> rollback to SV2; TBR-21008: Savepoint 'SV2' was not found. ROLLBACK/COMMIT 이되면 SAVEPOINT 는삭제된다 50
Information Intelligence, Tibero