Tenormis's Blog

Appearance

SQL Server 入门习题 ​

1. SQL 基础语法掌握 ​

SQL 语法参考学习链接

要求至少掌握：

  1. 基础语法：SELECT、WHERE、AND、OR、ORDER BY、INSERT、UPDATE、DELETE、TOP、LIKE、IN、BETWEEN、EXISTS、FULL JOIN、INNER JOIN、LEFT JOIN、RIGHT JOIN、UNION、UNION ALL、GROUP BY、CROSS APPLY、OUTER APPLY、IS NULL、IS NOT NULL
  2. 函数：AVG、COUNT、MAX、MIN、SUM、HAVING、LEN、ROUND、NOW、ROWNUMBER OVER、ISNULL
  3. 表结构：CREATE TABLE、DROP、ALTER

2. 创建表结构 & 数据初始化 ​

SQL
USE LearnDB
GO

-- 学生表
CREATE TABLE dbo.StudentTbl (
  id    BIGINT       IDENTITY(1, 1) NOT NULL
, name  NVARCHAR(50) NOT NULL
, birth DATE         NOT NULL
, sex   NVARCHAR(10) NOT NULL
, PRIMARY KEY CLUSTERED (Id ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

-- 教师表
CREATE TABLE dbo.TeacherTbl (
  id   BIGINT       IDENTITY(1, 1) NOT NULL
, name NVARCHAR(50) NOT NULL
, PRIMARY KEY CLUSTERED (Id ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

-- 课程表
CREATE TABLE dbo.CourseTbl (
  id        BIGINT       IDENTITY(1, 1) NOT NULL
, teacherId BIGINT       NOT NULL
, name      NVARCHAR(50) NOT NULL
, PRIMARY KEY CLUSTERED (Id ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

-- 成绩表
CREATE TABLE dbo.ScoreTbl (
  id        BIGINT IDENTITY(1, 1) NOT NULL
, studentId BIGINT NOT NULL
, courseId  BIGINT NOT NULL
, score     INT    NOT NULL
, PRIMARY KEY CLUSTERED (Id ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

--插入学生表测试数据
INSERT dbo.StudentTbl (name, birth, sex)
VALUES (N'赵雷', '1990-01-01', N'男')
     , (N'钱电', '1990-12-21', N'男')
     , (N'孙风', '1990-05-20', N'男')
     , (N'李云', '1990-08-06', N'男')
     , (N'周梅', '1991-12-01', N'女')
     , (N'吴兰', '1992-03-01', N'女')
     , (N'郑竹', '1989-07-01', N'女')
     , (N'王菊', '1990-01-20', N'女')

--教师表测试数据
INSERT dbo.TeacherTbl (name) VALUES (N'张三'), (N'李四'), (N'王五')

--课程表测试数据
INSERT dbo.CourseTbl (teacherId, name) VALUES (1, N'语文'), (2, N'数学'), (3, N'英语')

--成绩表测试数据
INSERT dbo.ScoreTbl (studentId, courseId, score)
VALUES (1, 1, 80)
     , (1, 2, 90)
     , (1, 3, 99)
     , (2, 1, 70)
     , (2, 2, 60)
     , (2, 3, 80)
     , (3, 1, 80)
     , (3, 2, 80)
     , (3, 3, 80)
     , (4, 1, 50)
     , (4, 2, 30)
     , (4, 3, 20)
     , (5, 1, 76)
     , (5, 2, 87)
     , (6, 1, 31)
     , (6, 3, 34)
     , (7, 2, 89)
     , (7, 3, 98)

3. 习题 ​

  1. 查询语文比数学成绩高的学生的信息及课程分数

    SQL
    SELECT     stu.*, zhcnScore.score zhcnScore, mathScore.score mathScore
FROM       dbo.CourseTbl zhch
INNER JOIN dbo.ScoreTbl zhcnScore ON zhcnScore.courseId = zhch.id
INNER JOIN dbo.ScoreTbl mathScore ON mathScore.studentId = zhcnScore.studentId AND mathScore.score < zhcnScore.score
INNER JOIN dbo.CourseTbl math ON math.id = mathScore.courseId
INNER JOIN dbo.StudentTbl stu ON stu.id = zhcnScore.studentId
WHERE      zhch.name = '语文' AND math.name = '数学'

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

    SQL
    SELECT     stu.id, stu.name, score.avgScore
FROM       (
  SELECT   score.studentId, AVG(score.score) avgScore
  FROM     dbo.ScoreTbl score
  GROUP BY score.studentId
  HAVING   AVG(score) > 60
) score
INNER JOIN dbo.StudentTbl stu ON stu.id = score.studentId

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

    SQL
    SELECT    stu.id, stu.name, courseInfo.courseNums, courseInfo.totalScore
FROM      dbo.StudentTbl stu
LEFT JOIN (
  SELECT   score.studentId, COUNT(score.courseId) courseNums, SUM(score) totalScore
  FROM     dbo.ScoreTbl score
  GROUP BY score.studentId
) courseInfo ON courseInfo.studentId = stu.id

  4. 查询 “李” 姓老师的数量

    SQL
    SELECT COUNT(*) nums FROM dbo.TeacherTbl teacher WHERE teacher.name LIKE '李%'

  5. 查询学过 “张三” 老师授课的同学的信息

    SQL
    SELECT stu.id, stu.name, stu.birth, stu.sex
FROM   dbo.StudentTbl stu
WHERE  stu.id IN (
         SELECT     DISTINCT
                    score.studentId
         FROM       dbo.TeacherTbl teacher
         INNER JOIN dbo.CourseTbl course ON course.teacherId = teacher.id
         INNER JOIN dbo.ScoreTbl score ON score.courseId = course.id
         WHERE      teacher.name = '张三'
       )

  6. 查询没学过 “张三” 老师授课的同学的信息

    SQL
    SELECT    stu.id, stu.name, stu.birth, stu.sex
FROM      dbo.StudentTbl stu
LEFT JOIN (
  SELECT     DISTINCT
             score.studentId
  FROM       dbo.TeacherTbl teacher
  INNER JOIN dbo.CourseTbl course ON course.teacherId = teacher.id
  INNER JOIN dbo.ScoreTbl score ON score.courseId = course.id
  WHERE      teacher.name = '张三'
) learned ON learned.studentId = stu.id
WHERE     learned.studentId IS NULL

  7. 查询学过数学并且也学过语文的课程的同学的信息

    SQL
    SELECT     stu.*
FROM       (
  SELECT zhcnScore.studentId
  FROM   dbo.ScoreTbl zhcnScore
  WHERE  zhcnScore.courseId IN (SELECT zhcn.id FROM dbo.CourseTbl zhcn WHERE zhcn.name = '语文')
) zhcnStu
INNER JOIN (
  SELECT mathScore.studentId
  FROM   dbo.ScoreTbl mathScore
  WHERE  mathScore.courseId IN (SELECT math.id FROM dbo.CourseTbl math WHERE math.name = '数学')
) mathStu ON zhcnStu.studentId = mathStu.studentId
INNER JOIN dbo.StudentTbl stu ON stu.id = zhcnStu.studentId

  8. 查询学过数学但是没有学过语文的课程的同学的信息

    SQL
    SELECT     stu.*
FROM       (
 SELECT mathScore.studentId
 FROM   dbo.ScoreTbl mathScore
 WHERE  mathScore.courseId IN (SELECT math.id FROM dbo.CourseTbl math WHERE math.name = '数学')
) mathStu
INNER JOIN dbo.StudentTbl stu ON stu.id = mathStu.studentId
LEFT JOIN  (
 SELECT zhcnScore.studentId
 FROM   dbo.ScoreTbl zhcnScore
 WHERE  zhcnScore.courseId IN (SELECT zhcn.id FROM dbo.CourseTbl zhcn WHERE zhcn.name = '语文')
) zhcnStu ON zhcnStu.studentId = mathStu.studentId
WHERE      zhcnStu.studentId IS NULL

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

    SQL
    SELECT     stu.*, notFullLearned.courses notFullLearnedCourses
FROM       (
 SELECT     stu.id stuId, STRING_AGG(course.name, ',') courses
 FROM       dbo.CourseTbl course
 CROSS JOIN dbo.StudentTbl stu
 LEFT JOIN  dbo.ScoreTbl score ON score.courseId = course.id AND score.studentId = stu.id
 WHERE      score.id IS NULL
 GROUP BY   stu.id
) notFullLearned
INNER JOIN dbo.StudentTbl stu ON stu.id = notFullLearned.stuId

  10. 查询至少有一门课与赵雷所学相同的同学的信息

    SQL
    SELECT     stu.*, theSameCourse.courseNames
FROM       (
  SELECT     otherStuScore.studentId, STRING_AGG(theSameCourse.name, ',') courseNames
  FROM       dbo.StudentTbl zhaoLei
  INNER JOIN dbo.ScoreTbl zhaoLeiScore ON zhaoLeiScore.studentId = zhaoLei.id
  INNER JOIN dbo.ScoreTbl otherStuScore ON otherStuScore.courseId = zhaoLeiScore.courseId AND otherStuScore.studentId <> zhaoLei.id
  INNER JOIN dbo.CourseTbl theSameCourse ON theSameCourse.id = otherStuScore.courseId
  WHERE      zhaoLei.name = '赵雷'
  GROUP BY   otherStuScore.studentId
) theSameCourse
INNER JOIN dbo.StudentTbl stu ON stu.id = theSameCourse.studentId

  11. 查询和赵雷学习的课程完全相同的其他同学的信息

    SQL
    DECLARE @zhaoLeiId INT = (SELECT zhaoLei.id FROM dbo.StudentTbl zhaoLei WHERE zhaoLei.name = '赵雷')

SELECT     stu.*
FROM       (
  SELECT     otherStuLearnInfo.stuId
  FROM       (
    SELECT     otherStu.id stuId, allCourse.id courseId, CASE WHEN otherStuScore.id IS NOT NULL THEN 1 ELSE 0 END learned
    FROM       (SELECT otherStu.id FROM dbo.StudentTbl otherStu WHERE otherStu.id <> @zhaoLeiId) otherStu
    CROSS JOIN dbo.CourseTbl allCourse
    LEFT JOIN  dbo.ScoreTbl otherStuScore ON otherStuScore.studentId = otherStu.id AND otherStuScore.courseId = allCourse.id
  ) otherStuLearnInfo
  INNER JOIN (
    SELECT    allCourse.id courseId, CASE WHEN zhaoLeiScore.id IS NOT NULL THEN 1 ELSE 0 END learned
    FROM      dbo.CourseTbl allCourse
    LEFT JOIN dbo.ScoreTbl zhaoLeiScore ON zhaoLeiScore.studentId = @zhaoLeiId AND zhaoLeiScore.courseId = allCourse.id
  ) zhaoLeiLearnInfo ON zhaoLeiLearnInfo.courseId = otherStuLearnInfo.courseId
  GROUP BY   otherStuLearnInfo.stuId
  HAVING     COUNT(CASE WHEN otherStuLearnInfo.learned <> zhaoLeiLearnInfo.learned THEN 1 END) = 0
) allTheSameStu
INNER JOIN dbo.StudentTbl stu ON stu.id = allTheSameStu.stuId

  12. 查询没学过 “张三” 老师讲授的任一门课程的学生姓名

    SQL
    SELECT    stu.*
FROM      dbo.StudentTbl stu
LEFT JOIN (
  SELECT     DISTINCT
             zhangSanCourseScore.studentId
  FROM       dbo.TeacherTbl zhangSan
  INNER JOIN dbo.CourseTbl zhangSanCourse ON zhangSanCourse.teacherId = zhangSan.id
  INNER JOIN dbo.ScoreTbl zhangSanCourseScore ON zhangSanCourseScore.courseId = zhangSanCourse.id
  WHERE      zhangSan.name = '张三'
) learnedStu ON learnedStu.studentId = stu.id
WHERE     learnedStu.studentId IS NULL

  13. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

    SQL
    SELECT     stu.id, stu.name, scoreStatistics.scoreAvg
FROM       (
  SELECT   score.studentId, COUNT(CASE WHEN score.score < 60 THEN 1 END) failCounts, AVG(score) scoreAvg
  FROM     dbo.ScoreTbl score
  GROUP BY score.studentId
) scoreStatistics
INNER JOIN dbo.StudentTbl stu ON stu.id = scoreStatistics.studentId
WHERE      scoreStatistics.failCounts >= 2

  14. 检索语文课程分数小于 60，按分数降序排列的学生信息

    SQL
    SELECT     stu.*, score.score
FROM       dbo.CourseTbl zhcn
INNER JOIN dbo.ScoreTbl score ON score.courseId = zhcn.id
INNER JOIN dbo.StudentTbl stu ON stu.id = score.studentId
WHERE      zhcn.name = '语文' AND score.score < 60
ORDER BY   score.score DESC

  15. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

    SQL
    SELECT     allStu.*, allCourse.*, score.score, scoreStatistics.scoreAvg
FROM       dbo.StudentTbl allStu
CROSS JOIN dbo.CourseTbl allCourse
LEFT JOIN  dbo.ScoreTbl score ON score.courseId = allCourse.id AND score.studentId = allStu.id
LEFT JOIN  (SELECT score.studentId, AVG(score.score) scoreAvg FROM dbo.ScoreTbl score GROUP BY score.studentId) scoreStatistics ON scoreStatistics.studentId = allStu.id
ORDER BY   scoreStatistics.scoreAvg DESC

  16. 查询各科成绩最高分、最低分和平均分：以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

    SQL
    SELECT     course.id courseId
         , course.name courseName
         , courseScoreStatistic.scoreMax
         , courseScoreStatistic.scoreMin
         , courseScoreStatistic.scoreAvg
         , FORMAT(courseScoreStatistic.passRate, '0.##') passRate
         , FORMAT(courseScoreStatistic.midRate, '0.##') midRate
         , FORMAT(courseScoreStatistic.fineRate, '0.##') fineRate
         , FORMAT(courseScoreStatistic.excellentRate, '0.##') excellentRate
FROM       (
  SELECT    course.id courseId
          , MAX(score.score) scoreMax
          , MIN(score.score) scoreMin
          , AVG(score.score) scoreAvg
          , CAST(COUNT(CASE WHEN score.score >= 60 THEN 1 END) AS FLOAT) / COUNT(*) passRate
          , CAST(COUNT(CASE WHEN score.score >= 70 THEN 1 END) AS FLOAT) / COUNT(*) midRate
          , CAST(COUNT(CASE WHEN score.score >= 80 THEN 1 END) AS FLOAT) / COUNT(*) fineRate
          , CAST(COUNT(CASE WHEN score.score >= 90 THEN 1 END) AS FLOAT) / COUNT(*) excellentRate
  FROM      dbo.CourseTbl course
  LEFT JOIN dbo.ScoreTbl score ON score.courseId = course.id
  GROUP BY  course.id
) courseScoreStatistic
INNER JOIN dbo.CourseTbl course ON course.id = courseScoreStatistic.courseId

  17. 按各科成绩进行排序，并显示排名

    SQL
    SELECT     stu.name stuName, course.name courseName, score.score, DENSE_RANK() OVER (PARTITION BY score.courseId ORDER BY score.score DESC) rankNum
FROM       dbo.ScoreTbl score
INNER JOIN dbo.StudentTbl stu ON stu.id = score.studentId
INNER JOIN dbo.CourseTbl course ON course.id = score.courseId
ORDER BY   course.id, score.score DESC

  18. 查询学生的总成绩并进行排名

    SQL
    SELECT    DENSE_RANK() OVER (ORDER BY scoreStatistic.scoreTotal DESC) rankNum, stu.name, scoreStatistic.scoreTotal
FROM      dbo.StudentTbl stu
LEFT JOIN (SELECT score.studentId, SUM(score.score) scoreTotal FROM dbo.ScoreTbl score GROUP BY score.studentId) scoreStatistic ON scoreStatistic.studentId = stu.id
ORDER BY  rankNum ASC

  19. 查询不同老师所教不同课程平均分从高到低显示

    SQL
    SELECT    teacher.name teacherName, course.name courseName, scoreStatistic.scoreAvg
FROM      dbo.TeacherTbl teacher
LEFT JOIN dbo.CourseTbl course ON course.teacherId = teacher.id
LEFT JOIN (SELECT score.courseId, AVG(score.score) scoreAvg FROM dbo.ScoreTbl score GROUP BY score.courseId) scoreStatistic ON scoreStatistic.courseId = course.id
ORDER BY  scoreStatistic.scoreAvg DESC

  20. 查询所有课程的成绩第 2 名到第 3 名的学生信息及该课程成绩

    SQL
    SELECT     stu.name stuName, course.name courseName, scoreStatistic.score, scoreStatistic.rankNum
FROM       (
  SELECT DENSE_RANK() OVER (PARTITION BY score.courseId ORDER BY score DESC) rankNum, score.courseId, score.studentId, score.score
  FROM   dbo.ScoreTbl score
) scoreStatistic
INNER JOIN dbo.StudentTbl stu ON stu.id = scoreStatistic.studentId
INNER JOIN dbo.CourseTbl course ON course.id = scoreStatistic.courseId
WHERE      scoreStatistic.rankNum BETWEEN 2 AND 3

  21. 统计各科成绩各分数段人数：课程编号、课程名称、100 ~ 85、85 ~ 70、70 ~ 60、60 ~ 0 及所占百分比

    SQL
    SELECT     course.name, scoreStatistic.[100~85], scoreStatistic.[85~70], scoreStatistic.[70~60], scoreStatistic.[60~0]
FROM       (
  SELECT   score.courseId
         , FORMAT(CAST(COUNT(CASE WHEN score.score >= 85 THEN 1 END) AS FLOAT) / COUNT(*), '0.##') [100~85]
         , FORMAT(CAST(COUNT(CASE WHEN score.score >= 70 AND score.score < 85 THEN 1 END) AS FLOAT) / COUNT(*), '0.##') [85~70]
         , FORMAT(CAST(COUNT(CASE WHEN score.score >= 60 AND score.score < 70 THEN 1 END) AS FLOAT) / COUNT(*), '0.##') [70~60]
         , FORMAT(CAST(COUNT(CASE WHEN score.score < 60 THEN 1 END) AS FLOAT) / COUNT(*), '0.##') [60~0]
  FROM     dbo.ScoreTbl score
  GROUP BY score.courseId
) scoreStatistic
INNER JOIN dbo.CourseTbl course ON course.id = scoreStatistic.courseId

  22. 查询学生平均成绩及其名次

    SQL
    SELECT    DENSE_RANK() OVER (ORDER BY scoreStatistic.scoreAvg DESC) rankNum, stu.name, scoreStatistic.scoreAvg
FROM      dbo.StudentTbl stu
LEFT JOIN (SELECT score.studentId, AVG(score.score) scoreAvg FROM dbo.ScoreTbl score GROUP BY score.studentId) scoreStatistic ON scoreStatistic.studentId = stu.id
ORDER BY  rankNum

  23. 查询各科成绩前三名的记录

    SQL
    SELECT     course.name courseName, stu.name stuName, score.score, score.rankNum
FROM       (
  SELECT RANK() OVER (PARTITION BY score.courseId ORDER BY score DESC) rankNum, score.studentId, score.courseId, score.score
  FROM   dbo.ScoreTbl score
) score
INNER JOIN dbo.StudentTbl stu ON stu.id = score.studentId
INNER JOIN dbo.CourseTbl course ON course.id = score.courseId
WHERE      score.rankNum <= 3
ORDER BY   course.id, score.rankNum ASC

  24. 查询每门课程被选修的学生数

    SQL
    SELECT    course.name, ISNULL(electiveCount.counts, 0) electiveCount
FROM      dbo.CourseTbl course
LEFT JOIN (SELECT score.courseId, COUNT(*) counts FROM dbo.ScoreTbl score GROUP BY score.courseId) electiveCount ON electiveCount.courseId = course.id

  25. 查询出只有两门课程的全部学生的学号和姓名

    SQL
    SELECT     stu.id stuId, stu.name stuName
FROM       (SELECT score.studentId FROM dbo.ScoreTbl score GROUP BY score.studentId HAVING COUNT(*) = 2) only2
INNER JOIN dbo.StudentTbl stu ON stu.id = only2.studentId

  26. 查询男生、女生人数

    SQL
    SELECT stu.sex, COUNT(*) counts FROM dbo.StudentTbl stu WHERE stu.sex IN (N'男', N'女') GROUP BY stu.sex

  27. 查询名字中含有 “风” 字的学生信息

    SQL
    SELECT stu.* FROM dbo.StudentTbl stu WHERE stu.name LIKE '%风%'

  28. 查询同名同性学生名单，并统计同名人数

    SQL
    SELECT   RIGHT(stu.name, LEN(stu.name) - 1) secName, stu.sex, COUNT(*) counts
FROM     dbo.StudentTbl stu
GROUP BY RIGHT(stu.name, LEN(stu.name) - 1), stu.sex
HAVING   COUNT(*) > 1

  29. 查询 1990 年出生的学生名单

    SQL
    SELECT stu.* FROM dbo.StudentTbl stu WHERE stu.birth >= '1990/1/1' AND stu.birth < '1991/1/1'

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

    SQL
    SELECT    course.id courseId, course.name courseName, scoreStatistic.scoreAvg
FROM      dbo.CourseTbl course
LEFT JOIN (SELECT score.courseId, AVG(score.score) scoreAvg FROM dbo.ScoreTbl score GROUP BY score.courseId) scoreStatistic ON scoreStatistic.courseId = course.id
ORDER BY  scoreStatistic.scoreAvg DESC, course.id ASC

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

    SQL
    SELECT     stu.id stuId, stu.name stuName, scoreStatistic.scoreAvg
FROM       (
  SELECT   score.studentId, AVG(score.score) scoreAvg
  FROM     dbo.ScoreTbl score
  GROUP BY score.studentId
  HAVING   AVG(score.score) >= 85
) scoreStatistic
INNER JOIN dbo.StudentTbl stu ON stu.id = scoreStatistic.studentId

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

    SQL
    SELECT     stu.name stuName, score.score
FROM       dbo.CourseTbl course
INNER JOIN dbo.ScoreTbl score ON score.courseId = course.id
INNER JOIN dbo.StudentTbl stu ON stu.id = score.studentId
WHERE      course.name = N'数学' AND score.score < 60

  33. 查询所有学生的课程及分数情况；

    SQL
    SELECT    stu.id stuId, stu.name stuName, course.name courseName, score.score
FROM      dbo.StudentTbl stu
LEFT JOIN dbo.ScoreTbl score ON score.studentId = stu.id
LEFT JOIN dbo.CourseTbl course ON course.id = score.courseId

  34. 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数

    SQL
    SELECT     stu.name stuName, course.name courseName, score.score
FROM       dbo.ScoreTbl score
INNER JOIN dbo.CourseTbl course ON course.id = score.courseId
INNER JOIN dbo.StudentTbl stu ON stu.id = score.studentId
WHERE      score.score > 70

  35. 查询不及格的课程

    SQL
    SELECT     course.name
FROM       (SELECT DISTINCT score.courseId FROM dbo.ScoreTbl score WHERE score.score < 60) notpassCourse
INNER JOIN dbo.CourseTbl course ON course.id = notpassCourse.courseId

  36. 查询课程编号为 1 且课程成绩在 80 分以上的学生的学号和姓名

    SQL
    SELECT     stu.id stuId, stu.name stuName
FROM       dbo.ScoreTbl score
INNER JOIN dbo.StudentTbl stu ON stu.id = score.studentId
WHERE      score.courseId = 1 AND score.score > 80

  37. 求每门课程的学生人数

    SQL
    SELECT    course.name courseName, scoreStatistic.stuCounts
FROM      dbo.CourseTbl course
LEFT JOIN (SELECT score.courseId, COUNT(1) stuCounts FROM dbo.ScoreTbl score GROUP BY score.courseId) scoreStatistic ON scoreStatistic.courseId = course.id

  38. 查询选修 “张三” 老师所授课程的学生中，成绩最高的学生信息及其成绩

    SQL
    SELECT     stu.id stu, stu.name stuName, maxScoreStu.score
FROM       (
  SELECT     RANK() OVER (ORDER BY score.score DESC) rankNum, score.score, score.studentId
  FROM       dbo.TeacherTbl teacher
  INNER JOIN dbo.CourseTbl course ON course.teacherId = teacher.id
  INNER JOIN dbo.ScoreTbl score ON score.courseId = course.id
  WHERE      teacher.name = '张三'
) maxScoreStu
INNER JOIN dbo.StudentTbl stu ON stu.id = maxScoreStu.studentId
WHERE      maxScoreStu.rankNum = 1

  39. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

    SQL
    SELECT     stu.id stuId, stu.name stuName, score1.courseId courseId1, score2.courseId courseId2, score1.score
FROM       dbo.ScoreTbl score1
INNER JOIN dbo.ScoreTbl score2 ON score2.studentId = score1.studentId AND score2.score = score1.score AND score2.courseId > score1.courseId
INNER JOIN dbo.StudentTbl stu ON stu.id = score1.studentId

  40. 查询每门课程中成绩最好的前两名

    SQL
    SELECT     course.name courseName, stu.name stuName, top2.score, top2.rankNum
FROM       (
  SELECT RANK() OVER (PARTITION BY score.courseId ORDER BY score.score DESC) rankNum, score.courseId, score.studentId, score.score
  FROM   dbo.ScoreTbl score
) top2
INNER JOIN dbo.CourseTbl course ON course.id = top2.courseId
INNER JOIN dbo.StudentTbl stu ON stu.id = top2.studentId
WHERE      top2.rankNum <= 2
ORDER BY   course.id, top2.rankNum

  41. 统计每门课程的学生选修人数（超过 5 人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，

    SQL
    SELECT     course.id courseId, course.name courseName, scoreStatistic.counts
FROM       (SELECT score.courseId, COUNT(*) counts FROM dbo.ScoreTbl score GROUP BY score.courseId HAVING COUNT(*) > 5) scoreStatistic
INNER JOIN dbo.CourseTbl course ON course.id = scoreStatistic.courseId
ORDER BY   scoreStatistic.counts DESC

  42. 检索至少选修两门课程的学生学号

    SQL
    SELECT score.studentId FROM dbo.ScoreTbl score GROUP BY score.studentId HAVING COUNT(*) >= 2

  43. 查询选修了全部课程的学生信息

    SQL
    SELECT     stu.*
FROM       (
  SELECT     stu.id stuId
  FROM       dbo.CourseTbl course
  CROSS JOIN dbo.StudentTbl stu
  LEFT JOIN  dbo.ScoreTbl score ON score.courseId = course.id AND score.studentId = stu.id
  GROUP BY   stu.id
  HAVING     COUNT(CASE WHEN score.id IS NULL THEN 1 END) = 0
) electiveAll
INNER JOIN dbo.StudentTbl stu ON stu.id = electiveAll.stuId

  44. 查询各学生的年龄

    SQL
    SELECT stu.name, DATEDIFF(YEAR, stu.birth, GETDATE()) + 1 year FROM dbo.StudentTbl stu

  45. 查询本周过生日的学生

    SQL
    DECLARE @TODAY DATE = CAST(GETDATE() AS DATE)
DECLARE @TODAY_WEEKDAY INT = DATEPART(WEEKDAY, DATEADD(DAY, @@DATEFIRST - 1, @TODAY))
DECLARE @THIS_YEAR_FIRST_DAY DATE = DATEADD(YEAR, DATEDIFF(YEAR, 0, @TODAY), 0)
      , @THIS_WEEK_FIRST_DAY DATE = DATEADD(DAY, 1 - @TODAY_WEEKDAY, @TODAY)
      , @THIS_WEEK_LAST_DAY  DATE = DATEADD(DAY, 7 - @TODAY_WEEKDAY, @TODAY)

SELECT stu.*
FROM   dbo.StudentTbl stu
WHERE  DATEADD(DAY, DATEPART(DAY, stu.birth) - 1, DATEADD(MONTH, DATEPART(MONTH, stu.birth) - 1, @THIS_YEAR_FIRST_DAY)) BETWEEN @THIS_WEEK_FIRST_DAY AND @THIS_WEEK_LAST_DAY

  46. 查询下月过生日的学生

    SQL
    SELECT * FROM dbo.StudentTbl stu WHERE MONTH(stu.birth) = DATEPART(MONTH, DATEADD(MONTH, 1, GETDATE()))

转载请注明作者和出处

Copyright © 2025-至今 Tenormis