'경제'카테고리에 속하는 도서들의 도서 ID(BOOK_ID), 저자명(AUTHOR_NAME), 출판일(PUBLISHED_DATE) 리스트를 출력하는 SQL문을 작성해주세요. 결과는 출판일을 기준으로 오름차순 정렬해주세요.
해결 방식 :
-- LEFT JOIN -- 경제 카테고리에 속하는 도서 >> CATEGORY = '경제' -- 도서 ID(BOOK_ID), 저자명(AUTHOR_NAME), 출판일(PUBLISHED_DATE) -- 결과는 출판일 기준으로 오름차순 정렬 >> PUBLISHED_DATE ASC
결과 : 정답
답안 :
SELECT B.BOOK_ID, A.AUTHOR_NAME, DATE_FORMAT(B.PUBLISHED_DATE, '%Y-%m-%d')
FROM BOOK B LEFT JOIN AUTHOR A ON A.AUTHOR_ID = B.AUTHOR_ID
WHERE B.CATEGORY = '경제'
ORDER BY B.PUBLISHED_DATE ASC
다음은 어느 자동차 대여 회사에서 대여중인 자동차들의 정보를 담은 CAR_RENTAL_COMPANY_CAR 테이블입니다. CAR_RENTAL_COMPANY_CAR 테이블은 아래와 같은 구조로 되어있으며, CAR_ID, CAR_TYPE, DAILY_FEE, OPTIONS 는 각각 자동차 ID, 자동차 종류, 일일 대여 요금(원), 자동차 옵션 리스트를 나타냅니다.
Column nameTypeNullable
CAR_ID
INTEGER
FALSE
CAR_TYPE
VARCHAR(255)
FALSE
DAILY_FEE
INTEGER
FALSE
OPTIONS
VARCHAR(255)
FALSE
자동차 종류는 '세단', 'SUV', '승합차', '트럭', '리무진' 이 있습니다. 자동차 옵션 리스트는 콤마(',')로 구분된 키워드 리스트(옵션 리스트 값 예시: '열선시트', '스마트키', '주차감지센서')로 되어있으며, 키워드 종류는 '주차감지센서', '스마트키', '네비게이션', '통풍시트', '열선시트', '후방카메라', '가죽시트' 가 있습니다.
문제
CAR_RENTAL_COMPANY_CAR 테이블에서 '통풍시트', '열선시트', '가죽시트' 중 하나 이상의 옵션이 포함된 자동차가 자동차 종류 별로 몇 대인지 출력하는 SQL문을 작성해주세요. 이때 자동차 수에 대한 컬럼명은 CARS로 지정하고, 결과는 자동차 종류를 기준으로 오름차순 정렬해주세요.
해결 방식 :
-- 통풍시트, 열선시트, 가죽시트 중 하나 이상 옵션 > IN >> LIKE 변 -- 자통차 종류 별로 몇 대 >> GROUP BY, COUNT -- 자동차 수에 대한 컬럼 CARS -- 종류를 기준으로 오름차순 ASC
결과 : 틀림
답안 :
SELECT CRCC.CAR_TYPE, COUNT(CRCC.CAR_ID) AS CARS
FROM CAR_RENTAL_COMPANY_CAR AS CRCC
WHERE CRCC.OPTIONS LIKE ('%통풍시트%') OR ('%열선시트%') OR ('%가죽시트%')
GROUP BY 1
ORDER BY 1 ASC
결과 :
해설 :
(like [option]) or ([option]) or ([option]) (like [option]) or (like [option]) or (like [option])
like 함수 적용 범위
다른 풀이법 :
LIKE 활용
SELECT CRCC.CAR_TYPE, COUNT(CRCC.CAR_ID) AS CARS
FROM CAR_RENTAL_COMPANY_CAR AS CRCC
WHERE CRCC.OPTIONS LIKE ('%통풍시트%') OR
CRCC.OPTIONS LIKE ('%열선시트%') OR
CRCC.OPTIONS LIKE ('%가죽시트%')
GROUP BY 1
ORDER BY 1 ASC
INSTR 활용
SELECT CAR_TYPE, COUNT(*) CARS
FROM CAR_RENTAL_COMPANY_CAR
WHERE
INSTR(OPTIONS, '통풍시트') > 0 OR
INSTR(OPTIONS, '열선시트') > 0 OR
INSTR(OPTIONS, '가죽시트') > 0
GROUP BY CAR_TYPE
ORDER BY CAR_TYPE ASC
REGEXP 활용
SELECT CAR_TYPE, COUNT(*) CARS
FROM CAR_RENTAL_COMPANY_CAR
WHERE
OPTIONS REGEXP '통풍시트|열선시트|가죽시트'
GROUP BY CAR_TYPE
ORDER BY CAR_TYPE ASC
#이상이 없는것을 확있했다면 바로 적용 필요
학부.rename(columns={'초':'초등학교','중':'중학교','고':'고등학교'}, inplace=True)
#이름으로 적용된다. *숫자는 인덱스
학부.loc[ : , ['초등학교','중학교',]]
#행을 구분하자면 #이름이다.
학부.loc[ 0:2 , ['초등학교','중학교',]]
학부.iloc[ 0:2 ,[0,1]]
#concat 으로 데이터 합침, axis 1은 가로로, axis 0 은 세로로 붙인다
pd.concat([kbo,학부],axis=1)
#전제조건 raw 값이 같아야해, 인덱스 같은 정보가 있어야해
kbo2 = pd.concat([kbo, 학부], axis=1)
#부산수영초 뽑기
kbo2[kbo2['초등학교'] == '부산수영초'].groupby(['team'])[['초등학교']].count().sort_values(by=['초등학교'],ascending=False)
#team 은 인덱스로 들어갔어
#인덱스를 컬럼으로 변경하고 싶을때 #(/는 다음 줄도 같은 행에 있다는 것을 알려주는 것
kbo2[kbo2['초등학교'] =='부산수영초'].groupby(\
['team'], as_index=False)[['초등학교']].count().sort_values(by=['초등학교'], ascending=False)
#수영초가 들어간 팀
a= kbo2[kbo2['초등학교'] =='부산수영초'].groupby(\
['team'], as_index=False)[['초등학교']].count().sort_values(by=['초등학교'], ascending=False)['team'].tolist()
b = kbo2.team.unique().tolist()
#a와 b 차 집합을 하면 수영초가 없는 팀이 나온다.
set(b)- set(a)
#각 팀별로 연봉 많이 받는 사람 한명 확인
kbo2.sort_values(by=['연봉'], ascending=False).groupby(['team']).first()
#bmi가 높은 사람 뽑아보기
#기존 변수에서 새로운 변수 만들었음 >> 파생변수
#신장 파생변수 만들기
kbo2['신장'] = kbo2['신장/체중'].apply(lambda x : int(x.split("/")[0].replace("cm","")))
#bmi
kbo2['체중'] / (kbo2['신장']/100) **2
#체중 파생변수 만들기
kbo2['체중'] = kbo2['신장/체중'].apply(lambda x : int(x.split("/")[1].replace("kg","")))
kbo2['bmi'] = kbo2['체중'] / (kbo2['신장']/100) **2
#팀별로 bmi 가장 높은 사람 뽑아보기
kbo2.sort_values(by=['bmi'], ascending=False).groupby(['team']).first()
#axis = 1 은 컬럼이라는 뜻 #inplace를 해야 적용
kbo2.drop(['신장/체중','경력'], axis =1, inplace=True)
#웹드라이버매니저 설치해야 함 #회사에서 쓰게되면 파이썬 버전이 여러개 나눠짐. #크롬을 제어할 있는 것 셀레니움 #이것을 악용하게 되면 매크로, 봇 이 된다. #셀레니움 자동화 테스트 하다가 용도가 변경 된 것
pip install selenium
pip install webdriver_manager
#virtualenv >> 파이썬 친해지고 그러면 프라이버시 모드로 활용 가능
#크롬 버전 바꿔야함
from selenium import webdriver
from webdriver_manager.chrome import ChromeDriverManager
from selenium.webdriver.chrome.service import Service
driver = webdriver.Chrome(service=Service(ChromeDriverManager(driver_version="131.0.6778.109").install()))
# 그럼 data; 창이 뜬다 >> 창이 팝업 된 뒤에 뒤에 것들이 실행 된다.
#get 은 내가 chrome을 통해서 네이버 카페로 이동 하겠다.
driver.get("http://cafe.naver.com")
#데이터 로딩이 된 후 가져와야 한다.
import time
time.sleep(2)
pg2 = pd.read_html(io.StringIO(driver.page_source))[0]
타자 = pd.concat([pg1],[pg2] )
타자 = pd.concat([pg1,pg2], ignore_index=True)
타자.columns
#corr 만 쓰면 상관 관계가 나온다.
타자[[ 'AVG', 'G', 'PA', 'AB', 'R', 'H', '2B', '3B', 'HR',
'TB', 'RBI', 'SAC', 'SF']].corr()
#시각화 -> 히트맵
#상관관계를 볼때는 데이터 자체가 int 여야 한다.
sns.heatmap(타자[[ 'AVG', 'G', 'PA', 'AB', 'R', 'H', '2B', '3B', 'HR',
'TB', 'RBI', 'SAC', 'SF']].corr())
#타자 기준으로 kbo 데이터를 합치고 싶다.
#팀, 이름으로 조인을 해야하지만 중복되어서 데이터가 이상해 질수 있어
#팀을 처리를 해줘야해
kbo2['팀명'] = kbo2['team'].apply(lambda x : x.split()[0])
kbo2['팀명'].unique()
타자['팀명'].unique()
#merge 를 한다
#합칠떄 기준을 먼저 왼쪽에 적는다.
#how 에는 inner, right, outer 도 쓸 수 있음
타자2 = pd.merge(타자, kbo2, left_on=['팀명', '선수명'], right_on=['팀명','선수명'], how='inner')
SUBWAY_DISTANCE 테이블에서 노선별로 노선, 총 누계 거리, 평균 역 사이 거리를 노선별로 조회하는 SQL문을 작성해주세요.
총 누계거리는 테이블 내 존재하는 역들의 역 사이 거리의 총 합을 뜻합니다. 총 누계 거리와 평균 역 사이 거리의 컬럼명은 각각 TOTAL_DISTANCE, AVERAGE_DISTANCE로 해주시고, 총 누계거리는 소수 둘째자리에서, 평균 역 사이 거리는 소수 셋째 자리에서 반올림 한 뒤 단위(km)를 함께 출력해주세요. 결과는 총 누계 거리를 기준으로 내림차순 정렬해주세요.
해결 방식 :
-- 코드를 작성해주세요 -- select route, sum(d_cumulative), avg(d.between_dist) -- 누계거리 소수 둘째 / 평균 역사 소수 셋째 >> round 함수 -- km 함께 출력 >> concat -- 누계거리 desc
결과 : 틀림
concat 함수를 사용해서 텍스트(Km) 넣어야 함
답안 :
select route, concat(round(sum(d_between_dist),3),"km") as TOTAL_DISTANCE, concat(round(avg(d_between_dist), 4),"km") as AVERAGE_DISTANCE
from subway_distance
group by 1
order by 2 desc
주의할 점 :
정렬 기준
'TOTAL_DISTANCE'를 기준으로 하면 안된다. 해당 칼럼은 concat() 함수로 인해 더이상 숫자가 아니다. 문자열이다. 따라서 이 컬럼을 기준으로 정렬 시 문자열(사전식) 정렬기준에 따라 정렬되는 버그가 발생한다.
반올림 함수 인자 설정
round(숫자, 반올림할 자릿수)
반올림할 자릿수하는 말이 좀 헷갈림의 여지가 있다고 느껴지는데, 예를 들어 '소수 둘째 자리에서 반올림한다'고 가정하면 반올림할 자릿수라는 매개변수에는 1이 들어가야 한다.
SELECT p.product_code, sum(p.price*os.sales_amount) as sales
from product p
inner join offline_sale os on p.product_id = os.product_id
group by 1
order by 2 desc, 1 asc
문제 :
PRODUCT 테이블과 OFFLINE_SALE 테이블에서 상품코드 별 매출액(판매가 * 판매량) 합계를 출력하는 SQL문을 작성해주세요. 결과는 매출액을 기준으로 내림차순 정렬해주시고 매출액이 같다면 상품코드를 기준으로 오름차순 정렬해주세요.
SELECT p.product_code, sum(p.price*os.sales_amount) as sales
from product p
inner join offline_sale os on p.product_id = os.product_id
group by 1
order by 2 desc, 1 asc
APPOINTMENT테이블에서 2022년 5월에 예약한 환자 수를 진료과코드 별로 조회하는 SQL문을 작성해주세요. 이때, 컬럼명은 '진료과 코드', '5월예약건수'로 지정해주시고 결과는 진료과별 예약한 환자 수를 기준으로 오름차순 정렬하고, 예약한 환자 수가 같다면 진료과 코드를 기준으로 오름차순 정렬해주세요.
해결 방식 :
-- WHERE 2022년 5월 -- COUNT(예약 환자 수) '5월예약건수' -- GROUP BY 진료과 코드 AS '진료과 코드' -- 진료과별 예약 환자수 ASC / 진료과 코드 기준
결과 : 정답
답안 :
SELECT MCDP_CD AS'진료과 코드' , COUNT(*) AS '5월예약건수'
FROM APPOINTMENT
WHERE APNT_YMD LIKE '2022-05%'
GROUP BY 1
ORDER BY 2 ASC, 1 ASC
다른 풀이법 (참고) :
DATE_FORMAT은날짜와 시간 값을 지정된 형식의 문자열로 변환하는 데 사용됩니다. 기본 구문은 다음과 같습니다.
date: 형식을 지정할 날짜 또는 날짜시간 값
format: 원하는 출력 형식을 지정하는 문자열
여기에서format부분을 잘 확인하고 사용해야합니다. 대문자인지 소문자인지에 따라 바뀌기 때문에 조금만 실수해도 값을 가져오지 못합니다.