SQLD 2과목 기본 정리

관계형 데이터베이스 개요

 

• 데이터베이스(Database)와 DBMS(Database Management System)

 - 데이터베이스 : 데이터의 집합. 꼭 형식을 갖추지 않아도 엑셀 파일을 모아 둔다면 그것 또한 데이터베이스

 - DBMX : 데이터를 효과적으로 관리하기 위한 시스템

  => 개인이 파일을 여러 개 묶어서 폴더에 보관하면 데이터를 찾고 관리하는데 많은 비용이 발생, 이를 보다 시스템적으로 작동하게 만든 시스템을 DBMX라고 한다(ORACLE, MySQL 등)

 

• 관계형 데이터베이스 구성 요소

 - 계정 : 데이터의 접근 제한을 위한 여러 업무별/시스템별 계정이 존재

 - 테이블 : DBMS의 DB 안에서 데이터가 저장되는 형식

 - 스키마 : 테이블이 어떠한 구성으로 되어있는지, 어떠한 정보를 가지고 있는지에 대한 기본적인 구조를 정의

 

• 테이블

1. 정의

  - 엑셀에서의 워크시트처럼 행(로우)과 열(컬럼)을 갖는 2 차원 구조로 구성, 데이터를 입력하여 저장하는 최소 단위

  - 컬럼은 속성이라고도 부름(모델링 단계마다 부르는 용어가 다름)

 

2. 특징

  - 하나의 테이블은 반드시 하나의 유저(계정) 소유여야 함

  - 테이블간 관계는 일대일(1:1), 일대다(1:N), 다대다(N:N)의 관계를 가질 수 있음

  - 테이블 명은 중복 될수 있지만, 소유자가 다른 경우 같은 이름으로 생성 가능

   ex) SCOTT 소유의 EMP 테이블 존재, HR 소유의 EMP 테이블 생성 가능(같은 계정 내 동일한 객체명 생성 불가)

  - 테이블은 행 단위로 데이터가 입력, 삭제되며 수정은 값의 단위로 가능

 

• SQL

 - 관계형 데이터베이스에서 데이터 조회 및 조작, DBMS 시스템 관리 기능을 명령하는 언어

 - 데이터 정의(DDL), 데이터 조작(DML), 데이터 제어 언어(DCL) 등으로 구분

 - SQL 문법은 대,소문자를 구분하지 않음

 

• 관계형 데이터베이스 특징

 - 데이터의 분류, 정렬, 탐색 속도가 빠름

 - 신뢰성이 높고, 데이터의 무결성 보장

 - 기존의 작성된 스키마를 수정하기 어려움

 - 데이터베이스의 부하를 분석하는 것이 어려움

 

• 데이터 무결성(integrity)

 - 데이터의 정확성과 일관성을 유지하고, 데이터에 결손과 부정합이 없음을 보증하는 것

 - 데이터베이스에 저장된 값과 그것이 표현하는 현실의 비즈니스 모델의 값이 일치하는 정확성을 의미

 - 데이터 무결성을 유지하는 것이 데이터베이스 관리시스템에 중요한 기능

 

• 데이터 무결성 종류

 1. 개체 무결성 : 테이블의 기본키를 구성하는 컬럼(속성)은 NULL 값이나 중복값을 가질 수 없음 

 2. 참조 무결성 : 외래키 값은 NULL이거나 참조 테이블의 기본키 값과 동일해야 한다.

 3. 도메인 무결성 : 주어진 속성 값이 정의된 도메인에 속한 값이 이어야 함

 4. NULL 무결성 : 특정 속성에 대해 NULL을 허용하지 않는 특징

 5. 고유 무결성 : 특정 속성에 대해, 값이 중복되 않는 특징

 6. 키 무결성 : 하나의 릴레이션(관계)에는 적어도 하나의 키가 존재해야함

 

  * 도메인 : 각 컬럼(속성)이 갖는 범위

  * 릴레이션 : 테이블간 관계를 말함

  * 튜플 : 하나의 행의 의미

  * 키 : 식별자

 

• ERD(Entity Relationship Diagram)

 - ERD란 테이블 간 서로의 상관 관계를 그림으로 표현한 것

 - ERD 의 구성요소에는 엔터티(Entity), 관계(Relationship), 속성(Attribute)가 있다.

  => 현실 세계의 데이터는 이 3가지의 구성으로 모두 표현 가능

 

---

 

SELECT 문

 

• SQL 종류

 - SQL 은 그 그기능에 따라 다음과 같이 구분함

구분	종류
DDL(Data Definition Language)	CREATE, ALTER, DROP, TRUNCATE
DML(Data Manipulation Language)	INSERT, DELETE, UPDATE, MERGE
DCL(Data Control Language)	GRANT, REVOKE
TCL(Transaction Control Language)	COMMIT, ROLLBACK
DQL(Data Query Language)	SELECT

 

 * SELECT 문은 따로 SQL 종류 중 어디에도 속하지 않음

 

