정보처리기사 실기 기출문제

  • 24 minutes to read

1. 부서 및 직원 테이블 정의 및 데이터 삽입

  • 문제: 다음과 같이 부서 및 직원 테이블을 정의하고 데이터를 삽입한 후, SQL 문을 실행한 결과를 쓰시오.

  • 코드:

CREATE TABLE 부서 (
    부서코드 INT PRIMARY KEY,
    부서명 VARCHAR(20)
);
CREATE TABLE 직원 (
    직원코드 INT PRIMARY KEY,
    부서코드 INT,
    직원명 VARCHAR(20),
    FOREIGN KEY(부서코드) REFERENCES 부서(부서코드)
        ON DELETE CASCADE
);
INSERT INTO 부서 VALUES(10, '영업부');
INSERT INTO 부서 VALUES(20, '기획부');
INSERT INTO 부서 VALUES(30, '개발부');
INSERT INTO 직원 VALUES(1001, 10, '이진수');
INSERT INTO 직원 VALUES(1002, 10, '곽연경');
INSERT INTO 직원 VALUES(1003, 20, '김선길');
INSERT INTO 직원 VALUES(1004, 20, '최민수');
INSERT INTO 직원 VALUES(1005, 20, '이용갑');
INSERT INTO 직원 VALUES(1006, 30, '박종일');
INSERT INTO 직원 VALUES(1007, 30, '박미경');

① SELECT DISTINCT COUNT(부서코드) FROM 직원 WHERE 부서코드 = 20;
② DELETE FROM 부서 WHERE 부서코드 = 20;
   SELECT DISTINCT COUNT(부서코드) FROM 직원;

  • 예시:

    • ① 문제의 SQL문 실행 결과는?
    • ② 문제의 SQL문 실행 후 남은 직원의 수는?

  • 해설:

    • ① SQL 문은 부서코드가 20인 직원의 수를 구하는 쿼리입니다. 부서코드 20에 해당하는 직원은 3명이므로 결과는 3입니다.
    • ② 부서 테이블에서 부서코드 20을 삭제한 후, 남은 직원의 수를 구하는 쿼리입니다. 삭제 시 CASCADE 옵션에 의해 관련 직원도 함께 삭제되므로, 결과는 4입니다.

  • 정답:

    • ① 3
    • ② 4

2. 학생 테이블 질의

  • 문제: 학생(STUDENT) 테이블에 전기과 학생이 50명, 전산과 학생이 100명, 전자과 학생이 50명 있다고 할 때, 다음 SQL 문 ①, ②, ③의 실행 결과로 표시되는 튜플의 수를 쓰시오. (단, DEPT 필드는 학과를 의미한다.)

  • 코드:

① SELECT DEPT FROM STUDENT;
② SELECT DISTINCT DEPT FROM STUDENT;
③ SELECT COUNT(DISTINCT DEPT) FROM STUDENT WHERE DEPT = '전산과';

  • 예시:

    • ① 문제의 SQL문 실행 결과는?
    • ② 문제의 SQL문 실행 결과는?
    • ③ 문제의 SQL문 실행 결과는?

  • 해설:

    • ① 전체 학생 수를 조회하므로 결과는 200명입니다.
    • ② 학과의 종류를 조회하므로 결과는 3입니다.
    • ③ '전산과'의 고유한 학과 수를 조회하므로 결과는 1입니다.

  • 정답:

    • ① 200
    • ② 3
    • ③ 1

3. 제품 정보 조회

  • 문제: 다음은 <제품>(제품명, 단가, 제조사) 테이블을 대상으로

"H" 제조사에서 생산한 제품들의 단가보다 높은 단가를 가진 제품의 정보를 조회하는 <SQL문>입니다. 괄호에 알맞은 답을 적어 <SQL문>을 완성하시오.

  • 코드:
