230602_SQL

문제 1. sandwich 테이블에서 가계 이름은 대문자, 메뉴 이름은 소문자로 조회하세요.
mysql> select ucase(cafe), lcase(menu) from sandwich;
+---------------------------------+----------------------------------+
| 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 |
| HENDRICKX BELGIAN BREAD CRAFTER | belgian chicken curry salad |
| ACADIA | lobster roll |
| BIRCHWOOD KITCHEN | smoked salmon salad |
| CEMITAS PUEBLA | atomica cemitas |
| NANA | grilled laughing bird shrimp and |
| LULA CAFE | ham and raclette panino |
| RICOBENE’S | breaded steak |
| FROG N SNAIL | the hawkeye |
| CROSBY’S KITCHEN | chicken dip |
| LONGMAN & EAGLE | wild boar sloppy joe |
| BARI | meatball sub |
| MANNY’S | corned beef |
| EGGY’S | turkey club |
| OLD JERUSALEM | falafel |
| MINDY’S HOTCHOCOLATE | crab cake |
| OLGA’S DELICATESSEN | chicken schnitzel |
| DAWALI MEDITERRANEAN KITCHEN | shawarma |
| BIG JONES | toasted pimiento cheese |
| LA PANE | vegetarian panino |
| PASTORAL | cali chèvre |
| MAX’S DELI | pastrami |
| LUCKY’S SANDWICH CO. | the fredo |
| CITY PROVISIONS | smoked ham |
| PAPA’S CACHE SABROSO | jibarito |
| BAVETTE’S BAR & BOEUF | shaved prime rib |
| HANNAH’S BRETZEL | serrano ham and manchego cheese |
| LA FOURNETTE | tuna salad |
| PARAMOUNT ROOM | paramount reuben |
| MELT SANDWICH SHOPPE | the istanbul |
| FLORIOLE CAFE & BAKERY | b.a.d. |
| FIRST SLICE PIE CAFÉ | duck confit and mozzarella |
| TROQUET | croque monsieur |
| GRAHAMWICH | green garbanzo |
| SAIGON SISTERS | the hen house |
| ROSALIA’S DELI | tuscan chicken |
| Z&H MARKETCAFE | the marty |
| MARKET HOUSE ON THE SQUARE | whitefish |
| ELAINE’S COFFEE CALL | oat bread, pecan butter, and fru |
| MARION STREET CHEESE MARKET | cauliflower melt |
| CAFECITO | cubana |
| CHICKPEA | kufta |
| THE GODDESS AND GROCER | debbie’s egg salad |
| ZENWICH | beef curry |
| TONI PATISSERIE | le végétarien |
| PHOEBE’S BAKERY | the gatsby |
+---------------------------------+----------------------------------+
50 rows in set (0.30 sec)
2. sandwich 테이블에서 10위 메뉴의 마지막 단어를 조회해라.
mysql> select ranking, cafe, mid(menu, -3, 3) from sandwich where ranking=10;
+---------+------+------------------+
| ranking | cafe | mid(menu, -3, 3) |
+---------+------+------------------+
| 10 | Nana | and |
+---------+------+------------------+
1 row in set (0.23 sec)
3. sandwich 테이블에서 메뉴 이름의 평균 길이를 조회하라.
mysql> select avg(length(menu)) from sandwich ;
+-------------------+
| avg(length(menu)) |
+-------------------+
| 13.9600 |
+-------------------+
1 row in set (0.23 sec)
4. oil_price 테이블에서 가격을 십원 단위에서 반올림해서 조회하라.
mysql> select 가격 , round(가격, -2) from oil_price;
+--------+-------------------+
| 가격 | round(가격, -2) |
+--------+-------------------+
| 1484 | 1500 |
| 1485 | 1500 |
| 1498 | 1500 |
| 1498 | 1500 |
| 1509 | 1500 |
| 1598 | 1600 |
| 1635 | 1600 |
| 2160 | 2200 |
| 1498 | 1500 |
+--------+-------------------+
9 rows in set (0.27 sec)
5. oil_price 테이블에서 가격이 십원 단위에서 반올림 했을 때 2000 원 이상인 경우, 천단위에 콤마를 넣어 조회하세요.
mysql> select 가격, format(가격, 0) from oil_price where round(가격, -2) >= 2000;
+--------+-------------------+
| 가격 | format(가격, 0) |
+--------+-------------------+
| 2160 | 2,160 |
+--------+-------------------+
1 row in set (0.29 sec)

