SQL - Not sure if I should use joins to resolve this query -


i have following question presented me:

"write sql command display each student has average result of greater 60, student’s name, total number of attempts student has had @ exams, , student’s average result."

the tables working are: mark, fields examid(pk), studentid(pk), result, occurred, noofattempts

and student fields id(pk) , name.

the field studentid in mark foreign key student.

anyways i've come far:

select s.name, avg(m.result) student s, mark m  s.id = ( select studentid mark 60 <  (select avg(result) mark)); 

but igiving me "not single group function" error. i've tried using other joins resulted in whole ton of errors i'm pretty sure did wrong.

at point lost. know "deepest" subquery return me student id's have average result bigger 60, when try return student id's match particular id doesnt work. i'm not sure how count total no. of attempts in query didnt attempt now.

table data: mark

student

you can try that:

select      s.name, sum(m.noofattempts) attempts, avg(m.result) result     student s inner join     mark m on (s.id = m.studentid) group     s.id, s.name having      avg(m.result) > 60 

Comments