오라클 예제 및 문제

환경

윈도우10 64비트
오라클 18C

 

 

 

 

 

 

시스템에서 아래 파일 쿼리를 실행한다

F5를 누르면 워크시트에 있는 모든 쿼리가 실행된다

 

12c_script.txt

0.00MB

 

새로운 유저를 만든다 아이디 비번모두 c##madang

테스트 - 저장 - 접속

 

 

 

문제

-- 도서번호가 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
                            );

.