数据库笔试题及其答案.docx

1、数据库笔试题及其答案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的学员姓名

2、和所属单位；5)查询选修课程超过 5 门的学员学号和所属单位；2.请根据以下四张表（其中 course_t 表的 teacher_id 字段是 teacher_t 表的 id 字段的外键引用），拼写出相应的 sql 语句（oracle 语法）（15 分）。学生表：students_tid name sex001 赵学生 Male002 钱学生 Male003 孙学生 Male004 李学生 Female005 周学生 Female教师表：teacher_tid name sex001 吴老师 Male002 郑老师 Male003 王老师 Male004 刘老师 Female005 张老师 F

3、emale课程表：course_tid name credit teacher_id001 语文 3 001002 数学 3 002003 英语 4 003004 物理 3 004005 化学 2 005006 政治 1 001007 生物 1 005008 计算机 2 005选课表：student_course_tid student_id course_id001 001 001002 001 002003 001 003004 002 001005 002 0071)统计每个学生选修的学分，并按学分降序排序2)统计每个学生选修的所有课程和对应的任课老师；并按学生 Id 和课程 Id 排序

4、3)统计所有学生、所有课程和所有任课老师的对应关系；并按学生 Id 和课程 Id 排序3、已有“成绩”如下表所示：学号 课程号 分数S1 C1 80S1 C2 75S2 C1 nullS2 C2 55S3 C3 901） 执行 SQL 语句：Select Count（学号）From 成绩 Where 分数60后的结果是什么？2）请写出 SQL 语句来进行查询“成绩”表中学号为 S1、课程号为 C2 的学号和分数4.有如下两张表：部门表和职员表，每个职员都属于一个部门，表结构如下：Dept 表Deptno DeptnameEmp 表Empno Empname Deptno请使用 SQL 语句查询

5、每个部门有多少职员，要求查询结果包含两例（部门名称，人数）？5.表结构: 1) 表名:apply 字段(字段名/类型/长度): applyno varchar 8;/申请单号(关键字) applydate bigint 8;/申请日期 state varchar 2;/申请状态 2) 表名:applydetail 字段(字段名/类型/长度): applyno varchar 8;/申请单号(关键字) name varchar 30;/申请人姓名 idcard varchar 18;/申请人身份证号 state varchar 2;/申请状态 其中，两个表的关联字段为申请单号。 题目:1)查询身

6、份证号码为 440401430103082 的申请日期2)查询同一个身份证号码有两条以上记录的身份证号码及记录个数3)删除 applydetail 表中所有姓李的记录6、有一个数据表 userinfo，包含 userid，username 字段，其中 userid 是唯一的，username 可能重复，请写一句 sql 查询语句，把重复的记录全部取出来。userid username1 老王2 老王3 老李4 老李5 小张要求返回记录集userid username1 老王2 老王3 老李4 老李7、建表 Department 部门字段名 中文名称 类型 长度 备注depid 部门号 变长字符

7、 10 主键depname 部门名称 变长字符depcj 部门平均成绩 浮点型保留 2 位小数表 Employee 人员表字段名 中文名称 类型 长度 备注empid 员工号 变长字符 10 主键name 姓名 变长字符 10depid 部门号 变长字符 10Cj 成绩 浮点型保留 2 位小数xorder 名次 整型实现表中的记录备下面相关题目使用Department 表中嵌入记录部门号 部门名称A001 人力资源部A002 财务部Employee 表中嵌入记录员工号 姓名 部门号 成绩001 张三 A001 90002 李四 A001 90003 王五 A001 80004 张飞 A002

8、70005 刘备 A002 60006 关羽 A002 501）写出建表以及嵌入记录语句2）显示 A001 部门员工的姓名、成绩3）显示所有员工的员工号、姓名、部门名称、成绩4）将关羽的成绩修改成 52 分5）按要求写视图 VdepEmpMax 求各部门的最高分，显示部门号、最高分成绩6）按要求写存储过程 SP_Calc 求各部门的平均成绩，并更新到 Department 表 depcj 字段中 7）按要求写存储过程 SP_Order 求员工的名次，并更新到 Employee 表 xorder 字段中8）按要求写视图 VdepEmp2，求各部门的前 2 名，显示部门号、员工号、成绩 排序规则如

9、下：员工 部门 分数 名次张三 A001 90 1李四 A001 90 1张飞 A002 70 1刘备 A002 60 28、数据库基础:1)使用 SQL 语句创建学生表 students字段: 学号:s_id 姓名:s_name 年龄:age 班级:class 辅导员:assistant (请设计各字段类型与长度)2)查询学生表中年龄大于 20 的所有学生的学号与姓名3)删除 0201 班的所有同学4)查询 0302 班姓李的学生的个数5)将班编号以02开头的所有班级的辅导员修改为李四9、有一个表 LEANR，表里有三个字段分别是学号（student_id）,课程（kc）,成绩（grade）

