SELECT #1
MEMBER_PROFILE 테이블에서 생일이 3월인 여성 회원의 ID, 이름, 성별, 생년월일을 조회하는 SQL문을 작성해주세요. 이때 전화번호가 NULL인 경우는 출력대상에서 제외시켜 주시고, 결과는 회원ID를 기준으로 오름차순 정렬해주세요.
SELECT MEMBER_ID, MEMBER_NAME, GENDER, DATE_FORMAT(DATE_OF_BIRTH, '%Y-%m-%d') AS DATE_OF_BIRTH
FROM MEMBER_PROFILE
WHERE MONTH(DATE_OF_BIRTH)=3 AND GENDER='W' AND TLNO IS NOT NULL
ORDER BY MEMBER_ID;
date,string #1
CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 대여 시작일이 2022년 9월에 속하는 대여 기록에 대해서 대여 기간이 30일 이상이면 '장기 대여' 그렇지 않으면 '단기 대여' 로 표시하는 컬럼(컬럼명: RENT_TYPE)을 추가하여 대여기록을 출력하는 SQL문을 작성해주세요. 결과는 대여 기록 ID를 기준으로 내림차순 정렬해주세요.
SELECT HISTORY_ID, CAR_ID,
DATE_FORMAT (START_DATE, "%Y-%m-%d") AS START_DATE,
DATE_FORMAT (END_DATE, "%Y-%m-%d") AS END_DATE,
CASE WHEN DATEDIFF(END_DATE, START_DATE) < 30 THEN '단기 대여'
ELSE '장기 대여'
END AS RENT_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE LIKE '2022-09-%'
ORDER BY HISTORY_ID DESC;date, string #2
PRODUCT 테이블에서 상품 카테고리 코드(PRODUCT_CODE 앞 2자리) 별 상품 개수를 출력하는 SQL문을 작성해주세요. 결과는 상품 카테고리 코드를 기준으로 오름차순 정렬해주세요.
SELECT LEFT(PRODUCT_CODE, 2) CATEGORY, COUNT(PRODUCT_ID) PRODUCTS FROM PRODUCT
GROUP BY CATEGORY- left , group by에 order by 필요없음
date,string #3
USED_GOODS_BOARD 테이블에서 2022년 10월 5일에 등록된 중고거래 게시물의 게시글 ID, 작성자 ID, 게시글 제목, 가격, 거래상태를 조회하는 SQL문을 작성해주세요. 거래상태가 SALE 이면 판매중, RESERVED이면 예약중, DONE이면 거래완료 분류하여 출력해주시고, 결과는 게시글 ID를 기준으로 내림차순 정렬해주세요.
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 DATE_FORMAT(CREATED_DATE, '%Y-%m-%d') = '2022-10-05'
ORDER BY BOARD_ID DESC;
date,string #4
CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 평균 대여 기간이 7일 이상인 자동차들의 자동차 ID와 평균 대여 기간(컬럼명: AVERAGE_DURATION) 리스트를 출력하는 SQL문을 작성해주세요. 평균 대여 기간은 소수점 두번째 자리에서 반올림하고, 결과는 평균 대여 기간을 기준으로 내림차순 정렬해주시고, 평균 대여 기간이 같으면 자동차 ID를 기준으로 내림차순 정렬해주세요.
SELECT CAR_ID, ROUND(AVG(DATEDIFF(END_DATE, START_DATE))+1, 1) AS AVERAGE_DURATION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
HAVING AVG(DATEDIFF(END_DATE, START_DATE)+1) >= 7
ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC- 문제 잘 읽고, group by
- datediff()에서 +1 해주기
date,string #5
PRODUCT 테이블에서 상품 카테고리 코드(PRODUCT_CODE 앞 2자리) 별 상품 개수를 출력하는 SQL문을 작성해주세요. 결과는 상품 카테고리 코드를 기준으로 오름차순 정렬해주세요.
SELECT LEFT(PRODUCT_CODE, 2) CATEGORY, COUNT(PRODUCT_ID) PRODUCTS FROM PRODUCT
GROUP BY CATEGORY- 함수: left, distinct, ifname , ~ limit 1
subquery #1
FOOD_PRODUCT 테이블에서 가격이 제일 비싼 식품의 식품 ID, 식품 이름, 식품 코드, 식품분류, 식품 가격을 조회하는 SQL문을 작성해주세요.
FOOD_PRODUCT 테이블에서 가격이 제일 비싼 식품의 식품 ID, 식품 이름, 식품 코드, 식품분류, 식품 가격을 조회하는 SQL문을 작성해주세요.- 제일 ~ 한 것을 고르시오 : max()
- 제일 ~ 한 것의 ~들을 고르시오 : ( max() )
group by #1
ONLINE_SALE 테이블에서 동일한 회원이 동일한 상품을 재구매한 데이터를 구하여, 재구매한 회원 ID와 재구매한 상품 ID를 출력하는 SQL문을 작성해주세요. 결과는 회원 ID를 기준으로 오름차순 정렬해주시고 회원 ID가 같다면 상품 ID를 기준으로 내림차순 정렬해주세요.
SELECT USER_ID, PRODUCT_ID FROM ONLINE_SALE
GROUP BY USER_ID, PRODUCT_ID
HAVING COUNT(*) >= 2
ORDER BY USER_ID, PRODUCT_ID DESC- 두 개 이상 항목이 반복되는
group by #2
상반기 동안 각 아이스크림 성분 타입과 성분 타입에 대한 아이스크림의 총주문량을 총주문량이 작은 순서대로 조회하는 SQL 문을 작성해주세요. 이때 총주문량을 나타내는 컬럼명은 TOTAL_ORDER로 지정해주세요.
SELECT B.INGREDIENT_TYPE, SUM(A.TOTAL_ORDER) TOTAL_ORDER
FROM FIRST_HALF A, ICECREAM_INFO B
WHERE A.FLAVOR=B.FLAVOR
GROUP BY B.INGREDIENT_TYPE- join 아닌경우에도 A.=B. 해주기
group by #3 ***
PRODUCT 테이블에서 만원 단위의 가격대 별로 상품 개수를 출력하는 SQL 문을 작성해주세요. 이때 컬럼명은 각각 컬럼명은 PRICE_GROUP, PRODUCTS로 지정해주시고 가격대 정보는 각 구간의 최소금액(10,000원 이상 ~ 20,000 미만인 구간인 경우 10,000)으로 표시해주세요. 결과는 가격대를 기준으로 오름차순 정렬해주세요.
SELECT TRUNCATE(PRICE,-4) PRICE_GROUP,
COUNT(PRODUCT_ID) PRODUCTS FROM PRODUCT
GROUP BY PRICE_GROUP
ORDER BY PRICE_GROUP- truncate(,자릿수) :
lv.3 over
join #1
MEMBER_PROFILE와 REST_REVIEW 테이블에서 리뷰를 가장 많이 작성한 회원의 리뷰들을 조회하는 SQL문을 작성해주세요. 회원 이름, 리뷰 텍스트, 리뷰 작성일이 출력되도록 작성해주시고, 결과는 리뷰 작성일을 기준으로 오름차순, 리뷰 작성일이 같다면 리뷰 텍스트를 기준으로 오름차순 정렬해주세요.
SELECT B.MEMBER_NAME, A.REVIEW_TEXT, DATE_FORMAT(A.REVIEW_DATE,'%Y-%m-%d')
FROM REST_REVIEW A
JOIN (
SELECT R.MEMBER_ID, M.MEMBER_NAME, RANK() OVER(ORDER BY CNT DESC) AS RANKING
FROM
( SELECT *, COUNT(MEMBER_ID) AS CNT
FROM REST_REVIEW
GROUP BY MEMBER_ID
) AS R
JOIN MEMBER_PROFILE M ON R.MEMBER_ID = M.MEMBER_ID
) B
ON A.MEMBER_ID = B.MEMBER_ID
WHERE B.RANKING = 1
ORDER BY A.REVIEW_DATE;- 너무 복잡하고 어렵다...ㅠㅠ
join #2
7월 아이스크림 총 주문량과 상반기의 아이스크림 총 주문량을 더한 값이 큰 순서대로 상위 3개의 맛을 조회하는 SQL 문을 작성해주세요.
SELECT A.FLAVOR FROM FIRST_HALF A
JOIN (SELECT FLAVOR, SUM(TOTAL_ORDER) TOTAL_ORDER FROM JULY GROUP BY FLAVOR) B
ON A.FLAVOR=B.FLAVOR
ORDER BY (B.TOTAL_ORDER + A.TOTAL_ORDER) DESC
LIMIT 3
- 7월에는 group by로 판매량 묶어주기
join #3
FOOD_PRODUCT와 FOOD_ORDER 테이블에서 생산일자가 2022년 5월인 식품들의 식품 ID, 식품 이름, 총매출을 조회하는 SQL문을 작성해주세요. 이때 결과는 총매출을 기준으로 내림차순 정렬해주시고 총매출이 같다면 식품 ID를 기준으로 오름차순 정렬해주세요.
SELECT A.PRODUCT_ID, A.PRODUCT_NAME,
SUM(A.PRICE * B.AMOUNT) TOTAL_SALES FROM FOOD_PRODUCT A
JOIN (SELECT * FROM FOOD_ORDER) B
ON A.PRODUCT_ID=B.PRODUCT_ID
WHERE DATE_FORMAT(B.PRODUCE_DATE, '%Y-%m')='2022-05'
# WHERE B.PRODUCE_DATE LIKE '2022-05%'
GROUP BY A.PRODUCT_NAME
ORDER BY TOTAL_SALES DESC, A.PRODUCT_ID- 아직 서브쿼리 묶는 부분을 잘 모르겠다 ㅠ
join #4
CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 '세단' 또는 'SUV' 인 자동차 중 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능하고 30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차에 대해서 자동차 ID, 자동차 종류, 대여 금액(컬럼명: FEE) 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 자동차 종류를 기준으로 오름차순 정렬, 자동차 종류까지 같은 경우 자동차 ID를 기준으로 내림차순 정렬해주세요.
# 30일 대여 금액 50 - 200 CAR_ID
select a.CAR_ID , a.CAR_TYPE ,round(DAILY_FEE * 30 * (100-DISCOUNT_RATE)/100) as 'FEE' #, DAILY_FEE, b.DURATION_TYPE , DISCOUNT_RATE ,
from CAR_RENTAL_COMPANY_CAR a right join CAR_RENTAL_COMPANY_DISCOUNT_PLAN b
on a.CAR_TYPE = b.CAR_TYPE
where DURATION_TYPE = '30일 이상' and DAILY_FEE * 30 * (100-DISCOUNT_RATE)/100 between 500000 and 2000000
and CAR_ID in
(
# CAR_TYPE = 세단 or SUV
select CAR_ID
from CAR_RENTAL_COMPANY_CAR
where CAR_TYPE in ('세단','SUV') and
CAR_ID not in
(
# 11/1 - 11/30 대여 불가능 CAR_ID
SELECT CAR_ID
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
where DATE_FORMAT(START_DATE,'%y%m%d') between '221101' and '221130'
or DATE_FORMAT(END_DATE,'%y%m%d') between '221101' and '221130'
or (DATE_FORMAT(START_DATE,'%y%m%d') < '221101' and DATE_FORMAT(END_DATE,'%y%m%d') > '221130')
)
)
order by 3 desc , 2 asc , 1 desc- 이건 너무너무 어려워서 내가 손도 못대겠다. 나중에 다시봐야지
상품을 구매한 회원비율
SELECT YEAR, MONTH, COUNT(*) AS PUCHASED_USERS,
ROUND((COUNT(*)/ (SELECT COUNT(*)
FROM USER_INFO WHERE YEAR(JOINED) = 2021)), 1) AS PUCHASED_RATIO
FROM (
SELECT DISTINCT YEAR(S.SALES_DATE) AS YEAR, MONTH(S.SALES_DATE) AS MONTH, U.USER_ID
FROM ONLINE_SALE S
JOIN USER_INFO U ON S.USER_ID = U.USER_ID AND YEAR(JOINED) = 2021
) A
GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH조건에 맞는 사용자와 총 거래금액 조회하기
USED_GOODS_BOARD와 USED_GOODS_USER 테이블에서 중고 거래 게시물을 3건 이상 등록한 사용자의 사용자 ID, 닉네임, 전체주소, 전화번호를 조회하는 SQL문을 작성해주세요. 이때, 전체 주소는 시, 도로명 주소, 상세 주소가 함께 출력되도록 해주시고, 전화번호의 경우 xxx-xxxx-xxxx 같은 형태로 하이픈 문자열(-)을 삽입하여 출력해주세요. 결과는 회원 ID를 기준으로 내림차순 정렬해주세요.
SELECT A.USER_ID, A.NICKNAME,
CONCAT(CITY,' ', STREET_ADDRESS1, ' ' ,STREET_ADDRESS2) 전체주소,
CONCAT(LEFT(TLNO,3),'-',SUBSTRING(TLNO,4,4),'-',RIGHT(TLNO,4)) 전화번호 FROM USED_GOODS_USER A
JOIN USED_GOODS_BOARD B
ON A.USER_ID = B.WRITER_ID
GROUP BY A.USER_ID
HAVING COUNT(*)>=3
ORDER BY A.USER_ID DESC
- SUBSTRING( ,시작위치, 길이) : 중간 문자열 자르기
- CONCAT() : 문자열 합치기
string,date #2
USED_GOODS_BOARD와 USED_GOODS_FILE 테이블에서 조회수가 가장 높은 중고거래 게시물에 대한 첨부파일 경로를 조회하는 SQL문을 작성해주세요. 첨부파일 경로는 FILE ID를 기준으로 내림차순 정렬해주세요. 기본적인 파일경로는 /home/grep/src/ 이며, 게시글 ID를 기준으로 디렉토리가 구분되고, 파일이름은 파일 ID, 파일 이름, 파일 확장자로 구성되도록 출력해주세요. 조회수가 가장 높은 게시물은 하나만 존재합니다.
SELECT CONCAT('/home/grep/src/', A.BOARD_ID,'/', FILE_ID ,FILE_NAME, FILE_EXT) FILE_PATH FROM USED_GOODS_FILE A
JOIN USED_GOODS_BOARD B
ON A.BOARD_ID=B.BOARD_ID
WHERE VIEWS = (SELECT MAX(VIEWS) FROM USED_GOODS_BOARD)
ORDER BY FILE_ID DESC;서브쿼리
