sql语句练习题及答案

一在数据库school中建立student,sc,course表。学生表、课程表、选课表属于数据库School，其各自的数据结构如下：学生Student(Sno,Sname,Ssex,Sage,Sdept)序号列名含义数据类型长度1Sno学号字符型(char)62Sname姓名字符型(varchar)83Ssex性别字符型(char)24Sage年龄整数(smallint)5sdept系科字符型(varchar)15课程表course(Cno,Cname,Cpno,Ccredit)序号列名含义数据类型长度1Cno课程号字符型(char)42cname课程名字符型(varchar)203Cpno先修课字符型(char)44Ccredit学分短整数(tinyint)学生选课SC(Sno,Cno,Grade)序号列名含义数据类型长度1Sno学号字符型(char)62Cno课程号字符型(char)43Grade成绩小数(decimal)12,2二设定主码1Student表的主码：sno2Course表的主码：cno3Sc表的主码：sno，cno1写出使用CreateTable语句创建表student,sc,course的SQL语句2在student表中插入信息学号姓名性别年龄系科4001赵茵男20SX4002杨华女21JSJ3删除student表中的元组4在数据库school中删除关系student5在student表添加属性sbirthdate类型datetime练习Delete1删除所有JSJ系的男生deletefromStudentwhereSdept=’JSJ’andSsex=’男’;2删除“数据库原理”的课的选课纪录deletefromSCwhereCnoin(selectCnofromCoursewhereCname=’数据库原理’);Update1修改0001学生的系科为:JSJ2把陈小明的年龄加1岁，性别改为女。2修改李文庆的1001课程的成绩为93分3把“数据库原理”课的成绩减去1分Select查询语句一单表1查询年龄在19至21岁之间的女生的学号,姓名,年龄,按年龄从大到小排列。2查询姓名中第2个字为“明”字的学生学号、性别。3查询1001课程没有成绩的学生学号、课程号4查询JSJ、SX、WL系的年龄大于25岁的学生学号,姓名，结果按系排列5按10分制查询学生的sno,cno,10分制成绩（1-10分为1，11-20分为2，30-39分为3，。。。90-100为10）6查询student表中的学生共分布在那几个系中。（distinct）7查询0001号学生1001，1002课程的成绩。二统计1查询姓名中有“明”字的学生人数。2计算‘JSJ’系的平均年龄及最大年龄。3查询学生中姓名为张明、赵英的人数4计算每一门课的总分、平均分，最高分、最低分，按平均分由高到低排列5计算1001,1002课程的平均分。6查询平均分大于80分的学生学号及平均分7统计选修课程超过2门的学生学号8统计有10位成绩大于85分以上的课程号。9统计平均分不及格的学生学号10统计有大于两门课不及格的学生学号三连接1查询JSJ系的学生选修的课程号2查询选修1002课程的学生的学生姓名(不用嵌套及嵌套2种方法）3查询数据库原理不及格的学生学号及成绩4查询选修“数据库原理”课且成绩80以上的学生姓名(不用嵌套及嵌套2种方法）5查询平均分不及格的学生的学号，姓名,平均分。6查询女学生平均分高于75分的学生姓名。7查询男学生学号、姓名、课程号、成绩。(一门课程也没有选修的男学生也要列出，不能遗漏)四嵌套、相关及其他1查询平均分不及格的学生人数2查询没有选修1002课程的学生的学生姓名3查询平均分最高的学生学号及平均分（2种方法TOP,any,all）*4查询没有选修1001，1002课程的学生姓名。5查询1002课程第一名的学生学号（2种方法）6查询平均分前三名的学生学号7查询JSJ系的学生与年龄不大于19岁的学生的差集8查询1001号课程大于90分的学生学号、姓名及平均分大于85分的学生学号、姓名9查询每门课程成绩都高于该门课程平均分的学生学号10查询大于本系科平均年龄的学生姓名答案参考答案1createtablestudent(snochar(6),snamevarchar(8),ssexchar(2),sagesmallint,sdeptvarchar(15),primarykey(sno));createtablesc(snochar(6),cnochar(4),gradedecimal(12,2),primarykey(sno,cno));insertintostudentvalues(’4001’,’赵茵’,’男’,20,’SX’)deletefromstudentdroptablestudentaltertablestudentaddsbirthdatedatetime1selectsno,sname,sagefromstudentwheressex=’女’andsagebetween19and21orderbysagedesc;2selectsno,ssexfromstudentwheresnamelike’_明%’;3selectsno,cnofromscwheregradeisnullandcno=’1001’;4selectsno,snamefromstudentwheresdeptin(’JSJ’,’SX’,’WL’)andsage25groupbysdept;selectsno,cno,grade/10.0+1aslevelfromsc;selectdistinctsdeptfromstudent;selectgradefromscwheresno=’0001’and(cno=’1001’orcno=’1002’);selectcount(*)fromstudentwheresnamelike’%明%’;selectavg(sage),max(sage)fromstudentwheresdept=’JSJ’;selectcno,sum(grade),avg(grade),max(grade),min(grade)fromscgroupbycnoorderbyavg(grade)desc;selectcno,avg(grade)fromscwherecnoin(‘1001’,’1002’)groupbycno;selectsc.sno,avg(grade)fromscgroupbysc.snohavingavg(grade)80;selectsnofromscgroupbysnohavingcount(*)2;selectcnofromscwheregrade85groupbycnohavingcount(*)=10;selectsnofromscgroupbysnohavingavg(grade)60;selectsnofromscwheregrade60groupbysnohavingcount(*)2;selectcnofromstudent,scwherestudent.sno=sc.snoandsdept=’JSJ’;a:selectsnamefromstudent,scwherestudent.sno=sc.snoandcno=’1002’b:selectsnamefromstudentwheresnoin(selectsnofromscwherecno=’1002’)selectsno,gradefromsc,coursewheresc.cno=course.cnoandcname=’数据库原理’andgrade60a:selectsnamefromstudent,sc,coursewherestudent.sno=sc.snoandsc.cno=course.cnoandgrade80andcname=’数据库原理’b:selectsnamefromstudentwheresnoin(selectsnofromscwheregrade80andcnoin(selectcnofromcoursewherecname=’数据库原理’))selectsno,sname,avg(grade)fromsc,studentwherestudent.sno=sc.snogroupbystudent.snohavingavg(grade)60a:selectsnamefromstudentwheressex=’女’andsnoin(selectsnofromscgroupbysnohavingavg(grade)75)b:selectsnamefromsc,studentwherestudent.sno=sc.snoandssex=’女’groupbystudent.snohavingavg(grade)75selectstudent.sno,sname,cno,gradefromstudentleftjoinsconstudent.sno=sc.snoandssex=’男’selectcount(*)fromstudentwheresnoin(selectsnofromscgroupbysnohavingavg(grade)60)selectsnamefromstudentwheresnonotin（selectsnofromscwherecno=’1002’）student0001aaX0002bb0003ccXSc00011001000110020002100100031002Selectsnamefromstudentwherenotexists(select*fromscwherecno=’1002’andsc.sno=student.sno)a:selecttop1sno,avg(grade)fromscgroupbysnoorderbyavg(grade)descb:selectsno,avg(grade)fromscgroupbysnohavingavg(grade)=(selecttop1avg(grade)fromscgroupbysnoorderbyavg(grade)desc)c:selectsno,avg(grade)fromscgroupbysnohavingavg(grade)=all(selectavg(grade)fromscgroupbysno)selectsnamefromstudentwherenotexists(select*fromcoursewherecnoin(‘1001’,’1002’)andnotexists(select*fromscwheresno=student.snoandcno=course.cno))a:selecttop1snofromsccno=’1002’orderbygradedescb:selectsnofromscwherecno=’1002’andgrade=all(selectgradefromscwherecno=’1002’)selecttop3snofromscgroupbysnoorderbyavg(grade)desca:select*fromstudentwheresdept=’JSJ’andsage19b:select*fromstudentwheresdept=’JSJ’exceptselect*fromstudentwheresage19selectstudent.sno,snamefromstudent,scwherecno=’1001’andgrade90unionselectsno,snamefromstudentwheresnoin（selectsnofromscgroupbysnohavingavg(grade)85)selectsnofromstudentwheresn