数据库原理及应用 数据库查询 实验二.docx

数据库原理及应用 数据库查询 实验二.docx

《数据库原理及应用 数据库查询 实验二.docx》由会员分享，可在线阅读，更多相关《数据库原理及应用 数据库查询 实验二.docx（20页珍藏版）》请在冰豆网上搜索。

数据库原理及应用数据库查询实验二

班别：

姓名：

学号：

年级：

指导老师：

西安财经学院信息学院

《数据库原理及应用》实验报告

实验名称数据库查询实验室502实验日期

一、实验目的及要求

Ø掌握SQL的单表查询操作

Ø掌握SQL的连接查询操作

Ø掌握SQL的嵌套查询操作

Ø掌握SQL的集合查询操作

二、实验环境

硬件平台：

PC；

软件平台：

Windows2000/MICROSOFTSQLSERVER；

三、实验内容

（1）简单查询实验

1）利用T-SQL语句在“学生选课”数据库中实现下列数据查询操作：

⏹求数学系学生的学号和姓名。

⏹求选修了课程的学生学号。

⏹求选修1102课程的学生学号和成绩，并要求对查询结果按成绩的降序排列，如果成绩相同则按学号的升序排列。

⏹求选修课程1102且成绩在80～90分之间的学生学号和成绩，并将成绩乘以系数0.8输出。

⏹求数学系或计算机系姓张的学生的信息。

⏹求缺少了成绩的学生的学号和课程号。

（2）连接查询实验

利用T-SQL语句在“学生选课”数据库中实现下列数据连接查询操作。

⏹查询每个学生的情况以及他（她）所选修的课程。

⏹求学生的学号、姓名、选修的课程名及成绩。

⏹求选修1105课程且成绩为90分以上的学生学号、姓名及成绩。

⏹查询每门课的选课人数。

⏹查询每个学生每一门课的选修情况。

（3）嵌套查询实验

利用T-SQL语句在“学生选课”数据库中实现下列数据嵌套查询操作。

⏹求选修了数学的学生学号和姓名。

⏹求1102课程的成绩低于张峰的学生学号和成绩。

⏹求其他系中比计算机系某一学生年龄小的学生（即求年龄小于计算机系年龄最大者的学生）。

⏹求其他系中比计算机系学生年龄都小的学生（即求年龄小于计算机系年龄最小者的学生）。

⏹求选修了英语课程的学生姓名。

⏹求没有选修1105课程的学生姓名。

⏹查询选修了1101和1102课程的学生的姓名。

⏹求选修了学号为“2”的学生所选修的课程的学生学号和姓名。

（4）组合查询与统计查询实验

在“图书借阅”数据库中实现下列查询操作：

Ø查询书名以‘经济’开头或者出版社号为清华，而且价格大于16的书的信息。

Ø按照类型的升序和价格的降序（在类型相同时）显示书的信息（书名、作者、出版社、类型、价格）

Ø查询借阅量大于1的书名及借阅数

Ø查询在2010-1-1到2011-12-31间，每本书的借阅总人数

Ø查询所有作者的所在单位，要求没有重复信息

Ø计算多少种书已被定价

Ø查询每本书的书名、作者及它的借阅总数

Ø计算所有书的平均价格

Ø查询价格最高的书的书名、作者及价格

Ø查找这样的图书类别：

要求类别中最高的图书定价不低于全部按类别分组的图书平均定价的2倍。

Ø求新华出版社出版的各类图书的平均定价，用GROUPBY表示。

Ø列出经济类图书的书号、名称及价格，最后求出册数和总价格。

Ø列出经济类图书的书号、名称及价格，并求出各出版社这类书的总价格，最后求出全部册数和总价格。

Ø查询计算机类和经济类出版社出版的图书。

四、实验步骤及结果

（1）简单查询实验

1）利用T-SQL语句在“学生选课”数据库中实现下列数据查询操作：

⏹求数学系学生的学号和姓名。

⏹Use学生课程

⏹Go

⏹select学号,姓名from学生where所在系='数学'

