ms-SQL 네 번째 수업

복습

 

create database 서일

use 서일

 

create table 의견
(
 번호 int identity,
 제목 varchar(10),
 내용 char(20),
 작성자 varchar(10),
 작성일 datetime default getdate()
)

1.
alter table 의견 add 추천 int default (0)

 

2.
alter table 의견 alter column 제목 varchar(20)

 

3.
alter table 의견 alter column 내용 varchar(50)

 

4.
alter table 의견 add check(제목 like ('%!'))

 

5.
alter table 의견 add constraint uq_의견_작성자 unique(작성자)

 

6.
sp_help 의견
alter table 의견 drop constraint CK__의견__제목__00551192

 

7.
alter table 의견 drop constraint uq_의견_작성자

 

8.
sp_help 의견
alter table 의견 drop constraint DF__의견__추천__023D5A04
alter table 의견 drop column 추천

 

 

---

 

sp_help 고객

select * from 고객

 

1. 고객번호 두번째 글자가 N이거나 S이면서 4번째 글자가
 T나 Q인 고객의 모든 정보 보이기

select * from 고객 where 고객번호 like '_[NS]_[TQ]%'

 

2. 지역에 값이 안들어있는 고객

select * from 고객 where 지역 is NULL

 

3. 고객회사명as 회사명, 담당자명, 도시, 마일리지, 10%인상된 마일리지를 보이되
 도시 순으로 보이기, 같은 도시일 경우 이름 순으로 보이기

select 고객회사명, 담당자명, 도시, 마일리지,
마일리지*1.1 as [인상된 마일리지]

from 고객
where 고객회사명 like '%백화점%'
--where 회사명 like '%백화점%' --X
--order by 도시, 담당자명 --O
--order by 2, 3 --O
order by 도시, 담당자명

 

4. 도시가 광역시, 김해시가 아닌 고객들을 보여라
select * from 고객 where 도시 not in ('광역시', '김해시')

 

5. 마일리지가 높은 고객부터 상위 5% 만큼 고객을 보여라

select top 5 percent 담당자명, 마일리지 from 고객 order by 마일리지 desc

 

6. 마일리지가 낮은 고객부터 7명을 뽑아라

select top(7)담당자명, 마일리지 from 고객 order by 마일리지 asc

 

7. 우리 고객들은 어느 도시에 살고 있는지 도시명을 한번씩만 보여라

select distinct 도시 from 고객

 

8. 마일리지가 1000보다 작거나 50000보다 큰 고객 찾기

select * from 고객 where 마일리지 not between 1000 and 50000

 

 

---

 

select 그룹으로 묶을 필드명, 그룹함수
from 테이블명
where 그룹을 묶기전의 조건
group by 그룹으로 묶을 필드명
having 그룹을 묶고난 후의 조건
order by 정렬

 

1. 도시별로 인원수와 그들의 평균 마일리지

select 도시, count(*) 인원수, avg(마일리지) from 고객 group by 도시

 

2. 지역에 값이 안들어 있는 고객에 대해
 도시별로 인원 수와 그들의 평균 마일리지
select 도시, count(*) 인원수, avg(마일리지) from 고객
where 지역 is null
group by 도시

 

3. 광역시(도시) 고객들에 대해
 도시별로 인원수와 그들의 평균 마일리지
select 도시, count(*) 인원수, avg(마일리지) from 고객
where 도시 like '%광역시'
group by 도시

 

4. 도시별로 인원수와 그들의 평균 마일리지를 구하되
 평균 마일리지가 6000이상인 것 들만 보이기
select 도시, count(*) 인원수, avg(마일리지) from 고객
where 도시 like '%광역시'
group by 도시
having avg(마일리지)>=6000

 

 

---

 

select 제품명, 제품번호, 단가, 단가*재고 as 재고액
from 제품
where 단가 >= 10000

select 제품명, 단가, 재고
from 제품
where 제품명 like '%셰이크%' or 제품명 like '%아이스크림%'
order by 재고 desc
 

select 제품명, 재고
from 제품
where 포장단위 like '%box%' and 재고 >= 30

select top 3 percent * from 제품
order by 단가 desc

select 담당자명, 고객번호
from 고객
where 고객번호 like '[AC]_[FZK]%'

sp_help 사원
select year(입사일) 입사년도, count(*) '사원 수' from 사원
group by year(입사일)
order by '사원 수' desc

 

select top 5 도시, 지역, count(*) '고객 수' from 고객
group by 도시, 지역
order by 3

 

sp_help 사원
select 직위, count(*) '사원 수'
from 사원
where 직위 like '%영업%'
group by 직위
having count(*) <= 3

 

select year(주문일) 주문년도, month(주문일) 주문월, count(*) from 주문
group by year(주문일), month(주문일)