oracle -데이터 조작어 공부하기 #1

공부2014. 12. 25. 23:02

뷰어
댓글로
이전글
다음글

336x280(권장), 300x250(권장), 250x250, 200x200 크기의 광고 코드만 넣을 수 있습니다.

728x90

oracle -데이터 조작어 공부하기 #1

sql의 select문은 데이터를 검색하는 기본 문장.

select [ all | distinct ] 속성 이름 , 속성 이름 ,,,

from 테이블 이름,,,,,

where 검색 조건

group by 속성이름

having 검색 조건

order by 속성 이름 [ ASC 순차 | DESC 역순 ]

다음은 oracle 기본 예제인 madang을 통한 예문입니다.

도서를 구매하지 않은 고객을 포함하여 고객의 이름과 고객이 주문한

도서의 판매가격을 구하시오

select customer.name, saleprice

from customer left outer join orders on customer.custid = orders.custid;

가격이 20000 이상인 도서의 이름은?

select bookname

from book

where price>=20000;

BOOKNAME

-------------------

축구의 이해

골프 바이블

야구의 추억

' 박지성 ' 의 총 구매 금액은?

이 문장을 이해하기 위해서는 where 절의 조인을 이해해야 합니다.

따라서

select *

from customer c, orders o

where c.custid = o.custid;

로 검색하게 되면, 이런 출력이 나옵니다.

customer 테이블에서 custid 와 order 테이블에서 custid 를 조인합니다.

customer 테이블에서 custid 1 인 데이터는 한줄

order 테이블에서 custid가 1인 데이터는 세줄

custid 인 데이터가 총 3개 생기게 됩니다.

나온 데이터 중 '박지성' 인 데이터를 뽑아내고,

그 데이터중 saleprice를 다 더합니다.

전부 더하기 위해서 sum(데이터이름) 을 사용하고,

where 절에 name ='박지성' 을 추가합니다.

select sum(o.saleprice)

from customer c, orders o

where c.custid = o.custid and c.NAME='박지성';

SUM(O.SALEPRICE)

----------------

39000

6000 + 21000 + 12000 해서 39000원이 나왔습니다.

좀 더 세련되게 꾸미려면 as 를 붙여줍니다.

select sum(o.saleprice) as sumprice

from customer c, orders o

where c.custid = o.custid and c.NAME='박지성';

SUMPRICE

----------

39000

select

(select name

from customer c2

where c2.custid=c.custid) name2, sum(o.saleprice) as sumprice

from customer c, orders o

where c.custid = o.custid and c.NAME='박지성'

group by c.custid;

이름까지 출력.

2014년 7월 4일에서 7월 7일 사이에 주문 받은 도서의 주문 번호는?

select orderid

from orders

where

orderdate between to_date('20140704','YYYYMMDD')

and to_date('20140707','YYYYMMDD');

ORDERID

----------

성이 '김'인 고객의 이름과 주소는?

select name, address

from customer

where name like '김%%';

NAME ADDRESS

-------------------- -------------------------------

김연아 대한민국 서울

고객의 이름과 고객이 구매한 도서 목록은?

select c.name, o.bookid, b.bookname

from customer c, orders o , book b

where o.CUSTID = c.CUSTID and o.BOOKID = b.BOOKID;

NAME BOOKID BOOKNAME

---------------------------------------- --------

박지성 1 축구의 역사

박지성 2 축구아는 여자

박지성 3 축구의 이해

김연아 5 피겨 교본

장미란 6 역도 단계별기술

추신수 7 야구의 추억

추신수 8 야구를 부탁해

장미란 8 야구를 부탁해

장미란 10 Olympic Champions

김연아 10 Olympic Champions

사람별로 좀 정렬하고 싶다 하면

order by 를 사용.

select c.name, o.bookid, b.bookname

from customer c, orders o , book b

where o.CUSTID = c.CUSTID and o.BOOKID = b.BOOKID

order by c.custid;

NAME BOOKID BOOKNAME

---------------------------------------- ---------

박지성 1 축구의 역사

박지성 2 축구아는 여자

박지성 3 축구의 이해

김연아 5 피겨 교본

김연아 10 Olympic Champions

장미란 6 역도 단계별기술

장미란 10 Olympic Champions

장미란 8 야구를 부탁해

추신수 8 야구를 부탁해

추신수 7 야구의 추억

10개의 행이 선택됨

******************

2.13 도서의 판매액 평균보다 자신의 구매액 평균이 더 높은 고객의 이름은?

잘라서 생각합시다.

도서의 판매액 평균을 구하기 위해서는?

order 테이블에서 총 판매액을 더해서 평균을 만듭니다.

select avg(saleprice)

from orders;

AVG(SALEPRICE)

--------------

11800

각 고객의 구매액의 평균은?

group by 를 사용해 고객 아이디 별로 묶고, ( 조인의 느낌으로)

avg 를 통해 평균 구매액을 찾습니다.

select avg(saleprice)

from orders

group by custid;

AVG(SALEPRICE)

--------------

13000

7500

16500

10333.3333

출력되는 값은

13000원인 custid 1번과

16500원인 custid 4번입니다.

이제 우리가 해야하는 일은 두 문장을 하나로 합치는 일입니다.

쉽게쉽게 뷰를 하나 더 만들어서 검색하는 방법도 있지만,

여러 뷰를 만들고 나면, 무리가 많이 간다는? 단점이 있다고 들었?

HAVING 절

- WHERE 절에서는 집계함수를 사용 할 수 없다.
- HAVING 절은 집계함수를 가지고 조건비교를 할 때 사용한다.
- HAVING절은 GROUP BY절과 함께 사용이 된다.

NAME

-------------------

박지성

추신수

3.1 ‘박지성’이 구매한 도서의 출판사와 같은 출판사에서 도서를 구매한 고객의 이름

먼저 박지성이 구매한 도서의 출판사를 찾습니다.

customer 테이블과 orders 테이블을 조인하여

박지성이 산 책을 찾고, book 테이블과 조인하여

