嵌套查询练习

--1. 查询 “计算机 系”的班级信息。

select *from Class where DepartmentID=(select DepartmentID from Department where DepartmentName='计算机系')

--2. 查询“计算机系”的全部学生信息。

select *from Student

inner join Class on Class.ClassID=Student.ClassID

where Class.DepartmentID=(select DepartmentID from Department where DepartmentName='计算机系')

--3. 查询Dp010001课程中成绩未达到该门课程平均分的选课信息。

select *from Course

inner join Grade on Course.CourseID=Grade.CourseID

where Course.CourseID ='Dp010001' and Grade<(select avg(Grade) from Grade where CourseID='Dp010001')

--4. 查询Dp010001课程中最低分的学生信息。

select *from Student

inner join Grade on Student.StudentID=Grade.StudentID where Grade=(select min(Grade)from Grade where CourseID='Dp010001')

--5. 查询Cs010901班比Cs010902班年龄都大的学生信息。

select *from Student

where ClassID='Cs010901'and Birth <all(select Birth from Student where ClassID='Cs010902')

--6. 用带EXISTS子查询选修了Dp010001的学生学号和姓名

select StudentID,StudentName from Student where exists(select * from Course where CourseID='Dp010001')

--7. 查询course表中的最大学分和最小学分的课程名。

select CourseName from Course where credit =(select max(credit)  from Course) or credit=(select min(credit) from Course)

--8.查询选修了Dp010001课程而没有选修Dp010002号课程的学生学号。

select StudentID from Grade where CourseID='Dp010001'and CourseID!='Dp010002'

--9.查询计算机系选修了5门以上课程的学生的学号和姓名。

select Student.StudentID,StudentName from Student

inner join Grade on Grade.StudentID=Student.StudentID 

where exists(select DepartmentID from Department where DepartmentName='计算机系')

group by Student.StudentID,StudentName having count(Grade)>=5