数据查询(数据库实验2)

精选文档 供参考 数据库基础与实践实验报告 实验二 数据查询 班级 惠普测试 142 学号 1408090213 姓名 闫伟明 日期 2016 11 06 精选文档 供参考 1 实验目的 实验目的 1 掌握针对单张基本表的数据查询方法 2 掌握 SQL 常用库函数及统计汇总查询方法 3 掌握分组查询方法和结果排序的方法 4 掌握连接查询和嵌套查询的方法 2 实验平台 实验平台 操作系统 Windows xp 实验环境 SQL Server 2000 以上版本 3 实验内容与步骤实验内容与步骤 利用实验一创建的 sch id 数据库完成下列查询 并对查询语句的功能进行测试 1 查询学号 s5 的同学的姓名和所在系 将查询结果的列名显示为中文 代码 SELECT sn AS 姓名 dn AS 所在系 FROM S D WHERE S sno S5 AND S dno D dno 测试记录 S D 运行结果截图 精选文档 供参考 2 查询教过课程号为 C2 或 C5 的课程的教师的编号 写两个查询语句完成同样的功能 代码 SELECT tno FROM TC WHERE cno C2 OR cno C5 SELECT tno FROM TC WHERE cno IN C2 C5 测试记录 运行结果截图 3 查询学号为 S5 的同学所学课程的平均成绩 代码 SELECT AVG score AS 平均分 FROM SC WHERE sno S5 测试记录 精选文档 供参考 运行结果截图 4 查询选过课程号为 C1 的课程的人数 代码 SELECT COUNT FROM SC WHERE cno C1 测试记录 精选文档 供参考 运行结果截图 5 查询计算机系学生的最大年龄 显示系名和最大年龄 查询结果的每列都要有列名且为中文 代码 SELECT dn AS 系名 MAX age AS 最大年龄 FROM S D WHERE S dno D dno AND D dn 计算机 GROUP BY dn 测试记录 精选文档 供参考 运行结果截图 6 查询所有教过课的老师的人数 教过多门课的同一位老师只计数 1 次 代码 SELECT COUNT DISTINCT tno AS 所有教过课的老师的人数 FROM TC 测试记录 运行结果截图 7 查询信息系年龄在 30 到 39 岁之间 包括 30 和 39 的男老师人数 代码 SELECT COUNT AS 信息系年龄在到岁之间 包括和 的男老师人数 FROM T D WHERE T dno D dno AND age BETWEEN 30 AND 39 AND dn 信息 测试记录 精选文档 供参考 运行结果截图 8 查询选过课的各位学生的学号 选课门数和平均成绩 代码 SELECT sno AS 学号 COUNT AS 选课门数 AVG score AS 平均成绩 FROM SC GROUP BY sno 测试记录 运行结果截图 精选文档 供参考 9 查询各系的名称和所属教师人数 查询结果的每列都要有列名且为中文 代码 SELECT dn AS 系名 COUNT AS 教师人数 FROM T D WHERE T dno D dno GROUP BY dn 测试记录 运行结果截图 10 查询选课表中各门课的课程号 最高分 最低分和平均分 查询结果的每列都要有列名且为中文 代码 SELECT cno AS 课程号 MAX score AS 最高分 MIN score AS 最低分 AVG score AS 平均分 FROM SC 精选文档 供参考 GROUP BY cno 测试记录 运行结果截图 11 查询计算机系女生的学生信息 按年龄的降序 学号的升序显示学生学号 姓名 年龄 代码 SELECT FROM S WHERE sex 女 AND dno SELECT dno FROM D WHERE dn 计算机 ORDER BY age DESC sno ASC 测试记录 精选文档 供参考 运行结果截图 12 查询各职称的老师的职称 最高工资 最低工资 最高和最低工资间的差额 平均工资 按平均 工资的降序显示上述信息 查询结果的每列都要有列名且为中文 代码 SELECT prof AS 职称 MAX sal AS 最高工资 MIN sal AS 最低工资 MAX sal MIN sal AS 最高和最低 工资间的差额 AVG sal AS 平均工资 FROM T GROUP BY prof ORDER BY prof DESC 测试记录 运行结果截图 13 查询选修了 C1 和 C2 两门课的学生的学号 使用连接和嵌套两种方式完成查询 代码 SELECT sno FROM SC WHERE sno IN SELECT sno FROM SC WHERE cno C2 AND cno C1 SELECT SC1 sno FROM SC SC1 SC SC2 WHERE SC1 sno SC2 sno AND SCo C2 AND SCo C1 精选文档 供参考 测试记录 运行结果截图 14 查询计算机系所有学生选修信息 显示学生学号 课程号和成绩 代码 SELECT sno cno score FROM SC WHERE sno IN SELECT sno FROM S WHERE dno SELECT dno FROM D WHERE dn 计算机 测试记录 精选文档 供参考 运行结果截图 15 查询所有教过课程号为 C1 的课程的老师姓名 职称 系别 代码 SELECT tn AS 姓名 prof AS 职称 dn AS 系别 FROM T TC D WHERE T tno TC tno AND cno C1 AND T dno D dno 精选文档 供参考 测试记录 运行结果截图 16 查询具有教授和副教授职称的老师所教课程的编号和名称 代码 SELECT C cno C cn FROM T TC C WHERE prof IN 教授 副教授 AND T tno TC tno AND TC cno C cno 测试记录 精选文档 供参考 运行结果截图 17 查询孙珊同学的成绩单 显示课程号 课程名和成绩 代码 SELECT sn AS 姓名 SC cno AS 课程号 cn AS 课程名 score AS 成绩 FROM SC C S WHERE SC cno C cno AND S sno SC sno AND S sn 孙珊 测试记录 精选文档 供参考 运行结果截图 18 查询选修了 C1 但没有选修 C2 课程的学生的学号 代码 SELECT sno FROM SC WHERE sno NOT IN SELECT sno FROM SC WHERE cno C2 AND cno C1 测试记录 精选文档 供参考 运行结果截图 19 查询计算机系学生选修过的课程编号和名称 代码 SELECT DISTINCT SC cno AS 课程编号 cn AS 名称 FROM C SC S WHERE C cno SC cno AND SC sno S sno AND S dno SELECT dno FROM D WHERE dn 计算机 测试记录 精选文档 供参考 运行结果截图 精选文档 供参考 20 查询所有没有选修过任何课程的学生的学号和姓名 代码 SELECT sno AS 学号 sn AS 姓名 FROM S WHERE sno NOT IN SELECT sno FROM SC 测试记录 精选文档 供参考 运行结果截图 21 查询教授课程门数超过 3 门的老师的编号和姓名 代码 SELECT tno 教师编号 tn 姓名 FROM T WHERE tno IN SELECT tno FROM TC GROUP BY tno HAVING COUNT 3 测试记录 运行结果截图 22 查询选课表中出现过的不及格情况的学生编号 学生姓名及其不及格门数 代码 版本 1 SELECT 学生编号 sn 姓名 不及格门数 FROM S SELECT sno 学生编号 COUNT sno 不及格门数 FROM 精选文档 供参考 SC WHERE score 60 GROUP BY sno A WHERE S sno A 学生编号 版本 2 SELECT SC sno 学生编号 sn 姓名 COUNT cno 不及格门数 FROM S SC WHERE S sno SC sno AND score 60 GROUP BY sn SC sno 测试记录 运行结果截图 精选文档 供参考 4 讨论讨论 1 请根据自己的理解解释 exists 谓词常用于进行相关子查询的原因 只要子查询有记录 exists 谓词就返回 true 而不用去管子查询中一共有多少条记录 因此 如果 外层的主查询记录较少 子查询中的表大时使用 exists 会提高性能和查询效率 2 请用除 exists 外的谓词构造相关子查询 先定义查询需求再给出查询语句 查询选课学生的学生信息 SELECT FROM S WHERE EXISTS SELECT SNO FROM SC WHERE S SNO SC SN