MySQL數(shù)據(jù)庫原理及應(yīng)用答案實訓(xùn)題.docx

1、課后習(xí)題名稱：學(xué)生選課管理系統(tǒng)(stuCourse)/*/*工程3*/*為學(xué)生選課管理系統(tǒng)創(chuàng)立名為“stucourse”的數(shù)據(jù)庫。*/ create database stucourse;/*為學(xué)生選課管理系統(tǒng)數(shù)據(jù)庫(stucourse)創(chuàng)立表,表結(jié)構(gòu)如下，字段名參考表3-26表3-30。(1)創(chuàng)立學(xué)生表，學(xué)生student (學(xué)號，姓名，性別，年齡，系別)。(2)創(chuàng)立教師表，教師teacher (教師編號，姓名，職稱，工資，系別，課程號)。(3)創(chuàng)立課程表，課程courseinfo (課程編號，課程名稱，教材編號，測試時間，系別)。(4)創(chuàng)立選課表，選課scourse (學(xué)號，分數(shù)，課程編號

2、，教師編號)。(5)創(chuàng)立教材表，教材bookinfo (教材編號，教材名稱，出版社，價格，數(shù)量)*/create table student(sid char(10) not null primary key,sname char(8) not null, sex char(2) null, age int null,dept varchar(20) null)create table teacher(tid char(10) not null primary key, tname char(8) not null, tpassword char(8) not null, title varc

3、har(20), salary float, dept varchar(20), cid char(10)create table courseinfocid char(10) primary key not null, cname varchar(20), ebook char(10),2)、更新數(shù)據(jù)updatescourse set credit=casewhen cid='C4' then 1when cid='C3' then 2when cid='C2' then 3when cid='Cl' then 4 endsel

4、ect * from scourse3)、輸出語句Declare var char(4)select var = (select distinct credit from scourse where cid='Cl')if var>lif var <4print'學(xué)分在23之間，elseprint，學(xué)分大于4,elseprint '學(xué)分小1分，1*3.調(diào)整課程的學(xué)分，對學(xué)分為2的調(diào)整為3,對學(xué)分為1的調(diào)整為2,其他的學(xué)分調(diào)整為 lo */update scourseset credit=casewhen credit=2 then credit+1w

5、hen credit=l then credit+1 else 1end select credit from scourse/*4.事務(wù)的使用。在SC表中，學(xué)號為“1001”學(xué)生的平均成績?nèi)绻∮?5,那么該學(xué)生的每門成績以5%的比例提高，當平均成績大于等于75,或者所有課程都及格時, 終止操作。*/*5,游標的使用。某一選修課程考試結(jié)束后，教師錄入學(xué)生的成績后，出于某些原因（如試 卷本身可能存在缺陷），老師需要將該課程所有的學(xué)生成績加5分（但是總分不能超過100分），修改后的成績?nèi)绻?介于55分59分之間，將這些學(xué)生的成績修改為60分。*/*在SC表中，學(xué)號為1001 學(xué)生的平均成績?nèi)绻?/p>

6、于75,那么該學(xué)生的每門成績以5%的 比例提高，當平均成績大于等于75或者所有課程都及格時，終止操作。*/*（分析:此題可以分四個步驟，請根據(jù)以下提示完成此題.）1）、為了不影響scourse表的其他應(yīng)用，首先將學(xué)號為1001的學(xué)生的所有信息復(fù)制到新表 the scourse*/select *into the_scoursefrom scoursewhere sid='1001'/*2）、查詢復(fù)制后表the_sc中的數(shù)據(jù)及平均成績*/select * from the_scourseselect avg（score） from the_scourse/*3）、更新表the_s

7、course中的數(shù)據(jù)*/while（select avg（score）from the_scourse）75beginupdate the_scourse set score=score*1.05if(select min(score)from the_scourse)>=60 breakend/*4)、查詢更新后表the_scourse中的數(shù)據(jù)及平均成績*/select * from the_scourseselect avg(score) from the_scourse/*工程 6*/.在stucourse數(shù)據(jù)庫中創(chuàng)立一個的觸發(fā)器，該觸發(fā)器不允許的courseinfo表中的cnam

