網(wǎng)絡(luò)數(shù)據(jù)庫技術(shù)教材相關(guān)章節(jié)答案

1、有一些實(shí)驗(yàn)內(nèi)容較為簡單，或在課堂上講過的題目并沒有提供答案，請自行完 成沒有提供答案的各章習(xí)題。 P94 3.實(shí)驗(yàn)內(nèi)容和步驟 (4 )建立約束 獨(dú)立實(shí)踐：為表studnfo 的zipcode列建立default約束 use student go alter tablestudj nfo add con stra int zipcode_dfltdefault 210005 for zipcode (6 )向數(shù)據(jù)庫student的表中插入數(shù)據(jù) 獨(dú)立實(shí)踐：將趙明同學(xué)的入學(xué)成績更新成與學(xué)號為03的孔榮同學(xué)的入學(xué)成績相同 use student go update stud_ info set mar

2、k= (select mark from studnfo where studd=O3) where name =趙明 (7)刪除數(shù)據(jù)庫student的表數(shù)據(jù) 2)刪除計(jì)算機(jī)工程系所有學(xué)生的成績記錄 Delete from studnfo where substri ng (studd,3,2) = 01* P116 3.實(shí)驗(yàn)內(nèi)容及步驟 (1) SELECT語句的基本使用 1)略 2 )略 3 )略 4) Select studd as學(xué)號,name as姓名,address as 地址,telcode as 電話 from studjnfo where qender=，女， 6) Selec

3、t techerjd, name,techitle from teacher_ info where name like 王 * 獨(dú)立實(shí)踐: Select teacher id,course id from teacher info Where telcode like *%3460% 7) Select stud_id,course_id5grade from stud_grade Where grade betwee n 80 and 90 獨(dú)立實(shí)踐 use student go Select studjd ,n ame5address,zipcode from stud_ info wh

4、ere birthday betwee n 01-01-1987a ndf12-31-1987 子查詢的使用 1) Select * from teacherjnfo Where substring (teachjd (select deptcode 獨(dú) 立實(shí)踐：use student ,1,2)= from dept_code where deptname計(jì)算機(jī)工程系 go Select Where (select * from substri ng jyshd teacherj nfo (teacherjd from staffroom info ,1,4)= Where jysh_ na

5、me 2) _ J計(jì)算機(jī)應(yīng)用) use student go select teach Where substring ,name from teacherjnfo (teachjd ,1,2)= (select deptcode And course_id = (select coursejd Where course_name 提示練習(xí)： from dept_code where from lessonnfo -計(jì)算機(jī)專業(yè)英語) deptname =f計(jì)算機(jī)工程系 Select * from stud_grade Where substring (studd ,3, 2)=( select

6、 Deptcode from and dept_code where dept_name id -計(jì)算機(jī)工程) couresejd =(select course from lessonnfo where coure 3) use student name =計(jì)算機(jī)專業(yè)英語 go Select studd ,name , age , telcode ,address From studnfo Where substring spec, 3, 4)=( select substring 計(jì)算機(jī)應(yīng)用技術(shù)提示練習(xí)：) (speccode , 3, 4) from specialty_code whe

7、re use student go Se Select teachd, name, coursejd From teacherjnfo Where subsring (teachjd , 1,2)= (select deptcode And from dept_name substring (teachjd, 3, 2)= (select substring (jyshjd , 3, 2) 4) use student where dept name from staffjnfo 計(jì)算機(jī)工程系1) where jysh_name =f計(jì)算機(jī)應(yīng)用f) go select n ame, telep

8、h one , coursejd from teacherj nfo where substring (teacherjd ,1,2) in (select deptcode from dept_code where dept name =f計(jì)算機(jī)工程系1)and coursejd =( select 獨(dú)立實(shí)踐： coursejdfi rom less on info where course name J多媒體技術(shù)) use student go select studd ame , grade from stud_grade where substri ng (studd , 3,2)=(

9、 selectdeptcode from dept_code where dept name =計(jì)算機(jī)工程 系) and coursejd =(selectcourse, Jd from less onnfo where course_name多媒體技術(shù) J (3 )連接查詢的使用 D use student go select * from studnfoinner join stud_grade on = 獨(dú)立實(shí)踐： use student go select * from teach_schedule on =inner join less onnfo 2) use student go

