1、查询7号课程没有考试成绩的学生学号。
Selectsnofromscwherecno='7'andgradeisnull
2、查询7号课程成绩在90分以上或60分以下的学生学号。
Selectsnofromscwherecno='7'andgradenotbetween60and90
3、查询课程名以“数据”两个字开头的所有课程的课程号和课程名。
Selectcno,cnamefromcwherecnamelike'数据%'
4、查询每个学生所有课程的平均成绩，输出学生学号和平均成绩。
Selectsno,avg(grade)fromscgroupbysno
5、查询每门课程的选修人数，输出课程号和选修人数。
Selectcno,count(*)fromscgroupbycno
6、查询选修7号课程的学生的学号、姓名、性别。
Selects.sno,sname,ssexfroms,scwheres.sno=sc.snoandcno='7'
或：Selectsno,sname,ssexfromswheresnoin
(Selectsnofromscwherecno='7')
7、查询选修7号课程的学生的平均年龄。
Selectavg(sage)froms,scwheres.sno=sc.snoandcno='7'
或：Selectavg(sage)fromswheresnoin
8、查询有30名以上学生选修的课程号。
Selectcnofromscgroupbycnohavingcount(*)>30
9、查询至今没有考试不及格的学生学号。
Selectdistinctsnofromscwheresnonotin
(Selectsnofromscwheregrade<60)
或：Selectsnofromscgroupbysnohavingmin(grade)>=60
10、查询所有考试成绩的平均分相同的学生学号分组
二、
1、找出选修课程号为C2的学生学号与成绩。
Selectsno,gradefromscwherecno='C2'
2、找出选修课程号为C4的学生学号与姓名。
Selects.sno,snamefroms,scwheres.sno=sc.snoandcno='C4'
注意本题也可以用嵌套做
思考本题改为“找出选修课程号为C4的学生学号、姓名与成绩”后还能用嵌套做吗？
3、找出选修课程名为Maths的学生学号与姓名。
Selects.sno,snamefroms,sc,c
wheres.sno=sc.snoandc.cno=sc.cnoandcname='Maths'
4、找出选修课程号为C2或C4的学生学号。
Selectdistinctsnofromscwherecnoin('C2','C4')
或：Selectdistinctsnofromscwherecno='C2'orcno='C4'
5、找出选修课程号为C2和C4的学生学号。
Selectsnofromscwherecno='C2'andsnoin
(Selectsnofromscwherecno='C4')
注意本题也可以用连接做
思考：Selectdistinctsnofromscwherecno='C2'andcno='C4'正确吗？
6、找出不学C2课程的学生姓名和年龄。
Selectsname,sagefromswheresnonotin
(Selectsnofromscwherecno='C2')
或：Selectsname,sagefromswherenotexists
(Select*fromscwheresno=s.snoandcno='C2')
7、找出选修了数据库课程的所有学生姓名。(同3)
Selectsnamefroms,sc,c
wheres.sno=sc.snoandc.cno=sc.cnoandcname='数据库'
8、找出数据库课程不及格的女生姓名。
连接：Selectsnamefroms,sc,c
andgrade<60andssex='女'
嵌套：Selectsnamefromswheressex='女'andsnoin
(Selectsnofromscwheregrade<60andcnoin
(Selectcnofromcwherecname='数据库')
)
9、找出各门课程的平均成绩，输出课程名和平均成绩。
Selectcname,avg(grade)fromsc,c
wherec.cno=sc.cnogroupbyc.cno,cname
思考本题也可以用嵌套做吗？
10、找出各个学生的平均成绩，输出学生姓名和平均成绩。
Selectsname,avg(grade)froms,sc
wheres.sno=sc.snogroupbys.sno,sname
11、找出至少有30个学生选修的课程名。
Selectcnamefromcwherecnoin
(Selectcnofromscgroupbycnohavingcount(*)>=30)
12、找出选修了不少于3门课程的学生姓名。
Selectsnamefromswheresnoin
(Selectsnofromscgroupbysnohavingcount(*)>=3)
13、找出各门课程的成绩均不低于90分的学生姓名。
Selectsnamefroms,scwheres.sno=sc.sno
groupbys.sno,snamehavingmin(grade)>=90
方法二：
Selectsnamefromswheresnonotin
(Selectsnofromscwheregrade<90)
只要有一门不小于90分就会输出该学生学号
14、找出数据库课程成绩不低于该门课程平均分的学生姓名。
wheres.sno=sc.snoandsc.cno=c.cnoandcname='数据库'andgrade>
(Selectavg(grade)fromsc,c
wheresc.cno=c.cnoandcname='数据库'
15、找出各个系科男女学生的平均年龄和人数。
Selectsdept,ssex,avg(sage),count(*)fromsgroupbysdept,ssex
16、找出计算机系(JSJ)课程平均分最高的学生学号和姓名。
Selects.sno,snamefroms,scwheres.sno=sc.snoandsdept='JSJ'
groupbys.sno,sname
havingavg(grade)>=ALL
(Selectavg(grade)froms,sc
wheres.sno=sc.snoandsdept='JSJ'
groupbys.sno
17、(补充)查询每门课程的及格率。
本题可以分三步做：
第1步：得到每门课的选修人数
createviewv_all(cno,cnt)
asselectcno,count(*)fromscgroupbycno
第2步：得到每门课及格人数
createviewv_pass(cno,cnt_pass)
asselectcno,count(*)fromscwheregrade>=60groupbycno
第3步：每门课的及格人数/每门课选修人数
selectv_all.cno,cnt_pass*100/cntfromv_all,v_pass
wherev_all.cno=v_pass.cno
18、查询平均分不及格的学生的学号，姓名，平均分。
Selectsc.sno,sname,avg(grade)fromstudent,sc
wherestudent.sno=sc.sno
groupbysc.sno,sname
havingavg(grade)<60
19、查询平均分不及格的学生人数。
Selectcount(*)fromstudent
wheresnoin
(selectsnofromscgroupbysnohavingavg(grade)<60)
下面是一个典型的错误
Selectcount(*)fromscgroupbysnohavingavg(grade)<60
这是每个学生有几门不及格的数目
三、
1、查询工资在1000到3000元之间的男性业务员的姓名和办公室编号。
SelectYname,OnofromYWY
whereSalarybetween1000and3000andYsex='男'
2、查询各个办公室的业务员人数，输出办公室编号和对应的人数。
SelectOno,count(*)fromYWYgroupbyOno
3、查询每个客户在2002年5月购买的总金额，输出客户号和相应的总金额。
SelectKno,sum(Fmoney)fromFP
whereFdatebetween'2002.5.1'and'2002.5.31'
groupbyKno
4、查询2002年5月购买次数超过5次的所有客户号，且按客户号升序排序。
SelectKnofromFP
havingcount(*)>5
orderbyKnoASC
5、查询各办公室男性和女性业务员的平均工资。
SelectOno,Ysex,avg(Salary)fromYWYgroupbyOno,Ysex
6、查询2002年5月曾经在王海亮业务员手中购买过商品的客户号、
SelectKno,Kname,PhonefromKHwhereKnoin
(SelectKnofromFP
whereFdatebetween'2002.5.1'and'2002.5.31'andYnoin
(SelectYnofromYWYwhereYname='王海亮')
7、查询所有工资比1538号业务员高的业务员的编号、姓名和工资。
SelectYno,Yname,SalaryfromYWYwhereSalary>
(SelectSalaryfromYWYwhereYno='1538')
8、查询所有与1538号业务员在同一个办公室的其他业务员的编号和姓名。
SelectYno,YnamefromYWYwhereYno!='1538'andOnoin
(SelectOnofromYWYwhereYno='1538')
9、查询销售总金额最高的业务员的编号。
SelectYnofromFPgroupbyYnohavingsum(Fmoney)>=ALL
(Selectsum(Fmoney)fromFPgroupbyYno)
10、查询所有业务员的编号、姓名、工资以及工资比他高的其他业务员的平均工资。
利用自连接
SelectY1.Yno,Y1.Yname,Y1.Salary,avg(Y2.Salary)
fromYWYY1,YWYY2
whereY1.Salary groupbyY1.Yno 四、 1、找出每个班级的班级代码、学生人数、平均成绩。 SelectBJDM,count(*),avg(CJ)fromSCgroupbyBJDM 2、找出每个学生的班级代码、学生姓名、考试科目数、总成绩。 SelectBJDM,XSXM,count(*),sum(CJ)fromSC groupbyBJDM,BNXH,XSXM 3、输出一张表格，每位学生对应一条记录，包括字段： 班级代码、学生姓名、语文成绩、数学成绩、外语成绩。 SelectSC1.BJDM,SC1.XSXM,SC1.CJ,SC2.CJ,SC3.CJ fromSCSC1,SCSC2,SCSC3 whereSC1.BJDM=SC2.BJDMandSC1.BNXH=SC2.BNXHand SC2.BJDM=SC3.BJDMandSC2.BNXH=SC3.BNXHand SC1.KM='语文'andSC2.KM='数学'andSC3.KM='外语' 4、输出一张表格，有成绩低于60分的每位学生对应一条记录，包括字段： 班级代码、学生姓名、最低成绩。 SelectBJDM,XSXM,min(CJ)fromSC whereCJ<60groupbyBJDM,BNXH,XSXM 或：SelectBJDM,XSXM,min(CJ)fromSC havingmin(CJ)<60 5、输出一张表格，有成绩低于60分的每位学生对应一条记录，包括字段： 班级代码、学生姓名、最高成绩、平均成绩。 SelectBJDM,XSXM,max(CJ)fromSC 请思考下列做法是否正确： SelectBJDM,XSXM,max(CJ),avg(CJ)fromSC 6、输出一张表格，所有成绩都不低于60分的每位学生对应一条记录，包括字段： 班级代码、学生姓名、平均成绩。 SelectBJDM,XSXM,avg(CJ)fromSC havingmin(CJ)>=60 7、输出一张表格，每一位学生对应一条记录，包括字段： 班级代码、学生姓名、去掉一个最低分后的平均成绩。 SelectBJDM,XSXM,(sum(CJ)-min(CJ))/(count(*)-1)fromSC 8、输出一张表格，每门科目对应一条记录，包括字段： 科目、去掉一个最低分后的平均成绩。 SelectKM,(sum(CJ)-min(CJ))/(count(*)-1)fromSC groupbyKM 实验指导中“八SQL查询语句”的答案 1、查询年龄在19至21岁之间的女生的学号，姓名，年龄，按年龄从大到小排列。 Selectsno,sname,sagefromstudent wheresagebetween19and21andssex='女' orderbysagedesc 2、查询姓名中有“明”字的学生人数。 wheresnamelike"%明%" 3、查询1001课程没有成绩的学生的学号。 Selectsnofromscwherecno='1001'andgradeisnull 4、查询JSJ、SX、WL系的学生学号，姓名，结果按系及学号排列。 Selectsno,sname,sdeptfromstudent wheresdeptin('JSJ','SX','WL') orderbysdept,sno 5、计算每一门课的总分、平均分，最高分、最低分。 Selectcno,sum(grade),avg(grade),max(grade),min(grade) fromsc groupbycno 6、查询平均分大于90分的男学生学号及平均分。 连接： selectsc.sno,avg(grade)fromstudent,sc wherestudent.sno=sc.snoandssex=’男’ groupbysc.sno havingavg(grade)>90 嵌套： selectsno,avg(grade)fromsc wheresnoin(selectsnofromstudentwheressex='男') groupbysno 7、查询选修课程超过2门的学生姓名。 selectsnamefromstudent,sc havingcount(*)>2 本题也可以用嵌套做 8、查询JSJ系的学生选修的课程号。 Selectdistinctcnofromstudent,sc wherestudent.sno=sc.snoandsdept='JSJ' 9、查询选修1002课程的学生的学生姓名(用连接和嵌套2种方法) 连接：Selectsnamefromstudent,sc wherestudent.sno=sc.snoandcno='1002' 嵌套：Selectsnamefromstudentwheresnoin (selectsnofromscwherecno='1002') 10、查询学生姓名以及他选修课程的课程号及成绩。 Selectsname,cno,gradefromstudent,sc 11、查询选修“数据库原理”课且成绩80以上的学生姓名(用连接和嵌套2种方法) 连接：Selectsnamefromstudent,sc,course wherestudent.sno=sc.snoandsc.cno=course.cnoand cname='数据库原理'andgrade>80 (selectsnofromscwheregrade>80andcnoin (selectcnofromcoursewherecname='数据库原理') 14、查询没有选修1002课程的学生的学生姓名。 Selectsnamefromstudent wheresnonotin(selectsnofromscwherecno='1002') 或：selectsnamefromstudent wherenotexists (select*fromscwherecno='1002'andsno=student.sno) 思考本题也可以用一般的连接做吗？ 15、查询平均分最高的学生学号及平均分。 Selectsno,avg(grade) havingavg(grade)>=ALL(Selectavg(grade) 16、查询每门课程成绩都高于该门课程平均分的学生学号。 可以先计算每门课程平均分 createviewc_avg(cno,avg_grade) asselectcno,avg(grade)fromscgroupbycno 再查询 Selectdistinctsnofromsc wheresnonotin(Selectsnofromsc,c_avg wheresc.cno=c_avg.cnoandgrade =========================================== SELECTDISTINCTSno FROMSCSC1 WHERESC1.SnoNOTIN (SELECTSC2.Sno FROMSCSC2 WHERESC2.Grade<= (SELECTAVG(SC3.Grade) FROMSCSC3 WHERESC3.Cno=SC2.Cno 或： WHERENOTEXISTS (SELECT* WHERESC2.Sno=SC1.SnoANDSC2.Grade<= （3）检索至少选修LIU老师所授课程中一门课程的女学生姓名。SELECTSNAMEFROMSWHERESEX=‘F’ANDS#IN(SELECTS#FROMSCWHEREC#IN(SELECTC#FROMCWHERETEACHER=‘LIU’) NOTICE：有多种写法，比如联接查询写法：SELECTSNAMEFROMS,SC,CWHERESEX=‘F’ANDSC.S#=S.S#ANDSC.C#=C.C#ANDTEACHER='LIU'但上一种写法更好一些。 要从语义上分解：（1）选择课程的课程号与课程名，不存在不选这门课的同学。其中，“不选这门课的同学”可以表示为： SELECT* FROMS WHERES#NOTIN （SELECT* FROMSC WHERESC.C#=C.C#) 或者 WHERES.S#=C.S#AND SC.C#=C.C#) （电商）数据库原理及应用_模拟考核试题及参考答案 一、单选(每空1分,共10分) 1．在Access数据库中，数据保存在（）对象中。 A．窗体B．查询C．报表D．表 2．如果某一字段数据型为文本型，字段大小为8，该字段中最多可输入（）个汉字。 A．8B．4C．16D．32 3．文本型字段最多可以存放（）个字符。 A．250B．252C．254D．255 4．Access用户操作界面由（）部分组成。 A．4B．5C．3D．6 5．下列（）图标是Access中表对象的标志。 A．B．C．D． 6．在设计Access数据表时，“索引”属性有（）取值。 A．1B．2C．3D．4 7．Access中包含有（）种数据类型。 A．9B．10C．7D．8 8．在一个宏中要打开一个报表，应该使用的操作是（）。 A．OpenFormB．OpenReportC．OpenTableD．OpenQuery 9．可以通过Internet进行数据发布的对象是（）。 A．窗体B．报表C．查询D．数据访问页 10．模块窗口由（）个部分组成。 A．2B．3C．4D．5 二、填空(每空1分,共20分) 1．在人工管理和文件管理阶段，程序设计__依赖于___数据表示。 2．在文件系统中，存取数据的基本单位为___记录____，在数据库系统中，存取数据的基本单位为___数据项_____。 3．若实体A和B是多对多的联系，实体B和C是1对1的联系，则实体A和C是___多_____对___多_____的联系。 4．在一个关系中不允许出现重复的____元组____，也不允许出现具有相同名字的___属性_____。 5．数据库系统中的四类用户分别为____数据库管理员、数据库设计员、应用程序员、终端用户_____。 6．在存取数据库的数据的过程中，使用了两个数据缓冲区，分别为___系统_____缓冲区和____用户____缓冲区。 7．学生关系中的班级号属性与班级关系中的班级号主码属性相对应，则____班级号____为学生关系中的___外码___。 8．设一个关系A具有a1个属性和a2个元组，关系B具有b1个属性和b2个元组，则关系A′B具有___a1+b1____个属性和____a2′b2____个元组。 9．设一个学生关系为S(学生号，姓名)，课程关系为C(课程号，课程名)，选课关系为X(学生号，课程号，成绩)，求出所有选课的学生信息的运算表达式为_____学生号(X)______与____S____的自然连接。 10．在一个关系R中，若存在X→Y和X→Z，则存在_____X→(Y,Z)_______，称此为函数依赖的合并性规则。 三、填空(每空1分,共20分) 1．若一个关系的任何非主属性都不部分依赖依赖于任何候选码，则称该关系达到____第二____范式。 2．在SQL中，列级完整性约束分为__6__种情况，表级完整性约束分为__4__种情况。 3.在SQL中，每个视图中的列可以来自不同的___表___，它是在原有表的基础上____建立____的逻辑意义上的新关系。 4.在SQL的查询语句中，groupby选项实现____分组统计______功能，orderby选项实现对结果表的____排序_____功能。 5．对于较复杂的系统，概念设计阶段的主要任务是：首先根据系统的各个局部应用画出各自对应的____局部ER图______，然后再进行综合和整体设计，画出_____整体ER图_____。 6．机器实现阶段的目标是在计算机系统中得到一个满足______设计要求、功能完善、操作方便___的数据库应用系统。 7．Access的用户操作界面由标题栏、菜单栏、工作区、工具栏、状态栏等五个部分组成。 8．Access“表”结构设计窗口中上半部分的“表设计器”是由字段名称、数据类型、说明等三列组成。 9．Access中的窗体由页眉、主体和页脚等三个部分组成。 四、填空(每空1分,共20分) 1.设一个关系为R(A,B,C,D,E)，它的最小函数依赖集为FD={A→B,A→C,(C,D)→E}，则该关系的候选码为_____(A,D)___，候选码函数决定E是___伪传递___性。 2．设一个关系为R(A,B,C,D,E)，它的最小函数依赖集为FD={A→B,A→C,(A,D)→E}，该关系只满足___第一_____范式，若要规范化为高一级的范式，则将得到____2____个关系。 3．在实际的数据库管理系统中，对数据库的操作方式有_____命令交互、程序执行、窗口界面______等三种。 4．在SQL中，主码约束的关键字为____primarykey________，外码约束的关键字为______foreignkey______。 5．基本表属于全局模式中的表，它是____实表____，而视图则属于局部模式中的表，它是____虚表____。 6.在SQL新版的查询语句中，select选项实现投影运算，from选项实现____连接____运算，where选项实现____选择___运算。 7．数据字典是对系统工作流程中____数据____和____处理____的描述。 8．关系数据库系统中的全局模式由若干个基本表所组成，表与表之间的联系是通过定义的____主码____和____外码____实现的。 9．在旅店管理中，涉及到的基本表有四个，它们分别为客房表、住宿表、_____旅客登记表、消费卡表____。 10．在设置或撤消数据库密码的过程中，密码对于字母大小写是敏感的。 五、填空(每空1分,共10分) 1．Access的用户操作界面由标题栏、菜单栏、工作区、工具栏、状态栏等五个部分组成。 2．Access“表”结构设计窗口中上半部分的“表设计器”是由字段名称、数据类型和说明等三列组成。 3．Access中的窗体由页眉、主体、页脚等三个部分组成。 4．在Access中模块分为类模块和标准模块两种类型。 六、根据主教材第四章所给的商品库和教学库，或者按照下列所给的每条SQL查询语句写出相应的功能，或者按照下列所给的每种功能写出相应的SQL查询语句。（每小题4分，共20分） 在名称为商品库的数据库中包含有商品表1和商品表2，它们的定义分别为： 商品表1(商品代号char(8),分类名char(8),单价float,数量int) 商品表2(商品代号char(8),产地char(6),品牌char(6),) 在名称为教学库的数据库中包含有学生、课程和选课三个表，它们的定义分别为： 学生(学生号char(7),姓名char(6),性别char(2),出生日期datetime, 专业char(10),年级int) 课程(课程号char(4),课程名char(10),课程学分int 选课(学生号char(7),课程号char(4),成绩int) 1．selectdistinct产地 from商品表2 功能：从商品库中查询出所有商品的不同产地。 2．select* from学生 where学生号in(select学生号 from选课 groupby学生号havingcount(*)=1 功能：从教学库中查询出只选修了一门课程的全部学生。 3．select* groupby学生号havingcount(*)<=2 )ornotexists(select* where学生.学生号=选课.学生号 功能：从教学库中查询出最多选修了2门课程（含未选任何课程）的全部学生。 4．从商品库中查询出每类（即分类名相同）商品的最高单价。 select分类名,max(单价)as最高单价 from商品表1 groupby分类名 5．从教学库中查询出至少选修了姓名为@m1学生所选课程中一门课的全部学生。 selectdistinct学生.* from学生,选课 where学生.学生号=选课.学生号and课程号=any(select课程号 where学生.学生号=选课.学生号and姓名=@m1 1.Student(S#,Sname,Sage,Ssex)学生表 2.Course(C#,Cname,T#)课程表 3.SC(S#,C#,score)成绩表 4.Teacher(T#,Tname)教师表 5. 6.问题： 7.1、查询“001”课程比“002”课程成绩高的所有学生的学号； 8.selecta.S#from(selects#,scorefromSCwhereC#='001')a,(selects#,score 9.fromSCwhereC#='002')b 10.wherea.score>b.scoreanda.s#=b.s#; 11.2、查询平均成绩大于60分的同学的学号和平均成绩； 12.selectS#,avg(score) 13.fromsc 14.groupbyS#havingavg(score)>60; 15.3、查询所有同学的学号、姓名、选课数、总成绩； 16.selectStudent.S#,Student.Sname,count(SC.C#),sum(score) 17.fromStudentleftOuterjoinSConStudent.S#=SC.S# 18.groupbyStudent.S#,Sname 19.4、查询姓“李”的老师的个数； 20.selectcount(distinct(Tname)) 21.fromTeacher 22.whereTnamelike'李%'; 23.5、查询没学过“叶平”老师课的同学的学号、姓名； 24.selectStudent.S#,Student.Sname 25.fromStudent 26.whereS#notin(selectdistinct(SC.S#)fromSC,Course,TeacherwhereSC.C#=Course.C#andTeacher.T#=Course.T#andTeacher.Tname='叶平'); 27.6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名； 28.selectStudent.S#,Student.SnamefromStudent,SCwhereStudent.S#=SC.S#andSC.C#='001'andexists(Select*fromSCasSC_2whereSC_2.S#=SC.S#andSC_2.C#='002'); 29.7、查询学过“叶平”老师所教的所有课的同学的学号、姓名； 30.selectS#,Sname 31.fromStudent 32.whereS#in(selectS#fromSC,Course,TeacherwhereSC.C#=Course.C#andTeacher.T#=Course.T#andTeacher.Tname='叶平'groupbyS#havingcount(SC.C#)=(selectcount(C#)fromCourse,TeacherwhereTeacher.T#=Course.T#andTname='叶平')); 33.8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名； 34.SelectS#,Snamefrom(selectStudent.S#,Student.Sname,score,(selectscorefromSCSC_2whereSC_2.S#=Student.S#andSC_2.C#='002')score2 35.fromStudent,SCwhereStudent.S#=SC.S#andC#='001')S_2wherescore2 36.9、查询所有课程成绩小于60分的同学的学号、姓名； 37.selectS#,Sname 38.fromStudent 39.whereS#notin(selectStudent.S#fromStudent,SCwhereS.S#=SC.S#andscore>60); 40.10、查询没有学全所有课的同学的学号、姓名； 41.selectStudent.S#,Student.Sname 42.fromStudent,SC 43.whereStudent.S#=SC.S#groupbyStudent.S#,Student.Snamehavingcount(C#)<(selectcount(C#)fromCourse); 44.11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名； 45.selectS#,SnamefromStudent,SCwhereStudent.S#=SC.S#andC#inselectC#fromSCwhereS#='1001'; 46.12、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名； 47.selectdistinctSC.S#,Sname 48.fromStudent,SC 49.whereStudent.S#=SC.S#andC#in(selectC#fromSCwhereS#='001'); 50.13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩； 51.updateSCsetscore=(selectavg(SC_2.score) 52.fromSCSC_2 53.whereSC_2.C#=SC.C#)fromCourse,TeacherwhereCourse.C#=SC.C#andCourse.T#=Teacher.T#andTeacher.Tname='叶平'); 54.14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名； 55.selectS#fromSCwhereC#in(selectC#fromSCwhereS#='1002') 56.groupbyS#havingcount(*)=(selectcount(*)fromSCwhereS#='1002'); 57.15、删除学习“叶平”老师课的SC表记录； 58.DelectSC 59.fromcourse,Teacher 60.whereCourse.C#=SC.C#andCourse.T#=Teacher.T#andTname='叶平'; 61.16、向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“003”课程的同学学号、2、 62.号课的平均成绩； 63.InsertSCselectS#,'002',(Selectavg(score) 64.fromSCwhereC#='002')fromStudentwhereS#notin(SelectS#fromSCwhereC#='002'); 65.17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显示：学生ID,,数据库,企业管理,英语,有效课程数,有效平均分 66.SELECTS#as学生ID 67.,(SELECTscoreFROMSCWHERESC.S#=t.S#ANDC#='004')AS数据库 68.,(SELECTscoreFROMSCWHERESC.S#=t.S#ANDC#='001')AS企业管理 69.,(SELECTscoreFROMSCWHERESC.S#=t.S#ANDC#='006')AS英语 70.,COUNT(*)AS有效课程数,AVG(t.score)AS平均成绩 71.FROMSCASt 72.GROUPBYS# 73.ORDERBYavg(t.score) 74.18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分 75.SELECTL.C#As课程ID,L.scoreAS最高分,R.scoreAS最低分 76.FROMSCL,SCASR 77.WHEREL.C#=R.C#and 78.L.score=(SELECTMAX(IL.score) 79.FROMSCASIL,StudentASIM 80.WHEREL.C#=IL.C#andIM.S#=IL.S# 81.GROUPBYIL.C#) 82.AND 83.R.Score=(SELECTMIN(IR.score) 84.FROMSCASIR 85.WHERER.C#=IR.C# 86.GROUPBYIR.C# 87.); 88.19、按各科平均成绩从低到高和及格率的百分数从高到低顺序 89.SELECTt.C#AS课程号,max(course.Cname)AS课程名,isnull(AVG(score),0)AS平均成绩 90.,100*SUM(CASEWHENisnull(score,0)>=60THEN1ELSE0END)/COUNT(*)AS及格百分数 91.FROMSCT,Course 92.wheret.C#=course.C# 93.GROUPBYt.C# 94.ORDERBY100*SUM(CASEWHENisnull(score,0)>=60THEN1ELSE0END)/COUNT(*)DESC 95.20、查询如下课程平均成绩和及格率的百分数(用"1行"显示):企业管理（001），马克思（002），OO&UML（003），数据库（004） 96.SELECTSUM(CASEWHENC#='001'THENscoreELSE0END)/SUM(CASEC#WHEN'001'THEN1ELSE0END)AS企业管理平均分 97.,100*SUM(CASEWHENC#='001'ANDscore>=60THEN1ELSE0END)/SUM(CASEWHENC#='001'THEN1ELSE0END)AS企业管理及格百分数 98.,SUM(CASEWHENC#='002'THENscoreELSE0END)/SUM(CASEC#WHEN'002'THEN1ELSE0END)AS马克思平均分 99.,100*SUM(CASEWHENC#='002'ANDscore>=60THEN1ELSE0END)/SUM(CASEWHENC#='002'THEN1ELSE0END)AS马克思及格百分数 100.,SUM(CASEWHENC#='003'THENscoreELSE0END)/SUM(CASEC#WHEN'003'THEN1ELSE0END)ASUML平均分 101.,100*SUM(CASEWHENC#='003'ANDscore>=60THEN1ELSE0END)/SUM(CASEWHENC#='003'THEN1ELSE0END)ASUML及格百分数 102.,SUM(CASEWHENC#='004'THENscoreELSE0END)/SUM(CASEC#WHEN'004'THEN1ELSE0END)AS数据库平均分 103.,100*SUM(CASEWHENC#='004'ANDscore>=60THEN1ELSE0END)/SUM(CASEWHENC#='004'THEN1ELSE0END)AS数据库及格百分数 104.FROMSC 105.21、查询不同老师所教不同课程平均分从高到低显示 106.SELECTmax(Z.T#)AS教师ID,MAX(Z.Tname)AS教师姓名,C.C#AS课程ＩＤ,MAX(C.Cname)AS课程名称,AVG(Score)AS平均成绩 107.FROMSCAST,CourseASC,TeacherASZ 108.whereT.C#=C.C#andC.T#=Z.T# 109.GROUPBYC.C# 110.ORDERBYAVG(Score)DESC 111.22、查询如下课程成绩第3名到第6名的学生成绩单：企业管理（001），马克思（002），UML（003），数据库（004） 112.[学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩 113.SELECTDISTINCTtop3 114.SC.S#As学生学号, 115.Student.SnameAS学生姓名, 116.T1.scoreAS企业管理, 117.T2.scoreAS马克思, 118.T3.scoreASUML, 119.T4.scoreAS数据库, 120.ISNULL(T1.score,0)+ISNULL(T2.score,0)+ISNULL(T3.score,0)+ISNULL(T4.score,0)as总分 121.FROMStudent,SCLEFTJOINSCAST1 122.ONSC.S#=T1.S#ANDT1.C#='001' 123.LEFTJOINSCAST2 124.ONSC.S#=T2.S#ANDT2.C#='002' 125.LEFTJOINSCAST3 126.ONSC.S#=T3.S#ANDT3.C#='003' 127.LEFTJOINSCAST4 128.ONSC.S#=T4.S#ANDT4.C#='004' 129.WHEREstudent.S#=SC.S#and 130.ISNULL(T1.score,0)+ISNULL(T2.score,0)+ISNULL(T3.score,0)+ISNULL(T4.score,0) 131.NOTIN 132.(SELECT 133.DISTINCT 134.TOP15WITHTIES 135.ISNULL(T1.score,0)+ISNULL(T2.score,0)+ISNULL(T3.score,0)+ISNULL(T4.score,0) 136.FROMsc 137.LEFTJOINscAST1 138.ONsc.S#=T1.S#ANDT1.C#='k1' 139.LEFTJOINscAST2 140.ONsc.S#=T2.S#ANDT2.C#='k2' 141.LEFTJOINscAST3 142.ONsc.S#=T3.S#ANDT3.C#='k3' 143.LEFTJOINscAST4 144.ONsc.S#=T4.S#ANDT4.C#='k4' 145.ORDERBYISNULL(T1.score,0)+ISNULL(T2.score,0)+ISNULL(T3.score,0)+ISNULL(T4.score,0)DESC); 146. 147.23、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[<60] 148.SELECTSC.C#as课程ID,Cnameas课程名称 149.,SUM(CASEWHENscoreBETWEEN85AND100THEN1ELSE0END)AS[100-85] 150.,SUM(CASEWHENscoreBETWEEN70AND85THEN1ELSE0END)AS[85-70] 151.,SUM(CASEWHENscoreBETWEEN60AND70THEN1ELSE0END)AS[70-60] 152.,SUM(CASEWHENscore<60THEN1ELSE0END)AS[60-] 153.FROMSC,Course 154.whereSC.C#=Course.C# 155.GROUPBYSC.C#,Cname; 156. 157.24、查询学生平均成绩及其名次 158.SELECT1+(SELECTCOUNT(distinct平均成绩) 159.FROM(SELECTS#,AVG(score)AS平均成绩 160.FROMSC 161.GROUPBYS# 162.)AST1 163.WHERE平均成绩>T2.平均成绩)as名次, 164.S#as学生学号,平均成绩 165.FROM(SELECTS#,AVG(score)平均成绩 166.FROMSC 167.GROUPBYS# 168.)AST2 169.ORDERBY平均成绩desc; 170. 171.25、查询各科成绩前三名的记录:(不考虑成绩并列情况) 172.SELECTt1.S#as学生ID,t1.C#as课程ID,Scoreas分数 173.FROMSCt1 174.WHEREscoreIN(SELECTTOP3score 175.FROMSC 176.WHEREt1.C#=C# 177.ORDERBYscoreDESC 178.) 179.ORDERBYt1.C#; 180.26、查询每门课程被选修的学生数 181.selectc#,count(S#)fromscgroupbyC#; 182.27、查询出只选修了一门课程的全部学生的学号和姓名 183.selectSC.S#,Student.Sname,count(C#)AS选课数 184.fromSC,Student 185.whereSC.S#=Student.S#groupbySC.S#,Student.Snamehavingcount(C#)=1; 186.28、查询男生、女生人数 187.Selectcount(Ssex)as男生人数fromStudentgroupbySsexhavingSsex='男'; 188.Selectcount(Ssex)as女生人数fromStudentgroupbySsexhavingSsex='女'； 189.29、查询姓“张”的学生名单 190.SELECTSnameFROMStudentWHERESnamelike'张%'; 191.30、查询同名同性学生名单，并统计同名人数 192.selectSname,count(*)fromStudentgroupbySnamehavingcount(*)>1;; 193.31、1981年出生的学生名单(注：Student表中Sage列的类型是datetime) 194.selectSname,CONVERT(char(11),DATEPART(year,Sage))asage 195.fromstudent 196.whereCONVERT(char(11),DATEPART(year,Sage))='1981'; 197.32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列 198.SelectC#,Avg(score)fromSCgroupbyC#orderbyAvg(score),C#DESC; 199.33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩 200.selectSname,SC.S#,avg(score) 201.fromStudent,SC 202.whereStudent.S#=SC.S#groupbySC.S#,Snamehavingavg(score)>85; 203.34、查询课程名称为“数据库”，且分数低于60的学生姓名和分数 204.SelectSname,isnull(score,0) 205.fromStudent,SC,Course 206.whereSC.S#=Student.S#andSC.C#=Course.C#andCourse.Cname='数据库'andscore<60; 207.35、查询所有学生的选课情况； 208.SELECTSC.S#,SC.C#,Sname,Cname 209.FROMSC,Student,Course 210.whereSC.S#=Student.S#andSC.C#=Course.C#; 211.36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数； 212.SELECTdistinctstudent.S#,student.Sname,SC.C#,SC.score 213.FROMstudent,Sc 214.WHERESC.score>=70ANDSC.S#=student.S#; 215.37、查询不及格的课程，并按课程号从大到小排列 216.selectc#fromscwherescore<60orderbyC#; 217.38、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名； 218.selectSC.S#,Student.SnamefromSC,StudentwhereSC.S#=Student.S#andScore>80andC#='003'; 219.39、求选了课程的学生人数 220.selectcount(*)fromsc; 221.40、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩 222.selectStudent.Sname,score 223.fromStudent,SC,CourseC,Teacher 224.whereStudent.S#=SC.S#andSC.C#=C.C#andC.T#=Teacher.T#andTeacher.Tname='叶平'andSC.score=(selectmax(score)fromSCwhereC#=C.C#); 225.41、查询各个课程及相应的选修人数 226.selectcount(*)fromscgroupbyC#; 227.42、查询不同课程成绩相同的学生的学号、课程号、学生成绩 228.selectdistinctA.S#,B.scorefromSCA,SCBwhereA.Score=B.ScoreandA.C#<>B.C#; 229.43、查询每门功课成绩最好的前两名 230.SELECTt1.S#as学生ID,t1.C#as课程ID,Scoreas分数 231.FROMSCt1 232.WHEREscoreIN(SELECTTOP2score 233.FROMSC 234.WHEREt1.C#=C# 235.ORDERBYscoreDESC 236.) 237.ORDERBYt1.C#; 238.44、统计每门课程的学生选修人数（超过10人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，查询结果按人数降序排列，若人数相同，按课程号升序排列 239.selectC#as课程号,count(*)as人数 240.fromsc 241.groupbyC# 242.orderbycount(*)desc,c# 243.45、检索至少选修两门课程的学生学号 244.selectS# 245.fromsc 246.groupbys# 247.havingcount(*)>=2 248.46、查询全部学生都选修的课程的课程号和课程名 249.selectC#,Cname 250.fromCourse 251.whereC#in(selectc#fromscgroupbyc#) 252.47、查询没学过“叶平”老师讲授的任一门课程的学生姓名 253.selectSnamefromStudentwhereS#notin(selectS#fromCourse,Teacher,SCwhereCourse.T#=Teacher.T#andSC.C#=course.C#andTname='叶平'); 254.48、查询两门以上不及格课程的同学的学号及其平均成绩 255.selectS#,avg(isnull(score,0))fromSCwhereS#in(selectS#fromSCwherescore<60groupbyS#havingcount(*)>2)groupbyS#; 256.49、检索“004”课程分数小于60，按分数降序排列的同学学号 257.selectS#fromSCwhereC#='004'andscore<60orderbyscoredesc; 258.50、删除“002”同学的“001”课程的成绩 259.deletefromScwhereS#='001'andC#='001'; 260. 模拟考核试题参考答案 1.D2.B3.D4.B5.C6.C7.B8.B9.D10.B 1.依赖于2.记录、数据项 3.多、多4.元组、属性 5.数据库管理员、数据库设计员、应用程序员、终端用户（次序无先后） 6.系统、用户7.班级号、外码 8.a1+b1、a2′b29.学生号(X)、S（次序无先后） 10.X→(Y,Z) 1.第二2.6、4 3.表、建立4.分组统计、排序 5.局部ER图、整体ER图6.设计要求、功能完善、操作方便 7.标题栏、菜单栏、工作区（次序无先后） 8.字段名称、数据类型、说明（次序无先后） 9.页眉、主体（次序无先后） 1.(A,D)、伪传递2.第一、2 3.命令交互、程序执行、窗口界面（次序无先后） 4.primarykey、foreignkey 5.实表、虚表6.连接、选择 7.数据、处理（次序无先后）8.主码、外码（次序无先后） 9.旅客登记表、消费卡表（次序无先后）10.大小写 1.标题栏、菜单栏、工作区（次序无先后） 2.字段名称、数据类型（次序无先后） 3.页眉、主体、页脚（次序无先后） 4.类模块、标准模块（次序无先后） 1.从商品库中查询出所有商品的不同产地。 2.从教学库中查询出只选修了一门课程的全部学生。 3.从教学库中查询出最多选修了2门课程（含未选任何课程）的全部学生。