⏹

⏹求选修了课程的学生学号。

⏹Use学生课程

⏹Go

⏹Select学号from学生where学号in（select学号from选课where课程号isnotnull）

⏹

⏹求选修1102课程的学生学号和成绩，并要求对查询结果按成绩的降序排列，如果成绩相同则按学号的升序排列。

⏹Use学生课程

⏹Go

⏹Select学号,成绩from课程,选课where课程.课程号=选课.课程号and课程号=1102orderby成绩desc,学号asc

⏹

⏹求选修课程1102且成绩在80～90分之间的学生学号和成绩，并将成绩乘以系数0.8输出。

⏹Use学生课程

⏹Go

⏹Select学号,成绩*0.8as成绩from选课where成绩between80and90and课程号=1102

⏹

⏹求数学系或计算机系姓张的学生的信息。

⏹Use学生课程

⏹Go

⏹Select*from学生,课程,选课where学生.学号=选课.学号and选课.课程号=课程.课程号and姓名like’张%’and（所在系=’数学’or所在系=’计算机’）

⏹

⏹求缺少了成绩的学生的学号和课程号。

⏹Use学生课程

⏹Go

⏹Select学号,课程号from选课where成绩isnull

⏹

（2）连接查询实验

利用T-SQL语句在“学生选课”数据库中实现下列数据连接查询操作。

⏹查询每个学生的情况以及他（她）所选修的课程。

⏹Use学生课程

⏹Go

⏹Select*from学生,选课,课程where学生.学号=选课.学号and选课.课程号=课程.课程号

⏹

⏹求学生的学号、姓名、选修的课程名及成绩。

⏹Use学生课程

⏹Go

⏹Select学号,姓名,课程名,成绩from学生,选课,课程where学生.学号=选课.学号and选课.课程号=课程.课程号

⏹

⏹求选修1105课程且成绩为90分以上的学生学号、姓名及成绩。

⏹Use学生课程

⏹Go

⏹Select学号,姓名,成绩from学生,选课where学生.学号=选课.学号and课程号=1105and成绩>90

⏹

⏹查询每门课的选课人数。

⏹Use学生课程

⏹Go

⏹Select课程号,count（学号）as选课人数from选课groupby课程号

⏹

⏹查询每个学生每一门课的选修情况。

⏹Use学生课程

⏹Go

⏹Select*from选课,课程where选课.课程号=课程.课程号

⏹

（3）嵌套查询实验

利用T-SQL语句在“学生选课”数据库中实现下列数据嵌套查询操作。

⏹求选修了数学的学生学号和姓名。

⏹Use学生课程

⏹Go

⏹Select学生.学号,姓名from学生,选课,课程where学生.学号=选课.学号and选课.课程号=课程.课程号and课程名=’数学’

⏹

⏹求1102课程的成绩低于张峰的学生学号和成绩。

⏹Use学生课程

⏹Go

⏹Select学生.学号,成绩from学生,选课,课程where学生.学号=选课.学号and选课.课程号=课程.课程号and课程号=1102and成绩<（select成绩from选课,学生where学生.学号=选课.学号and姓名=’张峰’）

⏹

⏹求其他系中比计算机系某一学生年龄小的学生（即求年龄小于计算机系年龄最大者的学生）。

⏹Use学生课程

⏹Go

⏹Select姓名from学生where年龄<（selectmax（年龄）as年龄from学生where所在系=’电子商务’）

⏹

⏹求其他系中比计算机系学生年龄都小的学生（即求年龄小于计算机系年龄最小者的学生）。

⏹Use学生课程

⏹Go

⏹Select姓名from学生where年龄<（selectmin（年龄）as年龄from学生where所在系=’电子商务’）

⏹

⏹求选修了英语课程的学生姓名。

⏹Use学生课程

⏹Go

⏹Select姓名from学生,课程,选课where学生.学号=选课.学号and选课.课程号=课程.课程号and课程名=’英语’

⏹

⏹求没有选修1105课程的学生姓名。

⏹Use学生课程

