SQL查询更新语句课堂练习和习题.docx

SQL查询更新语句课堂练习和习题.docx

《SQL查询更新语句课堂练习和习题.docx》由会员分享，可在线阅读，更多相关《SQL查询更新语句课堂练习和习题.docx（12页珍藏版）》请在冰豆网上搜索。

SQL查询更新语句课堂练习和习题

SQL查询更新语句-课堂练习和习题

SQL查询语句课堂练习和习题

一、试用SQL查询语句表达下列对教学数据库中三个基本表S、SC、C的查询：

S（sno,sname,SAGE,SSEX）各字段表示学号，姓名，年龄，性别

Sc（sno,cno,grade）各字段表示学号，课程号，成绩

C（cno,cname,TEACHER）各字段表示课程号，课程名和教师名

其中SAGE，grade是数值型，其他均为字符型。

要求用SQL查询语句实现如下处理：

1．统计有学生选修的课程门数。

2．求选修C4课程的学生的平均年龄。

3．求LIU老师所授课程的每门课程的学生平均成绩。

4．统计每门课程的学生选修人数（超过2人的课程才统计）。

要求输出课程号和选修人数，

查询结果按人数降序排列，若人数相同，按课程

询结果按人数降序排列，若人数相同，按课程号升序排列。

SELECTDISTINCTCno,COUNT（Sno）

FROMSC

GROUPBYCno

HAVINGCOUNT（Sno）>2

ORDERBY2DESC,CnoASC

或：

SELECTDISTINCTCno,COUNT（Sno）as人数

FROMSC

GROUPBYCno

HAVINGCOUNT（Sno）>2

ORDERBY人数DESC,CnoASC

5．检索学号比WANG同学大，而年龄比他小的学生姓名。

SELECTX.SNAME

FROMSASX,SASY

WHEREY.SNAME='WANG'ANDX.Sno>Y.SnoANDX.SAGE

或：

SELECTSNAME

froms

wheresno>（selectsnofromswhereSNAME='WANG'）

andSAGE<（selectsAGEfromswhereSNAME='WANG'）

6．检索姓名以WANG打头的所有学生的姓名和年龄。

　　　SELECTSNAME,SAGE

　　　FROMS

　　　WHERESNAMELIKE'WANG%'

7．在SC中检索成绩为空值的学生学号和课程号。

　　　SELECTSno,CnoFROMSCWHEREGRADEISNULL

8．求年龄大于女同学平均年龄的男学生姓名和年龄。

SELECTSNAME,SAGE

FROMS

WHERESSEX='男'

ANDSAGE>（SELECTAVG（SAGE）FROMSWHERESSEX='女'）

9．求年龄大于所有女同学年龄的男学生姓名和年龄。

SELECTSNAME,SAGE

FROMSASX

WHEREX.SSEX='男'ANDX.SAGE>ALL（SELECTSAGEFROMSASYWHEREY.SSEX='女'）

二、试用SQL更新语句表达对教学数据库中三个基本表S、SC、C的各个更新操作：

要求用SQL更新语句实现如下处理：

1．往基本表S中插入一个学生元组（‘S9’，‘WU’，18）。

2．在基本表S中检索每一门课程成绩都大于等于80分的学生学号、姓名和性别，并把

检索到的值送往另一个已存在的基本表S1（Sno，SNAME，SSEX）。

3．在基本表SC中删除尚无成绩的选课元组。

4．把WANG同学的学习选课和成绩全部删去。

5．把选修数据库原理课不及格的成绩全改为空值。

6．把低于总平均成绩的女同学成绩提高5%。

7．在基本表SC中修改4号课程的成绩，若成绩小于等于75分时提高5%，若成绩大于75分时提高4%（用两个UPDATE语句实现）。

参考答案：

1．往基本表S中插入一个学生元组（‘S9’，‘WU’，18）。

　　　INSERTINTOS（Sno,SNAME,SAGE）VALUES（'59','WU',18）

2．在基本表S中检索每一门课程成绩都大于等于80分的学生学号、姓名和性别，并把检索到的值送往另一个已存在的基本表S1（Sno，SANME，SSEX）。

