SQLD 3 - SQL 기본

 

3 - 10 연습문제

1번 - 3

종류	명령어
데이터 조작어(DML)	INSERT, SELECT, UPDATE, DELETE == ISSUE
데이터 정의어(DDL)	CREATE, ALTER, RENAME, DROP == CARD
데이터 제어어(DCL)	REVOKE, GRANT == R.G
트랜잭션 제어어(TCL)	COMMIT, ROLLBACK == C.R

2번 - 3(강의 필요)

ALTER TABLE SQLD.TB_EMP ADD (HEAD_WORK_YN CHAR(1));

 

 

3번 - 4

TCL

- 트랜잭션은 데이터베이스의 논리적 연산단위이다.

- 하나의 트랜잭션에는 하나 이상의 SQL 문장이 포함된다.

트랜잭션은 분할할 수 없는 최소의 단위이다. 그렇기 때문에 전부 적용하거나 전부 취소한다. 즉, TRANSACTION 은 ALL OR NOTHING 의 개념

 

특성	설명
원자성	트랜잭션에서 정의된 연산들은 모두 성공 or 실패
일관성	이전 DB 내용에 문제 없다면 트랜잭션 이후에도 DB 내용에 문제 없어야 한다.
고립성	트랜잭션 중간에 다른 트랜잭션 영향 없어야 함
지속성	트랜잭션 성공시 그 트랜잭션이 갱신한 내용은 영구적으로 저장

ACID

 

직원을 출력하라 -> 직원별로 GROUP BY를 써라

집계결과에 대한 행에 제한을 거는 것 -> HAVING을 써라

 

정답 2,4 => 

GROUP BY와 HAVING은 위치를 바꿀 수 있기 때문에 두 개 다 정답이 될 수 있다.

 

[ 서브쿼리, HAVING, GROUP BY ]

항목	설명
COUNT(*)	NULL 값을 포함한 행의 수를 출력
COUNT(표현식)	표현식의 값이 NULL 아닌 행의 수를 출력
SUM(표현식)	표현식이 NULL 값인 것을 제외한 합계를 출력
AVG(표현식)	표현식이 NULL 값인 것을 제외한 평균을 출력
MAX(표현식)	표현식이 NULL 값인 것을 제외한 최대값을 출력
MIN(표현식)	표현식이 NULL 값인 것을 제외한 최소값을 출력
STDDEV(표현식)	표현식이 NULL 값인 것을 제외한 표준편차를 출력
VARIAN(표현식)	표현식이 NULL 값인 것을 제외한 분산을 출력

* 중요한점은 집계함수는 NULL에 사용 불가이다. 단, COUNT만이 NULL 포함 계산한다.

* 또 AVG와 같은 것을 수행시 NULL 값을 제외하고 AVG를 구한다(분모가 NULL을 제외한 갯수이다) 

 

1. GROUP BY 절은 집계함수와 많이 사용

2. 단, 집계 함수의 통계 정보는 NULL 값을 가진 행은 제외(단, COUNT는 예외)

3. GROUP BY 절에서는 ALIAS 불가

4. 집계 함수는 SELECT, HAVING, GROUP BY에서만 사용 가능

5. 4번의 이유는 WHERE이 먼저 행들을 제거하기 떄문이다.

6. ???

7. 

8. HAVING절과 GROUP BY 절의 위치를 바꿀 수 있다.

+ (중요) SELECT 절에서 집계함수는 일반 Column과 함꼐 사용될 수 없다.

 

SELECT A.DEPT_CD
	, (SELECT L.DEPT_NM
		FROM TB_DEPT L
		WHERE L.DEPT_CD = A.DEPT_CD) AS DEPT_NM
	, MAX(A.BIRTH_DE) AS "가장 늦은 생년월일"
	, MIN(A.BIRTH_DE) AS "가장 빠른 생년월일"
	, COUNT(*) AS "직원수"
FROM TB_EMP A
ORDER BY A.DEPT_CD ;

위와같이 집계 함수를 사용하는데 GROUP BY도 없이 그냥 A.DEPT_CD 하면 에러난다.

1. WHERE 절에 집계 함수 금지 -> 위에도 있던 말

2,3. 집계 결과로 조건을 찾을때는 HAVING

 

SELECT A.DEPT_CD
	, (SELECT L.DEPT_NM FROM TB_DEPT L WHERE L.DEPT_CD = A.DEPT_CD) AS DEPT_NM
	, MAX(A.BIRTH_DE) AS "가장 늦은 생년월일"
	, MIN(A.BIRTH_DE) AS "가장 빠른 생년월일"
	, COUNT(*) AS "직원수"
FROM TB_EMP A
GROUP BY A.DEPT_CD
HAVING(COUNT(*)) > 1
ORDER BY A.DEPT_CD ;

 