SELECT 제품명, 단가, 제조사
FROM 제품
WHERE 단가 > (SELECT 단가 FROM 제품 WHERE 제조사 = 'H');

  • 예시:

    • 해당 SQL 문을 올바르게 완성하려면 괄호 안에 무엇이 와야 하나요?

  • 해설:

    • 문제의 SQL문은 "H" 제조사의 제품 단가보다 높은 단가를 가진 제품을 조회하기 위한 것입니다. 여기서는 모든 "H" 제조사의 제품 단가보다 높아야 하므로, ALL 키워드를 사용해야 합니다.

  • 정답:

    • ALL

4. 특정 조건에 맞는 튜플의 수 계산

  • 문제: 다음 <TABLE>을 참조하여 <SQL문>을 실행했을 때 출력되는 결과를 쓰시오. (<TABLE>에 표시된 NULL은 값이 없음을 의미한다.)

  • 코드:

INDEX COL1 COL2
1     2    NULL
2     4    6
3     3    5
4     6    3
5     NULL 3

<TABLE>

SELECT COUNT(COL2)
FROM TABLE
WHERE COL1 IN (2, 3)
      OR COL2 IN (3, 5);

  • 예시:

    • 위 <SQL문>을 실행했을 때, 어떤 결과가 출력되나요?

  • 해설:

    • 이 SQL문은 COL1 값이 2 또는 3인 행, 또는 COL2 값이 3 또는 5인 행의 COL2 값을 세는 것입니다. COL2 값이 NULL인 행은 카운트에서 제외됩니다. 따라서, 조건에 맞는 COL2 값이 있는 행은 총 3개입니다.

  • 정답:

    • 3

5. EMPLOYEE 릴레이션 관계 대수식 결과

  • 문제: 다음은 <EMPLOYEE> 릴레이션에 대해 <관계 대수식>을 수행했을 때 출력되는 <결과>입니다. <결과>의 각 괄호(①~⑤)에 들어갈 알맞은 답을 쓰시오.

  • 코드:

πTTL(EMPLOYEE)

INDEX AGE TTL
1     48  부장
2     25  대리
3     41  과장
4     36  차장

<EMPLOYEE>

  • 예시:

    • 위 관계 대수식의 결과, 괄호(①~⑤)에 들어갈 알맞은 답은 무엇인가요?

  • 해설:

    • 이 문제에서는 EMPLOYEE 릴레이션에서 TTL 속성만을 추출하여 새로운 릴레이션을 만드는 PROJECT 연산을 수행합니다. 따라서, TTL 속성명과 그 아래 모든 TTL 속성값들이 결과로 나타납니다.

  • 정답:

    • ① TTL
    • ② 부장
    • ③ 대리
    • ④ 과장
    • ⑤ 차장

6. SQL UPDATE문 설명

  • 문제: SQL과 관련한 다음 설명에서 괄호(①, ②)에 들어갈 알맞은 답을 쓰시오.

  • 코드:

UPDATE문은 테이블에 있는 튜플의 내용을 갱신할 때 사용하는 명령문으로, DML에 해당한다. 다른 DML로는 INSERT, DELETE가 있으며, 각각 새로운 튜플을 삽입하거나 삭제할 때 사용한다.

다음은 <학부생> 테이블에서 '입학생수'가 300 이상인 튜플의 '학과번호'를 999로 갱신하는 SQL문이다.

(①) 학부생 (②) 학과번호 = 999 WHERE 입학생수 >= 300;

  • 예시:

    • 위 설명에서 괄호(①, ②)에 들어갈 알맞은 답은 무엇인가요?

  • 해설:

    • 이 설명은 UPDATE 명령문의 사용법을 설명하는 것입니다. `

UPDATE` 명령문은 지정된 조건에 맞는 튜플의 내용을 변경하는 데 사용됩니다. 여기서는 '입학생수'가 300 이상인 튜플의 '학과번호'를 999로 변경하는 명령문입니다.

  • 정답:
    • ① UPDATE
    • ② SET

7. EMP_TBL 테이블에 대한 SQL문 실행 결과

  • 문제: <EMP_TBL> 테이블을 참고하여 <SQL문>의 실행 결과를 쓰시오.

  • 코드:

EMPNO SAL
100   1500
200   3000
300   2000

<EMP_TBL>

