1、【精編匯總版】SQL測試內(nèi)容合集】8/8【精編匯總版】SQL測試內(nèi)容合集試驗(yàn)1成就描述： 本題用到下面三個關(guān)系表格模板： CARD (CXO, NAME, CLASS)借書卡。CNO卡號，NAMB姓名，CLASS班級 BOOKS (BNO, BNAME, AUTHOR, PRICE, QUANTITY)圖書。BNO書號，BNAME書名，AUTHOR作者，PRICE單價， QUANTITY庫存冊數(shù)BORROW (CNO, BNO, RDATE)借書記朵:。CNO借號，BNO書號，RDATE還歸日期 剖析：限定每人每種書只能借一本；庫存冊數(shù)隨借書、還書而改動。試驗(yàn)內(nèi)容：實(shí)現(xiàn)如下16個措置方案：1.

2、用SQL語句険立CARD、BOOKS. BORROW表格模板，要求定義主碼完整性約朿和引用 完整性約束；用INSERT語句向表格模板中插入數(shù)據(jù)，每個表格模板中至少冇5筆記錄。CREATE TABLE CARD(CNO CHAR(8) PRIMARY KEY,NAME CHAR(10), CLASS CHAR(10) )； CREATE TABLE BOOKS (BNO CHAR(8) PRIMARY KEY,BNAME CHAR(10),AUTHOR CHAR(10),P RTCE TNT, QUANTITY INT )； CREATE TABLE BORROW (CNO CHAR(8),BN

3、O CHAR(8),RDATE DATETIME, PRIMARY KEY(CXO,BNO), FOREIGN KEY(CXO) REIEREXCES CARD(CXO), FOREIGN KEY(BXO)REFERENCES BOOKS(BXO), )； INSERT INTO CARDVALUES ( 1305210，李連杰，08-1);INSERT INTO CARDYALUESC 1305211,王，087);INSERT INTO CARDVALUES ( 1305212李，08T);INSERTINTO CARDVALUESC 1305213,王，087);INSERTINTO C

4、ARDVALCESC 1305214,趙，087);INSERTINTO BOOKSVALUES C 10000，,)經(jīng)濟(jì)學(xué)，梁曉明，50, 2):INSERTINTO BOOKSVALLES C 10001, / 信息平靜英語，余芳，50, 2)：INSERTINTO BOOKSALLES( 10002盤算機(jī)網(wǎng)絡(luò)，上的撒，50, 2):INSERTINTO BOOKSVALUES ( 10003,數(shù)據(jù)庫，土里，50, 2);INSERTINTO BOOKSVALUES ( 10004網(wǎng)絡(luò)，王軍，50, 2);INSERTINTO BORROWVALUES ( 130521010000, 11

5、111);INSERTINTO BORROWVALUES(, 130521T f * 10001*, 11112);INSERTINTO BORROWVALUESC 1305212,J 10002, 11112):INSERTINTO BORROWVALUES (, 1305213,* 10003, 11112);INSERTINTO BORROWVALUES (, 1305214, * 10004, 11113);2.找出借書超越5本的讀者,輸岀借書卡號及所借圖書冊數(shù)。select CXO, COUNT(CNO)FROM BORROWGROUP BY CNOHAVING COUNT(CNO)

6、7IIselect CNO, COUET ： CNOI FROM BORROWH GROUP BY CNOU HAVING COUNT(CNO)7L令已成功光成。；3.杳詢借閱了水滸”一書的隹者，輸力姓名及視級。select CARD. NAME, CLASSFROM CARD, BOOKS, BORROW where BNAME -水滸AND BOOKS. BXO-BORROW. BNO AND CARD. CKOBORROW. CNOselect CARD.NAME. CLASSFROM CARD, BOOKS. BORROWwhere BNAME =水滸AND BOOKS. BNO=BO