8、e列 進行更新。1 .在stucourse數(shù)據(jù)庫中創(chuàng)立一個觸發(fā)器，當學(xué)生選課時，計算已選的課程門數(shù)，超過5門 不允許進行選課操作。/*工程 7*/.在stucourse數(shù)據(jù)庫中創(chuàng)立新視圖v_score_avg。要求計算每個同學(xué)的選課成績的平均分。1 .在stucourse數(shù)據(jù)庫中使用CREATE INDEX語句為表stu創(chuàng)立一個非聚集索引，索引字段 s_name,索引名為 IX_STUDENT_name。2 .在stucourse數(shù)據(jù)庫中為表course info創(chuàng)立一個復(fù)合索引，按照cname為降序，ctest為升 序進行排序。ctest datetime, dept varchar(lO)

9、 ) create table bookinfo(bid char(10) primary key, bname varchar(30), bpublish varchar(30), bprice double, quantity int)create table scourse(sid char(10) not null, score float null, cid char(5) null, tid char(10)/*向上述表中插入數(shù)據(jù)*/ insert into student values(1001 :宋江男,'25','計算機系）（,3002丁 張明一蛻網(wǎng)&

10、#39;生物系'）,（,1003李小鵬：,男,26。計算機系，）,（1004,嘟冬匚1 女,，'25',計算機系）（,4005丁李小紅:'女'27、'工商管理）（50067趙紫月）女，'24','外語系'）；insert into teacher values('3102', 1 李明，初級'2500',，計算機系,，'C1'),（3108：，黃小明,初級）'4000',生物系）'C3'),('4105',張小紅,“&#

11、39;中級J'3500','工商管理。('5102', 1 宋力月,1高級）'3500',物理系，,C4'),(3106', 1趙明陽,初級'1500',地理系：'C2'),(,71081, 1 張麗一,高級J'3500',生物系,'C3'),('9103', ,王彬,'高級）'3500',計算機系,，'ci'LC7ior,，王力號,初級'1800',1生物系:'Cl')；

12、insert into courseinfo valuesinsert into bookinfo values('bl23T,Image Processing',('bl232','Signal Processing,('bl233'，'Digital Signal Processing',"1234','The Logic Circuit',"1235','SQL Techniques',Ccr,計算機基礎(chǔ),1231；'2009-4-6'

13、;,計算機系)CC2','工商管理基礎(chǔ)'bl232','2009-7-16',工商管理)(3'生物科學(xué),bl233','2010-3-6','生物系')，CC4',大學(xué)物理,'bl234','2009-4-26','物理系'),CC5','數(shù)據(jù)庫原理)'bl235','2010-2-6',計算機系')；insert into bookinfo values('bl23T,Image

14、 Processing',('bl232','Signal Processing,('bl233'，'Digital Signal Processing',"1234','The Logic Circuit',"1235','SQL Techniques','人民出版社,，34.56, 8),'清華出版社'51.75, 10),'郵電出版社'48.5',11),'北大出版社,，'49.2',4

