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
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
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
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
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
首先pass是studentMajor表中的字段。不过我理解这个pass应该是一个bool值(BIT),表示某个学生的某门课是否通过,感觉应该用集合函数MAX和COUNT,不过条件的确比较复杂……