수업 SQL Query 예제 (데이터 포함)

--선생님(store - menu - review 조인)

select 

  smenu.sno, min(smenu.sname) ,min(smenu.regdate) ,min(viewcnt), 

  count(reviewno) , avg( nvl(score,0))

from  (

  select 

    store.sno , menu.mno, store.sname,  store.regdate, store.viewcnt

  from  (

    select *

    from  (

      select /*+INDEX_DESC(tbl_store pk_store) */ rownum rn, sno, sname, regdate, viewcnt 

      from tbl_store 

      where sno > 0 and rownum <= 10

      )

    where rn > 0 ) store, tbl_menu menu 

  where store.sno = menu.sno(+) ) smenu, tbl_review review

where smenu.mno = review.mno(+)

group by smenu.sno;

--나(store와 review만 조인)

  select store.sno, min(sname), count(reviewno), avg(score), max(reviewdate)

  from(

    select /*+INDEX_DESC(tbl_store pk_store) */ rownum rn, sno, sname 

    from tbl_store

    where sno > 0 and rownum<=10

  ) store, tbl_review review

  where store.sno = review.sno(+)

  group by store.sno;