[zerobase_데이터취업스쿨] SQL_CH16-1~17-7

CH16-1~5: Scalar Functions(입력값을 기준으로 단일 값 반환하는 함수)

UCASE: 영문을 대문자로 변환

SELECT UCASE(STRING);

15달러 넘는 메뉴를 대문자로 조회

mysql> select ucase(menu), price 
    -> from sandwich
    -> where price > 15;
+--------------------------------------+-------+
| ucase(menu)                          | price |
+--------------------------------------+-------+
| LOBSTER ROLL                         |    16 |
| GRILLED LAUGHING BIRD SHRIMP AND FRI |    17 |
| SHAVED PRIME RIB                     |    21 |
+--------------------------------------+-------+
3 rows in set (0.01 sec)

LCASE: 영문을 소문자로 변환

SELECT LCASE(STRING) FROM TABLE;

5달러가 안되는 메뉴를 소문자조회

mysql> SELECT LCASE(MENU), PRICE    
    -> FROM sandwich
    -> WHERE PRICE < 5;
+--------------+-------+
| LCASE(MENU)  | PRICE |
+--------------+-------+
| meatball sub |   4.5 |
+--------------+-------+
1 row in set (0.01 sec)

MID 문자열 부분을 반환

SELECT MID(원본문자열, 시작문자지정, 몇글자가져올건지지정) 

11위 카페이름 중 두번째 단어만 조회

mysql> SELECT MID(CAFE, 6, 4)
    -> FROM sandwich
    -> WHERE RANKING = 11;
+-----------------+
| MID(CAFE, 6, 4) |
+-----------------+
| Cafe            |
+-----------------+
1 row in set (0.01 sec)

LENGTH 문자열 길이 반환(문자없어도 0 리턴, 공백은 1을 리턴, NULL은 NULL을 리턴)

SELECT LENGTH(STRING) FROM TABLE;

3위 까지의 주소 길이를 검색

mysql> SELECT LENGTH(ADDRESS), ADDRESS 
    -> FROM sandwich
    -> ORDER BY RANKING 
    -> LIMIT 3;
+-----------------+---------------------+
| LENGTH(ADDRESS) | ADDRESS             |
+-----------------+---------------------+
|              19 | 2109 W. Chicago Ave |
|              18 | 800 W. Randolph St  |
|              15 | 445 N. Clark St     |
+-----------------+---------------------+
3 rows in set (0.01 sec)

ROUND 지정한 자리에서 숫자를 반올림(디폴트는 소수점 첫째자리에서 반올림), (일단위는 -1, 십단위는 -2, 백단위는 -3)

SELECT ROUND(숫자, 반올림할 소수점 위치 - 1)

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

mysql> SELECT RANKING, PRICE, ROUND(PRICE)
    -> FROM sandwich
    -> ORDER BY RANKING DESC
    -> LIMIT 3;
+---------+-------+--------------+
| RANKING | PRICE | ROUND(PRICE) |
+---------+-------+--------------+
|      50 |  6.85 |            7 |
|      49 |  8.75 |            9 |
|      48 |   7.5 |            8 |
+---------+-------+--------------+
3 rows in set (0.01 sec)

NOW 현재 날짜, 시간 반환

SELECT NOW();

FORMAT() 함수는 숫자를 문자열로 포맷팅하고, 천 단위 구분자로 컴마를 넣어줌. FORMAT(FLOOR(ROUND(price, -2) / 1000), 0)는 천원 단위로 나눈 값을 문자열로 변환하면서 정수 부분에 컴마를 넣어서 포맷팅합니다. 두 번째 인자인 0은 소수점 이하 자릿수를 0으로 하여 소수점을 표시하지 않도록 지정합니다.

FORMAT(1230.5678, 1)의 경우 반환값: 1,230.6(소수점 첫째짜리까지 표기해야하니까 소수점 둘째자리에서 반올림하고 천단위 컴마 찍어줌 .. 데이터타입은 숫자에서 문자로 바뀜)

SELECT FORMAT(숫자 혹은 문자 , 표시할 소수점 위치);

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

mysql> select 상호, 가격, format(round(가격, -3), 0) from oil_price
    -> where round(가격, -3) >= 2000;
+--------------+------+----------------------------+
| 상호         | 가격 | format(round(가격, -3), 0) |
+--------------+------+----------------------------+
| 쌍문주유소   | 1509 | 2,000                      |
| 21세기주유소 | 1598 | 2,000                      |
| 살피재주유소 | 1635 | 2,000                      |
| 뉴서울(강남) | 2160 | 2,000                      |
+--------------+------+----------------------------+
4 rows in set (0.00 sec)

