Oracle SQL: group by
select count(*) from book group by publisher; 결과가 publisher의 종류만큼 이렇게만 하면 어느 출판사의 도서의 수인지 알 수 없다.
select publisher, count(*) from book group by publisher;
집계 함수를 사용할 떄는 groupby절에 쓴 컬럼만 select 절에 올 수 있다. 그 이외의 컬럼은 올 수 없다!
select custid, count(*) from orders group by custid;
select bookid, count(*) from orders group by bookid;
select publisher, avg(price), max(price), min(price) from book group by publisher;
select custid, avg(saleprice), max(saleprice) from orders group by custid;
select orderdate,count(*),sum(saleprice) from orders group by orderdate;
##join과 함께 group by사용
select name, count(*) from customer c, orders o where c.custid = o.custid group by name;
select bookname, count(*) from book b, orders o where b.bookid = o.bookid group by bookname;
select publisher, count(*), sum(saleprice) from book b, orders o where b.bookid = o.bookid group by publisher;
select name, sum(saleprice), avg(saleprice), count() from customer c, orders o where c.custid = o.custid group by name order by sum(saleprice) desc, count() desc;
having => group by 절에 나타난 결과에 의해 조건식을 부여할 때 사용
select 칼럼, 집계함수, ,,, from 테이블이름 where 조건식 group by 칼럼 having 조건식
select name, count(*) from customer c, orders o, book b where c.custid = o.custid and b.bookid = o.bookid and price >=8000 group by name;
select publisher, count(*), sum(saleprice) from book b, orders o where b.bookid = o.bookid and orderdate between ‘2021/11/01’ and ‘2021/11/07’ and bookname like ‘_구%’ group by publisher having sum(saleprice) >=10000 order by sum(saleprice) desc;
select bookname, count(), sum(saleprice) from book b, orders o where b.bookid = o.bookid group by bookname having count()>=2 order by count(*) desc, sum(saleprice) desc;
select bookname, count() from book b, orders o where publisher in(‘이상미디어’,’대한미디어’,’굿스포츠’) and b.bookid = o.bookid group by bookname having count() >= 2 order by count(*) desc, bookname;
select job, count(*), avg(salary) from dept d, emp e where d.dno = e.dno and dloc = ‘서교동’ group by job;
select dname, count(), avg(salary), sum(salary), max(salary), min(salary) from dept d, emp e where d.dno = e.dno and dname like ‘개발%’ group by dname having count()>=2 order by count(*) desc;
insert into emp values(1014,’윤석열’,20,null,null,sysdate,’010-1000-0001’,’서울’,1005,’사원’);
** null인 경우에 특정한 값으로 대체해서 출력 nvl(컬렴명, 대체값)
select ename, nvl(salary, 0) from emp;
select avg((nvl(salary,0)) from emp; select sum(salary)/count(*) from emp;
self join
동일한 테이블에서 어떤 칼럼의 자신의 다른 칼럼을 참조하는 경우에 self join사용
self 조인을 하는 방법은 실제로 물리적으로 테이블에 하나밖에 없으므로 그것을 각각 애칭을 주어 join식에 표현할 수 있다.
select ~~~ from 테이블 애칭1, 테이블 애칭2 where 조건식
select e.ename, m.ename from emp e, emp m where e.mgr = m.eno;
select m.ename from emp e, emp m where e.mgr = m.eno and e.ename = ‘임상진’;
select e.ename, e.salary, e.hiredate from emp e, emp m where e.mgr= m.eno and m.ename=’김시아’ order by hiredate desc;
select e.eno, e.ename, d.dno, d.dname, m.ename, e.salary from emp e, emp m, dept d where e.mgr = m.eno and e.dno = d.dno and dloc = ‘서교동’ order by salary desc, e.ename;
select e.eno, e.ename, m.ename, e.hiredate, m.hiredate, d.dno, d.dname from emp e, emp m, dept d where e.mgr = m.eno and e.dno = d.dno and m.hiredate > e.hiredate order by d.dname, e.ename;
select m.ename, count(*), count(e.salary), avg(e.salary) from emp e, emp m, dept d where e.mgr = m.eno and e.dno = d.dno and dloc = ‘서교동’ group by m.ename;
select e.mgr, count(e.eno), count(e.salary), avg(e.salary) from emp e, emp m, dept d where e.mgr = m.eno and e.dno = d.dno and dloc in(‘서교동’,’판교’) group by e.mgr having count(e.eno)>=2;
select name, saleprice from customer c, order o where c.custid = o.custid order by name;
outer join left outer join right outer join
select ~~~~ from 테이블1 left outer join 테이블2 on 조건식
select name, nvl(saleprice,0) from customer left outer join orders on customer.custid = orders.custid;
select name, count(), sum(saleprice), avg(saleprice) from orders o right outer join customer c on c.custid = o.custid and address like ‘대한%’ group by name order by count(),sum(saleprice) desc;
NAME COUNT(*) SUM(SALEPRICE) AVG(SALEPRICE) ———- ———- ————– ————– 박지성 1 추신수 1 박세리 1 이재명 1 20000 20000 윤석열 1 14000 14000 김연아 2 15000 7500 장미란 3 31000 10333.3333
대한민국에 거주하지 않는 추신수, 박지성도 나온다.
select name, count(saleprice), sum(saleprice), avg(saleprice) from orders o right outer join (select custid, name from customer where address like ‘대한민국%’) c on c.custid = o.custid group by name order by count(saleprice),sum(saleprice) desc;
서브쿼리
sql을 만들어서 그것을 sql에 포함시키는 것을 말한다 sql문에 포함되는 또다른 sql문을 말한다. select 절, from 절, where절에 올 수 있다.
select c.name, m.name from customer c
select e.ename, nvl(m.ename,’관리자없음’) from emp e left outer join emp m on e.mgr = m.eno;
select bookname, count(orderid) from orders o right outer join book b on o.bookid = b.bookid group by bookname order by count(orderid) desc;
select d.dname, count(ename), nvl(avg(salary),0) from dept d left outer join emp e on d.dno = e.dno group by d.dname;
select d.dname, count(ename), nvl(avg(salary),0) from dept d, emp e where d.dno = e.dno(+) group by d.dname;
select bookname from book where price = 22000;
select bookname from book where price = ( select max(price) from book);
sub쿼리의 위치 select절 from절 where절
update할때 set 칼럼명 = (서브쿼리) where 컬럼명 = (서브쿼리)
delete 할때 where절에 올 수 있다
select max(price) from book;
select bookid from book where price = (select max(price) from book);
select custid from orders where bookid = (select bookid from book where price = (select max(price) from book));
select name from customer where custid in (select custid from orders where bookid = (select bookid from book where price = (select max(price) from book)) );
select avg(salary) from emp;
select e.eno, e.ename, m.ename, dname from emp e, emp m, dept d where e.mgr = m.eno and e.dno = d.dno and e.salary > (select avg(salary) from emp);
select custid from customer where name = ‘박지성’
select bookid from orders where custid = (select custid from customer where name = ‘박지성’);
BOOKID
1
3
5
10
select distinct custid from orders where bookid in (select bookid from orders where custid = (select custid from customer where name = ‘박지성’));
select name 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 bookname from book where bookid not in (select bookid from orders where custid = (select custid from customer where name=’박지성’));
select custid from customer where name = ‘박지성’;
select bookid from orders where custid = (select custid from customer where name = ‘박지성’);
select bookname from book where bookid not in (select bookid from orders where custid = (select custid from customer where name = ‘박지성’));
숙제 -주문하지 않은 고객의 이름 출력
주문한 고객의 custid 중복없이 추출 select distinct custid from orders
select name from customer where custid not in (select distinct custid from orders);
-두 개 이상의 서로 다른 출판사에 도서를 구매한 고객의 이름 출력
1)고객별로 구매한 출판사의 수를 출력 select name, count(distinct publisher) cnt from customer c, book b, orders o where c.custid = o.custid and b.bookid = o.bookid group by name; ->from 절에다가 넣는다
select name from (select name, count(distinct publisher) cnt from customer c, book b, orders o where c.custid = o.custid and b.bookid = o.bookid group by name) where cnt>=2;
-박지성이 구매한 도서의 출판사 수 출력
select count(distinct publisher) from customer c, orders o, book b where c.custid = o.custid and b.bookid = o.bookid and name = ‘박지성’;
박지성의 custid 출력 select custid from customer where name=’박지성’
박지성이 구매한 도서의 bookid 출력
select bookid from orders where custid = (select custid from customer where name=’박지성’);
select count(distinct publisher) from book where bookid in (select bookid from orders where custid = (select custid from customer where name=’박지성’));
- 윤석열에게 추천할 도서 이름 출력 -> 윤석열이 구매한 도서와 동일한 도서를 가장 많이 구매한 사람이 구매한 도서중에 윤석열이 구매하지 않은 도서이름 출력
select ~from 테이블 1 left outer join 테이블2 and 다른조건식 => 다른조건식이 and로 적용되지 않는다. 서브쿼리 필요 select ~from 테이블 1, 테이블2 where 조인식(+) and 다른조건식 => 다른조건식이 and로 적용된다. +기호는 오라클에만 있는 기호
다중행 연산자 ==> 서브쿼리가 where절에 사용이 될 때에 사용하는 연산자
- 박지성의 총구매액 select custid from customer where name =’박지성’;
select sum(saleprice) from orders where custid = (select custid from customer where name =’박지성’);
-박지성이 구매한 도서의 이름, 가격, 정가와 판매가격의 차이 select bookname, price, abs(price-saleprice) from customer c, book b, orders o where c.custid = o.custid and b.bookid = o.bookid and name = ‘박지성’;
=>음수이던 양수이던 절대값을 구해주는 함수 abs
-
마당서점의 도서의 총 개수 select count(bookid) from book; select count(*) from book; => bookid가 null일리가 없기 떄문에 둘다 결과 동일
-
마당서점에 도서를 출고하는 출판사의 총 개수 select count(distinct publisher) from book;
- 도서의 가격과 판매가격의 차이가 가장 많은 주문 ->도서의 가격과 판매가격의 차이의 최대값 select abs(price-saleprice) from book b, orders o where b.bookid = o.bookid;
select max(abs(price-saleprice)) from book b, orders o where b.bookid = o.bookid;
=>37000
도서의 가격과 판매가격의 차이가 37000원인 주문번호를 검색 select orderid from orders o, book b where o.bookid = b.bookid and abs(price-saleprice) = (select max(abs(price-saleprice)) from book b, orders o where b.bookid = o.bookid);
==> 14
주문번호가 14번인 주문내역을 출력 select * from orders where orderid=(select orderid from orders o, book b where o.bookid = b.bookid and abs(price-saleprice) = (select max(abs(price-saleprice)) from book b, orders o where b.bookid = o.bookid));
- 도서의 판매액 평균보다 자신의 구매액 평균이 더 높은 고객 이름
도서의 판매액 평균 select avg(saleprice) from orders; 15928.5714
고객id별 구매액 평균 출력 select custid, avg(saleprice) from orders group by custid;
CUSTID AVG(SALEPRICE) --------- --------------
1 15000
2 7500
3 10333.3333
4 16500
6 32000
7 20000 =>A
a중에서 a의 평균이 15928.5714 보다 높은 고객번호를 검색 select custid from (select custid, avg(saleprice) avg from orders group by custid) A where A.avg > (select avg(saleprice) from orders);
CUSTID
----------
4
6
7
고객번호가 4,6,7에 해당하는 고객의 이름을 출력 select name from customer where custid in (select custid from (select custid, avg(saleprice) avg from orders group by custid) A where A.avg > (select avg(saleprice) from orders));
NAME
----------------------------------------
추신수
윤석열
이재명
-
모든 직원의 이름과 직위 출력 select ename, job from emp;
-
30번 부서에 근무하는 모든 사원의 이름과 급여 출력 select ename, salary from emp where dno=30;
-
개발팀에 근무하는 모든 사원의 이름과 급여출력 select dno from dept where dname like ‘개발%’;
select ename, salary from emp where dno in (select dno from dept where dname like ‘개발%’);
- 직급별로 동일한 직급의 직원수를 출력 select job, count(*) from emp group by job;
연습) 20번 부서의 구성원수와 직원들의 급여의 합계와 평균을 출력합니다. select count(*),sum(salary), avg(salary) from emp where dno=20;
연습) 개발1팀에 근무하는 직원들의 구성원수와 직원들의 급여의 합계와 평균을 출력합니다. select dno from dept where dname like ‘개발1팀’
select count(*), sum(salary), avg(salary) from emp where dno in (select dno from dept where dname =’개발1팀’);
연습) 서교동에 근무하는 직원들의 구성원수와 직원들의 급여의 합계와 평균을 출력합니다. select dno from dept where dloc =’서교동’
select count(*), sum(salary), avg(salary) from emp where dno in (select dno from dept where dloc =’서교동’);
연습) 평균급여가 가장 높은 부서번호를 출력하시오. 부서번호별로 평균급여를 출력 select dno, avg(salary) avg from emp group by dno; DNO AVG(SALARY) ———- ———– 30 233.333333 10 617.5 20 75 =>A
부서별 평균급여의 최대값 출력 select max(avg(salary)) from emp group by dno;
MAX(AVG(SALARY))
----------------
617.5
A중에서 평균급여가 max 인 부서번호 출력 select dno from (select dno, avg(salary) avg from emp group by dno) A where A.avg = (select max(avg(salary)) from emp group by dno);
연습) 평균급여가 가장 높은 부서명을 출력합니다. select dname from dept where dno in (select dno from (select dno, avg(salary) avg from emp group by dno) A where A.avg = (select max(avg(salary)) from emp group by dno));
연습) 평균급여가 가장 높는 부서에 근무하는 직원들의 이름을 출력합니다. select ename from emp where dno in (select dno from (select dno, avg(salary) avg from emp group by dno) A where A.avg = (select max(avg(salary)) from emp group by dno));
연습) 전체직원들 중에 직속상관이 있는 사원의 수를 출력 select count() from emp where mgr != null; x select count() from emp where mgr is not null; o select count(mgr) from emp;
연습) 사원테이블에서 이름,급여,수당, 총액을 구하여 총액이 높은순으로 출력합니다. 단, 수당이 NULL인 사람은 제외합니다.
select ename, salary, comm, salary + comm from emp where comm is not null order by salary + comm desc;
연습) 각 부서별로 같은 직급의 사람의 수를 구하여 부서번호, 직급, 사람수를 출력합니다. select dno, job, count(*) from emp group by dno, job order by dno, job;
연습) 직원이 한명도 없는 부서의 이름을 출력하시오 select dname from dept where dno not in(select distinct dno from emp);
연습) 같은 직급의 사람의 수가 2명이상인 직급과 인원수를 출력 직급별로 인원수 출력 select job, count(*) from emp group by job;
JOB COUNT(*) —————————————- ———- 사장 1 부장 4 과장 2 사원 8
select job, count() from emp group by job having count()>=2;
연습) “박지예”보다 급여를 더 많이 받는 사람의 이름을 출력하시오. select salary from emp where ename=’박지예’;
select ename from emp where salary > (select salary from emp where ename=’박지예’);
연습) “박지예”가 일하는 부서 혹은 “서교동”에 있는 부서번호를 출력하시오. select dno from emp where dname = ‘박지예’
select dno from dept where dno = (select dno from emp where dname = ‘박지예’) or dloc = ‘서교동’;
연습) 대한미디어에서 출판한 도서를 구매한 고객의 이름을 출력 select name from customer where custid in (select custid from orders where bookid in (select bookid from book where publisher = ‘대한미디어’)); distinct select name from customer c, book b, orders o where c.custid = o.custid and b.bookid = o.bookid and publisher = ‘대한미디어’;
=> 둘 다 같은 결과가나옵니다. 그러나 데이터의 레코드의 수가 많을 때에는 서브쿼리로 작성하는게 훨씬 효율적
연습) 출판사별로 출판사의 평균 도서 가격보다 비싼 도서를 구하시오. select publisher, avg(price) from book group by publisher;
select bookname from book b, (select publisher, avg(price) from book group by publisher) A where b.publisher = A.publisher and price > A.avg;
출판사별로 평균 도서 가격을 출력 select publisher, avg(price) from book group by publisher;
select bookname from book b1 where b1.price > (select avg(b2.price) from book b2 where b1.publisher = b2.publisher);
상관서브쿼리 => 서브쿼리에서 메인쿼리의 컬럼의 값과 관계식을 표현하는 것을 말한다.
데이터베이스 명령어의 종류 DDL DML DCL
DDL(Data Definition Language) 데이터 정의어 테이블 생성, 테이블의 구조 변경, 테이블 삭제 테이블 삭제 drop
테이블 생성하는 형식
create table 테이블이름(
칼럼이름 자료형 [제약(들)],
...
);
자료형
숫자 number
문자 varchar2(크기)
날짜 date
제약의 종류
null 생략할 수 있음
not null 생략할 수 없음
unique 값이 중복할 수 없음. null 허용
primary key 중복할 수 없고 null을 허용 x
default 기본값을 설정
check 조건을 만족해야 하는 값을 설정
foreign key 부모테이블의 값을 참조 ---
create table newbook(
bookid number null,
bookname varchar2(20) null,
publisher varchar2(20),
price number
);
레코드를 추가하는 방법
insert into 테이블이름 values(값1, 값2,,,,);
이때에는 값의 수와 순서가 테이블의 컬럼의 수와 순서가 동일해야한다.
insert into 테이블이름(컬럼1, 컬럼2, ...) values(값1, 값2...);
명시한 컬럼의 순서대로 값을 나열
이때에는 명시한 컬럼 이외의 칼럼의 null을 허용하는 컬럼이어야 한다.
insert into newbook values(1, ‘재미있는 자바’,’쌍용미디어’,40000); insert into newbook values(2, ‘신나는 자바’, null, null); insert into newbook(bookid, bookname) values(3,’즐거운 오라클’); insert into newbook values(null, ‘신나는 오라클’, null, null); insert into newbook values(1, ‘즐거운 자바’, ‘한빛미디어’, 30000); —
테이블 삭제
drop table 테이블이름;
create table newbook( bookid number not null, bookname varchar2(20), publisher varchar2(20), price number );
insert into newbook values(null, ‘재미잇는 자바’,’쌍용미디어’,40000); insert into newbook(bookid) values(2);
not null -> null이 아니기만 하면 된다. -> 중복 허용 – create table newbook( bookid number unique, bookname varchar2(30), publisher varchar2(30), price number );
unique는 유일한지만 검사하기때문에 null을 허용한다. — primary key => not null + unique => null을 허용하지 않고 유일해야 한다. => 주식별자 => 다른 레코드와 구별하기 위한 용도로 사용
create table newbook( bookid number primary key, bookname varchar2(30), publisher varchar2(30), price number ); —
create table newbook( bookid number, bookname varchar2(30), publisher varchar2(30) default ‘쌍용미디어’, price number ); —
create table newbook( bookid number primary key, bookname varchar2(30) not null, publisher varchar2(30) default ‘쌍용미디어’, price number default 10000 check(price>=1000) );
insert into newbook values(2,’신나는 자바’,’한빛’,1000);
create table newcustomer( custid number primary key, name varchar2(20) not null, addr varchar2(50) default ‘서울’, phone varchar2(20) unique );
insert into newcustomer values(1,’박지성’,default,’010-1111-1111’,40);
insert into newcustomer values(2,’이순신’,default,’010-2222-1111’,40);
insert into newcustomer values(3,’김유신’,default,null,40);
이미 있는 테이블의 구조와 데이터를 복사하여 새로운 테이블 생성
create table newbook as select * from book;
book테이블의 레코드를 모두 검색하여 newbook에 insert하기
insert into newbook select * from book;
customer 테이블의 레코드를 모두 검색하여 newcustomer에 insert하기
insert into newcustomer select * from customer;
primary key 설정을 컬럼레벨에 설정할 수도 있고 테이블레벨에 설정할 수도 있다.
//컬럼 레벨 create table 테이블이름( 컬럼 1 primary key )
//테이블 레벨 create table 테이블이름( 컬럼 1, 컬럼 2, … primary key 컬럼1 )
create table member( id varchar2(20), pwd varchar2(20), name varchar2(20), primary key(id) ) —
참조키 설정
foreign key 어떤 컬럼의 값이 반드시 이미 있는 다른 테이블의 값을 참조해야하는 경우에 참조키를 설정한다.
참조키를 설정할때는 반드시 부모ㅔ이블의 주식별자만이 참조키로 설정할 수 있다.
create table member( id varchar2(20), pwd varchar2(20), name varchar2(20) );
create table board( no number primary key, title varchar2(50), id varchar2(20) references member(id), content varchar2(3000) ); 4행에 오류: ORA-02270: 이 열목록에 대해 일치하는 고유 또는 기본 키가 없습니다.
==>참조키로 설정하려는 id가 member테이블의 주식별자가 아니어서 오류 발생
create table member( id varchar2(20) primary key, pwd varchar2(20), name varchar2(20) );
member와 board는 주종관계(부모자식)관계까 된다. member가 부모가 되고, board가 자식이 된다. 관계형데이터베이스에서 테이블을 개체(Entity)라고도 표현한다.
insert into member values(‘tiger’,’1234’,’김범’); insert into member vlaues(‘lion’,’1234’‘홍사자);
insert into board values(1,’안녕’, ‘tiger’, ‘만나서 반갑습니다.’) insert into board values(2,’금요일’, ‘lion’, ‘즐거운 금요일입니다.’) insert into board values(3,’주말 스터디’,’kim’,’주말에 만나서 같이 공부해요’);
모든 레코드는 주식별자에 의해서 구별되어야 하는것을 개체무결성이라고 한다. 만약 중복된 주식별자 값을 갖게 된다면 개체무결성에 위배된다고 한다.
참조무결성은 참조키에 설정된 값은 반드시 부모테이블에 나타난 값이어야 한다. 그렇지 않은 경우 참조무결성에 위배된다고 한다.
select dno from emp where ename=’정자영’;
select dno from dept where dloc=’판교’;
select ename from emp where dno=(select dno from emp where ename=’정자영’) or dno in (select dno from dept where dloc=’판교’);
select ename from emp where dno=(select dno from emp where ename = ?) or dno in (select dno from dept where dloc = ?);