2022.04.20. 수요일 이공계 전문기술 연수사업 18일차

오늘 배운 것

ex)2022년 4월 3일과 4월 10일 사이에 주문한 건 중에 고객별로 총 구매건수와 총 구매금액을 출력
단 총 판매건수가 2건 이상인 것만 출력하고 총 판매건수가 높은 순으로 출력
select custid, count(*), sum(saleprice) from orders
where orderdate between '2022/04/03' and '2022/04/10' group by custid having count(*)>=2
order by count(*) desc;
//having의 존재 잊지 말고 공부하기 !

------------------------------------------------------------------------------------------
join
ㄴ관계형 데이터베이스에서 검색하고자하는 컬럼이 두 개 이상의 테이블에 있을 때 join사용
조인할 땐 두 개의 테이블에 공통으로 들어가는 컬럼으로 조건식을 표현함

ex)박지성 고객이 주문한 모든 도서명과 도서의 가격 구매가격 주문일을 출력
select bookname, price, saleprice, orderdate from book, customer, orders
where name= '박지성' and
customer.custid=orders.custid and
orders.bookid=book.bookid;

ex)축구의 이해를 구매한 모든 고객의 이름,주소,전화,구매일을 출력하시오
select name, address, phone, orderdate from customer,orders,book where bookname='축구의 이해' 
and customer.custid=orders.custid and orders.bookid=book.bookid;

ex)축구 관련 도서 중에 가격이 8000원 이상인 도서를 구매한 고객의 이름, 도서명, 출판사명 구매일을 출력하시오
단, 최근의 구매일 순으로 출력하고 동일할 때에는 고객이름 순으로 출력
select name,bookname,publisher,orderdate from book b,orders o,customer c 
where bookname like '축구%' and price>=8000 and
c.custid=o.custid and b.bookid=o.bookid
order by orderdate desc, name;

//where 다음 조인식을 써주는 것임!

ex)대한민국에 거주하는 고객별로 총주문건수, 총주문금액을 출력하시오 단, 총 주문금액이 만원이상인 것만 출력
select name, count(*), sum(saleprice) from orders, customer where address like '대한민국%' 
and customer.custid=orders.custid group by name having sum(saleprice)>=10000;

질문) having에 사용한 sum(saleprice)>= 10000 이 조건식을 where절에 사용하면 오류임
왜? where는 집계함수를 적용할 조건식(무엇에 대하여 개수를 구하고 합을 구할 것인지 그런 '무엇', 대상에 대해서 할건지를 where에 써줌)
그 sum한 결과에 대하여 조건식을 주는 것이 having (group by 절에 나타난 결과에 대하여 조건식을 표현할 때 사용하는 것이 having임!)

ex)모든 도서의 수를 출력
select count(*) from book; -레코드가 한 개 나옴

ex)출판사별로 총 도서의 수를 출력
select publisher,count(*) from book group by publisher; -레코드가 출판사의 수만큼 나타남
ㄴ이렇게 나타난 결과에 대해서 총 도서의 수가 두 건 이상인 것만 출력하고 싶다 ! -> having(그룹바이절에 나타난 조건을 줄 때는 해빙)
ㄴ이렇게 나타난 결과에 대해서 가격이 20000원 이상인 것만 출력하고 싶다 ! -> where
//솔직히 무슨 차이지..?

ex)도서의 가격이 팔천원이상인 도서에 대하여 출판사별로 총 도서의 수를 출력하시오
단, 총 도서의 수가 두 건 이상인 것만 출력 
select publisher,count(*) from book where price>=8000 group by publisher having count(*)>=2;

ex)2022년 4월 3일에서 10일 사이에 판매된 도서명별로 총 판매건수를 출력
select bookname, count(*) from book,orders 
where orderdate between '2022/04/03' and '2022/04/10' and book.bookid=orders.bookid 
group by bookname;

ex)2022년 4월 3일에서 10일 사이에 판매된 도서명별로 총 판매건수를 출력 단 판매 건수가 두 건 이상인 것만 출력
select bookname, count(*) from book,orders 
where orderdate between '2022/04/03' and '2022/04/10' and book.bookid=orders.bookid 
group by bookname having count(*) >=2;

ex)출판사명이 '미디어'로 끝나는 출판사의 도서를 구매한 고객이름 별로 총 주문 건수, 총 주문금액을 출력하시오
단, 총 주문금액이 만원이상인 것만 출력합니다 또 총 주문 금액이 높은 순으로 출력하고 동일할 때는 이름순으로 출력
select name,count(*),sum(saleprice) from customer, orders, book 
where publisher like '%스포츠' 
and customer.custid=orders.custid and book.bookid=orders.bookid 
group by name having sum(saleprice)>=10000 order by sum(saleprice) desc, name;

ex)2022년 4월 3일에 구매한 고객의 고객번호와 이름과 주소를 출력
select custid, name, address from customer c, orders o
where orderdate='2022/04/03' and
c.custid=o.custid;
=>이건 오류남 ! 왜?
name,address는 customer테이블에만 있지만 custid는 customer테이블에도 있고 orders테이블에도 있기 때문에
어디에 있는 custid를 출력해야할 지 모르겠어서 오류나는 것임.
select c.custid, name, address from customer c, orders o
where orderdate='2022/04/03' and
c.custid=o.custid;로 바꾸면 됨.

