JAVA企业面试题精选 数据库31-40

1.31.两个表情况如下:

表名:wu_plan

ID  plan  model  corp_code  plannum  prixis
1  00001  exx22   nokia    2000   0
2  00002  lc001    sony    3000   0

表名:wu_bom

ID  plan  pact  amount
1  00001  aa1  300
1  00001  aa2  200
3  00002  bb1  500
4  00002  bb2  800
5  00002  bb3  400

  查询这两个表中plan唯一,每一个plan中,amount最少的,plannum大于prixis的记录.结果为:

ID plan model corp_code plannum prixis pact amount
1 00001 exx22  nokia  2000  0   aa2  200
2 00002 lc001   sony  3000  0   bb3  400

参考答案:

  在Oracle数据库中,创建测试表和测试数据,代码如下所示:

CREATE TABLE wu_bom(    id number(20),    plan varchar2(20),    pact varchar2(20),    amount number(11));insert into wu_bom(id,plan,pact,amount) values(1,'00001','aa1',300);insert into wu_bom(id,plan,pact,amount) values(2,'00001','aa2',200);insert into wu_bom(id,plan,pact,amount) values(3,'00002','bb1',500);insert into wu_bom(id,plan,pact,amount) values(4,'00002','bb2',800);insert into wu_bom(id,plan,pact,amount) values(5,'00002','bb3',400);CREATE TABLE wu_plan(    id number(20),    plan varchar2(20),    model varchar2(20),    corp_code varchar2(20),    plannum number(11),    prixis number(11));insert into wu_plan(id,plan,model,corp_code,plannum,prixis) values(1,'00001','exx22','nokia',2000,0);insert into wu_plan(id,plan,model,corp_code,plannum,prixis) values(2,'00002','lc001','sony',3000,0);

  查询这两个表中plan唯一,每一个plan中,amount最少的,plannum大于prixis的记录,SQL语句如下所示:

select A.*,B.pact,B.minamount as amount from wu_plan A,(select plan,max(pact) pact,min(amount) as minamount from wu_bom group by plan) B where A.plan=B.plan and A.plannum>A.prixis;

1.32.数据库方面:

