强化-SQL语句强化训练(史上最全最经典-呕血推荐)-sql语句练习

2010/71.有4个关系模式如下：出版社（出版社编号，出版社名称）；图书（图书编号，书名，出版社编号，定价）；作者（作者编号，姓名）；著书（图书编号，作者编号，作者排序）注：作者排序=1表示第一作者，依此类推。用SQL语句，完成第36~39题。(1).检索所有定价超过20元的书名。答案：SELECT书名（1分）FROM图书（1分）WHERE定价20（2分）(2).统计每个出版社图书的平均定价。答案：SELECT出版社编号，AVG（定价）（2分）FROM图书（1分）GROUPBY出版社编号（1分）(3).将科学出版社的所有图书定价下调5%。答案：UPDATE图书SET定价=定价*0.95（1分）WHERE出版社编号IN（1分）（SELECT出版社编号FROM出版社（1分）WHERE出版社名称=科学）（1分）【说明】WHERE出版社名称LIKE科学也正确。(4).列出所有图书的书名、第一作者姓名和出版社名称。答案：SELECT书名，姓名，出版社名称（1分）FROM出版社A，图书B，作者C，著书D（1分）WHEREA.出版社编号=B.出版社编号ANDB.图书编号=D.图书编号（1分）ANDC.作者编号=D.作者编号AND作者排序=1。(1分)S(SNO,SNAME,AGE,SEX，SDEPT)SC(SNO,CNO,GRADE)C(CNO,CNAME,CDEPT，TNAME)1.试用SQL的查询语句表达下列查询：①检索LIU老师所授课程的课程号和课程名。②检索年龄大于23岁的男学生的学号和姓名。③检索至少选修LIU老师所授课程中一门课程的女学生姓名。④检索WANG同学不学的课程的课程号。⑤检索至少选修两门课程的学生学号。⑥检索全部学生都选修的课程的课程号与课程名。⑦检索选修课程包含LIU老师所授课程的学生学号。2．试用SQL查询语句表达下列对教学数据库中三个基本表S、SC、C的查询：①统计有学生选修的课程门数。②求选修C4课程的学生的平均年龄。③求LIU老师所授课程的每门课程的学生平均成绩。④统计每门课程的学生选修人数（超过10人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。⑤检索学号比WANG同学大，而年龄比他小的学生姓名。⑥检索姓名以WANG打头的所有学生的姓名和年龄。⑦在SC中检索成绩为空值的学生学号和课程号。⑧求年龄大于女同学平均年龄的男学生姓名和年龄。⑨求年龄大于所有女同学年龄的男学生姓名和年龄。3．试用SQL更新语句表达对教学数据库中三个基本表S、SC、C的各个更新操作：①往基本表S中插入一个学生元组（'S9'，'WU'，18）。②在基本表S中检索每一门课程成绩都大于等于80分的学生学号、姓名和性别，并把检索到的值送往另一个已存在的基本表STUDENT（SNO，SNAME，SEX）。③在基本表SC中删除尚无成绩的选课元组。④把WANG同学的学习选课和成绩全部删去。⑤把选修MATHS课不及格的成绩全改为空值。⑥把低于总平均成绩的女同学成绩提高5％。⑦在基本表SC中修改C4课程的成绩，若成绩小于等于75分时提高5％，若成绩大于75分时提高4％（用两个UPDATE语句实现）。4．设教学数据库的模式如下：S(S#,SNAME,AGE,SEX)SC(S#,C#,GRADE)C(C#,CNAME,TEACHER)试用多种方式定义下列完整性约束：(1)在关系S中插入学生年龄值应在16～25岁之间(2)在关系SC中插入元组时，其S#值和C#值必须分别在S和C中出现。(3)在关系SC中修改GRADE值时，必须仍在0～100之间。(4)在删除关系C中一个元组时，首先要把关系SC中具有同样C#的元组全部删去。(5)在关系S中把某个S#值修改为新值时，必须同时把关系SC中那些同样的S#值也修改为新值。1.解：（1）检索LIU老师所授课程的课程号和课程名。SELECTC#，CNAMEFROMCWHERETEACHER=‘LIU’（2）检索年龄大于23岁的男学生的学号和姓名。SELECTS#，SNAMEFROMSWHERE(AGE23)AND(SEX=‘M’)（3）检索至少选修LIU老师所授课程中一门课程的女学生姓名。SELECTSNAMEFROMSWHERESEX=‘F’ANDS#IN(SELECTS#FROMSCWHEREC#IN(SELECTC#FROMCWHERETEACHER=‘LIU’)（4）检索WANG同学不学的课程的课程号。SELECTC#FROMCWHEREC#NOTIN(SELECTC#FROMSCWHERES#IN(SELECTS#FROMSWHERESNAME='WANG'))（5）检索至少选修两门课程的学生学号。SELECTS#FROMSCGROUPBYS#HAVINGCOUNT(C#)＝2（6）检索全部学生都选修的课程的课程号与课程名。SELECTC#,CNAMEFROMCWHERENOTEXISTS(SELECT*FROMSWHERENOTEXISTS(SELECT*FROMSCWHERESC.S#=S.S#ANDSC.C#=C.C#))（7）检索选修课程包含LIU老师所授课的学生学号。SELECTS#FROMSWHERES#IN(SELECTS#FROMSCWHERES.S#=SC.S#ANDC#IN(SELECTC#FROMCWHERETEACHER='LIU'))（请讨论一下，本题是否可如此：SELECTDISTINGCTS#FROMSCWHEREEXISTS(SELECTC#FROMCWHERETEACHER='LIU')）2．答：①SELECTCOUNT(DISTINCTCNO)FROMSC;②SELECTAVG(AGE)FROMS,SCWHERES.SNO=SC.SNOANDCNO=’C4’;③SELECTSC.CNO,CNAME,AVG(GRADE)FROMSC,CWHERESC.CNO=C.CNOANDTNAME=’LIU’GROUPBYSC.CNO;④SELECTCNO,COUNT(SNO)FROMSCGROUPBYCNOHAVINGCOUNT(*)10ORDERBY2DESC,CNOASC;⑤SELECTX.SNAMEFROMSASX,SASYWHEREY.SNAME=’WANG’ANDX.SNOY.SNOANDX.AGEY.AGE;⑥SELECTSNAME,AGEFROMSWHERESNAMELIKE’WANG%’;⑦SELECTSNO,CNOFROMSCWHEREGRADEISNULL;⑧SELECTSNAME,AGEFROMSWHERESEX=’M’ANDAGE(SELECTAVG(AGE)FROMSWHERESEX=’F’);⑨SELECTSNAME,AGEFROMSWHERESEX=’M’ANDAGEALL(SELECTAGEFROMSWHERESEX=’F’);3．答案：①INSERTINTOS(SNO,SNAME,AGE)VALUES(‘S9’,‘WU’,18);②INSERTINTOSTUDENTSELECTSNO,SNAME,SEXFROMSWHERE80=ALL(SELECTGRADEFROMSCWHERESNO=S.SNO);③DELETEFROMSCWHEREGRADEISNULL;④DELETEFROMSCWHERESNOIN(SELECTSNOFROMSWHERESNAME=’WANG’);⑤UPDATESCSETGRADE=NULLWHEREGRADE60ANDCNOIN(SELECTCNOFROMCWHERECNAME=’MATHS’);⑥UPDATESCSETGRADE=GRADE*1.05WHERESNOIN(SELECTSNOFROMSWHERESEX=’F’)ANDGRADE(SELECTAVG(GRADE)FROMSC);⑦UPDATESCSETGRADE=GRADE*1.04WHERECNO=’C4’ANDGRADE75;UPDATESCSETGRADE=GRADE*1.05WHERECNO=’C4’ANDGRADE=75;注：这两个语句顺序不能颠倒。4．答：(1)定义S时采用检查子句：CREATTABLES(S#CHAR(4),SNAMEchar(10)NOTNULL,AGESMALLINT,primarykey(S#)CHECK(AGE=16andAGE=25))(2)采用外键子句约束CREATTABLESC(S#CHAR(4),C#CHAR(4),GRADESMALLINT,FOREIGNkey(S#)REFERENCES(S#)FOREIGNkey(C#)REFERENCEC(C#)(3)采用元组检查CREATTABLESC(S#CHAR(4),C#CHAR(4),GRADESMALLINT,FOREIGNkey(S#)REFERENCES(S#)FOREIGNkey(C#)REFERENCEC(C#)CHECK(GRADE=0andAGE=100))(4)采用外键约束CREATTABLESC(S#CHAR(4),C#CHAR(4),GRADESMALLINT,FOREIGNkey(S#)REFERENCES(S#),FOREIGNkey(C#)REFERENCEC(C#))注，在ONDELETE短语缺省的时候表示RESTRICT方式。(5)采用外键约束CREATTABLESC(S#CHAR(4),C#CHAR(4),GRADESMALLINT,FOREIGNkey(S#)REFERENCES(S#)ONUPDATECASCADE,FOREIGNkey(C#)REFERENCEC(C#))2009/07有一个教学数据库，包括三个关系模式：(1)学生S(S#，SNAME，AGE，SEX)其属性的含义依次为学号、姓名、年龄、性别(2)学习SC(S#，C#，GRADE)其属性的含义依次为学号、课程号、成绩(3)课程C(C#，CNAME，TEACHER)学生关系其属性的含义依次为课程号、课程名、任课教师根据以上3个关系模式用SQL语句完成36～39题。36.检索选修课程包含LIU老师所授课的学生学号。37.把低于总平均成绩的女同学成绩提高5％。38．检索选修3门以上（含3门）课程学生的学号和姓名。39．检索“王兰”同学不学的课程的课程号。1.检索选修课程包含LIU老师所授课的学生学号.SELECTS#FROMSC,CWHERESC.C#=C.C#ANDTEACHER='LIU';补充练习—续二2.把低于总平均成绩的女同学成绩提高5%.UPDATESCSETGRADE=GRADE*1.05WHEREGRADE=3);补充练习—续四4.检索王兰同学不学的课程的课程号.SELECTDISTINCTC#FROMSCWHEREC#NOTIN(SELECTC#FROMSC,SWHERESC.S#=S.S#ANDSNAME='王兰');1．以下各小题题基于教学数据库的三个基本表学生S(S#,SNAME,AGE,SEX)学习SC(S#,C#,GRADE)课程C(C#,CNAME,TEACHER)1.试用元组表达式和关系代数表达式表示下列查询语句：检索年龄小于23岁的男学生的学号和姓名。关系代数表达式:πS#,SNAME(σAGE＞'23'∧SEX='男'(S))元组表达式:{t|(u)(S(u)∧u[3]＞'23'∧u[4]='男'∧t[1]=u[1]∧t[2]=u[2])}2.试用SQL的查询语句表达下列查询:检索王兰同学不学的课程的课程号。SELECTC#FROMCWHEREC#NOTIN(SELECTC#FROMSCWHERES#IN(SELECTS#FROMSWHERESNAME='王兰'))3.试用SQL的查询语句表达下列查询:检索全部学生都选修的课程的课程号与课程名。SELECTC#,CNAMEFROMCWHERENOTEXISTS(SELECT*FROMSW