mysql-sql练习题和参考解

转自

https://www.cnblogs.com/deng-cc/p/6515166.html

查询每个班成绩排名前三的学生信息

select 
    * 
from t_student t
where 
    (select count(1)+1 from t_student where class_id=t.class_id and score>t.score)
    <= 3

 

Part 1 练习题和参考解

（1）查询“001”课程比“002”课程成绩低的所有学生的学号、001学科成绩、002学科成绩

SELECT
  s1.StudentNo,
  s1.score AS '001',
  s2.score AS '002'
FROM 
  score s1, 
  score s2
WHERE 
  s1.CourseNo = 001 
  AND 
  s2.CourseNo = 002 
  AND 
  s1.StudentNo = s2.StudentNo
  AND
  s1.score < s2.score
ORDER BY s1.StudentNo

（2）查询平均成绩大于60分的同学的学号和平均成绩

 SELECT
  s1.StudentNo,
  AVG(s1.score)
 FROM
  score s1
  GROUP BY s1.StudentNo
  HAVING AVG(s1.score)>60

（3）查询所有同学的学号、姓名、选课数、总成绩

SELECT
  s1.StudentNo,
  stu1.name,
  COUNT(*),
  SUM(s1.score)
FROM
  score s1,
  student stu1
WHERE
  s1.StudentNo = stu1.StudentNo
  GROUP BY s1.StudentNo

（4）查询姓“李”的老师的个数

SELECT
 COUNT(*)
FROM
 teacher t1
WHERE
 t1.name like '李%'

（5）查询没学过“叶平”老师课的同学的学号、姓名

SELECT
 stu1.StudentNo,
 stu1.name
 FROM
 student stu1
WHERE
 stu1.StudentNo NOT IN
 (
  SELECT DISTINCT
 s1.StudentNo
 FROM
  score s1,
  course c1,
  teacher t1
 WHERE
 s1.courseNo = c1.CourseNo
  AND
 c1.teacherNo = t1.teacherNo
 AND
 t1.name = '叶平'
 )

（6）查询学过“001”并且也学过编号“002”课程的同学的学号、姓名

SELECT
  s1.StudentNo,
  stu1.name
FROM
  score s1,
  student stu1
WHERE
  s1.StudentNo = stu1.StudentNo
  AND
  s1.CourseNo IN (1, 2)
GROUP BY s1.StudentNo
HAVING COUNT(*) = 2

SELECT
 s1.StudentNo,
 stu1.name
FROM
 score s1,
 student stu1
WHERE
 s1.StudentNo = stu1.StudentNo
 AND
 s1.CourseNo = 1
 AND
 s1.StudentNo IN
 (
 SELECT
  s2.StudentNo
   FROM
   score s2
   WHERE
   s2.CourseNo = 2
 )

（7）查询学过“叶平”老师所教的所有课的同学的学号、姓名

-- 如果学生学习叶平老师的课程数量，与叶平老师所教学课程的数量相同，那么说明该同学学了叶平老师的所有课程

SELECT
  stu1.StudentNo,
  stu1.name
FROM
  score s1,
  student stu1,
  course c1,
  teacher t1
WHERE
  s1.StudentNo = stu1.StudentNo
  AND
  s1.CourseNo = c1.CourseNo
  AND
  c1.teacherNo = t1.teacherNo
  AND
  t1.name = '叶平'
GROUP BY s1.StudentNo
HAVING COUNT(*) =
(
SELECT
  COUNT(*)
FROM
  course c2,
  teacher t2
WHERE
  c2.teacherNo = t2.teacherNo
  AND
  t2.name = '叶平' 
)

（8）查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名

SELECT
  stu1.studentNo,
  stu1.name
FROM
  score s1,
  (
  SELECT
    s2.StudentNo,
    s2.score
  FROM
    score s2
  WHERE
    s2.CourseNo = 1
  ) t2,
  student stu1
WHERE
  s1.CourseNo = 2
  AND 
  s1.StudentNo = t2.StudentNo
  AND 
  s1.score < t2.score
  AND 
  s1.StudentNo = stu1.studentNo

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

SELECT DISTINCT
  s1.StudentNo,
  stu1.name
FROM
  score s1,
  student stu1
WHERE
  s1.StudentNo = stu1.studentNo
  AND
  s1.score < 60
  group by  s1.StudentNo

（10）查询没有学全所有课的同学的学号、姓名

SELECT
  stu1.StudentNo,
  stu1.name
FROM
  score s1,
  student stu1
WHERE
  s1.StudentNo = stu1.StudentNo
GROUP BY s1.StudentNo
HAVING COUNT(*) < 
(

SELECT
  COUNT(*)
FROM
  course c1
)

（11）查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名

SELECT DISTINCT
  stu1.StudentNo,
  stu1.name
FROM
  score s1,
  student stu1
WHERE
  s1.StudentNo = stu1.StudentNo
  AND
  s1.CourseNo IN

  (

  SELECT
    s2.CourseNo
  FROM
    score s2
  WHERE
    s2.StudentNo = 1
  )

（12）查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名（和11题撞脸，排除1号同学就可以了）

SELECT DISTINCT
  stu1.StudentNo,
  stu1.name
FROM
  score s1,
  student stu1
WHERE
  s1.StudentNo = stu1.StudentNo

  AND
  s1.StudentNo != 1
    AND
  s1.CourseNo IN

  (

  SELECT
    s2.CourseNo
  FROM
    score s2
  WHERE
    s2.StudentNo = 1
  )

（13）把“score”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩

-- 涉及将两表联合，将本表某字段的值按条件设置为另个表的某个字段的值 （参考链接：MySQL:把一个表中的数据按键值更新(update)到另一个表）

UPDATE
  score s,
  (
  SELECT
    s1.CourseNo as courseNo,
    AVG(s1.score) as avgScore 
  FROM
    score s1,
    course c1,
    teacher t1
  WHERE
    s1.CourseNo = c1.CourseNo
    AND
    c1.teacherNo = t1.teacherNo
    AND
    t1.name = '叶平'
  GROUP BY s1.CourseNo
  ) as t
SET
  s.score = t.avgScore
WHERE
  s.CourseNo = t.courseNo

（14）查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名

SELECT
  stu.studentNo,
  stu.name
FROM
  score s,
  student stu
WHERE
  s.StudentNo != 2
  AND
  s.StudentNo = stu.studentNo
GROUP BY s.StudentNo
HAVING SUM(s.CourseNo)=
(
SELECT
  SUM(s1.CourseNo)
FROM
  score s1
WHERE
  s1.StudentNo = 2
)