7주차 실습. SQL 실습

1. 데이터베이스 구축하기

① 메모장에 아래의 내용을 입력하고 company_database.sql이라는 이름으로 working directory 하위에 저장한다.

CREATE TABLE EMPLOYEE
( Fname           VARCHAR(10)   NOT NULL,
  Minit           CHAR,
  Lname           VARCHAR(20)      NOT NULL,
  Ssn             CHAR(9)          NOT NULL,
  Bdate           DATE,
  Address         VARCHAR(30),
  Sex             CHAR(1),
  Salary          DECIMAL(5),
  Super_ssn       CHAR(9),
  Dno             INT               NOT NULL,
PRIMARY KEY   (Ssn));

CREATE TABLE DEPARTMENT
( Dname           VARCHAR(15)       NOT NULL,
  Dnumber         INT               NOT NULL,
  Mgr_ssn         CHAR(9)           NOT NULL,
  Mgr_start_date  DATE,
PRIMARY KEY (Dnumber),
UNIQUE      (Dname),
FOREIGN KEY (Mgr_ssn) REFERENCES EMPLOYEE(Ssn) );

CREATE TABLE DEPT_LOCATIONS
( Dnumber         INT               NOT NULL,
  Dlocation       VARCHAR(15)       NOT NULL,
PRIMARY KEY (Dnumber, Dlocation),
FOREIGN KEY (Dnumber) REFERENCES DEPARTMENT(Dnumber) );

CREATE TABLE PROJECT
( Pname           VARCHAR(15)       NOT NULL,
  Pnumber         INT               NOT NULL,
  Plocation       VARCHAR(15),
  Dnum            INT               NOT NULL,
PRIMARY KEY (Pnumber),
UNIQUE      (Pname),
FOREIGN KEY (Dnum) REFERENCES DEPARTMENT(Dnumber) );

CREATE TABLE WORKS_ON
( Essn            CHAR(9)           NOT NULL,
  Pno             INT               NOT NULL,
  Hours           DECIMAL(3,1)      NOT NULL,
PRIMARY KEY (Essn, Pno),
FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn),
FOREIGN KEY (Pno) REFERENCES PROJECT(Pnumber) );

CREATE TABLE DEPENDENT
( Essn            CHAR(9)           NOT NULL,
  Dependent_name  VARCHAR(15)       NOT NULL,
  Sex             CHAR,
  Bdate           DATE,
  Relationship    VARCHAR(8),
PRIMARY KEY (Essn, Dependent_name),
FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn) );

INSERT INTO EMPLOYEE 
VALUES      ('John','B','Smith',123456789,'1965-01-09','731 Fondren, Houston TX','M',30000,333445555,5),
            ('Franklin','T','Wong',333445555,'1965-12-08','638 Voss, Houston TX','M',40000,888665555,5),
            ('Alicia','J','Zelaya',999887777,'1968-01-19','3321 Castle, Spring TX','F',25000,987654321,4),
            ('Jennifer','S','Wallace',987654321,'1941-06-20','291 Berry, Bellaire TX','F',43000,888665555,4),
            ('Ramesh','K','Narayan',666884444,'1962-09-15','975 Fire Oak, Humble TX','M',38000,333445555,5),
            ('Joyce','A','English',453453453,'1972-07-31','5631 Rice, Houston TX','F',25000,333445555,5),
            ('Ahmad','V','Jabbar',987987987,'1969-03-29','980 Dallas, Houston TX','M',25000,987654321,4),
            ('James','E','Borg',888665555,'1937-11-10','450 Stone, Houston TX','M',55000,null,1);

INSERT INTO DEPARTMENT 
VALUES      ('Research',5,333445555,'1988-05-22'),
            ('Administration',4,987654321,'1995-01-01'),
            ('Headquarters',1,888665555,'1981-06-19');

INSERT INTO PROJECT 
VALUES      ('ProductX',1,'Bellaire',5),
            ('ProductY',2,'Sugarland',5),
            ('ProductZ',3,'Houston',5),
            ('Computerization',10,'Stafford',4),
            ('Reorganization',20,'Houston',1),
            ('Newbenefits',30,'Stafford',4);

INSERT INTO WORKS_ON 
VALUES     (123456789,1,32.5),
           (123456789,2,7.5),
           (666884444,3,40.0),
           (453453453,1,20.0),
           (453453453,2,20.0),
           (333445555,2,10.0),
           (333445555,3,10.0),
           (333445555,10,10.0),
           (333445555,20,10.0),
           (999887777,30,30.0),
           (999887777,10,10.0),
           (987987987,10,35.0),
           (987987987,30,5.0),
           (987654321,30,20.0),
           (987654321,20,15.0),
           (888665555,20,16.0);

INSERT INTO DEPENDENT 
VALUES      (333445555,'Alice','F','1986-04-04','Daughter'),
            (333445555,'Theodore','M','1983-10-25','Son'),
            (333445555,'Joy','F','1958-05-03','Spouse'),
            (987654321,'Abner','M','1942-02-28','Spouse'),
            (123456789,'Michael','M','1988-01-04','Son'),
            (123456789,'Alice','F','1988-12-30','Daughter'),
            (123456789,'Elizabeth','F','1967-05-05','Spouse');

INSERT INTO DEPT_LOCATIONS 
VALUES      (1,'Houston'),
            (4,'Stafford'),
            (5,'Bellaire'),
            (5,'Sugarland'),
            (5,'Houston');

ALTER TABLE DEPARTMENT
 ADD CONSTRAINT Dep_emp FOREIGN KEY (Mgr_ssn) REFERENCES EMPLOYEE(Ssn);

