sql - Display course with which we earned the most -

i trying learn new skills in oracle sql searching examples on web. found one, 6 questions. made tables , connections. knew how solve 5 questions this, last 1 seems hard me.

i don't know how display tables course earned most. have check preson visited course , person paid bill, type of price on bill (normal od student).

i realy happy if guys me.

thanks lot!

here printscreen of e-r model , code of oracle sql:

http://imgur.com/om5ibtl,
http://pastebin.com/c1cqf3k8

if don't understand despite bad english or anythning else, feel free ask. realy happy if solve problem!

you need query. used visitor's age check type of bill, because design wasn't possible. in column bill.fk_student store weird gives no valuable information, it's not clear how table bill joins other tables. anyway here have example how build multiple joins , manipulate data:

sqlfiddle

with data (   select c.id id, c.name, l.id lid, fk_visitor vid, p.price     course c        join lecture l on l.fk_course = c.id       join visiting vg on vg.fk_lecture = l.id       join visitor vr on vr.id = vg.fk_visitor       join price p on p.fk_course = c.id         , price_type = case when vr.age < 25 'student' else 'normal' end ) select id, name, sum(price) income    data   group id, name   order income desc 

output:

        id name                     income ---------- -------------------- ----------          1 programming                1170           6 acting                      500           2 mathematic                  440           4 diving                      310           5 driving                     200           3 swimming                    200 

to lucrative course first row output using rownum, possible solutions functions row_number() or rank().