SQL 심화 8~10

Scalar Function

UCASE

• 영문을 대문자로

15$가 넘는 메뉴를 대문자로 조회

select ucase(menu) from sandwich where price>15;

LCASE

• 소문자로

5$가 안되는 메뉴 소문자로 조회

select lcase(menu) from sandwich where price<5;

MID

• 문자열 부분 반환

select mid(string,start_position,length);

- string : 원본 문자열
- start : 문자열 반환 시작 위치 (첫글자를 1, 마지막 글자는 -1)
- length : 반환할 문자열 길이

1번위치에서 4글자 조회

select mid('this is mid test',1,4);

11위 카페 이름 중 두번째 단어만 조회 - 6번 위치에서 4글자

select mid(cafe,6,4) from sandwich where rangkin=11;

Length

• 문자열의 길이 반환 (문자가 없는 경우 0, 공백은 1, NULL은 NULL로 리턴)

select length('this is len test');

Round

• 지정한 자리에서 숫자 반올림

select round(number,decimals_place);

- number : 반올림 대상
- decimals : 반올림 할 소수점 위치 (option) 위치 지정 안할 시 소수점 (0)에서 반올림
				1단위 위치는 -1

sandwich 테이블에서 소수점 자리는 반올림 해서 1달러 단위까지만 표시

select rand,price,round(price) from sandwich order by rand desc limit 3;

Now

• 현재 날짜 및 시간 함수

select now();

Format

• 천단위로 콤마를 반환

select format(number, decimal_place);

- number : 숫자
- decimals : 표시할 소수점 위치

select format(12345.6789,0);

oil_price 테이블에서 가격이 백원단위에서 반올림 했을 때 2000원 이상인 경우 천원 단위 콤마 넣어서 조회

select format(가격,0) from oil_price where round(가격,-3) >=2000;

SQL Subquery

About Subquery

• 하나의 SQL 문안에 포함되어 있는 또 다른 SQL 문을 말한다.
• 메인 쿼리가 서브쿼리를 포함하는 종속 관계
  - 서브 쿼리는 메인쿼리의 칼럼 사용 가능
  • 메인 쿼리는 서브쿼리의 칼럼 사용 불가
• 서브 쿼리는 괄호로 묶어서 사용
• 단일 행 혹은 복수 행 비교 연산자와 함께 사용 가능
• 서브 쿼리에서는 oreder by 사용 X

1. 스칼라 서브쿼리 : select 절에 사용
2. 인라인 뷰 : from 절에 사용
3. 중첩 서브 쿼리 : where 절에 사용

스칼라 서브 쿼리

• select 절에 사용하는 서브쿼리, 결과는 하나의 컬럼이어야 한다.

select column1,(select column2 from table2 where condition)
from table1
where condition;

서울 은평경찰서의 강도 검거 건수와 서울시 경찰서 전체의 평균 강도 검거 건수 조회

select case_number,(select avg(case_number) from crime_status where crime_stype like '강도' and status_type like '검거') avg
from crime_status
where police_station like '은평' and crime_stype like '강도' and status_type like '검거';

인라인 뷰

• from 절에 사용하는 서브쿼리, 메인 쿼리에서는 인라인 뷰에서 조회한 column 만 사용가능하다

select a.column,b.column
from table1 a, (select column1,column2 from table2) b
where condition;

경찰서 별로 가장 많이 발생한 범죄 건수와 범죄 유형을 조회

select c.police_station, c.crime_stype,c.case_number
from crime_status c, (select police_station,max(case_number) count from crime_status where status_type like '발생' 
group by police_station) m
where c.police_station = m.police_station
and c.case_number = m.count;

중첩 서브 쿼리

• where 절에 사용하는 서브 쿼리
  - single row : 하나의 열 검색
  • multiple row : 하나 이상의 열을 검색
  • multiple column : 하나 이상의 행을 검색

1. single row 서브 쿼리 : 비교연산자(>,<,=)와 사용되는 경우 서브 쿼리의 검색 결과는 한개의 결과 값을 가져야한다.

select column_names
from table_name
where column_name=(select column_name from table_name where conditon)
order by column_name;

celeb 에서 snl_show 에서 host와 이름이 같은 값 찾기

select name from celeb where name=(select host from snl_show);

select name from celeb where name=(select host from snl_show where id=1);

2-1. multiple row + IN : 서브 쿼리 결과 중에 포함 될 때

select col
from table 
where col IN (select col from table where condition) order by col;

snl에 출연한 영화배우 조회

select host
from snl_show
where host IN (select name from celeb where JOB_TITLE like '%영화배우%');

2-2. multiple row + exists : 서브 쿼리 결과에 값이 있으면 반환

select col from table
where exists (select col from table where condition)
order by col;

범죄 검거 혹은 발생 건수가 2000건 보다 큰 경찰서 조회

select name
from police_station p
where exists (select police_station from crime_status c where p.name=c.reference and case_number>2000);

2-3 multiple row + Any : 서브쿼리 결과 중 최소한 하나라도 만족하면

select col from table
where col = any (select col from table where condition) order by col;

snl에 출연한 적이 있는 연예인 이름 조회

select name from celeb
where name=any(select host from snl_show);

2-4 multiple row + ALL : 서브 쿼리 결과를 모두 만족하면 (비교연산자 사용)

select col from table
where col=all(select col from table where condition) order by col;

snl에 출연한 적이 있는 연예인 이름 조회

select name from celeb
where name=all(select host from snl_show where id=1);

3. 연관 서브 쿼리 : 서브쿼리 내에 메인쿼리 컬럼이 같이 사용되는 경우

select col
from table a
where (a.column1,a.column2,..) IN (select b.column1,b.column2,... from table b where a.column_name = b.column_name) order by col;

강동원과 성별, 소속사가 같은 연예인의 이름,성별,소속사를 조회

select name,sex,agency
from celeb
where (sex,agency) IN (select sex,agency from celeb where name='강동원');

문제

1. oil_price 테이블에서 셀프 주유의 평균 가격과 SK에너지의 가장 비싼 가격을 Scalar subquery 사용

select max(가격),(select avg(가격) from oil_price where 셀프='Y')
from oil_price
where 상표='SK에너지'

2. 상표별로 가장 비싼 가격과 상호를 INLINE VIEW 사용

select o.상호,o.상표,s.max_price from oil_price o, (select 상표,max(가격) max price from oil_price group by 상표) s where o.상표=s.상표 and o.가격=s.max_price;

3. 평균가격보다 높은 주유소 상호와 가격을 Nested Subquery 사용

select 상호,가격
from oil_price
where 가격> (select avg(가격) from oil_price);

4. 3번 주유소에서 주유한 연예인의 이름과 주유소, 주유일을 Nested 사용

select 이름,주유소,주유일 from refueling where 주유소 IN (select 상호 from oil_price where 가격>(select avg(가격) from oil_price));

5. 10만원 이상 주유한 연예인 이름,상호,상표,주유 금액,가격

select r.이름,o.상호,o.상표,r.금액,o.가격
from oil_price o, (select 이름,주유소,금액 from refueling where 금액>=100000) r
where o.상호=r.주유소;