《上機(jī)實(shí)驗(yàn)題(一、二)》由會(huì)員分享，可在線閱讀，更多相關(guān)《上機(jī)實(shí)驗(yàn)題(一、二)（9頁珍藏版）》請?jiān)谘b配圖網(wǎng)上搜索。

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