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

<함수>

SELECT orderid + 1, saleprice * 0.1 FROM ORDERS ; 

출력할때 SELECT 부분에 임의의 숫자를 연산해서 출력가능

SELECT 1234, SALEPRICE FROM ORDERS ; 

 

SELECT '안녕하세요', saleprice FROM ORDERS;

SELECT '안녕하세요' || '반갑' , saleprice FROM ORDERS; 

SELECT bookname || '안녕' FROM book;

오라클에서 ||는 문자열 더하기 연산이다.

SELECT * FROM book WHERE BOOKNAME LIKE '%' || '축구' || '%'; 

 '%축구%'와 같다. 굳이 이렇게 표현하는 이유는 나중에 써먹을라고 .. ?

 

SELECT SYSDATE FROM dual;

dual을 쓰는 이유는 데이터를 간단히 한행으로 확인하고 싶을때 사용한다.

SELECT 1234 + 555 FROM dual;

 

반올림 ROUND(a, b) , 버림 TRUNC(a, b)

--> a에는 값을 입력, b에는 어디까지 표시하고 싶은지 나타낸다.

b가 양수면 소수점. 기준으로 오른쪽, 음수면 소수점. 기준으로 왼쪽이다.

SELECT ROUND(12.3456782332, 2) FROM dual; 

SELECT TRUNC(12.3456782332, 2) FROM dual; 

SELECT TRUNC(443, -1) FROM dual; --> 출력값 440

 

REPLACE(a, b, c)

-->a 는 바꿀 대상 VECTOR(아마도?) , b는 바꿀 단어 선택, c는 바꿔질 단어 선택

SELECT REPLACE (BOOKNAME, '축구', '바보' ) FROM BOOK;

 

CONCAT(a, b)

-->a는 추가할 대상 선택, b는 추가할 문자 입력

SELECT concat (bookname, ' 안녕')FROM book;

 

숫자 ←→ 문자 

TO_CHAR( NUMBER TYPE ), TO_NUMBER( 문자 타입 ) 

SELECT TO_CHAR(1234) FROM dual; 

SELECT TO_NUMBER( '1234' ) FROM dual; 

 

*중요* 날짜를 문자 TO_CHAR 

SELECT TO_CHAR (ORDERDATE, 'yyyy-mm-dd, hh-mm-ss') FROM ORDERS o;

SELECT TO_CHAR (SYSDATE, 'yyyy-mm-dd, hh-mm-ss') FROM dual;

,뒤의 형식을 통해서 나타내고 싶은 위치까지 표시할 수 있다.

*중요* 문자를 날짜로 TO_DATE

SELECT TO_DATE('2014-07-11', 'yyyy-mm-dd') FROM Dual;

 

LENGTH()

-->길이 반환 - 공백 1, 한글 1, 알파벳(대,소 포함) 1 로 처리된다.

SELECT LENGTH(bookname) FROM book; 

 

-- NVL - null값을 변환, NVL(a , b) - ()안의 a 값이 null이면 , b를 출력해라

-- 고객별 총 구매액

 

SELECT c.NAME, NVL(sum(o.SALEPRICE),0) FROM CUSTOMER c --count를 쓰는 순간 틀려진다.(NULL값이 한번계산되기때문에)

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

GROUP BY c.NAME

;

 

SELECT SYSDATE FROM dual;

 

SELECT * FROM CUSTOMER c ;

 

SELECT * FROM CUSTOMER WHERE PHONE IS NULL; --로 null값 비교

SELECT * FROM CUSTOMER WHERE PHONE IS NOT NULL;

 

-- < ROWNUM > -- 정복해야함 !!!중요!!!

SELECT c.*, ROWNUM FROM CUSTOMER c ; -- 출력된 행에 순서를 붙인다.

 

 

--<서브 쿼리>

--인라인뷰 -> for문 끝나고, for문 실행되는 느낌~

 

SELECT * FROM (

SELECT o.CUSTID AS a, AVG(o.SALEPRICE) AS b FROM ORDERS o --> 별칭을 꼭 지어줘야 사용가능

GROUP BY o.CUSTID --> 아래것을 테이블로 사용하고 싶을때

) t1 --< from() 안에 있는것을 t1로 칭하겠다

INNER JOIN CUSTOMER c ON c.CUSTID = t1.a --> t1.a 는 바뀐 custid

WHERE t1.b > 10000

;

 

SELECT o.CUSTID, AVG(o.SALEPRICE) FROM ORDERS o

GROUP BY o.CUSTID

;

 