ALTER TABLE EMPLOYEE
 ADD CONSTRAINT Emp_dno FOREIGN KEY  (Dno) REFERENCES DEPARTMENT(Dnumber);
ALTER TABLE EMPLOYEE
 ADD CONSTRAINT Emp_super FOREIGN KEY  (Super_ssn) REFERENCES EMPLOYEE(Ssn);

② cmd 창에 아래의 명령을 입력한다.

create database week7;
use week7;
source ./company_database.sql;

③ select * 또는 desc를 이용해 sql 문이 잘 적용되었는지 확인한다.

2. Query 작성하기

아래의 데이터베이스 스키마를 참고하여 쿼리를 작성해보자.

1) 기초 단계

기초 수준의 쿼리는 막힘없이 작성할 수 있어야 한다.

① 모든 사원의 이름, 주소, 급여를 출력하는 쿼리

select Fname, Minit, Lname, Address, Salary from Employee;

② 회사의 총 사원 수를 출력하는 쿼리

select count(*) from Employee;

③ 3만 이상의 급여를 받는 사원들의 이름과 급여를 출력하는 쿼리

select Fname, Minit, Lname, Salary 
from Employee e where e.salary >= 30000;

④ 3만 이상, 5만 이하의 급여를 받는 사람들의 이름과 급여를 출력하는 쿼리

select Fname, Minit, Lname, Salary 
from Employee e where e.salary between 30000 and 50000;

⑤ Houston에서 진행 중인 프로젝트의 이름과 번호를 출력하는 쿼리

select Pname, Pnumber from Project p where p.Plocation = 'Houston';

⑥ 급여가 3만에서 4만 사이에 있는 5번 부서 소속의 모든 사원의 이름과 급여를 출력하는 쿼리

• 괄호는 생략해도 무방하다.

select Fname, Minit, Lname, Salary 
from Employee e 
where (e.salary between 30000 and 40000) and (e.Dno = 5);

⑦ 모든 사원을 급여(높은 순서), 생년월일(나이가 많은 순서) 기준으로 정렬하여 출력하는 쿼리

select * from Employee e order by e.Salary desc, e.Bdate asc;

⑧ 프로젝트 번호 1, 2, 3에서 일하는 사원의 SSN을 출력하는 쿼리

• 일반적으로, SSN이 중복되는 것은 원하지 않을 것이다.

select distinct w.Essn from works_on w, project p
where p.Pnumber in(1, 2, 3) and p.PNumber=w.Pno;

⑨ 사원의 급여의 합, 최고 급여, 최저 급여, 평균 급여를 출력하는 쿼리

select sum(e.salary) as total_salary, max(e.salary) as max_salary
min(e.salary) as min_salary, avg(e.salary) as avg_salary 
from Employee e; 

• 참고로 alias를 지정하지 않으면, 명령어자체가 필드명이 되어버린다.
  

2) 응용 단계

① Newbenefits 프로젝트에 참여하는 모든 사원의 급여를 10% 올린 경우의 급여(Increased_Sal)를 출력하는 쿼리

select Fname, Lname, Salary*1.1 as Increased_Sal
from Employee e, Works_on w, Project p
where p.Pname='Newbenefits' 
and p.Pnumber = w.Pno and w.Essn = e.Ssn; 

② 각 부서에 대해서 부서 이름과 근무하는 사원의 수를 출력하는 쿼리

select d.Dname, count(*) as employee_count
from Department d, Employee e
where e.Dno = d.Dnumber
group by d.Dname;

③ 각 부서에 대해서 부서이름, 부서에 소속된 사원의 수와 최고 급여와 평균 급여를 출력하는 쿼리

select Dname, count(*) as employee_count,
max(e.Salary) as max_sal, avg(e.salary) as avg_sal
from Department d, Employee e
where e.Dno = d.Dnumber
group by d.Dname;

④ 프로젝트 번호, 프로젝트 이름, 그 프로젝트에서 근무하는 사원들의 이름을 출력하는 쿼리

select p.Pnumber, p.Pname, e.Fname, e.Lname
from Project p, Employee e, Works_on w
where p.Pnumber = w.Pno and w.Essn = e.Ssn

⑤ 세 명 이상의 사원이 근무하는 프로젝트에 대해서 프로젝트 번호, 프로젝트 이름, 그 프로젝트에서 근무하는 사원들의 수를 출력하는 쿼리

select p.Pnumber, p.Pname, count(*) as employee_count
from Project p, Works_on w, Employee e
where p.Pnumber = w.Pno and w.Essn = e.Ssn
group by p.Pnumber, p.Pname
having count(*) >= 3;

⑥ 프로젝트에 대해서 프로젝트 번호, 프로젝트 이름, 5번 부서에 속하면서 프로젝트에서 근무하는 사원의 수를 출력하는 쿼리

select p.Pnumber, p.Pname, count(*) as employee_count
from Project p, Department d, Employee e
where d.Dnumber = e.Dno 
and p.Dnum = d.Dnumber
and d.Dnumber =5
group by p.Pname;

⑦ 3명 이상의 사원이 근무하는 각 부서에 대해서 부서 번호와 40,000달러가 넘는 급여를 받는 사원의 ssn 및 salary를 출력하는 쿼리

select d.Dnumber, e.Ssn, e.Salary
from Department d, Employee e
where d.Dnumber = e.Dno 
and e.Salary >= 40000
and d.Dnumber in (select Dno 
				  from Employee
				  group by Dno
                  having count(*) >= 3);

⑧ 부양가족이 있는 사원들의 이름과 부양가족의 이름을 출력하는 쿼리

select e.Fname, e.Minit, e.Lname, d.dependent_name 
from Employee e, Dependent d
where e.Ssn in (select Essn from dependent) and e.Ssn=d.Essn;