입금후 변신하는 헐리우드 코딩 스타

참조 : https://jmi7313.tistory.com/12 fm 안 붙여도 정답 처리 되기는 하지만... --Mysql SELECT YEAR (SALES_DATE) AS YEAR, MONTH (SALES_DATE) AS MONTH, GENDER , COUNT ( DISTINCT ONLINE_SALE.USER_ID ) AS USERS FROM ONLINE_SALE INNER JOIN USER_INFO ON USER_INFO.USER_ID = ONLINE_SALE.USER_ID WHERE GENDER IS NOT NULL GROUP BY YEAR, MONTH, GENDER ORDER BY YEAR, MONTH, GENDER ; --Oracle SELECT YEAR, TO_NUMBER( MONTH ) AS MONTH, GENDER , COUNT ( DISTINCT USER_ID ) AS USERS FROM ( SELECT TO_CHAR(SALES_DATE , 'YYYY' ) AS YEAR, TO_CHAR(SALES_DATE , 'MM' ) AS MONTH, GENDER , USER_INFO.USER_ID FROM USER_INFO , ONLINE_SALE WHERE USER_INFO.USER_ID = ONLINE_SALE.USER_ID AND GENDER IS NOT NULL ) GROUP BY YEAR, MONTH, GENDER ORDER BY YEAR ,MONTH, GENDER ;

--Mysql SELECT MAX ( DATETIME ) FROM ANIMAL_INS --Oracle SELECT MAX ( DATETIME ) FROM ANIMAL_INS

--Mysql SELECT USER_ID , PRODUCT_ID FROM ONLINE_SALE GROUP BY USER_ID , PRODUCT_ID HAVING COUNT ( * ) >= 2 ORDER BY USER_ID , PRODUCT_ID DESC; --Oracle SELECT USER_ID , PRODUCT_ID FROM ONLINE_SALE GROUP BY USER_ID , PRODUCT_ID HAVING COUNT ( * ) >= 2 ORDER BY USER_ID , PRODUCT_ID DESC;

--Mysql SELECT     CAR_ID,     CAR_TYPE,     DAILY_FEE,     OPTIONS FROM     CAR_RENTAL_COMPANY_CAR WHERE       OPTIONS LIKE '%네비게이션%' ORDER BY     CAR_ID DESC --Oracle SELECT     CAR_ID,     CAR_TYPE,     DAILY_FEE,     OPTIONS FROM     CAR_RENTAL_COMPANY_CAR WHERE       OPTIONS LIKE '%네비게이션%' ORDER BY     CAR_ID DESC 이거 왜 틀릴 확률이 30%지...

--Mysql SELECT     BOARD_ID,     WRITER_ID,     TITLE,     PRICE,     CASE         WHEN             STATUS = 'SALE' THEN '판매중'         WHEN             STATUS = 'RESERVED' THEN '예약중'         WHEN             STATUS = 'DONE' THEN '거래완료'     END         STATUS FROM     USED_GOODS_BOARD WHERE     BOARD_ID         IN (             SELECT                 BOARD_ID             FROM                 USED_GOODS_BOARD             WHERE                 CREATED_DATE = '2022-10-05'         ) ORDER BY     BOARD_ID DESC ; --Oracle SELECT     BOARD_ID,     WRITER_ID,     TITLE,     PRICE,     CASE         WHEN             STATUS = 'SALE' THEN '판매중'         WHEN             STATUS = 'RESERVED' THEN '예약중'         WHEN             STATUS = 'DONE' THEN '거래완료'     END         AS STATUS FROM     USED_GOODS_BOARD WHERE     TO_CHAR(CREATED_DATE, 'YYYYMMDD' )

--Mysql SELECT FLOOR( PRICE / 10000 ) * 10000 AS PRICE_GROUP , COUNT ( * ) AS PRODUCTS FROM PRODUCT GROUP BY PRICE_GROUP ORDER BY PRICE_GROUP ASC --Oracle SELECT RPAD(SUBSTR( PRICE , 0 , 1 ) , 5 , 0 ) AS PRICE_GROUP , COUNT ( * ) FROM PRODUCT GROUP BY SUBSTR( PRICE , 0 , 1 ) ORDER BY SUBSTR( PRICE , 0 , 1 ) --Oracle SELECT SUBSTR( PRICE , 1 , 1 ) * 10000 AS PRICE_GROUP , COUNT ( * ) AS PRODUCTS FROM PRODUCT GROUP BY SUBSTR( PRICE , 1 , 1 ) * 10000 ORDER BY SUBSTR( PRICE , 1 , 1 ) * 10000

