soominkim Study
article thumbnail
[Programmers] 오프라인/온라인 판매 데이터 통합하기

▶MySQL SELECT DATE_FORMAT(SALES_DATE,'%Y-%m-%d') AS SALES_DATE ,PRODUCT_ID ,USER_ID ,SALES_AMOUNT FROM ONLINE_SALE WHERE SALES_DATE BETWEEN DATE_FORMAT('2022-03-01 00:00:00','%Y-%m-%d HH24:MI:SS') AND DATE_FORMAT('2022-03-31 23:59:59','%Y-%m-%d HH24:MI:SS') UNION ALL SELECT DATE_FORMAT(SALES_DATE,'%Y-%m-%d') AS SALES_DATE ,PRODUCT_ID ,NULL AS USER_ID ,SALES_AMOUNT FROM OFFLINE_SALE WHERE SALES_D..

article thumbnail
[Programmers] 중복 제거하기

▶MySQL SELECT COUNT(DISTINCT NAME) AS COUNT FROM ANIMAL_INS ▶Oracle SELECT COUNT(DISTINCT NAME) AS COUNT FROM ANIMAL_INS

article thumbnail
[Programmers] 동물 수 구하기

▶MySQL SELECT COUNT(*) AS COUNT FROM ANIMAL_INS ▶Oracle SELECT COUNT(*) AS COUNT FROM ANIMAL_INS

article thumbnail
[Programmers] 최솟값 구하기

▶MySQL SELECT MIN(DATETIME) AS 시간 FROM ANIMAL_INS ▶Oracle SELECT MIN(DATETIME) AS 시간 FROM ANIMAL_INS

article thumbnail
[Programmers] 최댓값 구하기

▶MySQL SELECT MAX(DATETIME) AS 시간 FROM ANIMAL_INS ▶Oracle SELECT MAX(DATETIME) AS 시간 FROM ANIMAL_INS

article thumbnail
[Programmers] 가장 비싼 상품 구하기

▶MySQL SELECT MAX(PRICE) AS MAX_PRICE FROM PRODUCT ▶Oracle SELECT MAX(PRICE) AS MAX_PRICE FROM PRODUCT

article thumbnail
[Programmers] 가격이 제일 비싼 식품의 정보 출력하기

▶MySQL SELECT * FROM FOOD_PRODUCT WHERE PRICE = (SELECT MAX(PRICE) FROM FOOD_PRODUCT) ▶Oracle SELECT * FROM FOOD_PRODUCT WHERE PRICE = (SELECT MAX(PRICE) FROM FOOD_PRODUCT) MySQL의 경우 LIMIT을 활용한 풀이도 가능합니다. Oracle은 ROWNUM과 FETCH를 활용해서도 가능합니다. ▶LIMIT SELECT * FROM FOOD_PRODUCT ORDER BY PRICE DESC LIMIT 1; ▶ROWNUM SELECT * FROM (SELECT * FROM FOOD_PRODUCT ORDER BY PRICE DESC) WHERE ROWNUM

article thumbnail
[Programmers] 서울에 위치한 식당 목록 출력하기

▶MySQL SELECT RI.REST_ID ,RI.REST_NAME ,RI.FOOD_TYPE ,RI.FAVORITES ,RI.ADDRESS ,ROUND(AVG(RR.REVIEW_SCORE),2) AS SCORE FROM REST_INFO RI JOIN REST_REVIEW RR ON RI.REST_ID = RR.REST_ID WHERE RI.ADDRESS LIKE '서울%' GROUP BY RI.REST_ID,RI.REST_NAME,RI.FOOD_TYPE,RI.FAVORITES,RI.ADDRESS ORDER BY SCORE DESC,RI.FAVORITES DESC; ▶Oracle SELECT RI.REST_ID ,RI.REST_NAME ,RI.FOOD_TYPE ,RI.FAVORITES ,RI.ADDRE..

article thumbnail
[Programmers] 진료과별 총 예약 횟수 출력하기

▶MySQL SELECT MCDP_CD AS "진료과코드" ,COUNT(*) AS "5월예약건수" FROM APPOINTMENT WHERE DATE_FORMAT(APNT_YMD,'%Y%m') = '202205' GROUP BY MCDP_CD ORDER BY COUNT(*),MCDP_CD ▶Oracle SELECT MCDP_CD AS "진료과코드" ,COUNT(*) AS "5월예약건수" FROM APPOINTMENT WHERE TO_CHAR(APNT_YMD,'YYYYMM') = '202205' GROUP BY MCDP_CD ORDER BY COUNT(*),MCDP_CD

article thumbnail
[Programmers] 성분으로 구분한 아이스크림 총 주문량

▶MySQL SELECT II.INGREDIENT_TYPE ,SUM(FH.TOTAL_ORDER) AS TOTAL_ORDER FROM FIRST_HALF FH JOIN ICECREAM_INFO II ON FH.FLAVOR = II.FLAVOR GROUP BY II.INGREDIENT_TYPE ORDER BY TOTAL_ORDER ASC; ▶Oracle SELECT II.INGREDIENT_TYPE ,SUM(FH.TOTAL_ORDER) AS TOTAL_ORDER FROM FIRST_HALF FH ,ICECREAM_INFO II WHERE FH.FLAVOR = II.FLAVOR GROUP BY II.INGREDIENT_TYPE ORDER BY TOTAL_ORDER ASC;

article thumbnail
[Programmers] 동명 동물 수 찾기

▶MySQL SELECT NAME ,COUNT(NAME) AS COUNT FROM ANIMAL_INS GROUP BY NAME HAVING COUNT(NAME) >= 2 ORDER BY NAME; ▶Oracle SELECT NAME ,COUNT(NAME) AS COUNT FROM ANIMAL_INS GROUP BY NAME HAVING COUNT(NAME) >= 2 ORDER BY NAME; 집계 함수의 COUNT는 다음과 같이 서로 다른 결과 값을 출력합니다. COUNT(*) : NULL을 포함한 모든 행의 수 COUNT(컬럼) : NULL을 미포함한 모든 행의 수 COUNT(DISTINCT 컬럼) : 중복된 컬럼을 제외한 모든 행의 수

article thumbnail
[Programmers] 재구매가 일어난 상품과 회원 리스트 구하기

▶MySQL SELECT USER_ID , PRODUCT_ID FROM ONLINE_SALE GROUP BY USER_ID, PRODUCT_ID HAVING COUNT(*) > 1 ORDER BY USER_ID, PRODUCT_ID DESC; ▶Oracle SELECT USER_ID , PRODUCT_ID FROM ONLINE_SALE GROUP BY USER_ID, PRODUCT_ID HAVING COUNT(*) > 1 ORDER BY USER_ID, PRODUCT_ID DESC;

검색 태그