day 0612 데이터베이스(2)

데이터베이스 명령어의 종류

1. DDL 데이터 정의어

2. DCL 데이터 제어어

3. DML 데이터 조작어

    추가 insert

    조회 select

    수정 update

    삭제 delete

 

-- select문의 형식
select * form 테이블명;
select * form 테이블명 where 조건절;
select 칼럼명 form 테이블명 where 조건절;

select 집계함수, 칼럼명
from 테이블명
[where 조건절]                                        <-- 조회하고자하는 레코드의 조건식을 써준다
[group by 칼럼명]                                    <-- 특정 칼럼의 값의 종류별로 집계함수를 나타내고자 할 때 사용
[order by 칼럼명]; (group by뒤에 온다)   <-- 조회한 칼럼중에 특정 칼럼을 기준으로 정렬하고자 할 때 사용

 

-- clear screen 화면 지우기

 

직책별로 사원의 수, 평균급여액을 출력하시오

(사원수가 높은순으로 출력합니다)

 

-- 한 건만 나온다

select count(*), avg(salary) from emp;

 

-- group by 사용

select job, count(*), avg(salary) from emp group by job;

 

부서명별로 사원의 수, 총 급여액, 평균 급여액을 출력하시오 // 부서명 dept, 급여 emp 

(단, 총 급여액이 높은순으로 출력합니다)

             부서명 / 사원수 / 총급여액 / 평균급여액
select dname, count(*), sum(salary), avg(salary)
from emp e, dept d
where e.dno = d.dno
group by dname
order by sum(salary) desc;

 

-- 별칭주기 (as 별칭)

select dname, count(*) cnt, sum(salary) sum, avg(salary) avg
from emp e, dept d
where e.dno = d.dno
group by dname
order by sum desc;

 

개발팀에 근무하는 직원들의 직책별 평균 급여액을 출력합니다

(평균 급여액이 높은순으로 출력합니다)

select job, avg(salary)
from dept d, emp e
where d.dno = e.dno and
dname like '%개발%'
group by job
order by avg(salary) desc;

 

'대한미디어'나 '이상미디어'의 도서명별로 판매건수를 출력합니다 (~의 where)

(단, 판매건수가 높은순으로 출력)

select bookname, count(*)
from book b, orders o 
where b.bookid = o.bookid and
publisher in ('대한미디어', '이상미디어')
group by bookname
order by count(*) desc;

 

위의 결과에서 만약에 판매건수가 2개 이상인 것만 조회하고 싶을 때

사용하는 구문이 having 입니다

 

<< having의 사용 >>

group by 절에 나타난 결과에 대하여 조건식을 주고자 할 때 사용한다

 

'대한미디어'나 '이상미디어'의 도서명별로 판매건수를 출력합니다

(단, 판매건수가 2건 이상만 출력하고 판매건수가 높은순으로 출력)

select bookname, count(*)
from book b, orders o
where b.bookid = o.bookid and
publisher in ('대한미디어', '이상미디어')
group by bookname
having count(*) >= 2
order by count(*) desc;

 

<< 데이터 조회하는 형식 >>

 

select 칼럼명

from 테이블명

[where 조건식]

[group by 칼럼명]

[having 조건식]

[order by 칼럼명]

 

고객명별로 구매건수와 총 구매금액을 출력하시오

(단, 총 구매금액이 10000원 이상인 것만 출력하고 총 구매금액이 높은 순으로 출력)

select name, count(*), sum(saleprice)
from customer c, orders o
where c.custid = o.custid
group by name
having sum(saleprice) >= 10000
order by sum(saleprice) desc;

 

'판교'나 '종각'에 근무하는 직원들의 부서명별로 근무하는 직원의 수, 총 급여액, 평균 급여액을 출력하시오

(단, 직원수가 2명 이상인 부서만 출력 / 총 급여액이 높은순으로 출력 / 총 급여액이 동일하면 평균급여액 순으로 출력)

