day 35 作业
-
查询所有大于60分的学生的姓名和学号 (DISTINCT: 去重)
select student.sid,sname,number from score left join student on student_id = student.sid where number>59;
-- 2.查询每个老师教授的课程数量 和 老师信息
mysql> select count(tid) as course_count ,tname from (select tid,tname,cname from teacher right join course on tid=course.teacher_id) as t group by tname ;
-- 3. 查询学生的信息以及学生所在的班级信息
mysql> select sname ,gender ,class_id,caption from student left join class on class_id = class.cid;
-- 4、学生中男生的个数和女生的个数
select count(gender),gender from (select sname ,gender ,class_id,caption from student left join class on class_id = class.cid) ast group by gender;
-- 5、获取所有学习'生物'的学生的学号和成绩;姓名
select student_id, sname,gender,number,cname from score left join course on score_id=course.cid left join student on student_id=student.sid where score_id=1;
-- 6、查询平均成绩大于60分的同学的学号和平均成绩;
select student.sid, number from score left join student on student_id = student.sid where number>59
-- 7、查询姓“李”的老师的个数;
select tname ,count(tname) from teacher where tname like '李%' group by tname;
-- 8、查询课程成绩小于60分的同学的学号、姓名;
mysql> select student.sid,sname, number from score left join student on student_id = student.sid where number<60;
-- 9. 删除学习“叶平”老师课的SC表记录
delete from course where cname='叶平'
-- 10.查询各科成绩最高和最低的分:以如下
形式显示:课程ID,最高分,最低分;
mysql> select score_id ,max(number),min(number) from score group by score_id;
-- 11.查询每门课程被选修的学生数
select score_id ,count(student_id) from score group by score_id;
-- 12.查询姓“张”的学生名单;
select * from student where sname like '%张';
-- 13.查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列
mysql> select score_id, avg(number) from score group by score_id order by avg(number), score_id desc;
-- 14.查询平均成绩大于85的所有学生的学号、姓名和平均成绩
mysql> select student.sid,student.sname,avg(number) from student left join score on student.sid = score.student_id group by student.sid having avg(number)>85;
-- 15.查询课程编号为3且课程成绩在80分以上的学生的学号和姓名;
select student_id,student.sname from student left join score on student.sid = score.student_id where score_id = 3 and number > 80;
Empty set (0.00 sec)
-- 16.查询各个课程及相应的选修人数
select course.cname, count(student_id) from score left join course on score.score_id=course.cid group by course.cname;
-- 17.查询“4”课程分数小于60,按分数降序排列的同学学号
select student_id from score where score_id = 4 and number<61 order by number desc;
-- 18.删除学号为“2”的同学的“1”课程的成绩
delete from score where student_id = 2 and score_id = 1;