SQLD 자격증 스터디

Oracle/Oracle

SQLD 자격증 스터디

longer 2015. 1. 18. 15:57

1.CREATE TABLE

2.ALTER TABLE

2-1. ADD COLUMN ; 기존에 테이블에 필요한 컬럼을 추가

ALTER TABLE 테이블 명

ADD 추가할 칼럼명 데이터 유형;

2-2. DROP COLUMN ; 데이터가 있거나 없거나 모두 삭제 가능. 한번에 하나의 칼럼만 삭제 가능하며,

칼럼 삭제 후 최소 하나 이상의 칼럼이 테이블에 존재해야 하며 한번 삭제된 칼럼은 복구 불가.

ALTER TABLE 테이블 명

DROP COLUMN 삭제할 컬럼명;

2-3. MODIFY COLUMN ; 테이블에 존재하는 칼럼에 칼럼 유형을 변경

ALTER TABLE 테이블 명

MODIFY (칼럼명1 데이터 유형 [Default식][NOT NULL],

칼럼명2 데이터 유형...);

또는

ALTER TABLE 테이블명

ALTER (칼럼명1 데이터 유형 [Default식][NOT NULL],

칼럼명2 데이터 유형...);

칼럼을 변경할 때 고려사항

-칼럼의 크기를 늘릴수는 있지만 줄이지는 못한다. (기존 데이터가 훼손될 수 있기 때문에)

-null값만 가지고 있거나 테이블에 아무 행도 없으면 칼럼의 폭을 줄일 수 있다.

-null값만 가지고 잇으면 데이터 유형을 변경할 수 있다.

-default값을 바꾸면 변경 작업 이후 발생하는 행 삽입에만 영향을 미치게 된다.

-null값이 없을 경우에만 not null 제약조건을 추가할 수 있다.

2-4. RENAME COLUMN ;테이블 생성하면서 만들어졌던 칼럼명을 어떤 이유로 불가피하게 변경해야하는 경우 사용

ALTER TABLE 테이블명

RENAME COLUMN 변경해야할 칼럼명 TO 새로운 칼럼명;

2-5. DROP CONSTRAINT ; 테이블 생성 시 부여했던 제약조건을 삭제하는 명령어

ALTER TABLE 테이블명

DROP CONSTRAINT 제약조건명;

2-6. ADD CONSTRAINT ; 테이블 생성이후에 제약조건을 추가하는 명령어

ALTER TABLE 테이블명

ADD CONSTRAINT 제약조건명 제약조건(칼럼명);

3.RENAME TABLE

3-1. RENAME ; RENAME명령어를 사용하여 테이블의 이름을 변경 할 수 있다.

RENAME 변경전 테이블명 TO 변경후 테이블명;

4.DROP TABLE

4-1. DROP ; 잘못만든 테이블을 삭제해야하는 경우 사용

DROP TABLE 테이블명 [CASECADE CONSTRAINT];

DROP명령어를 사용하면 테이블의 모든 데이터 및 구조를 삭제한다.

CASECADE CONSTRAINT옵션은 해등 테이블과 관계가 있었던 참조되는 제약조건에 대해서도 삭제한다는 것을 의미.

5.TRUNCATE TABLE

5-1. TRUNCATE ; 테이블 자체가 삭제되는게 아니고 해당 테이블에 들어있던 모든 행들이 제거되고 저장공간을

재사용가능하도록 해제한다.

TRUNCATE TABLE 테이블명;

※TRUNCATE는 데이터 구조의 변경없이 테이블의 데이터를 일괄 삭제하는 명령어로 DML로 분류할 수도 있지만 내부처리 방식이나 Auto commit특성 등으로 인해 DDL로 분류된다.(DELETE보다 부하가 적음 - 메모리 사용x

단, 정상적인 복구가 불가능 하므로 사용에 유의해야 한다.)

[DML - DATA MANIPULATION LANGUAGE]

1.INSERT

INSERT INTO 테이블명(COLUMN_LIST)

VALUES (COLUMN_LIST에 넣은 VALUE_LIST);

또는

INSERT INTO 테이블명

VALUES(전체 COLUMN에 넣을 VALUE_LIST);

2.UPDATE

UPDATE 테이블명

SET 수정되어야할 컬럼명 = 수정되기를 원하는 새로운 값 ;

3.DELETE

DELETE FROM 테이블명 ;

※DML(INSERT, UPDATE, DELETE, SELECT)명령어의 경우, 조작하려는 테이블을 메모리 버퍼에 올려놓고 작업을 하기 때문에 실시간으로 테이블에 영향을 미치는 것은 아니다. 따라서 버퍼에서 처리한 DML명령어가 실제 테이블에 반영되기 위해서는 COMMIT명령어를 입력해서 TRANSACTION을 종료해야한다.

테이블의 전체데이터를 삭제하는 경우, 시스템 활용 측면에서는 삭제된 데이터를 로그로 저장하는 DELETE TABLE보다는 시스템 부하가 적은 TRUNCATE TABLE을 권고한다.

