SQL 문제 풀이 | 데이터베이스 기본 2

적자생존

 

07. SQL 응용

Chapter 01. 데이터베이스 기본

 

#01. 다음 괄호 (   ) 안에 알맞은 말을 쓰시오.

 

스키마는 사용자나 개발자의 관점에서 필요로 하는 데이터베이스의 논리적 구조를 나타낸 (  ①  ) 스키마, 데이터베이스의 전체적인 논리적 구조를 나타낸 (  ②  ) 스키마, 물리적 저장 장치의 관점에서 보는 데이터베이스의 구조를 나타낸 (  ③  ) 스키마가 있다.

 

 

A. ① 외부   ② 개념   ③ 내부

 

 

 

해설.

스키마는 외부/개념/내부 3계층으로 구성되어 있다.

 

1. 외부 스키마 (External Schema) 

사용자나 개발자 관점에서 필요로 하는 데이터베이스의 논리적 구조로 사용자 뷰를 나타낸다. 서브 스키마라고도 불린다.

 

2. 개념 스키마 (Conceptual Schema)

데이터베이스의 전체적인 논리적 구조로 전체적인 뷰를 나타낸다. 개체 간 관계, 제약 조건, 접근 권한, 무결성, 보안에 대해 정의한다.

 

3. 내부 스키마 (Internal Schema)

물리적 저장 장치 관점에서 보는 데이터베이스 구조로 실제 데이터베이스에 저장될 레코드 형식을 정의한다. 저장 데이터의 항목 표현 방법, 내부 레코드의 물리적 순서 등을 표현한다.

 

 

#02. 사원 테이블의 '사번' 컬럼에 대해 '사번인덱스'라는 인덱스 명으로 인덱스를 생성하고자 한다. 알맞은 쿼리를 쓰시오.

 

 

 

A. 

CREATE INDEX 사번인덱스 ON 사원(사번);

 

 

해설.

CREATE INDEX 문법

CREATE INDEX [UNIQUE] 인덱스명 ON 테이블명(컬럼명1, 컬럼명2, ...);

• 복수 컬럼을 인덱스로 걸 수 있다.

 

 

#03. 다음은 사람이라는 테이블을 만들려고 한다. 사람이라는 테이블에는 이름, 성별이라는 컬럼이 있는데, 이름은 VARCHAR(10) 데이터 타입을 가지고 있다. 성별은 추가로 'M'과 'F' 값만 가질 수 있도록 제약 조건을 걸고자 한다. 알맞은 쿼리를 작성하시오.

 

 

A.

CREATE TABLE 사람
(
   이름 VARCHAR(10),
   성별 CHAR(1),
   CONSTRAINT 성별제약 CHECK 성별 IN ('M', 'F')
   
);

 

 

#CREATE TABLE 명령 - 데이터 정의어 (DDL ; Data Definition Language)
CREATE TABLE은 테이블을 생성하는 명령

▶ 하나의 컬럼(속성)에 대해 '컬럼명 데이터타입 제약조건' 으로 구성

▶ CREATE TABLE 제약조건
기본 키(PRIMARY KEY), 외래 키(FOREIGN KEY), UNIQUE, NOT NULL, CHECK, DEFAULT 

1. PRIMARY KEY 
테이블 기본 키 정의
유일하게 테이블 각 행 식별

2. FOREIGN KEY
외래 키 정의, 참조 대상은 참조 테이블명(컬럼명)으로 명시
해당 테이블의 열과 참조된 테이블의 열 사이 외래 키 관계 적용 

3. UNIQUE
테이블 내에서 유일한 값을 갖도록 하는 제약조건 

4. NOT NULL
해당 컬럼은 NULL 값을 포함하지 않도록 하는 제약조건

5. CHECK
개발자가 별도 정의하는 제약조건
참(TRUE)이어야 하는 조건 지정

6. DEFAULT
데이터 INSERT 할 때, 해당 컬럼 값 넣지 않는 경우 기본값으로 설정해주는 제약조건 

