HANCODING

없어진 기록 찾기 -- 코드를 입력하세요 SELECT ao.animal_id , ao.name FROM ANIMAL_INS ai ,ANIMAL_OUTS ao WHERE ai.animal_id(+) = ao.animal_id AND ai.animal_id is null order by 1

그룹별 조건에 맞는 식당 목록 출력하기 SELECT a.member_name, b.review_text, to_char(b.review_date,'YYYY-MM-DD') REVIEW_DATE FROM (-- 회원 별 리뷰 수 순위 SELECT r.member_id,mp.MEMBER_NAME, RANK() OVER (ORDER BY COUNT(*) DESC) as rank FROM REST_REVIEW r ,MEMBER_PROFILE mp WHERE r.member_id = mp.member_id GROUP BY r.member_id,mp.MEMBER_NAME) a ,REST_REVIEW b WHERE a.member_id = b.member_id AND a.rank = 1 ORDER BY 3 asc, 2..

5월 식품들의 총매출 조회하기 -- 코드를 입력하세요 SELECT fr.product_id, fr.PRODUCT_NAME, sum(fr.price * fo.amount) as total_sales FROM FOOD_PRODUCT fr ,FOOD_ORDER fo where fr.product_id = fo.product_id and extract(month from fo.PRODUCE_DATE) = 05 group by fr.product_id, fr.PRODUCT_NAME order by 3 desc, 1 asc

주문량이 많은 아이스크림들 조회하기 SELECT c.flavor FROM ( SELECT a.flavor, (a.total_order + b.total_order) total_order FROM -- 상반기 총 주문량 (select f.flavor, f.total_order from FIRST_HALF f) a, -- 7월 맛 별 총 주문량 (select flavor, sum(total_order) total_order from july group by flavor ) b where a.flavor = b.flavor order by 2 desc ) c where rownum

입양 시각 구하기(2) select b.hour HOUR, NVL(a.count,0) as count from (select to_char(DATETIME, 'HH24') hour, count(*) count from ANIMAL_OUTS GROUP BY to_char(DATETIME, 'HH24')) a, (select (level-1) as hour from dual connect by level

년,월,성별 별 상품 구매 회원 수 구하기 SELECT extract(year from o.SALES_DATE) as year ,extract(month from o.SALES_DATE) as month ,u.gender as gender ,count(distinct u.user_id) as users FROM USER_INFO u ,ONLINE_SALE o WHERE u.user_id = o.user_id AND u.gender is not null GROUP BY extract(year from o.SALES_DATE) ,extract(month from o.SALES_DATE) ,u.gender order by 1,2,3 asc count 함수 내에 distinct 써도 가능 !!!

자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기 SELECT CAR_ID, '대여중' AS AVAILABILITY FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY WHERE CAR_ID IN (SELECT CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY WHERE (START_DATE = DATE'2022-10-16') GROUP BY CAR_ID) GROUP BY CAR_ID UNION SELECT CAR_ID, '대여 가능' AS AVAILABILITY FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY WHERE CAR_ID NOT IN (SELECT CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL..

카테고리 별 도서 판매량 집계하기 -- 코드를 입력하세요 SELECT b.category, sum(bs.sales) as total_sales FROM BOOK b ,BOOK_SALES bs WHERE b.book_id = bs.book_id AND extract(year from bs.sales_date) = 2022 AND extract(month from bs.sales_date) = 01 group by b.category order by 1 asc

식품분류별 가장 비싼 식품의 정보 조회하기 SELECT CATEGORY, PRICE AS MAX_PRICE , PRODUCT_NAME FROM FOOD_PRODUCT WHERE (CATEGORY, PRICE) IN (-- '과자', '국', '김치', '식용유' 에 해당하는 가장 비싼 금액 SELECT CATEGORY, MAX(PRICE) FROM FOOD_PRODUCT WHERE CATEGORY IN ('과자', '국', '김치', '식용유') GROUP BY CATEGORY) ORDER BY 2 DESC

즐겨찾기가 가장 많은 식당 정보 출력하기 -- 코드를 입력하세요 SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES FROM REST_INFO WHERE (FOOD_TYPE,FAVORITES) IN (SELECT R.FOOD_TYPE, max(R.favorites) FROM REST_INFO R GROUP BY R.FOOD_TYPE) order by 1 desc 식당 종류별 즐겨찾기가 가장 많은 정보를 먼저 찾은 후, 해당 정보를 가진 식당 정보를 출력했다