Sql-Server-SQL练习题

测试表格--1.学生表Student(S#,Sname,Sage,Ssex)--S#学生编号,Sname学生姓名,Sage出生年月,Ssex学生性别--2.课程表Course(C#,Cname,T#)--C#--课程编号,Cname课程名称,T#教师编号--3.教师表Teacher(T#,Tname)--T#教师编号,Tname教师姓名--4.成绩表SC(S#,C#,score)--S#学生编号,C#课程编号,score分数创建测试数据学生表StudentcreatetableStudent(S#varchar(10),Snamenvarchar(10),Sagedatetime,Ssexnvarchar(10))insertintoStudentvalues('01',N'赵雷','1990-01-01',N'男')insertintoStudentvalues('02',N'钱电','1990-12-21',N'男')insertintoStudentvalues('03',N'孙风','1990-05-20',N'男')insertintoStudentvalues('04',N'李云','1990-08-06',N'男')insertintoStudentvalues('05',N'周梅','1991-12-01',N'女')insertintoStudentvalues('06',N'吴兰','1992-03-01',N'女')insertintoStudentvalues('07',N'郑竹','1989-07-01',N'女')insertintoStudentvalues('08',N'王菊','1990-01-20',N'女')科目表CoursecreatetableCourse(C#varchar(10),Cnamenvarchar(10),T#varchar(10))insertintoCoursevalues('01',N'语文','02')insertintoCoursevalues('02',N'数学','01')insertintoCoursevalues('03',N'英语','03')教师表TeachercreatetableTeacher(T#varchar(10),Tnamenvarchar(10))insertintoTeachervalues('01',N'张三')insertintoTeachervalues('02',N'李四')insertintoTeachervalues('03',N'王五')成绩表SCcreatetableSC(S#varchar(10),C#varchar(10),scoredecimal(18,1))insertintoSCvalues('01','01',80)insertintoSCvalues('01','02',90)insertintoSCvalues('01','03',99)insertintoSCvalues('02','01',70)insertintoSCvalues('02','02',60)insertintoSCvalues('02','03',80)insertintoSCvalues('03','01',80)insertintoSCvalues('03','02',80)insertintoSCvalues('03','03',80)insertintoSCvalues('04','01',50)insertintoSCvalues('04','02',30)insertintoSCvalues('04','03',20)insertintoSCvalues('05','01',76)insertintoSCvalues('05','02',87)insertintoSCvalues('06','01',31)insertintoSCvalues('06','03',34)insertintoSCvalues('07','02',89)insertintoSCvalues('07','03',98)５０道练习题1.查询01课程比02课程成绩高的学生的信息及课程分数1.1查询同时存在01课程和02课程的情况1.2查询存在01课程但可能不存在02课程的情况(不存在时显示为null)1.3查询不存在01课程但存在02课程的情况2.查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩selectnewmap(sc.student.id,sc.student.name,avg(sc.num))fromScorescleftjoinsc.studentgroupbysc.studenthavingavg(sc.num)603.查询在SC表存在成绩的学生信息fromScore4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为null)selectnewmap(sc.student.id,sc.student.name,count(sc),sum(sc.num))fromScorescrightjoinsc.studentgroupbysc.student4.1查有成绩的学生信息selectdistinctnewmap(stu.id,stu.name,stu.birthday)fromScorescjoinStudentstuonsc.student=stu5.查询「李」姓老师的数量selectcount(*)fromTeachertwherenamelike?6.查询学过「张三」老师授课的同学的信息selectdistinctnewmap(stu.id,stu.name)fromselectsc.studentfromScorescwherescin(selectScorefromTeachertleftjoinCourseconc.teacher=twheret.name='张三')groupbysc.student)asBleftjoinStudentstuonB=stu7.查询没有学全所有课程的同学的信息selectnewmap(stu.id,stu.name)fromStudentstuwherestu.idin(selectsc.student.idfromScorescgroupbysc.studenthavingcount(sc.student)3)8.查询至少有一门课与学号为01的同学所学相同的同学的信息selectdistinctnewmap(stu.id,stu.name)fromStudentstujoinScoresconsc.student=stuwheresc.course.idin(selectnewmap(sc.course.id)fromScorescwheresc.student.id=1)9.查询和01号的同学学习的课程完全相同的其他同学的信息10.查询没学过张三老师讲授的任一门课程的学生姓名11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩selectnewmap(stu.id,stu.name,avg(sc.num))fromStudentstujoinScoresconsc.student=stuwherestu.idin(selectnewmap(sc.student.id)fromScorescwheresc.num60groupbysc.studenthavingcount(sc.student)=2)groupbystu12.检索01课程分数小于60，按分数降序排列的学生信息selectnewmap(sc.student.id,sc.student.name,sc.num)fromScorescwheresc.course.id=1andsc.num60orderbysc.numdesc13.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩selectnewmap(sc.student.name,avg(sc.num))fromScorescgroupbysc.studentorderbyavg(sc.num)desc14.查询各科成绩最高分、最低分和平均分：selectnewmap(sc.course.name,max(sc.num),min(sc.num),avg(sc.num))fromScorescgroupbysc.course以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率及格为=60，中等为：70-80，优良为：80-90，优秀为：=90要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列15.按各科成绩进行排序，并显示排名，Score重复时保留名次空缺selectnewmap(sc.student.name,sc.course.name,sc.num)fromScorescwheresc.course.id=1orderbysc.numselectnewmap(sc.student.name,sc.course.name,sc.num)fromScorescwheresc.course.id=2orderbysc.numselectnewmap(sc.student.name,sc.course.name,sc.num)fromScorescwheresc.course.id=3orderbysc.num15.1按各科成绩进行排序，并显示排名，Score重复时合并名次selectnewmap(sc.student.name,sc.course.name,sc.num)fromScorescwheresc.course.id=1orderbysc.numselectnewmap(sc.student.name,sc.course.name,sc.num)fromScorescwheresc.course.id=2orderbysc.numselectnewmap(sc.student.name,sc.course.name,sc.num)fromScorescwheresc.course.id=3orderbysc.num16.查询学生的总成绩，并进行排名，总分重复时保留名次空缺selectnewmap(index()sc.student.name,sum(sc.num)astotal)fromScorescgroupbysc.studentorderbysum(sc.num)desc16.1查询学生的总成绩，并进行排名，总分重复时不保留名次空缺selectnewmap(index()sc.student.name,sum(sc.num)astotal)fromScorescgroupbysc.studentorderbysum(sc.num)desc17.统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0]及所占百分比18.查询各科成绩前三名的记录session.createQuery(selectnewmap(sc.student.name,sc.course.name,sc.num)fromScorescwheresc.course.id=1orderbysc.numdesc).setFirstResult(0).setMaxResults(3).list();session.createQuery(selectnewmap(sc.student.name,sc.course.name,sc.num)fromScorescwheresc.course.id=2orderbysc.numdesc).setFirstResult(0).setMaxResults(3).list();session.createQuery(selectnewmap(sc