CH16-6: Scalar Functions 문제풀이(단일값 함수)

sandwich 테이블에서 가게이름은 대문자, 메뉴이름은 소문자로 조회

mysql> select ucase(cafe), lcase(menu)
    -> from sandwich
    -> limit 5;
+------------------------+-------------------+
| ucase(cafe)            | lcase(menu)       |
+------------------------+-------------------+
| OLD OAK TAP            | blt               |
| AU CHEVAL              | fried bologna     |
| XOCO                   | woodland mushroom |
| AL’S DELI             | roast beef        |
| PUBLICAN QUALITY MEATS | pb&l              |
+------------------------+-------------------+
5 rows in set (0.01 sec)

sandwich 테이블에서 10위 메뉴의 마지막 단어 조회하기(Fri에서 메뉴가 잘려서 i가 맞다)

mysql> select mid(menu, -1, 1) 
    -> from sandwich
    -> where ranking = 10;
+------------------+
| mid(menu, -1, 1) |
+------------------+
| i                |
+------------------+
1 row in set (0.01 sec)

sandwich 테이블에서 메뉴이름의 평균길이를 조회하기

mysql> select avg(length(menu))
    -> from sandwich;
+-------------------+
| avg(length(menu)) |
+-------------------+
|           14.1200 |
+-------------------+
1 row in set (0.01 sec)

oil_price테이블에서 가격을 십원단위에서 반올림하여 조회하기

mysql> select ID, 상호, 주소, round(가격, -2)
    -> from oil_price
    -> limit 5;
+----+---------------------------------------+----------------------------------------------+-----------------+
| ID | 상호                                  | 주소                                         | round(가격, -2) |
+----+---------------------------------------+----------------------------------------------+-----------------+
|  0 | 재건에너지 재정제2주유소 고속셀프지점 | 서울특별시 강동구  천호대로 1246 (둔촌제2동) |            1600 |
|  1 | 구천면주유소                          | 서울 강동구 구천면로 357 (암사동)            |            1600 |
|  2 | (주)소모 신월주유소                   | 서울 강동구 양재대로 1323 (성내동)           |            1600 |
|  3 | 대성석유(주)길동주유소                | 서울 강동구 천호대로 1168                    |            1600 |
|  4 | 방아다리주유소                        | 서울 강동구 동남로 811 (명일동)              |            1700 |
+----+---------------------------------------+----------------------------------------------+-----------------+
5 rows in set (0.01 sec)

oil_price 테이블에서 십원단위에서 가격 반올림하면 2000원 이상인 경우, 천단위에 콤마 넣어서 조회하기

mysql> select 상호,주소,가격,format(round(가격,-2),0)
    -> from oil_price
    -> where round(가격, -2) >= 2000 
    -> limit 10;
+----------------------------+-----------------------------------------------+------+--------------------------+
| 상호                       | 주소                                          | 가격 | format(round(가격,-2),0) |
+----------------------------+-----------------------------------------------+------+--------------------------+
| 광성주유소                 | 서울 강동구 올림픽로 673 (천호동)             | 1968 | 2,000                    |
| 대신석유㈜서원주유소       | 서울 서초구 반포대로 142 (서초동)             | 1968 | 2,000                    |
| (주)선문에너지 중앙주유소  | 서울특별시 서초구  바우뫼로 219 (양재1동)     | 2168 | 2,200                    |
| 동일주유소                 | 서울 성동구 광나루로 254 (성수동2가)          | 1967 | 2,000                    |
| 청계로주유소               | 서울 성동구 청계천로 454 (하왕십리동)         | 1995 | 2,000                    |
| (주)옥수하이웨이스테이션   | 서울 성동구 독서당로 168 (옥수동)             | 2148 | 2,100                    |
| 국회대로주유소             | 서울 영등포구 국회대로 746 (여의도동)         | 1975 | 2,000                    |
| 성원이앤에스(주)영등포지점 | 서울 영등포구 국회대로52길 9-13 (영등포동7가) | 1985 | 2,000                    |
| 버드나룻길주유소           | 서울 영등포구 버드나루로 111 (당산동)         | 1995 | 2,000                    |
| SK KH에너지 여의도주유소   | 서울 영등포구 국회대로 794 (여의도동)         | 2195 | 2,200                    |
+----------------------------+-----------------------------------------------+------+--------------------------+
10 rows in set (0.01 sec)

CH17-01: 서브쿼리

서브쿼리: 하나의 SQL문 안에 포함된 또 다른 SQL 문

