一些简略的sql语句应付面试

一些简单的sql语句应付面试

我们来演示一个数据库练习题中的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;