SQL 다루기_2편

오늘은 이전에 짜여진 테이블들을 바탕으로 예제를 통해 한 번 SQL을 진행해 보겠습니다.:

1. Retrieve the birthdate and address of the employee whose name is 'John B. Smith'.

'John B. Smith라는 인물의 생일과 주소를 검색하십시오.

select bdate, bname
from employee
where fname = 'john' and minit = 'B' and Lname = 'smith'

가장 쉬운 문법으로, 찾고자 하는 모든 정보가 한 테이블 내에 존재합니다.

2. Retrieve the name and address of all employees who work for the ‘Research' department.

Research 부서에 근무하는 직원들의 이름과 주소를 검색하십시오.

select fname, minit, lname, address
from employee, department
where dname = 'research' and dnumber = dno;

employee에 존재하는 테이블을 가져 와야 이름, 주소에 대해 접근이 가능하고, Research 부서에 대한 정보는 department 테이블 내에 존재합니다.
조건은 부서명이 'research'이며, department 내에 dnumber 컬럼이 employee 테이블의 dno를 참조하고 있기 때문에 같은 값을 맞춰줍니다.

3. For every project located in ‘Stafford’, list the project number, the controlling department number, and the department manager’s last name, address, and birth date.

'Stafford'에 위치하며, 해당 프로젝트의 번호, 관리 부서의 번호, 관리자의 마지막 이름, 주소, 생일을 나타내십시오.

select pnuber, dnum, lname, address, bdate
from employee, department, project
where plocation = 'stafford' and dnum = dnumber and mgr_ssn = ssn;

1. 해당되는 정보는 모두 employee와 department, project 테이블에서 찾을 수 있습니다.
2. project 테이블에서 stafford에 해당하는 plocation을 찾습니다.
3. stafford에 존재하는 plocation을 통해 dnum을 얻을 수 있습니다.
4. 찾은 dnum으로는 employee 테이블의 dno를 통해 근무지를 찾을 수 있습니다.
5. 찾은 dnum으로는 department 테이블의 dnumber를 통해 mgr_ssn을 찾을 수 있습니다.

4. For each employee, retrieve the employee’s first and last name and the first and last name of his or her immediate supervisor.

각각의 직원의 성과 마지막 이름, 그들 각각의 직속상관을 검색하십시오.

select e.fname, e.lname, s.fname, s.lname
from employee as e, employee as s
where e.super_ssn = s.ssn;

5. Select all EMPLOYEE Ssns (Q9) and all combinations of EMPLOYEE Ssn and DEPARTMENT Dname (Q10) in the database.

모든 사원의 ssn을 출력하고, employee 의 ssn과 department의 dname을 합친 db를 출력하십시오.

select ssn
from employee;

select ssn, bname
from employee, department;

6. Retrieve the salary of every employee (Q11) and all distinct salary values (Q11A).

모든 사원의 봉급을 검색하고 중복되는 값 없이 봉급을 출력하십시오.

select all salary from employee;

select distinct salary
from employee;

distinct는 목록에서의 중복값을 없애달라는 명령어 입니다.

7. Make a list of all project numbers for projects that involve an employee whose last name is ‘Smith’, either as a worker or as a manager of the department that controls the project.

직원이거나 프로젝트의 관리자로서, 마지막 이름이 'Smith'인 직원의 프로젝트 번호 목록을 출력하십시오.

주 목표는 pnumber를 찾는거지만 조건이 2개 입니다. 하나씩 살펴보도록 하겠습니다.

조건 1. 프로젝트의 관리자로서 이름이 'smith'인 직원

select distinct pnumber
from employee, department, project
where lname = 'smith' and dnum = dnumber and mgr_ssn = ssn;

조건 2. 직원으로서 이름이 'smith'인 직원

select distinct pnuber
from employee, project, works_on
where lname = 'smith' and pnumber = pno and essn = ssn;

둘을 합쳐줍니다.

union

위의 쿼리문에 대한 출력 결과는 없습니다. (관리자가 아니기 때문)

