数据库面试题4Word文档下载推荐.docx
《数据库面试题4Word文档下载推荐.docx》由会员分享，可在线阅读，更多相关《数据库面试题4Word文档下载推荐.docx（29页珍藏版）》请在冰豆网上搜索。
005周学生Female
………
教师表：
teacher_t
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）统计所有学生、所有课程和所有任课老师的对应关系；
1）selectsc.student_id,count（c.credit）
fromstudents_ts,course_tc,student_course_tsc
wheres.id=sc.student_idandc.id=sc.course_idgroupby
sc.student_idorderbycount（c.credit）;
2）selects.nameass_name,c.nameasc_name,t.nameast_name
fromstudents_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）与2）相同
3.假设有以下的两个表：
Cus_A
ID*NameAddress
Cus_B
*主键
表Cus_A和表Cus_B的结构完全相同，表Cus_A和表Cus_B中既存在ID相同的记录，也存在ID不
同的记录。
现要求将ID只存在于表表Cus_A中而不存在于表Cus_B中的记录全部插入到Cus_B表中，
并用表Cus_A中的记录更新Cus_B中相同的ID的记录，请写出完成这一功能的存储过程。
createorreplaceproceduretest
is
cust_recordcus_a%rowtype;
cursorcust_cursorisselectid,name,addressfromcus_a;
Begin
Opencust_cursor;
LOOP
Fetchcust_cursorintocust_record;
EXITWHENcust_cursor%NOTFOUND;
--先删除在插入
deletefromcus_bwhereid=cust_record.id;
insertintocus_bvalues（cust_record.id,cust_record.name,cust_record.address）;
ENDLOOP;
end;
4、已有“成绩”如下表所示：
学号课程号分数
S1C180
S1C275
S2C1null
S2C255
S3C390
1）执行SQL语句：
SelectCount（学号）From成绩Where分数〉60
后的结果是什么？
2）请写出SQL语句来进行查询“成绩”表中学号为S1、课程号为C2的学号和分数
1）统计分数超过60的学生总数。
2）select学号，分数from成绩where学号=‘S1’and课程号=‘C2’;
5.SAL是Product表中的索引列，请优化如下SQL语句，并简述原因。
原语句：
SELECT*
FROMProduct
WHERESAL*12〉25000；
Select*fromproductwheresal>
（25000/12）;
理由：
WHERE子句中，如果索引列是函数的一部分．优化器将不使用索引而使用全表扫描．
6.有一张表，字段有用户名、口令及备注，请用SQL选择出用户名和口令完全相同的记录（应包括用
户名和数量的出现次数）
T_USER（USER_NAME,PASSWORD）
显示
USER_NAMECOUNT（*）
QWE4
WER5
selectuser_name,count（*）fromt_usergroupbyuser_name,password;
7.有一张表，T_MONEY，字段有ID，FEE，请用SQL语言选择出FEE值为前三条记录。
T_MONEY（ID，FEE）
IDFEE
2100
190
280
SelectId,feefrom（Selectid,feefromt_moneyorderbyfeedesc）whererownum<
=3;
8、table_nametemp
Idname
1a
2b
3a
4a
结果为
2b
写出sql语句。
selectrownumasid,namefrom（selectdistinctnamefromtemp）;
9、已知原表（t_salary）
yearsalary
20001000
20012000
20023000
20034000
先要实现显示结果（salary为以前的工资和）
20013000
20026000
selectt.year,sum（t.salary）over（orderbyt.year）assum_salaryfromsalary_tt;
10.有两个表A和B，均有key和value两个字段，如果B的key在A中也有，就把B的value换为A
中对应的value
这道题的SQL语句怎么写？
解答:
mergeintoAa
usingBb
on（a.key=b.key）
whenmatchedthen
updateset
a.value=b.value
11.创建一张数据表，并插入如下数据。
购物人商品名称数量
A甲2
B乙4
C丙1
A乙2
B丙5
1）写出创建表和插入内容的sql语句
2）写出sql语句使其产生如下结果
购物人商品甲商品乙商品丙
A22Null
BNull45
CNullNull1
createtabletb_order（
customervarchar2（20）,
product_namevarchar2（20）,
quantitynumber
（2）
）
Insertintotb_order（customer,product_name,quantity）values（‘A’,’甲’,2）;
Insertintotb_order（customer,product_name,quantity）values（‘B’,’乙’,4）;
Insertintotb_order（customer,product_name,quantity）values（‘C’,’丙’,1）;
Insertintotb_order（customer,product_name,quantity）values（‘B’,’乙’,5）;
2）selectcustomer"
购物人"
sum（decode（product_name,'
甲'
quantity,0））"
商品甲"
乙'
商品乙"
丙'
商品丙"
fromtb_order
groupbycustomer;
12.有如下两张表：
部门表和职员表，每个职员都属于一个部门，表结构如下：
Dept表
DeptnoDeptname
……
Emp表
EmpnoEmpnameDeptno
请使用SQL语句查询每个部门有多少职员，要求查询结果包含两例（部门名称，人数）？
selectd.deptname,count（*）fromdeptd,empewhered.deptno=e.deptno
groupbyd.deptno,d.deptname;
13.业务场景：
存在下面的表及记录
GOODS（进货表）
GOODSID（主键）GOODSNAMEMEMO
1青霉素
2西瓜霜
3创可贴
4西洋参
SU（进货表）
GOODSID（主键）SUQTY
160
270
SA（销售表）
GOODSID（主键）SAQTY
380
490
要求一：
进货记录，给出SQL达到以下结果
GOODSID（主键）GOODSNAMESUQTY
1青霉素60
2西瓜霜70
3创可贴0
4西洋参0
要求二：
进销对比，给出SQL达到以下结果
GOODSID（主键）GOODSNAMESUQTYSAQTY
1青霉素600
2西瓜霜7070
3创可贴080
要求三：
将GOODS.MEMO更新为[进货数量SU.SUQTY]
1）selectg.goodsid,g.goodsname,s.quqtyfromgoodsginnerjoinsusong.goodsid=s.goodsid;
2）selectg.goodsid,g.goodsname,s.quqty,a.saqtyfromgoodsg,sus,saaon
g.goodsid=s.goodsidandg.goodsid=a.goodsid;
3）updategoodssetdemo=（selects.suqtyfromsuswheres.goodsId=goods.goodsId）
14.表结构:
1）表名:
apply
字段（字段名/类型/长度）:
applynovarchar8;
//申请单号（关键字）
applydatebigint8;
//申请日期
statevarchar2;
//申请状态
2）表名:
applydetail
namevarchar30;
//申请人姓名
idcardvarchar18;
//申请人身份证号
其中，两个表的关联字段为申请单号。
题目:
1）查询身份证号码为440401*********的申请日期
2）查询同一个身份证号码有两条以上记录的身份证号码及记录个数
3）删除applydetail表中所有姓李的记录
1）Selectapplydatefromapplyajoinapplydetaildona.applyno=d.applynoand
Idcard='
440401430103082'
2）selectidcard,count（*）fromapplydetailgroupbyidcardhavingcount（*）>
2;
3）deletefromapplydetailwherename='
李%'
15.在system方案中建立表table1,表中包含如下字段
字段名称数据类型要求
nameVarchar2非空
idNumber非空
ageNumber
sexVarchar2
salaryNumber
Createtablesystem.tablel1（
Idnumbernotnull,
Namevarchar（8）notnull,
Agenumber,
Sexvarchar
（2）,
Salarynumber
16、某公司的机构结构为树型结构，对应的表结构为TableCompany（ComCode—机构代码，
UpperComCode—上级机构代码），如何查询出总公司的所有下级机构？
（java或者SQL均可）。
你觉得
这种思维和设计是否合理？
有什么好建议的？
答：
selectt1.*fromTableCompanyt1,TableCompanyt2
Wheret1.ComCode=t2.UpperComCode
这种设计比较容易让人理解，但是表中的数据联系过于紧密，数据量很大，会给后期维护造成不
便，如果根据第三范式要求，将每一子公司独立成一张表，对于关系的维护和数据的管理都会变得比
较方便。
17、一个简单的论坛系统，以数据库存储如下数据：
用户名，发帖标题，发帖内容，回复标题，回复内容。
每天论坛访问量200万左右，更新帖子10万左右。
请给出数据库表结构设计，并结合范式简要说明设计思路。
用户表：
存储用户信息；
用户所发的帖子表：
存储用户所发的帖子；
回复表：
存储对帖子所做的回复。
设计：
User：
Createtabletb_user（
idnumber（10）primarykey,
Unamevarchar2（20）notnullunique
Comments:
Createtabletb_comments（
idnumber（10）,
comments_idnumber（20）notnullunique,
titlevarchar2（20）notnull,
commentsvarchar2（255）notnull,
foreignkey（id）referencestb_user（id）
Replay:
Createtabletb_replay（
idnumber（10）,
foreignkey（id）referencestb_comments（comments_id）
思路：
因为此应用所要存储的数据量比较大，所以为了避免数据的冗余，表的设计依托于第三范式。
18、有一个数据表userinfo，包含userid，username字段，其中userid是唯一的，username可能
重复，请写一句sql查询语句，把重复的记录全部取出来。
useridusername
1老王
2老王
3老李
4老李
5小张
要求返回记录集
3老李
4老李
select*fromuserinfowhereusernamein（selectusernamefromuserinfogroupbyusername
havingcount（username）>
1）;
19、建表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
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'
'
人力资源部'
A002'
财务部'
insertintoEmployee（empid,name,depid,cj）values（'
001'
张三'
90）;
002'
李四'
003'
王五'
80）;
004'
张飞'
70）;
005'
刘备'
60）;
006'
关羽'
50）;
2）selectname,cjfromemployeewheredepid='
3）selecte.empid,e.name,d.depname,e.cjfromemployeee,departmentdwheree.depid=d.depid;
4）updateemployeesetcj=52wherename='
5）
createviewVdepEmpMaxas（selectdeptid,max（cj）fromemployeeegroupbydeptid）
6）
createorreplaceprocedureSP_Calc
as
begin
updatedepartmentdsetdepcj=（
selectnvl（avg（cj）,0）fromemployeee
wheree.depid（+）=d.depid）;
7）
cr
升级会员 