7、RROW. BNOAND CARD.CNO:BORROW.CNO飭令已成功完成.4.査詢過期未還圖書，輸出借閱者(卡號)、書號及還書日期。select CNO, BNO, RDATEFROM BORROWwhere RDATEGETDATE()select CNO, BNO, RDATE FROM BORROWwhere RDATEGETDATE()5.杳詢書名包括網(wǎng)絡(luò)”關(guān)鍵詞的圖書，輸出書修、書名、作者。 select BNO, BNAME, AUTHORFROM BOOKSwhere BNAME LIKE %網(wǎng)絡(luò)対select BIK), BNAME. AUTHOR FROM BOOKSw

8、here BNAME LIKE 納給飭令已成功完成。6.査詢現(xiàn)有圖書中代價展高的圖書，輸出書名及作者。 select BNAME, AUTHORFROM BOOKSWHERE EXISTS(selecT PRICE, MAX(PRICE)FROM BOOKSGROUP BY PRICEHAVING PR1CE=MAX(PRICE)select BNAMEj AUTHORFROM BOOKSWHERE EXISTS(selecT PRICE, MAX PRICE)FROM BOOKSGROUP BY PRICEHAVING PRICE=MAX PRICE)飭令已成功克成7- Z7.百詢W前借了H

9、翦由r但沒力.借”計(jì)兌方式習(xí)題集”的讀者，輸他其借書卡 號，并按卜號降序排序輸出。select BNO1ROM BOOKSWHERE BNAME=盤算方式AND BAME!=盤算機(jī)方式習(xí)題集ORDER BY BNO DESCselect BNOFROM BOOKSWHERE BNAME盤算方式AND BNAHE1=盤算機(jī)方式習(xí)題集ORDER BY BNO DESC0INSERT BORROW SAVE SELECT i.*FROM INSERTED i,BOOKS bWHERE i. BNO-b. BNOAND b.BNAME=N數(shù)據(jù)庫技術(shù)及應(yīng)用構(gòu)同BORROW表格模板),ORRO SAVE0

10、 CREATE TABLE BORROW.SAVE(CNO CHARS?！?BNO CHAR 10), RDATE DATETIME ;CREATE TRIGGER TR_SAVE ON BORROW FOR INSERT, UPDATE AS IF aROWCOUNT0 INSERT BORROW SAVE SELECT i. * FROM INSERTED i,BOOKS b WHERE i.BNO b. BNO AND b.BNAME二N數(shù)據(jù)庫技術(shù)及應(yīng)用 t飭令已成功先成。12.成立一個視圖，浮現(xiàn)丿J 01班學(xué)生的借書佶息(只要求浮現(xiàn)姓幺和書名)。CREATE VIEW BORROWS(

11、NAME, BNAME)ASSELECT CARD. NAME, BNAMEFROM CARD, BOOKS, BORROWWHERE CLASS=信息 087 AND BOOKS. BNO-BORROW. BNOAND CARD. CNOBORROW. CKOCREATE VIEW BORROWS NAME, BNAME) ASSELECT CARD. NAME, BNAMEFROM CARD, BOOKS, BORROWWHERE CLASS=信息Q8-1 AND BOOKS. BNO二BORROW. BNOANIi CARD. CNOBORROW. CIK) 13.-査詢當(dāng)前同時借仃計(jì)兌

12、方式和組合數(shù)學(xué)”兩本書的讀擰，輸他.貝:借書卡 號，并按卡號升序排序輸出。select enofrom borrowwhere bno=(select bno from bookswherebname=(盤算方式)and eno in (select eno from borrow where bno二(select bno from books where bname=組合數(shù)學(xué)) order by eno asc14.假定在建BOOKS表格模板時沒冇定義主椚，寫出為BOOKS表格模板追加定義主碼的語句, ALTER TABLE Books ADD PRIMARY KEY(BNO)嗖查詢 一 20100817T920arter. 21Mol)817-19 4 ALTER TABLE Books ADD PRIMARY KEY (BNO)I 口成果I15.對CARD我做如下改削：a.將NAME最大列寬增加到10個字符(假定原為6個字符)。ALTER TABLE CARI) ALTER COLUMN NAME CHAR (10)b.為該我增加1列MME (系名)，可變長，最人20個字符。ALTER TABLE CARD ADD SC CHAR(20)試驗(yàn)報(bào)吿要求：按下面的次第記錄每一個措置方案步驟及成果:1、題H2、正確的