출판사를 찾습니다.

ORDER BY [ASC|DESC]

asc 는 오름차순

desc 는 내림차순

select c.name, o.bookid, b.bookname, b.PUBLISHER

from customer c, orders o, book b

where c.custid=o.custid and o.bookid=b.bookid and

c.name ='박지성'

order by b.bookid asc;

검색해야할 출판사는

굿스포츠

나무수

대한미디어 입니다.

select c.name

from orders o, customer c, book b

where c.CUSTID = o.CUSTID and o.BOOKID= b.BOOKID

and b.PUBLISHER in

(select b.PUBLISHER

from customer c, orders o, book b

where c.custid=o.custid and o.bookid=b.bookid and

c.name ='박지성');

NAME

----------------------------------------

박지성

김연아

장미란

DISTINCT를 이용한 중복 제거

중복된 레코드를 제거하고 유일한 값에 대해서만 결과를 출력하려면 DISTINCT를 사용한다. DISTINCT를 사용하지 않은 [결과 1] 에서는 동일한 값을 가지는 레코드가 포함되어 있지만, DISTINCT를 사용한 [결과 2]에서는 중복된 레코드가 제거되었다.

즉, DISTINCT를 사용하는 경우, DBMS엔진 내부에서는 데이터에 대해 정렬 연산을 수행하므로 출력된 순서가 알파벳순으로 정렬된 것을 확인할 수 있다.

select distinct c.name

from orders o, customer c, book b

where c.CUSTID = o.CUSTID and o.BOOKID= b.BOOKID

and b.PUBLISHER in

(select b.PUBLISHER

from customer c, orders o, book b

where c.custid=o.custid and o.bookid=b.bookid and

c.name ='박지성');

를 써주게 되면

박지성이 한번만 출력하게 됩니다.

3.2 두 개 이상의 서로 다른 출판사에서 도서를 구매한 고객의 이름

아이디어는 이렇습니다.

출판사 별로 order by 해서 묶어두고 숫자를 세서 2개 이상인 custid를 뽑아

각 custid 의 name을 뽑습니다.

select publisher, count(*)

from book

group by publisher

having count(*)>=2;

이건 그냥..

orders 와 book 테이블을 조인한 후에 출판사로 그룹 바이 한 후

개수를 셉니다.

select b.publisher, count(*)

from book b, orders o

where o.BOOKID = b.BOOKID

group by b.PUBLISHER;

select b.publisher, count(*)

from book b, orders o

where o.BOOKID = b.BOOKID

group by b.PUBLISHER

having count(*)>=2;

2개 이상만 모아줍니다.

헐 모르겠다 일단 패스...

select

(select name

from customer c1

where c.custid = c1.custid) name

, count(*) "구매한 출판사 수"

from(

select distinct b.publisher, o.CUSTID

from book b, orders o

where o.BOOKID = b.BOOKID ) cu, customer c

where c.CUSTID = cu.CUSTID

group by c.CUSTID

having count(*)>1

;

3.3 전체 고객의 30% 이상이 구매한 도서 이름은?

select bookid, count(*)

from orders

group by bookid;

이해를 잘못 함.

전체 고객 ( 총 다섯 명 ) 중 30% 5 * 3/10 = 1.5

반올림해서 2명 이상 구매한 책을 찾아라.

select round(count(custid)*3/10, 0)

from customer;

다음은

select bookid, count(*)

from orders

group by bookid

having count(*)>=

(select round(count(custid)*3/10, 0)

from customer);

이렇게 하면 bookid 8번과 10번이 출력된다.

그럼 그것의 이름을 출력하려면 스칼라 중첩질의로 바꿔준다.

select

(select bookname

from book b

where b.bookid=orders.bookid) bookname, count(*) "30%이상 구매"

from orders

group by bookid

having count(*)>=

(select round(count(custid)*3/10, 0)

from customer);

‘잠실’에 사는 고객의 이름을 오름차순으로 보이시오

select 이름, 주소

from "고객"

where 주소 like '잠실'

order by 이름;

극장 위치와 고객의 주소가 같은 고객들을 보이시오.

select distinct cus."이름"

from "고객" cus, "극장" m

where cus."주소" = m."위치";

왜지.. 이렇게 비교하면 넷이 뜨니까 중복 제거 distinct..

뭐 결과는 맞으니까.. 허허

2014년 9월 1일에 영화를 관람한 고객의 수는 얼마인가?

select count(*)

from 예약

where 날짜 = '14/09/01';

‘대한’ 극장에서 영화를 본 고객의 이름을 보이시오.

select *

from 극장 mo, 예약 re

where mo.극장번호 = re.극장번호;

이렇게 조인하면

극장번호 + 상영관번호 + 고객번호가 출력됨.

select *

from 극장 mo, 예약 re

where mo.극장번호 = re.극장번호 and 극장이름 ='대한';

극장이름이 대한 인 것을 출력

select 고객번호,

(select 이름

from "고객" cus

where cus.고객번호=re.고객번호)

from 극장 mo, 예약 re

where mo.극장번호 = re.극장번호 and 극장이름 ='대한';

스칼라 중첩 질의로 이름 출력.

홍길동과 김철수가 출력됨.

‘대한’ 극장의 전체 수입을 보이시오

select sum(up.가격)

from 상영관 up, 예약 re

where up.극장번호=re.극장번호 and up.상영관번호 = re.상영관번호 and

re.극장번호 = (select mo.극장번호

from 극장 mo

where mo.극장이름 like '대한');

극장 이름 까지 출력하기

select up.극장번호, sum(up.가격),

(select 극장이름

from 극장 mo2

where mo2.극장번호=up.극장번호) 극장이름

from 상영관 up, 예약 re

where up.극장번호=re.극장번호 and up.상영관번호 = re.상영관번호 and

re.극장번호 = (select mo.극장번호

from 극장 mo

where mo.극장이름 like '대한')

group by up.극장번호

;

극장별 상영관 수를 보이시오.

select mo.극장이름, count(*)