CREATE TABLE 테이블명
(
   컬럼명 데이터타입 [DEFAULT 값][NOT NULL]
    [, PRIMARY KEY(기본키, ...)]
    [, UNIQUE(컬럼명, ...)]
    [, FOREIGN KEY (외래키, ...) REFERENCES 참조 테이블(기본키)]
    [, CONSTRAINT 제약조건명 CHECK(조건식)]
);

예시.
CREATE TABLE 사원
( 
   사번 VARCHAR(10) NOT NULL,
   부서번호 VARCHAR(20),
   생년월일 DATE NOT NULL,
   성별 VARCHAR(1),
   PRIMARY KEY(사번),
   FOREIGN KEY(부서번호) REFERENCES 부서(부서코드),
   CONSTRAINT 성별제약 CHECK 성별 IN ('M', 'F')
);

 

 

해설.

CREATE TABLE 상세 문법은 다음과 같다.

CREATE TABLE 테이블명
(
  컬럼명 데이터타입 PRIMARY KEY, -- 기본 키 설정
  컬럼명 데이터타입 FOREIGN KEY REFERENCES 참조테이블(기본 키), --외래 키 설정 
  컬럼명 데이터타입 UNIQUE,
  컬럼명 데이터타입 NOT NULL,
  컬럼명 데이터타입 CHECK(조건식) -- 제약 조건 설정
  컬럼명 데이터타입 DEFAULT 값
);

 

 

 

 

#04. 사원테이블에 VARCHAR(11) 타입으로 전화번호라는 컬럼을 추가하려고 한다. 알맞은 쿼리를 작성하시오.

 

 

 

A.

ALTER TABLE 사원 ADD 전화번호 VARCHAR(11);

 

 

해설.

ALTER TABLE 컬럼 추가 문법은 다음과 같다.

ALTER TABLE 테이블명 ADD 컬럼명 데이터타입 [제약조건];

 

 

 

#05. 사원 테이블에 사번, 업무, 이름, 생년월일, 성별, 입사일이라는 컬럼이 있을 때 사원 테이블에서 성별 값이 'M'을 가진 사번, 이름으로 생성된 '사원뷰'라는 이름의 뷰 생성하는 쿼리를 작성하시오.

 

 

 

A.

CREATE VIEW 사원뷰 AS
SELECT 사번, 이름 
  FROM 사원 
 WHERE 성별='M'

 

# VIEW 관련 DDL - 데이터 정의어(DDL; Data Definition Language)

1. CREATE VIEW
뷰를 생성하는 명령

CREATE VIEW 뷰이름 AS
조회쿼리;

예시.
CREATE VIEW 사원뷰 AS
SELECT 사번, 이름
   FROM 사원
WHERE 성별='M';

※ VIEW 테이블 SELECT 문에서는 UNION이나 ORDER BY 절 쓸 수 X

▶컬럼명 기술하지 않으면 SELECT 문 컬럼명 자동 사용


2. CREATE OR REPLACE VIEW
CREATE OR REPLACE VIEW는 뷰 교체 명령
OR REPLEACE 키워드 추가 제외, CREATE VIEW와 사용 방법 동일

※ VIEW는 수정(ALTER)할 수 없으므로 삭제(DROP) 후에 재생성 혹은 CREATE OR REPLACE VIEW 명령으로 기존 뷰 교체 필요

CREATE OR REPLACE VIEW 뷰이름 AS
조회쿼리; 


3. DROP VIEW
DROP VIEW는 뷰 삭제 명령

DROP VIEW 뷰이름;

 

 

 

해설.

뷰를 생성하기 위해서는 다음과 같은 문법을 사용한다.

CREATE VIEW 뷰이름 AS
조회쿼리;

 

 

 

#06. 다음 테이블은 학생 테이블의 일부이다. 해당 테이블에서 전공만 출력하는 쿼리를 쓰시오. 단, 전공명은 중복되지 않아야 한다.

 

[ 학생 ]

이름	전공	생년월일
이완용	일본어학과	580607
박영효	일본어학과	610612
기철	몽골어학과	840101
안중근	국문학과	790902

 

 

 

A. 

SELECT DISTINCT 전공
  FROM 학생;

 

 

 

해설. 

