实验10S基本查询(进阶篇 实验目的 1.熟悉 SELECT语句的语法结构; 2.熟练掌握组合使用 where、 group by、 having和 order by子句 3.熟悉聚合函数的使用 实验环境 硬件 每位学生配备计算机一台,满足相关软件的安装; 2.软件 Windows操作系统,安装 Oracle database11g企业版; 3.网络 需要局域网支持 4.工具 无 三、实验理论与预备知识 1. SELECT语句的语法结构; 2. where、 group by、 having和 order by子句 四、实验任务 1.SQL查询语句的使用 五、实验内容及步骤 1.SQL查询语句的使用 以数据库ORCL、表空间 stu oracle及数据表 student、 course、sc及表数据 为基础(数据表及相关表数据如附表所示),执行数据库査询,并写出详细SQL 语句 (1)查询院系不明的学生的信息 (2)查询软件学院姓“张”的学生的学号、姓名、出生日期; (3)查询所有学生的学号、姓名、入学年份、出生年份及入学年龄 (提示:抽取年份推荐使用函数 to number( to char( birth,wwy))
实验 10 SQL 基本查询(进阶篇) 一、实验目的 1. 熟悉 SELECT 语句的语法结构； 2. 熟练掌握组合使用 where、group by、having 和 order by 子句； 3. 熟悉聚合函数的使用。 二、实验环境 1. 硬件 每位学生配备计算机一台，满足相关软件的安装； 2. 软件 Windows 操作系统，安装 Oracle Database 11g 企业版； 3. 网络 需要局域网支持； 4. 工具 无 三、实验理论与预备知识 1. SELECT 语句的语法结构； 2. where、group by、having 和 order by 子句。 四、实验任务 1. SQL 查询语句的使用。 五、实验内容及步骤 1. SQL 查询语句的使用 以数据库 ORCL、表空间 stu_oracle 及数据表 student、course、sc 及表数据 为基础（数据表及相关表数据如附表所示），执行数据库查询，并写出详细 SQL 语句。 （1） 查询院系不明的学生的信息； （2） 查询软件学院姓“张”的学生的学号、姓名、出生日期； （3） 查询所有学生的学号、姓名、入学年份、出生年份及入学年龄； （提示：抽取年份推荐使用函数 to_number(to_char(sbirth,’yyyy’)) ）
(4)查询数据表中男生总人数和女生总人数 (5)查询数据表 course中,学分为3至6的课程的基本信息; (6)査询数据表 course中,信息学院的必修课和限选课的数量及平均学分; (7)查询数据表sc中,每名学生的选课数量及平均成绩,按照平均成绩降序 排列 (8)査询每门课程的选修学生数及平均成绩 (9)查询各门课程及其及格人数; (10)査询各门课程中学生的最高成绩、最低成绩、平均成绩: (11)查询数据表sc中,平均分在70分以下的学生的学号、选修课程号及成 绩 (12)查询选课人数前五名的课程; (13)查询选修课程数最多和最少的学生的学号及选课数; 六、实验分析 1.试总结SQL查询语句中关键词 Where和 Having的使用区别及联系; 2.试分析若数据表 student中有无存在数据不一致的情况,如学生学号代码和 归属院系是否一致(学号第5-6位是18则属于信息学院),如何判断? 七、课外自主实验 无 八、实验扩展资源 1.图书 (1)王彬周士贵 Oracle11g基础与提高M]电子工业出版社,2008 (2) Oracle database11g数据库管理艺术[M]人民邮电出版社2010 2.期刊论文 (1)蔡雷.数据库査询语言sQL的语法分析及实现[D].天津大学,2006 (2)李旻,陈和平.正则表达式在数据库查询中的应用U计算机工程与设 计2006(12) 3.在线学习平台 (1)http:/xg.hactcm.edu.cn 其他资源
（4） 查询数据表中男生总人数和女生总人数； （5） 查询数据表 course 中，学分为 3 至 6 的课程的基本信息； （6） 查询数据表 course 中，信息学院的必修课和限选课的数量及平均学分； （7） 查询数据表 sc 中，每名学生的选课数量及平均成绩，按照平均成绩降序 排列； （8） 查询每门课程的选修学生数及平均成绩； （9） 查询各门课程及其及格人数； （10） 查询各门课程中学生的最高成绩、最低成绩、平均成绩； （11） 查询数据表 sc 中，平均分在 70 分以下的学生的学号、选修课程号及成 绩； （12） 查询选课人数前五名的课程； （13） 查询选修课程数最多和最少的学生的学号及选课数； 六、实验分析 1. 试总结 SQL 查询语句中关键词 Where 和 Having 的使用区别及联系； 2. 试分析若数据表 student 中有无存在数据不一致的情况，如学生学号代码和 归属院系是否一致（学号第 5-6 位是 18 则属于信息学院），如何判断？ 七、课外自主实验 无 八、实验扩展资源 1. 图书 （1）王彬,周士贵.Oracle 11g 基础与提高[M].电子工业出版社,2008. （2）Oracle Database 11g 数据库管理艺术[M].人民邮电出版社,2010. 2. 期刊论文 （1）蔡雷. 数据库查询语言 SQL 的语法分析及实现[D]. 天津大学,2006. （2）李旻,陈和平.正则表达式在数据库查询中的应用[J].计算机工程与设 计.2006(12). 3. 在线学习平台 （1）http://xg.hactcm.edu.cn 4. 其他资源
(1)http://www.oracle.com/cn (2)https://academy.oracle.com 附表: 表1学生信息表( student) 段名称 数据类型 长度 说明 备注 字符类型 学生学号主关键字 字符类型 学生姓名 非空 字符类型 学生性别 可为空 birth 日期类型 学生出生日期可为空 Sdept 变长字符类型|30 学生所在院系可为空 表2课程信息表( course) 字段名称 数据类型 长度 说明 备注 字符类型 课程编号主关键字 字符类型 30 课程名称非空 字符类型 6 果程性质可为空 Cxam字符类型4 考核方式可为空 Ccredit 数值型 学分 [1-16],可为空 字符类型30 开课院系|可为空 表3选课信息表(Sc) 字段名称 数据类型 长度 说明 备注 字符类型 10 学生学号主关键字,外键 Cno 字符类型 11 课程编号主关键字,外键 数值型 成绩 整数,[0-100,可为空 表4学生信息表( student) Sno Ssex Birth 201018001张蕊 女 1987/3/6信息技术学院 2010180002李鹏 男 1989/5/3信息技术学院 2010180003贾艳芳女 1987/3/6信息技术学院 2011181001卫晓琼女 1985/2/5信息技术学院 2011181002孙慧琪男 1990/6/7信息技术学院 2011181003王凌霄男 1991/1/8信息技术学院 2011000凯男19069信息技术学院 2011181005付婷婷女 1992/5/10信息技术学院
（1）http://www.oracle.com/cn （2）https://academy.oracle.com 附表： 表 1 学生信息表（student） 字段名称 数据类型 长度 说明 备注 Sno 字符类型 10 学生学号 主关键字 Sname 字符类型 8 学生姓名 非空 Ssex 字符类型 2 学生性别 可为空 Sbirth 日期类型 学生出生日期 可为空 Sdept 变长字符类型 30 学生所在院系 可为空 表 2 课程信息表（course） 字段名称 数据类型 长度 说明 备注 Cno 字符类型 11 课程编号 主关键字 Cname 字符类型 30 课程名称 非空 Cclass 字符类型 6 课程性质 可为空 Cexam 字符类型 4 考核方式 可为空 Ccredit 数值型 学分 [1-16]，可为空 Cdept 字符类型 30 开课院系 可为空 表 3 选课信息表（sc） 字段名称 数据类型 长度 说明 备注 Sno 字符类型 10 学生学号 主关键字，外键 Cno 字符类型 11 课程编号 主关键字，外键 Grade 数值型 成绩 整数，[0-100]，可为空 表 4 学生信息表（student） Sno Sname Ssex Sbirth Sdept 2010180001 张蕊 女 1987/3/6 信息技术学院 2010180002 李鹏 男 1989/5/3 信息技术学院 2010180003 贾艳芳 女 1987/3/6 信息技术学院 2011181001 卫晓琼 女 1985/2/5 信息技术学院 2011181002 孙慧琪 男 1990/6/7 信息技术学院 2011181003 王凌霄 男 1991/1/8 信息技术学院 2011181004 丁正凯 男 1990/6/9 信息技术学院 2011181005 付婷婷 女 1992/5/10 信息技术学院
2012181001张刚华女 199212/11信息技术学院 2012181002崔晓 男男女 1992/12/12信息技术学院 2012181003李延 1990/6/13信息技术学院 2013181001高莉 1996/6/14信息技术学院 2013181002丁安安男 1995/15信息技术学院 2013181003于冠军男 19912/16信息技术学院 2011182001师玲玲女 1989/8/3软件技术学院 2011182002王乐 190103软件技术学院 2012183001张建锋男 1993/1/4软件技术学院 2012183002李斯 男 1992/10/5软件技术学院 2012184001张珊珊女 1991/10/3软件技术学院 2012184002张山 1993/6/7软件技术学院 201311000何明 19912/121第二临床医学院 2013110002李璐璐 199713第二临床医学院 20131110张晓 男男女女男 1995/10/12第一临床医学院 2013111002董明 1993/6/13第一临床医学院 2013111003王妍妍女 1990/9/14第一临床医学院 2013125001张珊珊女 1994/12/10药学院 2013125002张山 男 199/7/11药学院 20:13600王明男195108针推学院 2031360李娜女1919针推学院 2013150001刘洋 女 1996/1/10基础医学院 表5课程信息表( course) Cname class exam Ccredit Cdept 200010b6ice应用开发必修课考试4 信息技术学院 2009091006计算机网络 必修课考试4 信息技术学院 2009091010数据库原理 必修课考试4 信息技术学院 2009091048电子商务 限选课考查3 信息技术学院 2000医院信息系统限选课考查|3 信息技术学院 2009091095应用统计学 必修课考查3 信息技术学院 200909115平面设计基础 限选课考查3 信息技术学院 009091116数据库模型设计 限选课考查3 信息技术学院 20090 JAVA程序设计基础 限选课考查4 信息技术学院 13091 程序设计基础 必修课考试4 信息技术学院 130911400数据结构 必修课考试4 信息技术学院 13092230009平面设计基础 限选课考查3 信息技术学院
2012181001 张刚华 女 1992/12/11 信息技术学院 2012181002 崔晓 男 1992/12/12 信息技术学院 2012181003 李延 男 1990/6/13 信息技术学院 2013181001 高莉 女 1996/6/14 信息技术学院 2013181002 丁安安 男 1994/5/15 信息技术学院 2013181003 于冠军 男 1995/12/16 信息技术学院 2011182001 师玲玲 女 1989/8/3 软件技术学院 2011182002 王乐 男 1990/10/3 软件技术学院 2012183001 张建锋 男 1993/1/4 软件技术学院 2012183002 李斯 男 1992/10/5 软件技术学院 2012184001 张珊珊 女 1991/10/3 软件技术学院 2012184002 张山 男 1993/6/7 软件技术学院 2013110001 何明 男 1995/12/12 第二临床医学院 2013110002 李璐璐 女 1993/7/13 第二临床医学院 2013111001 张晓 女 1995/10/12 第一临床医学院 2013111002 董明 男 1993/6/13 第一临床医学院 2013111003 王妍妍 女 1990/9/14 第一临床医学院 2013125001 张珊珊 女 1994/12/10 药学院 2013125002 张山 男 1994/7/11 药学院 2013136001 王明 男 1995/10/8 针推学院 2013136002 李娜 女 1996/11/9 针推学院 2013150001 刘洋 女 1996/1/10 基础医学院 表 5 课程信息表（course） Cno Cname Cclass Cexam Ccredit Cdept 2009091002 Web Service 应用开发 必修课 考试 4 信息技术学院 2009091006 计算机网络 必修课 考试 4 信息技术学院 2009091010 数据库原理 必修课 考试 4 信息技术学院 2009091048 电子商务 限选课 考查 3 信息技术学院 2009091052 医院信息系统 限选课 考查 3 信息技术学院 2009091095 应用统计学 必修课 考查 3 信息技术学院 2009091115 平面设计基础 限选课 考查 3 信息技术学院 2009091116 数据库模型设计 限选课 考查 3 信息技术学院 2009092026 JAVA 程序设计基础 限选课 考查 4 信息技术学院 13091140046 程序设计基础 必修课 考试 4 信息技术学院 13091140009 数据结构 必修课 考试 4 信息技术学院 13092230009 平面设计基础 限选课 考查 3 信息技术学院
13091230119计算机文化基础 限选课考查3 信息技术学院 13091140006计算机网络 必修课考试4 信息技术学院 2009221005就业指导课 必修课考试1 人文学院 20091013号克思主义基本原理概 必修课考试3 思政教研部 论 13411200思想道德修养与法律基 必修课考试3 思政教研部 1341127中国近现代史纲要 必修课考试2 人文学院 20090811大学英语 必修课考试|16 外语学院 13052150084中医理论基础 必修课考试|4 基础医学院 2009052067中医养生与康复 任选课考查2 基础医学院 表6选课信息表(sc) sno cno 20101800012009091002 2010180002200909100287 2010180003200909100256 5一8G 2010180001200909105280 20101800022009091052 2010180003200909105268 2010180001200909203285 20101800220090920290 20111810012009092032 2011181002200909203285 2011181003200909203272 2011181003200909111680 2011810042009091167 20111810052009091116 2012181001200909101080 2012181002200909101075 2012181003200909101060 2012181001200909109576 2012181002200909109566 20121810032009091095 20121810032009092026 20131810011309114004667 20131810021309114004689
13091230119 计算机文化基础 限选课 考查 3 信息技术学院 13091140006 计算机网络 必修课 考试 4 信息技术学院 2009221005 就业指导课 必修课 考试 1 人文学院 2009411013 马克思主义基本原理概 论 必修课 考试 3 思政教研部 13411130003 思想道德修养与法律基 础 必修课 考试 3 思政教研部 13411120007 中国近现代史纲要 必修课 考试 2 人文学院 2009081121 大学英语 必修课 考试 16 外语学院 13052150084 中医理论基础 必修课 考试 4 基础医学院 2009052067 中医养生与康复 任选课 考查 2 基础医学院 表 6 选课信息表（sc） sno cno grade 2010180001 2009091002 90 2010180002 2009091002 87 2010180003 2009091002 56 2010180001 2009091052 80 2010180002 2009091052 76 2010180003 2009091052 68 2010180001 2009092032 85 2010180002 2009092032 90 2011181001 2009092032 53 2011181002 2009092032 85 2011181003 2009092032 72 2011181003 2009091116 80 2011181004 2009091116 78 2011181005 2009091116 89 2012181001 2009091010 80 2012181002 2009091010 75 2012181003 2009091010 60 2012181001 2009091095 76 2012181002 2009091095 66 2012181003 2009091095 50 2012181003 2009092026 80 2013181001 13091140046 67 2013181002 13091140046 89
20131810031309114004656 20131810011309223000978 2013181002130922300956 20131810021309114000980 20131810031309114000990 2011182001200909105280 2011820022009091052 2011182001200909101078 2011182002200909101065 2012183001200909101055 2012183002200909101092 2012184001200909202685 20121840022009092026 20131360011309123011990 2013136002 1309123011988 20131250011309123011978 2013125002130912301985 2013101313019456 20131100021309123011989 20131100031309123011980 2013111001 1309123011996 20131110021309123011956 20131110031309123011989
2013181003 13091140046 56 2013181001 13092230009 78 2013181002 13092230009 56 2013181002 13091140009 80 2013181003 13091140009 90 2011182001 2009091052 80 2011182002 2009091052 55 2011182001 2009091010 78 2011182002 2009091010 65 2012183001 2009091010 55 2012183002 2009091010 92 2012184001 2009092026 85 2012184002 2009092026 83 2013136001 13091230119 90 2013136002 13091230119 88 2013125001 13091230119 78 2013125002 13091230119 85 2013110001 13091230119 56 2013110002 13091230119 89 2013110003 13091230119 80 2013111001 13091230119 96 2013111002 13091230119 56 2013111003 13091230119 89