10、 。1).查询每一门课程的前两名2).查询以 Grade 降序排列的第 31 至 40 条记录(不需要区分课程)3).查询表中存在课程重复 4 次以上的记录,显示课程和重复的次数,并且按照重复次数的降序排列1.按要求写 SQL 语句：根据集团成员培训业务，建立以下三张表：S (S#,SN,SD,SA) S#,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄 create table s_suyong( S# VARCHAR2(20), SN VARCHAR2(20),SD VARCHAR2(50),SA NUMBER(3) ) INSERT INTO S_SUYONG values

11、(S#1,王麻子,技术一部,22);COMMIT; INSERT INTO S_SUYONG values(S#2,吕秀才,技术二部,25);COMMIT; -由于是测试案例 都没有添加主键约束C (C#,CN ) C#,CN 分别代表课程编号、课程名称 create table c_suyong( C# VARCHAR2(20),CN VARCHAR2(50) ) INSERT INTO C_SUYONG values(C1,编译原理); INSERT INTO C_SUYONG values(C2,计算机科学与技术); INSERT INTO C_SUYONG values(C3,操作系统

12、); INSERT INTO C_SUYONG values(C4,数据结构); INSERT INTO C_SUYONG values(C5,软件工程);SC ( S#,C#,G ) S#,C#,G 分别代表学号、所选修的课程编号、学习成绩 create table sc_suyong( S# VARCHAR2(3),C# VARCHAR2(3), G NUMBER(3) ) INSERT INTO SC_SUYONG VALUES(S#1,C1,95); INSERT INTO SC_SUYONG VALUES(S#1,C2,90); INSERT INTO SC_SUYONG VALUE

13、S(S#1,C3,93); INSERT INTO SC_SUYONG VALUES(S#1,C4,92); INSERT INTO SC_SUYONG VALUES(S#1,C5,91); INSERT INTO SC_SUYONG VALUES(S#2,C5,90); SELECT * FROM SC_SUYONG; 要求如下：1)使用标准 SQL 语句查询成员名单中所属单位叫“技术一部”的人员总数及平均年龄； select SD,COUNT(SN),AVG(SA) FROM S_SUYONG WHERE SD=技术一部 group by SD;2)使用标准的 SQL 语句更新学号为S#1

14、的姓名为“Mike”； UPDATE S_SUYONG SET SN=Mike where S#=S#1;3)使用嵌套语句查询选修课程编号为C2的学员姓名和所属单位； SELECT SN,SD FROM S_SUYONG WHERE S#=( SELECT S# FROM SC_SUYONG where C#=C2);-也可以使用in4)使用嵌套语句查询不选修课程编号为C5的学员姓名和所属单位； SELECT SN,SD FROM S_SUYONG WHERE S# NOT IN( SELECT S# FROM SC_SUYONG where C#=C5);5)查询选修课程超过 5 门的学员学

15、号和所属单位； SELECT S#,SD FROM S_SUYONG WHERE S# IN(SELECT S# FROM SC_SUYONG GROUP BY S# HAVING COUNT(*) =5); 2.请根据以下四张表（其中 course_t 表的 teacher_id 字段是 teacher_t 表的 id 字段的外键引用），拼写出相应的 sql 语句（oracle 语法）（15 分）。学生表：students_tid name sex001 赵学生 Male002 钱学生 Male003 孙学生 Male004 李学生 Female005 周学生 Female教师表：teach

16、er_tid name sex001 吴老师 Male002 郑老师 Male003 王老师 Male004 刘老师 Female005 张老师 Female课程表：course_tid name credit teacher_id001 语文 3 001002 数学 3 002003 英语 4 003004 物理 3 004005 化学 2 005006 政治 1 001007 生物 1 005008 计算机 2 005选课表：student_course_tid student_id course_id001 001 001002 001 002003 001 003004 002 001

17、005 002 0071)统计每个学生选修的学分，并按学分降序排序 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 排序 select s.name as s_name,c.name as c_na

18、me ,t.name as t_name from students_t s, course_t c, student_course_t sc,teacher_t t where s.id=sc.student_id and c.id=sc.course_id and t.id=c.teacher_id order by s.id,c.id;3)统计所有学生、所有课程和所有任课老师的对应关系；并按学生 Id 和课程 Id 排序 select s.name as s_name,c.name as c_name ,t.name as t_name from students_t s, course

19、_t c, student_course_t sc,teacher_t t where s.id=sc.student_id and c.id=sc.course_id and t.id=c.teacher_id order by s.id,c.id;3、已有“成绩”如下表所示：学号 课程号 分数S1 C1 80S1 C2 75S2 C1 nullS2 C2 55S3 C3 901） 执行 SQL 语句：Select Count（学号）From 成绩 Where 分数60后的结果是什么？ 大于60分学生学生学号2）请写出 SQL 语句来进行查询“成绩”表中学号为 S1、课程号为 C2 的学号和

20、分数 select?学号，分数?from?成绩?where?学号=S1?and课程号=C24.有如下两张表：部门表和职员表，每个职员都属于一个部门，表结构如下：Dept 表Deptno DeptnameEmp 表Empno Empname Deptno请使用 SQL 语句查询每个部门有多少职员，要求查询结果包含两例（部门名称，人数）？select d.dname,count(e.ename) from emp e join dept d on e.deptno=d.deptno group by d.dname; 5.表结构: 1) 表名:apply 字段(字段名/类型/长度): applyn

