SQL 연습 문제 ②

DEPARTMENT TABLE

DEPTNO	DEPTNAME	MANAGER
10	전산팀	1001
20	회계팀	1004
30	영업팀	1007
40	총무팀	1011
50	인사팀	1013

EMPLOYELL TABLE

EMPNO	NAME	PHONENO	ADDRESS	SEX	POSITION	SALARY	DEPTNO
1001	홍길동1	010-111-1001	울산1	남	팀장	7000000	10
1002	홍길동2	010-111-1002	울산2	남	팀원1	4000000	10
1003	홍길동3	010-111-1003	울산3	남	팀원2	3000000	10
1004	홍길동4	010-111-1004	부산1	여	팀장1	6000000	20
1005	홍길동5	010-111-1005	부산2	남	팀원1	3500000	20
1006	홍길동6	010-111-1006	부산3	남	팀원2	2500000	20
1007	홍길동7	010-111-1007	서울1	남	팀장	5000000	30
1008	홍길동8	010-111-1008	서울2	남	팀원1	4000000	30
1009	홍길동9	010-111-1009	서울3	남	팀원2	3000000	30
1010	홍길동10	null	서울4	남	팀원3	2000000	30
1011	홍길동11	010-111-1011	대구1	여	팀장	5500000	40
1012	홍길동12	010-111-1012	대구2	남	팀원1	2000000	40
1013	홍길동13	010-111-1013	제주1	남	팀장	6500000	50
1014	홍길동14	010-111-1014	제주2	남	팀원1	3500000	50

PROJECT TABLE

PROJNO	PROJNAME	DEPTNO
101	빅데이터구축	10
102	IFRS	20
103	마케팅	30

WORKS TABLE

PROJNO	EMPNO	HOURSWORKED
101	1001	800
101	1002	400
101	1003	300
102	1004	700
102	1005	500
102	1006	200
103	1007	500
103	1008	400
103	1009	300
103	1010	200

※ 해당 교재에 해설 및 풀이가 없으므로, 제가 푼 풀이가 틀릴 수도 있습니다.

 

1. 테이블을 생성하는 create문을 작성하고 구조를 보이시오.

--구조보기
desc department;
desc employee;
desc project;
desc works;
--제약조건보기
select *
  from user_constraints
 where table_name in ('DEPARTMENT','EMPLOYEE','PROJECT','WORKS')
   AND constraint_type = 'P'; 
   
select * 
  from user_indexes;

 

2. 여자 사원의 이름, 연락처, 주소를 보이시오. 

select name "이름" ,phoneno "연락처" ,address "주소"
  from employee
 where sex = '여';

 

3. 팀장의 급여를 10%인상해 보이시오. (단, department테이블을 활용하시오.)

update employee t1
set salary = salary * 1.1
where empno = ( select t2.manager
                 from department t2
                where t2.deptno = t1.deptno);                
update employee t1
   set salary = salary * 1.1
 where empno in ( select t2.manager
                    from department t2);
commit;
select * from employee;

 

4. 사원 중 같은 성(姓)을 가진 사람이 몇 명이나 되는지 성별 인원수를 구하시오.

select substr(t1.name,1,1) "성", count(*) "인원수"
from employee t1
group by substr(t1.name,1,1);

 

5. ‘영업팀’ 부서에서 일하는 사원의 이름, 연락처, 주소를 보이시오. (단 연락처 없으면 ‘연락처 없음’ , 연락처 끝4자리 중 앞2자리는 별표 * 로 표시하시오. 예) 010-111-**78 )

select t2.name "이름", 
       nvl2(phoneno,substr(phoneno,1,8) || '**' || substr(phoneno,11,2),'연락처없음') "연락처", 
       t2.address "주소"
  from department t1, employee t2
 where t1.deptno = t2.deptno
   and t1.deptname = '영업팀';

 

6. ‘홍길동7’ 팀장(manager) 부서에서 일하는 팀원의 수를 보이시오. 

select count(*) "팀원 수"
  from employee
 where deptno = ( select t2.deptno
                    from department t1, employee t2
                   where t1.deptno = t2.deptno
                     and t2.name = '홍길동7' )
   and name <> '홍길동7';

 

7. 프로젝트에 참여하지 않은 사원의 이름을 보이시오.

left outer join 사용

  select t1.name
    from employee t1, works t2
   where t1.empno = t2.empno(+)
     and t2.projno is null;

 

상관쿼리 사용

  select t1.name
    from employee t1
   where not exists ( select *
                        from works t2
                       where t2.empno = t1.empno );