create table tb1 threat(    pk_threat_id      int unsigned not null auto_increment,    dt_log_time       datetime,      //发生时间    i_severity        int,           //严重程度    i_device_id       int,           //设备id    str_tr_type       varchar(64),   //告警类型    str_tr_name       varchar(256),  //类型名称    i_work_id         int unsigned,  //工单id    dt_complete_time  datetime,      //完成时间    i_status          tinyint default 0 comment '0-新分派,1-重新激活,2-完成',//告警状态    primary key (pk_threat_id));create table tb1_work(    pk_work_id        int not null auto_increment,    str_title         varchar(256),  //工单名称    i_owner_org_id    int unsigned,  //负责人组织id    i_owner_id        int,           //负责人id    dt_dispatch_time  datetime,      //派单时间    dt_finish_time    datetime,      //完成时间    i_is_history      tinyint default 0, //是否为历史工单    dt_expect_time    datetime,      //期望完成时间    i_in_time         tinyint(1) default 0, //及时性    i_priority        tinyint(1)     //优先级    primary key (pk_work_id));

  ps:threat为告警,work为工单,i_severity严重程度(0代表一般,1代表低危,2代表中危,3代表高危).

  1.请查出当前日期前三天发生的告警的类型名称,告警类型,发生时间,严重程度及派单时间用日期按照降序排列.

  2.请查出当前日期前三天发生的告警的数量最多的告警类型及数量,数量按降序排列的前5个.

参考答案:

  在MySQL数据库中,创建测试表和测试数据,代码如下所示:

insert into tb1_threat values(2,'20131028',1,1,'s','s',1,'20131029',0);insert into tb1_threat values(3,'20131027',1,1,'s','s',1,'20131029',0);insert into tb1_threat values(4,'20131025',1,1,'s','s',1,'20131029',0);insert into tb1_threat values(5,'20131025',1,1,'r','r',1,'20131029',0);insert into tb1_threat values(6,'20131027',1,1,'r','r',1,'20131029',0);insert into tb1_threat values(7,'20131027',1,1,'r','r',1,'20131029',0);insert into tb1_threat values(8,'20131027',1,1,'r','r',1,'20131029',0);insert into tb1_threat values(9,'20131027',1,1,'r','r',1,'20131029',0);insert into tb1_threat values(10,'20131027',1,1,'y','y',1,'20131029',0);insert into tb1_work values(1,'e',1,1,'20131027','20131027',0,'20131027',0,1);

  1.查出当前日期前三天发生的告警的类型名称,告警类型,发生时间,严重程度及派单时间用日期按照降序排列,SQL语句如下所示:

select str_tr_name,str_tr_type,dt_log_time,i_severity,dt_dispatch_timefrom tb1_threat thread join tb1_work workon work.pk_work_id=thread.i_work_idand(dt_log_time=(CURRENT_DATE-1) or dt_log_time=(CURRENT_DATE-2) or dt_log_time=(CURRENT_DATE-3)order by dt_dispatch_time;

  2.查出当前日期前三天发生的告警的数量最多的告警类型及数量,数量按降序排列的前5个,如下所示:

select * from (select str_tr_type,count(str_tr_type) quantityfrom tb1 threatwhere dt_log_time=(CURRENT_DATE-1) or dt_log_time=(CURRENT_DATE-2) or dt_log_time=(CURRENT_DATE-3)group by str_tr_type) sorder by quantity desclimit 5;

1.33.设有图书管理数据库:

  图书(总编号C(6),分类号C(8),书名C(16),作者C(6),出版单位C(20),单价N(6,2).

  1.检索书价在15元至25元(含15元和25元)之间的图书的书名,作者,书价和分类号,结果按分类号生序排列.

  2.为图书表建立一个视图.

参考答案:

  在Oracle数据库中,创建测试表和测试数据,代码如下所示:

create or replace view v bookasselect * from book;

1.34.写一个oracle函数,输入参数(字符串str,整型len,字符c)返回字符串rstr

要求:如果字符串str的长度小于len,则返回的字符串rstr为在字符串str前填充字符c达到长度为len的字符串.如果字符串str的长度大于等于len,则返回的字符串rstr为str.

参考答案:

  在oracle数据库中创建get_str函数的SQL语句如下所示:

create or replace function get_str(str in varchar2,len in int,c in char) return varchar2 is Result varchar2(1000);v len int;rsrt varchar2(1000);begin    rstr := str;    v_len := length(str);    if v_len < len then        while(v_len < len) loop            rstr := c||rstr;            v len := v len + 1;        end loop;    end if;    Result := rstr;    return(Result);end get_str;

1.35.表结构如下:

1.表名:g_cardapply

字段(字段名/类型/长度):

g_applyno  varchar 8; //申请单号(关键字)

g_applydate bigint  8; //申请日期

g_state   varchar 2; //申请状态

2.表名:g_cardapplydetail

字段(字段名/类型/长度):

g_applyno  varchar 8;//申请单号

g_name   varchar 30;//申请人姓名

g_idcard    varchar 18;//申请人身份证号

g_state    varchar 2;//申请状态

其中,两个表的关联字段为申请单号.详细要求如下:

1.查询身份证号码为440401430103082的申请日期;

2.查询同一个身份证号码有两条以上记录的身份证号码及记录个数;

3.将身份证号码为440401430103082的记录在两个表中的申请状态改为07;

4.删除g_cardapplydetail表中的所有李姓记录.

参考答案:

  在MySQL数据库中,创建测试表和测试数据的SQL语句如下所示:

create table g_cardapply(    g_applyno varchar(8),    g_applydate bigint(8),    g_state varchar(2));create table g_cardapplydetail(    g_applyno varchar(8),    g_name varchar(30),    g_idcard varchar(18),    g_state varchar(2));insert into g_cardapply values(1,'20130909','Y');insert into g_cardapplydetail values(1,'张三','440401430103082','01');insert into g_cardapplydetail values(2,'张三','440401430103082','01');insert into g_cardapplydetail values(2,'李四','440401430103083','02');

  1.查询身份证号码为440401430103082的申请日期,SQL语句如下所示:

select apply.g_applydatefrom g_cardapply apply join g_cardapplydetail detailon apply.g_applyno=detail.g_applyno and detail.g_idcard='440401430103082';

  2.查询同一个身份证号码有两条以上记录的身份证号码及记录个数,SQL语句如下所示:

select g_idcard,count(g_idcard) as xcount from g_cardapplydetailGroup by g_idcard having count(g_idcard)>=2;

  3.将身份证号码为440401430103082的记录在两个表中的申请状态改为07,更新g_cardapplydetail表的SQL语句如下所示:

update g_cardapplydetail set g_state='07' where g_idcard='440401430103082';

  更新g_cardapply表的SQL语句如下所示:

update g_cardapply set g_state='07';where g_applyno in(select g_applynofrom g_cardapplydetail where g_idcard='440401430103082');

  4.删除g_cardapplydetail表中的所有李姓记录,SQL语句如下所示:

delete from g_cardapplydetail where g_name like '李%';

1.36.参见如下表结构回答问题

出版社:

出版社代码 char(2),

出版社名称 varchar2(32)

图书:

图书编号 char(8),

图书名称 varchar2(128),

出版社代码 char(2),

作者代号 char(4),

图书简介 varchar2(128)

作者:

作者代号 char(4),

作者名称 varchar2(10),

性别 char(1),

年龄 number(3),

文学方向 varchar2(64)

获奖名单:

获奖日期 date,

获奖人员 char(4)

  详细要求如下:

  1.编写SQL语句,找出”作者”库中没有出现在”获奖名单”库中所有作者信息的SQL语句,要求使用not in,not exists以及外关联三种方法,并说明哪种方法最优.

  2.”获奖名单”表,写出SQL语句,查询在上一个月获奖的人员.

参考答案:

  在oracle数据库中,创建测试表和测试数据:

create table author(    aid char(4),    aname char(10),    sex char(1));insert into author values('1','John','1');insert into author values('2','Karl','0');insert into author values('3','Foul','0');insert into author values('4','Jell','1');create table award(    adate date,    aid char(4));insert into award values(sysdate,'1');insert into award values((sysdate-1),'4');

  1.当数据量比较大时not exists的方式效率较高.
  使用not in的SQL语句写法如下所示:

select  * from author where aid not in (select aid from award);

  使用not exists的SQL语句写法如下所示:

select * from author a where not exists (select 1 from award b where b.aid=a.aid);

  使用外联的SQL语句写法如下所示:

select a.*from author a left join award b on a.aid=b.aid where b.aid is null;

  2.查询出在上一个月获奖的人员的SQL语句如下所示:

select a.aname from award w join author a on a.aid=w.aidwhere to_char(adate,'yyyy-mm')=to_char(add_months(sysdate,-1),'yyyy-mm');

1.37.某公司信息管理系统的需求分析和部分关系模式的结果描述如下:

  1.公司有多个部门,每个部门有一名负责人,一间办公室,一部电话,多名职员,每个职员最多属于一个部门,负责人也是公司一名职员.

  2.数据库的部分关系模式设计如下:

   职员(职员号,职工姓名,月工资,部门号,办公室,电话)

   部门(部门号,部门名,负责人代码,任职时间)

  请回答下述问题:

  1.根据上述说明,请分别给出”职员”和”部门”关系模式的主键及外键.

  2.请编写SQL语句,针对人数大于等于10的部门创建视图D_View(Dept,D_num,D_Avgpay),其中,Dept为部门号,D_num为部门人数,D_Avgpay为平均工资.

  3.目前的”职员”关系模式存在什么问题?在不增加新关系模式的前提下,请给出修改后的”职员”和”部门”关系模式.

参考答案:

  职员表的职员号作为主键,部门表的部门号作为主键,职员表的部门号作为外键.
  针对人数大于等于10的部门创建视图D_View的SQL语句如下所示:

create or replace view D_Viewasselect部门号as dept,count(职员号) as d_num,avg(月工资) as D_Avgpay from 职员表 group by 部门号 having count(职员号)>=10;

  3.无法从职员表中查询出职员的领导和任职时间,修改后的关系模型如下:
  职员(职员号,职工姓名,月工资,部门号,办公室,电话,负责人代码,任职时间)
  部门(部门号,部门名)

1.38.请看如下数据库操作要求:

  1.建一个员工信息表employee,表中id(员工代码),sex(员工性别),name(姓名),department(部门代码),address(地址),birthday(生日),postcode(邮编),salary(薪水),workdate(入职时间),remark(备注信息),其中postcode,remark可以为空,薪水需为number类型,生日,入职日期为date类型.以员工代码作为主键.
  2.插入两条记录,id分别为0023,1023,其余信息自己编造.
  3.查询员工总数,薪水总额.
  4.查询出各部门的最小年龄,最大年龄.
  5.创建入职日期(workdate)索引employee_idx.
  6.修改id为0023的员工的入职日期为2007-12-31.
  7.删除id为1023的员工信息.
  8.使前面所做的修改,删除生效(假定数据库设置不是自动生效).
  9.假定还有一表结构和employee完全一样的表employee_bak,把employee表的数据完全导入employee_bak表.
  10.假设还有一表duty,其记录为员工的级别level,也是以员工id为主键,根据表employee,duty查询出级别在10级以上的所有员工详细信息.
  11.不区分部门查询出入职日期最早的10位员工信息.
  12.删除索引employee_idx.

参考答案:

  1.创建employee表的SQL语句如下:

create table employee(    id number(10) primary key,    sex char(2) not null,    name varchar2(20) not null,    deptmentid number(4) not null,    address varchar2(50) not null,    birthdate date not null,    postcode number(6),    salary number(10,2) not null,    workdate date not null,    remark varchar2(100));

  2.向employee表中插入测试数据的SQL语句如下所示:

insert into employee(id,sex,name,deptmentid,address,birthdate,postcode,salary,workdate,remark) values(0023,'女','黄蓉',12,'北京市',to_date('1985-12-1','yyyy-mm-dd'),100000,2345,to_date('2013-6-1','yyyy-mm-dd','共产党好');insert into employee(id,sex,name,deptmentid,address,birthdate,postcode,salary,workdate,remark) values(1023,'男','郭靖',12,'北京市',to_date('1985-12-1','yyyy-mm-dd'),100000,5345,to_date('2013-7-1','yyyy-mm-dd','共产党好');

  3.查询员工总数,薪水总额的SQL语句如下所示:

select count(*),sum(salary) from employee;

  4.查询出各部门的最小年龄,最大年龄的SQL语句如下所示:

select min(birthdate),max(birthdate) from employee;

  5.创建入职日期(workdate)索引employee_idx的SQL语句如下所示:

create index employee_idx on employee(workdate);

  6.修改id为0023的员工的入职日期为2007-12-31的SQL语句如下所示:

update employee set workdate=to_date('2007-12-31','yyyy-mm-dd') where id=0023;

  7.删除id为1023的员工信息的SQL语句如下所示:

delete from employee where id=1023;

  8.使前面所做的修改,删除生效,SQL语句如下所示:

commit;

  9.把employee表的数据完全导入employee_bak表,SQL语句如下所示:

create table employee_bakasselect * from employee;

  10.根据employee,duty查询出级别在10级以上的所有员工详细信息,SQL语句如下所示:

select * from employee e join duty d on e.id=d.id and level>10;

  11.不区分部门查询入职日期最早的10位员工信息,SQL语句如下所示:

select * from (select * from employee order by workdate)where rownum<=10;

  12.删除索引employee_idx,SQL语句如下所示:

drop index employee_idx;

1.39.有一表格(T_user)有如下数据:

Id  name  age  gender
- - - - - - - - - - - - - - - - - - - - - - - - - 
1  张三   20   男
2  李四   22   男
3  张三   20   男
4  王五   21   男
5  王五   20   男

请用SQL查询出姓名相同而且年龄也相同的人员姓名.

参考答案:

  在oracle数据库中,创建测试表和测试数据,代码如下所示:

create table t_user(    id number,    name varchar2(20),    age number(3),    gender char(2));insert into t_user values(1,'张三',20,'男');insert into t_user values(2,'李四',22,'男');insert into t_user values(3,'张三',20,'男');insert into t_user values(4,'王五',20,'男');insert into t_user values(5,'王五',21,'男');

  查询出姓名相同而且年龄也相同的人员姓名,SQL语句如下所示:

select distinct a.name from t_user a join t_user b on a.name=b.name and a.age=b.age and a.id<>b.id;

1.40.下面是某班级进行期末考试的相关数据表:

学生信息表(T_Student)

学生ID 学生姓名 1 小明 2 小刚 3 小红

课程信息表(T_Course)

课程编号 课程名称 A 语文 B 数学 C 英语

考试成绩表(T_Score)

学生ID 课程编号 分数 1 A 82 1 C 95 2 A 54 2 C 62

  1.按照区间对学生成绩进行优良评级,>=85对应”优”;>=75并且<85对应”良”;>=60并且<75对应”及格”,<60对应”不及格”(输出列:学生姓名,课程名称,成绩级别)

  2.找出本次考试平均分高于70的学生姓名(输出列:学生姓名)

参考答案:

  在Oracle数据库中,创建测试表和测试数据,代码如下所示:

create table t_student(    stu_id number primary key,    name varchar2(20));insert into t_student values(1,'小明');insert into t_student values(2,'小刚');insert into t_student values(3,'小红');create table t_course(    course_id varchar2(20) primary key,    name varchar2(20));insert into t_course values('A','语文');insert into t_course values('B','数学');insert into t_course values('C','英语');create table t_score(    stu_id number references t student(stu_id),    course_id carchar2(20) references t_course(course_id),    score number(5,2));insert into t_score values(1,'A',82);insert into t_score values(1,'C',95);insert into t_score values(2,'A',54);insert into t_score values(2,'C',62);

  1.按照区间对学生成绩进行优良评级,>=85对应”优”;>=75并且<85对应”良”;>=60并且<75对应”及格”,<60对应”不及格”,SQL语句如下所示:

select stu.name,cour.name,    case when sc.score>=85 then '优'         when sc.score>=75 and sc.score<85 then '良'         when sc.score>=60 and sc.score<75 then '及格'         when sc.score<60 then '不及格' end    as "成绩级别"from t_student stujoin t_score scon stu.stu_id=sc.stu_idjoin t_course couron sc.courese_id=cour.course_id;

  2.找出本次考试平均分高于70的学生姓名,SQL语句如下所示:

select stu.namefrom t_student stujoin t_score scon stu.stu_id=sc.stu_idgroup by stu.namehaving avg(sc.score)>70;