[정처기]실기 8장 - SQL 응용

📝102. SQL - DDL

• DDL(데이터 정의어)
  • DB를 구욱하거나 수정할 목적으로 사용하는 언어이다.

🚩CREATE 

• CREATE SCHEMA

CREATE SCHEMA 스키마명 AUTHORIZATION 사용자_ID

• CREATE DOMAIN

CREATE DOMAIN SEX CHAR(1)
	DEFAULT '남'
    CONSTRAINT VALID-SEX CHACK(VALUE IN ('남','여');

• CREATE TABLE
• CREATE VIEW 

CREATE VIEW 안산고객(고객, 전화번호)
AS SELECT 성명, 전화번호
FROM 고객
WHERE 주소 = '안산시';

• CREATE INDEX
  • UNIQUE를 사용하는 경우 중복 값이 없이 인덱스를 생성한다. 
  • ASC는 오름차순 정렬 DESC는 내림차순 정렬
  • CLUSTER : 클러스터드 인덱스로 설정

CREATE UNIQUE INDEX 고객번호_idx
ON 고객(고객번호 DESC);

 

🚩ALTER 

ALTER TABLE 테이블명 ADD 속성명 데이터_타입  //속성 추가
ALTER TABLE 테이블명 ALTER 속성명 [SET DEFAULT '기본값'] //디폴트 설정
ALTER TABLE 테이블명 DROP COLUMN 속성명 [CASCADE] //속성 삭제

 

🚩DROP

DROP TABLE 테이블명 [CASCADE | RESTRICT]

• CASCADE : 제거할 요소를 참조하는 다른 모든 개체를 함께 제거
• RESTRICT : 다른 개체가 제거할 요소를 참조중일 때는 제거를 취소 

 

문제 1. <학생> 테이블에 20자리의 가변 길이를 가진 주소 속성을 추가하는 SQL 문을 작성하시오

 

정답 : ALTER 학생 ADD 주소 VARCHAR(20);

 

문제 2. name 속성으로 idx_name이라는 인덱스를 생성하는 SQL문을 작성하시오

 

정답 : CREATE INDEX idx_name

          ON Student(name);

📝103. SQL - DCL 

• 데이터 보안, 무결성, 회복, 병행 제어 등을 정의하는 데 사용하는 언어이다.

🚩GRANT / REVOKE

• 각각 권한을 부여하는 명령어와 권한을 취소하는 명령어이다.

GRANT 권한_리스트 ON 개체 TO 사용자 [WITH GRANT OPTION];
REVOKE [GRANT OPTION FOR] 권한_리스트 ON 개체 FROM 사용자 [CASCADE]

• 권한 종류 : ALL, SELECT, INSERT, DELETE, UPDATE 등
• WITH GRANT OPTION : 부여받은 권한을 다른 사용자에게 다시 부여할 수 잇는 권한을 준다.
• GRANT OPTION FOR : 다른 사용자에게 권한을 부여할 수 잇는 권한을 취소함
• CASCADE : 권한 부여할 수 잇는 권한으로 권한을 준 것도 연쇄 취소

🚩COMMIT

• 트랜잭션이 수행한 내용을 데이터베이스에 반영하는 명령이다.

🚩ROLLBACK

• 아직 COMMIT되지 않는 모든 내용을 취소하고 이전 상태로 돌아가는 것 

문제 1. 데이터를 제어하는 DCL의 하나인 ROLLBACK에 대해 간략히 서술하시오

 

정답 : 트랜잭션 처리 후 아직 COMMIT되지 않은 모든 내용을 취소하고 데이터베이스를 이전의 상태로 돌리는 명령어

 

문제 2. 데이터를 제어하는 DCL의 하나인 GRANT에 대한 기능을 간략히 설명하시오

 

정답 : 권한을 부여하는 명령어

📝104. SQL - DML 

• 저장된 데이터를 실질적으로 관리하는데 사용되는 언어

🚩삽입문 INSERT 

INSERT INTO 테이블명(속성명1, 속성명2)
VALUES (데이터1, 데이터2)

🚩삭제문 DELETE

DELETE 
FROM 테이블명
[WHERE 조건]

🚩갱신문 UPDATE

UPDATE 테이블명
SET 속성명 = 데이터
[WHERE 조건]

문제 1. 학생 테이블에서 이름이 민수인 튜플을 삭제하고자 한다. 

최소한의 코드로 작성될 수 있도록 SQL문을 구성
명령문 마지막의 세미콜론은 생략이 가능
인용 부호가 필요한 경우 작은 따옴표 사용

 

정답

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

 

문제 2. 학생 테이블은 학번, 이름, 학년, 신청과목, 연락처로 이루어져 있다.

학생 테이블에 (9816021, '한국산',3,'경영학개론', '050-1234-1234') 데이터를 삽입하는 구문을 쓰시오

 

정답

INSERT INTO 학생
VALUES(9816021, '한국산', 3, '경영학개론', '050-1234-1234');

📝105. DML - SELECT -1 

• 일반형식

SELETE [DISTINCT] [테이블명.]속성명 [AS 별칭]
FROM 테이블명
[GROUP BY 속성명]
[HAVING 조건]
[ORDER BY 속성명 [ASC|DESC]];

• 기본 검색

SELECT * FROM 사원;

• 조건 지정 검색

SELECT *
FROM 사원
WHERE 부서 = '기획';

• 정렬 검색

SELECT TOP 2 *
FROM 사원
ORDER BY 주소 DESC;

문제 1. 학생 테이블에 전기과 학생이 50명, 전산과 학생이 100명, 전자과 학생이 50명있다고 할때 다음 SQL문의 튜플 수를 쓰시오

1. SELECT DEPT FROM STUDENT;
2. SELECT DISTINCT DEPT FROM STUDENT;
3. SELECT COUNT(DISTINCT DEPT) FROM STUDENT WHERE DEPT = '전산과';

 

정답 : 1. 200 , 2. 3, 3. 1 

 

문제 2. 다음 성적 테이블에서 이름과 점수를 조회하되 점수를 기준으로 내림차순 정렬하는 SQL문을 만드시오

 

정답

SELECT name, score
FROM 성적
ORDER BY score DESC;

📝106. DML - SELECT2 

• 그룹함수
  • GROUP BY 절에 지정된 그룹별로 속성의 값을 집계할 때 사용
• WINDOW 함수
  • GROUP BY절을 이용하지 않고 함수의 인수를 지정한 속성 값을 집계한다. 
• 그룹 지정 검색

SELECT 부서, AVG(상여금) AS 평균
FROM 상여금
GROUP BY 부서;

• 집합 연산자를 이용한 통합 질의
  • UNION : 두 SELECT 문의 조회 결과를 톨합하여 모두 출력 중복행은 한번만
  • UNION ALL : 중복행을 그대로 표시 

SELECT *

FROM 사원

UNION

SELECT *

FROM 직원

📝107. DML - JOIN

• 연관된 튜플들을 결합하여, 하나의 새로운 릴레이션으로 변환한다.
• INNER JOIN
  • EQUI JOIN
    • 공통된 속성을 기준으로 비교에 의해 같은 값을 가지는 행을 연결하여 결과를 생성하는 JOIN 방법
    • 이 방법을 사용하면 동일한 속성이 두 번 나타나는데 이걸 해결하는 것이 자연조인(NATURAL)이다.

SELECT 학번, 이름, 학생.학과코드, 학과명
FROM 학생, 학과
WHERE 학생.학과코드 = 학과.학과코드;

SELECT 학번, 이름, 학생.학과코드, 학과명
FROM 학생 NATURAL JOIN 학과;

SELECT 학번, 이름, 학생.학과코드, 학과명
FROM 학생 JOIN 학과 USING(학과코드);

• OUTER JOIN
  •  릴레이션에서 JOIN 조건에 만족하지 않는 튜플도 결과로 출력하기 위한 JOIN 방법이다. 
  • LEFT OUTER JOIN
    • 우측항에 맞지 않는 좌측 항의 릴레이션을 NULL값으로 붙여서 INNER JOIN의 결과에 추가
  • RIGHT OUTER JOIN
    • 좌측항 릴레이션의 어떤 튜플과도 맞지 않는 우측항의 릴레이션에 있는 튜플들을 NULL 값을 붙여서 INNER JOIN한 결과
  • FULL OUTER JOIN 
    • 위 두 LEFT와 RIGHT를 합쳐놓은것

📝108. 트리거 

• 트리거는 이벤트가 발생할 때 관련 작업이 자동으로 수행되게 하는 절차형 SQL이다.