4단계(1)

sql쿼리 각 절의 우선 순위

from >>> where >>> select >>> order by >>> group by >>> having >>> limit(이절은 order by 이후 실행)

 

1. 보호소에서 중성화한 동물

ANIMAL_INS 테이블 : 동물 보호소 들어온 동물의 정보를 담은 테이블

 

ANIMAL_OUTS 테이블 : 동물 보호소에 입양 보낸 동물의 정보를 담은 테이블

 

문제 :  보호소에 들어올 당시에는 중성화 되지 않았지만, 보호소를 나갈 당시에는 중성화된 동물의 아이디와 생물 종, 이름을 조회하는 아이디 순으로 조회하는 SQL 문을 작성

 

나의 쿼리

SELECT animal_id, animal_type, name # 컬럼 선택
FROM ANIMAL_INS 
WHERE SEX_UPON_INTAKE LIKE '%Intact%' # 들어올떄 중성화 안한 동물 
AND animal_id in ( 
    SELECT animal_id # 나갈떄 중성화 한 동물
    FROM ANIMAL_OUTS
    WHERE (SEX_UPON_OUTCOME LIKE 'Spayed%') or (SEX_UPON_OUTCOME LIKE 'Neutered%')
)
ORDER BY animal_id # 동물 id로 필터

 

 

2. 식품분류별 가장 비싼 식품의 정보 조회하기

FOOD_PRODUCT 테이블 : 식품 정보를 담은

 

문제 :  식품분류별로 가격이 제일 비싼 식품의 분류, 가격, 이름을 조회하는 SQL문을 작성해주세요. 이때 식품분류가 '과자', '국', '김치', '식용유'인 경우만 출력시켜 주시고 결과는 식품 가격을 기준으로 내림차순 정렬

 

나의 쿼리

SELECT A.category, B.max_price, A.product_name # 컬럼 선택
FROM
(SELECT category, price, product_name # 카테고리가 과자, 국, 김치, 식용유인 데이터
FROM FOOD_PRODUCT 
WHERE category in ('과자', '국', '김치', '식용유')) A
LEFT JOIN # 왼쪽 테이블 기준 조인
(SELECT category, MAX(price) AS max_price # 카테고리별 가장 비싼 가격
FROM FOOD_PRODUCT 
GROUP BY category) B
ON A.category=B.category # 카테고리 기준 조인
WHERE A.price=B.max_price # 가격이 가장 비싼 가격인것 필터
ORDER BY max_price desc # 가격 기준 내림차순

 

 

3. 5월 식품들의 총매출 조회하기

FOOD_PRODUCT 테이블 : 식품 정보를 담은

 

FOOD_ORDER 테이블 : 식품의 주문 정보를 담은

 

문제 :  생산일자가 2022년 5월인 식품들의 식품 ID, 식품 이름, 총매출을 조회하는 SQL문을 작성해주세요. 이때 결과는 총매출을 기준으로 내림차순 정렬해주시고 총매출이 같다면 식품 ID를 기준으로 오름차순 정렬

 

나의 쿼리

SELECT A.product_id, A.product_name, (A.price*B.sum_amt) as total_sales # 컬럼 선택
FROM
(SELECT product_id, product_name, price # 식품id, 제품명, 가격 데이터
FROM FOOD_PRODUCT) A
JOIN # 제품id 기준 조인
(SELECT product_id, sum(amount) as sum_amt # 2022년 5월 생산된 제품id와 총 생산수 데이터
FROM FOOD_ORDER 
WHERE date_format(produce_date, '%Y-%m')='2022-05'
GROUP BY product_id) B
ON A.product_id=B.product_id
ORDER BY total_sales desc, product_id # total가격 내림차순, 제품id 오름차순 정렬

 

 

4. 취소되지 않은 진료 예약 조회하기

PATIENT 테이블 : 환자 정보를 담은

 

DOCTOR 테이블 : 의사 정보를 담은

 

APPOINTMENT 테이블 : 예약목록을 담은

 

문제 :  2022년 4월 13일 취소되지 않은 흉부외과(CS) 진료 예약 내역을 조회하는 SQL문을 작성해주세요. 진료예약번호, 환자이름, 환자번호, 진료과코드, 의사이름, 진료예약일시 항목이 출력되도록 작성해주세요. 결과는 진료예약일시를 기준으로 오름차순 정렬

 

나의 쿼리

SELECT a.apnt_no, b.pt_name, a.pt_no, a.mcdp_cd, c.dr_name, a.apnt_ymd # 컬럼 선택
FROM
(SELECT apnt_no, pt_no, mcdp_cd, apnt_ymd, mddr_id # 2022년 4월 13일 취소되지 않은 진료
FROM APPOINTMENT 
WHERE date_format(apnt_ymd, '%Y-%m-%d')='2022-04-13' AND apnt_cncl_yn='N') a
LEFT JOIN # 왼쪽 테이블 기준 조인
(SELECT pt_no, pt_name # 환자 이름 가져옴
FROM PATIENT ) b
ON a.pt_no=b.pt_no # 환자 번호 기준 조인
LEFT JOIN # 왼쪽 테이블 기준 조인
(SELECT dr_name, dr_id, mcdp_cd # 의사 이름 가져옴
FROM DOCTOR ) c
on a.mddr_id=c.dr_id # 의사 id 기준 조인
ORDER BY apnt_ymd # 진료일자 기준 오름차순

 

 

5. 년, 월, 성별 별 상품 구매 회원 수 구하기

USER_INFO 테이블 : 의류 쇼핑몰에 가입한 회원 정보

 

ONLINE_SALE 테이블 : 온라인 상품 판매 정보

 

문제 :  년, 월, 성별 별로 상품을 구매한 회원수를 집계하는 SQL문을 작성해주세요. 결과는 년, 월, 성별을 기준으로 오름차순 정렬해주세요. 이때, 성별 정보가 없는 경우 결과에서 제외

 

나의 쿼리

SELECT a.year, a.month, b.gender, count(*) as users # 컬럼 선택
FROM
(SELECT user_id, date_format(SALES_DATE, '%Y') as year, date_format(SALES_DATE, '%m') as month
FROM ONLINE_SALE
GROUP BY user_id, year, month) a # 년,월 별 온라인 상품 판매 데이터
left join # 왼쪽 테이블 기준 조인
(SELECT user_id, gender
FROM USER_INFO) b # 쇼핑몰 회원 데이터
ON a.user_id=b.user_id # id 기준 조인
WHERE b.gender is not null # # 성별이 null이 아닌것 필터
GROUP BY year, month, gender # 년, 월, 성별 로 묶어주기
ORDER BY year, month, gender # 년, 월, 성별 오름차순 정렬