오라클 쿼리 실습예제. 서브쿼리, join, exists, in

DROP USER madang CASCADE;

CREATE USER madang IDENTIFIED BY madang DEFAULT TABLESPACE users TEMPORARY

TABLESPACE temp PROFILE DEFAULT;

GRANT CONNECT, RESOURCE TO madang;

GRANT CREATE VIEW, CREATE SYNONYM TO madang;

ALTER USER madang ACCOUNT UNLOCK;

/* 여기서부터는 마당 계정으로 접속 */

conn madang/madang;

CREATE TABLE Book (

 bookid NUMBER(2) PRIMARY KEY,

 bookname VARCHAR2(40),

 publisher VARCHAR2(40),

 price NUMBER(8)

);

CREATE TABLE Customer (

 custid NUMBER(2) PRIMARY KEY,

 name VARCHAR2(40),

 address VARCHAR2(50),

 phone VARCHAR2(20)

);

CREATE TABLE Orders (

 orderid NUMBER(2) PRIMARY KEY,

 custid NUMBER(2) REFERENCES Customer(custid),

 bookid NUMBER(2) REFERENCES Book(bookid),

 saleprice NUMBER(8) ,

 orderdate DATE

);

/* Book, Customer, Orders 데이터 생성 */

INSERT INTO Book VALUES(1, '축구의 역사', '굿스포츠', 7000);

INSERT INTO Book VALUES(2, '축구아는 여자', '나무수', 13000);

INSERT INTO Book VALUES(3, '축구의 이해', '대한미디어', 22000);

INSERT INTO Book VALUES(4, '골프 바이블', '대한미디어', 35000);

INSERT INTO Book VALUES(5, '피겨 교본', '굿스포츠', 8000);

INSERT INTO Book VALUES(6, '역도 단계별기술', '굿스포츠', 6000);

INSERT INTO Book VALUES(7, '야구의 추억', '이상미디어', 20000);

INSERT INTO Book VALUES(8, '야구를 부탁해', '이상미디어', 13000);

INSERT INTO Book VALUES(9, '올림픽 이야기', '삼성당', 7500);

INSERT INTO Book VALUES(10, 'Olympic Champions', 'Pearson', 13000);

INSERT INTO Customer VALUES (1, '박지성', '영국 맨체스타', '000-5000-0001');

INSERT INTO Customer VALUES (2, '김연아', '대한민국 서울', '000-6000-0001');

INSERT INTO Customer VALUES (3, '장미란', '대한민국 강원도', '000-7000-0001');

INSERT INTO Customer VALUES (4, '추신수', '미국 클리블랜드', '000-8000-0001');

INSERT INTO Customer VALUES (5, '박세리', '대한민국 대전', NULL);

INSERT INTO Orders VALUES (1, 1, 1, 6000, TO_DATE('2014-07-01','yyyy-mm-dd'));

INSERT INTO Orders VALUES (2, 1, 3, 21000, TO_DATE('2014-07-03','yyyy-mm-dd'));

INSERT INTO Orders VALUES (3, 2, 5, 8000, TO_DATE('2014-07-03','yyyy-mm-dd'));

INSERT INTO Orders VALUES (4, 3, 6, 6000, TO_DATE('2014-07-04','yyyy-mm-dd'));

INSERT INTO Orders VALUES (5, 4, 7, 20000, TO_DATE('2014-07-05','yyyy-mm-dd'));

INSERT INTO Orders VALUES (6, 1, 2, 12000, TO_DATE('2014-07-07','yyyy-mm-dd'));

INSERT INTO Orders VALUES (7, 4, 8, 13000, TO_DATE( '2014-07-07','yyyy-mm-dd'));

INSERT INTO Orders VALUES (8, 3, 10, 12000, TO_DATE('2014-07-08','yyyy-mm-dd'));

INSERT INTO Orders VALUES (9, 2, 10, 7000, TO_DATE('2014-07-09','yyyy-mm-dd'));

INSERT INTO Orders VALUES (10, 3, 8, 13000, TO_DATE('2014-07-10','yyyy-mm-dd'));

