컴퓨토피아

--**********************************************************************
--      함수(날짜열)
--**********************************************************************

함수(날짜열)
DATE구분	구분약자	DATE구분	구분약자
year	yy	week	wk
quarter	qq	hour	hh
month	mm	minute	mi
day of year	dy	second	ss
day	dd	millisecond	ms
항목	내용
GETDATE()	시스템에서 제공하는 날짜와 시간 값
산술연산	-- date + NUMBER -- date - NUMBER

--날짜는 계산할 수 있다.
--getdate()
select getdate()'현재날짜 & 시간'
--getdate() -1, +1
select getdate() - 1'어제 이 시간',
   getdate() + 1'내일 이 시간'
--오늘부터 100일 전의 날짜를 검색!
select getdate() - 100
--회원테이블의 생일과 1200일째 되는 날을 검색! (Alias는 회원명, 생일, 12000일째)
select mem_name "회원명", mem_bir "생일",
   getdate() + 1200 "1200일째"
 from member

--**********************************************************************
--   DATEADD 함수(날짜열)
--**********************************************************************
--DATEADD (Part,n,date)/Part부분에 n만큼 더한 date
--      (DATE구분별로 모두 적용)  

select getdate() '현재날짜 & 시간',
   dateadd(year, 1, getdate()) '1년뒤'
--
select getdate() '현재날짜 & 시간',
   dateadd(mm,-1, getdate()) '한달전'
--
select getdate()'현재날짜 & 시간',
   dateadd(dd, -50, getdate()) '50일전'
--
select getdate()'현재날짜 & 시간',
   dateadd(hour, 7, getdate()) '7시간 뒤'
--
select dateadd(yy, 1, '19990228') "1년후",
   dateadd(yy, 2, '19990228') "2년후",
   dateadd(yy, 3, '19990228') "3년후"
--
select dateadd(mm, 12, '19000228') "1년후",
   dateadd(mm, 24, '19000228') "2년후",
   dateadd(mm, 36, '19000228') "3년후"
--오늘부터 6개월 전의 날짜를 검색!
select dateadd(mm, -6, getdate())"6개월전의 날짜"
--회원테이블에서 구길동회원의 생일과 12000일째 되는 날을 검색!
--(DATEADD사용) (Alias는 회원명, 생일, 12000일째)
--1.
select mem_name"회원명", mem_bir"생일", dateadd(dd,1200,mem_bir)"1200일째" from member
   where mem_name like '구길동%'
--2.
select mem_name"회원명", mem_bir"생일", dateadd(dd,1200,mem_bir)"1200일째" from member
   where mem_name like '구길동'
--3.
select mem_name"회원명", mem_bir"생일", dateadd(dd,1200,mem_bir)"1200일째" from member
   where mem_name='구길동'

--********************************************************************
--      YEAR,MONTH,DAY,DATEDIFF함수(날짜열)
--********************************************************************
--year, month, day 해당일의 년월일 값을 돌려준다.
select year(getdate())'year',
   month(getdate())'month',
   day(getdate())'day'
--datediff(part,date1,date2)/두 날짜 사이의 DATE구분 사이 값을 계산함.
--   (part, date1, date2)
select datediff(dd,'1999/02/15','2000/02/15')'day',
   datediff(mm,'1999/02/15','2000/02/15')'month',
   datediff(yy,'1999/02/15','2000/02/15')'year'
--
select datediff(dd,'1999/11/06','2000/02/15')'day',
   datediff(mm,'1999/11/06','2000/02/15')'month',
   datediff(yy,'1999/11/06','2000/02/15')'year'
--회원테이블의 생일과 오늘의 일수 차이를 검색! (Alias는 회원명, 생일, 오늘, 태어난지?)
select mem_name"회원명", mem_bir"생일",
   getdate()"오늘",
   datediff(dd,mem_bir,getdate())"태어난지?"
 from member
--본인의 생년월일과 오늘의 일수 차이를 비교 검색!
select datediff(dd,'1972/08/14',getdate()) "차이는"

