데이터베이스 기초 명령어 2(+예제)

<join>

<오라클 JOIN>

INNER JOIN 과 OUTER JOIN 이 있다. (주로 INNER JOIN 사용)

SELECT * FROM ORDERS o, CUSTOMER c

WHERE o.custId = c.custId

;

 

SELECT * FROM ORDERS o , BOOK b

WHERE o.BOOKID = b.BOOKID

;

 

(옳지 못한 예)

SELECT * FROM book b, CUSTOMER c ; 

-> 이렇게 실행시 크로스 조인이 되지만, 의미없는 데이터가 나열된다.

 

SELECT

o.ORDERID , c.NAME, b.BOOKNAME, o.SALEPRICE

FROM ORDERS o ,CUSTOMER c ,BOOK b

WHERE o.CUSTID = c.CUSTID AND o.BOOKID = b.BOOKID

;

->3개의 테이블을 연결하고 필요한것만 출력한다.

 

!고객과 고객의 주문에 관한 데이터를 모두 보이시오.

SELECT * FROM CUSTOMER c, ORDERS o

WHERE o.CUSTID = c.CUSTID

;

 

<ANSI JOIN> ( ANSI 조인을 사용하는 경우가 더 많기 때문에 우리는 ANSI 조인을 사용한다 )

SELECT *

FROM ORDERS o

INNER JOIN CUSTOMER c ON o.CUSTID = c.CUSTID

INNER JOIN book b ON o.BOOKID = b.BOOKID

;

[ 

↓는 oracle join 이다. ( ↑의 ANSI join 과 같은 코드 )

SELECT *

FROM ORDERS o ,CUSTOMER c ,BOOK b

WHERE o.CUSTID = c.CUSTID AND o.BOOKID = b.BOOKID

;

]

 

SELECT * FROM ORDERS o

INNER JOIN CUSTOMER c ON o.CUSTID = c.CUSTID ;

(순서는 상관없다)

SELECT * FROM CUSTOMER c

INNER JOIN ORDERS o ON o.CUSTID = c.CUSTID ;

 

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

SELECT * FROM CUSTOMER c

INNER JOIN ORDERS o ON c.CUSTID = o.CUSTID

ORDER BY c.CUSTID ASC

;

 

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

SELECT c.name, o.SALEPRICE FROM CUSTOMER c

INNER JOIN ORDERS o ON c.CUSTID = o.CUSTID

ORDER BY c.CUSTID ASC

;

 

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

(단, 동명이인 고려하지 않음)

SELECT c.NAME, sum(o.SALEPRICE) FROM CUSTOMER c --sum안에 vector를 넣으면 scalar 값이나옴

INNER JOIN ORDERS o ON c.CUSTID = o.CUSTID

GROUP BY c.NAME

ORDER BY c.NAME ASC 

;

 

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

SELECT c.NAME, b.BOOKNAME FROM CUSTOMER c

INNER JOIN ORDERS o ON c.CUSTID = o.CUSTID

INNER JOIN BOOK b ON b.BOOKID = o.BOOKID 

;

(tip - ORDER BY 할때 여기 나오지 않은 번호로도 정렬 가능하다.)

 

!가격이 20,000원인 도서를 주문한 고객의 이름과 도서의 이름을 구하시오

SELECT c.NAME, b.BOOKNAME FROM CUSTOMER c

INNER JOIN ORDERS o ON c.CUSTID = o.CUSTID

INNER JOIN BOOK b ON b.BOOKID = o.BOOKID

WHERE b.PRICE = 20000

;

 

<OUTER JOIN>

SELECT * FROM CUSTOMER c

LEFT OUTER JOIN ORDERS o ON c.CUSTID = o.CUSTID;

->여기서의 LEFT는 CUSTOMER  왼쪽기준으로 맞추겠다.

->만약에 공통된 이력이 없다면 NULL 값을 넣고 불러온다.

 

SELECT * FROM ORDERS o

RIGHT OUTER JOIN CUSTOMER c ON c.CUSTID = o.CUSTID;

->여기서의 RIGHT는 CUSTOMER  오른쪽 기준으로 맞추겠다.

-> outer만 쓰게되면 양쪽 다에 NULL값을 넣어준다.

 