--Mysql SELECT USER_ID , NICKNAME , SUM (PRICE) AS TOTAL_SALES FROM USED_GOODS_BOARD AS B JOIN USED_GOODS_USER AS U ON B. WRITER_ID = U. USER_ID WHERE STATUS = 'DONE' # 완료된 중고 거래 GROUP BY USER_ID HAVING TOTAL_SALES >= 700000 # 총금액 70 만원 이상 ORDER BY TOTAL_SALES ASC; --Oracle SELECT U. USER_ID AS USER_ID , U. NICKNAME AS NICKNAME , SUM (B. PRICE ) AS TOTAL_SALES FROM USED_GOODS_BOARD B JOIN USED_GOODS_USER U ON B. WRITER_ID = U. USER_ID WHERE STATUS = 'DONE' GROUP BY USER_ID , NICKNAME HAVING SUM (PRICE) >= 700000 ORDER BY TOTAL_SALES

--Mysql SELECT B. BOOK_ID AS BOOK_ID , A. AUTHOR_NAME AS AUTHOR_NAME , DATE_FORMAT(B. PUBLISHED_DATE , '%Y-%m-%d' ) AS PUBLISHED_DATE FROM BOOK B LEFT JOIN AUTHOR A ON B. AUTHOR_ID = A. AUTHOR_ID WHERE B. CATEGORY = ' 경제 ' ORDER BY PUBLISHED_DATE ; --Oracle SELECT B. BOOK_ID AS BOOK_ID , A. AUTHOR_NAME AS AUTHOR_NAME , TO_CHAR(B. PUBLISHED_DATE , 'yyyy-mm-dd' ) AS PUBLISHED_DATE FROM BOOK B JOIN AUTHOR A ON B. AUTHOR_ID = A. AUTHOR_ID WHERE B. CATEGORY = ' 경제 ' ORDER BY PUBLISHED_DATE ;

--Mysql SELECT ORDER_ID , PRODUCT_ID , DATE_FORMAT( OUT_DATE , '%Y-%m-%d' ) as OUT_DATE , CASE WHEN DATE_FORMAT( OUT_DATE , '%m-%d' ) <= '05-01' THEN ' 출고완료 ' WHEN DATE_FORMAT( OUT_DATE , '%m-%d' ) > '05-01' THEN ' 출고대기 ' ELSE ' 출고미정 ' END AS 출고여부 FROM FOOD_ORDER ORDER BY ORDER_ID ASC; --Oracle SELECT ORDER_ID , PRODUCT_ID , TO_CHAR( OUT_DATE , 'YYYY-MM-DD' ) AS OUT_DATE , CASE WHEN TO_NUMBER(TO_CHAR( OUT_DATE , 'YYYYMMDD' )) <= '20220501' THEN ' 출고완료 ' WHEN TO_NUMBER(TO_CHAR( OUT_DATE , 'YYYYMMDD' )) > '20220501' THEN ' 출고대기 ' ELSE ' 출고미정 ' END AS " 출고여부 " FROM

--Mysql SELECT B. CATEGORY , SUM (SALES) AS TOTAL_SALES FROM BOOK B , BOOK_SALES BS WHERE B. BOOK_ID = BS. BOOK_ID AND BS. SALES_DATE BETWEEN '2022-01-01' AND '2022-01-31' GROUP BY B. CATEGORY ORDER BY B. CATEGORY ASC --Oracle SELECT B. CATEGORY , SUM (SALES) AS TOTAL_SALES FROM BOOK B INNER JOIN BOOK_SALES BS ON B. BOOK_ID = BS. BOOK_ID WHERE TO_CHAR(BS. SALES_DATE , 'YYYYMM' ) = '202201' GROUP BY B. CATEGORY ORDER BY B. CATEGORY ASC

