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

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

2、，教師編號(hào))。(5)創(chuàng)立教材表，教材bookinfo (教材編號(hào)，教材名稱，出版社，價(jià)格，數(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)、輸出語(yǔ)句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é)分，對(duì)學(xué)分為2的調(diào)整為3,對(duì)學(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é)號(hào)為“1001”學(xué)生的平均成績(jī)?nèi)绻∮?5,那么該學(xué)生的每門成績(jī)以5%的比例提高，當(dāng)平均成績(jī)大于等于75,或者所有課程都及格時(shí), 終止操作。*/*5,游標(biāo)的使用。某一選修課程考試結(jié)束后，教師錄入學(xué)生的成績(jī)后，出于某些原因（如試 卷本身可能存在缺陷），老師需要將該課程所有的學(xué)生成績(jī)加5分（但是總分不能超過(guò)100分），修改后的成績(jī)?nèi)绻?介于55分59分之間，將這些學(xué)生的成績(jī)修改為60分。*/*在SC表中，學(xué)號(hào)為1001 學(xué)生的平均成績(jī)?nèi)绻?/p>

6、于75,那么該學(xué)生的每門成績(jī)以5%的 比例提高，當(dāng)平均成績(jī)大于等于75或者所有課程都及格時(shí)，終止操作。*/*（分析:此題可以分四個(gè)步驟，請(qǐng)根據(jù)以下提示完成此題.）1）、為了不影響scourse表的其他應(yīng)用，首先將學(xué)號(hào)為1001的學(xué)生的所有信息復(fù)制到新表 the scourse*/select *into the_scoursefrom scoursewhere sid='1001'/*2）、查詢復(fù)制后表the_sc中的數(shù)據(jù)及平均成績(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ù)及平均成績(jī)*/select * from the_scourseselect avg(score) from the_scourse/*工程 6*/.在stucourse數(shù)據(jù)庫(kù)中創(chuàng)立一個(gè)的觸發(fā)器，該觸發(fā)器不允許的courseinfo表中的cnam

8、e列 進(jìn)行更新。1 .在stucourse數(shù)據(jù)庫(kù)中創(chuàng)立一個(gè)觸發(fā)器，當(dāng)學(xué)生選課時(shí)，計(jì)算已選的課程門數(shù)，超過(guò)5門 不允許進(jìn)行選課操作。/*工程 7*/.在stucourse數(shù)據(jù)庫(kù)中創(chuàng)立新視圖v_score_avg。要求計(jì)算每個(gè)同學(xué)的選課成績(jī)的平均分。1 .在stucourse數(shù)據(jù)庫(kù)中使用CREATE INDEX語(yǔ)句為表stu創(chuàng)立一個(gè)非聚集索引，索引字段 s_name,索引名為 IX_STUDENT_name。2 .在stucourse數(shù)據(jù)庫(kù)中為表course info創(chuàng)立一個(gè)復(fù)合索引，按照cname為降序，ctest為升 序進(jìn)行排序。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','計(jì)算機(jī)系）（,3002丁 張明一蛻網(wǎng)&

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

11、39;中級(jí)J'3500','工商管理。('5102', 1 宋力月,1高級(jí)）'3500',物理系，,C4'),(3106', 1趙明陽(yáng),初級(jí)'1500',地理系：'C2'),(,71081, 1 張麗一,高級(jí)J'3500',生物系,'C3'),('9103', ,王彬,'高級(jí)）'3500',計(jì)算機(jī)系,，'ci'LC7ior,，王力號(hào),初級(jí)'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,計(jì)算機(jī)基礎(chǔ),1231；'2009-4-6'

13、;,計(jì)算機(jī)系)CC2','工商管理基礎(chǔ)'bl232','2009-7-16',工商管理)(3'生物科學(xué),bl233','2010-3-6','生物系')，CC4',大學(xué)物理,'bl234','2009-4-26','物理系'),CC5','數(shù)據(jù)庫(kù)原理)'bl235','2010-2-6',計(jì)算機(jī)系')；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中，將教師“黃小明”的稱職由“初級(jí)”改為“中級(jí)”。*/update teacher set title二"中級(jí)“ where tname="黃小明”；/*在教師表teacher中，刪除張小紅教師的記錄。*/delete from teacher where tname='張小紅'/*在教材表bookinf。中

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

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

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

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

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

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

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

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

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