관계 대수와 SQL 연습 문제

DBMS에서 아래의 SQL문을 실행한다.

USE practice_chapter4;

#DROP TABLE employee;

#DROP TABLE department;

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

);

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로 표현하라.

EMPLOYEE(EMPNO, EMPNAME, TITLE, MANAGER, SALARY, DNO)

DEPARTMENT(DEPTNO, DEPTNAME, FLOOR)

PROJEMP(PROJNO, PROJNAME, PMANAGER, BUDGET)

(1) 직급이 대리인 모든 사원들의 이름과 사원번호, 이들이 소속된 부서의 이름과 부서번호를 검색하라.

select EMPNO, EMPNAME, DEPTNAME, DEPTNO

from employee as E, department as D

where E.dno = D.deptno and title = '대리';

(2) 각 프로젝트에 대하여 프로젝트의 이름, 관리자의 이름, 그 관리자가 소속된 부서의 이름을 검색하라.

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) 모든 사원들에 대하여 이름과 관리자의 이름을 검색하라.

select E.empname as 직원, M.empname as 매니저

from employee as E, employee as M

where E.empno = M.manager;

(4) 부서번호 2에 근무하면서 프로젝트 예산이 100,000,000원인 프로젝트를 관리하는 사원들의 이름과 급여를 검색하라.

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;

여기서 사원들의 이름이라고 하였는데 employee 테이블 상에 사원은 없다...? 김창섭의 직급은 대리다.

(5) 9층에 위치한 부서에서 근무하는 사원들의 이름을 검색하라.

select empname

from employee as E, department as D

where E.dno = D.deptno and D.floor = 9;

(6) 부서번호 1에 근무하는 사원들 중에서 적어도 부서번호 3에 근무하는 어떤 한 사원만큼 급여를 받는 사원들의 이름을 열거하라.

select empname, salary

from employee as E

where E.dno = 1 and title = '사원' and

(select salary

    from employee as E

    where E.dno = 3 and title = '사원');

(7) 부서번호 3에 근무하지 않는 사원들 중에서 부서번호 3에 근무하는 모든 사원들의 급여보다 많은 급여를 받는 사원들의 이름을 열거하라.

select empname

from employee as E

where E.dno != 3 and E.salary >

(select max(salary)

    from employee as E

    where E.dno = 3);

(8) 사원들이 한 명도 소속되지 않은 부서들의 이름을 검색하라.