SELECT COUNT(*) FROM EMP_TBL WHERE EMPNO > 100 AND SAL >= 3000 OR EMPNO = 200;

  • 예시:

    • 위 <SQL문>을 실행했을 때, 어떤 결과가 출력되나요?

  • 해설:

    • SQL 문은 EMPNO가 100보다 크고 SAL이 3000 이상이거나, EMPNO가 200인 튜플의 개수를 계산합니다. SQL에서 AND 연산자는 OR 연산자보다 우선순위가 높기 때문에, 먼저 EMPNO > 100 AND SAL >= 3000의 조건을 만족하는 튜플을 찾고, 그 다음 EMPNO = 200의 조건을 만족하는 튜플을 찾습니다. 결과적으로, EMPNO가 200인 튜플 하나만이 최종 조건을 만족합니다.

  • 정답:

    • 1

8. 학생 테이블에서 특정 튜플 삭제

  • 문제: <학생> 테이블에서 '이름'이 "민수"인 튜플을 삭제하고자 한다. 다음 <처리 조건>을 참고하여 SQL문을 작성하시오.

  • 처리 조건:

    • 명령문 마지막의 세미콜론(;)은 생략이 가능하다.
    • 인용 부호가 필요한 경우 작은따옴표(' ')를 사용한다.

  • 코드:

DELETE FROM 학생 WHERE 이름 = '민수';

  • 예시:

    • '이름'이 "민수"인 학생을 삭제하기 위한 SQL 문을 작성하시오.

  • 해설:

    • 이 SQL문은 <학생> 테이블에서 '이름' 속성 값이 "민수"인 튜플을 찾아 삭제합니다. 처리 조건에 따라 문자열은 작은따옴표(' ')로 둘러싸여야 합니다.

  • 정답:

    • DELETE FROM 학생 WHERE 이름 = '민수';

9. 학생 테이블에 속성 추가

  • 문제: 다음 <속성 정의서>를 참고하여 <학생> 테이블에 대해 20자의 가변 길이를 가진 '주소' 속성을 추가하는 <SQL문>을 완성하시오. (단, SQL문은 ISO/IEC 9075 표준을 기반으로 작성하시오.)

  • 속성 정의서:

    • 학번 CHAR(10) UNIQUE 학생
    • 이름 VARCHAR(8) NOT NULL 학생
    • 주민번호 CHAR(13) 학생
    • 학과 VARCHAR(16) FOREIGN KEY 학생
    • 학년 INT 학생

  • 코드:

ALTER TABLE 학생 ADD 주소 VARCHAR(20);

  • 예시:

    • <학생> 테이블에 '주소' 속성을 추가하기 위한 SQL 문을 작성하시오.

  • 해설:

    • 이 SQL문은 기존의 <학생> 테이블에 새로운 속성인 '주소'를 추가합니다. '주소' 속성은 최대 20자의 가변 길이 문자열을 저장할 수 있도록 VARCHAR(20)으로 정의됩니다.

  • **정

답**:

  • ALTER TABLE 학생 ADD 주소 VARCHAR(20);

10. 학생 테이블 처리 조건에 따른 SQL문 작성

  • 문제: 다음 <학생> 테이블을 참고하여 <처리 조건>에서 요구하는 SQL문을 작성하시오.

  • 처리 조건:

    • 3, 4학년의 학번, 이름을 조회한다.
    • IN 예약어를 사용해야 한다.
    • 속성명 아래의 괄호는 속성의 자료형을 의미한다.

  • 코드:

SELECT 학번, 이름 FROM 학생 WHERE 학년 IN (3, 4);

  • 예시:

    • 3학년과 4학년 학생의 학번과 이름을 조회하기 위한 SQL 문을 작성하시오.

  • 해설:

    • 이 SQL문은 <학생> 테이블에서 '학년' 속성의 값이 3 또는 4인 튜플을 대상으로 '학번'과 '이름' 속성을 조회합니다. IN 연산자를 사용하여 여러 개의 가능한 값 중 하나와 일치하는 튜플을 선택합니다.

  • 정답:

    • SELECT 학번, 이름 FROM 학생 WHERE 학년 IN (3, 4);