• SELECT 문의 구조

 - SELECT 문은 다음과 같이 6개 절로 구성

 - 각 절의 순서대로 작성해야 함(GROUP BY 와 HAVING은 서로 바꿀 수 있지만 보통 사용하지 않음)

 - SELECT 문의 내부 파싱(문법적 해석) 순서는 나열된 순서와는 다름

 - FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER BY 순서대로 실행 됨

SELECT * | 컬럼명 | 표현식
FROM 테이블명 또는 뷰명
WHERE 조회 조건
GROUP BY 그룹핑컬럼명
HAVING 그룹핑 필터링 조건
ORDER BY 정렬컬럼명;

 

• SELECT 

 - SELECT 문장을 사용하여 불러올 컬럼명, 연산 결과를 작성하는 절

 - *를 사용하여 테이블 내 전체 컬럼명을 불러올 수 있음

 - 원하는 컬럼을 ,로 나열하여 작성가능(순서대로 출력)

 - 표현식이란 원래 컬럼명을 제외한 모든 표현 가능한 대상(연산식, 기존 컬럼의 함수 변형식 포함)

 

• 문법

SELECT * | 컬럼명 | 표현식
FROM 테이블 또는 뷰명;

 

• 특징

 - SELECT 절에서 표시할 대상 컬럼 Alias(별칭) 지정 가능

 - 대소문자를 구분하지 않아도 인식

 

• emp 테이블의 전체 컬럼 조회

SELECT * FROM EMP;

 

• 표현식을 사용하여 원본과 다른 데이터 출력

SELECT EMPNO, ENAME, SAL
FROM EMP;

 

• 컬럼 Alias(별칭)

 - 컬럼명 대신 출력할 임시 이름 지정(SELECT 절에서만 정의 가능, 원본 컬럼명은 변경 X)

 - 컬럼명 뒤에 AS와 함께 컬럼 별칭 전달 (AS는 생략 가능)

 

• 특징 및 주의사항

 - SELECT 문보다 늦게 수행되는 ORDER BY 절에서만 컬럼 별칭 사용가능(그외 절에서 사용시 에러 발생)

 - 한글 사용가능(한글 지원 캐릭터셋 설정 시)
 - 이미 존재하는 예약어는 별칭으로 사용 불가(avg, decode, SELECT, FROM 등)

 

 - 다음의 경우 별칭에 반드시 쌍따옴표 전달 필요

 1) 별칭에 공백을 포함

 2) 별칭에 특수문자를 포함

 3) 별칭 그대로 전달할 경우(입력한 대소를 그대로 출력하고자 할 떄)

 

• 별칭 사용

SELECT EMPNO AS 사번
  	 ENAME 사원이름,
       SAL * 1.1 AS NEW_SAL
	FROM EMP;

 

 

• 쌍따옴표가 필요한 경우

SELECT EMPNO, ENAME, SAL * 1.1 AS "NEW SAL"
FROM EMP;

 

 - 띄워쓰기가 포함될 경우 쌍따옴표를 포함하지 않으면 오류

 

• FROM 절

 - 데이터를 불러올 테이블명 또는 뷰명 전달

 - 데이터 여러 개 전달 가능(콤마로 구분) -> 조인 조건 없이 테이블명만 나열 시 카티시안 곱 발생 주의

 - 테이블 별칭 선언 가능(AS 쓰지 않음)

  * 테이블 별칭 선언 시 컬럼 구분자는 테이블 별칭으로만 전달(테이블명으로 사용 시 에러 발생)
 - ORACLE 에서는 FROM 절 생략 불가(의미상 필요없는 경우 DUAL 테이블 선언)

  * ORACLE 23c 버전부터는 생략가능

 - MSSQL 에서는 FROM 절 필요 없을 경우 생략 가능(오늘 날짜 조회시)

 

 * 뷰 : 테이블과 동일하게 데이터를 조회할 수 있는 객체이지만 테이블처럼 실제 데이터가 저장된 것이 아닌, SELECT문 결과에 이름을 붙여 그 이름만으로 조회가 가능하도록 한 기능

 

• ORCALE에서의 FROM 절 생략 시 에러

SELECT * 24 * 123; #오류

SELECT 24 * 123
FROM DUAL; # 실행가능

 

• 테이블 별칭 사용하기

SELECT E.ENAME, E.SAL, E.DEPTNO #( 별칭 지정시 EMP.SAL은 오류 )
FROM EMP E # E로 별칭 지정
WHERE E.DEPTNO = 10;

 

---

 

함수

 

• 함수 정의

 - input value 가 있을 경우 그에 맞는 output value 를 출력해주는 객체

 - input value 와 output value의 관계를 정의한 경우

 - from 절을 제외한 모든 절에서 사용 가능

 

• 함수 기능

 - 기본적인 쿼리문을 더욱 강력하게 해줌

 - 데이터의 계산을 수행

 - 개별 데이터의 항목을 수정

 - 표시할 날짜 및 숫자 형식을 지정

 - 열 데이터의 유형(data type)을 변환

 

