DB/2 Sample Test Taken on May 10, 2002 Pre-Assessment/Sample Test for Test #509, DB2 UDB V6.1 Fundamentals Assessment 1) Which of the following can prevent a user from inserting a row with a large salary into a table with a column holding employee salaries? > a) Defining a primary key constraint on the column b) Defining a user defined scalar function c) Defining a referential constraint d) Defining a table check constraint 2) Given the following table definition and SQL statements: CREATE TABLE TABLE1 (COL1 INTEGER, COL2 CHAR(40), COL3 INTEGER) GRANT CONTROL ON TABLE TABLE1 TO USER USERA REVOKE CONTROL ON TABLE TABLE1 FROM USER USERA Which of the following SQL statements will revoke privileges for user USERA on COL1 and COL2? a) Type of database ( DB2 UDB, Oracle, Sybase, or InstantDB ) > b) REVOKE ALL PRIVILEGES ON TABLE TABLE1 FROM USER USERA c) REVOKE REFERENCES ON TABLE TABLE1 COLUMNS (COL2, COL2) FROM USER USERA d) REVOKE UPDATE ON TABLE TABLE1 FROM USER USERA 3) What product is required to access a DB2 for OS/390 from a DB2 Runtime Client workstation? a) DB2 Connect Enterprise Edition b) DB2 Universal Developer's Edition c) DB2 Workgroup > d) DB2 Personal Edition 4) Which of the following tools allows the entering of an SQL statement, execution of the statement, and then checking how it was optimized? a) Control Center > b) Visual Explain c) Script Center d) Command Center 5) Which DB2 UDB product cannot accept requests from remote clients? a) DB2 Workgroup > b) DB2 Personal Edition c) DB2 Enterprise Edition d) DB2 Enterprise-Extended Edition 6) Which of the following products must be installed in order to write an application on AIX which will access a DB2 for OS/390 database? a) DB2 Universal Database Workgroup Edition b) DB2 Personal Developers Edition c) DB2 Universal Developers Edition > d) DB2 Connect Personal Edition 7) Which two of the following authorities are required for a user USER1 to create a package for an application using the command: bind p1.bnd ? > a) USER1 needs the BIND privilege b) USER1 needs the BINDADD privilege c) USER1 needs all privileges required to compile static SQL > d) USER1 needs all privileges required to compile dynamic SQL e) USER1 needs all privileges required to compile static or dynamic SQL Multiple Select - Please select all of the correct answers (this question has 2 correct choices). 8) Which of the following delete rules will not allow a row to be deleted from the parent table if a row with the corresponding key value still exists in the child table? > a) CASCADE b) DELETE c) RESTRICT d) SET NULL 9) Where is a view definition stored when the view is created? a) in the database log files b) in the user tablespace > c) in the system catalog tablespace d) in the index tablespace 10) Given the statements and operations: "CREATE TABLE t1 (c1 CHAR(1))" 6 rows are inserted with values of: 'a', 'b', 'c', 'd', 'e' and 'f' "SET CONSTRAINTS FOR t1 OFF" "ALTER TABLE t1 ADD CONSTRAINT con1 CHECK (c1 ='a')" Which of the following describes what happens to the rows with values of b, c, d, e and f? a) deleted from T1 only b) deleted from T1 and written into the db2diag.log file c) deleted from T1 and written into the t1exp file > d) deleted from T1 and inserted into the table t1exp e) deleted from T1 and inserted into the table syscat.checks 11) For which database object can a lock be obtained? a) a database directory > b) a database tablespace c) a database bufferpool d) a database column 12) When issuing "CREATE TABLE temptable (column1 CHAR(1) NOT NULL, PRIMARY KEY COLUMN1)" Which two of the following catalog views' results is changed? > a) SYSCAT.INDEXES b) SYSCAT.PRIMKEYS > c) SYSCAT.TABLES d) SYSCAT.TRIGGERS e) SYSCAT.NOTNULL Multiple Select - Please select all of the correct answers (this question has 2 correct choices). 13) Given the statement, "CREATE INDEX nameindx ON T1(name)", in which of the following is the index data stored? a) the catalog table space b) the log file directory c) the temporary table space > d) the user table space 14) Given the statement: "CREATE VIEW v1 AS SELECT c1 FROM t1 WHERE c1 ='c' WITH CHECK OPTION" Which of the following SQL statements will insert data into the table? a) INSERT INTO V1 VALUES ('ca') > b) INSERT INTO V1 VALUES ('c') c) INSERT INTO V1 VALUES ('d') d) INSERT INTO V1 VALUES (c)} 15) Which SQL command successfully removes uncommitted changes from a DB2 database? > a) ROLLBACK b) DELETE c) DROP d) DECOMMIT 16) Which of the following DDL statements creates a table where employee ids are unique? a) CREATE TABLE T1 (UNIQUE employid INTEGER) b) CREATE TABLE T1 (employid UNIQUE INTEGER) c) CREATE TABLE T1 (UNIQUE employid INTEGER NOT NULL) > d) CREATE TABLE T1 (employid INTEGER NOT NULL, primary key (employid)) 17) Which two of the following can NOT be created using the Control Center? > a) a user-defined function b) a table space c) a database d) a buffer pool > e) a stored procedure Multiple Select - Please select all of the correct answers (this question has 2 correct choices). 18) Which of the following statements will catalog the database mydb on the node mynode with an alias of mynewdb? a) catalog database mynewdb at node mynode b) catalog database mynewdb as mydb at node mynode c) catalog database mydb at node mynode > d) catalog database mydb as mynewdb at node mynode 19) When cataloging a node, which of the following is NOT a valid type of node to catalog? a) APPCLU b) LDAP c) LOCAL > d) NETWORK 20) Given the tables and the SQL statement COUNTRY STAFF id name person cities id last_name 1 Argentina 1 10 1 Jones 2 Canada 2 20 2 Smith 3 Cuba 2 10 4 Germany 1 0 5 France 7 5 SELECT DISTINCT 'ID', s.id FROM staff s, country c WHERE s.id =c.person How many rows will be returned? a) 1 > b) 2 c) 3 d) 4 21) Given the table: STAFFTABLE IDEN LASTNME 1 Smitye 2 Dinekd When issuing the query "SELECT * FROM STAFFTABLE", the row return order will be based on which of the following? a) no guaranteed order b) the primary key order c) the order that the rows were inserted into the table > d) the order based on the index used in the last table REORG 22) Given the table definition: STAFF: ID INT LASTNAME CHAR(30) Which of the following statements removes all rows from the table where there is a NULL value for LASTNAME? a) DELETE ALL FROM STAFF WHERE LASTNAME IS NULL b) DELETE ALL FROM STAFF WHERE LASTNAME = 'NULL' c) DELETE FROM STAFF WHERE LASTNAME = `NULL` > d) DELETE FROM STAFF WHERE LASTNAME IS NULL 23) Given successfully executed embedded SQL: INSERT INTO STAFF VALUES (3, 'Gaylord', 'Geneva', 8) ROLLBACK WORK INSERT INTO STAFF VALUES (1, 'Colbert', 'Dorchester', 1) INSERT INTO STAFF VALUES (2, 'Cert', 'Dorchr', 21) COMMIT How many new rows would be in the STAFF table? a) 0 b) 1 > c) 2 d) 3 24) Given the two table definitions: TABLE1: DEPTNUMB INTEGER DEPTNAME CHAR(30) MGR INTEGER TABLE2: IDEN INTEGER NAME CHAR(30) DEPT INTEGER COMM DECIMAL(10,2) Which of the following statements will display each department, alphabetically by name, and the name of the manager of the department? > a) select a.deptname, b.name from table1 a, table2 b where a.mgr=b.iden group by a.deptname,b.name b) select a.deptname, b.name from table1 a, table2 b where a.mgr=b.iden c) select a.deptname, b.name from table1 a, table2 b where b.mgr=a.iden group by a.deptname,b.name d) select a.deptname, b.name from table1 a, table2 b where b.mgr=a.iden 25) Given the table definitions: TABLE1: ID INT NAME CHAR(30) PERSON INT CITIES INT TABLE2: ID INT LASTNAME CHAR(30) Which of the following statements removes the rows from the TABLE1 table that have PERSONS in the TABLE2 table? > a) DELETE FROM TABLE1 WHERE ID IN (SELECT ID FROM TABLE2) b) DELETE FROM TABLE1 WHERE ID IN (SELECT PERSON FROM TABLE2) c) DELETE FROM TABLE1 WHERE PERSON IN (SELECT ID FROM TABLE2) d) DELETE FROM TABLE1 WHERE PERSON IN (SELECT PERSON FROM TABLE2) 26) Given the table: COUNTRY ID NAME PERSON CITIES 1 Argentina 1 10 2 Canada 2 20 3 Cuba 2 10 4 Germany 1 0 5 France 7 5 Which of the following statements does NOT return a single row result set? a) SELECT PERSON FROM COUNTRY WHERE NAME LIKE 'a%' b) SELECT COUNT(*) FROM COUNTRY > c) SELECT COUNT(1) FROM COUNTRY d) SELECT ID, NAME FROM COUNTRY WHERE PERSON BETWEEN 7 AND 7 27) Given the transaction "CREATE TABLE t1 (id INTEGER,CONSTRAINT chkid CHECK (id<11))" "INSERT INTO t1 values (11)" "INSERT INTO t1 values (4)" "COMMIT" Which of the following results from the transaction? a) a row is inserted with a value of 11 > b) the row insertion with a value of 11 is rejected c) a row is inserted with a NULL value d) the trigger called chkid is fired to validate the data 28) Given the following DDL statement, which two of the following would NOT occur as a result of the statement execution? CREATE TABLE MYTAB1 LIKE YOURTAB1 > a) MYTAB1 is populated with YOURTAB1 data b) MYTAB1 columns have same attributes as YOURTAB1 c) MYTAB1 columns have same names as YOURTAB1 d) MYTAB1 has same number of columns as YOURTAB1 > e) MYTAB1 has same triggers as YOURTAB1 Multiple Select - Please select all of the correct answers (this question has 2 correct choices). 29) For which of the following database objects can locks NOT be obtained a) a table b) an index key > c) a column d) a row 30) Which of the following database configuration parameters affects when lock escalation will occur? a) NUMLOCKS > b) MAXLOCKS c) LOCKLIMIT d) LOCKTHRESH =================================================================================== Final Results: # Questions: 30 Passing Score: 66.0 Number Correct Needed: 20 Your Score: 70.0 You Answered Correct: 21 Grade: Pass