--*******************************************************************
--      Datename, Datepart함수(날짜열)
--*******************************************************************
--datename(part,date)/date에서 datepart부분의 ASCII값(구분문자)
--datepart(part,date)/date에서 datepart부분의 정수값(구분숫자)
select convert(char,getdate()), '<==현재날짜'
select datename(yy,getdate()),datepart(yy,getdate()),'<==년도'
select datename(qq,getdate()),datepart(qq,getdate()),'<==분기'
select datename(mm,getdate()),datepart(mm,getdate()),'<==월'
select datename(dy,getdate()),datepart(dy,getdate()),'<==일수'
select datename(dd,getdate()),datepart(dd,getdate()),'<==해당일'
select datename(wk,getdate()),datepart(wk,getdate()),'<==주수'
select datename(dw,getdate()),datepart(dw,getdate()),'<==요일'
select datename(hh,getdate()),datepart(hh,getdate()),'<==시간'
--회원테이블에서 구길동회원의 생일의 DATENAME 과 DATEPART를 검색!
--(Alias 는 회원명, 생일, 기타 上 同)
select mem_name"회원명", mem_bir"생일",
   datename(dw,mem_bir)'기타上同',
   datepart(dw,mem_bir)'기타上同'
 from member
   where mem_name='구길동'
/* 월 (2002년 2월)만 입력받아 해당월의 시작일과 종료일을 검색!
Alias는 해당월, 시작일, 종료일) */
--******************** 1. 2002-02-01의 1달 후 -1....
select '200년02월' 해당월, '200-02-01' 시작일,
   dateadd(mm,1,'2000-02-01')-1 "종료일"
--******************** 2. 2002-03-01의 1일 전....
select '200년02월' 해당월, '200-02-01' "시작일",
   dateadd(dd,-1,'2000-03-01') "종료일"

--*******************************************************************
--      함수(Conversion)
--*******************************************************************
--cast, convert : 문자로 치환/number와 date를 문자로 치환.
--   /간단한 형 변환(CAST)
--   /날짜를 문자로 변환 시 여러가지 형식이 가능하므로 주로 사용하는 함수는(CONVERT)이다.
select convert(char, 123456789) result1,
   cast(123456789 as char) result2
--
select convert(varchar,456789) result1,
   cast(456789 as varchar) result2,
   str(456789, 10) result3
/* 숫자를 바꾸는 convert함수를 썼는데 2번째줄의 경우는 '300567'이라는 6섯자를
'char(4)' 4자로 바꾸라는 것: 이것은 말이 안되므로 * 표시가 프린트됨!(주의 사항) */
select convert(char(7),300567) result1,
   convert(char(4),300567) result2
--
select convert(char,getdate(),0) result1,
cast(getdate() as char) result2
--
select convert(CHAR, GETDATE(), 109) RESULT
select convert(CHAR, GETDATE(), 111) RESUlT
select convert(CHAR(10), GETDATE(), 121) RESULT
select convert(CHAR, GETDATE(), 114) RESULT

--
select convert(char, getdate(),0)
select convert(char, getdate(),1)
select convert(char, getdate(),2)
select convert(char, getdate(),3)
select convert(char, getdate(),4)
select convert(char, getdate(),5)
select convert(char, getdate(),6)
select convert(char, getdate(),7)
select convert(char, getdate(),8)
select convert(char, getdate(),9)
select convert(char, getdate(),10)
select convert(char, getdate(),11)
select convert(char, getdate(),12)
select convert(char, getdate(),13)
select convert(char, getdate(),14)
--다음 아래는 ERR...
select convert(char, getdate(),15)
select convert(char, getdate(),16)
select convert(char, getdate(),17)
select convert(char, getdate(),18)
select convert(char, getdate(),19)
--여기까지 ERR...
select convert(char, getdate(),20)
select convert(char, getdate(),21)
select convert(char, getdate(),22)
select convert(char, getdate(),23)
select convert(char, getdate(),24)
select convert(char, getdate(),25)
--여기서 또 아래는 ERR...
select convert(char, getdate(),26)
select convert(char, getdate(),27)
select convert(char, getdate(),28)
select convert(char, getdate(),29)
select convert(char, getdate(),30)
select convert(char, getdate(),31)
select convert(char, getdate(),32)
select convert(char, getdate(),33)
select convert(char, getdate(),34)
-- .
-- .
-- .
select convert(char, getdate(),99)
--99까지 ERR
--100단위로 위와 똑같은 숫자안에 포함되어 쓰인다.
--120과 121번의 경우는 우리나라에서 많이 쓴다. 그냥 21번을 쓰기도 한다.
select convert(char, getdate(),120)
select convert(char, getdate(),121)
/* 위에서 일반숫자형을 문자로 변환할 때 변환되어지려는 값과 변환하려는 값의
자릿숫자값이 다르면 에러가 났었지만, 날짜형은 그 자릿수만큼 잘라서 출력한다! */
--다음은 그 예이다.
--1. 날짜형 변환의 예)
select convert(char(10), getdate(),121)
--2. 숫자형 변환의 예)
select convert(char(7),300567) result1,
convert(char(4),300567) result2
--3. 문자형를 자르기 예)
select convert(char(10), 'abcdefghijklmnopqrstuvwxyz')

