23.12.14

[오늘 할 일]

1. SQL 코드카타 문제 풀기(오늘은 5문제)

2. SQLD 자격증 대비만 10주차 강의

3. 아티클 스터디

 

[내용 정리]

1. SQL 코드카타 문제풀기

SELECT NAME, DATETIME
FROM ANIMAL_INS
WHERE ANIMAL_ID NOT IN (SELECT ANIMAL_ID FROM ANIMAL_OUTS)
ORDER BY DATETIME
FETCH FIRST 3 ROWS ONLY
-- FETCH FIRST n ROWS ONLY/WITH THIS;
-- OFFSET n ROWS는 원하는 갯수를 뽑기 이전에 스킵하고 싶은 row 갯수가 있을 시에 써주면 된다.
-- MySql경우 LIMIT n을 사용

-- 혹은 JOIN을 활용한 방법
SELECT I.NAME, I.DATETIME
 FROM ANIMAL_INS I LEFT JOIN ANIMAL_OUTS O ON I.ANIMAL_ID = O.ANIMAL_ID
 WHERE O.ANIMAL_ID IS NULL
 ORDER BY 2
FETCH FIRST 3 ROWS ONLY

-- 카테고리별 합계 구하기 JOIN과 날짜포맷 변환 사용
SELECT A.CATEGORY, SUM(B.SALES) AS TOTAL_SALES
FROM BOOK A LEFT JOIN BOOK_SALES B ON A.BOOK_ID = B.BOOK_ID
WHERE TO_CHAR(B.SALES_DATE, 'YYYY-MM') = '2022-01'
GROUP BY A.CATEGORY
ORDER BY A.CATEGORY ASC

2. SQLD 자격증 대비반 10주차

<10주차>

[GROUP BY와 HAVING]

1. 집계함수 (Aggregate Function)
- 여러 데이터들의 정보를 집계하여 연산을 해주는 함수.
- GROUP BY 절에 작성한 칼럼을 기준으로, 그룹으로 모인 상태에서 각 그룹의 집계를 계산
1) 기본 구조

집계 함수명( [DISTINCT | ALL] 칼럼이나 표현식 )

2) 집계함수 종류
-> NULL값을 제외하여 계산한다. (단, 괄호 안에 *를 넣는 경우엔 NULL을 합쳐서 계산)
SUM / AVG / MIN / MAX / COUNT / STDDEV / VARIAN / 기타 통계함수

 

2. GROUP BY 절
1) 기본개념
데이터들을 작은 그룹으로 분류하여 해당 그룹에 대한 항목별 통계 정보를 얻고자 할 때 사용하는 절이다.
(집계 함수의 통계 정보에서 NULL값은 제외된다.)
2) 특징
- ALIAS 사용 X
- WHERE절은 GROUP으로 나누기 전에 행들을 미리 주어진 조건에 맞춰 가져온다. 그래서 GROUP에 대한 조건은 HAVING 사용
- 기본구조

SELECT [DISTINCT] 칼럼명 [ALIAS명] 
FROM 테이블명 
[WHERE 조건식] 
[GROUP BY 칼럼이나 표현식] 
[HAVING 그룹조건식] ;

3. HAVING절
- WHERE 절과 같이 특정 조건을 만족하는 결과 데이터만 표시하나 집계된 결과에 대해 사용한다는 것이 특징
- HAVING절은 GROUP BY 절 앞 뒤 상관없지만 문맥의 흐름상 GROUP BY 뒤에 넣는 것이 좋다.
4. 집계 함수와 NULL
- 조회 결과 중 NULL이 있는 경우 집계 연산을 하기 위해 NVL 혹은 ISNULL함수를 사용하여 0으로 변경할 수 있다. 다만 이 함수를 집계 함수 내부에 넣어 사용하는 경우에는 불필요한 부하가 발생한다. (기본적으로 집계함수는 NULL값을 제외하고 연산하기 때문이다.)
- 단, 괄호 안에  *을 넣으면 NULL을 포함해서 계산

[ORDER BY]
1. ORDER BY
1) 개념
- 조회되는 데이터들을 특정 칼럼을 기준으로 정렬하여 출력해준다.
2) 특징
- 오름차순(ASC)이 기본 값으로 설정되어 있으며, 내림차순으로 지정하려면 DESC를 명시하면 된다.
- SELECT절에 사용한 ALIAS명을 사용하거나 칼럼의 순서 정수(1~n)로 명시해도 된다.
- (데이터별 오름차순 정렬 기준) 숫자는 작은 값부터, 날짜는 가장 빠른 날짜부터 정렬
- (NULL값 기준) ORACLE은 가장 큰 값으로, SQL Server는 가장 작은 값으로 간주

