数据库面试题4.docx

1、数据库面试题4 数据库面试题四数据库写 SQL题（ 30）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)使用嵌套语句查询

2、不选修课程编号为C5的学员姓名和所属单位；5)查询选修课程超过 5门的学员学号和所属单位；解答： 1) select count(SN),avg(SA) from S where SD=技术一部; 2) update S set SN=Mike where S#=S#1; 3) select SN,SD from S where S#=(select S# from SC where C#=C2); 4) select SN,SD from S where S# not in(select S# from SC where C#=C5); 5) select S#,SD from S wher

3、e S#= (select S# from SC group by S# having count(S#)=5);2.请根据以下四张表（其中course_t表的 teacher_id字段是teacher_t表的 id字段的外键引用），拼写出相应的sql语句（oracle语法）。（15分）学生表：students_t id name sex 001赵学生 Male 002钱学生 Male 003孙学生 Male 004李学生 Female 005周学生 Female 教师表：teacher_t id name sex 001吴老师 Male 002郑老师 Male 003王老师 Male 004

4、刘老师 Female 005张老师 Female课程表：course_t id name credit teacher_id 001语文 3 001 002数学 3 002 003英语 4 003 004物理 3 004 005化学 2 005 006政治 1 001 007生物 1 005 008计算机 2 005选课表：student_course_t id student_id course_id 001 001 001 002 001 002 003 001 003 004 002 001 005 002 007 1)统计每个学生选修的学分，并按学分降序排序2)统计每个学生选修的所有课

5、程和对应的任课老师；并按学生 Id和课程 Id排序3)统计所有学生、所有课程和所有任课老师的对应关系；并按学生 Id和课程 Id排序解答：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) select s.name as s_name,c.name as c_name ,

6、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)与 2）相同3.假设有以下的两个表： Cus_A ID* Name Address Cus_B ID* Name Address *主键表 Cus_A 和表 Cus_B的结构完全相同，表 Cus_A 和表 Cus_B中既存在 ID相同的记录，也存在 ID不同的

7、记录。现要求将ID只存在于表表Cus_A 中而不存在于表Cus_B中的记录全部插入到Cus_B表中，并用表 Cus_A中的记录更新 Cus_B中相同的 ID的记录，请写出完成这一功能的存储过程。解答： create or replace procedure test is cust_record cus_a%rowtype ; cursor cust_cursor is select id,name,address from cus_a; Begin Open cust_cursor; LOOP Fetch cust_cursor into cust_record; EXIT WHEN cus

8、t_cursor %NOTFOUND;-先删除在插入 delete from cus_b where id=cust_record.id; insert into cus_b values(cust_record.id, cust_record.name, cust_record.address); END LOOP; end;4、已有“成绩”如下表所示：学号课程号分数 S1 C1 80 S1 C2 75 S2 C1 null S2 C2 55 S3 C3 901）执行 SQL语句： Select Count（学号）From成绩 Where 分数60后的结果是什么？2）请写出SQL语句来进行查

9、询“成绩”表中学号为S1、课程号为 C2的学号和分数解答：1）统计分数超过 60的学生总数。2）select 学号，分数 from 成绩 where 学号=S1 and课程号=C2;5.SAL是 Product表中的索引列，请优化如下 SQL语句，并简述原因。原语句： SELECT* FROM ProductWHERE SAL * 1225000；解答：Select * from product where sal(25000/12);理由：WHERE子句中，如果索引列是函数的一部分优化器将不使用索引而使用全表扫描6.有一张表，字段有用户名、口令及备注，请用 SQL选择出用户名和口令完全相同的

