ms-SQL 열 번째 수업

select replace('hello', 'el', 'xxxxxxx'),
 stuff('hello', 2, 3, 'qqqqqqqq'),
 str(2000) +'원',
 len('hello'),
 charindex('z', 'abczxc'),
 replicate('#', 5)
 
select datename(wk, getdate())
select dateadd(dd, 100, getdate())
select datediff(dy, '1991-12-30', getdate())

이너뷰(inner view) from 절에 쓰인 select문

select * from (select 고객번호, 마일리지,
 case when 마일리지 >= 10000 then '우수고객'
   else '일반고객' end 고객구분
from 고객) 고객 마일리지
where 고객구분 = '우수고객'

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

1.

select 제품번호, 제품명, 단가, 단가*1.1 인상단가
from 제품

2.

select 제품명, sum(주문수량) 주문수량합, sum(주문세부.단가*주문수량) 주문금액합, sum(재고) 재고
from 제품, 주문세부
where 제품.제품번호 = 주문세부.제품번호
group by 제품명

3.

select 고객번호, charindex('ar', 고객번호)
from 고객

4.

select 제품명, charindex('아', 제품명)
from 제품

5.

select month(주문일) 월, 제품명, sum(주문수량)
from 주문, 주문세부, 제품
where
주문.주문번호 = 주문세부.주문번호 and
주문세부.제품번호 = 제품.제품번호 and
datename(qq, 주문일) = 3
group by month(주문일), 제품명

6. [주문]요청일보다 발송이 늦게 된 주문에 대한 정보를 보여라
select * from 주문
where 발송일 > 요청일

7. 주문일과 발송일 사이가 30일 이상이 주문내역을 보이세요.
select *, datediff(dd, 발송일, 요청일) 늦어진일수
from 주문
where datediff(dd, 발송일, 요청일) < 0

8. 주문한지 일주일 이내에 발송이 이루어진 주문내역을 보이세요.
select * from 주문
where datediff(dd, 주문일, 발송일) > 30

10.
select top 1 year(주문일) 년도, month(주문일) 월, sum(주문수량)
from 주문, 주문세부
where
주문.주문번호 = 주문세부.주문번호
group by year(주문일), month(주문일)
order by 3 desc

11.

select str(month(입사일))+'월', count(*)
from 사원
group by month(입사일)
with rollup

12

select replace(right(전화번호, len(전화번호)-1), ')', '-') from 고객

보너스 1

사원이름, 생일, 만나이, 연령대를 보이는 질의문을 만드세요

select 이름, 생일, datediff(yy, 생일, getdate()) 나이,

case when datediff(yy, 생일, getdate()) % 10 between 0 and 3
then  str(datediff(yy, 생일, getdate())/10*10)+'대 초반'

when datediff(yy, 생일, getdate()) % 10 between 3 and 7
then str(datediff(yy, 생일, getdate())/10*10)+'대 중반'

when datediff(yy, 생일, getdate()) % 10 between 7 and 9
then str(datediff(yy, 생일, getdate())/10*10)+'대 후반'
else '' end 연령대

from 사원

보너스 2

select '(' + stuff('02-145-2324', charindex('-', '02-145-2324'), 1, ')')

sp_helptext sp_help

alter proc proc_ldh
 @고객번호 varchar(10) -- 매개변수
 as
begin
 declare @회사명 varchar(100) -- 변수선언
 declare @마일리지 int
 
 select @회사명= 고객회사명, @마일리지 = 마일리지
  from 고객
 where 고객번호 =@고객번호
 
 if @회사명 is not null 
  print @고객번호 + '님의 회사는 ' + @회사명 + '입니다. 보유 마일리지는 ' + str(@마일리지) + '입니다.'
 else
  print '입력한 고객번호가 틀렸습니다.'
end
 
proc_ldh 'ALFKI'
proc_ldh 'AAAAA'

생산지를 입력받으면
  생산지별로 재고, 단가를 보이세요
 
  제품들에 대해서 재고가 5개 이하 남은 수량을 보이세요
 
  '재고가 부족한 제품은 현재 x개 입니다' 라고 출력하는 프로시저 작성
 
  재고가 5개 이하인 제품이 없을 경우에는
 '재고가 부족한 제품은 현재는 없습니다'
 
alter proc proc_ldh
 @생산지 varchar(10) -- 매개변수
 as
begin
 declare @제품명 varchar(100) -- 변수선언
 declare @개수 int
 
 select @개수=count(*)
 from 제품
 where 생산지 = @생산지
 
 select 제품명, 제품.재고, 제품.단가
 from 제품
 where 생산지 = @생산지 and 재고<=5
 
 if @개수 is not null 
  print  '재고가 부족한 제품은 현재' + str(@개수) + '입니다.'
 else
  print '재고가 부족한 제품은 현재 없습니다.'
end

select * from 제품
proc_ldh '한국'

2. 생일달을 매개변수로 입력받으면
해당월에 생일을 맞은 사원의 명단을 보여주고
x월에 생일을 맞은 사원은 x명 입니다. 라고 출력하는 프로시저 작성

alter proc proc_ldh
 @생일달 int
 as
begin
 declare @생일자수 int
 
 select 사원번호, 이름, 생일
 from 사원
 where month(생일) = @생일달
 
 select @생일자수=count(*)
 from 사원
 where month(생일)=@생일달
 
 if @생일자수 = 0
  print  '해당월에는 생일자가 없습니다.'
 else
  print @생일달 + '월에 생일자는 ' + str(@생일자수) +'입니다.'
end