5.Mysql多表练习题20道

1.查询“1”号学生的姓名和各科成绩

SELECT     s.id sid,     s.`name` sname,     c.`name` cname,     sc.score  FROM  student s     LEFT JOIN scores sc ON s.id = sc.s_id     LEFT JOIN course c ON c.id = sc.c_id  WHERE  s.id = 1

2.查询各个学科的平均成绩和最高成绩

SELECT     c.id,     c.`name`,     AVG( sc.score ),     max( sc.score )  FROM     course c     LEFT JOIN scores sc ON c.id = sc.c_id  GROUP BY     c.id,     c.`name`

3.查询所有姓张的同学的各科成绩

SELECT     s.id,     s.`name`,     c.`name` cname,     sc.score  FROM     student s     LEFT JOIN scores sc ON sc.s_id = s.id     LEFT JOIN course c ON c.id = sc.c_id  WHERE     s.`name` LIKE '张%'

4.查询每个同学的最高成绩和科目名称

SELECT     t.id, t.name,  c.id,   c.name, r.score  FROM     (SELECT  s.id, s.name,(SELECT max( score ) FROM scores r  WHERE  r.s_id = s.id ) score FROM   student s  ) t     LEFT JOIN scores r ON r.s_id = t.id  AND r.score = t.score     LEFT JOIN course c ON r.c_id = c.id

5.查询名字中含有'张'或'李'字的学生的信息和各科成绩

SELECT     s.id,     s.NAME sname,     sc.score,     c.NAME  FROM     student s     LEFT JOIN scores sc ON s.id = sc.s_id     LEFT JOIN course c ON sc.c_id = c.id  WHERE     s.NAME LIKE '%张%'      OR s.NAME LIKE '%李%'

6.查询平均成绩大于70的同学的信息

SELECT   *   FROM  student WHERE  id IN (         SELECT  sc.s_id  FROM  scores sc          GROUP BY  sc.s_id          HAVING avg(sc.score) >= 70)

7.将学生按照总分数进行排名。（从高到低）

SELECT     s.id,     s.NAME,     sum( sc.score ) score  FROM     student s     LEFT JOIN scores sc ON s.id = sc.s_id  GROUP BY     s.id,     s.NAME  ORDER BY     score DESC, s.id ASC

8.查询数学成绩的最高分、最低分、平均分

SELECT     c.NAME,     max( sc.score ),     min( sc.score ),     avg( sc.score )  FROM     course c     LEFT JOIN scores sc ON c.id = sc.c_id  WHERE     c.NAME = '数学'

9.将各科目按照平均分排序

SELECT     c.id,     c.NAME,     avg( sc.score ) score  FROM     course c     LEFT JOIN scores sc ON c.id = sc.c_id  GROUP BY     c.id,     c.NAME  ORDER BY     score DESC

10.查询老师的信息和他所带的科目的平均分

SELECT     t.id,     t.NAME,     c.id cid,     c.NAME cname,     avg( r.score )  FROM     teacher t     LEFT JOIN course c ON t.id = c.t_id     LEFT JOIN scores r ON r.c_id = c.id  GROUP BY     t.id,     t.NAME,     c.id,     c.NAME

11.查询被"Tom"和"Jerry"教的课程的最高分和最低分

SELECT     t.id,     t.NAME,     c.id cid,     c.NAME cname,     max( r.score ),     min( r.score )  FROM     teacher t     LEFT JOIN course c ON t.id = c.t_id     LEFT JOIN scores r ON r.c_id = c.id  GROUP BY     t.id,     t.NAME,     c.id,     c.NAME  HAVING     t.NAME IN ( 'Tom', 'Jerry' )

12.查询每个学生的最好成绩的科目名称（子查询）

SELECT     t.id,     t.sname,     r.c_id,     c.NAME,     t.score  FROM     (     SELECT         s.id,         s.NAME sname,         max( r.score ) score      FROM         student s         LEFT JOIN scores r ON r.s_id = s.id      GROUP BY         s.id,         s.NAME      ) t     LEFT JOIN scores r ON r.s_id = t.id  AND r.score = t.score     LEFT JOIN course c ON r.c_id = c.id

13.查询所有学生的课程及分数

SELECT     s.id,     s.NAME,     c.id,     c.NAME,     r.score  FROM     student s     LEFT JOIN scores r ON s.id = r.s_id     LEFT JOIN course c ON c.id = r.c_id

14.查询课程编号为1且课程成绩在60分以上的学生的学号和姓名（子查询）

SELECT     s.*,     r.*  FROM     student s     LEFT JOIN scores r ON s.id = r.s_id  WHERE     r.c_id = 1      AND r.score > 60

15.查询平均成绩大于等于70的所有学生学号、姓名和平均成绩

SELECT     s.id,     s.NAME,     t.score  FROM     student s     LEFT JOIN ( SELECT r.s_id, avg( r.score ) score FROM scores r GROUP BY r.s_id ) t ON s.id = t.s_id  WHERE     t.score >= 70

16.查询有不及格课程的学生信息

SELECT     *  FROM     student s  WHERE     id IN ( SELECT r.s_id FROM scores r GROUP BY r.s_id HAVING min( r.score ) < 60 )

17.查询每门课程有成绩的学生人数

SELECT     c.id,     c.NAME,     count(*)  FROM     course c     LEFT JOIN scores r ON c.id = r.c_id  GROUP BY     c.id,     c.NAME

18.查询每门课程的平均成绩，结果按照平均成绩降序排列，如果平均成绩相同，再按照课程编号升序排列

SELECT     c.id,     c.NAME,     avg( score ) score  FROM     course c     LEFT JOIN scores r ON c.id = r.c_id  GROUP BY     c.id,     c.NAME  ORDER BY     score DESC,     c.id ASC

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

SELECT     s.id,     s.NAME sname,     avg( r.score ) score  FROM     student s     LEFT JOIN scores r ON r.s_id = s.id     LEFT JOIN course c ON c.id = r.c_id  GROUP BY     s.id,     s.NAME  HAVING     score > 60

20.查询有且仅有一门课程成绩在80分以上的学生信息

SELECT     s.id,     s.NAME,     s.gender  FROM     student s     LEFT JOIN scores r ON s.id = r.s_id  WHERE     r.score > 80  GROUP BY     s.id,     s.NAME,     s.gender  HAVING     count(*) = 1

21.查询有不及格课程的课程信息

SELECT     *  FROM     course c  WHERE     id IN (     SELECT         r.c_id      FROM         scores r      GROUP BY         r.c_id      HAVING     min( r.score ) < 60      )