SQL 실습(1)

---

DB schema

중요한 SQL문에 대해서 공부해보자. 우선 SQL문을 테스트 해보기 위한 COMPANY DB schema를 만들어두자.

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);

 

---

 

1. 이름이 ‘John B. Smith’인 사원의 생일(BDATE)과 주소(ADDRESS)를 검색하시오.

SELECT BDATE, ADDRESS
FROM EMPLOYEE
WHERE FNAME=‘John’ AND MINIT=‘B’ AND LNAME=‘Smith’;

2. dnumber=dno인 모든 튜플을 선택하라. (동등 조인)

SELECT *
FROM EMPLOYEE, DEPARTMENT
WHERE DNUMBER=DNO ;

inner join을 이용한 똑같은 표현

SELECT *
FROM EMPLOYEE
INNER JOIN DEPARTMENT
ON employee.dno = department.dnumber;

 

3. ‘Research’ 부서에서 일하는 모든 사원의 이름(FNAME, LNAME)과 주소를 검색하시오.

SELECT FNAME,LNAME, ADDRESS
FROM EMPOYEE, DEPARTMENT
WHERE DNAME='Research' AND DNUMBER = DNO;

 

4. ‘Stafford’에 위치한 모든 프로젝트에 대하여 프로젝트 번호, 담당부서 번호, 부서 관리자의성, 주소, 생일을 검색하라.

SELECT PNUMBER, DNUM, LNAME, ADDRESS, BDATE
FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE DNUM=DNUMBER AND MGR_SSN=SSN AND PLOCATION=‘Stafford’;

조인조건 DNUM=DNUMBER는 프로젝트와 담당 부서를 조인하고 조인조건 MGR_SSN=SSN은 부서와 담당 관리자를 조인한다.

 

5. 종업원에 대해, 종업원의 성과 이름, 직속 감독자의 성과 이름을 검색하시오.

SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME
FROM EMPLOYEE E, EMPLOYEE S // EMPLOYEE에 대한 별명
	 //EMPLOYEE AS E, EMPLOYEE AS S 와 같다고 볼 수 있다.
WHERE E.SUPER_SSN=S.SSN;

위 예는 EMPLOYEE에 대해서 두 개의 별명(alias) E와 S를 선언하여 사용한다.

6. 데이터베이스에서 EMPLOYEE의 모든 SSN을 검색하라.

SELECT SSN
FROM EMPLOYEE;

 

7. EMPLOYEE의 SSN과 DEPARTMENT의 DNAME의 모든 조합을 선택하시오.

SELECT SSN, DNAME
FROM EMPLOYEE, DEPARTMENT;

 

8. 중복된 투플의 삭제

SELECT 항목에서 키워드 DISTINCT를 사용한다. DISTINCT는 질의 결과에서 유일한 투플들만 남기라는 의미의 키워드이다.

//모든 사원의 급여를 검색하라. (ALL 생략 가능)
SELECT ALL SALARY
FROM EMPLOYEE ;

//모든 사원의 구별되는 급여를 검색하라. 
SELECT DISTINCT SALARY
FROM EMPLOYEE ;

 

9. SELECT와 결합된 INSERT 명령

INSERT INTO WORKS_ON_INFO (EMP_NAME, PROJ_NAME, HOURS_PER_WEEK)
SELECT E.LNAME, P.PNAME, W.HOURS
FROM PROJECT P, WORKS_ON W, EMPLOYEE E
WHERE P.PNUMBER=W.PNO AND W.ESSN=E.SSN;

EMP_NAME, PROJ_NAME, HOURS_PER_WEEK이 각각 E.LNAME, P.PNAME, W.HOURS을 선택해서 넣어주는 것이다.

 

10. ‘Research’ 부서(DNO=5)에 있는 모든 종업원들의 봉급을 10％ 인상하라.

UPDATE EMPLOYEE
SET SALARY=SALARY*1.1
WHERE DNO = 5;

---