--122~에서부턴 형식이 맞지 않는다 ERR
select convert(char, getdate(),122)
select convert(char, getdate(),123)
select convert(char, getdate(),124)
select convert(char, getdate(),125)
   :
   :

--상품테이블에서 상품코드와 상품명을 연결하여 30자리로 치환하여 검색!
--Alias는 상품코드, 상품명, 치환상품명)
select prod_id 상품코드, prod_name 상품명,
   convert(char(30), prod_id + prod_name) 치환상품명
  from prod

--***********************************************
--      conversion
--***********************************************
--예제1) // datetime, smalldatetime, decimalzero, decimalpoint, numericzero, numericpoint

select convert(char(8), getdate(),112) result
--
select convert(datetime, '2001-01-01') DATETIME
--
select smalldatetime = convert(smalldatetime,'2001-01-01')
--
select decimalzero = convert(decimal(15),12345678912345)
--
select decimalpoint = convert(decimal(17,2),123456789012345.11)
--
select numericzero = convert(numeric,123456789012345)
select numericpoint = convert(numeric(17,2),123456789012345.11)

--예제2) // float, int, smallint, tinyint, money

select convert(float,123456789012345) float
--
select convert(float,10/3.0) float
--
select convert(int,1234567890) int
--
select convert(smallint,12345) smallint
--
select convert(tinyint, 123) tinyint
--
select convert(money,123456789012345) money

--***********************************************************************
--함수(Conversion) : 숫자로 치환
--***********************************************************************
--   숫자로 치환 // 모양만 수치인 문자를 NUMBER로 치환
--   /모양만 수치라면 연산에는 영향이 없다.
select convert(numeric,'123456.9') result1,
   cast('123456.9' as numeric) result2
--
select convert(numeric(10,2),'123456.9') result1,
   cast('123456.9' as numeric(10,2)) result2
--
select convert(float,'123456.9') result1,
   cast('123456.9' as float) result2
--
select convert(decimal,'123456.9') result1,
   cast('123456.9' as decimal) result2
--*****************************************************
/* 회원테이블에서 이쁜이 회원의 회원ID(b001) 2~4문자열을 숫자형으로 치환한 후 10을 더하여
 새로운 회원ID(b011)로 조합(Alias는 회원ID, 조합회원ID) */

회원ID     조합회원ID    -------   ------------      b001          b011    (1 row(s) affected)

--1. 방법1
select mem_id "회원ID",
   left(mem_id,1) + right(convert(char(4),right(mem_id,3) + 1010),3) "조합회원ID"
   from member
   where mem_name = '이쁜이'
--2. 방법2
select mem_id "회원ID",
   left(mem_id,1) + right(convert(char(4),substring(mem_id,2,4)+1010),3) "조합회원ID"
   from member
   where mem_name = '이쁜이'
--***********************************************************************
--      함수(conversion) : 날짜로 치환
--***********************************************************************
--날짜로 치환/모양만 날짜형인 문자를 DATE로 치환
select'19990101'result1,'1999-01-01'result2,
   '1999-01-01 00:00:00.000'result3
--
select convert(datetime,'20201025',112)" result1",
   convert(datetime,'20201025')" result2",
   convert(datetime,'2020-10-25 10:15:20.000') " result3",
   cast('2020-10-25 10:15:20.000' as datetime) " result4"
--
--회원테이블에서 주민등록번호1을 날짜로 치환한 후 검색!
--(Alias는 회원명, 주민등록번호1, 치환날짜)
select mem_name 회원명, mem_regno1 주민등록번호1,
   convert(datetime,'19'+mem_regno1) 치환날짜
   from member
