20道 sql server 嵌套查询题

好的，以下是20道SQL Server嵌套查询题：

1. 查询所有成绩大于平均成绩的学生信息。

SELECT *
FROM students
WHERE score > (SELECT AVG(score) FROM students)

2. 查询所有没有选修“数据库”课程的学生信息。

SELECT *
FROM students
WHERE id NOT IN (SELECT student_id FROM course_selection WHERE course_name = '数据库')

3. 查询选修了所有课程的学生信息。

SELECT *
FROM students
WHERE NOT EXISTS (
	SELECT *
	FROM courses
	WHERE NOT EXISTS (
		SELECT *
		FROM course_selection
		WHERE course_selection.course_name = courses.course_name
		AND course_selection.student_id = students.id
	)
)

4. 查询选修了“数据库”课程的学生信息。

SELECT *
FROM students
WHERE id IN (SELECT student_id FROM course_selection WHERE course_name = '数据库')

5. 查询选修了“数据库”课程且成绩大于等于80分的学生信息。

SELECT *
FROM students
WHERE id IN (
	SELECT student_id
	FROM course_selection
	WHERE course_name = '数据库'
	AND score >= 80
)

6. 查询选修了“数据库”课程的男生信息。

SELECT *
FROM students
WHERE id IN (
	SELECT student_id
	FROM course_selection
	WHERE course_name = '数据库'
)
AND gender = '男'

7. 查询选修了“数据库”课程且成绩不及格的学生信息。

SELECT *
FROM students
WHERE id IN (
	SELECT student_id
	FROM course_selection
	WHERE course_name = '数据库'
	AND score < 60
)

8. 查询选修了至少一门课程的学生信息。

SELECT *
FROM students
WHERE id IN (SELECT DISTINCT student_id FROM course_selection)

9. 查询选修了所有课程且成绩均大于等于80分的学生信息。

SELECT *
FROM students
WHERE NOT EXISTS (
	SELECT *
	FROM courses
	WHERE NOT EXISTS (
		SELECT *
		FROM course_selection
		WHERE course_selection.course_name = courses.course_name
		AND course_selection.student_id = students.id
		AND score >= 80
	)
)

10. 查询选修了“数据库”课程的学生数量。

SELECT COUNT(DISTINCT student_id)
FROM course_selection
WHERE course_name = '数据库'

11. 查询成绩排名在第3到第5名的学生信息。

SELECT *
FROM (
	SELECT *, ROW_NUMBER() OVER (ORDER BY score DESC) AS rank
	FROM students
) AS t
WHERE rank BETWEEN 3 AND 5

12. 查询选修了“数据库”课程且成绩排名在前10名的学生信息。

SELECT *
FROM (
	SELECT students.*, ROW_NUMBER() OVER (ORDER BY score DESC) AS rank
	FROM students
	INNER JOIN course_selection ON students.id = course_selection.student_id
	WHERE course_selection.course_name = '数据库'
) AS t
WHERE rank <= 10

13. 查询选修了“数据库”课程且成绩排名在前10%的学生信息。

SELECT *
FROM (
	SELECT students.*, PERCENT_RANK() OVER (ORDER BY score DESC) AS rank
	FROM students
	INNER JOIN course_selection ON students.id = course_selection.student_id
	WHERE course_selection.course_name = '数据库'
) AS t
WHERE rank <= 0.1

14. 查询每个课程的平均成绩。

SELECT course_name, AVG(score) AS average_score
FROM course_selection
GROUP BY course_name

15. 查询每个学生的总成绩和平均成绩。

SELECT student_id, SUM(score) AS total_score, AVG(score) AS average_score
FROM course_selection
GROUP BY student_id

16. 查询没有选修任何课程的学生信息。

SELECT *
FROM students
WHERE id NOT IN (SELECT DISTINCT student_id FROM course_selection)

17. 查询每个学生选修的课程数量。

SELECT student_id, COUNT(*) AS course_count
FROM course_selection
GROUP BY student_id

18. 查询每个学生选修的课程数量及其平均成绩。

SELECT course_selection.student_id, COUNT(*) AS course_count, AVG(score) AS average_score
FROM course_selection
INNER JOIN students ON course_selection.student_id = students.id
GROUP BY course_selection.student_id

19. 查询选修了“数据库”课程的学生数量及其平均成绩。

SELECT COUNT(DISTINCT student_id) AS student_count, AVG(score) AS average_score
FROM course_selection
WHERE course_name = '数据库'

20. 查询每个学生选修的课程中，成绩最高的课程名称和成绩。

SELECT course_selection.student_id, courses.course_name, MAX(course_selection.score) AS max_score
FROM course_selection
INNER JOIN courses ON course_selection.course_name = courses.course_name
GROUP BY course_selection.student_id, courses.course_name