数据库作业.docx
《数据库作业.docx》由会员分享，可在线阅读，更多相关《数据库作业.docx（16页珍藏版）》请在冰点文库上搜索。
数据库作业
2．将“习题（学生数据库数据）.xls”中的数据导入到这三张表中。
INSERTINTOstudentVALUES（'9512101','李勇','男',49,'计算机系'）;
INSERTINTOstudentVALUES（'9512102','刘晨','男',20,'计算机系'）;
INSERTINTOstudentVALUES（'9512103','王敏','女',20,'计算机系'）;
INSERTINTOstudentVALUES（'9521101','张立','男',22,'信息系'）;
INSERTINTOstudentVALUES（'9521102','吴宾','女',21,'信息系'）;
INSERTINTOstudentVALUES（'9521103','张海','男',20,'信息系'）;
INSERTINTOstudentVALUES（'9531101','钱小平','女',18,'数学系'）;
INSERTINTOstudentVALUES（'9531102','王大力','男',19,'数学系'）;
INSERTINTOcourseVALUES（'c01','计算机文化学',3,1）;
INSERTINTOcourseVALUES（'c02','VB',2,3）;
INSERTINTOcourseVALUES（'c03','计算机网络',4,7）;
INSERTINTOcourseVALUES（'c04','数据库基础',6,6）;
INSERTINTOcourseVALUES（'c05','高等数学',8,2）;
INSERTINTOcourseVALUES（'c06','数据结构',5,4）;
INSERTINTOscVALUES（'9512101','c01',90,'必修'）;
INSERTINTOscVALUES（'9512101','c02',86,'选修'）;
INSERTINTOsc（sno,cno,xslb）VALUES（'9512101','c06','必修'）;
INSERTINTOscVALUES（'9512102','c02',78,'选修'）;
INSERTINTOscVALUES（'9512102','c04',66,'必修'）;
INSERTINTOscVALUES（'9521102','c01',82,'选修'）;
INSERTINTOscVALUES（'9521102','c02',75,'选修'）;
INSERTINTOscVALUES（'9521102','c04',92,'必修'）;
INSERTINTOscVALUES（'9521102','c05',50,'必修'）;
INSERTINTOscVALUES（'9521103','c02',68,'选修'）;
INSERTINTOsc（sno,cno,xslb）VALUES（'9521103','c06','必修'）;
INSERTINTOscVALUES（'9531101','c01',80,'选修'）;
INSERTINTOscVALUES（'9531101','c05',95,'必修'）;
INSERTINTOscVALUES（'9531102','c05',85,'必修'）;
3．完成如下查询
（1）查询计算机系没有选课的学生的姓名和年龄。
SELECTa.sname,a.sageFROMstudentaWHEREa.snoNOTIN（SELECTb.snoFROMscb）
（2）统计‘VB’课程的考试最高分、最低分和平均分。
SELECTMAX（sc.grade）AS最高分,MIN（sc.grade）AS最低分,AVG（sc.grade）AS平均分
FROMsc,coursec
WHEREo=o
ANDame='VB'
（3）统计‘数据库’课程的选课人数。
SELECTCOUNT（*）AS选课人数
FROMsc,coursec
WHEREo=o
ANDame='数据库基础'
（4）统计计算机系学生’vb’课程考试的最高分、最低分、平均分。
SELECTMAX（sc.grade）AS最高分,MIN（sc.grade）AS最低分,AVG（sc.grade）AS平均分
FROMsc,coursec,students
WHEREo=o
ANDsc.sno=s.sno
ANDame='VB'
ANDs.sdept='计算机系'
（5）统计每个系学生’vb’课程考试的最高分、最低分、平均分，列出系名和三个分数。
SELECTs.sdept,MAX（sc.grade）AS最高分,MIN（sc.grade）AS最低分,AVG（sc.grade）AS平均分
FROMsc,coursec,students
WHEREo=o
ANDsc.sno=s.sno
ANDame='VB'
GROUPBYs.sdept
（6）查询每个学生的修课总学分，并进行如下处理：
如果总学分高于10分，则显示‘好学生’；如果总学分在6到10分间，则显示‘一般学生’；如果总学分低于6分，则显示‘不好学生’。
SELECTtable1.tcreditAS总分,
CASEWHENtable1.tcredit>10THEN'好学生'
WHENtable1.tcredit<6THEN'不好学生'
ELSE'一般学生'ENDAS学生性质
FROM（SELECTSUM（c.credit）AStcredit
FROMsc,coursec,students
WHEREo=o
ANDsc.sno=s.sno
GROUPBYs.sno）table1
（7）统计每个系每个学生的修课门数和平均成绩，如果修课门数大于3并且平均成绩高于90的显示‘优秀’；如果修课门数大于3并且平均成绩在80～90，则显示‘较好’；对于修课门数小于等于3的并且平均成绩高于90的显示‘较好’；如果修课门数小于等于3并且平均成绩在80～90，则显示‘一般’；其他情况显示为‘应努力’。
列出系名、学号、原修课门数和平均成绩以及处理后的显示结果。
SELECTtable1.*,CASE
WHENtable1.`修课门数`>3ANDtable1.`平均成绩`>90THEN'优秀'
WHENtable1.`修课门数`>3ANDtable1.`平均成绩`>=80ANDtable1.`平均成绩`<=90THEN'较好'
WHENtable1.`修课门数`<=3ANDtable1.`平均成绩`>90THEN'较好'
WHENtable1.`修课门数`<=3ANDtable1.`平均成绩`>=80ANDtable1.`平均成绩`<=90THEN'一般'
ELSE'应努力'ENDAS学生性质
FROM（SELECTs.sdept,s.sno,COUNT（o）AS修课门数,AVG（sc.grade）AS平均成绩
FROMsc,coursec,students
WHEREo=o
ANDsc.sno=s.sno
GROUPBYs.sno）table1
（8）统计每个系每个学生的考试平均成绩，如果平均成绩高于90，则显示‘好’；如果平均成绩80~90，则显示‘较好’；如果平均成80，则显示‘一般’；否则显示‘不太好’，对空值则显示‘没有’。
SELECTtable1.*,CASE
WHENtable1.`平均成绩`>90THEN'好'
WHENtable1.`平均成绩`>=80ANDtable1.`平均成绩`<=90THEN'较好'
WHENtable1.`平均成绩`<80THEN'一般'
WHENtable1.`平均成绩`=''THEN'没有'
ELSE'不太好'ENDAS学生性质
FROM（SELECTs.sdept,s.sno,COUNT（o）AS修课门数,AVG（sc.grade）AS平均成绩
FROMsc,coursec,students
WHEREo=o
ANDsc.sno=s.sno
GROUPBYs.sno）table1
（9）查询每门课程的选课人数，如果选课人数超过6人，则显示‘人多’；如果选课人数4~6人，则显示‘一般’；如果选课人数少于4人，则显示‘较少’；如果此门课程无人选，则显示‘无人选’。
SELECTtable1.*,CASE
WHENtable1.`选课人数`>6THEN'人多'
WHENtable1.`选课人数`>=4ANDtable1.`选课人数`<=6THEN'一般'
WHENtable1.`选课人数`<4THEN'较少'
ELSE'无人选'ENDAS课程性质
FROM（SELECTo,ame,COUNT（sc.sno）AS选课人数
FROMsc,coursec
WHEREo=o
GROUPBYo）table1
（10）查询VB考试成绩最高的前两名学生的姓名和成绩。
SELECTs.sname,sc.grade
FROMsc,coursec,students
WHEREo=o
ANDsc.sno=s.sno
ANDame='VB'
ORDERBYsc.gradeDESC
LIMIT2
（11）查询计算机系学生考试成绩最低的两个成绩所对应的学生的姓名、课程名和成绩。
SELECTs.sname,ame,sc.grade
FROMsc,coursec,students
WHEREo=o
ANDsc.sno=s.sno
ANDs.sdept='计算机系'
ORDERBYsc.gradeASC
LIMIT2
（12）列出没有选课的学生的学号、姓名和所在系。
SELECTa.sno,a.sname,a.sdept
FROMstudenta
WHEREa.snoNOTIN（SELECTb.snoFROMscb）
（13）查询计算机系年龄大于总平均年龄的学生的姓名和年龄。
SELECTs.sname,s.sage
FROMstudents
WHEREs.sdept='计算机系'
ANDs.sage>（
SELECTAVG（a.sage）
FROMstudenta
）
（14）查询计算机系年龄大于计算机系平均年龄的学生的姓名和年龄。
SELECTs.sname,s.sage
FROMstudents
WHEREs.sdept='计算机系'
ANDs.sage>（
SELECTAVG（a.sage）
FROMstudenta
WHEREa.sdept='计算机系'
）
（15）查询计算机系考试成绩小于总平均分的学生的学号、姓名。
SELECTs.sname,s.sage
FROMstudents,scsc1
WHEREs.sdept='计算机系'
ANDsc1.sno=s.sno
ANDsc1.grade<（
SELECTAVG（sc2.grade）
FROMscsc2
）
GROUPBYs.sno
（16）查询计算机系男生‘VB’考试成绩小于‘VB’平均分的学生的学号、姓名。
SELECTs.sname,s.sage
FROMstudents,scsc1,coursec1
WHEREo=o
ANDsc1.sno=s.sno
ANDame='VB'
ANDs.ssex='男'
ANDs.sdept='计算机系'
ANDsc1.grade<（
SELECTAVG（sc2.grade）
FROMscsc2,coursec
WHEREo=o
ANDame='VB'
）
（17）查询选修了‘VB’的学生的选课门数以及平均成绩，要求只列出平均成绩大于70且选课门数超过2门的情况。
SELECTCOUNT（o）AS选课门数,AVG（sc.grade）AS平均成绩
FROMstudents,sc,coursec1
WHEREo=o
ANDsc.sno=s.sno
ANDame='VB'
GROUPBYs.sno
HAVINGAVG（sc.grade）>70
ANDCOUNT（o）>2
（18）查询选课门数超过学生的平均选课门数的学生的姓名。
SELECT*
FROM（SELECTs.sname,COUNT（o）AS选课人数
FROMstudents,sc
WHEREsc.sno=s.sno
GROUPBYs.sno）table1,
（SELECTAVG（table3.`选课人数`）AS平均选课人数
FROM（（SELECTCOUNT（o）AS选课人数
FROMsc
GROUPBYsc.sno
）table3））table2
WHEREtable1.`选课人数`>table2.`平均选课人数`
（19）将考试成绩最低的学生的最低修课成绩改为60。
SET@cno1=（SELECToFROMscWHEREsc.grade=（SELECTMIN（sc.grade）FROMsc））;
SET@sno1=（SELECTsc.snoFROMscWHEREsc.grade=（SELECTMIN（sc.grade）FROMsc））;
UPDATEscSETsc.grade=60
WHEREo=@cno1
ANDsc.sno=@sno1
（20）将数据库基础考试成绩最低的且成绩为不及格学生的数据库考试成绩改为60。
CREATEVIEWnewtable1
ASSELECTsc.sno,oFROMscWHEREsc.grade=
（SELECTMIN（sc.grade）FROMsc,coursecWHEREo=oANDame='数据库基础'ANDsc.grade<60）;
SET@cno1=（SELECTcnoFROMnewtable1）
SET@sno1=（SELECTsnoFROMnewtable1）
UPDATEscSETsc.grade=60
WHEREo=@cno1
ANDsc.sno=@sno1
（21）删除计算机系“计算机网络”课程的全部选课记录。
DELETEFROMsc
WHEREsc.snoIN（SELECTsc.sno,o
FROMsc,coursec,students
WHEREsc.sno=s.sno
ANDo=o
ANDs.sdept='计算机系'
ANDame='计算机网络'
）
ANDoIN（
SELECTsc.sno,o
FROMsc,coursec,students
WHEREsc.sno=s.sno
ANDo=o
ANDs.sdept='计算机系'
ANDame='计算机网络'
）
（22）删除vb考试成绩最低的两个学生的vb考试记录。
DELETE
FROMsc
WHEREsnoIN
（SELECTa.sno
FROMsca,courseb
WHEREo=o
ANDcname='VB'
orderbygradeASC
LIMIT2）
ANDcnoIN
（SELECTo
FROMsca,courseb
WHEREo=o
ANDcname='VB'
orderbygradeASC
LIMIT2）
（23）对数据库考试成绩进行如下修改：
如果成绩低于60分，则提高10％；如果成绩在60到80之间，则增加6％；如果成绩在80到95之间则提高4％，其他情况不提高。
updatesc
setgrade=grade*1.04
wheregrade>=80andgrade<95；
updatesc
setgrade=grade*1.06
wheregrade>=60andgrade<80；
updatesc
setgrade=grade*1.10
wheregrade<60
（24）对学分进行如下修改：
如果是第1到第3学期开始的课程，则学分增加1分；如果是第4到第6学期开设的课程，学分增加2分，其他学期开始的课程学分增加3分。
updatecourse
setcredit=credit+3
wheresemester>6;
updatecourse
setcredit=credit+2
wheresemesterbetween4and6;
updatecourse
setcredit=credit+1
wheresemesterbetween1and3
用子查询完成如下查询：
（25）查询男生年龄最大的学生的姓名和所在系。
selectsname,sdept
fromstudent
wheressex='男'andsagein（selectMAX（sage）fromstudent）
（26）查询选修了‘数据库基础’的学生的姓名、所在系。
selectsname,sdept
fromstudent
wheresnoin（selecta.sno
fromsca,courseb
whereo=oandame='数据库基础'）
（27）选修了第6学期开设的课程的学生的学号、姓名和所在系。
selectsno,sname,sdept
fromstudent
wheresnoin（selecta.sno
fromsca,courseb
whereo=oandb.semester=6）
（28）查询男生所修的课程的课程名。
SELECTame
FROMsc,coursec,students
WHEREo=o
ands.sno=sc.sno
ands.ssex='男'
（29）查询年龄最小的学生所选的课程名。
SELECTame
FROMsc,coursec,students
WHEREo=o
ands.sno=sc.sno
ands.sage=（
SELECTMIN（sage）
FROMstudent）
升级会员 