--1900년대 사람들만 있다고 가정했을 경우 19를 더해서 밀레니엄버그를 없앨 수 가 있다.
--회원테이블에서 주민등록번호1을 날짜로 치환한 후 500일을 더한 날짜를 검색!
--(Alias는 회원명, 주민등록번호1, 치환날짜)
select mem_name 회원명, mem_regno1 주민등록번호1,
   convert(datetime,'19'+mem_regno1) + 500 치환날짜
   from member
--회원테이블에서 주민등록번호1을 날짜로 치환한 후 '1974-01-01'부터 '1975-12-31'사이의 날짜를 검색!
--(Alias 회원명, 주민등록번호1, 치환날짜)
select mem_name 회원명, mem_regno1 주민등록번호1,
   convert(datetime,'19'+mem_regno1) 치환날짜
   from member
   where convert(datetime,'19'+mem_regno1)between'1974-01-01'and'1975-12-31'
--회원테이블에서 생일을 문자로 치환한 후 LIKE '1975%'의 조건을 사용하여 해당회원을 검색!
--Alias는 회원명, 생일)
select mem_name 회원명, mem_bir 생일
   from member
   where convert(char, mem_bir, 121) like '1975%'


<INPUT style="BORDER-TOP-WIDTH: 1px; PADDING-LEFT: 33px; FONT-WEIGHT: bold; BORDER-LEFT-WIDTH: 1px; FONT-SIZE: 11pt; BORDER-LEFT-COLOR: white; BACKGROUND: #d4d4d4; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: white; WIDTH: 770px; BORDER-TOP-COLOR: white; PADDING-TOP: 7px; HEIGHT: 30px; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: white" value="MSSQL 함수 (기타함수)">

--******************************************************************
--      함수(NULL)
--******************************************************************
/* ## 데이터를 처리할 때 NULL값의 사용은 최대한 줄여야 하지만 사용해야 할 경우가 있다.
## 학생에 대한 정보를 입력할 때 '전화번호' 속성은 전화번호가 없는 학생이 있을 수가 있다 .이런 경우에는 NULL값을 사용한다.
## NULL값은 0.1과 같은 특정한 값이 아니고 아무 것도 없는 것을 뜻한다.
## SQL에서 NULL값을 허용하지 않는 속성에 대해 NULL값으로 수정하려 한다면 에러가 발생한다. */

--null값을 찾을 때에는 is null
select buyer_name buyer_charger
   from buyer
   where buyer_charger is null
--null인 값을 찾을 때 '='은 성립되지 않는다.
select buyer_name buyer_charger
   from buyer
   where buyer_charger=null
--
select buyer_name buyer_charger
   from buyer
   where buyer_charger =''
--null값이 아닌 값을 찾으려 할 때 is not null
select buyer_name buyer_charger
   from buyer
   where buyer_charger is not null
--ISNULL(c,d) / c가 NULL값이면 d값으로 치환
--null값일 때 '이름없다' 로 출력!
select buyer_name, buyer_charger, isnull(buyer_charger, '이름없다')
   from buyer
--null에다 100을 더하면 null이다.
select null + 100
--
select isnull(null,0)+100
--
select mem_name, mem_mileage, mem_mileage+100
   from member
--
select mem_name, mem_mileage, mem_mileage + 100
   from member
   where mem_name like '[바-빟]%'
--
update member set mem_mileage = null
   where mem_name like '[바-빟]%'
--
select mem_name, mem_mileage, isnull(mem_mileage,0) + 100
   from member

--NULLIF(c,d) / c와 d를 비교하여 같으면 NULL을 다르면 c값을 돌려준다.
--반환값 : NULL
select nullif(123,123)
--반환값 : NULL
select nullif(1234,'1234')
--반환값 : 123
select nullif(123,1234)
--반환값 : a
select nullif('a','b')

--**************************************************
--예제)
--**************************************************
--거래처테이블에서 거래처명, 담당자 조회!
select buyer_name 거래처,buyer_charger 담당자
   from buyer
--거래처 담당자 성씨가 '김'이면 null로 갱신! 하기전에 먼저 확인!
select buyer_name, buyer_charger
   from buyer
   where buyer_charger like '김%'
--거래처 담당자 성씨가 '김'이면 null로 갱신!
update buyer set buyer_charger=null
   where buyer_charger like '김%'
--거래처 담당자 성씨가 '성'이면 SPACE로 갱신! 하기전에 먼저 확인!
select buyer_name, buyer_charger
   from buyer
   where buyer_charger like '성%'
