비전공자 정보처리기사 공부법(3)-SQL 문제
안녕하세요.
오늘은 2017년-2020년 정보처리기사 및 정보처리기사 기출문제 중에서 SQL 부분만 문제를 모아 공부했던 내용을 공유해볼까 해요.
문제와 답이 구분이 가는 경우에는 그냥 썼고, 구분이 안 갈 것 같은 것은 구분선을 사용했어요.
SQL은 개정 전이나 후에 나오는 문제 경향이 정해져 있어서 이 부분은 전부 다 맞는다는 생각으로 했었어요.
학생 테이블에 학번이 98170823, 성명이 '한국산', 학년이 3, 과목이 '경영학개론', 연락처가 '?-123-1234'인 학생의 정보를 입력하시오.
INSERT INTO 학생 VALUES(98170823, '한국산', 3, '경영학개론', '?=1234-1234');
학생 테이블을 제거하고, 학생 테이블을 참조하는 모든 데이터도 함께 제거하는 SQL을 쓰시오.
DROP TABLE 학생 CASCADE;
사원 테이블에서 직급이 ‘차장’인 사원의 연봉을 100000원 인상하시오.
UPDATE 사원 SET 연봉 = 연봉 + 100000 WHERE 직급 = ‘차장’;
(1) CUSTOMER 테이블에서 ID, NAME을 검색하는 SQL문을 작성하시오.
SELECT ID, NAME FROM CUSTOMER;
(2) CUSTOMER 테이블에서 GRADE를 중복 없이 검색하는 SQL문을 작성하시오.
SELECT DISTINCT GRADE FROM CUSTOMER;
(3) CUSTOMER 테이블에서 모든 데이터를 ID를 기준으로 내림차순 정렬하여 검색하는 SQL문을 작성하시오.
SELECT * FROM CUSTOMER ORDER BY ID DESC;
(4) CUSTOMER 테이블에서 AGE가 입력되지 않은(NULL인) NAME을 검색하는 SQL문을 작성하시오.
SELECT NAME FROM CUSTOMER WHERE AGE IS NULL;
(5) CUSTOMER 테이블에서 AGE가 입력된 (NULL이 아닌) NAME을 검색하는 SQL문을 작성하시오.
SELECT NAME FROM CUSTOMER WHERE AGE IS NOT NULL;
원소 함수 IN을 사용하여 학생 테이블에서 3학년과 4학년 학생의 학번과 이름을 검색하는 SQL문을 작성하시오.
SELECT 학번, 이름 FROM 학생 WHERE 학년 IN (3, 4);
(1) <학생> 테이블에서 전공이 computer이고 1학년인 학생의 이름과 생년월일을 검색하는 SQL문을 작성하시오.
SELECT 학생이름, 생년월일 FROM 학생 WHERE 전공 = ‘computer’ AND 학년=1;
(2) <학생> 테이블에서 2학년 또는 4학년인 학생의 전공을 검색하되, 전공이 같은 경우 한 번만 검색하는 SQL문을 작성하시오.
SELECTt DISTINCT 전공 FROM 학생 WHERE 학년 = 2 OR 학년 = 4;
또는 SELECT DISTINCT 전공 FROM 학생 WHERE 학년 IN(2, 4);
(3) <학생> 테이블에서 2학년 이상인 학생의 이름을 검색하되, 학년을 기준으로 내림차순 검색하는 SQL문을 작성하시오.
SELECT 학생이름 FROM 학생 WHERE 학년 >= 2 ORDER BY 학년 DESC;
<학생> 테이블에 최대 3 문자로 구성되는 학년 속성을 추가하는 SQL문을 작성하시오.
ALTER TABLE 학생 ADD 학년 VARCHAR(3);
CREATE TABLE 사원
(직원코드 NUMBER NOT NULL,
성명 CHAR(10) UNIQUE,
직책 CHAR(10) ( ① )(직책 ( ② )(‘사원’, ‘대리’, ‘과장’, ‘팀장’)),
연봉 NUMBER);
① CHECK ② IN
<학생> 테이블에서 이름이 ‘이’로 시작하는 학생들의 학번을 검색하되, 학년을 기준으로 내림차순으로 출력하는 SQL문을 쓰시오.
SELECT 학번 FROM 학생 WHERE 이름 LIKE ‘이%’ ORDER BY 학년 DESC;
(1) <직원> 테이블에서 이름을 조회하는 SQL문을 완성하시오.
SELECT 이름 FROM 직원;
(2) <직원> 테이블에서 부서가 ‘인사’인 튜플을 검색하는 SQL문을 완성하시오.
SELECT * FROM 직원 WHERE 부서 = ‘인사’;
(3) <동호회> 테이블에서 분야가 ‘등산’이고, 활동 점수가 3000 이상인 튜플을 검색하는 SQL문을 완성하시오.
SELECT * FROM 동호회
WHERE 분야 = ‘등산’ AND 활동점수 >= 3000;
(4) <동호회> 테이블에서 분야만 검색하되, 같은 분야는 한 번만 검색하는 SQL문을 완성하시오.
SELECT DISTINCT 분야 FROM 동호회;
(5) <동호회> 테이블에서 이름과 분야를 검색하되, 활동 점수를 기준으로 오름차순 정렬하는 SQL문을 완성하시오.
SELECT 이름, 분야 FROM 동호회
ORDER BY 활동점수;
GILDONG에게 STUDENT 테이블에 대한 모든 권한과 다른 사람에게도 권한을 부여할 수 있는 권한까지 부여한다.
(GRANT) ALL ON STUDENT TO GILDONG WITH GRANT OPTION;
GILDONG에게 부여된 STUDENT 테이블에 대한 권한은 유지하고, 다른 사용자에게 권한을 부여할 수 있는 권한만 취소한다.
(REVOKE) GRANT OPTION FOR ALL ON STUDENT FROM GILDONG;
아래는 정규화 과정을 거친 <학부생> 테이블에서 담당관의 이름이 “이”로 시작하는 튜플의 학과번호를 999로 갱신하는 SQL문이다.
UPDATE 학부생 ( SET ) ( 학과번호 ) = 999 WHERE 담당관 ( LIKE ) ‘이%’;
학생 테이블에 학번이 193739, 성명이 ‘홍길동’, 학년이 3, 과목이 ‘경영학개론’, 연락처가 ‘010-1234-1234’인 학생의 정보를 입력하시오.
INSERT INTO 학생 VALUES(193739, ‘홍길동’, 3, ‘경영학개론’, ‘010-1234-1234’);
(1) <학생> 테이블에서 학번이 “19”로 시작하고 나이가 21인 튜플을 검색하는 SQL문을 완성하시오.
SELECT * FROM 학생 WHERE 학번 LIKE ‘19%’ AND 나이 = 21;
(2) <성적> 테이블에서 학번과 평균을 검색하되, 평균을 기준으로 오름차순 정렬하는 SQL문을 완성하시오.
SELECT 학번, 평균 FROM 성적 ORDER BY 평균 ASC;
(3) <학생> 테이블의 학생 중에서 성적 자료가 없는 튜플을 검색하는 SQL문을 완성하시오.
SELECT * FROM 학생 WHERE 학번 NOT IN (SELECT 학번 FROM 성적);
학생(STUDENT) 테이블에 전기과 학생이 50명, 전산과 학생이 100명, 전자과 학생이 50명 있다고 할 때, 다음 SQL문 ①, ②, ③의 실행 결과로 표시되는 튜플의 수를 쓰시오. (단, DEPT 필드는 학과를 의미한다)
① SELECT DEPT FROM STUDENT;
200
② SELECT DISTINCT DEPT FROM STUDENT;
3
③ SELECT COUNT(DISTINCT DEPT) FROM STUDENT WHERE DEPT = ‘전산과’;
1
다음은 <학생> 테이블로부터 2학년 학생들의 성명, 사진, 학년을 가져와 <출석부> 뷰를 생성하는 SQL문이다. 괄호에 적합한 예약어를 넣어 SQL문을 완성하시오.
CREATE VIEW 출석부
(AS SELECT) 성명, 사진, 학년
FROM 학생
WHERE 학년 = 2;
3, 4학년의 학번, 이름을 조회하는 SQL을 IN 예약어를 사용하여 작성하시오.
SELECT 학번, 이름 FROM 학생 WHERE 학년 IN (3, 4);
사원 테이블에서 직급이 ‘차장’인 사원의 연봉을 100000원 인상하시오.
UPDATE 사원 ( SET ) 연봉 = 연봉 + 100000 ( WHERE ) 직급 = ‘차장’;
다음 릴레이션에 대한 (1), (2)의 물음에 답하시오.
릴레이션 <회원>
회원번호 이름 나이 주소 등급
G-001 시나공 23 서울시 마포구 일반
G-002 김길벗 22 서울시 구로구 실버
G-003 박토익 25 서울시 종로구 골드
(1) 회원 릴레이션의 차수는 얼마인가? 5
(2) 회원 릴레이션의 카디널리티는 얼마인가? 3
<사원> 릴레이션의 카디널리티(CardINality) 개수를 구하시오. 6
<사원>
사번 성명 부서 직급 수당
11 김예소 기획부 대리 30
32 고강민 인사부 과장 40
41 이향기 재무부 부장 50
다음은 SELECT문의 실행 작동 순서를 나열한 것이다. 괄호(①~③)에 알맞은 명령을 보기에서 찾아 쓰시오.
FROM → ( HAVING ) → ( GROUP BY ) → ( WHERE ) → SELECT → ORDER BY
<도서>, <도서가격> 테이블을 참고하여 다음 SQL문의 실행 결과를 쓰시오. 18000
SELECT 가격 FROM 도서가격 WHERE 책번호 = (SELECT 책번호 FROM 도서 WHERE 책명 = ‘데이터베이스’);
<도서> <도서가격>
책번호 책명 책번호 가격
1111 데이터베이스 1111 18000
2222 운영체제 2222 20000
3333 자료구조 3333 10000
4444 15000
<HRD> 테이블의 ‘주민번호’ 속성에 “900351”을 입력할 때 오류가 발생하여 <HRD> 테이블의 ‘주민번호’ 속성의 크기를 6으로 변경하는 SQL문을 작성하였다. 괄호에 알맞은 명령을 쓰시오. ALTER
<테이블 생성 SQL문>
CREATE TABLE HRD
주민번호 NUMBER(5);
<데이터 입력 SQL문> - 오류 발생
INSERT INTO HRD (주민번호) VALUES (‘900351’);
<테이블 속성 변경 SQL문>
ALTER TABLE HRD ( ) 주민번호 NUMBER(6);
다음은 <topic> 테이블과 <auth> 테이블을 결합하여 검색하는 <SQL>문이다. 괄호에 알맞은 명령을 쓰시오.
SELECT * FROM topic LEFT ( OUTER JOIN ) auth ON topic.auth_id = auth.id;
<학생> 테이블에서 ‘이름’이 “민수”인 튜플을 삭제하고자 한다. 다음 <처리조건>을 참고하여 SQL문을 작성하시오.
DELETE FROM 학생 WHERE 이름 = ‘민수’;
<성적> 테이블에서 과목별 점수의 평균이 90점 이상인 ‘과목이름’, ‘최소점수’, ‘최대점수’를 검색하고자 한다. <처리조건>을 참고하여 적합한 SQL문을 작성하시오.
SELECT 과목이름, MIN(점수) AS 최소점수, MAX(점수) AS 최대점수 FROM 성적 GROUP BY 과목이름 HAVING AVG(점수) >= 90;
<처리조건>
- WHERE문은 사용하지 않는다.
- GROUP BY와 HAVING을 이용한다.
- 집계함수(Aggregation Function)를 사용하여 명령문을 구성한다.
- ‘최소점수’, ‘최대점수’는 별칭(Alias)을 위한 AS문을 이용한다.
다음 <속성 정의서>를 참고하여 <학생> 테이블에 대해 20자의 가변 길이를 가진 ‘주소’ 속성을 추가하는 <SQL문>을 완성하시오.
( ALTER ) TABLE 학생 ( ADD ) 주소 VARCHAR(20);
CREATE TABLE 사원
(직원코드 NUMBER NOT NULL,
성명 CHAR(10) UNIQUE,
직책 CHAR(10) ( CHECK )(직책 ( IN )(‘사원’, ‘대리’, ‘과장’, ‘팀장’)),
연봉 NUMBER);
다음 <회원>, <테이프>, <대여> 테이블을 참고하여 <SQL>의 실행 결과를 쓰시오.
SELECT 회원.성명, 회원.전화번호
FROM 회원, 대여
WHERE 회원.회원번호 = 대여.회원번호 AND 대여.테이프번호 = 'T3';
이동국 111-1111
이동국 333-3333
박찬성 444-4444
DBA는 <회원> 테이블을 생성한 이후 테이블에 ‘주소’ 속성이 누락된 것을 발견하고, 이를 해결하기 위해 다음의 SQL문을 작성하였다.
ALTER TABLE 회원 ( ADD ) 주소 CHAR(30);
SELECT no, s_name, depart, score
FROM test00.student, test00.score
( WHERE ) test00.student.no = test00.score.no;
<Student> 테이블에서 ‘S_NO’가 ‘201900B1’인 학생의 레코드를 삭제하기 위한 SQL문에 알맞은 명령을 쓰시오.
DELETE * FROM Student ( WHERE ) S_NO = '201900B1';
다음 <처리 조건>을 준수하여 색인을 생성하는 SQL문에 알맞은 명령을 쓰시오.
<처리 조건>
ㆍ기본 테이블 T의 열(P, Q, R)에 관한 조합으로 X 색인을 생성한다.
ㆍ색인 내용은 P(오름차순), Q(내림차순), R(오름차순)이다.
ㆍSQL 작성시 UNIQUE와 CLUSTER는 생략 가능
SQL> CREATE ( INDEX ) X ON T(P, Q DESC, R);
다음은 뷰(View)를 생성하는 SQL문이다. <보기> 중 이를 옳게 설명한 것을 골라 기호(가~라)로 쓰시오. 가
CREATE VIEW vi_salary AS SELECT * FROM EMPLOYEE WHERE POSITION LIKE "%부장%";
<보기>
가. EMPLOYEE 테이블에서 POSITION이 “부장”을 포함하는 레코드를 대상으로 vi_salary 뷰를 생성한다.
나. EMPLOYEE 테이블에서 POSITION이 “감찰부 부부장”인 레코드는 vi_salary 뷰에 포함되지 않는다.
다. vi_salary 뷰에서 POSITION 외의 다른 속성은 조회할 수 없다.
라. vi_salary 뷰와 EMPLOYEE 테이블의 카디널리티(Cardinality)와 차수(Degree)는 항상 같다.
다음의 두 릴레이션(Relation)에서 외래키(foreign)를 찾아 쓰시오. (밑줄은 기본키를 의미함) 제품코드
제품(제품코드, 제품명, 단가, 구매처)
판매(판매코드, 판매처, 제품코드, 수량)
<학생> 테이블에서 학년이 2학년 이상인 레코드만 검색하되 동일한 학과코드는 한 번만 검색되도록 하는 SQL문을 완성하시오.
SQL> SELECT ( DISTINCT ) 학과코드 FROM 학생 WHERE 학년 >= 2;
학생 테이블에서 학과별 튜플의 개수를 검색하시오.
SELECT 학과, COUNT(*) AS 학과별튜플수 FROM 학생 GROUP BY 학과;
(단, 아래의 실행 결과가 되도록 한다.)
- WHERE 조건절은 사용할 수 없다.
- GROUP BY는 반드시 포함한다.
- 집계함수(Aggregation Function)를 적용한다.
- 학과별튜플수 컬럼이름 출력에 Alias(AS)를 활용한다.
- 문장 끝의 세미콜론(;)은 생략해도 무방하다.
- 인용부호 사용이 필요한 경우 단일 따옴표(‘ ’ : SINgle Quotation)를 사용한다
(1) <student> 테이블에서 전공이 computer이고 1학년인 학생의 이름과 생년월일을 검색하는 SQL문을 작성하시오.
SELECT 학생이름, 생년월일 FROM student WHERE 전공 = ‘computer’ AND 학년 = 1;
(2) <student> 테이블에서 2학년 또는 4학년인 학생의 전공을 검색하되, 전공이 같은 경우 한 번만 검색하는 SQL문을 작성하시오. (단, IN을 이용해야 한다.)
SELECT DISTINCT 전공 FROM student WHERE 학년 IN(2, 4);
(3) <student> 테이블에서 2학년 이상인 학생의 이름을 검색하되, 학년을 기준으로 내림차순 검색하는 SQL문을 작성하시오.
SELECT 학생이름 FROM student WHERE 학년 >= 2 ORDER BY 학년 DESC;
<student> 테이블을 참고하여 ‘name’ 속성으로 ‘idx_name’이라는 인덱스를 생성하는 SQL문을 작성하시오.
CREATE INDEX idx_name ON student(name);
(1) <직원> 테이블에서 이름을 조회하는 SQL문을 완성하시오.
SELECT 이름 FROM 직원;
(2) <직원> 테이블에서 부서가 ‘인사’인 튜플을 검색하는 SQL문을 완성하시오.
SELECT * FROM 직원 WHERE 부서 = ‘인사’;
(3) <동호회> 테이블에서 분야가 ‘등산’이고, 활동점수가 3000 이상인 튜플을 검색하는 SQL문을 완성하시오.
SELECT * FROM 동호회
WHERE 분야 = ‘등산’ AND 활동점수 >= 3000;
(4) <동호회> 테이블에서 분야만 검색하되, 같은 분야는 한 번만 검색하는 SQL문을 완성하시오.
SELECT DISTINCT 분야 FROM 동호회;
(5) <동호회> 테이블에서 이름과 분야를 검색하되, 활동점수를 기준으로 오름차순 정렬하는 SQL문을 완성하시오.
SELECT 이름, 분야 FROM 동호회 ORDER BY 활동점수;