select의 요소

use tempdb

--태이블생성
create table dbo.customers
(
Num int identity(1,1) primary key,
Name varchar(25) not null,
Age tinyint null,
Address varchar(100)
)
go

--샘플 데이터 입력
insert customers values('태연',21,'서울')
insert customers values('설리',19,'서울')
insert customers values('수지',19,'인천')
insert customers values('나은',18,'대전')
insert customers values('리지',20,'서울')
insert customers values('지효',31,'부산')
insert customers values('보라',31,'부산')
insert customers values('보미',20,'광주')
go


update customers
set Address= '부산'
where  name='태연'



select * from customers
go
--1.중복제거(distinct)
select distinct address from customers
--2.집계함수 그룹화: 같은 지역 고객의 나이의 평균
select address, AVG(age) from customers
group by address

--3.조건이 있는 집계함수 그룹화: 같은지역 아이돌중 나이가 20이상인
-- 아이돌나이의 평균
select address,AVG(age) from customers
where age >= 20
group by address

select address,AVG(age) from customers
where age >= 20
group by all address

--4.having:집계함수에 대한 조건처리
--같은 지역 아이돌의 나이의 평균이 20 이상인 데이터만 출력

select address,AVG(age) from customers
group by address
having AVG(age) >= 30

--5.rollup:소계: 지역별 나이들 출력후 나의 소계(중간 합계)
select address,AVG(age) from customers
group by address with rollup

--6.cube
select address,AVG(age) from customers
group by address with cube

--7.grouping()함수
--
select address,AVG(age),GROUPING(address) as 그룹화여부
from customers
group by address with cube

--8.compute :출력 결과에 대한 집계
select address, age
from customers
compute sum(age),avg(age)

--9.compute by :출력 결과에 대한 집계에 대한 정렬
select address, age
from customers
order by address
compute sum(age),avg(age) by address

--10.case:문장대체
select name, age,
address = case address when '서울' then 'seoul'
when '부산' then 'busan'
else '다른지역'
end
from customers