• 함수의 종류(입력값의 수에 따라)

 - 단일행 함수 와 복수행 함수로 구분

 - 단일행 함수 : input 과 output의 관계가 1:1

 - 복수행 함수 : 여러 건의 데이터를 동시에 입력 받아서 하나의 요약값을 리턴

  => 그룹함수 또는 집계함수라고도 함

 

 

• 입/출력값의 타입에 따른 함수 분류

 1. 문자형 함수

  - 문자열 결합, 추출, 삭제 등을 수행

  - 단일행 함수 형태

  - output 은 대부분 문자값(LENGTH, INSTR 제외)

 

• 문자 함수 종류

함수명	함수기능	사용예시	출력	기타설명
LOWER(대상)	문자열을 소문자로	LOWER("ABC")	abc
UPPER(대상)	문자열을 대문자로	UPPER("abc")	ABC
SUBSTR(대상,m,n)	문자열m 위치에서 n개의 문자열 추출	SUBSTR("ABCDE",2,3)	BCD	프로그래밍 언어와 달리 인덱스는 1부터
LTRIM(대상, 삭제문자열)	문자열 중 특정 문자열을 왼쪽에서 삭제	LTRIM("AABABAA",'A')	BABA	삭제 문자열 공백시 공백 삭제
RTRIM(대상,삭제문자)	문자열 중 특정 문자열을 오른쪽에서 삭제	RTRIM("AABABAA",'A')	AABAB	""
LPAD(대상, n,문자열)	대상 왼쪽에 문자열 추가하여 n의 길이 리턴	LPAD("ABC",5,'*')	**ABC
RPAD(대상, n, 문자열)	대상 오른쪽에 문자열 추가하여 n의 길이 리턴	RPAD("ABC",5,'*')	ABC**
CONCAT(대상1, 대상2)	문자열 결합	CONCAT('A','B')	AB	두개의 인수만 전달 가능
LENGTH(대상)	문자열 길이	LENGTH("ABC")	3
REPLACE(대상, 찾을문자열, 바꿀 문자열)	문자열 치환 및 삭제	REPLACE("ABBA","AB","ab")	abBA	세번 째 인수를 생략 하거나 빈문자열 전달 시 찾을 문자열 삭제 가능
TRANSLATE(대상, 찾을문자열 바꿀문자열)	글자를 1대1로 치환	TRANSLATE("ABBA", "AB", "ab")	abba	매칭되는 글자끼리 치환(A는 a로, B는 b로 각각) 바꿀 문자열 생략 불가, 빈문자열 전달시 null 리턴
INSTR(대상, 찾을 문자열)	대상에서 찾을문자열 위치 반환	INSTR("A#B#C#",1,1)	2
		INSTR("A#B#C#",3,2)	6	3번쨰 부터 두번째 발견한 #위치

 

• SQL-Server

 - SUBSTR -> SUBSTRING

 - LENGTH -> LEN

 - INSTR -> CHARINDEX

 

2. 숫자형 함수

 - 숫자를 입력하면 숫자 값을 반환

 - 단일행 함수 형태의 숫자 함수

 - ORACLE과 SQL-Server 함수 거의 동일

 

함수명	함수기능	사용예시	출력	기타설명
ABS(숫자)	절대값 반환	ABS(-1.5)	1.5
ROUND(숫자, 자리수)	소수점 특정 자리에서 반올림	ROUND(123.456, 2)	123.46
		ROUND(123.456,-2)	100	자리수가 음수이면 정수에서 반올람
TRUNC(숫자, 자리수)	소수점 특정 자리에서 버림	TRUNC(123.456, -2)	100
		TRUNC(123.456. 2)	123.45
SIGN(숫자)	양수면 1, 음수면 -1, 0이면 0 반환	SIGN(-123)	-1
FLOOR(숫자)	작거나 같은 최대 정수 리턴	FLOOR(3.5)	3
CEIL	크거나 같은 최소 정수 리턴	CEIL(3.5)	4
MOD(숫자1, 숫자2)	숫자1을 숫자2로 나누어 나머지 반환	MOD(7, 2)	1
POWER(m,n)	m의 n 거듭제곱	POWER(2, 4)	16
SORT(숫자)	루트값 리턴	SORT(16)	4

 

 

3) 날짜형 함수

 - 날짜 연산과 관련된 함수

 - ORACLE 과 SQL-Server 함수 거의 다름

 

