数据库sql查询教学答案.docx
《数据库sql查询教学答案.docx》由会员分享，可在线阅读，更多相关《数据库sql查询教学答案.docx（18页珍藏版）》请在冰豆网上搜索。
数据库sql查询教学答案
现在有一教学管理系统，具体的关系模式如下：
Student（no,name,sex,birthday,class）
Teacher（no,name,sex,birthday,prof,depart）
Course（cno,cname,tno）
Score（no,cno,degree）
其中表中包含如下数据：
Course表：
Score表：
Student表：
Teacher表：
根据上面描述完成下面问题：
（注意：
注意保存脚本，尤其是DDL和DML，以便进行数据还原）
DDL
1.写出上述表的建表语句。
命令：
createtableStudent（noint,namevarchar（14）,sexvarchar
（2）,birthdaydate,classint）;
createtableTeacher（noint,namevarchar（14）,sexvarchar
（2）,birthdaydate,profvarchar（10）,departvarchar（10））;
createtableCourse（cnovarchar（8）,cnamevarchar（14）,tnoint）;
createtableScore（noint,cnovarchar（8）,degreeint）;
DML
2.给出相应的INSERT语句来完成题中给出数据的插入。
命令：
insertintoStudentvalues（5001,'李勇','男','1987-7-22',95001）;
insertintoStudentvalues（5002,'刘晨','女','1987-11-15',95002）;
insertintoStudentvalues（5003,'王敏','女','1987-10-5',95001）;
insertintoStudentvalues（5004,'李好尚','男','1987-9-25',95003）;
insertintoStudentvalues（5005,'李军','男','1987-7-17',95004）;
insertintoStudentvalues（5006,'范新位','女','1987-6-18',95005）;
insertintoStudentvalues（5007,'张霞东','女','1987-8-29',95006）;
insertintoStudentvalues（5008,'赵薇','男','1987-6-15',95007）;
insertintoStudentvalues（5009,'钱民将','女','1987-6-23',95008）;
insertintoStudentvalues（5010,'孙俪','女','1987-9-24',95002）;
insertintoStudentvalues（108,'赵里','男','1987-6-15',95007）;
insertintoStudentvalues（109,'丘处机','男','1987-6-23',95008）;
insertintoStudentvalues（107,'杨康','男','1987-9-24',95001）;
insertintoTeachervalues（1,'李卫','男','1957-11-5','教授','电子工程系'）;
insertintoTeachervalues（2,'刘备','男','1967-10-9','副教授','math'）;
insertintoTeachervalues（3,'关羽','男','1977-9-20','讲师','sc'）;
insertintoTeachervalues（4,'李修','男','1957-6-25','教授','elec'）;
insertintoTeachervalues（5,'诸葛亮','男','1977-6-15','教授','计算机系'）;
insertintoTeachervalues（6,'殷素素','女','1967-1-5','副教授','sc'）;
insertintoTeachervalues（7,'周芷若','女','1947-2-23','教授','sc'）;
insertintoTeachervalues（8,'赵云','男','1980-6-13','副教授','计算机系'）;
insertintoTeachervalues（9,'张敏','女','1985-5-5','助教','sc'）;
insertintoTeachervalues（10,'黄蓉','女','1967-3-22','副教授','sc'）;
insertintoTeachervalues（11,'张三','男','1967-3-22','副教授','sc'）;
insertintoCoursevalues（'3-101','数据库',1）;
insertintoCoursevalues（'5-102','数学',2）;
insertintoCoursevalues（'3-103','信息系统',3）;
insertintoCoursevalues（'3-104','操作系统',4）;
insertintoCoursevalues（'3-105','数据结构',5）;
insertintoCoursevalues（'3-106','数据处理',5）;
insertintoCoursevalues（'4-107','pascal语言',6）;
insertintoCoursevalues（'4-108','C++',7）;
insertintoCoursevalues（'4-109','java',8）;
insertintoCoursevalues（'3-245','数据挖掘',10）;
insertintoCoursevalues（'3-111','软件工程',11）;
insertintoScorevalues（5001,'3-105',69）;
insertintoScorevalues（5001,'5-102',55）;
insertintoScorevalues（5003,'4-108',85）;
insertintoScorevalues（5004,'3-105',77）;
insertintoScorevalues（5005,'3-245',100）;
insertintoScorevalues（5006,'3-105',53）;
insertintoScorevalues（5003,'4-109',45）;
insertintoScorevalues（5008,'3-105',98）;
insertintoScorevalues（5004,'4-109',68）;
insertintoScorevalues（5010,'3-105',88）;
insertintoScorevalues（5003,'3-105',98）;
insertintoScorevalues（5005,'4-109',68）;
insertintoScorevalues（5002,'3-105',88）;
insertintoScorevalues（107,'3-105',98）;
insertintoScorevalues（108,'4-109',68）;
insertintoScorevalues（109,'3-105',88）;
insertintoScorevalues（109,'4-109',80）;
insertintoScorevalues（107,'3-111',88）;
insertintoScorevalues（5003,'3-111',80）;
单表查询
3.以class降序输出student的所有记录（student表全部属性）
命令：
select*fromStudentorderbyclassdesc;
4.列出教师所在的单位depart（不重复）。
命令：
selectdistinctdepartfromTeacher;
5.列出student表中所有记录的name、sex和class列
命令：
selectname,sex,classfromStudent;
6.输出student中不姓王的同学的姓名。
命令：
selectnamefromStudentexceptselectnamefromStudentwherenamelike'王%';或
selectnamefromStudentwherenamenotlike'王%';
7.输出成绩为85或86或88或在60-80之间的记录（no,cno,degree）
命令：
selectno,cno,DEGREEfromScorewheredegree=85ordegree=86ordegree=88ordegreebetween60and80;
8.输出班级为95001或性别为‘女’的同学（student表全部属性）
命令：
select*fromStudentwhereclass=95001orsex='女';
9.以cno升序、degree降序输出score的所有记录。
（score表全部属性）
命令：
select*fromScoreorderbycnoasc,degreedesc;
10.输出男生人数及这些男生分布在多少个班级中
命令：
selectCOUNT（*）,count（distinctclass）fromStudentwheresex='男';
11.列出存在有85分以上成绩的课程编号。
命令：
selectdistinctcnofromScorewheredegree>85;
12.输出95001班级的学生人数
命令：
selectCOUNT（*）fromStudentwhereclass=95001;
13.输出‘3-105’号课程的平均分
命令：
selectavg（cast（degreeasfloat））fromScorewherecno='3-105';
14.输出student中最大和最小的birthday日期值
命令：
selectMAX（birthday）,MIN（birthday）fromStudent;
15.显示95001和95004班全体学生的全部个人信息（不包括选课）。
（student表全部属性）
命令：
select*fromStudentwhereclass=95001orclass=95004;
聚合查询
16.输出至少有5个同学选修的并以3开头的课程的课程号，课程平均分，课程最高分，课程最低分。
命令：
selectcno,avg（cast（degreeasfloat））,MAX（degree）,MIN（degree）fromScorewherecnolike'3%'groupbycnohavingCOUNT（cno）>5;
或者：
selectcno,AVG（cast（DEGREEasfloat））,MAX（degree）,MIN（DEGREE）fromScoregroupbycnohavingCOUNT（cno）>=5andcnolike'3%'
17.输出所选修课程中最低分大于70分且最高分小于90分的学生学号及学生姓名
命令：
selectStudent.no,namefromStudentjoinScoreonStudent.no=Score.nogroupbyStudent.no,namehavingMAX（Score.degree）<90andMIN（Score.degree）>70;
18.显示所教课程选修人数多于5人的教师姓名
命令：
selectnamefromTeacherjoinCourseonTeacher.no=Course.tnowhereCoin（selectcnofromScoregroupbycnohavingCOUNT（So）>5）;
19.输出’95001’班级所选课程的课程号和平均分
命令：
selectcno,avg（cast（degreeasfloat））fromScorewherenoin（selectnofromStudentwhereclass=95001）groupbycno;
或者：
selectcno,AVG（cast（degreeasfloat））fromScorejoinStudentonScore.no=Student.nogroupbycno,classhavingclass='95001'
20.输出至少有两名男同学的班级编号。
命令：
selectclassfromStudentwheresex='男'groupbyclasshavingCOUNT（class）>=2;
或者：
selecta.classfrom（select*fromStudentwheresex='男'）agroupbya.classhavingCOUNT（a.class）>=2
多表查询
21.列出与108号同学同年出生的所有学生的学号、姓名和生日
命令：
selectno,name,birthdayfromStudentwhereyear（birthday）=（selectyear（birthday）fromStudentwhereno=108）;
或者：
selectb.no,b.name,b.birthdayfromStudentajoinStudentbondatediff（YEAR,a.birthday,b.birthday）=0anda.no='108'
22.列出存在有85分以上成绩的课程名称
命令：
selectcnamefromCoursewherecnoin（selectdistinctcnofromScorewheredegree>85）;或
selectdistinctcnamefromCoursejoinScoreonCo=Sowheredegree>85;
23.列出“计算机系”教师所教课程的成绩表（课程编号，课程名，学生名，成绩）。
命令：
selectCo,cname,Student.name,DEGREEfromTeacherjoinCourseonTeacher.no=Course.tnojoinScoreonCo=SojoinStudentonScore.no=Student.nowhereTeacher.depart='计算机系';
不写
24、列出所有可能的“计算机系”与“电子工程系”不同职称的教师配对信息，要求输出每个老师的姓名（name）和（职称）------------------------------->（难度太大，看不懂代码，放弃）
命令：
selecta.name,a.prof,b.name,b.proffrom（selectname,prof,departfromTeacherwheredepart='计算机系'ordepart='电子工程系'）ajoin（selectname,prof,departfromTeacherwheredepart='电子工程系'ordepart='计算机系'）bonnota.prof=b.profandnota.depart=b.depart;
不写
24.列出所有处于不同班级中，但具有相同生日的学生，要求输出每个学生的学号和姓名。
（提示：
使用datediff函数，具体用法可以参考：
命令：
selecta.no,a.name,b.no,b.namefromStudentajoinStudentbonnota.class=b.classanda.birthday=b.birthday;
25.显示‘张三’教师任课的学生姓名，课程名，成绩
命令：
selectStudent.name,cname,DEGREEfromTeacherjoinCourseonTeacher.no=Course.tnojoinScoreonCo=SojoinStudentonScore.no=Student.nowhereTeacher.name='张三';
26.列出所讲课已被选修的教师的姓名和系别
命令：
selectdistinctname,departfromTeacherjoinCourseonTeacher.no=Course.tnojoinScoreonCo=So;
不写
27.输出所有学生的name、no和degree。
（degree为空的不输出和为空的输出两种情况）。
命令：
selectname,Student.no,DEGREEfromStudentleftjoinScoreonStudent.no=Score.no;
selectname,Student.no,DEGREEfromStudentjoinScoreonStudent.no=Score.no;
28.列出所有任课教师的name和depart。
（从课程选修和任课两个角度考虑）
命令：
selectdistinctname,departfromTeacherjoinCourseonTeacher.no=Course.tno;
selectdistinctname,departfromTeacherjoinCourseonTeacher.no=Course.tnojoinScoreonCo=So;
29.输出男教师所上课程名称。
命令：
selectcnamefromTeacherjoinCourseonTeacher.no=Course.tnowhereTeacher.sex='男';
30.写出与“李军”同学性别相同的所有同学的name。
命令：
selectnamefromStudentwheresex=（selectsexfromStudentwherename='李军'）;
31.输出选修“数据结构”课程的男同学的成绩。
命令：
selectDEGREEfromScorejoinStudentonScore.no=Student.nojoinCourseonSo=Cowheresex='男'andcname='数据结构';
不写
32.列出选修编号为‘3-105’课程并且该门课程成绩比课程‘3-111’的最高分要高的cno,no和degree。
命令：
selectCo,Score.no,DEGREEfromCoursejoinScoreonCo=SojoinStudentonScore.no=Student.nowhereCo='3-105'andScore.degree>（selectmax（degree）fromScorewhereSo='3-111'）;
子查询
33.输出score中成绩最高的学号和课程号
命令：
selectno,cnofromScorewheredegreein（selectMAX（degree）fromScore）;
34.输出选修3-105课程，其成绩高于109号同学在此课程所得成绩的所有同学的学号，姓名
命令：
selectStudent.no,namefromStudentjoinScoreonStudent.no=Score.nowhereSo='3-105'anddegree>（selectdegreefromScorewhereno='109'andcno='3-105'）;
不写
35.列出成绩比该课程平均成绩低的同学的学号，成绩和该门课的平均成绩
命令：
selectno,DEGREE,a.avg_degreefromScorejoin（selectcno,AVG（cast（degreeasfloat））fromScoregroupbycno）a（cno,avg_degree）onSo=owhereScore.degree不写
36.列出没有实际授课的教师的姓名和系别
命令：
selectname,departfromTeacherexceptselectname,departfromTeacherjoinCourseonTeacher.no=Course.tnojoinScoreonCo=So;
37.列出选修了编号为‘3-105’课程且其成绩高于‘4-109’课程最高成绩的同学的课程编号，学号和成绩
命令：
selectcno,no,DEGREEfromScorewherecno='3-105'anddegree>（selectMAX（degree）fromScorewherecno='4-109'）;
升级会员 