!도서를 구매하지 않은 고객을 포함하여 // 고객의 이름과 고객이 주문한 도서의 판매가격을 구하시오.

SELECT c.NAME, o.SALEPRICE FROM CUSTOMER c

INNER JOIN ORDERS o ON o.CUSTID = c.CUSTID;

 

부속 질의(서브 쿼리) 기초

-->일반적으로 join으로 처리가능하면 퍼포먼스 때문에 join로 처리하는게 낫다.

 

!가장 비싼 도서의 이름을 보이시오

SELECT b.price FROM BOOK b ;

 

SELECT

*

FROM

BOOK b1

WHERE b1.PRICE = ( --문법적으로 = 은 값이 같아야함

SELECT max(PRICE) FROM BOOK

);

 

SELECT max(PRICE) FROM BOOK; 

--> 쉬운 서브쿼리는 = 뒤에 뜯어내도 실행가능!!

 

!도서를 구매한 적이 있는 고객의 이름을 검색하시오.

SELECT * FROM CUSTOMER c

WHERE c.custid IN (SELECT o.CUSTID FROM ORDERS o) --(1,2,3,4,5) -- in은 벡터를 받는당~!~!~!

;

 

SELECT o.CUSTID FROM ORDERS o

 

!대한미디어에서 출판한 도서를 구매한 고객의 이름을 보이시오.

SELECT * FROM CUSTOMER c

WHERE c.CUSTID IN (

SELECT o.CUSTID

FROM ORDERS o

WHERE o.BOOKID IN (

SELECT b.BOOKID

FROM BOOK b

WHERE b.PUBLISHER = '대한미디어'

)

)

;

 

SELECT o.CUSTID FROM ORDERS o

WHERE o.BOOKID IN (

SELECT b.BOOKID FROM BOOK b WHERE b.PUBLISHER = '대한미디어'

)

;

 

SELECT b.BOOKID FROM BOOK b

WHERE b.PUBLISHER = '대한미디어'

;

 

 

<상관 부속지>

!출판사별로 출판사의 평균 도서 가격보다 비싼 도서를 구하시오.

SELECT * FROM BOOK b1

WHERE b1.price > (

SELECT AVG(PRICE) FROM BOOK b2 WHERE b2.PUBLISHER = b1.PUBLISHER -->반복문과 비슷한 구조이다.

)

;

 

--SELECT AVG(PRICE) FROM BOOK b2 WHERE b2.PUBLISHER = b1.PUBLISHER

SELECT AVG(PRICE) FROM BOOK b2 WHERE b2.PUBLISHER = '굿스포츠';

 

!도서를 주문하지 않은 고객의 이름을 보이시오.

 

--way1 서브쿼리

SELECT * FROM CUSTOMER c

WHERE c.custid NOT IN (

SELECT o.CUSTID FROM ORDERS o

)

;

 

SELECT o.CUSTID FROM ORDERS o;

 

--way2 집합 연산 -- 일단 알아만 23

SELECT * FROM CUSTOMER

MINUS

SELECT * FROM CUSTOMER WHERE CUSTID IN(1,2,3,4);

 

INTERSECT는 같은 스칼라를 찾아내는 명령어 이다

SELECT * FROM CUSTOMER

INTERSECT

SELECT * FROM CUSTOMER WHERE CUSTID IN(1,3,5);

 

 union 용법을 확인하기 위한 예제이다.

SELECT BOOKID a, BOOKNAME b FROM BOOK

UNION

SELECT CUSTID a, name b FROM CUSTOMER;

 

EXISTS

-- 무조건 상관 부속 질의이다.

-- 퍼포먼스가 좋은 쿼리이다.(발견하면 반복문 탈출함)

-- 유일하게 매트릭스를 받는 녀석(스칼라나, 벡터 포함)

 

!주문이 있는 고객의 이름과 주소를 보이시오.

SELECT * FROM CUSTOMER c

WHERE EXISTS(

SELECT * FROM ORDERS o WHERE o.CUSTID = c.custid --5가 되면 주문한내역이 없어서 false

)

;

 

SELECT * FROM ORDERS o WHERE o.CUSTID =5 ; --결과가 하나라도 나오면 TRUE 이다

SELECT * FROM ORDERS o WHERE o.CUSTID = 1000; --없으니까 false