관계형 데이터베이스의 기본 원리를 이해하고 SQL 질의문을 작성하는 연습은 데이터 관리에 있어 매우 중요하다. 본 글에서는 주어진 SQL문을 바탕으로 다양한 질의를 통해 관계형 데이터베이스를 탐구하는 방법을 제시한다.
데이터베이스 스키마 및 테이블 생성
테이블 생성 및 구조
2017년 10월 27일 기준으로, 다음과 같은 데이터베이스 스키마를 생성하였다. 이 스키마에는 부서, 사원 및 프로젝트와 관련된 세 개의 테이블이 포함되어 있다.
- DEPARTMENT 테이블은 부서 번호, 부서 이름, 층 수를 저장한다.
- EMPLOYEE 테이블은 사원 번호, 사원 이름, 직급, 관리자, 급여와 부서 번호를 포함한다.
- PROJEMP 테이블은 프로젝트 번호, 프로젝트 이름, 프로젝트 관리자, 예산을 저장한다.
각 테이블의 생성 SQL 문은 다음과 같다.
“`sql
CREATE TABLE department(
deptno int,
deptname varchar(32),
floor int,
PRIMARY KEY(deptno)
);
CREATE TABLE employee(
empno int NOT NULL,
empname varchar(32) UNIQUE,
title varchar(32) DEFAULT ‘사원’,
manager int,
salary int CHECK(salary < 6000000),
dno int DEFAULT 1 CHECK(dno IN (1,2,3,4,5,6)),
PRIMARY KEY(empno),
CONSTRAINT fk1 FOREIGN KEY(manager) REFERENCES employee(empno),
CONSTRAINT fk2 FOREIGN KEY(dno) REFERENCES department(deptno)
);
CREATE TABLE PROJEMP(
projno int not null,
projname varchar(30) unique,
pmanager int,
budget int
);
“`
테이블이 성공적으로 생성된 후, 다음과 같은 데이터가 삽입되었다.
sql
INSERT INTO DEPARTMENT VALUES (1, "영업", 8);
INSERT INTO DEPARTMENT (FLOOR, DEPTNAME, DEPTNO) VALUES (10, "기획", 2);
INSERT INTO DEPARTMENT VALUES (3, "개발", 9);
INSERT INTO DEPARTMENT VALUES (4, "총무", 7);
INSERT INTO EMPLOYEE VALUES (4377, "이성래", "사장", NULL, 5000000, 2);
INSERT INTO EMPLOYEE VALUES (3426, "박영권", "과장", 4377, 3000000, 1);
INSERT INTO EMPLOYEE VALUES (3011, "이수민", "부장", 4377, 4000000, 3);
INSERT INTO EMPLOYEE VALUES (1003, "조민희", "과장", 4377, 3000000, 2);
INSERT INTO EMPLOYEE VALUES (2106, "김창섭", "대리", 1003, 2500000, 2);
INSERT INTO EMPLOYEE VALUES (3427, "최종철", "사원", 3011, 1500000, 3);
INSERT INTO EMPLOYEE VALUES (1365, "김상원", "사원", 3426, 1500000, 1);
INSERT INTO PROJEMP VALUES (100, "날씨예보", 4377, 80000000);
INSERT INTO PROJEMP VALUES (101, "기숙사 배달", 3427, 60000000);
INSERT INTO PROJEMP VALUES (102, "홍보 앱", 2106, 100000000);
INSERT INTO PROJEMP VALUES (100, "오목 게임", 4377, 90000000);
이제 각 질의를 SQL로 표현하여 데이터베이스에서 필요한 정보를 검색하는 방법을 탐구한다.
SQL 질의 및 결과
질의문 1: 대리인 사원 정보 조회
대리 직급의 모든 사원들의 이름과 사원번호, 소속된 부서의 이름과 부서번호를 검색하는 질의는 다음과 같다.
sql
SELECT EMPNO, EMPNAME, DEPTNAME, DEPTNO
FROM employee AS E, department AS D
WHERE E.dno = D.deptno AND title = '대리';
이 질의는 대리인 사원 정보를 부서 정보와 결합하여 결과를 도출한다.
질의문 2: 프로젝트 관리자 정보 조회
각 프로젝트에 대해 프로젝트의 이름, 관리자의 이름 및 관리자가 소속된 부서의 이름을 검색하는 SQL 문은 다음과 같다.
sql
SELECT projname, empname, deptname
FROM employee AS E, department AS D, projemp AS P
WHERE E.dno = D.deptno AND E.empno = P.pmanager;
이 질의는 프로젝트와 사원, 부서 정보를 통합하여 명확한 결과를 제공한다.
질의문 3: 사원과 관리자 이름 조회
모든 사원들의 이름과 그들의 관리자의 이름을 검색하는 질의는 다음과 같다.
sql
SELECT E.empname AS 직원, M.empname AS 매니저
FROM employee AS E, employee AS M
WHERE E.empno = M.manager;
이 SQL 문은 자기 조인을 사용하여 사원과 그들의 관리자 정보를 함께 조회한다.
질의문 4: 특정 부서의 프로젝트 관리자 조회
부서번호 2에 근무하며 예산이 100,000,000원인 프로젝트를 관리하는 사원들의 이름과 급여를 조회하는 SQL 문은 다음과 같다.
sql
SELECT empname, salary
FROM employee AS E, department AS D, projemp AS P
WHERE E.dno = D.deptno AND E.empno = P.pmanager AND E.dno = 2 AND budget = 100000000;
이 질의는 조건에 맞는 사원의 정보를 추출한다.
질의문 5: 특정 층의 사원 이름 조회
9층에 위치한 부서에서 근무하는 사원들의 이름을 검색하는 질의는 다음과 같다.
sql
SELECT empname
FROM employee AS E, department AS D
WHERE E.dno = D.deptno AND D.floor = 9;
이 질의는 특정 층에 근무하는 사원 정보를 제공한다.
질의문 6: 급여 비교 조회
부서번호 1에 근무하는 사원들 중에서 부서번호 3에 근무하는 사원과 급여가 같은 사원들의 이름을 열거하는 SQL 문은 다음과 같다.
sql
SELECT empname, salary
FROM employee AS E
WHERE E.dno = 1 AND title = '사원' AND salary >= (SELECT salary FROM employee WHERE dno = 3 AND title = '사원');
이 질의는 서브쿼리를 활용하여 급여를 비교한다.
질의문 7: 급여가 높은 사원 조회
부서번호 3에 근무하지 않는 사원들 중 부서번호 3에 근무하는 모든 사원들의 급여보다 더 많은 급여를 받는 사원들의 이름을 열거하는 SQL 문은 다음과 같다.
sql
SELECT empname
FROM employee AS E
WHERE E.dno != 3 AND E.salary > (SELECT MAX(salary) FROM employee WHERE dno = 3);
이 SQL 문은 조건에 맞는 사원 정보를 효과적으로 추출한다.
질의문 8: 소속되지 않은 부서 조회
사원들이 한 명도 소속되지 않은 부서들의 이름을 검색하는 SQL 문은 다음과 같다.
sql
SELECT deptname
FROM department AS D
WHERE D.deptno NOT IN (SELECT dno FROM employee);
이 질의는 사원과 연결되지 않은 부서를 조회하는 데 사용된다.
결론
위의 SQL 질의를 통해 관계형 데이터베이스에서 정보를 효과적으로 검색하는 방법을 살펴보았다. 각 질의는 데이터베이스 구조와 테이블 간의 관계를 이해하는 데 도움이 되며, 실제 데이터베이스 관리에 적용할 수 있는 기초적인 지식을 제공한다. 데이터베이스 설계와 SQL 활용 능력을 지속적으로 발전시키는 것이 중요하다.