SQL查询实战：从基础到进阶

"这些练习题主要涉及SQL查询语言的基础运用，包括从多个表格（如student、class、score）中提取数据，以及对数据进行条件筛选、排序和聚合操作。" 在SQL查询语句的实践中，我们可以看到一系列针对特定场景的问题，这些问题涵盖了多种常见的SQL语法和概念： 1. 查询所有记录：`SELECT Sname, Ssex, Class FROM Student;` 2. 查询去重列：`SELECT DISTINCT Depart FROM Teacher;` 3. 查询全部记录：`SELECT * FROM Student;` 4. 条件查询：`SELECT * FROM Score WHERE Degree BETWEEN 60 AND 80;` 5. 多条件查询：`SELECT * FROM Score WHERE Degree IN (85, 86, 88);` 6. 逻辑运算符：`SELECT * FROM Student WHERE Class = '95031' OR Ssex = '女';` 7. 排序：`SELECT * FROM Student ORDER BY Class DESC;` 8. 复合排序：`SELECT * FROM Score ORDER BY Cno ASC, Degree DESC;` 9. 计数：`SELECT COUNT(*) FROM Student WHERE Class = '95031';` 10. 最值与分组：`SELECT Sno, Cno FROM Score GROUP BY Sno, Cno HAVING MAX(Degree) = Degree;` 11. 平均分计算：`SELECT AVG(Degree) FROM Score WHERE Cno = '3-105';` 12. 过滤与聚合：`SELECT AVG(Degree) FROM Score WHERE Cno LIKE '3%' GROUP BY Cno HAVING COUNT(*) >= 5;` 13. 子查询：`SELECT Sno FROM Score WHERE MIN(Degree) > 70 AND MAX(Degree) < 90;` 14. 基本选择：`SELECT Sname, Cno, Degree FROM Student;` 15. 不同列选择：`SELECT Sno, Cname, Degree FROM Student;` 16. 重复问题：与问题14相同 17. 班级平均分：`SELECT AVG(Degree) FROM Score WHERE Sno IN (SELECT Sno FROM Student WHERE Class = '95033');` 18. 新建表与查询：`SELECT Sno, Cno, rank FROM grade JOIN Score ON Score.Degree BETWEEN low AND upp;` 19. 相对比较：`SELECT * FROM Score WHERE Cno = '3-105' AND Degree > (SELECT Degree FROM Score WHERE Sno = '109');` 20. 非最高分记录：`SELECT * FROM Score AS s1 WHERE EXISTS (SELECT 1 FROM Score AS s2 WHERE s1.Sno = s2.Sno AND s1.Cno = s2.Cno AND s1.Degree < s2.Degree) GROUP BY Sno, Cno HAVING COUNT(*) > 1;` 21. 高于特定记录的分数：`SELECT * FROM Score WHERE Degree > (SELECT Degree FROM Score WHERE Sno = '109' AND Cno = '3-105');` 22. 同年出生的学生：`SELECT Sno, Sname, Sbirthday FROM Student WHERE YEAR(Sbirthday) = (SELECT YEAR(Sbirthday) FROM Student WHERE Sno = '108');` 23. 教师任课成绩：`SELECT * FROM Score JOIN class ON Score.Cno = class.Cno WHERE Tname = '张旭';` 24. 课程选修人数：`SELECT Tname FROM class GROUP BY Tname HAVING COUNT(DISTINCT Sno) > 5;` 25. 多班级学生记录：`SELECT * FROM Student WHERE Class IN ('95033', '95031');` 这些练习题覆盖了SQL查询的基本元素，包括SELECT语句、FROM子句、WHERE子句、ORDER BY子句、聚合函数（COUNT、AVG、MAX等）、JOIN操作、子查询以及逻辑运算符等。通过这些练习，可以提升对SQL语言的理解和应用能力。