5.SELECT

SELECT 칼럼.. FROM 테이블;

[산술연산자와 합성 연산자]

-산술연산자

산술연산자는 NUMBER와 DATE자료형에 대해 적용되며 일반적으로 수학에서의 4칙 연산과 동일하다.

수학에서와 같이 () , * , / , + , -의 우선순위를 가진다.

-합성연산자(CONCATE)연산자

문자와 문자를 연결하는 합성 연산자를 사용하면 별도의 프로그램 도움 없이도 SQL문장만으로도 유용한 리포트를 출력할 수 잇다. 합성 연산자의 특징은 다음과 같다.

-문자와 문자를 연결하는 경우 2개의 수직바(||)에 의해 이루어진다.

-문자와 문자를 연결하는 경우 + 표시에 의해 이루어진다.

-칼럼과 문자 또는 다른 칼럼과 연결시킨다.

-문자표현식의 결과에 의해 새로운 칼럼을 생성한다.

예제) 선수명 선수, 키cm , 몸무게 kg

싸빅선수 185cm 78kg

SELECT PLAYER_NAME||'선수' 선수명, HEIGHT||'cm' 키 , WEIGHT||'kg' 몸무게
FROM PLAYER

[TCL - TRANSCATIN CONTROL LANGUAGE]

-특징

-원자성; 트랜잭션에 정의된 연산들은 모두 성공적으로 실행되던지 아니면 전혀 실행되지 않은 상태로 남아있어야 한다.

-일관성; 트랜잭션이 실행되지 전의 데이터 내용이 잘못되어있지 않다면 트랜잭션이 실행된 이후에도

데이터 베이스 내용이 잘못되어있어서는 안된다.

-고립성; 트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안된다.

-지속성; 트랜잭션이 성공적으로 수행되면 그 트랜잭션이 생신한 데이터베시스의 내용은 영구적으로 저장된다.

*COMMIT

입력한 자료나 수정한 자료에 대해서 또는 삭제한 자료에 대해서 전혀 문제가 없다고 판단되었을 경우 COMMIT명령어를 통해서 트랜잭션을 완료할 수 있다.

-단지 메모리 BUFFER에만 영향을 받았기 때문에 데이터의 변경 이전 상태로 복구가 가능하다.

-현재 사용자는 SELECT문장으로 결과를 확인 가능하다.

-다른 사용자는 현재 사용자가 수행한 명령의 결과를 볼 수 없다.

-변경된 행은 잠금이 설정되어서 다른 사용자가 변경할 수 없다.

-AUTO COMMIT

명령어가 성공적으로 수행되면 자동으로 COMMIT을 수행하고 오류가 발생하면 자동으로 ROLLBACK을 수행한다.

-암시적 트랜잭션

Oracle과 같은 방식으로 처리된다. 즉, 트랜잭션의 시작은 DBMS가 처리하고 트랜잭션의 끝은 사용자가 명시적으로 COMMIT또는 ROLLBACK으로 처리한다. 인스턴스 단위 또는 세션 단위로 설정할 수 있다. 인스턴스 단위로 설정하려면 서버 속성 창의 연결화면에서 기본연결 옵션 중 암시적 트랜잭션에 체크를 해주면 된다. 세션 단위로 설정하기 위해서는 세션 옵션 중 SET IMPLICIT TRANSACTIN ON을 사용하면 된다.

-명시적 트랜잭션

트랜잭션의 시작과 끝은 모두 사용자가 명시적으로 지정하는 방식이다. BEGIN TRANSACTION으로 트랜잭션을 시작하고 COMMIT TRANSACTION또는 ROLLBACK TRANSACTION으로 트랜잭션을 종료한다. ROLLBACK구문을 만나면 최초의 BEGIN TRANSACTION시점까지 모두 ROLLBACK이 수행된다.

-ROLLBACK

테이블 내 입력한 데이터나 수정한 데이터, 삭제한 데이터에 대하여 COMMIT이전에는 변경 사항을 취소할 수 있는데 데이터베이스에서는 롤백 기능을 사용한다.

-SAVEPOINT

저장점을 정의하면 롤백할 때 트랜잭션에 포함된 전체 작업을 롤백하는 것이 아니라 현 시점에서 SAVEPOINT까지 트랜잭션의 일부만 롤백할 수 있다. 따라서 복잡한 대규모 트랜잭션에서 에러가 발생했을 때 SAVEPOINT까지의 트랜잭션만 롤백하고 실패한 부분에 대해서만 다시 실행할 수 있다.

**IUD시 그 변경되는 데이터의 무결성을 보장하는 것이 커밋과 롤백의 목적이다.

예제 1)
소속팀이 삼성블루윙즈이거나 전남 드래곤즈에 소속된 선수들이어야 하고,
포지션이 미드필더이어야 한다.
키는 170cm이상이고 180이하여야 한다.

조회 테이블 : TEAM A, PLAYER B
컬럼 : A.TEAM_NAME , B.POSITION, B.HEIGHT

