SQL分类查询大全.doc

SQL分类查询大全SQL分类查询攻略建立数据库”成绩管理DBC”，完成以下数据表文件：学生DBF(学号(C/6)、姓名(C/8)、性别(C/2)、出生日期(D)、年龄(N/2)、党员否(L)、入学分数(N/3)、院系号(C/8)、籍贯(C/10)、备注(M)，以学号建立了主索引，以院系建立了普通索引；课程DBF(课程号(C/2)、课程名称(C/12)、先行课(C/2)、学分(N/1)、任课教师(C/6)，以课程号建立了主索引。成绩DBF(学号(C/6)、课程号(C/2)、成绩(N/2)，学号和课程号分别建立了普通索引；学生DBF学号姓名性别出生日期年龄党员否入学分数院系籍贯备注201001刘芳菲女1989-12-03T512计算机系武汉201002刘丹男1989-06-14F536计算机系黄石201003高菲女1991-11-07T528信息系武汉201004李丽女1990-01-23F496信息系随州201005欧阳志飞男1989-06-28T469数学系孝感201006胡艳女1988-02-11F531计算机系荆州201007张阳男1990-03-28F520数学系武汉201008刘俊男1989-07-08F509计算机系天门201009李向阳男1990-11-13F486数学系汉川课程DBF成绩DBF学号课程号成绩201001C185201001C296201001C348201002C184201002C295201002C475201003C193201003C5NULL201003C348201004C189201004C242201004C378201005C186201005C295201005C3NULL201006C187201006C477201007C294201008C283课程号课程名称先行课学分任课教师C1数据库C54晓寒C2数学2王江C3信息系统C14蒋发C4操作系统C63刘海军C5数据结构C74赵丽C6数据处理2苏小朋 一、条件查询1. 查询学生表中计算机系或信息系男生的姓名及性别。SELECT 姓名，性别 FROM 学生 WHERE (院系=”计算机系”OR 院系=”信息系”) AND 性别=”男”2. 查询”信息系”的学生来自哪些城区。SELECT 籍贯 FROM 学生 WHERE 院系=”信息系”3. 查询所有90年以前出生的女生姓名与实际年龄。SELECT 姓名，2011-YEAR(出生日期) AS 实际年龄 FROM 学生 WHERE 性别=”女” AND YEAR(出生日期)=907. 查询考试成绩有不及格的学生的学号。SELECT 学号 FROM 成绩 WHERE 成绩=20 AND 年龄=2112. 查询年龄不在20-21岁之间的学生姓名、院系和年龄。SELECT 姓名，院系，年龄 FROM 学生 WHERE 年龄 NOT BETWEEN 20 AND 21条件等价于：WHERE 年龄21 三、集合查询13. 查询信息系、计算机系、外语系学生的姓名和性别。SELECT 姓名，性别 FROM 学生 WHERE 院系 IN (”信息系”，”计算机系”，”外语系”)条件等价于：WHERE 院系=”信息系”or院系=”计算机系”or院系=”外语系”14. 查询即不是信息系、数学系，也不是外语系的学生姓名和性别。SELECT 姓名，性别 FROM 学生 WHERE 院系 NOT IN (”信息系”，”计算机系”，”外语系”)条件等价于：WHERE 院系!=”信息系”and院系!=”计算机系”and院系! =”外语系” 四、字符匹配查询15. 查询学号为200701的学生详细情况。SELECT * FROM 学生 WHERE 学号 =”200701”条件等价于：WHERE 学号 IN (”200701”) 或 WHERE 学号 LIKE “200701”16. 查询所有姓刘的学生的姓名、学号和性别。SELECT 姓名，学号，性别 FROM 学生 WHERE 姓名 LIKE “刘%”比较：SELECT 姓名，学号，性别 FROM 学生 WHERE 姓名 LIKE “刘_”17. 查询名字中第2个字为”阳”字的学生姓名和学号。SELECT 姓名，学号 FROM 学生 WHERE 姓名 LIKE “_阳%”比较：SELECT 姓名，学号 FROM 学生 WHERE 姓名 LIKE “%阳_”18. 查询名字中包含”阳”字的学生姓名和学号。SELECT 姓名，学号 FROM 学生 WHERE 姓名 LIKE “%阳%”19. 查询所有不姓刘的学生姓名。SELECT 姓名 FROM 学生 WHERE 姓名 NOT LIKE “刘%” 五、空值查询20. 查询缺少成绩的学生学号和相应课程号。SELECT 学号，课程号 FROM 成绩 WHERE 成绩 IS NULL21. 查询全部有成绩的学生学号和相应课程号。SELECT 学号，课程号 FROM 成绩 WHERE 成绩 IS NOT NULL22. 查询有成绩的学生学号及姓名。(嵌套或连接)嵌套：SELECT 学号，姓名 FROM 学生 WHERE 学号 IN (SELECT DISTINCT 学号 FROM 成绩WHERE 成绩 IS NOT NULL)连接：SELECT 学生学号，姓名 FROM 学生，成绩 WHERE (成绩 IS NOT NULL) AND (学生学号=成绩学号)23. 查询没有成绩的学生姓名。(不包括没有参加选修的学生)嵌套：SELECT 学号，姓名 FROM 学生 WHERE 学号 IN (SELECT DISTINCT 学号 FROM 成绩 WHERE 成绩 IS NULL)连接：SELECT 学生学号，姓名 FROM 学生，成绩 WHERE (成绩 IS NULL) AND (学生学号=成绩学号) 六、多条件查询24. 查询计算机系年龄在20岁以下的学生以及信息系的学生姓名。SELECT 姓名 FROM 学生 WHERE (院系=”计算机系” AND 年龄=9026. 查询选修C2课程且成绩在90分以上的所有学生的姓名。嵌套：SELECT 学号，姓名 FROM 学生 WHERE 学号 IN (SELECT 学号 FROM 成绩 WHERE 课程号=”C2” AND 成绩=90)连接：SELECT 学生学号，姓名 FROM 学生，成绩 WHERE 学生学号=成绩学号 AND 课程号=”C2” AND 成绩=90 七、排序查询27. 查询选修了C1课程的学生的学号及其成绩，查询结果按分数的降序排列。SELECT 学号，成绩 FROM 成绩 WHERE 课程号=”C1” ORDER BY 成绩 DE成绩28. 查询全体学生情况，查询结果按所在院系降序排列，同一系中的学生按学号升序排列。SELECT * FROM 学生 ORDER BY 院系 DESC，学号29. 查询选修了C1课程的学生的姓名及其成绩，查询结果按分数的降序排列。(只能使用联接查询)SELECT 姓名，成绩 FROM 学生，成绩 WHERE 课程号=”C1” AND 学生学号=成绩学号 ORDER BY 成绩 DESC 八、函数查询30. 查询参加了选修课程的学生人数。SELECT COUNT(DIST 学号) FROM 成绩31. 查询成绩表中选修” 数学”课程的学生人数。嵌套：SELECT COUNT(学号) FROM 成绩 WHERE 课程号=(SELECT 课程号 FROM 课程 WHERE 课程名称=”数学”)连接：SELECT COUNT(学号) FROM 成绩，课程 WHERE 成绩课程号=课程课程号 AND 课程名称=”数学”)32. 查询选修课程号为”C4”的学生的平均年龄。(此题先求出年龄后再做)SELECT AVG(年龄) FROM 学生 WHERE 学号 IN (SELECT 学号 FROM 成绩 WHERE 课程号=”C4”)33. 查询选修课程名为”数据库”课程的学生的平均年龄。SELECT AVG(年龄) FROM 学生 WHERE 学号 IN(SELECT 学号 FROM 课程，成绩 WHERE 课程名=”数据库” AND 课程课程号=成绩课程号)34. 统计每门课程的平均成绩。SELECT 课程号，AVG(成绩) FROM 成绩 GROUP BY 课程号35. 查询学生表中男女生人数。SELECT 性别，COUNT(性别) FROM 学生 GROUP BY 性别36. 按课程号统计每门成绩的总分、平均分、最高分及最低分。SELECT 课程号，SUM(成绩)，AVG(成绩)，MAX(成绩)，MIN(成绩)FROM 成绩 GROUP BY 课程号37. 查找并显示低于总平均分的学生姓名和平均成绩。错误：SELECT 姓名，AVG(成绩) FROM 学生，成绩 WHERE 成绩(SELECT AVG(成绩) FROM 成绩) GROUP BY 学生.学号正确：CREATE VIEW 平均成绩 AS SELECT 学号，AVG(成绩) AS 平均成绩 FROM 成绩 GROUP BY 学号SELECT 姓名，平均成绩 FROM 学生，平均成绩 WHERE 学生学号=平均成绩学号 AND 平均成绩=242. 检索选修3门以上(含3门)课程学生的学号和姓名。SELECT 学号，姓名 FROM 学生 WHERE 学号 IN (SELECT 学号 FROM 成绩 GROUP BY 课程号 HAVING COUNT(课程号)=3) 联接与嵌套查询43. 查询每个学生及其选修课程的情况，包括学号、姓名、课程名称及成绩。联接：SELECT 学生学号，姓名，课程名称，成绩 FROM 学生，课程，成绩 WHERE 学生学号=成绩学号 AND 课程课程号=成绩课程号 44. 查询成绩不及格的学生姓名、课程名称及成绩。联接：SELECT 姓名，课程名称，成绩 FROM 学生，课程，成绩 WHERE 学生学号=成绩学号 AND 课程课程号=成绩课程号 AND 成绩6045. 查询没有参加选修课程的学生姓名。嵌套：SELECT 姓名 FROM 学生 WHERE 学号 NOT IN(SELECT DISTINCT 学号 FROM 成绩)46. 查询与”王芳”在同一个系学习的学生学号、姓名、院系。嵌套：SELECT 学号，姓名，院系 FROM 学生 WHERE 院系= (SELECT 院系 FROM 学生 WHERE 姓名=”王芳”)47. 查询”王芳”同学没有选修课程的课程号。嵌套：SELECT 课程号 FROM 课程 WHERE 课程号 NOT IN (SELECT 课程号 FROM 学生，成绩 WHERE 姓名=”王芳” AND 学生学号=成绩学号)48. 查询”王芳”同学没有选修课程的课程名称。嵌套：SELECT 课程名称 FROM 课程 WHERE 课程号 NOT IN (SELECT 课程号 FROM 学生，成绩 WHERE 姓名=”王芳” AND 学生学号=成绩学号)49. 查询刘海军老师所授课的学生的学号。联接：SELECT 学号 FROM 成绩，课程 WHERE 成绩课程号=课程课程号 AND 任课教师=”刘海军”)嵌套：SELECT 学号 FROM 成绩 WHERE 课程号 IN (SELECT 课程号 FROM 课程 WHERE 任课教师=”刘海军”)50. 查询选修”数学”课程的学生学号；嵌套1：Select 学号 From 成绩 Where 课程号 In (Select 课程号 From 课程 Where 课程名称=”数学”)嵌套2：Select 学号 From 成绩 Where 课程号 =(Select 课程号 From 课程 Where 课程名称=”数学”)联接：Select 学号 From 成绩，课程 Where 成绩课程号=课程课程号 And 课程名称=”数学”51. 查询选修”数学”课程的学生姓名；嵌套：Select 姓名 From 学生 Where 学号 In ； (Select 学号 From 成绩，课程 Where 成绩课程号=课程课程号 And 课程名称=”数学”)52. 查询选修C1课程的学生姓名、成绩，结果按成绩从高到低排序。嵌套：SELECT 姓名，成绩 FROM 学生，成绩 WHERE 课程号=”C1” AND 学生学号=成绩学号) ORDER 成绩 DESE53. 查询没有选修C1课程的学生姓名。嵌套：SELECT 姓名 FROM 学生 WHERE 学号 NOT IN (SELECT 学号 FROM 成绩 WHERE 课程号=”C1”)54. 按成绩降序输出”计算机系”学生选修了”数据库”课程的学生姓名和成绩。嵌套：SELECT 姓名，成绩 FROM 学生，成绩 WHERE (学生学号=成绩学号) AND (课程号 IN(SELECT 课程号 FROM 课程 WHERE 课程名=”数据库” AND 院系=”计算机”) ORDER BY 成绩 DESC联接：SELECT 姓名，成绩 FROM 学生，课程，成绩 WHERE 学生学号=成绩学号 AND 课程课程号=成绩课程号 AND 课程名=”数据库” AND 院系=”计算机” ORDER BY 成绩 DESC55. 查询女生选修课程的课程名称和任课教师。嵌套：SELECT 课程名称，任课教师 FROM 课程 WHERE 课程号 IN (SELECT 课程号 FROM 学生，成绩 WHERE 性别=”女” AND 学生学号=成绩学号)联接：SELECT 课程名称，任课教师 FROM 学生，成绩，课程 WHERE 性别=”女” AND 学生学号=成绩学号 AND 成绩课程号=课程课程号56. 查询先行课为”数据库”的课程名称；内嵌套：Select 课程名称 From 课程 Where 课程号=(Select 课程号 From 课程 Where 课程名称=”数据库”)；57. 查询”数据库”先行课的课程名称；内联接：Select S课程名称 From 课程 S，课程 T Where S课程号=T课程号 And T课程名称=”数据库”; 并操作查询58. 查询选修了C1课程或者选修C2课程的学生学号。并操作：(SELECT 学号 FROM 成绩 WHERE 课程号=”C1”) UNION (SELECT 学号 FROM 成绩 WHERE 课程号=”C2”)条件操作：SELECT 学号 FROM 成绩 WHERE 课程号=”C1” OR 课程号=”C2” 数据更新59. 计算学生表中每个学生的年龄UPDATE 学生 SET 年龄=2011-YEAR(出生日期)60. 将成绩表中所有55-59分的学生成绩增加5分。UPDATE 成绩 SET 成绩=成绩+5 WHERE 成绩 BETWEEN 55 AND 5961. 把女生成绩提高5。UPDATE 成绩 SET 成绩=成绩*1.05 WHERE 学号 IN (SELECT 学号 FROM 学生 WHERE 性别=”女”)62. 将成绩表中所有55-59分的女生成绩增加5分。UPDATE 成绩 SET 成绩=成绩+5 WHERE (成绩 BETWEEN 55 AND 59) AND 学号 IN (SELECT 学号 FROM 学生 WHERE 性别=”女”)63. 把低于总平均成绩的学生成绩提高5。UP