数据库课件：第三章习题课

试用SQL的查询语句表达下列查询：1)检索年龄小于17岁的女同学的学号和姓名2)检索男同学所学课程的课程号和课程名SELECTS#,SNAMEFROMSWHEREAGE＜17ANDSEX=‘F’;S(S#,SNAME,AGE,SEX)SC(S#,C#,SCORE)C(C#,CNAME,T#)T（T#，TNAME，TITLE）3.2对于教学数据库的四个基本表：

数据库系统习题课一第三章习题

参考答案：SELECTC.C#,CNAMEFROMS,SC,CWHERES.S#=SC.S#ANDSC.C#=C.C#ANDSEX=‘M’;3)检索男同学所学课程的任课老师的工号和姓名4)检索至少选修两门课的学生学号5)检索WANG同学不学的课程的课程号SELECTT.T#,TNAMEFROMS,SC,C,TWHERES.S#=SC.S#ANDSC.C#=C.C#ANDC.C#=T.T#ANDSEX=‘M’;SELECTC#FROMCWHEREC#NOTIN(SELECTC#FROMS,SCWHERES.S#=SC.S#ANDSNAME=‘WANG’);

数据库系统习题课一第三章习题

SELECTDISTINCTX.S#FROMSCASX，SCASYWHEREX.S#=Y.S#ANDX.C#!=Y.C#;SELECTS#FROMSCGROUPBYS#HAVINGCOUNT(*)>1;

数据库系统习题课一第三章习题

6)检索至少有学号为S2和S4的学生选修的课程的课程号SELECTDISTINCTX.C#

FROMSCASX，SCASYWHEREX.S#=‘S2’ANDY.S#=‘S4’ANDX.C#=Y.C#;7)检索全部学生都选修的课程的课程号与课程名

SELECTC#,CNAMEFROMCWHERENOTEXISTS(SELECT*FROMSWHERENOTEXISTS(SELECT*FROMSCWHEREC.C#=SC.C#ANDSC.S#=S.S#));

数据库系统习题课一第三章习题

7)检索全部学生都选修的课程的课程号与课程名

SELECTC.C#,CNAMEFROMSC,CWHERESC.C#=C.C#GROUPBYC.C#,CNAMEHAVINGCOUNT(*)=(SELECTCOUNT(S#)FROMS)另一解法：8)检索选修课程包含LIU老师所授全部课程的学生学号

数据库系统习题课一第三章习题

SELECTDISTINCTS#FROMSCASXWHERENOTEXISTS(SELECT*FROMC,TWHEREC.T#=T.T#ANDTNAME=‘LIU’ANDNOTEXISTS(SELECT*FROMSCASYWHEREY.S#=X.S#ANDY.C#=C.C#));1)统计有学生选修的课程门数2)求选修C4课程的女同学的平均年龄SELECTCOUNT(DISTINCTC#)FROMSC；SELECTAVG(AGE)FROMSWHERESEX=‘女’ANDS#IN(SELECTS#FROMSCWHEREC#=‘C4’);S(S#,SNAME,AGE,SEX)SC(S#,C#,SCORE)C(C#,CNAME,T#)T（T#，TNAME，TITLE）3.7试用SQL查询语句表达下列对3.2题的教学数据库中四个基本表S、SC、C、T的查询：

数据库系统习题课一第三章习题

参考答案：3)求LIU老师所授课程的每门课程的平均成绩4)统计每门课程的学生选修人数（超过10人的课程才统计）。要求显示课程号和人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。SELECTC.C#,AVG(SCORE)FROMSC,C,TWHERESC.C#=C.C#ANDC.T#=T.T#ANDTNAME=‘LIU’GROUPBYC.C#;

数据库系统习题课一第三章习题

SELECTC#,COUNT(S#)FROMSCGROUPBYC#HAVINGCOUNT(S#)>10ORDEYBY2DESC,1ASC;5)检索学号比WANG同学大，而年龄比他小的学生姓名6)在表SC中检索成绩为空值的学生学号和课程号。SELECTSNAMEFROMSWHERES#>ALL(SELECTS#FROMSWHERESNAME=‘WANG’)ANDAGE<ALL(SELECTAGEFROMSWHERESNAME=‘WANG’);

