DB 정리 3 - Company 스키마, SQL 질의문 연습, 데이터

DB 정리 3 - Company 스키마, SQL 질의문 연습, 데이터


1. Company Schema

2. SQL 질의문
#1. 이름이 John B.Smith 인 사원의 생년월일과 주소를 검색 하라.
select Bdate, Address
from employee
where Fname='John' and Minit = 'B' and Lname='Smith';

#2. Research 부서에서 근무하는 모든 사원의 이름과 주소를 검색하라.
select e.Fname, e.minit, e.lname, e.address
from employee e, department d
where e.dno = d.dnumber and d.dname='research';

#3. Stafford에 위치한 모든 프로젝트에 대해서 프로젝트 번호, 담당부서 번호, 부서관리자 성, 주소, 생년월일을 검색하라.
select p.pnumber, p.dnum, e.lNAME, e.ADDRESS, e.BDATE
from department d, project p, employee e
where p.plocation='Stafford' and e.ssn=d.mgrssn and p.dnum=e.dno;

#4. 각 사원에 대해 사원의 이름과 성, 직속상사의 이름과 성을 검색하라.
#e1의 ssn으로 e2의 superssn을 매칭시켜가면서 찾는 듯.
select e1.fname, e1.lname, e2.fname, e2.lname
from employee e1, employee e2
where e1.ssn=e2.superssn;

#5. employee의 ssn과 department의 dname의 모든 조합을 선택하라.
select ssn, dname
from employee, department;

#6. 일반직원이든 프로젝트를 담당하는 부서의 관리자이든 간에 성이 Smith인 사원을 포함하는 모든 프로젝트에 대해서 프로젝트 번호의 리스트를 검색하라.
(select distinct w.pno
from employee e, works_on w
where e.lname='smith' and e.ssn=w.essn)
union
(select distinct w.pno
from employee e, works_on w, department d
where e.lname='smith' and d.mgrssn=w.essn and d.mgrssn=e.ssn);

(select distinct pnumber
from project, department, employee
where dnum=dnumber and mgrssn=ssn and lname='smith')
union
(select distinct pnumber
from project, works_on, employee
where pnumber=pno and essn=ssn and lname='smith');

#7. 주소가 Houston, Texas 인 모든 사원을 검색하라.
select *
from employee
where address LIKE '%Houston, TX%';

#8. 1950년대에 태어난 모든 사원을 검색하라.
select *
from employee
where BDATE LIKE '195%';

select *
from employee
where BDATE LIKE '__5_______'; #_가 한글자

#9. ProductX 프로젝트에 참여하는 모든 사원의 급여를 10% 올린 경우의 급여를 구하라.
select 1.1*(salary)
from project p, works_on w, employee e
where p.pname='ProductX' and p.pnumber=w.pno and e.ssn=w.essn;

#10. 급여가 30,000달러에서 40,000 달러 사이에 있는 5번 부서의 모든 사원을 검색하라.
select *
from employee
where (salary between 30000 and 40000) and dno = 5;

#11. 사원 및 각 사원이 근무하는 프로젝트들의 리스트를 검색하는데, 부서 이름 순서대로, 그리고 각 부서 내에서는 사원의 성과 이름의 알파벳 순서대로 구하라.
# d.dnumber=p.dnum로 조인 했으나 결과가 잘못 나옴.
-- 테이블 4개를 그림으로 그려보시면 이해가 빠를것 같습니다 간략하게 정리해보면,
-- employee (n) --------- (1) department (1) -------- (n) project
-- (1) | __________________(n) work_on (n) _______________| (1)
-- works_on 은 m:n 을 해소하기 위한 교차 엔터티므로 join 의 연결 고리가 됨.
-- 요건에 필요한 테이블이 4개이므로 조인은 3번만 하면 되는데,
-- project name, employee name 이 나와야 하므로 employee와 project는 works_on 을 사용하여 연결하면 되고
-- 나머지 요건인 project 참여 employee의 department 명을 알기위한 조인은
-- employee.dno = department.dnumber 가 됨.
-- 만약 department.dnumber = project.dnum 을 하게되면
-- project 참여한 department 가 나오므로 다수의 중복이 발생할 수 있고, 의도와는 다른 결과가 나오게됨.
-- 즉, employee에는 사원들이 중복없이 있으므로 employee것으로 department를 접근하면 해당되는 부서를 하나만 찾음.
-- project로 접근하면 project안에서도 여러 부서번호가 있으니까 중복된것이 나타남.
-- project 에는 project 진행 부서번호가 들어오니 부서번호가 중복될수 있어 같은값이 여러개 나올 수 있음.
-- 1:n 구조에서 조인시 중복 건이 나와 distinct 처리를 하는 이유가 이러한 경우

select d.dname, e.lname, e.fname, p.pname
from works_on w, project p, employee e, department d
where w.pno=p.pnumber and e.ssn=w.essn and d.dnumber=p.dnum
order by d.dname asc, e.lname, e.fname;

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

#12. 상사가 없는 모든 사원의 이름을 검색하라.
select lname, fname
from employee
where SUPERSSN is null;

#13. John Smith가 일하는 어떤 프로젝트의 번호, 시간 조합과 동일한 모든 사원의 주민등록번호를 검색하라.
select distinct(essn)
from works_on
where (pno, hours) in (select w.pno, w.hours
                                from works_on w, employee e
                              where e.fname='John' and e.lname='Smith' and e.ssn=w.essn);