--거래처 담당자 성씨가 '성'이면 SPACE로 갱신!
update buyer set buyer_charger=''
   where buyer_charger like '성%'
--***********************************************************************
--      함수 (NULL 관련)
--***********************************************************************
--is null, is not null /null값인지 아닌지 비교
--isnull(c,d)/c가 null값이면 d값으로 치환!
--nullif(c,d)/c와 d를 비교하여 같으면 null을, 다르면 c값을 돌려준다.
--***********************************************************************
--해당 컬럼이 null값 비교 조회
--1. null이 존재하는 상태로 조회
select buyer_name 거래처, buyer_charger 담당자
   from buyer
--2. null을 이용 null값 비교
select buyer_name 거래처, buyer_charger 담당자
   from buyer
   where buyer_charger = null      --▷' = null ' 대신 ' is null '을 사용해야 제대로 된 구문 !

--***********************************************************************
--      함수(GROUP)
--***********************************************************************
--AVG(columm)/조회범위 내에 해당 컬럼들의 평균값
--                     /DISTINCT : 중복된 값은 제외
--                     /ALL : Default로써 모든 값을 포함(all을 쓰지 않아도 Default값으로 적용)
--                     /Column명 : NULL값은 제외
--                     /* : NULL값도 포함(COUNT함수만 사용)
--                     isnull을 사용하여 NUll값은 '0'등으로 나오게 한다.
--
select avg(distinct prod_cost),avg(all prod_cost),
   avg(prod_cost) 매입가평균
   from prod
--상품테이블의 상품분류별 매입가격 평균 값
/* (집게함수 앞에 있는 것은 group by에 포함되어있어야만 한다.
      그러나, group by에 있는 것이 집게함수나 그 앞에 포함되지 않아도 무관!) */
select prod_lgu, avg(prod_cost)'상품분류별 매입가격 평균'
   from prod
   group by prod_lgu
--상품테이블의 총 판매가격 평균값을 구하시요?
--(Alias는 상품총판매가격평균)
select prod_lgu, avg(prod_sale)'상품총판매가격평균'
   from prod
   group by prod_lgu
--상품테이블의 상품분류별 판매가격 평균값을 구하시요? (Alias는 상품분류, 상품분류별판매가평균)
select prod_name'상품분류', avg(prod_sale)'상품분류별판매가평균'
   from prod
   group by prod_name

--**************************************************************************
--      함수(GROUP)
--**************************************************************************
--COUNT(col) / 조회 범위내 해당컬럼들의 자료수
--COUNT(*) / 선택된 자료의 수
--      NULL값까지 포함해서 갯수를 헤아린다.
--
select count(distinct prod_cost), count(all prod_cost),
   count(prod_cost),count(*)
  from prod
--상품테이블의 자료수
select count(*) result1, count(prod_lgu) result2
  from prod
--상품테이블의 상품분류별 자료수
select prod_lgu, count(*)'상품분류별 자료의 수'
  from prod
group by prod_lgu
--1.거래처테이블의 담당자를 컬럼으로 하여 count집게
--  (Alias는 "자료수(Distinct)", 자료수, 자료수(*))
--방식1
select count(distinct buyer_charger) "자료수(Distinct)",
   count(buyer_charger) "자료수",
   count(*) "자료수(*)"
  from buyer
--방식2
select count(distinct buyer_charger) "자료수(Distinct)",
   count(all buyer_charger) "자료수",
   count(*) "자료수(*)"
  from buyer
--2. 회원테이블의 취미종류를 count집계
--   (Alias는 취미종류)
select distinct mem_like "취미종류" from member
--3. 회원테이블의 취미별 COUNT집계 (Alias는 취미,자료수,자료수(*))
select mem_like 취미, count(mem_like) "자료수", count(*) "자료수(*)"
   from member group by mem_like
--4. 회원테이블의 직업종류수를 COUNT집계(Alias는 직업종류수)
select count(distinct mem_job) "직업종류수"
   from member
--5. 회원테이블의 직업종류를 집계
select distinct mem_job "직업종류" from member
--6. 회원테이블의 직업별 카운트 집계
select mem_job "직업", count(mem_job) "자료수", count(*) "자료수(*)"
   from member group by mem_job
--7. 장바구니 테이블의 회원별 카운트 집계
select cart_member, count(*), count(cart_member), count(distinct cart_member)
   from cart
  group by cart_member