--고객번호가 2 이하인 고객의 판매액을 보이시오.

SELECT * FROM CUSTOMER WHERE CUSTID <= 2

 

SELECT cs.name, sum(od.SALEPRICE) from(

SELECT * FROM CUSTOMER WHERE CUSTID <= 2

) cs

INNER JOIN ORDERS od ON od.CUSTID = cs.custid

GROUP BY cs.name

;

 

--스칼라 서브쿼리 (SELECT 서브쿼리)

SELECT

o.*,

(

SELECT c.NAME FROM CUSTOMER c WHERE c.CUSTID = o.CUSTID

) AS name

FROM ORDERS o;

 

--마당서점의 고객별 판매액을 보이시오 ( 결과는 고객이름과 고객별 판매액을 출력).

 

SELECT (

SELECT c.NAME FROM CUSTOMER c WHERE c.CUSTID = o.CUSTID

) AS NAME

, sum(o.SALEPRICE) AS sum FROM ORDERS o

GROUP BY o.CUSTID -->PRIMARY key로 그룹바이

 

-- 네스티드 서브쿼리 (where 부속질의)

-- 평균 주문금액 이하의 주문에 대해서 주문번호와 금액을 보이시오.

 

SELECT o.ORDERID, o.SALEPRICE FROM ORDERS o

WHERE o.SALEPRICE <= (SELECT avg(o2.SALEPRICE) FROM ORDERS o2

)

;

 

SELECT avg(o2.SALEPRICE) FROM ORDERS o2

;

 

-- 각 고객의 평균 주문금액보다 큰 금액의 주문 내역에 대해서 주문번호, 고객번호, 금액을 보이시오.

SELECT * FROM ORDERS o1

WHERE o1.SALEPRICE > (

SELECT avg(o2.SALEPRICE) FROM ORDERS o2 WHERE o2.CUSTID = o1.CUSTID

)

 

;

 

SELECT avg(o2.SALEPRICE) FROM ORDERS o2 WHERE o2.CUSTID = 1;

 

-- 대한민국에 거주하는 고객에게 판매한 도서의 총 판매액을 구하시오.

 

SELECT sum(o1.SALEPRICE) FROM ORDERS o1

WHERE o1.CUSTID IN ( --IN 키워드가 핵심, IN 안에는 vector가 드러감

SELECT c.CUSTID FROM CUSTOMER c

WHERE ADDRESS LIKE '%대한민국%'

);

 

SELECT c.CUSTID FROM CUSTOMER c

WHERE c.ADDRESS LIKE '%대한민국%'

;

 

--where 연산자 종류: = < > , IN, ALL, ANY(SOME), EXISTS

SELECT * FROM CUSTOMER c

WHERE c.CUSTID = ANY (3,3,4) --> 완벽하게 IN과 같다

;

 

SELECT * FROM CUSTOMER c

WHERE c.CUSTID >= ANY(3,3,4) --> ANY는 앞에 부등호(크냐, 작냐) 가 나와야 한다. + 연산자와 조합가능

;

 

SELECT * FROM CUSTOMER c

WHERE c.CUSTID >= SOME(3,3,4) --> ANY 와 같다 + OR 연산자 같은 느낌

;

 

SELECT * FROM CUSTOMER c

WHERE c.CUSTID >= ALL (3,3,4) --> ALL 은 AND 연산자 안에다가 벡터를 서브 쿼리로 넣을 수 있다.

;

 

--3번 고객이 주문한 도서의 최고 금액보다 더 비싼 도서를 구입한 주문의 주문번호와 금액을 보이시오.

SELECT * FROM ORDERS o1

WHERE o1.SALEPRICE > ALL(

SELECT o2.SALEPRICE FROM ORDERS o2

WHERE o2.CUSTID = 3

)

;

 

SELECT o2.SALEPRICE FROM ORDERS o2

WHERE o2.CUSTID = 3

;

 

-- Exists

-- Exists 연산자로 대한민국에 거주하는 고객에게 판매한 도서의 총 판매액을 구하시오.

SELECT * FROM ORDERS o1

WHERE EXISTS(

SELECT * FROM CUSTOMER c

WHERE c.CUSTID = o1.CUSTID

AND c.ADDRESS LIKE '%대한민국%'

)

;

 

SELECT * FROM CUSTOMER c

WHERE c.CUSTID = 2 --> 1,2,3,4,5 넣어보는 이유는 나오냐 안나오냐로 TRUE,FALSE 확인

AND c.ADDRESS LIKE '%대한민국%'

;