0111danye
오전 11:49
SQL 서브 쿼리
3가지
scalar 서브쿼리 - select 절에 사용, 결과는 하나의 컬럼이어야 한다.
inline view - from 절에 사용하는 서브 쿼리, 메인 쿼리에서는 인라인 뷰에서 조회한 컬럼만 사용이 가능.
nested 서브쿼리 - where 절에 사용하는 서브 쿼리, 종류가 세 가지 / 서브 쿼리가 비교 연산자와 사용되는 경우, 서브 쿼리의 검색 결과는 한 개 행의 결과 값을 가져야 한다.
nested 서브 쿼리에는 또 다시 세 종류로 나뉜다.

• single row : 하나의 행을 검색하는 서브 쿼리
• multiple row / IN, EXISTS, ANY, ALL(서브쿼리 결과를 모두 만족하면 비교 연산자를 사용한다.) 사용 : 하나 이상의 행을 검색하는 서브 쿼리
• multiple column : 하나 이상의 열을 검색하는 서브 쿼리 / 서브 쿼리 내에 메인 쿼리 컬럼이 같이 사용되는 경우
  예제 1
  서울 은평 경찰서의 강도 검거 건수와 서울시 경찰서 전체의 강도 검거 건수를 조회
  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
  crime_type like '강도' and
  status_type like '검거';
  +-------------+--------+
  | case_number | avg |
  +-------------+--------+
  | 1 | 4.0541 |
  +-------------+--------+
  1 row in set (0.20 sec)
  예제 2
  경찰서 별로 가장 많이 발생한 범죄 건수와 범죄 유형을 조회하려고 한다.
  mysql> select c.police_station, c.crime_type, 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;
  +----------------+------------+-------------+
  | police_station | crime_type | case_number |
  +----------------+------------+-------------+
  | 중부 | 폭력 | 997 |
  | 종로 | 폭력 | 964 |
  | 남대문 | 절도 | 699 |
  | 서대문 | 폭력 | 1292 |
  | 혜화 | 폭력 | 747 |
  | 중부 | 폭력 | 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 |
  +----------------+------------+-------------+
  36 rows in set (0.20 sec)
  예제 3
  snl_show 테이블에서 아이디가 1인 사람의 이름, celeb 테이블 중에서.
  mysql> select name from celeb where name = (select host from snl_show where id = 1);
  +-----------+
  | name |
  +-----------+
  | 강동원 |
  +-----------+
  1 row in set (0.20 sec)
  IN 에 대한 예제 - snl에 출연한 영화 배우 조회
  mysql> select name from celeb where job_title like '%영화배우%';
  +-----------+
  | name |
  +-----------+
  | 강동원 |
  | 차승원 |
  +-----------+
  2 rows in set (0.19 sec)
  mysql> select host from snl_show;
  +-----------+
  | host |
  +-----------+
  | 강동원 |
  | 유재석 |
  | 차승원 |
  | 이수현 |
  | 이병헌 |
  | 하지원 |
  | 제시 |
  | 조정석 |
  | 조여정 |
  | 옥주현 |
  +-----------+
  10 rows in set (0.20 sec)
  mysql> select host
  -> from snl_show
  -> where host in (select name from celeb where job_title like '%영화배우%');
  +-----------+
  | host |
  +-----------+
  | 강동원 |
  | 차승원 |
  +-----------+
  2 rows in set (0.20 sec)
  EXISTS 에 대한 예제 - 범죄 검거 혹은 발생 건수가 2000건 보다 큰 경찰서 조회
  mysql> select from police_station limit 3;
  +-----------------------+--------------------------------------------------+
  | name | address |
  +-----------------------+--------------------------------------------------+
  | 서울강남경찰서 | 서울특별시 강남구 테헤란로 114길 11 |
  | 서울강동경찰서 | 서울특별시 강동구 성내로 33 |
  | 서울강북경찰서 | 서울특별시 강북구 오패산로 406 |
  +-----------------------+--------------------------------------------------+
  3 rows in set (0.22 sec)
  mysql> select from crime_status limit 3;
  +------+----------------+------------+-------------+-------------+-----------------------+
  | year | police_station | crime_type | status_type | case_number | reference |
  +------+----------------+------------+-------------+-------------+-----------------------+
  | 2020 | 중부 | 살인 | 발생 | 1 | 서울중부경찰서 |
  | 2020 | 중부 | 살인 | 검거 | 1 | 서울중부경찰서 |
  | 2020 | 중부 | 강도 | 발생 | 3 | 서울중부경찰서 |
  +------+----------------+------------+-------------+-------------+-----------------------+
  3 rows in set (0.19 sec)
  mysql> select police_station
  -> from crime_status
  -> where case_number > 2000 ;
  +----------------+
  | police_station |
  +----------------+
  | 영등포 |
  | 영등포 |
  | 영등포 |
  | 중랑 |
  | 강남 |
  | 관악 |
  | 관악 |
  | 관악 |
  | 강서 |
  | 강서 |
  | 구로 |
  | 송파 |
  | 송파 |
  | 송파 |
  | 노원 |
  +----------------+
  15 rows in set (0.20 sec)
  mysql> select police_station, reference from crime_status where case_number > 2000;
  +----------------+--------------------------+
  | police_station | reference |
  +----------------+--------------------------+
  | 영등포 | 서울영등포경찰서 |
  | 영등포 | 서울영등포경찰서 |
  | 영등포 | 서울영등포경찰서 |
  | 중랑 | 서울중랑경찰서 |
  | 강남 | 서울강남경찰서 |
  | 관악 | 서울관악경찰서 |
  | 관악 | 서울관악경찰서 |
  | 관악 | 서울관악경찰서 |
  | 강서 | 서울강서경찰서 |
  | 강서 | 서울강서경찰서 |
  | 구로 | 서울구로경찰서 |
  | 송파 | 서울송파경찰서 |
  | 송파 | 서울송파경찰서 |
  | 송파 | 서울송파경찰서 |
  | 노원 | 서울노원경찰서 |
  +----------------+--------------------------+
  15 rows in set (0.41 sec)
  mysql> select name
  -> from police_station p
  -> where exists (select police_station from crime_status c where p.name = c.reference and case_number > 2000);
  +--------------------------+
  | name |
  +--------------------------+
  | 서울강남경찰서 |
  | 서울강서경찰서 |
  | 서울관악경찰서 |
  | 서울구로경찰서 |
  | 서울노원경찰서 |
  | 서울송파경찰서 |
  | 서울영등포경찰서 |
  | 서울중랑경찰서 |
  +--------------------------+
  8 rows in set (0.25 sec)
  ANY 에 대한 예제 - snl에 출연한 적이 있는 연예인 이름 조회
  mysql> select name
  -> from celeb
  -> where name = any (select host from snl_show);
  +-----------+
  | name |
  +-----------+
  | 강동원 |
  | 유재석 |
  | 차승원 |
  | 이수현 |
  +-----------+
  4 rows in set (0.19 sec)
  ALL 에 대한 예제 - snl에 출연한 적이 있는 연예인 이름 조회
  mysql> select name from celeb where name = all (select host from snl_show where id = 1);
  +-----------+
  | name |
  +-----------+
  | 강동원 |
  +-----------+
  1 row in set (0.39 sec)
  예제 4 - multi column subquery
  강동원과 성별, 소속사가 같은 연예인의 이름, 성별, 소속사를 조회하라.