CREATE TABLE Imported_Book (

 bookid NUMBER,

 bookname VARCHAR(40),

 publisher VARCHAR(40),

 price NUMBER(8)

 );

INSERT INTO Imported_Book VALUES(21, 'Zen Golf', 'Pearson', 12000);

INSERT INTO Imported_Book VALUES(22, 'Soccer Skills', 'Human Kinetics', 15000);

COMMIT;

-- 1.1 ~ 1.4

SELECT * FROM BOOK WHERE BOOKID = 1;

SELECT * FROM BOOK WHERE PRICE >= 20000 ;

SELECT SUM(ORDERS.SALEPRICE) 

FROM CUSTOMER, ORDERS

WHERE CUSTOMER.CUSTID = ORDERS.CUSTID

AND CUSTOMER.NAME = '박지성';

SELECT COUNT(*)

FROM CUSTOMER, ORDERS

WHERE CUSTOMER.CUSTID = ORDERS.CUSTID

AND CUSTOMER.NAME = '박지성';

-- 2.1 ~ 2.7

SELECT COUNT(*) FROM BOOK ;

SELECT COUNT(DISTINCT PUBLISHER)

FROM BOOK ;

SELECT NAME, ADDRESS FROM CUSTOMER ;

-- 4,5 

SELECT * 

FROM ORDERS

WHERE ORDERDATE BETWEEN '20140704' AND '20140707';

SELECT * 

FROM ORDERS

WHERE ORDERDATE NOT BETWEEN '20140704' AND '20140707';

SELECT * 

FROM ORDERS

WHERE ORDERDATE < '20140704' OR ORDERDATE > '20140707';

SELECT NAME, ADDRESS

FROM CUSTOMER 

WHERE NAME LIKE '김%';

SELECT NAME, ADDRESS 

FROM CUSTOMER 

WHERE NAME LIKE '김%아';

-- 1.5

SELECT COUNT(DISTINCT PUBLISHER)

FROM CUSTOMER, ORDERS, BOOK

WHERE CUSTOMER.CUSTID = ORDERS.CUSTID

AND ORDERS.BOOKID = BOOK.BOOKID

AND CUSTOMER.NAME = '박지성';

-- 1.6

SELECT BOOK.BOOKNAME, BOOK.PRICE, ORDERS.SALEPRICE, BOOK.PRICE - ORDERS.SALEPRICE

FROM CUSTOMER, ORDERS, BOOK

WHERE CUSTOMER.CUSTID = ORDERS.CUSTID

AND ORDERS.BOOKID = BOOK.BOOKID

AND CUSTOMER.NAME = '박지성';

-- 1.7

SELECT BOOKNAME

FROM BOOK

WHERE BOOKID NOT IN (   SELECT BOOK.BOOKID

                        FROM CUSTOMER, ORDERS, BOOK

                        WHERE CUSTOMER.CUSTID = ORDERS.CUSTID

                        AND ORDERS.BOOKID = BOOK.BOOKID

                        AND CUSTOMER.NAME = '박지성'

);

SELECT BOOKNAME FROM BOOK B1

    WHERE NOT EXISTS (SELECT BOOKNAME 

                        FROM CUSTOMER, ORDERS

                        WHERE CUSTOMER.CUSTID = ORDERS.CUSTID 

                        AND ORDERS.BOOKID=B1.BOOKID

                        AND CUSTOMER.NAME LIKE '박지성');

-- 2.8

SELECT NAME 

FROM CUSTOMER 

LEFT OUTER JOIN ORDERS

ON CUSTOMER.CUSTID = ORDERS.CUSTID

WHERE ORDERS.ORDERID IS NULL;

-- 2.9

SELECT SUM(SALEPRICE), AVG(SALEPRICE) FROM ORDERS;

-- 2.10

SELECT CUSTOMER.NAME, SUM(SALEPRICE) 

FROM CUSTOMER, ORDERS

WHERE CUSTOMER.CUSTID = ORDERS.CUSTID

