SQL课后题.docx
《SQL课后题.docx》由会员分享，可在线阅读，更多相关《SQL课后题.docx（13页珍藏版）》请在冰豆网上搜索。
SQL课后题
学生选课教师关系表
1）打开“SQLserverManagementStudio”，单击在“新建查询”按钮；在查询窗口中，输入如下命令。
createtableStudent（
StudentNoint,
snamenvarchar（20）,
ageint,
sexBITNOTNULLDEFAULT1）
INSERTINTOStudentVALUES
（990015,'王芳',21,'0'）,
（990016,'王柳',20,'1'）,
（990017,'张笑',19,'0'）,
（990018,'李明',20,'1'）,
（990019,'学友',21,'1'）,
（990020,'马晓',20,'0'）
createtableCourse（
CourseNoint,
coursenamenvarchar（20）,
TeacherNoint）
INSERTINTOCourseVALUES
（1234,'高数',15）,
（1122,'大物',16）,
（1017,'英语',17）,
（1019,'地概',18）
createtableTeacher（
TeacherNoint,
tnamenvarchar（20）,
titlenvarchar（20））
INSERTINTOTeacherVALUES
（15,'娜娜','高数'）,
（16,'王二','大物'）,
（17,'小刚','英语'）,
（18,'老大','地概'）
createtableSeleCourse（
StudentNoint,
CourseNoint,
scoreint）
INSERTINTOSeleCourseVALUES
（990015,1234,88）,
（990016,1122,90）,
（990016,1234,90）,
（990017,1122,79）,
（990018,1017,76）,
（990019,1019,83）,
（990019,1234,null）,
（990020,1019,88）
2）在“SQL编辑器’工具栏中，单击”执行“按钮，表创建成功后，在结果窗口会出现“命令已成功完成。
”的信息提示；向表中读入数据，运行成功后，出现“x行受影响”的信息提示；
3）在窗口中输入以下SQL查询命令并执行：
3.2题：
1.检索年龄小于21岁的女学生的学号和姓名
SELECTStudentNo,sname
FROMStudent
WHEREAge<21andsex='0'
2.检索男学生所学课程的课程号和课程名
SELECTCourse.CourseNo,Course.coursename
FROMStudent,SeleCourse,Course
WHEREStudent.StudentNo=SeleCourse.StudentNoandSeleCourse.CourseNo=Course.CourseNoandsex='1'
3.检索男学生所学课程的任课老师的工号和姓名
SELECTTeacher.TeacherNo,Teacher.tname
FROMTeacher,Student,SeleCourse,Course
WHEREStudent.StudentNo=SeleCourse.StudentNoandSeleCourse.CourseNo=Course.CourseNoandCourse.TeacherNo=Teacher.TeacherNo
andsex='1'
4.检索至少选修两门课程的学生学号
SELECTDISTINCTX.StudentNo/*至少选修两门课程的学生学号*/
FROMSeleCourseASX,SeleCourseASY
WHEREX.StudentNo=Y.StudentNoandX.CourseNo!
=Y.CourseNo
5.检索至少有学号为S2和S4的学生选修课程的课程号
SELECTDISTINCTX.CourseNo/*至少有学号为S2和S4的学生选修课程的课程号*/
FROMSeleCourseASX,SeleCourseASY
WHEREX.StudentNo='990016'andY.StudentNo='990017'andX.CourseNo=Y.CourseNo
6.检索王同学不学的课程的课程号
SELECTCourseNo/*王同学不学的课程的课程号*/
FROMCourse
WHERENOTEXISTS（SELECT*FROMStudent,SeleCourseWHEREStudent.StudentNo=SeleCourse.StudentNoandSeleCourse.CourseNo=
Course.CourseNoandStudent.sname='王琳'）
7.检索全部学生都选修的课程的课程号课程名
selectCourseNo,coursename/*全部学生都选修的的课程的课程号与课程名*/
fromCourse
wherenotexists（（selectStudentNofromStudent）
except
（selectStudentNofromSeleCoursewhereCourseNo=Course.CourseNo））
8.检索选修课程包含LIU老师所授全部课程的学生学号
SELECTDISTINCTStudentNo/*选修课程包含LIU老师所授的全部课程的学生学号*/
FROMSeleCourseasx
WHERENOTEXISTS（（selectCourseNo
fromCourse,Teacher
whereCourse.TeacherNo=Teacher.TeacherNoandtname='娜娜'）
except
（selectCourseNofromSeleCourseasywherex.StudentNo=y.StudentNo））
3.7题：
1.统计有学生选修的课程门数
SELECTCOUNT（CourseNo）/*有学生选修的课程门数*/
FROMSeleCourse
2.求选修1234课程的女学生的平均年龄
SELECTAVG（Age）/*选修1234课程的女生的平均年龄*/
FROMStudent,SeleCourse
WHERESeleCourse.StudentNo=Student.StudentNoANDCourseNo='1234'ANDsex='0'
3.求娜娜老师所授课程的每门课程的平均成绩
SELECTCourse.CourseNo,AVG（score）/*娜娜老师所授每门课程的平均成绩*/
FROMCourse,SeleCourse,Teacher
WHERESeleCourse.CourseNo=Course.CourseNoANDCourse.TeacherNo=Teacher.TeacherNoandtname='娜娜'
GROUPBYCourse.CourseNo
4.统计每门课程的选修人数（超过1人才统计）。
要求显示课程号和人数，查询按人数降序排列，若人数相同，按课程号升序排列。
SELECTCourseNo,count（StudentNo）/*选修各门课程的学生人数（人数大于），显示课程号和人数，按人数降序排列，人数相同时按课程号升序*/
FROMSeleCourse
GROUPBYCourseNohavingCOUNT（*）>1
orderby2DESC,1
5.检索学号比王琳同学大，年龄比她小的学生姓名
SELECTsname/*学号比王琳同学大，年龄比她小的学生姓名*/
FROMStudent
WHEREStudentNo>ALL（SELECTStudentNofromStudentwheresname='王琳'）and
age6.在表SC中检索成绩为空值的学生学号和课程号
SELECTStudentNo,CourseNo/*成绩为空值的学生学号和课程号*/
FROMSeleCourse
WHEREscoreisnull
7.检索姓名以王开头的学生姓名和年龄
SELECTsname,age/*姓名以王开头的学生姓名和年龄*/
FROMStudent
WHEREsnamelike'王%'
8.求年龄大于等于女同学平均年龄的男生姓名和年龄
SELECTsname,age/*年龄大于等于所有女同学平均年龄的男生姓名和年龄*/
FROMStudent
WHEREsex='1'andage>=（selectavg（age）fromStudentwheresex='0'）
9.求年龄大于等于所有女同学年龄的男生姓名和年龄
SELECTsname,age/*年龄大于等于所有女同学年龄的男生姓名和年龄*/
FROMStudent
WHEREsex='1'andage>=all（selectagefromStudentwheresex='0'）
3.12题：
1.往关系Course中插一个课程元组（0008,'VC++',06）
insertintoCourse
values（0008,'VC++',06）
2.检索所授每门课程的平均成绩均大于80分的教师姓名，并把检索到的值送往另一个已存在表COMP中
createtableFACULTY
（TNAMEchar（8）notnull）
insertintoFACULTY（TNAME）
selecttname
fromTeacher
whereTeacherNoin
（selectTeacherNofromCoursewhereCourseNoin
（selectCourseNofromSeleCoursegroupbyCourseNo
havingAVG（SCORE）>80））
3.在SC中删除无成绩的选课元组
deletefromSeleCourse
wherescoreisnull
4.把选修LIU老师课程的女同学选课元组全部删去
deletefromSeleCourse
whereStudentNoin（selectStudentNofromStudentwheresex=0）
andCourseNoin（selectCourseNofromCourse,TeacherwhereCourse.TeacherNo=Teacher.TeacherNoandtname='王二'）
5.把高数课不及格的成绩全部改为60
updateSeleCourse
setscore=60
wherescore<60andCourseNoin（selectCourseNofromCoursewherecoursename='高数'）
6.把低于所有课程总平均成绩的女学生成绩提高5%
updateSeleCourse
setscore=score*1.05
whereStudentNoin（selectStudentNofromStudentwheresex=0）
andscore<（selectAVG（score）fromSeleCourse）
7.在表SC中修改1234课程的成绩，当成绩小于等于70时提高5%，若成绩大于70分提高4%（两种方法，一种是用两个UPDATE语句实现，另一种是用带CASE的一个UPDATE语句实现）
用两个UPDATE语句实现：
updateSeleCourse
setscore=score*1.04
whereCourseNo=1234andscore>70;
updateSeleCourse
setscore=score*1.05
whereCourseNo=1234andscore<=70;
用带CASE的一个UPDATE语句实现：
updateSeleCourse
setscore=score*CASE
whenscore>70then1.04
else1.05
end
whereCourseNo=1234
7.在表SC中，当某个成绩低于全部课程的平均成绩时，提高5%
updateSeleCourse
setscore=score*1.05
wherescore<（selectAVG（score）fromSeleCourse）
（2）职工工作公司关系表
3.13题：
1）打开“SQLserverManagementStudio”窗口，单击在“新建查询”按钮；在查询窗口中，输入如下的命令。
1．用createtable语句创建上述三个表，需指出主键和外键
createtableEMP
（E#char（8）notnull,
ENAMEchar（8）notnull,
AGEint,
SEXchar
（1）,
ECITYchar（8）
primarykey（E#））
INSERTINTOEMPVALUES
（1001,'王名',50,'M','山东'）,
（1002,'小火',46,'F','北京'）,
（1003,'大雷',33,'M','上海'）,
（1004,'刘二',35,'M','山东'）,
（1005,'王琪琪',40,'F','黑龙江'）,
（1006,'刘梅',35,'F','陕西'）,
（1007,'韩雪',29,'F','甘肃'）
createtableCOMP
（C#char（8）notnull,
CNAMEchar（8）notnull,
CITYchar（8）
primarykey（C#））
INSERTINTOCOMPVALUES
（'A1','信息公司','上海'）,
（'A5','建设公司','深圳'）
createtableWORKS
（E#char（8）notnull,
C#char（8）notnull,
SALARYint,
primarykey（E#,C#）,
foreignkey（E#）referencesEMP,
foreignkey（C#）referencesCOMP）
INSERTINTOWORKSVALUES
（1001,'A1',15000）,
（1002,'A5',4600）,
（1003,'A5',4000）,
（1004,'A1',3500）,
（1005,'A1',4000）,
（1006,'A5',3500）,
（1007,'A1',3500）
2）在“SQL编辑器’工具栏中，单击”执行“按钮，表创建成功后，在结果窗口会出现“命令已成功完成。
”的信息提示；向表中读入数据，运行成功后，出现“x行受影响”的信息提示；
3）输入以下查询语句：
2.检索超过40岁的男职工的工号和姓名
selectE#,ENAME
fromEMP
whereAGE>40andSEX='M'
3.假设每个职工只在一个公司工作，检索工资超过3600元的男职工工号和姓名
selectEMP.E#,ENAME
fromEMP,WORKS
whereEMP.E#=WORKS.E#andSEX='M'andSALARY>3600
4.假设每个职工可在多个公司工作，检索至少在编号A1和A5公司兼职的职工工号和姓名
droptableEMP
createtableEMP
（E#char（8）notnull,
ENAMEchar（8）notnull,
AGEint,
SEXchar
（1）,
ECITYchar（8）
primarykey（E#））
INSERTINTOEMPVALUES
（1001,'王名',50,'M','山东'）,
（1002,'小火',46,'F','北京'）,
（1003,'大雷',33,'M','上海'）,
（1004,'刘二',35,'M','山东'）,
（1005,'王琪琪',40,'F','黑龙江'）,
（1006,'刘梅',35,'F','陕西'）,
（1007,'韩雪',29,'F','甘肃'）
droptableCOMP
createtableCOMP
（C#char（8）notnull,
CNAMEchar（8）notnull,
CITYchar（8）
primarykey（C#））
INSERTINTOCOMPVALUES
（'A1','信息公司','上海'）,
（'A5','建设公司','深圳'）,
（'B3','联华公司','北京'）
droptableWORKS
createtableWORKS
（E#char（8）notnull,
C#char（8）notnull,
SALARYint,
primarykey（E#,C#）,
foreignkey（E#）referencesEMP,
foreignkey（C#）referencesCOMP）
INSERTINTOWORKSVALUES
（1001,'A1',15000）,
（1002,'A1',4600）,
（1002,'A5',4600）,
（1002,'B3',4000）,
（1003,'A5',4000）,
（1004,'A1',3500）,
（1005,'A1',4000）,
（1005,'A5',3000）,
（1006,'A5',3500）,
（1007,'A1',3500）
检索：
selectEMP.E#,EMP.ENAME
fromEMP,WORKSasa,WORKSasb
whereEMP.E#=a.E#anda.E#=b.E#anda.C#='A1'andb.C#='A5'
5.检索在“建设公司”工作，工资超过1000元的男性职工的工号和姓名
selectEMP.E#,EMP.ENAME
fromEMP,WORKS,COMP
whereEMP.E#=WORKS.E#andWORKS.C#=COMP.C#andCNAME='建设公司'andSALARY>1000andSEX='M'
6.假设每个职工可在多个公司工作，检索每个职工的兼职公司数目和公资总数，显示（E#，NUM，SUM_SALARY），分别表示工号，公司数目和工资总数
selectE#,COUNT（C#）asNUM,COUNT（SALARY）asSUM_SALARY
fromWORKS
groupbyE#
7.工号为1005的职工在多个公司工作，试检索至少在1005职工兼职的所有公司工作的职工工号
selectx.E#
fromWORKSx
wherenotexists
（select*
fromWORKSy
whereE#=1004
andnotexists
（select*
fromWORKSz
wherez.E#=x.E#andz.C#=y.C#））;
8.检索信息公司中低于本公司平均工资的职工工号和姓名
selectEMP.E#,EMP.ENAME
fromEMP,WORKS,COMP
whereEMP.E#=WORKS.E#andWORKS.C#=COMP.C#andCNAME='信息公司'
andSALARY<（selectAVG（SALARY）
fromWORKS,COMP
whereWORKS.C#=COMP.C#andCNAME='信息公司'）
9.在每一公司中为40岁以上职工加薪100元（若为多个公司工作，可重复加）
updateWORKS
setSALARY=SALARY+100
whereE#in（selectE#fromEMPwhereAGE>=40）
10.在EMP表和WORKS表中删除年龄大于60岁的职工的有关元组
deletefromWORKS
whereE#in（selectE#fromEMPwhereAGE>=40）;
deletefromEMP
whereAGE>40;
3.15题：
对3.2题中的数据库中基本表SC建立一个视图
1）打开“SQLServerManagementStudio”窗口。
2）单击：
“标准”工具栏上的“新建查询”按钮，打开“查询编辑器”窗口。
在窗口内直接输入以下语句，按要求创建视图。
在数据库中，基于SC表创建一个名为”S_SCORE”的视图，要求该视图中包含列“课程号”、“课程名”、“开课学期”和“学时”、并且限定视图中返回的行中只包括第3学期及以后开课的课程信息。
CREATEVIEWS_SCORE（StudentNo,CourseNo_NUM,AVG_score））
ASSELECTStudentNo,COUNT（CourseNo）,AVG（score）
FROMSeleCourse
GROUPBYStudentNo;
3）单击“SQL编辑器”工具栏上的“分析”按钮，检查输入的T-SQL语句是否有语法错误。
4）查询与分析
1.select*fromS_SCORE
允许查询：
selectStudentNo,COUNT（CourseNo）asCourseNo_NUM,AVG（score）asAVG_score
fromSeleCourse
groupbyStudentNo
2.selectStudentNo,CourseNo_NUM
fromS_SCORE
whereAVG_score>80
允许查询：
selectStudentNo,COUNT（CourseNo）asCourseNo_NUM
fromSeleCourse
升级会员 