我们来演示一个数据库练习题中的HelloWorld表吧
student(#id, name)
course(#id, name)
grade(#sid, #cid, grade)
?
1. 查出选修的所有课程的学生姓名
select name from grade, student where grade.sid = student.id group by sid having count(distinct cid) = (select count(distinct id) from course);
?
2. 查询选修了 数学的 学生姓名
select name from grade, student where grade.sid = student.id and cid in (select id from course where name like '数学');
或者
select name from student where id in (select sid from grade, course where course.id = grade.cid and course.name like '数学');
?
3. 查询了至少选修了一门课的学生的姓名
select name from student, grade where student.id = grade.sid group by sid having count(distinct cid)>0;
?
4.查询不选修课程号为’5‘的学生姓名
select name from student where id not in (select distinct sid from grade where cid = '5');
?
5.查询了选修的课程的学生人数
select count(distinct sid) as '学员人数' from grade;
?
6.查询选修了5门及以上课程的学生姓名
select name from student, grade where student.id = grade.sid group by sid having count(distinct cid) >= 5;
?
7.查询2门以上不合格成绩及以上的学生姓名和平均成绩
select name, avg(grade) as '平均成绩', sum(grade<60) as '不及格门数' from student, grade where student.id = grade.sid group by sid having sum(grade<60)>=2;
?
8.查询既选修了1，又选修了2课程的学生姓名
select name from student, grade where student.id = grade.sid group by sid having sum(case when (cid=1 or cid=2) then 1 else 0 end)=2;?
或者
select name from grade, student where grade.sid = student.id and cid in ('1', '2') group by sid having count(distinct cid) = 2;