10、 selectn from teacher_ infoleft outer joinless onnfo on = 獨(dú)立實(shí)踐： use student go select ，n course_date , course_week , roomjd , deptcode from teacher_ inforight outer joi n teach_schedule on = (4)數(shù)據(jù)匯總 1) select from avg (salary )as計(jì)算機(jī)工程系教師平均工資 teacher nfo where left( teacher _id ,2)=( select deptcode

11、from dept_code where dept name 計(jì)算機(jī)工程系 獨(dú)立實(shí)踐： select max (age ) as 計(jì)算機(jī)工程系教師最大年齡 min ( age )計(jì)算機(jī)工程系教師最小年齡 from teacher _info where 2) left( teacher _id ,2)=( select deptcode from dept_code where dept name 7計(jì)算機(jī)工程系 select avg(age) as 平均年齡 from teacher _info where left( teacher _id ,2)=( select deptcode fr

12、om dept code where dept name 計(jì)算機(jī)工程系 獨(dú) 立 實(shí) 踐 select max( mark )入學(xué)最高分,min ( mark )入學(xué)最低分 計(jì)算機(jī)工程 from studnfo where substri ng (studjd , 3, 2)=( select deptcode from dept_code where dept name 系*) 3) select count (*) fromteacherjnfo where left( teacherjd 獨(dú)立實(shí)踐： ,2)=( select deptcode from dept_code where de

13、ptname =f計(jì)算機(jī)工程系 *) use student go selectsum( salary) from teacherjnfo where left( teacherjd ,2)=( select deptcode from dept_code where deptname =計(jì)算機(jī)工程系 *) and coursejd =( select coursejd from lesson_ info wherecourse_name =* 多媒體技術(shù)) (5) GROUP B丫及ORDERBY子句的使用 1) use student (teacherjd ,1, 4), count (t

14、eacherjd )人數(shù) from go select tech_title 職稱，substring teacherj nfo group by tech_title , substring (teacherjd ,1,4) 獨(dú)立實(shí)踐 use student go select gender, count ( studjd ) from studnfo where substring ( studjd , 3, 2)=( select deptcode from dept_code where deptname =f 計(jì)算機(jī)工程 系 1) group by gender 2) select

15、teacherjd 、name , tech_title , age from teacherjnfo where left( teacherjd , 2)=( select deptcode from dept_code where deptname =計(jì)算機(jī)工程系)and tech_title =講師order by age asc 獨(dú)立實(shí)踐: select * from studnfo where substring ( studjd , 3, 2)=( select deptcode from dept_code where deptname =f 計(jì)算機(jī)工程 系 1) order b