QUERY :
SELECT * FROM PLAYER
WHERE team_id IN('K07','K02')
AND POSITION = 'MF'
AND HEIGHT>=170 AND HEIGHT<=180

예제2)사원 테이블에서 JOB이 MANAGER이면서 20번 부서에 속하거나
JOB이 CLERK이면서 30번 부서에 속하는 사원의 정보를
IN연산자의 다중 리스트를 이용해 출력하라.

QUERY :
SELECT * FROM EMP
WHERE (JOB,DEPTNO) IN(('MANAGER',20),('CLERK',30))

3)
삼성 블루윙즈 소속인 선수들 중에서 포지션이 MF(MID FIELD)가 아니고
키가 175cm 이상 185cm 이하가 아닌 선수들의 자료를 찾아본다.

QUERY :
SELECT *FROM PLAYER
WHERE TEAM_ID ='K02'
AND NOT POSITION ='MF'
AND HEIGHT NOT BETWEEN 175 AND 185

*문자형 함수) 문자를 입력하면 문자나 숫자값을 반환한다.

1. LOWER - 소문자로 표현 / UPPER - 대문자로 표현

SQL> select 34.4578, floor(34.5678) from dual;

34.4578 FLOOR(34.5678)
---------- --------------
34.4578 34

SQL> SELECT 'abc', UPPER('abc') FROM DUAL;

'AB UPP
--- ---
abc ABC

2.SUBSTR - 문자형 데이터를 지정 사이즈만큼 자르기

SQL> SELECT 'IT_PROG', SUBSTR('IT_PROG',4) FROM DUAL; //4번째 부터 출력

'IT_PRO SUBS
------- ----
IT_PROG PROG

SQL> SELECT 'IT_PROG', SUBSTR('IT_PROG',0,2) FROM DUAL; //0부터 2번째 까지만 출력

'IT_PRO SU
------- --
IT_PROG IT

SQL> SELECT 'IT_PROG', SUBSTR('IT_PROG',-4) FROM DUAL; //뒤에서부터 4번째

'IT_PRO SUBS
------- ----
IT_PROG PROG

3.LENGTH - 문자열 길이 구하기

SQL> SELECT 'IT_PROG', LENGTH('IT_PROG') FROM DUAL;

'IT_PRO LENGTH('IT_PROG')
------- -----------------
IT_PROG 7

4.LTRIM / RTRIM / TRIM - 문자열 공백제거

SQL> SELECT TEST, LENGTH(TEST), LTRIM(TEST), LENGTH(LTRIM(TEST)) FROM TEST01;

TEST LENGTH(TEST) LTRIM(TEST) LENGTH(LTRIM(TEST))
-------------------- ------------ -------------------- -------------------
TEST _ 01 _ TEST 18 TEST _ 01 _ TEST 17

SQL> SELECT TEST, LENGTH(TEST), RTRIM(TEST), LENGTH(RTRIM(TEST)) FROM TEST01;

TEST LENGTH(TEST) RTRIM(TEST) LENGTH(RTRIM(TEST))
-------------------- ------------ -------------------- -------------------
TEST _ 01 _ TEST 18 TEST _ 01 _ TEST 17

SQL> SELECT TEST, LENGTH(TEST), TRIM(TEST), LENGTH(TRIM(TEST)) FROM TEST01;

TEST LENGTH(TEST) TRIM(TEST) LENGTH(TRIM(TEST))
-------------------- ------------ -------------------- ------------------
TEST _ 01 _ TEST 18 TEST _ 01 _ TEST 16

5. ASCII - 문자열의 알파벳 문자를 ASCII 코드 번호로바꾸어줌

SQL> SELECT 'A', ASCII('A') FROM DUAL;

' ASCII('A')
- ----------
A 65

6.CHR - ASCII코드 번호를 문자나 숫자로 변환
SQL> SELECT 65, CHR(65) FROM DUAL;

65 C
---------- -
65 A

7.CONCAT(문자열1, 문자열2) -문자와문자를 연결함
SQL> SELECT 'TEST', '01', CONCAT('TEST','01') FROM DUAL;

'TES '0 CONCAT
---- -- ------
TEST 01 TEST01

>>> 2개의 문자열만 조합가능
>>> || 이용하여 여러개의문자조합 가능

SQL> SELECT 'TEST', '01','02', 'TEST'||'01'||'02' FROM DUAL;

'TES '0 '0 'TEST'||
---- -- -- --------
TEST 01 02 TEST0102

*)숫자형 함수 - 숫자를 입력하면 숫자값을 반환한다.
1.ABS - 절대값을 돌려준다.
SQL> SELECT -10, ABS(-10) FROM DUAL;

-10 ABS(-10)
---------- ----------
-10 10

2.MOD - MOD(숫자1,숫자2) - 숫자1을 숫자2로 나누어 나머지 값을 리턴
SQL> SELECT MOD(10,2) FROM DUAL;

MOD(10,2)
----------
0

SQL> SELECT MOD(5,2) FROM DUAL;