함수명	함수기능	사용예시	출력	기타설명
SYSDATE	현재 날짜와 시간 리턴	SYSDATE	2024/05/23 14:36:33	날짜 출력 형식에 따라 다르게 출력됨(날짜만 출력 될 수 있음)
CURRENT_DATE	현재 날짜 리턴	CURRENT_DATE	2024/05/24	날짜출력형식에 따라 다르게 출력됨
CURRENT_TIMESTAMP	현재 타임스탬프 리턴	CURRENT_TIMESTAMP	2024/05/24 14:36:33 +09:00
ADD_MONTHS(날짜, n)	날짜에서 n개월 후 리턴	ADD_MONTHS(SYSDATE, 1)	2024/06/24 14:36:33	음수인 경우 이전 날짜 리턴
LAST_DAY(날짜)	주어진 월의 마지막 날짜 리턴	LAST_DAY(SYSDATE)	2024/05/31 14:36:33
NEXT_DAY(날짜, n)	주어진 날짜 이후 지정된 요일의 첫 번쨰 날짜 리턴	NEXT_DAY(SYSDATE, 1)	2024/05/26 14:36:33	1:일, 2:월, ~ 7:토
ROUND(날짜, 자리수)	날짜 반올림	ROUND(SYSDATE, 'MONTH')	2024/06/01 0:00	월 이전자리에서 반 올림
TRUNC(날짜, 자리수)	날짜 버림	TRUNC(날짜, 자리수)	2024/05/01 0:00	월 이전자리에서 버림

 

• SQL-Server

 - SYSDATE -> GETDATE

 - ADD_MONTHS -> DATEADD(월 뿐 아니라 모든 단위 날짜 연산 가능)

 - MONTHS_BETWEEN -> DATEDIFF(두 날짜 사이의 년, 월, 일 추출)

 

4) 변환함수

 - 값이 데이터 타입을 변환

 - 문자를 숫자로, 숫자를 문자로, 날짜를 문자로 변경

 

함수명	함수기능	사용예시	출력	기타설명
TO_NUMBER(대상)	숫자 타입으로 변경하여 리턴	TO_NUMBER('100')	100	문자 100을 숫자 100으로 리턴
TOCHAR(대상, 포맷)	1.날짜의 포맷으로	TO_CHAR(SYSDATE, 'MM/DD-YYYY')	05/23-2024	날짜 형식 변경(문자타입)
	2.숫자의 포맷변경	TO_CHAR(9000, '9,999') TO_CHAR(9000,'09999')	9,000 09000	천단위 구분기호, 총 5자리로 리턴(앞자리 수 0으로)
TO_DATE(문자, 포맷)	주어진 문자를 포맷 형식에 맞게 읽어 날짜로 리턴	TO_DATE('2024/01/01', 'YYYY/MM/DD')	2024/01/01 00:00:00	날짜로 리턴됨
CATE(대상 AS 데이터타입	대상을 주어진 데이터타입으로 변환	CAST('100' AS int)	100	문자 100을 숫자 100으로

 

• SQL-Server

 - TO_NUMBER, TO_DATE, TO_CHAR -> CONVERT(포맷 전달시)

 - 단순 변환일 경우 주로 CAST 사

 

5) 그룹함수

 - 다중행 함수

 - 여러 값이 input값으로 들어가서 하나의 요약된 값으로 리턴

 - group by와 함께 자주 사용됨

 - ORACLE과 SQL-Server 거의 동일

함수명	함수기능	사용예시	출력	기타설명
COUNT(대상)	행의 수 리턴	SELECT COUNT(컬럼) FROM EMP;	각 연산 결과	NULL을 무시하고 연산
SUM(대상)	총 합 리턴	SELECT SUM(컬럼) FROM EMP;	각 연산 결과	NULL을 무시하고 연산
AVG(대상)	평균 리턴	SELECT AVG(컬럼) FROM EMP;	각 연산 결과	NULL을 무시하고 연산
MIN(대상)	최솟값 리턴	SELECT MIN(컬럼) FROM EMP;	각 연산 결과	NULL을 무시하고 연산
MAX(대상)	최댓값 리턴	SELECT MAX(컬럼) FROM EMP;	각 연산 결과	NULL을 무시하고 연산
VARIANCE(대상)	분산 리턴	SELECT VARIANCE(컬럼) FROM EMP;	각 연산 결과	NULL을 무시하고 연산
STDDEV(대상)	표준편차 리턴	SELECT STDDEV(컬럼) FROM EMP;	각 연산 결과	NULL을 무시하고 연산

 

• SQL-Server

 - VARIANCE -> VAR

 - STDDEV -> STDEV

 

6) 일반 함수

 - 기타 함수(널 치환 함수 등)

 

함수명	함수기능	사용예시	출력	기타
DECODE( 대상, 값1, 리턴1, 값2, 리턴2, ....)	대상 값이 1이면 리턴1, 값이 2와 같으면 리턴2, 그외 리턴값 리턴	DECODE(DEPTNO,10,"인사부", 20,"재무부")	"인사부" 또는 "재무부"	대소비교에 따른 치환 불가 그외리턴 생략 시 널 리턴
NVL(대상, 치환값)	대상이 널이면 치환값으로 치환	NVL(COL, 0)	COL값 또는 0 리턴
NVL2(대상, 치환값1, 치환값 2)	대상이 널이면 치환값2로 리턴, 널이 아니면 치환값1로 리턴	NVL2(COL, "NULL이 아닐 때", "NULL일 때")	"NULL이 아닐 때" 또는 "NULL일 때"
COALESCE(대상1, 대상2, ..., 그외리턴)	대상1이 널이면 대상2, 대상2가 널이면 대상3, 모두 널이면 그외 리턴값이 리턴)	COALESCE(NULL,NULL, 1)	1 리턴
CASE	조건별 치환 및 연산 수행

 

 

