数据库练习题

1.查询学生科目1的成绩高于平均分的学生信息

select * from student

where id in(

select stu_id from score //返回学号

where score >//查询科目一分数大于平均分

(select avg(score) from score where subject_id=10001 )//查询科目一平均分

and subject_id=10001)

2.查询所有科目都在所在科目平均分以上的学生信息

select student.* from student

    where id IN//查询学生信息

       (select stu_id from score a  

             where score >

             (select avg(score) from score b

                 where a.subject_id=b.subject_id

                 group by b.subject_id)//第三层，查询选修课程大于该科平均分的结果

             group by stu_id HAVING COUNT(score)=3)//第二层查询选修的所有课程大于平均分的结果

 

 

1. 查询总成绩最高的学生信息和最低的学生信息以及总成绩类似第7题

select  * from(//输出第一行

SELECTstudent.id,student.`name`,student.sex,student.address,student.classid,score.score,SUM(score)

FROM student INNER JOIN score ON score.stu_id = student.id

group by id

ORDER BY SUM(score)desc) a

LIMIT 1

 

1. 查各科成绩最高的学生信息

select * from student where id in(//查询学生信息

     select stu_id //通过成绩嵌套查询各科最高分学生学号，

     from score a

     where score =(select MAX(score) from score b //查询各科最高分，无法输出对应学号

                   where a.subject_id=b.subject_id

                   group by subject_id having MAX(DISTINCT score) ) )

 

5.查询各科成绩都是优秀的学生信息（分数大于80）

select * from student where id in(//学生信息查询

         select stu_id from score where score>80  GROUP BY stu_id HAVING COUNT(stu_id)=3)//查询各科成绩都的学生学号

 

6.查询各班平均成绩

SELECT student.classid,score.subject_id,AVG(score)

FROM student  LEFT JOIN score ON score.stu_id = student.id

group by classid,subject_id（会使个数减一）

表设置有问题，一个学生没有选课、没有成绩，不正常
7.查询各班最高分学生的信息
select student.* from student//查询101班最高分学生信息

where id in(

select stu_id from//查询101班最高分学号

(SELECT//派生表

sum(score) result,stu_id

FROM

score

INNER JOIN student ON score.stu_id = student.id//连接，筛选班级

where classid=101

GROUP BY stu_id//统计每个学生总成绩

order by SUM(score) desc) a//降序排列，保证返回最高分学号正确，否则返回第一行学号

HAVING MAX(result))

 

 

select student.* from student

where id in(

select stu_id from

(SELECT

sum(score) result,stu_id

FROM

score

INNER JOIN student ON score.stu_id = student.id

where classid=101

GROUP BY stu_id

order by SUM(score) desc) a

HAVING MAX(result))

 

select student.* from student//求并集

where id in(

select stu_id from

(SELECT

sum(score) result,stu_id

FROM

score

INNER JOIN student ON score.stu_id = student.id

where classid=101

GROUP BY stu_id

order by SUM(score) desc) a

HAVING MAX(result))

UNION

select student.* from student

where id in(

select stu_id from

(SELECT

sum(score) result,stu_id

FROM

score

INNER JOIN student ON score.stu_id = student.id

where classid=102

GROUP BY stu_id

order by SUM(score) desc) a

HAVING MAX(result))

8.查询所有学生各科成绩按照总成绩降序排列。

select score.*,a.* from score,//连接查询

(SELECT student.id,student.`name`,student.classid,SUM(score)

FROM score RIGHT OUTER JOIN student ON score.stu_id = student.id

GROUP BY id

order by SUM(score) desc//查询总和，降序排列

) a

where score.stu_id=a.id