--**************************************************************************
--      함수(GROUP)
--**************************************************************************
--MAX(col)     / 조회범위 내 해당컬럼들 중 최대값
--MIN(col)     /조회범위 내 해당컬럼들 중 최소값
-- 어차피 중복을 배제하나 않하나 최대값과 최소값은 같으므로 distinct를 쓰나 마나이다!
select max(distinct prod_cost), max(prod_cost),
   min(distinct prod_cost), min(prod_cost)
  from prod
--상품중 최고판매가겨과 최저판매가격
select max(prod_sale) 최고판매가,
   min(prod_sale) 최저판매가
  from prod
--상품중 거래처별 최고매입가격과 최저매입가격
select prod_buyer 거래처,
   max(prod_cost) 최고매입가,
   min(prod_cost) 최저매입가
  from prod
  group by prod_buyer
--문제)
--1. 장바구니 테이블의 회원별 최대구매수량을 검색
--   (Alias는 회워ID, 최대수량, 최소수량)
select cart_member 회원ID,
   max(cart_qty) 최대수량,
   min(cart_qty) 최소수량
  from cart
group by cart_member
--2. 오늘이 2002년도 5월 15일이라 가정하고 장바구니 테이블에 발생될 추가주문번호를 검색?
--   (Alais는 최고치주문번호, 추가주문번호)
-- 우선, cart의 내용을 확인한다.
select * from cart
-- 그다음은, 2002년도 5월 15일을 출력하기 위해...
select * from cart where cart_no like '20020515%'
--2002년도 5월 15일의 최고치주문번호를 검색
select max(cart_no) from cart where cart_no like '20020515%'
--*************************** 중 요 *********************************
--2002년도 5월 15일의 최고치주문번호와 추가주문번호를 모두 검색!
--정수형의 범위가 정해져 있기 때문에 convert함수를 썼다.
select max(cart_no) 최고치주문번호, convert(decimal(13),max(cart_no)) + 1 추가주문번호
  from cart where cart_no like '20020515%'

--**************************************************************************
--      함수(GROUP)
--**************************************************************************
--SUM(column) / 조회범위 내 해당컬럼들의 합계
--상품테이블의 매입가의 총합계 값
select sum(distinct prod_cost), sum(prod_cost)
   from prod
--상품테이블의 판매가의 총합계 값
select sum(prod_sale)'상품 판매가 총합계'
   from prod
--상품테이블의 상품분류별 판매가 합계값
select prod_lgu, sum(prod_sale)'상품 분류별 판매가 합계'
   from prod
   group by prod_lgu
--상품입고테이블의 상품별 입고수량의 합계값
select buy_prod 상품, sum(buy_qty)'입고수량합계'
   from buyprod
   group by buy_prod
--문제)
--1. 장바구니테이블의 상품분류별 판매수량의 합계값
--   (Alias는 상품, 판매수량합계)
-- 장바구니테이블 모두 출력
select * from cart
--판매수량합계
select left(cart_prod,4) 상품, sum(cart_qty) 판매수량합계
   from cart
  group by left(cart_prod,4)
--2. 회원테이블의 회원전체의 마일리지 평균, 마일리지 합계, 최고마일리지, 최소마일리지, 인원수를 검색
--   (Alias는 마일리지평균, 마일리지합계, 최고마일리지, 최소마일리지, 인원수)
select avg(mem_mileage) 마일리지평균, sum(mem_mileage) 마일리지합계,
   max(mem_mileage) 최고마일리지, min(mem_mileage) 최소마일리지,
   count(*) 인원수 from member

--**************************************************************************
--      함수(소 GROUP 분리)
--**************************************************************************
--소 GROUP / 집계함수를 제외한 select절에 기술된 column명들은 모두 group by절에 기술!
--       / group by절에 기술된 column명들은 select절에 기술되지 않아도 무방
--       / 하지만 결과를 파악하기 위해서는 select절에 기술해주는 것이 타당
--       / group by절을 기술하면 group by 절에 기술된 column값으로 1개의 table이 소group으로 나눠진다.
             결과는 column값으로 sort되어서 출력된다.
--상품테이블에서 거래처, 상품분류별로 최고판매가, 최소판매가, 자료수를 검색
select * from prod
--
select prod_buyer 거래처, prod_lgu 상품분류,
   max(prod_sale) 최고판매가,
   min(prod_sale) 최소판매가,
   count(prod_sale) 자료수
   from prod
  group by prod_buyer, prod_lgu
