국비학원 28일차 : SQL 시험

문제

A 회사는 새로운 인사관리 프로젝트를 위한 데이터베이스를 구축하려 한다.

Entity

관리하고자 하는 객체는 사원, 부서, 직급로 구성하려 한다.

Table

사원은 사번, 이름, 생년월일, 전화번호, 주소, 부서, 직급으로 구성되어 있으며 사원은 사번으로 관리한다. → 사번 primary key

부서는 부서코드, 부서명, 부서전화번호, 부서주소, 부서장 사번으로 구성되어 있으며 부서는 부서코드로 관리한다. → 부서코드 primary key

직급은 직급코드, 직급명으로 구성되어 있으며 직급은 직급코드로 관리한다. → 직급코드 primary key

Relation

한명의 사원은 하나의 부서에 반드시 속해 있으며, 하나의 부서에는 여러명의 사원이 존재한다.

⭐하나의 부서에는 한명의 부서장이 존재할 수도 있으며, 한명의 사원은 부서장일 수도 있고 아닐 수도 있다. → 부서장의 값은 처음은 null로 존재
추후에 UPDATE 하는 방식으로 데이터 입력해야한다

한명의 사원은 하나의 직급에 반드시 속해 있으며, 하나의 직급에는 여러명의 사원이 존재한다.

하나의 부서에 여러개의 직급이 존재하며 하나의 직급에 여러개의 부서가 존재 할 수 있고 부서와 직급에 따라 연봉이 지정되어 있다.

연봉은 부서코드, 직급코드, 연봉으로 구성된다.

조건

예시를 보고 이에 해당하는 테이블을 생성하시오.
(외래키 제약 조건은 작성하지 않는다.)

부서(Department)

• 부서코드(department_code): DEV
• 부서명(department_name): 개발부 (필수)
• 부서전화번호(department_tel_number): 051-240-1155 (필수, 중복불가)
• 부서주소(department_address): 부산광역시 부산진구 (필수)
• 부서장 사번(head_employee_number): 2023010101

직급(Posit)

• 직급코드(position_code): PO1
• 직급이름(position_name): 사원 (필수)

사원(Employee)

• 사번(employee_number): 2023010101
• 이름(employee_name): John Doe (필수)
• 생년월일(employee_birth_date): 1980-11-12 (필수)
• 전화번호(employee_tel_number): 010-9184-6574 (필수, 중복불가)
• 주소(employee_address): 부산광역시 부산진구 (필수)
• 부서코드(department_code): DEV
• 직급코드(position_code): PO1

선생님 풀이

CREATE TABLE Department (

    department_code VARCHAR(3) PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL,
    department_tel_number VARCHAR(15) NOT NULL UNIQUE,
    department_address TEXT NOT NULL,
    head_employee_number VARCHAR(10)

);

CREATE TABLE Posit (

    position_code VARCHAR(3) PRIMARY KEY,
    position_name VARCHAR(20) NOT NULL

);

CREATE TABLE Employee (

    employee_number VARCHAR(10) PRIMARY KEY,
    employee_name VARCHAR(50) NOT NULL,
    employee_birth_date DATE NOT NULL,
    employee_tel_number VARCHAR(15) NOT NULL UNIQUE,
    employee_address TEXT NOT NULL,
    department_code VARCHAR(3) NOT NULL,
    position_code VARCHAR(3) NOT NULL

);

내가 푼것

-- 테이블을 생성한 다음 관계를 추가하지 않고 한번에 다 진행해서
-- 외래키까지 같이 적음.

CREATE TABLE Department(

    department_code VARCHAR(20) PRIMARY KEY,
    department_name VARCHAR(50) NOT NULL,
    department_tel_number VARCHAR(50) NOT NULL UNIQUE,
    department_address VARCHAR(200) NOT NULL,
    head_employee_number INT

);

CREATE TABLE Posit(

    position_code VARCHAR(50) PRIMARY KEY,
    position_name VARCHAR(50) NOT NULL

);

CREATE TABLE Employee(

    employee_number INT PRIMARY KEY,
    employee_name VARCHAR(50) NOT NULL,
    employee_birth_date DATE NOT NULL,
    employee_tel_number VARCHAR(50) NOT NULL UNIQUE,
    employee_address VARCHAR(200) NOT NULL,
    department_code VARCHAR(50),
    FOREIGN KEY (department_code)
    REFERENCES Department(department_code),
    position_code VARCHAR(50),
    FOREIGN KEY (position_code) 
    REFERENCES Posit(position_code)
);

한명의 사원은 하나의 부서에 반드시(NOT NULL) 속해 있으며,
하나의 부서에는 여러명의 사원이 존재한다.
하나의 부서에는 한명의 부서장이 존재할 수도 있으며,
한명의 사원은 부서장일 수도 있고 아닐 수도 있다.
한명의 사원은 하나의 직급에 반드시(NOT NULL) 속해 있으며,
하나의 직급에는 여려명의 사원이 존재한다.
NOT NULL의 값이 빠졌다.

🖊️오답노트

department_code VARCHAR(50) NOT NULL,
FOREIGN KEY (department_code) REFERENCES Department(department_code),
position_code VARCHAR(50) NOT NULL,
FOREIGN KEY (position_code) REFERENCES Posit(position_code)

생성한 테이블과 상단에 제시된 조건에 따라 관계를 추가하시오.
(관계 테이블인 연봉 테이블 또한 생성하시오.)

연봉(Annual_Income)

• 부서코드(department_code): DEV
• 직급코드(position_code): PO1
• 연봉(annual_income_amount): 38000000 (필수)

선생님 풀이