　　　selectSno,SNAME,SSEXintos1fromstudent

deletefroms1

INSERTINTOS1（Sno,SNAME,SSEX）

SELECTSno,SNAME,SSEX

FROMSWHERENOTEXISTS（SELECT*FROMSCWHERE

GRADE<80ANDS.Sno=SC.Sno）

select*froms1

考虑：

以上会有什么问题？

INSERTINTOS1（Sno,SNAME,SSEX）

SELECTSno,SNAME,SSEX

FROMSWHERENOTEXISTS（SELECT*FROMSCWHERE

GRADE<80ANDS.Sno=SC.SnoorS.Sno=SC.Snoandgradeisnull）

andsnoin（selectsnofromsc）

3．在基本表SC中删除尚无成绩的选课元组。

　　　DELETEFROMSCWHEREGRADEISNULL

4．把WANG同学的学习选课和成绩全部删去。

DELETEFROMSCWHERESnoIN（SELECTSno

FROMS

WHERESNAME='WANG'）

5．把选修数据库原理课不及格的成绩全改为空值。

UPDATESCSETGRADE=NULL

WHEREGRADE<60ANDCno

IN（SELECTCnoFROMC

WHERECNAME='数据库原理'）

6．把低于总平均成绩的女同学成绩提高5%。

UPDATESC

SETGRADE=GRADE*1.05

WHEREGRADE<（SELECTAVG（GRADE）FROMSC）

ANDSnoIN（SELECTSnoFROMSWHERESSEX='女'）

7．在基本表SC中修改4号课程的成绩，若成绩小于等于75分时提高5%，若成绩大于75分时提高4%（用两个UPDATE语句实现）。

UPDATESC

SETGRADE=GRADE*1.05

WHERECno='4'ANDGRADE<=75

UPDATESC

SETGRADE=GRADE*1.04

WHERECno='4'ANDGRADE>75

三、问题描述：

为管理岗位业务培训信息，建立3个表:

S （Sno,SN,SD,SA）   Sno,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄

C （Cno,CN ）        Cno,CN       分别代表课程编号、课程名称

SC （ Sno,Cno,G ）    Sno,Cno,G     分别代表学号、所选修的课程编号、学习成绩

要求实现如下5个处理：

  1. 使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名

  2. 使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位

  3. 使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位

  4. 使用标准SQL嵌套语句查询只选修了一门课程的学员姓名和所属单位

  5. 查询选修了课程的学员人数

6. 查询选修课程超过5门的学员学号和所属单位

参考答案：

 1．使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名

SELECT SN,SD FROM S

WHERE [Sno] IN（

    SELECT [Sno] FROM C,SC

    WHERE C.[Cno]=SC.[Cno]

        AND CN=N'税收基础'）

2．使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位

SELECT S.SN,S.SD FROM S,SC

WHERE S.[Sno]=SC.[Sno]

AND SC.[Cno]='C2'

3．使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位

SELECT SN,SD FROM S

WHERE [Sno] NOT IN（

    SELECT [Sno] FROM SC 

    WHERE [Cno]='C5'）

4．使用标准SQL嵌套语句查询只选修了一门课程的学员姓名和所属单位

SELECTSN,SDFROMS

WHERE[Sno]IN（

SELECT[Sno]FROMSCinnerJOINCONSC.[Cno]=C.[Cno]

GROUPBY[Sno]

HAVINGCOUNT（*）=1）

5．查询选修了课程的学员人数

SELECT 学员人数=COUNT（DISTINCT [Sno]） FROM SC

6．查询选修课程超过5门的学员学号和所属单位

SELECT SN,SD FROM S

WHERE [Sno] IN（

    SELECT [Sno] FROM SC 

    GROUP BY [Sno]

    HAVING COUNT（DISTINCT [Cno]）>5）

四、问题描述：

已知关系模式：

S （SNO,SNAME）                      学生关系。

SNO 为学号，SNAME 为姓名

C （CNO,CNAME,TEACHER）  课程关系。

CNO 为课程号，CNAME 为课程名，TEACHER 为任课教师

SC（SNO,CNO,GRADE）       选课关系。

