MS SQL 문제 feat.재탕삼탕

=========================================================================

select * from 고객

select * from 마일리지등급

select * from 부서

select * from 사원

select * from 제품

select * from 주문

select * from 주문세부

-- 1. 제품테이블에는 각 제품에 대하여 단가가 들어있습니다. 제품번호, 제품명, 현재단가, 10% 인상된 단가를 보이는 질의문을 작성하세요.

select 제품번호, 제품명, 단가, 단가*1.1 "10%인상된 단가"

from 제품

-- 2. 제품명별 주문수량합, 주문금액합, 재고를 보이세요. 단, 결과는 다음과 같은 형식으로 보입니다.

-- 예) 참치 샌드위치의 주문수량합계는 100 개 , 금액합계는 500000원이며, 재고는 30개 남아있습니다.

select 제품명+ '의 주문수량합계는 ' + ltrim(rtrim(str(SUM(주문수량)))) +'개, 금액합계는 '+ltrim(rtrim(str(SUM(제품.단가*주문수량))))+'원이며, 재고는 '+ltrim(rtrim(str(SUM(제품.재고))))+'개 남아있습니다.'

from 제품, 주문세부

where 제품.제품번호 = 주문세부.제품번호

group by 제품명

-- 3.고객번호중에서 ‘ar’이 들어가는 고객에 대해 ‘ar’의 시작 위치값을 볼 수 있도록 질의문을 작성하세요. 

select 고객번호, charindex('ar',고객번호) '위치'

from 고객

where 고객번호 like '%ar%'

-- 4. 제품명 중에서 ‘아’가 들어간 제품에 대해 아의 위치값을 볼 수 있도록 질의를 작성하세요.

select 제품명, charindex('아', 제품명) '위치'

from 제품

where 제품명 like '%아%'

-- 5. 김씨 성을 가진 사원에 대해 김을 kim으로 바꿔 보이세요.

select 이름, replace(이름,'김','kim') 

from 사원

where 이름 like '%김%'

-- 6.  사원테이블의 직위 중에 들어 있는 값 중 xxx 사원을 xxx staff로 바꾸어 보이세요.

select 이름, 직위, replace(직위, '사원', 'staff') 

from 사원

-- 7. 도착예정일보다  늦게 택배 발송이 이루어진 주문에 대해 주문 내역을 보이세요.

select * from 주문

where datediff(DAY,도착예정일,택배발송일) > 0

-- 8.  주문일과 도착예정일 사이가 30일 이상 차이가 나는 주문 내역을 보이세요.

select *, datediff(DAY, 주문일, 도착예정일) from 주문 

where datediff(DAY, 주문일, 도착예정일) > 30

-- 9.  3사분기에 주문된 제품들에 대하여 월별 , 제품명별 주문수량합을 보이세요.월별, 제품명별 소계도 보이고 전체 총계도 함께 보이세요.

select month(주문일) '월별', 제품명, sum(주문수량) '주문수량합'

from 주문, 주문세부, 제품

where datepart(QUARTER, 주문일) = 3

and (주문.주문번호 = 주문세부.주문번호)

and (제품.제품번호 = 주문세부.제품번호)

group by month(주문일), 제품명

with cube

-- 10. 몇 년도, 몇월 달에  주문수량합이 제일 많았나요?

select top 1 datepart(MONTH, 주문일) '월', datepart(YEAR,주문일) '년', sum(주문수량) '주문수량합'

from 주문, 주문세부

where 주문.주문번호 = 주문세부.주문번호

group by datepart(MONTH, 주문일) , datepart(YEAR, 주문일)

order by sum(주문수량) desc

-- 11.  입사월별 입사한 사원수를 보이세요. 전체 사원수도 함께 보이세요. 

select datepart(MONTH, 입사일) '입사월', count(*) '사원수'

from 사원

group by datepart(MONTH, 입사일)

with rollup

-- 12. 고객테이블의 집전화 컬럼에 대하여 ()대신 –로 지역번호를 구별할 수 있도록 적절한 함수를 사용하여 질의문을 만드세요.예)(02)824-0909   02-824-0909 

select 담당자명, 전화번호, replace(replace(전화번호, ')', '-'),'(', '')

from 고객

/*

*보너스 문제 

** 사원이름, 생일, 만나이, 연령대를 보이는 질의문을 만드세요.(예) 23세 이하이면 20대 초반  27세 이하면 20대 중반, 29세 이하면 20대 후반)

Hint) 나머지를 구하는 함수는 별도로 없다.

35이면 30대 :  버림함수(나이/10) * 10

35세이면 중반 : 

(나이 - 버림함수(나이/10) * 10) 이 3이하면 초반

(나이 - 버림함수(나이/10) * 10) 이 7이하면 중반

      그 나머지이면 후반 이라고 보이면 됨.

*/

select 이름, 나이, str(나이/10*10) + '대 ' + case when right(str(나이),1) <=3 then '초반' when right(str(나이),1) <=6 then '중반'

else '후반' end

from (select 이름, datediff(YEAR, 생일, getdate() ) 나이 from 사원) 결과