21、o varchar 8;/申请单号(关键字) applydate bigint 8;/申请日期 state varchar 2;/申请状态 -建表 create table apply_suyong( applyno varchar2(8), applydate number(8), state varchar2(2) ) 2) 表名:applydetail 字段(字段名/类型/长度): applyno varchar 8;/申请单号(关键字) name varchar 30;/申请人姓名 idcard varchar 18;/申请人身份证号 state varchar 2;/申请状态 cre

22、ate table applydetail( applyno varchar2(8), name varchar2(30), idcard varchar2(18), state varchar2(2) ) 其中，两个表的关联字段为申请单号。 题目:1)查询身份证号码为 440401430103082 的申请日期 Select?applydate?from?apply?a?join?applydetail?d?on?a.applyno=d.applyno?and?Idcard=440401430103082;?2)查询同一个身份证号码有两条以上记录的身份证号码及记录个数 ?select?idc

23、ard,count(*)?from?applydetail?group?by?idcard?having?count(*)2;3)删除 applydetail 表中所有姓李的记录 delete?from?applydetail?where?name=李%;6、有一个数据表 userinfo，包含 userid，username 字段，其中 userid 是唯一的，username 可能重复，请写一句 sql 查询语句，把重复的记录全部取出来。userid username1 老王2 老王3 老李4 老李5 小张要求返回记录集userid username1 老王2 老王3 老李4 老李-sel

24、ect?*?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 姓名 变长字符 10depid 部门号 变长字符 10Cj 成绩 浮点

25、型保留 2 位小数xorder 名次 整型实现表中的记录备下面相关题目使用Department 表中嵌入记录部门号 部门名称A001 人力资源部A002 财务部Employee 表中嵌入记录员工号 姓名 部门号 成绩001 张三 A001 90002 李四 A001 90003 王五 A001 80004 张飞 A002 70005 刘备 A002 60006 关羽 A002 501）写出建表以及嵌入记录语句 create table Department( depid varchar2(20) primary key, depname varchar2(20), depcj number(1

26、0,2); create table Employee( empid varchar2(20) primary key, name varchar2(20), depid varchar2(20), cj number(10,2), xorder number(10); insert into Department(depid,depname) values(A001,人力资源部); insert into Department(depid,depname) values(A002,财务部); insert into Employee(empid, name, depid, cj) value

27、s(001,张三,A001,90); insert into Employee(empid, name, depid, cj) values(002,李四,A001,90); insert into Employee(empid, name, depid, cj) values(003,王五,A001,80); insert into Employee(empid, name, depid, cj) values(004,张飞,A002,70); insert into Employee(empid, name, depid, cj) values(005,刘备,A002,60); inser

28、t into Employee(empid, name, depid, cj) values(006,关羽,A002,50);2）显示 A001 部门员工的姓名、成绩 select name,cj from employee where depid=A001;3）显示所有员工的员工号、姓名、部门名称、成绩 select e.empid,e.name,d.depname,e.cj from employee e, department d where e.depid=d.depid;4）将关羽的成绩修改成 52 分 update employee set cj=52 where name= 关羽

29、;5）按要求写视图 VdepEmpMax 求各部门的最高分，显示部门号、最高分成绩 create view VdepEmpMax as (select deptid,max(cj) from employee e group by deptid) 6）按要求写存储过程 SP_Calc 求各部门的平均成绩，并更新到 Department 表 depcj 字段中-不做 7）按要求写存储过程 SP_Order 求员工的名次，并更新到 Employee 表 xorder 字段中-不做8）按要求写视图 VdepEmp2，求各部门的前 2 名，显示部门号、员工号、成绩 create or replace

30、view VdepEmp2 as select depid,name,cj,rn from ( select e.*,rank() over (partition by depid order by cj desc) rn from employee e) where rn3;排序规则如下：员工 部门 分数 名次张三 A001 90 1李四 A001 90 1张飞 A002 70 1刘备 A002 60 28、数据库基础:1)使用 SQL 语句创建学生表 students字段: 学号:s_id 姓名:s_name 年龄:age 班级:class 辅导员:assistant (请设计各字段类型与长度)create table students( s_id number(10) primary key, s_name varchar(30) not null, age number(3) not null,