[정보처리기사/실기] SQL문 연습하기!

한 문제 이상 꼭 출제되는 SQL문 연습하기

SQL 정의어 (DDL)

CREATE, ALTER, DROP, FOREIGN KEY~REFERENCES, CASCADE/RESTRICT, CHECK

CREATE

학생

학번	성명	학과	학년	학점

※테이블 작성 지시사항
- 학번, 성명,학과,학년,학점으로 구성된 학생 테이블을 만들어라.
- 학번과 학년은 숫자형 자료이며, 나머지는 문자형이다.
- 학번을 기본키로 ~
- 성명속성은 공백이 없다.
- 학과 항목을 이용하여 [수강]테이블의 학과를 참조하도록 외래키를 지정하며, 참조 테이블에서 삭제가 발생하면 NULL값으로 하고, 수정이 발생하면 연쇄적으로 수정하도록 한다.
- 학년의 속성값은 4이하의 값을 갖도록 'hak' 이름으로 제약한다.

CREATE TABLE 학생 (학번 INT, 성명 CHAR(5) NOT NULL, 학과 CHAR(10), 학년 INT, 학점 CHAR (1), PRIMARY KEY(학번), FOREIGN KEY(학과) REFERENCES 수강(학과) ON DELETE SET NULL, ON UPDATE CASCADE, CONSTRAINT hak CHECK(학년 <= 4));

ALTAR

학생

학번	성명	학과	학년	학점
070203	이영진	전기통신	3	A
081517	홍길동	산업공학	2	B

※테이블 작성 지시사항
[학생] 테이블에 '주소' 속성을 추가하시오, 주소 항목은 가변길이 문자형으로 하고 30자 까지 입력될 수 있다.

ALTAR TABLE 학생 ADD 주소 VARCHAR(30);

※테이블 작성 지시사항
[학생] 테이블에서 '학년' 속성을 제거하시오.  

ALTAR TABLE 학생 DROP 학년 CASCADE;

DROP

학적

학번	성명	학과	학년	학점
070203	이영진	전기통신	3	A
081517	홍길동	산업공학	2	B

※테이블 작성 지시사항
- [학적] 테이블을 삭제하시오.

DROP TABLE 학적 CASCADE;

CASCADE : 삭제할 요소가 사용(참조)중이어도 삭제가 이루어지며,
삭제할 테이블을 참조 중인 다른 테이블도 연쇄적으로 같이 삭제된다.
RESTRICT : 삭제할 요소가 사용중이면 삭제가 이루어지지 않는다.

SQL 조작어 (DML)

SELECT, INSERT, UPDATE, DELETE

SELECT

학생

학번	성명	학년	수강과목	점수	연락처
070203	이영진	1	정보통신	90	234-4567
081517	홍길동	2	컴퓨터	80	322-2422
890808	박태인	3	데이터베이스	94	643-2642
442424	김길동	4	운영체제	57	456-3242
242452	김철수	2	산업공학	85	333-4342

※테이블 작성 지시사항

1)단순질의문

[학생]테이블에서 모든 학생의 성명을 검색하시오.

SELECT 성명 FROM 학생;

[학생]테이블에서 데이터베이스를 수강하는 학생의 학번과 성명을 검색하시오.

SELECT 학번,성명 FROM 학생 WHERE 수강과목 = '데이터베이스';

[학생] 테이블에서 3학년 학생의 모든 속성을 검색하시오.

SELECT*FROM 학생 WHERE 학년=3;

[학생] 테이블에서 학년이 3학년이고, 수강과목이 '산업공학'인 학생의 성명과 연락처를 검색하시오.

SELECT 성명, 연락처 FROM 학생 WHERE 학년=3 AND 수강과목 ='산업공학';

[학생] 테이블에서 학년이 '1학년'이거나 수강과목이 '운영체제'인 학생의 성명을 검색하시오.

SELECT 성명 FROM 학생 WHERE 학년='1학년' OR 수강과목='운영체제';

[학생] 테이블에서 학생의 점수가 85점 이상 90점 이하인 학생의 연락처를 검색하시오.

SELECT 연락처 FROM 학생 WHERE 점수>=85 AND 점수<=90; (WHERE 점수 BETWEEN 85 AND 90);

2) DISTINCT 옵션을 이용하여 중복된 값을 제거한 검색의 경우

[학생] 테이블에서 2학년 이상인 학생의 수강과목을 검색하되, 같은 수강과목 값은 한번만 검색되도록 하시오. 

SELECT DISTINCT 수강과목 FROM 학생 WHERE 학년>=2;