select dname, count(*), sum(salary), avg(salary)
from emp e, dept d
where e.dno = d.dno and
dloc in ('판교', '종각')
group by dname
having count(*) >= 2
order by sum(salary) desc, avg(salary);

 

'축구' 관련 도서에 대하여 도서명별로 판매건수를 출력합니다

(단, 판매건수가 높은순으로 출력합니다)

select bookname, count(*)
from book b, orders o
where b.bookid = o.bookid and
bookname like '%축구%'
group by bookname
order by count(*) desc;

 

---

 

14:00 ~

 

팀프로젝트 공지

-- 팀장 정하기

-- 팀이름 정하기

-- 1차 프로젝트 주제 선정 (기획/설계) : 6/18 5교시

      java/oracle 연동, sql 복잡하게 만들어보기

       포트폴리오는 간단하게 pdf로

         이후 일정은 추후 안내

 

1차 프로젝트 최종 발표 : 7/2

 

 

직원이름과 그 직원의 관리자이름 출력
emp 테이블 mgr은 emp 테이블에 있는 eno를 참조

 

<< self 조인 >>

 

'박대리'의 관리자는 '이과장'입니다

emp 테이블에 mgr은 emp 테이블에 eno를 참조합니다

만약 모든 직원의 이름과 관리자 이름을 출력해야 한다면

self 조인을 할 수 있다

 

실제로 물리적으로 테이블은 emp라는 것 한개만 있다

이것을 한번은 "직원"이라고 보고 한번은 "관리자"로 보여

두개를 조인하여 직원이름과 관리자이름을 조회할 수 있다

 

-- 실제로는 하나의 테이블인데 칼럼의 값이 그 테이블의 다른 칼럼을 참조할 때 사용하는 조인

-- emp 테이블의 mgr은 emp 테이블의 eno를 참조하고 있다

   만약 직원의 이름과 관리자 이름을 출력하고자 한다면 emp 테이블을

   하나는 "직원"이라고 별칭을 주고 하나는 "관리자"라고 별칭을 주어 조인할 수 있다

   이것을 self 조인이라고 한다

 

-- 별칭

emp e 직원

emp m 관리자

 

select e. ename, m.ename 
from emp e, emp m
where e.mgr = m.eno and; 
(직원에 있는 mgr번호는 관리자에 있는 eno를 참조한다)

 

'개발'팀에 근무하는 모든 직원들의 사원번호, 사원명, 관리자명, 부서번호, 부서명을 출력

select e.eno, e.ename, m.mgr, e.dno
from emp e, emp m, dept d
where e.mgr = m.eno and e.dno = d.dno and
dname like '%개발%';

 

'개발'팀에 근무하는 직원들중에 관리자보다 입사일이 빠른 직원의

사원번호, 사원명, 관리자명, 입사일, 관리자의 입사일 출력

select e.eno, e.ename, m.ename, e.hiredate, m.hiredate
from emp e, emp m, dept d
where e.mgr = m.eno and e.dno = d.dno and
dname like '개발%' and
e.hiredate < m.hiredate;

 

'판교'나 '종각'에 근무하고 직책이 '사원'이거나 '대리'인 직원들중에 입사일이 관리자보다 빠르거나

급여가 관리자보다 많은 직원들의 사원번호, 사원명, 관리자명,입사일, 급여, 관리자의 급여를 출력

(단, 입사일 순으로 출력하고 동일할 때는 급여가 높은순으로 출력)

select e.eno, e.ename, e.hiredate, e.salary, m.ename, m.hiredate, m.salary
from emp e, emp m, dept d
where e.mgr = m.eno and e.dno = d.dno and
dloc in ('판교', '종각') and
e.job in ('사원', '대리') and
(e.hiredate < m.hiredate or e.salary > m. salary)
order by e.hiredate, e.salary desc;

 

'박대리'의 부하직원들의 사원번호, 사원명, 입사일, 급여를 출력하시오

