Java面试中出现率极高的数据库查询题-木庄网络博客

本文摘自PHP中文网，作者little bottle，侵删。

经历过面试的程序员都知道，面试过程中面试官可能对你问出千奇百怪的问题，但万变不离其宗，终归也是要问道重点上来，比如像是某一工作的基本操作步骤以及代码的如何编写等等，本文讲的就是最最经典的数据库查询问题。

基本表结构：

teacher(tno,tname) 教师表

student(sno,sname,sage,ssex)学生表

course(cno,cname,tno) 课程表

sc(sno,cno,score) 成绩表

NO.1查询课程1的成绩比课程2的成绩高的所有学生的学号

1	`select` `a.sno` `from(select` `sno,score` `from` `sc` `where` `cno=1) a,(select` `sno,score` `from` `sc` `where` `cno=2) bwhere a.score>b.score` `and` `a.sno=b.sno`

NO.2查询平均成绩大于60分的同学的学号和平均成绩

1	`select` `a.sno` `as` `"学号",` `avg(a.score)` `as` `"平均成绩"` `from(select` `sno,score` `from` `sc) a` `group` `by` `sno` `having` `avg(a.score)>60`

NO.2查询所有同学的学号、姓名、选课数、总成绩

1	`select` `a.sno` `as` `学号, b.sname` `as` `姓名,count(a.cno)` `as` `选课数,` `sum(a.score)` `as` `总成绩from` `sc a, student bwhere a.sno = b.snogroup` `by` `a.sno, b.sname`

或者：

1	`selectstudent.sno` `as` `学号, student.sname` `as` `姓名,` `count(sc.cno)` `as` `选课数,` `sum(score)` `as` `总成绩from` `student` `left` `Outer` `join` `sc` `on` `student.sno = sc.snogroup` `by` `student.sno, sname`

NO.3查询姓“张”的老师的个数

1	`selectcount(distinct(tname))` `from` `teacher` `where` `tname` `like` `'张%‘`

或者：

1	`select` `tname` `as` `"姓名",` `count(distinct(tname))` `as` `"人数"` `from` `teacher` `where` `tname` `like'张%'group` `by` `tname`

NO.4查询没学过“张三”老师课的同学的学号、姓名

select student.sno,student.sname from student
where sno not in (select distinct(sc.sno) from sc,course,teacher
where sc.cno=course.cno and teacher.tno=course.tno and teacher.tname='张三')

NO.5查询同时学过课程1和课程2的同学的学号、姓名

1	`select` `sno, sname` `from` `studentwhere sno` `in` `(select` `sno` `from` `sc` `where` `sc.cno = 1)and` `sno` `in` `(select` `sno` `from` `sc` `where` `sc.cno = 2)`

或者：

1	`selectc.sno, c.sname` `from(select` `sno` `from` `sc` `where` `sc.cno = 1) a,(select` `sno` `from` `sc` `where` `sc.cno = 2) b,student cwhere a.sno = b.sno` `and` `a.sno = c.sno`

或者：

1	`select` `student.sno,student.sname` `from` `student,sc` `where` `student.sno=sc.sno` `and` `sc.cno=1and exists(` `select` `*` `from` `sc` `as` `sc_2` `where` `sc_2.sno=sc.sno` `and` `sc_2.cno=2)`

NO.6查询学过“李四”老师所教所有课程的所有同学的学号、姓名

1	`select` `a.sno, a.sname` `from` `student a, sc bwhere a.sno = b.sno` `and` `b.cno` `in(select` `c.cno` `from` `course c, teacher d` `where` `c.tno = d.tno` `and` `d.tname =` `'李四')`

或者：

1	`select` `a.sno, a.sname` `from` `student a, sc b,(select` `c.cno` `from` `course c, teacher d` `where` `c.tno = d.tno` `and` `d.tname =` `'李四') ewhere a.sno = b.sno` `and` `b.cno = e.cno`

NO.7查询课程编号1的成绩比课程编号2的成绩高的所有同学的学号、姓名

select a.sno, a.sname from student a,(select sno, score from sc where cno = 1) b,(select sno, score from sc where cno = 2) cwhere b.score > c.score and b.sno = c.sno and a.sno = b.sno 

NO.8查询所有课程成绩小于60分的同学的学号、姓名

1	`select` `sno,sname` `from` `studentwhere sno` `not` `in` `(select` `distinct` `sno` `from` `sc` `where` `score > 60)`

NO.9查询至少有一门课程与学号为1的同学所学课程相同的同学的学号和姓名

1	`select` `distinct` `a.sno, a.snamefrom student a, sc bwhere a.sno <> 1` `and` `a.sno=b.sno andb.cno` `in` `(select` `cno` `from` `sc` `where` `sno = 1)`

或者：

1	`select` `s.sno,s.sname` `from` `student s,(select` `sc.sno` `from` `scwhere sc.cno` `in` `(select` `sc1.cno` `from` `sc sc1` `where` `sc1.sno=1)and` `sc.sno<>1group` `by` `sc.sno)r1where r1.sno=s.sno`