#14. 부양가족의 성과 성별이 같은 사원들의 이름을 검색하라.
#근데 검색결과 아무것도 없음.
# 바로 밑에 있는건 내가 한것 그 아래 있는건 책의 것. 둘다 결과가 아무것도 안나옴.
select fname, lname
from employee e
where e.ssn in (select d.essn
                     from dependent d
                       where e.fname=d.DEPENDENT_NAME and e.sex=d.sex);

select e.fname, e.lname
from employee e
where e.ssn in (select essn
                     from dependent
                       where e.fname=dependent_name and e.sex=sex);

#14번과 문제는 같지만 다른 쿼리들.
# e.ssn=d.essn 이건 없어도 될것 같음.
select e.fname, e.lname
from employee e, dependent d
where e.ssn=d.essn and e.sex=d.sex and e.fname=d.dependent_name;

select e.fname, e.lname
from employee e
where exists ( select *
                     from dependent
                     where e.ssn=essn and e.sex=sex and e.fname=dependent_name);

#15. 5번 부서가 담당하는 모든 프로젝트에 근무하는 사원들의 이름을 검색하라.
select distinct e.fname, e.lname
from employee e, project p, works_on w
where p.dnum=5 and p.pnumber=w.pno and e.ssn=w.essn;

#16. 부양가족이 없는 종업원의 이름을 검색하라.
# 서브쿼리에 없는 사람(not exists)은 부양가족이 없는 사람.
select e.fname, e.lname
from employee e
where not exists (select *
                       from dependent d
                         where d.essn = e.ssn);

#17. 부양가족이 적어도 한명 이상 있는 관리자의 이름을 검색하라.
select e.fname, e.lname
from employee e, department d
where d.mgrssn = e.ssn and exists (select *
                                                      from dependent dp
                                                      where d.mgrssn=dp.essn);

select fname, lname
from employee
where exists (select * from dependent where ssn=essn)
        and
        exists (select * from department where ssn=mgrssn);
      
#18. 프로젝트 1,2,3에서 일하는 사원의 사원번호를 검색하라.
select distinct essn
from works_on
where pno in (1,2,3);

#19. 모든 사원의 급여의 합, 최고 급여, 최저 급여, 평균 급여를 구하라.
select sum(salary), max(salary), min(salary), avg(salary)
from employee;

#20. 'Research' 부서에 근무하는 모든 사원의 급여의 합, 최고급여, 최소급여, 평균급여를 구하라.
select sum(salary), max(salary), min(salary), avg(salary)
from employee e, department d
where d.dname='Research' and d.dnumber=e.dno;

#21. 회사의 총 사원 수를 구하라.
select count(*)
from employee;

#22. 'Research'부서에서 근무하는 총 사원수를 검색하라.
select count(*)
from employee e, department d
where d.dname='Research' and d.dnumber=e.dno;

#23. 서로 다른 급여들의 개수를 구하라.
select count(distinct SALARY)
from employee;

#24. 둘 이상의 부양가족이 있는 모든 사원의 이름을 검색하라.
select e.fname, e.lname
from employee e
where (select count(*) from dependent d where d.essn=e.ssn)>=2;

#25. 각 부서에 대해서 부서번호, 부서에 속한 사원들의 수, 각 부서에 속한 사원들의 평균 급여를 구하라.
select e.dno, count(*), avg(salary)
from employee e
group by e.dno;

#26. 각 프로젝트에 대해서 프로젝트 번호, 프로젝트 이름, 그 프로젝트에서 근무하는 사원들의 수를 검색하라.
select p.pnumber, p.pname, count(w.pno)
from project p, works_on w
where p.pnumber=w.pno
group by p.pnumber, p.pname;

select p.pnumber, p.pname, count(*)
from project p, works_on w
where p.pnumber=w.pno
group by p.pnumber;

#27. 두명 이상의 사원이 근무하는 각 프로젝트에 대해서 프로젝트 번호, 프로젝트 이름, 프로젝트에서 근무하는 사원의 수를 검색하라.
select p.pnumber, p.pname, c
from project p, (select w.pno, count(*) c from works_on w group by w.pno) temp
where p.pnumber = temp.pno and temp.c > 2;

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

#28. 각 프로젝트에 대해서 프로젝트 번호, 프로젝트 이름, 5번 부서에 속하면서 프로젝트에서 근무하는 사원의 수를 검색하라.
select w.pno, p.pname, count(*)
from employee e, works_on w, project p
where e.dno=5 and w.essn=e.ssn and p.pnumber = w.pno
group by w.pno, p.pname;

#29. 6명 이상의 사원이 근무하는 각 부서에 대해서 부서 번호와 40000달러가 넘는 급여를 받는 사원의 수를 검색하라.
select count(*)
from employee p
where p.salary>40000 and exists (select * from employee where count(distinct(dno)) >=6 ); # 이건 에러남. count는 where서 못쓰나 봄

select d.dnumber, count(*)
from employee p, department d
where d.dnumber=dno and p.salary>40000 and p.dno in (select dno from employee group by dno having count(*)>5)
group by d.dnumber;


3. 데이터