GROUP BY CUSTOMER.NAME;

-- 2.11

SELECT CUSTOMER.NAME, BOOK.BOOKNAME 

FROM CUSTOMER, ORDERS, BOOK

WHERE CUSTOMER.CUSTID = ORDERS.CUSTID

AND ORDERS.BOOKID = BOOK.BOOKID;

-- 2.12

SELECT *

FROM ORDERS, BOOK

WHERE BOOK.BOOKID = ORDERS.BOOKID

AND ( PRICE - SALEPRICE ) = (SELECT MAX(PRICE - SALEPRICE) DIFF

                            FROM BOOK, ORDERS

                            WHERE BOOK.BOOKID = ORDERS.BOOKID

                                );

-- 2.13

SELECT CUSTOMER.NAME 

FROM CUSTOMER, ORDERS

WHERE CUSTOMER.CUSTID = ORDERS.CUSTID

GROUP BY CUSTOMER.NAME

HAVING AVG(SALEPRICE)> (SELECT AVG(SALEPRICE) 

                        FROM ORDERS);

                        

-- 3.1

SELECT CUSTOMER.NAME

FROM CUSTOMER, ORDERS, BOOK

WHERE CUSTOMER.CUSTID = ORDERS.CUSTID

AND ORDERS.BOOKID = BOOK.BOOKID

AND PUBLISHER IN (SELECT PUBLISHER 

                    FROM CUSTOMER, ORDERS, BOOK

                    WHERE CUSTOMER.CUSTID = ORDERS.CUSTID

                    AND ORDERS.BOOKID = BOOK.BOOKID

                    AND CUSTOMER.NAME = '박지성')

AND CUSTOMER.NAME <> '박지성';

    

-- 3.2

SELECT CUSTOMER.NAME, COUNT(DISTINCT PUBLISHER)

FROM CUSTOMER, ORDERS, BOOK

WHERE CUSTOMER.CUSTID = ORDERS.CUSTID

AND ORDERS.BOOKID = BOOK.BOOKID

GROUP BY CUSTOMER.NAME

HAVING COUNT(DISTINCT PUBLISHER) >= 2;

SELECT NAME FROM CUSTOMER C1

WHERE 2 <= (SELECT COUNT(DISTINCT PUBLISHER) 

            FROM CUSTOMER, ORDERS, BOOK

            WHERE CUSTOMER.CUSTID = ORDERS.CUSTID

            AND ORDERS.BOOKID=BOOK.BOOKID AND (NAME LIKE C1.NAME));

SELECT COUNT(DISTINCT PUBLISHER) 

            FROM CUSTOMER, ORDERS, BOOK

            WHERE CUSTOMER.CUSTID = ORDERS.CUSTID

            AND ORDERS.BOOKID=BOOK.BOOKID;

-- 3.3

SELECT BOOK.BOOKNAME

FROM ORDERS, BOOK

WHERE ORDERS.BOOKID = BOOK.BOOKID

GROUP BY BOOK.BOOKNAME

HAVING COUNT(DISTINCT ORDERS.CUSTID) >= (SELECT COUNT(*)*0.3 

                                            FROM CUSTOMER);

SELECT BOOKNAME FROM BOOK B1

WHERE ((SELECT COUNT(BOOK.BOOKID) 

        FROM BOOK, ORDERS

        WHERE BOOK.BOOKID=ORDERS.BOOKID 

        AND BOOK.BOOKID=B1.BOOKID)

        >= 0.3 * (SELECT COUNT(*) FROM CUSTOMER));

-- 4.1 ~ 4.4

INSERT INTO BOOK (BOOKID, BOOKNAME,PUBLISHER,PRICE) VALUES (11, ‘스포츠 세계’, ‘대한미디어’, 10000);

DELETE FROM BOOK WHERE PUBLISHER = '삼성당';

DELETE FROM BOOK WHERE PUBLISHER = '이상미디어';

UPDATE BOOK SET PUBLISHER = '대한출판사' WHERE PUBLISHER = '대한미디어';