• 메인쿼리가 서브쿼리를 포함하는 종속적인 관계
• 서브쿼리는 메인쿼리의 칼럼 사용 가능
• 메인쿼리는 서브쿼리의 칼럼 사용 불가
• 서브쿼리는 괄호로 묶어서 사용
• 단일 행 또는 복수 행 비교 연산자와 함께 사용 가능
• 서브쿼리 안에는 order by 사용 불가능

SELECT절의 스칼라 서브쿼리

SELECT COL1, (SELECT COL2 FROM TABLE2 WHERE COND)
FROM TABLE1
WHERE COND;

CH17-02: 스칼라, 인라인뷰 예제

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

mysql> select case_number,
    -> (select avg(case_number)
    -> from crime_status
    -> where crime_type like '강도'
    -> and status_type like '검거') avg
    -> from crime_status
    -> where police_station like '%은평%' and status_type like '%검거%' and crime_type like '강도';
+-------------+--------+
| case_number | avg    |
+-------------+--------+
|           1 | 4.1935 |
+-------------+--------+
1 row in set (0.01 sec)

인라인뷰(FROM 절의 서브쿼리), 메인쿼리에서는 인라인 뷰에서 조회한 테이블의 칼럼 쓸 때는 인라인 뷰에서 조회한 칼럼만 사용가능

SELECT A.COL, B.COL
FROM TABLE1 A, (SELECT COL1, COL2 FROM TABLE2) B
WHERE COND;

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

mysql> select c.police_station, c.crime_type, c.case_number
    -> from crime_status c,
    -> (select police_station, max(case_number) as cnt
    -> from crime_status
    -> where status_type like '발생'
    -> group by police_station) as m
    -> where c.police_station = m.police_station
    -> and c.case_number = m.cnt;
+----------------+------------+-------------+
| police_station | crime_type | case_number |
+----------------+------------+-------------+
| 중부           | 폭력       |         997 |
| 종로           | 폭력       |         964 |
| 남대문         | 절도       |         699 |
| 서대문         | 폭력       |        1292 |
| 혜화           | 폭력       |         747 |
| 용산           | 폭력       |        1617 |
| 성북           | 폭력       |         672 |
| 동대문         | 폭력       |        1784 |
| 마포           | 폭력       |        1844 |
| 영등포         | 폭력       |        2701 |
| 성동           | 폭력       |        1223 |
| 동작           | 폭력       |        1631 |
| 광진           | 폭력       |        1676 |
| 서부           | 폭력       |         748 |
| 강북           | 폭력       |        1817 |
| 금천           | 폭력       |        1471 |
| 중랑           | 폭력       |        2022 |
| 강남           | 폭력       |        2283 |
| 관악           | 폭력       |        2614 |
| 강서           | 폭력       |        2445 |
| 강동           | 폭력       |        1942 |
| 종암           | 폭력       |         758 |
| 구로           | 폭력       |        2204 |
| 서초           | 폭력       |        1750 |
| 양천           | 폭력       |        1582 |
| 송파           | 폭력       |        2675 |
| 노원           | 폭력       |        2163 |
| 방배           | 폭력       |         423 |
| 은평           | 폭력       |        1092 |
| 도봉           | 폭력       |        1234 |
| 수서           | 폭력       |        1394 |
+----------------+------------+-------------+
31 rows in set (0.01 sec)

CH17-03~7: 중첩 서브쿼리(where절의 서브쿼리)

multi row: 메인쿼리가 서브쿼리의 결과에 포함될때 많이 씀(IN, ALL, ANY)

mysql> select host, broadcast_date
    -> from snl_show
    -> where host in (select name from celeb where job_title like '%영화배우%')
    -> ;

mysql> select name
    -> from celeb
    -> where name = any (select host from snl_show);
+--------+
| name   |
+--------+
| 강동원 |
| 유재석 |
| 차승원 |
| 이수현 |
+--------+
4 rows in set (0.01 sec

mysql> select name
-> from celeb
-> where name = all (select host from snl_show where id = 1);
+--------+
| name   |
+--------+
| 강동원 |
+--------+
1 row in set (0.01 sec)

multi column

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

mysql> select sex, agency, name
    -> from celeb
    -> where (sex, agency) in (select sex, agency  from celeb where name like '강동원');
+------+----------------+--------+
| sex  | agency         | name   |
+------+----------------+--------+
| M    | YG엔터테이먼트 | 강동원 |
| M    | YG엔터테이먼트 | 차승원 |
+------+----------------+--------+
2 rows in set (0.01 sec)

single row 서브쿼리가 비교연산자와 사용되는 경우 서브쿼리의 결과는 반드시 한 행만 반환해야 한다

SELECT COL
FROM TABLE
WHERE COL = (SELECT COL2 FROM TABLE2, WHERE COND)
ORDER BY COL