数据库面试题

1、数据库面试题四数据库写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# f

3、rom SC where C#='C5');5) select S#,SD from S where S#=(select S# from SC group by S# havi ng coun t(S#)>=5);2请根据以下四张表(其中 course_t表的teacher_id字段是teacher_t表的id字段的外键引 用)，拼写出相应的sql语句(oracle语法)。(15分)学生表：stude nts_tid n ame sex001赵学生Male002钱学生Male003孙学生Male004李学生Female005周学生Female教师表：teacher_id

4、 n ame sex001吴老师Male002郑老师Male003王老师Male004刘老师Female005张老师Female课程表：courseid n ame credit teacher_id001 语文 3 001002 数学 3 002003 英语 4 003004 物理 3 004005 化学 2 005006 政治 1 001007 生物 1 005008计算机 2 005选课表：stude nt_course_t id stude nt_id course_id001 001 001002 001 002003 001 003004 002 001005 002 0071）统

5、计每个学生选修的学分，并按学分降序排序2）统计每个学生选修的所有课程和对应的任课老师；并按学生Id和课程Id排序3）统计所有学生、所有课程和所有任课老师的对应关系；并按学生Id和课程Id排序解答：1) select sc.student_id,count(c.credit)from stude nts_t s, course_t c, stude nt_course_t scwhere s.id=sc.stude nt_id and c.id=sc.course_id group bysc.stude nt_id order by coun t(c.credit);2) select s.n

6、ame as s_n ame,c. name as c_n ame ,t. name as t_n amefrom stude nts_t s, course_t c, stude nt_course_t sc,teacher_t twhere s.id=sc.stude nt_id and c.id=sc.course_id and t.id=c.teacher_id order by s.id,c.id;3) 与2)相同3. 假设有以下的两个表：Cus_AID* Name AddressCus_BID* Name Address*主键表Cus_A和表 Cus_B的结构完全相同，表 Cus_

7、A和表 Cus_B中既存在ID相同的记录,也存在ID不同的记录。现要求将ID只存在于表表 Cus_A中而不存在于表 Cus_B中的记录全部插入到Cus_B表中，并用表Cus_A中的记录更新 Cus_B中相同的ID的记录，请写出完成这一功能的存储过程。 解答：create or replace procedure testiscust_record cus_a%rowtype ;cursor cust_cursor is select id, name,address from cus_a;BeginOpen cust_cursor;LOOPFetch cust_cursor into cust

8、_record;EXIT WHEN cust_cursor %NOTFOUND;-先删除在插入delete from cus_b where id=cust_record.id;insert into cus_b values(cust_record.id, cust_record .n ame, cust_record.address);END LOOP;en d;4、已有“成绩”如下表所示：学号课程号分数S1C1 8051 C2 7552 C1 n ull52 C2 5553 C3 901) 执行SQL语句：Select Count (学号)From 成绩 Where 分数60后的结果是什

9、么？2) 请写出SQL语句来进行查询“成绩”表中学号为 S1、课程号为 C2的学号和分数解答：1) 统计分数超过 60的学生总数。2) select学号，分数 from 成绩 where学号= S1' and课程号='C2'5.SAL是Product表中的索引列，请优化如下SQL语句，并简述原因。原语句：SELECT*FROM ProductWHERE SAL * 12 > 25000;解答：Select * from product where sal>(25000/12);理由：WHERE子句中，如果索引列是函数的一部分.优化器将不使用索引而使用全表扫描

10、.6有一张表，字段有用户名、口令及备注，请用SQL选择出用户名和口令完全相同的记录(应包括用户名和数量的出现次数)T_USER(USER_NAME,PASSWORD)显示USER_NAME COUNT(*)QWE 4WER 5解答：select user_ name,co un t(*) from t_user group by user_ name,password;7有一张表，T_MONEY，字段有ID , FEE，请用SQL语言选择出 FEE值为前三条记录。T_MONEY(ID , FEE)显示ID FEE2 1001 902 80Select Id,fee from (Select i

11、d,fee from t_money order by fee desc) where row num <=3;8、table_name tempId n ame1 a2 b3 a4 a结果为Id n ame1 a2 b写出sql语句。解答：select row num as id , n ame from(select disti net n ame from temp);9、已知原表(t_salary)year salary2000 10002001 20002002 30002003 4000先要实现显示结果(salary为以前的工资和)year salary2000 100020

