주요내용 : 텍스트 제어함수, 문자자르기, 숫자 제어함수, 데이터 조작함수
/*-- (빈공백은 나오지 않게 처리한다) */
/*1. 고객정보에서 고객이름이 ‘The’로 시작되고 고객국가가 ‘USA’인 고객의 주소정보를 다음의 Format으로 추출하시오
(빈공백은 나오지 않게 처리한다)
Format = 고객주소’ ‘고객시/도’ ‘고객주’ ‘고객우편번호’(‘고객국가’)’
*/
SELECT TRIM(CUST_ADDRESS) || '''' || '''' ||TRIM(cust_city) || '''' || '''' || TRIM(cust_state)|| '''' || '''' || TRIM(cust_zip) || '''' || '(' || '''' || TRIM(cust_country) || '''' || ')' || ''''
FROM CUSTOMERS
WHERE CUST_NAME LIKE 'The%'
AND CUST_country = 'USA';
/*2. 주문제품항목에서 주문번호가 ‘20005’, ‘20007’ 인 주문에 대해 주문번호, 제품번호와 주문총금액(항목수량*항목가격) 을 추출하시오*/
SELECT order_num, prod_id, (quantity*item_price)
FROM orderitems
WHERE order_num IN ('20005', '20007');
/*3. 제품정보에서 공급업체번호가 ‘BRS01’, ‘DLL01’이 아닌 제품이름과 제품가격을 다음의 Format으로 추출하시오
Format = 제품이름’[’제품가격’]’
*/
SELECT TRIM(prod_name) ||'['||prod_price||']'
FROM Products
WHERE vend_id NOT IN ('BRS01', 'DLL01');
/*텍스트 제어함수*/
/*공급업체이름의 길이*/
SELECT vend_id, LENGTH(TRIM(vend_name))
FROM vendors;
/*공급업체 주소를 소문자 or 대문자로 */
SELECT vend_id, LOWER(vend_address), UPPER(vend_address)
FROM vendors;
/*문자 자르기*/
SELECT vend_id, SUBSTR(vend_address, 1, 3), SUBSTR(vend_address, 4)
FROM vendors;
--문자 대체하기--
SELECT vend_id, REPLACE(vend_address, 'Street', 'Road')
FROM vendors;
--NULL이 아닌 첫번째 값--
SELECT vend_id, vend_state, vend_city, COALESCE(vend_state, vend_city)
FROM vendors
WHERE vend_id = 'FNG01';
-- 공급업체주가 CA이면 1, NY이면 2 모두 아니면 3--
SELECT vend_id, vend_state, DECODE(TRIM(vend_state), 'CA', 'NY', 2, 3)
FROM vendors;
--NULL이면 다른 값으로
--nvl : 널값이면 디폴트값 리턴
SELECT nvl(cust_email, 'test@naver.com')
FROM customers;
--1. 고객의 주소 중에 소문자로 ‘south’가 들어간 고객정보를 다음의 Format으로 추출하시오(공백제거) - Format: 고객이름’/’고객주소, 소문자로
SELECT RTRIM(cust_name)||'/'||LTRIM(cust_address)
FROM Customers
WHERE LOWER(cust_address) LIKE '%south%';
--2. 주문상품의 주문번호가 ‘20005’, ‘20007’가 아닌 제품유형코드를 추출하시오 - 제품유형코드: 제품번호의 앞의 2자리
SELECT SUBSTR(prod_id, 0, 2)
FROM Orderitems
WHERE order_num NOT IN ('20005', '20007');
--3. 고객의 고객이름과 고객주소정보를 추출하시오 - 고객주소정보: 고객주소중에 Drive는 Car로 변경
SELECT cust_name, REPLACE(cust_address, 'Drive', 'Car')
FROM Customers;
--4. 공급업체의 공급업체번호가 ‘BR’로 시작되는 공급업체이름과 공급업체지역번호를 공급업체이름으로 내림차순으로 추출한다 - 공급업체지역번호: 우편번호의 앞에 2자리
SELECT vend_name, SUBSTR(vend_zip, 0, 2)
FROM Vendors
WHERE vend_id LIKE 'BR%' ORDER by vend_name DESC;
--5. 공급업체의 공급업체번호가 ‘BRE02’이 아닌 공급업체이름과 공급업체사업구역번호를 추출한다 - 공급업체사업구역번호: 공급업체지역번호가 44이면 A, 99이면 B, 11이면 C 나머지는 D
SELECT vend_name, DECODE(SUBSTR(vend_zip,1,2), '44', 'A', '99', 'B', '11', 'C', 'D')
FROM Vendors
WHERE vend_id <> 'BRE02';
--6. 고객의 고객명과 고객주소정보를 추출한다 - 고객주소정보: 고객메일주소 or 고객주소, 고객메일주소가 있으면 고객메일주소가 우선
SELECT cust_name, COALESCE(cust_email, cust_address)
FROM Customers;
--7. 제품의 제품명에 ‘bear’이 들어있는 모든 제품에 대해 제품이름을 추출하는데 bear를 toy로 변경하여 추출한다
SELECT REPLACE(prod_name, 'bear', 'toy')
FROM Products
WHERE prod_name LIKE '%bear%';
/*숫자제어함수*/
-- 올림, 내림
SELECT prod_id, prod_price, CEIL(prod_price), FLOOR(prod_price)
FROM products;
-- 반올림, 버림
SELECT prod_id, prod_price, ROUND(prod_price,1), TRUNC(prod_price,1)
FROM products;
-- 제곱, 제곱근
SELECT prod_id, prod_price, POWER(prod_price,2), SQRT(prod_price)
FROM products;
-- 나머지
SELECT prod_id, prod_price, MOD(prod_price,2)
FROM products;
--1. 제품의 제품번호가 ‘BR’로 시작되는 상품의 가격을 소수점 첫째자리에서 반올림 하여 제품이름과 함께 추출하시오
SELECT RTRIM(prod_name), ROUND(prod_price, 0)
FROM Products
WHERE prod_id LIKE 'BR%';
--2. 주문상품중에 가격이 10$이상인 주문번호와 상품번호, 항목가격을 추출하시오 - 항목가격: 항목가격은 소수점 버림으로 처리하여 추출
SELECT order_num, prod_id, TRUNC(item_price,0)
FROM Orderitems
WHERE item_price >=10;
/*데이터 조작함수 사용하기*/
-- 현재날짜와 주문날짜 간의 월차이
SELECT order_num, TO_CHAR(order_date, 'YYYY-MM-DD'), MONTHS_BETWEEN(sysdate, order_date)
FROM orders;
-- 주문날짜에서 10개월을 추가
SELECT order_num, TO_CHAR(order_date, 'YYYY-MM-DD'), TO_CHAR(ADD_MONTHS(order_date,10), 'YYYY-MM-DD')
FROM orders;
-- 주문한 달의 마지막 날짜
SELECT order_num, TO_CHAR(order_date, 'YYYY-MM-DD'), TO_CHAR(LAST_DAY(order_date), 'YYYY-MM-DD')
FROM orders;
-- 특정날짜와 주문날짜간의 월차이
SELECT order_num, TO_CHAR(order_date, 'YYYY-MM-DD'), MONTHS_BETWEEN(TO_DATE('2018-12-05', 'YYYY-MM-DD'), order_date)
FROM orders;
-- 숫자를 문자로
SELECT order_num, quantity, 'A'||TO_CHAR(quantity)
FROM orderitems;
-- 날짜를 문자로
SELECT order_num, TO_CHAR(order_date, 'YYYY-MM-DD'), TO_CHAR(sysdate, 'YYYY/MM/DD HH24:MI:SS')
FROM orders;
-- 2004년에 주문된 항목의 주문번호
SELECT order_num
FROM Orders
WHERE TO_NUMBER(TO_CHAR(order_date, 'YYYY'))=2004;
-- BETWEEN으로 구현
SELECT order_num
FROM Orders
WHERE order_date BETWEEN TO_DATE('20040101') AND TO_DATE('20041231');
-- 1. 고객주문에서 주문월일 2월인 모든 주문번호를 주문번호를 내림차순으로 하여 추출한다
SELECT order_num, order_date
FROM Orders
WHERE TO_NUMBER(TO_CHAR(order_date, 'MM'))=2 Order by order_num DESC;
-- 2. 고객주문에서 주문일에 +1일을 한 날자가 해당 주문월의 마지막 날자와 같은 주문번호를 오름차순으로 추출한다
SELECT order_num
FROM Orders
WHERE LAST_DAY(order_date) = (order_date + 1)
Order by order_num ASC;
-- 3. 고객주문에서 현재일과 주문일간의 차이가 185개월을 넘는 주문번호를 추출한다
SELECT order_num, order_date
FROM Orders
WHERE MONTHS_BETWEEN(sysdate, order_date) > 185;
-- 4. 고객주문에서 고객번호가 1로 끝나는 주문의 주문날짜의 월일을 추출하시오 (Format예 : 07-01)
SELECT cust_id, TO_CHAR(order_date, 'MM')||'-'||TO_CHAR(order_date, 'DD') /* TO_CHAR(order_date, 'MM-DD')*/
FROM Orders
WHERE cust_id LIKE '%1'; /* WHERE SUBSTR(cust_id, LENGTH(cust_id),1) = '1'*/