• DECODE 사용해보기

SELECT DEPTNO, JOB,
	DECODE(DEPTNO,
    		10, # DEPTNO가 10이면 DECODE를 한번 더 
            DECODE(JOB,  
            	'CLERK', 
                'A', # DEPTNO가, 10이면서, JOB이 CLERK일 때 A를 반환
                'B') #DEPTNO만 10일 때 반환
            , 'C') # 둘다 포함하지 않을 때 반환
            AS RESULT
        FROM EMP;

 

 

• 에제 데이터

 

 

 

 

• NVL, NVL2 사용해보기

SELECT col1,
	col2,
    NVL(col2, 0) AS co2__with_nvl # co2가 null이면 0으로 대체
from table;

 

 

SELECT col1,
	col2,
    NVL2(col2, col2 * 2, 100) AS col2_with_nvl2 # NULL아니면 값 두배, NULL이면 100
    FROM table;

 

 

 

• COALESCE 사용해보기

SELECT col1,
       col2,
       COALESCE(col2, col2, 200, 300) AS col2_with_coalesce # 최초로 null이 아닌 값 반환(200)
FROM table;

 

 

• CASE

SELECT col1,
	col2,
    CASE
    	WHEN col2 IS NULL THEN 'No Value' # NULL 일경우 
        WHEN col2 >= 100 AND col2 < 200 THEN '100 ~ 199' #100에서 199사이의 값일 경우
        WHEN col2 >= 200 AND col2 < 300 THEN '200 ~ 299' #200에서 299사이의 값일 경우
        ELSE '300 ~ ' # 300이상인 경우
        END AS col2_case
    FROM table;

 

 

 

---

 

WHERE 절

 - 테이블의 데이터 중 원하는 조건에 데이터만 조회하고 싶을 경우 ㅅ용

 - 여러 조건 동시 전달 가능(AND와  OR로 연결)

 - NULL 조회 시 IS NULL/ IS NOT NULL 연산자 사용(= 연산자 X)

 - 연산자를 사용하여 다양한 표현이 가능

 - 조건 전달 시 비교 대상의 데이터 타입 일치하는것이 좋음

연산자 종류	설명
=	같은 조건을 검색
!=, <>	같지 않은 조건을 검색
>	큰 조건을 검색
>=	크거나 같은 조건을 검색
<	작은 조건을 검색
<=	작거나 같은 조건을 검색
BETWEEN a AND b	A와 B 사이에 있는 범위 값을 모두 검색
IN(a, b, c)	a 또는 b 또는 c 인 조건을 검색
LIKE	특정 패턴을 가지고 있는 조건을 검색
IS NULL/ IS NOT NULL	NULL 값을 검색 / NULL이 아닌 값을 검색
A AND B	A와 B를 만족하는 조건의 값을 검색
A OR B	A나 B중 두 개 중 하나의 조건을 만족하는 값을 검색
NOT A	A가 아닌 모드 조건을 검

 

 

문법

SELECT * | 컬럼명 | 조건식
	FROM 테이블 명 또는 뷰명
    WHERE 조회할 데이터 조건

 

주의

 - 문자나 ㅅ아수 표현시 반드시 홑따옴표 사용(다른 절에서도 동일)

 - ORACLE은 문자 상수의 경우 대소문자를 구분

 - MSSQL은 기본적으로 문자상수의 대소문자를 구분X

 

사용해보기

SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE ENAME = 'SMITH'; # ENAME이 SMITH인 데이터를 조회

SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE SAL >= 1500; # SAL이 1500이상인 데이터를 조회

SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE EMPNO IS NULL; # EMPNO이 NULL인 데이터를 조회

SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE ENAME = 'SMITH'
	AND SAL >= 1000; # ENAME이 SMITH, SAL이 1000이상인 데이터를 조회
    
# AND 대신 OR는 둘 중 하나만 만족하는 데이터를 조회

SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE ENAME IN('SMITH', 'PARK') # ENAME이 SMITH, PARK 인 데이터를 조회

SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE SAL BETWEEN 2000 AND 3000; # SAL이 2000에서 3000사이인 데이터를 조회

 

• LIKE 연산자

SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE ENAME
	LIKE 'S%' OR # S로 시작하는
    LIKE '%S' OR # S로 끝나는
    LIKE '_S%' OR # 두번 째 글자가 S로 시작하는
    LIKE '__S_' ; # 4글자 이면서 3번 째 글자가 S인

 