0111danye
오후 4:43
문제 1.
oil_price 테이블에서 셀프 주유의 평균 가격과 SK에너지의 가장 비싼 가격을 Scalar Subquery를 사용하려 조회하라.
oil_price 테이블에서 셀프 주유의 평균 가격 / SK에너지의 가장 비싼 가격
이렇게 나눠서?
mysql> select avg(가격) from oil_price where 셀프= 'Y';
+-------------+
| avg(가격) |
+-------------+
| 1517.6000 |
+-------------+
1 row in set (0.19 sec)
mysql> select max(가격) from oil_price where 상표 like 'SK%';
+-------------+
| max(가격) |
+-------------+
| 2160 |
+-------------+
1 row in set (0.19 sec)
mysql> select max(가격), (select avg(가격) from oil_price where 셀프= 'Y')
-> from oil_price where 상표 like 'SK%';
+-------------+-------------------------------------------------------+
| max(가격) | (select avg(가격) from oil_price where 셀프= 'Y') |
+-------------+-------------------------------------------------------+
| 2160 | 1517.6000 |
+-------------+-------------------------------------------------------+
1 row in set (0.19 sec)
mysql> select avg(가격), (select max(가격) from oil_price where 상표 like 'SK%') from oil_price where 셀프= 'Y';
+-------------+-------------------------------------------------------------+
| avg(가격) | (select max(가격) from oil_price where 상표 like 'SK%') |
+-------------+-------------------------------------------------------------+
| 1517.6000 | 2160 |
+-------------+-------------------------------------------------------------+
1 row in set (0.19 sec)
문제 2.
oil_price 테이블에서 상표별로 가장 비싼 가격과 상호를 Inline View 를 사용하여 조회하세요.
oil_price 테이블에서 상표별로 가장 비싼 가격, oil_price 테이블에서 상표별로 가장 비싼 상호
mysql> select 상표, max(가격) from oil_price group by 상표;
+--------------------+-------------+
| 상표 | max(가격) |
+--------------------+-------------+
| SK에너지 | 2160 |
| 현대오일뱅크 | 1498 |
| S-OIL | 1509 |
| GS칼텍스 | 1498 |
+--------------------+-------------+
4 rows in set (0.19 sec)
mysql> select o.상호, o.상표, s.max_price
-> from oil_price as o,
-> (select 상표, max(가격) max_price from oil_price group by 상표) as s
-> where o.상표 = s.상표 and o.가격 = s.max_price;
+-------------------+--------------------+-----------+
| 상호 | 상표 | max_price |
+-------------------+--------------------+-----------+
| (주)MS주유소 | 현대오일뱅크 | 1498 |
| 쌍문주유소 | S-OIL | 1509 |
| 뉴서울(강남) | SK에너지 | 2160 |
| 신길주유소 | GS칼텍스 | 1498 |
+-------------------+--------------------+-----------+
4 rows in set (0.19 sec)
문제 3.
평균 가격보다 높은 주유소 상호와 가격을 Nested Subquery를 사용하여 조회하세요.
mysql> select avg(가격) from oil_price;
+-------------+
| avg(가격) |
+-------------+
| 1596.1111 |
+-------------+
1 row in set (0.19 sec)
mysql> select 상호, 가격 from oil_price where 가격 > (select avg(가격) from oil_price);
+--------------------+--------+
| 상호 | 가격 |
+--------------------+--------+
| 21세기주유소 | 1598 |
| 살피재주유소 | 1635 |
| 뉴서울(강남) | 2160 |
+--------------------+--------+
3 rows in set (0.19 sec)
문제 4.
3번에서 조회한 주유소에서 주유한 연예인의 이름과 주유소, 주유일을 Nested Subquery를 사용하여 조회하세요. (refueling 테이블)
mysql> select from refueling;
+------+-----------+--------------------+------------+--------+
| ID | NAME | GAS_STATION | OIL_DAY | PRICE |
+------+-----------+--------------------+------------+--------+
| 1 | 유재석 | 뉴서울(강남) | 2021-10-01 | 50000 |
| 2 | 이미주 | 뉴서울(강남) | 2021-10-01 | 120000 |
| 3 | 이효리 | 제주주유소 | 2021-10-03 | 80000 |
| 4 | 아이유 | 타이거주유소 | 2021-10-03 | 80000 |
| 5 | 유재석 | 뉴서울(강남) | 2021-10-03 | 60000 |
| 6 | 강동원 | 타이거주유소 | 2021-10-10 | 50000 |
| 7 | 유재석 | 쌍문주유소 | 2021-10-10 | 60000 |
| 8 | 이미주 | 타이거주유소 | 2021-10-10 | 50000 |
| 9 | 아이유 | 뉴서울(강남) | 2021-10-14 | 150000 |
| 10 | 아이유 | 뉴서울(강남) | 2021-10-14 | 120000 |
| 11 | 유재석 | 쌍문주유소 | 2021-10-14 | 80000 |
| 12 | 유재석 | 뉴서울(강남) | 2021-10-16 | 110000 |
| 13 | 이미주 | 타이거주유소 | 2021-10-16 | 50000 |
| 14 | 이효리 | 제주주유소 | 2021-10-20 | 80000 |
| 15 | 이상순 | 제주주유소 | 2021-10-20 | 50000 |
| 16 | 이상순 | 타이거주유소 | 2021-10-20 | 140000 |
+------+-----------+--------------------+------------+--------+
16 rows in set (0.22 sec)
mysql> select name, gas_station, oil_day from refueling where gas_station in (select 상호 from oil_price where 가격 > (select avg(가격) from oil_price)) ;
+-----------+-------------------+------------+
| name | gas_station | oil_day |
+-----------+-------------------+------------+
| 유재석 | 뉴서울(강남) | 2021-10-01 |
| 이미주 | 뉴서울(강남) | 2021-10-01 |
| 유재석 | 뉴서울(강남) | 2021-10-03 |
| 아이유 | 뉴서울(강남) | 2021-10-14 |
| 아이유 | 뉴서울(강남) | 2021-10-14 |
| 유재석 | 뉴서울(강남) | 2021-10-16 |
+-----------+-------------------+------------+
6 rows in set (0.20 sec)
문제 5.
refueling 테이블과 oil_price 테이블에서 10만원 이상 주유한 연예인 이름, 상호, 상표, 주유 금액, 가격을 Inline View 를 사용하여 조회하세요.
mysql> select from refueling limit 3;
+------+-----------+-------------------+------------+--------+
| ID | NAME | GAS_STATION | OIL_DAY | PRICE |
+------+-----------+-------------------+------------+--------+
| 1 | 유재석 | 뉴서울(강남) | 2021-10-01 | 50000 |
| 2 | 이미주 | 뉴서울(강남) | 2021-10-01 | 120000 |
| 3 | 이효리 | 제주주유소 | 2021-10-03 | 80000 |
+------+-----------+-------------------+------------+--------+
3 rows in set (0.19 sec)
mysql> select * from oil_price limit 3;
+------+----------------------+-------------------------------------------------+--------+--------+--------------------+--------------+
| ID | 상호 | 주소 | 가격 | 셀프 | 상표 | 구 |
+------+----------------------+-------------------------------------------------+--------+--------+--------------------+--------------+
| 1 | 타이거주유소 | 서울 은평구 수색로 188(중산동) | 1484 | N | SK에너지 | 은평구 |
| 2 | (주)명연에너지 | 서울 은평구 수색로 236(수색동) | 1485 | Y | 현대오일뱅크 | 은평구 |
| 3 | 성락주유소 | 서울 영등포구 가마산로 414(신길동) | 1498 | Y | S-OIL | 영등포구 |
+------+----------------------+-------------------------------------------------+--------+--------+--------------------+--------------+
3 rows in set (0.19 sec)
mysql> select r.name, o.상호, o.상표, r.price, o.가격
-> from oil_price o,
-> (select name, gas_station, price from refueling where price >= 100000) r
-> where o.상호 = r.gas_station;
+-----------+--------------------+-------------+--------+--------+
| name | 상호 | 상표 | price | 가격 |
+-----------+--------------------+-------------+--------+--------+
| 이상순 | 타이거주유소 | SK에너지 | 140000 | 1484 |
| 유재석 | 뉴서울(강남) | SK에너지 | 110000 | 2160 |
| 아이유 | 뉴서울(강남) | SK에너지 | 120000 | 2160 |
| 아이유 | 뉴서울(강남) | SK에너지 | 150000 | 2160 |
| 이미주 | 뉴서울(강남) | SK에너지 | 120000 | 2160 |
+-----------+--------------------+-------------+--------+--------+
5 rows in set (0.19 sec)