12、01 30002002 6000写出sql语句。解答：select t.year, sum(t.salary) over (order by t.year) as sum_salary from salary_t t;10有两个表 A和B，均有key和value两个字段，如果B的key在A中也有，就把B的value换为 A中对应的 value这道题的SQL语句怎么写？解答：merge into A ausing B bon (a.key=b.key)whe n matched the nupdate seta.value=b.value11. 创建一张数据表，并插入如下数据。购物人商品名称数量

13、A甲2B乙4C丙11）写出创建表和插入内容的 sql语句2）写出sql语句使其产生如下结果 购物人商品甲商品乙商品丙A 2 2 NullB Null 4 5C Null Null 1解答：create table tb_order( customer varchar2(20), product, name varchar2(20), qua ntity nu mber(2)Insert into tb_order(customer,product_ name,qua ntity)values( Insert into tb_order(customer,product_ name,qua nt

14、ity)values( Insert into tb_order(customer,product_ name,qua ntity)values( Insert into tb_order(customer,product_ name,qua ntity)values( Insert into tb_order(customer,product_ name,qua ntity)values('A ','甲，2);'B','乙',4)；'C','丙'，1);'A ','甲，2);

15、9;B','乙',5)；2) select customer "购物人",sum(decode(product_name,'甲',quantity,O)"商品甲", sum(decode(product_name,'乙',quantity,O)"商品乙", sum(decode(product_name,'丙',quantity,O)"商品丙" from tb_order group by customer;12. 有如下两张表：部门表和职员表

16、，每个职员都属于一个部门，表结构如下:Dept 表Dept no Dept nameEmp表Emp no Empn ame Dept no请使用SQL语句查询每个部门有多少职员，要求查询结果包含两例（部门名称，人数）？ 解答：select d.dept name,co un t（*） from dept d,emp e where d.dept no=e.dept no group by d.dept no ,d.dept name;13. 业务场景：存在下面的表及记录GOODS （进货表）GOODSID （主键）GOODSNAME MEMO1青霉素2西瓜霜3创可贴4西洋参SU （进货表）GO

17、ODSID （主键）SUQTY1 602 70SA （销售表）GOODSID （主键）SAQTY3 804 90要求一：进货记录，给出SQL达到以下结果GOODSID （主键）GOODSNAME SUQTY1青霉素602西瓜霜703创可贴04西洋参0要求二：进销对比，给出SQL达到以下结果GOODSID （主键）GOODSNAME SUQTY SAQTY1青霉素60 02西瓜霜70 703创可贴0 80要求三：将 GOODS.MEMO更新为进货数量SU.SUQTY解答：1）select g.goodsid,g.goods name,s.quqty from goods g inner join

18、 sus on g.goodsid=s.goodsid;2）select g.goodsid,g.goods name,s.quqty,a.saqty from goods g, su s,sa a ong.goodsid=s.goodsid and g.goodsid=a.goodsid;3）update goods set demo=（select s.suqty from su s where s.goodsld=goods.goodsld）14.表结构：1）表名即ply字段（字段名/类型/长度）:applyno varchar 8;/申请单号（关键字）applydate bigint

19、8;/ 申请日期state varchar 2;/ 申请状态2）表名:applydetail字段（字段名/类型/长度）:applyno varchar 8;/申请单号（关键字）name varchar 30;/ 申请人姓名idcard varchar 18;/申请人身份证号state varchar 2;/ 申请状态其中，两个表的关联字段为申请单号。题目：1) 查询身份证号码为440401430103082的申请日期2) 查询同一个身份证号码有两条以上记录的身份证号码及记录个数3) 删除applydetail表中所有姓李的记录解答：1) Select applydate from apply

20、a join applydetail d on a.appl yno=d.appl yno and Idcard='440401430103082'2) select idcard,co un t(*) from applydetail group by idcard hav ing coun t(*)>2;3) delete from applydetail where name='李%'15、在system方案中建立表 table1,表中包含如下字段 字段名称数据类型要求n ame Varchar2 非空id Number 非空age Numberse

21、x Varchar2salary Number解答：Create table system.tablel1 (Id nu mber not nu II,Name varchar(8) not n ull,Age nu mber,Sex varchar(2),Salary nu mber);16、 某公司的机构结构为树型结构，对应的表结构为TableCompa ny(ComCode 机构代码，UpperComCode 上级机构代码)，如何查询出总公司的所有下级机构？(java或者SQL均可)。你觉得这种思维和设计是否合理？有什么好建议的？答：select t1.* from TableCompa