SELECT 명령어 문법은 다음과 같다.

SELECT [ALL | DISTINCT] 속성명1, 속성명2, ...
  FROM 테이블명1, ...
[WHERE 조건]
[GROUP BY 속성명1, ...]
[HAVING 그룹조건]
[ORDER BY 속성 [ASC | DESC] ];

• DISTINCT 키워드 붙이면 중복된 속성 조회될 경우, 그 중 한 개만 검색 (SELECT 뒤 명시된 속성이 중복될 경우 한 개만 검색) 한다.

 

 

 

#07. 다음은 학생 테이블의 일부이다. '이' 씨 성을 가진 사람의 학번을 출력하는 쿼리를 작성하시오.

 

[ 학생 ]

이름	전공	학번
이완용	일본어학과	580607
박영효	일본어학과	610612
기철	몽골어학과	840101
안중근	국문학과	790902

 

 

 

A.

SELECT 학번
  FROM 학생
 WHERE 이름 LIKE '이%'

 

 

 

해설.

WHERE 절에서 패턴 이용할 경우, LIKE 키워드를 사용 

컬럼 LIKE 패턴

 

LIKE 패턴에 쓰이는 와일드카드는 다음과 같다.

 

% : 0개 이상 문자열과 일치

[ ] : 1개 문자와 일치

[^] : 1개 문자와 불일치

_ : 특정 위치 1개 문자와 일치

 

LIKE 사용 예제는 다음과 같다.

 

WHERE 이름 LIKE '이%' : 이름이 '이'로 시작하는 사람 검색WHERE 이름 LIKE '%이' : 이름이 '이'로 끝나는 사람 검색WEHRE 이름 LIKE '%이%' : 이름에 '이'가 들어가는 사람 검색WEHRE 이름 LIKE '이_' : 이름이 '이'로 시작되고 뒤에 1글자만 있는 사람 검색WHERE 이름 LIKE '이__' : 이름이 '이'로 시작되고 뒤에 2글자만 있는 사람 검색WEHRE 이름 LIKE '이_신' : 이름이 '이*신' 인 사람 검색, 즉 '이'로 시작하고 '신'으로 끝나는 모든 사람 검색

 

 

 

#08. 다음은 학생 테이블에서 주소 컬럼이 있다. 주소 컬럼이 NULL이 아닌 주솟값을 출력하는 쿼리를 작성하시오.

 

 

A.

SELECT 주소
  FROM 학생
 WHERE 주소 IS NOT NULL;

 

 

 

해설.

컬럼이 NULL이 아닌 데이터를 조회할 경우, WHERE 절에서 IS NOT NULL 구문 사용

컬럼 IS NOT NULL;

 

 

 

#09. 다음 쿼리와 동일한 쿼리를 IN 키워드를 써서 작성하시오. (IN은 한 번만 써야 함)

SELECT *
  FROM 교수
 WHERE 전공='컴퓨터공학' OR 전공='전자공학';

 

 

 

 

A.

SELECT *
  FROM 교수
 WHERE 전공 IN ('컴퓨터공학','전자공학');

 

 

 

 

해설.

WHERE 절에서 IN은 다음과 같이 작성한다.

컬럼 IN (값1, 값2, ...)

 

•  컬럼이 IN 안에 포함된 경우의 데이터 조회

 

 

 

#10. 다음은 고객 테이블이다. 나이가 50살 이상이면서 59살 이하이고 성별이 남자인 사람의 이름을 출력하는 쿼리를 작성하시오. (단, BETWEEN 구문을 사용해야 한다.)

 

<고객 테이블>

이름	나이	성별	주소
홍길동	20	남	경기도
임꺽정	59	남	전라도
유관순	35	여	경상도
나혜석	41	여	충청도
이순신	33	남	강원도

 

 

 

 

A. 

SELECT 이름
  FROM 고객
 WHERE 나이 BETWEEN 50 AND 59 
       AND 성별 ='남';

 

 

 

해설.

BETWEEN 문법은 다음과 같다.

컬럼 BETWEEN 값1 AND 값2

• 값1보다 크거나 같고, 값2보다 작거나 같은 데이터 조회