數(shù)據(jù)庫上機(jī)練習(xí)第七章

1、 第七章 1、SELECT sc. Sno, Sname, Grade , CASE WHENGrade =90 THEN 好 WHENGrade betwee n 80 and 89 THEN 較好 WHENGrade betwee n 70 and 79 THEN 一般 WHENGrade betwee n 60 and 69 THEN 較差 WHENGrade 4 THEN 多 WHENCOUNTSC. Cno) BETWEEN? AND 4 THEN 一般 WHENCOUNTSC. Cno) BETWEEN1 AND 2 THEN 少 WHENCOUNTSC. Cno) =0 THEN

2、 未選 END AS選課情況 GROUPBY S. Sno ORDER BY COUNT SC. Cno) DESC SQLQuery2.sqi - Cl.,.-PC匚占(55) SQLQueryl.sqI - PCCh El SELECT 3-Sno, CCUTTT fSC-Cnoi 選i東| J數(shù) WHEN COUNT (SC. Cno) 4 THEN 孩 WHEN COUNT if SC .匚noi TWEEN 2 AN匸 THEN 般 WHEN COUNT (SC. Cno) BETWEEN 1 AND 2 THEN .少. WHEN COUNT (SC. Cno) = O TrTEN

3、 未選 * end as選課情況 FROM Student S 匚EFT JOIN SC ON S.Sno = SC.Sno GROUP BY S.Sno -ORDER BY COUNTiSC.Cno) DESC =90 THEN 好 WHENavg ( Grade ) betwee n 80 and 89 THEN 較好 WHENavg ( Grade ) betwee n 70 and 79 THEN 一般 WHENavg ( Grade ) betwee n 60 and 69 THEN 較差 WHENavg ( Grade ) -90 THEN 好 between 80 and 89

4、THEN between 70 and 79 THEN 匕皂tween 60 and 9 THEN 60 THEN 嗟. 好般差 saT 肪 t V- CASE SC ON a . Snc = SC. Sno ，- 匚aurae c CM c . Cno = 5C. Cnc WHERE Cname - * JAVA* roup by depc 壞結(jié)果|由誚扈 dept考試情況 in in 1 計(jì)尊機(jī)系較墓 ir 2 唾信工程系較差 3 信息管理茶一般 4、UPDATE course SET credit =credit CASE WHEN semester betwee n 1 and 2 T

5、HEN 5 WHEN semester betwee n 3 and 4 THEN 3 WHEN semester betwee n 5 and 6 THEN 1 ELSE 0 END SQLQuerylsql 亠(1PCChengwei (53)* credit credic 曰 UPDATE course SET 匸ASE WHEN sexester between 1 -snd W?iEN seireste r Jsecween 3 and WHEN seice ste r t)etween S and THEN 1 ELSE END rrr 估行受影響 8 cno cname cred

6、it S|em cir. SC on CclIi36. Cnosc. Znc - Qin. Student on SEuzenc. SnoSC. Snc where snane=T5L3 1 intersect select cnaire, sir.esterf credit from Course join SC on Couzse.Cno=5C*Coo join Studenu on 5ldent.5no=SC.Sdo -where 5nane=* J大力 / 111 口第果 j消息 cname semester credit 18、selectCname, semester , cred

7、it from Studen Course join SC on Course t . Sno = SC. Sno .Cno=SC. Cno join Stude nton where sname =李勇 except select Cname, semester ,credit from Course joi n SC on Course .Cno=SC. Cno join Stude nton Student . Sno = SC. Sno where sname =王大力 /SQLQuery4.qI - (1.-PCChengwei (56)* 日 select Cnairsef sem

8、eBterf credit： f ram Course join SC on Course .口口口二匸C：nci join Student on Studen匸.S:no=5C Snci where snaire=l：S 1 except sei皂匚匸 Cnairsef semester, u工亡dit (from Course join SC on Course 5口=5匚C!nci join Student on Student Snci=S匚 S:nci -wher皂曰口已un.亡=王大力1 1mi 結(jié)果 齒消息 Cname semester credit 1 Java 3 2 2 犬

9、學(xué)英語 1 3 3 犬學(xué)英語 2 3 4 高等數(shù)學(xué) 1 4 19、select SC. Sno, SC Cno from Course join SC on Course .Cno=SC. Cno join Stude nton Student . Sno = SC. Sno where SC. Sno in( select t1 . sno from (select * from SC where Cno=C001) as t1 join (select* from SC where Cn o=C002) as t2 on t1 . Sno=t2 . Sno) QLQuery4.sql -

10、(1- PCChengwei (56)* select SC.Snof SCxCno frem Course join SC on Course + CnoSCUno join Suudenc on Scudent,5no3C + Sno where SC.Sno in select cl.sno from- from SC where Cno= hC0011 as 匸1 olti select - frox SC where Cnc= * C002 as Z2 -on tl. Sno=t2.5noj| 彳 Irrr 結(jié)果 j涓息 Sro Cno ! CM1 1 * 011101 2 0811

11、101 CW2 3 0811101 4 Q811101 CODE 5 0811102 CM1 G 0811102 C002 7 811102 CCM 20、select sno , sname , dept , COUNT*) over ( partition by dept )該 系的學(xué)生人數(shù)from Student 5QLQuery4.sql - (l_-PCChengwei (56)* select sna ? snaiEe f dept P CCUMT - T over partition by depr)該系的學(xué)生人數(shù) frciri SuudenT 賈一J cn結(jié)果庇消息 sno s

12、name dept 該系的學(xué)生人數(shù) 1 1 0911101 I李闍 計(jì)直機(jī)系 4 2 (1(811102 劉艮 計(jì)尊機(jī)系 4 3 O8111Q3 王敏 計(jì)聲機(jī)乘 4 4 08111 IM 張小紅 計(jì)聲機(jī)熹 4 5 0(831101 賤小平 區(qū)信工程累 3 盟31訕2 王大力 通信工程系 3 7 063110(3 SHIM 誦信工程系 3 E 0B211Q1 張立 信邑宣理事 3 S K8211U2 吳賓 3 10 wnm 張海 肯息官理系 3 11 0(831 ItM 張明 1 21、create VIEW IS_student as select sname , dept , year (

13、GETDATEQ)- year ( Birthdate ) age from stude nt select sname , dept AVG( age) over () as平均年齡 MAX age) over () as最大年齡 MIN( age) over () as最小年齡 from IS_stude nt (local ” 活動(dòng)鑒視器 SQLQueryLsql - (1-PCChengwei (Sfi)* 【 3 How many loved your mome nts of glad grace, And loved your beauty with love false or t

14、rue, But one man loved the pilgrim soul in you, And loved the sorrows of your cha nging face; And bending dow n beside the glow ing bars, Murmur, a little sadly, how love fled And paced upon the mountains overhead And hid his face amid a crowd of stars. The furthest dista nee in the world Is not bet

15、wee n life and death But whe n I sta nd in front of you Yet you dont know that I love you. The furthest dista nee in the world Is not whe n I sta nd in front of you Yet you cant see my love But whe n un doubtedly knowing the love from both Yet cannot be together. The furthest dista nee in the world Is not being apart while being in love But whe n I pla inly cann