22、ny t1, TableCompany t2Where t1.ComCode = t2.UpperComCode这种设计比较容易让人理解，但是表中的数据联系过于紧密，数据量很大，会给后期维护造成不便，如果根据第三范式要求，将每一子公司独立成一张表，对于关系的维护和数据的管理都会变得比较方便。17、一个简单的论坛系统，以数据库存储如下数据：用户名，发帖标题，发帖内容，回复标题，回复内容。每天论坛访问量 200万左右，更新帖子10万左右。请给出数据库表结构设计，并结合范式简要说明设计思路。答：用户表：存储用户信息；用户所发的帖子表：存储用户所发的帖子； 回复表：存储对帖子所做的回复。设计：User

23、：Create table tb_user(id nu mber(10) primary key,Un ame varchar2(20) not n ull unique);Comme nts:Create table tb_comme nts(id number(10),comme nts_id nu mber(20) not n ull uniq ue,title varchar2(20) not null,comme nts varchar2(255) not n ull,foreig n key(id) referen ces tb_user(id);Replay:Create tab

24、le tb_replay(id number(10),comme nts varchar2(255) not n ull,foreig n key(id) referen ces tb_comme nts(comme nts_id);表的设计依托于第是唯一的，user name思路：因为此应用所要存储的数据量比较大，所以为了避免数据的冗余，三范式。18、有一个数据表 useri nfo，包含 userid, username 字段，其中 userid 可能重复，请写一句sql查询语句，把重复的记录全部取出来。userid user name1老王2老王3老李4老李5小张要求返回记录集useri

25、d user name1老王2老王3老李4老李答：select * from useri nfo where user name in (select user name from useri nfo group by user name hav ing coun t(user name)>1);19、建表 Department 部门字段名中文名称类型长度备注depid部门号变长字符 10主键dep name部门名称变长字符depcj部门平均成绩浮点型保留2位小数表Employee人员表字段名中文名称类型长度备注empid员工号变长字符 10主键name姓名变长字符 10depid部门号

26、变长字符 10Cj成绩浮点型保留 2位小数xorder名次整型实现表中的记录备下面相关题目使用Department表中嵌入记录部门号部门名称A001人力资源部A002财务部Employee表中嵌入记录员工号姓名部门号成绩001 张三 A001 90002 李四 A001 90003 王五 A001 80004 张飞 A002 70005 刘备 A002 60006 关羽 A002 501) 写出建表以及嵌入记录语句2) 显示 A001部门员工的姓名、成绩3) 显示所有员工的员工号、姓名、部门名称、成绩4) 将关羽的成绩修改成52分5) 按要求写视图VdepEmpMax求各部门的最高分，显示部门

27、号、最高分成绩6) 按要求写存储过程SP_Calc求各部门的平均成绩，并更新到Department表depcj字段中7)按要求写存储过程SP_Order求员工的名次，并更新到Employee表xorder字段中8)按要求写视图VdepEmp2，求各部门的前2名，显示部门号、员工号、成绩排序规则如下：员工部门分数名次张三 A001 90 1李四 A001 90 1张飞 A002 70 1刘备 A002 60 2答：1)create table Departme nt(depid varchar2(20) primary key,dep name varchar2(20),depcj number

28、(10,2);create table Employee(empid varchar2(20) primary key,n ame varchar2(20),depid varchar2(20), cj number(10,2),xorder nu mber(10);insert into Department(depid,depname) values('A001','人力资源部');insert into Department(depid,depname) values('A002','财务部');insert into Em

29、ployee(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','王五','A001',80);insert into Employee

30、(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 emp

31、loyee where depid='A001'3) select e.empid,,d.depname,e.cj from 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_Calcasbeg

32、inupdate departme nt d set depcj=(select n vl(avg(cj),0) from employee ewhere e.depid(+)=d.depid);en d;7)create or replace procedure SP_Orderasbeginupdate employee w set xorder =(select b.r n from(select empid,ra nk() over (partiti on by depid order by cj desc ) rn from employee) bwhere w.empid=b.em

33、pid);en d;8)create or replace view VdepEmp2 asselect depid ,n ame,cj,r n from(select e.*,ra nk() over (partiti on by depidorder by cj desc) rn from employee e)where rn<3;20、数据库基础：1) 使用SQL语句创建学生表students字段：学号:s_id姓名:s_name年龄:age班级:class辅导员:assistant (请设计各字段类型 与长度)2) 查询学生表中年龄大于 20的所有学生的学号与姓名3) 删除02

