Planning
| Which of the following is the lowest cost DB2 product that can be legally installed on a Windows server that has 2 CPUs? · A. DB2 Everyplace · B. DB2 Express Edition · C. DB2 Workgroup Server Edition · D. DB2 Enterprise Server Edition | |
| 2. | Which of the following products is allowed to access other DB2 servers, but cannot accept requests from other remote clients? · A. DB2 Personal Edition · B. DB2 Workgroup Server Edition · C. DB2 Enterprise Server Edition · D. DB2 Data Warehouse Edition |
| 3. | A client application on z/OS must access a DB2 database on a Solaris Server. At a minimum, which of the following products must be installed on the Solaris workstation? · A. DB2 Connect Enterprise Edition · B. DB2 Workgroup Server Edition · C. DB2 Workgroup Server Edition and DB2 Connect Enterprise Edition · D. DB2 Enterprise Server Edition and DB2 Connect Enterprise Edition |
| 4. | Which of the following is the lowest cost DB2 product that can be legally installed on an HP-UX server? · A. DB2 Express-C · B. DB2 Express · C. DB2 Personal Edition · D. DB2 Enterprise Server Edition |
| 5. | Which of the following products must be installed on an AIX server in order to build an application for AIX that will access a DB2 for z/OS database? · A. DB2 Enterprise Server Edition · B. DB2 Personal Developer's Edition · C. DB2 Universal Developer's Edition · D. DB2 Universal Database Enterprise Edition and DB2 Connect Enterprise Edition |
| 6. | Which of the following DB2 products can only be installed on a System i server? · A. DB2 for z/OS · B. DB2 for i5/OS · C. DB2 Data Warehouse Edition · D. DB2 Enterprise Server Edition |
| 7. | What is the purpose of the Design Advisor? · A. To analyze workloads and make recommendations for indexes and MQTs · B. To present a graphical representation of a data access plan and recommend design changes that will improve performance · C. To replicate data between a DB2 database and another relational database · D. To configure clients so they can access databases stored on remote servers |
| 8. | Which of the following tools can be used to catalog a database? · A. Visual Explain · B. Alert Center · C. Journal · D. Configuration Assistant |
| 9. | Which of the following is used to create and debug user-defined functions? · A. SQL Assist · B. Control Center · C. Command Editor · D. Developer Workbench |
| 10. | Which of the following DB2 tools allows a user to set DB2 registry parameters? · A. Task Center · B. Visual Explain · C. Configuration Assistant · D. Satellite Administration Center |
| 11. | What is the SQL Performance Monitor used for? · A. To examine the health of a DB2 Database Manager instance · B. To visually construct complex DML statements and examine the results of their execution · C. To schedule tasks, run tasks, and send notifications about completed tasks to other users · D. To analyze database operations performed against a DB2 for i5/OS database |
| 12. | Which two of the following allow you to perform administrative tasks against database objects? · A. Control Center · B. Journal · C. Command Line Processor · D. Task Center · E. Health Center |
| 13. | Which of the following tasks can NOT be performed using the Developer Workbench? · A. Develop and debug an SQL stored procedure · B. Develop and debug a user-defined data type · C. Develop and debug a user-defined function · D. Develop and run XML queries |
| 14. | Which of the following tools can be used to automate table reorganization operations? · A. Control Center · B. Command Center · C. Command Line Processor · D. Task Center |
| 15. | Which of the following can be viewed with the Journal? · A. Historical information about tasks, database changes, messages, and notifications · B. Information about licenses associated with each DB2 9 product installed on a particular system · C. Graphical representations of data access plans chosen for SQL statements · D. Warning and alarm thresholds for database indicators |
| 16. | Which of the following is NOT a characteristic of a data warehouse? · A. Summarized queries that perform aggregations and joins · B. Heterogeneous data sources · C. Voluminous historical data · D. Sub-second response time |
| 17. | Which of the following is NOT a characteristic of an OLTP database? · A. Granular transactions · B. Current data · C. Optimized for queries · D. Frequent updates |
| 18. | Which of the following is true about XML columns? · A. XML columns are used to store XML documents as a hierarchical set of entities · B. Only XQuery can be used to retrieve an XML document from an XML column · C. XML columns must be altered to accommodate additional parent/child relationships if they are used in referential constraints · D. In order to access any portion of an XML document stored in an XML column, the entire document must be retrieved |
| 19. | Which of the following products is used to shred extensible markup language documents? · A. DB2 AVI Extender · B. DB2 Text Extender · C. DB2 XML Extender · D. DB2 Spatial Extender |
| 20. | Which of the following best describes the difference between the DB2 Spatial Extender and the DB2 Geodetic Extender? · A. The DB2 Spatial Extender uses a latitude-longitude coordinate system; the DB2 Geodetic Extender uses a planar, x- and y-coordinate system · B. The DB2 Geodetic Extender is used to describe points, lines, and polygons; the DB2 Spatial Extender is used to find area, endpoints, and intersects · C. The DB2 Spatial Extender treats the world as a flat map; the DB2 Geodetic Extender treats the world as a round globe · D. The DB2 Geodetic Extender can be used to manage information like the locations of office buildings or the size of a flood zone; the DB2 Spatial Extender can be used for calculations and visualizations in disciplines like military command/control and asset management, meteorology and oceanography |
| 21. | Which of the following is the major difference between relational data and XML data? · A. Relational data is self-describing; XML data is not · B. Relational data has inherent ordering; XML data does not · C. Relational data must be tabular; XML data does not have to be tabular · D. Relational data is comprised of entities; XML data is comprised of numbers, characters, and dates |
Security
| 22. | Which of the following is NOT a valid method of authentication that can be used by DB2 9? · A. SERVER · B. SERVER_ENCRYPT · C. CLIENT · D. DCS |
| 23. | In a client-server environment, which two of the following can be used to verify passwords? · A. System Catalog · B. User ID/password file · C. Client Operating System · D. Communications layer · E. Application Server |
| 24. | A table named DEPARTMENT has the following columns: DEPT_IDDEPT_NAMEMANAGERAVG_SALARYWhich of the following is the best way to prevent most users from viewing AVG_SALARY data? · A. Encrypt the table's data · B. Create a view that does not contain the AVG_SALARY column · C. Revoke SELECT access for the AVG_SALARY column from users who should not see AVG_SALARY data · D. Store AVG_SALARY data in a separate table and grant SELECT privilege for that table to the appropriate users |
| 25. | Which authority or privilege is granted by the DB2 Database Manager configuration file? · A. CONNECT · B. CONTROL · C. SYSMAINT · D. EXECUTE |
| 26. | Which two of the following authorities allow a user to create a new database? · A. SYSADMN · B. SYSCTRL · C. SYSMAINT · D. DBADM · E. CREATEDB |
| 27. | Assuming USER1 has no authorities or privileges, which of the following will allow USER1 to create a view named VIEW1 that references two tables named TAB1 and TAB2? · A. CREATEIN privilege on the database · B. REFERENCES privilege on TAB1 and TAB2 · C. CREATE_TAB privilege on the database · D. SELECT privilege on TAB1 and TAB2 |
| 28. | Which of the following will allow user USER1 to change the comment associated with a table named TABLE1? · A. GRANT UPDATE ON TABLE table1 TO user1 · B. GRANT CONTROL ON TABLE table1 TO user1 · C. GRANT ALTER ON TABLE table1 TO user1 · D. GRANT REFERENCES ON TABLE table1 TO user1 |
| 29. | A table called DEPARTMENT has the following columns: DEPT_IDDEPT_NAMEMANAGERWhich of the following statements will ONLY allow user USER1 to modify the DEPT_NAME column? · A. GRANT ALTER ON TABLE department TO user1 · B. GRANT ALTER (dept_name) ON TABLE department TO user1 · C. GRANT UPDATE ON TABLE department TO user1 · D. GRANT UPDATE (dept_name) ON TABLE department TO user1 |
| 30. | An index named EMPID_X exists for a table named EMPLOYEE. Which of the following will allow user USER1 to drop the EMPID_X index? · A. GRANT DROP ON INDEX empid_x TO user1 · B. GRANT DELETE ON INDEX empid_x TO user1 · C. GRANT INDEX ON TABLE employee TO user1 · D. GRANT CONTROL ON INDEX empid_x TO user1 |
| 31. | On which two of the following database objects may the SELECT privilege be controlled? · A. Sequence · B. Nickname · C. Schema · D. View · E. Index |
| 32. | User USER1 wants to utilize an alias to remove rows from a table. Assuming USER1 has no authorities or privileges, which of the following privileges are needed? · A. DELETE privilege on the table · B. DELETE privilege on the alias · C. DELETE privilege on the alias; REFERENCES privilege on the table · D. REFERENCES privilege on the alias; DELETE privilege on the table |
| 33. | User USER1 holds CONTROL privilege on table TABLE1. Which two of the following statements is user USER1 allowed to execute? · A. GRANT CONTROL ON table1 TO user2 · B. GRANT LOAD ON table1 TO user2 · C. GRANT INSERT, UPDATE ON table1 TO user2 WITH GRANT OPTION · D. GRANT BINDADD ON table1 TO PUBLIC · E. GRANT ALL PRIVILEGES ON table1 TO PUBLIC |
| 34. | A user wishing to invoke an SQL stored procedure that queries a table must have which of the following privileges? · A. CALL privilege on the procedure; SELECT privilege on the table · B. CALL privilege on the procedure; REFERENCES privilege on the table · C. EXECUTE privilege on the procedure; SELECT privilege on the table · D. EXECUTE privilege on the procedure; REFERENCES privilege on the table |
| 35. | After the following SQL statement is executed: GRANT ALL PRIVILEGES ON TABLE employee TO USER user1Assuming user USER1 has no other authorities or privileges, which of the following actions is user USER1 allowed to perform? · A. Drop an index on the EMPLOYEE table · B. Grant all privileges on the EMPLOYEE table to other users · C. Alter the table definition · D. Drop the EMPLOYEE table |
| 36. | Which two of the following privileges is required in order to use a package? · A. BINDADD · B. BIND · C. CONNECT · D. EXECUTE · E. USE |
| 37. | Which of the following statements allows user USER1 to take the ability to create packages in a database named SAMPLE away from user USER2? · A. REVOKE CONNECT ON DATABASE FROM user2 · B. REVOKE CREATETAB ON DATABASE FROM user2 · C. REVOKE BIND ON DATABASE FROM user2 · D. REVOKE BINDADD ON DATABASE FROM user2 |
| 38. | Which of the following will provide user USER1 and all members of the group GROUP1 with the ability to perform DML, but no other operations on table TABLE1? · A. GRANT INSERT, UPDATE, DELETE, SELECT ON TABLE table1 TO user1 AND group1 · B. GRANT INSERT, UPDATE, DELETE, SELECT ON TABLE table1 TO USER user1, GROUP group1 · C. GRANT ALL PRIVILEGES EXCEPT ALTER, INDEX, REFERENCES ON TABLE table1 TO USER user1, GROUP group1 · D. GRANT CONTROL ON TABLE table1 TO user1 AND group1 |
| 39. | What does the following statement do? GRANT REFERENCES (col1, col2) ON TABLE table1 TO user1 WITHGRANT OPTION· A. Gives user USER1 the ability to refer to COL1 and COL2 of table TABLE1 in queries, along with the ability to give this authority to other users and groups. · B. Gives user USER1 the ability to refer to COL1 and COL2 of table TABLE1 in views, along with the ability to give this authority to other users and groups. · C. Gives user USER1 the ability to define a referential constraint on table TABLE1 using columns COL1 and COL2 as the parent key of the constraint. · D. Gives user USER1 the ability to define a referential constraint on table TABLE1 using columns COL1 and COL2 as the foreign key of the constraint. |
| 40. | User USER1 is the owner of TABLE1. Assuming user USER1 only holds privileges for TABLE1, which of the following is the best way to remove all privileges user USER1 holds? · A. REVOKE CONTROL ON table1 FROM user1 · B. REVOKE ALL PRIVILEGES ON table1 FROM user1 · C. REVOKE CONTROL ON table1 FROM user1; REVOKE ALL PRIVILEGES ON table1 FROM user1; · D. REVOKE CONTROL, ALL PRIVILEGES ON table1 FROM user1 |
| 41. | User USER1 has the privileges needed to invoke a stored procedure named GEN_RESUME. User USER2 needs to be able to call the procedure - user USER1 and all members of the group PUBLIC should no longer be allowed to call the procedure. Which of the following statement(s) can be used to accomplish this? · A. GRANT EXECUTE ON ROUTINE gen_resume TO user2 EXCLUDE user1, PUBLIC · B. GRANT EXECUTE ON PROCEDURE gen_resume TO user2; REVOKE EXECUTE ON PROCEDURE gen_resume FROM user1, PUBLIC; · C. GRANT CALL ON ROUTINE gen_resume TO user2 EXCLUDE user1 PUBLIC · D. GRANT CALL ON PROCEDURE gen_resume TO user2; REVOKE CALL ON PROCEDURE gen_resume FROM user1, PUBLIC; |
| 42. | A view named V.VIEW1 is based on a table named T.TABLE1. A user with DBADM authority issues the following statement: GRANT INSERT ON v.view1 TO user1 WITH GRANT OPTIONWhich of the following statements is USER1 authorized to execute? · A. GRANT INSERT ON t.table1 TO user2 · B. GRANT CONTROL ON v.view1 TO user2 · C. GRANT ALL PRIVILEGES ON v.view1 TO user2 · D. GRANT INSERT ON v.view1 TO user2 |
| 43. | What does the following statement do? GRANT ALTER ON SEQUENCE gen_empid TO user1 WITH GRANT OPTION· A. Gives USER1 the ability to change the comment associated with a sequence named GEN_EMPID, along with the ability to give this CONTROL authority for the sequence to other users and groups. · B. Gives USER1 the ability to change the values returned by the PREVIOUS_VALUE and NEXT_VALUE expressions associated with a sequence named GEN_EMPID, along with the ability to give CONTROL authority for the sequence to other users and groups. · C. Gives USER1 the ability to change the comment associated with a sequence named GEN_EMPID, along with the ability to give this authority to other users and groups. · D. Gives USER1 the ability to change the values returned by the PREVIOUS_VALUE and NEXT_VALUE expressions associated with a sequence named GEN_EMPID, along with the ability to give this authority to other users and groups. |
Working with Databases and Database Objects
| 44. | While attempting to connect to a database stored on an iSeries server from a Windows client, the following message was displayed: · SQL1013N The database alias name or database name "TEST_DB" could not be found. Which of the following actions can be used to help determine why this message was displayed? · A. Execute the LIST REMOTE DATABASES command on the server; look for an entry for the TEST_DB database · B. Execute the LIST DCS DIRECTORY command on the server; look for an entry for the TEST_DB database · C. Execute the LIST REMOTE DATABASES command on the client; look for an entry for the TEST_DB database · D. Execute the LIST DCS DIRECTORY command on the client; look for an entry for the TEST_DB database |
| 45. | A database named TEST_DB resides on a z/OS system and listens on port 446. The TCP/IP address for this system is 192.168.10.20 and the TCP/IP host name is MYHOST. Which of the following commands is required to make this database accessible to a Linux client? · A. CATALOG TCPIP NODE zos_srvr REMOTE myhost SERVER 192.168.10.20; CATALOG DATABASE zos_db AS test_db AT NODE zos_srvr; CATALOG DCS DATABASE zos_db AS test_db; · B. CATALOG TCPIP NODE zos_srvr REMOTE myhost SERVER 192.168.10.20; CATALOG DCS DATABASE zos_db AS test_db AT NODE zos_srvr; · C. CATALOG TCPIP NODE zos_srvr REMOTE myhost SERVER 446; CATALOG DCS DATABASE zos_db AS test_db AT NODE zos_srvr; · D. CATALOG TCPIP NODE zos_srvr REMOTE myhost SERVER 446; CATALOG DATABASE zos_db AS test_db AT NODE zos_srvr; CATALOG DCS DATABASE zos_db AS test_db; |
| 46. | Which of the following statements will catalog the database MYDB on the node MYNODE and assign it the alias 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 |
| 47. | Which of the following are NOT stored in the system catalog tables? · A. SQL statements used to create views · B. SQL statements used to create triggers · C. SQL statements used to create constraints · D. Table names |
| 48. | Which of the following tools can NOT be used to catalog a database? · A. Control Center · B. SQL Assist · C. Configuration Assistant · D. Command Line Processor |
| 49. | In which of the following scenarios would a stored procedure be beneficial? · A. An application running on a remote client needs to be able to convert degrees Celsius to degrees Fahrenheit and vice versa · B. An application running on a remote client needs to collect three input values, perform a calculation using the values provided, and store the input data, along with the results of the calculation in two different base tables · C. An application running on a remote client needs to track every modification made to a table that contains sensitive data · D. An application running on a remote client needs to ensure that every new employee that joins the company is assigned a unique, sequential employee number |
| 50. | If the following SQL statements are executed in the order shown: CREATE TABLE orders (order_num INTEGER NOT NULL, buyer_name VARCHAR(35), amount NUMERIC(5,2)); CREATE UNIQUE INDEX idx_orderno ON orders(order_num);Which of the following describes the resulting behavior? · A. Every ORDER_NUM value entered must be unique; whenever the ORDERS table is queried rows should be displayed in order of increasing ORDER_NUM values · B. Every ORDER_NUM value entered must be unique; whenever the ORDERS table is queried rows will be displayed in no particular order · C. Duplicate ORDER_NUM values are allowed; no other index can be created for the ORDERS table that reference the ORDER_NUM column · D. Every ORDER_NUM value entered must be unique; no other index can be created for the ORDERS table that reference the ORDER_NUM column |
| 51. | An alias can be an alternate name for which two of the following DB2 objects? · A. Sequence · B. Trigger · C. View · D. Schema · E. Table |
| 52. | Which of the following events will NOT cause a trigger to be activated? · A. A select operation · B. An insert operation · C. An update operation · D. A delete operation |
| 53. | If a view named V1 is created in such a way that it references every column in a table named EMPLOYEE except a column named SALARY, which of the following is NOT an accurate statement? · A. View V1 can be used in the same context as the EMPLOYEE table for all data retrieval operations that do not acquire SALARY information · B. View V1 can be used as a data source for other views · C. View V1 does not have to reside in the same schema as the EMPLOYEE table · D. All data, except SALARY data that is stored in the EMPLOYEE table is copied to the physical location associated with view V1 |
| 54. | Which of the following would NOT provide access to data stored in table TABLE1 using the name T1? · A. An alias named T1 that references table TABLE1 · B. A view named T1 that references table TABLE1 · C. A schema named T1 that references table TABLE1 · D. An alias named T1 that references a view named V1 that references table TABLE1 |
| 55. | Which of the following DB2 objects can be referenced by an INSERT statement to generate values for a column? · A. Sequence · B. Identity column · C. Trigger · D. Table function |
| 56. | A sequence was created with the DDL statement shown below: CREATE SEQUENCE my_seq START WITH 10 INCREMENT BY 10 CACHE 10User USER1 successfully executes the following statements in the order shown: VALUES NEXT VALUE FOR my_seq INTO :hvar;VALUES NEXT VALUE FOR my_seq INTO :hvar;User USER2 successfully executes the following statements in the order shown: ALTER SEQUENCE my_seq RESTART WITH 5 INCREMENT BY 5 CACHE 5;VALUES NEXT VALUE FOR my_seq INTO :hvar;After users USER1 and USER2 are finished, user USER3 executes the following query: SELECT NEXT VALUE FOR my_seq FROM sysibm.sysdummy1What value will be returned by the query? · A. 5 · B. 10 · C. 20 · D. 30 |
| 57. | Given the following statements: CREATE TABLE tab1 (c1 INTEGER, c2 CHAR(5));CREATE VIEW view1 AS SELECT c1, c2 FROM tab1 WHERE c1 < 100;CREATE VIEW view2 AS SELECT c1, c2 FROM view1 WITH CASCADED CHECK OPTION;Which of the following INSERT statements will fail to execute? · A. INSERT INTO view2 VALUES(50, 'abc') · B. INSERT INTO view1 VALUES (100, 'abc') · C. INSERT INTO view2 VALUES(150, 'abc') · D. INSERT INTO view1 VALUES(100, 'abc') |
| 58. | Given the following statements: CREATE TABLE t1 (c1 INTEGER, c2 CHAR(5));CREATE TABLE t1audit (user VARCHAR(20), date DATE, actionVARCHAR(20)); CREATE TRIGGER trig1 AFTER INSERT ON t1FOR EACH ROWMODE DB2SQLINSERT INTO t1audit VALUES (CURRENT USER, CURRENT DATE,'Insert');If user USER1 executes the following statements: INSERT INTO t1 VALUES (1, 'abc');INSERT INTO t1 (c1) VALUES (2);UPDATE t1 SET c2 = 'ghi' WHERE c1 = 1;How many new records will be written to the database? · A. 0 · B. 2 · C. 3 · D. 4 |
| 59. | Which of the following is NOT an attribute of Declared Global Temporary Tables (DGTTs)? · A. Each application that defines a DGTT has its own instance of the DGTT · B. Two different applications cannot create DGTTs that have the same name · C. DGTTs can only be used by the application that creates them, and only for the life of the application · D. Data stored in a DGTT can exist across transaction boundaries |
| 60. | Which of the following is an accurate statement about packages? · A. Packages provide a logical grouping of database objects. · B. Packages contain control structures that are considered the bound form for SQL statements · C. Packages describe the objects in a DB2 database and their relationship to each other · D. Packages may be used during query optimization to improve the performance for a subset of SELECT queries |
| 61. | Given the following information: Protocol: TCP/IPPort Number: 5000Host Name: DB_SERVERDatabase Name: TEST_DBDatabase Server Platform: LinuxWhich of the following will allow a client to access the database stored on the server? · A. CATALOG DATABASE test_db AS test_db REMOTE TCPIP SERVER db_server PORT 5000 OSTYPE LINUX; · B. CATALOG TCPIP NODE 5000 REMOTE SERVER db_server OSTYPE LINUX; CATALOG DATABASE test_db AS test_db AT NODE db_server AUTHENTICATION SERVER; · C. CATALOG TCPIP NODE db_server REMOTE db_server SERVER 5000 OSTYPE LINUX; CATALOG DATABASE test_db AS test_db AT NODE db_server AUTHENTICATION SERVER; · D. CATALOG TCPIP NODE db_server REMOTE db_server PORT 5000 OSTYPE LINUX; CATALOG DATABASE test_db AS test_db AT NODE db_server AUTHENTICATION SERVER; |
| 62. | A declared temporary table is used for which of the following purposes? · A. Backup purposes · B. Storing intermediate results · C. Staging area for load operations · D. Sharing result data sets between applications |
| 63. | Which of the following DB2 objects is NOT considered executable using SQL? · A. Routine · B. Function · C. Procedure · D. Trigger |
| 64. | Which of the following is NOT an accurate statement about views? · A. Views are publicly referenced names and no special authority or privilege is needed to use them. · B. Views can be used to restrict access to columns in a base table that contain sensitive data · C. Views can be used to store queries that multiple applications execute on a regular basis in a database · D. Views support INSTEAD OF triggers |
| 65. | Which of the following SQL statements can be used to create a DB2 object to store numerical data as EURO data? · A. CREATE NICKNAME euro FOR DECIMAL (9,3) · B. CREATE ALIAS euro FOR DECIMAL (9,3) · C. CREATE DISTINCT TYPE euro AS DECIMAL (9,3) · D. CREATE DATA TYPE euro AS DECIMAL (9,3) |
Working with DB2 Data Using SQL and XQuery
| 66. | Given the following two tables: NAMES-----------------------------NAME NUMBER---------- -------Wayne Gretzky 99Jaromir Jagr 68Bobby Orr 4Bobby Hull 23Mario Lemieux 66 POINTS-----------------------------NAME POINTS---------- ------Wayne Gretzky 244Bobby Orr 129Brett Hull 121Mario Lemieux 189Joe Sakic 94How many rows would be returned using the following statement? SELECT name FROM names, points· A. 0 · B. 5 · C. 10 · D. 25 | ||||||||||||||||||||||||
| 67. | Given the following CREATE TABLE statement: CREATE TABLE EMPLOYEE (EMPNO CHAR(3) NOT NULL, FIRSTNAME CHAR(20) NOT NULL, MIDINIT CHAR(1), LASTNAME CHAR(20) NOT NULL, SALARY DECIMAL(10, 2))Which of the following will retrieve the rows that have a missing value in the MIDINIT column? · A. SELECT * FROM employee WHERE midinit = ' ' · B. SELECT * FROM employee WHERE midinit = NULL · C. SELECT * FROM employee WHERE midinit = " " · D. SELECT * FROM employee WHERE midinit IS NULL | ||||||||||||||||||||||||
| 68. | Given the following two tables: TAB1 ----------------------COL_1 COL_2----- -----A 10B 12C 14 TAB2----------------------COL_A COL_B----- -----A 21C 23D 25Assuming the following results are desired: COL_1 COL_2 COL_A COL_BA 10 A 21B 12 - -C 14 C 23- - D 25Which of the following joins will produce the desired results? · A. SELECT * FROM tab1 INNER JOIN tab2 ON col_1 = col_a · B. SELECT * FROM tab1 LEFT OUTER JOIN tab2 ON col_1 = col_a · C. SELECT * FROM tab1 RIGHT OUTER JOIN tab2 ON col_1 = col_a · D. SELECT * FROM tab1 FULL OUTER JOIN tab2 ON col_1 = col_a | ||||||||||||||||||||||||
| 69. | If the following SQL statements are executed in the order shown: CREATE TABLE table1 (c1 INTEGER, c2 INTEGER);INSERT INTO table1 VALUES (123, 456);UPDATE table1 SET c1 = NULL;What will be the result of the following statement? SELECT * FROM table1;
| ||||||||||||||||||||||||
| 70. | Given the following table: TAB1 COL1 COL2----- -----A 10B 20C 30A 10D 40C 30Assuming the following results are desired: TAB1 COL1 COL2----- -----A 10B 20C 30D 40Which of the following statements will produce the desired results? · A. SELECT UNIQUE * FROM tab1 · B. SELECT DISTINCT * FROM tab1 · C. SELECT UNIQUE(*) FROM tab1 · D. SELECT DISTINCT(*) FROM tab1 | ||||||||||||||||||||||||
| 71. | Assuming table TAB1 contains 100 rows, which of the following queries will return only half of the rows available? · A. SELECT * FROM tab1 FIND FIRST 50 ROWS · B. SELECT * FROM tab1 FETCH FIRST 50 ROWS ONLY · C. SELECT * FROM tab1 WHILE ROW_NUM < 50 · D. SELECT * FROM tab1 MAXROWS 50 | ||||||||||||||||||||||||
| 72. | Given the following two tables: EMPLOYEE ID NAME DEPTID-- ---------------- ---01 Mick Jagger 1002 Keith Richards 2003 Ronnie Wood 2004 Charlie Watts 2005 Bill Wyman 3006 Brian Jones - DEPARTMENT ID DEPTNAME-- ------------------10 Executive Staff20 Sales30 Marketing40 Engineering50 Human ResourcesWhich two of the following queries will display the employee name and department name for all employees that are in Sales?
| ||||||||||||||||||||||||
| 73. | Given the following queries: SELECT c1 FROM tab1;SELECT c1 FROM tab2;Which of the following set operators can be used to produce a result data set that contains only records that are not found in the result data set produced by each query after duplicate rows have been eliminated? · A. UNION · B. INTERSECT · C. EXCEPT · D. MERGE | ||||||||||||||||||||||||
| 74. | Given the following two tables: NAMES NAME NUMBER---------- -------Wayne Gretzky 99Jaromir Jagr 68Bobby Orr 4Bobby Hull 23Brett Hull 16Mario Lemieux 66Mark Messier 11 POINTS NAME POINTS---------- ------Wayne Gretzky 244Jaromir Jagr 168Bobby Orr 129Brett Hull 121Mario Lemieux 189Joe Sakic 94Which of the following statements will display the player name, number, and points for all players that have scored points? · A. SELECT p.name, n.number, p.points FROM names n INNER JOIN points p ON n.name = p.name · B. SELECT p.name, n.number, p.points FROM names n LEFT OUTER JOIN points p ON n.name = p.name · C. SELECT p.name, n.number, p.points FROM names n RIGHT OUTER JOIN points p ON n.name = p.name · D. SELECT p.name, n.number, p.points FROM names n FULL OUTER JOIN points p ON n.name = p.name | ||||||||||||||||||||||||
| 75. | Which of the following is a valid wildcard character in a LIKE clause of a SELECT statement? · A. % · B. * · C. ? · D. \ | ||||||||||||||||||||||||
| 76. | Given the following tables: YEAR_2006 EMPID NAME----- ---------------1 Jagger, Mick2 Richards, Keith3 Wood, Ronnie4 Watts, Charlie5 Jones, Darryl6 Leavell, Chuck YEAR_1962 EMPID NAME----- ---------------1 Jagger, Mick2 Richards, Keith3 Jones, Brian4 Wyman, Bill5 Watts, Charlie6 Stewart, IanIf the following SQL statement is executed, how many rows will be returned? SELECT name FROM year_2007UNION ALLSELECT name FROM year_1962· A. 6 · B. 9 · C. 10 · D. 12 | ||||||||||||||||||||||||
| 77. | Given the following table definition: SALES--------------------------------------INVOICE_NO CHAR(20) NOT NULLSALES_DATE DATESALES_PERSON VARCHAR(25)REGION CHAR(20)SALES_AMT DECIMAL(9,2)Which of the following queries will return SALES information, sorted by SALES_PERSON, from A to Z, and SALES_DATE, from most recent to earliest? · A. SELECT invoice_no, sales_person, sales_date, sales_amt FROM sales SORT BY sales_person, sales_date DESC · B. SELECT invoice_no, sales_person, sales_date, sales_amt FROM sales SORT BY sales_person DESC, sales_date · C. SELECT invoice_no, sales_person, sales_date, sales_amt FROM sales ORDER BY sales_person, sales_date DESC · D. SELECT invoice_no, sales_person, sales_date, sales_amt FROM sales ORDER BY sales_person DESC, sales_date | ||||||||||||||||||||||||
| 78. | Given the following statement: SELECT hyear, AVG(salary)FROM (SELECT YEAR(hiredate) AS hyear, salary FROM employee WHERE salary > 30000)GROUP BY hyearWhich of the following describes the result if this statement is executed? · A. The statement will return the year and average salary for all employees that have a salary greater than $30,000, sorted by year. · B. The statement will return the year and average salary for all employees hired within a given year that have a salary greater than $30,000. · C. The statement will return the year and average salary for all years that every employee hired had a salary greater than $30,000. · D. The statement will return the year and average salary for all years that any employee had a salary greater than $30,000. | ||||||||||||||||||||||||
| 79. | Which two of the following statements are true about the HAVING clause? · A. The HAVING clause is used in place of the WHERE clause. · B. The HAVING clause uses the same syntax as the WHERE clause. · C. The HAVING clause can only be used with the GROUP BY clause. · D. The HAVING clause accepts wildcards. · E. The HAVING clause uses the same syntax as the IN clause. | ||||||||||||||||||||||||
| 80. | Given the following table definitions: DEPARTMENT--------------------------DEPTNO CHAR(3)DEPTNAME CHAR(30)MGRNO INTEGERADMRDEPT CHAR(3) EMPLOYEE--------------------------EMPNO INTEGERFIRSTNAME CHAR(30)MIDINIT CHARLASTNAME CHAR(30)WORKDEPT CHAR(3)Which of the following statements will list every employee number and last name, along with the employee number and last name of their manager, including employees that have not been assigned to a manager? · A. SELECT e.empno, e.lastname, m.empno, m.lastname FROM employee e LEFT INNER JOIN department INNER JOIN employee m ON mgrno=m.empno ON e.workdept=deptno · B. SELECT e.empno, e.lastname, m.empno, m.lastname FROM employee e LEFT OUTER JOIN department INNER JOIN employee m ON mgrno=m.empno ON e.workdept=deptno · C. SELECT e.empno, e.lastname, m.empno, m.lastname FROM employee e RIGHT OUTER JOIN department INNER JOIN employee m ON mgrno=m.empno ON e.workdept=deptno · D. SELECT e.empno, e.lastname, m.empno, m.lastname FROM employee e RIGHT INNER JOIN department INNER JOIN employee m ON mgrno=m.empno ON e.workdept=deptno | ||||||||||||||||||||||||
| 81. | Given the following table: EMPLOYEE EMPID NAME INSTRUMENT--- --------------- -----1 Jagger, Mick 012 Richards, Keith 023 Wood, Ronnie 024 Watts, Charlie 035 Jones, Darryl 046 Leavell, Chuck 05If the following query is executed: SELECT name, CASE WHEN instrument = '01' THEN 'HARMONICA' WHEN instrument = '02' THEN 'GUITAR' WHEN instrument = '03' THEN 'DRUMS' ELSE 'UNKNOWN' END AS instrumentFROM employeeWhat will be the results?
| ||||||||||||||||||||||||
| 82. | Given the following UPDATE statement: UPDATE employees SET workdept = (SELECT deptno FROM department WHERE deptno = 'A01') WHERE workdept IS NULLWhich of the following describes the result if this statement is executed? · A. The statement will fail because an UPDATE statement cannot contain a subquery. · B. The statement will only succeed if the data retrieved by the subquery does not contain multiple records. · C. The statement will succeed; if the data retrieved by the subquery contains multiple records, only the first record will be used to perform the update. · D. The statement will only succeed if every record in the EMPLOYEES table has a null value in the WORKDEPT column. | ||||||||||||||||||||||||
| 83. | Given the following table: CURRENT_EMPLOYEES--------------------------EMPID INTEGER NOT NULLNAME CHAR(20)SALARY DECIMAL(10,2) PAST_EMPLOYEES--------------------------EMPID INTEGER NOT NULLNAME CHAR(20)SALARY DECIMAL(10,2)Assuming both tables contain data, which of the following statements will NOT successfully add data to table CURRENT_EMPLOYEES? · A. INSERT INTO current_employees (empid) VALUES (10) · B. INSERT INTO current_employees VALUES (10, 'JAGGER', 85000.00) · C. INSERT INTO current_employees SELECT empid, name, salary FROM past_employees WHERE empid = 20 · D. INSERT INTO current_employees (name, salary) VALUES (SELECT name, salary FROM past_employees WHERE empid = 20) | ||||||||||||||||||||||||
| 84. | Given the following table definition: SALES--------------------------SALES_DATE DATESALES_PERSON CHAR(20)REGION CHAR(20)SALES INTEGERWhich of the following SQL statements will remove all rows that had a SALES_DATE in the year 1995? · A. DELETE * FROM sales WHERE YEAR(sales_date) = 1995 · B. DELETE FROM sales WHERE YEAR(sales_date) = 1995 · C. DROP * FROM sales WHERE YEAR(sales_date) = 1995 · D. DROP FROM sales WHERE YEAR(sales_date) = 1995 | ||||||||||||||||||||||||
| 85. | Given the following table definition: EMPLOYESS--------------------------EMP ID INTEGERNAME CHAR(20)DEPT CHAR(10)SALARY DECIMAL (10, 2)COMMISSION DECIMAL (8, 2)Assuming the DEPT column contains the values ‘ADMIN’, ‘PRODUCTION’, and ‘SALES’, which of the following statements will produce a result data set in which all ADMIN department employees are grouped together, all PRODUCTION department employees are grouped together, and all SALES department employees are grouped together? · A. SELECT name, dept FROM employees ORDER BY dept · B. SELECT name, dept FROM employees GROUP BY dept · C. SELECT name, dept FROM employees GROUP BY ROLLUP (dept) · D. SELECT name, dept FROM employees GROUP BY CUBE (dept) | ||||||||||||||||||||||||
| 86. | The following SQL statement: DELETE FROM tab1 WHERE CURRENT OF csr1 WITH RRIs used to perform which type of delete operation? · A. Positioned · B. Searched · C. Embedded · D. Dynamic | ||||||||||||||||||||||||
| 87. | Given the following data: TAB1 C1 C2-- ---200 abc250 abc150 def300 ghi175 defIf the following query is executed: WITH subset (col1, col2) AS (SELECT c1, c2 FROM tab1 WHERE c1 > 150)SELECT col2, SUM(col1) AS col1_sum FROM subset GROUP BY col2 ORDER BY col2Which of the following result data sets will be produced?
| ||||||||||||||||||||||||
| 88. | Given the following table definitions: TABLE1-----------------------------------ID INTNAME CHAR(30)PERSON INTCITIES INTTABLE2-----------------------------------ID INTLASTNAME CHAR(30)Which of the following statements will remove all rows in table TABLE1 that have matching PERSONs in table TABLE2? · 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) | ||||||||||||||||||||||||
| 89. | Given the following two tables: NAMESNAME NUMBER----------- -------------- Wayne Gretzky 99Jaromir Jagr 68Bobby Orr 4Bobby Hull 23Brett Hull 16Mario Lemieux 66Mark Messier 11POINTSNAME POINTS----------- -------------- Wayne Gretzky 244Jaromir Jagr 168Bobby Orr 129Brett Hull 121Mario Lemieux 189Joe Sakic 94Which of the following statements will display the player name, number, and points for all players that have scored points? · A. SELECT p.name, n.number, p.points FROM names n INNER JOIN points p ON n.name = p.name · B. SELECT p.name, n.number, p.points FROM names n LEFT OUTER JOIN points p ON n.name = p.name · C. SELECT p.name, n.number, p.points FROM names n RIGHT OUTER JOIN points p ON n.name = p.name · D. SELECT p.name, n.number, p.points FROM names n FULL OUTER JOIN points p ON n.name = p.name | ||||||||||||||||||||||||
| 90. | Given the following table definitions: EMPLOYEES------------------------------------------------EMPID INTEGERNAME CHAR(20)DEPTID CHAR(3)SALARY DECIMAL(10,2)COMMISSION DECIMAL(8,2)DEPARTMENTS------------------------------------------------DEPTNO INTEGERDEPTNAME CHAR(20)Which of the following statements will produce a result data set that satisfies all of these conditions: · > Displays the total number of employees in each department · >> Displays the corresponding department name for each department ID · >> Sorted by department employee count, from greatest to least · A. SELECT *, COUNT(empno) FROM departments, employees WHERE deptid = deptno GROUP BY deptname ORDER BY 2 DESC · B. SELECT deptname, COUNT(empno) FROM departments, employees WHERE deptid = deptno GROUP BY deptname ORDER BY 2 DESC · C. SELECT deptname, COUNT(empno) FROM departments, employees WHERE deptid = deptno GROUP BY deptname ORDER BY 2 ASC · D. SELECT deptname, COUNT(*) FROM departments, employees WHERE deptid = deptno GROUP BY deptname ORDER BY 2 | ||||||||||||||||||||||||
| 91. | Given the following table: CURRENT_EMPLOYEES--------------------------------------EMPID INTEGER NOT NULLNAME CHAR(20)SALARY DECIMAL(10,2)PAST_EMPLOYEES--------------------------------------EMPID INTEGER NOT NULLNAME CHAR(20)SALARY DECIMAL(10,2)Assuming both tables contain data, which of the following statements will NOT successfully add data to table CURRENT_EMPLOYEES? · A. INSERT INTO current_employees (empid) VALUES (10) · B. INSERT INTO current_employees VALUES (10, ‘JAGGER’, 85000.00) · C. INSERT INTO current_employees SELECT empid, name, salary FROM past_employees WHERE empid = 20 · D. INSERT INTO current_employees (name, salary) VALUES (SELECT name, salary FROM past_employees WHERE empid = 20) | ||||||||||||||||||||||||
| 92. | Given the following table: STOCK--------------------------CATEGORY CHAR(1)PARTNO CHAR(12)DESCRIPTION VARCHAR(40)QUANTITY INTEGERPRICE DEC(7,2)If items are indicated to be out of stock by setting DESCRIPTION to NULL and QUANTITY and PRICE to zero, which of the following statements updates the STOCK table to indicate that all items except those with CATEGORY of ‘S’ are temporarily out of stock? · A. UPDATE stock SET description = ’NULL’, quantity = 0, price = 0 WHERE category ‘S’ · B. UPDATE stock SET description = NULL, SET quantity = 0, SET price = 0 WHERE category ‘S’ · C. UPDATE stock SET (description, quantity, price) = (‘null’, 0, 0) WHERE category ‘S’ · D. UPDATE stock SET (description, quantity, price) = (NULL, 0, 0) WHERE category ‘S’ | ||||||||||||||||||||||||
| 93. | Given the following SQL statements: CREATE TABLE tab1 (col1 INTEGER)INSERT INTO tab1 VALUES (NULL)INSERT INTO tab1 VALUES (1)CREATE TABLE tab2 (col2 INTEGER)INSERT INTO tab2 VALUES (NULL)INSERT INTO tab2 VALUES (1)INSERT INTO tab2 VALUES (2)What will be the result when the following statement is executed? SELECT * FROM tab1 WHERE col1 IN (SELECT col2 FROM tab2)
| ||||||||||||||||||||||||
| 94. | Given the following table definition: SALES---------------------------------------------INVOICE_NO CHAR(20) NOT NULLSALES_DATE DATESALES_PERSON CHAR(20)REGION CHAR(20)SALES INTEGERIf the following SELECT statement is executed, which of the following describes the order of the rows in the result data set produced? SELECT * FROM sales· A. The rows are sorted by INVOICE_NO in ascending order. · B. The rows are sorted by INVOICE_NO in descending order. · C. The rows are ordered based on when they were inserted into the table. · D. The rows are not sorted in any particular order. | ||||||||||||||||||||||||
| 95. | Given the following tables: YEAR_2006EMPID NAME---------------------------------1 Jagger, Mick2 Richards, Keith3 Wood, Ronnie4 Watts, Charlie5 Jones, Darryl6 Leavell, ChuckYEAR_1962EMPID NAME---------------------------------1 Jagger, Mick2 Richards, Keith3 Jones, Brian4 Wyman, Bill5 Chapman, Tony6 Stewart, IanIf the following SQL statement is executed, how many rows will be returned? SELECT name FROM year_2006UNIONSELECT name FROM year_1962· A. 0 · B. 6 · C. 10 · D. 12 | ||||||||||||||||||||||||
| 96. | Which of the following best describes a unit of work? · A. It is a recoverable sequence of operations whose point of consistency is established when a connection to a database has been established or when a mechanism known as a savepoint is created. · B. It is a recoverable sequence of operations whose current point of consistency can be determined by querying the system catalog tables. · C. It is a recoverable sequence of operations whose point of consistency is established when an executable SQL statement is processed after a connection to a database has been established or a previous transaction has been terminated. · D. It is a recoverable sequence of operations whose point of consistency is only established if a mechanism known as a savepoint is created. | ||||||||||||||||||||||||
| 97. | Given the following set of statements: CREATE TABLE tab1 (col1 INTEGER, col2 CHAR(20));COMMIT;INSERT INTO tab1 VALUES (123, 'Red');INSERT INTO tab1 VALUES (456, 'Yellow');SAVEPOINT s1 ON ROLLBACK RETAIN CURSORS;DELETE FROM tab1 WHERE col1 = 123;INSERT INTO tab1 VALUES (789, 'Blue');ROLLBACK TO SAVEPOINT s1;INSERT INTO tab1 VALUES (789, 'Green');UPDATE tab1 SET col2 = NULL WHERE col1 = 789;COMMIT;Which of the following records would be returned by the following statement? SELECT * FROM tab1
| ||||||||||||||||||||||||
| 98. | Given the following table: TAB1 COL1 COL2----- -----A 10B 20C 30D 40E 50And the following SQL statements: DECLARE c1 CURSOR WITH HOLD FOR SELECT * FROM tab1 ORDER BY col_1;OPEN c1;FETCH c1;FETCH c1;FETCH c1;COMMIT;FETCH c1;CLOSE c1;FETCH c1;Which of the following is the last value obtained for COL_2? · A. 20 · B. 30 · C. 40 · D. 50 | ||||||||||||||||||||||||
| 99. | A stored procedure has been created with the following statement: CREATE PROCEDURE proc1 (IN var1 VARCHAR(10), OUT rc INTEGER)SPECIFIC myproc LANGUAGE SQL …What is the correct way to invoke this procedure from the command line processor (CLP)? · A. CALL proc1 ('SALES', ?) · B. CALL myproc ('SALES', ?) · C. CALL proc1 (SALES, ?) · D. RUN proc1 (SALES, ?) | ||||||||||||||||||||||||
| 100. | Given the following table: TEMP_DATA TEMP DATE----- -----45 12/25/200651 12/26/200667 12/27/200672 12/28/200634 12/29/200642 12/30/2006And the following SQL statement: CREATE FUNCTION degf_to_c (temp INTEGER) RETURNS INTEGER LANGUAGE SQL CONTAINS SQL NO EXTERNAL ACTION DETERMINISTIC BEGIN ATOMIC DECLARE newtemp INTEGER; SET newtemp = temp - 32; SET newtemp = newtemp * 5; RETURN newtemp / 9; ENDWhich two of the following SQL statements illustrate the proper way to invoke the scalar function DEGF_TO_C? · A. VALUES degf_to_c(32) · B. SELECT date, degf_to_c(temp) AS temp_c FROM temp_data · C. CALL degf_to_c(32) · D. SELECT * FROM TABLE(degf_to_c(temp)) AS temp_c · E. VALUES degf_to_c(32) AS temp_c | ||||||||||||||||||||||||
| 101. | Given the following CREATE TABLE statement: CREATE TABLE customer(custid INTEGER, info XML)And the following INSERT statements: INSERT INTO customer VALUES (1000,'<customerinfo xmlns="http://custrecord.dat" custid="1000"> <name>John Doe</name> <addr country="United States"> <street>25 East Creek Drive</street> <city>Raleigh</city> <state-prov>North Carolina</state-prov><zip-pcode>27603</zip-pcode> </addr><phone type="work">919-555-1212</phone> <email>john.doe@abc.com</email></customerinfo>'); INSERT INTO customer VALUES (1000,'<customerinfo xmlns="http://custrecord.dat" custid="1001"> <name>Paul Smith</name> <addr country="Canada"> <street>412 Stewart Drive</street> <city>Toronto</city><state-prov>Ontario</state-prov> <zip-pcode>M8X-3T6</zip-pcode> </addr><phone type="work">919-555-4444</phone> <email>psmith@xyz.com</email></customerinfo>');What is the result of the following XQuery expression? XQUERY declare default element namespace "http://custrecord.dat"; for$info in db2-fn:xmlcolumn('CUSTOMER.INFO')/customerinfo where$info/addr/state-prov="Ontario" return $info/name/text();· A. Paul Smith · C. <customerinfo xmlns="http://custrecord.dat" custid="1001"><name xmlns="http://custrecord.dat">Paul Smith</name> | ||||||||||||||||||||||||
| 102. | Which of the following is a valid DB2 data type? · A. NUMBER · B. INTERVAL · C. BYTE · D. NUM | ||||||||||||||||||||||||
| 103. | Which of the following DB2 data types does NOT have a fixed length? · A. INT · B. CHAR · C. XML · D. DOUBLE | ||||||||||||||||||||||||
| 104. | Which of the following is the best statement to use to create a user-defined data type that can be used to store currency values? · A. CREATE DISTINCT TYPE currency AS NUMERIC(7,2) · B. CREATE DISTINCT TYPE currency AS SMALLINT · C. CREATE DISTINCT TYPE currency AS BIGINT · D. CREATE DISTINCT TYPE currency AS DOUBLE | ||||||||||||||||||||||||
| 105. | Which of the following DB2 data types can be used to store 1000 MB of single-byte character data? · A. BLOB · B. CLOB · C. DBCLOB · D. GRAPHIC | ||||||||||||||||||||||||
| 106. | Which of the following DB2 data types can NOT be used to create an identity column? · A. SMALLINT · B. INTEGER · C. NUMERIC · D. DOUBLE | ||||||||||||||||||||||||
| 107. | Which of the following strings can NOT be inserted into an XML column using XMLPARSE()? · A. "<employee />" · B. "<name>John Doe</name>" · C. "<?xml version='1.0' encoding='UTF-8' ?>" · D. "<p></p>" | ||||||||||||||||||||||||
| 108. | Which two of the following are optional and do not have to be specified when creating a table? · A. Table name · B. Column name · C. Default constraint · D. Column data type · E. NOT NULL constraint | ||||||||||||||||||||||||
| 109. | Which of the following can NOT be used to restrict specific values from being inserted into a column in a particular table? · A. Index · B. Check constraint · C. Referential constraint · D. Default constraint | ||||||||||||||||||||||||
| 110. | Given the following CREATE TABLE statement: CREATE TABLE table2 LIKE table1Which two of the following will NOT occur when the statement is executed? · A. TABLE2 will have the same column names and column data types as TABLE1 · B. TABLE2 will have the same column defaults as TABLE1 · C. TABLE2 will have the same nullability characteristics as TABLE1 · D. TABLE2 will have the same indexes as TABLE1. · E. TABLE2 will have the same referential constraints as TABLE1 | ||||||||||||||||||||||||
| 111. | If the following SQL statements are executed: CREATE TABLE tab1 (id SMALLINT NOT NULL PRIMARY KEY, name VARCHAR(25)); CREATE TABLE tab2 (empid SMALLINT, weekno SMALLINT, payamt DECIMAL(6,2), CONSTRAINT const1 FOREIGN KEY (empid) REFERENCES taba(id) ON UPDATE NO ACTION);Which of the following statements is true? · A. Only values that exist in the ID column of table TAB1 are allowed to be inserted in the EMPID column of table TAB2 · B. The updating of values in the ID column of table TAB1 is not allowed · C. Only values that do not already exist in the ID column of table TAB1 are allowed to be inserted in the EMPID column of table TAB2 · D. When values that exist in the ID column of table TAB1 are updated, corresponding values in the EMPID column of table TAB2 are updated as well | ||||||||||||||||||||||||
| 112. | Which of the following is used to indicate a column will not accept NULL values and can be referenced in another table's foreign key specification? · A. Check constraint · B. Unique constraint · C. Default constraint · D. Informational constraint | ||||||||||||||||||||||||
| 113. | If table TAB1 is created using the following statement: CREATE TABLE tab1 (col1 INTEGER NOT NULL, col2 CHAR(5), CONSTRAINT cst1 CHECK (col1 in (1, 2, 3)))Which of the following statements will successfully insert a record into table TAB1? · A. INSERT INTO tab1 VALUES (0, 'abc') · B. INSERT INTO tab1 VALUES (NULL, 'abc') · C. INSERT INTO tab1 VALUES (ABS(2), 'abc') · D. INSERT INTO tab1 VALUES (DEFAULT, 'abc') | ||||||||||||||||||||||||
| 114. | If the following SQL statements are executed: CREATE TABLE make (makeid SMALLINT NOT NULL PRIMARY KEY, make VARCHAR(25)); CREATE TABLE model (modelid SMALLINT, model VARCHAR(25), makeid SMALLINT, CONSTRAINT const1 FOREIGN KEY (makeid) REFERENCES make(makeid) ON DELETE RESTRICT);And each table created is populated as follows: MAKE MAKEID MAKE------ --------1 Ford2 Chevrolet3 Toyota MODEL MODELID MODEL MAKEID------- ------- --------1 Mustang 12 Escort 13 Malibu 24 Camry 3If the following SQL statement is executed: DELETE FROM make WHERE makeid = 1What is the total number of rows that will be deleted? · A. 0 · B. 1 · C. 2 · D. 3 | ||||||||||||||||||||||||
| 115. | Which of the following is NOT a characteristic of a unique index? · A. Each column in a base table can only participate in one unique index, regardless of how the columns are grouped (the same column cannot be used in multiple unique indexes) · B. In order for an index to be used to support a unique constraint, it must have been defined with the UNIQUE attribute · C. A unique index cannot be created for a populated table if the key column specified contains more than one NULL value · D. A unique index can only be created for a non-nullable column | ||||||||||||||||||||||||
| 116. | If the following statement is executed: CREATE TABLE employee (empid INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 5), name VARCHAR(20), dept INT CHECK (dept BETWEEN 1 AND 20), hiredate DATE WITH DEFAULT CURRENT DATE, salary DECIMAL(7,2), PRIMARY KEY(empid), CONSTRAINT cst1 CHECK (YEAR(hiredate) > 2006 OR Salary > 60500));Which of the following INSERT statements will fail? · A. INSERT INTO employee VALUES (15, 'Smith', 5, '01/22/2004', 92500.00) · B. INSERT INTO employee VALUES (DEFAULT, 'Smith', 2, '10/07/2002', 80250.00) · C. INSERT INTO employee VALUES (20, 'Smith', 5, NULL, 65000.00) · D. INSERT INTO employee VALUES (DEFAULT, 'Smith', 10, '11/18/2004', 60250.00) | ||||||||||||||||||||||||
| 117. | Which type of key is defined on the child table to implement a referential constraint? · A. Unique key · B. Primary key · C. Foreign key · D. Composite key | ||||||||||||||||||||||||
| 118. | Which of the following is NOT true about schemas? · A. If a schema name is not specified, either by qualifying a database object name or by executing the SET CURRENT SCHEMA statement, the authorization ID of the current session user is used as the schema name by default · B. The value assigned to the CURRENT SCHEMA special register is persistent across database restarts · C. A schema enables the creation of multiple objects in a database without encountering namespace collisions · D. When most database objects are created, they are either implicitly or explicitly assigned to a schema | ||||||||||||||||||||||||
| 119. | When does a view get populated? · A. When it is created · B. When it is referenced in an INSERT statement · C. The first time any executable SQL statement references it · D. Any time an executable SQL statement references it | ||||||||||||||||||||||||
| 120. | Given the following statements: CREATE TABLE table1 (col1 INTEGER, col2 CHAR(3));CREATE VIEW view1 AS SELECT col1, col2 FROM table1 WHERE col1 < 100 WITH LOCAL CHECK OPTION;Which of the following INSERT statements will execute successfully? · A. INSERT INTO view1 VALUES (50, abc) · B. INSERT INTO view1 VALUES(100, abc) · C. INSERT INTO view1 VALUES(50, 'abc') · D. INSERT INTO view1 VALUES(100, 'abc') | ||||||||||||||||||||||||
| 121. | Which of the following actions will NOT cause a trigger to be fired? · A. INSERT · B. DELETE · C. ALTER · D. UPDATE | ||||||||||||||||||||||||
| 122. | The following triggers were defined for a table named SALES in the order shown: CREATE TRIGGER trigger_aNO CASCADE BEFORE UPDATE ON salesREFERENCING NEW AS newFOR EACH ROWSET new.commission = sale_amt * .05 WHERE invoice = n.invoice; CREATE TRIGGER trigger_bAFTER INSERT ON salesREFERENCING NEW AS newFOR EACH ROWUPDATE sales SET bill_date = CURRENT DATE + 30 DAYS WHERE invoice = n.invoice; CREATE TRIGGER trigger_cNO CASCADE BEFORE DELETE ON salesFOR EACH ROWSIGNAL SQLSTATE '75005' SET MESSAGE_TEXT = 'Deletes not allowed!';Which of the following statements is NOT true? · A. Once a row has been added to the SALES table, it cannot be removed · B. Whenever a row is inserted into the SALES table, the value in the BILL_DATE column is automatically set to 30 days from today · C. Each time a row is inserted into the SALES table, trigger TRIGGER_A is fired first, followed by trigger TRIGGER_B · D. Whenever a row in the SALES table is updated, all three triggers are fired but nothing happens because none of the triggers have been coded to trap update operations | ||||||||||||||||||||||||
| 123. | Which of the following CREATE TABLE statements will NOT be successful? · A. CREATE TABLE t1 (c1 XML NOT NULL UNIQUE, c2 INT) · B. CREATE TABLE t1 (c1 XML NOT NULL, c2 CHAR(20)) · C. CREATE TABLE t1 (c1 XML CHECK (c1 IS VALIDATED), c2 INT) · D. CREATE TABLE t1 (c1 XML, c2 XML) | ||||||||||||||||||||||||
| 124. | If the following SQL statement is executed: CREATE TABLE sales (invoice_no NOT NULL PRIMARY KEY, sales_date DATE, sales_amt NUMERIC(7,2)) IN tbsp0, tbsp1, tbsp2, tbsp3 PARTITION BY RANGE (sales_date NULLS FIRST) (STARTING '1/1/2007' ENDING '12/31/2007' EVERY 3 MONTHS)Which of the following statements is true? · A. Administrative tasks such as backing up, restoring, and reorganizing data stored in the SALES table must be done at the table level; not at the partition level · B. Data can be quickly rolled in and out of the SALES table by using the ATTACH PARTITION and DETACH PARTITION clauses of the ALTER TABLE statement · C. If an index is created for the SALES table, its data must be stored in table space TBSP0 · D. When resolving queries against the SALES table, each partition used is scanned asynchronously and the results of each partition scan are merged to produce the result data set returned | ||||||||||||||||||||||||
| 125. | Which of the following is NOT a characteristic of a declared temporary table? · A. Declared temporary tables are not persistent and can only be used by the application that creates them · B. It is possible for many applications to create declared temporary tables that have the same name · C. Declared temporary tables are created by executing a CREATE TABLE statement with the DECLARED GLOBAL TEMPORARY clause specified · D. Once the application that created a global temporary table is terminated, any records in the table are deleted and the table is automatically destroyed | ||||||||||||||||||||||||
Working with DB2 Tables, Views, and Indexes
Data Concurrency
| Which of the following isolation levels will lock all rows scanned to build a result data set? · A. Uncommitted Read · B. Cursor Stability · C. Read Stability · D. Repeatable Read | |
| 138. | Application A holds an Exclusive lock on table TAB1 and needs to acquire an Exclusive lock on table TAB2. Application B holds an Exclusive lock on table TAB2 and needs to acquire an Exclusive lock on table TAB1. If lock timeout is set to -1 and both applications are using the Read Stability isolation level, which of the following will occur? · A. Applications A and B will cause a deadlock situation · B. Application B will read the copy of table TAB1 that was loaded into memory when Application A first read it · C. Application B will read the data in table TAB1 and see uncommitted changes made by Application A · D. Application B will be placed in a lock-wait state until Application A releases its lock |
| 139. | A transaction using the Read Stability isolation level scans the same table multiple times before it terminates. Which of the following can occur within this transaction's processing? · A. Uncommitted changes made by other transactions can be seen from one scan to the next. · B. Rows removed by other transactions that appeared in one scan will no longer appear in subsequent scans. · C. Rows added by other transactions that did not appear in one scan can be seen in subsequent scans. · D. Rows that have been updated can be changed by other transactions from one scan to the next. |
| 140. | Two applications have created a deadlock cycle in the locking subsystem. If lock timeout is set to 30 and both applications were started at the same time, what action will the deadlock detector take when it "wakes up" and discovers the deadlock? · A. It will randomly pick an application and rollback its current transaction · B. It will rollback the current transactions of both applications · C. It will wait 30 seconds, then rollback the current transactions of both applications if the deadlock has not been resolved · D. It will go back to sleep for 30 seconds, then if the deadlock still exists, it will randomly pick an application and rollback its current transaction |
| 141. | Application A is running under the Repeatable Read isolation level and holds an Update lock on table TAB1. Application B wants to query table TAB1 and cannot wait for Application A to release its lock. Which isolation level should Application B run under to achieve this objective? · A. Repeatable Read · B. Read Stability · C. Cursor Stability · D. Uncommitted Read |
| 142. | Which of the following DB2 UDB isolation levels will only lock rows during read processing if another transaction tries to drop the table the rows are being read from? · A. Repeatable Read · B. Read Stability · C. Cursor Stability · D. Uncommitted Read |
| 143. | Application A holds a lock on a row in table TAB1. If lock timeout is set to 20, what will happen when Application B attempts to acquire a compatible lock on the same row? · A. Application B will acquire the lock it needs · B. Application A will be rolled back if it still holds its lock after 20 seconds have elapsed · C. Application B will be rolled back if Application A still holds its lock after 20 seconds have elapsed · D. Both applications will be rolled back if Application A still holds its lock after 20 seconds have elapsed |
| 144. | To which of the following resources can a lock NOT be applied? · A. Table spaces · B. Buffer pools · C. Tables · D. Rows |
| 145. | Which of the following causes a lock that is being held by an application using the Cursor Stability isolation level to be released? · A. The cursor is moved to another row · B. The row the cursor is on is deleted by the application · C. The row the cursor is on is deleted by another application · D. The row the cursor is on needs to be updated by another application |
| 146. | Which of the following modes, when used with the LOCK TABLE statement, will cause the DB2 Database Manager to acquire a table-level lock that prevents other concurrent transactions from accessing data stored in the table while the owning transaction is active? · A. SHARE MODE · B. ISOLATED MODE · C. EXCLUSIVE MODE · D. RESTRICT MODE |
| 147. | An application has acquired a Share lock on a row in a table and now wishes to update the row. Which of the following statements is true? · A. The application must release the row-level Share lock it holds and acquire an Update lock on the row · B. The application must release the row-level Share lock it holds and acquire an Update lock on the table · C. The row-level Share lock will automatically be converted to a row-level Update lock · D. The row-level Share lock will automatically be escalated to a table-level Update lock |
| 148. | Application A wants to read a subset of rows from table TAB1 multiple times. Which of the following isolation levels should Application A use to prevent other users from making modifications and additions to table TAB1 that will affect the subset of rows read? · A. Repeatable Read · B. Read Stability · C. Cursor Stability · D. Uncommitted Read |
| 149. | Application A issues the following SQL statements within a single transaction using the Uncommitted Read isolation level: SELECT * FROM department WHERE deptno = 'A00'; UPDATE department SET mgrno = '000100' WHERE deptno = 'A00';As long as the transaction is not committed, which of the following statements is FALSE? · A. Other applications not running under the Uncommitted Read isolation level are prohibited from reading the updated row · B. Application A is allowed to read data stored in another table, even if an Exclusive lock is held on that table · C. Other applications running under the Uncommitted Read isolation level are allowed to read the updated row · D. Application A is not allowed to insert new rows into the DEPARTMENT table as long as the current transaction remains active |
| 150. | A table contains a list of all seats available at a football stadium. A seat consists of a section number, a seat number, and whether or not the seat has been assigned. A ticket agent working at the box office generates a list of all unassigned seats. When the agent refreshes the list, it should only change if another agent assigns one or more unassigned seats. Which of the following is the best isolation level to use for this application? · A. Repeatable Read · B. Read Stability · C. Cursor Stability · D. Uncommitted Read |
No comments:
Post a Comment