14주차 수 1122( 데이터분석, 함수)

-- 회사 종속 함수들...`다른` DB에서는 함수이름이 조금 다름


-- [문제]
-- 상품분류명에 컴퓨터가 포함되어 있고,
-- 주문이력이 있는 상품에 대한 데이터를 조회
-- 상품명, 상품분류명 조회하기
SELECT prod_name,
		(SELECT 
					lprod_nm
		FROM lprod
		WHERE lprod_gu = prod_lgu) AS lprod_nm
FROM prod
-- 조건절
WHERE prod_lgu IN (SELECT lprod_gu
							FROM lprod
							WHERE lprod_nm LIKE '%컴퓨터%')
AND prod_id IN (SELECT cart_prod
					FROM cart);

-- Replace() : 치환하기
SELECT REPLACE('컬럼1에 찾을값이 있다',
					 '찾을값', 
					 '바꿀값') AS msg;

-- 회원의 성씨 중에 이씨를 리씨로 바꾸기
SELECT
		(CONCAT(REPLACE(substring(mem_name,1,1),'이','리'),
		SUBSTRING(MEM_NAME,2,2)))AS REP_NAME
FROM member;

SELECT 
		MEM_NAME,
			(REPLACE(MEM_NAME, '이', '리'))AS REP1,
			(REPLACE(SUBSTRING(MEM_NAME,1,1), '이', '리'))AS REP2,
			SUBSTRING(MEM_NAME,2,2)AS REP3,
			(CONCAT(REPLACE(SUBSTRING(MEM_NAME,1,1), '이', '리'),
			SUBSTRING(MEM_NAME,2,2)))AS REP_NAME
FROM MEMBER
WHERE SUBSTRING(MEM_NAME,1,1) = '이';

-- Round() : 반올림 함수
-- 0은 : 소수점의 위치를 의미함

-- [문제]
-- 상품정보에서 상품명, 원가율을 조회
-- 원가율 = 매입가 / 판매가의 백분율 값
-- 원가율은 소숫점 2자리까지 표현
SELECT 
		prod_name,
		ROUND((prod_cost / prod_sale *100),2)AS 원가율
FROM prod;

-- [문제]
-- 회원중에 취미가 수영인 회원이 구매한 상품들을 조회
-- 서울, 대전, 광주에 거주하는 회원
-- 상품분류 중에 피혁이라는 분류에 속해있는 상품을 구매한 회원
-- 조회컬럼 상품명, 상품분류명, 원가(소숫점 2자리까지) 
SELECT 
		prod_name,
		(SELECT lprod_nm
		FROM lprod
		WHERE lprod_gu = prod_lgu) AS lprod_nm,
		ROUND((prod_cost / prod_sale *100),2)AS 원가율
FROM prod
WHERE prod_id IN (SELECT cart_prod
						FROM cart
						WHERE cart_member IN (SELECT mem_id
													FROM member
													WHERE mem_like LIKE '%수영%' 
													AND SUBSTRING(mem_add1, 1, 2) IN ('서울','대전','광주')))
AND prod_lgu IN (SELECT lprod_gu
						FROM lprod
						WHERE lprod_nm LIKE '%피혁%') ;

SELECT prod_name,
		 (SELECT lprod_nm
			FROM lprod
			WHERE lprod_gu = prod_lgu)AS lprod_nm,
		 ROUND((prod_cost / prod_sale *100),2)AS 원가율
FROM prod
WHERE prod_id IN (
		SELECT cart_prod
		 FROM cart
		  WHERE cart_member IN (
				SELECT mem_id
				 FROM member
				  WHERE mem_like LIKE '%수영%'
				   AND SUBSTRING(mem_add1, 1, 2) IN ('서울','대전','광주')))
 AND prod_lgu IN (SELECT lprod_gu
 						 FROM lprod
						  WHERE lprod_nm LIKE '%피혁%' );

-- 조건 컬럼 대신 [조건문]으로 조회하기
-- Case 문
-- 작성방법
/*
	Case 기준값
		When 비교값 Then 처리할 값
		When 비교값 Then 처리할 값
		Else 처리할 값
	End
*/
SELECT Case 1
			when 1 then '1 이올시다'		
			when 0 then '0 이올시다'
			ELSE '음수 이올시다'
		END AS case_data;
		
/*
	Case 
		When 비교연산자 Then 처리할 값
		When 비교연산자 Then 처리할 값
		Else 처리할 값
	End
*/
SELECT Case 
			when 1 < 0 then '1 이올시다'		
			when 1 > 0 then '0 이올시다'
			ELSE '음수 이올시다'
		END AS case_data;