--Mysql SELECT P. PRODUCT_CODE , (P. PRICE * SUM (SALES_AMOUNT)) AS SALES FROM PRODUCT P JOIN OFFLINE_SALE O ON P. PRODUCT_ID = O. PRODUCT_ID GROUP BY P. PRODUCT_CODE ORDER BY SALES DESC, P. PRODUCT_CODE ; --Oracle SELECT P. PRODUCT_CODE , SUM (P. PRICE * SALES_AMOUNT) AS SALES FROM PRODUCT P INNER JOIN OFFLINE_SALE O ON P. PRODUCT_ID = O. PRODUCT_ID GROUP BY P. PRODUCT_CODE ORDER BY SALES DESC, P. PRODUCT_CODE ;

--Mysql SELECT MCDP_CD AS ' 진료과코드 ' , COUNT ( * ) AS '5 월예약건수 ' FROM APPOINTMENT WHERE APNT_YMD LIKE '2022-05%' GROUP BY MCDP_CD ORDER BY COUNT ( MCDP_CD ) ASC, MCDP_CD ASC; --Oracle SELECT MCDP_CD AS " 진료과코드 " , COUNT ( * ) AS "5 월예약건수 " FROM APPOINTMENT GROUP BY MCDP_CD , TO_CHAR( APNT_YMD , 'MM' ) HAVING TO_CHAR( APNT_YMD , 'MM' ) IN ( '05' ) ORDER BY "5 월예약건수 " ASC , " 진료과코드 " ASC

--Mysql SELECT PT_NAME , PT_NO , GEND_CD , AGE , IFNULL( TLNO , 'NONE' ) AS TLNO FROM PATIENT WHERE AGE <= 12 AND GEND_CD = 'W' ORDER BY AGE DESC, PT_NAME ASC; --Oracle SELECT PT_NAME , PT_NO , GEND_CD , AGE , NVL( TLNO , 'NONE' ) AS TLNO FROM PATIENT WHERE AGE <= 12 AND GEND_CD = 'W' ORDER BY AGE DESC, PT_NAME ASC;

--Mysql SELECT LEFT (PRODUCT_CODE , 2 ) AS CATEGORY , COUNT (PRODUCT_ID) FROM PRODUCT GROUP BY CATEGORY ORDER BY CATEGORY --Oracle SELECT SUBSTR( PRODUCT_CODE , 0 , 2 ) AS CATEGORY , COUNT ( PRODUCT_ID ) FROM PRODUCT GROUP BY SUBSTR( PRODUCT_CODE , 0 , 2 ) ORDER BY CATEGORY

--Mysql SELECT DR_NAME , DR_ID , MCDP_CD , DATE_FORMAT( HIRE_YMD , '%Y-%m-%d' ) AS HIRE_YMD FROM DOCTOR WHERE MCDP_CD = 'CS' OR MCDP_CD = 'GS' ORDER BY HIRE_YMD DESC, DR_NAME ASC; --Oracle SELECT DR_NAME , DR_ID , MCDP_CD , TO_CHAR( HIRE_YMD , 'YYYY-MM-DD' ) AS HIRE_YMD FROM DOCTOR WHERE MCDP_CD = 'CS' OR MCDP_CD = 'GS' ORDER BY HIRE_YMD DESC, DR_NAME ASC; --Oracle SELECT DR_NAME , DR_ID , MCDP_CD , TO_CHAR( HIRE_YMD , 'YYYY-MM-DD' ) AS HIRE_YMD FROM DOCTOR WHERE MCDP_CD IN ( 'CS' , 'GS' ) ORDER BY HIRE_YMD DESC, DR_NAME ASC

--Mysql SELECT HOUR (DATETIME) AS HOUR, COUNT (DATETIME) AS COUNT FROM ANIMAL_OUTS GROUP BY HOUR (DATETIME) HAVING HOUR >= 9 AND HOUR <= 19 ORDER BY HOUR (DATETIME) ASC; --Oracle SELECT TO_NUMBER(TO_CHAR(DATETIME , 'HH24' )) AS HOUR, COUNT (*) AS COUNT FROM ANIMAL_OUTS WHERE TO_CHAR(DATETIME , 'HH24' ) >= '09' AND TO_CHAR(DATETIME , 'HH24' ) <= '19' GROUP BY TO_CHAR(DATETIME , 'HH24' ) ORDER BY HOUR ASC;

--Mysql SELECT COUNT ( * ) AS USERS FROM USER_INFO WHERE AGE >= 20 AND AGE <= 29 AND YEAR (JOINED) = 2021 --Oracle SELECT COUNT ( * ) AS USERS FROM USER_INFO WHERE AGE >= 20 AND AGE <= 29 AND TO_CHAR( JOINED , 'YYYY' ) = '2021'

