你知道为什么考你这个？——Java面试题及答案汇总分析（6）

10.数据库相关

1）创建数据库表T_STUDENT，分别存储学号（XH），姓名（NAME），年龄（AGE），性别（SEX），班级（CLASS），成绩（SCORE）。其中学号作为主键，姓名，年龄不能为空，班级外键于T_CLASS表。

T_CLASS表

班级ID（ID）	班级名称（CNAME）
01	一年级一班
02	一年级二班
……	……

2）为表T_STUDENT的成绩字段创建索引。

3）从表T_STUDENT中查询出每个班级中成绩最高的学生的详细信息，并按照T_CLASS中的班级顺序排序。

4）为一年级一班的学生信息创建一张视图，包括T_STUDENT和T_CLASS表中的所有字段信息。

5）写一存储过程，计算指定学生在所班级的平均成绩，并写一段JAVA代码调用此存储过程，获取指定学生所在班级的平均成绩。

6、创建一个触发器，每次修改一个学生的成绩时，都往日志表中记入一条新的记录。日志表结构如图所示。

T_LOG

日志ID（ID）	学生ID（STU_ID）	插入日志时间（TIME）	进行操作（ACTION）
1	3	2009-09-14 09:00:00	insert
2	4	2009-09-14 16:10:00	update
……	……	……	……

日志ID SEQUENCE为S_LOG_ID

参考答案：

1）CREATE TABLE T_STUDENT(
       XH NUMBER(11) primary key,
       NAME VARCHAR2(255) not null,
       AGE NUMBER(2) not null,
       SEX NUMBER(1),
       CLASS NUMBER(4) references T_CLASS(ID),
       SCORE NUMBER(4)
  )

或者

CREATE TABLE T_STUDENT(
       XH NUMBER(11) ,
       NAME VARCHAR2(255) not null,
       AGE NUMBER(2) not null,
       SEX NUMBER(1),
       CLASS NUMBER(4),
       SCORE NUMBER(4),
  constraint PK_1 primary key(XH),
  constraint FK_1 foreign key(CLASS) references T_CLASS(ID)
  ) ;

2) CREATE INDEX index_stu_1 on T_STUDENT(SCORE)

3) SELECT * FROM T_STUDENT T1,

(SELECT MAX(score) score,class FROM T_STUDENT GROUP BY class) t2
WHERE t1.class = t2.class and t1.score = t2.score

4) CREATE OR REPLACE VIEW V_CLASS_ONE AS

SELECT * FROM T_STUDENT T1, T_CLASS T2

WHERE T1.CLASS = T2.ID AND T1.CLASS =1

5) 存储过程代码：

CREATE OR REPLACE PROCEDURE GET_AVE(

STU_ID in NUMBER,

AVE_SCORE out NUMBER

)

IS

BEGIN

SELECT AVG(SCORE) INTO AVE_SCORE

FROM T_STUDENT WHERE CLASS = (

SELECT CLASS FROM T_STUDENT WHERE XH = STU_ID

);

END;

JAVA代码：

/**

     * 执行存储过程GET_AVE

     * @param conn 数据库连接

     * @param xh 指定学生的学号

     * @return 该学生所在班级的平均成绩

     */

    public int callProcedure(Connection conn, int xh){

       int aveScore = -1;

       CallableStatement cs = null;

       String call = "CALL GET_AVE(?,?)";

       try {

           cs = conn.prepareCall(call);

           cs.setInt(1, xh);

           cs.registerOutParameter(2, Types.INTEGER);

           boolean succ = cs.execute();

           aveScore = cs.getInt(2);

           cs.close();

           cs = null;

       } catch (SQLException e) {

           e.printStackTrace();

       } finally{

           if(cs != null){

              try {

                  cs.close();

                  cs = null;

              } catch (SQLException e) {

              }

           }

       }

       return aveScore;

    }

6) CREATE OR REPLACE TRIGGER T_STU_LOG AFTER INSERT OR UPDATE OR DELETE

OF SCORE ON T_STUDENT FOR EACH ROW

DECLARE

VAR_ACTION T_LOG.ACTION%TYPE;

BEGIN

IF INSERTING THEN

VAR_ACTION := 'INSERT';

ELSIF UPDATING THEN

VAR_ACTION := 'UPDATE';

ELSIF DELETING THEN

VAR_ACTION := 'DELETE';

END IF;

INSERT INTO T_LOG (LOG_ID, STU_ID, ACTION) VALUES(S_LOG_ID.NEXTVAL,  :new.XH, VAR_ACTION);

END;

分析：

数据库SQL考查，虽然不是Java内容，但是，哪个做开发的不跟数据库打交道！

此题目，考查了从建表、索引、存储过程、触发器的基本语法，借以考察应试者的SQL基础，比较适合考察初入行者。对于工作多年但是较少接触数据库的开发人员来说，其中的某些部分会遗忘也很正常。不过，对于这类基础知识，如果你有不会的地方，还是需要回去补习一下的。

11、ORACLE数据库表Account（ID，Email，Name）以数字类型字段ID为唯一值键，因开发人员失误，插入3条完全一致的数据（223475，test@test.com，test），请尝试使用SQL删除重复数据只保留一条（223475，test@test.com，test）数据。

参考答案：

方法一：新建临时表保存不重复的数据，然后用临时表数据库替换原表数据

CREATE TABLE T_TEMP AS (SELECT DISTINCT* FROM ACCOUNT)；

TRUNCATE TABLE ACCOUNT;

INSERT INTO ACCOUNT SELECT * FROM T_TEMP;

方法二：利用ORACLE数据库记录ROWID值进行删除。

DELETE FROM ACCOUNT WHERE ID=223475 AND ROWID <> (SELECT MAX(ROWID) FROM ACCOUNT WHERE ID = 223475)

分析：方法一是比较容易想到的方法，但是对于海量数据的表就不适用了，而且效率确实不高。方法二利用ORACLE的ROWID来删除，此方法应该算是比较效率的一种，其中max也可以改为min函数，主要是确定唯一一条记录。

本题目是今天上午去搜狐笔试的笔试题，实话说，笔者没有完全答对，虽然考虑用rowid来解决，但是因为没法测试导致写的答案有些疏漏。

笔者以为，此题目考察比较适合工作多年的同志，如果工作中没接触过，仅凭应试前重新复习一下SQL基础，可能一时想不起如何下手，因此也是出题比较巧妙的一种，如果能用几道这种实际工作中才能遇到的问题联合考察，我相信对于考察一个应试者的经验，以及解决问题能力还是很有参考价值的，至少比起那些纯粹考察基本语法的题目要高明的多。

个人小补充：今天去搜狐笔试的所有题目，个人以为此题目出的最有水平！