(selectYEAR(GETDATE())-year(birthdate)fromstudentwheredept='信息管理系')
anddept='计算机系'
8、selectsname姓名,YEAR(GETDATE())-year(birthdate)年龄fromstudent
whereYEAR(GETDATE())-year(birthdate)>all
(selectYEAR(GETDATE())-year(birthdate)fromstudentwheredept='信息管理系')
anddept='计算机系'
9、SELECTCno课程号,Cname课程名FROMCourse
WHERENOTEXISTS
(SELECT*FROMSCWHERECno=Course.Cno)
10、SELECTSname姓名FROMStudent
WHEREDept='计算机系'ANDNOTEXISTS
(SELECT*FROMSCWHERESno=Student.Sno)
11、SELECTSno学号,C.Cno所选课程的课程号,Semester开课学期
FROMCourseCJOINSCONSC.Cno=C.CnoWHERENOTEXISTS
(SELECT*FROMCourseWHERESemester=2ANDNOTEXISTS
(SELECT*FROMSCXWHEREX.Cno=C.Cno))
12、selectsno,deptfromstudents
wherenotexists(select*fromCoursec
wheresemester=4andnotexists(select*fromSC
whereSC.Cno=oandSC.Sno=s.sno))
13、selectsnofromstudents
wherenotexists(select*fromSCc
wherec.Sno='0831102'andnotexists(select*fromSC
whereSC.Cno=oandSC.Sno=s.sno))
14、SELECTS.Sno学号,Dept所在系,C.Cno课程号
FROMStudentSJOINSCS1ONS.Sno=S1.Sno
JOINCourseCONC.Cno=S1.Cno
WHERENOTEXISTS(
SELECT*FROMCourseCJOINSCONC.Cno=SC.Cno
JOINStudentSONS.Sno=SC.Sno
WHERESname='张海'
ANDNOTEXISTS(
SELECT*FROMSCX
WHEREX.Cno=C.Cno
ANDX.Sno=S1.Sno
))
ANDSname!
='张海'
15、selectsc.Sno,Dept,sc.Cno,cname,credit
fromstudentsjoinSCons.Sno=SC.SnojoinCourseconc.Cno=SC.Sno
wherenotexists(select*fromCoursec
wherec.Credit>3andnotexists(select*fromSC
whereSC.Cno=oandSC.Sno=s.sno))
16、SELECTCname课程名,Credit学分FROMCourseC
WHERESemester=4ANDNOTEXISTS
(SELECT*FROMCourseC1
WHEREC1.Credit=C.CreditANDC.Semester=1)
17、selectcname,semester,credit
fromCoursejoinSConCourse.Cno=SC.CnojoinStudentonStudent.Sno=SC.Sno
wheresname='李勇'
intersect
selectcname,semester,credit
fromCoursejoinSConCourse.Cno=SC.CnojoinStudentonStudent.Sno=SC.Sno
wheresname='王大力'
18、selectCname,semester,credit
fromCoursejoinSConCourse.Cno=SC.CnojoinStudentonStudent.Sno=SC.Sno
wheresname='李勇'
except
selectCname,semester,credit
fromCoursejoinSConCourse.Cno=SC.CnojoinStudentonStudent.Sno=SC.Sno
wheresname='王大力'
19、selectSC.Sno,SC.Cno
fromCoursejoinSConCourse.Cno=SC.CnojoinStudentonStudent.Sno=SC.Sno
whereSC.Snoin(selectt1.snofrom(select*fromSCwhereCno='C001')ast1
join(select*fromSCwhereCno='C002')ast2
ont1.Sno=t2.Sno)
20、selectsno,sname,dept,COUNT(*)over(partitionbydept)该系的学生人数fromStudent
21、createVIEWIS_student
as
selectsname,dept,year(GETDATE())-year(Birthdate)agefromstudent
selectsname,dept,
AVG(age)over()as平均年龄,
MAX(age)over()as最大年龄,
MIN(age)over()as最小年龄
fromIS_student
22、selectsno,sname,sex,dept,
COUNT(*)over(partitionbydept)as系总人数,
COUNT(Sex)over(partitionbysex,dept)as男女生人数,
cast(1.0*count(sex)over(partitionbysex,dept)/COUNT(*)
over(partitionbydept)asdecimal(5,2))男女生百分比
fromStudent
orderbydept
23、selectsc.Sno,sname,Cname,grade,
dense_RANK()over(orderbygradedesc)as成绩排名
fromCoursejoinSConCourse.Cno=SC.CnojoinStudentonStudent.Sno=SC.Sno
24、selectsc.Sno,sc.Cno,grade,
dense_RANK()over(partitionbyoorderbygradedesc)asRANK
fromCoursejoinSConCourse.Cno=SC.CnojoinStudentonStudent.Sno=SC.Sno
25、selectSname,Dept,birthdate,
dense_RANK()over(partitionbydeptorderbybirthdateasc)asRANK
fromStudent