34、01班的所有同学4) 查询0302班姓李的学生的个数5) 将班编号以02'开头的所有班级的辅导员修改为李四答：1) create table students(s_id number(10) primary key,s_n ame varchar(30) not nu II,age nu mber(3) not n ull,class varchar(20) not nu II,assista nt varchar(30);2) select s_id,s_name from students where age>20;3) delete from students where

35、class= ' 0201'4) select count(s_name) from studentswhere s_n ame like '李 %' and class='0302：5) update students set assista门上=李四'where class like '02%：21、表名：高考信息表stude nts_i nfo准考证号科目成绩no subject score2006001语文1192006001数学1082006002物理1422006001化学1362006001物理1272006002数学149

36、2006002英语1102006002语文1052006001英语982006002化学129写出高考总分在600以上的学生准考证号的SQL答：select nofrom stude nts_infogroup by nohaving sum(score)>600 ；22、有一个表 LEANR，表里有三个字段分别是学号(student_id)课程(kc)，成绩(grade)。1) .查询每一门课程的前两名2) .查询以Grade降序排列的第 31至40条记录(不需要区分课程)，并且按照重复次数的降3) .查询表中存在课程重复4次以上的记录，显示课程和重复的次数序排列答：1) .selec

37、t stude nt_id,kc,gradefrom (select stude nt_id,kc,grade,row_nu mber() over(partiti on by kc order by grade desc)r n from LEANR) where rn<=2;2) select stude nt_id,gradefrom (select lea.*,row num rmfrom (select *from LEANRorder by grade desc)leawhere rownum < 41)where rm betwee n 31 and 40;3). s

38、elect kc,co un t(kc)from LEANRgroup by kchav ing coun t(kc)>=2order by coun t(kc) desc;23、a部门表b员工表a表字段(id -部门编号 departmentName-部门名称 )b表字段(id-部门编号 employee-员工名称)问题:如何一条sql语句查询出每个部门共有多少人答：建表语句：create table a(id nu mber primary key,departme ntName varchar(20)；create table b(id nu mber,employee varch

39、ar(20);in sert into a values(1,'部门 1');insert into a values(2,'部门 2');insert into a values(3,'部门 3');in sert into b values(1,'emp1');in sert into b values(1,'emp2');in sert into b values(1,'emp3');in sert into b values(2,'emp4');in sert into b

40、values(2,'emp5');in sert into b values(3,'emp6');select departme ntName,co un t(employee) from a,b where a.id=b.id group by departme ntName;24、为管理岗位业务培训信息，建立3个表：S (SID,SN,SD,SA) SID,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄C (CID,CN ) CID,CN 分别代表课程编号、课程名称SC ( SID,CID,G ) SID,CID,G分别代表学号、所选修的课程编

41、号、学习成绩1. 使用标准 SQL嵌套语句查询选修课程名称为税收基础的学员学号和姓名2. 使用标准SQL嵌套语句查询选修课程编号为02 '的学员姓名和所属单位3. 使用标准SQL嵌套语句查询不选修课程编号为03'的学员姓名和所属单位4. 使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位5. 查询选修课程超过 5门的学员学号和所属单位答：建表sql语句：create table s(sid in t(10) primary key,sn varchar(20) not n ull,sd varchar(20) not n ull,sa int(3) not null);

42、create table c(cid in t(10) primary key,cn varchar(20) not n ull);create table sc(sid in t(10) references s(sid),cid in t(10) references c(cid),g in t(10), primary key(sid,cid);in sert into s values(1,"zha ngsa n","project",25);in sert into s values(2,"lisi","mis&q

43、uot;,26);in sert into s values(3,"wa ngwu","ma nager",27);in sert into s values(4,"zhaoliu","mis",26);insert into c values(01,"税收基础”)；in sert into c values(02,"Core Java");in sert into c values(03,"NetWork");in sert into sc values(1,01

44、,70);insert into sc values(1,02,75);in sert into sc values(1,03,80);in sert into sc values(2,01,80);in sert into sc values(2,03,69);in sert into sc values(3,02,73);1)select s.sid,s.s nfrom s,c,scwhere s.sid=sc.sidand c.cid=sc.cidand ='税收基础'2) select a.s n, a.sdfrom s a, c bwhere b.cid

45、in( select c.cid from sc c where a.sid=c.sid and b.cid=c.cid) and b.cid=02;3) select a.s n, a.sdfrom s a, c bwhere b.cid not in( select c.cid from sc c where a.sid=c.sid and b.cid=c.cid) and b.cid=03;4) select sn,sd from s where sid in(select sid from sc group by sid hav ing coun t(cid)=(select coun

46、 t(cid) from c);5) select sn,sd from swhere sid in( select sid from sc group by sid havi ng coun t(dist inct cid)>5);25、请根据以下要求来完成题目：会议室预定模块：某公司有多个会议室，以房间号区分。如果某部门需要预定会议室，则会 提交预定请求(包含预定开始使用时间、预定结束使用，所预定会议室房间号)。设计一个表，保存会议室预定信息。要求采用SQL语句及JAVA代码段判断在2003-3-10下午3: 004:00 3号会议室是否空闲。 请写出有关SQL语句以及相关 JAVA