MOD(5,2)
----------
1

3.CEIL - 숫자보다 크거나 같은 최소 정수를 리턴(올림)

SQL> SELECT 10.2, CEIL(10.2) FROM DUAL;

10.2 CEIL(10.2)
---------- ----------
10.2 11

SQL> SELECT -10.2, CEIL(-10.2) FROM DUAL;

-10.2 CEIL(-10.2)
---------- -----------
-10.2 -10

4.FLOOR - 숫자보다 작거나 같은 최대 정수를 리턴(내림)

SQL> SELECT 34.56789, FLOOR(34.56789) FROM DUAL;

34.56789 FLOOR(34.56789)
---------- ---------------
34.56789 34

SQL> SELECT -13.2, FLOOR(-13.2) FROM DUAL;

-13.2 FLOOR(-13.2)
---------- ------------
-13.2 -14

5.ROUND - 소수점 반올림 //자릿수 지정 가능

SQL> SELECT 34.56789, ROUND(34.56789) FROM DUAL;

34.56789 ROUND(34.56789)
---------- ---------------
34.56789 35

SQL> SELECT 34.56789, ROUND(34.56789,2) FROM DUAL;

34.56789 ROUND(34.56789,2)
---------- -----------------
34.56789 34.57

6,TRUNC - 특정 자리수 버리기

SQL> SELECT TRUNC(15.5678,1), TRUNC(15.5678,-1), TRUNC(15.5678)FROM DUAL;

TRUNC(15.5678,1) TRUNC(15.5678,-1) TRUNC(15.5678)
---------------- ----------------- --------------
15.5 10 15

7.SIGN - 숫자가 양수인지 음수인지 0인지 구분

SQL> SELECT SIGN(10), SIGN(-10), SIGN(0) FROM DUAL;

SIGN(10) SIGN(-10) SIGN(0)
---------- ---------- ----------
1 -1 0
:정수면1, 음수면 -1 0이면 0 리턴

8.SIN/COS/TAN - 숫자의 삼각함수값 리턴
9.EXP/POWER/SQRT/LOG/LN - 숫자의 지수, 거듭 제곱, 제곱근, 자연 로그 값을 리턴

SQL> SELECT EXP(10), POWER(10,2), SQRT(10), LOG(10,2), LN(10) FROM DUAL;

EXP(10) POWER(10,2) SQRT(10) LOG(10,2) LN(10)
---------- ----------- ---------- ---------- ----------
22026.4658 100 3.16227766 .301029996 2.30258509

*)날짜형 항수
1.SYSDATE - 현재 날짜와 시각을 출력

SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
--------
15/01/18

2)EXTRACT - 날짜 데이터에서 년/월/일 데이터를 출력할 수 있다.시/분/초 도 가능함
SQL> SELECT EXTRACT(YEAR FROM DATE '1998-03-07') FROM DUAL;

EXTRACT(YEARFROMDATE'1998-03-07')
---------------------------------
1998

SQL> SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL;

EXTRACT(YEARFROMSYSDATE)
------------------------
2015

SQL> SELECT EXTRACT(MONTH FROM SYSDATE) FROM DUAL;

EXTRACT(MONTHFROMSYSDATE)
-------------------------
1

SQL> SELECT EXTRACT(DAY FROM SYSDATE) FROM DUAL;

EXTRACT(DAYFROMSYSDATE)
-----------------------
18

3. TO_NUMBER(TO_CHAR(d,'YYYY')) / TO_NUMBER(TO_CHAR(d,MM'))/ TO_NUMBER(d,'DD'))
- 날짜 데이터에 년/월/일 데이터를 출력할 수 있다. TO_NUMBER함수를 제외할 경우
문자형으로 출력이 된다.
'
SQL> SELECT TO_NUMBER(TO_CHAR(HIRE_DATE,'YYYY')) "YEAR",
2 TO_NUMBER(TO_CHAR(HIRE_DATE,'MM')) "MONTH",
3 TO_NUMBER(TO_CHAR(HIRE_DATE,'DD')) "DAY" FROM EMPLOYEES;

      YEAR      MONTH        DAY
---------- ---------- ----------
      2007          6         21
      2008          1         13
      2003          9         17
      2004          2         17

*)변환형 함수

명시적 데이터 유형 변환 함수 :
1)TO_NUMBER(문자열) - alphanumeric 문자열을 숫자로 변환한다.

2)TO_CHAR(숫자|날짜, FORMAT); - 숫자나 날짜를 주어진 FORMAT형태로 문자열 타입으로 변환

SQL> SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD') "날짜",
2 TO_CHAR(SYSDATE,'YYYY, MON, DAY') "문자형" FROM DUAL;

날짜 문자형
---------- -------------------------
2015/01/18 2015, 1월 , 일요일

*)CASE 표현
1.SIMPLE_CASE_EXPRESSION - SIMPLE_CASE_EXPRESSION조건이 맞으면 SIMOLE_CASE_EXPRESSION
조건내의 THEN절을 수행하고, 조건이 맞지 않으면 ELSE절을 수행한다.

