数据库笔试题及其答案.docx
《数据库笔试题及其答案.docx》由会员分享，可在线阅读，更多相关《数据库笔试题及其答案.docx（18页珍藏版）》请在冰豆网上搜索。
数据库笔试题及其答案
1.按要求写SQL语句：
根据集团成员培训业务，建立以下三张表：
S（S#,SN,SD,SA）S#,SN,SD,SA分别代表学号、学员姓名、所属单位、学员年龄
C（C#,CN）C#,CN分别代表课程编号、课程名称
SC（S#,C#,G）S#,C#,G分别代表学号、所选修的课程编号、学习成绩
要求如下：
1）使用标准SQL语句查询成员名单中所属单位叫“技术一部”的人员总数及
平均年龄；
2）使用标准的SQL语句更新学号为‘S#1’的姓名为“Mike”；
3）使用嵌套语句查询选修课程编号为‘C2’的学员姓名和所属单位；
4）使用嵌套语句查询不选修课程编号为‘C5’的学员姓名和所属单位；
5）查询选修课程超过5门的学员学号和所属单位；
2.请根据以下四张表（其中course_t表的teacher_id字段是teacher_t表的id字段的外键引用），
拼写出相应的sql语句（oracle语法）（15分）。
学生表：
students_t
idnamesex
001赵学生Male
002钱学生Male
003孙学生Male
004李学生Female
005周学生Female
教师表：
teacher_t
idnamesex
001吴老师Male
002郑老师Male
003王老师Male
004刘老师Female
005张老师Female
课程表：
course_t
idnamecreditteacher_id
001语文3001
002数学3002
003英语4003
004物理3004
005化学2005
006政治1001
007生物1005
008计算机2005
选课表：
student_course_t
idstudent_idcourse_id
001001001
002001002
003001003
004002001
005002007
1）统计每个学生选修的学分，并按学分降序排序
2）统计每个学生选修的所有课程和对应的任课老师；并按学生Id和课程Id排序
3）统计所有学生、所有课程和所有任课老师的对应关系；并按学生Id和课程Id排序
3、已有“成绩”如下表所示：
学号课程号分数
S1C180
S1C275
S2C1null
S2C255
S3C390
1）执行SQL语句：
SelectCount（学号）From成绩Where分数〉60
后的结果是什么？
2）请写出SQL语句来进行查询“成绩”表中学号为S1、课程号为C2的学号和分数
4.有如下两张表：
部门表和职员表，每个职员都属于一个部门，表结构如下：
Dept表
DeptnoDeptname
Emp表
EmpnoEmpnameDeptno
请使用SQL语句查询每个部门有多少职员，要求查询结果包含两例（部门名称，人数）？
5.表结构:
1）表名:
apply
字段（字段名/类型/长度）:
applynovarchar8;//申请单号（关键字）
applydatebigint8;//申请日期
statevarchar2;//申请状态
2）表名:
applydetail
字段（字段名/类型/长度）:
applynovarchar8;//申请单号（关键字）
namevarchar30;//申请人姓名
idcardvarchar18;//申请人身份证号
statevarchar2;//申请状态
其中，两个表的关联字段为申请单号。
题目:
1）查询身份证号码为440401430103082的申请日期
2）查询同一个身份证号码有两条以上记录的身份证号码及记录个数
3）删除applydetail表中所有姓李的记录
6、有一个数据表userinfo，包含userid，username字段，其中userid是唯一的，username可能
重复，请写一句sql查询语句，把重复的记录全部取出来。
useridusername
1老王
2老王
3老李
4老李
5小张
要求返回记录集
useridusername
1老王
2老王
3老李
4老李
7、建表Department部门
字段名中文名称类型长度备注
depid部门号变长字符10主键
depname部门名称变长字符
depcj部门平均成绩浮点型保留2位小数
表Employee人员表
字段名中文名称类型长度备注
empid员工号变长字符10主键
name姓名变长字符10
depid部门号变长字符10
Cj成绩浮点型保留2位小数
xorder名次整型
实现表中的记录备下面相关题目使用
Department表中嵌入记录
部门号部门名称
A001人力资源部
A002财务部
Employee表中嵌入记录
员工号姓名部门号成绩
001张三A00190
002李四A00190
003王五A00180
004张飞A00270
005刘备A00260
006关羽A00250
1）写出建表以及嵌入记录语句
2）显示A001部门员工的姓名、成绩
3）显示所有员工的员工号、姓名、部门名称、成绩
4）将关羽的成绩修改成52分
5）按要求写视图VdepEmpMax求各部门的最高分，显示部门号、最高分成绩
6）按要求写存储过程SP_Calc求各部门的平均成绩，并更新到Department表depcj字段中7）按要
求写存储过程SP_Order求员工的名次，并更新到Employee表xorder字段中
8）按要求写视图VdepEmp2，求各部门的前2名，显示部门号、员工号、成绩
排序规则如下：
员工部门分数名次
张三A001901
李四A001901
张飞A002701
刘备A002602
8、数据库基础:
1）使用SQL语句创建学生表students
字段:
学号:
s_id姓名:
s_name年龄:
age班级:
class辅导员:
assistant（请设计各字段类型
与长度）
2）查询学生表中年龄大于20的所有学生的学号与姓名
3）删除0201班的所有同学
4）查询0302班姓李的学生的个数
5）将班编号以’02’开头的所有班级的辅导员修改为‘李四’
9、有一个表LEANR，表里有三个字段分别是学号（student_id）,
课程（kc）,成绩（grade）。
1）.查询每一门课程的前两名
2）.查询以Grade降序排列的第31至40条记录（不需要区分课程）
3）.查询表中存在课程重复4次以上的记录,显示课程和重复的次数,并且按照重复次数的降序排列
1.按要求写SQL语句：
根据集团成员培训业务，建立以下三张表：
S（S#,SN,SD,SA）S#,SN,SD,SA分别代表学号、学员姓名、所属单位、学员年龄
createtables_suyong（
S#VARCHAR2（20）,SNVARCHAR2（20）,SDVARCHAR2（50）,SANUMBER（3）
）
INSERTINTOS_SUYONGvalues（'S#1','王麻子','技术一部',22）;COMMIT;
INSERTINTOS_SUYONGvalues（'S#2','吕秀才','技术二部',25）;COMMIT;
--由于是测试案例都没有添加主键约束
C（C#,CN）C#,CN分别代表课程编号、课程名称
createtablec_suyong（
C#VARCHAR2（20）,CNVARCHAR2（50）
）
INSERTINTOC_SUYONGvalues（'C1','编译原理'）;
INSERTINTOC_SUYONGvalues（'C2','计算机科学与技术'）;
INSERTINTOC_SUYONGvalues（'C3','操作系统'）;
INSERTINTOC_SUYONGvalues（'C4','数据结构'）;
INSERTINTOC_SUYONGvalues（'C5','软件工程'）;
SC（S#,C#,G）S#,C#,G分别代表学号、所选修的课程编号、学习成绩
createtablesc_suyong（
S#VARCHAR2（3）,C#VARCHAR2（3）,GNUMBER（3）
）
INSERTINTOSC_SUYONGVALUES（'S#1','C1',95）;
INSERTINTOSC_SUYONGVALUES（'S#1','C2',90）;
INSERTINTOSC_SUYONGVALUES（'S#1','C3',93）;
INSERTINTOSC_SUYONGVALUES（'S#1','C4',92）;
INSERTINTOSC_SUYONGVALUES（'S#1','C5',91）;
INSERTINTOSC_SUYONGVALUES（'S#2','C5',90）;
SELECT*FROMSC_SUYONG;
要求如下：
1）使用标准SQL语句查询成员名单中所属单位叫“技术一部”的人员总数及平均年龄；
selectSD,COUNT（SN）,AVG（SA）FROMS_SUYONGWHERESD='技术一部'groupbySD;
2）使用标准的SQL语句更新学号为‘S#1’的姓名为“Mike”；
UPDATES_SUYONGSETSN='Mike'whereS#='S#1';
3）使用嵌套语句查询选修课程编号为‘C2’的学员姓名和所属单位；
SELECTSN,SDFROMS_SUYONGWHERES#=（SELECTS#FROMSC_SUYONGwhereC#='C2'）;--也可以使用in
4）使用嵌套语句查询不选修课程编号为‘C5’的学员姓名和所属单位；
SELECTSN,SDFROMS_SUYONGWHERES#NOTIN（SELECTS#FROMSC_SUYONGwhereC#='C5'）;
5）查询选修课程超过5门的学员学号和所属单位；
SELECTS#,SDFROMS_SUYONGWHERES#IN（SELECTS#FROMSC_SUYONGGROUPBYS#HAVINGCOUNT（*）>=5）;
2.请根据以下四张表（其中course_t表的teacher_id字段是teacher_t表的id字段的外键引用），
拼写出相应的sql语句（oracle语法）（15分）。
学生表：
students_t
idnamesex
001赵学生Male
002钱学生Male
003孙学生Male
004李学生Female
005周学生Female
教师表：
teacher_t
idnamesex
001吴老师Male
002郑老师Male
003王老师Male
004刘老师Female
005张老师Female
课程表：
course_t
idnamecreditteacher_id
001语文3001
002数学3002
003英语4003
004物理3004
005化学2005
006政治1001
007生物1005
008计算机2005
选课表：
student_course_t
idstudent_idcourse_id
001001001
002001002
003001003
004002001
005002007
1）统计每个学生选修的学分，并按学分降序排序
select?
sc.student_id,count（c.credit）?
?
from?
students_t?
s,?
course_t?
c,?
student_course_t?
sc?
?
where?
s.id=sc.student_id?
and?
c.id=sc.course_id?
group?
by?
?
sc.student_id?
order?
by?
count（c.credit）;?
?
2）统计每个学生选修的所有课程和对应的任课老师；并按学生Id和课程Id排序
selects.nameass_name,c.nameasc_name,t.nameast_namefromstudents_ts,course_tc,student_course_tsc,teacher_tt
wheres.id=sc.student_idandc.id=sc.course_idandt.id=c.teacher_idorderbys.id,c.id;
3）统计所有学生、所有课程和所有任课老师的对应关系；并按学生Id和课程Id排序
selects.nameass_name,c.nameasc_name,t.nameast_namefromstudents_ts,course_tc,student_course_tsc,teacher_tt
wheres.id=sc.student_idandc.id=sc.course_idandt.id=c.teacher_idorderbys.id,c.id;
3、已有“成绩”如下表所示：
学号课程号分数
S1C180
S1C275
S2C1null
S2C255
S3C390
1）执行SQL语句：
SelectCount（学号）From成绩Where分数〉60后的结果是什么？
大于60分学生学生学号
2）请写出SQL语句来进行查询“成绩”表中学号为S1、课程号为C2的学号和分数
select?
学号，分数?
from?
成绩?
where?
学号=‘S1’?
and课程号=‘C2’
4.有如下两张表：
部门表和职员表，每个职员都属于一个部门，表结构如下：
Dept表
DeptnoDeptname
Emp表
EmpnoEmpnameDeptno
请使用SQL语句查询每个部门有多少职员，要求查询结果包含两例（部门名称，人数）？
selectd.dname,count（e.ename）fromempejoindeptdone.deptno=d.deptnogroupbyd.dname;
5.表结构:
1）表名:
apply
字段（字段名/类型/长度）:
applynovarchar8;//申请单号（关键字）
applydatebigint8;//申请日期
statevarchar2;//申请状态
--建表
createtableapply_suyong（
applynovarchar2（8）,
applydatenumber（8）,
statevarchar2
（2）
）
2）表名:
applydetail
字段（字段名/类型/长度）:
applynovarchar8;//申请单号（关键字）
namevarchar30;//申请人姓名
idcardvarchar18;//申请人身份证号
statevarchar2;//申请状态
createtableapplydetail（
applynovarchar2（8）,
namevarchar2（30）,
idcardvarchar2（18）,
statevarchar2
（2）
）
其中，两个表的关联字段为申请单号。
题目:
1）查询身份证号码为440401430103082的申请日期
Select?
applydate?
from?
apply?
a?
join?
applydetail?
d?
on?
a.applyno=d.applyno?
and?
?
Idcard='440401430103082';?
2）查询同一个身份证号码有两条以上记录的身份证号码及记录个数
?
select?
idcard,count（*）?
from?
applydetail?
group?
by?
idcard?
having?
count（*）>2;
3）删除applydetail表中所有姓李的记录
delete?
from?
applydetail?
where?
name='李%';
6、有一个数据表userinfo，包含userid，username字段，其中userid是唯一的，username可能
重复，请写一句sql查询语句，把重复的记录全部取出来。
useridusername
1老王
2老王
3老李
4老李
5小张
要求返回记录集
useridusername
1老王
2老王
3老李
4老李
--select?
*?
from?
userinfo?
where?
username?
in?
（select?
username?
from?
userinfo?
group?
by?
username?
?
having?
count（username）>1）;?
7、建表Department部门
字段名中文名称类型长度备注
depid部门号变长字符10主键
depname部门名称变长字符
depcj部门平均成绩浮点型保留2位小数
表Employee人员表
字段名中文名称类型长度备注
empid员工号变长字符10主键
name姓名变长字符10
depid部门号变长字符10
Cj成绩浮点型保留2位小数
xorder名次整型
实现表中的记录备下面相关题目使用
Department表中嵌入记录
部门号部门名称
A001人力资源部
A002财务部
Employee表中嵌入记录
员工号姓名部门号成绩
001张三A00190
002李四A00190
003王五A00180
004张飞A00270
005刘备A00260
006关羽A00250
1）写出建表以及嵌入记录语句
createtableDepartment（
depidvarchar2（20）primarykey,depnamevarchar2（20）,depcjnumber（10,2））;
createtableEmployee（
empidvarchar2（20）primarykey,namevarchar2（20）,depidvarchar2（20）,
cjnumber（10,2）,
xordernumber（10））;
insertintoDepartment（depid,depname）values（A001,人力资源部）;
insertintoDepartment（depid,depname）values（A002,财务部）;
insertintoEmployee（empid,name,depid,cj）values（001,张三,A001,90）;
insertintoEmployee（empid,name,depid,cj）values（002,李四,A001,90）;
insertintoEmployee（empid,name,depid,cj）values（003,王五,A001,80）;
insertintoEmployee（empid,name,depid,cj）values（004,张飞,A002,70）;
insertintoEmployee（empid,name,depid,cj）values（005,刘备,A002,60）;
insertintoEmployee（empid,name,depid,cj）values（006,关羽,A002,50）;
2）显示A001部门员工的姓名、成绩
selectname,cjfromemployeewheredepid=A001;
3）显示所有员工的员工号、姓名、部门名称、成绩
selecte.empid,e.name,d.depname,e.cjfromemployeee,departmentdwheree.depid=d.depid;
4）将关羽的成绩修改成52分
updateemployeesetcj=52wherename=关羽;
5）按要求写视图VdepEmpMax求各部门的最高分，显示部门号、最高分成绩
createviewVdepEmpMaxas（selectdeptid,max（cj）fromemployeeegroupbydeptid）
6）按要求写存储过程SP_Calc求各部门的平均成绩，并更新到Department表depcj字段中--不做
7）按要求写存储过程SP_Order求员工的名次，并更新到Employee表xorder字段中--不做
8）按要求写视图VdepEmp2，求各部门的前2名，显示部门号、员工号、成绩
createorreplaceviewVdepEmp2as
selectdepid,name,cj,rnfrom
（selecte.*,rank（）over（partitionbydepid
orderbycjdesc）rnfromemployeee）
wherern<3;
排序规则如下：
员工部门分数名次
张三A001901
李四A001901
张飞A002701
刘备A002602
8、数据库基础:
1）使用SQL语句创建学生表students
字段:
学号:
s_id姓名:
s_name年龄:
age班级:
class辅导员:
assistant（请设计各字段类型
与长度）
createtablestudents（
s_idnumber（10）primarykey,
s_namevarchar（30）notnull,
agenumber（3）notnull,
升级会员 