SQL

DDL

• DB 구조, 데이터 형식, 접근 방식 등 DB를 구축하거나 수정할 목적으로 사용하는 언어

CREATE DOMAIN

• 도메인을 정의하는 명령문

• '성별'을 '남' 또는 '여'와 같이 정해진 1개의 문자로 표현되는 도메인 SEX를 정의하는 SQL문을 작성하시오

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

CREATE TABLE

• 테이블을 정의하는 명령문

• '이름', '학번', '전공', '성별', '생년월일' 로 구성된 <학생> 테이블을 정의하는 SQL문을 작성하시오. 단, 제약 조건은 다음과 같다.

  • '이름'은 NULL이 올 수 없고 '학번'은 기본키이다.
  • '전공'은 <학과> 테이블의 '학과코드'를 참조하는 외래키로 사용된다.
  • <학과> 테이블에서 삭제가 일어나면 관련된 튜플들의 전공 값을 NULL로 만든다.
  • <학과> 테이블에서 '학과코드'가 변경되면 전공 값도 값은 값으로 변경한다.
  • '생년월일'은 1980-01-01 이후의 데이터만 저장할 수 있다.
  • 제약 조건의 이름은 '생년월일제약'으로 한다.
  • 각 속성의 데이터 타입은 적당하게 지정한다. 단 '성별'은 도메인 'SEX'를 사용한다.

  CREATE TABLE 학생
  (
  이름 VARCHAR(8) NOT NULL,
  학번 CHAR(8),
  전공 CHAR(5),
  성별 SEX,         // 도메인 SEX 지정
  생년월일 DATE,
  PRIMARY KEY(이름),
  FOREIGN KEY(전공) REFERENCES 학번(학과코드)
    ON DELETE SET NULL
    ON UPLETE CASCADE,
  CONSTRAINT 생년월일제약 CHECK(생년월일>='1980-01-01');
  );

CREATE INDEX

• 인덱스를 정의하는 명령문이다.

• <고객> 테이블에서 UNIQUE한 특성을 갖는 '고객번호' 속성에 대해 내림차순으로 정렬하여 '고객번호_idx'라는 이름으로 인덱스를 정의하시오.

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

ALTER TABLE

• 테이블에 대한 정의를 변경하는 명령문

• <학생> 테이블에 최대 3문자로 구성되는 '학년' 속성을 추가하시오

  ALTER TABLE 학생
  ADD 학년 VARCHAR(3);

• <학생> 테이블의 '학번' 필드의 데이터 타입과 크기를 VARCHAR(10)으로 하고 NULL 값이 입력되지 않도록 변경하시오

  ALTER TABLE 학생
  ALTER 학번 VARCHAR(10) NOT NULL;

DROP

• 스키마, 도메인, 기본 테이블, 뷰 테이블, 인덱스, 제약 조건 등을 제거하는 명령문

• DROP SCHEMA 스키마명 [CASCADE | RESTRICT];
  DROP DOMAIN 도메인명 [CASCADE | RESTRICT];
  DROP TABlE 테이블명 [CASCADE | RESTRICT];
  DROP VIEW 뷰명 [CASCADE | RESTRICT];
  DROP INDEX 인덱스명 [CASCADE | RESTRICT];
  DROP CONSTRAINT 제약조건명;

• <학생> 테이블을 제거하되, <학생> 테이블을 참조하는 모든 데이터를 함께 제거하시오

  DROP TABLE 학생 CASCADE;

---

DML

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

INSERT

• 기본 테이블에 새로운 튜플을 삽입할 때 사용

• <사원> 테이블에 (이름-홍승현, 부서 - 인터넷)을 삽입하시오

  INSERT INTO 사원 VALUES('홍승연', '인터넷');