• NOT 연산자

SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE SAL NOT BETWEEN 1000 AND 3000; # SAL이 1000에서 3000사이가 아닌 값 (1000미만, 3000초과)

 

---

 

GROUP BY와 HAVING

 

• GROUP BY

 - 각 행을 특정 조건에 따라 그룹으로 분리하여 계산하도록 하는 구문식

 - GROUP BY 절에 그룹을 지정할 컬럼을 전달(여러 개 전달 가능)
 - 만약 그룹 연산에서 제외할 대상이 있다면 미리 WHERE절에서 해당 행을 제외

   (WHERE 절이 GROUP BY 절보다 먼저 수행되므로)

 - 그룹에 대한 조건은 WHERE 절에서 사용X

 - SELECT 절에 집계 함수를 ㅅ용하여 그룹연산 결과 표현

 - GROUP BY 절을 사용하면 데이터가 요약되므로 요약되기 전 데이터와 함께 출력할 수 없음

 

• 문법

SELECT * | 컬럼명 | 표현식
FROM 테이블명 또는 뷰명
WHERE 조회할 데이터 조건
GROUP BY 그룹핑 컬럼명
HAVING 그룹핑 대상 필터링 조건;

 

SELECT DEPARTMENT_ID, SUM(SAL), ROUND(AVG(SALARY)) # DEPARTMENT_ID 별로 합계, AVG를 구함
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID;

 

• HAVING 절

 - 그룹 함수 결과를 조건으로 사용할 때 사용하는 절

 - WHERE 절을 사용하여 그룹을 제한할 수 없으므로 HAVING 절에 전달

 - HAVING 절이 GROUP BY 절 앞에 올 수는 있지만 뒤에 쓴느 것을 권장

 - 내부적 연산 순서가 SELECT 절보다 먼저이므로 SELECT 절에서 선언된 Alias 사용불가

 

SELECT DEPARTMENT_ID,
	SUM(SAL)
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (1,2,3,4) #ID가 1,2,3,4 인 것을 필터링
GROUP BY DEPARTMENT_ID # DEPARTMENT_ID 그룹핑
HAVING SUM(SAL); #SUM 결과가 20000이상인 데이터

 

 - 순서상 WHERE 절을 먼저 수행, 원하는 데이터만 필터링 한 후 GROUP BY에 의해 그룹연을 수행한 뒤 HAVING절에서 만족하는 데이터만 선택하여 출력

 

---

ORDER BY

 - 데이터는 입력된 순서대로 출력되나, 출력되는 행의 순서를 사용자가 변경하고자 할 떄 ORDER BY 절을 사용

 - ORDER BY 뒤에 명시된 컬럼 순서대로 정렬 -> 1차정렬, 2차정렬 전달 가능

 - 정렬 순서를 오름차순 ASC, 내림차순 DESC로 전달, 생략 시 오름차순

 - 유일하게 SELECT 절에 정의한 컬럼 별칭 사용 가능

 - SELECT 절에 선언된 순서대로의 숫자로도 사용 가능

 

• 문법

SELECT * | 컬럼명 | 표현식
FROM 테이블명 또는 뷰명
WHERE 조회할 데이터 조건
GROUP BY 그룹핑 컬럼명
HAVING 그룹핑 대상 필터링 조건
ORDER BY 정렬컬럼명 [ASC|DESC];

 

• 사용해보기

SELECT * FROM employees
ORDER BY Age; # 나이 오름차순

SELECT * FROM employees
ORDER BY Age DESC; # 나이 내림차순

 

SELECT * FROM employees
ORDER BY age ASC, e_name ASC; # 1. 나이 오름차순, 나이가 같으면 이름 오름차순

SELECT EMPLOYEE_ID AS EID,
	SAL,
    DEPARTMENT_ID
FROM EMPLOYEES E
WHERE DEPARTMENT_ID = 100
ORDER BY EID DESC; # ORDER BY는 별칭 적용 가능

 

 

---

JOIN

 

• JOIN

 - 어려 테이블의 데이터를 사용하여 동시 출력하거나 참조 할 경우 사용

 - FROM 절에 조인할 테이블 나열

 - ORACLE 표준은 테이블 나열 순서는 중요하지 않음, ANSI 표준은 OUTER JOIN 시 순서 중요

 - WHERE 절에서 조인 조건을 작성(ORACLE 표준)

 - 동일한 열 이름이 여러 테이블에 존재할 경우 열 이름 앞에 테이블 이름이나 테이블 Alias 붙임

 - N 개의 테이블을 조인하려면 최소 N-1 개의 조인 조건이 필요

 - ORACLE 표준과 ANSI 표준이 서로 다름

 

• 조인 종류

1. 조건의 형태에 따라

 1) EQUI JOIN(등가 JOIN) : JOIN 조건이 동등 조건인 경우 (=)

 2) NON EQUI JOIN : JOIN 조건이 동등 조건이 아닌 경우 (>=, <>)

 

