数据库查询练习

department(dNo,dName,officeRoom,homepage)
student(sNo,sName,sex,age,dNo)
course(cNo,cName,cPNo,credit,dNo)
sc(sNo,cNo,score,recordDate)

1、综合
（1）查询所有学生及其选课信息（包括没有选课的学生）；
（2）查询“形式语言与自动机”先修课的课程名称；
（3）查询“形式语言与自动机”间接先修课课程名称；
（4）查询先修课为组合数学的课程名称；
（5）查询所有没有先修课的课程名称；
（6）查询所有没选修组合数学课程的学生姓名；
（7）查询所有选修了组合数学但没选修其先修课的学生姓名；
（8）查询选修课程总学分大于等于8的学生姓名；
（9）查询选修了3门以上课程且成绩都大于85分的学生学号与姓名；
（10）查询恰好选修了3门课并且都及格的学生姓名；
（11）查询人数多于10的学院名称；
（12）查询平均成绩高于张三的学生姓名；
（13）查询所有选修了离散数学并且选修了组合数学课程的学生姓名；
（14）查询软件学院离散数学课程平均分；
（15）查询平均学分积小于70分的学生姓名。
（16）查询选修了“信息学院”开设全部课程的学生姓名。
（17）查询选修了“杨佳伟”同学所选修的全部课程的学生姓名。


（1）查询所有学生及其选课信息（包括没有选课的学生）；

select *
from student left outer join sc on (student.Sno=sc.Sno)

（2）查询“形式语言与自动机”先修课的课程名称；

select Cname
from course
where Cno in
(
select Cpno
from course
where Cname = '形式语言与自动机'
)


（3）查询“形式语言与自动机”间接先修课课程名称；

select Cname
from course
where Cno in
(
select Cpno
from course
where Cno in
(
select Cpno
from course
where Cname = '形式语言与自动机'
)
)

（4）查询先修课为组合数学的课程名称；

select Cname
from course
where Cpno in
(
select Cno
from course
where Cname = '信号与系统'
)


（5）查询所有没有先修课的课程名称；

select Cname
from course
where Cpno is null

（6）查询所有没选修组合数学课程的学生姓名；

select Sname
from student
where Sno in
(
select distinct Sno
from sc
where not exists 
(
select Cno
from course
where Cname = '组合数学'
)
)

（7）查询所有选修了组合数学但没选修其先修课的学生姓名；

select Sname
from student
where Sno in
(
	select distinct Sno
	from sc
	where Cno in
	(
	select Cno
	from course
	where Cname = '概率论'
	)
union
	select Cno
	from course
	where Cno in
	(
	select Cpno
	from course
	where Cname = '概率论'
	)
)

（8）查询选修课程总学分大于等于8的学生姓名；
select Sname
from student
where Sno in
(	select sno
    from course,sc
    group by sc.sno
    having  SUM(Ccredit)>=8
)
（9）查询选修了3门以上课程且成绩都大于85分的学生学号与姓名；
select Sname
from student
where Sno in
(	select sc.sno
    from sc
    where grade>=85
    group by sc.sno
    having  count(*)>=3 
)
（10）查询恰好选修了3门课并且都及格的学生姓名；
select Sname
from student
where Sno in
(	select sc.sno
    from sc
    where grade>=60
    group by sc.sno
    having  count(*)=3
   ) 
（11）查询人数多于10的学院名称
select dName
from department
where dNo in
(	select dNo
    from student  
    group by student.dNo
    having  count(*)>=10
   ) 

（12）查询平均成绩高于张三的学生姓名；
select sname
from student
where Sno in 
(
select Sno 
from sc
group by sc.Sno
having AVG(grade)>
(
	select AVG(grade)
	from sc,student
	where Sname='安相成' and student.Sno=sc.Sno
)
)
（13）查询所有选修了离散数学并且选修了组合数学课程的学生姓名；
select sname
from student
where sno in
(
		select sno
		from sc,course 
		where sc.Cno=course.Cno and course.cname='程序设计基础'
		intersect
		select sno
		from sc,course
		where sc.Cno=course.Cno and course.cname='离散数学' 
)

（14）查询软件学院离散数学课程平均分；
select AVG(grade) as 平均分
from sc
where cno in
(
	select sc.cno
	from sc,course
	where sc.Cno=course.Cno and Cname='离散数学' 
union
	select student.sno
	from department,student
	where department.dNo=student.dNo and dName='软件学院' 
)
或者
select avg(grade)
	from sc
	where cNo = (select cNo
		       from course
		      where cName = '离散数学')
（15）查询平均学分积小于70分的学生姓名。

select sname 
from student 
where sno in
(select sc.sno 
 from sc,course 
 where sc.cno=course.cno and grade is not null and Ccredit is not null 
 group by sno 
 having SUM(grade*Ccredit)/SUM(Ccredit)<70)
（16）查询选修了“信息学院”开设全部课程的学生姓名。

select sName
from student
where sNo not in 
(   select sNo
	from sc
	where cNo not in 
	( select cNo
	  from course
	  where dNo = 
	  (   select dNo
	     from department
	      where dName = '信息学院')
	)
)


（17）查询选修了“杨佳伟”同学所选修的全部课程的学生姓名。
select sName
from student s
where sNo in 
(select sNo
from sc
where cNo in 
(select cNo
from sc
where sNo = 
(select sNo
from student
where sName = '杨佳伟')
)
)