전체 글

상품 별 오프라인 매출 구하기

SELECT A.PRODUCT_CODE,SUM(A.PRICE * B.SALES_AMOUNT) AS SALESFROM PRODUCT A JOIN OFFLINE_SALE BON A.PRODUCT_ID = B.PRODUCT_IDGROUP BY A.PRODUCT_CODEORDER BY SALES DESC, A.PRODUCT_CODE;

부서별 평균 연봉 조회하기

SELECT A.DEPT_ID,A.DEPT_NAME_EN,ROUND(AVG(SAL), 0) AS AVG_SALFROM HR_DEPARTMENT A JOIN HR_EMPLOYEES BON A.DEPT_ID = B.DEPT_IDGROUP BY A.DEPT_IDORDER BY AVG_SAL DESC;

조건에 맞는 사용자와 총 거

SELECT A.USER_ID, A.NICKNAME,SUM(B.PRICE) AS TOTAL_SALESFROM USED_GOODS_USER A JOIN USED_GOODS_BOARD BON A.USER_ID = B.WRITER_IDWHERE B.STATUS = 'DONE'GROUP BY B.WRITER_IDHAVING SUM(B.PRICE) >= 700000ORDER BY TOTAL_SALES;

즐겨찾기가 가장 많은 식당 정보 출력하기

SELECT A.FOOD_TYPE,A.REST_ID,A.REST_NAME, A.FAVORITESFROM REST_INFO A WHERE (A.FOOD_TYPE, A.FAVORITES) IN (SELECT B.FOOD_TYPE, MAX(B.FAVORITES) FROM REST_INFO B GROUP BY B.FOOD_TYPE)ORDER BY A.FOOD_TYPE DESC;

카테고리 별 도서 판매량 집계하기

SELECT A.CATEGORY,SUM(B.SALES) AS TOTAL_SALESFROM BOOK A JOIN BOOK_SALES B ON A.BOOK_ID = B.BOOK_IDAND MONTH(B.SALES_DATE) = 1GROUP BY A.CATEGORYORDER BY A.CATEGORY;

자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기

SELECT CAR_ID,CASE WHEN CAR_ID IN (SELECT CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY WHERE '2022-10-16' BETWEEN START_DATE AND END_DATE) THEN '대여중' ELSE '대여 가능' END AS AVAILABILITYFROM CAR_RENTAL_COMPANY_RENTAL_HISTORY GROUP BY CAR_IDORDER BY CAR_ID DESC;

3월에 태어난 여성 회원 목록 출력하기

SELECT MEMBER_ID, MEMBER_NAME,GENDER,DATE_FORMAT(DATE_OF_BIRTH, '%Y-%m-%d') AS DATE_OF_BIRTHFROM MEMBER_PROFILEWHERE MONTH(DATE_OF_BIRTH) = 3 AND GENDER = 'W'AND TLNO IS NOT NULLORDER BY MEMBER_ID;

오랜 기간 보호한 동물(2)

SELECT A.ANIMAL_ID,A.NAMEFROM ANIMAL_OUTS A JOIN ANIMAL_INS BON A.ANIMAL_ID = B.ANIMAL_IDORDER BY DATEDIFF(A.DATETIME, B.DATETIME) DESC LIMIT 2;

조건에 맞는 사용자 정보 조회

SELECT B.USER_ID, B.NICKNAME, CONCAT(B.CITY, ' ', B.STREET_ADDRESS1, ' ', B.STREET_ADDRESS2) AS '전체주소', CONCAT(LEFT(B.TLNO, 3), '-', MID(B.TLNO, 4, 4), '-', RIGHT(B.TLNO, 4)) AS '전화번호'FROM USED_GOODS_USER BWHERE B.USER_ID IN ( SELECT A.WRITER_ID FROM USED_GOODS_BOARD A GROUP BY A.WRITER_ID HAVING COUNT(*) >= 3)ORDER BY B.USER_ID DESC;- 서브쿼리를 쓸 때 별칭을 주어서 명확하게 구분하자

대여 기록이 존재하는 자동차

SELECT DISTINCT(A.CAR_ID)FROM CAR_RENTAL_COMPANY_CAR A JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY BON A.CAR_ID = B.CAR_IDWHERE A.CAR_TYPE = '세단' AND MONTH(B.START_DATE) = 10ORDER BY A.CAR_ID DESC;