11. student 테이블에 인덱스 생성

  • 문제: 다음 <student> 테이블을 참고하여 'name' 속성으로 'idx_name'이라는 인덱스를 생성하는 SQL문을 작성하시오.

  • 코드:

stid  name   score  deptid
2001  brown  85     PE01
2002  white  45     EF03
2003  black  67     UW11

<student>
  • SQL문:
CREATE INDEX idx_name ON student(name);

  • 해설:

    • 이 SQL 문은 <student> 테이블의 'name' 속성에 대한 인덱스 'idx_name'을 생성합니다. 인덱스는 데이터베이스에서 데이터 검색 속도를 향상시키는데 사용됩니다.

  • 정답:

    • CREATE INDEX idx_name ON student(name);

12. 성적 테이블에서 이름과 점수를 내림차순으로 조회

  • 문제: 다음은 <성적> 테이블에서 이름(name)과 점수(score)를 조회하되, 점수를 기준으로 내림차순 정렬하여 조회하는 <SQL문>입니다. 괄호(①~③)에 알맞은 답을 적어 <SQL문>을 완성하시오.

  • 코드:

name   class  score
정기찬  A      85
이영호  C      74
환정형  C      95
김지수  A      90
최은영  B      82

<성적>
  • SQL문:
SELECT name, score
FROM 성적
ORDER BY score DESC;

  • 해설:

    • 이 SQL문은 <성적> 테이블에서 'name'과 'score'를 선택하여 'score' 기준으로 내림차순 정렬한 결과를 조회합니다. 'DESC' 키워드는 내림차순 정렬을 의미합니다.

  • 정답:

    • ORDER BY score DESC;

13. 회원 테이블에서 이름이 "이"로 시작하는 회원 조회

  • 문제: 다음은 <회원> 테이블에서 '이름'이 "이"로 시작하는 회원들을 '가입일' 순으로 내림차순 정렬하는 <SQL문>입니다. 괄호(①, ②)에 들어갈 알맞은 답을 쓰시오.

  • 코드:

회원번호  이름   성별  가입일
1001    이진성  남    2021-06-23
1002    조이령  여    2021-06-24
1003    최민수  남    2021-06-28
1004    김차희  여    2021-07-03
1005    이미경  여    2021-07-10

<회원>
  • SQL문:
SELECT * FROM 회원 WHERE 이름 LIKE '이%' ORDER BY 가입일 DESC;

  • 해설:

    • 이 SQL문은 <회원> 테이블에서 '이름'이 "이"로 시작하는 모든 튜플을 선택하고, '가입일'을 기준으로 내림차순으로 정렬하여 결과를 조회합니다. 'LIKE' 연산자와 '%' 와일드카드를 사용하여 "이"로 시작하는 이름을 필터링합니다.

  • 정답:

    • LIKE '이%'
    • ORDER BY 가입일 DESC;

14. 학생 테이블에서 학과별 튜플의 개수 조회

  • 문제: 다음 질의 내용에 대한 SQL문을 완성하시오. 학생 테이블에서 학과별 튜

플의 개수를 검색하시오. (단, 아래의 실행 결과가 되도록 한다.)

  • 코드:
학번     이름   학년  학과  주소
20160011 김영란  2   전기  서울
19210113 이재우  3   컴퓨터 대구
21168007 함소진  1   전자  부산
19168002 우혜정  3   전자  광주
18120073 김진수  4   컴퓨터 울산

<학생>
  • SQL문:
