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(학생정보.학과=학과인원.학과);
공단예시문제
※테이블 작성 지시사항
[학생] 테이블에서 학번 95115, 성명 '김정미', 학년 4, 수강과목 '데이터베이스', 연락처 '243-0707'INSERT INTO 학생(학번,성명,학년,수강과목,연락처) VALUES (95115,'김정미',4,'데이터베이스','243-0707');
UPDATE※테이블 작성 지시사항
[학생] 테이블에서 '이영진' 학생의 점수를 92점으로 수정하시오UPDATE 학생 SET 점수=92 WHERE 성명='이영진';
DELETE※테이블 작성 지시사항
[학생] 테이블에서 2학년 학생의 자료를 삭제하시오.DELETE FROM 학생 WHERE 학년=2;
[출처] [정보처리기사 / 실기 ] 데이터베이스 SQL 문 연습|작성자 클로이
