120911 오라클

/*

   1. 1999년 이후 입사자 중에서 재고 업무를 담당하는 사원들의 정보를 조회하세요.

*/

  select d.department_name,e.*

  from employees e, departments d

  where e.department_id=d.department_id

  and d.department_name='IT'

  /*and between sysdate and hire_date;*/

  and hire_date > to_date('99/01/01');

 

  

/*

   2. 급여가 $5,000 미만인 사원 중에서 배송이나 구매 업무를 하는 사원들의 정보를 조회하세요.

*/

  select e.salary,d.department_name,e.*

  from employees e, departments d

  where e.department_id=d.department_id

  and d.department_name='Shipping'

  or d.department_name='Purchasing'

  and e.salary <=5000;

/*

   3. 1996년 3월부터 1999년 2월 기간 입사한 사원을 대상으로 부서별 인워수(COUNT() 함수)를

       얻어 보세요. 결과는 인원수가 많은 순서대로 정렬합니다.

*/

  select count(employee_id)

  from employees

  where hire_date between to_date('99/03/01') and to_date('96/03/01');

  

/*

   4. 영업이나 마케팅 업무를 하는 사원을 대상으로 직무별로 평균(AVG) 급여를 계산해보세요. 단,

       평균 급여가 $10,000을 초과하는 경우는 제외합니다. 평균 급여에 대해 내림차순 정렬합니다.

*/

select d.department_name, AVG(e.salary) 

 from employees e, departments d

 where e.salary<10000

 and d.department_name='Marketing'

 or d.department_name='Sales'

 group by d.department_name

 order by avg(e.salary) desc;

