COMPANY SQL EXAMPLE (BY oracle)

-- John Smith가 일하는 project number를 검색하라

select w.pno

from employee e, works_on w

where e.fname='John' and e.lname='Smith' and e.ssn = w.essn;

 

-- John Smith가 일하는 project 를 수행하는 사람들의fname, lname 를 검색하라. Hint: 중첩, IN

select distinct e.fname, e.lname

from employee e, works_on w

where w.pno IN (

    select w.pno

    from employee e, works_on w

    where e.fname='John' and e.lname='Smith' and e.ssn = w.essn)

    and w.essn = e.ssn;

 

-- ‘ProductX’ 프로젝트에 참여하는 모든 사원의 급여를 10% 올린 경우의 급여를 구하라.  attribute이름 alias할 것

select salary*1.1 as upsalary

from employee e, project p, works_on w

where p.pname = 'ProductX' and p.Pnumber = w.pno and w.essn = e.ssn;

 

-- ssn, first name, last name, 부양하는 명수를 검색하라.

Hint: 필요에 따라 grouping attributes를 여러 개 쓸 수 있다. 합쳐서 유일하기만 하다면.

select e.ssn, e.fname, e.lname, count(*)

from employee e, dependent d

where e.ssn = d.essn

group by e.ssn, e.fname, e.lname;

 

-- 프로젝트 번호 1, 2, 3 을 수행하는 사원의 이름, 주민등록번호를 검색하라.  Hint: 1,2,3중의 아무 하나를 수행하면 됨.

select distinct e.fname, e.lname, e.ssn

from employee e, works_on w

where (w.pno=1 or w.pno=2 or w.pno=3) and  w.essn = e.ssn;

 

-- 각 부서에 대해서 부서 번호, 부서에 속한 사원들의 수, 각 부서에 속한 사원들의 평균 급여를 구하라.

select e.dno, count(*), avg(salary)

from department d, employee e

where d.dnumber = e.dno

group by e.dno;

 

-- 각 프로젝트에 대해서 프로젝트 번호, 프로젝트 이름, 그 프로젝트에서 근무하는 사원들의 수를 검색하라.

select p.pnumber, p.pname, count(*)

from project p, employee e, works_on w

where p.pnumber = w.pno and w.essn = e.ssn

group by p.pnumber, p.pname;

 

-- 두 명 이상의 사원이 근무하는 각 프로젝트에 대해서 프로젝트 번호, 프로젝트 이름, 프로젝트에서 근무하는 사원수를 검색하라.

select p.pnumber, p.pname, count(*)

from project p, employee e, works_on w

where p.pnumber = w.pno and w.essn = e.ssn

group by p.pnumber, p.pname

having count(*)>=2;