MySQL基础训练50题之1~10

查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

select s.s_id "学号",s.s_name "姓名",round(avg(sc.s_score),1) "平均分" from student s inner join score sc on sc.s_id=s.s_id group by s.s_id HAVING 平均分>=60

查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)

SELECT s.s_id "学号",s.s_name "姓名",round(avg(sc.s_score),1) "平均分" FROM student s RIGHT JOIN score sc on sc.s_id=s.s_id GROUP BY s.s_id HAVING 平均分<60

查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

SELECT s.s_id 学号,s.s_name 姓名,COUNT(sc.c_id) 选课总数, SUM(sc.s_score) FROM student s INNER JOIN score sc ON sc.s_id=s.s_id GROUP BY s.s_id

查询"李"姓老师的数量

SELECT COUNT(t_name) 李姓老师的数量 FROM teacher WHERE t_name LIKE "李%"

查询学过"张三"老师授课的同学的信息

SELECT * FROM student s INNER JOIN score sc ON sc.s_id=s.s_id INNER JOIN course c on sc.c_id=c.c_id INNER JOIN teacher t ON t.t_id=c.t_id WHERE t.t_name='张三';

查询没学过"张三"老师授课的同学的信息

SELECT * FROM student s INNER JOIN score sc ON sc.s_id=s.s_id INNER JOIN course c on sc.c_id=c.c_id INNER JOIN teacher t ON t.t_id=c.t_id WHERE t.t_name!='张三';

查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

SELECT s.* from student s WHERE s.s_id in ( SELECT sc1.s_id from score sc1 INNER JOIN score sc2 ON sc1.s_id=sc2.s_id WHERE sc1.c_id=01 AND sc2.c_id=02)

查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

SELECT s.* FROM student s INNER JOIN score sc ON s.s_id=sc.s_id WHERE sc.c_id=01 and sc.c_id!=02

查询没有学全所有课程的同学的信息

select s.* from student s right join ( select s_id,count(1) "所学门数" from score group by s_id having 所学门数<3 )c1 on c1.s_id=s.s_id

查询至少有一门课与学号为"01"的同学所学相同的同学的信息

SELECT s.* from student s INNER JOIN score sc on s.s_id=sc.s_id WHERE sc.c_id IN (SELECT sc.c_id from score sc where sc.s_id=01) AND s.s_id!=01 GROUP BY s.s_id