15、0),'郵電出版社)654,20 );insert into scourse values('1001', ClOOl1, ('1001', ('1001', ('3002', ('3002', (,10031, ('1004', ('4005', ('5006)('1001', ClOOl1, ('1001', ('1001', ('3002', ('3002', (,10031, (&

16、#39;1004', ('4005', ('5006)87,77,63,56,78,78,89,56,87,*Cl； 'C2', 'C3', 'C4', 'C3', 'C4', ,Cl； 'C2', 'C4',null, 'Cl','3102'), '4105'), ,31081), '3108'), '3108'), '5102'), '9103&

17、#39;), '3106'), '5102'),'7101');/*在教師表student中，將所有學(xué)生年齡增加1歲。*/update student set age=age+l;/*在教師表teacher中，將教師“黃小明”的稱職由“初級”改為“中級”。*/update teacher set title二"中級“ where tname="黃小明”；/*在教師表teacher中，刪除張小紅教師的記錄。*/delete from teacher where tname='張小紅'/*在教材表bookinf。中

18、，刪除“郵電出版社”的圖書*/delete from bookinfo where bname="由|S電出版社”;/*工程 4*/*查詢?nèi)w學(xué)生的學(xué)號、姓名和年齡。*/select sid,sname,agefrom student/*查詢選修了課程的學(xué)生號。*/select distinct sidfrom scourse/*查詢選修課程號（3,的學(xué)號和成績。*/select sid,scorefrom scoursewhere cid='C3'/*查詢成績高于85分的學(xué)生的學(xué)號、課程號和成績。*/select sid,cid,scorefrom scoursew

19、here score>85/*查詢沒有選修Cl,也沒有選修C2的學(xué)生的學(xué)號、課程號和成績。*/select sid,cid,scorefrom scoursewhere cid not in（'Cl'C2'）select sid,cid,scorefrom scourse where cid!=,Cl' and cid!='C2'/*查詢工資在15002000之間的教師的教師號、姓名及職稱。*/select tid,tnname,titlefrom teacherwhere salary>=1500 and salary<=20

20、00/*查詢選修Cl或C2的學(xué)生的學(xué)號、課程號和成績。*/select sid,cid,scorefrom scoursewhere cid in (,C1,'C2,)select sid,cid,scorefrom scoursewhere cid='Cl' or cid='C2'/*查詢所有姓張的教師的教師號和姓名。*/select tidztnamefrom teacherwhere tnme like '張,/*查詢姓名中第二個漢字是“力”的教師號和姓名。*/select tid,tnamefrom teacherwhere tname/

21、*查詢沒有考試成績的學(xué)生的學(xué)號和相應(yīng)的課程號。*/select sid,cidfrom scoursewhere score is null/*查詢選修Cl的學(xué)生學(xué)號和成績，并按成績降序排列。*/select sid,scorefrom scoursewhere cid='Cl'order by score desc/*查詢選修C2、C3、C4或C5課程的學(xué)號、課程號和成績，查詢結(jié)果按學(xué)號升序排列，學(xué)號 相同再按成績降序排列。*/select sid,cid,scorefrom scoursewhere cid in('C2,C3,C4,C5,)order by sid

22、,score desc/*查詢選修Cl的學(xué)生學(xué)號和成績，并顯示成績前3名的學(xué)生。*/select sid,scorefrom scourseorder by score desc limit 0,3；/*查詢計算機系學(xué)生的總數(shù)。*/select count(sid)from studentwhere dept='計算機系，/*查詢每位學(xué)生的學(xué)號及其選課的門數(shù)。*/select sid,count(*) as 選修課程數(shù)目from scoursegroup by sid/*在分組查詢中使用HAVING條件，查詢平均成績大于85的學(xué)生學(xué)號及平均成績。*/select sid,avg(sco

23、re) as 平均成績from scoursegroup by sidhaving avg(score)>85/*查詢選課在二門以上且各門課均及格的學(xué)生的學(xué)號及其總成績，查詢結(jié)果按總成績降序列 出。*/select sid,sum(score) as 總分from scoursewhere score>=60group by sidhaving count(*)>2order by sum(score) desc/*查詢所有選課學(xué)生的學(xué)號、姓名、選課名稱及成績。*/select student.sidnamecid,scorefrom student,scoursewhere

24、 student.sid=scourse.sid/*查詢選修'Cl'課程且成績在60以上的所有學(xué)生的學(xué)號、姓名和分數(shù)。*/select student.sid,sname,scorefrom student,scoursewhere student.sid=scourse.sid and scourse.cid='Cl' and score>60/*查詢與李明教師職稱相同的教師號、姓名。*/select tid,tname,titlefrom teacherwhere title=(select title from teacher where tname

25、='李明')工程5*/*查詢選修了 C1或C2且分數(shù)大于等于85分的學(xué)生和學(xué)號。*/select sid,cid,scorefrom scoursewhere(cid='Cl' or cid='C2') and score>=85/*查詢工資不在15002000之間的教師的教師號、姓名及職稱。*/select tidztname,titlefrom teacherwhere salary not between 1500 and 2000/*從表bookinfo中查詢書的名稱和單價,使書的單價精確個位即可。*/select bname,ro

26、und(bprice,0) as round_price from bookinfoorder by bname/*使用系統(tǒng)函數(shù)編寫T6QL語句，查詢七月份考試的課程名稱和考試時間。*/select cname,ctestfrom courseinfowhere month(ctest)=4/* 日期采用字符型月份的形式時：datename(month,ctest)=,JULY,*/order by ctest/*從bookinf。表中查詢所有的書名，數(shù)量以及單價信息，并要求所有書名用大寫字母表示。*/Select left(upper(bname),20)as BOOK,quantity, bpriceFrom bookinfo/*從bookinfo表中查詢所有以“Processing”結(jié)尾的書名、數(shù)量以及單價信息。*/Select bname.quantity,bpriceFrom bookinfoWhere right(bname,10)=,Processing'Order by bname/*從bookinfo表中查詢所有的書名、單價信息以及將書名中的字符串"Processin