GRADE 为成绩

要求实现如下5个处理：

  1． 找出没有选修过“李明”老师讲授课程的所有学生姓名

  2． 列出有二门以上（含两门）不及格课程的学生姓名及其平均成绩

  3． 列出既学过“1”号课程，又学过“2”号课程的所有学生姓名

  4． 列出“1”号课成绩比“04010002”号同学该门课成绩高的所有学生的学号

5． 列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩

参考答案：

1．找出没有选修过“李明”老师讲授课程的所有学生姓名

selectsnamefromswherenotexists（select*fromc,scwhereo=oANDc.teacher=N'李明'ANDs.sno=sc.sno）

参考：

selectsno,snamefroms

wheresnonotin

（selectsnofromsc,cwhereo=o

ANDc.teacher=N'liu'）

2．列出有二门以上（含两门）不及格课程的学生姓名及其平均成绩

SELECTS.SNO,SNAME,AVG_GRADE=AVG（SC.GRADE）

FROMS,SC

WHEREGRADE<60ands.sno=sc.sno

GROUPBYs.SNO,s.sname

HAVINGCOUNT（DISTINCTCNO）>=2

或

SELECTS.SNO,S.SNAME,AVG_GRADE=AVG（SC.GRADE）

FROMS,SC,（

SELECTSNO

FROMSC

WHEREGRADE<60

GROUPBYSNO

HAVINGCOUNT（DISTINCTCNO）>=2

）AWHERES.SNO=A.SNOANDSC.SNO=A.SNO

GROUPBYS.SNO,S.SNAME

3．列出既学过“1”号课程，又学过“2”号课程的所有学生姓名

SELECTS.SNO,S.SNAME

FROMS,sc

whereS.SNO=SC.SNOandcno='1'ands.snoin

（selectS.snofromS,sc

whereS.SNO=SC.SNOandcno='2'）

或

SELECTS.SNO,S.SNAME

FROMS,（

SELECTSC.SNO

FROMSC,C

WHERESC.CNO=C.CNO

ANDC.cnoIN（'1','2'）

GROUPBYSNO

HAVINGCOUNT（DISTINCTc.CNO）=2

）SCWHERES.SNO=SC.SNO

4。

列出“1”号课成绩比“04010002”号同学该门课成绩高的所有学生的学号

SELECTS.SNO,S.SNAME

FROMS,SC

WHERESC.CNO='1'andSC.sNO=S.sNO

ANDgrade>（selectgradefroms,sc

wheres.SNO='04010002'andSC.CNO='1'andSC.sNO=S.sNO）

5。

列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩

SELECTSC1.SNO,[1号课成绩]=SC1.GRADE,[2号课成绩]=SC2.GRADE

FROMSCSC1,SCSC2

WHERESC1.CNO='1'

ANDSC2.CNO='2'

ANDSC1.SNO=SC2.SNO

ANDSC1.GRADE>SC2.GRADE

四、通配符理解题（请说出下列通配符的含义）

1．LIKE'Mc%'将搜索以字母Mc开头的所有字符串（如McBadden）。

2．LIKE'%inger'将搜索以字母inger结尾的所有字符串（如Ringer、Stringer）。

3．LIKE'%en%'将搜索在任何位置包含字母en的所有字符串（如Bennet、Green、McBadden）。

4．LIKE'_heryl'将搜索以字母heryl结尾的所有六个字母的名称（如Cheryl、Sheryl）。

5．LIKE'[M-Z]inger'将搜索以字符串inger结尾、以从M到Z的任何单个字母开头的所有名称（如Ringer）。

6．LIKE'M[^c]%'将搜索以字母M开头，并且第二个字母不是c的所有名称（如MacFeather）。

7．LIKE'5[%]'--5%

8．LIKE'5%'--5后跟0个或更多字符的字符串

9．LIKE'[_]n'--_n

10．LIKE'_n'--an,in,on（andsoon）

11．LIKE'[a-cdf]'--a,b,c,d,orf

12．LIKE'[-acdf]'---,a,c,d,orf

13．LIKE'[[]'--[

14．LIKE']'--]