Mysql练习题答案

坚果测试笔记 2021-06-29

690

01、查询同时选修了体育课和生物课的学生id和姓名

SELECT s.sid,s.sname FROM student s
    JOIN score sr ON sr.student_id=s.sid
    JOIN course c ON c.cid=sr.course_id 
    WHERE c.cname in('体育','生物')
    GROUP BY sr.student_id
    HAVING count(sr.student_id)=2;

02、查询“2”课程分数小于60，按分数降序排列的同学学号

SELECT student_id from score 
where course_id = 2 and score < 60 
ORDER BY score DESC;

03、查询有两门及以上课程超过60分的学生id及其平均成绩

SELECT student_id,AVG(score) FROM score 
where score>60  
GROUP BY student_id 
HAVING COUNT(course_id)>=2;

04、查询没有带过任何班级的老师id和姓名

SELECT t.tid,t.tname FROM teacher t 
WHERE t.tid NOT in(SELECT tc.tid FROM teacher2cls tc );

05、查询没有学生选修的课程的课程号和课程名

SELECT cid,cname FROM course 
where cid not in(SELECT course_id FROM score )；


SELECT c.cid,c.cname FROM course c 
LEFT JOIN score s on c.cid=s.course_id 
WHERE s.student_id is null;

06、查询至少选修两门课程的学生学号

SELECT DISTINCT student_id FROM score 
GROUP BY student_id 
HAVING COUNT(student_id)>=2;

07、查询各个课程及相应的选修人数

SELECT c.cname,COUNT(s.course_id) FROM course c 
LEFT JOIN score s ON s.course_id=c.cid 
GROUP BY c.cid;

08、查询课程编号为“2”且课程成绩在80分以上的学生的学号和姓名

SELECT s.sid,s.sname FROM student s 
JOIN score sc on s.sid=sc.student_id 
where sc.course_id=2 AND sc.score>=80;

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

SELECT s.sname,sr.score FROM student s 
JOIN score sr ON s.sid=sr.student_id
JOIN course c ON c.cid=sr.course_id 
WHERE c.cname='生物' AND sr.score<60;

10、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列

SELECT c.cname,a.av 平均分 FROM course c JOIN 
(SELECT course_id,avg(score) av FROM score GROUP BY course_id) a
ON c.cid=a.course_id 
ORDER BY a.av asc,c.cid DESC;

11、查询同名同姓学生名单，并统计同名人数

SELECT sname,COUNT(sname) FROM student 
GROUP BY sname 
HAVING COUNT(sname)>1;

12、查询男生、女生的人数，按倒序排列；

SELECT gender,COUNT(sid) FROM student 
GROUP BY gender 
ORDER BY COUNT(sid) DESC;

13、查询选修了2门以上课程的全部学生的学号和姓名

方式1：
SELECT s.sid,s.sname FROM student s 
WHERE s.sid in(
  SELECT student_id FROM score 
  GROUP BY student_id 
  HAVING COUNT(*)>2);
方式2：
SELECT s.sid,s.sname FROM student s 
LEFT JOIN score sr ON s.sid=sr.student_id 
GROUP BY s.sid 
HAVING COUNT(sr.course_id)>2;

14、查询每门课程被选修的学生数

SELECT c.cname,COUNT(s.student_id) 学生数 FROM score s 
LEFT JOIN course c ON s.course_id=c.cid 
GROUP BY c.cid;

15、查询至少学过学号为“1”同学所选课程中任意一门课的其他同学学号和姓名

方式1：
SELECT s.sid,s.sname FROM student s  JOIN score sr ON s.sid=sr.student_id where sr.course_id in(
SELECT sr.course_id FROM score sr where sr.student_id=1) and s.sid !=1;
方式2：
SELECT DISTINCT s.sid,s.sname FROM student s  JOIN score sr ON s.sid=sr.student_id 
where sr.course_id in(
SELECT sr.course_id FROM student s1 JOIN score sr1 ON sr1.student_id=s1.sid where sr1.student_id=1) and s.sid !=1;

16、查询没有学全所有课的同学的学号、姓名

SELECT s.sid,s.sname FROM student s
WHERE s.sid in (
  SELECT sr.student_id FROM score sr
  GROUP BY sr.student_id
  HAVING COUNT(*)<(SELECT count(DISTINCT c.cid) FROM course c)
);

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

SELECT DISTINCT s.sid,s.sname FROM student s 
JOIN score sr ON s.sid=sr.student_id
WHERE sr.score<60 ORDER BY s.sid;

