[정보처리기사] SQL 응용 - 기본 SQL 작성하기

 

SQL이란?

Structed Query Language
관계 데이터베이스에서 사용되는 대표적인 언어

 

SQL의 종류에는 정의어, 조작어, 제어어가 있다.

 

1. SQL 정의어(DDL)

• Data Definition Language
• 정의
  : 관계 데이터베이스에서 사용될 테이블, 스키마, 도메인, 인덱스, 뷰 등을 정의(생성)하거나 수정, 제거하기 위해 사용되는 언어
• 종류
  • CREATE
  • ALTER
  • DROP

 

- CREATE

1) 테이블 정의

CREATE TABLE 테이블_이름(
	속성_이름 데이터_타입 [NOT NULL],
    PRIMARY KEY(속성_이름),
    UNIQUE(속성_이름),
    FOREIGN KEY(속성_이름) REFFERENCES 참조테이블_이름(속성_이름)
    	ON DELETE CASCADE | SET NULL | SET DEFAULT | NO ACTION
        ON UPDATE CASCADE | SET NULL | SET DEFAULT | NO ACTION,
    CONSTRAINT 제약조건_이름 CHECK(속성_이름 = 범위 값)
);

예제

<테이블 생성 지시사항>

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

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

 

2) 스키마 정의

CREATE SCHEMA 스키마_이름 AUTHORIZATION 사용자;

예제

: 스키마 이름이 JUNGBO 이고, 허가권자가 이영진인 스키마를 정의하시오.

CREATE SCHEMA JUNGBO AUTHORIZATION 이영진;

 

3) 도메인 정의

CREATE DOMAIN 도메인_이름 데이터_타입 [DEFAULT 기본값]
	[CONSTRAINT 제약조건_이름 CHECK(VALUE IN(범위 값))];

예제

속성의 값으로 T와 F로만 구성되는 success라는 이름의 도메인을 정의하시오

(단, 속성값이 입력되지 않을 경우 기본값은 T로 한다.)

CREATE DOMAIN success CHAR(1) DEFAULT 'T'
	CONSTRAINT success CHECK(VALUE IN('T', 'F'));

 

4) 인덱스 정의(중복 가능)

CREATE INDEX 인덱스_이름 ON 테이블_이름(속성_이름 ASC|DESC);

 

5) 인덱스 정의(중복 불가능)

CREATE UNIQUE INDEX 인덱스_이름 ON 테이블_이름(속성_이름 ASC|DESC);

예제

학생 테이블의 학과 속성을 오름차순 정렬하여, 중복을 허용하지 않도록 stud_idx라는 이름의 인덱스를 정의하시오.

CREATE UNIQUE INDEX stud_idx ON 학생(학과 ASC);

 

---

 

- ALTER

1) 속성 추가

ALTER TABLE 테이블_이름 ADD 속성_이름 데이터_타입 [DEFAULT 기본값];

예제

아래 학생 테이블에 주소 속성을 추가하시오

(단, 주소 항목은 가변길이 문자형으로 30자까지 입력될 수 있다.)

학번	성명	학과	학년	학점
071025	이영진	전기통신	3	A
081517	홍길동	산업공학	2	B
081520	강희영	컴퓨터공학	4	A

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

 

2) 속성 변경(기본값 변경)

ALTER TABLE 테이블_이름 ALTER 속성_이름 [SET DEFAULT 기본값];

 

3) 속성 삭제

ALTER TABLE 테이블_이름 DROP 속성_이름 [CASCADE | RESTRICT];

예제

아래 학적 테이블에서 '학년' 속성을 제거하시오.

학번	성명	연락처	전공	학년
072233	김기김	010-1234-5678	컴퓨터	3
123455	이이이	010-2345-6789	국문	3
123456	서서서	010-9876-5432	영문	2
654321	박박박	010-8765-4321	법학	4

ALTER TABLE 학적 DROP 학년;

 

---

 

- DROP

1) 테이블, 스키마, 도메인, 뷰 삭제

DROP TABLE 테이블_이름 [CASCADE | RESTRICT];
DROP SCHEMA 스키마_이름 [CASCADE | RESTRICT];
DROP DOMAIN 도메인_이름 [CASCADE | RESTRICT];
DROP VIEW 뷰_이름 [CASCADE | RESTRICT];