SELECT 학과, COUNT(*) AS 학과별튜플수 FROM 학생 GROUP BY 학과;

  • 해설:

    • 이 SQL문은 <학생> 테이블에서 '학과'별로 그룹화하고, 각 '학과'의 튜플 수를 계산하여 '학과별튜플수'라는 별칭으로 결과를 표시합니다. 'GROUP BY' 구문을 사용하여 '학과' 속성에 따라 튜플을 그룹화합니다.

  • 정답:

    • SELECT 학과, COUNT(*) AS 학과별튜플수 FROM 학생 GROUP BY 학과;

    15. 과목별 점수 평균이 90점 이상인 과목 조회

  • 문제: 다음의 <성적> 테이블에서 과목별 점수의 평균이 90점 이상인 ‘과목이름’, ‘최소점수’, ‘최대점수’를 검색하고자 한다. <처리 조건>을 참고하여 적합한 SQL문을 작성하시오.

  • 처리 조건:

    • WHERE문은 사용하지 않는다.
    • GROUP BY와 HAVING을 이용한다.
    • 집계함수(Aggregation Function)를 사용하여 명령문을 구성한다.
    • ‘최소점수’, ‘최대점수’는 별칭(Alias)을 위한 AS문을 이용한다.
    • 명령문 마지막의 세미콜론(;)은 생략이 가능하다.
    • 인용 부호가 필요한 경우 작은따옴표(‘ ’)를 사용한다.

  • 코드:

학번    과목번호  과목이름       학점  점수
a2001  101     컴퓨터구조    6    95
a2002  101     컴퓨터구조    6    84
a2003  302     데이터베이스  5    89
a2004  201     인공지능      5    92
a2005  302     데이터베이스  5    100
a2006  302     데이터베이스  5    88
a2007  201     인공지능      5    93

<성적>
  • SQL문:
SELECT 과목이름, MIN(점수) AS 최소점수, MAX(점수) AS 최대점수 
FROM 성적 
GROUP BY 과목이름 
HAVING AVG(점수) >= 90;

  • 해설:

    • 이 SQL문은 각 과목별로 최소 점수와 최대 점수를 계산하고, 과목의 평균 점수가 90점 이상인 경우에 한하여 결과를 표시합니다. HAVING 절은 GROUP BY로 생성된 그룹에 조건을 적용할 때 사용됩니다.

  • 정답:

    • SELECT 과목이름, MIN(점수) AS 최소점수, MAX(점수) AS 최대점수 FROM 성적 GROUP BY 과목이름 HAVING AVG(점수) >= 90;

16. A 테이블과 B 테이블의 교차 조인 결과

  • 문제: <A> 테이블과 <B> 테이블을 참고하여 <SQL문>의 실행 결과를 쓰시오.

  • 코드:

NAME
Smith
Allen
Scott

<A>

RULE
S%
%T%

<B>
  • SQL문:
SELECT COUNT(*) CNT 
FROM A 
CROSS JOIN B 
WHERE A.NAME LIKE B.RULE;

  • 해설:

    • 이 SQL문은 <A> 테이블과 <B> 테이블을 교차 조인하여 생성된 모든 조합에서, <A> 테이블의 NAME이 <B> 테이블의 RULE에 정의된 패턴과 일치하는 행의 수를 계산합니다. LIKE 연산자와 함께 사용되는 %는 0개 이상의 임의의 문자열과 일치함을 의미합니다.

  • 정답:

    • 4

17. 사원 테이블과 동아리 테이블 조인 결과

  • 문제: 다음 <사원> 테이블과 <동아리> 테이블을 조인한 <결과>를

확인하여 <SQL문>의 괄호(①, ②)에 들어갈 알맞은 답을 쓰시오.

  • 코드:
코드   이름   부서
1601 김명해 인사
1602 이진성 경영지원
1731 박영광 개발
2001 이수진

<사원>

코드   동아리명
1601 테니스
1731 탁구
2001 볼링

<동아리>
  • SQL문:
SELECT a.코드, 이름, 동아리명 
FROM 사원 a 
LEFT JOIN 동아리 b ON a.코드 = b.코드;

  • 해설:

    • 이 SQL문은 <사원> 테이블과 <동아리> 테이블을 왼쪽 외부 조인(LEFT JOIN)합니다. 이는 모든 <사원> 테이블의 행과, <동아리> 테이블에서 일치하는 행을 결합한 결과를 생성합니다. 조인 조건은 두 테이블의 코드가 같은 경우입니다.

  • 정답:

    • ON a.코드 = b.코드