47、的代码段。答：1)Sql语句：create table meeti ng(id nu mber primary key ,room_id varchar(10),isUsed char,begi n timestamp,end timestamp);in sert into meet ing values(1,'201',1,to_date('2003-03-10 15:00:00','yyyy-mm-dd hh24:mi:ss'),to_date('2003-03-10 16:00:00','yyyy-mm-dd hh2

48、4:mi:ss');in sert into meet ing values(2,'201',1,to_date('2003-03-10 17:00:00','yyyy-mm-dd hh24:mi:ss'),to_date('2003-03-10 22:00:00','yyyy-mm-dd hh24:mi:ss');2)package com.tare na;public class Test public static void main( Stri ng args) Stri ng user name

49、= "scott"Stri ng pwd = "tiger"Connection con = n ull;Stateme nt stmt = n ull;ResultSet rs = null;try Class.forName(driverName);con = DriverMa nager.getC onn ectio n(url, user name, pwd); stmt = con. createStateme nt();String sql = "select isUsed from " +"meeti ng &

50、quot; +"where (begi n between to_date('2003-03-10 15:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2003-03-10 16:00:00','yyyy-mm-dd hh24:mi:ss') " + "or(e nd between to_date('2003-03-10 15:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('

51、2003-03-10 16:00:00','yyyy-mm-dd hh24:mi:ss')" +"and roomd=201"if (stmt.execute(sql) rs = stmt.getResultSet();Stri ngBuffer sb = new Stri ngBuffer(); while (rs. next() sb.appe nd("isFree:" + rs.get In t(1) + ""); catch (Exceptio n e) e.pri ntStackTrace(

52、); fin ally try con. close(); catch (Exceptio n e1) e1.pri ntStackTrace();26、下面是两个数据库表，分别记录员工姓名和工资T_EMPLOYEEID NAME2张三3李四5王五T_SALARY ID SALARY2 34003 43005 25001.查询表 T_EMPLOYEE 中id = 3的员工记录2查询表T_EMPLOYEE中所有员工记录3联合查询表EMPLOYEE和T_SALARY中所有员工的姓名和工资记录，并按照薪水从 高到低排列答：1) .select * from t_employee where id =

53、 3;2) .select * from t_employee;3) .select e. name,s.salaryfrom t_employee e,t_salary swhere e.id=s.idorder by s.salary;27、有三张表，学生表 S,课程表C,学生课程表SC,学生可以选修多门课程，一门课程可能被多个学生选修，通过SC表关联。1) 写出建表以及插入语句；2) 写出SQL语句，查询选修了所有选修课程的学生；3) 写出SQL语句，查询选修了至少2门以上的课程的学生。答：1)create table stude nt (id nu mber(10) primary k

54、ey ,n ame varchar2(20);create table course (id nu mber(10) primary key ,n ame varchar2(20);create table sc(sid nu mber(10) references stude nt(id),cid nu mber(10) referencescourse(id),grade nu mber(4,2);in sert into stude nt values(1,'feifei');in sert into stude nt values(2,'j ingjin g&#

55、39;);in sert into stude nt values(3,' nannan');insert into stude nt values(4,'yua nyuan');in sert into stude nt values(5,'jiejie');in sert into course values(1,'corejava');in sert into course values(2,'c+');in sert into course values(3,'jdbc');in sert into course values(4,'hiber nate');in sert into sc values(1,1,98);in sert into sc values(2,1,97);in sert into sc values(3,1,94);in sert into sc values(4,1,92);in sert into sc values(5,1,93);in sert into sc values(1,2,94);in sert into sc values(2,2,92); in sert