⏹Go

⏹Select姓名from学生,选课where学生.学号=选课.学号and课程号!

=1105

⏹

⏹查询选修了1101和1102课程的学生的姓名。

⏹Use学生课程

⏹Go

⏹Select姓名from学生,选课where学生.学号=选课.学号and课程号=1101and课程号=1102

⏹

⏹求选修了学号为“2”的学生所选修的课程的学生学号和姓名。

⏹Use学生课程

⏹Go

⏹Select学生.学号,姓名from学生,课程,选课where学生.学号=选课.学号and选课.课程号=课程.课程号and学生.学号='2’

⏹

（4）组合查询与统计查询实验

在“图书借阅”数据库中实现下列查询操作：

Ø查询书名以‘经济’开头或者出版社号为清华，而且价格大于16的书的信息。

Use图书读者

Go

ØSelect*from图书where书名like‘经济%’or（出版社=’清华’and定价>16）

Ø

Ø按照类型的升序和价格的降序（在类型相同时）显示书的信息（书名、作者、出版社、类型、价格）

Use图书读者

Go

ØSelect书名,作者,出版社,类型=类别,价格=定价from图书orderby类型asc,价格desc

Ø

Ø查询借阅量大于1的书名及借阅数

Use图书读者

Go

ØSelect书名,count（图书.书号）as借阅数from图书,借阅where图书.书号=借阅.书号groupby图书.书号,书名havingcount（图书.书号）>1

Ø

Ø查询在2010-1-1到2011-12-31间，每本书的借阅总人数

Use图书读者

Go

ØSelect书名,借阅人数=count（借阅.书号）from图书,借阅where借阅.书号=图书.书号and借阅日期between‘2010-1-1’and‘2011-12-31’groupby图书.书号,书名

Ø

Ø查询所有作者的所在单位，要求没有重复信息

Use图书读者

Go

ØSelectdistinct所在单位=出版社from图书

Ø

Ø计算多少种书已被定价

Use图书读者

Go

ØSelectcount（书名）as已被定价的书总数from图书where定价isnotnull

Ø

Ø查询每本书的书名、作者及它的借阅总数

Use图书读者

Go

ØSelect书名,作者,count（图书.书号）as借阅总数from图书,借阅where图书.书号=借阅.书号groupby图书.书号,书名,作者

Ø

Ø计算所有书的平均价格

Use图书读者

Go

ØSelectavg（定价）as平均价格from图书

Ø

Ø查询价格最高的书的书名、作者及价格

Use图书读者

Go

ØSelect书名,作者,价格=定价from图书where定价in（select定价=max（定价）from图书）

Ø

Ø查找这样的图书类别：

要求类别中最高的图书定价不低于全部按类别分组的图书平均定价的2倍。

Use图书读者

Go

ØSelect类别from图书groupby类别havingmax（定价）>=2*avg（定价）

Ø

Ø求新华出版社出版的各类图书的平均定价，用GROUPBY表示。

Use图书读者

Go

ØSelect平均定价=avg（定价）from图书where出版社=’新华’groupby书名

Ø

Ø列出经济类图书的书号、名称及价格，最后求出册数和总价格。

Use图书读者

Go

ØSelect书号,书名,定价from图书where类别=’经济’computecount（书号）,sum（定价）

Ø

Ø列出经济类图书的书号、名称及价格，并求出各出版社这类书的总价格，最后求出全部册数和总价格。

Use图书读者

Go

ØSelect书号,书名,定价,总价格=sum（定价）from图书where类别=’经济’groupby类别computecount（书号）,sum（定价）

Ø

Ø查询计算机类和经济类出版社出版的图书。

Use图书读者

Go

ØSelect书名from图书where类别=’计算机’or类别=’经济’

Ø

五、实验总结

本次作业以查询语句为主，考察了我们对数据库查询的熟悉程度，考核了简单查询，条件查询，嵌套查询，函数查询等，利用查询分析器查询相应的信息，对于不懂得都可以从书本上找到，所以必须要熟悉课本并灵活应用。