18. 재고 및 판매 테이블 정의 및 데이터 삽입

  • 문제: 다음과 같이 재고(STOCK) 및 판매(SALES) 테이블을 정의하고 데이터를 삽입한 후, SQL 문을 실행한 결과를 쓰시오.

  • 코드:

CREATE TABLE STOCK (
    제품코드 INT PRIMARY KEY,
    제품명 VARCHAR(50),
    재고수량 INT
);

CREATE TABLE SALES (
    판매번호 INT PRIMARY KEY,
    제품코드 INT,
    판매수량 INT,
    FOREIGN KEY(제품코드) REFERENCES STOCK(제품코드)
);

INSERT INTO STOCK VALUES(1, '노트북', 100);
INSERT INTO STOCK VALUES(2, '키보드', 200);
INSERT INTO STOCK VALUES(3, '마우스', 300);

INSERT INTO SALES VALUES(101, 1, 10);
INSERT INTO SALES VALUES(102, 2, 20);
INSERT INTO SALES VALUES(103, 3, 30);

① SELECT SUM(재고수량) FROM STOCK;
② SELECT 제품명 FROM STOCK WHERE 제품코드 NOT IN (SELECT 제품코드 FROM SALES);

  • 예시:

    • ① 문제의 SQL문 실행 결과는?
    • ② 문제의 SQL문 실행 결과는?

  • 해설:

    • ① SQL 문은 모든 제품의 재고수량 총합을 조회합니다. 제품의 재고수량이 각각 100, 200, 300으로 총합은 600입니다.
    • ② 이 SQL 문은 판매된 적 없는 제품의 이름을 조회합니다. 모든 제품이 한 번씩 판매되었으므로, 결과는 없습니다.

  • 정답:

    • ① 600
    • ② (결과 없음)

19. 재고 상태 갱신

  • 문제: 판매 정보에 따라 재고 상태를 갱신하는 SQL문을 작성하시오.

  • 처리 조건:

    • 판매된 제품의 재고수량을 판매수량만큼 감소시킨다.
    • 제품코드 1인 제품이 10개 판매되었다.

  • 코드:

UPDATE STOCK SET 재고수량 = 재고수량 - (SELECT 판매수량 FROM SALES WHERE 제품코드 = 1) WHERE 제품코드 = 1;

  • 해설:

    • 이 SQL문은 제품코드가 1인 제품의 재고수량을, 해당 제품의 판매수량만큼 감소시킵니다. 제품코드 1의 제품이 10개 판매되었으므로, 재고수량은 판매수량 10만큼 감소됩니다.

  • 정답:

    • UPDATE STOCK SET 재고수량 = 재고수량 - (SELECT 판매수량 FROM SALES WHERE 제품코드 = 1) WHERE 제품코드 = 1;

20. 재고 부족 제품 조회

  • 문제: 재고수량이 특정 값 이하인 제품의 이름과 재고수량을 조회하는 SQL문을 작성하시오. (특정 값은 50이하)

  • 코드:

SELECT 제품명, 재고수량 FROM STOCK WHERE 재고수량 <= 50;
  • 해설:
    • 이 SQL문은 STOCK 테이블에서 재고수량이 50 이하인 모든 제품

의 '제품명'과 '재고수량'을 조회합니다. 재고수량이 50 이하인 제품이 없으므로, 추가된 데이터에 따라 결과가 달라질 수 있습니다.

  • 정답:
    • SELECT 제품명, 재고수량 FROM STOCK WHERE 재고수량 <= 50;
VisualAcademy Docs의 모든 콘텐츠, 이미지, 동영상의 저작권은 박용준에게 있습니다. 저작권법에 의해 보호를 받는 저작물이므로 무단 전재와 복제를 금합니다. 사이트의 콘텐츠를 복제하여 블로그, 웹사이트 등에 게시할 수 없습니다. 단, 링크와 SNS 공유, Youtube 동영상 공유는 허용합니다. www.VisualAcademy.com
박용준 강사의 모든 동영상 강의는 데브렉에서 독점으로 제공됩니다. www.devlec.com