一道sql面试题

Question

tables;
1.Student
studentID, name, DOB, address2.Major
MajorID, name,credit3.StudentMajor
SMID,studentID, MajorID, pass4.Teacher
TeacherID,Name, DOB,Address, Salary5.TeacherMajor
TMID,TeacherID, MajorIDQuestion 1:
Please write a SQL statement which can retrieve the total credit of the students they grant.  
Sample output:
StudentID   Name                                               TotalCredit 
----------- -------------------------------------------------- ----------- 
1           Alan Tsui                                          8           
2           Ronstar                                            9           
3           Steven                                             9           
Question 2:
Please write a SQL statement to show which major has the highest PASS rate and which teacher teaches the major. (Hints: get the top 1 major with count (passed student) and then join the teacher table)
Sample output:
TeacherID   TeacherName             MajorID       TotalPass       Name
----------- ----------------------- ------------- -------------- -----------------------
1           Professor Ronstar        2             3              Business Management 

Answer 1

1.
select a.Student,a.name,sum(b.MajorID) as 'TotalCredit'  from Student a ,Major b ,StudentMajor c where a.studentID= c.studentID and  b.MajorID= c.MajorID group by a.igroupid,a.cgroupname HAVING a.igroupid > 2

Answer 2

1. 正解:
select a.Student,a.name,sum(b.MajorID) as 'TotalCredit'  from Student a ,Major b ,StudentMajor c where a.studentID= c.studentID and  b.MajorID= c.MajorID group by a.Student,a.name

Answer 3

2.
select a.TeacherID,a.name as 'TeacherName',b.MajorID,
 count(c.studentID) as 'TotalPass',b.name as 'Name'
 from Teacher a,Major b,StudentMajor c,TeacherMajor d
where a.TeacherID = d.TeacherID and d.MajorID = b.MajorID 
and d.pass >= b.credit
group by a.TeacherID,a.name,b.MajorID,b.name

Answer 4

感觉好像Q2的回答有点怪……
首先pass是studentMajor表中的字段。不过我理解这个pass应该是一个bool值（BIT），表示某个学生的某门课是否通过，感觉应该用集合函数MAX和COUNT，不过条件的确比较复杂……