/*

SQL 연습문제

1. 회원들의 정보를 받을 테이블을 생성하라.(member_info)

컬럼의역활/컬럼명/속성/공백여부/pk여부

회원데이터순서/member_seq/number/not null/primary key

회원이름/member_name/varchar2(30)/not null

회원전화번호/member_tel/varchar2(11)/null

회원휴대폰번호/member_pon/varchar2(11)/not null

회원이메일주소/member_mail/varchar2(100)/not null

회원메일수신동의/member_Y&N/char(1)/not null

회원집주소(우편번호)/member_zipcode/varchar2(6)/not null

회원집주소(시작주소)/member_st_post/varchar2(100)/not null

회원집주소(끝주소)/member_ed_post/varchar2(100)/not null

회원등급여부/member_grades/char(1)/not null

create table member_info(

 member_seq number not null primary key,

 member_name varchar2(30) not null,

  member_tel varchar2(11) null,

 member_pon varchar2(11) not null,

 member_mail varchar2(100) not null,

 member_YN char(1) not null,

 member_zipcode varchar2(6) not null,

 member_st_post varchar2(100) not null,

 member_ed_post varchar2(100) not null,

 member_grades char(1) not null);

2. 회원들의 등급 테이블을 생성하라.(grades_info)

컬럼의역활/컬럼명/속성/공백여부/pk여부

등급데이터순서/grades_seq/number/not null/primary key

등급코드/grades_id/char(1)/not null

등급이름/grades_name/varchar2(50)/not null

등급비고/grades_note/varchar2(100)/null

create table grades_info(

 grades_seq number not null primary key,

 grades_id char(1) not null,

 grades_name varchar2(50) not null,

 grades_note varchar2(100) null);

3. 우편번호 테이블을 생성하라.(zip_code)

컬럼의역활/컬럼명/속성/공백여부/pk여부

우편번호/zipcode/varchar2(8)/not null

특별시,광역시,도/sido/varchar2(4)/not null

시,군,구/gugun/varchar2(17)/null

읍,면,동/dong/varchar2(26)/null

리명/ri/varchar2(15)/null

건물명/bldg/varchar2(42)/null

시작번지/st_bunji/varchar2(9)/null

끝번지/ed_bunji/varchar2(9)/null

주소데이터순서/seq/varchar2(5)/not null/primary key

4. 회원테이블에 들어가 시퀀스를 생성하시오

- member_seq/시작은 1번부터 1씩 증가하도록

5. 등급테이블에 들어가 시퀀스를 생성하시오

- grades_seq/시작은 1번부터 1씩 증가하도록

6. 우편번호 데이블에 데이터를 집어 넣는다.

확장자 *.ctl이라는파일을 만들어 준다. 아래의 내용을 넣어주고

내용으론 

LOAD DATA INFILE 'c:\ctl\zipcode.csv' <- 로드할 파일의 경로

REPLACE INTO TABLE zipcode <- 로드할 테이블명을 잡아 주는곳

FIELDS TERMINATED BY ',' <- 로드한 문서에 구분 코드가 무엇인지 찾는다

TRAILING NULLCOLS(zipcode,sido,gugun,dong,ri,bldg,st_bunji,ed_bunji,seq) <- 로드할 테이블에 있는 컬럼들을 순서대로 적어준다

아래의 순서대로 해주도록 한다.

실행 -> cmd -> ctl이 저장되어져 있는 경로까지 간뒤 -> 

sqlldr userid=자신의아이디/비밀번호@시드번호 control=XXX.ctl

ex)sqlldr userid=test/test@xe control=zipcode.ctl

-> 기다리자.

7. 등급 테이블에 데이터를 집어 넣는다.

구분코드 분류

1 -> 관리자(통합관리자,관리자 등급은 무조건 한명이여야함)

2 -> 개발자(프로그램 관리자)

3 -> 웹마스터(홈페이지 관리자)

4 -> 테스터

5 -> VIP회원

6 -> 일반회원

코드별 이름

1 -> project_manager

2 -> program_manager

3 -> web_manager

4 -> tester

5 -> vip_member

6 -> member

등급 비고

1 -> 통합관리자

2 -> 프로그램관리자

3 -> 웹관리자

4 -> 테스터

5 -> vip회원

6 -> 일반회원

 insert into grades_info values(grades_seq.nextval,'1','project_manager','통합관리자'); 

 insert into grades_info values(grades_seq.nextval,'2','program_manager','프로그램관리자');

 insert into grades_info values(grades_seq.nextval,'3','web_manager','웹관리자');

 insert into grades_info values(grades_seq.nextval,'4','tester','테스터');

 insert into grades_info values(grades_seq.nextval,'5','vip_member','vip회원');

 insert into grades_info values(grades_seq.nextval,'6','member','일반회원');

8. 회원 테이블에 데이터를 집어 넣는다.

회원데이터순서/회원이름/회원전화번호/회원휴대폰번호/회원이메일주소/회원메일수신동의/회원집주소(우편번호)/회원집주소(시작주소)/회원집주소(끝주소)/회원등급여부

member_seq/임수억/0511611611/01011111234/hello_1.naver.com/y/614060/부산광역시북구금곡동/화명APT 3동 308호/1

member_seq/홍동식/0512611611/01021111234/hello_2.naver.com/n/614060/부산광역시북구금곡동/주공APT 3동 308호/2

member_seq/남규욱/null/01031111234/hello.naver_3.com/y/614060/부산광역시진구부전1동/347-47번지 삼화원룸 308호/3

member_seq/최주연/0514611611/01041111234/hello_4.naver.com/n/614060/부산광역시북구금곡동/화명APT 3동 308호/4

member_seq/박성학/null/01051111234/hello.naver_6.com/n/134741/서울강동구고덕2동고덕주공2단지아파트/201동 108호/5

member_seq/강경환/0516611611/01061111234/hello_5.naver.com/n/134759/서울강동구둔촌2동신성둔촌미소지움아파트 /101-102번지 210호/6

member_seq/박판우/null/0104112244/hello_7.naver.com/y/609769/부산금정구청룡동경동아파트/101-106번지/7

INSERT INTO member_info VALUES (member_seq.nextval,'임수억','0511611611','01011111234','hello_1.naver.com','y','614060','부산광역시북구금곡동','화명APT 3동 308호','1');

 INSERT INTO member_info VALUES (member_seq.nextval,'홍동식','0512611611','01021111234','hello_2.naver.com','n','614060','부산광역시북구금곡동','주공APT 3동 308호','2');

 INSERT INTO member_info VALUES (member_seq.nextval,'남규욱','null','01031111234','hello.naver_3.com','y','614060','부산광역시진구부전1동','347-47번지 삼화원룸 308호','3');

 INSERT INTO member_info VALUES (member_seq.nextval,'최주연','0514611611','01041111234','hello_4.naver.com','n','614060','부산광역시북구금곡동','화명APT 3동 308호','4');

 INSERT INTO member_info VALUES (member_seq.nextval,'박성학','null','01051111234','hello.naver_6.com','n','134741','서울강동구고덕2동고덕주공2단지아파트','201동 108호','5');

 INSERT INTO member_info VALUES (member_seq.nextval,'강경환','0516611611','01061111234','hello_5.naver.com','n','134759','서울강동구둔촌2동신성둔촌미소지움아파트 ','101-102번지 210호','6');

 INSERT INTO member_info VALUES (member_seq.nextval,'박판우','null','0104112244','hello_7.naver.com','y','609769','부산금정구청룡동경동아파트','101-106번지','7');

 

9. 회원 테이블에 관리자 등급의 회원의 정보를 가져오자

단, 등급 테이블에 해당되는 등급이름, 비고도 같이 불러와야 한다.

select * from grades_info where grades_note like '%관리자%';

 select m.*, g.grades_name, g.grades_note 

 from member_info m, grades_info g

 where m.member_grades=g.grades_id

 and member_grades in ('1','2','3');

10. 회원 테이블에 박성학이라는 사람을 조회 하여 회원등급을 일반회원으로 강등 시키도록 한다.

  select member_grades

  from member_info

  where member_name='박성학';

  

  update member_info

  set member_grades = 6

  where member_name='박성학';

  

11. 회원 테이블에 최주연이라는 사람의 주소를 홍동식이라는 사람의 주소와 똑같이 변경하도록 하자.

 update member_info set member_st_post = (

                                            select member_st_post

                                            from member_info

                                            where member_name='홍동식'

                                          )

where member_name='최주연';                                        

select * from member_info;

12. 회원테이블에 강경환이라는 회원을 탈퇴(삭제) 시키자.

select * from member_info;

delete from member_info where member_name='강경환';

*/