Oracle SQL: ALTER
null에 대한 처리
null은 아직 값이 정해지지 않은 상태를 말하며 0과 ‘’ ‘ ‘과는 차이가 있다. 0,’’,’ ‘은 값이 있는 상태이고 null은 값이 없는 상태이다.
where phone is null이라고 검색하였는데 나와야 하는 데이터가 안나온다면 값에 ‘null’이 들어갔는지 확인할 필요가 있다.
** null인 레코드를 찾기 위해선 비교연산자를 사용할 수 없다.
select * from customer where phone = null; xxx
=> select * from customer where phone is null; is not null
만약 null인 경우 다른 값으로 대체하려면 nvl 함수를 이용한다. select eno, ename, nvl(salary,0)+10 from emp where ename = ‘윤석열’; select eno, ename, nvl(salary,(select avg(salary) from emp e2 where e1.dno = e2.dno))+10 from emp where ename = ‘윤석열’;
- 집계 함수 계산에 null이 포함된 행은 집계에서 빠진다.
avg함수라면 전체합을 전체레코드수로 나누는 것이 아니라 null이 아닌 개수로 나눠주게 된다.
select nvl(avg(salary),0) from emp;
-> 전체레코드 수로 나누기하려면 nvl 함수를 이용하여 다른값으로 대체한다.
- 해당하는 행이 하나도 없을 경우 sum, avg 함수의 결과는 null이 되며 count 함수의 결과는 0이 된다.
select sum(saleprice), avg(saleprice), count(custid) from orders where custid=1; select nvl(sum(saleprice),0),nvl(avg(saleprice),0), count(custid) from orders where custid=14;
select name,nvl(sum(saleprice),0), nvl(avg(saleprice),0), count(*) from orders o, customer c where o.custid = c.custid group by name; xxxxx 이렇게 조인하면 둘다 있는것만 뜨니까 구매내역 없는 사람 출력 x — **left outer join이나 right outer join으로 해야함~!!
select name,nvl(sum(saleprice),0), nvl(avg(saleprice),0), count(*) from customer c left outer join orders o on c.custid = o.custid group by name;
rownum (행번호 붙이기)
select에 의해 검색된 결과에 차례대로 순번을 매겨주는 가상컬럼 select에 의해 검색된 결과로부터 일부분만 잘라내기 위하여 rownum 사용
select rownum, ename from emp; select rownum, ename from emp where rownum <=3;
select ename from emp where rownum <=3;
select eno, ename, dname from emp e, dept d where e.dno = d.dno and dloc = ‘서교동’ and substr(jumin,8,1)=’2’ and rownum <=2;
가장 비싼 도서의 정보를 출력 select * from book where rownum = 1 order by price desc; xxxxxx rownum조건절을 정렬보다 먼저 실행시켜버리기 때문에 결과xxxxx
** order by 한 것에다가 rownum 조건식을 사용하려면 반드시 서브쿼리를 사용해야한다! order by 로 정렬한 select의 결과를 from절의 가상의 테이블로 두고 사용**
select * from book order by price desc;
select * from (select * from book order by price desc) where rownum =1;
select * from (select eno, ename, nvl(salary,0) salary from emp e, dept d where e.dno = d.dno and dname like ‘개발%’ order by salary desc) where rownum <=2;
select eno, ename, salary from emp e, dept d where e.dno = d.dno and dname like ‘개발%’ order by salary desc;
판매수량이 가장 저조한 2권ㅢ 도서
orders 테이블에서 도서번호별로 판매수량을 출력
select bookid, count(bookid) from orders group by bookid order by count(bookid) desc;
select bookid from (select bookid, count(bookid) from orders group by bookid order by count(bookid) desc) where rownum <=2;
select * from book where bookid in (select bookid from (select bookid, count(bookid) from orders group by bookid order by count(bookid)) where rownum <=2);
판매가 되지 않은 도서를 포함시켜야 한다.
select b.bookid, count(o.bookid) from book b left outer join orders o on b.bookid = o.bookid group by b.bookid order by count(o.bookid);
판매수량인 1권인 모든 도서를 절판도서에 포함
도서번호별로 판매수량으로 정렬하여 3번째에 해당하는 판매수량을 검색 select b.bookid, count(o.bookid) cnt from book b left outer join orders o on b.bookid = o.bookid group by b.bookid order by count(o.bookid)
insert into Department values(10, ‘개발1팀’, null); insert into Department values(20, ‘개발2 팀’, null); insert into Department values(30, ‘개발3팀’, null);
insert into Employee values(1,’김수연’,’010-1111-1111’,’서울’,’여’,’팀장’,10); insert into Employee values(2,’박지예’,’010-1111-1111’,’서울’,’여’,’사원’,10); insert into Employee values(3,’유하림’,’010-1111-1111’,’서울’,’여’,’사원’,10); insert into Employee values(4,’이건우’,’010-1111-1111’,’서울’,’남’,’사원’,10); insert into Employee values(5,’임하연’,’010-1111-1111’,’서울’,’여’,’사원’,10);
insert into Employee values(6,’최현호’,’010-1112-1111’,’서울’,’남’,’팀장’,20); insert into Employee values(7,’김지현’,’010-1113-1111’,’서울’,’여’,’사원’,20); insert into Employee values(8,’윤서우’,’010-1114-1111’,’서울’,’여’,’사원’,20); insert into Employee values(9,’정자영’,’010-1115-1111’,’서울’,’남’,’사원’,20);
update Employee set sex=’여’ where name=’정자영’;
update Department set manager=1 where deptno=10;
insert into Employee values(10,’윤태인’,’010-1112-1112’,’서울’,’남’,’팀장’,30); insert into Employee values(11,’김시아’,’010-1113-1113’,’서울’,’여’,’사원’,30); insert into Employee values(12,’노유나’,’010-1114-1114’,’서울’,’여’,’사원’,30); insert into Employee values(13,’박성미’,’010-1115-1115’,’서울’,’여’,’사원’,30); insert into Employee values(14,’임상진’,’010-1115-1116’,’서울’,’남’,’사원’,30);
insert into Project values(1, ‘우리은행 차세대 시스템’, 10); insert into Project values(2, ‘하나은행 차세대 시스템’, 20); insert into Project values(3, ‘국회도서관 고도화’, 30); insert into Project values(4, ‘쌍용교육센터 LMS’, 10);
insert into works values(1,1,0); insert into works values(2,1,0); insert into works values(3,1,0); insert into works values(4,1,0);
insert into works values(5,4,0);
insert into works values(6,2,0); insert into works values(7,2,0); insert into works values(8,2,0); insert into works values(9,2,0);
insert into works values(10,3,0); insert into works values(11,3,0); insert into works values(12,3,0); insert into works values(13,3,0); insert into works values(14,3,0);
select name from employee where empno in (select manager from department);
select name, address from employee where deptno = (select deptno from department where deptname =’개발3팀’);
select deptno from department where manager = (select empno from employee where name =’김수연’);
select count(*) from employee where deptno = (select deptno from department where manager = (select empno from employee where name =’김수연’)) and position=’사원’;’
update works set hours_worked = 40; update works set hours_worked = 40 where projno =3; update works set hours_worked = 80 where projno =2; update works set hours_worked = 100 where empno =3; update works set hours_worked = 90 where empno =8; update works set hours_worked = 70 where empno =13;
select deptname, name, hours_worked from employee e, department d, project p, works w where e.deptno = d.deptno and p.projno = w.projno and e.empno = w.empno order by deptname, name;
select p.projno, projname, count(empno) from project p, works w where p.projno=w.projno group by p.projno, projname having count(empno)>=2;
select deptno from employee group by deptno having count(empno)>=5;
select name from employee where deptno in(select deptno from employee group by deptno having count(empno)>=5);
서브쿼리
SQL 문장안에 포함되는 또다른 SQL문
서브쿼리의 위치에 따라 부르는 이름
select 절 스칼라 서브쿼리 from 절 인라인 뷰 where 중첩 서브쿼리
동작방식에 따라 부르는 이름
상관 서브쿼리 메인쿼리와 조건식이 필요한 경우 비상관 서브쿼리 메인쿼리와 조건식이 필요없는 경우
반환하는 레코드의 수에 따라 부르는 이름
단일행 서브쿼리 서브쿼리의 결과가 1건인 경우 다중행 서브쿼리 서브쿼리의 결과가 여러건인 경우
스칼라 서브쿼리의 실습
=>select절에 서브쿼리가 오는것
고객번호별로 총구매액을 출력 select custid, sum(saleprice) from orders group by custid;
고객별로 총구매액을 출력 고객번호, 고객이름, 총구매액 출력
조인으로 하는 경우
select c.custid, name, sum(saleprice) from customer c, orders o where c.custid = o.custid group by c.custid, name;
스칼라 서브쿼리 쓰는 경우
select custid,(select name from customer c where c.custid = o.custid ) name, sum(saleprice) from orders o group by custid;
alter table orders add bookname varchar2(40);
update orders o set bookname = (select bookname from book b where o.bookid = b.bookid);
인라인 뷰
from 절에 오는 서브쿼리
고객번호가 2이하인 고객이름과 총판매액 출력
select name, sum(saleprice) from customer c, orders o where c.custid = o.custid and c.custid<=2 group by name;
select name, sum(saleprice) from (select custid, name from customer where custid<=2 ) c, orders o where c.custid = o.custid group by name; —
중첩 서브쿼리
서브쿼리 where절에 조건식이 오는경d=우
서브쿼리의 건수가 다중행일때는 = 대신에 in != 대신에 not in <,>,>=,<=,any(some),all in, any(some), all exists not exists
select orderid, saleprice from orders where saleprice <= (select avg(saleprice) from orders);
select orderid, custid, saleprice from orders o1 where saleprice > (select avg(saleprice) from orders o2 where o1.custid = o2.custid ) order by custid;
select custid, avg(saleprice) from orders group by custid order by custid;
중첩서브쿼리의 결과가 다중행 일때의 실습
단일행 연산자를 사용할 수 없으며 다중행 연산자 in, not in, any(som), all을 사용
select sum(saleprice) from orders where custid in (select custid from customer where address like ‘%대한민국%’);
박지성이 구매하지 않은 도서정보 select * from book where bookid not in (select bookid from orders where custid = (select custid from customer where name=’박지성’));
select * from customer where custid in (select distinct custid from orders where bookid in (select bookid from orders where custid = (select custid from customer where name=’박지성’))) and name !=’박지성’;
select distinct custid from orders where bookid in (select bookid from orders where custid = (select custid from customer where name=’박지성’));
박지성이 구매한 도서번호들 select bookid from orders where custid = (select custid from customer where name=’박지성’)
insert into orders values(19,3,1,10000,sysdate, null); insert into orders values(20,3,2,10000,sysdate, null); insert into orders values(21,3,3,10000,sysdate, null); insert into orders values(22,3,13,10000,sysdate, null); insert into orders values(23,2,1,10000,sysdate, null); insert into orders values(24,2,3,10000,sysdate, null); insert into orders values(25,6,1,10000,sysdate, null);
select custid from orders where bookid in = ()
select custid, count(bookid) from orders where bookid in(select bookid from orders where custid = (select custid from customer where name = ‘박지성’)) and custid != (select custid from customer where name = ‘박지성’) group by custid order by count(bookid) desc;
위의 결과에 rownum을 붙여서 제일 첫번째꺼 행의 custid를 출력
select custid from () where rownum = 1;
select custid from (select custid, count(bookid) from orders where bookid in(select bookid from orders where custid = (select custid from customer where name = ‘박지성’)) and custid != (select custid from customer where name = ‘박지성’) group by custid order by count(bookid) desc) where rownum = 1;
select * from book where custid = ();
가장 많이 구매
박지성이 구매한 도서와 동일한 도서를 구매한 고객 select distinct custid, count(bookid) from orders where bookid in (select bookid from orders where custid = (select custid from customer where name =’박지성’)) and rownum =1 group by custid order by count(bookid) desc;
박지성이 구매하지 안흥ㄴ 도번호
select bookid from () where bookid not in(select bookid from orders where custid = (select custid from customer where name =’박지성’));
박지성이 구매한 도서번호 select bookid from orders where custid = (select custid from customer where name =’박지성’);
박지성 번호 select custid from customer where name =’박지성’
select minus select
select union select
– 박지성과 구매성향이 가장 비슷한 사람의 구매목록 minus 박지성의 구매목록
박지성 고객에게 어떤 도서를 추천할까? 박지성과 동일한 도서를 가장 많이 구매한 사람의 구매도서중에서 박지성이 구매하지 않은 도서를 출력 1) 박지성이 구매한 도서목록 -> A 2) A를 구매한 사람들의 고객번호별로 구매건수를 기준으로 내림차순 정렬하여 맨 첫번째줄의 고객번호를 출력 -> B 3) B가 구매한 도서번호중에 박지성이 구매하지 않은 도서번호를 출력 -> C 4) 도서번호 C에 해당하는 도서번호를 출력
1) 박지성이 구매한 도서목록 -> A select bookid from orders where cutsid = (select custid from customer where name = ‘박지성’);
2) A를 구매한 사람들의 고객번호별로 구매건수를 기준으로 내림차순 정렬하여 맨 첫번째줄의 고객번호를 출력 -> B
select custid from ( select custid, count(bookid) from orders where bookid in (select bookid from orders where custid = (select custid from customer where name = ‘박지성’)) and custid != (select custid from customer where name = ‘박지성’) group by custid order by count(bookid) desc) where rownum = 1;
3) B가 구매한 도서번호중에 박지성이 구매하지 않은 도서번호를 출력 -> C
4) 도서번호 C에 해당하는 도서번호를 출력 select * from book where bookid in (select bookid from orders where custid = (select custid from( select custid, count(bookid) from orders where bookid in (select bookid from orders where custid = (select custid from customer where name = ‘박지성’)) and custid != (select custid from customer where name = ‘박지성’) group by custid order by count(bookid) desc) where rownum = 1) minus select bookid from orders where custid = (select custid from customer where name = ‘박지성’));
-가장 잘 팔리는 책 한권 출력 select bookid, count(bookid) from orders group by bookid order by count(bookid);
select bookid from (select bookid, count(bookid) from orders group by bookid order by count(bookid)) where rownum <=3; r가장 비슷하게 산 고객의 정보에서 가장 중복되지 않은 책하고 두묶음을 합해서
복습
select name, nvl(sum(saleprice),0), nvl(avg(saleprice),0), count(orderid) from orders o right outer join customer c on o.custid = c.custid group by name;
(select * from book order by price desc) ->rownum을 쓸때는 order by를 끝낸 sql쿼리를 from 의 가상테이블로 설정한다.
select * from (select * from book order by price desc) where rownum <=3;
select eno, ename, nvl(salary,0) from emp e where dno in (select dno from dept where dname like ‘개발%’) order by nvl(salary,0) desc;
select dno from dept where dname like ‘개발%’
select * from (select eno, ename, nvl(salary,0) from emp e where dno in (select dno from dept where dname like ‘개발%’) order by nvl(salary,0) desc) where rownum <=2;
select * from (select eno, ename, nvl(salary ,0) salary from emp e, dept d where e.dno = d.dno and dname like ‘개발%’ order by nvl(salary ,0) desc) where rownum <= 2;
select * from book where bookid in (select bookid from (select bookid, count(bookid) from orders group by bookid order by count(bookid)) where rownum <=3);
select bookid, count(bookid) from orders group by bookid order by count(bookid)
판매가 되지 않은 도서를 포함시킨다.
select b.bookid, count(o.bookid) from orders o right outer join book b on o.bookid = b.bookid group by b.bookid order by count(o.bookid);
select * from book where bookid in (select bookid from (select b.bookid, count(o.bookid) from orders o right outer join book b on o.bookid = b.bookid group by b.bookid order by count(o.bookid)) where rownum <=4);
판매수량순으로 정렬하였을때에 4번째 판매수량은 1권입니다. ==> 판매수량인 1권인 모든 도서를 절판도서에 포함시키려고 합니다.
- 도서번호별로 판매수량순으로 정렬하여 4번째에 해당하는 판매수량을 검색
select * from book where bookid in (select bookid from (select b.bookid, count(o.bookid) cnt from orders o right outer join book b on o.bookid = b.bookid group by b.bookid order by count(o.bookid)) where cnt <= (select cnt from (select rownum n, cnt from (select b.bookid, count(o.bookid) cnt from orders o right outer join book b on o.bookid = b.bookid group by b.bookid order by count(o.bookid))) where n=4));
select bookid from (select b.bookid, count(o.bookid) cnt from orders o right outer join book b on o.bookid = b.bookid group by b.bookid order by count(o.bookid)) where cnt <= (select cnt from (select rownum n, cnt from (select b.bookid, count(o.bookid) cnt from orders o right outer join book b on o.bookid = b.bookid group by b.bookid order by count(o.bookid))) where n=4);
select cnt from (select rownum n, cnt from (select b.bookid, count(o.bookid) cnt from orders o right outer join book b on o.bookid = b.bookid group by b.bookid order by count(o.bookid))) where n=4;
select b.bookid, count(o.bookid) cnt from orders o right outer join book b on o.bookid = b.bookid group by b.bookid order by count(o.bookid);
select name from employee where empno in (select manager from department);
select count(*) from employee where deptno = (select deptno from department where manager = (select empno from employee where name = ‘김수연’));
select deptno from department where manager = (select empno from employee where name = ‘김수연’);
select empno from employee where name = ‘김수연’
select p.projno, projname, count(empno) from project p, works w where p.projno = w.projno group by p.projno, p.projname having count(empno)>=2;
select name from employee where deptno in (select deptno from employee group by deptno having count(*) >=5);
select deptno from employee group by deptno having count(*) >=5;
select custid, sum(saleprice) from orders group by custid;
select c.custid, name, sum(saleprice) from orders o, customer c where o.custid = c.custid group by c.custid, name;
select custid, (select name from customer c where c.custid = o.custid) name, sum(saleprice) from orders o group by custid;
select name, sum(saleprice) from customer c, orders o where c.custid = o.custid and c.custid <= 2 group by name;
select name, sum(saleprice) from (select custid, name from customer where custid <=2) c, orders o where c.custid = o.custid group by name;
select * from book where bookid not in (select bookid from orders where custid = (select custid from customer where name = ‘박지성’));
select bookid from orders where custid = (select custid from customer where name = ‘박지성’);
select * from customer where custid in (select distinct custid from orders where bookid in (select bookid from orders where custid = (select custid from customer where name = ‘박지성’))) and name != ‘박지성’;
박지성이 구매한 도서 select bookid from orders where custid = (select custid from customer where name = ‘박지성’);
박지성과 동일한 도서를 구매한 사람의 고객번호 select distinct custid from orders where bookid in (select bookid from orders where custid = (select custid from customer where name = ‘박지성’)) and custid != (select custid from customer where name=’박지성’);
박지성이 구매한 도서와 동일한 도서를 가장 많이 구매한 고객의 고개번호 select custid from (select custid, count(bookid) from orders where bookid in (select bookid from orders where custid = (select custid from customer where name = ‘박지성’)) and custid != (select custid from customer where name=’박지성’) group by custid order by count(bookid) desc) where rownum =1;
select custid, count(custid) from orders where bookid in (select bookid from orders where custid = (select custid from customer where name = ‘박지성’)) and custid != (select custid from customer where name=’박지성’) group by custid order by count(custid) desc;
select * from book where bookid in (select bookid from (select distinct bookid from orders where custid = (select custid from (select custid, count(bookid) from orders where bookid in (select bookid from orders where custid = (select custid from customer where name = ‘박지성’)) and custid != (select custid from customer where name=’박지성’) group by custid order by count(bookid) desc) where rownum =1)) where bookid not in (select bookid from orders where custid = (select custid from customer where name = ‘박지성’)));
박지성이 구매한 도서와 동일한 도서를 가장 많이 구매한 고객의 고객번호 select custid from (select custid, count(bookid) from orders where bookid in (select bookid from orders where custid = (select custid from customer where name = ‘박지성’)) and custid != (select custid from customer where name=’박지성’) group by custid order by count(bookid) desc) where rownum =1;
박지성이 구매한 도서와 동일한 도서를 가장 많이 구매한 고객이 구매한 도서들 select distinct bookid from orders where custid = (select custid from (select custid, count(bookid) from orders where bookid in (select bookid from orders where custid = (select custid from customer where name = ‘박지성’)) and custid != (select custid from customer where name=’박지성’) group by custid order by count(bookid) desc) where rownum =1);
위의 도서번호 중에서 박지성이 구매하지 않은 도서번호를 출력 select bookid from (select distinct bookid from orders where custid = (select custid from (select custid, count(bookid) from orders where bookid in (select bookid from orders where custid = (select custid from customer where name = ‘박지성’)) and custid != (select custid from customer where name=’박지성’) group by custid order by count(bookid) desc) where rownum =1)) where bookid not in (select bookid from orders where custid = (select custid from customer where name = ‘박지성’));
select~~ minus~~~ select~~~
– select ~~~ union~~~~ select ~~~
박지성과 구매성향이 가장 비슷한 사람의 구매목록 minus 박지성의 구매목록
select * from book where bookid in (select distinct bookid from orders where custid = (select custid from (select custid, count(bookid) from orders where bookid in (select bookid from orders where custid = (select custid from customer where name = ‘박지성’)) and custid != (select custid from customer where name=’박지성’) group by custid order by count(bookid) desc) where rownum =1) minus select bookid from orders where custid = (select custid from customer where name = ‘박지성’));
박지성이 구매한 도서목록->a select bookid from orders where custid = (select custid from customer where name = ‘박지성’);
a를 구매한 사람들의 고객번호별로 구매건수를 기준으로 내림차순 정렬하여 맨 첫번째줄의 고객번호를 출력->b select custid from (select distinct custid, count(bookid) from orders where bookid in (select bookid from orders where custid = (select custid from customer where name = ‘박지성’)) group by custid order by count(bookid) desc) where rownum=1;
select distinct custid, count(bookid) from orders where bookid in (select bookid from orders where custid = (select custid from customer where name = ‘박지성’)) group by custid order by count(bookid) desc;
b가 구매한 도서번호 중에 박지성이 구매하지 않은 도서번호를 출력
select distinct bookid from orders where custid = (select custid from (select distinct custid, count(bookid) from orders where bookid in (select bookid from orders where custid = (select custid from customer where name = ‘박지성’)) group by custid order by count(bookid) desc) where rownum=1);
select * from (select distinct bookid from orders where custid = (select custid from (select distinct custid, count(bookid) from orders where bookid in (select bookid from orders where custid = (select custid from customer where name = ‘박지성’)) group by custid order by count(bookid) desc) where rownum=1)) where bookid not in (select bookid from orders where custid = (select custid from customer where name = ‘박지성’)); 4) select * from book where bookid in (select * from (select distinct bookid from orders where custid = (select custid from (select distinct custid, count(bookid) from orders where bookid in (select bookid from orders where custid = (select custid from customer where name = ‘박지성’)) group by custid order by count(bookid) desc) where rownum=1)) where bookid not in (select bookid from orders where custid = (select custid from customer where name = ‘박지성’)));
가장 잘팔리는 책 한권 출력 select bookid, count(bookid) from orders group by bookid order by count(bookid) desc;
select bookid from (select bookid, count(bookid) from orders group by bookid order by count(bookid) desc) where rownum <=3;
박지성과 구매성향이 가장 비슷한 사람의 구매목록 union 가장 잘팔리는 책 세권 minus 박지성의 구매목록 -> 박지성에게 추천할 도서 목록!
select * from book where bookid in (select * from (select distinct bookid from orders where custid = (select custid from (select distinct custid, count(bookid) from orders where bookid in (select bookid from orders where custid = (select custid from customer where name = ‘박지성’)) group by custid order by count(bookid) desc) where rownum=1)) union select bookid from (select bookid, count(bookid) from orders group by bookid order by count(bookid) desc) where rownum <=3 minus select bookid from orders where custid = (select custid from customer where name = ‘박지성’));