上机实验题(一、二)

上机实验题1 相关知识 第6章 第8章 目的 通过本实验 熟悉SQL Server 2012系统 掌握使用SQL Server管理控制器以及 T SQL语句创建数据库和数据表的方法 实验内容 完成以下任务 1 创建一个数据库 Library 其数据库文件存放在 D DB 文件夹中 数据库的创建可以用以下 SQL 语句完成 CREATE DATABASE Library ON NAME Library FILENAME D DB Library MDF SIZE 10MB MAXSIZE 50MB FILEGROWTH 5MB LOG ON NAME Library FILENAME D DB Library Log LDF SIZE 10MB MAXSIZE 20MB FILEGROWTH 5MB 数据库中包含如下 4 个关系表 depart 班号 系名 student 学号 姓名 性别 出生日期 班号 book 图书编号 图书名 作者 定价 出版社 borrow 学号 图书编号 借书日期 要求 depart 表的 班号 列为主键 student 表的 学号 列为主键 book 表 图 书编号 列为主键 borrow 表的 学号 和 图书编号 列为主键 数据表的创建可以使用以下 SQL 语句完成 USE Library CREATE TABLE depart 班号 int PRIMARY KEY 系名 char 20 CREATE TABLE student 学号 int PRIMARY KEY 姓名 char 10 性别 char 2 出生日期 datetime 班号 int CREATE TABLE book 图书编号 char 10 PRIMARY KEY 图书名 char 40 作者 char 10 定价 float 出版社 char 30 CREATE TABLE borrow 学号 int 图书编号 char 10 借书日期 datetime PRIMARY KEY CLUSTERED 学号 图书编号 2 使用 SQL Server 管理控制器或 T SQL 语句输入以下数据 depart 表包含如下数据 班号 系名 0501 计算机系 0502 计算机系 0801 电子工程系 0802 电子工程系 student 表包含如下数据 学号 姓名 性别 出生日期 班号 1 张任 男 1995 01 02 0501 2 程华 男 1996 01 10 0501 3 张丽 女 1995 06 07 0502 4 王英 女 1994 12 10 0502 5 李静 男 1995 04 05 0502 10 许兵 男 1995 08 10 0801 11 张功 男 1995 06 02 0801 12 李华 男 1994 10 03 0801 13 马超 男 1996 02 03 0802 14 曾英 女 1994 03 06 0802 book 表包含如下数据 图书编号 图书名 作者 定价 出版社 10011 C 程序设计 李洪 24 清华大学出版社 10012 C 程序设计 李洪 24 清华大学出版社 10013 C 习题解答 李洪 12 清华大学出版社 10014 C 习题解答 李洪 12 清华大学出版社 10020 数据结构 徐华 29 人民邮电出版社 10021 数据结构 徐华 29 清华大学出版社 10023 高等数学 王涛 30 高等教育出版社 10034 软件工程 张明 34 机械工业出版社 20025 信息学 张港 35 清华大学出版社 20026 信息学 张港 35 清华大学出版社 20042 电工学 王民 30 人民邮电出版社 20056 操作系统 曾平 26 清华大学出版社 20057 操作系统 曾平 26 清华大学出版社 20058 操作系统 曾平 26 清华大学出版社 20067 数字电路 徐汉 32 高等教育出版社 20140 数据库原理 陈曼 32 高等教育出版社 20090 网络工程 黄军 38 高等教育出版社 borrow 表包含如下数据 学号 图书编号 借书日期 1 10020 2013 12 05 1 20025 2013 11 08 1 20059 2014 04 11 2 10011 2013 10 02 2 10013 2014 04 03 3 10034 2014 04 10 3 20058 2014 04 11 4 10012 2014 04 06 5 10023 2014 02 03 10 20056 2014 02 05 12 20067 2014 03 06 相关 T SQL 语句如下 USE Library INSERT INTO depart VALUES 0501 计算机系 INSERT INTO depart VALUES 0502 计算机系 INSERT INTO depart VALUES 0801 电子工程系 INSERT INTO depart VALUES 0802 电子工程系 USE Library INSERT INTO student VALUES 1 张任 男 1995 01 02 0501 INSERT INTO student VALUES 2 程华 男 1996 01 10 0501 INSERT INTO student VALUES 3 张丽 女 1995 06 07 0502 INSERT INTO student VALUES 4 王英 女 1994 12 10 0502 INSERT INTO student VALUES 5 李静 男 1995 04 05 0502 INSERT INTO student VALUES 10 许兵 男 1995 08 10 0801 INSERT INTO student VALUES 11 张功 男 1995 06 02 0801 INSERT INTO student VALUES 12 李华 男 1994 10 03 0801 INSERT INTO student VALUES 13 马超 男 1996 02 03 0802 INSERT INTO student VALUES 14 曾英 女 1994 03 06 0802 USE Library INSERT INTO book VALUES 10011 C 程序设计 李洪 24 清华大学出版社 INSERT INTO book VALUES 10012 C 程序设计 李洪 24 清华大学出版社 INSERT INTO book VALUES 10013 C 习题解答 李洪 12 清华大学出版社 INSERT INTO book VALUES 10014 C 习题解答 李洪 12 清华大学出版社 INSERT INTO book VALUES 10020 数据结构 徐华 29 人民邮电出版社 INSERT INTO book VALUES 10021 数据结构 徐华 29 清华大学出版社 INSERT INTO book VALUES 10023 高等数学 王涛 30 高等教育出版社 INSERT INTO book VALUES 10034 软件工程 张明 34 机械工业出版社 INSERT INTO book VALUES 20025 信息学 张港 35 清华大学出版社 INSERT INTO book VALUES 20026 信息学 张港 35 清华大学出版社 INSERT INTO book VALUES 20042 电工学 王民 30 人民邮电出版社 INSERT INTO book VALUES 20056 操作系统 曾平 26 清华大学出版社 INSERT INTO book VALUES 20057 操作系统 曾平 26 清华大学出版社 INSERT INTO book VALUES 20058 操作系统 曾平 26 清华大学出版社 INSERT INTO book VALUES 20067 数字电路 徐汉 32 高等教育出版社 INSERT INTO book VALUES 20080 数据库原理 陈曼 32 高等教育出版社 INSERT INTO book VALUES 20090 网络工程 黄军 38 高等教育出版社 USE Library INSERT INTO borrow VALUES 1 10020 2013 12 05 INSERT INTO borrow VALUES 1 20025 2013 11 08 INSERT INTO borrow VALUES 1 20059 2014 04 11 INSERT INTO borrow VALUES 2 10011 2013 10 02 INSERT INTO borrow VALUES 2 10013 2014 04 03 INSERT INTO borrow VALUES 3 10034 2014 04 10 INSERT INTO borrow VALUES 3 20058 2014 04 11 INSERT INTO borrow VALUES 4 10012 2014 04 06 INSERT INTO borrow VALUES 5 10023 2014 02 03 INSERT INTO borrow VALUES 10 20056 2014 02 05 INSERT INTO borrow VALUES 12 20067 2014 03 06 上机实验题2 相关知识 第9章 目的 通过本实验 掌握使用T SQL语言使用方法 实验内容 对于实验题1创建的Library数据库和表数据 编写程序实现如下功能并给 出执行结果 1 查询图书品种的总数目 2 查询每种图书品种的数目 3 查询各班的人数 4 查询各系的人数 5 查询借阅图书学生的学号 姓名 书名和借书日期 6 查询借有图书的学生学号和姓名 7 查询每个学生的借书数目 8 找出借书超过 2 本的学生学号 姓名和所借图书册数 9 查询借阅了 操作系统 一书的学生 输出学号 姓名及班号 10 查询每个班的借书总数 11 若图书编号以前 3 位数字进行分类 查询每类图书的平均价 12 查询平均价高于 30 的图书类别 13 查询图书类别的平均价最高价 14 假设借书期限为 45 天 查询过期未还图书编号 书名和借书人学号 姓名 15 查询书名包括 工程 关键词的图书 输出书号 书名 作者 16 查询现有图书中价格最高的图书 输出书名及作者 17 查询所有借阅 C 程序设计 一书的学生学号和姓名 再查询所有借了 C 程 序设计 但没有借 C 习题解答 的学生学号和姓名 18 查询所有没有借书的学生学号和姓名 19 查询每个系所借图书总数 20 查询各出版社的图书总数 21 查询各出版社的图书占图书总数的百分比 四舍五入到一位小数 22 查询各出版社的图书被借的数目 参考代码如下 1 对应的程序如下 USE Library SELECT COUNT DISTINCT 图书名 AS 图书品种数目 FROM book 2 对应的程序如下 USE Library SELECT 图书名 AS 书名 COUNT AS 数目 FROM book GROUP BY 图书名 执行结果如下 3 对应的程序如下 USE Library SELECT 班号 AS 班号 COUNT AS 人数 FROM student GROUP BY 班号 4 对应的程序如下 USE Library SELECT sc 系名 AS 系名 COUNT AS 人数 FROM student s depart sc WHERE s 班号 sc 班号 GROUP BY sc 系名 5 对应的程序如下 USE Library SELECT s 学号 s 姓名 b 图书名 bor 借书日期 FROM student s book b borrow bor WHERE s 学号 bor 学号 AND b 图书编号 bor 图书编号 ORDER BY s 学号 6 对应的程序如下 USE Library SELECT s 学号 s 姓名 FROM student s WHERE s 学号 IN SELECT DISTINCT 学号 FROM borrow 7 对应的程序如下 USE Library SELECT s 学号 s 姓名 COUNT bor 学号 As 数目 FROM student s borrow bor WHERE s 学号 bor 学号 GROUP BY s 学号 s 姓名 8 对应的程序如下 USE Library SELECT s 学号 学号 s 姓名 姓名 COUNT bor 学号 As 借图书册数 FROM student s borrow bor WHERE s 学号 bor 学号 GROUP BY s 学号 s 姓名 HAVING COUNT bor 学号 2 9 对应的程序如下 USE Library SELECT s 学号 s 姓名 s 班号 FROM student s book b borrow bor WHERE s 学号 bor 学号 AND b 图书编号 bor 图书编号 AND b 图书名 操作系统 ORDER BY s 班号 10 对应的程序如下 USE Library SELECT s 班号 COUNT bor 学号 AS 总数 FROM student s borrow bor WHERE s 学号 bor 学号 GROUP BY s 班号 11 对应的程序如下 USE Library SELECT SUBSTRING 图书编号 1 3 As 图书类别 CAST AVG 定价 AS decimal 4 1 AS 平均价 FROM book GROUP BY SUBSTRING 图书编号 1 3 12 对应的程序如下 USE Library SELECT SUBSTRING 图书编号 1 3 As 图书类别 CAST AVG 定价 AS decimal 4 1 AS 平均价 FROM book GROUP BY SUBSTRING 图书编号 1 3 HAVING AVG 定价 30 13 对应的程序如下 USE Library SELECT MAX 平均价 AS 最高平均价 FROM SELECT SUBSTRING 图书编号 1 3 As 图书类别 CAST AVG 定价 AS decimal 4 1 AS 平均价 FROM book GROUP BY SUBSTRING 图书编号 1 3 a 14 对应的程序如下 USE Library PRINT 当前日期 CONVERT char 10 GETDATE 102 SELECT s 学号 s 姓名 bor 图书编号 b 图书名 FROM student s book b borrow bor WHERE s 学号 bor 学号 AND b 图书编号 bor 图书编号 AND DATEDIFF day 借书日期 GETDATE 45 15 对应的程序如下 USE Library SELECT 图书编号 图书名 作者 FROM book WHERE 图书名 LIKE 工程 16 对应的程序如下 USE Library SELECT 图书编号 图书名 作者 FROM book WHERE 定价 SELECT MAX 定价 FROM book 17 对应的程序如下 USE Library GO PRINT 借的学生 SELECT s 学号 s 姓名 FROM student s book b borrow bor WHERE s 学号 bor 学号 AND b 图书编号 bor 图书编号 AND b 图书名 C 程序设计 GO PRINT 借的但没借的学生 SELECT s 学号 s 姓名 FROM student s borrow bor book b WHERE s 学号 bor 学号 A