--31) 회원테이블의 회원ID를 대문자로 변환하여 검색(Alias명은 변환 전ID, 변환 후ID )
SELECT MEM_ID 변환전ID,
UPPER(MEM_ID) 변환후ID
FROM MEMBER;
--32) 회원테이블의 성씨 조회
SELECT SUBSTR(MEM_NAME,1,1) 성씨
FROM MEMBER;
--33) 상품테이블의 상품명의 4째 자리부터 2글자가 '칼라' 인 상품의 상품코드, 상품명을 검색( Alias명은 상품코드, 상품명 )
SELECT PROD_ID 상품코드,
PROD_NAME 상품명
FROM PROD
WHERE SUBSTR(PROD_NAME,4,2)='칼라';
--34) 상품테이블의 상품코드에서 왼쪽4자리, 오른쪽6자리를 검색
SELECT PROD_ID 상품코드,
SUBSTR(PROD_ID,1,4) 대분류,
SUBSTR(PROD_ID,-6) 순번
FROM PROD;
--(Alias명은 상품코드, 대분류, 순번 )
--35) 거래처 테이블의 거래처명 중 '삼' --> '육' 으로 치환
SELECT REPLACE(BUYER_NAME,'삼','육') 거래처명
FROM BUYER;
--36) 회원테이블의 회원성명 중 '이' --> '리' 로 치환 검색
--(Alias명은 회원명, 회원명치환 )
SELECT REPLACE(MEM_NAME,'이','리')
FROM MEMBER;
--37) 회원 테이블의 마일리지를 12로 나눈 값을 검색
--(소수3째자리 반올림, 절삭)
SELECT ROUND(MEM_MILEAGE/12,3) 마일리지
FROM MEMBER;
--38) 상품테이블의 상품명, 원가율( 매입가 / 판매가 )을 비율(%)로(반올림 없는 것과 소수 첫째자리 반올림 비교) 검색
--(Alias는 상품명, 원가율1, 원가율2)
SELECT PROD_NAME 상품명,
PROD_COST/PROD_PRICE*100 원가율1,
ROUND(PROD_COST/PROD_PRICE*100,1) 월가율2
FROM PROD;
--39) 상품테이블에서 상품입고일을 '2008-09-28' 형식으로 나오게 검색
--(Alias 상품명, 상품판매가, 입고일)
SELECT PROD_NAME 상품명,
PROD_PRICE 상품판매가,
TO_CHAR(PROD_INSDATE,'YYYY-MM-DD') 입고일
FROM PROD;
--1) 상품테이블(PROD)의 상품분류(PROD_LGU)별 매입가격(PROD_COST) 평균 값
--(소수점 2자리 반올림)
SELECT PROD_LGU 상품분류,
ROUND(AVG(PROD_COST),2) 매입평균값
FROM PROD
GROUP BY PROD_LGU;
--2) 상품테이블(PROD)의 총 판매가격(PROD_SALE) 평균 값
--(Alias는 상품총판매가격평균)
SELECT AVG(PROD_SALE) 상품총판매가격평균
FROM PROD;
--3) 상품테이블(PROD)의 상품분류별(PROD_LGU) 판매가격(PROD_SALE) 평균 값
SELECT PROD_LGU 상품분류,
AVG(PROD_SALE) 판매가격평균
FROM PROD
GROUP BY PROD_LGU;
--(Alias는 상품분류, 상품분류별판매가격평균)
--4) 회원테이블(MEMBER)의 취미종류(MEM_LIKE)수를 COUNT집계
--( Alias는 취미종류수 )
SELECT COUNT(MEM_LIKE) 취미종류수
FROM MEMBER;
--5) 회원테이블(MEMBER)의 취미(MEM_LIKE)별 COUNT집계
--( Alias는 취미, 자료수, 자료수(*) )
SELECT MEM_LIKE 취미,
COUNT(MEM_NAME) 자료수,
COUNT(*) AS "자료수(*)"
FROM MEMBER
GROUP BY MEM_LIKE;
--6) 회원테이블(MEMBER)의 직업종류(MEM_JOB)수를 COUNT집계
--( Alias는 직업종류수 )
SELECT COUNT(MEM_JOB) 직업종류수
FROM MEMBER;
--7) 장바구니테이블(CART)의 회원별 최대구매수량(CART_QTY)을 검색
--( Alias는 회원ID(CART_MEMBER), 최대수량, 최소수량 )
SELECT CART_MEMBER 회원ID,
MAX(CART_QTY) 최대수량,
MIN(CART_QTY) 최소수량
FROM CART
GROUP BY CART_MEMBER;
--8) 오늘이 2005년도7월11일이라 가정하고 장바구니테이블(CART)에 발생될 추가주문번호(CART_NO)를 검색
--( Alias는 최고치주문번호, 추가주문번호 )
SELECT MAX(TO_NUMBER(CART_NO))최고치주문번호,
MAX(TO_NUMBER(CART_NO))+1 추가주문번호
FROM CART
WHERE SUBSTR(CART_NO,1,8)='20050711';
--9) 상품테이블(PROD)의 상품분류(PROD_LGU)별 판매가(PROD_SALE) 합계 값
SELECT PROD_LGU 상품분류,
SUM(PROD_SALE) 판매가합계
FROM PROD
GROUP BY PROD_LGU;
--10) 상품입고테이블(BUYPROD)의 상품(BUY_PROD)별 입고수량(BUY_QTY)의 합계 값
SELECT BUY_PROD 상품,
SUM(BUY_QTY) 입고수량합계
FROM BUYPROD
GROUP BY BUY_PROD;
--11) 장바구니테이블(CART)의 상품분류(CART_PROD 왼쪽 네자리)별 판매수량(CART_QTY)의 합계 값
--( Alias는 상품, 판매수량합계 )
SELECT SUBSTR(CART_PROD,1,4) 상품분류,
SUM(CART_QTY) 판매수량합계
FROM CART
GROUP BY SUBSTR(CART_PROD,1,4);
--12) 회원테이블(MEMBER)의 회원전체의 마일리지(MEM_MILEAGE) 평균, 마일리지 합계, 최고 마일리지, 최소 마일리지,인원수를 검색
--(Alias는 마일리지평균, 마일리지합계,최고마일리지, 최소마일리지,인원수)
SELECT ROUND(AVG(MEM_MILEAGE)) 마일리지평균,
SUM(MEM_MILEAGE) 마일리지합계,
MAX(MEM_MILEAGE) 최고마일리지,
MIN(MEM_MILEAGE) 최소마일리지,
COUNT(*) 인원수
FROM MEMBER;
--13) 상품테이블(PROD)에서 거래처(PROD_BUYER), 상품분류(PROD_LGU)별로 최고판매가(PROD_SALE), 최소판매가, 자료 수를 검색
SELECT PROD_BUYER 거래처,
PROD_LGU 상품분류,
MAX(PROD_PRICE) 최고판매가,
MIN(PROD_PRICE) 최소판매가,
COUNT(*) 자료수
FROM PROD
GROUP BY PROD_BUYER,PROD_LGU;
--14) 장바구니테이블(CART)에서 회원(CART_MEMBER), 상품분류(CART_PROD 왼쪽 네자리)별로
--구매수량(CART_QTY)평균, 구매수량합계, 자료수를 검색( Alias는 회원ID, 상품분류, 구매수량평균, 구매수량합계, 자료수 )
--(회원ID(CART_MEMBER), 상품분류 순으로 SORT하시오)
SELECT CART_MEMBER 회원ID,
SUBSTR(CART_PROD,1,4) 상품분류,
AVG(CART_QTY) 구매수량평균,
SUM(CART_QTY) 구매수량합계,
COUNT(*) 자료수
FROM CART
GROUP BY CART_MEMBER ,
(CART_PROD,1,4);
--15) 회원테이블(MEMBER)에서 지역(주소1(MEM_ADD1)의 2자리),생일년도(MEM_BIR)별로 마일리지(MEM_MILEAGE)평균,
--마일리지합계, 최고마일리지, 최소마일리지, 자료수를 검색(
--Alias는 지역, 생일연도, 마일리지평균, 마일리지합계, 최고마일리지,최소마일리지, 자료수 )
SELECT SUBSTR(MEM_ADD1,1,2) 지역,
EXTRACT(YEAR FROM MEM_BIR) 생일년도,
AVG(MEM_MILEAGE) 마일리지평균,
SUM(MEM_MILEAGE) 마일리지합계,
MAX(MEM_MILEAGE) 최고마일리지,
MIN(MEM_MILEAGE) 최소마일리지,
COUNT(*) 자료수
FROM MEMBER
GROUP BY SUBSTR(MEM_ADD1,1,2) ,
EXTRACT(YEAR FROM MEM_BIR);
--16) 거래처(BUYER_NAME) 담당자(BUYER_NAME)가 NULL인 자료 검색
--(alias는 거래처, 담당자)
SELECT BUYER_NAME 거래처,
BUYER_CHARGER 담당자
FROM BUYER
WHERE BUYER_CHARGER IS NULL;
--16-1) 거래처(BUYER_NAME) 담당자(BUYER_CHARGER)가 NULL이 아닌 자료 검색
--(alias는 거래처, 담당자)
SELECT BUYER_NAME 거래처,
BUYER_CHARGER 담당자
FROM BUYER
WHERE BUYER_CHARGER IS NOT NULL;
--17) 거래처(BUYER_NAME) 담당자(BUYER_CHARGER)가 없는 경우 ‘없다’로 치환하여 자료 검색(alias는 거래처, 담당자)
SELECT BUYER_NAME 거래처,
NVL(BUYER_CHARGER,'없다') 담당자
FROM BUYER;
--18) 상품 분류(PROD_LGU) 중 앞의 두 글자가 'P1' 이면 판매가(PROD_SALE)를 10%인상하고 'P2' 이면 판매가를 15%인상하고,
--나머지는 동일 판매가로 검색
--(DECODE 함수 사용, Alias는 상품명(PROD_NAME), 판매가, 변경판매가 )
SELECT PROD_NAME 상품명,
PROD_SALE 판매가,
DECODE(SUBSTR(PROD_LGU,1,2),'P1',PROD_SALE*1.1,'P2',PROD_SALE*1.15,PROD_SALE) 판매변경가
FROM PROD;
--19) 회원정보테이블(MEMBER)의 주민등록 뒷자리(MEM_REGNO2)(7자리 중 첫째자리)에서 성별 구분을 검색
--( CASE 구문 사용, Alias는 회원명,주민등록번호(주민1-주민2), 성별 )
SELECT MEM_NAME 회원명,
CONCAT(MEM_REGNO1,'-'||MEM_REGNO2) 주민등록번호,
CASE WHEN SUBSTR(MEM_REGNO2,1,1)='1' THEN '남자' ELSE '여자' END 성별
FROM MEMBER;