--Mysql SELECT ANIMAL_ID FROM ANIMAL_INS WHERE NAME IS NULL ORDER BY ANIMAL_ID ; --Oracle SELECT ANIMAL_ID FROM ANIMAL_INS WHERE NAME IS NULL ORDER BY ANIMAL_ID ;

SELECT MAX ( PRICE ) AS MAX_PRICE FROM PRODUCT ;

--Mysql #Mysql 은 Alias 안 써주면 정답 처리 안 됨 SELECT DATE_FORMAT(O. SALES_DATE , '%Y-%m-%d' ) AS SALES_DATE , O. PRODUCT_ID , O. USER_ID , O. SALES_AMOUNT FROM ( SELECT SALES_DATE , PRODUCT_ID , USER_ID , SALES_AMOUNT FROM ONLINE_SALE UNION ALL #UNION 해도 됨 , UNION ALL 은 중복 허용 SELECT SALES_DATE , PRODUCT_ID , null AS USER_ID , SALES_AMOUNT FROM OFFLINE_SALE) AS O WHERE YEAR (O.SALES_DATE) = 2022 AND MONTH (O.SALES_DATE) = 3 ORDER BY SALES_DATE ASC, PRODUCT_ID ASC, USER_ID ASC --Oracle SELECT TO_CHAR( SALES_DATE , 'YYYY-MM-DD' ) AS SALES_DATE , PRODUCT_ID , USER_ID , SALES_AMOUNT FROM ( SELECT SALES_DATE , PRODUCT_ID , USER_ID , SALES_AMOUNT FROM ONLINE_SALE UNION -- 합쳐주기

--Mysql SELECT CAR_ID , ROUND( AVG (DATEDIFF( END_DATE , START_DATE )+ 1 ) , 1 ) AS AVERAGE_DURATION # 소수 두번째에서 할라면 1 ( 2 는 세번째 ) FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY GROUP BY CAR_ID HAVING AVG (DATEDIFF( END_DATE , START_DATE ) + 1 ) >= 7 # 끝나는 날짜 , 시작 날짜 + 1 해야 7 일 이상으로 비교 가능 ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC --Oracle SELECT CAR_ID , ROUND( AVG ( END_DATE - START_DATE + 1 ) , 1 ) AS AVERAGE_DURATION FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY GROUP BY CAR_ID HAVING ROUND( AVG ( END_DATE - START_DATE + 1 ) , 1 ) >= 7 ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC --Oracle 은 DateDiff 대신 그냥 '-' 하면 됨

--Mysql SELECT CONCAT( "/home/grep/src/" , B. BOARD_ID , "/" , F. FILE_ID , F. FILE_NAME , F. FILE_EXT ) AS FILE_PATH FROM USED_GOODS_BOARD AS B JOIN USED_GOODS_FILE AS F ON B. BOARD_ID = F. BOARD_ID WHERE VIEWS = ( SELECT MAX ( VIEWS ) FROM USED_GOODS_BOARD ) ORDER BY F. FILE_ID DESC; --Oracle SELECT '/home/grep/src/' ||B. BOARD_ID || '/' ||F. FILE_ID ||F. FILE_NAME ||F. FILE_EXT AS FILE_PATH FROM USED_GOODS_BOARD B JOIN USED_GOODS_FILE F ON B. BOARD_ID = F. BOARD_ID WHERE VIEWS IN ( SELECT MAX ( VIEWS ) FROM USED_GOODS_BOARD ) ORDER BY F. FILE_ID DESC; 각종 감기로 골골거리고 있습니다. 다들 건강 조심하십시오... 아파도 추워도 힘들어도 잔디를 심기 위한 노력...★

SELECT ANIMAL_ID , NAME , SEX_UPON_INTAKE FROM ANIMAL_INS WHERE NAME IN ( 'Lucy' , 'Ella' , 'Pickle' , 'Rogan' , 'Sabrina' , 'Mitty' ) ORDER BY ANIMAL_ID 피클을 피키라고 써서 ?????????????? 했던 문제...  font issue...

