上機實驗題(一、二)

《上機實驗題(一、二)》由會員分享，可在線閱讀，更多相關《上機實驗題(一、二)（9頁珍藏版）》請在裝配圖網(wǎng)上搜索。

上機實驗題1 相關知識：第6章～第8章。 目的：通過本實驗，熟悉SQL Server 2012系統(tǒng)，掌握使用SQL Server管理控制器以及T－SQL語句創(chuàng)建數(shù)據(jù)庫和數(shù)據(jù)表的方法。 實驗內(nèi)容：完成以下任務 （1）創(chuàng)建一個數(shù)據(jù)庫Library，其數(shù)據(jù)庫文件存放在D:\DB文件夾中。 數(shù)據(jù)庫的創(chuàng)建可以用以下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 ) 數(shù)據(jù)庫中包含如下4個關系表： depart(班號，系名) student(學號，姓名，性別，出生日期，班號) book(圖書編號，圖書名，作者，定價，出版社) borrow(學號，圖書編號，借書日期) 要求：depart表的“班號”列為主鍵，student表的“學號”列為主鍵，book表“圖書編號”列為主鍵，borrow表的“學號”和“圖書編號”列為主鍵。 數(shù)據(jù)表的創(chuàng)建可以使用以下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語句輸入以下數(shù)據(jù)。 ① depart表包含如下數(shù)據(jù)： 班號 系名 ------ ---------- 0501 計算機系 0502 計算機系 0801 電子工程系 0802 電子工程系 ② student表包含如下數(shù)據(jù)： 學號 姓名 性別 出生日期 班號 ----- ---------- ---- ------------ ------ 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表包含如下數(shù)據(jù)： 圖書編號 圖書名 作者 定價 出版社 --------- ------------ ---------- ----------- -------------- 10011 C程序設計 李洪 24 清華大學出版社 10012 C程序設計 李洪 24 清華大學出版社 10013 C習題解答 李洪 12 清華大學出版社 10014 C習題解答 李洪 12 清華大學出版社 10020 數(shù)據(jù)結構 徐華 29 人民郵電出版社 10021 數(shù)據(jù)結構 徐華 29 清華大學出版社 10023 高等數(shù)學 王濤 30 高等教育出版社 10034 軟件工程 張明 34 機械工業(yè)出版社 20025 信息學 張港 35 清華大學出版社 20026 信息學 張港 35 清華大學出版社 20042 電工學 王民 30 人民郵電出版社 20056 操作系統(tǒng) 曾平 26 清華大學出版社 20057 操作系統(tǒng) 曾平 26 清華大學出版社 20058 操作系統(tǒng) 曾平 26 清華大學出版社 20067 數(shù)字電路 徐漢 32 高等教育出版社 20140 數(shù)據(jù)庫原理 陳曼 32 高等教育出版社 20090 網(wǎng)絡工程 黃軍 38 高等教育出版社 ④ borrow表包含如下數(shù)據(jù)： 學號 圖書編號 借書日期 ----- ---------- ------------- 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,數(shù)據(jù)結構,徐華,29,人民郵電出版社) INSERT INTO book VALUES(10021,數(shù)據(jù)結構,徐華,29,清華大學出版社) INSERT INTO book VALUES(10023,高等數(shù)學,王濤,30,高等教育出版社) INSERT INTO book VALUES(10034,軟件工程,張明,34,機械工業(yè)出版社) INSERT INTO book VALUES(20025,信息學,張港,35,清華大學出版社) INSERT INTO book VALUES(20026,信息學,張港,35,清華大學出版社) INSERT INTO book VALUES(20042,電工學,王民,30,人民郵電出版社) INSERT INTO book VALUES(20056,操作系統(tǒng),曾平,26,清華大學出版社) INSERT INTO book VALUES(20057,操作系統(tǒng),曾平,26,清華大學出版社) INSERT INTO book VALUES(20058,操作系統(tǒng),曾平,26,清華大學出版社) INSERT INTO book VALUES(20067,數(shù)字電路,徐漢,32,高等教育出版社) INSERT INTO book VALUES(20080,數(shù)據(jù)庫原理,陳曼,32,高等教育出版社) INSERT INTO book VALUES(20090,網(wǎng)絡工程,黃軍,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語言使用方法。 實驗內(nèi)容：對于實驗題1創(chuàng)建的Library數(shù)據(jù)庫和表數(shù)據(jù)，編寫程序?qū)崿F(xiàn)如下功能并給出執(zhí)行結果： （1）查詢圖書品種的總數(shù)目。 （2）查詢每種圖書品種的數(shù)目。 （3）查詢各班的人數(shù)。 （4）查詢各系的人數(shù)。 （5）查詢借閱圖書學生的學號、姓名、書名和借書日期。 （6）查詢借有圖書的學生學號和姓名。 （7）查詢每個學生的借書數(shù)目。 （8）找出借書超過2本的學生學號、姓名和所借圖書冊數(shù)。 （9）查詢借閱了"操作系統(tǒng)"一書的學生，輸出學號、姓名及班號。 （10）查詢每個班的借書總數(shù)。 （11）若圖書編號以前3位數(shù)字進行分類，查詢每類圖書的平均價。 （12）查詢平均價高于30的圖書類別。 （13）查詢圖書類別的平均價最高價。 （14）假設借書期限為45天，查詢過期未還圖書編號、書名和借書人學號、姓名。 （15）查詢書名包括“工程”關鍵詞的圖書，輸出書號、書名、作者 （16）查詢現(xiàn)有圖書中價格最高的圖書，輸出書名及作者 （17）查詢所有借閱“C程序設計”一書的學生學號和姓名，再查詢所有借了“C程序設計”但沒有借“C習題解答”的學生學號和姓名。 （18）查詢所有沒有借書的學生學號和姓名。 （19）查詢每個系所借圖書總數(shù)。 （20）查詢各出版社的圖書總數(shù)。 （21）查詢各出版社的圖書占圖書總數(shù)的百分比（四舍五入到一位小數(shù)）。 （22）查詢各出版社的圖書被借的數(shù)目。  參考代碼如下： （1）對應的程序如下： USE Library SELECT COUNT(DISTINCT(圖書名)) AS 圖書品種數(shù)目 FROM book （2）對應的程序如下： USE Library SELECT 圖書名 AS 書名,COUNT(*) AS 數(shù)目 FROM book GROUP BY 圖書名 執(zhí)行結果如下： （3）對應的程序如下： USE Library SELECT 班號 AS 班號,COUNT(*) AS 人數(shù) FROM student GROUP BY 班號 （4）對應的程序如下： USE Library SELECT sc.系名 AS 系名,COUNT(*) AS 人數(shù) 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 數(shù)目 FROM student s,borrow bor WHERE s.學號=bor.學號 GROUP BY s.學號,s.姓名 （8）對應的程序如下： USE Library SELECT s.學號 學號,s.姓名 姓名,COUNT(bor.學號) As 借圖書冊數(shù) 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.圖書名=操作系統(tǒng) ORDER BY s.班號 （10）對應的程序如下： USE Library SELECT s.班號,COUNT(bor.學號) AS 總數(shù) 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.學號 AND bor.圖書編號=b.圖書編號 AND b.圖書名=C程序設計 AND NOT EXISTS( SELECT * FROM borrow bor1,book b1 WHERE bor1.圖書編號=b1.圖書編號 AND b1.圖書名=C習題解答 AND bor1.學號=bor.學號) （18）對應的程序如下： USE Library SELECT 學號,姓名 FROM student WHERE 學號 NOT IN (SELECT DISTINCT 學號 FROM borrow) （19）對應的程序如下： USE Library SELECT sc.系名 AS 系名,COUNT(bor.學號) AS 借書總數(shù) FROM student s,depart sc,borrow bor WHERE s.學號=bor.學號 AND s.班號=sc.班號 GROUP BY sc.系名 （20）對應的程序如下： USE Library SELECT 出版社 AS 出版社,COUNT(*) AS 圖書總數(shù) FROM book GROUP BY 出版社 （21）對應的程序如下： USE Library DECLARE @num int SELECT @num=(SELECT COUNT(*) FROM book) SELECT 出版社 AS 出版社, CAST(ROUND(COUNT(*)*100.0/@num,1) AS decimal(5,1)) AS 圖書比例(%) FROM book GROUP BY 出版社 （22）對應的程序如下： USE Library SELECT 出版社 AS 出版社,COUNT(*) AS 被借圖書數(shù)目 FROM book b,borrow bor WHERE b.圖書編號=bor.圖書編號 GROUP BY 出版社