SQLD 자격증 문제 풀이 정리 3-4

 

1. select문 기본 구조 - 함수

 

단일행 함수

 

1) 문자형 함수: 문자를 입력하면 문자나 숫자 값을 반환

문법	설명	예시
LOWER(문자열)	영어 문자열 소문자로 변환	LOWER('DATA') -> 'data'
UPPER(문자열)	영어 문자열 대문자로 변환	UPPER('data') - > 'DATA'
ASCII(문자)	문자나 숫자를 ASCII 코드 번호로 변환
CHR/CHAR(ASCII번호)	ASII코드 번호를 문자나 숫자로 변환
CONCAT(문자열1, 문자열2)	문자열 1과 문자열 2 결합	CONCAT('안', '녕') = '안'||'녕' = '안'+'녕'
SUBSTR/SUBSTRING(문자열, m, n)	문자열에서 m번째 자리값부터 n개	SUBSTR('DATA', 2, 2) -> 'AT'
LENGTH(문자열) / LEN(문자열)	공백 포함 문자열의 길이 값	LEN('안녕하세요') -> 5
TRIM(문자열, 제거대상)	*왼쪽과 오른쪽에서 지정된 문자 삭제	TRIM('aabbccaa', 'a') -> 'bbcc'
	지정된 문자 없을 시 공백 제거	TRIM(' aabbccaa ', ) -> 'aabbccaa'
LTRIM(문자열, 제거대상)	*왼쪽에서 지정된 문자 삭제	LTRIM('aabbccaa', 'a') -> 'bbccaa'
	지정된 문자 없을 시 좌측 공백 제거	LTRIM(' aabbccaa ', ) -> 'aabbccaa '
RTRIM(문자열, 제거대상)	*오른쪽에서 지정된 문자 삭제	RTRIM(' aabbccaa ', 'a') -> ' aabbcc'
	지정된 문자 없을 시 우측 공백 제거	RTRIM(' aabbccaa ', ) -> ' aabccaa'

설명1) '/' 표시 있는 것은 왼쪽 Oracle함수, 오른쪽 SQL Server

설명2) '*'표시는 Oracle만 해당, SQL Server는 지정 문자 사용 불가(공백 제거만 가능)

 

2) 숫자형 함수: 숫자를 입력하면 숫자 값을 반환

문법	설명	예시
ROUND(숫자, 소수점 자리수)	반올림	ROUND(3.14159265, 2) -> 3.14
TRUNC(숫자, 소수점 자리수)	버림	TRUNC(3.14159262, 2) -> 3.141592
CEIL/CELING(숫자)	크거나 같은 최소 정수 반환	CEIL(31.45) -> 32
FLOOR(숫자)	작거나 같은 최대 정수 반환	FLLOR(31.45) -> 31
MOD(분자, 분모)	분자를 분모로 나눈 나머지 반환	MOD(9, 2) -> 1
SIGN(숫자)	양수는 1, 0은  0, 음수는 -1 반환	SIGN(9) -> 1, SIGN(0) -> 0, SIGN(-9) -> -9
ABS(숫자)	절대값	ABS(3.14 ) -> 3

기타: floor, exp, log, lnm power, sin, cos, tan

 

3) 날짜형 함수

문법	설명	예시
SYSDATE/GETDATE	쿼리를 돌리는 현재 날짜&시각 출력	2022/10/31 16:00:53(datetime형태)
EXTRACT/DATEPART (정보 FROM 날짜)	날짜형 데이터에서 원하는 값 추출	EXTRACT ( YEAR FROM date '2022-10-31') -> 2022
		EXTRACT (YEAR FROM sysdate) -> 2022
		TO_NUMBER(TO_CHAR(sysdate, 'YYYY')) -> 2022

*정보: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND

 

4) 변환형 함수

명시적 / 암시적 형변환

1) 명시적 형변환: 형변환 함수를 사용하여 강제로 data type를 변환하는 것

2) 암시적 형변환: 데이터베이스가 자동으로 변환해주는 것

ex. 고객번호가 number형, pk인 테이블 member에 대해 조회

// 고객번호 문자열'12345'로 조건이 입력되었지만, 고객번호 컬럼 자체가 number형이기 때문에 숫자 12345로 암시적 형변환(자동 형변환)
select * from member where 고객번호 '12345'

-> 고객번호는 number형이고, pk 이므로 인덱스가 됨 But, 인덱스에 대해 암시적 형변환이 발생한 경우 인덱스를 사용할 수 없음 