2. 조인 결과에 따라

 1) INNER JOIN : JOIN 조건에 성립하는 데이터만 출력하는 경우

 2) OUTER JOIN : JOIN 조건에 성립하지 않는 데이터도 출력하는 경우

    (LEFT/ RIGHT/ FULL OUTER JOIN으로 나뉨)

3. NATURAL JOIN : 조인조건 생략 시 두 테이블에 같은 이름으로 자연 연결되는 조인

4. CROSS JOIN : 조인조건 생략 시 두 테이블의 발생 가능한 모든 행을 출력하는 조인

5. SELF JOIN : 하나의 테이블을 두 번 이상 참조하여 연결하는 조인

 

• EQUI JOIN(등가 JOIN)

 - 조인 조건이 '='(equal) 비교를 통해 같은 값을 가지는 행을 연결하여 결과를 얻는 조인 방법

 - SQL 명령문에서 가장 많이 사용하는 조인 방법

 - FROM절에 조인하곶 하는 테이블을 모두 명시

 - FROM 절에 명시하는 테이블은 테이블 별칭(Alias) 사용 가능

 - WHERE 절에 두 테이블의 공통 컬럼에 대한 조인 조건을 나열

 

문법(ORACLE 표준)

SELECT 테이블1.컬럼, 테이블2.컬럼
FROM 테이블1, 테이블2
WHERE 테이블1.컬럼 = 테이블2.컬럼;

 

 

EMP 테이블

 

DEPT 테이블

 

EMP, DEPT 테이블을 이용해 이름과 부서명을 출력

SELECT EMP.ENAME, DEPT.DNAME #EMP의 ENAME, DEPT의 DNAME
FROM EMP, DEPT # EMP오 DEPT 테이블
WHERE EMP.DEPTNO = DEPT.DEPTNO; # DEPTNO을 기준으로 조인

 

 

• NON-EQUI JOIN

 - 테이블을 연결짓는 조인 컬럼에 대한 비교 조건이 '<', BETWEEN A AND B 와 같이 '=' 조건이 아닌 연산자를 사용하는 경우의 조인

SELECT 테이블1.컬럼, 테이블2.컬럼
FROM 테이블1, 테이블2
WHERE 테이블1.컬럼 비교조건 테이블2.컬럼;

 

EMP 테이블의 급여를 확인하고 SAL_GRADE에 있는 급여 등급 기준에 따라 직원이름과 급여, 급여등급 출력

SELECT E.NAME, E.SAL, S.GRADE
FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL; # EMP의 SAL이 LOSAL ~ HISAL의 GRADE를 매김

 

 

• 세 테이블 이상의 조인

 - 관계를 잘 파악하여 모든 테이블이 연결되도록 조인 조건 명시

 - N개 테이블의 경우 최소 N-1개의 조인조건 필요

 

SELECT e.ENAME, d.DNAME, e.SAL, sg.GRADE
FROM EMP e, DEPT d, SAL_GRADE sg
WHERE e.DEPTNO = d.DEPTNO # EMP와 DEPT를 DEPTNO으로 조인
AND e.SAL BETWEEN sg.LOW_SAL AND sg.HIGH_SAL; # EMP의 SAL은 LOW~HIGH에 따라서 GRADE가 결정

 

 

• SELF JOIN

 - 한 테이블 내 각 행끼리 관계를 갖는 경우 조인 기법

 - 한 테이블을 참조할 떄마다 명시

 - 테이블명이 중복되므로 반드시 테이블 별칭 사용

 

 

SELECT e.사원이름 AS 사원, m.사원이름 AS 상사 # 두 테이블을 나누어 사원과 상사로 별칭
FROM 사원 e, 사원 m
WHERE e.상사ID = m.사원ID; # 사원의 상사 ID와 m의 사원이름과 같은 것

 

 

---

표준조인

 - ANSI 표준으로 작성되는 INNER JOIN, CROSS JOIN, NATURAL JOIN OUTER JOIN 을 말함

 

• INNER JOIN

 - 내부 조인이라고 하며, 조인 조건이 일치하는 행만 추출(ORACLE 조인 기본)

 - ANSI 표준의 경우 FROM 절에 INNER JOIN 혹은 줄여서 JOIN을 명시

 - ANSI 표준의 경우 USING 이나 ON 조건절을 필수적으로 사용

 

• ON절

 

 - 조인할 양 컬럼의 컬럼명이 서로 다르더라도 사용 가능

 - ON 조건의 괄호는 옵션(생략가능)
 - 컬럼명이 같을 경우 테이블 이름이나 별칭을 사용하여 명확하게 지정(테이블 출처 명확히)
 - ON 조건절에서 조인조건 명시, WHERE 절에서는 일반조건 명시

  (WHERE 절과 ON 절을 쓰임에 따라 명확히 구분)

 

• 문법

