數(shù)據(jù)庫試驗3答案

實驗三：交互式SQL語句的使用1、實驗?zāi)康腟QL2、實驗平臺SQLServerSQLServerSQL3實驗內(nèi)容及要求。3、4、和5???實驗要求包括如下方面的內(nèi)容：3.1數(shù)據(jù)定義1．2．3．3.2數(shù)據(jù)操作1．2．3.3數(shù)據(jù)查詢操作1．2．3.4數(shù)據(jù)操作1．2．參考示例：數(shù)據(jù)定義表保存學(xué)生基本信息，Course課程信息，SC表1.SnSnam2SsexSage4Sclass表2.CourseSC,((8KEY8CHAR,SsexCHAR2(20),SageSdeptCourseCourse(CHAR(4CnameCHAR)4()SMALLINT,SC3）創(chuàng)建Sno(),REFERENCES)(4CHAR,8CHARSno((CHAR()KEY,VARCHAR()CHR,numeric(,2datetime，char(****char4)??UNIQUECname)?2．創(chuàng)建索引（1）為CourseCourse(Cname)（2）為Student(Sname)（3）為SCSCSno,)（4）為Course請自己完成該操作3．創(chuàng)建視圖SnoSnameSdept='IS';3.2數(shù)據(jù)操作1．表女女班表5.課程信息表412436264語言7C表13180213表1）插入到Student（'1001'),男',StudentVALUES('20100001','李勇','INSERT19,'CS','1001')'VALUES'20100002','劉晨,'女',Student女','VALUES,Sdept,Sclass)('20100021','王敏,INTOStudent(SnoSname'1002')'18'MA'男VALUES'20100031','張立','StudentSnoSnameSsex,SageSdept,sclass)),'1003'',19,','1001')VALUES,StudentSnoSnameSsex,sclass)('20100003',,女INTOVALUES('20100010',趙斌'1002'19VALUES'20100022',,,'男''CS'表4)','5',VALUESCourse(CnoCname,CpnoCcredit)('1',null,),,Ccredit)VALUES('2''高等數(shù)學(xué)',CpnoCnoCourseCname,),息系統(tǒng)''1',4,CpnoCnoCourseCnameCcredit)VALUES(3）插入到SC'1'SC('20100001')80VALUES'20100002','2'),(CnoSnoSCVALUESGradeCnoSnoSC(,,)VALUES）多行插入到表中（4創(chuàng)(char8char(8SnoSnameSdept=;***2．22=Sno='20100001';=+1=Sno=Cno5分Grade=+5'CS'student.Sno=.Sno?280?將“20100021”同學(xué)的學(xué)號修改為“20100025”Sno=1tmpSC*在tmpSCSnoCno='CS'student.SnotmpSC.3.3數(shù)據(jù)查詢操作1．?*Student;?Sno,Sname,Student;?Sname'2004-,LOWER(Sdept)Student;?Sname,BIRTH,2000BIRTHDAY,=LOWER(Sdept)Student;?ALL和SC;SC;?Sdept=;?20~23SnameSdept?Sname('IS''MA',?SnameSno,'劉%'?Sno,?Sdept=Sage20;?*Sdept,DESC;??23?17、18、20、23?21~242．?COUNTStudent;?COUNTSno)?MAX(Grade)?2SUM(grade),AVG(grade),MAXgrade)Cno=?,count,AVG(grade)MAXgrade)?90,count,AVG(grade)MAXgrade)AVG(grade)>??（1）在WHERE?StudentSno,Sname,SsexSage,Sdept,CnoStudentStudent.=.?*firstfirst.=second..Cno,second.first,first.=second（2）在FROM?Student.SnoSname,Ssex,,Sdept,Cno,(Student.Sno=.)?Student.SnoSname,Ssex,,Sdept,Cno,(Student.Sno=.Sno)?Student.Sno,(Student.=.)SCCno=SC.>90;?Student.,Sname,Cname,Student,,Student.=..=CourseCno;?2??（1）由In?Sno,Sname,(Sname='劉晨'?Sno,x(AVGGrade)y.Sno=xSno?Sname<(Sdept=);（4）由EXISTS?1(*SnoStudent.Cno='1')?*Sdept=*<=?*Sdept=*Sage?*Sdept*;3.4視圖操作Student_VIEW(,,,,系,)Sno,Sname,,SageSdeptStude