--
--1. 장바구니테이블에서 회원, 상품분류별로 구매수량평균, 구매수량합계, 자료수를 검색?
--   (Alias는 회원ID,상품분류,구매수량평균,구매수량합계,자료수)
--   (회원ID, 상품분류 순으로 sort하시요)
select * from cart
--
select cart_member 회원ID, left(cart_prod,4) 상품분류,
   avg(cart_qty) 구매수량평균,
   sum(cart_qty) 구매수량합계, count(cart_qty) 자료수
  from cart
   group by cart_member, left(cart_prod,4)
   order by cart_member, left(cart_prod,4)
--
--2. 회원테이블에서 지역(주소1의 2자리),생일년도별로 마일리지평균,마일리지합계, 최고마일리지,최소마일리지,자료수를 검색?
--   (Alias는 지역,생일연도,마일리지평균,마일리지합계,최고마일리지,최소마일리지,자료수)
select * from member
--
select left(mem_add1,2) 지역, year(mem_bir) 생일연도,
   avg(mem_mileage) 평균, sum(mem_mileage) 합계,
   max(mem_mileage) 최대, min(mem_mileage) 최소,
   count(*) 자료수
  from member
 group by left(mem_add1,2), year(mem_bir)

--*************************************************************************
--      함수(system)
--*************************************************************************
--ISDATE(c) / 타당한 날짜 포맷인지 확인 : 날짜면 1, 아니면 0
--ISNUMERIC(n) / 타당한 숫자포맷인지 확인 : 숫자면 1, 아니면 0
--CASE WHEN / 연속적인 조건문(자주활용되는 함수)
--                     CASE WHEN ~ THEN ~ ELSE ~ END
--
select isdate('20000101') result1,
   isdate('12345678') result2,
   isdate('abc') result3
--
select isnumeric(1234.5678) result1,
   isnumeric('1234.5678') result2,
   isnumeric('ABCDEFG') result3
--
select case when'나'='나' then'맞다'
   else'아니다' end result
--
select case'나'when'철호'then'아니다'
   when'너' then'아니다'
   when'나' then'맞다'
   else'모르겠다' end result
--
select prod_name 상품, prod_lgu 분류,
   상품분류=
   case when prod_lgu = 'p101' then '컴퓨터제품'
      when prod_lgu = 'p102' then '전자제품'
      when prod_lgu = 'p201' then '여성케주얼'
      when prod_lgu = 'p202' then '남성케주얼'
      when prod_lgu = 'p301' then '피혁잡화'
      when prod_lgu = 'p302' then '화장품'
      when prod_lgu = 'p401' then '음반/CD'
      when prod_lgu = 'p402' then '도서'
      when prod_lgu = 'p403' then '문구류'
    else '미등록분류'
   end
  from prod

--10만원 초과 상품판매가 가격대를 검색
select * from prod
--
select prod_name 상품, prod_price 판매가,
    case
      when(100000-prod_price)>0 then'10만원미만'
      when(200000-prod_price)>0 then'10만원대'
      when(300000-prod_price)>0 then'20만원대'
      when(400000-prod_price)>0 then'30만원대'
      when(500000-prod_price)>0 then'40만원대'
      when(600000-prod_price)>0 then'50만원대'
      when(700000-prod_price)>0 then'60만원대'
      when(800000-prod_price)>0 then'70만원대'
      when(900000-prod_price)>0 then'80만원대'
      when(1000000-prod_price)>0 then'90만원대'
    else'100만원이상'
   end'가격대'
  from prod
 where prod_price>100000
--문제)
--회원정보테이블의 주민등록 뒷자리(7자리 중 첫째자리)에서 성별 구분을 검색
--  (Alias는 회원명, 주민등록번호(주민1-주민),성별)
select * from member
--
select (mem_regno2)
  from member
--첫번 째 방법
select mem_name 회원명, mem_regno1+'-'+mem_regno2 주민등록번호,
  case when left(mem_regno2,1)=1 then'남자'
    when left(mem_regno2,1)=2 then'여자'
    else'알수없는성별'
  end'성별'
 from member
--또 다른 방법
select mem_name 회원명, mem_regno1+'-'+mem_regno2 주민등록번호,
  case left(mem_regno2,1) when '1' then '남자'
    when '2' then '여자'
   else'알수없는성별'
  end'성별'
 from member