18、查询所带班级数最多的老师id和姓名

SELECT t.tid,t.tname FROM teacher t JOIN teacher2cls tc ON t.tid=tc.tid
GROUP BY tc.tid ORDER BY count(tc.tid) DESC LIMIT 1;

19、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名

SELECT s.sid,s.sname FROM student s WHERE s.sid in(
SELECT s1.s1_id FROM 
(SELECT student_id s1_id,score s1_score FROM score WHERE course_id=1) s1
JOIN (SELECT student_id s2_id,score s2_score FROM score WHERE course_id=2) s2 
ON s1.s1_id=s2.s2_id
WHERE s1.s1_score>s2.s2_score);

20、查询带过超过2个班级的老师的id和姓名

SELECT DISTINCT t.tid,t.tname FROM teacher t JOIN teacher2cls tc ON t.tid=tc.tid
GROUP BY tc.tid HAVING count(tc.tid)>2;

21、查询没有带过高年级的老师id和姓名

SELECT t.tid,t.tname FROM teacher t
  WHERE t.tid NOT in(
  SELECT DISTINCT tc.tid FROM teacher2cls tc JOIN class c ON tc.cid=c.cid WHERE c.grade_id in(5,6)
);

22、查询学过编号‘1’课程和编号‘2’课程的同学的学号、姓名

SELECT s.sid,s.sname FROM student s 
  JOIN score sr ON s.sid=sr.student_id
  WHERE sr.course_id in(1,2)
  GROUP BY s.sid 
  HAVING count(*)=2;

23、查询教授课程超过2门的老师的id和姓名

SELECT t.tid,t.tname FROM teacher t
  JOIN course c ON t.tid=c.teacher_id
  GROUP BY c.teacher_id
  HAVING count(*)>2;

24、查询学过‘张老师’老师2门课以上的

SELECT s.sid,s.sname FROM student s
  JOIN score sr ON s.sid=sr.student_id
  JOIN course c ON c.cid=sr.course_id
  JOIN teacher t ON t.tid=c.teacher_id
  WHERE t.tname='张老师'
  GROUP BY sr.student_id
  HAVING count(*)>2;

25、查询班级信息，包括班级id、班级名称、年级、年级级别（12为低年级，34为中年级，56位高年级）

SELECT cl.cid,cl.caption,gr.gname,t.`年级级别` FROM class_grade gr
  JOIN class cl ON cl.grade_id=gr.gid
  JOIN (SELECT DISTINCT grade_id,
    CASE grade_id
    WHEN 1 THEN '低'
    WHEN 2 THEN '低'
    WHEN 3 THEN '中'
    WHEN 4 THEN '中'
    WHEN 5 THEN '高'
    WHEN 6 THEN '高'
    END AS '年级级别'
FROM class ORDER BY grade_id) t ON t.grade_id=cl.grade_id;

26、查询班级数小于5的年级id和年级名

SELECT cg.gid,cg.gname FROM class_grade cg
  JOIN class c ON c.grade_id=cg.gid
  GROUP BY c.grade_id
  HAVING count(c.cid)<5;

27、查询每位学生的学号，姓名，选课数，平均成绩

SELECT s.sid,s.sname,count(sr.sid)'选课数',avg(sr.score) FROM student s
LEFT JOIN score sr ON s.sid=sr.student_id
GROUP BY s.sid;

28、查询每个年级的学生人数

SELECT cg.gid,cg.gname,count(s.sid) FROM class_grade cg
LEFT JOIN class c ON cg.gid=c.grade_id
LEFT JOIN student s ON c.cid=s.class_id
GROUP BY cg.gid;

29、查询每个年级的班级数，取出班级数最多的前三个年级

SELECT cg.gid,cg.gname,count(c.cid) AS '班级数' FROM class_grade cg
LEFT JOIN class c ON c.grade_id=cg.gid
GROUP BY cg.gid
ORDER BY count(c.cid) DESC
LIMIT 3;

30、查询“生物”课程和“体育”课程成绩都及格的学生id和姓名

SELECT s.sid,s.sname FROM student s
JOIN score sr ON sr.student_id=s.sid
JOIN course c ON c.cid=sr.course_id
WHERE c.cname in('生物','体育') AND sr.score >=60
GROUP BY sr.student_id
HAVING count(*)=2;

mysql

文章转载自坚果测试笔记，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Mysql练习题答案

评论