from 극장 mo, 상영관 up

where mo.극장번호 = up.극장번호

group by mo.극장이름;

2014년 9월 1일에 극장별 평균 관람 고객의 수를 보이시오.

select *

from 극장 mo, 예약 re

where mo.극장번호 = re.극장번호 and re.날짜='14/09/01';

문제가 개똥. 안풀어

제출은 avg 로 했으나 의미 없는 문제.

2014년 9월 1일에 가장 많은 고객이 관람한 영화를 보이시오.

select up.영화제목, count(*)

from 상영관 up, 예약 re

where up.극장번호 = re.극장번호 and up.상영관번호 = re.상영관번호 and re.날짜='14/09/01'

group by up.영화제목;

죄다 1이라 ... 가장 많은것도 1 그러니 셋다 1 가장 많은 고객이 본 영화도 셋 다.

select up.영화제목, count(*)

from 상영관 up, 예약 re

where up.극장번호 = re.극장번호 and up.상영관번호 = re.상영관번호 and re.날짜='14/09/01'

group by up.영화제목

having

count(*) =

(select max(count(*))

from 상영관 up, 예약 re

where up.극장번호 = re.극장번호 and up.상영관번호 = re.상영관번호 and re.날짜='14/09/01'

group by up.영화제목);

② 영화의 가격을 10% 인상하시오. - UPDATE 기본문제

UPDATE 상영관

SET 가격= 가격*1.1;

3장 심화문제 9번

5) 모든 사원의 최대 및 최소 급여, 합계 및 평균 급여를 출력하시오. 열 이름은 각각

MAX, MIN, SUM, AVG로 한다. 단, 소수점 이하는 반올림하여 정수로 출력한다.

select max(sal) max, min(sal) min, sum(sal) sum, round(avg(sal)) avg

from emp;

9) 평균급여가 가장 높은 부서의 번호를 출력하시오.

select d1.deptno

from dept d1, emp e1

where e1.DEPTNO = d1.DEPTNO

group by d1.deptno

having avg(e1.sal) =

(select max(avg(sal)) max

from emp e, dept d

where e.deptno = d.deptno

group by d.deptno);

12) Emp 테이블에서 이름, 급여, 커미션 금액,

총액(sal + comm)을 구하여 총액이 많은

순서대로 출력하시오.

단, 커미션이 NULL인 사람은 제외한다.

집합연산 MINUS(또는 EXCEPT), ORDER BY 사용

select ename, sal, comm, sum(comm+sal) sum

from emp

where comm is not null

group by ename, sal, comm

order by sum desc;

15) 같은 업무를 하는 사람의 수가

4명 이상인 업무와 인원수를 출력하시오.

select job, count(job)

from emp

group by job

having count(job)>=4;

19) 사원 SCOTT보다 급여를

많이 받는 사람의 이름을 출력하시오.

SELECT FROM WHERE sal > (SELECT FROM WHERE );

select e1.ename

from emp e1

where e1.sal >

(select e.sal

from emp e

where e.ename like 'SCOTT');

7. [사원 데이터베이스] 3장의 연습문제

9번 데이터베이스를 이용하여 다음 질의에 해당

되는 SQL 문을 작성하시오.

2) 사원의 이름과 부서의 이름을 보이시오

(조인/스칼라 부속질의 사용).

① [조인]

select e.ename "사원명", d.dname "부서명"

from dept d, emp e

where e.deptno = d.deptno;

② [스칼라 부속질의]

SELECT (SELECT FROM WHERE)

FROM

select e.ename "사원명",

(select d.dname

from dept d

where e.deptno = d.deptno) "부서명"

from emp e;

3) ‘CHICAGO’에 근무하는 사원의 이름을 보이시오

(조인/인라인 뷰/중첩질의, EXISTS 사용).

① [조인]

select e.ename "사원명"

from dept d, emp e

where d.deptno=e.deptno and d.loc like 'CHICAGO';

② [인라인 뷰]

SELECT

FROM xxx , (SELECT FROM WHERE)

WHERE

select e.ename "사원명", e.dname "부서명"

from (select d.dname, e.ename

from dept d, emp e

where d.deptno = e.deptno and d.loc like 'CHICAGO') e;

③ [중첩질의]

SELECT

FROM

WHERE xxx IN (SELECT FROM WHERE)

select e.ename "사원명"

from emp e

where e.deptno in

(select d.deptno

from dept d

where d.LOC like 'CHICAGO');

④ [EXISTS]

SELECT

FROM

WHERE EXISTS (SELECT FROM WHERE)

select e.ename "사원명"

from emp e

where exists

(select e.ename

from dept d

where e.deptno = d.deptno and d.loc like 'CHICAGO');

3) 자기 부서의 평균보다 급여가 많은 직원의 이름을 보이시오

(상관 부속질의 사용).

① [조인]

SELECT FROM

WHERE xxx > (SELECT FROM WHERE )

select e1.ename "사원명"

from emp e1

where sal >

(select avg(e2.sal)

from emp e2

where e1.deptno = e2.deptno);

6. 다음에 해당하는 뷰를 작성하시오. 데이터는 마당서점 데이터베이스를 이용한다.

1) 판매가격이 20,000원인 도서의 도서번호, 도서이름, 고객이름, 출판사, 판매가격을 보여주는

highorders 뷰를 생성하시오.

create view highorders

as select b.bookid bookid, b.bookname bookname, c.name name,

b.publisher publisher, o.saleprice

from book b, customer c, orders o

where b.bookid = o.bookid and o.custid=c.custid ;

select *

from highorders;

2) 생성한 뷰를 이용하여 판매된 도서의 이름과 고객의 이름을 출력하

는 SQL 문을 작성하시오.

select bookname, name

from highorders;

3) highorders 뷰를 변경하고자 한다.

판매가격 속성을 삭제하는 명령을 수행하시오.

삭제 후 (2)번 SQL 문을 다시 수행하시오.

create or replace view highorders

as select b.bookid bookid, b.bookname bookname, c.name name,