(단, 입사일 순으로 출력)

select e.eno, e.ename, e.hiredate, e.salary
from emp e, emp m
where e.mgr = m.eno and 
m.ename = '박대리';

 

<< select 문의 형식 순서 >>

5. select

1. from

2. where

3. group by

4. having

6. order by

 

고객번호별로 주문한 건수를 출력 (조인 x)

select custid, count(*)
from orders
group by custid;

 

고객이름별로 주문한 건수를 출력 (조인 o)

select c.name, count(*)
from customer c, orders o 
where c.custid = o.custid
group by name;

 

-- 주문이 없는 고객은 출력되지 않는다 (inner join)

   양쪽 테이블에 모든 조건을 만족하는 레코드만 조회한다

-- 만약 주문이 없는 고객이름도 출력하고싶다면 (outer join)

   outer join을 이용한다

 

일반적인 조인(inner join)

:  두개의 테이블에 모두 조건식을 만족하는 레코드를 검색

 

외부 조인(outer join)

: 두개의 테이블에 왼쪽이나 오른쪽에 레코드는

  조건을 만족하지 않더라도 모두 검색

 

-- left outer join

  조건을 만족하지 않더라도 왼쪽의 테이블은 무조건 검색

 

-- right outer join

  조건을 만족하지 않더라도 오른쪽의 테이블은 무조건 검색

 

<< outer join의 형식 >>

 

select 칼럼명

from 테이블1 right/left outer join 테이블2

on 조건식;

 

모든 고객에 대한 주문건수를 출력

(단, 주문이 없는 고객도 출력)

select c.name, count(saleprice)
from customer c left outer join orders o 
on c.custid = o.custid
group by name;

 

ed ff

set linesize 100;
set pagesize 100;

column bookid format 9999;
column bookname format a20;
column price format 999,999;
column publisher format a20;

column custid format 9999;
column name format a10;
column address format a20;
column phone format a15;

column eno format 9999;
column ename format a10;
column job format a10;
column hiredate format a15;
column salary format 999,999;
column dno format 9999;
column mgr format 9999;

@@ ff

 

관리자 이름별로 부하직원의 수를 출력하시오

(단, 부하직원이 없는 직원의 이름도 출력)

select m.ename, count(e.ename)
from emp m left outer join emp e
on e.mgr = m.eno
group by m.ename;

 

부서명별로 총직원의 수를 출력하시오

(단, 소속된 직원이 없는 부서명도 출력)

select dname, count(ename)
from dept d left outer join emp e
on d.dno = e.dno
group by dname;

 

출판사별로 총 주문건수, 총 주문금액을 출력

(주문내역이 없는 출판사도 출력, 없어도 나타나는건 left outer) 

select publisher, count(saleprice), sum(saleprice)
from book b left outer join orders o
on b.bookid = o.bookid
group by publisher;

 

-- count 함수는 조건을 만족하는 레코드가 한 개도 없으면 0

-- count 이외의 집계함수는 조건을 만족하는 레코드가 없으면 null

-- null 값을 다른값으로 채우고 싶으면 nvl 함수 이용

 

nvl(칼럼명, 채울 값)

 

select publisher, count(saleprice) cnt, nvl(sum(saleprice), 0) sum
from book b left outer join orders o
on b.bookid = o.bookid
group by publisher;

 

- 오늘 학습한 내용에 대하여 요약 및 정리하고 궁금한 점 질문하도록 합니다

- 팀별 프로젝트 관련 회의를 진행합니다

- 프로그램 연습

  1) 관리자 이름을 입력받아 그 관리자의 부하직원의 정보를 출력하는 프로그램을 작성 해 봅니다

  2) 출판사명을 입력받아 해당 출판사의 총 구매건수, 총 구매금액을 조회하는 프로그램을 작성 해 봅니다

      (단, 구매건수가 없는 출판사명도 출력하도록 합니다)