3) 집계함수를 이용한 검색문 

   - SUM(속성이름) : 합계를 구함
   - AVG(속성이름) : 평균을 구함
   - MAX(속성이름) : 최대값을 구함 
   - MIN(속성이름) : 지정된 속성의 값 중 최솟값을 구함
   - COUNT(속성이름) : 튜플수를 세어줌

[학생] 테이블에서 1학년 학생의 점수합계를 구하시오. 

SELECT SUM(점수) FROM 학생 WHERE 학년=1;

[학생] 테이블에서 3학년 이상 학생의 수를 '학생수' 라는 속성 이름으로 구하시오.

SELECT COUNT(*) AS 학생수 FROM 학생 WHERE 학년 >=3;

4) 원소 함수 IN을 이용한 검색문

[학생] 테이블에서 3학년과 4학년 학생의 학번과 성명을 검색하시오

SELECT 학번, 성명 FROM 학생 WHERE 학년 IN(3,4)

5) 검색된 결과를 정렬해서 표현하고자 하는 경우

[학생] 테이블에서 점수가 85점 이상인 학생을 학번의 오름차순으로 성명을 검색하시오.

SELECT 성명 FROM 학생 WHERE 점수 >= 85 ORDER BY 학번 ASC;

ASC 오름차순
DESC 내림차순

6) 그룹 분류 질의문

[학생] 테이블에서 2명 이상인 학년을 검색하시오.

SELECT 학년 FROM 학생 GROUP BY 학년 HAVING COUNT(*)>=2;

:학년은 순서대로 되어있지 않아 수를 세기가 효율적이지 못하다. 이런경우 학년별로 그룹을 분류하면 
보다 효율적으로 처리할 수 있다.

☆☆[학생] 테이블에서 점수가 85점 이상인 학생이 2명 이상인 학년을 검색하시오.

SELECT 학년 FROM 학생 WHERE 점수 >=85 GROUP BY 학년 HAVING COUNT(*)>=2;

:점수가 85점 이상인 학생을 선별하고, 그 뒤 학년별로 그룹으로 분류해서 2명 이상인 학년을 검색하면 된다.

7) 부속(하위) 질의문

학생정보

학생정보	이름	학과	학년	연락처
990111	김강찬	컴퓨터	1	0909034
980123	이철수	기계	4	3424242
987979	이영진	법학	3	3424211

학과인원

학과	학생수
컴퓨터	35
기계	33
법학	34

[학생정보] 테이블과 [학과인원] 테이블을 이용하여 '이영진'학생이 속한 학과의 학생수를 검색하시오.

SELECT 학생수 FROM 학과인원 WHERE 학과= (SELECT 학과 FROM 학생정보 WHERE 이름='이영진');

[학생정보] 테이블과 [학과인원] 테이블을 이용하여 학과 학생수가 30명 이하인 학과 학생의 이름을 검색하시오.

SELECT 이름 FROM 학생정보 WHERE 학과 IN (SELECT 학과 FROM 학과인원 WHERE 학생수<=30);

8) 부분 매치 질의문

[학생] 테이블에서 연락처의 번호가 '7588'로 끝나는 학생의 성명을 검색하시오. 

SELECT 성명 FROM 학생 WHERE 연락처 LIKE '%7588';

9) 'NULL'값과 비교하는 질의문

[학생] 테이블에서 연락처가 NULL 인 학생의 학번을 검색하시오.

SELECT 학번 FROM 학생 WHERE 연락처 IS NULL;

10) JOIN 질의문

[학생정보] 테이블과 [학과인원] 테이블에서 학과명이 같은 튜플을 JOIN하여 이름,학과,학생수를 검색하시오.

SELECT 이름,학과,학생수 FROM 학생정보 JOIN 학과인원 ON(학생정보.학과=학과인원.학과);

공단예시문제

INSERT

※테이블 작성 지시사항
[학생] 테이블에서 학번 95115, 성명 '김정미', 학년 4, 수강과목 '데이터베이스', 연락처 '243-0707'

INSERT INTO 학생(학번,성명,학년,수강과목,연락처) VALUES (95115,'김정미',4,'데이터베이스','243-0707');

UPDATE

※테이블 작성 지시사항
[학생] 테이블에서 '이영진' 학생의 점수를 92점으로 수정하시오

UPDATE 학생 SET 점수=92 WHERE 성명='이영진';

DELETE

※테이블 작성 지시사항
[학생] 테이블에서 2학년 학생의 자료를 삭제하시오.

DELETE FROM 학생 WHERE 학년=2;