b.publisher publisher

from book b, customer c, orders o

where b.bookid = o.bookid and o.custid=c.custid ;

select *

from highorders;

사원정보(Employees) 테이블에서사원번호,

이름, 급여, 업무, 입사일, 상사의사원번호를출력하시오.

이때이름은이름과성을연결하여Name이라는별칭으로출력하시오

SELECT employee_id, first_name|| ' ' || last_nameas "Name",

salary, job_id, hire_date, manager_id

FROM Employees;

문제 1 사원정보(Employees) 테이블에서사원의

이름과성은Name, 업무는Job, 급여는Salary, 연봉에$100 보너스를

추가하여계산한값은Increase Ann_Salary,

급여에$100 보너스를추가하여계산한연봉은Increase Salary라는별칭을

붙여출력하시오(107행).

select employee_id, first_name || ' ' || last_name as "Name"

salary as Salary,

job_id as Job,

salary + 100 as Increase_Ann_Salary

from employees;

[문제3]H R 부서에서는급여(salary)와수당율(commission_pct)에

대한지출보고서를작성하려고한다.

수당을받는모든사원의이름과성(Name으로별칭), 급여, 업무, 수당율을출력하시오.

이때급여가큰순서대로정렬하되,

급여가같으면수당율이큰순서대로정렬하시오(35행).

select employee_id, first_name || ' ' || last_name as "Name",

salary as Salary,

job_id ad Job,

salary+100 as Increase_Ann_Salary,

commission_pct as C_PCT

from employees

where commission_pct is NOT NULL

order by salary desc, commission_pct desc;

[문제2] 모든사원의연봉을표시하는보고서를작성하려고한다.

보고서에사원의이름과성(Name으로별칭), 급여,

수당여부에따른연봉을포함하여출력하시오.

수당여부는수당이있으면“Salary + Commission”,

수당이없으면“Salary only”라고표시하고, 별칭은적절히붙이시오.

또한출력시연봉이높은순으로정렬하시오(107행).

select employee_id,

first_name || ' ' || last_name as "Name",

Salary+(salary*commission_pct) as CommissionSalary,

case

when commission_pct is NULL then 'Salary Only'

else 'Salary+Commission' end "수당 여부"

from employees

order by salary desc;

[문제2] 사원들의업무별전체급여평균이$10,000보다큰경우를

조회하여업무별급여평균을출력하시오.

단업무에사원(CLERK)이포함된경우는제외하고

전체급여평균이높은순서대로출력하시오(7행).

select job_id, avg(salary)

from employees

where job_id != '%CLERK%'

group by job_id

having avg(salary) > 10000

order by avg(salary) desc;

[문제3] 각사원과직속상사와의관계를이용하여

다음과같은형식의보고서를작성하고자한다.

(예) 홍길동은허균에게보고한다→ EleniZlotkeyreport to Steven King

어떤사원이누구에게보고하는지위예를참고하여출력하시오.

단, 보고할상사가없는사원이있다면그정보도포함하여출력하고,

상사의이름과성은대문자로출력하시오(107행).

select

case

when e1.manager_id is null

then e1.first_name || ' ' || e1.last_name || ' report to ' || ' NOTHING '

else e1.first_name || ' ' || e1.last_name || 'report to ' || upper(e2.first_name)

|| ' ' || upper(e2.last_name)

end " report who "

from employees e1

left outer join employees e2

on e2.employee_id = e1.manager_id;

[문제3] 사원들의지역별근무현황을조회하고자한다.

도시이름이영문‘O’로시작하는지역에살고있는사원의사원번호,

이름과성(Name으로별칭), 업무, 입사일을출력하시오(34행).

select employee_id, e.first_name || ' '|| e.last_name "name",

e.job_id,

e.hire_date

from departments d, employees e, locations l

where d.department_id=e.department_id and

d.location_id = l.location_id and

l.city like 'o%';

[문제3] 우리회사는1년에한번업무를변경하여

전체적인회사업무를사원들이익히도록하는Role change 정책을시행하고있다.

이번인사이동때아직업무가변경된적이없는사원들을적합한업무로이동시키려고한다.

HR 부서의사원정보(Employees) 테이블과업무이력정보(Job_history)

테이블을이용하여한번도업무가변경되지않은사원의사원번호를출력하시오(100행).

select e.employee_id

from employees e

minus

(select j.employee_id

from job_history j);

[샘플문제] HR 부서에서는신규프로젝트를성공으로

이끈해당업무자들의급여를인상하기로결정하였다.

사원은현재107명이며19개의업무에소속되어근무중이다.

급여인상대상자는회사의업무(Distinct job_id)

중다음5개업무에서일하는사원에해당된다.

나머지업무에대해서는급여가동결된다.

5개업무의급여인상안은다음과같다(107행).

HR_REP(10%), MK_REP(12%), PR_REP(15%), SA_REP(18%), IT_PROG(20%)

/* CASE */

SELECT employee_id, last_name|| ' ' || first_nameas "Name", job_id, salary,

CASE job_idWHEN 'HR_REP' THEN 1.10 * salary

WHEN 'MK_REP' THEN 1.12 * salary

WHEN 'PR_REP' THEN 1.15 * salary

WHEN 'SA_REP' THEN 1.18 * salary

WHEN 'IT_PROG' THEN 1.20 * salary

ELSE salary

END "New Salary"

FROM Employees;

/* DECODE */

SELECT employee_id, last_name|| ' ' || first_nameas "Name", job_id, salary,

DECODE (job_id, 'HR_REP', 1.10 * salary,

'MK_REP', 1.12 * salary,

'PR_REP', 1.15 * salary,

'SA_REP', 1.18 * salary,

'IT_PROG', 1.20 * salary,

salary) "New Salary"

FROM Employees;

13. [마당서점데이터베이스인덱스] 마당서점데이터베이스에서다음SQL 문을수행하고데이터베이스가인덱스를사용하는과정을확인해보시오.

(1)

다음SQL 문을수행해본다.

(2)

