历年吉大数据库应用技术试卷答案.doc

数据库应用技术2005年标准答案一、判断对错并简单说明原因（12分）1、正确，NULL在运算时可以得到不一样结果2、错误，可能减慢DML语句执行速度3、正确，需要配合完整备份4、正确，不可剥夺，因此可能做危险操作，不安全。二、简单回答以下问题（25分），每小题5分。1、原子性，事务不可分割；一致性，要满足商业规则；隔离性，互不干扰，和串行结果相同；持续性，结果要永久保存2、安全性；提高性能；代码复用性；数据一致性3、R1(A), R2(A), W1(A), COMMIT1, W2(A), COMMIT2。以上3分，也可以使用图形化表示。在可重复读和可串行化级别下可以避免。4、申请语句句柄，准备，绑定参数，绑定结果变量，执行，获取数据，关闭/释放语句句柄。5、C/S结构软件不易分发，升级困难，需要在各个客户端安装B/S结构分发升级容易，只需要在服务器端完成三、共10分1、课程编号为主键，可以唯一标志课程（2分）2、成绩表中，学号和课程号应该使用外键约束（2分）3、CREATE TABLE transcripts(SNO NUMBER(10) NOT NULL REFERENCE students(SNO),LNO CHAR(4) NOT NULL REFERENCE lessons(LNO),SCORE NUMBER(3) NOT NULL CHECK(SCORE BETWEEN 0 AND 100),DEGREE CHAR(2) NULL,PRIMARY KEY(SNO, LNO); 6分，语句基本结构3分（细节错误不扣分），外键，主键，CHECK约束各1分。四、使用一条SQL语句，完成以下功能（20分）参考答案（语句正确即可，答案具体形式不唯一，细节错误一般不扣分）1、3分SELECT SCOREFROM transcripts T, students S, lessons LWHERE T.SNO= S.SNOAND T.LNO= L.LNOAND S.NAME= TomAND L.TEACHER=John;也可以使用子查询完成2、4分SELECT LNOFROM transcriptsWHERE SCORE= (SELECT MAX(SCORE)FROM transcripts WHERE SNO= (SELECT SNO FROM students WHERE SNAME=Tom)AND SNO= (SELECT SNO FROM students WHERE SNAME=Tom)此题不考虑TOM重名问题即可3、4分SELECT SNO, AVG(SCORE)FROM transcriptsWHERE GRADE=2002AND CLASS=1GROUP BY SNO4、3分INSERT INTO LESSONS(LNO, LNAME, TEACHER, CREDIT)VALUES(0005, 数据库, John,2)5、6分CREATE VIEW V_1 ASSELECT T.SNO, SNAME, LNO, SCOREFROM transcripts T, lessons LWHERE T.SNO= L.SNOAND T.GRADE=2002AND T.SEX=女 (4分)此视图不可修改（1分）， 因为包含了两个表的数据（1分）五、写出下面语句的含义：(10分，各5分)1、找出平均成绩大于80的学生的名字，不重复的列出。2、列出学时前5名（从大到小）的课程编号和名称，按学时降序排列。六、15分，基本框架10分，细节5分。不使用游标者最多10分CREATE PROCEDURE P_1(V_GRADE NUMBER, V_CLASS CHAR)ASBEGINDECLAREV_D CHAR(2);CURSOR C_1 ISSELECT SNO, SCOREFROM transcripts TWHERE SNO IN(SELECT SNO FROM studentsWHERES.GRADE= V_GRADEAND S.CLASS= V_CLASS);BEGINFOR REC_1 IN CUR_1 LOOPIF REC_1.SCORE 60 THENV_D= 差;ELSIF REC_1.SCORE 90AND MIN(SCORE)803、DELETE FROM STUDENTS WHERE SNO=1001;DELETE FROM TRANSCRIPTS WHERE SNO=1001;4、GRANT SELECT, UPDATE ON LESSONS TO USER1;REVOKE DELETE ON LESSONS FROM USER1;五、写出下面语句的含义：(15分，各5分)1、列出1001学生的总学分2、将1999级学生考试成绩在5059之间的分数改为60；3、列出年龄最小的前5名同学的学号（允许重复）六：10分，创建视图6分，第二问4分。1、CREATE VIEW V_1 ISSELECT NAME, LNO, SCOREFROM STUDENTS S, LESSONS L, TRANSCRIPTS TWHERE S.SNO= T.SNOANS L.LNO= T.LNO;2、不可修改，因为没有学号这个主键，如果加上则可以修改。七、15分，基本框架10分，细节5分。参考答案略：CREATE FUNCTION AvgOfBest3 (V_SNO NUMBER)ASBEGINDECLAREV_S NUMBER;V_C NUMBER;CURSOR CUR_1 ISSELECT SCOREFROM transcripts TWHERE SNO = V_SNO;BEGINV_S :=0;V_C :=0;FOR REC_1 IN CUR_1 LOOPEXIT WHEN CUR1%ROWCOUNT3;V_S := V_S + REC_1%SCORE;V_C := V_C + 1;END LOOP;AvgOfBest3 : =V_S /V_C;RETURN ;EXCEPTIONWHEN DATA_NOT_FOUNDAvgOfBest3 := NULL;RAISE_APPLICATION_ERROR(20002，学号不存在);END;END;2003级数据库应用技术期末考试试题（B卷）标准答案及评分标准一、判断对错并简单说明原因（12分）每小题4分，判断正确给2分，正确说明原因2分。判断正确回答错误不给分。标准答案1、错误。可以作减法，不能作加法2、正确。需要简要解释隔离级别和并发错误的定义3、正确，需要以完全备份为基础二、简单回答以下问题（20分），每小题4分。标准答案1、原子性，事务不可分割；一致性，要满足商业规则；隔离性，互不干扰，和串行结果相同；持续性，结果要永久保存2、序列是产生唯一性编号的方法。（2分），使用方法包括创建序列，和使用序列的CURR_VAL，NEXT_VAL的例子。4、使用存储过程，使用中间服务器，BS结构5、安全性；提高性能；代码复用性；数据一致性三、共10分1、学号为主键，可以唯一标志学生（2分）2、成绩表中，学号和课程号应该使用外键约束（2分）3、4分，语句基本结构2分（细节错误不扣分），外键，主键，CHECK约束2分。CREATE TABLE transcripts(SNO NUMBER(10) NOT NULL REFERENCE students(SNO),LNO CHAR(4) NOT NULL REFERENCE lessons(LNO),SCORE NUMBER(3) NOT NULL CHECK(SCORE BETWEEN 0 AND 100),DEGREE CHAR(2) NULL,PRIMARY KEY(SNO, LNO); 4、2分CREATE INDEX IDX1 ON STUDENTS(NAME);只要语句正确，不限定答案方式。四、写出完成下列操作的SQL语句（20分）1、SELECT SCOREFROM transcripts T, students S, lessons LWHERE T.SNO= S.SNOAND T.LNO= L.LNOAND S.NAME= TomAND L.LNAME= 数字逻辑;也可以使用子查询完成2、SELECT LNOFROM transcriptsGROUP BY LNOHAVING AVG(SCORE)90AND MIN(SCORE)803、DELETE FROM STUDENTS WHERE SNO=1001;DELETE FROM TRANSCRIPTS WHERE SNO=1001;4、GRANT SELECT, UPDATE ON LESSONS TO USER1;REVOKE DELETE ON LESSONS FROM USER1;五、写出下面语句的含义：(15分，各5分)1、列出1001学生的总学分2、将1999级学生考试成绩在5059之间的分数改为60；3、列出年龄最小的前5名同学的学号（允许重复）六：8分，创建视图5分，第二问3分。1、CREATE VIEW V_1 ISSELECT NAME, LNO, SCOREFROM STUDENTS S, LESSONS L, TRANSCRIPTS TWHERE S.SNO= T.SNOANS L.LNO= T.LNO;2、不可修改，因为没有学号这个主键，如果加上则可以修改。七、15分，基本框架10分，细节5分。参考答案：CREATE FUNCTION AvgOfBest3 (V_SNO NUMBER)ASBEGINDECLAREV_S NUMBER;V_C NUMBER;CURSOR CUR_1 ISSELECT SCOREFROM transcripts TWHERE SNO = V_SNO;BEGINV_S :=0;V_C :=0;FOR REC_1 IN CUR_1 LOOPEXIT WHEN CUR1%ROWCOUNT3;V_S := V_S + REC_1%SCORE;V_C := V_C + 1;END LOOP;AvgOfBest3 : =V_S /V_C;RETURN ;EXCEPTIONWHEN DATA_NOT_FOUNDAvgOfBest3 := NULL;RAISE_APPLICATION_ERROR(20002，学号不存在);END;END;2003级数据库应用技术（A卷）参考答案及评分标准一、（12分）错（1分），SQL是关系完备不是图灵完备，但两者不是一一对应的（1分）。1、 对（1分），备份与恢复是DBA的基本工作，需要经常执行（1分）。2、 错（1分），可以减不可以加（1分）。3、 对（1分），当使用NULL时（1分）。4、 错（1分），不一定，特别会减慢DML执行速度（1分）。5、 对（1分），一次只能插入一行数据（1分）。二、（24分。每题4分）1、 序列是产生唯一性编号的方法。（2分）使用方法包括创建序列、使用序列的CURR_VAL和NEXT_VAL值，给出例子。（2分）2、 降低权限管理工作量；动态权限管理；应用程序可控制；应用程序内置角色（各1分）3、 R1(A), R2(A), W1(A), COMMIT1, W2(A), COMMIT2。（3分，也可以使用图形化表示。）在可重复读和可串行化级别下可以避免。（1分）4、 ODBC会调用其它数据库专用接口。（4分）5、 比较约束和触发器在实现数据库一致性（商业规则）方面的相同点和区别。（4分）相同点：保证数据满足商业规则。能够自动完成不同点：约束比较简单，但只有5种；触发器可以完成比较复杂约束，但性能代价较大6、 C/S结构软件不易分发，升级困难，需要在各个客户端安装。（4分）B/S结构分发升级容易，只需要在服务器端完成。三、（8分）答：主键(LNO,NO) （1分），此组合键唯一且非空（1分）。外键LNO和BNO（2分）。 CREATE TABLE lrds ( LNO NUMBER(10) NOT NULL, NO NUMBER(2) NOT NULL, BNONUMBER(10) NOT NULL, RDATEDATE NULL, PRIMARY KEY(LNO,NO), FOREIGN KEY(LNO) REFERENCES lrms(LNO), FOREIGN KEY(bno) REFERENCES books(BNO) ) （4分。其中语句基本结构2分，细节错误不扣分；外键、主键约束各1分。只要语句正确，不限定答案方式。）四、（25分）1、SELECT SUM(NOR) FROM LRMS L, STUDENTS S WHERE S.SNAME = Tom AND S.SNO = L.SNO;（3分）2、SELECT COUNT(*) FROM BOOKS B, BOOKINFOS BI WHERE BI.ISBN = B.ISBN AND BI.BNAME = Oracle 9i AND B.ISHERE = T;（4分）3、SELECT GRADE, CLASS, SUM(CANLEND - LENDED)/COUNT(*) FROM STUDENTS GROUP BY GRADE, CLASS ORDER BY GRADE DESC, CLASS DESC;（4分）4、UPDATE STUDENTS SET CANLEND = CANLEND +5 WHERE SNAME = Tom；（4分）5、SELECT S1.SNO, S2.SNOFROM STUDENTS S1, STUDENTS S2WHERE S1.BIRTHDATE= S2.BIRTHDATEAND S1.SNO 3000;3、SELECT PID, SUM(QTY_SOCKET)FROM INCOMEGROUP BY PIDHAVING SUM(QTY_SOCKET) 0;4、SELECT PID, PNAMEFROM PRODUCTSWHERE PID NOT IN(SELECT PID FROM SALESWHERE PDATE SYSDATE 300);5、SELECT * FROM INCOMEWHERE INDATE=(SELECT MAX(INDATE) FROM INCOME);6、UPDATE PRODUCTSSET PNAME= UPPER(PNAME);四、写出以下语句的含义（15分，每小题5分）1、答：从PRODUCTS表中删除那些没有进货过，或者现在库存量为零的产品（5分）2、答：创建一个视图，包括每个产品的PID和总成本。（5分）3、答：列出总销售成本最少的前5种产品及其总成本。（5分）五、编写存储过程，完成以下要求（15分）（其中基本框架10分，细节5分。）参考答案：CREATE PROCEDURE MAKE_SALE(APID CHAR, QTY_WILL NUMBER, ASID CHAR)ASBEGINDECLARECURSOR CUR1 IS SELECT INID, QTY_SOCKET, PRICEFROM INCOMEWHERE PID= APIDAND QTY_SOCKET 0ORDER BY INDATE ASC;REST NUMBER;TCOST NUMBER; BEGINSELECT SUM(QTY_SOCKET) INTO RESTFROM INCOME WHERE PID= APID;IF (REST R_1.QTY_SOCKET) THENBEGINUPDATE INCOMESET QTY_SOCKET=0WHERE INID= R_1.INID;TCOST := TCOST + R_1.QTY_SOCKET * R_1.PRICE;QTY_WILL = QTY_WILL R_1.QTY_SOCKET;ENDELSEBEGINUPDATE INCOMESET QTY_SOCKET= QTY_SOCKET - QTY_WILLWHERE INID= R_1.INID;TCOST := TCOST + (R_1.QTY_SOCKET QTY_WILL) * R_1.PRICE;EXIT;END;END LOOP;INSERT INTO SALES(SID, PID, PDATE, QTY_SALE, COST)VALUES(ASID, APID, SYSDATE, QTY_WILL, TCOST);END;END;2009-2010学年第1学期2006级数据库应用技术期末考试试题（A卷）答案和评分标准一、 判断是否正确，错误的给出理由。（共10分，每小题2分。正确命题答对给2分；错误命题判断正确1分，理由正确1分。）1、在PL/SQL中可以使用DQL和DML，不能使用DDL。（正确）2、使用INSERT语句时，如果某个字段没有列出，则使用空值代替。（错误）若该字段不允许为空，INSERT出错。若允许为空，首先采用DEFAULT值，没有才使用NULL。3、使用索引，一定会提高SQL语句的执行速度。（错误）只有当DBMS能够借用索引为SQL提速时才可以。在许多情况下，DBMS无法使用索引，不会提速；对于多数的DML语句，索引又会降低SQL的执行速度，例如INSERT语句和无法使用索引加速记录定位的UPDATE和DELETE语句。4、触发器不能读取、修改正在变化的表。（正确）5、C/S结构中，客户端不直接与数据库服务器连接。（错误）那是B/S结构的特点。二、简答题（25分，每小题5分）1、SQL语言分成哪几类？各举例。答案：SQL语言分为4类：DQL、DML、DDL、DCL。（1分）举例各1分（4分）。2、描述产生“脏写”并发异常的一个事务执行序列，在何种并发隔离级别下能够避免此类异常的产生？答案：R1(A), R2(A), W1(A), W2(A), COMMIT1, COMMIT2。（3分）解释（2分）。3、使用ODBC执行复杂的SQL语句的过程。答案：复杂执行SQL的过程如下：申请语句句柄、准备SQL、参数绑定、列绑定、执行、循环取结果集、释放/关闭语句句柄。4、什么是游标？举例简单说明使用显式游标的过程。答案：游标：用来处理从数据库中查询一组数据的机制。（2分）简单举例，包括声明、open、fetch、close。5、DBA的职责是什么？举例5个即可。答案：初始化任务：数据库运行软硬件选择、数据库前后台软件安装、数据库建立、协助数据库逻辑设计、实现数据库物理设计。日常任务：维护数据库实例的运行和关闭、维护数据库对象、维护数据库安全（权限和对象）、分析、调整数据库性能、数据库审计、日常备份。三、1、写出建立借阅信息主表borrow的DDL语句。CREATE TABLE borrow( brwno NUMBER(8) PRIMARY KEY, rdno CHAR(8) NOT NULL REFERENCES readers(rdno), brw_date DATE NOT NULL, brw_num NUMBER(2) NOT NULL CHECK (brw_num 0), nrtn_num NUMBER(2) DEFAULT 0 NOT NULL CHECK (nrtn_num = 0);2、为图书基本信息表books中图书名称bname建立名为idx_books_bname索引。CREATE INDEX idx_books_bname ON books (bname);3、将图书证号为20070925的读者的借阅天数增加30天。UPDATE readersSET dnum = dnum + 30WHERE rdno = 20070925;4、增加一个可供借阅的isbn编号为7-115-15032-X的复本。（序列seq_bdno。）INSERT INTO book_dups VALUES(seq_bdno.NEXTVAL, 7-115-15032-X, T, SYSDATE);5、删除目前已办证1年(365天)且从未借阅过图书的读者信息。DELETE FROM readersWHERE SYSDATE - opening_date = 365 AND rdno NOT IN (SELECT rdno FROM borrow WHERE brw_date = opening_date);6、查询图书数据库应用技术的isbn编号、图书复本数及可借阅复本数，当有多种同名图书时按图书isbn编号进行分组统计。SELECT b.isbn, b.bd_num, SUM(DECODE(is_avail,T, 1, F, 0, 0) AS avail_numFROM books b, book_dups bdWHERE b.bname = 数据库应用技术 AND b.isbn = bd.isbn(+)GROUP BY b.isbn, b.bd_num;7、列出曾借阅图书总数量第二大的读者的信息，包括读者证号、读者姓名和曾借阅图书数量，当有多名读者时先按读者姓名降序排列，后按读者证编号升序排列。SELECT r.rdno, r.rname, SUM(brw.brw_num) -要返回的信息 FROM readers r, borrow brw WHERE r.rdno = brw.rdno GROUP BY r.rdno, r.rname HAVING SUM(brw.brw_num) = (SELECT MAX(SUM(brw_num) FROM borrow GROUP BY rdno -曾借书总数第二大值HAVING SUM(brw_num) = 0);四、1、SELECT * FROM readersWHERE rdno IN (SELECT rdno FROM borrow GROUP BY brw.rdno HAVING SUM(brw.brw_bnum) 1000);查询已经借阅过1000本以上图书的读者信息。2、SELECT brw.rdno FROM borrow brw, borrow_books brwb WHERE brw.brwno = brwb.brwno AND brwb.bdno IN (SELECT bd.bdno FROM books b, book_dups bd WHERE b.authors LIKE %,王强,% AND b.isbn = bd.isbn);查询读者证号，这些读者曾借阅过满足下面条件的图书。条件是该图书由王强参与编写，且其在作列表中不是排在第一或最后一位。3、UPDATE book_dupsSET is_avail = F, avail_date = NULLWHERE is_avail = T AND bdno IN (SELECT bdno FROM borrow_booksGROUP BY bdno HAVING COUNT(dbno) = 5000);将借出次数达到5000且在馆的图书复本强制下架（即可借状态变为不可借）。4、GRANT SELECT, UPDATE ON books TO user1 WITH GRANT OPTION将对books表上数据进行SELECT和UPDATE的权利授予user1，且其可以转授给其他人。五、编写一个存储过程，完成满足以下要求的功能。（12分）编写存储函数fun_latestlost，对于读者的某借阅单中所有尚未归还的图书，都按照丢失业务处理。输入为读者证号和借阅单编号，返回为丢失的图书本数。要求使用游标。图书丢失时需要进行