ex)대한민국에 거주하는 고객이 2022년 4월 3일에서 10일 사이에 축구관련 도서를 구매한 
고객번호,고객명,도서번호,도서명,도서가격,구매가격을 출력하시오 단,최근의 구매일순으로 출력하고 동일하면 이름순으로 출력
select c.custid,name,b.bookid,bookname,price,saleprice,orderdate 
from book b ,customer c ,orders o
where address like '대한민국%' and orderdate between '2022/04/03' and '2022/04/10' 
and bookname like '%축구%' 
and c.custid=o.custid and b.bookid=o.bookid 
order by orderdate desc,name;

축구관련 도서 1,2
대한민국거주 2,3,6
insert into orders values (11,2,1,20000,'2022/04/03');
insert into orders values (12,2,2,8000,'2022/04/03');
insert into orders values (13,5,2,21000,'2022/04/08');
insert into orders values (14,6,2,22000,'2022/04/08');

create table dept(
dno number primary key, 
dname varchar2(20), 
dloc varchar2(20)
);

insert into dept values (10,'총무팀','서교동');
insert into dept values (20,'기획팀','판교');
insert into dept values (30,'개발1팀','서교동');
insert into dept values (40,'개발2팀','판교');

create table emp(
eno number primary key,
ename varchar2(20),
dno number references dept(dno),
salary number,
comm number,
hiredate date,
phone varchar2(20),
addr varchar2(50),
mgr number,
job varchar2(30)
);

insert into emp values(1000,'박정수',10,700,70,'2005/01/05','010-1234-1111','서울',null,'사장');
insert into emp values(1001,'김도연',40,500,50,'2020/03/05','010-1234-2222','인천',1000,'과장');

ex)개발팀의 평균 급여 출력
select avg(salary) from emp where dno=30 or dno=40;

ex)부서 이름별로 근무자의 수를 출력
select dname, count(*) from dept, emp where dept.dno=emp.dno group by dname;
where 다음이 조인식!!!!!!!!!!!!!!!!

ex)사원의 이름, 직위를 출력
select ename, job from emp;

ex)판교에 근무하는 직원의 이름, 부서번호, 부서명, 급여, 수당, 실수령액을 출력
단, 실수령액이 높은 순으로 출력하고 동일하면 이름 순으로 출력
select ename, emp.dno, dname, salary, comm, salary+comm 
from dept, emp 
where dloc='판교' and dept.dno=emp.dno 
order by salary+comm desc, ename;

ex)개발 팀에 근무하는 사원들의 급여를 10%인상하려고 함.
해당 직원들의 사원번호, 부서번호, 부서명, 부서위치, 급여, 증가된 급여분,10% 인상된 급여를 사원번호 순으로 출력
select eno,e.dno,dname,dloc,salary,0.1*salary,1.1*salary 
from dept d, emp e 
where dname like '개발%' and d.dno=e.dno 
order by eno;

ex)김씨인 모든 직원들의 사원 이름과 부서번호 부서명을 출력
select ename,d.dno,dname 
from dept d,emp e 
where ename like '김%' and d.dno=e.dno;

ex)직위가 대리이거나 사원인 직원들의 최대급여, 최소급여, 총급여, 평균급여 출력
select max(salary), min(salary), sum(salary), avg(salary) 
from emp where job in ('대리','사원');

ex)판교에 근무하는 직급이 사원과 대리,과장들의 직급별 근무자의 수를 출력
단, 근무자의 수가 2명이상인 직급만 출력
select job, count(*) 
from emp e, dept d 
where dloc='판교' and job in ('사원','대리','과장') and d.dno=e.dno 
group by job 
having count(*)>=2;

ex)모든 직원의 "최대급여"와 "최소급여"의 차이를 출력
select max(salary)-min(salary) from emp;

ex)전체 직원들중에 직속상관이 있는 직원의 수를 출력
select count(mgr) from emp;
select count(*) from emp where mgr is not null;

is null
is not null 사용!

ex)사원번호가 1005번에서 1009번 사이의 직원들의 이름,부서번호,부서명,부서위치를 출력
select ename,d.dno,dname,dloc 
from emp e,dept d 
where eno between 1005 and 1009 and e.dno=d.dno;

ex)서교동에 근무하는 직원들 중에 직급이 '과장'인 사람들의 평균급여 출력
select avg(salary) 
from dept d,emp e 
where dloc='서교동' and job='과장' and d.dno=e.dno;

ex)서교동에 근무하거나 직급이 과장인 사람들의 사원번호,사원명,부서번호,부서명,부서위치,직급,급여를 출력
단,급여가 높은 순으로 출력
select eno, ename, e.dno, dname, dloc, job, salary 
from emp e, dept d 
where(dloc='서교동' or job='과장') and d.dno=e.dno 
order by salary desc;
//괄호를 안하면 and가 먼저 연산됨!
//like는 패턴을 따를 때 사용!

--------------------------------------------------------------------------
데이터베이스 연결 프로그래밍을 jsp로 학습

컴퓨터를 웹서버로 꾸미기 위하여 톰캣 설치

오늘 헷갈린 것

• having을 쓸 때와 where을 쓸 때의 차이
• 조인식은 무조건 where에 와야함

오늘 한 생각

sql 생각보다 어렵지 않다!

어떻게하면 자바 실력 올라갈까 궁리중이다....

복습 다 하고 토이플젝 조그마한거 해봐야겠다