1. JSP 수업) 데이터 베이스 10개 문제 풀어보기 - 조인 쿼리 작성
    1. 1. 남녀 성별 인원수 조회
    2. 2. 지금까지 한번도 상품을 구매한 적이 없는 회원의 인적사항 조회(아이디, 이름, 이메일)
    3. 3. 등록된 상품이 한건도 없는 상품분류 조회(상품분류코드, 분류명)
    4. 4. 지금까지 가장 많이 팔린 상품 조회(상품코드, 상품명, 상품분류명, 거래처명, 마일리지)
    5. 5. 지금까지 가장 적게 팔린 상품 조회(상품코드, 상품명, 상품분류명, 거래처명, 마일리지)
    6. 6. 거래처 중 거래 품목 수가 가장 많은 거래처 조회(거래처코드, 거래처명, 거래처분류명, 담당자명)
    7. 7. 거래처 중 거래 품목 수가 가장 적은 거래처 조회(거래처코드, 거래처명, 거래처분류명, 담당자명)
    8. 8. 남녀 성별 각각 구매율이 높은 상품 조회(성별, 상품코드, 상품명, 분류명)
    9. 9. 각 회원별 구매 총액 조회(회원아이디, 회원명, 구매총액)
    10. 10. 각 상품별 구매왕 조회(구매왕아이디, 회원명, 구매총액)

JSP 수업) 데이터 베이스 10개 문제 풀어보기 - 조인 쿼리 작성

작성: 2021.04.07

수정: 2023.04.11

읽는시간: 06 분

Data/Oracle

반응형

Oracle 데이터 베이스 10개 문제 풀어보기

1. 남녀 성별 인원수 조회

sql
SELECT *
FROM
(select COUNT(*) AS MEN
from member
where SUBSTR(mem_regno2,1,1) = '1' OR SUBSTR(mem_regno2,1,1) ='3'
),
(select COUNT(*) AS WOMEN
from member
where SUBSTR(mem_regno2,1,1) = '2' OR SUBSTR(mem_regno2,1,1) ='4'
)

2. 지금까지 한번도 상품을 구매한 적이 없는 회원의 인적사항 조회(아이디, 이름, 이메일)

select mem_id, mem_name, mem_mail
from (
select mem_id, mem_name, mem_mail, count(cart_member) as orders
from member
left outer join cart on (mem_id = cart_member)
group by mem_id, mem_name, mem_mail)
where orders = 0

3. 등록된 상품이 한건도 없는 상품분류 조회(상품분류코드, 분류명)

select lprod_gu, lprod_nm
from(
select lprod_gu, lprod_nm, count(prod_lgu) cnt
from lprod
left outer join prod on( lprod_gu = prod_lgu)
group by lprod_gu, lprod_nm)
where cnt = 0

4. 지금까지 가장 많이 팔린 상품 조회(상품코드, 상품명, 상품분류명, 거래처명, 마일리지)

5. 지금까지 가장 적게 팔린 상품 조회(상품코드, 상품명, 상품분류명, 거래처명, 마일리지)

select prod_id, prod_name,lprod_nm,prod_mileage, buyer_name,
nvl(sum(cart_qty), 0) as sales
from prod
left outer join cart on (prod_id = cart_prod)
left outer join lprod on (prod_lgu = lprod_gu)
left outer join buyer on (prod_buyer = buyer_id)
group by prod_id, prod_name,lprod_nm,prod_mileage, buyer_name
order by sales desc

가장 많이 팔린 상품

select a.*
from(
select prod_id, prod_name,lprod_nm,nvl(prod_mileage,0) prod_mileage, buyer_name,
nvl(sum(cart_qty), 0) as sales
from prod
left outer join cart on (prod_id = cart_prod)
left outer join lprod on (prod_lgu = lprod_gu)
left outer join buyer on (prod_buyer = buyer_id)
group by prod_id, prod_name,lprod_nm,prod_mileage, buyer_name
order by sales desc) a
where rownum = 1;

