建立数据库School其中存放以下三个表：学生Student学号姓名性别年龄系别即Sno char 3 Sname char4 Ssex char 2 Sage int Sdept char6；课程Course课程号课程名任课教师即Cno char3 Cname char8 teacher char4；选修SC学号课程号分数即Sno char 3 Cno char 3 Grade int。 查询各

1. 查询各系学生的选课平均成绩及其系名：

SELECT Sdept AS '系名', AVG(Grade) AS '平均成绩' FROM Student, SC WHERE Student.Sno = SC.Sno GROUP BY Sdept;

2. 查询每门课的平均成绩及其课程名：

SELECT Cname AS '课程名', AVG(Grade) AS '平均成绩' FROM Course, SC WHERE Course.Cno = SC.Cno GROUP BY Cname;

3. 查询平均成绩在75以上的学生姓名：

SELECT Sname AS '姓名' FROM Student, SC WHERE Student.Sno = SC.Sno AND Grade >= 75 GROUP BY Sname;

4. 查询选修了2门以上课程的学生姓名：

SELECT Sname AS '姓名' FROM Student, SC WHERE Student.Sno = SC.Sno GROUP BY Sname HAVING COUNT(Cno) >= 2;

5. 查询至少选修了张三所选修的一门课程的学生姓名及其课程名：

SELECT Sname AS '姓名', Cname AS '课程名' FROM Student, Course, SC WHERE Student.Sno = SC.Sno AND Course.Cno = SC.Cno AND EXISTS ( SELECT * FROM SC, Student WHERE SC.Sno = Student.Sno AND Student.Sname = '张三' AND SC.Cno = '001' )