16、y birthday asc p121 3.按照下列題目要求，寫出對應(yīng)的的sql語句 (D use student go select name, gender, address from studnfo (2) use student go insert into stud_grade ( 3) values (W / 代燕 1, f04, 60 ) go select studjd group by studd from stud_grade use student go select * from (4) use student stud_grade where grade in( 85

17、,86,88 ) go select * from (5) use student go stud_grade where grade betwee n select studd stud_grade) (6) use student go ,coursejd ,grade from stud_grade select * from (7) use student studjnfo order by mark desc 60 and 80 where grade =( select max( grade) from go select * from studjnfowhere substri

18、ng dept name =計(jì)算機(jī)工程系*) and left() studjd , 6)=( select speccode and substring(studjd ,7, 2)= *02* select count ( studjd )from studjnfo (studjd ,3, 2)=( select from specialty_code deptcode from dept_code where where specname =計(jì)算機(jī)網(wǎng)絡(luò)技術(shù) where substring ( studjd , 3, 2)=( select 系) and left( studd , 6)=(

19、 select speccode )and substring ( studjd , 7, 2)= 02 (9) use student deptcode from dept_code where dept name =計(jì)算機(jī)工程 from specialty_code where specname =計(jì)算機(jī)網(wǎng)絡(luò)技術(shù) 80 having min ( grade ) 60 and max( grade )( select grade from stud_grade where studd =0V and coursejd =05 (11) use student go select studd

20、,name, birthday from studjnfo where year (birthday )=(select year ( birthday ) from studnfo 12) use student go declare a where studd int , b char (10) =02*) =,05, where coursejd select a = count ( studjd ) from stud_grade where coursejd select b = name from teacherjnfo select a學(xué)生人數(shù)，b老師姓名 (13) use st

21、udent go select, dept name from teacherj nfo , dept_code where substri ng (teacherjd , 1,2)= (15) use student go go select teacherjd where = and substring 程系) (16) use student grade from teach_schedule (teacherjd , 1,2)=( select deptcode stud_grade from dept_code where dept name select name, gender

22、from teacherjnfo go select course_namestudjd name, grade from lessonjnfo, stud_grade where = and grade ( select gradefrom stud_gradewhere coursejd =,04,) where substring (teacherjd ,1,2) in (select deptcode from dept_code where deptnamein(計(jì)算機(jī)工程系機(jī)電工程系 *) (17) _ use student and =03 order by grade (18)

23、 desc use student go select * from stud_grade order by substring ( studjd , 7, 4) ( 19) use student go update stud_grade set grade = grade +5 where coursejd =,06, ( 20) delete from studjnfo where name =張?jiān)?P134-135 3.實(shí)驗(yàn)內(nèi)容和步驟 (1)創(chuàng)建視圖 D use student gocreate view stud info female as select * from stud i

24、nfo go select * from stud info female 2) use student go create view studfo_birthday as select studjd , name , birthday go where gender =女 from stud info go create as select view stud_grade_average （學(xué)號，平均成績） stud id ,avg (grade) from stud_grade group by studjd go select * from s t u d_g rad e_ave rag

25、e (2)使用視圖 2) select stud id ，name , year (getdate ()- year ( birthday 丿 as age from stud_ in fo_birthday stud id select 3) in sert 4) update 5) delete from p13 6 2.按照題目要求寫出下列SQL命令, 小ame , datediff ( year, birthday , getdate () as age from stud_ in fo_birthday 并在機(jī)器上進(jìn)行測試 3) use student use student go

26、create view stud view inn er joi n stud_grade on as select stud id ，name、grade from stud info where substri ng (studd , 3, 2)= *01* with check opti on p186 3) use student go create trigger teacher_tri2 on teacher info for delete as if (select begin raiserror will be can celled rollback end (you can

27、not delete the ,10,1) tran sacti on inner join deletedon =0 teacher info with the order recordAthe count (*) from teach_schedule tran sacti on use student go create triggerteacher_tri2 on teacherjnfo for delete as selectinto table_bakk from deleted if (select count (*) from teach_schedule inner join

28、 table_bakkon =table_bakk teacherjd) 0 begin raiserror (you can not delete the teacherj nfo with theorder recordAthe will be can celled ,10,1) rollback tran sacti on end tran sactio n use student go create trigger teacher tri2 on teacher info for delete as if exists( select * from teach schedule in

29、ner join deleted on =deleted .teacher id begi n raiserror will be can celled (you can not delete the ,10,1) teacher info with the order recordAthe tran sacti on end rollback tran sacti on 獨(dú)立實(shí)踐：編寫一個(gè)觸發(fā)器，在對 stude nt 數(shù)據(jù)庫中的表數(shù)據(jù)表studjnfo 執(zhí)行插入、更新和刪除操作后給出相應(yīng) 的提示。 declare a int , b int a = 觀些 if (a0 an)b=0) (你

30、執(zhí)行的是插入操作! select select raiserror (*) (*) from from else if ( a0 and b0) P187 in serted deleted C你執(zhí)行的是更新操作! else if ( a=0 and b0) raiserror raiserror (1你執(zhí)行的是刪除操作! 10J) 2按題目要求寫岀T-SQL語句，并在機(jī)器上進(jìn)行測試 (1) use student go create procedure teache_age40 (tage tinyint ,ttech char (10) as select * from teacher i

31、nfo where age tage and tech title =ttech ttech =f副教授1 go create procedure stud_gender (sex char ( 2), num tinyint output) as select num = count ( gender) from stud info where gender =sex ( 2) (3) declare exec stud_gender select stu_num (4) use student stu_num tinyint 男,stu_num output go create trigg

32、er for delete teacher del on teacher info as declare courid select courid char ( 10)= course id from deleted if courid begin raiserror is not null rollback （該教師已經(jīng)授 課，tran sacti on 無法刪除! 10, 1) end (5) use student go create trigger teacher_cancel_update for insert, update on teacher info as declare select courid char ( 10) courid = coursejd from inserted if not exists( select * from lesson_info where course id =courid ) exec teac