데이빗의 도스 게임 자료실

--**************************************************************************
--      함수(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