CASE
SIMPLE_CASE_EXPRESSION 조건
ELSE 표현절
END

2.SEARCHED_CASE_EXPRESSION - SEARCHED_CASE_EXPRESSION 조건이 맞으면 SEARCHED_CASE_EXPRESSION 조건내의
THEN 절을 수행하고, 조건이 맞지 않으면 ELSE절을 수행한다.

CASE
SEARCHED_CASE_EXPRESSION 조건
ELSE 표현절
END

3.DECODE - Oracle에서만 상요되는 함수로, 표현식의 값이 기준값1이면 값1을
출력하고, 기준값2면 값2를 출력한다. 그리고 기준값이 없으면 디폴트값을 출력한다.
CASE표현의 SIMPLE_CASE_EXPRESSION조건과 동일하다.

예제) 부서정보에서 부서위치를 미국의 동부, 중부, 서부로 구분한다.

SQL> SELECT LOC, CASE LOC WHEN 'NEW YORK' THEN 'EAST'
2 WHEN 'BOSTON' THEN 'EAST'
3 WHEN 'DALLAS' THEN 'CENTER'
4 WHEN 'CHICAGO' THEN 'CENTER'
5 ELSE 'ETC'
6 END as AREA
7 FROM DEPT;

LOC           AREA
------------- ------
NEW YORK      EAST
DALLAS        CENTER
CHICAGO       CENTER
BOSTON        EAST

예제) 사원 정보에서 급여가 3000 이상이면 상등급으로, 1000 이상이면 중등급으로,
1000미만이면 하등급으로 분류하라.

SELECT ENAME,
       SAL,
       CASE WHEN SAL>=3000 THEN '상'
       WHEN SAL>=1000 THEN '중'
       WHEN SAL<1000 THEN '하'
ELSE 'N/A'
END as GRADE FROM EMP;

예제)사원 정보에서 급여가 2000 이상이면 보너스를 10000으로,
1000이상이면 500으로, 1000미만이면 0으로 계산한다.

SELECT ENAME,
       SAL,
       CASE WHEN SAL>=2000 THEN 10000
       WHEN SAL>=1000 THEN 500
       ELSE 0
       END as BONUS
FROM EMP;

*)NULL관련 함수
1.NVL(표현식1, 표현식2) - 표현식 1의 결과값이 NULL이면 표현식2의 값을 출력한다.
단, 표현식1과 표현식2의 결과 데이터 타입이 같아야 한다.
NULL관련 가장 많이 사용되는 함수이므로 상당히 중요함.★★

예제) 선수 테이블에서 성남 일화천마(K08)소속 선수의 이름과 포지션을 출력하는데,
포지션이 없는 경우는 '없음'으로 표시한다.

NVL 사용 ;
SELECT PLAYER_NAME, TEAM_ID, NVL(POSITION,'없음') POSITION FROM PLAYER

CASE 사용 ;
SELECT PLAYER_NAME,
       TEAM_ID,
       CASE WHEN POSITION IS NULL THEN '없음'
       ELSE POSITION
       END as POSITION
FROM PLAYER

예제)급여와 커미션을 포함한 연봉을 계산

2.NULLIF(표현식1,표현식2) - 표현식1이 표현식2와 같으면 NULL을,
같지 않으면 표현식1을 리턴한다.

예제) 사원 테이블에서 MGR와 7698이 같으면 NULL을 표시하고, 같지 않으면 MGR을
표시한다.

SELECT ENAME,NULLIF(7698,MGR) FROM EMP

SELECT ENAME, CASE MGR WHEN 7698 THEN NULL
ELSE MGR
END as NULIF
FROM EMP

임의의 개수 EPR에서 NULL이 아닌 최초의 EXPR을 나타낸다. 만일 모든 EPR이
NULL이라면 NULL을 리턴한다.

예제) 사원테이블에서 커미션을 1차 선택값으로, 급여를 2차 선택값으로
선택하되 두 칼럼 모두 NULL인 경우 NULL로 표시한다.

SELECT ENAME, COMM, SAL, COALESCE(COMM,SAL) FROM EMP;

SELECT ENAME,COMM,SAL,
       CASE WHEN COMM IS NOT NULL THEN COMM
            WHEN SAL IS NOT NULL THEN SAL
       ELSE NULL
       END as GOAL
FROM EMP;

*)집계함수
예제)포지션별 최대,최소,평균 키 구하기

SELECT POSITION, COUNT(*), COUNT(HEIGHT),MAX(HEIGHT),MIN(HEIGHT),AVG(HEIGHT) FROM PLAYER
GROUP BY POSITION

>>POSITION DF의 경우, HEIGHT이 NULL이 아닌 142명만 집계함수 대상으로 계산되었음

예제)k리그 선수들의 포지션별 평균키를 구하는데, 평균키가 180센티미터 이상인
정보만 표시하라는 요구사항이 접수되었으므로 where절과 group by 절을
사용해 sql문장을 작성한다.

