https://school.programmers.co.kr/learn/courses/30/lessons/59040

[GROUP BY] 고양이와 개는 몇 마리 있을까

SELECT animal_type, count(animal_id)
from animal_ins 
group by animal_type
having animal_type in ('Cat', 'Dog')
order by animal_type

https://school.programmers.co.kr/learn/courses/30/lessons/131533?language=oracle

1. JOIN 상품 별 오프라인 매출 구하기

SELECT p.product_code as PRODUCT_CODE,
(p.price * sum(o.sales_amount)) as SALES
from product p 
join offline_sale o on p.product_id = o.product_id
group by p.price, p.product_code
order by SALES desc, p.product_code

- 오라클에서는 행을 압축할 때 single value 를 보장하므로, SELECT, HAVING, ORDER BY 에 포함된 컬럼이 GROUP BY에 선언되지 않거나 집계 함수(SUM, MAX, MIN)에 포함되지 않으면 오류를 발생시킨다. 

https://school.programmers.co.kr/learn/courses/30/lessons/131120

1. SELECT 3월에 태어난 여성 회원목록 출력

SELECT member_id, member_name, gender as GENDER,
to_char(date_of_birth, 'YYYY-MM-DD') as DATE_OF_BIRTH
from member_profile
where gender = 'W' 
and to_number(to_char(date_of_birth, 'MM')) = 03
and tlno is not null 
order by member_id

https://school.programmers.co.kr/learn/courses/30/lessons/59409?language=oracle

1. String, Date 중성화 여부 파악하기

SELECT animal_id, name, 
(case 
 when substr(sex_upon_intake, 0, instr(sex_upon_intake, ' ')-1) in ('Neutered' , 'Spayed' ) then 'O'
 else 'X'
 end) as 중성화
 from animal_ins
 order by animal_id

• SUBSTR @return char, INSTR @return index
  • INSTR(STR, ' ') 공백 위치이므로 -1 해야 추출값 비교에서 단어만으로 비교 가능