Oracle SQL: join

select bookid, bookname, publisher, price, saleprice, orderdate from book, orders where book.bookid = orders.bookid and orderdate between ‘2021/11/03’ and ‘2021/11/07’;

bookname, pubisher, price 칼럼 -> book테이블 saleprice, orderdate -> orders테이블 bookid => book, orders테이블 양쪽의 테이블에 모두 있는 경우에는 둘 중 한 곳의 테이블의 이름을 같이 써줘야한다.

select b.bookid, bookname, publisher, price, saleprice, orderdate from book b, orders o where book.bookid = orders.bookid and orderdate between ‘2021/11/03’ and ‘2021/11/07’;

select c.custid, name, bookname, publisher, price, saleprice, orderdate from orders o,customer c, book b where o.custid = c.custid and b.bookid = o.bookid and name=’박지성’ order by price desc;

select c.custid, name, bookname, price, saleprice, orderdate from customer c, orders o, book b where c.custid = o.custid and b.bookid = o.bookid and publisher = ‘이상미디어’ order by orderdate desc, saleprice desc;

select c.custid, name, bookname, price, saleprice, orderdate from customer c, orders o, book b where c.custid = o.custid and b.bookid = o.bookid and publisher in(‘이상미디어’,’대한미디어’,’굿스포츠’) and orderdate between ‘2021/11/03’ and ‘2021/11/07’ and price between 10000 and 20000 order by publisher, bookname ;

select c.custid, name, bookname, price, saleprice, orderdate from customer c, orders o, book b where c.custid = o.custid and b.bookid = o.bookid and bookname like ‘%축구%’ order by publisher;

select name, address, phone from customer where address like ‘대한민국%’;

select distinct bookname from customer c, orders o, book b where c.custid = o.custid and b.bookid = o.bookid and address like ‘대한민국%’;

select distinct name from customer c, book b, orders o where c.custid =o.custid and b.bookid = o.bookid and publisher=’굿스포츠’;

select c.custid, name, bookname, orderdate from custormer c, book b, orders o where name not in(‘김연아’,’박지성’) and c.custid = o.custid and b.bookid = o.bookid

select sum(saleprice), avg(saleprcie), min(saleprice), max(saleprice)

select count(*) from orders;

count(컬럼이름) <- 컬럼이름의 값이 null이 아닌 레코드 수 count(*) <- 모든 레코드수 (null값 포함)

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

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(20), mgr number, job varchar2(20) ) insert into dept values(10, ‘개발1팀’,’서교동’); insert into dept values(20, ‘개발2팀’,’판교’); insert into dept values(30, ‘기획팀’,’서교동’);

insert into emp values(1000, ‘이건우’,30,1000,100,’2020/03/05’,’010-0000-1111’,’서울’,1000, null);

insert into emp values(1001, ‘김수연’,10,800,80,’2020/03/05’,’010-0000-2222’,’서울’,1000, null); insert into emp values(1002, ‘최현호’,20,70,100,’2020/03/03’,’010-0000-3333’,’인천’,1000, null); insert into emp values(1003, ‘윤태인’,30,80,100,’2018/05/10’,’010-0000-4444’,’김포’,1000, null);

insert into emp values(1004, ‘박지예’,10,800,80,’2020/03/05’,’010-0000-2222’,’서울’,1001, ‘과장’); insert into emp values(1005, ‘김지현’,20,70,100,sysdate,’010-0000-3333’,’인천’,1002, ‘부장’); insert into emp values(1006, ‘김시아’,30,80,100,’2018/05/10’,’010-0000-4444’,’김포’,1003, ‘부장’);

insert into emp values(1007, ‘유하림’,10,800,80,’2020/03/05’,’010-0000-2222’,’서울’,1004, ‘사원’); insert into emp values(1008, ‘임하연’,10,70,100,sysdate,’010-0000-3333’,’인천’,1004, ‘사원’); insert into emp values(1009, ‘윤서우’,20,80,100,’2018/11/10’,’010-0000-4444’,’김포’,1005, ‘사원’); insert into emp values(1010, ‘정자영’,20,80,100,’2022/05/10’,’010-0000-4444’,’대구’,1005, ‘사원’); insert into emp values(1011, ‘노유나’,30,80,100,’2018/08/10’,’010-0000-4444’,’울산’,1006, ‘사원’); insert into emp values(1012, ‘박성미’,30,80,100,’2018/05/22’,’010-0000-4444’,’김포’,1006,’사원’); insert into emp values(1013, ‘임상진’,30,80,100,’2019/04/10’,’010-0000-4444’,’김포’,1006, ‘사원’);

select avg(salary) from dept d, emp e where d.dno=e.dno and dname like ‘%개발%’;

select count(*) from dept d, emp e where d.dno=e.dno and dname like ‘%기획%’

join하는 방법

select 컬럼1, 컬럼2,… from 테이블1,테이블2… where 조인식 and 조건식1 ..

=> 조인식은 두개의 테이블에 공통으로 들어가는 칼럼을 조건식을 표현식으로 표현하는 것을 말하낟.

1) select ename, job from emp where job=’사원’; 2) select ename, salary, comm, mgr from emp e where dno=30; 3) select eno, ename, salary, salary0.1 “인상액”, salary1.1 “인상된 급여” from emp order by eno; 4) select ename, d.dno, dname from emp e, dept d where e.dno=d.dno and ename like ‘김%’; 5) select max(salary), min(salary), sum(salary), avg(salary) from emp e where job = ‘사원’; 6) select max(salary)-min(salary) from emp e;

select count(*) from emp where mgr is not null; (0) mgr != null (x)

select ename, d.dno, dname, dloc from dept d, emp e where d.dno = e.dno and eno between 1005 and 1009;

select avg(salary) from emp e, dept d where e.dno = d.dno and dloc = ‘서교동’ and job = ‘과장’;

select eno, ename, d.dno, dloc, job, salary from emp e, dept d where e.dno = d.dno and (dloc = ‘서교동’ or job = ‘과장’) order by salary desc;

---

select eno, ename, comm, salary+comm, d.dno, dname from dept d, emp e where d.dno = e.dno and dloc = ‘파주’ order by dname, ename;