现在又一个库作为学生信息库,有三个表,课程,成绩,学生信息。
1、打印学生的选课数,课程总分,平均分
select student_id,count(course_id),sum(score),avg(score) from xiangzai.score group by student_id;
2、打印学生id,选课数,总成绩,平均成绩,最高分数,最低分数
select stu.student_id,count(s.course_id),sum(s.score),avg(s.score),max(s.score),min(s.score) from xiangzai.student stu,xiangzai.score s where s.student_id=stu.student_id group by stu.student_id;
3、打印成绩平均分大于60的学生的ID,姓名和平均分
select s.student_id,stu.name,avg(s.score) from xiangzai.score s,xiangzai.student stu where s.student_id=stu.student_id group by student_id having avg(score)>60;
4、打印选课不齐全的学生的id和姓名,并显示选课数
select stu.student_id,stu.name from xiangzai.student stu,xiangzai.score s where stu.student_id=s.student_id group by s.student_id having count(*)<(select count(*) from xiangzai.course);
5、打印每天数据量
select date_format(created_date,'%Y-%M-%d') as time,count(created_date) from wenda.comment group by time;