I am guessing there is a mistake in the nested query however I cannot seem to find it myself. Here is the query:
Select student.sid, name
from student join exam on exam.sid = student.sid
where student.sid in (select *
from course join exam on cid=courseid
group by exam.sid
having sum(credits) >= 20)
Thank you in advance!
You can use the group by as follows:
select s.sid, s.name
from student s
Join exam e on s.sid = e.sid
Join course c on c.cid = e.courseid
group by s.sid, s.name
having sum(c.credits) >= 20
Yes, there is a mistake. The nested query is returning multiple columns but the comparison is to only a single column.
This will obviously generate an error. Presumably, you don't want the sum() but without sample data, desired results, and an explanation of what the query is supposed to be doing, it is hard to make a concrete suggestion.
The query may have other errors, but presumably, you intend something like this:
select s.sid, s.name
from student s
where s.sid in (select e.sid,
from course c join
exam e
on c.cid = e.courseid
group by e.sid
having sum(c.credits) >= 20
)
exists allows any number of columns, although it ignores the values in them (I just use exists (select 1 . . . ).