[SQLD] 2과목 SQL 기본 및 활용 (PART1 SQL기본) 요약 정리

테이블(TABLE)

• 데이터 모델에서 인스턴스에 해당하는 것은 로우, 속성에 해당하는 것은 컬럼
• 데이터 모델에서 엔터티에 해당
• 데이터를 저장하기 위해 사용
• 데이터베이스는 일반적으로 여러 개의 테이블로 구성

​

​

SELECT문

• 저장되어 있는 데이터를 조회하고자 할 때 사용하는 명령어

SELECT 컬럼1, 컬럼2, ... FROM 테이블 WHERE 컬럼1 = '아무개';

 

• 컬럼을 따로 명시하지 않고 *(asterisk)를 쓰면 전체 컬럼이 조회되며, 조회되는 컬럼의 순서는 테이블의 컬럼 순서와 동일하다. 그리고 WHERE 절이 없으면 테이블의 전체 Row가 조회됨
• 테이블명이나 컬럼명에 별도의 별칭(Alias)을 붙여줄 수 있는데, 테이블명에 Alias를 설정했을 경우 테이블명 대신 Alias를 사용해야 함

​

​

산술 연산자

• (): 괄호로 우선순위를 조정할 수 있음 (우선순위 1)
• *: 곱하기 (우선순위 2)
• /: 나누기 (우선순위 2)
• +: 더하기 (우선순위 3)
• -: 빼기 (우선순위 3)
• 다른 컬럼끼리의 연산(가로 연산)에서 NULL이 포함되어 있으면 결과값은 NULL

​

​

문자 함수