SELECT * FROM (
SELECT POSITION, AVG(HEIGHT) HEIGHT
FROM PLAYER
GROUP BY POSITION
) A WHERE A.HEIGHT>180

SELECT POSITION "포지션", ROUND(AVG(HEIGHT),2) "평균키"
FROM PLAYER
GROUP BY POSITION
HAVING AVG(HEIGHT) >=180

COUNT(*), COUNT(표현식),SUM,AVG,MAX,MIN,STDDEV,VARIAN,

예제)
k리그의 선수들 중 삼성블루윙즈(k02)와 FC서울(k09)의 인원수는 얼마인가란 요구사항이 접수되었다.
where절과 group by절을 사용한 sql과 group by절과 having절을 사용한 sql을 모두 작성한다.

QUERY:
select team_id, count(player_name)
from player
where team_id ='K02'or team_id='K09'
group by team_id

select team_id
,count(player_name)
from player
group by team_id
having team_id in('K02','K09');

예제)포지션별 평균키만 출력하는데, 최대키가 190CM이상인 선수를 가지고 있는
포지션의 정보만 출력한다.

query:
SELECT POSITION "포지션"
,AVG(HEIGHT) "평균키"
FROM PLAYER
GROUP BY POSITION
HAVING MAX(HEIGHT)>=190

*)case표현을 활용한 월별 데이터 집계

예제1)개별 데이터 확인 - 개별 입사정보에서 월별 데이터를 추출하는 작업을 진행한다.
이 단계는 월별 정보가 있다면 생략한다.

QUERY:
SELECT ENAME, DEPTNO, EXTRACT(MONTH FROM HIREDATE) "입사월", SAL
FROM EMP

예제2)월별 데이터 구분 - 추출된 month데이터를 simple case Expression을 이용해 12개의 월별 칼럼으로 구분한다.
실행결과에서 보여주는 ename컬럼은 최종 리포트에서 요구되는 데이터는 아니지만, 정보의 흐름을
이해하기 위해 부가적으로 보여주는 임시정보이다.

QUERY:

SELECT ENAME,
       DEPTNO,
       CASE EXTRACT(MONTH FROM HIREDATE) WHEN 1 THEN SAL END M01,
       CASE EXTRACT(MONTH FROM HIREDATE) WHEN 2 THEN SAL END M02,
       CASE EXTRACT(MONTH FROM HIREDATE) WHEN 3 THEN SAL END M03,
       CASE EXTRACT(MONTH FROM HIREDATE) WHEN 4 THEN SAL END M04,
       CASE EXTRACT(MONTH FROM HIREDATE) WHEN 5 THEN SAL END M05,
       CASE EXTRACT(MONTH FROM HIREDATE) WHEN 6 THEN SAL END M06,
       CASE EXTRACT(MONTH FROM HIREDATE) WHEN 7 THEN SAL END M07,
       CASE EXTRACT(MONTH FROM HIREDATE) WHEN 8 THEN SAL END M08,
       CASE EXTRACT(MONTH FROM HIREDATE) WHEN 9 THEN SAL END M09,
       CASE EXTRACT(MONTH FROM HIREDATE) WHEN 10 THEN SAL END M10,
       CASE EXTRACT(MONTH FROM HIREDATE) WHEN 11 THEN SAL END M11,
       CASE EXTRACT(MONTH FROM HIREDATE) WHEN 12 THEN SAL END M12
FROM EMP;

예제3)최종적으로 보여주는 리포트는 부서별로 월별 입사자의 평균 급여를 알고 싶다는 요구사항이므로 부서별
평균값을 구하기 위해 group by 절과 avg집계 함수를 사용한다. 직원 개인에 대한 정보는 더 이상 필요없으므로 제외한다.
ORDER BY절을 사용하지 않았기 때문에 부서번호별로 정렬이 되지는 않는다.

QUERY:
(내가함)
SELECT DEPTNO,
       AVG(M01),AVG(M02),AVG(M03),AVG(M04),AVG(M05),AVG(M06),
       AVG(M07),AVG(M08),AVG(M09),AVG(M10),AVG(M11),AVG(M12)
FROM(
SELECT --ENAME,
       DEPTNO,
       CASE EXTRACT(MONTH FROM HIREDATE) WHEN 1 THEN SAL END M01,
       CASE EXTRACT(MONTH FROM HIREDATE) WHEN 2 THEN SAL END M02,
       CASE EXTRACT(MONTH FROM HIREDATE) WHEN 3 THEN SAL END M03,
       CASE EXTRACT(MONTH FROM HIREDATE) WHEN 4 THEN SAL END M04,
       CASE EXTRACT(MONTH FROM HIREDATE) WHEN 5 THEN SAL END M05,
       CASE EXTRACT(MONTH FROM HIREDATE) WHEN 6 THEN SAL END M06,
       CASE EXTRACT(MONTH FROM HIREDATE) WHEN 7 THEN SAL END M07,
       CASE EXTRACT(MONTH FROM HIREDATE) WHEN 8 THEN SAL END M08,
       CASE EXTRACT(MONTH FROM HIREDATE) WHEN 9 THEN SAL END M09,
       CASE EXTRACT(MONTH FROM HIREDATE) WHEN 10 THEN SAL END M10,
       CASE EXTRACT(MONTH FROM HIREDATE) WHEN 11 THEN SAL END M11,
       CASE EXTRACT(MONTH FROM HIREDATE) WHEN 12 THEN SAL END M12
FROM EMP)
GROUP BY DEPTNO;

