학원수업_Oracle

테스트

logloglog 2020. 10. 30. 19:38
--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;




'학원수업_Oracle' 카테고리의 다른 글

201102  (0) 2020.11.02
테스트2  (0) 2020.10.30
201027 SUB_QUERY  (0) 2020.10.27
201027  (0) 2020.10.27
201026 외부조인(OUTER JOIN)  (0) 2020.10.26