3) 실행순서

5. SELECT 칼럼명 [ALIAS명]         -- 5. 데이터의 값을 출력/계산
1. FROM 테이블명                   -- 1. 발췌대상 테이블 참조
2. WHERE 조건식                    -- 2. 발췌 대상 데이터가 아닌 것은 제거
3. GROUP BY 칼럼(Column)이나 표현식 -- 3. 행동들을 소그룹화
4. HAVING 그룹조건식               -- 4. 그룹핑된 값의 조건에 맞는 것만을 출력
6. ORDER BY 칼럼(Column)이나 표현식;-- 6. 데이터를 정렬

4) TOP N 쿼리
상위 n개의 데이터를 보여주기 위한 커리를 지칭한다. - 조회된 데이터에 번호를 매겨 원하는 개수의 데이터를 가져올 수 있다.
a. ROWNUM(Oracle)

SELECT ename, sal FROM emp WHERE ROWNUM < 4 ORDER BY sal DESC;
--> sal 이 제일 높은 3개의 데이터를 조회하는 것이 아닌
-- 3개를 랜덤하게 가져온 후 정렬을 하게 됨

b. TOP(SQL Server)

-- SQL Server
SELECT 
    TOP(3) WITH TIES 
    name, attr, 
    height 
FROM pokemon ORDER BY height, name;

c. PERCENT
- 개수가 아닌 퍼센트 범위에 해당하는 데이터 출력 
d. WITH TIES
- 개수를 초과하더라도 동일한 값의 데이터를 추가로 표시하고자 할 때 사용하는 옵션

 

[TCL]
1. 개요
1) TCL(Transaction Control Language)과 트랜잭션
DML 수행 이후 변경된 데이터베이스를 최종적으로 반영(COMMIT)하거나 변경 사항을 취소(ROLLBACK)하는 구문이다.
2) 트랜잭션
- 하나의 논리적인 연산의 단위
- 특성 : 원자성 / 일관성 / 고립성 / 지속성

2. COMMIT /ROLLBACK
1) COMMIT
- DML 수행 후 자료에 대해서 문제가 없다고 판단이 되었을 때 최종 트랜잭션을 확정하는 TCL 명령어다.
*LOCKING(잠금) - 트랜잭션이 수행하는 동안 특정 데이터 대해서 다른 트랜잭션이 동시에 접근하지 못하도록 제한

-- Oracle
SQL 쿼리문;
COMMIT;

-- SQL Server
BEGIN {TRANSACTION|TRAN}
SQL 쿼리문
COMMIT [TRANSACTION];

2) ROLLBACK
- 트랜잭션 진행중 에러가 발생하거나 잘못된 연산이 이뤄진 경 변경전  가장 최신 상태로 되돌리는 명령어다. 
ROLLBACK이 실행되면 COMMIT되지 않은 모든 데이터 변경 사항들이 취소되어 이전 상태로 복구된다.

-- Oracle
SQL 쿼리문
ROLLBACK;

-- SQL Server
BEGIN {TRANSACTION|TRAN}
SQL 쿼리문
ROLLBACK [TRANSACTION];

3. SAVEPOINT
트랜잭션의 크기가 크고 시스템의 부하를 많이 주는 트랜잭션인 경우 그냥 ROLLBACK하는 경우에 비효율적일수  있다. 이때 트랜잭션 중간에 SAVEPOINT를 설정하여 시스템 부하를 줄일 수 있다.

-- Oracle
SAVEPOINT 저장점_이름;

ROLLBACK TO 저장점_이름;

-- SQL Server
SAVE TRANSACTION 저장점_이름;

ROLLBACK TRANSACTION 저장점_이름;

 

[DCL]
1. DCL
특정 유저의 권한을 제어할 수 있는 명령어로 권한을 부여할 수 있는 GRANT문과 권한을 회수할 수 있는
REVOKE문이 있다.

 

2. 구문정리
- 사용자 계정 생성 CREATE USER user IDENTIFIED BY 'password'
- 사용자 비밀번호 변경 : ALTER USER user IDENTIFIED BY 'password'
- 사용자 계정 잠금/해체 : ALTER USER user ACCOUNT LOCK/UNLOCK
- 사용자 계정 삭제 : DROP USER user [CASCADE]
- 사용자 권한 부여 : GRANT PRIVILEGE TO user [with admin option)
- 사용자 권한 회수 : REVOKE PRIVILEGE FROM user