14단원 'SQL 고급'의 여섯 레슨에서 6문제, 앞 단원(11)에서 2문제를 섞었고, 끝에 실기 꼴 3문제가 있습니다. 앞 단원 문제가 어느 레슨인지는 정답을 펼치면 보입니다. 문제의 SQL은 아래 네 표로 Oracle에서 돌린다고 보고 풀어 보세요. 실기 꼴은 종이에 답을 끝까지 쓴 뒤 펼치세요. 띄어쓰기만 다르면 같은 답입니다.
학과
| 학과코드 | 학과명 | 건물 |
|---|---|---|
| CS | 컴퓨터 | 공학관 |
| EE | 전자 | 공학관 |
| BA | 경영 | 경영관 |
학생
| 학번 | 이름 | 학년 | 학과코드 |
|---|---|---|---|
| 101 | 김민수 | 3 | CS |
| 102 | 이서준 | 2 | CS |
| 103 | 김지민 | 2 | CS |
| 104 | 박서연 | 1 | EE |
| 105 | 최민준 | 3 | EE |
| 106 | 정하윤 | 3 | EE |
| 107 | 강도현 | 1 | NULL |
과목
| 과목코드 | 과목명 | 학점 | 선수과목 |
|---|---|---|---|
| DS | 자료구조 | 3 | NULL |
| OS | 운영체제 | 3 | DS |
| NW | 네트워크 | 2 | OS |
수강
| 학번 | 과목코드 | 점수 |
|---|---|---|
| 101 | DS | 85 |
| 101 | OS | 75 |
| 101 | NW | 80 |
| 102 | DS | 70 |
| 102 | OS | 60 |
| 103 | DS | 65 |
| 103 | NW | 55 |
| 104 | OS | 75 |
| 104 | NW | 95 |
| 105 | DS | 100 |
| 105 | NW | 90 |
| 107 | NW | 50 |
문제 1
과목 표에 별칭 A와 B를 붙여 두 번 쓰고, A의 선수과목을 B의 과목코드에 이었습니다. 다음 쿼리의 결과로 알맞은 것을 고르세요.
SELECT B.과목명 AS 선수,
A.과목명,
A.학점
FROM 과목 A
RIGHT OUTER JOIN 과목 B
ON A.선수과목 = B.과목코드
ORDER BY B.과목명;- ① 2행 · 짝 없는 줄은 결과에 없다
- ② 3행 · 선수 칸이 네트워크인 줄이 더해지고 그 줄의 과목명·학점 칸이 NULL이다
- ③ 3행 · 과목명 칸이 자료구조인 줄이 더해지고 그 줄의 선수 칸이 NULL이다
- ④ 4행 · 선수 칸이 NULL인 줄과 과목명 칸이 NULL인 줄이 모두 더해진다
정답
② — RIGHT OUTER JOIN은 JOIN 오른쪽에 쓴 B의 행을 모두 남깁니다. B의 세 과목 가운데 네트워크를 선수과목으로 가진 과목이 없어, 네트워크 줄은 A에서 오는 과목명·학점 칸이 NULL로 채워진 채 남습니다. 짝이 맞는 줄 둘에 이 줄이 더해져 3행입니다.
- ① 짝 없는 줄을 버린다고 봤습니다. 짝만 남기는 것은
INNER JOIN이고, 이 쿼리는 오른쪽 표의 행을 모두 남깁니다. - ③ 왼쪽과 오른쪽을 바꿔 봤습니다.
LEFT OUTER JOIN으로 바꾸면 선수과목이 NULL인 자료구조 줄이 남아 그 결과가 됩니다. - ④ 양쪽의 짝 없는 줄을 모두 남긴다고 봤습니다. 그것은
FULL OUTER JOIN이고, 그렇게 이으면 4행입니다.
| 선수 | 과목명 | 학점 |
|---|---|---|
| 네트워크 | NULL | NULL |
| 운영체제 | 네트워크 | 2 |
| 자료구조 | 운영체제 | 3 |
다시 볼 레슨: 조인
문제 2
다음 쿼리의 결과로 알맞은 것을 고르세요.
SELECT S.이름
FROM 학생 S
WHERE NOT EXISTS (
SELECT *
FROM 수강 E
WHERE E.학번 = S.학번
AND E.과목코드 = 'DS'
)
ORDER BY S.이름;- ① 한 행도 나오지 않는다
- ② 정하윤
- ③ 강도현·박서연·정하윤
- ④ 김민수·김지민·이서준·최민준
정답
③ — NOT EXISTS는 서브쿼리 결과에 행이 한 행도 없으면 참입니다. 서브쿼리는 바깥 학생 행의 학번을 받아 그 학생이 자료구조를 들은 행을 찾으므로, 자료구조를 듣지 않은 104 박서연·106 정하윤·107 강도현 세 명이 남습니다.
- ① 서브쿼리가 여러 행을 돌려줄 수 있어 쓸 수 없다고 봤습니다.
EXISTS는 값을 견주지 않고 행이 있는지만 보므로 돌아온 행이 몇이어도 됩니다. - ② 서브쿼리의
AND E.과목코드 = 'DS'를 빼고 읽어 수강 기록이 아예 없는 학생만 남긴다고 봤습니다. 그 조건까지 보면 자료구조만 듣지 않은 박서연·강도현도 남습니다. - ④
NOT을 놓치고 자료구조를 들은 학생으로 봤습니다.EXISTS로 바꾸면 그 네 명이 나옵니다.
| 이름 |
|---|
| 강도현 |
| 박서연 |
| 정하윤 |
다시 볼 레슨: 서브쿼리
문제 3
수강 표에서 학번과 과목코드만 남긴 릴레이션을 R, 운영체제(OS)와 네트워크(NW) 두 과목코드만 든 릴레이션을 S라고 합니다. R ÷ S의 결과로 알맞은 것을 고르세요.
- ① 101
- ② 101·104
- ③ 101·102·104
- ④ 101·102·103·104·105·107
정답
② — 디비전(÷)은 나뉘는 쪽에서 나누는 쪽의 값을 모두 가진 튜플만 남깁니다. 학번마다 들은 과목을 모으면 101은 DS·NW·OS, 102는 DS·OS, 103은 DS·NW, 104는 NW·OS, 105는 DS·NW, 107은 NW입니다. 운영체제와 네트워크를 함께 들은 학번은 101·104입니다.
- ① 나누는 쪽을 과목 표의 세 과목으로 봤습니다. 세 과목을 모두 들은 학번은 101뿐이고, S에는 과목코드가 둘뿐입니다.
- ③ 나누는 쪽의 한쪽만 확인했습니다. 운영체제를 들은 학번이 101·102·104이고, 그 가운데 네트워크까지 들은 학번만 남습니다.
- ④ '모두 가진'을 '하나라도 가진'으로 봤습니다. 둘 가운데 하나라도 들은 학번은 여섯입니다.
다시 볼 레슨: 관계대수: 셀렉트·프로젝트·조인·디비전
문제 4
다음 쿼리는 2학년 이하인 학번에서 90점 이상을 받은 학번을 뺍니다. 이 쿼리의 결과 행 수가 (가), 두 SELECT 문의 자리를 바꾼 쿼리의 결과 행 수가 (나)입니다. 알맞은 것을 고르세요.
SELECT 학번
FROM 학생
WHERE 학년 <= 2
EXCEPT
SELECT 학번
FROM 수강
WHERE 점수 >= 90
ORDER BY 학번;- ① (가) 3행 · (나) 1행
- ② (가) 1행 · (나) 3행
- ③ (가) 3행 · (나) 3행
- ④ (가) 5행 · (나) 5행
정답
① — EXCEPT는 위 SELECT 문의 결과에서 아래 SELECT 문에도 있는 행을 뺍니다. 2학년 이하는 102·103·104·107이고 90점 이상을 받은 학번은 104·105라서, (가)는 104가 빠진 3행입니다.
- ② (가)와 (나)를 거꾸로 봤습니다. 빼는 쪽이 위에 있는 쿼리가 (가)입니다.
- ③ 자리를 바꿔도 결과가 같다고 봤습니다. 자리를 바꿔도 같은 것은
UNION·INTERSECT이고,EXCEPT는 어느 쪽에서 빼는지에 따라 결과가 갈립니다. - ④ 두 결과를 합친다고 봤습니다.
UNION으로 이으면 102·103·104·105·107 5행입니다.
| 학번 |
|---|
| 102 |
| 103 |
| 107 |
자리를 바꾼 쿼리는 104·105에서 104를 빼 1행입니다.
SELECT 학번
FROM 수강
WHERE 점수 >= 90
EXCEPT
SELECT 학번
FROM 학생
WHERE 학년 <= 2
ORDER BY 학번;| 학번 |
|---|
| 105 |
수강 표에는 105의 90점 이상인 행이 둘 있지만, EXCEPT가 겹치는 행을 지워 105는 한 번만 나옵니다.
다시 볼 레슨: 집합 연산자
문제 5
수강 표에서 점수가 70 이상인 행의 학번만 남긴 것과, 학생 표에서 3학년인 학번만 남긴 것이 있습니다. 아래 관계대수 식의 결과 튜플 수가 (가), 같은 두 목록을 UNION ALL로 이은 쿼리의 결과 행 수가 (나)입니다. 알맞은 것을 고르세요.
π 학번 (σ 점수>=70 (수강))
∪ π 학번 (σ 학년=3 (학생))
SELECT 학번
FROM 수강
WHERE 점수 >= 70
UNION ALL
SELECT 학번
FROM 학생
WHERE 학년 = 3
ORDER BY 학번;- ① (가) 5튜플 · (나) 5행
- ② (가) 11튜플 · (나) 5행
- ③ (가) 5튜플 · (나) 11행
- ④ (가) 11튜플 · (나) 11행
정답
③ — 관계대수의 결과는 릴레이션이라 같은 튜플이 둘 있을 수 없습니다. 프로젝트(π)가 겹치는 학번을 하나로 남겨 앞은 101·102·104·105 네 튜플이 되고, 3학년 101·105·106과 합집합(∪)으로 이으면 106이 더해져 5튜플입니다. UNION ALL은 겹치는 행을 지우지 않아 8행과 3행을 그대로 더한 11행입니다.
- ①
UNION ALL도 겹치는 행을 지운다고 봤습니다. 지우는 것은UNION이고, 그렇게 이으면 관계대수와 같은 5행입니다. - ② 둘을 거꾸로 봤습니다. 중복을 남기는 쪽은
UNION ALL이고, 관계대수의 릴레이션에는 같은 튜플이 둘 있을 수 없습니다. - ④ 관계대수의 합집합도 중복을 남긴다고 봤습니다. 점수가 70 이상인 수강 행은 101이 셋, 104·105가 둘씩이지만, 프로젝트는 겹치는 튜플을 하나로 남깁니다.
| 학번 |
|---|
| 101 |
| 101 |
| 101 |
| 101 |
| 102 |
| 104 |
| 104 |
| 105 |
| 105 |
| 105 |
| 106 |
다시 볼 레슨: 관계대수: 집합 연산과 카티션 곱 · 집합 연산자
문제 6
과목 표에서 학점이 3인 과목의 과목코드·과목명만 보이는 뷰 세학점과목을 만들었습니다. 다음 가운데 옳지 않은 것을 고르세요.
- ① 기본 테이블에서 자료구조의 학점을 2로 고치면 세학점과목에서 자료구조 줄이 사라진다
- ② 세학점과목을 바탕으로 또 다른 뷰를 만들 수 있다
- ③ 세학점과목에는 인덱스를 만들 수 없다
- ④ 세학점과목에 보이는 열을 둘에서 하나로 줄이려면
ALTER VIEW로 정의를 고친다
정답
④ — 뷰의 정의는 ALTER 문으로 바꾸지 못합니다. 보이는 열을 줄이려면 DROP VIEW로 지우고 CREATE VIEW로 다시 만듭니다.
- ① 맞습니다. 뷰는 행을 저장하지 않고 조회할 때마다 기본 테이블을 읽어, 학점이 2가 된 자료구조는 뷰의 조건에서 벗어나 보이지 않습니다.
- ② 맞습니다. 뷰를 바탕으로 또 다른 뷰를 만들 수 있습니다.
- ③ 맞습니다. 뷰에는 행이 저장되어 있지 않아 뷰 자체에 인덱스를 만들 수 없습니다.
다시 볼 레슨: 뷰
문제 7
학생 표의 학번에는 기본키가 걸려 있습니다. 여기에 이름 열로 찾는 인덱스를 하나 더 만들었습니다. 그 뒤에 일어나는 일로 알맞은 것을 고르세요.
- ① 학번에 기본키가 걸려 있어 다른 열에는 인덱스를 만들 수 없다
- ② 이름 열에 같은 이름이 두 번 들어오면 오류로 막힌다
- ③ 인덱스가 표 안에 함께 저장되어 저장 공간은 더 들지 않는다
- ④ 행을 넣고 지울 때마다 인덱스도 함께 고쳐야 해서 느려진다
정답
④ — 인덱스는 열 값과 그 값이 든 행의 위치를 정렬해 따로 저장한 구조라, 행이 바뀌면 인덱스의 자리도 함께 고쳐야 합니다. 넣고 고치고 지우는 일이 잦은 표에서는 이것이 부담이 됩니다.
- ① 표에 인덱스를 하나만 둘 수 있다고 봤습니다. 열마다 만들 수 있고, 학번에 기본키를 걸 때 고유 인덱스가 저절로 생겨 이미 둘입니다.
- ② 인덱스가 겹치는 값을 막는다고 봤습니다. 막는 것은
UNIQUE를 붙여 만든 고유 인덱스이고,UNIQUE없이 만든 인덱스는 같은 값이 여럿이어도 됩니다. - ③ 인덱스를 표의 일부로 봤습니다. 인덱스는 표와 따로 저장되어 그만큼 공간을 더 씁니다.
다시 볼 레슨: 인덱스
문제 8
교수 표를 만들고 바로 다시 지웠습니다. 그 사이 내 표 목록을 담은 USER_TABLES에 일어난 일로 알맞은 것을 고르세요.
- ①
CREATE TABLE때 교수 줄이 더해지고,DROP TABLE때 그 줄이 사라진다 - ②
CREATE TABLE때 교수 줄이 더해지고,DROP TABLE뒤에도 그 줄은 남는다 - ③ 두 문장 모두 USER_TABLES를 바꾸지 않는다. 사용자가
INSERT·DELETE를 하지 않았다 - ④ 두 문장 모두 오류로 막힌다. 사용자는 데이터 사전을 바꾸는 문장을 실행할 수 없다
정답
① — DDL 문을 실행하면 DBMS가 데이터 사전을 함께 고칩니다. 표를 만들면 USER_TABLES에 줄이 하나 더해지고, 지우면 그 줄이 없어집니다. 데이터 사전은 언제나 지금 상태를 담습니다.
- ② 지울 때는 반영되지 않는다고 봤습니다. 지운 표의 줄이 남아 있으면 없는 표가 목록에 보입니다.
- ③ 사용자가
INSERT를 해야 줄이 는다고 봤습니다. 데이터 사전의 줄은 DDL 문을 실행할 때 DBMS가 더합니다. - ④ '사용자는 조회만 할 수 있다'를 DDL 문도 막힌다는 뜻으로 봤습니다. 막히는 것은 데이터 사전 뷰를
UPDATE·DELETE로 직접 고치는 일입니다.
CREATE TABLE 교수 (
교번 NUMBER PRIMARY KEY,
이름 VARCHAR2(10 CHAR)
);
DROP TABLE 교수;
SELECT COUNT(*) AS 표수
FROM USER_TABLES;| 표수 |
|---|
| 4 |
교수 줄이 더해졌다가 다시 없어져, 처음의 네 표(과목·수강·학과·학생)만 남습니다.
다시 볼 레슨: 시스템 카탈로그와 데이터 사전
문제 9 (실기)
다음 (가)·(나)에 맞는 조인의 이름을 영문으로 쓰세요. 답을 끝까지 쓴 뒤 정답을 펼치세요.
- (가) 수강 표에 별칭 둘을 붙여 FROM 절에 두 번 적고, 과목코드는 같고 학번은 다른 짝을 이어 같은 과목을 들은 학생 짝을 뽑았습니다.
- (나) 강의실 표의 네 줄과 교시 표의 여섯 줄을 이을 조건 없이 모두 짝지어 24줄의 시간표 틀을 만들었습니다.
정답
받는 답: (가) SELF JOIN · (나) CROSS JOIN
(가)의 실마리는 '한 표를 두 번 적어 자기끼리 잇는다', (나)는 '이을 조건이 없고 모든 짝을 만든다'입니다.
- 같은 이름의 다른 꼴: (가) 셀프 조인 · (나) 교차 조인 — 한글 이름입니다. 문제가 영문으로 물었습니다.
- 틀리는 꼴 NATURAL JOIN — (가)에: 서로 다른 두 표에서 이름이 같은 열을 찾아 잇는 조인입니다. (가)는 한 표를 두 번 적었습니다.
- 틀리는 꼴 Cartesian Product — (나)에: 교차 조인이 만드는 결과의 이름입니다. 문제가 조인의 이름을 물었습니다.
- 틀리는 꼴 INNER JOIN — (나)에: 조인 조건이 맞는 짝만 남깁니다. (나)에는 이을 조건이 없습니다.
다시 볼 레슨: 조인
문제 10 (실기)
다음 (가)·(나)에 맞는 이름을 한글로 쓰세요. 답을 끝까지 쓴 뒤 정답을 펼치세요.
- (가) 조교에게는 학번·이름만 보이는 뷰를, 학과 사무실에는 학년과 학과명까지 보이는 뷰를 따로 만들어 주었습니다. 이렇게 쓰는 쪽마다 따로 적어 둔 데이터베이스의 일부를 스키마 3단계에서 무엇이라고 부르는지 쓰세요.
- (나) 그 뒤 학생 표에 입학년도 열을 더했는데도 두 뷰와 그것을 쓰는 프로그램은 한 줄도 고치지 않았습니다. 이렇게 지켜진 성질의 이름입니다.
정답
받는 답: (가) 외부 스키마 또는 서브 스키마 · (나) 논리적 데이터 독립성
(가)의 실마리는 '쓰는 쪽마다 보는 범위가 따로 있다', (나)는 '전체 짜임에 열이 늘어도 보는 쪽이 그대로다'입니다.
- 같은 이름의 다른 꼴: (가) External Schema · (나) Logical Data Independence — 영문 원어입니다. 문제가 한글로 물었습니다.
- 틀리는 꼴 개념 스키마 — (가)에: 조직 전체의 데이터를 한 벌로 모은 논리 구조로, 데이터베이스에 하나뿐입니다. (가)는 쓰는 쪽마다 따로 있습니다.
- 틀리는 꼴 뷰 — (가)에: 외부 스키마를 실제로 만들어 보이는 수단의 이름입니다. 묻는 것은 스키마 3단계의 층 이름입니다.
- 틀리는 꼴 물리적 데이터 독립성 — (나)에: 저장 방식이 바뀌어도 위 층이 그대로인 성질입니다. (나)에서 바뀐 것은 표의 열입니다.
다시 볼 레슨: 스키마 3단계와 데이터 독립성 · 뷰
문제 11 (실기)
다음 쿼리의 결과를 위에서부터 차례로 쓰세요. 답을 끝까지 쓴 뒤 정답을 펼치세요.
SELECT S.이름
FROM 학생 S
WHERE S.학번 IN (
SELECT E.학번
FROM 수강 E
WHERE E.과목코드 IN (
SELECT 과목코드
FROM 과목
WHERE 학점 = 3
)
GROUP BY E.학번
HAVING AVG(E.점수) >= 80
)
ORDER BY S.이름;정답
받는 답: 김민수, 최민준
안쪽부터 풉니다. 학점이 3인 과목은 DS·OS입니다. 두 과목만 놓고 학번별 평균을 내면 101은 80, 102는 65, 103은 65, 104는 75, 105는 100이고, 80 이상은 101·105입니다.
- 틀리는 꼴 김민수, 박서연, 최민준: 학점 조건을 건너뛰고 NW까지 평균을 냈습니다. 박서연(104)은 NW 95가 들어가야 85가 됩니다.
- 틀리는 꼴 최민준:
>=를>로 읽었습니다. 김민수의 평균은 정확히 80이라 남습니다. - 틀리는 꼴 박서연, 최민준: 두 오해가 겹쳤습니다.
| 이름 |
|---|
| 김민수 |
| 최민준 |
다시 볼 레슨: 서브쿼리 · 그룹과 집계: GROUP BY·HAVING