예제)팀별 포지션별 FW, MF,DF,GK 포지션의 인원수와 팀별 전체 인원수를 구하는 SQL문장을
작성한다. 데이터가 없는 경우는 0으로 표시한다.

QUERY:
(내가 한것)
SELECT TEAM_ID, NVL(SUM(FW),0) FW,NVL(SUM(MF),0) MF,NVL(SUM(DF),0) DF,NVL(SUM(GK),0) GK
FROM(
SELECT TEAM_ID,CASE POSITION WHEN 'FW' THEN 1 END FW,
               CASE POSITION WHEN 'MF' THEN 1 END MF,
               CASE POSITION WHEN 'DF' THEN 1 END DF,
               CASE POSITION WHEN 'GK' THEN 1 END GK
FROM PLAYER)
GROUP BY TEAM_ID

(교재)
SELECT TEAM_ID,NVL(SUM(CASE POSITION WHEN 'FW' THEN 1 END ),0) FW,
               NVL(SUM(CASE POSITION WHEN 'MF' THEN 1 END ),0) MF,
               NVL(SUM(CASE POSITION WHEN 'DF' THEN 1 END ),0) DF,
               NVL(SUM(CASE POSITION WHEN 'GK' THEN 1 END ),0) GK,
               COUNT(*) SUM
FROM PLAYER
GROUP BY TEAM_ID

예제)GROUP BY 절 없이 전체 선수들의 포지션별 평균 키 및 전체 평균 키 출력

QUERY:
SELECT ROUND(AVG(CASE POSITION WHEN 'MF' THEN HEIGHT END),2) MF,
       ROUND(AVG(CASE POSITION WHEN 'FW' THEN HEIGHT END),2) FW,
       ROUND(AVG(CASE POSITION WHEN 'DF' THEN HEIGHT END),2) DF,
       ROUND(AVG(CASE POSITION WHEN 'GK' THEN HEIGHT END),2) GK,
       ROUND(AVG(HEIGHT),2) AVG
FROM PLAYER

*)EQUI JOIN

예제) 팀테이블과 구장 테이블의 관계를 이용해서 소속팀이 가지고 있는 전용구장의 정보를 팀의 정보와 함께 출력하라.
QUERY:
SELECT TEAM_ID,
       TEAM_NAME,
       REGION_NAME,
       STADIUM_ID
FROM TEAM

SELECT STADIUM_ID,
STADIUM_NAME,
SEAT_COUNT
FROM STADIUM

SELECT REGION_NAME,
       TEAM_NAME,
       B.STADIUM_ID,
       STADIUM_NAME,
       SEAT_COUNT
FROM TEAM A,STADIUM B
WHERE A.STADIUM_ID=B.STADIUM_ID

*)INNER JOIN

예제)
사원 번호와 사원 이름, 소속 부서 코드와 소속부서 이름을 찾아본다.

SELECT EMPNO, ENAME, DEPTNO
FROM EMP

SELECT DEPTNO, DNAME
FROM DEPT

(DEFAULT 옵션)
SELECT EMPNO, ENAME, D.DEPTNO, DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO

SELECT EMPNO, ENAME, D.DEPTNO, DNAME
FROM EMP E JOIN DEPT D
ON E.DEPTNO=D.DEPTNO

*)NATURAL JOIN
-두 테이블간의 동일한 이름을 갖는 모든 컬럼들에 대해 equi join을 수행함
-NATURAL JOIN으로 명시되면 추가로 using 조건절, where 조건절에서 join조건을 정의할 수 없다.

예제)
SELECT EMPNO, ENAME, DEPTNO, DNAME
FROM EMP NATURAL JOIN DEPT

**참고 - join에 사용된 커럼들은 같은 데이터 유형이어야 하며, alias나 접두사를 붙일 수 없음

CREATE TABLE DEPT_TEMP
AS SELECT * FROM DEPT;

UPDATE DEPT_TEMP
SET DNAME = 'MARKETING'
WHERE DNAME='SALES'

SELECT *
FROM DEPT_TEMP

SELECT *
FROM DEPT NATURAL INNER JOIN DEPT_TEMP

SELECT *
FROM DEPT D INNER JOIN DEPT_TEMP T
ON D.DEPTNO=T.DEPTNO
AND D.DNAME=T.DNAME
AND D.LOC=T.LOC

*)USING 조건절

SELECT *
FROM DEPT JOIN DEPT_TEMP
USING (DEPTNO);

