Oracle 复杂查询

1. 相关表结构

TB_Dept(DeptID,DeptName,DeptSetDate)
TB_Teacher(TeacherID,TeacherName,DeptID,Sex,Birthday),TeacherID char(6),TeacherName char(8)
TB_Class(ClassID,ClassName,DeptID,TeacherID)
TB_Student(StuID,StuName,DeptID,ClassID,Sex,Birthday)
TB_Course(CourseID,CourseName,DeptID,CourseGrade,LessonTime)
TB_CourseClass(CourseClassID,CourseID,TeacherID,CommonPart,MiddlePart,LastPart)
TB_SelectCourse(StuID,CourseClassID,SelectDate)
TB_Grade(StuID,ClassID,CourseClassID,CourseID,CommonScore,MiddleScore,LastScore,TotalScore,RetestScore)

2. 查询电子工程系所有男生的学号、姓名、性别、生日，按生日降序排列。

SQL> SELECT StuID,StuName,Sex,Birthday
  2  FROM TB_Student
  3  WHERE Sex='M'
  4  and DeptID='03'
  5  order by Birthday DESC;

3. 查询有二门以上（含二门）低于60分的课程的学生姓名及其平均成绩。

SQL> select StuName,AVG(TotalScore)
  2  from TB_Student join TB_Grade on TB_Student.StuID=TB_Grade.StuID
  3  where StuName in ( select StuName
  4  from TB_Student join TB_Grade on TB_Student.StuID=TB_Grade.StuID
  5  where TotalScore<=60
  6  group by StuName
  7  having count(*)>=2)
  8  group by StuName;

4. 根据课程班和任课教师对学生平均分进行分类汇总，并显示平均成绩大于75分的信息，要求显示标题为：课程班号、教师姓名、平均分。

SQL> select TB_CourseClass.CourseClassID, TeacherName,AVG(TotalScore)
  2  from TB_Grade left outer join TB_courseclass on TB_Grade. CourseClassID=TB_CourseClass.CourseClassID left outer join TB_Teacher on
  3  TB_CourseClass.TeacherID = TB_Teacher.TeacherID
  4  group by TB_CourseClass.CourseClassID, TeacherName, TB_Grade.StuID
  5  having AVG(TotalScore)>=75 ;

5. 统计各个系各个班的总评平均成绩，要求列出系编号、系名、班编号、班名及总评平均成绩，按系编号及班编号升序排列。

SQL> set linesize 200
SQL> SELECT ClassID,ClassName,DeptID,DeptName,avg(TotalScore)
  2  FROM TB_Dept NATURAL
  3  JOIN TB_Class NATURAL JOIN TB_Grade GROUP BY ClassID,ClassName,DeptID,DeptName
  4  HAVING avg(TotalScore)>=0
  5  order by DeptID,ClassID;

6. 统计每个学生的选课数（包括没有选课的学生），列出学号、姓名、选课门数。

SQL> select TB_Student. StuID,StuName,count(CourseClassID)
  2             from TB_Student left outer join
  3  TB_SelectCourse on TB_Student. StuID = TB_SelectCourse. StuID
  4             group by TB_Student. StuID,StuName;

7. 找出没有选修任何课程的学生的学号、姓名及所在系名。

SQL> SELECT StuID, StuName, DeptName
  2  FROM TB_Student, TB_Dept
  3  WHERE NOT EXISTS(
  4  SELECT *
  5  FROM TB_SelectCourse
  6  WHERE EXISTS(
  7  SELECT *
  8  FROM TB_Grade
  9  WHERE StuID= TB_Student.StuID
 10  AND CourseClassID= TB_SelectCourse. CourseClassID
 11  )
 12  )
 13  AND TB_Student.DeptID = TB_Dept.DeptID;

8. 计算机系开设的课程中哪些课程平均成绩最高？要求列出课程号、课程名及平均成绩。

SQL> select TB_Grade.CourseID,CourseName,avg(TotalScore)
  2  from TB_Grade natural join TB_Class natural join TB_Dept join TB_Course on TB_Grade.CourseID=TB_Course.CourseID
  3  where TB_Course.DeptID='08'
  4  group by TB_Grade.CourseID,CourseName
  5  having avg(TotalScore)>=all(select avg(TotalScore)
  6  from TB_Grade natural join TB_Class natural join TB_Dept join TB_Course on TB_Grade.CourseID=TB_Course.CourseID
  7  where TB_Course.DeptID='08'
  8  group by TB_Grade.CourseID)
  9  order by CourseName;

9. 找出C08003号课程比C08004号课程成绩高的所有学生的姓名。

SQL> SELECT StuName
  2  FROM TB_Student,TB_Grade
  3  WHERE TB_Student.StuID= TB_Grade.StuID
  4  and CourseID='C08003' and TotalScore >
  5  ( SELECT MAX(TotalScore)
  6  from TB_Grade
  7  WHERE CourseID='C08004');