吉大数据库应用技术在线作业一答案

吉大《数据库应用技术》在线作业一答案
试卷总分：100 测试时间：-- 试卷得分：100
单选题
一、单选题（共25 道试题，共100 分。

）得分：100V
1. Given the following table:
TestTable C1 ----------- 12345 And if the following CLI calls are made:
SQLAlloc Handle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&henv); SQLSetEnvAttr( henv,
SQL_A TTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3,0); SQL
AllocHandle(SQL_HANDLE_DBC,henv,&hdbc); SQLConnect( hdbc, (SQLCHAR *)"db",
SQL_NTS, (SQLCHAR *)"userid", SQL_NTS, (SQLCHAR *)"password", SQL_NTS );
SQLSetConnectAttr( hdbc, SQL_A TTR_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF, 0);
SQLAlloc Handle(SQL_HANDLE_STMT,hdbc,&hstmt); SQLPrepare(hstmt,(unsigned
char*)"select *from Test order by C1',SQL_NTS);
SQLBindCol(hstmt,1,SQL_C_SHORT,&data,0,NULL); SQLExecute(hstmt);
SQLFetch(hstmt); printf(Data:%i\n",data); SQLFetch(hstmt);
printf(Data:%i\n",data); SQLFetch(hstmt); printf(Data:%i\n",data);
SQLEndTran(SQL_HANDLE_ENV,henv,SQL_COMMIT); SQLFetch(hstmt);
printf(Data:%i\n",data); Which of the following will be returned by the
program?
A. Data: 1 Data: 2 Data: 3 Data: 3
B. Data: 1 Data: 2 Data: 3 Data: 4
C. Data: 1 Data: 2 Data: 3 Data: 1
D. Data: 1 Data: 2 Data: 3 Data: 5
满分：4 分得分：4
2. Given the code: EXEC SQL DECLARE cursor1 CURSOR FOR SELECT
name,age,b_date FROM person; EXEC SQL OPEN cursor1; Under which of the
following situations will the above cursor be implicitly closed?
A. When a CLOSE statement is issued
B. When a COMMIT statement is issued
C. When there are no rows in the result set
D. When all rows are FETCHed from the result set
满分：4 分得分：4
3. Given the application code: EXEC SQL DECLARE cur CURSOR WITH HOLD FOR
SELECT c1 FROM t1 EXEC SQL OPEN cur EXEC SQL FETCH cur INTO :hv /*
Statement 1 */ EXEC SQL COMMIT /* Statement 2 */ EXEC SQL FETCH cur INTO
:hv /* Statement 3 */ EXEC SQL ROLLBACK /* Statement 4 */ EXEC SQL CLOSE
cur /* Statement 5 */ If the table T1 has no rows in it, which statement
will cause the cursor "cur" to be closed first?
A. Statement 1
B. Statement 2
C. Statement 3
D. Statement 4
满分：4 分得分：4
4. How many rows can be retrieved using a single SELECT INTO statement?
A. Only one row
B. As many as are in the result
C. As many as are host variables used in the call
D. As many as host variable array structures can hold
满分：4 分得分：4
5. Given the table T1 with the following data: COL1 IDX ---- ---- A
single-threaded CLI application executes the following pseudocode in
sequence: SQLAllocHandle( SQL_HANDLE_ENV, NULL, &hEnv ) SQLAllocHandle(
SQL_HANDLE_DBC, hEnv, &hDbc ) SQLConnect( hDbc, "SAMPLE", SQL_NTS, NULL,
SQL_NTS, NULL, SQL_NTS ) SQLSetConnectAttr( hDbc, SQL_A TTR_AUTOCOMMIT,
SQL_AUTOCOMMIT_ON ) SQLAllocHandle( SQL_HANDLE_STMT, hDbc, &hStmt ) SQLExecDirect( hStmt, "UPDA TE table1 SET col1=10 WHERE idx=1", SQL_NTS )
SQLExecDirect( hStmt, "UPDA TE table1 SET col1=20 WHERE idx=2", SQL_NTS )
SQLEndTran( SQL_HANDLE_DBC, hDbc, SQL_COMMIT ) SQLExecDirect( hStmt,
"UPDA TE table1 SET col1=30 WHERE idx=1", SQL_NTS ) SQLExecDirect( hStmt,
"UPDA TE table1 SET col1=40 WHERE idx=1", SQL_NTS ) SQLEndTran(
SQL_HANDLE_DBC, hDbc, SQL_ROLLBACK ) SQLExecDirect( hStmt, "SELECT col1
FROM table1 WHERE idx=1", SQL_NTS ) Which of the following values for COL1
will be fetched when the sequence for the pseudocode listed above is
successfully executed?
A. 10
B. 20
C. 30
D. 40
满分：4 分得分：4
6. Given the table T1 with the following data: C1 C2 -- -- 1 1 2 2 An
application issues the following SQL statements with AUTOCOMMIT disabled:
UPDA TE t1 SET c1 = 10 WHERE c2 = 1 UPDA TE t1 SET c1 = 20 WHERE c2 = 2
SA VEPOINT sp1 UPDA TE t1 SET c1 = 30 WHERE c2 = 1 UPDA TE t1 SET c1 = 40, c2 = 3 WHERE c2 = 2 SA VEPOINT sp1 UPDA TE t1 SET c1 = 50 WHERE c2 = 1 UPDA TE t1 SET c1 = 60 WHERE c2 = 2 ROLLBACK TO SA VEPOINT sp1 UPDA TE t1 SET c1 =
50 WHERE c2 = 3 COMMIT What is the result of the following query? SELECT
c1, c2 FROM t1 ORDER BY c2
A. 10 1 20 2
B. 30 1 50 3
C. 30 1 40 3
D. 10 1 50 3
满分：4 分得分：4
7. Which of the following cursor definitions will define a cursor called
c2 that will fetch rows from table t2, and for every row fetched will
update column c1 in table t2?
A. DECLARE c2 CURSOR FOR SELECT * FROM t2 FOR UPDA TE OF t2
B. DECLARE c2 CURSOR FOR SELECT * FROM t2 FOR UPDA TE OF c2
C. DECLARE c2 CURSOR FOR SELECT * FROM t2 FOR UPDA TE OF c1
D. DECLARE c2 CURSOR WITH HOLD FOR SELECT * FROM t2 FOR UPDA TE OF t2
满分：4 分得分：4
8. Given an ODBC/CLI program with a single connection, two threads and
the following actions which complete successfully: Thread 1: INSERT INTO
mytab V ALUES (1) Thread 2: INSERT INTO mytab V ALUES (2) Thread 1: COMMIT
Thread 2: INSERT INTO mytab V ALUES (3) Thread 1: ROLLBACK Thread 2: COMMIT How many records will be inserted and retained in the table MYTAB?
A. 0
B. 1
C. 2
D. 3
满分：4 分得分：4
9. Given the following code: EXEC SQL EXECUTE IMMEDIA TE :sqlstmt Which of
the following values must sqlstmt contain so that all rows are deleted
from the STAFF table?
A. DROP TABLE staff
B. DELETE FROM staff
C. DROP * FROM staff
D. DELETE * FROM staff
满分：4 分得分：4
10. Given the expression: WITH most_cities AS ( SELECT
b.id,,a.cities FROM country a, staff b WHERE a.person = b.id AND
cities > :threshold ) SELECT * FROM most_cities In which of the following
does MOST_CITIES exist?
A. user tables
B. server memory
C. user table space
D. system catalog tables
满分：4 分得分：4
11. Given the following statements: EXEC SQL INSERT INTO employee
V ALUES(:new_emp, :new_name) EXEC SQL UPDA TE company SET
num_employees=num_employees+1 WHERE company_id=1 EXEC SQL COMMIT Which of
the following can be added to the database so that the company table will
still be updated without the need for the explic it UPDA TE SQL statement?
A. An INSERT trigger on COMPANY
B. An UPDA TE trigger on COMPANY
C. An INSERT trigger on EMPLOYEE
D. An UPDA TE trigger on EMPLOYEE
满分：4 分得分：4
12. Which of the following produces a sequentially increasing number,
suitable for use as a primary key?
A. ROWID data type
B. Generated IDENTITY column
C. GENERA TE_UNIQUE built-in function
D. CURRENT SEQUENCE special register
满分：4 分得分：4
13. A cursor is declared with the WITH HOLD option. Which of the
following statements is always true?
A. The cursor will remain open after a COMMIT.
B. All rows retrieved are locked until a COMMIT.
C. A COMMIT will not be allowed until the cursor is closed.
D. Locks obtained by the cursor will be kept after a COMMIT.
满分：4 分得分：4
14. Given the table called NAME with the following column and data: lname
------ Smith SMITH SmiTh smith Which of the following SQL statements will
return all four rows in upper case?
A. SELECT CAPS(lname) FROM name
B. SELECT UCASE(lname) FROM name
C. SELECT STRUPR(lname) FROM name
D. SELECT TOUPPER(lname) FROM name
满分：4 分得分：4
15. Given the tables T1 and T2, each with an INTEGER column: T1 COL1
----------- 1- 1- 22 T2 COL1 ----------- 1- 2- 22 and the following query
that executes successfully: SELECT * FROM T1 LEFT OUTER JOIN T2 ON
T1.COL1=T2.COL1 How many rows will the query return?
A. 5
B. 6
C. 10
D. 36
满分：4 分得分：4
16. Which of the following will retrieve results that will only be in
lower case?
A. SELECT NAME FROM EMPLOYEE WHERE NAME='ali'
B. SELECT NAME FROM EMPLOYEE WHERE LCASE(NAME)='ali'
C. SELECT UCASE(NAME) FROM EMPLOYEE WHERE LCASE(NAME)='ali'
D. SELECT NAME FROM EMPLOYEE WHERE NAME IN (SELECT NAME FROM EMPLOYEE
WHERE LCASE(NAME)=LCASE('ALI'))
满分：4 分得分：4
17. Given the tables: COUNTRY id name 1 Argentina 3 Cuba 4 - NA TION id
name 2 Belgium 4 USA and the code: EXEC SQL DECLARE C1 CURSOR FOR SELECT *
FROM country WHERE name IS NOT NULL UNION SELECT * FROM nation EXEC SQL
OPEN C1 How many rows are in the result set?
A. 1
B. 2
C. 3
D. 4
满分：4 分得分：4
18. Given the tables: EMPLOYEE DEPT emp_num emp_name dept dept_id
dept_name 1 Adams 1 1 Planning 2 Jones 1 2 Support 3 Smith 2 4 Williams 1
and the statement: ALTER TABLE employee ADD FOREIGN KEY (dept) REFERENCES dept (dept_id) ON DELETE CASCADE How many rows will be deleted when the
following statement is executed? DELETE FROM employee WHERE dept=1
A. 0
B. 1
C. 3
D. 4
满分：4 分得分：4
19. Which of the following is a benefit of user-defined functions?
A. Improves application concurrency
B. Improves blocking of result sets
C. Simplifies application maintenance
D. Reduces memory requirements on the server
满分：4 分得分：4
20. An application uses static SQL to connect to a remote DB2 server and
inserts data into the CUST.ORDERS table on that remote DB2 server. To
enable access to the remote DB2 server, FOO needs to create a package with
default options so that BAR is the only non-administrative user that can
use this package on the remote DB2 server. Which statement describes the
privileges that FOO requires to accomplish this?
A. FOO requires EXECUTE privilege on the package.
B. FOO requires the privilege to create the package on the remote DB2
server.
C. FOO requires EXECUTE privilege on the package and INSERT privilege on
CUST.ORDERS.
D. FOO requires the privilege to create the package on the remote DB2
server and INSERT privilege on CUST.ORDERS.
满分：4 分得分：4
21. If a stored procedure returns multiple rows, which of the following
must the calling application use to access the result set?
A. A cursor
B. A select statement
C. A declared temporary table
D. A table user-defined function
满分：4 分得分：4
22. Which of the following CLI/ODBC functions should be used to delete
rows from a DB2 table?
A. SQLDelete()
B. SQLExecDirect()
C. SQLBulkDelete()
D. SQLExecuteUpdate()
满分：4 分得分：4
23. Which of the following is used to run an embedded dynamic SQL UPDA TE
statement?
A. UPDA TE
B. . PREPARE
C. . DECLARE
D. . EXECUTE
满分：4 分得分：4
24. An ODBC/CLI application performs an array insert into a table
containing a primary key. If one of the values inserted generates a
duplicate row error, which of the following APIs can be called to
determine the failing row?
A. SQLError()
B. SQLNumRows()
C. SQLRowCount()
D. SQLGetDiagField()
满分：4 分得分：4
25. Given the following code: BEGIN A TOMIC UPDA TE country SET
cities=:count WHERE CURRENT OF C1; INSERT INTO country
V ALUES(:co11,:co12,:co13); INSERT INTO country V ALUES(:co14,:co15,:co16);
INSERT INTO country V ALUES(:co17,:co18,:co19); INSERT INTO country
V ALUES(:co110,:co111,:co112); COMMIT; END Given that all statements
succeed except the following: INSERT INTO country
V ALUES(:co17,:co18,:co19); How many rows will be affected in table COUNTRY?
A. 0
B. 3
C. 4
D. 5
满分：4 分得分：4。