数据库系统习题课一第三章习题

SELECTS#,C#FROMSCWHERESCOREISNULL;

数据库系统习题课一第三章习题

7)检索姓名以L打头的所有学生的姓名和年龄SELECTSNAME,AGEFROMSWHERESNAMELIKE‘L%’;8)检索年龄大于女同学平均年龄的男同学姓名和年龄

SELECTSNAME,AGEFROMSWHERESEX=‘M’ANDAGE>(SELECTAVG(AGE)FROMSWHERESEX=‘F’);9)检索年龄大于所有女同学年龄的男同学姓名和年龄

SELECTSNAME,AGEFROMSWHERESEX=‘M’ANDAGE>(SELECTMAX(AGE)FROMSWHERESEX=‘F’);1)往关系C中插一个课程元组(‘C8’,’VC++’,’T6’)2)检索所授每门课程平均成绩均大于80分的教师姓名，并把检索到的值送往另一个已存在的表

FACULTY(TNAME)INSERTINTOCVALUES(‘C8’,’VC++’,’T6’)；

INSERTINTOFACULTYSELECT

DISTINCT

TNAME

FROM(SELECTTNAME,C.C#,AVG(SCORE)FROMT,C,SCWHERET.T#=C.T#ANDC.C#=SC.C#GROUPBYTNAME,C.C#）ASRESULE(TNAME,C#,AVG_SCORE)ASXWHERE80<=ALL(SELECTAVG_SCOREFROMRESULTASYWHEREY.TNAME=X.TNAME);3.12试用SQL更新语句表达下列对3.2题的教学数据库中关系S、SC、C、T的更新操作：

数据库系统习题课一第三章习题

参考答案：

数据库系统习题课一第三章习题

2)检索所授每门课程平均成绩均大于80分的教师姓名，并把检索到的值送往另一个已存在的表

FACULTY(TNAME)INSERTINTOFACULTYSELECTDISTINCTTNAMEFROMC,SC,TWHEREC.C#=SC.C#ANDC.T#=T.T#ANDT#NOTIN(SELECTT#FROMCWHEREC#IN(SELECTC#FROMSCGROUPBYC#HAVINGAVG(SCORE)<=80));另一解法：3)在SC中删除尚无成绩的选课元组4)把选修LIU老师课程的女同学选课元组全部删去。DELETEFROMSCWHERESCOREISNULL;

数据库系统习题课一第三章习题

DELETEFROMSCWHEREC#IN(SELECTC#FROMC,T

WHEREC.T#=T.T#

ANDTNAME=‘LIU’)ANDS#IN(SELECTS#FROMSWHERESEX=‘F’)；5)把MATHS课不及格的成绩全改为60分。UPDATESCSETSCORE=60WHERESCORE<60ANDC#IN(SELECTC#FROMCWHERECANME=‘MATHS’)；6)把低于所有课程总平均成绩的女同学成绩提高5%8)在表SC中，当某个成绩低于全部课程的平均成绩时，提高5%。UPDATESCSETSCORE=SCORE*1.05WHERES#IN(SELECTS#FROMSWHERESEX=‘F’)ANDSCORE<(SELECTAVG(SCORE)

FROMSC);

数据库系统习题课一第三章习题

UPDATESCSETSCORE=SCORE*1.05WHERESCORE<(SELECTAVG(SCORE)FROMSC);7)在表SC修改C4课程的成绩，若成绩小于等于70分时提高5%，若成绩大于70分时提高4%。

数据库系统习题课一第三章习题

UPDATESCSETSCORE=SCORE*1.04WHEREC#=‘C4’ANDSCORE>70;UPDATESCSETSCORE=SCORE*1.05WHEREC#=‘C4’ANDSCORE<=70;这两个UPDATE语句的顺序不能颠倒3.14

对3.13题中的关系建立一个有关女职工信息的视图EMP-WOMAN,属性包括（E#,ENAME,C#,CNAME,SALARY).然后对视图EMP-WOMA