Java常用面试题之数据库篇（十一）

基本表结构：

    student(sno,sname,sage,ssex)学生表
    course(cno,cname,tno) 课程表
    sc(sno,cno,score) 成绩表
    teacher(tno,tname) 教师表
    
101，查询课程1的成绩比课程2的成绩高的所有学生的学号

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

102，查询平均成绩大于60分的同学的学号和平均成绩

	select a.sno as "学号", avg(a.score) as "平均成绩" 
	from
	(select sno,score from sc) a 
	group by sno having avg(a.score)>60
	
103，查询所有同学的学号、姓名、选课数、总成绩

	select a.sno as 学号, b.sname as 姓名