# ORACLE CHAPTER.6
###### tags `Ming` `ORACLE`

{%hackmd BJrTq20hE %}

Question. 6-1

SELECT ORDER_ID, ORDER_DATE, CUSTOMER_NAME, Cus.CUSTOMER_ID, PHONE, ADDRESS
FROM ORDER_MASTER Ord
RIGHT JOIN CUSTOMER Cus ON Ord.CUSTOMER_ID = Cus.CUSTOMER_ID;

SELECT O.ORDER_ID, CUSTOMER_NAME, ORDER_DATE, BOOk_NAME, PRICE, QUANTITY
FROM ORDER_MASTER O
JOIN CUSTOMER C ON O.CUSTOMER_ID = C.CUSTOMER_ID
JOIN ORDER_DETAIL D ON O.ORDER_ID = D.ORDER_ID
JOIN BOOK B ON D.ISBN = B.ISBN;

Question. 6-2

SELECT O.ISBN, B.BOOK_NAME, SUM(O.QUANTITY)
FROM ORDER_DETAIL O
JOIN BOOK B ON O.ISBN = B.ISBN
GROUP BY O.ISBN, B.BOOK_NAME;

SELECT C.CUSTOMER_ID, C.CUSTOMER_NAME, M.ORDER_ID, SUM(D.QUANTITY)
FROM CUSTOMER C
JOIN ORDER_MASTER M ON C.CUSTOMER_ID = M.CUSTOMER_ID
JOIN ORDER_DETAIL D ON M.ORDER_ID = D.ORDER_ID
GROUP BY C.CUSTOMER_ID, C.CUSTOMER_NAME, M.ORDER_ID
ORDER BY C.CUSTOMER_ID;

SELECT P.PUBLISHER_ID, P.PUBLISHER_NAME, SUM(D.QUANTITY)
FROM PUBLISHER P
JOIN BOOK B ON P.PUBLISHER_ID = B.PUBLISHER_ID
JOIN ORDER_DETAIL D ON B.ISBN = D.ISBN
GROUP BY P.PUBLISHER_ID, P.PUBLISHER_NAME
HAVING SUM(D.QUANTITY) >= 3
ORDER BY P.PUBLISHER_NAME;


SELECT TO_CHAR(PUBLICATION_DATE, 'YYYY-MM') AS PUB_MON , COUNT(ISBN) AS TOTOL
FROM BOOK B
WHERE PUBLICATION_DATE BETWEEN TO_DATE('2016-01-01', 'YYYY-MM-DD') AND TO_DATE('2017-12-31', 'YYYY-MM-DD')
GROUP BY TO_CHAR(PUBLICATION_DATE, 'YYYY-MM')
ORDER BY PUB_MON;

Question. 6-3

SELECT C.CUSTOMER_ID, C.CUSTOMER_NAME, (SUM(D.QUANTITY)*100 / (SELECT SUM(QUANTITY) FROM ORDER_DETAIL)) AS PECENT_SALES
FROM CUSTOMER C
JOIN ORDER_MASTER M ON C.CUSTOMER_ID = M.CUSTOMER_ID
JOIN ORDER_DETAIL D ON M.ORDER_ID = D.ORDER_ID
GROUP BY C.CUSTOMER_ID, C.CUSTOMER_NAME;

SELECT ORDER_ID, SUM(QUANTITY)
FROM ORDER_DETAIL
GROUP BY ORDER_ID
HAVING SUM(QUANTITY) >
(SELECT AVG(SUM(QUANTITY))
FROM ORDER_DETAIL
GROUP BY ORDER_ID);