SELECT
DEPT_CD
, (SELECT L.DEPT_NM FROM TB_DEPT L WHERE L.DEPT_CD = A.DEPT_CD) AS DEPT_NM
, SUM(CASE WHEN A.BIRTH_DE LIKE '195%' THEN 1 ELSE 0 END) AS "1950년대생"
, SUM(CASE WHEN A.BIRTH_DE LIKE '196%' THEN 1 ELSE 0 END) AS "1960년대생"
, SUM(CASE WHEN A.BIRTH_DE LIKE '197%' THEN 1 ELSE 0 END) AS "1970년대생"
, SUM(CASE WHEN A.BIRTH_DE LIKE '198%' THEN 1 ELSE 0 END) AS "1980년대생"
, SUM(CASE WHEN A.BIRTH_DE LIKE '199%' THEN 1 ELSE 0 END) AS "1990년대생"
, COUNT(*) CNT
FROM TB_EMP A
GROUP BY A.DEPT_CD
ORDER BY A.DEPT_CD;

문제에는 잘 나오지 않지만 CASE-WHEN이라는 SQL문도 있다.

??? NVL

 

 

COUNT(*) = *은 NULL까지도 연산 대상에 포함 (이 외는 포함 안 함)

COUNT(NUM) = 4, NULL 포함 못함

SUM(NUM) = 100

AVG(NUM) = 25, NULL 포함 못하기 때문에 4개로 나눈 결과값이 정답이다.

MAX(NUM) = 40

MIN(NUM) = 10

 

??? UNION, UNION ALL

 

정답 3

 

7 - 3

NULL은 그냥 NULL이고 숫자 0을 의미하지는 않는다.

 

NULL에 대한 자료는 없지만 대충 문제에 나온데로

1. 모르는 값

2. 값의 부재

3. NULL과 숫자의 연산은 NULL 반환

(숫자 포함 어떤 데이터도 의미하지 않는다)

 

8 - 4

1. 숫자로 시작하면 안된다

2. 특수문자는 _, &, # 만 가능하다.

 

9 - 3(영구적으로 만드는 것이 COMMIT과 ROLLBACK이다)

> COMMIT(저장기능)

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

 

- COMMIT 이나 ROLLBACK 이전의 데이터 상태

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

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

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

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

 

- COMMIT 이후의 상태

◼ 데이터에 대한 변경 사항이 데이터베이스에 반영된다.

◼ 이전 데이터는 영원히 잃어버리게 된다.

◼ 모든 사용자는 결과를 볼 수 있다.

◼ 관련된 행에 대한 잠금(LOCKING)이 풀리고, 다른 사용자들이 행을 조작할 수 있게 된다.

 

> ROLLBACK

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

- 롤백(ROLLBACK)은 데이터 변경 사항이 취소되어 데이터의 이전 상태로 복구되며, 관련된 행에 대한 잠금(LOCKING)이 풀리고 다른 사용자들이 데이터 변경을 할 수 있게 된다.

 

10 - 2

3-8 ORDER BY 정렬

- ORDER BY 절은 SQL 문장으로 조회된 데이터들을 다양한 목적에 맞게 특정 칼럼을 기준으로 정렬하여 출력하는데 사용

- ORDER BY 절에 칼럼(Column)명 대신에 SELECT 절에서 사용한 ALIAS 명이나 칼럼 순서를 나타내는 정수도 사용 가능

- 별도로 정렬 방식을 지정하지 않으면 기본적으로 오름차순이 적용되며, SQL 문장의 제일 마지막에 위치

- 숫자 형 데이터 타입은 오름차순으로 정렬했을 경우에 가장 작은 값부터 출력, 날짜 형 데이터 타입은 오름차순으로 정렬했을 경우 날짜 값이 가장 빠른 값이 먼저 출력

- NULL 값을 가장 큰 값으로 간주하여 오름차순으로 정렬했을 경우에는 가장 마지막에, 내림차순으로 정렬했을 경우에는 가장 먼저 위치

 

ORDERBY – 널 포함 정렬

◼ 오라클은 널 값이 가장 크다고 인식한다

◼ 내림차순의 결과 널인 행이 가장 위에 위치한다.

 

ORDERBY – SELECT 절에 존재하지 않는 컬럼으로 정렬

◼ SELECT 절에 기재하지 않은 칼럼을 기준으로 ORDER BY 해도 정상적으로 실행됨

◼ SELECT 절에 CERTI_NM 칼럼은 존재하지 않음

◼ ORDER BY 절에 CERTI_NM 칼럼으로 ORDER BY 함 정상 수행됨

 

ORDERBY – HAVING 절의 결과를 정렬

 

11 - 1

언뜻 보면 4번일 수 있지만 ORDER BY는 SELECT 절에 존재하지 않는 컬럼으로도 정렬이 가능하다.

1번인 이유는 DEPT_CD를 기준으로 JOIN하는데 A와 B중 어떤것을 기준으로 데이터를 가지고 와야하는지 명시해주지 않았다.

 

12 - 2

table의 수 - 1 이 최소 조인 조건의 개수이다.

 

13 - 1

> BETWEEN, IN , LIK, IS NULL 의 사용

⚫ BETWEEN : A 와 B 사이의 값

⚫ IN : 리스트에 있는 값 중 하나라도 있으면 된다.

⚫ LIKE : 비교문자열의 형태와 일치하면 된다.

  ◼ 와일드카드

  ◆ % : 0 개 이상의 어떤 문자를 의미한다.

  ◆ ㅡ : 1 개인 단일 문자를 의미한다.

⚫ IS NULL : 값이 NULL 이면 된다.