1、上机实验题1相关知识：第6章第8章。目的：通过本实验，熟悉SQL Server 2012系统，掌握使用SQL Server管理控制器以及TSQL语句创建数据库和数据表的方法。实验内容：完成以下任务（1）创建一个数据库 Library，其数据库文件存放在 D:DB 文件夹中。数据库的创建可以用以下 SQL 语句完成：CREATE DATABASE LibraryON (NAME = Library, FILENAME = D:DBLibrary.MDF,SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 5MB)LOG ON (NAME = Library , F
2、ILENAME = D:DBLibrary_Log.LDF,SIZE = 10MB, MAXSIZE = 20MB, FILEGROWTH = 5MB)数据库中包含如下 4 个关系表：depart(班号，系名)student(学号，姓名，性别，出生日期，班号)book(图书编号，图书名，作者，定价，出版社)borrow(学号，图书编号，借书日期)要求：depart 表的“班号”列为主键，student 表的“学号 ”列为主键，book 表“图书编号”列为主键，borrow 表的 “学号”和“图书编号” 列为主键。数据表的创建可以使用以下 SQL 语句完成：USE LibraryCREATE T
3、ABLE 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(学号, 图书编
4、号)（2）使用 SQL Server 管理控制器 或 TSQL 语句输入以下数据。 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 050210 许兵 男 1995-08-10 0801 11 张功 男 1995-06-0
5、2 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 高等教育出版社
6、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-051 20
7、025 2013-11-081 20059 2014-04-112 10011 2013-10-022 10013 2014-04-033 10034 2014-04-103 20058 2014-04-114 10012 2014-04-065 10023 2014-02-0310 20056 2014-02-0512 20067 2014-03-06相关 TSQL 语句如下：USE LibraryINSERT INTO depart VALUES(0501,计算机系)INSERT INTO depart VALUES(0502,计算机系)INSERT INTO depart VALUES(
8、0801,电子工程系)INSERT INTO depart VALUES(0802,电子工程系)USE LibraryINSERT 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,李静,男,199
9、5-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 LibraryINSERT INTO book
10、 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
11、 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 IN
12、TO 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 LibraryINSERT INTO borrow VALUES(1,10020,2013-12-05)INSE
13、RT 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,
14、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）查询借阅图书学生的学号
15、、姓名、书名和借书日期。（6）查询借有图书的学生学号和姓名。（7）查询每个学生的借书数目。（8）找出借书超过 2 本的学生学号、姓名和所借图书册数。（9）查询借阅了“操作系统“一书的学生，输出学号、姓名及班号。（10）查询每个班的借书总数。（11）若图书编号以前 3 位数字进行分类，查询每类图书的平均价。（12）查询平均价高于 30 的图书类别。（13）查询图书类别的平均价最高价。（14）假设借书期限为 45 天，查询过期未还图书编号、书名和借书人学号、姓名。（15）查询书名包括“工程”关键词的图书，输出书号、书名、作者（16）查询现有图书中价格最高的图书，输出书名及作者（17）查询所有借阅“
16、C 程序设计 ”一书的学生学号和姓名，再查询所有借了“C 程序设计”但没有借“C 习题解答 ”的学生学号和姓名。（18）查询所有没有借书的学生学号和姓名。（19）查询每个系所借图书总数。（20）查询各出版社的图书总数。（21）查询各出版社的图书占图书总数的百分比（四舍五入到一位小数）。（22）查询各出版社的图书被借的数目。参考代码如下：（1）对应的程序如下：USE LibrarySELECT COUNT(DISTINCT(图书名) AS 图书品种数目 FROM book（2）对应的程序如下：USE LibrarySELECT 图书名 AS 书名,COUNT(*) AS 数目 FROM book
17、GROUP BY 图书名执行结果如下：（3）对应的程序如下：USE LibrarySELECT 班号 AS 班号,COUNT(*) AS 人数 FROM studentGROUP BY 班号（4）对应的程序如下：USE LibrarySELECT sc.系名 AS 系名,COUNT(*) AS 人数 FROM student s,depart scWHERE s.班号=sc. 班号GROUP BY sc.系名（5）对应的程序如下：USE LibrarySELECT s.学号,s. 姓名,b. 图书名,bor.借书日期FROM student s,book b,borrow borWHERE s
18、.学号=bor.学号 AND b.图书编号=bor.图书编号ORDER BY s.学号（6）对应的程序如下：USE LibrarySELECT s.学号,s. 姓名FROM student sWHERE s.学号 IN (SELECT DISTINCT 学号 FROM borrow)（7）对应的程序如下：USE LibrarySELECT s.学号,s. 姓名,COUNT(bor.学号) As 数目FROM student s,borrow borWHERE s.学号=bor.学号GROUP BY s.学号,s. 姓名（8）对应的程序如下：USE LibrarySELECT s.学号 学号,s
19、. 姓名 姓名,COUNT(bor. 学号) As 借图书册数FROM student s,borrow borWHERE s.学号=bor.学号GROUP BY s.学号,s. 姓名HAVING COUNT(bor.学号)=2（9）对应的程序如下：USE LibrarySELECT s.学号,s. 姓名,s. 班号 FROM student s,book b,borrow borWHERE s.学号=bor.学号 AND b.图书编号=bor.图书编号AND b.图书名=操作系统 ORDER BY s.班号（10）对应的程序如下：USE LibrarySELECT s.班号,COUNT(bo
20、r. 学号) AS 总数FROM student s,borrow borWHERE s.学号=bor.学号GROUP BY s.班号（11）对应的程序如下：USE LibrarySELECT SUBSTRING(图书编号,1,3) As 图书类别,CAST(AVG(定价) AS decimal(4,1) AS 平均价FROM bookGROUP BY SUBSTRING(图书编号,1,3)（12）对应的程序如下：USE LibrarySELECT SUBSTRING(图书编号,1,3) As 图书类别,CAST(AVG(定价) AS decimal(4,1) AS 平均价FROM bookG
21、ROUP BY SUBSTRING(图书编号,1,3)HAVING AVG(定价)30（13）对应的程序如下：USE LibrarySELECT MAX(平均价) AS 最高平均价 FROM (SELECT SUBSTRING(图书编号,1,3) As 图书类别,CAST(AVG(定价) AS decimal(4,1) AS 平均价FROM bookGROUP BY SUBSTRING(图书编号,1,3) a（14）对应的程序如下：USE LibraryPRINT 当前日期:+CONVERT(char(10),GETDATE(),102)SELECT s.学号,s. 姓名,bor. 图书编号,
22、b.图书名 FROM student s,book b,borrow borWHERE s.学号=bor.学号 AND b.图书编号=bor.图书编号 AND DATEDIFF(day,借书日期,GETDATE()45（15）对应的程序如下：USE LibrarySELECT 图书编号,图书名,作者FROM bookWHERE 图书名 LIKE %工程% （16）对应的程序如下：USE LibrarySELECT 图书编号,图书名,作者FROM bookWHERE 定价 =(SELECT MAX(定价) FROM book) （17）对应的程序如下：USE LibraryGOPRINT 借的学
23、生:SELECT s.学号,s. 姓名FROM student s,book b,borrow borWHERE s.学号=bor.学号 AND b.图书编号=bor.图书编号 AND b.图书名=C 程序设计GOPRINT 借的但没借的学生:SELECT s.学号,s. 姓名FROM student s,borrow bor,book bWHERE s.学号=bor.学号 AND bor.图书编号=b. 图书编号 AND b.图书名=C 程序设计AND NOT EXISTS(SELECT * FROM borrow bor1,book b1WHERE bor1.图书编号=b1.图书编号 AN
24、D b1.图书名=C 习题解答 AND bor1.学号=bor.学号)（18）对应的程序如下：USE LibrarySELECT 学号,姓名FROM studentWHERE 学号 NOT IN (SELECT DISTINCT 学号 FROM borrow)（19）对应的程序如下：USE LibrarySELECT sc.系名 AS 系名,COUNT(bor.学号) AS 借书总数 FROM student s,depart sc,borrow borWHERE s.学号=bor.学号 AND s.班号=sc. 班号GROUP BY sc.系名（20）对应的程序如下：USE LibraryS
25、ELECT 出版社 AS 出版社,COUNT(*) AS 图书总数FROM bookGROUP BY 出版社（21）对应的程序如下：USE LibraryDECLARE num intSELECT num=(SELECT COUNT(*) FROM book)SELECT 出版社 AS 出版社,CAST(ROUND(COUNT(*)*100.0/num,1) AS decimal(5,1) AS 图书比例(%)FROM bookGROUP BY 出版社（22）对应的程序如下：USE LibrarySELECT 出版社 AS 出版社,COUNT(*) AS 被借图书数目FROM book b,borrow borWHERE b.图书编号=bor.图书编号GROUP BY 出版社
