搜索
数据库系统上机实验题一、学生学习系统的基本表学生：s(sno，sname,sex,age,dept)；表中属性分别为：学号，姓名，性别，年龄和系名。课程：c(cno,cname,tname)；表中属性分别为：课号，课名，教师名。选课：sc(sno,cno,grade)；表中属性分别为：学号，课号和成绩。要求：利用SQL语句创建上面三个基本表，包括：主键，外键定义。有针对性地输入一些实验数据。二、利用SQL语句，在上面三个基本表中，完成如下的功能1.查找学号2至10号学生的学号、姓名和系名。2.查找女同学的人数。3.查找学号2至10号学生中，女同学的人数。4.查找男、女同学各有多少人（注：输出性别和人数）。5.查找‘CS’系中，男、女同学各有多少人（注：输出性别和人数）6.查找年龄在18至20岁之间的所有男同学的学号、姓名和系名。7.查找所有学生选修课程的门数。8.查找学号2至15号学生中，英语成绩超过80分的学生学号和姓名。9.查找没有学习‘物理’课程的学生姓名和系名。10.查找‘王明’同学所学课程的门数。11.统计‘王明’同学所学各门课程的平均分数。12.统计英语成绩高于该课程平均成绩的人数。13.查找查找至少选修了‘王一’和‘王二’老师所授课程的学生学号与姓名。14.查找‘CS’系比‘MA’系的学生年龄都小的学生姓名和系名。15.统计每个学生的总分，输出学生的学号和总分，总分按降序排列。16.查找学号比‘王一’同学大，而年龄比他小的学生学号、姓名和年龄。17.查找年龄大于女同学的平均年龄的男同学的姓名与年龄。18.查找只选修了一门课程的同学的学号和姓名。19.查找‘CS’系学生中，没有选修‘王一’老师课程的学号、姓名和系名。20.查找至少选修了3门课程的学生学号和姓名。21.查找每门课程的最高分和最低分。（注：输出课号、最高分和最低分）22.采用查询视图方式完成21题的功能：利用视图方式的步骤：建立视图，如：gdf（cno，max，min）属性：课号、最高分和最低分。利用SELECT语句对视图gdf查询显示。1.将所有学生的年龄增加1岁。2.将‘物理’课程不及格的成绩改为0分。3.删除‘王五’老师的任课信息4.将一门课的成绩大于等于80分学生的学号、姓名和系名插入到一个已存在（要建立）基本表stu（sno，sname，dept）中。分配空间createdatabaseccjj创建表s,c,sccreatetables(snochar(10)primarykey,snamevarchar(10)unique,sexchar(2),agesmallint,deptchar(4));createtablec(cnochar(10)primarykey,cnamevarchar(10),tnamechar(10),);createtablesc(snochar(10),cnochar(10),gradesmallint,primarykey(sno,cno),foreignkey(sno)referencess(sno),foreignkey(cno)referencesc(cno),);S表SC表C表11.查找学号2至10号学生的学号、姓名和系名。selectsno,sname,deptfromswheresnobetween2and5;2.查找女同学的人数。selectsex,count(sno)人数fromswheresex='女'groupbysex;3.查找学号2至10号学生中，女同学的人数。selectsex,count(sno)fromswheresex='女'andsnobetween2and5groupbysex;4.查找男、女同学各有多少人（注：输出性别和人数）selectsex,count(sno)人数fromsgroupbysex;5.查找‘CS’系中，男、女同学各有多少人（注：输出性别和人数）fromswheredept='ce'groupbysex;6.查找年龄在18至20岁之间的所有男同学的学号、姓名和系名。selectsno,sname,deptfromswhereagebetween18and20andsex='男';7.查找所有学生选修课程的门数。selectsno,count(cno)门数fromscgroupbysno;8.查找学号2至15号学生中，英语成绩超过80分的学生学号和姓名。selects.sno,snamefroms,c,scwheres.sno=sc.snoandc.cno=sc.cnoands.snobetween2and4andcname='英语'andgrade80;9.查找没有学习‘物理’课程的学生姓名和系名。selectsname,deptfromswherenotsnoin(selectsnofromscwherecnoin(selectcnofromcwherenotcname'物理'))10.查找‘王明’同学所学课程的门数。selectsname,count(cno)门数froms,scwheres.sno=sc.snoands.sname='王明'groupbysname;11.统计‘王明’同学所学各门课程的平均分数。selectsname,avg(grade)平均分fromsc,swheresc.snoin(selectsnofromswheresname='王明')ands.sname='王明'groupbysname;12.统计英语成绩高于该课程平均成绩的人数。selectcount(*)人数fromscwheregrade(selectavg(grade)fromscwherecnoin(selectcnofromcwherecname='英语'));13.查找查找至少选修了‘王一’和‘王二’老师所授课程的学生学号与姓名。selectsno,snamefromswheresnoin(selectsnofromscwherecno=(selectcnofromcwheretname='王一')orcno=(selectcnofromcwheretname='王二')groupbysnohavingcount(cno)=2);14.查找‘CS’系比‘MA’系的学生年龄都小的学生姓名和系名。selectsname,deptfromswhereage(selectmin(age)fromswheredept='ma')anddept='cs';15.统计每个学生的总分，输出学生的学号和总分，总分按降序排列。selectsno,sum(grade)总分fromscgroupbysnoorderby2desc16.查找学号比‘王一’同学大，而年龄比他小的学生学号、姓名和年龄。selectsno,sname,agefromswheresno(selectsnofromswheresname='王一')andage(selectagefromswheresname='王一')17.查找年龄大于女同学的平均年龄的男同学的姓名与年龄。selectsname,agefromswhereage(selectavg(age)fromswheresex='女')andsex='男';18.查找只选修了一门课程的同学的学号和姓名。selects.sname,s.snofromsc,swheres.sno=sc.snogroupbys.sno,s.snamehavingcount(*)=1;19.查找‘CS’系学生中，没有选修‘王一’老师课程的学号、姓名和系名。selectsno,sname,deptfromswherenotsnoin(selectsnofromscwheresc.cnoin(selectcnofromcwherenottname'王一'))anddept='cs';20.查找至少选修了3门课程的学生学号和姓名。selects.sname,s.snofromsc,swheres.sno=sc.snogroupbys.sno,s.snamehavingcount(*)=3;21.查找每门课程的最高分和最低分。（注：输出课号、最高分和最低分）selectmax(grade)最高分,min(grade)最低分fromscgroupbycno;22.采用查询视图方式完成21题的功能：利用视图方式的步骤：建立视图，如：gdf（cno，max，min）属性：课号、最高分和最低分。利用SELECT语句对视图gdf查询显示。createviewgdf(cno,max,min)asselectcno,max(grade),min(grade)fromscgroupbycno;1将所有学生的年龄增加1岁。updatessetage=age+1;2将‘物理’课程不及格的成绩改为0分。updatescsetgrade=0wherecnoin(selectcnofromcwherecname='物理'andgrade60)3删除‘王五’老师的任课信息deletefromscwherecnoin(selectcnofromcwheretname='王五')deletefromcwheretname='王五'4将一门课的成绩大于等于80分学生的学号、姓名和系名插入到一个已存在（要建立）基本表stu（sno，sname，dept）中。createtablestu(snochar(10)primarykey,snamevarchar(10)unique,deptchar(4));insertintostu(sno,sname,dept)selectsno,sname,deptfromswheresnoin(selectsnofromscwheregrade80)举一反三1.查询每个学生及其选修课的情况selects.*,sc.*froms,scwheres.sno=sc.sno2.查询选修“政治”的学生姓名与学号selectsname,snofromswheresnoin(selectsnofromscwherecnoin(selectcnofromcwherecname='政治'))3.找出每个学生超过他选修课平均成绩的课程号selectsno,cnofromsct1wheregrade=(selectavg(grade)fromsct2wheret1.sno=t2.sno)