数据库面试题4课案.docx
《数据库面试题4课案.docx》由会员分享，可在线阅读，更多相关《数据库面试题4课案.docx（32页珍藏版）》请在冰点文库上搜索。
数据库面试题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）使用嵌套语句查询不选修课程编号为‘C5’的学员姓名和所属单位；
5）查询选修课程超过5门的学员学号和所属单位；
解答：
1）selectcount（SN）,avg（SA）fromSwhereSD='技术一部';
2）updateSsetSN='Mike'whereS#='S#1';
3）selectSN,SDfromSwhereS#=（selectS#fromSCwhereC#='C2'）;
4）selectSN,SDfromSwhereS#notin（selectS#fromSCwhereC#='C5'）;
5）selectS#,SDfromSwhereS#=
（selectS#fromSCgroupbyS#havingcount（S#）>=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排序
解答：
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
ID*NameAddress
………
*主键
表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
结果为
Idname
1a
2b
写出sql语句。
解答：
selectrownumasid,namefrom（selectdistinctnamefromtemp）;
9、已知原表（t_salary）
yearsalary
20001000
20012000
20023000
20034000
先要实现显示结果（salary为以前的工资和）
yearsalary
20001000
20013000
20026000
写出sql语句。
解答：
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（‘A’,’甲’,2）;
Insertintotb_order（customer,product_name,quantity）values（‘B’,’乙’,5）;
2）selectcustomer"购物人",
sum（decode（product_name,'甲',quantity,0））"商品甲",
sum（decode（product_name,'乙',quantity,0））"商品乙",
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
字段（字段名/类型/长度）:
applynovarchar8;//申请单号（关键字）
namevarchar30;//申请人姓名
idcardvarchar18;//申请人身份证号
statevarchar2;//申请状态
其中，两个表的关联字段为申请单号。
题目:
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）,
commentsvarchar2（255）notnull,
foreignkey（id）referencestb_comments（comments_id）
）;
思路：
因为此应用所要存储的数据量比较大，所以为了避免数据的冗余，表的设计依托于第三范式。
18、有一个数据表userinfo，包含userid，username字段，其中userid是唯一的，username可能
重复，请写一句sql查询语句，把重复的记录全部取出来。
useridusername
1老王
2老王
3老李
4老李
5小张
要求返回记录集
useridusername
1老王
2老王
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','人力资源部'）;
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）selectname,cjfromemployeewheredepid='A001';
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）;
end;
7）
createorreplaceprocedureSP_Order
as
begin
updat
升级会员 