실행계획을살펴본다. 실행계획은[F10] 키를누른후[계획설명] 탭을선택하면표시된다(SQL Developer를실행하고SQL 문위에서오른쪽마우스클릭–[설명]-[계획설명]).

(3)

Customer 테이블에name으로인덱스를생성하시오. 생성후(1)번의SQL 문을다시수행하고실행계획을살펴보시오.

create index name

on customer (name);

수정은

alter index 이름 on

(4)

같은질의에대한두가지실행계획을비교해보시오.

(5)

(3)번에서생성한인덱스를삭제하시오.

drop index name;

A.8 조건부논리표현식제어: CASE & DECODE

[문제1] 부서별급여합계를구하고, 그결과를다음과같이표현하시오(12행).

Sum Salary > 100000 이면, “Excellent”

Sum Salary > 50000 이면, “Good”

Sum Salary > 10000 이면, “Medium”

Sum Salary <= 10000 이면, “Well”

select department_id, salary,

case

when salary > 100000 then 'excellent'

when salary > 50000 then 'good'

when salary > 10000 then 'medium'

else 'well'

end "new salary"

from

(select sum(salary) salary, department_id

from employees

group by department_id

having sum(salary)>0);

[문제3] 월별로입사한사원수출력

(방식1) 월별로입사한사원수가아래와같이각행별로출력되도록하시오(12행).

select decode (to_char(hire_date, 'mm'), '01', count (*), 0) "1month",

decode (to_char(hire_date, 'mm'), '01', count (*), 0) "1month",

......

from employees

group by to_char(hire_date, 'mm')

