SQL經(jīng)典練習(xí)題目-初學(xué)者必看

1、本題用到下面三個(gè)關(guān)系表:CARD 借書(shū)卡。 CNO 卡號(hào),NAME姓名,CLASS 班級(jí)BOOKS 圖書(shū)。 BNO 書(shū)號(hào),BNAME 書(shū)名,AUTHOR 作者,PRICE 單價(jià), QUANTITY 庫(kù)存冊(cè)數(shù)BORROW 借書(shū)記錄。CNO 借書(shū)卡號(hào),BNO 書(shū)號(hào),RDATE 還書(shū)日期備注:限定每人每種書(shū)只能借一本;庫(kù)存冊(cè)數(shù)隨借書(shū)、還書(shū)而改變。要求實(shí)現(xiàn)如下個(gè)處理:1.寫(xiě)出建立BORROW表的SQL語(yǔ)句,要求定義主碼完整性約束和引用完整性約束。2.找出借書(shū)超過(guò)本的讀者,輸出借書(shū)卡號(hào)及所借圖書(shū)冊(cè)數(shù)。3.查詢借閱了"水滸"一書(shū)的讀者,輸出姓名及班級(jí)。4.查詢過(guò)期未還圖書(shū),輸出借閱者(

2、卡號(hào)、書(shū)號(hào)及還書(shū)日期。5.查詢書(shū)名包括"網(wǎng)絡(luò)"關(guān)鍵詞的圖書(shū),輸出書(shū)號(hào)、書(shū)名、作者。6.查詢現(xiàn)有圖書(shū)中價(jià)格最高的圖書(shū),輸出書(shū)名及作者。7.查詢當(dāng)前借了"計(jì)算方法"但沒(méi)有借"計(jì)算方法習(xí)題集"的讀者,輸出其借書(shū)卡號(hào),并按卡號(hào)降序排序輸出。8.將"C01"班同學(xué)所借圖書(shū)的還期都延長(zhǎng)一周。9.從BOOKS表中刪除當(dāng)前無(wú)人借閱的圖書(shū)記錄。10.如果經(jīng)常按書(shū)名查詢圖書(shū)信息,請(qǐng)建立合適的索引。11.在BORROW表上建立一個(gè)觸發(fā)器,完成如下功能:如果讀者借閱的書(shū)名是"數(shù)據(jù)庫(kù)技術(shù)及應(yīng)用",就將該讀者的借閱記錄保存

3、在BORROW_SAVE表中(注ORROW_SAVE 表結(jié)構(gòu)同BORROW表。12.建立一個(gè)視圖,顯示"力"班學(xué)生的借書(shū)信息(只要求顯示姓名和書(shū)名。13.查詢當(dāng)前同時(shí)借有"計(jì)算方法"和"組合數(shù)學(xué)"兩本書(shū)的讀者,輸出其借書(shū)卡號(hào),并按卡號(hào)升序排序輸出。14.假定在建BOOKS表時(shí)沒(méi)有定義主碼,寫(xiě)出為BOOKS表追加定義主碼的語(yǔ)句。15.對(duì)CARD表做如下修改:a.將NAME最大列寬增加到個(gè)字符(假定原為個(gè)字符。b.為該表增加列NAME(系名,可變長(zhǎng),最大個(gè)字符。-創(chuàng)建數(shù)據(jù)庫(kù)create database libon primary(nam

4、e='lib',filename='D:lib.mdf',size=3Mb,maxsize=10Mb,filegrowth= 1Mb,(name='lib_ndf',filename='D:lib_ndf.ndf',size=3Mb,maxsize=10Mb,filegrowth= 1Mblog on(name='lib_log',filename='D:lib_log.ldf',size=3Mb,maxsize=10Mb,filegrowth= 10%-使用自己創(chuàng)建的數(shù)據(jù)庫(kù)use lib;/*CA

5、RD 借書(shū)卡。 CNO 卡號(hào),NAME 姓名,CLASS 班級(jí)BOOKS 圖書(shū)。 BNO 書(shū)號(hào),BNAME 書(shū)名,AUTHOR 作者,PRICE 單價(jià), QUANTITY 庫(kù)存冊(cè)數(shù)BORROW 借書(shū)記錄。CNO 借書(shū)卡號(hào),BNO 書(shū)號(hào),RDATE 還書(shū)日期*/create table card(cno int primary key identity(1,1,cname varchar(20not null,class varchar(20not nullcreate table books(bno int primary key identity(1,1,bname varchar(20,a

6、uthor varchar(20,price float,quantity intcreate table borrow(cno int not null foreign key references card(cno,bno int not null foreign key references books(bno, rdate datetimedrop table borrow;-限定每人每種書(shū)只能借一本alter table borrowadd constraint PK_borrow_cno_bno primary key(cno,bno; insert into cardselect

7、'張三','test01'unionselect'李四','test01'unionselect'王五','java01'unionselect'趙六','java02'unionselect'孫七','java01'insert into booksselect'SQL','李華',89,50 unionselect'oracle','唐僧',80,15 unionsel

8、ect'java','孫悟空',78,23 unionselect'c','豬八戒',80,34 unionselect'c+','沙僧',67,56 unionselect'測(cè)試','白骨精',34,45 unionselect'水滸','小白龍',45,55 unionselect'網(wǎng)絡(luò)技術(shù)','牛魔王',34,65insert into borrowselect 1,1,null unionsele

9、ct 1,2,null unionselect 1,3,null unionselect 1,4,null unionselect 1,5,null unionselect 2,3,null unionselect 2,4,null unionselect 2,5,null unionselect 2,6,null unionselect 3,1,null unionselect 3,2,null unionselect 3,3,nullinsert into borrowvalues(3,7,nullinsert into borrowvalues(2,7,null-SQL題目的秘籍-(1看

10、題目中間要求我們“顯示|查詢|列出|輸出”什么內(nèi)容。那么select后面就接什么內(nèi)容。-(2看這些字段出自哪些表,那么from后面就接哪些表-(3如果from后面有多張表,首先考慮表之間的關(guān)聯(lián)關(guān)系(隱士關(guān)系。然后再考慮題目中顯示的告訴我們的關(guān)系(條件。-(4看是否需要分組,那么就考慮是否要使用group by,-看select子句后面是否出現(xiàn)了聚合函數(shù),如果出現(xiàn)了聚合函數(shù),那么沒(méi)有放在聚合函數(shù)里面的字段,全部放到group by后面去。-(5看是否需要對(duì)查詢出來(lái)的結(jié)果集再次進(jìn)行篩選,如果需要,那么使用having-(6看是否需要排序,考慮使用order by-(7如果一個(gè)題目,覺(jué)得比較復(fù)雜,那

11、么將題目拆分,采用子查詢。create table card(cno int not null,name varchar(20,class varchar(20,alter table cardadd constraint PK_card_cno primary key(cno;create table books(bno int not null,bname varchar(20,author varchar(20,price money,quantity intalter table booksadd constraint PK_books_bno primary key(bno;crea

12、te table borrow(cno int not null,bno int not null,rdate datetimealter table borrowadd constraint PK_card_books_cno_bno primary key(cno,bno; use huitinginsert into booksselect 1,'語(yǔ)文','張三', 20 unionselect 2,'數(shù)學(xué)','李四', 30 unionselect 3,'英語(yǔ)','王五', 40 union

13、select 4,'物理','馬六', 50select*from booksalter table booksadd constraint CK_books_price check(price between 20 and 100;use huitinginsert into borrowselect 9,1,'2011-07-08 00:00:00'unionselect 8,2,'2011-07-08 00:00:01'unionselect 7,3,'2011-07-08 00:00:02'unionsel

14、ect 6,4 ,'2011-07-08 00:00:03'select*from borrowinsert into cardselect 9,'張三', 0 unionselect 8,'李四',1 unionselect 7,'王五', 2 unionselect 6,'馬六',3select*from card- 1.寫(xiě)出建立BORROW表的SQL語(yǔ)句,要求定義主碼完整性約束和引用完整性約束。- 2.找出借書(shū)超過(guò)本的讀者,輸出借書(shū)卡號(hào)及所借圖書(shū)冊(cè)數(shù)。select o "卡號(hào)",cname

15、 "姓名",t.num "所借圖書(shū)冊(cè)數(shù)"from card,(select cno,count(bnoas numfrom borrowgroup by cnohaving count(bno>=5 twhere o =o-表示借書(shū)查過(guò)本的卡號(hào)以及借書(shū)數(shù)目select cno,count(bnoas numfrom borrowgroup by cnohaving count(bno>=5;select*from books;select*from card;select*from borrow;-3.查詢借閱了"水滸"一

16、書(shū)的讀者,輸出姓名及班級(jí)。select cname,classfrom cardwhere cno in(select cno from borrow where bno=(select bno from books where bname ='水滸'- in 拿一個(gè)字段和多個(gè)值進(jìn)行比較,表示在某個(gè)范圍里面- = 一個(gè)字段和一個(gè)值比較-首先查看“水滸”這本書(shū)的編號(hào)select bno from books where bname ='水滸'-到borrow表中看哪些卡號(hào)借了號(hào)這本書(shū)select cno from borrow where bno=(select

17、bno from books where bname ='水滸'4.查詢過(guò)期未還圖書(shū),輸出借閱者(卡號(hào)、書(shū)號(hào)及還書(shū)日期。-5.查詢書(shū)名包括"網(wǎng)絡(luò)"關(guān)鍵詞的圖書(shū),輸出書(shū)號(hào)、書(shū)名、作者。select bno,bname,authorfrom bookswhere bname like'%網(wǎng)絡(luò)%'- 6.查詢現(xiàn)有圖書(shū)中價(jià)格最高的圖書(shū),輸出書(shū)名及作者。select bname,authorfrom bookswhere price in(select max(pricefrom books- 7.查詢當(dāng)前借了"計(jì)算方法"但沒(méi)有借&

18、quot;計(jì)算方法習(xí)題集"的讀者,輸出其借書(shū)卡號(hào),-并按卡號(hào)降序排序輸出。Card(cno,name,classBooks(bno,bname,author,price,quantityBorrow(cno,bno,rdateselect cnofrom borrowwhere bno =(select bno from books where bname='c'select cnofrom borrowwhere bno in(select bno from books where bname='水滸'order by cno desc-自身連接查詢

19、select*from borrow;select*from borrow;-查詢既接了號(hào)書(shū)又借了號(hào)書(shū)的卡號(hào)select ofrom borrow b1,borrow b2where o =o and b1.bno =(select bno from books where bname='水滸'and b2.bno =(select bno from books where bname='c'-o = o 表示同一個(gè)人select distinct t.bcnofrom(select o bcno,b1.bno,b1.rdatefrom borrow b1,bo

20、rrow b2where o = o and b1.bno <> 7 and b2.bno = 1 and o not in(select ofrom borrow b1,borrow b2where o = o and b1.bno = 7 and b2.bno = 1t-查看計(jì)算方法這本書(shū)的編號(hào)select bno from books where bname='SQL'-查看計(jì)算方法習(xí)題集這本書(shū)的編號(hào)select bno from books where bname='網(wǎng)絡(luò)技術(shù)'- 8.將"C01"班同學(xué)所借圖書(shū)的還期都延長(zhǎng)

21、一周。update borrow set rdate=dateadd(dd,7,rdate;- 9.從BOOKS表中刪除當(dāng)前無(wú)人借閱的圖書(shū)記錄。-首先:查看有哪些書(shū)已經(jīng)被借了select distinct bnofrom borrow-再去刪除delete from books where bno not in(select distinct bnofrom borrow 10.如果經(jīng)常按書(shū)名查詢圖書(shū)信息,請(qǐng)建立合適的索引。11.在BORROW表上建立一個(gè)觸發(fā)器,完成如下功能:如果讀者借閱的書(shū)名是"數(shù)據(jù)庫(kù)技術(shù)及應(yīng)用",就將該讀者的借閱記錄保存在BORROW_SAVE表中(注

22、ORROW_SAVE表結(jié)構(gòu)同BORROW表。12.建立一個(gè)視圖,顯示"力"班學(xué)生的借書(shū)信息(只要求顯示姓名和書(shū)名。- 13.查詢當(dāng)前同時(shí)借有"計(jì)算方法"和"組合數(shù)學(xué)"兩本書(shū)的讀者,輸出其借書(shū)卡號(hào),-并按卡號(hào)升序排序輸出。select ofrom borrow b1,borrow b2where o =o and b1.bno =(select bno from books where bname='計(jì)算方法'and b2.bno =(select bno from books where bname='組合數(shù)學(xué)

23、- 14.假定在建BOOKS表時(shí)沒(méi)有定義主碼,寫(xiě)出為BOOKS表追加定義主碼的語(yǔ)句。alter table borrowadd constraint PK_borrow_cno_bno primary key(cno,bno;- 15.對(duì)CARD表做如下修改:- a. 將NAME最大列寬增加到個(gè)字符(假定原為個(gè)字符。alter table cardalter column cname varchar(10not null- b. 為該表增加列NAME(系名,可變長(zhǎng),最大個(gè)字符。alter table cardadd DeptName varchar(20復(fù)制代碼執(zhí)行,創(chuàng)建好表,然后往里面插入數(shù)

24、據(jù),完成下面?zhèn)€題目:CREATE TABLE STUDENT(SNO VARCHAR(3NOT NULL,-學(xué)號(hào)SNAME VARCHAR(4NOT NULL,-姓名SSEX VARCHAR(2NOT NULL,-性別SBIRTHDAY DATETIME,-出生日期CLASS VARCHAR(5-班級(jí)goCREATE TABLE COURSE(CNO VARCHAR(5NOT NULL,-課程號(hào)CNAME VARCHAR(10NOT NULL,-課程名TNO VARCHAR(10NOT NULL-教師編號(hào)goCREATE TABLE SCORE(SNO VARCHAR(3NOT NULL,-學(xué)

25、號(hào)CNO VARCHAR(5NOT NULL,-課程號(hào)DEGREE NUMERIC(10, 1NOT NULL-成績(jī)goCREATE TABLE TEACHER(TNO VARCHAR(3NOT NULL,-教師編號(hào)TNAME VARCHAR(4NOT NULL, TSEX VARCHAR(2NOT NULL,-教師姓名TBIRTHDAY DATETIME NOT NULL, PROF VARCHAR(6,-出生日期DEPART VARCHAR(10NOT NULL-系部名稱-測(cè)試數(shù)據(jù)INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASSVALU

26、ES (108 ,'曾華','男',1977-09-01,95033;INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASSVALUES (105 ,'匡明','男',1975-10-02,95031;INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASSVALUES (107 ,'王麗','女',1976-01-23,95033;INSERT INTO STUDENT (SNO,SNAME,SSEX,S

27、BIRTHDAY,CLASSVALUES (101 ,'李軍','男',1976-02-20,95033;INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASSVALUES (109 ,'王芳','女',1975-02-10,95031;INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASSVALUES (103 ,'陸君','男',1974-06-03,95031;GOINSERT INTO COURS

28、E(CNO,CNAME,TNOVALUES('3-105','計(jì)算機(jī)導(dǎo)論',825INSERT INTO COURSE(CNO,CNAME,TNOVALUES('3-245','操作系統(tǒng)',804; INSERT INTO COURSE(CNO,CNAME,TNOVALUES('6-166','數(shù)據(jù)電路',856; INSERT INTO COURSE(CNO,CNAME,TNOVALUES('9-888','高等數(shù)學(xué)',100; GOINSERT INTO SCOR

29、E(SNO,CNO,DEGREEVALUES(103,'3-245',86; INSERT INTO SCORE(SNO,CNO,DEGREEVALUES(105,'3-245',75; INSERT INTO SCORE(SNO,CNO,DEGREEVALUES(109,'3-245',68; INSERT INTO SCORE(SNO,CNO,DEGREEVALUES(103,'3-105',92; INSERT INTO SCORE(SNO,CNO,DEGREEVALUES(105,'3-105',88; I

30、NSERT INTO SCORE(SNO,CNO,DEGREEVALUES(109,'3-105',76; INSERT INTO SCORE(SNO,CNO,DEGREEVALUES(101,'3-105',64; INSERT INTO SCORE(SNO,CNO,DEGREEVALUES(107,'3-105',91; INSERT INTO SCORE(SNO,CNO,DEGREEVALUES(108,'3-105',78; INSERT INTO SCORE(SNO,CNO,DEGREEVALUES(101,'6

31、-166',85; INSERT INTO SCORE(SNO,CNO,DEGREEVALUES(107,'6-106',79; INSERT INTO SCORE(SNO,CNO,DEGREEVALUES(108,'6-166',81;GOINSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART VALUES(804,'李誠(chéng)','男','1958-12-02','副教授','計(jì)算機(jī)系'INSERT INTO TEACH

32、ER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART VALUES(856,'張旭','男','1969-03-12','講師','電子工程系'INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART VALUES(825,'王萍','女','1972-05-05','助教','計(jì)算機(jī)系'INSERT INTO TEACHER(TNO,TNAME,TSEX

33、,TBIRTHDAY,PROF,DEPART VALUES(831,'劉冰','女','1977-08-14','助教','電子工程系'練習(xí)題目1、查詢Student表中的所有記錄的Sname、Ssex和Class列。2、查詢教師所有的單位即不重復(fù)的Depart列。3、查詢Student表的所有記錄。4、查詢Score表中成績(jī)?cè)诘街g的所有記錄。5、查詢Score表中成績(jī)?yōu)?或的記錄。6、查詢Student表中"95031"班或性別為"女"的同學(xué)記錄。7、以Class降序查詢

34、Student表的所有記錄。8、以Cno升序、Degree降序查詢Score表的所有記錄。9、查詢"95031"班的學(xué)生人數(shù)。10、查詢Score表中的最高分的學(xué)生學(xué)號(hào)和課程號(hào)。11、查詢'3-105'號(hào)課程的平均分。12、查詢Score表中至少有名學(xué)生選修的并以開(kāi)頭的課程的平均分?jǐn)?shù)。13、查詢最低分大于,最高分小于的Sno列。14、查詢所有學(xué)生的Sname、Cno和Degree列。15、查詢所有學(xué)生的Sno、Cname和Degree列。16、查詢所有學(xué)生的Sname、Cname和Degree列。17、查詢"95033"班所選課程的平均分

35、。18、假設(shè)使用如下命令建立了一個(gè)grade表:create table grade(low float,upp float,rank char(1; insert into grade values(90,100,'A'insert into grade values(80,89,'B'insert into grade values(70,79,'C'insert into grade values(60,69,'D'insert into grade values(0,59,'E'commit;現(xiàn)查詢所有同學(xué)的Sno、Cno和rank列。19、查詢選修"3-105"課程的成績(jī)高于"109"號(hào)同學(xué)成績(jī)的所有同學(xué)的記錄。20、查詢score中選學(xué)一門(mén)以上課程的同學(xué)中分?jǐn)?shù)為非最高分成績(jī)的記錄。21、查詢成績(jī)高于學(xué)號(hào)為"109"、課程號(hào)為"3-105"的成績(jī)的所有記錄。22、查詢和學(xué)號(hào)為的同學(xué)同年出生的所有學(xué)生的Sno、Sname和Sbirthday列。23、查詢"張旭"教師任