SELECT 테이블1.컬럼명, 테이블2.컬럼명
FROM 테이블1 INNER JOIN 테이블 2
ON 테이블1.조인컬럼 = 테이블2.조인컬럼;

 

EMP 테이블과 DEPT 테이블을 사용하여 각 직원의 이름과 부서명을 함꼐 출력(EQUI JOIN)

SELECT EMP.ENAME. DEPT.DNAME
FROM EMP JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO;

 

 - ORALCE 표준은 FROM 절에 테이블을 콤마로 구분, WHERE 절에 조인 조건 나열

 - ORACLE 은 INNER JOIN이 기본 조인 연산이므로 별도의 문법 존재 안함

 

• USING절

 

 - 조인할 컬럼명이 같을 경우 사용

 - Alias나 테이블 이름 같은 접두사 붙이기 불가

 - 괄호 필수

 

USING절을 이용한 사원이름과 부서이름 조회

SELECT EMP.ENAME, DEPT.DNAME
FROM EMP JOIN DEPT
USING (DEPTNO);

 

 

• NATURAL JOIN

 - 두 테이블 간의 동일한 이름을 가지는 모든 컬럼들에 대해 EQUI JOIN을 수행

 - USING, ON, WHERE 절에서 조건 정의 불가

 - JOIN에 사용된 컬럼들은 데이터 유형이 동일해야 하며 접두사를 사용불가

 

NATRUAL 조인을 이용한 사원 이름, 부서명 출력

SELECT EMP.ENAME, DEPT.DNAME
FROM EMP NATURAL JOIN DEPT;

 

NATURAL JOIN시 주의

SELECT * 
	FROM STUDENT NATURAL JOIN PROFESSOR;

 

 - NATURAL JOIN은 동일한 이름의 모든 컬럼을 조인 컬럼으로 사용하므로 조인 컬럼의 값이 모두 같을 때만 결과가 리턴됨

 - STUDENT와 PROFESSOR 테이블에는 NAME 컬럼과 PROFNO컬럼이 컬럼명이 서로 동일

 

• CROSS JOIN

 - 테이블 간 JOIN 조건이 없는 경우 생성 가능한 모든 데이터들의 조합 (카티시안곱) 출력

 - 양쪽 테이블 행의 수의 곱한 수의 데이터 좋바 발생(M * N)

 

SELECT EMP.ENAME, DEPT.DNAME
FROM EMP CROSS JOIN DEPT;

 

 - 양쪽 행의 수만큼 카티시안 곱을 하여 출력

 

• OUTER JOIN
   - INNER JOIN과 대비되는 조인 방식

 - JOIN조건에서 동일한 값이 없는 행도 반환할 떄때사용

 - 두 테이블 중 한쪽에 NULL을 가지면 EQUI JOIN시 출력되지 않음 -> 이를 출력시 OUTER JOIN 사용

 - 테이블 기준 방향에 따라 LEFT OUTER, RIGHT OUTER, FULL OUTER JOIN으로 구분

 

OUTER JOIN 종류

1. LEFT OUTER JOIN

 - FROM절에 나열된 왼쪽 테이블에 해당되는 데이터를 읽은 후, 우측 테이블에서 JOIN 대상을 읽어옴

 - 즉, 왼쪽 테이블이 기준이 되어 오른쪽 데이터를 채우는 방식

 - 우측 값에서 같은 값이 없는 경우 NULL 값으로 출력

 

2. RIGHT OUTER JOIN

 - LEFT OUTER JOIN 의 반대

 

3.FULL OUTER JOIN

 - 두 테이블 전체 기준으로 결과를 생성하여 중복 데이터는 삭제 후 리턴

 - LEFT OUTER JOIN 결과와 RIGHT OUTER JOIN 결과의 UNION 연산 리턴과 동일

 - ORACLE 표준에는 없음

 

LEFT OUTER JOIN

 

ORACLE 표준

SELECT * 
FROM STUDENT S, PROFESSOR P
WHERE S.PROFNO = P.PROFNO(+)
AND S.GRADE IN(1,4);

 

 - ORALCE 표준은 WHERE절에 조인 조건을 작성, LEFT OUTER JOIN을 기술X

 - WHERE절에서 기준 되는 테이블의 반대 테이블 조건 컬럼 뒤에 (+)를 붙임

 

ANSI 표준

SELECT * 
FROM STUDENT S LEFT OUTER JOIN PROFESSOR P
ON S.PROFNO = P.PROFNO
WHERE S.GRADE IN (1,4);

 

 

FULL OUTER JOIN

 

ANSI

SELECT *
FROM STUDENT S FULL OUTER JOIN PROFESSOR P
ON S.PROFNO = P.PROFNO;

 

 

ORACLE은 LEFT, RIGHT OUTER끼리 합친다

SELECT *
FROM STUDENT S, PROFESSOR P
WHERE S.PROFNO = P.PROFNO(+)
UNION
SELECT *
FROM STUDENT S, PROFESSOR P
WHERE S.PROFNO(+) = P.PROFNO;