Kosta 클라우드 네이티브 애플리케이션 개발 (CNA) 교육 7일차

   1. 회원별 주문 상품 통계

    회원아이디 상품번호 상품갯수 구매금액 --(조건:주문건이 없더라도 회원출력)

SELECT o.order_code, g.good_seq, o.tot_price, g.order_amount
FROM orders o,
(SELECT good_seq, good_price, order_amount FROM goods) g;
SELECT u.user_id, q.good_seq, q.ss, q.tot_price
FROM users u,
(SELECT o.order_code,o.user_seq,o.user_seq AS uq,
o.tot_price,
g.good_seq,
g.order_amount
FROM orders o,
(SELECT good_seq,order_code ,order_amount FROM orders_goods) g
WHERE g.order_code = o.order_code) q
WHERE u.user_seq = q.uq;

 

 

1. 업체별 공급 상품 리스트

• -업체번호 업체명 상품번호 상품명 --(조건:상품이 없더라도 업체명 출력)

SELECT c.com_seq,
c.com_name,
gcg.good_seq,
gcg.good_name
FROM company c,
(SELECT g.good_seq,
g.good_name,
cg.com_seq
FROM goods g LEFT OUTER JOIN company_goods cg ON g.good_seq = cg.good_seq) gcg
WHERE c.com_seq = gcg.com_seq(+);

• 1. 회원관리
  2. 정규직/비정규직 구분하여 출력 --조건1:정규직이면A,비정규직이면B로 출력 --조건2:급여(1일 8시간 한달:20일 기준으로 계산) --회원번호 회원명 정규/비정규여부 월급여

SELECT * FROM fulltime UNION SELECT * FROM parttime;
SELECT u.user_seq,
u.user_name,
CASE WHEN a.asal <1500000 THEN a.asal*8*20 ELSE asal END AS 급여,
CASE WHEN u.user_seq < 3 THEN 'B' ELSE 'A' END AS 구분
FROM users u,
(SELECT * FROM fulltime UNION SELECT * FROM parttime) a
WHERE u.user_seq = a.user_seq;

• 1. 상품/주문관리
  2. 주문된 상품별 판매량, 판매금액 출력 --조건:판매량이 높은 순으로 정렬 --상품번호 상품명 총판매량 총판매금액

SELECT g.good_seq AS 상품번호,
g.good_name AS 상품명,
SUM(og.order_amount) AS 총판매량,
SUM(og.order_price) AS 총판매금액
FROM goods g,
orders_goods og
WHERE g.good_seq = og.good_seq
GROUP BY g.good_seq,g.good_name
ORDER BY 총판매량 DESC,g.good_name ASC;

• 1. 사용자별 구매 통계
  2. 회원아이디 총구매횟수 총구매금액 --조건1 : 구매금액이 높은 순 출력

SELECT u.user_id,s.cnt AS 총구매횟수,s.tot AS 총구매금액
FROM (SELECT user_seq,SUM(tot_price) AS tot,count(1) AS cnt FROM orders GROUP BY user_seq) s,
users u
WHERE u.user_seq = s.user_seq ORDER BY 총구매금액 DESC;

• 1. 휴먼회원 통계
• 구매실적이 전혀 없는 회원 목록 출력
• -회원아이디 회원명
• ex) --lee 이씨

SELECT * FROM orders;
SELECT * FROM users;
SELECT u.user_seq,u.user_id,u.user_name,SUM(o.tot_price) AS 구매실적
FROM users u LEFT OUTER JOIN orders o ON u.user_seq = o.user_seq
GROUP BY u.user_seq,u.user_id,u.user_name HAVING SUM(o.tot_price) IS NULL;

• 1. 전체 회원 목록 중 휴먼 회원이 차지하는 비율?
  2. 조건1 : 관리자 제외 --조건2: 휴먼회원은 구매 실적이 전혀 없는 회원 -- 회원수 휴먼회원비율
  3. 2/5 40%

SELECT COUNT(1) ucnt FROM users WHERE user_gubun != 'a';
SELECT COUNT(1) AS hcnt FROM users WHERE user_seq NOT IN (SELECT DISTINCT user_seq FROM orders);
SELECT b.hcnt|| '/'|| a.ucnt,b.hcnt/a.ucnt*100 ||'%' AS 휴먼회원비율
FROM (SELECT COUNT(1) ucnt FROM users WHERE user_gubun != 'a') a ,
(SELECT COUNT(1) AS hcnt FROM users WHERE user_seq NOT IN (SELECT DISTINCT user_seq FROM orders)) b;

• 1. 각 회원별로 매니저-회원 관계를 출력하시오
  2. 조건1: 관리자 제외 --조건2: 매니저번호 오름차순 회원번호 오름차순 정렬

SELECT * FROM users;
SELECT u.mgr_seq,u.user_id,f.user_id
FROM users u,
users f
WHERE u.mgr_seq = f.user_seq AND u.mgr_seq IS NOT NULL AND u.user_gubun = 'u';

• 1. 주문/상품/업체 대시보드 현황판
  2. 총주문수량 총주문금액 총회원수 총업체수 총상품수 -- 58 1025000 5 7 12
     • AMT PRICE UCNT CCNT GCNT
     • 48 244000 5 7 10

SELECT SUM(1) AS ocnt,SUM(tot_price) AS total FROM orders;
SELECT SUM(order_amount) AS og FROM orders_goods;
SELECT COUNT(1)AS ucnt FROM users;
SELECT COUNT(1)AS ccnt FROM company;
SELECT a.og AS 총주문수량,c.total AS 총주문금액,d.ucnt AS 총회원수,b.ccnt AS 총업체수,c.ocnt 총상품수
FROM (SELECT SUM(order_amount) AS og FROM orders_goods) a,
(SELECT COUNT(1)AS ccnt FROM company) b,
(SELECT SUM(1) AS ocnt,SUM(tot_price) AS total FROM orders) c,
(SELECT COUNT(1)AS ucnt FROM users) d;

• 1. 월별 판매 실적
  2. 1월 2월 3월 4월
  3. 20000 12000 50000

select 
    sum(decode(ss.mm,01,tt,0)) as 월1,
    sum(decode(ss.mm,02,tt,0)) as 월2,
    sum(decode(ss.mm,03,tt,0)) as 월3,
    sum(decode(ss.mm,04,tt,0)) as 월4,
    sum(decode(ss.mm,05,tt,0)) as 월5, 
    sum(decode(ss.mm,06,tt,0)) as 월6,
    sum(decode(ss.mm,07,tt,0)) as 월7,
    sum(decode(ss.mm,08,tt,0)) as 월8,
    sum(decode(ss.mm,09,tt,0)) as 월9,
    sum(decode(ss.mm,10,tt,0)) as 월10,  
    sum(decode(ss.mm,11,tt,0)) as 월11,
    sum(decode(ss.mm,12,tt,0)) as 월12
from(select sum(tot_price) as tt, to_char(order_date, 'MM') as mm
        from orders
        group by to_char(order_date, 'MM')) ss;