SELECT ENAME, EMPNO, D.DEPTNO, DNAME
FROM EMP E JOIN DEPT D
ON E.DEPTNO=D.DEPTNO
--AND D.DEPTNO=30
WHERE E.MGR=7698

예제)사원과 dept테이블의 소속 부서명, DEPT_TEMP테이블의 바뀐 부서명 정보를 출력한다.
QUERY:
SELECT ENAME, EMPNO,D.DEPTNO, DNAME
FROM EMP ,(
SELECT DEPT.DEPTNO, DEPT.DNAME
FROM DEPT INNER JOIN DEPT_TEMP
ON DEPT.DEPTNO=DEPT_TEMP.DEPTNO
AND DEPT.DNAME!=DEPT_TEMP.DNAME) D
WHERE EMP.DEPTNO=D.DEPTNO ----------문제를 잘못이해함 ㅋ

SELECT E.EMPNO,
       E.ENAME,
       D.DEPTNO,
       D.DNAME,
       T.DNAME "RENAME"
FROM EMP E JOIN DEPT D
ON E.DEPTNO=D.DEPTNO
    JOIN DEPT_TEMP T
ON E.DEPTNO=T.DEPTNO

예제)GK포지션의 선수별 연고지명, 팀명, 구장명을 출력한다.

SELECT --P.PLAYER_ID 선수명,
       P.PLAYER_NAME 선수명,
       P.POSITION 포지션,
       T.REGION_NAME 연고지명,
       T.TEAM_NAME 팀명,
       --S.STADIUM_ID,
       S.STADIUM_NAME 구장명
FROM PLAYER P JOIN TEAM T
    ON P.TEAM_ID=T.TEAM_ID
JOIN STADIUM S
    ON T.STADIUM_ID=S.STADIUM_ID
WHERE P.POSITION='GK'
ORDER BY PLAYER_NAME

예제)홈팀이 3점 이상 차이로 승리한 경기의
경기장 이름, 경기일정, 홈팀 이름과 원정팀 이름 정보를 출력

QUEYR:
SELECT SC.STADIUM_ID,
       SC.HOMETEAM_ID,
       SC.AWAYTEAM_ID,
       SC.SCHE_DATE,
       SC.HOME_SCORE,
       SC.AWAY_SCORE,
       ST.STADIUM_NAME,
       T.TEAM_NAME 홈팀명,
       TT.TEAM_NAME 맞수명
FROM STADIUM ST JOIN SCHEDULE SC
    ON ST.STADIUM_ID=SC.STADIUM_ID
    AND SC.HOME_SCORE-SC.AWAY_SCORE>=3
JOIN TEAM T
    ON SC.HOMETEAM_ID=T.TEAM_ID
JOIN TEAM TT
    ON SC.AWAYTEAM_ID=TT.TEAM_ID

*)LEFT OUTER JOIN
예제) STADIUM에 등록된 운동장 중에는 홈팀이 없는 경기장도 있다.
STADIUM과 TEAM을 JOIN하되 홈팀이 없는 경기장의 정보도 같이 출력하도록 한다.

QUERY:
SELECT S.STADIUM_NAME,
T.TEAM_NAME
FROM STADIUM S LEFT OUTER JOIN TEAM T
ON S.HOMETEAM_ID=T.TEAM_ID

SELECT STADIUM_NAME,
TEAM_NAME
FROM STADIUM S, TEAM T
WHERE S.HOMETEAM_ID=T.TEAM_ID(+)

*)RIGHT OUTER JOIN

예제)DEPT에 등록된 부서 중에는 사원이 없는 부서도 있다.
DEPT와 EMP를 조인하되, 사원이 없는 부서 정보도 같이 출력하도록 한다.

q3)
SELECT LAST_NAME, JOB_ID,HIRE_DATE FROM EMPLOYEES
WHERE LAST_NAME in('Matos','Taylor')
ORDER BY HIRE_DATE

Q4)
SELECT LAST_NAME, HIRE_DATE FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE,'YYYY')='1994'

Q5)
SELECT LAST_NAME, JOB_ID FROM EMPLOYEES
WHERE MANAGER_ID IS NULL

6)
SELECT LAST_NAME, SALARY, COMMISSION_PCT FROM EMPLOYEES
WHERE COMMISSION_PCT > 0
ORDER BY SALARY DESC, COMMISSION_PCT DESC

7)
SELECT LAST_NAME, JOB_ID, SALARY
FROM EMPLOYEES
WHERE JOB_ID IN ('SA_REP','ST_CLERK')
AND SALARY NOT IN(2500,3500,7000)

8)
SELECT EMPLOYEE_ID, LAST_NAME, SALARY,
ROUND((SALARY*1.155),0) "newSalary" FROM EMPLOYEES

9)
SELECT FIRST_NAME, LENGTH(FIRST_NAME) "Length" FROM EMPLOYEES
WHERE FIRST_NAME LIKE 'J%' OR FIRST_NAME LIKE 'A%' OR FIRST_NAME LIKE 'M%'
ORDER BY LAST_NAME

저작자표시 (새창열림)