-- 도서번호가 1인 도서의 이름
SELECT bookname
FROM BOOK
WHERE BOOKID = 1;
-- 가격이 20000원 이상인 도서의 이름
SELECT bookname
FROM BOOK
WHERE PRICE >= 20000;
-- 박지성의 총 구매액
SELECT SUM(SALEPRICE)
FROM ORDERS
WHERE CUSTID =
(
SELECT CUSTID
FROM customer
WHERE name = '박지성'
);
-- 박지성의 총 구매액 다른방법
SELECT SUM(SALEPRICE)
FROM CUSTOMER, ORDERS
WHERE CUSTOMER.CUSTID = ORDERS.CUSTID AND NAME LIKE '박지성';
-- 박지성이 구매한 도서의 수
SELECT COUNT(ROWNUM)
FROM ORDERS
WHERE CUSTID =
(
SELECT CUSTID
FROM customer
WHERE name = '박지성'
);
-- 박지성이 구매한 도서의 수 다른방법
SELECT COUNT(*)
FROM CUSTOMER, ORDERS
WHERE CUSTOMER.CUSTID = ORDERS.CUSTID AND CUSTOMER.NAME LIKE '박지성';
-- 도서의 총개수
SELECT COUNT(*)
FROM BOOK;
-- 출판사의 총개수
SELECT COUNT(DISTINCT publisher)
FROM BOOK;
-- 모든 고객의 이름 주소
SELECT NAME, ADDRESS
FROM customer;
-- 2014년 7월4일 ~ 7월7일 사이에 주문받은 도서의 주문번호
SELECT *
FROM ORDERS
WHERE ORDERDATE BETWEEN '14/07/04' AND '14/07/07';
-- 2014년 7월4일 ~ 7월7일 사이에 주문받은 도서를 제외한 도서의 주문번호
SELECT *
FROM ORDERS
WHERE ORDERDATE < '14/07/04' OR ORDERDATE > '14/07/07';
-- 성이 '김'씨인 고객의 이름과 주소
SELECT *
FROM customer
WHERE NAME LIKE '김%';
-- 성이 '김'씨이고 이름이 '아'로 끝나는 고객의 이름과 주소
SELECT *
FROM customer
WHERE NAME LIKE '김%아';
----------------------------
-- 박지성이 구매한 도서의 출판사 수
SELECT COUNT(DISTINCT b.publisher)
FROM (
SELECT BOOKID
FROM CUSTOMER C, ORDERS O
WHERE c.custid = o.custid AND NAME = '박지성'
) A, BOOK B
WHERE a.bookid = b.bookid;
-- 박지성이 구매한 도서의 출판사 수 다른방법
SELECT COUNT(DISTINCT b.publisher)
FROM CUSTOMER C, ORDERS O, BOOK B
WHERE c.custid = O.CUSTID AND b.bookid = o.bookid AND NAME = '박지성';
-- 박지성이 구매한 도서의 이름, 가격, 정가와 판매가격의 차이
SELECT b.bookname, b.price, o.saleprice, (b.price - o.saleprice)
FROM BOOK B, customer C, ORDERS O
WHERE O.bookid = b.bookid AND o.custid = c.custid AND NAME = '박지성';
-- 박지성이 구매하지 않은 도서의 이름
SELECT DISTINCT BOOKNAME
FROM BOOK
WHERE BOOKID NOT IN (SELECT BOOKID
FROM customer C, ORDERS O
WHERE c.custid = O.CUSTID AND c.name = '박지성');
-- 박지성이 구매하지 않은 도서의 이름 다른방법1
SELECT DISTINCT BOOKNAME
FROM BOOK
WHERE NOT EXISTS (SELECT BOOKID
FROM customer C, ORDERS O
WHERE c.custid = O.CUSTID AND BOOK.BOOKID = O.BOOKID AND c.name = '박지성');
-- 박지성이 구매하지 않은 도서의 이름 다른방법2
select bookname
from book
minus
select b.bookname
from customer c , orders o , book b
where c.custid = o.custid
and o.bookid = b.bookid
and name like '박지성';
-- 주문하지 않은 고객의 이름 (조인 사용)
SELECT NAME
FROM CUSTOMER
MINUS
SELECT DISTINCT NAME
FROM CUSTOMER C, ORDERS O, BOOK B
WHERE C.CUSTID = O.CUSTID AND B.BOOKID = O.BOOKID;
-- 주문하지 않은 고객의 이름 NOT IN
SELECT NAME
FROM CUSTOMER
WHERE CUSTID NOT IN (
SELECT CUSTID
FROM ORDERS
GROUP BY custid
);
-- 주문하지 않은 고객의 이름 NOT EXISTS
SELECT NAME
FROM CUSTOMER
WHERE NOT EXISTS (
SELECT CUSTID
FROM ORDERS
WHERE customer.custid = orders.custid
);
-- 주문 금액의 총액과 주문의 평균 금액
SELECT SUM(SALEPRICE), AVG(SALEPRICE)
FROM ORDERS;
-- 고객의 이름과 고객별 구매액
SELECT C.NAME, SUM(o.saleprice)
FROM customer C, ORDERS O
WHERE C.CUSTID = O.CUSTID
GROUP BY C.NAME;
-- 고객의 이름과 고객이 구매한 도서 목록
SELECT c.name, b.bookname
FROM CUSTOMER C, ORDERS O, BOOK B
WHERE c.custid = o.custid AND B.BOOKID = O.BOOKID;
-- 도서의 가격(BOOK 테이블)과 판매가격(ORDERS 테이블)의 차이가 가장 많은 주문
SELECT *
FROM BOOK B, ORDERS O
WHERE b.bookid = o.BOOKid AND (B.PRICE - o.saleprice) = (
SELECT MAX(ABS(B.PRICE - o.saleprice))
FROM BOOK B, ORDERS O
WHERE b.bookid = o.BOOKid
);
-- 도서의 판매액 평균보다 자신의 구매액 평균이 더 높은 고객의 이름
SELECT C.NAME, AVG(o.saleprice)
FROM customer C, ORDERS O
WHERE c.custid = o.CUSTid
GROUP BY C.NAME
HAVING AVG(o.saleprice) > (
SELECT AVG(SALEPRICE)
FROM ORDERS
);
STUDENT 테이블과 DEPARTMENT 테이블을 사용하여 '서진수' 학생과 1전공(DEPTNO2)이 동일한 학생들의 이름과 1전공 이름을 출력하세요
먼저 서진수 학생의 전공을 추린다
SELECT DEPTNO1
FROM STUDENT
WHERE NAME = '서진수';
101번을 전공하는 학생이름과 전공을 출력한다
SELECT S.NAME STUD_NAME, d.dname DEPT_NAME
FROM STUDENT S, DEPARTMENT D
WHERE D.DEPTNO = 101 AND S.DEPTNO1 = d.deptno;
101자리에 맨 위 코드를 서브쿼리한다
SELECT S.NAME STUD_NAME, d.dname DEPT_NAME
FROM STUDENT S, DEPARTMENT D
WHERE D.DEPTNO =
(
SELECT DEPTNO1
FROM STUDENT
WHERE NAME = '서진수'
)
AND S.DEPTNO1 = d.deptno;
PROFESSOR 테이블과 DEPARTMENT 테이블을 조회하여 '주승재' 교수보다 나중에 입사한 사람의 이름과 입사일, 학과명을 출력하세요.
먼저 주승재교수의 입사일을 확인
SELECT HIREDATE
FROM PROFESSOR
WHERE NAME = '주승재';
위 코드를 서브쿼리하여 주승재 교수보다 입사일이 늦은 사람들을 추리기
SELECT P.NAME , P.HIREDATE, d.dname
FROM PROFESSOR P, DEPARTMENT D
WHERE HIREDATE >
(
SELECT HIREDATE
FROM PROFESSOR
WHERE NAME = '주승재'
)
AND p.deptno = d.deptno;
STUDENT 테이블에서 1전공(DEPTNO1)이 201번인 학과의 평균 몸무게보다 몸무게가 많은 학생들의 이름과 몸무게를 출력하세요.
먼저 학생들의 평균 몸무게를 확인
SELECT AVG(WEIGHT)
FROM STUDENT
WHERE DEPTNO1 = 201
GROUP BY DEPTNO1;
위 코드를 서브쿼리하여 필요한 요소를 출력
SELECT NAME, WEIGHT
FROM STUDENT
WHERE WEIGHT >
(
SELECT AVG(WEIGHT)
FROM STUDENT
WHERE DEPTNO1 = 201
GROUP BY DEPTNO1
);
EMP2 테이블을 사용하여 전체 직원 중 '과장' 직급의 최소 연봉자보다 연봉이 높은 사람의 이름과 직급, 연봉을 출력하세요. 단, 연봉 출력 형식은 아래와 같이 천 단위 구분 기호와 $표시를 하세요.
SELECT NAME, POSITION, to_char(pay, '$999,999,999') SALAERY
FROM EMP2
WHERE pay >
(
SELECT MIN(PAY)
FROM EMP2
WHERE POSITION = '과장'
);
EMP2 테이블과 DEPT2 테이블을 조회하여 각 부서별 평균 연봉을 구하고 그중에서 평균 연봉이 가장 적은 부서의 평균 연봉보다 적게받는 직원들의 부서명, 직원명, 연봉을 출력하세요.
SELECT DEPT2.DNAME, EMP2.NAME, TO_CHAR(EMP2.PAY, '$999,999,999') SALARY
FROM EMP2, DEPT2
WHERE PAY <
(
SELECT *
FROM
(
SELECT AVG(PAY)
FROM EMP2
GROUP BY DEPTNO
ORDER BY AVG(PAY)
)
WHERE ROWNUM = 1
) AND EMP2.DEPTNO = DEPT2.DCODE;
