mysql实战基础笔试题

1. 给定四个表

student（学生表）
teacher（老师表）
course（课程表）
sc（成绩表）

「表结构以及部分数据如下图展示」

根据题目要求，写出 SQL 语句。

「问题列表」

1、查询每个学生的学号、姓名和每门课的成绩
2、查询都学过2号同学（sid=2）学习过的课程的同学的学号
3、查询“语文（cid=1）”课程比“数学（cid=2）”课程成绩高的所有学生的学号；
4、查询平均成绩大于60分的同学的学号和平均成绩；
5、查询所有同学的学号、姓名、选课数、总成绩；
6、查询姓“李”的老师的个数；
7、查询没学过“叶平”老师课的同学的学号、姓名； 
8、查询学过“语文（cid=1）”并且也学过“数学（cid=2）”课程的同学的学号、姓名； 
9、查询学过“叶平”老师所教的所有课的同学的学号、姓名；
10、查询课程编号“数学（cid=2）”的成绩比课程编号“语文（cid=1）”课程低的所有同学的学号、姓名；
11、查询所有课程成绩小于60分的同学的学号、姓名； 
12、查询没有学全所有课的同学的学号、姓名； 
13、按平均成绩从高到低显示所有学生的“语文“、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生
    ID,语文,数学,英语,有效课程数,有效平均分 
14、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分 
15、查询男生、女生人数, 以如下形式显示：男生人数，女生人数
16、查询课程名称为“数学”，且分数低于60的学生姓名和分数 
17、查询两门以上不及格课程的同学的学号及其平均成绩；
18、检索“cid=4”课程分数小于60，按分数降序排列的同学学号 

1、查询每个学生的学号、姓名和每门课的成绩；

（1）利用隐式内联，只输出两表中都有的字段

SQL语句：
SELECT 
 student.sid AS 学生ID, 
    student.sname AS 学生姓名, 
    (SELECT course.cname FROM course WHERE course.cid=sc.cid) AS 课程名称, 
    sc.score AS 成绩 
FROM student,sc 
WHERE student.sid = sc.sid

（2）利用左连接，假如左表有，右表没有，则按左表为准，右表缺省字段置为 NULL

SQL 语句：

SELECT 
 student.sid AS 学生ID, 
    student.sname AS 学生姓名, 
    (SELECT course.cname FROM course WHERE course.cid=sc.cid) AS 课程名称, 
    sc.score AS 成绩 
FROM student
LEFT JOIN sc
ON student.sid = sc.sid

 因为 student 表 和 sc 表没有冗余字段，所以（1）和（2）两种查询方法结果一致。

2、查询都学过 2 号同学（sid=2）学习过的课程的同学的学号
SQL 语句：

SELECT sid 
FROM sc 
WHERE cid IN (SELECT cid FROM sc WHERE sid=2) 
GROUP BY sid 
HAVING COUNT(*) = (SELECT COUNT(*) FROM sc WHERE sid=2)

3、查询 “语文（cid=1）” 课程比 “数学（cid=2）” 课程成绩高的所有学生的学号；
SQL 语句：

SELECT a.sid 
FROM 
(SELECT sid, score FROM sc WHERE cid=1) a, 
(SELECT sid, score FROM sc WHERE cid=2) b 
WHERE a.sid=b.sid AND a.score > b.score;

4、查询平均成绩大于 60 分的同学的学号和平均成绩；
SQL 语句：

SELECT sid, AVG(score) 
FROM `sc` 
GROUP BY sid 
HAVING AVG(score) > 60;

5、查询所有同学的学号、姓名、选课数、总成绩；
SQL：

SELECT a.sid, a.sname, COUNT(a.cid), SUM(a.score) 
FROM
(SELECT student.sid, student.sname, sc.cid, sc.score FROM student JOIN sc WHERE student.sid=sc.sid) a
GROUP BY sid;

6、查询姓 “周” 的老师的个数；
SQL：

SELECT COUNT(*) FROM `teacher` WHERE tname LIKE '周%';

7、查询没学过 “叶平” 老师课的同学的学号、姓名；
SQL：

# 参考答案

