Oracle SQL Developer 활용 실습 예제 - (4)

1. 주어진 조건에 맞는 데이터를 조회 해보기(SELECT)

 

아래와 같이 BOOKS, CUSTOMERS, ORDERS 테이블이 있습니다.

동일한 이름의 컬럼명은 동일한 의미로 사용되었기 때문에 조인(JOIN)시 활용할 수 있다고 가정합니다.

 

<예제와 관련된 TABLE 스키마 정보>

 

책의 최저 단가와 최고 단가는 각각 얼마인가?

SELECT MAX(PRICE), MIN(PRICE)
FROM BOOKS;

 

출판사별로 책들의 단가의 총합을 구해 보세요.

SELECT PUBLISHER, SUM(PRICE)
FROM BOOKS
GROUP BY PUBLISHER;

 

출판사별로 책들의 단가의 총합을 구하되, '대한미디어'는 제외하도록 하고, 단가의 총합이 큰 것부터 정렬하세요.

SELECT PUBLISHER, SUM(PRICE) TOTAL
FROM BOOKS
WHERE PUBLISHER NOT IN('대한미디어')
GROUP BY PUBLISHER
ORDER BY TOTAL DESC;

 

출판사별로 책들의 단가의 총합을 구해 보세요.
단, 단가의 총합이 20,000이상인 항목만 조회하세요.

SELECT PUBLISHER, SUM(PRICE) TOTAL
FROM BOOKS
GROUP BY PUBLISHER
HAVING SUM(PRICE) >= 20000;

 

 

출판사별로 책들의 단가의 총합을 구해 보세요.
단, 단가의 총합이 20,000이상이고, 50,000이하인 항목만 조회하세요.

SELECT PUBLISHER, SUM(PRICE)
FROM BOOKS
GROUP BY PUBLISHER
HAVING SUM(PRICE) BETWEEN 20000 AND 50000;

 

고객과 고객의 주문에 대한 데이터를 모두 조회하시오.
Hint. Join 사용

SELECT *
FROM CUSTOMERS C JOIN ORDERS O
ON C.CUSTID = O.CUSTID;

 

고객과 고객의 주문에 대한 데이터를 고객 번호 순으로 정렬하여 조회하시오.

SELECT *
FROM CUSTOMERS C JOIN ORDERS O
ON C.CUSTID = O.CUSTID
ORDER BY C.CUSTID ASC;

 

고객의 이름과 고객이 주문한 도서의 판매 가격을 조회하시오.

SELECT C.NAME, O.SALEPRICE
FROM CUSTOMERS C JOIN ORDERS O
ON C.CUSTID = O.CUSTID;

 

고객의 이름과 고객이 주문한 도서의 판매 가격을 조회하시오.
단, 김연아와 장미란은 배제하시오.

SELECT C.NAME, O.SALEPRICE
FROM CUSTOMERS C JOIN ORDERS O
ON C.CUSTID = O.CUSTID
WHERE C.NAME NOT IN('김연아', '장미란');

 

고객의 이름과 고객이 주문한 도서의 이름을 조회하시오.

SELECT C.NAME, B.BOOKNAME
FROM CUSTOMERS C JOIN ORDERS O 
ON C.CUSTID = O.CUSTID
JOIN BOOKS B
ON O.BOOKID = B.BOOKID;

 

가격이 20000원인 도서를 주문한 고객의 이름과 도서의 이름을 구하시오.

SELECT C.NAME, B.BOOKNAME
FROM CUSTOMERS C JOIN ORDERS O 
ON C.CUSTID = O.CUSTID
JOIN BOOKS B
ON O.BOOKID = B.BOOKID
WHERE B.PRICE = 20000;

 

도서를 구매하지 않는 고객을 포함하여 고객의 이름과 고객이 주문한 도서의 판매 가격을 구하시오.
Hint. Left Outer Join 사용

SELECT C.NAME, O.SALEPRICE
FROM CUSTOMERS C LEFT OUTER JOIN ORDERS O
ON C.CUSTID = O.CUSTID;

 

고객별로 주문한 모든 도서의 총 판매액을 구하고, 고객별로 정렬하시오.
Hint. Group by 사용

SELECT C.NAME, SUM(O.SALEPRICE)
FROM CUSTOMERS C JOIN ORDERS O
ON C.CUSTID = O.CUSTID
GROUP BY C.NAME;

 

다음 요구 조건에 맞는 테이블 grades를 생성하고 non equi 조인 문제를 풀어 보세요.
책 가격이 0<=가격<=20000 이면, '저렴', 20001<=가격<=30000 이면, '보통', 
30001<=가격<=50000 이면, '비쌈'이라고 출력해야 합니다.
<출력 예시>
축구의 역사     7000    저렴
골프 바이블     35000  비쌈

-- GRADES 테이블 생성하기
CREATE TABLE GRADES(
   glevel varchar(6),
   lowprice number,
   highprice number
);

-- GRADES 데이터 입력하기
INSERT INTO GRADES(glevel, lowprice, highprice) VALUES('저렴', 0, 20000);
INSERT INTO GRADES(glevel, lowprice, highprice) VALUES('보통', 20001, 30000);
INSERT INTO GRADES(glevel, lowprice, highprice) VALUES('비쌈', 30001, 50000);

-- Non-Equi 조인문 작성하기 
SELECT B.BOOKNAME, B.PRICE, G.GLEVEL 
FROM BOOKS B, GRADES G
WHERE B.PRICE BETWEEN G.LOWPRICE AND G.HIGHPRICE;