cf) 인덱스: 빠른 조회를 돕는 책의 목차와 같은 기능을 함, 데이터는 인덱스 기준 자동정렬 됨

 

문법	설명	예시
TO_NUMBER(문자열)	문자열을 숫자로 변환	TO_NUMBER('2022')
TO_CHAR(숫자or 날짜, 포맷)	숫자 or 날짜형 데이터를 포맷에 맞는 문자로 변환	TO_CHAR(date '2022-10-31', 'day') -> 금요일 TO_CHAR(200) -> 200
TO_DATE(문자열, 포맷)	문자열 데이터를 날짜형 데이터로 변환	TO_DATE('2022103116', 'YYYYMMDDHH24') -> 2022/10/31 16:00:00

cast, convert

 

Q. 어제 날짜(YYYYMMDD) 조회하기

SELECT TO_CHAR ( SYSDATE -1, 'YYYYMMDD') // 날짜만 조회하기위해 TO_CHAR을 이용하여 날짜만 남도록 형변환
FROM DUAL;

DUAL: 듀얼 테이블은 오라클에 존재하는 기본 테이블로, 하나의 열로 되어있음, 오늘 날짜를 구하거나 간단한 계산에 적합

SYSDTE는 SQL을 작업하는 당일의 날짜와 시각을 알려주며, -1을 할 경우 전날의 날짜값이 출력됨

 

5) NULL 관련 함수

종류	설명	예시
NVL / ISNULL(col1, 대체값 )	NULL이면 다른 값으로 바꿔주는 함수	NVL(col1, 100) -> col1이 NULL이면 1100으로 바꿔줌
NVL2(col1, 결과1, 결과2)	삼항연산자 형식 (NULL, 참, 거짓)	NVL2 (col1, 'True', 'False') -> col1이 NULL이면 True, 아니면 False
NULLIF(v1, v2)	v1, v2가 같으면 NULL 다르면 v1 출력	v1 == v2 이면 NULL v1 != v2 이면 v1 출력
COALSESCE(v1, v2, v3... vn)	NULL이 아닌 최초의 값 반환	v1 IS NOT NULL 이면 v1 출력 v1 IS NULL이면 v2 출력...

 

 

다중행 함수

 

1) 집계함수

문법	설명
COUNT(*), COUNT(exp)	count(*): NULL포함, count(exp): NULL제외
SUM([DISTINCT | ALL] exp)	합계
AVG([DISTINCT | ALL] exp)	평균
MAX([DISTINCT | ALL] exp)	최대값
MIN([DISTINCT | ALL] exp)	최소값
STDDEV([DISTINCT | ALL] exp)	표준편차
VARIAN([DISTINCT | ALL] exp)	분산

* default는 ALL

* 통계적 집계 함수를 연산 시 NULL은 제외하고 계산 됨

 

2) 그룹 함수

문법	설명
GROUP BY 컬럼1, 컬럼2	컬럼1, 컬럼2 별 합계
GROUP BY ROLLUP (컬럼1, 컬럼2)	컬럼1, 컬럼2 별 합계 컬럼1 별 합계 전체 합계
GROUP BY CUBE(컬럼1, 컬럼2)	컬럼1, 컬림2 별 합계 컬럼1 합계 컬럼2 합계 전체합계 * 가능한 모든 조합
GROUP BY GROUPING SETS(컬럼1, 컬럼2)	컬럼1 별 합계 컬럼2 별 합계 *괄호 묶은 집합 별 집계 가능

  1) GROUP BY

Q. 결과값 기술

정답

 

⭐️ oracleDB는 NULL값 수까지 세서 결과가 나온다 ⭐️ SQL서버는 NULL을 제외하고 결과가 나온다

Q. 만약 위와 동일한 결과를 SQL서버에서 위와 같은 결과를 얻기 위해서는?

SELECT NVL (성별, 'N') AS gender, // NVL을 이용해서 별도로 NULL값에 데이터를 넣음
	COUNT(회원코드) cnt
FROM MEMBER
GROUP BY NVL(성별, 'N')

 

  2) GROUPING함수: 소계, 합계 등이 계산되면 1반환, 아니면 0 반환

SELECT 성별, GROUPING(성별)g1, 연령,
GROUPING(연령)g2, SUM(결제금액)
FROM 결제
GROUP BY ROLLUP(성별, 연령대)
ORDER BY 성별, 연령;

