Second lab (제출기한 : 11월 15일(목) 까지) 과제 제출 게시판 주소 : (나)반 – http://203.253.22.141/board/zboard.php?id=DB2 과제 제출 게시판 주소 : (다)반 – http://203.253.22.141/board/zboard.php?id=DB3 게시판에 과제를 올릴 때 반드시 비밀글로 작성하시기 바랍니다. (그러지 않을 시 치팅으로 간주) 글의 제목은 “이름(학번)_반_DB과제_#과제번호”로 하시고, 예) 홍길동(20020001)_나_DB과제_#2 쿼리문을 기입한 문서 파일(한글, 워드, 메모장 모두 사용 가능)을 “이름(학번).zip” 파일로 압축하여 첨부해 주세요. 예) 홍길동(20020001).zip 늦게 제출하면 감점 처리 하겠습니다. 기본 SELECT 구문의 작성 방법에 대해 연습해 보도록 하겠습니다. 문제를 해결하기 위한 SQL문을 작성하여 제출하여 주십시오 실습환경 DB2 UDB V9.1 for Windows 데이터베이스 명 : SAMPLE SAMPLE DATABASE는 데이터베이스를 설치후 db2sampl 이라는 명령어를 사용하면 기본적으로 생성되는 데이터베이스입니다. 실습을 돕기 위해서 sample 데이터베이스에 들어있는 테이블들의 정보를 예제 테이블로 첨부하였습니다. 테이블의 컬럼정보를 참고하여 실습을 진행하기 바랍니다.

예제 테이블

예제 테이블

예제 테이블

예제 테이블

예제 테이블

기본 SELECT 구문 1. EMPLOYEE 테이블의 모든 행을 선택하십시오. => 2. PROJECT 테이블에서 프로젝트명(PROJNAME), 시작일(PRSTDATE), 종료일(PRENDATE)을 가장 최근에 종료된 프로젝트 순서대로 선택하십시오. 3. EMPLOYEE 테이블에서 SALARY+BONUS+COMM 한 값을 TOTAL_PAY 라고 재명명해서 읽어오십시오. 결과의 순서는 TOTAL_PAY가 적은 사람 순서입니다.

컬럼 함수와 Group By구문 1. EMPLOYEE 테이블에서 성별(SEX)이 여자(‘F’)는 몇 명인지 조회해 보십시오. => 2. EMPLOYEE 테이블에서 여자(‘F’) member가 적어도 한 명 이상인 부서가 몇 군데인지 조회해 보십시오. 3. EMPLOYEE 테이블에서 같은 JOB CODE를 가진 각 그룹에서 JOB CODE와 최소 SALARY값과 최대 SALARY값을 선택하십 시오. 단 최대 SALARY값이 27000보다 크거나 같은 JOB이면서 해당 JOB이 1건보다 많은 경우만 선택하십시오.

스칼라함수와 Case구문 1. PROJECT 테이블에서 프로젝트이름(PROJNAME)이 ‘OPERATION’으로 시작하는 모든 행을 선택하십시오. => 2. PROJECT 테이블에서 프로젝트를 시작한 해와 프로젝트가 끝난 해가 같은 모든 행을 선택하십시오. 3. PROJECT 테이블에서 프로젝트가 1년 이내에 종료된 모든 행을 선택하십시오. 4. DEPARTMENT 테이블에 있는 모든 행에서 모든 값을 선택할 때 부서 관리자(MGRNO)가 빠지는 경우 (즉, 널(NULL)일 경우), 'ABSENT' 값이 표시되게 조회문을 작성하십시오. 5. EMPLOYEE 테이블에 있는 모든 행에서 직원 번호(EMPNO)와 급여(SALARY)를 선택할 때 급여가 빠지는 경우 (즉, 널(NULL)일 경우), 0 값이 표시되게 조회문을 작성하십시오. 6. 부서 번호의 첫 번째 문자가 조직의 부서일 경우, CASE 표현식을 사용하여 직원번호와 Lastname과 각 직원이 속하는 부서의 완전한 이름을 표시하게 조회문을 작성하십시오. (A:Administration,B:Human Resources, C:Accounting, D:Design, E:Operations) 7. EMPLOYEE 테이블에서 교육 레벨을 표시하는 데 CASE 표현식을 사용하여 사번과,FIRSTNME,MIDINIT,LASTNAME 과 교육 레벨을 표시하는 조회문을 작성하십시오.(EDLEVEL<15인 경우는 SECONDARY, EDLEVEL<19인 경우는 COLLEGE, 그 외에는 POST GRADUATE)

Join 1. EMP_ACT 테이블과 EMPLOYEE 테이블을 조인해서 EMPLOYEE 의 LASTNAME과 EMP_ACT의 모든 컬럼을 다 조회하는 조회문을 작성하십시오. => 2. EMPLOYEE테이블과 DEPARTMENT 테이블을 조인해서 1930년 이전에 태어난 사람들의 사번과, LASTNAME, 부서코드와 부서명을 조회하는 문장을 작성하십시오. 3. EMPLOYEE테이블과 DEPARTMENT 테이블을 조인해서 MANAGER가 없는 DEPARTMENT를 포함한 모든 DEPARTMENT 번호와 부서명, MANAGER의 사번과 LASTNAME을 조회하는 문장을 작성하십시오.

Union 1. EMPLOYEE 테이블에서 부서번호(WORKDEPT)가 ‘E’로 시작하는 사람들의 사원번호(EMPNO)와 EMP_ACT 테이블에서 프로젝트번호(PROJNO)가 ‘MA2100’이거나 ‘MA2110’ 또는 ‘MA2112’인 사람들의 사원번호(EMPNO)를 표시하십시오. => 2. 1번문제에서와 같은 조건을 수행하되 EMPLOYEE 테이블에서는 ‘emp’란 tag를, EMP_ACT 테이블에서는 ‘emp_act’란 tag를 표시하십시오. 3. 1번문제에서와 같은 조건을 수행하되 중복행을 제거하지 말고 그대로 표시하십시오. 4. 2번문제에서와 같은 조건을 수행하되 어느 테이블에도 속하지 않은 2명의 employee ‘NEWAAA’와 ‘NEWBBB’를 ‘new’라는 태그로 표시하십시오.

자료의 생성과 관리-UPDATE 1. EMPLOYEE 테이블의 직원 번호(EMPNO) '000290'의 작업(JOB)을 'LABORER'로 변경하십시오. => 2. 부서(DEPTNO) 'D21'에서 담당하는 PROJECT 테이블의 모든 프로젝트에 대한 프로젝트 스탭핑(PRSTAFF)을 1.5만큼 증가십시오. 3. 부서(WORKDEPT) 'E21'의 관리자를 제외한 모든 직원이 임시로 재지정되었습니다. 이들의 직책(JOB)을 NULL로 변경하고 EMPLOYEE 테이블에서 급여(SALARY, BONUS, COMM) 값을 제로로 변경함으로써 이를 표시하십시오. 4. 직원 번호 000120인 직원의 급여 및 상여금 컬럼을 각각 갱신된 행 부서의 직원 급여 및 상여금 평균으로 갱신하십시오.

자료의 생성과 관리-DELETE 1. DEPARTMENT 테이블에서 부서(DEPTNO) 'D11'을 삭제합니다. => 2. DEPARTMENT 테이블에서 모든 부서를 삭제합니다. 3. EMPLOYEE 테이블에서 1995년에 영업하지 않은 모든 SALESREP 또는 FIELDREP를 삭제하십시오.