07. SQL 응용
Chapter 01. 데이터베이스 기본
# 14 - 21년 2회
[ 학생정보 테이블 ]과 [ 학과정보 테이블 ]을 조인하려고 한다. 밑줄 친 곳을 채워 알맞은 쿼리를 작성하시오.
[ 학생정보 테이블 ]
| 학번 | 이름 | 학과 |
| 1001 | 홍길동 | 컴퓨터 |
| 1002 | 장길산 | 보안 |
| 1003 | 임꺽정 | 빅데이터 |
| 1004 | 강은미 | 인공지능 |
[ 학과정보 테이블 ]
| 학과 | 지도교수 |
| 컴퓨터 | 산업쌤 |
| 보안 | 보안쌤 |
| 빅데이터 | 중앙쌤 |
| 인공지능 | 혹시팟쌤 |
SELECT 학생정보.학번, 학생정보.이름, 학과정보.학과, 학과정보.지도교수
FROM 학생정보 JOIN 학과정보 ______ 학생정보.학과=학과정보.________;
A. ① ON ② 학과
해설.
JOIN 절은 조인조건으로 ON을 사용한다.
① 내부 조인
SELECT A.컬럼1, A.컬럼2, ..., B.컬럼1, B.컬럼2,..
FROM 테이블1 A [INNER]
JOIN 테이블2 B
ON 조인조건
[WHERE 검색조건];- 같은 이름 칼럼이 여러 테이블에 있을 경우, '별칭.컬럼명' 형태로 명시
- INNER 키워드 생략해도 내부 조인
- 검색 조건 추가 시, 조인된 값에서 해당 조건에 맞는 결과만 출력
② 왼쪽 외부 조인
SELECT A.컬럼1, A.컬럼2, ... ,B.컬럼1, B.컬럼2, ...
FROM 테이블1 A LEFT
[OUTER] JOIN 테이블2 B
ON 조인조건
[WHERE 검색조건];- OUTER 키워드 생략해도 왼쪽 외부 조인
- 검색 조건 추가 시, 조인 값에서 해당 조건에 맞는 결과만 출력
③ 오른쪽 외부 조인
SELECT A.컬럼1, A.컬럼2, ... , B.컬럼1, B.컬럼2, ...
FROM 테이블1 A RIGHT
[OUTER] JOIN 테이블2 B
ON 조인조건
[WHERE 검색조건];- OUTER 키워드 생략해도 오른쪽 외부 조인
- 검색 조건 추가 시, 조인 값에서 해당 조건에 맞는 결과만 출력
④ 완전 외부 조인
SELECT A.컬럼1, A.컬럼2, ... , B.컬럼1, B.컬럼2, ...
FROM 테이블1 A FULL
[OUTER] JOIN 테이블2 B
ON 조인조건
[WHERE 검색조건];- OUTER 키워드 생략해도 완전 외부 조인
- 검색 조건 추가 시, 조인 값에서 해당 조건에 맞는 결과만 출력
⑤ 교차 조인
SELECT 컬럼1, 컬럼2, ...
FROM 테이블1 CROSS
JOIN 테이블2- 조인 조건 없이 모든 데이터 조합을 추출하기 때문에 ON절 없음
⑥ 셀프 조인
SELECT A.컬럼1, A.컬럼2, ..., B.컬럼1, B.컬럼2, ...
FROM 테이블1 A [INNER]
JOIN 테이블1 B
ON 조인조건
[WHERE 검색조건];- 같은 테이블에 대한 조인이기 때문에 별칭만 A,B와 같이 다르게 함
- 검색 조건 추가 시, 조인된 값에서 해당 조건에 맞는 결과만 출력
#15 - 21년 2회
다음 [회원] 테이블에서 회원번호가 N4인 튜플의 전화번호를 수정하는 쿼리를 완성하시오.
[ 회원 테이블 ]
| 회원번호 | 이름 | 성별 | 전화번호 |
| A1 | 홍길동 | F | 010-11 |
| N4 | 임꺽정 | M | 010-12 |
_______ 회원 ________ 전화번호='010-14'
WHERE 회원번호='N4';
A. ① UPDATE ② SET
# UPDATE 명령어 - 데이터 조작어(DML; Data Manipulation Language)
테이블 내 칼럼에 저장된 데이터 내용 변경하는 명령어
UPDATE 테이블명
SET 속성명=데이터, ...
WHERE 조건;
▶ UPDATE 명령문은 WHERE절로 조건 만족 시에만 특정 컬럼 값 수정하는 용도로 자주 사용
※ DDL의 ALTER 명령문은 테이블 컬럼을 수정하는 것이고
DML의 UPDATE 명령문은 테이블 내 컬럼의 데이터 내용을 수정하는 것이므로 혼동하지 말 것
해설.
UPDATE는 데이터 내용 변경 시에 사용하는 명령어
UPDATE 테이블명
SET 속성명=데이터, ...
WHERE 조건;
# 16 - 21년 2회
다음 [학생] 테이블을 이용해 이름이 이로 시작하는 학생들에 대해 내림차순으로 정렬하려고 한다. 쿼리의 빈칸에 알맞은 키워드를 쓰시오.
[ 학생 테이블 ]
| 학번 | 이름 | 학년 | 학과 |
| 202101 | 이순신 | 3 | 컴퓨터공학 |
| 202102 | 김영희 | 1 | 전기공학 |
| 202103 | 이상 | 4 | 건축공학 |
| 202104 | 임꺽정 | 2 | 전자공학 |
| 202105 | 이정재 | 3 | 토목공학 |
SELECT *
FROM 학생
WHERE 이름 LIKE ______
ORDER BY 이름 ______;
A. ① '이%' ② DESC
# WHERE 절 - 데이터 조작어(DML; Data Manipulation Language)
WHERE 절 조건은 비교, 범위, 집합, 패턴, NULL, 복합조건이 있음
1. 비교
= : 값이 같은 경우 조회
<>, != : 값이 다른 경우 조회
< ,<=, >, >= : 비교 연산 해당 시 조회
2. 범위
BETWEEN : 컬럼 BETWEEN 값1 AND 값2
값1보다 크거나 같고 값2다 작거나 같은 데이터 조회
≒ 컬럼 >= 값1 AND 컬럼 <= 값2
3. 집합
IN : 컬럼 IN (값1, 값2, ...)
컬럼 값이 IN 안에 포함된 경우 데이터 조회
NOT IN : 컬럼 NOT IN (값1, 값2, ...)
컬럼 값이 IN 안에 포함되지 않은 경우 데이터 조회
4. 패턴
LIKE : 컬럼 LIKE 패턴
컬럼 값이 패턴에 포함된 경우 데이터 조회
▶ % : 0개 이상 문자열과 일치
▶ [ ] : 1개 문자와 일치
▶ [^] : 1개 문자와 불일치
▶ _ : 특정 위치의 1개 문자와 일치
5. NULL
IS NULL : 컬럼 IS NULL
컬럼 값이 NULL인 데이터 조회
IS NOT NULL : 컬럼 IS NOT NULL
컬럼 값이 NULL이 아닌 데이터 조회
6. 복합조건
AND : 조건1 AND 조건2
조건1과 조건2 모두를 만족하는 데이터 조회
OR : 조건1 OR 조건2
조건1 혹은 조건2 둘 중 하나라도 만족하는 데이터 조회
NOT : NOT 조건
조건에 해당하지 않는 데이터 조회
# ORDER BY절 - 데이터 조작어(DML; Data Manipulation Language)
속성값 정렬할 때 사용, 기본값은 ASC(오름차순)
ASC : 오름차순
DESC : 내림차순
해설.
컬럼이 패턴에 포함된 경우의 데이터를 조회할 때 LIKE 문자열 사용
% : 0개 이상 문자열과 일치
[ ] : 1개 문자와 일치
[^] : 1개 문자와 불일치
_ : 특정 위치의 1개 문자와 일치
속성값 정렬하고자 할 때, ORDER BY 절 사용 (기본값 ASC)
ASC : 오름차순
DESC : 내림차순
#17 - 22년 1회
다음은 점수에 대해 내림차순 하는 SQL이다. 괄호 ( ) 안에 들어갈 SQL 구문을 쓰시오.
SELECT NAME, SCORE
FROM 성적
( ) BY ( ) ( )
A. ① ORDER ② 점수 ③ DESC;
해설.ORDER BY 절은 속성값 정렬할 때 사용ASC ▶ 오름차순 , DESC ▶ 내림차순 키워드 생략 시, 오름차순이 기본값
#18 - 21년 3회
다음 SQL의 실행 결과를 쓰시오.
[ HOXY 테이블 ]
| NAME |
| SOPHIA |
| OLIVIA |
| SEMA |
[ POT 테이블 ]
| RULE |
| S% |
| %A% |
SELECT COUNT(*) CNT
FROM HOXY CROSS
JOIN POT
WHERE HOXY.NAME LIKE POT.RULE;
A.
CROSS JOIN 결과
| NAME | RULE |
| SOPHIA | S% |
| OLIVIA | S% |
| SEMA | S% |
| SOPHIA | %A% |
| OLIVIA | %A% |
| SEMA | %A% |
WHERE HOXY.NAME LIKE POT.RULE 결과
| NAME | RULE |
| SOPHIA | S% |
| SEMA | S% |
| SOPHIA | %A% |
| OLIVIA | %A% |
| SEMA | %A% |
COUNT(*) CNT 결과 (=최종)
| CNT |
| 5 |
- %는 0개 이상 문자열과 일치하므로 앞에 혹은 뒤에 문자열이 없어도 괜찮다 > 즉, 데이터 값에 A가 있기만 하면 OK
해설.
동일
#19 - 21년 3회
GRANT의 기능을 서술하시오.
A. 데이터베이스 관리자(DBA)가 사용자에 데이터베이스에 대한 권한을 부여하는 명령어
# 데이터 제어어 (DCL; Data Control Language)
데이터베이스 관리자(DBA)가 사용하는 제어용 언어
※ GR : GRANT / REVOKE
1. GRANT - 사용 권한 부여
관리자(DBA)가 사용자에 데이터베이스에 대한 권한 부여하는 명령어
GRANT 권한 ON 테이블 TO 사용자;
2. REVOKE - 사용 권한 취소
관리자(DBA)가 사용자에 부여했던 권한을 회수하기 위한 명령어
REVOKE 권한 ON 테이블 FROM 사용자;
해설.
데이터 제어어의 유형에는 GRANT, REVOKE 2가지가 있다.
GRANT ▶ 관리자(DBA)가 사용자에 데이터베이스에 대한 권한을 부여하는 명령어REVOKE ▶ 관리자(DBA)가 사용자에 부여했던 권한을 회수하기 위한 명령어
#20 - 22년 2회
다음 TB 테이블에 대하여 다음 SQL을 실행하였을 때 [결과]의 괄호 ( ) 안에 출력되는 값은 무엇인가?
[ TB 테이블 ]
| SEQ | COL1 | COL2 |
| 1 | 2 | NULL |
| 2 | 3 | 6 |
| 3 | NULL | 5 |
| 4 | 5 | 3 |
| 5 | 6 | 3 |
SELECT COUNT(COL2)
FROM TB
WHERE COL1 IN(2,3) OR COL2 IN(3,5);
[ 결과 ]
| COUNT(COL2) |
| ( ) |
A.
WHERE COL1 IN(2,3) OR COL2 IN(3,5) 결과
| SEQ | COL1 | COL2 |
| 1 | 2 | NULL |
| 2 | 3 | 6 |
| 3 | NULL | 5 |
| 4 | 5 | 3 |
| 5 | 6 | 3 |
SELECT COUNT(COL2) 결과
| COUNT(COL2) |
| 4 |
해설.
COUNT는 행의 개수를 구하는 집계함수이다.
▶ COUNT(컬럼명) : NULL 값 제외하고 COUNT
▶ COUNT(*) : NULL도 포함하여 전부 COUNT
COL1 IN(2,3)으로 SEQ 1,2 행이 선택되며, COL2 IN(3,5)의 결과로 SEQ 3,4,5 행이 선택OR 연산으로 모든 행이 선택되는 결과
COUNT(COL2)를 통해 NULL을 제외한 2,3,4,5 행 COUNT인 4 출력
#21 - 22년 3회
[직원 테이블]과 [부서 테이블]이 다음과 같을 때 다음 쿼리를 수행한 결과를 쓰시오.
[ 부서 테이블 ]
| 부서코드 | 부서명 |
| 10 | 기획부 |
| 20 | 영업부 |
| 30 | 디자인부 |
[ 직원 테이블 ]
| 부서코드 | 직원코드 | 부서원 |
| 10 | 1 | 홍길동 |
| 10 | 2 | 장길산 |
| 20 | 3 | 임꺽정 |
| 20 | 4 | 김철수 |
| 20 | 5 | 이영희 |
| 30 | 6 | 이순신 |
| 30 | 7 | 안중근 |
[ 조건 ]
- [부서 테이블] 생성할 시 부서코드는 PRIMARY KEY로 선언되어있고 CASCADE 함수 사용
- [직원 테이블] 부서코드는 [부서 테이블]의 부서코드를 FOREIGN KEY로 참조
[ 쿼리 ]
SELECT COUNT(DISTINCT 부서코드)
FROM 직원;DELETE FROM 부서
WHERE 부서코드='20';
SELECT COUNT(DISTINCT 직원코드)
FROM 직원;
A.
①
| COUNT(DISTINCT 부서코드) |
| 3 |
②
DELETE FROM 부서 WHERE 부서코드='20'; 결과
[부서 테이블]
| 부서코드 | 부서명 |
| 10 | 기획부 |
| 30 | 디자인부 |
[직원 테이블]
| 부서코드 | 직원코드 | 부서원 |
| 10 | 1 | 홍길동 |
| 10 | 2 | 장길산 |
| 30 | 6 | 이순신 |
| 30 | 7 | 안중근 |
SELECT COUNT(DISTINCT 직원코드) FROM 직원; 결과
| COUNT(DISTINCT 직원코드) |
| 4 |
# DROP TABLE 명령어 - 데이터 정의어 (DDL; Data Definition Language)
DROP TABLE은 테이블을 삭제하는 명령
DROP TABE 테이블명 [CASCADE | RESTRICT];
▶ CASCADE : 참조하는 테이블까지 연쇄적으로 제거하는 옵션
▶ RESTRICT : 다른 테이블이 삭제할 테이블을 참조 중이면 제거하지 않는 옵션
해설.
① DISTINCT(중복 제거)가 있으므로 부서코드를 기준으로 10 1건, 20 1건, 30 1건이므로 총 3건인 3 출력
② DELETE 문으로 [부서 테이블] 에서 부서코드가 '20'인 데이터를 삭제
[부서 테이블] 생성 당시 조건에서 부서코드가 PRIMARY KEY이며 CASCADE 함수를 사용
[직원 테이블] 의 부서코드는 [부서 테이블]의 부서코드를 FOREIGN KEY로 참조한 것
▶ DELETE의 CASCADE 함수는 참조하는 테이블까지 연쇄적으로 제거하는 옵션이므로
[부서 테이블]의 부서코드 값이 20인 행 뿐만 아니라 [직원 테이블]의 부서코드 값 20인 데이터들도 함께 삭제
▶ [직원 테이블] 부서코드 20인 데이터 직원코드 3,4,5가 삭제된 후에 COUNT(DISTINCT 직원코드)의 결과는
1,2,6,7 만 남았으므로 총 4건인 4 출력
#22 - 23년 1회
[학생] 테이블에서 학생 이름이 '민수'인 튜플을 삭제하는 쿼리를 작성하시오.
[ 학생 테이블 ]
| 학번 | 학년 | 이름 |
| 100 | 3 | 현정 |
| 200 | 2 | 민수 |
| 300 | 1 | 현수 |
| 400 | 4 | 민정 |
A.
DELETE FROM 학생
WHERE 이름='민수';
# DELETE 명령어 - 데이터 조작어(DML; Data Manipulation Language)
DELETE는 데이터 내용 삭제할 때 사용하는 명령어
※ 델프웨 : DELETE FROM / WHERE
DELETE FROM 테이블명
WHERE 조건;
▶ 모든 레코드 삭제 시, WHERE절 없이 DELTE만 사용
해설.
튜플 삭제 시에는 DELETE 명령어 사용
DELETE FROM 테이블명 WHERE 조건;
#23 - 23년 1회
다음은 스키마와 관련된 내용이다. 각 괄호 안에 알맞은 답을 [보기]에서 찾아 작성하시오.
- ( ① ) 스키마는 사용자나 개발자 관점에서 필요로 하는 데이터베이스의 논리적 구조이고, 사용자 뷰를 나타내며, 서브 스키마로 불린다.
- ( ② ) 스키마는 데이터베이스의 전체적인 논리적 구조이다. 그리고 전체적인 뷰를 나타내고, 개체 간의 관계, 제약조건, 접근 권한, 무결성, 보안에 대해 정의한다.
- ( ③ ) 스키마는 물리적 저장 장치의 관점에서 보는 데이터베이스 구조이고, 실제로 데이터베이스에 저장될 레코드의 형식을 정의한다. 그리고 데이터 항목의 표현 방법, 내부 레코드의 물리적 순서 등을 표현한다.
A. ① 외부 ② 개념 ③ 내부
해설.
스키마는 외부, 개념, 내부 3계층으로 구성되어 있다.
1. 외부 스키마 (External Schema)
사용자나 개발자 관점에서 필요로 하는 데이터베이스의 논리적 구조로 사용자 뷰를 나타낸다. 서브 스키마라고도 불린다.
2. 개념 스키마 (Conceptual Schema)
데이터베이스의 전체적인 논리적 구조로 전체적인 뷰를 나타낸다.
개체 간 관계, 제약조건, 접근 권한, 무결성, 보안에 대해 정의한다.
3. 내부 스키마 (Internal Schema)
물리적 저장 장치의 관점에서 보는 데이터베이스 구조로 실제 데이터베이스에 저장될 레코드 형식을 정의하고 저장한다.데이터 항목의 표현 방법, 내부 레코드의 물리적 순서 등을 표현한다.
#24 - 23년 2회
다음 [처리 조건]을 만족하는 SQL 문을 완성할 수 있도록 밑줄 안에 들어갈 옵션은 무엇인가?
[ 처리 조건 ]
- 'hoxypot' 뷰 테이블 제거
- 'hoxypot' 뷰 테이블을 참조하는 테이블도 연쇄적으로 제거
[ SQL 문 ]
DROP VIEW hoxypot _________________________;
A. CASCADE
해설.
VIEW 테이블 삭제 시 DROP 명령어 사용
DROP 명령어 옵션
▶ CASCADE : 참조 테이블까지 연쇄 제거하는 옵션
▶ RESTRICT : 다른 테이블이 삭제할 테이블 참조 중이면 제거하지 않는 옵션
#25 - 23년 2회
[학생 테이블]의 스키마 이용해 [처리 조건]에 맞는 쿼리문을 작성하시오.
[ 학생 테이블 ]
| 속성명 | 데이터 타입 | 비고 |
| 학번 | INT | PRIMARY KEY |
| 이름 | VARCHAR(20) | |
| 학년 | INT | |
| 과목 | VARCHAR(20) | |
| 연락처 | VARCHAR(20) |
[ 처리 조건 ]
- 학생 테이블에 학번이 9830287, 이름이 '한국산', 학년이 3, 과목이 '경영학개론', 연락처가 '050-1234-1234'인 학생의 정보를 입력하라.
- 명령문 마지막의 세미콜론(;)은 생략이 가능하다.
- 인용 부호가 필요한 경우 작은 따옴표(')를 사용한다.
A.
INSERT INTO 학생
VALUES (9830287, '한국산', 3, '경영학개론', '050-1234-1234')
# INSERT 명령어 - 데이터 조작어(DML; Data Manipulation Language)
INSERT는 데이터 내용 삽입 시에 사용하는 명령어
※ 인인벨 : INSERT INTO / VALUES
INSERT INTO 테이블명 (속성명1, ...)
VALUES (데이터1, ...);
▶ 삽입 데이터와 테이블 내 속성, 데이터 개수, 데이터 타입이 일치해야 함
▶ 속성명 생략 가능
▶ 속성 타입 숫자면 데이터에 따옴표 X
▶ 속성 타입 문자열이면 데이터에 따옴표 O
해설.
INSERT는 데이터 내용 삽입 시 사용하는 명령어
INSERT INTO 테이블명 (속성명1, ...)
VALUES (데이터1, ....);
- 속성과 데이터 개수, 데이터 타입이 일치해야 함
- 속성명 생략 가능
- 속성 타입이 숫자인 경우, 데이터 따옴표 붙이지 않아도 됨
- 속성 타입이 문자열인 경우, 데이터 따옴표 붙여야 함
#26 - 23년 3회
다음 빈칸에 들어갈 UNION 연산에 대한 결괏값을 작성하시오.
[ 쿼리 ]
SELECT A FROM t1
UNION
SELECT A FROM t2
ORDER BY A DESC;
[ t1 테이블 ]
| A | B |
| 3 | x |
| 1 | y |
| 4 | z |
| 2 | z |
[ t2 테이블 ]
| A | B |
| 2 | x |
| 4 | y |
| 3 | z |
| 1 | z |
[ 출력 결과 ]
| ① |
| ② |
| ③ |
| ④ |
| ⑤ |
A.
① A
② 4
③ 3
④ 2
⑤ 1
# 집합 연산자 (Set Operator)
집합 연산자는 테이블을 집합 개념으로 보고, 두 테이블 연산에 집합 연산자를 사용하는 방식
집합 연산자는 여러 질의 결과 연결하여 하나로 결합하는 방식 사용
1. UNION - 중복 제외
중복 행이 제거된 합연산 결과 반환
2. UNION ALL - 중복 허용
중복 행을 포함하는 합연산 결과 반환
3. INTERSECT - 중복만 포함
두 쿼리 결과에 공통으로 존재하는 결과만 반환
4. MINUS - 차집합
첫 쿼리에서 두 번째 쿼리에 있는 결과 제외하고 반환
해설.
UNION은 중복을 제외하는 집합 연산자이다.
t1 테이블의 A 컬럼과 t2 테이블의 A 컬럼을 선택하여 UNION 연산 진행▶ 중복 제거하므로 t1의 1,2,3,4 와 t2의 1,2,3,4 값은 모두 동일하므로 1,2,3,4 값 그대로 존재
ORDER BY 절을 통해 A 칼럼에 대해 내림차순 정렬 진행
▶ 결괏값은 A 칼럼에 4,3,2,1 순으로 조회
'WorkOut > 정보처리기사' 카테고리의 다른 글
| SQL 문제 풀이 | 데이터베이스 기본 3 (0) | 2024.04.15 |
|---|---|
| SQL 문제 풀이 | 데이터베이스 기본 2 (2) | 2024.04.12 |
| SQL 문제 풀이 | 데이터베이스 기본 (0) | 2024.04.11 |
| SQL 활용 및 최적화 (0) | 2024.04.09 |
| 응용 SQL | 집계성 SQL (0) | 2024.04.09 |