10、记录（应包括用户名和数量的出现次数） T_USER(USER_NAME,PASSWORD)显示 USER_NAME COUNT(*) QWE 4 WER 5解答：select user_name,count(*) from t_user group by user_name,password;7.有一张表，T_MONEY，字段有 ID，FEE，请用SQL语言选择出 FEE值为前三条记录。T_MONEY(ID，FEE)显示 ID FEE 2 100 1 90 2 80 Select Id,fee from (Select id,fee from t_money order by fee desc

11、) where rownum2;3) delete from applydetail where name=李%;15. 在 system 方案中建立表 table1,表中包含如下字段字段名称数据类型要求name Varchar2 非空id Number 非空 age Number sex Varchar2 salary Number 解答： Create table system.tablel1 ( Id number not null, Name varchar(8) not null, Age number, Sex varchar(2), Salary number );16、某公司的

12、机构结构为树型结构，对应的表结构为 TableCompany(ComCode机构代码，UpperComCode上级机构代码)，如何查询出总公司的所有下级机构？(java或者 SQL均可)。你觉得这种思维和设计是否合理？有什么好建议的？答：select t1.* from TableCompany t1, TableCompany t2 Where t1.ComCode = t2.UpperComCode这种设计比较容易让人理解，但是表中的数据联系过于紧密，数据量很大，会给后期维护造成不便，如果根据第三范式要求，将每一子公司独立成一张表，对于关系的维护和数据的管理都会变得比较方便。17、一个简单

13、的论坛系统，以数据库存储如下数据：用户名，发帖标题，发帖内容，回复标题，回复内容。每天论坛访问量 200万左右，更新帖子 10万左右。请给出数据库表结构设计，并结合范式简要说明设计思路。答：用户表：存储用户信息；用户所发的帖子表：存储用户所发的帖子；回复表：存储对帖子所做的回复。设计：User： Create table tb_user( id number(10) primary key, Uname varchar2(20) not null unique ); Comments: Create table tb_comments( id number(10), comments_id n

14、umber(20) not null unique, title varchar2(20) not null, comments varchar2(255) not null, foreign key(id) references tb_user(id) ); Replay: Create table tb_replay( id number(10), comments varchar2(255) not null, foreign key(id) references tb_comments(comments_id) );思路：因为此应用所要存储的数据量比较大，所以为了避免数据的冗余，表的设

15、计依托于第三范式。18、有一个数据表userinfo，包含userid，username 字段，其中userid是唯一的，username可能重复，请写一句 sql查询语句，把重复的记录全部取出来。 userid username 1老王 2老王 3老李4老李 5小张要求返回记录集 userid username 1老王 2老王 3老李 4老李答： select * from userinfo where username in (select username from userinfo group by username having count(username)1);19、建表 Dep

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

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

18、则如下：员工部门分数名次张三 A001 90 1李四 A001 90 1张飞 A002 70 1刘备 A002 60 2答：1） create table Department(depid varchar2(20) primary key, depname varchar2(20), depcj number(10,2); create table Employee(empid varchar2(20) primary key, name varchar2(20), depid varchar2(20), cj number(10,2), xorder number(10);insert in

19、to Department(depid,depname) values(A001,人力资源部);insert into Department(depid,depname) values(A002,财务部);insert into Employee(empid, name, depid, cj) values(001,张三,A001,90);insert into Employee(empid, name, depid, cj) values(002,李四,A001,90);insert into Employee(empid, name, depid, cj) values(003,王五,A0

20、01,80);insert into Employee(empid, name, depid, cj) values(004,张飞,A002,70);insert into Employee(empid, name, depid, cj) values(005,刘备,A002,60);insert into Employee(empid, name, depid, cj) values(006,关羽,A002,50);2）select name,cj from employee where depid=A001;3）select e.empid,e.name,d.depname,e.cj fr

21、om employee e, departmentd where e.depid=d.depid;4）update employee set cj=52 where name=关羽;5） create view VdepEmpMax as (select deptid,max(cj) from employee e group by deptid)6） create or replace procedure SP_Calc as begin update department d set depcj=( select nvl(avg(cj),0) from employee e where e.depid(+)=d.depid); end;7） create or replace procedure SP_Order as begin update