예제

아래 학적 테이블을 제거하시오. 만약, 관계된 데이터가 있을 경우 함께 삭제하시오.

학번	성명	주민등록번호	전공	학년
072233	김기김	990909-9999999	컴퓨터	3
123455	이이이	990909-8888888	국문	3
123456	서서서	990909-7777777	영문	2
654321	박박박	990909-6666666	법학	4

DROP TABLE 학적 CASCADE;

 

2) 인덱스 삭제

DROP INDEX 인덱스_이름;

 

3) 제약조건 삭제

DROP CONSTRAINT 제약조건_이름;

 

---

 

2. SQL 조작어(DML)

• Data Manipulation Language
• 정의: 데이터베이스 내의 자료를 실제 사용자가 이용(조작)하기 위한 언어
  데이터의 검색, 삽입, 수정, 삭제 등을 위해 사용
• 종류
  • SELECT(검색)
  • INSERT(삽입)
  • UPDATE(갱신)
  • DELETE(삭제)

 

- SELECT

SELECT [DISTINCT] 속성_이름 FROM 테이블_이름
    [WHERE 조건]
    [GROUP BY 속성_이름 [HAVING 그룹조건]]
    [ORDER BY 속성_이름 [ASC|DESC]];

 

예제1

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

SELECT 성명 FROM 학생;

예제2

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

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

예제3

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

SELECT * 
FROM 학생
WHERE 학년 = 3;

예제4

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

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

예제5

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

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

예제6

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

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

예제7

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

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

예제8

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

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

예제9

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

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

예제10

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

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

예제11

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

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

예제12

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

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

예제13

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

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

예제14

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

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

 

---

 

- INSERT

1) 데이터 삽입(일부 추가)

INSERT INTO 테이블_이름(속성_이름, 속성_이름, ...) VALUES (속성값, 속성값, ...);

예제

학생 테이블에 학번 051115, 성명 김정미, 학년 4, 수강과목 데이터베이스, 연락처 243-0707인 학생을 삽입하십시오.

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

 

2) 데이터 삽입(전체 추가)

INSERT INTO 테이블_이름 VALUES (속성값, 속성값, ...);

 

 

---

 

- UPDATE

UPDATE 테이블_이름 SET 속성_이름 = 변경 값 [WHERE 조건];

예제

학생 테이블에서 이영진 학생의 점수를 92점으로 수정하세요.

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

 

---

 

- DELETE

DELETE FROM 테이블_이름 [WHERE 조건];

예제1

학생 테이블에서 2학년 학생의 자료를 삭제하시오

DELETE FROM 학생 
WHERE 학년 = 2;

예제2

학생 테이블의 모든 학생을 삭제하시오.

DELETE FROM 학생;

 

---

 

3. SQL 제어어(DCL)

• 정의: 관리자가 데이터의 보안, 무결성 유지, 병행제어, 회복 등을 하기 위해 사용하는 언어
• 종류
  • COMMIT
  • ROLLBACK
  • GRANT
  • REVOKE

 

- COMMIT과 ROLLBACK

• TCL(Transaction Control Language)에 해당
• 트랜잭션 수행과정에서 사용

 

---

 

 - GRANT

: 권한 부여

GRANT 권한_종류 ON 테이블_이름 TO 사용자 [WITH GRANT OPTION | WITH ADMIN OPTION];

예제

관리자가 사용자 OTH에게 학생 테이블에 대해 UPDATE 할 수 있는 권한과 그 권한이 필요시 다른 사용자에게 부여할 수 있는 권한을 부여하시오.

GRANT UPDATE 
ON 학생 
TO OTH 
WITH GRANT OPTION;

 

---

 

- REVOKE

: 권한 회수

REVOKE 권한_종류 ON 테이블_이름 FROM 사용자 [CASCADE];

예제

사용자 OTH에게 부여했던 학생 테이블에 대한 UPDATE 권한을 취소하시오.

REVOKE UPDATE 
ON 학생 
FROM OTH;

 

---

 

출처

https://www.youtube.com/watch?v=ov1NIQda5Zs&list=PL6i7rGeEmTvoDgmkQsg8kUjunIR_G7Njr&index=10