Q. CASE WHEN을 활용하여 전체 합계 구분하기

SELECT 성별 CASE WHEN GROUPING(성별) = 1
            THEN '전체합계' END
            AS g1,
            연령, GROUPING(연령) g2, SUM(결제금액)
FROM 결제
GROUP BY ROLLUP(성별, 연령대)
ORDER BY 성별, 연령;

 

Q. 다음중 가장 적절하지 않은 것은?

1. CUBE 함수의 경우, 함수의 인자가 주어진 순서에 따라 결과가 달라지며 계층구조로 집계값을 반환함 // 설명은 ROLLUP함수 2. ROLLUP, CUBE 등 그룹 함수에 의해 집계된 결과에서 그룹 대상 컬럼 값은 NULL로 출력 3. ROLLUP, CUBE 등 그룹 함수에 의해 집계된 결과에서 집계 대상 컬럼 값은 NULL로 출력 4. ROLLUP, CUBE, GROUPING SETS 모두 일반 그룹 함수로 동일한 결과를 추출할 수 있음 5. ROLLUP은 함수 내 인자의 순서에 따라 다른 결과를 반환함

 

 

Q. MEMBER에서 평균 연령이 30대인 성별과 해당 성별의 평균 연령 출력

🍒 문제를 쪼개어 생각하기!!!! 성별 별로 평균 연령 먼저 구하기

🍒🍒 조건에 맞는 평균 연령 레코드 출력

SELECT 성별, AVG(연령)
FROM MEMBER
GROUP BY 성별
HAVING AVG(연령) >= 30 AND AVG(연령) < 40

 

3) 윈도우 함수 

 

 

 

2. WHERE 조건문

1) 연산자 종류

종류	설명
IN(x, y, z, ...)	x, y, z 등으로 구성된 목록 내 값 중 어느 하나라도 일치하면 됨
NOT IN(x, y, z, ... )	x, y, z 등으로 구성된 목록 내 값 중 어느 하나라도 일치하면 안됨
IS NULL	NULL인지 판단, NULL일경우 TRUE
IS NOT NULL	NOT NULL인지 판단, NOT NULL일경우 TRUE
BETWEEN a AND b	a와 b사이의 값
기타	비교 연산자(=, >, >=, <, <=) 등

2) 문자열 조건문 관련 연산자

종류	설명
A LIKE B	A에 대하여 B와 유사한 문자열을 찾아줌
%	문자 1개 이상이 존재
_	문자 한 개

 

 

 

3. WITH 구문

Q. MEMBER테이블에서 이름에 a가 들어가는 사람들을 대상으로 임시테이블을 만들고 전체 조회

WITH TableName AS(
SELECT *
FROM MEMBER
WHERE NAME LIKE '%a%'
)

1) 서브쿼리를 사용해서 임시 테이블이나 뷰(view)처럼 사용 가능

2) 별칭 지정 가능

3) 인라인  뷰나 임시 테이블로 판단함 

*서브쿼리 SELECT문 내 SELECT문이 또 쓰여있는 쿼리

*인라인뷰: 서브쿼리가 FROM절 내 쓰여진 것

 

4. VIEW테이블

정의: 일종의 가상 테이블로서 실제 데이터가 하드웨어에 저장되는 것은 아님(실제 데이터를 가지고 있지 않음), 테이블 구조가 변경되더라도 독립적으로 존재함

 

목적

1) 사용상 편의를 위해 사용

2) 수행속도의 향상을 위해 사용

3) SQL의 성능 향상을 위해

4) 임시적인 작업을 위해 사용

5) 보안 관리를 위해 사용

 

 

 

 

 

 

6. SQL문장 실행 순서: SELECT ALIAS > FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER BY

 

7. 조회되는 행 수 제한 

// ORACLE 기준, ROWNUM: 오라클에서 조회된 행이 몇번째 행인지 부여해주는 것
SELECT *
FROM MEMBER
WHERE ROWNUM = 1;

// SQL Server 기준
SELECT TOP(1)
FROM MEMBER;

// MySQL 기준
SELECT *
FROM MEMBER
LIMIT 1;

ROWID

1) 해당 데이터가 어떤 데이터 파일 상에서 어느 블록에 저장되었는지 알려줌

2) 데이터베이스에 저장되어 있는 데이터를 구분할 수 있는 유일한 값

3) ROWID의 번호는 데이터 블록에 데이터가 저장된 순서

4) 테이블에 데이터를 입력하면 자동으로 생성 됨