1、查询参加考试的学生的名单。
select s.StudentName,r.SubjectId from
Student as s
inner join Result r on r.StudentNo = s.StudentNo
group by s.StudentName,r.SubjectId
2、查询'夏一桐'每个科目的平均成绩。
select s.StudentName,avg(r.StudentResult) from
Student as s
inner join Result r on r.StudentNo = s.StudentNo
where s.StudentName = '夏一桐'
group by s.StudentName,r.StudentResult
3、查询哪个学生哪些科目多次补考。
select Student.StudentName,`Subject`.SubjectName from Student,`Subject`,Result
where Result.StudentNo = Student.StudentNo
and Result.SubjectId = `Subject`.SubjectId
GROUP BY Student.StudentName,`Subject`.SubjectName
HAVING count(1) >1
4、查询每个学员的年龄信息,将小于30岁的按年龄的从大到小列表出来。
select TIMESTAMPDIFF(YEAR,BornDate,CURDATE())AS 'age' from Student
where TIMESTAMPDIFF(YEAR,BornDate,CURDATE())<30
ORDER BY age DESC;
5、查询'白燕'学了哪些科目?
select sd.StudentName,sj.SubjectName from
Student as sd
inner join Subject sj on sj.GradeId = sd.GradeId
where sd.StudentName = '白燕'
group by sd.StudentName,sj.SubjectName
6、每页显示3条记录,查询第5页的记录。
select * from Student limit 15,3