SELECT student.sid,student.sname
FROM student  
WHERE sid NOT IN 
 (
     SELECT DISTINCT(sc.sid) 
     FROM sc, course, teacher 
     WHERE  sc.cid=course.cid AND teacher.tid=course.tid AND teacher.tname='叶平'
    ); 

8、查询学过 “语文（cid=1）” 并且也学过 “数学（cid=2）” 课程的同学的学号、姓名；
SQL：

SELECT student.sid,student.sname 
FROM student, sc 
WHERE student.sid=sc.sid AND sc.cid=1 
AND 
EXISTS
 ( SELECT * FROM sc AS sc_2 WHERE sc_2.sid=sc.sid AND sc_2.cid=2);

9、查询学过 “叶平” 老师所教的所有课的同学的学号、姓名；
SQL：

SELECT student.sid,student.sname 
FROM student 
WHERE sid IN 
  (
   SELECT sid 
   FROM sc ,course ,teacher 
   WHERE sc.cid=course.cid AND teacher.tid=course.tid AND teacher.tname='叶平' 
   GROUP BY sid
   HAVING COUNT(sc.cid)=
      (
          SELECT COUNT(cid) 
          FROM course,teacher
          WHERE teacher.tid=course.tid AND teacher.tname='叶平'
      )
  );  

10、查询课程编号 “数学（cid=2）” 的成绩比课程编号 “语文（cid=1）” 课程低的所有同学的学号、姓名；
SQL：

SELECT sid,sname 
FROM student
WHERE sid=
(
 SELECT a.sid 
 FROM 
  (SELECT sid, score FROM sc WHERE cid=1) a, 
  (SELECT sid, score FROM sc WHERE cid=2) b 
 WHERE a.sid=b.sid AND a.score > b.score
)

「11」、查询所有课程成绩小于 60 分的同学的学号、姓名；
SQL：

SELECT sid,sname 
FROM student 
WHERE sid NOT IN 
(
    SELECT student.sid FROM student AS s,sc WHERE s.sid=sc.sid AND score>60
); 

12、查询没有学全所有课的同学的学号、姓名；
SQL：

SELECT student.sid, student.sname
FROM student,sc
WHERE student.sid=sc.sid
GROUP BY sid 
HAVING COUNT(cid) < (SELECT COUNT(cid) FROM course)

13、按平均成绩从高到低显示所有学生的 “语文 “、“数学”、“英语” 三门的课程成绩，按如下形式显示： 学生 ID, 语文，数学，英语，有效课程数，有效平均分
SQL：

SELECT sid AS 学生ID,
(SELECT score FROM sc WHERE sc.sid=sc_2.sid AND cid=1) AS 语文,
(SELECT score FROM sc WHERE sc.sid=sc_2.sid AND cid=2) AS 数学,
(SELECT score FROM sc WHERE sc.sid=sc_2.sid AND cid=3) AS 英语,
COUNT(*) AS 有效课程数, 
AVG(score)
FROM sc AS sc_2
GROUP BY sid
ORDER BY AVG(sc_2.score)

14、查询各科成绩最高和最低的分：以如下形式显示：课程 ID，最高分，最低分
SQL：

SELECT cid AS 课程ID, MAX(score) AS 最高分, MIN(score) AS 最低分
FROM sc
GROUP BY cid

15、查询男生、女生人数 ：以如下形式显示：男生人数，女生人数
SQL：

SELECT 
(SELECT COUNT(ssex) FROM student GROUP BY ssex HAVING ssex='男') AS 男生人数, 
(SELECT COUNT(ssex) FROM student GROUP BY ssex HAVING ssex='女') AS 女生人数; 

16、查询课程名称为 “数学”，且分数低于 60 的学生姓名和分数
SQL：

SELECT student.sid, student.sname
FROM student,sc
WHERE student.sid=sc.sid AND cid=(SELECT cid FROM course WHERE cname='数学') AND score > 60

17、查询两门及两门以上不及格课程的同学的学号及其平均成绩
SQL：

SELECT sid, AVG(score) FROM sc WHERE sid IN (SELECT sid FROM sc WHERE score < 60 GROUP BY sid HAVING COUNT(*)>1) GROUP BY sid;

18、检索 “cid=4” 课程分数小于 60，按分数降序排列的同学学号
SQL：

SELECT sid FROM sc WHERE cid=4 AND score < 60 ORDER BY score DESC