/*
FIRST_HALF ICECREAM_INFO
상반기 동안 각 아이스크림 성분 타입과 성분 타입에 대한 아이스크림의 총주문량을 총주문량이 작은 순서대로 조회
INGREDIENT_TYPE TOTAL_ORDER
*/SELECT b.INGREDIENT_TYPE, SUM(a.TOTAL_ORDER) AS TOTAL_ORDER
FROM FIRST_HALF a
JOIN ICECREAM_INFO b
ON a.FLAVOR = b.FLAVOR
GROUPBY b.INGREDIENT_TYPE
ORDERBY TOTAL_ORDER ASC
/*
FIRST_HALF ICECREAM_INFO
상반기 아이스크림 총주문량이 3,000보다 높으면서
아이스크림의 주 성분이 과일인 아이스크림의 맛을
총주문량이 큰 순서대로 조회
*/SELECT FLAVOR
FROM FIRST_HALF
WHERE FLAVOR IN (
SELECTDISTINCT FLAVOR
FROM ICECREAM_INFO
WHERE INGREDIENT_TYPE ='fruit_based'
)
GROUPBY FLAVOR
HAVINGSUM(TOTAL_ORDER) >3000ORDERBYSUM(TOTAL_ORDER) DESC-- SELECT INGREDIENT_TYPE-- FROM ICECREAM_INFO -- WHERE INGREDIENT_TYPE = 'fruit_based'
/*
PATIENT : 환자
DOCTOR : 의사
APPOINTMENT : 진료 예약 목록
PATIENT, DOCTOR 그리고 APPOINTMENT 테이블에서
2022년 4월 13일 취소되지 않은 흉부외과(CS) 진료 예약 내역을 조회
진료예약번호, 환자이름, 환자번호, 진료과코드, 의사이름, 진료예약일시 항목 출력
결과는 진료예약일시를 기준으로 오름차순 정렬
*/SELECT APNT_NO,
(
SELECTDISTINCT PT_NAME
FROM PATIENT a
WHERE a.PT_NO = c.PT_NO
) AS PT_NAME,
PT_NO,
MCDP_CD,
(
SELECTDISTINCT DR_NAME
FROM DOCTOR b
WHERE b.DR_ID = c.MDDR_ID
) AS DR_NAME,
APNT_YMD
FROM APPOINTMENT c
WHERE MCDP_CD ='CS'-- AND APNT_YMD LIKE '2022-04-13%' --이건 안됨. 날짜형이기 때문AND TO_CHAR(APNT_YMD,'YYYY-MM-DD') ='2022-04-13'AND APNT_CNCL_YN ='N'-- AND APNT_CNCL_YMD IS NULL --선택 가능ORDERBY APNT_YMD ASC;
/*
DOCTOR
진료과가 흉부외과(CS)이거나 일반외과(GS)인 의사
의사의 이름, 의사ID, 진료과, 고용일자를 조회하는 SQL
고용일자를 기준으로 내림차순 정렬하고, 고용일자가 같다면 이름을 기준으로 오름차순 정렬
*/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')
ORDERBY HIRE_YMD DESC, DR_NAME ASC
/*
APPOINTMENT
2022년 5월에 예약한 환자 수를 진료과코드 별로 조회하는 SQL
컬럼명은 '진료과 코드', '5월예약건수'로 지정
결과는 진료과별 예약한 환자 수를 기준으로 오름차순 정렬하고,
예약한 환자 수가 같다면 진료과 코드를 기준으로 오름차순 정렬
*/-- SELECT MCDP_CD AS 진료과코드, COUNT(MCDP_CD) AS 5월예약건수-- FROM APPOINTMENT-- WHERE TO_CHAR(APNT_YMD, 'YYYY-MM') = '2022-05'-- GROUP BY MCDP_CD-- ORDER BY 5월예약건수 ASC, 진료과코드 ASCSELECT MCDP_CD AS 진료과코드, COUNT(MCDP_CD) AS "5월예약건수"
FROM APPOINTMENT
WHERE TO_CHAR(APNT_YMD, 'YYYY-MM') ='2022-05'GROUPBY MCDP_CD
ORDERBY "5월예약건수" ASC, 진료과코드 ASC--AS "5월예약건수" 처럼 숫자가 맨앞에 붙으면 따옴표로 감싸두어야 한다.
/*
PATIENT
12세 이하인 여자환자의
환자이름, 환자번호, 성별코드, 나이, 전화번호를 조회하는 SQL
전화번호가 없는 경우, 'NONE'으로 출력시켜 주시고
결과는 나이를 기준으로 내림차순 정렬
나이 같다면 환자이름을 기준으로 오름차순 정렬
*/SELECT PT_NAME, PT_NO, GEND_CD, AGE, (
CASEWHEN TLNO ISNULLTHEN'NONE'ELSE TLNO
END
) AS TLNO
FROM PATIENT
WHERE AGE <=12AND GEND_CD ='W'ORDERBY AGE DESC, PT_NAME ASC
/*
ONLINE_SALE OFFLINE_SALE
2022년 3월의 오프라인/온라인 상품 판매 데이터의 판매 날짜, 상품ID, 유저ID, 판매량을 출력
OFFLINE_SALE 테이블의 판매 데이터의 USER_ID 값은 NULL 로 표시
결과는 판매일을 기준으로 오름차순 정렬해주시고
판매일이 같다면 상품 ID를 기준으로 오름차순,
상품ID까지 같다면 유저 ID를 기준으로 오름차순 정렬
*/-- 정답지..?-- SELECT SALES_DATE, PRODUCT_ID, USER_ID USER_ID, SALES_AMOUNT-- FROM (SELECT TO_CHAR(SALES_DATE, 'YYYY-MM-DD') SALES_DATE, -- PRODUCT_ID, -- USER_ID,-- SALES_AMOUNT -- FROM ONLINE_SALE-- UNION ALL -- SELECT TO_CHAR(SALES_DATE, 'YYYY-MM-DD') SALES_DATE,-- PRODUCT_ID, -- NULL USER_ID, -- SALES_AMOUNT -- FROM OFFLINE_SALE)-- WHERE SALES_DATE LIKE '2022-03%'-- ORDER BY 1, 2, 3SELECT
TO_CHAR(SALES_DATE, 'YYYY-MM-DD') AS SALES_DATE,
PRODUCT_ID,
-- 왜 CASE로 나눈것은 틀린 결과가 되는거지???-- (-- CASE-- WHEN USER_ID IS NULL THEN 'NULL'-- WHEN USER_ID = '' THEN 'NULL'-- ELSE TO_CHAR(USER_ID)-- END-- ) AS USER_ID,
USER_ID,
SALES_AMOUNT
FROM (
SELECT USER_ID, PRODUCT_ID, SALES_AMOUNT, SALES_DATE
FROM ONLINE_SALE
UNIONALLSELECTNULLAS USER_ID, PRODUCT_ID, SALES_AMOUNT, SALES_DATE
FROM OFFLINE_SALE
) a
WHERE TO_CHAR(SALES_DATE, 'YYYY-MM') ='2022-03'ORDERBY SALES_DATE ASC, PRODUCT_ID ASC, USER_ID ASC;
/*
ONLINE_SALE
동일한 회원이 동일한 상품을 재구매한 데이터를 구하여,
재구매한 회원 ID와 재구매한 상품 ID를 출력하는 SQL문
결과는 회원 ID를 기준으로 오름차순 정렬해주시고
회원 ID가 같다면 상품 ID를 기준으로 내림차순 정렬
*/SELECT USER_ID, PRODUCT_ID
FROM ONLINE_SALE
GROUPBY USER_ID, PRODUCT_ID
HAVINGCOUNT(*) >=2ORDERBY USER_ID, PRODUCT_ID DESC
/*
USER_INFO
GENDER 컬럼은 비어있거나 0 또는 1의 값을 가지며
0인 경우 남자를, 1인 경우는 여자를 나타냅니다.
USER_INFO 테이블에서 2021년에 가입한 회원 중 나이가 20세 이상 29세 이하인 회원이 몇 명인지 출력하는 SQL문을 작성해주세요.
*/SELECTCOUNT(*) AS USERS
FROM USER_INFO
WHERE TO_CHAR(JOINED, 'YYYY') ='2021'AND AGE BETWEEN20AND29;
-- BETWEEN A AND B 활용법 다시 생각해보기
/*
USER_INFO ONLINE_SALE
USER_INFO 테이블과 ONLINE_SALE 테이블에서 2021년에 가입한 전체 회원들 중
상품을 구매한 회원수와 상품을 구매한 회원의 비율
(=2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수)
년, 월 별로 출력하는 SQL
상품을 구매한 회원의 비율은 소수점 두번째자리에서 반올림
전체 결과는 년을 기준으로 오름차순 정렬해주시고
년이 같다면 월을 기준으로 오름차순 정렬
*//*
-- SELECT
-- TO_CHAR(b.SALES_DATE, 'YYYY') AS YEAR,
-- TO_CHAR(b.SALES_DATE, 'MM') AS MONTH,
-- a.USER_ID AS PUCHASED_USERS,
-- ROUND(
-- (10.5)
-- , 1) AS PUCHASED_RATIO
-- FROM USER_INFO a
-- JOIN ONLINE_SALE b
-- ON a.USER_ID = b.USER_ID
-- WHERE TO_CHAR(a.JOINED, 'YYYY') = 2021
-- SELECT USER_ID
-- FROM USER_INFO
-- WHERE TO_CHAR(JOINED, 'YYYY') = 2021
*/------------------- 답이랑 내꺼랑 다르네... 왜지?-- SELECT -- TO_CHAR(sales_date,'YYYY') AS year,-- TO_NUMBER(TO_CHAR(sales_date,'MM')) AS month,-- -- 2021년에 가입한 전체 회원들 중 상품을 구매한 회원수-- COUNT(DISTINCT os.user_id) AS puchased_users,-- -- 소수점 두번째자리에서 반올림: -- -- 2021년에 가입한 회원 중 상품을 구매한 회원수/2021년에 가입한 전체 회원 수(인라인 뷰)-- ROUND(COUNT(DISTINCT os.user_id)/(-- SELECT COUNT(DISTINCT user_id) -- FROM user_info -- WHERE TO_CHAR(joined,'YYYY') = '2021'-- ),1) AS puchased_ratio-- FROM online_sale os LEFT JOIN user_info ui-- ON os.user_id = ui.user_id-- WHERE TO_CHAR(ui.joined,'YYYY') = '2021'-- GROUP BY TO_CHAR(sales_date,'YYYY'),TO_NUMBER(TO_CHAR(sales_date,'MM'))-- ORDER BY year, month---------------------SELECTYEAR, MONTH,
COUNT(DISTINCT USER_ID) AS PUCHASED_USERS,
ROUND(
COUNT(DISTINCT USER_ID) / (
SELECTCOUNT(*)
FROM (
SELECT USER_ID
FROM USER_INFO
WHERE TO_CHAR(JOINED, 'YYYY') ='2021'
)
)
, 1) AS PUCHASED_RATIO
FROM (
SELECT
TO_NUMBER(TO_CHAR(SALES_DATE, 'YYYY')) ASYEAR,
TO_NUMBER(TO_CHAR(SALES_DATE, 'MM')) ASMONTH,
USER_ID
FROM ONLINE_SALE
WHERE USER_ID IN (
SELECT USER_ID
FROM USER_INFO
WHERE TO_CHAR(JOINED, 'YYYY') ='2021'
)
)
GROUPBYYEAR, MONTHORDERBYYEAR, MONTH
/*
USER_INFO ONLINE_SALE
년, 월, 성별 별로 상품을 구매한 회원수를 집계하는 SQL
결과는 년, 월, 성별을 기준으로 오름차순 정렬
성별 정보가 없는 경우 결과에서 제외
*/SELECT
TO_CHAR(b.SALES_DATE,'YYYY') ASYEAR,
TO_NUMBER(TO_CHAR(b.SALES_DATE,'MM')) ASMONTH,
a.GENDER AS GENDER,
COUNT(DISTINCT a.USER_ID) AS USERS
FROM USER_INFO a JOIN ONLINE_SALE b
ON a.USER_ID = b.USER_ID
WHERE a.GENDER ISNOTNULLGROUPBY
TO_CHAR(b.SALES_DATE,'YYYY'),
TO_NUMBER(TO_CHAR(b.SALES_DATE,'MM')),
a.GENDER
ORDERBYYEAR, MONTH, GENDER
--TOCHAR(date, 'FMMM')
/*
PRODUCT
만원 단위의 가격대 별로 상품 개수를 출력하는 SQL 문
이때 컬럼명은 각각 컬럼명은 PRICE_GROUP, PRODUCTS로 지정
가격대 정보는 각 구간의 최소금액
(10,000원 이상 ~ 20,000 미만인 구간인 경우 10,000)으로 표시
결과는 가격대를 기준으로 오름차순 정렬
*/SELECTDISTINCT ((FLOOR(PRICE /10000))*10000) AS PRICE_GROUP,
COUNT(*) AS PRODUCTS
FROM PRODUCT
GROUPBY (FLOOR(PRICE /10000))
ORDERBY PRICE_GROUP ASC
/*
PRODUCT
상품 카테고리 코드(PRODUCT_CODE 앞 2자리) 별 상품 개수를 출력
결과는 상품 카테고리 코드를 기준으로 오름차순 정렬
*/SELECT SUBSTR(PRODUCT_CODE, 0, 2) AS CATEGORY, COUNT(SUBSTR(PRODUCT_CODE, 0, 2)) AS PRODUCTS
FROM PRODUCT
GROUPBY SUBSTR(PRODUCT_CODE, 0, 2)
ORDERBY CATEGORY;
/*
REST_INFO
테이블에서 리뷰를 가장 많이 작성한 회원의 리뷰들을 조회하는 SQL
회원 이름, 리뷰 텍스트, 리뷰 작성일이 출력되도록 작성
결과는 리뷰 작성일을 기준으로 오름차순,
리뷰 작성일이 같다면 리뷰 텍스트를 기준으로 오름차순
*/SELECT
(
SELECT MEMBER_NAME
FROM MEMBER_PROFILE
WHERE MEMBER_ID = a.MEMBER_ID
) AS MEMBER_NAME,
REVIEW_TEXT,
TO_CHAR(REVIEW_DATE, 'YYYY-MM-DD') AS REVIEW_DATE
FROM REST_REVIEW a
WHERE MEMBER_ID IN (SELECT MEMBER_ID
FROM REST_REVIEW
GROUPBY MEMBER_ID
HAVINGCOUNT(MEMBER_ID) = (SELECTMAX(COUNTED)
FROM (
SELECTCOUNT(MEMBER_ID) AS COUNTED
FROM REST_REVIEW
GROUPBY MEMBER_ID
)))
ORDERBY REVIEW_DATE, REVIEW_TEXT;
/*
REST_INFO
음식종류별로 즐겨찾기수가 가장 많은 식당의 음식 종류, ID, 식당 이름, 즐겨찾기수를 조회하는 SQL
결과는 음식 종류를 기준으로 내림차순 정렬
*/SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES
FROM REST_INFO
WHERE (FOOD_TYPE, FAVORITES) IN (
SELECT FOOD_TYPE, MAX(FAVORITES) AS FAVORITES
FROM REST_INFO
GROUPBY FOOD_TYPE
)
ORDERBY FOOD_TYPE DESC;
