사전캠프 10일차 - SQL 5주차 정리 / SQL 코드카타 11번~19번

20240607 TIL

window function

Window Function의 기본 구조

window_function(argument) over (partition by 그룹 기준 컬럼 order by 정렬 기준)

-argument 는 생략가능

-partition by  는 그룹을 나누기위한 기준

-order by 는 정렬 할 컬럼 기준을 적어줌

 

-RANK

-특정 기준으로 순위를 매겨주는 기능

[실습] 음식 타입별로 주문 건수가 가장 많은 상점 3개씩 조회하기

---

-SUM

-합계를 구하는 기능

[실습] 각 음식점의 주문건이 해당 음식 타입에서 차지하는 비율을 구하고, 주문건이 낮은 순으로 정렬했을 때 누적 합 구하기

---

날짜 포맷과 조건까지 SQL로 한 번에 끝내기

[실습] 날짜 데이터의 여러 포맷

select date(date) date_type,
       date_format(date(date), '%Y') "년",
       date_format(date(date), '%m') "월",
       date_format(date(date), '%d') "일",
       date_format(date(date), '%w') "요일"
from payments

---

5주차 숙제

음식 타입별, 연령별 주문건수 pivot view 만들기

---

SQL 알고리즘 코드타카

11번 - 여러 기준으로 정렬하기

동물 보호소에 들어온 모든 동물의 아이디와 이름, 보호 시작일을 이름 순으로 조회하는 SQL문을 작성. 단, 이름이 같은 동물 중에서는 보호를 나중에 시작한 동물을 먼저 보여줘야 함.

select animal_id,
       name,
       dateitme
from animal_ins
order by name, datetime desc

12번 - 이름에 el이 들어가는 동물 찾기

동물 보호소에 들어온 동물 이름 중, 이름에 "EL"이 들어가는 개의 아이디와 이름을 조회하는 SQL문을 작성. 이때 결과는 이름 순으로 조회해주세요. 단, 이름의 대소문자는 구분하지 않음.

select animal_id,
       name
from animal_ins
where name like '%el%' and animal_type = 'Dog'
order by name

13번 - 나이 정보가 없는 회원 수 구하기

나이 정보가 없는 회원이 몇 명인지 출력하는 SQL문을 작성. 이때 컬럼명은 USERS로 지정

select count(1) 'USERS'
from user_info
where age is null

14번 - 가장 비싼 상품 구매하기

PRODUCT 테이블에서 판매 중인 상품 중 가장 높은 판매가를 출력하는 SQL문을 작성. 이때 컬럼명은 MAX_PRICE로 지정

select max(price) 'max_price'
from product

15번 - NULL 처리하기

동물의 생물 종, 이름, 성별 및 중성화 여부를 아이디 순으로 조회하는 SQL문을 작성. 이때 프로그래밍을 모르는 사람들은 NULL이라는 기호를 모르기 때문에, 이름이 없는 동물의 이름은 "No name"으로 표시

select animal_type,
       case when name is null then 'No name'
            else name end name,
       sex_upon_intake
from animal_ins
order by animal_id

16번 - 경기도에 위치한 식품창고 목록 출력하기

FOOD_WAREHOUSE 테이블에서 경기도에 위치한 창고의 ID, 이름, 주소, 냉동시설 여부를 조회하는 SQL문을 작성. 이때 냉동시설 여부가 NULL인 경우, 'N'으로 출력시켜 주시고 결과는 창고 ID를 기준으로 오름차순 정렬

select warehouse_id,
       warehouse_name,
       address,
       case when freezer_yn is null then 'N'
            else freezer_yn end freezer_yn
from food_warehouse
where warehouse_name like '%경기%'
order by warehouse_id

17번 - 강원도에 위치한 생산공장 목록 출력하기

FOOD_FACTORY 테이블에서 강원도에 위치한 식품공장의 공장 ID, 공장 이름, 주소를 조회하는 SQL문을 작성. 이때 결과는 공장 ID를 기준으로 오름차순 정렬

select factory_id,
       factory_name,
       address
from food_factory
where address like '강원도%'
order by factory_id

18번 -DATETIME에서 DATE로 형 변환

ANIMAL_INS 테이블에 등록된 모든 레코드에 대해, 각 동물의 아이디와 이름, 들어온 날짜를 조회하는 SQL문을 작성. 이때 결과는 아이디 순으로 조회(시각(시-분-초)을 제외한 날짜(년-월-일)만 보이기)

select animal_id,
       name,
       substr(date(datetime), 1, 10) '날짜'
from animal_ins
order by animal_id

19번 - 흉부외과 또는 일반외과 의사 목록 출력하기

DOCTOR 테이블에서 진료과가 흉부외과(CS)이거나 일반외과(GS)인 의사의 이름, 의사ID, 진료과, 고용일자를 조회하는 SQL문을 작성. 이때 결과는 고용일자를 기준으로 내림차순 정렬하고, 고용일자가 같다면 이름을 기준으로 오름차순 정렬.

select dr_name,
       dr_id,
       mcdp_cd,
       substr(hire_ymd, 1, 10) 'hire_ymd'
from doctor
where mcdp_cd = 'CS' or mcdp_Cd = 'GS'
order by hire_ymd desc, dr_name