• <사원> 테이블에 (장보고, 기획, 05/03/73, 홍제동, 90)을 삽입하시오

  INSERT INTO 사원 VALUES('장보고, '기획', #05/03/73#, '홍제동', 90);

  날짜 데이터는 숫자로 취급하지만 '' 또는 ##으로 묶는다.

• <사원> 테이블에 있는 편집부의 모든 튜플을 편집부원(이름, 생일, 주소, 기본급) 테이블을 삽입하시오

  INSERT INTO 편집부원(이름, 생일, 주소, 기본급)
  SELECT 이름, 생일, 주소, 기본급
  FROM 사원
  WHERE 부서='편집';

DELETE

• 기본 테이블에 있는 튜플들 중에서 특정 튜플(행)을 삭제할 때 사용

• 모든 레코드를 삭제하더라도 테이블 구조는 남아있기 때문에 디스크에서 테이블을 완전히 삭제하는 DROP과는 다르다.

• <사원> 테이블에서 "임꺽정"에 대한 튜플을 삭제하시오

  DELETE
  FROM 사원
  WHERE 이름='임꺽정';

• <사원> 테이블에서 "인터넷" 부서에 대한 모든 튜플을 삭제하시오

  DELETE
  FROM 사원
  WHERE 부서='인터넷';

• <사원> 테이블의 모든 레코드를 삭제하시오

  DELETE
  FROM 사원;

UPDATE

• 기본 테이블에 있는 튜플등 중에서 특정 튜플의 내용을 변경할 때 사용

• <사원> 테이블에서 "홍길동"의 '주소'를 "수색동"으로 수정하시오

  UPDATE 사원
  SET 주소='수색동'
  WHERE 이름='홍길동';

• <사원> 테이블에서 "황진이"의 '부서'를 "기획부"로 변경하고 '기본급을 5만원 인상시키시오

  UPDATE 사원
  SET 부서='기획부', 기본급=기본급+5;
  WHERE 이름='황진이';

SELECT

• 기본 테이블에서 특정 튜플의 값을 보고자 할 때 사용

• <사원> 테이블의 모든 튜플을 검색하시오

  SELECT *
  FROM 사원;

• <사원> 테이블에서 '주소'만 검색하되 같은 '주소'는 한 번만 출력하시오

  SELECT DISTINCT 주소
  FROM 사원;

• <사원> 테이블에서 '기획'부의 모든 튜플을 검색하시오

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

• <사원> 테이블에서 "기획" 부서에 근무하면서 "대흥동"에 사는 사람의 튜플을 검색하시오

  SELECT *
  FROM 사원
  WHERE 부서='기획' AND 주소='대흥동';

• <사원> 테이블에서 '부서'가 "기획"이거나 "인터넷"인 튜플을 검색하시오

  SELECT *
  FROM 사원
  WHERE 부서='기획' OR '인터넷';

  또는

  SELECT *
  FROM 사원
  WHERE 부서 IN('기확', '인터넷');

• <사원> 테이블에서 성이 '김'인 사람의 튜플을 검색하시오

  SELECT *
  FROM 사원
  WHERE 이름 LIKE '김%';

  LIKE 연산자
  : 대표문자를 이용해 지정된 속성의 값이 문자패턴과 일치하는 튜플을 검색하기 위해 사용

  대표 문자	%	_	#
  의미	모든문자를 대표함	문자 하나를 대표함	숫자 하나를 대표함

• <사원> 테이블에서 '생일'이 '01/01/69'에서 '12/31/73' 사이인 튜플을 검색하시오

  SELECT *
  FROM 사원
  WHERE 생일 BETWEEN '01/01/69' AND '12/31/73';

• <사원> 테이블에서 '주소'가 NULL 인 튜플을 검색하시오

  SELECT *
  FROM 사원
  WHERE 주소 IS NULL;

• <사원> 테이블에서 '주소'를 기준으로 내림차순 정렬시켜 상위 2개 튜퓰만 검색하시오

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

• <사원> 테이블에서 '부서'를 기준으로 오름차순 정렬하고, 같은 '부서'에 대해서는 '이름'을 기준으로 내림차순 정렬시켜서 검색하시오

  SELECT *
  FROM 사원
  ORDER BY 부서 ASC, 이름 DESC;

• 취미활동을 하지 않는 사람들을 검색하시오.

  SELECT *
  FROM 사원
  WHERE 이름 NOT IN (
        SELECT 이름
        FROM 여가활동
  );

• 취미활동을 하는 사람들의 부서를 검색하시오

  SELECT 부서
  FROM 사원
  WHERE EXISTS (
        SELECT 이름
        FROM 사원
        WHERE 여가활동.이름 = 사원.이름
  );

• '경력'이 10년 이상인 사원의 '이름', '부서', '취미', '경력'을 검색하시오

  SELECT 사원.이름, 사원.부서, 여가활동.취미, 여가활동.경력
  FROM 사원, 여가활동
  WHERE 사원.이름 = 여가활동.이름
  AND
  여가활동.경력 >= 10;

• <상여금> 테이블에서 '상여내역'별로 상여금에 대한 일련 번호를 구하시오.
  단 순서는 내림차순이며 속성명은 "NO"로 할 것

  SELECT 상여내역, 상여금,
  ROW_NUMBER() OVER( PARTITION BY 상여내역
  ORDER BY 상여금 DESC) AS NO,
  FROM 상여금;

  ROW_NUMBER()
  : 윈도우별로 각 레코드에 대한 일련번호를 반환한다.

• <상여금> 테이블에서 '상여내역'별로 '상여금'에 대한 순위를 구하시오.
  단 순서는 내림차순이며, 속성명은 '상여금순위'로 하고 RANK() 함수를 이용

  SELECT 상여내역, 상여금,
  RANK() OVER( PARTITION BY 상여내역
  ORDER BY 상여금 DESC ) AS 상여금순위
  FROM 상여금;

  RANK()
  : 윈도우 별로 순위를 반환하며, 공동 순위를 반영
  DENSE_RANK()
  : 윈도우 별로 순위를 반환하며, 공동 순위를 무시하고 순위부여

• <상여금> 테이블에서 '부서'별 '상여금'의 평균을 구하시오

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

• <상여금> 테이블에서 부서별 튜플의 수를 검색하시오
  SELECT 부서, COUNT(*) AS 사원수
  FROM 상여금
  GROUP BY 부서;

• <상여금> 테이블에서 '상여금'이 100이상인 사원이 2명 이상인 '부서'의 튜플 수를 구하시오

  SELECT 부서, COUNT(*) AS 사원수
  FROM 상여금
  WHERE 상여금 >= 100
  GROUP BY 부서
  HAVING COUNT(*) >= 2;

  WHERE 절과 GROUP BY/HAVING 절이 같이 사용된 경우!!!

• <상여금> 테이블의 '부서', '상여내역' 그리고 '상여금' 에 대해 부서별 상여내역 별 소계와 전체 합계를 검색하시오.
  단 속성명은 '상여금합계'로 하고 ROLLUP 함수를 사용할 것

  SELECT 부서, 상여내역, SUM(상여금) AS 상여금합계
  FROM 상여금
  GROUP BY ROLLUP(부서, 상여금);

• <상여금> 테이블의 '부서', '상여내역' 그리고 '상여금' 에 대해 부서별 상여내역 별 소계와 전체 합계를 검색하시오.
  단 속성명은 '상여금합계'로 하고 CUBE 함수를 사용할 것

  SELECT 부서, 상여내역, SUM(상여금) AS 상여금합계
  FROM 상여금
  GROUP BY CUBE(부서, 상여금);

• <사원> 테이블과 <직원> 테이블을 통합하는 질의문을 작성하시오.
  단 같은 레코드가 중복되어 나오지 않게 하시오

  SELECT *
  FROM 사원
  UNION
  SELECT *
  FROM 직원;

• <사원> 테이블과 <직원> 테이블에 공통으로 존재하는 레코드만 통합하는 질의 문을 작성하시오

  SELECT *
  FROM 사원
  INTERSECT
  SELECT *
  FROM 직원;

JOIN

• 2개의 릴레이션에서 연관된 튜플들을 결합하여, 하나의 새로운 릴레이션을 반환
• INNER JOIN와 OUTER JOIN으로 구분된다.

INNER JOIN

• EQUI JOIN과 NON-EQUI JOIN으로 구분된다.
• 조건이 없는 INNER JOIN을 수행하면 CROSS JOIN과 동일한 결과를 얻는다.

  CROSS JOIN
  결과로 반환되는 행의 수는 두 테이블의 행 수를 곱한 것과 같다.

EQUI JOIN

• 대상 테이블에서 공통 속성을 기준으로 '='(equal) 비교에 의해 같은 값을 가지는 행을 연결하여 결과를 생성하는 방법
• EQUI JOIN 에서 JOIN 조건이 '='일 때 동일한 속성이 두번 나타게 되는데 이 중 중복된 속성을 제거하여 같은 속성을 한번만 표기하는 방법을 NATURAL JOIN이라고 한다.

• <학생> 테이블과 <학과> 테이블에서 '학과코드' 값이 같은 튜플을 JOIN하여 '학번', '이름', '학과코드', '학과명'을 출력하는 SQL문을 작성하시오

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

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

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

NON-EQUI JOIN

• JOIN 조건에 '=' 조건이 아닌 나버지 비교 연산자를 사용하는 방법

• <학생> 테이블과 <성적등급> 테이블을 JOIN하여 각 학생의 '학번', '이름', 성적', '등급'을 출력하는 SQL문을 작성하시오

  SELECT 학번, 이름, 성적, 등급
  FROM 학생, 성적등급
  WHERE 학생.성적 BETWEEN 성적등급.최저 AND 성적등급.최고;

OUTER JOIN

• 릴레이션에서 JOIN 조건에 만족하지 않는 튜플도 결과로 출력하기 위한 방법
• LEFT OUTER JOIN과 RIGHT OUTER JOIN, FULL OUTER JOIN이 있다.

LEFT OUTER JOIN

• INNER JOIN의 결과를 구한 후, 우측 항 릴레이션의 어떤 튜플과도 맞지 않는 좌측 항의 릴레이션에 있는 튜플들에 NULL 값을 붙여서 INNER JOIN의 결과에 추가한다.

• <학생> 테이블과 <학과> 테이블에서 '학과코드'값이 값은 튜플을 JOIN하여 '학번', '이름', '학과코드' '학과명'을 출력하는 SQL 문을 작성하시오.
  이때 '학과코드'가 입력되지 않은 학생도 출력하시오

  SELECT 학번, 이름, 학생.학과코드, 학과명
  FROM 학생 LEFT OUTER JOIN 학과
  ON 학생.학과코드 = 학과.학과코드;

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

RIGHT OUTER JOIN

• INNER JOIN의 결과를 구한 후, 좌측 항 릴레이션의 어떤 튜플과도 맞지 않는 우측 항의 릴레이션에 있는 튜플들에 NULL 값을 붙여서 INNER JOIN의 결과에 추가한다.

• <학생> 테이블과 <학과> 테이블에서 '학과코드'값이 값은 튜플을 JOIN하여 '학번', '이름', '학과코드' '학과명'을 출력하는 SQL 문을 작성하시오.
  이때 '학과코드'가 입력되지 않은 학생도 출력하시오

  SELECT 학번, 이름, 학생.학과코드, 학과명
  FROM 학과 RIGHT OUTER JOIN 학생
  ON 학생.학과코드 = 학과.학과코드;

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

FULL OUTER JOIN

• LEFT OUTER JOIN 과 RIGHT OUTER JOIN을 합쳐놓은 것

• <학생> 테이블과 <학과> 테이블에서 '학과코드' 값이 같은 튜플을 JOIN하여 '학번', '이름', '학과코드', '학과명'을 출력하는 SQL 문을 작성하시오.
  이때 '학과코드'가 입력 안된학생이나 학생이 없는 '학과코드'도 모두 출력하시오

  SELECT 학번, 이름, 학생.학과코드, 학과명
  FROM 학과 FULL OUTER JOIN 학생
  ON 학생.학과코드 = 학과.학과코드;

---

DCL

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

GRANT / REVOKE

• DBA가 데이터베이스 사용자에게 권한을 부여하거나 취소하는 명령어

• 사용자 ID가 'NABI' 인 사람에게 데이터베이스 및 테이블을 생성할 수 있는 권한을 부여하는 SQL문을 작성하시오

  GRANT RESOURCE TO NABI;

  DBA : 데이터베이스 관리자
  RESOURCE : 데이터베이스 및 테이블 생성 가능이 가능한 자
  CONNECT : 단순 사용자

• 사용자 ID가 "STAR" 인 사람에게 단순히 데이터베이스에 있는 정보를 검색할 수 있는 권한을 부여하는 SQL문을 작성하시오

  GRANT CONNECT TO STAR;

• 사용자 ID가 "NAVI"인 사람에게 <고객> 테이블에 대한 모든 권한과 다른 사람에게 부여할 수 있는 권한까지 부여하는 SQL문을 작성하시오

  GRANT ALL ON 고객 TO NAVI WITH GRANT OPTION;

• 사용자 ID가 "STAR"인 사람에게 부여한 <고객> 테이블에 대한 권한 중 UPDATE 권한을 다른사람에게 부여할 수 있는 권한만 취소하는 SQL문을 작성하시오

  REVOKE GRANT OPTION FOR UPDATE ON 고객 FROM STAR;

• <수강> 테이블에 대해 임꺽정에게 부여된 UPDATE 권한과 임꺽정이 다른 사람에게 UPDATE 권한을 부여할 수 있는 권한, 그리고 임꺽정이 다른 사람에게 부여한 UPDATE 권한도 모두 취소하는 SQL문을 작성하시오

  REVOKE UPDATE ON 수강 FROM 임꺽정 CASCADE;

COMMIT

• 트랜잭션 처리가 정상적으로 완료된 후 트랜잭션이 수행한 내용을 데이터베이스에 반영하는 명령어

• <사원> 테이블에서 '사원번호'가 40인 사원의 정보를 삭제한 후 COMMIT을 수행하시오

  DELETE FROM 사원 WHERE 사원번호=40;
  COMMIT;

ROLLBACK

• 변경되었으나 아직 COMMIT되지 않은 모든 내용을 취소하고 데이터베이스를 이전 상태로 되돌리는 명령어

• SAVEPOINT 'S2'까지 ROLLBACK을 수행하시오

  ROLLBACK TO S2;

SAVEPOINT

• 트랜잭션 내에 ROLLBACK 할 위치인 저장점을 지정하는 명령어

• SAVEPOINT 'S1'을 설정하고 '사원번호'가 20인 사원의 정보를 삭제하시오

  SAVEPOINT S1;
  DELETE FROM 사원 WHERE 사원번호=20;