가장 적게 팔린 상품

select a.*
from(
select prod_id, prod_name,lprod_nm,nvl(prod_mileage,0) prod_mileage, buyer_name,
nvl(sum(cart_qty), 0) as sales
from prod
left outer join cart on (prod_id = cart_prod)
left outer join lprod on (prod_lgu = lprod_gu)
left outer join buyer on (prod_buyer = buyer_id)
group by prod_id, prod_name,lprod_nm,prod_mileage, buyer_name
order by sales asc) a
where rownum = 1;

6. 거래처 중 거래 품목 수가 가장 많은 거래처 조회(거래처코드, 거래처명, 거래처분류명, 담당자명)

7. 거래처 중 거래 품목 수가 가장 적은 거래처 조회(거래처코드, 거래처명, 거래처분류명, 담당자명)

select buyer_id, buyer_name, lprod_nm, count(prod_id) cnt
from buyer
left outer join prod on (buyer_id = prod_buyer)
left outer join lprod on (buyer_lgu = lprod.lprod_gu)
group by buyer_id, buyer_name,lprod_nm
order by cnt desc;

가장 많은 거래처

select a.*
from(
select buyer_id, buyer_name, lprod_nm, count(prod_id) cnt
from buyer
left outer join prod on (buyer_id = prod_buyer)
left outer join lprod on (buyer_lgu = lprod.lprod_gu)
group by buyer_id, buyer_name,lprod_nm
order by cnt desc)a
where rownum = 1;

가장 적은 거래처

select a.*
from(
select buyer_id, buyer_name, lprod_nm, count(prod_id) cnt
from buyer
left outer join prod on (buyer_id = prod_buyer)
left outer join lprod on (buyer_lgu = lprod.lprod_gu)
group by buyer_id, buyer_name,lprod_nm
order by cnt asc)a
where rownum = 1;

8. 남녀 성별 각각 구매율이 높은 상품 조회(성별, 상품코드, 상품명, 분류명)

select '남성'as gender, cart_prod, prod_name, lprod_nm
from (
select cart_prod, prod_name,lprod_nm, sum(cart_qty)
from member
inner join cart on (mem_id = cart_member)
inner join prod on (cart_prod = prod_id)
inner join lprod on (prod_lgu = lprod_gu)
where SUBSTR(mem_regno2,0,1) = '1' OR SUBSTR(mem_regno2,0,1) ='3'
group by cart_prod, prod_name, lprod_nm
order by sum(cart_qty) desc)
where rownum=1
union all
select '여성'as gender, cart_prod, prod_name, lprod_nm
from (
select cart_prod, prod_name,lprod_nm, sum(cart_qty)
from member
inner join cart on (mem_id = cart_member)
inner join prod on (cart_prod = prod_id)
inner join lprod on (prod_lgu = lprod_gu)
where SUBSTR(mem_regno2,0,1) = '2' OR SUBSTR(mem_regno2,0,1) ='4'
group by cart_prod, prod_name, lprod_nm
order by sum(cart_qty) desc)
where rownum=1

9. 각 회원별 구매 총액 조회(회원아이디, 회원명, 구매총액)

select mem_id, mem_name, nvl(sum(prod_price * cart_qty),0) amount
from member
left outer join cart on (mem_id = cart_member)
left outer join prod on (cart_prod = prod_id)
group by mem_id, mem_name
order by amount desc

10. 각 상품별 구매왕 조회(구매왕아이디, 회원명, 구매총액)

select prod_name, mem_id, mem_name, amount
from(
select prod_name, mem_id, mem_name, prod_price*cart_qty as amount, rank() over(partition by prod_name order by prod_price*cart_qty desc ,mem_id)as rank
from prod
left outer join cart on (prod_id = cart_prod)
left outer join member on (cart_member = mem_id)
order by prod_name)
where rank=1

반응형