• CHR(ASCII 코드): CHR 함수는 ASCII 코드를 인수로 입력했을 때 매핑되는 문자가 무엇인지 알려주는 함수
• ex) CHR(65) -> A
• LOWER(문자열): 문자열을 소문자로 변환
• ex) LOWER('JENNIE') -> jennie
• UPPER(문자열): 문자열을 대문자로 변환
• ex) UPPER('jennie') -> JENNIE
• LTRIM(문자열 [,특정 문자]) *[]는 옵션: 특정 문자를 따로 명시해주지 않으면 왼쪽 공백을 제거하고, 명시했을 경우 왼쪽부터 제거
• ex) LTRIM(' JENNIE') -> JENNIE
• ex2)LTRIM('블랙핑크', '블랙') -> 핑크
• RTRIM(문자열 [,특정문자]) *[]는 옵션: 특정 문자를 따로 명시해주지 않으면 오른쪽 공백을 제거하고, 명시했을 경우 오른쪽부터 제거
• ex) RTRIM('JENNIE ') -> JENNIE
• ex2)RTRIM('블랙핑크', '핑크') -> 블랙
• TRIM([위치] [특정문자] [FROM] 문자열) *[]는 옵션: 옵션이 하나도 없을 경우 문자열의 왼쪽과 오른쪽 공백을 제거하고, 그렇지 않을 경우 문자열의 위치(LEADING or TRAILING or BOTH)로 지정된 곳부터 한 글자씩 특정 문자와 비교하여 같으면 제거하고 같지 않으면 멈춘다. LTRIM, RTRIM과는 달리 특정 문자는 한 글자만 지정할 수 있음
• ex) TRIM(' JENNIE ') -> JENNIE
• ex2) TRIM(LEADING '블' FROM '블랙핑크') -> 랙핑크
• ex3) TRIM(TRAILING '크' FROM '블랙핑크') -> 블랙핑
• SUBSTR(문자열, 시작점 [,길이] *[]는 옵션: 문자열의 원하는 부분만 잘라서 반환해주는 함수. 길이를 명시하지 않았을 경우 문자열의 시작점부터 문자열의 끝까지 반환
• ex) SUBSTR('블랙핑크제니', 3, 2) -> 핑크
• ex2) SUBSTER('블랙핑크제니', 3, 4) -> 핑크제니
• LENGTH(문자열): 문자열의 길이를 반환
• ex)LENGTH('블랙핑크') -> 4
• REPLACE(문자열, 변경 전 문자열 [,변경 후 문자열]) *[]는 옵션: 문자열에서 변경 전 문자열을 찾아 변경 후 문자열로 바꿔주는 함수. 변경 후 문자열을 명시해주지 않으면 문자열에서 변경 전 문자열을 제거
• ex) REPLACE('블랙핑크제니', '제니', '지수) -> 블랙핑크지수
• ex2) REPLACE('블랙핑크제니', '블랙) -> 핑크제니

​

​

Q. 다음 SQL의 결과?

[SQL]

SELECT LENGTH(SUBSTR(COL1,3,3)) + LENGTH(SUBSTR(COL2,2)) AS RESULT
   FROM SAMPLE;

[SAMPLE] 테이블

COL1	COL2
ORACLE	DATABASE
SQL	DEVELOPER

[결과]

RESULT

10

9

<해설> SUBSTR(COL1,3,3)의 결과 값은 ACL, L이고, SUBSTR(COL2,2)의 결과값은 ATABASE, EVELOPER

​

[SQL]

SELECT LENGTH(RTRIM(COL1, 'LE')) + LENGTH(LTRIM(COL2, 'DE')) AS RESULT
   FROM SAMPLE;

[RESULT]

RESULT

11

9

<해설> RTRIM(COL1, 'LE')는 ORAC, SQ이고, LTRIM(COL2, 'DE')는 ATABASE, VELOPER

​

​

숫자 함수

• ABS(수): 수의 절대값을 반환
• ex) ABS(-1) -> 1
• SIGN(수): 수의 부호를 반환. 양수이면 1, 음수이면 -1, 0이면 0을 반환
• ROUND(수 [,자릿수]) *[]는 옵션: 수를 지정된 소수점 자릿수까지 반올림하여 반환. 자릿수를 명시하지 않았을 경우 기본값은 0이며, 반올림된 정수로 반환하고, 음수일 경우 지정된 정수부를 반올림하여 반환
• ex) ROUND(163.76, 1) -> 163.8
• ex2) ROUND(163.76, -2) -> 200
• TRUNC(수 [,자릿수]) *[]는 옵션: 수를 지정된 소수점 자릿수까지 버림하여 반환. 자릿수를 명시하지 않았을 경우 기본값은 0이며, 버림된 정수로 반환하고, 자릿수가 음수일 경우 지정된 정수부에서 버림하여 반환
• ex) TRUNC(54.29, 1) -> 54.2
• ex2) TRUNC(54.29, -1) -> 50
• CEIL(수) : 소수점 이하의 수를 올림한 정수를 반환
• ex) CEIL(72.86) -> 73
• ex2) CEIL(-33.4) -> -33
• FLOOR(수): 소수점 이하의 수를 버림한 정수를 반환
• ex) FLOOR(22.3) -> 22
• ex2) FLOOR(-22.3) -> -23
• MOD(수1, 수2): 수1을 수2로 나눈 나머지를 반환
• ex) MOD(15, 7) -> 1
• ex2) MOD(15, -4) -> 3
• ex3) MOD(-15, 0) -> -15
• ex4) MOD(-15, -4) -> -3

​

​

날짜 함수