-- MySQL SELECT DISTINCT U. USER_ID , U. NICKNAME , CONCAT_WS(" " , U. CITY , U. STREET_ADDRESS1 , U. STREET_ADDRESS2 ) AS 전체주소 , CONCAT_WS("-" , SUBSTRING (TLNO , 1 , 3 ) , SUBSTRING (TLNO , 4 , 4 ) , SUBSTRING (TLNO , 8 , 4 )) AS 전화번호 FROM USED_GOODS_USER AS U JOIN USED_GOODS_BOARD AS B ON B. WRITER_ID = U. USER_ID WHERE U. USER_ID IN ( SELECT WRITER_ID FROM USED_GOODS_BOARD GROUP BY WRITER_ID HAVING COUNT ( * ) >= 3 ) ORDER BY U. USER_ID DESC; --Oracle SELECT U. USER_ID , U. NICKNAME , U. CITY || ' ' || U. STREET_ADDRESS1 || ' ' || U. STREET_ADDRESS2 AS " 전체주소 " , SUBSTR(U. TLNO , 0 , 3 ) || '-' || SUBSTR(U. TLNO , 4 , 4 ) || '-' || SUBSTR(U. TLNO , 8 ) AS " 전화번호 " FROM USED_GOO

--Mysql SELECT DISTINCT ( C . CAR_ID ) FROM CAR_RENTAL_COMPANY_CAR AS C INNER JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY AS H ON C . CAR_ID = H. CAR_ID WHERE C . CAR_TYPE = ' 세단 ' AND MONTH (H.START_DATE) = 10 ORDER BY C .CAR_ID DESC --Oracle SELECT DISTINCT C . CAR_ID FROM CAR_RENTAL_COMPANY_CAR C INNER JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY H ON C . CAR_ID = H. CAR_ID WHERE C . CAR_TYPE = ' 세단 ' AND TO_CHAR(H. START_DATE , 'MM' ) = '10' ORDER BY C . CAR_ID DESC

--Mysql SELECT MEMBER_ID , MEMBER_NAME , GENDER , DATE_FORMAT( DATE_OF_BIRTH , '%Y-%m-%d' ) AS DATE_OF_BIRTH FROM MEMBER_PROFILE WHERE GENDER = 'W' AND MONTH (DATE_OF_BIRTH) = 3 AND TLNO IS NOT NULL ORDER BY MEMBER_ID ; --Oracle SELECT MEMBER_ID , MEMBER_NAME , GENDER , TO_CHAR( DATE_OF_BIRTH , 'YYYY-MM-DD' ) AS DATE_OF_BIRTH FROM MEMBER_PROFILE WHERE GENDER = 'W' AND TO_CHAR( DATE_OF_BIRTH , 'MM' ) = '03' AND TLNO IS NOT NULL ORDER BY MEMBER_ID ; 다들 코로나 조심 하세요...

--Oracle SELECT * FROM PLACES H WHERE EXISTS ( SELECT * FROM PLACES P WHERE P. HOST_ID = H. HOST_ID GROUP BY HOST_ID HAVING COUNT ( ID ) >= 2 ) ORDER BY ID ASC; --Mysql SELECT * FROM PLACES H WHERE EXISTS ( SELECT * FROM PLACES P WHERE P. HOST_ID = H. HOST_ID GROUP BY HOST_ID HAVING COUNT ( ID ) >= 2 ) ORDER BY ID ASC;

--MySQL SELECT MONTH (START_DATE) AS MONTH, CAR_ID , COUNT (HISTORY_ID) AS RECORDS FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY WHERE CAR_ID IN ( SELECT CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY WHERE MONTH (START_DATE) BETWEEN 8 AND 10 GROUP BY CAR_ID HAVING COUNT (*) >= 5 ) AND MONTH (START_DATE) BETWEEN 8 AND 10 GROUP BY MONTH, CAR_ID ORDER BY MONTH ASC, CAR_ID DESC; --Oracle WITH GETCARID AS ( -- 8~10 월의 최소 1 개 이상인 자동차 아이디만 가져오는 것 SELECT CAR_ID , COUNT ( * ) AS GET_CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY WHERE EXTRACT ( MONTH FROM START_DATE ) IN ( 8 , 9 , 10 ) GROUP BY CAR_ID ORDER BY CAR_ID DESC ) SELECT EXTRACT ( MONTH FROM START_DATE) AS MONTH, C .CAR_ID , COUNT (*) AS RECORDS FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY C INNER JOIN GETCARID G ON C .CAR_ID = G.CAR_ID WHERE G.GET_CAR_ID >= 5 AND