not in 사용

  select name
    from employee
   where empno not in ( select distinct empno
                          from works );

차집합 사용

select name
  from employee
 where empno in (  select empno
                     from employee
                   minus
                    select distinct empno
                      from works );

 

8. 급여 상위 TOP 3를 순위와 함께 보이시오.

select rownum, t1.*
  from (  select *
            from employee
        order by salary desc ) t1
 where rownum <=3;

 

9. 사원들이 일한 시간 수를 부서별, 사원 이름별 오름차순으로 보이시오. 

    select t1.deptname "부서명", t2.name "이름", nvl(sum(t3.hoursworked),0) "일한 시간"
      from department t1, employee t2, works t3
     where t2.deptno = t1.deptno(+)
       and t2.empno  = t3.empno(+)
  group by t1.deptname, t2.name
  order by t1.deptname, t2.name;

 

10. 2명 이상의 사원이 참여한 프로젝트의 번호, 프로젝트명, 사원의 수를 보이시오. 

  select t2.projno "프로젝트 번호", 
         t2.projname "프로젝트명", 
         count(*) "사원수"
    from works t1, project t2
   where t1.projno = t2.projno 
group by t2.projno, t2.projname
  having count(*) >= 2;

 

11. 3명 이상의 사원이 있는 부서의 사원 이름을 보이시오. 

select t3.deptname "부서명", t4.name "이름"
  from department t3, employee t4
 where t3.deptno = t4.deptno
   and t3.deptno in  ( select t1.deptno
                         from department t1, employee t2
                        where t1.deptno = t2.deptno 
                     group by t1.deptno
                       having count(*) >= 3 );

 

12. 프로젝트에 참여시간이 가장 많은 사원과 적은 사원의 이름을 보이시오.

  select t2.empno "사번", t1.name "이름", sum(t2.hoursworked) "시간"
    from employee t1 , works t2
   where t1.empno = t2.empno 
group by t2.empno, t1.name
  having sum(t2.hoursworked) in ( (select max(sum(hoursworked))
                                     from works
                                 group by empno),
                                  (select min(sum(hoursworked))
                                     from works
                                 group by empno));


  select empno "사번",
         (select name 
            from employee 
           where empno = works.empno) "이름",
         sum(hoursworked) "시간"
    from works
group by empno
  having sum(hoursworked) in    ( (select max(sum(hoursworked))
                                     from works
                                 group by empno),
                                  (select min(sum(hoursworked))
                                     from works
                                 group by empno));

 

합집합 사용

 select t2.empno, t1.name, sum(t2.hoursworked)
    from employee t1 , works t2
   where t1.empno = t2.empno 
group by t2.empno, t1.name
  having sum(t2.hoursworked) in ( select max(sum(hoursworked))
                                     from works
                                 group by empno )
union                                 
  select t2.empno, t1.name, sum(t2.hoursworked)
    from employee t1 , works t2
   where t1.empno = t2.empno 
group by t2.empno, t1.name
  having sum(t2.hoursworked) in ( select min(sum(hoursworked))
                                     from works
                                 group by empno );

 

13. 사원이 참여한 프로젝트에 대해 사원명, 프로젝트명, 참여시간을 보이는 뷰를 작성하시오.

create or replace view vw_proj (name, projectname, hours)
as
    select t2.name, t3.projname, sum(t1.hoursworked)
      from works t1, employee t2, project t3
     where t1.empno  = t2.empno
       and t1.projno = t3.projno
  group by t2.name, t3.projname     
  order by t2.name, t3.projname;
select name "이름",projectname "프로젝트명",hours "참여시간"
  from vw_proj;

 

14. EXISTS 연산자로 ‘빅데이터 구축’ 프로젝트에 참여하는 사원의 이름을 보이시오.

select t1.name "이름" 
  from employee t1
 where exists ( select *
                  from project t2, works t3
                 where t2.projno = t3.projno
                   and t3.empno  = t1.empno 
                   and t2.projname = '빅데이터구축' );

 

15. employee 테이블의 name,phoneno 열을 대상으로 인덱스를 생성하시오. (단.인덱스명은 ix_employee2)

create index ix_employee2 on employee(name,phoneno);
select *
  from user_indexes
 where table_name = 'EMPLOYEE' 
   and index_name = 'IX_EMPLOYEE2';

 

Reference : 오라클로 배우는 데이터베이스 개론과 실습

https://www.hanbit.co.kr/store/books/look.php?p_code=B2082711299

IT CookBook, 오라클로 배우는 데이터베이스 개론과 실습(2판)