Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
noman0008
Starting Member
3 Posts |
 Posted - 2014-09-27 : 15:18:30
|
| Here is the schema that i have:Students (sid, name, gender, age, dept, GPA) Courses (cid, name, credit) Take (sid, cid, term, year, grade) The grade is a letter grade, e.g. A+, A, ... Gradepoints (grade, points) This table maps a letter grade to a grade in the scale of 0-4. For example, (A-, 3.7) is a tuple in it. RequiredCourse (dept, cid) This table records the required courses a student must take before receiving BA degree from a dept. Group (gid, cid, term, project, grade) InGroup (gid, sid)A student can get the degree from his/her department if he/she has taken a total of 90 credit hours’ courses, has a overall GPA over 2.0, and has taken all required courses. Please find the sid and name of the students who can get the degree from his/her department.I can't find out the last thing: how to check whether he/she has taken all required courses?Thanks. |
|
|
noman0008
Starting Member
3 Posts |
Posted - 2014-09-27 : 15:19:25
|
| Here is what i got so far:select sid, name from Students a inner join (select b.sid, sum(a.credit) as TotCredit from Courses a, take b where a.cid=b.cid group by b.sid)p on a.sid=p.sid and p.TotCredit>=90 where a.Gpa>2 |
 |
|
|
pbannist
Starting Member
3 Posts |
Posted - 2014-10-03 : 04:08:17
|
| AND cid IN (SELECT cid FROM RequiredCourse) |
|
|
|
|
|
|
|
|
SQL query help2