ALTER TABLE Employee
ADD CONSTRAINT Employee_Department_FK
FOREIGN KEY (department_code)
REFERENCES Department(department_code);

ALTER TABLE Employee
ADD CONSTRAINT Employee_Position_FK
FOREIGN KEY (position_code)
REFERENCES Posit(position_code);

ALTER TABLE Department
ADD CONSTRAINT Department_Head_FK
FOREIGN KEY (head_employee_number)
REFERENCES Employee(employee_number);

CREATE TABLE Annual_Income (
	department_code VARCHAR(3),
    position_code VARCHAR(3),
    annual_income_amount INT NOT NULL,
    CONSTRAINT Annual_Income_PK
    PRIMARY KEY (department_code, position_code),
    CONSTRAINT Annual_Income_Department_FK
    FOREIGN KEY (department_code)
    REFERENCES Department(department_code),
    CONSTRAINT Annual_Income_Posit_FK
    FOREIGN KEY (position_code)
    REFERENCES Posit(position_code)

);

Department와 Employee의 관계에서 head_employee_number는 Employee의 employee_number와 관계가 있다. 부서장과 사원에 대한 관계를 성립해두지 않았다. → ⭐관계 다시 생각해볼것.

🖊️오답풀이

CREATE TABLE Department(

    department_code VARCHAR(20) PRIMARY KEY,
    department_name VARCHAR(50) NOT NULL,
    department_tel_number VARCHAR(50) NOT NULL UNIQUE,
    department_address VARCHAR(200) NOT NULL,
    head_employee_number INT,
    FOREIGN KEY (head_employee_number)
    REFERENCES Employee(employee_number)
);

사원, 부서, 직급, 연봉 테이블에 3,4번 문제에 제시된 예시 데이터를 입력하시오. (필요하다면 먼저 입력 후 수정 작업을 진행해도 됨)

선생님풀이

INSERT INTO Department VALUES ('DEV', '개발부', '051-240-1155', 
							'부산광역시 부산진구', null);

INSERT INTO Posit VALUES ('PO1', '사원');

INSERT INTO Employee VALUES ('2023010101', 'John Doe', '1980-11-12', 
				'010-9184-6574', '부산광역시 부산진구', 'DEV', 'PO1');

UPDATE Department SET head_employee_number = 
'2023010101' WHERE department_code = 'DEV';

내가 푼것

INSERT INTO Department VALUES ('DEV', '개발부', '051-240-1155', 
						'부산광역시 부산진구', 2023010101);

INSERT INTO Posit VALUES ('PO1', '사원');

INSERT INTO Employee VALUES ( 2023010101, 'John Doe', '1980-11-12', 
				'010-9184-6574', '부산광역시 부산진구', 'DEV', 'PO1');

INSERT INTO Annual_Income VALUES (1, 'DEV', 'PO1', 2023010101, 38000000);

하나의 부서에는 한명의 부서장이 존재할 수도 있으며, 한명의 사원은 부서장일 수도 있고 아닐 수도 있다. → 처음에는 null 값으로 입력해서 추후에 업데이트 하는 방향으로 진행해야한다.

🖊️오답 노트

INSERT INTO Department VALUES ('DEV', '개발부', '051-240-1155', 
						'부산광역시 부산진구', null);
UPDATE Department SET head_employee_number = 
		'2023010101' WHERE department_code = 'DEV';

부서 코드가 DEV인 부서의 부서코드, 부서명, 부서장 이름, 부서장 전화번호를 검색하는 SQL을 작성하시오.

선생님 풀이

SELECT D.department_code AS '부서코드', 
		D.department_name AS '부서명', 
        E.employee_name AS '부서장 이름', 
        E.employee_tel_number AS '부서장 전화번호'
FROM Department D, Employee E
WHERE D.head_employee_number = E.employee_number
AND D.department_code = 'DEV';

내가 푼것

SELECT D.department_code, 
		D.department_name, 
        E.employee_name, 
        E.employee_tel_number 
FROM Department D, Employee E 
WHERE D.department_code = 'DEV';

부서장에 대한 조건이 들어가지 않았다! 부서장이 제대로 정립되지 않고 문제를 풀다보니까 자꾸 다 빼먹고 조건을 작성하게 됐다.

🖊️오답노트

SELECT D.department_code, 
		D.department_name, 
        E.employee_name, 
        E.employee_tel_number 
FROM Department D, Employee E 
WHERE D.head_employee_number = E.employee_number #부서장일때
AND D.department_code = 'DEV';

사원 테이블에서 부서코드와 직급코드로 검색속도를 높이려고 한다. 검색 속도를 높이기 위한 DDL을 사원 테이블에 추가하시오.

선생님 풀이

CREATE INDEX Employee_Index_1
ON Employee (department_code, position_code);

내가 푼것

CREATE INDEX employee_tel_number_index
ON Employee (employee_tel_number);

왜.. 전화번호로 인덱스 설정했지? 문제에 부서코드랑 직급코드로 하라고 나와았는데.. 왜.. 왜그랬지?? 시험칠때는 문제가 눈에 안들어왔나...

🖊️오답노트

CREATE INDEX index_1
ON Employee (department_code, position_code);

6번 문제에서 작성한 SQL을 읽기전용의 가상 테이블로 작성하시오.

조건의 설정이 잘못 들어간 SELECT문이여서 이 조건으로 VIEW만들어서 같이 틀린것.

🖊️오답풀이

CREATE VIEW Department_View AS
SELECT D.department_code, 
		D.department_name, 
        E.employee_name, 
        E.employee_tel_number 
FROM Department D, Employee E 
WHERE D.head_employee_number = E.employee_number #부서장일때
AND D.department_code = 'DEV';