order by to_char(hire_date, 'mm);

(방식2) 첫행에모든월별입사사원수가출력되도록하시오(1행).

select max(decode (to_char(hire_date, 'mm'), '01', count (*), 0) ) "1month",

max(decode (to_char(hire_date, 'mm'), '01', count (*), 0) ) "1month",

.....

from emplyees

group by to_char(hire_date, 'mm')

order by to_char(hire_date, 'mm');

5장 심화문제 6번

(1) insert book () 프로시저 수정

create procedure insertcustomer

(

newCustid number,

newCustname varchar2,

newCustaddress varchar2,

newCustphone number

)

begin

insert into

customer(custid, name, address, phone)

values (newCustid, newCustname, newCustaddress, newCustphone);

end;

(2) bookinsertorupdate() 프로시저 수정 삽입하려는것과

동일한 도서 있으면 가격이 높을 때만 새값

create or replace procedure bookinsertorupdate

(

myBookid number,

myBookname varchar2,

mtPublisher varchar2,

myPrice number

)

mycount number;

bigprice number;

BEGIN

select count(*) into mycount

from book

where bookname

like mybookname;

select count(*) into bigprice

from book

where PRICE <myPrice;

if mycount != 0

then

if bigprice !=0

then

update book

set price = myprice

where bookname

like mybookname;

end if;

else

insert into BOOK(BOOKID, BOOKNAME, PUBLISHER, PRICE)

values (myBookid, myBookname, mtPublisher, myPrice);

end if;

end;

심화문제 7번

(1) 출판사가 이상미디어 인 도서의 이름과 가격을 보여주는 프로시저

create or replace procedure media2

myname varchar(100);

myprice number;

cursor tes is select bookname, price

from book

where publisher = '이상미디어';

CURSOR test2 is select price

from book

where publisher = '이상미디어';

begin

open tes;

loop

fetch tes into myname, myprice;

exit when tes%notfound;

SYS.DBMS_OUTPUT.PUT_LINE('이름:'||myname);

SYS.DBMS_OUTPUT.PUT_LINE('가격:'||myprice);

end loop;

close tes;

end;

(2) 출판사 별로 출판사 이름과 도서의 판매 총액

create or replace procedure q7_2

pub varchar(100);

pri number;

cursor cur is select b.publisher, sum(o.SALEPRICE)

from book b, orders o

where b.bookid= o.BOOKID

group by publisher;

begin

open cur;

loop

fetch cur into pub, pri;

exit when cur%notfound;

SYS.DBMS_OUTPUT.PUT_LINE('출판사:'||pub);

SYS.DBMS_OUTPUT.PUT_LINE('판매총액:'||pri);

end loop;

close cur;

end;

(3) 출판사 별로 도서의 평균가 보다 비싼 도서의 이름

create or replace procedure q7_3

str varchar(100);

num number;

cursor cur is select b1.bookname

from book b1

where b1.price > (

select avg(b2.price)

from book b2

where b2.publisher = b1.publisher);

begin

open cur;

loop

fetch cur into str;

exit when cur%notfound;

SYS.DBMS_OUTPUT.PUT_LINE('bookname:'||str);

end loop;

close cur;

end;

(4) 고객별로 도서를 몇권 구입했는지 총 구매액

create or replace procedure q7_4

str varchar(100);

str2 varchar(100);

num number;

cursor cur is

select c.name, count(c.CUSTID), sum(o.SALEPRICE)

from CUSTOMER c, ORDERS o

where o.CUSTID = c.CUSTID

group by c.name;

begin

open cur;

loop

fetch cur into str,str2,num;

exit when cur%notfound;

SYS.DBMS_OUTPUT.PUT_LINE('custname:'||str);

SYS.DBMS_OUTPUT.PUT_LINE('count:'||str2);

SYS.DBMS_OUTPUT.PUT_LINE('allprice:'||num);

end loop;

close cur;

end;

(5) 주문이 있는 고객 이름 주문 총액, 없으면 이름만 출력

create or replace procedure q7_5

str varchar(100);

str2 varchar(100);

num number;

cursor cur is

select c.name, sum(o.SALEPRICE)

from customer c, orders o

where o.CUSTID = c.CUSTID

group by c.name;

cursor cur2 is

select name

from customer

minus

select name

from customer

where custid in (

select custid

from orders);

begin

open cur;

loop

fetch cur into str,num;

exit when cur%notfound;

SYS.DBMS_OUTPUT.PUT_LINE('custname:'||str);

SYS.DBMS_OUTPUT.PUT_LINE('allprice:'||num);

end loop;

close cur;

open cur2;

loop

fetch cur2 into str2;

exit when cur2%notfound;

SYS.DBMS_OUTPUT.PUT_LINE('custname:'||str2);

end loop;

close cur2;

end;

SET SERVEROUTPUT ON;

EXEC Q7_2;

심화문제 8번

(1) 고객 주문 총액 20000 이상 우수

미만 보통 반환하는 함수 GRADE()

create or replace function grade( price number)

return varchar2

rtgrade varchar2(100);

begin

if price >= 20000

then rtgrade := 'good';

else

rtgrade := 'nomal';

end if;

return rtgrade;

end;

(2) 고객 주소 국내면 국내거주 해외면 국외거주 반환

create or replace function domestic( address varchar2)

return varchar2

rtstr varchar2(100);

begin

if address like '대한민국%%'

then rtstr := '국내거주';

else

rtstr := '국외거주';

end if;

return rtstr;

end;

(3) 2번 호출 국내 국외 판매 총액 출력 sql

select domestic(c.address) ad, sum(salaprice)

from customer c, orders o

where o.custid = c.custid

group by domestic(c.address);

심화문제 9번 자바로 만들기

(1) 출판사가 이상미디어인 도서와 가격을 보여주는 프로그램

import java.io.*;

import java.sql.*;

import java.sql.CallableStatement;

public class q9_1_test {

Connection con;

public q9_1_test() {

String url="jdbc:oracle:thin:@localhost:1521:orcl"; /* 11g express edition은orcl대신XE를입력한다. */

String userid="madang";

String pwd="madang";

try { /* 드라이버를찾는과정*/

Class.forName("oracle.jdbc.driver.OracleDriver");

System.out.println("드라이버로드성공");

} catch(ClassNotFoundException e) {

e.printStackTrace();

}try { /* 데이터베이스를연결하는과정*/

System.out.println("데이터베이스연결준비...");

con=DriverManager.getConnection(url, userid, pwd);

System.out.println("데이터베이스연결성공");

} catch(SQLException e) {

e.printStackTrace();

}

private void sqlRun() {

String query="select bookname, price from book where publisher = '이상미디어'"; /* SQL 문*/

try { /* 데이터베이스에질의결과를가져오는과정*/

Statement stmt=con.createStatement();

ResultSet rs=stmt.executeQuery(query);

while(rs.next()) {

System.out.print(" " +rs.getString(1));

System.out.println(" " +rs.getInt(2));

//System.out.print("\t\t\t"+rs.getString(3));

//System.out.println("\t\t"+rs.getInt(4));

}

con.close();

} catch(SQLException e) {

e.printStackTrace();

}

public static void main(String args[]) {

q9_1_test so=new q9_1_test();

so.sqlRun();

}

(2) 출판사 별 출판사 이름과 도서의 판매 총액이 보이는 프로그램

import java.io.*;

import java.sql.*;

import java.sql.CallableStatement;

public class q9_2 {

Connection con;

public q9_2() {

String url="jdbc:oracle:thin:@localhost:1521:orcl"; /* 11g express edition은orcl대신XE를입력한다. */

String userid="madang";

String pwd="madang";

try { /* 드라이버를찾는과정*/

Class.forName("oracle.jdbc.driver.OracleDriver");

System.out.println("드라이버로드성공");

} catch(ClassNotFoundException e) {

e.printStackTrace();

}try { /* 데이터베이스를연결하는과정*/

System.out.println("데이터베이스연결준비...");

con=DriverManager.getConnection(url, userid, pwd);

System.out.println("데이터베이스연결성공");

} catch(SQLException e) {

e.printStackTrace();

}

private void sqlRun() {

String query="select b.publisher, sum(o.SALEPRICE) from book b, orders o where b.bookid= o.BOOKID group by publisher"; /* SQL 문*/

try { /* 데이터베이스에질의결과를가져오는과정*/

Statement stmt=con.createStatement();

ResultSet rs=stmt.executeQuery(query);

while(rs.next()) {

System.out.print(" " +rs.getString(1));

System.out.println(" " +rs.getString(2));

//System.out.print("\t\t\t"+rs.getString(3));

//System.out.println("\t\t"+rs.getInt(4));

}

con.close();

} catch(SQLException e) {

e.printStackTrace();

}

public static void main(String args[]) {

q9_2 so=new q9_2();

so.sqlRun();

}

(3) 출판사별 도서 평균가 보다 비싼 도서 보이는 프로그램

import java.io.*;

import java.sql.*;

import java.sql.CallableStatement;

public class q9_3 {

Connection con;

public q9_3() {

String url="jdbc:oracle:thin:@localhost:1521:orcl"; /* 11g express edition은orcl대신XE를입력한다. */

String userid="madang";

String pwd="madang";

try { /* 드라이버를찾는과정*/

Class.forName("oracle.jdbc.driver.OracleDriver");

System.out.println("드라이버로드성공");

} catch(ClassNotFoundException e) {

e.printStackTrace();

}try { /* 데이터베이스를연결하는과정*/

System.out.println("데이터베이스연결준비...");

con=DriverManager.getConnection(url, userid, pwd);

System.out.println("데이터베이스연결성공");

} catch(SQLException e) {

e.printStackTrace();

}

private void sqlRun() {

String query="select b1.bookname from book b1 where b1.price >= (select avg(b2.price) from book b2 where b2.publisher = b1.publisher)"; /* SQL 문*/

try { /* 데이터베이스에질의결과를가져오는과정*/

Statement stmt=con.createStatement();

ResultSet rs=stmt.executeQuery(query);

while(rs.next()) {

System.out.println(" " +rs.getString(1));

//System.out.println(" " +rs.getString(2));

//System.out.print("\t\t\t"+rs.getString(3));

//System.out.println("\t\t"+rs.getInt(4));

}

con.close();

} catch(SQLException e) {

e.printStackTrace();

}

public static void main(String args[]) {

q9_3 so=new q9_3();

so.sqlRun();

}

10번 웹 만들기

(1) 고객 이름 조회 주소 변경

1페이지

<%@ page import="java.sql.*" contentType="text/html; charset=EUC-KR"%>

Class.forName("oracle.jdbc.driver.OracleDriver");

String url = "jdbc:oracle:thin:@localhost:1521:orcl";

/* 11g express edition은orcl대신XE를입력한다. */

Connection dbconn = DriverManager.getConnection(url, "madang", "madang");

Statement stmt = dbconn.createStatement();

ResultSet myResultSet = stmt.executeQuery("select name from customer");

String name2 = request.getParameter("name");

String address2 = request.getParameter("address");

int count=0;

//ResultSet modify = stmt.executeQuery("update");

//Statement stmt2 = conn.createStatement();

// String queryString = "update CUSTOMER set address=? where name=?";

// Connection connection = null;

// PreparedStatement pstatement = null;

// pstatement = connection.prepareStatement(queryString);

// pstatement.setString(1, address2);

// pstatement.setString(2, name2);

<html>

<head>

</head>

<body bgcolor="white" text="black" link="blue" vlink="purple"

alink="red">

<table border="1" cellspacing="0" width="400" bordercolor="#9AD2F7"

bordercolordark="white" bordercolorlight="#B9E0FA">

<tr>

</p>

</td>

</tr>

if (myResultSet != null) {

while (myResultSet.next()) {

String W_name = myResultSet.getString("name");

// String W_BOOKNAME = myResultSet.getString("bookname");

//String W_PUBLISHER = myResultSet.getString("publisher");

//String W_PRICE = myResultSet.getString("price");

<tr>

<p>

<% count++; %>

<span style="font-size: 9pt;"> <font face="돋움체"

color="black"> <%=W_name%></font></span>

</form>

</p>

</td>

</tr>

}

stmt.close();

dbconn.close();

</table>

<tr>

<b><a href="booklist.jsp"> <font size="1" face="돋움체"

color="black">LIST</font></a></b>

</p>

</td>

</tr>

</table>

</body>

</html>

2페이지

<%@ page language="java" contentType="text/html; charset=EUC-KR"

pageEncoding="EUC-KR"%>

<%@ page import="java.sql.*" contentType="text/html; charset=EUC-KR"%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>

<head>

<title>selProduct.jsp</title>

</head>

// HTML FORM 에서 전달된 데이터의 한글인코딩

request.setCharacterEncoding("euc-kr");

// session 에 username 이름으로 HTML FORM 의 <input type="text" name="username"/> 에 입력된 값을 저장함.

session.setAttribute("name",request.getParameter("name"));

session.setAttribute("address",request.getParameter("address"));

Class.forName("oracle.jdbc.driver.OracleDriver");

String url = "jdbc:oracle:thin:@localhost:1521:orcl";

/* 11g express edition은orcl대신XE를입력한다. */

Connection dbconn = DriverManager.getConnection(url, "madang", "madang");

Statement stmt = dbconn.createStatement();

String name = request.getParameter("name");

String address = request.getParameter("address");

String queryString =" update CUSTOMER set address= '"+ address+"' where name like '"+name+"'";

ResultSet rs = null;

rs = stmt.executeQuery(queryString);

//pstatement.setString(1, address);

//pstatement.setString(2, name);

//rs = stmt.executeUpdate(queryString);

//ResultSet myResultSet = stmt.executeQuery("select name from customer");

System.out.println(name);

System.out.println(address);

int count=0;

// String queryString = "update CUSTOMER set address=? where name=?";

// Connection connection = null;

// PreparedStatement pstatement = null;

// pstatement = connection.prepareStatement(queryString);

// pstatement.setString(1, address2);

// pstatement.setString(2, name2);

<body>

<HR>

<%=session.getAttribute("name") %>님의 주소가

<%=session.getAttribute("address") %>로 변경되었습니다.

<HR>

</center>

</body>

</html>

(2) 도서 목록 출력 그리고 입력

1페이지

<%@ page import="java.sql.*" contentType="text/html; charset=EUC-KR"%>

Class.forName("oracle.jdbc.driver.OracleDriver");

String url = "jdbc:oracle:thin:@localhost:1521:orcl";

/* 11g express edition은orcl대신XE를입력한다. */

Connection dbconn = DriverManager.getConnection(url, "madang", "madang");

Statement stmt = dbconn.createStatement();

ResultSet myResultSet = stmt.executeQuery("select bookname from book");

String bookid = request.getParameter("bookid");

String bookname = request.getParameter("bookname");

int count=0;

//ResultSet modify = stmt.executeQuery("update");

//Statement stmt2 = conn.createStatement();

// String queryString = "update CUSTOMER set address=? where name=?";

// Connection connection = null;

// PreparedStatement pstatement = null;

// pstatement = connection.prepareStatement(queryString);

// pstatement.setString(1, address2);

// pstatement.setString(2, name2);

<html>

<head>

</head>

<body bgcolor="white" text="black" link="blue" vlink="purple"

alink="red">

<table border="1" cellspacing="0" width="400" bordercolor="#9AD2F7"

bordercolordark="white" bordercolorlight="#B9E0FA">

<tr>

</p>

</td>

</tr>

if (myResultSet != null) {

while (myResultSet.next()) {

String bookname2 = myResultSet.getString("bookname");

// String W_BOOKNAME = myResultSet.getString("bookname");

//String W_PUBLISHER = myResultSet.getString("publisher");

//String W_PRICE = myResultSet.getString("price");

<tr>

<p>

<% count++; %>

</p>

</td>

</tr>

}}

</table>

<tr>

</td>

bookid

</td>

bookname

publisher

price

</form>

</p>

</td>

</tr>

</table>

stmt.close();

dbconn.close();

</body>

</html>

2페이지

<%@ page language="java" contentType="text/html; charset=EUC-KR"

pageEncoding="EUC-KR"%>

<%@ page import="java.sql.*" contentType="text/html; charset=EUC-KR"%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>

<head>

<title>selProduct.jsp</title>

</head>

// HTML FORM 에서 전달된 데이터의 한글인코딩

request.setCharacterEncoding("euc-kr");

// session 에 username 이름으로 HTML FORM 의 <input type="text" name="username"/> 에 입력된 값을 저장함.

session.setAttribute("bookid",request.getParameter("bookid"));

session.setAttribute("bookname",request.getParameter("bookname"));

session.setAttribute("publisher",request.getParameter("publisher"));

session.setAttribute("price",request.getParameter("price"));

Class.forName("oracle.jdbc.driver.OracleDriver");

String url = "jdbc:oracle:thin:@localhost:1521:orcl";

/* 11g express edition은orcl대신XE를입력한다. */

Connection dbconn = DriverManager.getConnection(url, "madang", "madang");

Statement stmt = dbconn.createStatement();

String bookid = request.getParameter("bookid");

String bookname = request.getParameter("bookname");

String publisher = request.getParameter("publisher");

String price = request.getParameter("price");

String queryString =" insert into book (bookid, bookname, publisher, price) values("+ bookid +", '"+ bookname+ "' , '" + publisher + "' ,"+ price + " )";

ResultSet rs = null;

rs = stmt.executeQuery(queryString);

System.out.println(bookid);

System.out.println(bookname);

System.out.println(publisher);

System.out.println(price);

System.out.println(queryString);

int count=0;

<body>

<HR>

<%=session.getAttribute("bookid") %>

<%=session.getAttribute("bookname") %>

<%=session.getAttribute("publisher") %>

<%=session.getAttribute("price") %>

<HR>

</center>

</body>

</html>

심화문제 11번

(2) 프로시저 사용 새 도서 등록

1페이지

<%@ page import="java.sql.*" contentType="text/html; charset=EUC-KR"%>

Class.forName("oracle.jdbc.driver.OracleDriver");

String url = "jdbc:oracle:thin:@localhost:1521:orcl";

/* 11g express edition은orcl대신XE를입력한다. */

Connection dbconn = DriverManager.getConnection(url, "madang", "madang");

Statement stmt = dbconn.createStatement();

ResultSet myResultSet = stmt.executeQuery("select name from customer");

String name2 = request.getParameter("name");

String address2 = request.getParameter("address");

int count=0;

<html>

<head>

<meta http-equiv="content-type" content="text/html; charset=euc-kr">

<title>** BOOK LIST **</title>

</head>

<body bgcolor="white" text="black" link="blue" vlink="purple"

alink="red">

stmt.close();

dbconn.close();

<table cellpadding="0" cellspacing="0" width="400" height="23">

<tr>

<td width="1350">

<form name="form1" method="POST" action="q11_2.jsp">

</td>

<td width="1350">

bookid

<input type="text" name="bookid" />

</td>

<td width="1350">

bookname

<input type="text" name="bookname" /> </td>

<td width="1350">

publisher

<input type="text" name="publisher" /> </td>

<td width="1350">

price

<input type="text" name="price" /> </td>

<td width="1350">

<BR>

<input type="submit" value="submit" "/>

</form>

</td>

</tr>

</table>

</body>

</html>

2페이지

<%@ page language="java" contentType="text/html; charset=EUC-KR"

pageEncoding="EUC-KR"%>

<%@ page import="java.sql.*" contentType="text/html; charset=EUC-KR"%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>

<head>

<title>selProduct.jsp</title>

</head>

// HTML FORM 에서 전달된 데이터의 한글인코딩

request.setCharacterEncoding("euc-kr");

// session 에 username 이름으로 HTML FORM 의 <input type="text" name="username"/> 에 입력된 값을 저장함.

session.setAttribute("bookid",request.getParameter("bookid"));

session.setAttribute("bookname",request.getParameter("bookname"));

session.setAttribute("publisher",request.getParameter("publisher"));

session.setAttribute("price",request.getParameter("price"));

Class.forName("oracle.jdbc.driver.OracleDriver");

String url = "jdbc:oracle:thin:@localhost:1521:orcl";

/* 11g express edition은orcl대신XE를입력한다. */

Connection dbconn = DriverManager.getConnection(url, "madang", "madang");

Statement stmt = dbconn.createStatement();

String bookid = request.getParameter("bookid");

String bookname = request.getParameter("bookname");

String publisher = request.getParameter("publisher");

String price = request.getParameter("price");

System.out.println(bookid);

System.out.println(bookname);

System.out.println(publisher);

System.out.println(price);

System.out.println("====");

//Statement stmt = dbconn.prepareCall("{call bookinsertorupdate(?,?,?,? )}");

// stmt.setString(1,bookid);

// stmt.setString(2,bookname);

// stmt.setString(3,publisher);

// stmt.setString(4,price);

// stmt.execute();

String strProcedure="{call bookinsertorupdate(?,?,?,? )}";

CallableStatement cs=dbconn.prepareCall(strProcedure);

cs.setString(1,bookid);

cs.setString(2,bookname);

cs.setString(3,publisher);

cs.setString(4,price);

cs.registerOutParameter(3,java.sql.Types.VARCHAR);

cs.execute();

System.out.println(bookid);

System.out.println(bookname);

System.out.println(publisher);

System.out.println(price);

//System.out.println(queryString);

int count=0;

<body>

<HR>

<%=session.getAttribute("bookid") %>

<%=session.getAttribute("bookname") %>

<%=session.getAttribute("publisher") %>

<%=session.getAttribute("price") %>

<HR>

</center>

</body>

</html>

저작자표시 비영리 변경금지

'공부' 카테고리의 다른 글

C4D \| train tutorial (0)	2018.01.19
C4D \| missile site tutorial (0)	2018.01.19
데이터 보안 복습 문제 간추리기 (0)	2014.12.25
컴퓨터 보안 연습문제 (0)	2014.12.25
자바 안드로이드 이미지 버튼 만들기 (0)	2014.11.11

BlackSnowMan's