上機(jī)實(shí)驗(yàn)題(一、二)

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

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