8. Retrieve all employees whose address is in Houston, Texas.

주소가 Houston, Texas 인 직원을 검색하십시오.

select address
from employee
where adress like '%houston, TX%';

like(문자열 패턴 검색) 와 %(: 앞에 뭐가 나오든 상관없이) 문자를 익혀두는 것이 목적입니다.

9. Find all employees who were born during the 1950s.

1950년대에 태어난 모든 직원을 검색하십시오.

select fname, minit, lname
from employee
where bdate like '__5_______';

언더바 하나당 한 글자 입니다. bdate의 형식은 'yyyy-mm-dd' 임을 참고하시면 좋습니다.

10. Show the resulting salaries if every employee working on the ‘ProductX’ project is given a 10% raise.

Product X 부서에 근무하는 직원들의 봉급을 10% 인상하십시오.

select e.fname, e.lname, 1.1 * e.salary as increased_sal
from employee as e, works_on as w, project as p
where p.pname = 'productX' and p.pnumber = w.pno and w.essn = e.ssn;

11. Retrieve all employees in department 5 whose salary is between $30,000 and $40,000.

5번 부서에서 근무하는 직원들 중 3만~4만$의 봉급을 받는 인원만 검색하십시오.

select *
from employee
where salary between 30000 and 40000 and dno = 5;

between 질의어에 대한 문제입니다.

12. Find the maximum salary, the minimum salary, and the average salary among all employees.

최대 및 최소의 봉급을 가지며, 전 직원의 평균 봉급을 검색하십시오.

select max(salary), min(salary), avg(salary)
from employee;

13. Retrieve the total number of employees in the company (Q3), and the number of employees in the 'Research' department (Q4).

회사의 전체 인원을 검색하고, Research 부서에 근무하는 인원을 검색하십시오.

select count(*)
from employee;

select count(*)
from employee, department
where dname = 'Research' and dnumber = dno;

count(*)

이 모든 행을 검색한다는 점을 이용한 문제입니다.

14. For each department, retrieve the department number, the number of employees in the department, and their average salary.

각각의 부서에서 부서 번호를 검색하고, 부서별 직원의 인원을 검색하고, 그 직원들의 봉급의 평균값을 구하십시오.

select dno, count(*), avg(salary)
from employee
group by dno;

group by의 기본적인 사용입니다. 뒤에 항상 select 문에 나오는 컬럼을 사용해야 합니다. 뒤에 나오는 절에 따라 그룹화가 됩니다.

15. For each project on which more than two employees work, retrieve the project number, project name, and the number of employees who work on that project.

2명 이상의 직원이 근무하는 프로젝트 중, 프로젝트의 번호, 이름, 그리고 몇명이 그 프로젝트에서 근무하는지 검색하십시오.

select pnumber, pname, count(*)
from project, works_on
where pnumber = pno
group by pnumber, pname
having count(*) > 2;

1. where 구문으로 project와 works_on 테이블을 기본키(pk)를 이용해서 조인시켜 줍니다.
2. 조건에서 요구하는 pnumber, pname, 2명 이상의 근무 인원을 차례대로 group by 하며, 조건이 존재하는 구절은 having 으로 처리합니다.

16. Retrieve a list of employees and the projects they are working on, ordered by department and, within each department, ordered alphabetically by last name, then first name.

직원들이 작업하는 프로젝트 이름과 직원을 검색하고, 각 부서의 이름과 함께 직원을 알파벳 순으로 oreder by 시키십시오.

select d.dname, e.fname, e.lname, p.pname
from employee as a, department as d, project as p, works_on as w
where d.dnumber = e.dno and p.pnumber = w.pno and w.essn = e.ssn
order by d.dname DESC e.fname ASC e.lname ASC;

order by 구문은 정렬할 때 사용합니다. 이 때 조건에 맞춰 알파벳 순으로 정렬하는 방법은 ASC를 넣으면 되고, 역순은 DESC를 넣어서 사용합니다.