ms-SQL 아홉 번째 수업

주문 시작일과 주문 끝일을 매개변수로 입력받아서
 - 총 주문횟수
 - 월별 주문횟수
 - 제품명별로 주문수량합과 주문금액합을
  주문금액이 많은 것 부터 상위 10개
 -고객정보(담당자명, 고객회사명, 전화번호)
 

alter proc proc_ldh
@from datetime, @to datetime
 as
begin
 select count(*) 주문횟수 from 주문
 where 주문일 between @from and @to

 select month(주문일) 월, count(*) 주문횟수
 from 고객, 주문
 where 주문일 between @from and @to
 group by month(주문일)
 order by 1 asc

 select top 10 제품명, sum(주문수량) 수량합, sum(주문세부.단가*주문수량) 금액합
 from 고객, 주문, 주문세부, 제품
 where 고객.고객번호=주문.고객번호
 and 주문.주문번호=주문세부.주문번호
 and 제품.제품번호=주문세부.제품번호
 and 주문.주문일 between @from and @to
 group by 제품명
 order by 3 desc
 
 select distinct 고객.담당자명, 고객.고객회사명, 고객.전화번호
 from 고객, 주문
 where 고객.고객번호=주문.고객번호
 and 주문.주문일 between @from and @to
end

proc_ldh '2007-01-01' ,'2007-02-28'

-----------------------------------------------------------------------

sp_helptext proc_ldh
sp_helptext sp_helptext

프로시저 : do actions
함수 : do actions and return a value

문자열함수
숫자형함수
날짜형함수
시스템함수

select left('안녕하세요', 2), right('안녕하세요', 3),
  substring('안녕 하세요', 3, 1), reverse('hello'),
  replace('안녕하세요', '안녕', '안뇽'),
  stuff('안녕하세요', 2, 1,'호호호'),
  len(ltrim('   aaaa    ')),
  len(rtrim('   aaaa    ')),
  replicate('*', 5)
  

select * from 고객

1. 백화점 고객들에 대해 고객번호 5번째자를 모두 Z로 바꿔보이세요
select replace(고객번호, substring(고객번호, 5, 1), 'Z') from 고객
select 고객번호 , stuff(고객번호, 5, 1, 'Z') from 고객

2. 주소중 구를 gu로 동을 dong으로 바꿔보이세요
select 주소, replace(replace(주소, '구', 'GU'), '동', 'DONG')  from 고객

3. 담당자명 중 한 자만 보이고 나머지 글자 대신에 *로 보여주세요
select 담당자명,
 stuff(담당자명, 2, len(담당자명)-1,
   replicate('*', len(담당자명)-1))+'님'
from 고객

where len(담당자명) in (2,4)

4. (xxx)xxx-xxxx -> xxx-xxx-xxxx
select replace(right(전화번호, len(전화번호)-1), ')', '-') from 고객

숫자형함수 연습
select floor(35.789), ceiling(35.189),
round(245.1234, 0), round(245.1234, 2),
round(245.1234, 1)

select 주문번호, 제품번호, 단가, 주문수량,
str(할인율*100) + '%' 할인율,
주문수량*단가 주문금액,
주문수량*단가*할인율 할인금액, 주문수량*단가*(1-할인율) 실주문금액
from 주문세부

1. 제품번호별로 주문금액합을 보이세요.
 (10000원단위까지 유효 숫자로 표현)
 
select 제품번호, round(sum(주문수량*단가), -4) from 주문세부
group by 제품번호

1.1 100000원 단위까지 보이되 그 이하 금액을 버리기 처리

2. 주문번호별로 주문금액합, 할인액합, 실주문금액합
 (100원 단위까지 유효 숫자)
 
 select 주문번호, sum(주문수량*단가) 주문금액합,
 sum(주문수량*단가*할인) 할인액합,
 round(sum(주문수량*단가*(1-할인율)), -2) 실주문금액합
 from 주문세부
 group by 주문번호
 
 
-----------------------------------------------------------------------

*날짜형 함수 연습

select getdate(), year(getdate()), month(getdate()), day(getdate()),
datename(qq, getdate()),
dateadd(ss, 100000, getdate()),
dateadd(hh, -100, getdate()),
datediff(dd, '1900-01-01', getdate())

분기별로 주문횟수를 보이세요

select datename(qq, 주문일) 분기, count(*) 주문횟수
from 주문
group by datename(qq, 주문일)
order by 1 asc

분기별로 주문금액합을 보이세요.

select datename(qq, 주문일) 분기, sum(주문수량*단가) 주문금액합
from 주문, 주문세부
where 주문.주문번호 = 주문세부.주문번호
group by datename(qq, 주문일)
order by 1 asc

select datediff(dd, '1991-11-25', getdate()) '몇일살았을까',
datediff(hh, '1991-11-25', getdate()) '몇시간살았을까',
str(datediff(dd, getdate(), dateadd(yy, 100, '1991-11-25')))+'일' 남은날짜

이름, 입사일, 입사일수, 입사한지 1000일째 되는 날을 보여주세요

select 이름, 입사일, datediff(dd, 입사일, getdate()) '입사일 수',
dateadd(dd, 1000, 입사일) '1000일 후'
from 사원