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

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

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

3、在BORROW_SAVE表中(注ORROW_SAVE 表結(jié)構(gòu)同BORROW表。12.建立一個視圖,顯示"力"班學(xué)生的借書信息(只要求顯示姓名和書名。13.查詢當(dāng)前同時借有"計算方法"和"組合數(shù)學(xué)"兩本書的讀者,輸出其借書卡號,并按卡號升序排序輸出。14.假定在建BOOKS表時沒有定義主碼,寫出為BOOKS表追加定義主碼的語句。15.對CARD表做如下修改:a.將NAME最大列寬增加到個字符(假定原為個字符。b.為該表增加列NAME(系名,可變長,最大個字符。-創(chuàng)建數(shù)據(jù)庫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ù)庫use lib;/*CA

5、RD 借書卡。 CNO 卡號,NAME 姓名,CLASS 班級BOOKS 圖書。 BNO 書號,BNAME 書名,AUTHOR 作者,PRICE 單價, QUANTITY 庫存冊數(shù)BORROW 借書記錄。CNO 借書卡號,BNO 書號,RDATE 還書日期*/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;-限定每人每種書只能借一本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'測試','白骨精',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ù),那么沒有放在聚合函數(shù)里面的字段,全部放到group by后面去。-(5看是否需要對查詢出來的結(jié)果集再次進(jìn)行篩選,如果需要,那么使用having-(6看是否需要排序,考慮使用order by-(7如果一個題目,覺得比較復(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,'語文','張三', 20 unionselect 2,'數(shù)學(xué)','李四', 30 unionselect 3,'英語','王五', 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.寫出建立BORROW表的SQL語句,要求定義主碼完整性約束和引用完整性約束。- 2.找出借書超過本的讀者,輸出借書卡號及所借圖書冊數(shù)。select o "卡號",cname

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

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

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

18、quot;計算方法習(xí)題集"的讀者,輸出其借書卡號,-并按卡號降序排序輸出。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;-查詢既接了號書又借了號書的卡號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 表示同一個人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-查看計算方法這本書的編號select bno from books where bname='SQL'-查看計算方法習(xí)題集這本書的編號select bno from books where bname='網(wǎng)絡(luò)技術(shù)'- 8.將"C01"班同學(xué)所借圖書的還期都延長

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

22、ORROW_SAVE表結(jié)構(gòu)同BORROW表。12.建立一個視圖,顯示"力"班學(xué)生的借書信息(只要求顯示姓名和書名。- 13.查詢當(dāng)前同時借有"計算方法"和"組合數(shù)學(xué)"兩本書的讀者,輸出其借書卡號,-并按卡號升序排序輸出。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='組合數(shù)學(xué)

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

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

25、號CNO VARCHAR(5NOT NULL,-課程號DEGREE NUMERIC(10, 1NOT NULL-成績goCREATE TABLE TEACHER(TNO VARCHAR(3NOT NULL,-教師編號TNAME VARCHAR(4NOT NULL, TSEX VARCHAR(2NOT NULL,-教師姓名TBIRTHDAY DATETIME NOT NULL, PROF VARCHAR(6,-出生日期DEPART VARCHAR(10NOT NULL-系部名稱-測試數(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ī)導(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,'李誠','男','1958-12-02','副教授','計算機(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ī)系'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表中成績在到之間的所有記錄。5、查詢Score表中成績?yōu)?或的記錄。6、查詢Student表中"95031"班或性別為"女"的同學(xué)記錄。7、以Class降序查詢

34、Student表的所有記錄。8、以Cno升序、Degree降序查詢Score表的所有記錄。9、查詢"95031"班的學(xué)生人數(shù)。10、查詢Score表中的最高分的學(xué)生學(xué)號和課程號。11、查詢'3-105'號課程的平均分。12、查詢Score表中至少有名學(xué)生選修的并以開頭的課程的平均分?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è)使用如下命令建立了一個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"課程的成績高于"109"號同學(xué)成績的所有同學(xué)的記錄。20、查詢score中選學(xué)一門以上課程的同學(xué)中分?jǐn)?shù)為非最高分成績的記錄。21、查詢成績高于學(xué)號為"109"、課程號為"3-105"的成績的所有記錄。22、查詢和學(xué)號為的同學(xué)同年出生的所有學(xué)生的Sno、Sname和Sbirthday列。23、查詢"張旭"教師任