-- [문제]
-- 회원아이디, 회원이름, 성별을 조회해 주세요
SELECT
		mem_id,
		mem_name,
		case substring(mem_regno2,1,1) 
				when 1 then '남자'
				when 2 then '여자'	 		
		End AS '성별'
FROM member;

SELECT mem_id, mem_name,
		(Case SUBSTR(mem_regno2,1,1)
				when 1 Then'남자'
				When 3 Then'남자'
				ELSE '여자'
			END) AS MF
FROM member;

-- if : mysql, mariadb에만 있음
SELECT if(10 > 100, '크다', if (10 < 10, '같다','작다')) AS if_test;

-- 나눈 나머지의 값 함수 : mod()
SELECT mod(10, 2) AS mod_test;

-- [문제]
-- 회원이름, 회원성별, 마일리지, 고객구분
-- 고객구분은 마일리지의 값이 100이상이면 '우수고객'	
--                               미만이면 '일반고객'
SELECT
		mem_name,
		if(mod(SUBSTR(mem_regno2,1,1),2) > 0,'남자', '여자')AS '성별',
		mem_mileage,
		if(mem_mileage >= 5000, '우수고객', '일반고객') AS '고객구분'
FROM member;

-- [문제]
-- 회원이 구매한  상품명, 판매가격을 조회
-- 단, 여성인 회원들이 구매한 상품이고,
--     우수고객이 구매한 상품에 대해서만 조회
-- 우수고객은 마일리지 값이 5000점 이상인 회원을 의미
SELECT prod_name, prod_sale
FROM prod
WHERE prod_id IN (select cart_prod
						FROM cart
						WHERE cart_member IN(SELECT mem_id
													FROM member
													WHERE mem_mileage >= 5000 
													 AND	MOD(SUBSTR(mem_regno2,1,1),2) = 0));
													 
SELECT prod_name, prod_sale
FROM prod
WHERE prod_id IN (SELECT cart_prod
						FROM cart
						WHERE cart_member IN(SELECT mem_id
													FROM	member
													WHERE MOD(SUBSTR(mem_regno2,1,1),2) = 0
													AND	if(mem_mileage >= 5000, '우수', '일반')='우수'));

-- null, NaN, None : null 체크 -> nvl(null, 'null인경우 값')
-- nvl(컬럼명, null인경우 대체할 값)
-- null인 경우 : 메모리가 존재하지 않는 값
-- ''인 경우: 메모리가 존재하지만 값은 없는 것...(비어있는 것)
-- 			: white space 
-- insert Into Prod(, , ,prod_color, , ,) values(, , ,null, , ,)
-- insert Into Prod(, , ,prod_color, , ,) values(, , ,'', , ,)
-- 아래는 prod_color을 아예 넣지 않음
-- insert Into Prod(, , , , , ,) values(, , , , , ,)
SELECT nvl(NULL, 'null이구먼'),
		 nvl(1, 'null이구먼');
-- null값 찾기 : is null (where절 내에서만 사용가능)
SELECT	prod_name, nvl(prod_mileage, 0) AS prod_mileage,
			-- IFNULL(prod_mileage, 1) AS if_mileage
FROM prod
WHERE prod_mileage IS null;

-- 날짜 함수
-- now() : 데이터 insert시에 주로 사용
-- curdate() : 데이터 insert시에 년월일만 넣고자 할 때 주로 사용
-- 시간은 자주 사용되지는 않음
SELECT NOW() AS '오늘날짜', CURDATE() AS '오늘 년월일',
			CURTIME() AS '오늘 시분초';
			
-- 날짜 포맷 이용하기
SELECT DATE_FORMAT('2023112144059','%Y-%m-%d %H:%i:%s') AS dt1,
			DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS dt2;

-- 회원아이디, 회원이름, 회원생일 조회
-- 생일은 '년-월-일' 형태로 조회
SELECT 
		mem_id,
		mem_name,
		date_format(mem_bir, '%Y-%m-%d') AS mem_bir,
		date_format(mem_regno1, '%Y-%m-%d') AS mem_regno1
FROM member;

-- 형(타입) 변환 함수
-- Cast() or Convert()
-- 형변환을 주로 사용하는 경우 : 문자타입의 날자값을 날자타입으로 바꾸고자 할때 주로 사용됨
SELECT CAST('20231122' AS DATE), CONVERT('20231122', date)