• SYSDATE: 현재의 연, 월, 일, 시, 분, 초를 반환
• ex) SYSDATE -> 2021-09-21 22:08:08
• EXTRACT(특정 단위 FROM 날짜 데이터): 날짜 데이터에서 특정 단위(YEAR, MONTH, DAY, HOUR, MINUTE, SECOND)만을 출력해서 반환
• ex) EXTRACT(YEAR FROM SYSDATE) -> 2021
• ADD_MONTHS(날짜 데이터, 특정 개월 수): 날짜 데이터에서 특정 개월 수를 더한 날짜를 반환해주는 함수. 날짜의 이전 달이나 다음 달에 기준 날짜의 일자가 존재하지 않으면 해당 월의 마지막 일자가 반환
• ex) ADD_MONTHS(TO_DATE('2021-12-31', 'YYYY-MM-DD'), -1) -> 2021-11-30
• ex2) ADD_MONTHS(TO_DATE('2021-12-31', YYYY-MM-DD'), 1) -> 2022-01-31

​

​

명시적 형변환과 암시적 형변환

• 명시적 형변환: 변환 함수를 사용하여 데이터 유형 변환을 명시적으로 나타냄
• 암시적 형변환: 데이터베이스가 내부적으로 알아서 데이터 유형 반환

​

​

명시적 형변환에 쓰이는 함수

• TO_NUMBER(문자열): 문자열을 숫자형으로 변환
• ex) TO_NUMBER('1234') -> 1234
• TO_CHAR(수 or 날짜 [, 포맷]): 수나 날짜의 데이터를 포맷 형식의 문자형으로 변환
• ex) TO_CHAR(1234) -> '1234'
• ex2) 현재 2021년 9월 21일 22시 33분 21초일 경우 TO_CHAR(SYSDATE, 'YYYYMMDD HH24MISS')
• TO_DATE(문자열, 포맷): 포맷 형식의 문자형의 데이터를 날짜형으로 변환해주는 함수. YYYY: 년, MM: 월, DD: 일, HH: 시(12), HH24: 시(24), M: 분, SS: 초
• ex) TO_DATE('20210602', 'YYYYMMDD') -> 2021-06-02

​

​

NULL 관련 함수

• NVL(인수1, 인수2): 인수1의 값이 NULL일 경우 인수 2를 반환하고 NULL이 아닐 경우 인수 1을 반환
• NULLIF(인수1, 인수2): 인수1과 인수2가 같으면 NULL을 반환하고 같지 않으면 인수1을 반환
• COALESCE(인수1, 인수2, 인수3 ...): NULL이 아닌 최초의 인수를 반환

​

​

CASE: '~이면 ~이고, ~이면 ~이다' 식으로 표현되는 구문

​

​

WHERE 절

• 비교 연산자를 사용하여 원하는 데이터를 조회할 수 있다
• 논리 연산자는 SQL에 명시된 순서와 관계없이 ( ) -> NOT -> AND -> OR 순으로 처리
• NULL과의 연산 결과하는 항상 NULL
• 조건식에서 컬럼명은 일반적으로 좌측에 위치하지만 우측에 위치해도 정상적으로 동작

​

​

부정 비교 연산자

• !=: 같지 않음
• ^=: 같지 않음
• <>: 같지 않음
• not 컬럼명 =: 같지 않음
• not 컬럼명 >: 크지 않음

​

​

SQL 연산자

• BETWEEN A AND B: A와 B의 사이(A, B 포함)
• ex) where col between 1 and 10
• LIKE '비교 문자열': 비교 문자열 포함
• ex) where col like '방탄%'
• IN(LIST): LIST 중 하나와 일치
• ex) where col in (1, 3, 5)
• IS NULL: NULL 값
• ex) where col is null

​

​

부정 SQL 연산자

• NOT BETWEEN A AND B: A와 B의 사이가 아님(A, B 미포함)
• NOT IN (LIST): LIST 중 일치하는 것이 없음
• IS NOT NULL: NULL 값이 아님

​

​

SELECT * FROM EMP WHERE NOT (SAL < 300 AND SAL > 500); 쿼리의 WHERE 절의 괄호를 풀면 WHERE SAL >= 300 OR SAL <= 500

​

​

Q. PHONE 데이터가 NULL이 아닌 MEMBER를 찾는 SQL?

SELECT * FROM MEMBER WHERE PHONE IS NOT NULL;

​

​

Q. 2021년 1학기의 수학, 평균 점수가 90점 이상인 학생을 구하는 SQL?

