Simon Yoon's blog

문제:USER_INFO 테이블과 ONLINE_SALE 테이블에서 년, 월, 성별 별로 상품을 구매한 회원수를 집계 - 결과는 년, 월, 성별을 기준으로 오름차순 정렬 - 성별 정보가 없는 경우 결과에서 제외 해결방법:1. 유저 테이블과 판매내역 테이블을 INNER JOIN2. SALES_DATE 컬럼에서 YEAR와 MONTH 를 추출3. SELECT 절에서 COUNT DISTINCT 와 GROUP BY 를 사용하여 USER_ID 를 카운트4. WHERE 조건으로 성별이 NULL 값이 아닌 경우만 사용하도록 함 예시 코드:SELECT YEAR(SALES_DATE) YEAR, MONTH(SALES_DATE) MONTH, GENDER, COUNT(DISTINCT USER_ID) USERSFROM(SELECT ..

문제:우유와 요거트를 동시에 구입한 장바구니의 아이디를 조회 해결 방법:1. 장바구니ID 별로 GROUP_CONCAT 을 해서 장바구니 내용을 조회2. 장바구니에 우유와 요거트가 같이 담겨있는지 조건 설정 예시 코드:SELECT CART_IDFROM (SELECT CART_ID, GROUP_CONCAT(NAME) NAMESFROM CART_PRODUCTSGROUP BY CART_ID) T1WHERE NAMES LIKE "%Milk%" AND NAMES LIKE "%Yogurt%"ORDER BY CART_ID;

문제:2022년 1월의 도서 판매 데이터를 기준으로 저자 별, 카테고리 별 매출액(TOTAL_SALES = 판매량 * 판매가), 저자 ID(AUTHOR_ID), 저자명(AUTHOR_NAME), 카테고리(CATEGORY), 매출액(SALES) 리스트를 출력 - 결과는 저자 ID를 오름차순, 카테고리를 내림차순 정렬 해결방법:1. 도서 판매 테이블에서 판매일에 대한 기준을 WHERE 절에서 설정2. 책 ID를 기준으로 한 INNER JOIN과 저자 ID 를 기준으로 한 INNER JOIN이 필요3. GROUP BY 로 저자 별, 카테고리 별 매출액을 계산 예시 코드:SELECT A.AUTHOR_ID, AUTHOR_NAME, CATEGORY, SUM(TOTAL_SALES) TOTAL_SALESFROM AUTH..

문제:조회수가 가장 높은 중고거래 게시물에 대한 첨부파일 경로를 조회 첨부파일 경로는 FILE ID를 기준으로 내림차순 정렬 해결방법:1. 파일경로 출력을 위해서 CONCAT 사용2. 조회수와 파일명 테이블을 BOARD_ID 기준으로 INNER JOIN3. WHERE 절에서 최대 조회수 조건 설정4. ORDER BY 내림차순 정렬 예시 코드:SELECT CONCAT('/home/grep/src/', F.BOARD_ID, '/', F.FILE_ID, F.FILE_NAME, F.FILE_EXT) FILE_PATHFROM USED_GOODS_BOARD BINNER JOIN USED_GOODS_FILE FON B.BOARD_ID = F.BOARD_IDWHERE B.VIEWS = (SELECT MAX(VIEWS)..

문제:평균 대여 기간이 7일 이상인 자동차들의 자동차 ID와 평균 대여 기간 리스트를 출력 - 평균 대여 기간은 소수점 두번째 자리에서 반올림 - 평균 대여 기간을 기준으로 내림차순 정렬, 평균 대여 기간이 같으면 자동차 ID를 기준으로 내림차순 정렬 해결방법:1. 평균 대여 기간은 ID를 기준으로 GROUP BY, AVG 사용2. 대여 기간을 계산하기 위해서는 DATEDIFF 사용3. ORDER BY 로 정렬 예시 코드:SELECT CAR_ID, ROUND(AVG(DATEDIFF(END_DATE, START_DATE)+1), 1) AVERAGE_DURATIONFROM CAR_RENTAL_COMPANY_RENTAL_HISTORYGROUP BY CAR_IDHAVING AVERAGE_DURATION >= 7..

문제:대여 시작일이 2022년 9월에 속하는 대여 기록에 대해서 대여 기간이 30일 이상이면 '장기 대여' 그렇지 않으면 '단기 대여' 로 표시하는 대여기록을 출력 - 결과는 대여 기록 ID를 기준으로 내림차순 정렬 해결방법:1. 대여기간 차이는 DATEDIFF() 함수를 사용2. IF 문으로 장기 대여와 단기 대여 구분3. DATE_FORMAT() 으로 날짜 형식 설정4. 대여 시작일은 WHERE 절에서 BETWEEN A AND B 로 설정 예시 코드:SELECT HISTORY_ID, CAR_ID, DATE_FORMAT(START_DATE, "%Y-%m-%d") START_DATE, DATE_FORMAT(END_DATE, "%Y-%m-%d") END_DATE, IF(DATEDIFF(END_DATE, S..

문제:FISH_INFO 테이블에서 잡은 BASS와 SNAPPER의 수를 출력 해결방법:1. 종류별 수가 아닌 전체 수를 구하면 되므로 COUNT() 사용2. FISH_INFO 테이블과 FISH_NAME_INFO 테이블을 INNER JOIN3. WHERE - IN 을 사용하여 BASS 와 SNAPPER 만 조회 예시 코드:SELECT COUNT(1) FISH_COUNTFROM FISH_INFO T1INNER JOIN FISH_NAME_INFO T2ON T1.FISH_TYPE = T2.FISH_TYPEWHERE T2.FISH_NAME IN ("BASS", "SNAPPER");

문제:서울에 위치한 식당들의 식당 ID, 식당 이름, 음식 종류, 즐겨찾기수, 주소, 리뷰 평균 점수를 조회 - 리뷰 평균점수는 소수점 세 번째 자리에서 반올림 - 결과는 평균점수를 기준으로 내림차순 정렬, 즐겨찾기수를 기준으로 내림차순 정렬 해결방법:1. 리뷰 평균점수는 식당 ID 별 GROUP BY로 계산2. 식당 ID 로 식당 정보 테이블과 리뷰 테이블을 조인3. 서울에 위치한 식당을 찾기 위해 LIKE 사용4. ORDER BY 로 점수와 즐겨찾기 수 기준으로 내림차순 정렬 예시 답안:SELECT I.REST_ID, I.REST_NAME, I.FOOD_TYPE, I.FAVORITES, ADDRESS, ROUND(AVG(R.REVIEW_SCORE), 2) AS SCOREFROM REST_INFO AS..

문제:2022년도 평가 점수가 가장 높은 사원들의 점수, 사번, 성명, 직책, 이메일을 조회 해결방법:1. GROUP BY 로 평가 점수 상하반기 SUM()2. 2022년도에 해당하는 점수만 계산하도록 설정3. 가장 높은 평가 점수 확인을 위해 MAX() 사용4. 여러 테이블 조인에 INNER JOIN 사용5. HAVING 을 사용하여 최고 점수를 가진 레고드 반환 예시 코드:SELECT SUM(G.SCORE) SCORE, G.EMP_NO, E.EMP_NAME, E.POSITION, E.EMAILFROM (SELECT * FROM HR_GRADE WHERE YEAR = 2022) GINNER JOIN HR_EMPLOYEES EON G.EMP_NO = E.EMP_NOGROUP BY G.E..

문제PATIENT, DOCTOR 그리고 APPOINTMENT 테이블에서 2022년 4월 13일 취소되지 않은 흉부외과(CS) 진료 예약 내역을 조회- 진료예약번호, 환자이름, 환자번호, 진료과코드, 의사이름, 진료예약일시 컬럼을 출력- 진료예약일시를 기준으로 오름차순 정렬 해결방법1. 조건 3개를 동시에 설정: 예약일시, 진료과코드, 취소여부2. APPOINTMENT 테이블을 기준으로 PATIENT, DOCTOR 테이블을 INNER JOIN3. 정렬 조건 설정 예시 코드SELECT APNT.APNT_NO, P.PT_NAME, P.PT_NO, APNT.MCDP_CD, D.DR_NAME, APNT.APNT_YMDFROM APPOINTMENT APNT INNER JOIN PATIENT P ON P.PT_..

문제:자동차 ID와 AVAILABILITY 리스트를 출력- 2022년 10월 16일에 대여 중인 자동차인 경우 '대여중' 이라고 표시하고, 대여 중이지 않은 자동차인 경우 '대여 가능'을 표시- 반납 날짜가 2022년 10월 16일인 경우에도 '대여중'으로 표시해주시고 결과는 자동차 ID를 기준으로 내림차순 정렬 해결방법:1. IF 문과 BETWEEN A AND B 를 활용하여 가능여부 판단하기2. GROUP BY 를 통해 자동차별 가능 여부 정리하기3. 마지막으로 CASE 문으로 원하는 형태의 출력 만들기 예시 코드:SELECT CAR_ID, CASE SUM(IF(DATE("2022-10-16") BETWEEN START_DATE AND END_DATE, 1, 0))WHEN 1 THEN "대여중"WHE..

문제:동일한 회원이 동일한 상품을 재구매한 데이터를 구하여, 재구매한 회원 ID와 재구매한 상품 ID를 출력 - 회원 ID를 기준으로 오름차순 정렬, 상품 ID를 기준으로 내림차순 정렬 해결방법:1. 유저별, 상품별 재구매건이므로 GROUP BY 로 조회2. 조회한 유저별, 상품별 레코드가 중복일 경우 COUNT(1) 한 값은 2 이상3. ORDER BY로 정렬 처리하기 에시 코드:SELECT USER_ID, PRODUCT_IDFROM ONLINE_SALEGROUP BY USER_ID, PRODUCT_IDHAVING COUNT(1) > 1ORDER BY USER_ID ASC, PRODUCT_ID DESC;

문제:잡은 물고기의 평균 길이를 출력- 잡은 물고기의 길이가 10cm 이하인 경우 NULL 로 처리되어있음- 평균 길이는 소수점 3번째 자리에서 반올림 해결방법1. IFNULL() 함수로 길이가 10cm 이하인 경우에 대해서 10으로 값 설정2. 평균 길이는 AVG, ROUND 를 활용 예시 코드SELECT ROUND(AVG(IFNULL(LENGTH, 10)), 2) AVERAGE_LENGTHFROM FISH_INFO;

문제:FISH_INFO 테이블에서 2021년도에 잡은 물고기 수를 출력하는 SQL 문을 작성 해결방법:1. 잡은 날짜에 대한 컬럼에 YEAR() 함수를 사용해서 조건 설정하기2. SELECT 에서 COUNT(*) 를 써서 레코드 수를 출력 예시코드:SELECT COUNT(1) FISH_COUNTFROM FISH_INFOWHERE YEAR(TIME) = 2021;

문제:대장균 개체의 ID(ID) 와 분류(SIZE)를 출력하는 SQL 문을 작성- 대장균 개체의 크기가 100 이하라면 'LOW', 100 초과 1000 이하라면 'MEDIUM', 1000 초과라면 'HIGH' 라고 분류- 개체의 ID 에 대해 오름차순 정렬  해결방법:1. SELECT 절에서 CASE 문으로 개체 크기에 따라 분류하기 예시 코드:SELECT ID, (CASE WHEN SIZE_OF_COLONY 100 AND SIZE_OF_COLONY 1000 THEN "HIGH" END) SIZEFROM ECOLI_DATAORDER BY ID ASC;