SELECT empno
, ename
, job
, sal
, SUM(sal) OVER(ORDER BY empno) AS sum_sal
FROM emp
WHERE job IN ('MANAGER', 'SALESMAN')직업별 구분없이 누적합 구할때 그냥 sum over order by 사용하면됨.
만약 직업별 구분있게 누적합을 구하고 싶다면 sum over partition by 직업 order by ~ 하면됨.
SELECT empno
, ename
, job
, sal
, SUM(sal) OVER(PARTITION BY job ORDER BY empno) AS sum_sal
FROM emp
WHERE job IN ('MANAGER', 'SALESMAN')특정 기준으로 문자열 합치기는 listagg 사용
select deptno as 부서번호,
listagg(ename, ', ' <-- 구분할 문자열을 의미함) within group(order by hiredate) as 전체직원
from emp
group by deptno;만약 갯수도 표현하고 싶다면 그냥 count 사용
select deptno as 부서번호,
listagg(ename, ', ' <-- 구분할 문자열을 의미함) within group(order by hiredate) as 전체직원,
count(*) as 직원수
from emp
group by deptno;
oracle select max, min row
max or min 값을 가지는 행의 다른 칼럼이 필요한 경우가 있다.
이럴때는 sub query를 사용하거나 oracle 에 존재하는 max() keep(dense_rank first order by ~ desc) 나 min() keep(dense_rank last order by ~ desc)를 사용할 수 있다.
max() keep(dense_rank first order by ~ desc의 경우 다음과 같다.
SELECT '/home/grep/src/' || BOARD_ID || '/' || FILE_ID || FILE_NAME || FILE_EXT "FILE_PATH"
from USED_GOODS_FILE
where BOARD_ID in
(select max(BOARD_ID) keep(dense_rank first order by views desc) from USED_GOODS_BOARD)
order by file_id desc만약 sub query를 쓴다면 다음과 같이 해결이 가능하다.
SELECT '/home/grep/src/' || BOARD_ID || '/' || FILE_ID || FILE_NAME || FILE_EXT "FILE_PATH"
from USED_GOODS_FILE
where BOARD_ID in
(select board_id from USED_GOODS_BOARD where views in (select max(views) from USED_GOODS_BOARD))
order by file_id descoracle 핸드폰 번호 포맷
가끔 핸드폰 번호 형식에 맞게 select 하라는 조건이 있다. 그럴땐 regexp를 사용하자
SELECT USER_ID, NICKNAME, CITY || ' ' || STREET_ADDRESS1 || ' ' || STREET_ADDRESS2 전체주소,
regexp_replace(TLNO, '(.{3})(.+)(.{4})', '\1-\2-\3') 전화번호
from USED_GOODS_USER
where user_id in (select writer_id
from USED_GOODS_BOARD
group by writer_id
having count(*) > 2)
order by user_id descoracle join
특정 금액 이상을 충족하는 값을 뽑는 문제
SELECT user_id, nickname, sum(price) "TOTAL_SALES"
from USED_GOODS_USER
join USED_GOODS_BOARD on USED_GOODS_USER.user_id = USED_GOODS_BOARD.writer_id
where USED_GOODS_BOARD.status = 'DONE'
group by user_id, nickname
having sum(price) >= 700000
order by sum(price) asc특정 기간에 대여 가능한 자동차의 할인된 금액
select distinct(c.CAR_ID), c.CAR_TYPE, 30 * DAILY_FEE * (100 - NVL(DISCOUNT_RATE, 0)) / 100 FEE
from CAR_RENTAL_COMPANY_CAR c
join CAR_RENTAL_COMPANY_RENTAL_HISTORY h on c.CAR_ID = h.CAR_ID
join CAR_RENTAL_COMPANY_DISCOUNT_PLAN p on c.CAR_TYPE = p.CAR_TYPE
where c.CAR_ID not in (
select CAR_ID
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
where end_date >= to_date('2022-11-01', 'YYYY-MM-DD') and start_date <= to_date('2022-11-30', 'YYYY-MM-DD'))
and p.DURATION_TYPE = '30일 이상'
and c.CAR_TYPE in ('세단', 'SUV')
and (30 * DAILY_FEE * (100 - NVL(DISCOUNT_RATE, 0)) / 100 >= 500000 and 30 * DAILY_FEE * (100 - NVL(DISCOUNT_RATE, 0)) / 100 < 2000000)
order by 30 * DAILY_FEE * (100 - NVL(DISCOUNT_RATE, 0)) / 100 DESC, c.CAR_TYPE, c.CAR_ID DESC자동차 대여 기록 별 대여 금액 구하기
-- 코드를 입력하세요
with temp as(
SELECT HISTORY_ID, c.CAR_TYPE, c.DAILY_FEE, sum(END_DATE - START_DATE + 1) USE_DAYS,
case
when sum(END_DATE - START_DATE + 1) >=90 then '90일 이상'
when sum(END_DATE - START_DATE + 1) >=30 then '30일 이상'
when sum(END_DATE - START_DATE + 1) >=7 then '7일 이상'
else '0' end DURATION_TYPE
from CAR_RENTAL_COMPANY_CAR c
join CAR_RENTAL_COMPANY_RENTAL_HISTORY r on c.CAR_ID = r.CAR_ID
where c.CAR_TYPE = '트럭'
group by HISTORY_ID, c.CAR_TYPE, c.DAILY_FEE)
select history_id, daily_fee * use_days * (100 - NVL(discount_rate, 0)) / 100 FEE
from temp left outer join
CAR_RENTAL_COMPANY_DISCOUNT_PLAN p
on temp.car_type = p.car_type
and temp.duration_type = p.duration_type
order by daily_fee * use_days * (100 - NVL(discount_rate, 0)) / 100 DESC, history_id desc;카테고리별 도서 판매량 집계
SELECT distinct(CATEGORY), sum(SALES) over(partition by CATEGORY) TOTAL_SALES
from BOOK_SALES s join BOOK b on b.book_id = s.book_id
where SALES_DATE between to_date('2022-01-01', 'YYYY-MM-DD') and to_date('2022-01-31', 'YYYY-MM-DD')
order by CATEGORY아이스크림 성분으로 총판매량 구하기
SELECT distinct(INGREDIENT_TYPE),
sum(TOTAL_ORDER) over(partition by INGREDIENT_TYPE) TOTAL_ORDER
from ICECREAM_INFO i join FIRST_HALF f on i.flavor = f.flavor
order by sum(TOTAL_ORDER) over(partition by INGREDIENT_TYPE)과일로 만든 아이스크림
SELECT f.flavor
from FIRST_HALF f join ICECREAM_INFO i on f.flavor = i.flavor
where i.INGREDIENT_TYPE = 'fruit_based'
group by f.flavor
having sum(TOTAL_ORDER) > 3000;5월 환자
SELECT MCDP_CD 진료과코드, count(*) as "5월예약건수"
from APPOINTMENT
where APNT_YMD between to_date('2022-05-01', 'YYYY-MM-DD') and to_date('2022-05-31', 'YYYY-MM-DD')
group by MCDP_CD
order by count(*), MCDP_CD저자별 카테고리별 판매금액 집계
with temp as(
SELECT distinct(s.book_id), category, author_id, (price * sum(sales) over(partition by s.book_id)) TOTAL_SALES
from BOOK_SALES s join BOOK b on s.BOOK_ID = b.BOOK_ID
where SALES_DATE between to_date('2022-01-01', 'YYYY-MM-DD')
and to_date('2022-01-31', 'YYYY-MM-DD'))
select distinct(t.author_id),author_name, category, sum(total_sales) over(partition by t.author_id, category) TOTAL_SALES
from temp t join author a on t.author_id = a.author_id
order by t.author_id, t.category desc주문량이 많은 아이스크림 TOP 3
SELECT distinct(f.flavor)
from FIRST_HALF f left outer join (select flavor, sum(total_order) over(partition by flavor) total_order
from july) j on f.flavor = j.flavor
order by (f.total_order + j.total_order) desc
fetch first 3 rows only;취소되지 않은 예약 조회하기
SELECT apnt_no, p.pt_name, p.PT_NO, d.MCDP_CD, d.DR_NAME, apnt_ymd
from APPOINTMENT a join DOCTOR d on a.MDDR_ID = d.DR_ID
join PATIENT p on a.pt_no = p.pt_no
where APNT_CNCL_YN = 'N'
and to_char(APNT_YMD, 'YYYY-MM-DD') = '2022-04-13'
order by apnt_ymd상품 구매한 회원 비율 using sub query
with temp as(
select distinct(o.user_id), extract(year from SALES_DATE) year, extract(month from SALES_DATE) month, (select count(*) from user_info
where extract(year from joined) = 2021 ) users
from ONLINE_SALE o join USER_INFO u on o.user_id = u.user_id
where extract(year from joined) = 2021
order by month)
select distinct(year), month, count(*) over(partition by year, month) PUCHASED_USERS,
round(count(*) over(partition by year, month) / users, 1) PUCHASED_RATIO
from temp
order by year, month그룹별 조건에 맞는 식당 목록 출력
with temp as(
select m.member_id, m.member_name, count(*) over(partition by m.member_id) cnt, review_text, review_date
from MEMBER_PROFILE m join rest_review rr on m.member_id = rr.member_id)
select temp.member_name, temp.review_text, to_char(temp.review_date, 'YYYY-MM-DD') REVIEW_DATE
from temp
where temp.cnt = (select max(cnt) from temp)
order by review_date, review_textleft outer join
select contest_id, hacker_id, name, sum(ts), sum(tas), sum(tv), sum(tuv)
from(
select con.contest_id, hacker_id, name, ts, tas, tv, tuv
from contests con
left outer join colleges co on con.contest_id = co.contest_id
left outer join challenges cha on co.college_id = cha.college_id
left outer join (
select challenge_id, sum(total_views) tv, sum(total_unique_views) tuv
from view_stats
group by challenge_id) vs on cha.challenge_id = vs.challenge_id
left outer join(
select challenge_id, sum(total_submissions) ts, sum(total_accepted_submissions) tas
from submission_stats
group by challenge_id) vss on cha.challenge_id = vss.challenge_id) tot
group by contest_id, hacker_id, name
having sum(ts) > 0 or sum(tas) > 0 or sum(tv) > 0 or sum(tuv) > 0
order by contest_id;Union
오프라인/온라인 총합 판매
SELECT to_char(SALES_DATE, 'YYYY-MM-DD') SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
from ONLINE_SALE
where SALES_DATE between to_date('2022-03-01', 'YYYY-MM-DD')
and to_date('2022-03-31', 'YYYY-MM-DD')
union
SELECT to_char(SALES_DATE, 'YYYY-MM-DD') SALES_DATE, PRODUCT_ID, NULL USER_ID, SALES_AMOUNT
from OFFLINE_SALE
where SALES_DATE between to_date('2022-03-01', 'YYYY-MM-DD')
and to_date('2022-03-31', 'YYYY-MM-DD')to_date 이용하기
string과 date 비교를 위해 to_date 함수를 사용한 문제다.
SELECT car_id
from CAR_RENTAL_COMPANY_CAR
where car_type = '세단' and car_id in(
select car_id
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
where start_date > to_date('2022-10-01', 'YYYY-MM-DD'))
order by car_id descwith temp as(select car_id, history_id
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
where to_date('2022-10-16', 'YYYY-MM-DD') between start_date and end_date)
select distinct(CAR_RENTAL_COMPANY_RENTAL_HISTORY.car_id),
case when temp.car_id is null then '대여 가능'
else '대여중' end "AVAILABILITY"
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
left outer join temp on CAR_RENTAL_COMPANY_RENTAL_HISTORY.car_id = temp.car_id
order by CAR_RENTAL_COMPANY_RENTAL_HISTORY.car_id desc-- 코드를 입력하세요
with temp as(
SELECT CAR_ID, count(*) as "CNT"
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
where START_DATE between to_date('2022-08-01', 'YYYY-MM-DD') and to_date('2022-10-31', 'YYYY-MM-DD')
group by CAR_ID
having count(*) >=5)
SELECT extract(month from START_DATE) as "MONTH", CAR_RENTAL_COMPANY_RENTAL_HISTORY.CAR_ID, count(*) as "RECORDS"
from CAR_RENTAL_COMPANY_RENTAL_HISTORY join temp on CAR_RENTAL_COMPANY_RENTAL_HISTORY.CAR_ID = temp.CAR_ID
where START_DATE between to_date('2022-08-01', 'YYYY-MM-DD') and to_date('2022-10-31', 'YYYY-MM-DD')
group by extract(month from START_DATE), CAR_RENTAL_COMPANY_RENTAL_HISTORY.CAR_ID
order by extract(month from START_DATE) asc, CAR_RENTAL_COMPANY_RENTAL_HISTORY.CAR_ID DESC구구단
WITH N AS (
SELECT LEVEL AS N
FROM DUAL
CONNECT BY LEVEL < 10)
SELECT A.N, B.N, A.N||' X '||B.N||' = '||A.N*B.N AS DATA
FROM N A, N B
where A.N = 3
order by A.N*B.N
;to_char
-- 코드를 입력하세요
SELECT BOOK_ID, AUTHOR_NAME, to_char(PUBLISHED_DATE, 'YYYY-MM-DD') PUBLISHED_DATE
from BOOK
join AUTHOR on BOOK.AUTHOR_ID = AUTHOR.AUTHOR_ID
where CATEGORY = '경제'
order by PUBLISHED_DATE통계
SELECT r.REST_ID, r.REST_NAME, r.FOOD_TYPE, r.FAVORITES, r.ADDRESS,
round(AVG(rr.review_score), 2) SCORE
from REST_INFO r join rest_review rr on r.rest_id = rr.rest_id
group by r.rest_id, r.rest_name, r.food_type, r.FAVORITES, r.ADDRESS
having r.address like '서울%'
order by round(AVG(rr.review_score), 2) desc, FAVORITES desc
개발자로 입사했지만 정체성을 잃어가는중... 다시 준비 시작이다..