SELECT STUDENT_NO,
AVG(MATH_SCORE) AS AVG
FROM STUDENT_SCORE
WHERE YERE = '1'
AND SEMESTER = '1'
GROUP BY STUDENT_NO
HAVING AVG(MATH_SCORE >= 90;

​

ORDER BY

• ASC: 오름차순
• DESC: 내림차순
• 옵션 생략 시 ASC가 기본값
• Oracle의 경우 NULL 값을 최댓값으로 취급

​

​

JOIN

• 두 개 이상의 테이블을 연결하여 데이터를 출력하는 것
• EQUI JOIN과 Non EQUI JOIN은 하나의 쿼리에서 같이 사용할 수 있다
• EQUI JOIN은 JOIN하는 테이블들의 컬럼값에 '=' 조건이 성립하는 경우 가능
• 테이블 간에 PK, FK의 연관 관계가 없어도 JOIN 가능
• JOIN 되는 두 테이블에 모두 존재하는 컬럼의 경우 컬러명 앞에 반드시 테이블명이나 ALIAS를 명시해야 함
• WHERE 절에서 JOIN 컬럼 옆에 (+)를 붙이면 OUTER JOIN이 된다. WHERE A.COL1 = B.COL2(+)의 경우 EQUI JOIN이기 때문에 SELECT 절에서 JOIN 컬럼인 A.COL1을 B.COL1로 대체할 수 있음

​

​

Q. 다음 SQL의 결과?

[SQL]

SELECT SUM(A.COL2) AS RESULT1,
      COUNT(A.COL3) AS RESULT2,
      COUNT(DISTINCT A.COL3) AS RESULT3
   FROM SAMPLE A, SAMPLE2 B
WHERE A.COL1 = B.COL1;

[SAMPLE1 테이블]

COL1	COL2	COL3
가	100	A
나	110	A
다	200	NULL
라	150	B
마	50	NULL

[SAMPLE2 테이블]

COL1	COL2
가	10
나	0
다	5

[결과]

RESULT1	RESULT2	RESULT3
410	2	1

<해설> SAMLE1 테이블과 SAMPLE2 테이블을 INNER JOIN하였으므로 두 테이블에 공통적으로 존재하는 데이터만 출력된다.

COL1	COL2	COL3
가	100	A
나	110	A
다	200	NULL

COUNT(컬럼)의 경우 NULL 값을 제외하고 COUNT 하므로 RESULT2는 2, DISTINCT는 중복값을 제거하는 명령어이므로 RESULT3은 1

​

​

Q. 다음 두 테이블을 COL1을 JOIN 컬럼으로 하여 LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN했을 때 각각 출력되는 데이터 건수로 가장 적절한 것은 무엇인가? (단, SAMLE1 테이블이 LEFT TABLE, SAMPLE2 테이블이 RIGHT TABLE이라고 가정)

[SAMLE1 테이블]

COL1	COL2	COL3
1	A	D
3	B	E
5	C	F

[SAMPLE2 테이블]

COL1	COL2
2	G
3	H
3	I

[결과]

LEFT	RIGHT	FULL
4	3	5

<해설> 각 JOIN의 출력 결과는 다음과 같다.

SAMLE1 LEFT OUTER JOIN SAMPLE2

COL1	COL2	COL3	COL1	COL4
1	A	D	NULL	NULL
3	B	E	3	H
3	B	E	3	I
5	C	F	NULL	NULL

SAMPLE1 RIGHT OUTER JOIN SAMPLE2​

COL1	COL2	COL3	COL1	COL4
NULL	NULL	NULL	2	G
3	B	E	3	H
3	B	E	3	I

SAMPLE1 FULL OUTER JOIN SAMPLE2

COL1	COL2	COL3	COL1	COL4
1	A	D	NULL	NULL
3	B	E	3	H
3	B	E	3	I
5	C	F	NULL	NULL
NULL	NULL	NULL	2	G

​

Q. 다음 SQL 결과?

[SQL]

SELECT *
   FROM SAMPLE1 A RIGHT OUTER JOIN SAMPLE2 B
      ON (A.COL1 = B.COL1 AND B.COL2 IS NOT NULL);

[SAMPLE1 테이블]

COL1	COL2
2	G
3	H
3	I

[SAMPLE2 테이블]

COL1	COL2
1	A
2	B
3	NULL
4	D
5	E

[결과]

COL1	COL2	COL1	COL2
NULL	NULL	1	A
2	G	2	B
NULL	NULL	3	NULL
NULL	NULL	4	D
NULL	NUL	5	E

<해설> OUTER JOIN에서 기준이 되는 테이블은 항상 모두 출력이 되고 ON 절에 정의된 조건으로 JOIN의 여부를 판단. 문제에서는 SAMPLE2 테이블이 기준이므로 SAMPLE2 데이터는 일단 모두 출력되고, (A.COL1 = B.COL1 AND B.COL2 IS NOT NULL)를 만족하는 SAMPLE1의 데이터만 JOIN되어 최종 출력

​

​

[SQL]

SELECT *
   FROM SAMPLE1 A RIGHT OUTER JOIN SAMPLE2 B
      ON A.COL1 = B.COL1
WHERE B.COL2 IS NOT NULL;

[결과]

COL1	COL2	COL1	COL2
NULLL	NULL	1	A
2	G	2	B
NULL	NULL	4	D
NULL	NULL	5	E

<해설> OUTER JOIN에서 기준이 되는 테이블은 항상 모두 출력이 되지만 최종적으로 WHERE 조건에 의해 필터링을 거치게 된다. 이전 문제에서 WHERE 절이 없는 경우 COL1이 3인 Row도 JOIN에 성공하여 출력되었음.

​

​

NATURAL JOIN: A 테이블과 B 테이블에서 같은 이름을 가진 컬럼들이 모두 동일한 데이터를 가지고 있을 경우 JOIN이 되는 방식

​

​

Q. 다음 SQL의 결과?

[SQL]

SELECT SUM(COL1)
   FROM SAMPLE1 A NATURAL JOIN SAMPLE2 B;

[SAMPLE1 테이블]

COL1	COL2
2	G
3	H
4	I

[SAMPLE2 테이블]

COL1	COL2
1	A
2	G
3	H
4	D
4	I

[답] 9

​

<해설> SAMPLE1 테이블과 SAMPLE2 테이블의 COL1, COL2 두 컬럼 값이 모두 같아야 JOIN에 성공하게 되며 JOIN 결과는

COL1	COL2
2	G
3	H
4	I

​

Q. 다음 SQL의 결과? (Oracle이라고 가정)

[SQL]

SELECT SUM(COL1)
   FROM SAMPLE1 A JOIN SAMPLE2 B
   USING (COL1);

[답] 13

<해설> Oracle의 경우 NATURAL JOIN에서 USING 절을 이용하면 이름이 같은 컬럼이 존재하더라도 내가 원하는 컬럼만 골라서 EQUI JOIN할 수 있으며 JOIN의 결과는

COL1	COL2
2	G
3	H
4	D
4	I

​

CROSS JOIN: A 테이블과 B 테이블 사이에 JOIN 조건이 없는 경우, 조합할 수 있는 모든 경우를 출력

​

​

Q. 다음 SQL과 같은 결과를 출력하는 SQL?

[SQL]
SELECT BOY_NAME, GIRL_NAME
   FROM GIRL, BOY B;

<답>

SELECT BOY_NAME, GIRL_NAME
   FROM GIRL CROSS JOIN BOY B;

​

​

Q. 다음 SQL 중 에러가 발생하지 않는 SQL? (Oracle이라고 가정) <답> (1)

(1)
SELECT A.COL1, B.COL2
   FROM SAMPLE1 A, SAMPLE2 B
WHERE B.COL2 IS NULL;

별도의 JOIN 조건이 없는 경우 두 테이블은 Cartessian Product(카티션 곱)가 된다.

(2)
SELECT A.COL1, B.COL2
   FROM SAMPLE1 A NATURAL JOIN SAMPLE2 B
      ON A.COL1 = B.COL1;

NATURAL JOIN에는 ON 절을 쓸 수 없다.

(3)
SELECT A.COL1, B.COL2
   FROM SAMPLE1 A, SAMPLE2 B
WHERE A.COL1(+) = B.COL1(+);

Oracle의 경우 OUTER JOIN 작성 시 (+)기호를 사용하는데 좌변이나 우변 중 하나에만 표기해야 함.

LEFT OUTER JOIN일 때 우변, RIGHT OUTER JOIN일 때 좌변에 표기.

(4)
SELECT B.COL1, B.COL2
   FROM SAMPLE1 A JOIN SAMPLE2 B
USING (COL1, COL2);

JOIN에서 USING 절을 사용할 경우 USING 절로 정의된 컬럼 앞에는 별도의 테이블명이나 ALIAS를 표기할 수 없음