day35作业

  1. 查询所有大于60分的学生的姓名和学号 (DISTINCT: 去重)
select distinct sname from student left join score on student.sid=score.sid where number > 60;

2.查询每个老师教授的课程数量 和 老师信息

select tname, count(*) from teacher left join course on tid = teacher_id group by tname;
  1. 查询学生的信息以及学生所在的班级信息
select * from student left join class on class_id = cid;

4、学生中男生的个数和女生的个数

mysql> select count(*), gender from student group by gender;

5、获取所有学习'生物'的学生的学号和成绩;姓名

select student.sid, student.sname, score.number from student left join score on student.sid = score.student_id left join course on score.course_id = course.cid where course.cname = '生物';

6、查询平均成绩大于60分的同学的学号和平均成绩;

select student.sname, avg(score.number) from student left join score on student.sid = student_id group by student_id;

7、查询姓“李”的老师的个数;

select count(*) from teacher where tname like "李%";

8、查询课程成绩小于60分的同学的学号、姓名;

select student.sid, student.sname, number, course.cname from student left join score on student.sid = student_id left join course on course.cid = course_id where number < 60;
  1. 删除学习“叶平”老师课的SC表记录
create table temp as (select score.sid from score left join course on course.cid = course_id left join teacher on teacher_id = tid where tname='叶平');
delete from score where sid in (select sid from temp);
drop table temp;

10.查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分;

select course_id, max(number), min(number) from score group by course_id;

11.查询每门课程被选修的学生数

select course_id, count(*) from score group by course_id;

12.查询姓“张”的学生名单;

select * from student where sname like "张%"

13.查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列

select course_id, avg(number) from score group by course_id order by avg(number) asc, course_id desc;

14.查询平均成绩大于85的所有学生的学号、姓名和平均成绩

select student.sname, student.sid, avg(number) from student left join score on student.sid = student_id group by student_id having avg(number) > 80;

15.查询课程编号为3且课程成绩在80分以上的学生的学号和姓名;

select student.sname, student.sid from student left join score on student.sid=student_id where number > 80 and course_id = 3;

16.查询各个课程及相应的选修人数

select cname, course_id, count(*) from score left join course on course.cid = course_id group by course_id;

17.查询“4”课程分数小于60,按分数降序排列的同学学号

select student_id from score where course_id = 4 and number < 60 order by student_id desc;

18.删除学号为“2”的同学的“1”课程的成绩

delete from score where student_id=2 and course_id=1;
posted @ 2019-10-30 22:52  YajunRan  阅读(111)  评论(0编辑  收藏  举报