数据库题目集锦_软件测试工程师

1.假设有一个教学管理数据库有以下3个关系：

学生表：Students(学号sno,姓名sname,年龄age,性别sex,籍贯jg)、
课程表：Courses(课程号cno,课程名cname,学分xf)、
选课表：Enrolls(学号sno,课程号cno,分数score)：
2.1查询哪些课程只有女生选读；
2.2查各课程的平均成绩，按课程号分组，只选择学生超过3人的课程成绩；
2.3找出不选“数据库”课程的学生学号、姓名；
2.4找出选修了全部课程的学生姓名；
2.5求平均成绩90分以上，选课男生人数3人以上的课程名。
-- 2.1
select ee.* from enrolls ee 
where ee.cno not in 
(select b.cno 
from 
   (select e.* from enrolls e 
   where e.sno in (select s.sno from students s where s.sex='女')) a 
right join enrolls b 
on a.sno=b.sno 
where a.sno is null)
-- 2.2
select avg(score) from Enrolls e  group by e.cno having count(1)>3;
-- 2.3
select s.sno,s.sname from Students s 
where s.sno in (select e.sno from Enrolls e where e.cno !=(select c.cno from Courses c where c.cname='数据库'))
 
-- 2.4
select s.sname from Students s 
where s.sno in (select e.sno from Enrolls e group by e.sno having count(1)=(select count(1) from Courses c))
-- 2.5
select c.cname from Courses c where c.cno in (select e.cno from Enrolls e group by e.cno having count(1)>3 and avg(score)>90)