C 1. Which of the following tools can be used to identify inefficient(效率低的) SQL statements without executing them?
A. QMF
B. Task Center
C. Visual Explain//可视化解释器
D. Development Center
D 2. USERA needs to be able to read rows from TAB1 and add new rows to TAB1. Which of the following statements will give USERA only the needed privileges (特权)?
A. GRANT SELECT ON TABLE tab1 TO usera
B. GRANT SELECT, ALTER ON TABLE tab1 TO usera
C. GRANT ALL PRIVILEGES ON TABLE tab1 TO usera
D. GRANT SELECT, INSERT ON TABLE tab1 TO usera
?B 3. Given the statement:
CREATE TABLE t1
(C1 CHAR (3)
CONSTRAINT c1
CHECK (c1 IN ('A01','B01','C01'))
)
DB2 verifies that the table checks constraint is met during which of the following actions?
A. Reorganizing the table.
B. Updating any row in the table.
C. Adding an insert trigger to the table.
D. Creating a unique index for the table.
B 4. Given the following:
A table contains a list of all seats on an airplane. A seat consists of a seat number and whether or not it is assigned. An airline agent lists all the unassigned seats on the plane. When the agent refreshes the list from the table, the list should not change.
Which of the following isolation levels should be used for this application?
A. Read Stability
B. Repeatable Read
C. Cursor Stability
D. Uncommitted Read
A 5. Given the following information:
CREATE TABLE tab1 (c1 CHAR (4), c2 INTEGER)
INSERT INTO tab1 VALUES ('123', 345)
UPDATE tab1 SET (c1, c2) = (NULL, 0)
What will be the result of the following statement if issued in the Command Line Processor?
SELECT * FROM tab1;
A. C1 C2 ---- ------------ 0 1 record(s) selected.
B. C1 C2 ---- ----------- 123 345 1 record(s) selected.
C. C1 C2 ---- ----------- NULL 0 1 record(s) selected.
D. C1 C2 ---- ----------- 123 0 1 record(s) selected.
A 6. Given table EMPLOYEE with columns EMPNO and SALARY, and table JOB with columns ID and TITLE, what is the effect of the following statement?
UPDATE employee SET salary = salary * 1.15
WHERE salary < 15000 OR
EXISTS (SELECT 1 FROM job WHERE job.id = employee.empno AND job.title = 'MANAGER')
A. Employees who make less than 15,000 and all managers are given salary increases.
B. Only employees who are managers that make less than 15,000 are given salary increases.
C. Employees who are not managers and who make less than 15,000 are given salary increases.
D. Only employees who are not managers or make less than 15,000 are given salary increases
B 7. Which of the following occurs if an application ends abnormally during an active unit of work?
A. The unit of work is committed
B. The unit of work is rolled back
C. The unit of work remains active
D. The unit of work moves to pending state
D 8. Which of the following is used to build and manage a heterogeneous Data Warehouse environment?
A. Data Joiner
B. Control Center
C. Workload Manager
D. DB2 Warehouse Manager
B 9. A unit of work is using an isolation level of Read Stability. An entire table is scanned twice within the unit of work.
Which of the following can be seen on the second scan of the table?
cs A. Rows removed by other processes
rs B. Rows added to a result set by other processes
cs C. Rows changed in a result set by other processes
Ur D. Rows with uncommitted changes made by other processes
A 10. Given the tables:
TABLEA TABLEB
empid name empid weeknumber paycheck
1 USER1 1 1 1000.00
2 USER2 1 2 1000.00
2 1 2000.00
TABLEB was defined as follows:
CREATE TABLE tableb (empid CHAR (3),
weeknumber CHAR (3),
paycheck DECIMAL (6, 2),
CONSTRAINT const1 FOREIGN KEY (empid)
REFERENCES tablea (empid) ON DELETE RESTRICT)
How many rows would be deleted from tableb if the following command was issued?
DELETE FROM tablea WHERE empid = '2'
A. 0
B. 1
C. 2
D. 3
C 11. Given the SQL statement:
ALTER TABLE table1 ADD col2 INT WITH DEFAULT
Which of the following is the result of the statement?
A. The statement fails because no default value is specified.
B. A new column called COL2 is added to TABLE1 which would have a null value if selected.
C. A new column called COL2 is added to TABLE1 which would have a value of zero if selected.
D. A new column called COL2 is added to TABLE1 which would require the default value to be set before working with the table.
A 12. Assuming the proper privileges, which two of the following would allow access to data in a table T1 using the name A1?
(Select 2)
A. CREATE ALIAS a1 FOR t1
B. CREATE TABLE a1 LIKE t1
C. CREATE INDEX a1 ON t1 (col1)
D. CREATE VIEW a1 AS SELECT * FROM t1
E. CREATE TRIGGER trig1 AFTER INSERT ON t1 FOR EACH ROW MODE DB2SQL INSERT INTO a1
C 13. Which of the following will grant just DML operations on table T.T4 to all users?
A. GRANT ALL PRIVILEGES ON t.t4 TO PUBLIC
B. GRANT ALL PRIVILEGES ON t.t4 TO ALL USERS
C. GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE t.t4 to PUBLIC
D. GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE t.t4 TO ALL USERS
C 14. Which type of key is defined on the dependent table to implement referential constraints?
A. Unique key
B. Primary key
C. Foreign key
D. Composite key
D 15. Given a user defined function, U.UDF1, that takes an input parameter of type INTEGER, and USER6 running the following SQL statement:
SELECT w.udf1 (col6) FROM t.tab1 WHERE col4 = 'ABC'
Which of the following statement(s) would allow USER6 to execute the statement?
A. GRANT ALL PRIVILEGES ON TABLE t.tab1 TO user6
B. GRANT SELECT ON TABLE t.tab1 TO user6
GRANT USE ON FUNCTION u.udf1 (INT) TO user6
C. GRANT SELECT ON TABLE t.tab1 TO user6
GRANT REFERENCES ON FUNCTION u.udf1 (INT) TO user6
D. GRANT ALL PRIVILEGES ON TABLE t.tab1 TO user6
GRANT EXECUTE ON FUNCTION u.udf1 (INT) TO user6
C 16. Given these columns from the DEPARTMENT table:
deptno CHAR(3) NOT NULL
deptname CHAR(20) NOT NULL
mgrno CHAR(6)
admrdept CHAR(3)
location CHAR(20) NOT NULL
Which of the following will select the rows that do not have a value in the MGRNO column?
A. SELECT * FROM department WHERE mgrno = ' '
B. SELECT * FROM department WHERE mgrno = NULL
C. SELECT * FROM department WHERE mgrno IS NULL
D. SELECT * FROM department WHERE mgrno IS UNKNOWN
D 17. Given the following SQL statement:
GRANT REFERENCES ON TABLE tab1 TO USER usera
Which of the following describes what USERA is allowed to do?
A. Create a read-only view using TAB1.
B. Alter TAB1 to add a check constraint.
C. Define a primary key or unique constraint on TAB1.
D. Define a dependent table where TAB1 is the parent.
A 18. Given the following DDL statement:
CREATE TABLE newtab1 LIKE tab1
Which of the following would occur as a result of the statement execution?
A. NEWTAB1 would have the same column names and attributes as TAB1
B. NEWTAB1 would have the same column names, attributes, and data as TAB1
C. NEWTAB1 would have the same column names, attributes, indexes, and constraints as TAB1
D. NEWTAB1 would have the same column names, attributes, and referential integrity as TAB1
C 19. Which of the following tools is used to create subscription sets and add subscription-set members to subscription sets?
A. Journal
B. License Center
C. Replication Center
D. Development Center
B 20. Table T1 should only allow values of 1, 2, and 3 in column C1. Which of the following will cause the database manager to enforce this business requirement?
A. Delete trigger on T1
B. Check constraint on C1
C. Table level lock on T1
D. Update permission on C1
DE 21. Which two of the following can be done using the ALTER TABLE statement?
(Select 2)
A. Add a trigger.
B. Define an index.
C. Drop a table alias.
D. Add an INTEGER column.
E. Define a unique constraint.
D 22. Given the following statements from two embedded SQL programs:
Program 1:
CREATE TABLE mytab (col1 INT, col2 CHAR(24))
COMMIT
Program 2:
INSERT INTO mytab VALUES( 20989,'Joe Smith')
COMMIT
INSERT INTO mytab VALUES( 21334,'Amy Johnson')
DELETE FROM mytab
COMMIT
INSERT INTO mytab VALUES( 23430,'Jason French')
ROLLBACK
INSERT INTO mytab VALUES( 20993,'Samantha Jones')
COMMIT
DELETE FROM mytab WHERE col1=20993
ROLLBACK
Assuming Program 1 ran to completion and then Program 2 ran to completion, which of the following records would be returned by the statement:
SELECT * FROM mytab?
A. 20989, Joe Smith
B. 21334, Amy Johnson
C. 23430, Jason French
D. 20993, Samantha Jones
E. No records are returned.
A 23. Which of the following actions describes when SQL indexes can be explicitly referenced by name within an SQL statement?
A. When dropping the index.
B. When altering the index.
C. When selecting on the index.
D. When inserting using the index.
D 24. Given the following two tables:
TAB1 TAB2
C1 C2 CX CY
--- ---- ----- ----
A 11 A 21
B 12 C 22
C 13 D 23
The following results are desired:
C1 C2 CX CY
---- ---- ---- ----
A 11 A 21
B 12 - -
C 13 C 22
Which of the following joins will yield the desired results?
A. SELECT * FROM tab2 LEFT OUTER JOIN tab1 ON c1=cx
B. SELECT * FROM tab1 INNER JOIN tab2 ON c1=cx
C. SELECT * FROM tab2 FULL OUTER JOIN tab1 ON c1=cx
D. SELECT * FROM tab1 LEFT OUTER JOIN tab2 ON c1=cx
D 25. Which of the following extenders allows data to be presented in a three-dimensional format?
A. DB2 AVI Extender
B. DB2 XML Extender
C. DB2 Text Extender
D. DB2 Spatial Extender
D 26. Given the create statements:
CREATE DISTINCT TYPE kph AS INTEGER WITH COMPARISONS
CREATE DISTINCT TYPE mph AS INTEGER WITH COMPARISONS
CREATE TABLE speed_limits
(route_num SMALLINT,
canada_sl KPH NOT NULL,
us_sl MPH NOT NULL)
Which of the following is a valid query?
A. SELECT route_num FROM speed_limits WHERE canada_sl >; 80
B. SELECT route_num FROM speed_limits WHERE canada_sl >; kph
C. SELECT route_num FROM speed_limits WHERE canada_sl >; us_sl
D. SELECT route_num FROM speed_limits WHERE canada_sl >; kph(80)
?D 27. Given the following UPDATE statement:
UPDATE address2 SET house_building=
(SELECT building FROM address1
WHERE address2.id = address1.id)
WHERE house_building IS NULL
Which of the following describes the result of the statement?
A. The statement will succeed.
B. The statement will fail because a subquery cannot exist in an UPDATE statement.
C. The statement will succeed only if ADDRESS1.ID and ADDRESS2.ID are defined as primary keys.
D. The statement will succeed if the data retrieved from the subquery does not have duplicate values for ADDRESS1.ID.
C 28. A table called EMPLOYEE has the following columns:
NAME
DEPARTMENT
PHONE_NUMBER
Which of the following will allow USER1 to modify the PHONE_NUMBER column?
A. GRANT INDEX (phone_number) ON TABLE employee TO user1
B. GRANT ALTER (phone_number) ON TABLE employee TO user1
C. GRANT UPDATE (phone_number) ON TABLE employee TO user1
D. GRANT REFERENCES (phone_number) ON TABLE employee TO user1
C 29. A developer is building a Windows embedded SQL application that will access DB2 UDB for OS/390 or OS/400 servers. Which of the following products is required to be installed on the Windows system in order to build the application?
A. DB2 UDB Personal Edition
B. DB2 Connect Personal Edition
C. DB2 Personal Developer's Edition
D. DB2 UDB Workgroup Server Edition
?C 30. A database administrator has supplied the following information:
Protocol: TCP/IP
Port Number: 446
Host Name: ZEUS
Database Name: SAMPLE
Database Server Platform: OS/400
Which are the appropriate commands to set up the ability to connect to the database?
A. CATALOG TCPIP NODE zeus REMOTE zeus SERVER 446 OSTYPE os400 DATABASE sample;
B. CATALOG TCPIP DATABASE sample REMOTE zeus SERVER 446 OSTYPE os400;
CATALOG DATABASE dcssam AS sample AT NODE zeus AUTHENTICATION dcs;
C. CATALOG TCPIP NODE zeus REMOTE zeus SERVER 446 OSTYPE os400;
CATALOG DCS DB dcssam AS sample;
CATALOG DATABASE dcssam AS sample AT NODE zeus AUTHENTICATION dcs;
D. CATALOG TCPIP NODE sample REMOTE sample SERVER 446 OSTYPE os400;
CATALOG DCS DB sample AS dcssam;
CATALOG DATABASE dcssam AS sample AT NODE zeus AUTHENTICATION dcs;
B 31. Given the two following table definitions:
ORG
deptnumb INTEGER
deptname CHAR(30)
manager INTEGER
division CHAR(30)
location CHAR(30)
STAFF
id INTEGER
name CHAR(30)
dept INTEGER
job CHAR(20)
years INTEGER
salary DECIMAL(10,2)
comm DECIMAL(10,2)
Which of the following statements will display each department, by name, and the total salary of all employees in the department?
A. SELECT a.deptname, SUM(b.salary) FROM org a, staff b WHERE a.deptnumb=b.dept ORDER BY a.deptname
B. SELECT a.deptname, SUM(b.salary) FROM org a, staff b WHERE a.deptnumb=b.dept GROUP BY a.deptname
C. SELECT a.deptname, SUM(b.salary) FROM org a INNER JOIN staff b ON a.deptnumb=b.dept ORDER BY a.deptname
D. SELECT b.deptname, SUM(a.salary) FROM org a INNER JOIN staff b ON a.deptnumb=b.dept GROUP BY a.deptname
？B 32. A client application on OS/390 or OS/400 must access a DB2 server on AIX. At a minimum, which of the following products is required to provide DRDA Application Server functionality on the DB2 server?
A. DB2 Connect Enterprise Edition
B. DB2 UDB Workgroup Server Edition
C. DB2 Connect Enterprise Edition and DB2 UDB Workgroup Server Edition
D. DB2 Connect Enterprise Edition and DB2 UDB Enterprise Server Edition
A 33. Given the following table definition:
STAFF
id INTEGER
name CHAR(20)
dept INTEGER
job CHAR(20)
years INTEGER
salary DECIMAL(10,2)
comm DECIMAL(10,2)
Where the job column contains job types: manager, clerk, and salesperson.
Which of the following statements will return the data with all managers together, all clerks together, and all salespeople together in the output?
A. SELECT * FROM staff ORDER BY job
B. SELECT job, name FROM staff GROUP BY name, job
C. SELECT * FROM staff GROUP BY name, job, id, dept, years, salary, comm
D. SELECT * FROM staff ORDER BY name, job, id, dept, years, salary, comm
A 34. Which of the following SQL statements can remove all rows from a table named COUNTRY?
A. DELETE FROM country
B. DELETE TABLE country
C. DELETE * FROM country
D. DELETE ALL FROM country
D 35. USER3 has created table TAB1 and has no additional authorities.
Which of the following statements is USER3 authorized to execute?
A. GRANT LOAD ON TABLE tab1 TO user8
B. GRANT CONTROL ON TABLE tab1 TO user8
C. GRANT OWNERSHIP ON TABLE tab1 TO user8
D. GRANT ALL PRIVILEGES ON TABLE tab1 TO user8
AC 36. Given the following table definitions:
CREATE TABLE employee
(empid INT NOT NULL PRIMARY KEY,
emp_fname CHAR(30),
emp_lname CHAR(30)
)
CREATE TABLE payroll
(empid INTEGER,
weeknumber INTEGER,
paycheck DECIMAL(6,2),
CONSTRAINT fkconst FOREIGN KEY (empid)
REFERENCES employee (empid) ON DELETE SET NULL,
CONSTRAINT chk1
CHECK (paycheck>;0 AND weeknumber BETWEEN 1 and 52))
The appropriate indexes exist to support the tables created with the previous CREATE statements. Which two of the following operations can cause the enforcement of a constraint defined on PAYROLL?
(Select 2)
A. Update of a row in PAYROLL
B. Deletion of a row in PAYROLL
C. Deletion of a row in EMPLOYEE
D. Addition of a new column to PAYROLL
E. Rollback of a row deletion on PAYROLL
B 37. When granted to USER1, which of the following will allow USER1 to ONLY access table data?
A. Administrative authority
B. SELECT privilege on the table
C. REFERENCES privilege on the table
D. SELECT privilege WITH GRANT OPTION on the table
C 38. A stored procedure has been created with the following statement:
CREATE PROCEDURE P1(INOUT VAR1 VARCHAR(10))...
From the command line processor (CLP), which is the correct way to call this procedure?
A. Call P1 (?)
B. Call P1 (DB2)
C. Call P1 ('DB2')
D. Call P1 ("DB2")
BE 39. Which two of the following SQL data types should be used to store a small binary image?
(Select 2)
A. CLOB
B. BLOB
C. VARCHAR
D. GRAPHIC
E. VARCHAR FOR BIT DATA
C 40. USER1 indicates that when running the following command from their client:
DB2 CONNECT TO sample USER user1
They receive the following error message:
SQL1013N The database alias name or database name "SAMPLE" could not be found. SQLSTATE=42705
Assuming a valid password was entered when prompted, which of the following is the cause of the error?
A. SAMPLE is not listening for incoming connections from clients.
B. The DB2 client version is not compatible with the server version.
C. The client's database directory does not contain an entry SAMPLE.
D. The client's node directory has an entry in it that points at a non-existent server.
B 41. Table T1 has a column C1 char(3) that contains strings in upper and lower case letters. Which of the following queries will find all rows where C1 is the string 'ABC' in any case?
A. SELECT * FROM t1 WHERE c1 = 'ABC'
B. SELECT * FROM t1 WHERE UCASE (c1) ' =ABC'
C. SELECT * FROM t1 WHERE IGNORE_CASE (c1 = 'ABC')
D. SELECT * FROM t1 WHERE c1 = 'ABC' WITH OPTION CASE INSENSITIVE
D 42. Assuming the database has no distinct types, which of the following is an invalid data type on CREATE TABLE?
A. CLOB
B. DOUBLE
C. NUMERIC
D. DATETIME
？A 43. Given the two following tables:
Tablename: NAMES
Name Number
Wayne Gretzky 99
Jaromir Jagr 68
Bobby Orr 4
Bobby Hull 23
Brett Hull 16
Mario Lemieux 66
Steve Yzerman 19
Claude Lemieux 19
Mark Messier 11
Mats Sundin 13
Tablename:POINTS
Name Points
Wayne Gretzky 244
Jaromir Jagr 168
Bobby Orr 129
Bobby Hull 93
Brett Hull 121
Mario Lemieux 189
Joe Sakic 94
Which of the following statements will display the player's name, number and points for all players with an entry in both tables?
A. SELECT names.name, names.number, points.points
FROM names INNER JOIN points ON names.name=points.name
B. SELECT names.name, names.number, points.points
FROM names FULL OUTER JOIN points ON names.name=points.name
C. SELECT names.name, names.number, points.points
FROM names LEFT OUTER JOIN points ON names.name=points.name
D. SELECT names.name, names.number, points.points
FROM names RIGHT OUTER JOIN points ON names.name=points.name
A 44. Which of the following products is designed for analyzing data with more than two dimensions?
A. DB2 OLAP Server
B. DB2 Warehouse Manager
C. DB2 Relational Connect
D. DB2 Data Links Manager
B 45. Given the following table definition:
STAFF
id INTEGER
name CHAR(20)
dept INTEGER
job CHAR(20)
years INTEGER
salary DECIMAL(10,2)
comm DECIMAL(10,2)
Which of the following SQL statements will return a result set that satisfies these conditions?
Displays the total number of employees in each department
Displays the corresponding department ID for each department
Includes only departments with at least one employee receiving a commission (comm) greater than 5000
Sorted by the department employee count from greatest to least
A. SELECT dept, COUNT(*) FROM staff GROUP BY dept HAVING comm >; 5000 ORDER BY 2 DESC
B. SELECT dept, COUNT(*) FROM staff
WHERE comm >; 5000
GROUP BY dept, comm
ORDER BY 2 DESC
C. SELECT dept, COUNT(*) FROM staffF GROUP BY dept HAVING MAX(comm) >; 5000 ORDER BY 2 DESC
D. SELECT dept, comm, COUNT(id) FROM staff WHERE comm >; 5000 GROUP BY dept, comm ORDER BY 3 DESC
B 46. Which of the following DB2 data types CANNOT be used to contain the date an employee was hired?
A. CLOB
B. TIME
C. VARCHAR
D. TIMESTAMP
C 47. Which of the following DB2 UDB isolation levels will lock no rows during read processing?
A. Read Stability
B. Repeatable Read
C. Uncommitted Read
D. Cursor Stability
C(D) 48. Given that the following statements were executed in order:
CREATE TABLE tab1 (c1 CHAR (1))
INSERT INTO tab1 VALUES ('b')
CREATE VIEW view1 AS SELECT c1 FROM tab1 WHERE c1 ='a'
INSERT INTO view1 VALUES ('a')
INSERT INTO view1 VALUES ('b')
How many rows would be returned from the following statement?
SELECT c1 FROM tab1
A. 0
B. 1
C. 2 //理论上
D. 3 //答案有争议//做的实验
B 49. Given the following SQL statements:
CREATE TABLE birthday1
(day INT CHECK(day BETWEEN 1 AND 31),
month INT CHECK(month BETWEEN 1 AND 6),
year INT)
CREATE TABLE birthday2
(day INT CHECK(day BETWEEN 1 AND 31),
month INT CHECK(month BETWEEN 7 AND 12),
year INT)
CREATE VIEW birthdays AS
SELECT * FROM birthday1
UNION ALL //不删除重复行，若是UNION就删除重复行
SELECT * FROM birthday2
INSERT INTO birthday1 VALUES( 22,10, 1973)
INSERT INTO birthday1 VALUES( 40, 8, 1980)
INSERT INTO birthday1 VALUES( 8, 3, 1990)
INSERT INTO birthday1 VALUES( 22, 10, 1973)
INSERT INTO birthday1 VALUES( 3, 3, 1960)
What will be the result of the following SELECT statement?
SELECT COUNT(*) FROM birthdays
A. 0
B. 2
C. 3
D. 5
A 50. For which of the following database objects can locks be obtained?
A. Table（行和表可以被锁定）
B. Trigger
C. Column
D. User-defined Data Type
D 51. Given the following statements:
CREATE TABLE t1
(c1 INTEGER,
c2 INTEGER,
c3 DECIMAL(15,0 ))
INSERT INTO t1 VALUES (1, 2, 3.0)
Which of the following will cause C1 to be decremented each time a row is deleted from the T2 table?
A. ALTER TABLE t1 ADD CHECK(t2) c1 = c1 - 1
B. CREATE VIEW v1 (c1) AS (SELECT COUNT(*) FROM t2)
C. ALTER TABLE t1 ADD FOREIGN KEY (c1) REFERENCES t2 ON DELETE CASCADE
D. CREATE TRIGGER trig1 AFTER DELETE ON t2 FOR EACH ROW MODE DB2SQL UPDATE t1 SET c1 = c1 – 1
C 52. An embedded SQL application is bound with Cursor Stability isolation level. It issues a request that retrieves 20 rows out of 200000 in the table. Which of the following describes the locks that are held as a result of this request?
A. None of the rows are locked.
B. The retrieved(检索)rows are locked.
C. The last row accessed is locked.
D. The rows not previously updated by another application are locked.
A 53. An application bound with isolation level Uncommitted Read updates a row. Which of the following is true regarding row locking in this application?
A. The application acquires no row locks.
B. The row lock is released when the cursor accessing the row is closed.
C. The row lock is released when the application issues a COMMIT statement.
D. The row lock is released when the cursor accessing the row is moved to the next row.
A 54. To set up a client that can access DB2 UDB through DB2 Connect Enterprise Edition, which of the following is the minimum software client that must be installed?
A. DB2 Runtime Client
B. DB2 Personal Edition
C. DB2 Administration Client
D. DB2 Application Development Client
A. QMF
B. Task Center
C. Visual Explain//可视化解释器
D. Development Center
D 2. USERA needs to be able to read rows from TAB1 and add new rows to TAB1. Which of the following statements will give USERA only the needed privileges (特权)?
A. GRANT SELECT ON TABLE tab1 TO usera
B. GRANT SELECT, ALTER ON TABLE tab1 TO usera
C. GRANT ALL PRIVILEGES ON TABLE tab1 TO usera
D. GRANT SELECT, INSERT ON TABLE tab1 TO usera
?B 3. Given the statement:
CREATE TABLE t1
(C1 CHAR (3)
CONSTRAINT c1
CHECK (c1 IN ('A01','B01','C01'))
)
DB2 verifies that the table checks constraint is met during which of the following actions?
A. Reorganizing the table.
B. Updating any row in the table.
C. Adding an insert trigger to the table.
D. Creating a unique index for the table.
B 4. Given the following:
A table contains a list of all seats on an airplane. A seat consists of a seat number and whether or not it is assigned. An airline agent lists all the unassigned seats on the plane. When the agent refreshes the list from the table, the list should not change.
Which of the following isolation levels should be used for this application?
A. Read Stability
B. Repeatable Read
C. Cursor Stability
D. Uncommitted Read
A 5. Given the following information:
CREATE TABLE tab1 (c1 CHAR (4), c2 INTEGER)
INSERT INTO tab1 VALUES ('123', 345)
UPDATE tab1 SET (c1, c2) = (NULL, 0)
What will be the result of the following statement if issued in the Command Line Processor?
SELECT * FROM tab1;
A. C1 C2 ---- ------------ 0 1 record(s) selected.
B. C1 C2 ---- ----------- 123 345 1 record(s) selected.
C. C1 C2 ---- ----------- NULL 0 1 record(s) selected.
D. C1 C2 ---- ----------- 123 0 1 record(s) selected.
A 6. Given table EMPLOYEE with columns EMPNO and SALARY, and table JOB with columns ID and TITLE, what is the effect of the following statement?
UPDATE employee SET salary = salary * 1.15
WHERE salary < 15000 OR
EXISTS (SELECT 1 FROM job WHERE job.id = employee.empno AND job.title = 'MANAGER')
A. Employees who make less than 15,000 and all managers are given salary increases.
B. Only employees who are managers that make less than 15,000 are given salary increases.
C. Employees who are not managers and who make less than 15,000 are given salary increases.
D. Only employees who are not managers or make less than 15,000 are given salary increases
B 7. Which of the following occurs if an application ends abnormally during an active unit of work?
A. The unit of work is committed
B. The unit of work is rolled back
C. The unit of work remains active
D. The unit of work moves to pending state
D 8. Which of the following is used to build and manage a heterogeneous Data Warehouse environment?
A. Data Joiner
B. Control Center
C. Workload Manager
D. DB2 Warehouse Manager
B 9. A unit of work is using an isolation level of Read Stability. An entire table is scanned twice within the unit of work.
Which of the following can be seen on the second scan of the table?
cs A. Rows removed by other processes
rs B. Rows added to a result set by other processes
cs C. Rows changed in a result set by other processes
Ur D. Rows with uncommitted changes made by other processes
A 10. Given the tables:
TABLEA TABLEB
empid name empid weeknumber paycheck
1 USER1 1 1 1000.00
2 USER2 1 2 1000.00
2 1 2000.00
TABLEB was defined as follows:
CREATE TABLE tableb (empid CHAR (3),
weeknumber CHAR (3),
paycheck DECIMAL (6, 2),
CONSTRAINT const1 FOREIGN KEY (empid)
REFERENCES tablea (empid) ON DELETE RESTRICT)
How many rows would be deleted from tableb if the following command was issued?
DELETE FROM tablea WHERE empid = '2'
A. 0
B. 1
C. 2
D. 3
C 11. Given the SQL statement:
ALTER TABLE table1 ADD col2 INT WITH DEFAULT
Which of the following is the result of the statement?
A. The statement fails because no default value is specified.
B. A new column called COL2 is added to TABLE1 which would have a null value if selected.
C. A new column called COL2 is added to TABLE1 which would have a value of zero if selected.
D. A new column called COL2 is added to TABLE1 which would require the default value to be set before working with the table.
A 12. Assuming the proper privileges, which two of the following would allow access to data in a table T1 using the name A1?
(Select 2)
A. CREATE ALIAS a1 FOR t1
B. CREATE TABLE a1 LIKE t1
C. CREATE INDEX a1 ON t1 (col1)
D. CREATE VIEW a1 AS SELECT * FROM t1
E. CREATE TRIGGER trig1 AFTER INSERT ON t1 FOR EACH ROW MODE DB2SQL INSERT INTO a1
C 13. Which of the following will grant just DML operations on table T.T4 to all users?
A. GRANT ALL PRIVILEGES ON t.t4 TO PUBLIC
B. GRANT ALL PRIVILEGES ON t.t4 TO ALL USERS
C. GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE t.t4 to PUBLIC
D. GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE t.t4 TO ALL USERS
C 14. Which type of key is defined on the dependent table to implement referential constraints?
A. Unique key
B. Primary key
C. Foreign key
D. Composite key
D 15. Given a user defined function, U.UDF1, that takes an input parameter of type INTEGER, and USER6 running the following SQL statement:
SELECT w.udf1 (col6) FROM t.tab1 WHERE col4 = 'ABC'
Which of the following statement(s) would allow USER6 to execute the statement?
A. GRANT ALL PRIVILEGES ON TABLE t.tab1 TO user6
B. GRANT SELECT ON TABLE t.tab1 TO user6
GRANT USE ON FUNCTION u.udf1 (INT) TO user6
C. GRANT SELECT ON TABLE t.tab1 TO user6
GRANT REFERENCES ON FUNCTION u.udf1 (INT) TO user6
D. GRANT ALL PRIVILEGES ON TABLE t.tab1 TO user6
GRANT EXECUTE ON FUNCTION u.udf1 (INT) TO user6
C 16. Given these columns from the DEPARTMENT table:
deptno CHAR(3) NOT NULL
deptname CHAR(20) NOT NULL
mgrno CHAR(6)
admrdept CHAR(3)
location CHAR(20) NOT NULL
Which of the following will select the rows that do not have a value in the MGRNO column?
A. SELECT * FROM department WHERE mgrno = ' '
B. SELECT * FROM department WHERE mgrno = NULL
C. SELECT * FROM department WHERE mgrno IS NULL
D. SELECT * FROM department WHERE mgrno IS UNKNOWN
D 17. Given the following SQL statement:
GRANT REFERENCES ON TABLE tab1 TO USER usera
Which of the following describes what USERA is allowed to do?
A. Create a read-only view using TAB1.
B. Alter TAB1 to add a check constraint.
C. Define a primary key or unique constraint on TAB1.
D. Define a dependent table where TAB1 is the parent.
A 18. Given the following DDL statement:
CREATE TABLE newtab1 LIKE tab1
Which of the following would occur as a result of the statement execution?
A. NEWTAB1 would have the same column names and attributes as TAB1
B. NEWTAB1 would have the same column names, attributes, and data as TAB1
C. NEWTAB1 would have the same column names, attributes, indexes, and constraints as TAB1
D. NEWTAB1 would have the same column names, attributes, and referential integrity as TAB1
C 19. Which of the following tools is used to create subscription sets and add subscription-set members to subscription sets?
A. Journal
B. License Center
C. Replication Center
D. Development Center
B 20. Table T1 should only allow values of 1, 2, and 3 in column C1. Which of the following will cause the database manager to enforce this business requirement?
A. Delete trigger on T1
B. Check constraint on C1
C. Table level lock on T1
D. Update permission on C1
DE 21. Which two of the following can be done using the ALTER TABLE statement?
(Select 2)
A. Add a trigger.
B. Define an index.
C. Drop a table alias.
D. Add an INTEGER column.
E. Define a unique constraint.
D 22. Given the following statements from two embedded SQL programs:
Program 1:
CREATE TABLE mytab (col1 INT, col2 CHAR(24))
COMMIT
Program 2:
INSERT INTO mytab VALUES( 20989,'Joe Smith')
COMMIT
INSERT INTO mytab VALUES( 21334,'Amy Johnson')
DELETE FROM mytab
COMMIT
INSERT INTO mytab VALUES( 23430,'Jason French')
ROLLBACK
INSERT INTO mytab VALUES( 20993,'Samantha Jones')
COMMIT
DELETE FROM mytab WHERE col1=20993
ROLLBACK
Assuming Program 1 ran to completion and then Program 2 ran to completion, which of the following records would be returned by the statement:
SELECT * FROM mytab?
A. 20989, Joe Smith
B. 21334, Amy Johnson
C. 23430, Jason French
D. 20993, Samantha Jones
E. No records are returned.
A 23. Which of the following actions describes when SQL indexes can be explicitly referenced by name within an SQL statement?
A. When dropping the index.
B. When altering the index.
C. When selecting on the index.
D. When inserting using the index.
D 24. Given the following two tables:
TAB1 TAB2
C1 C2 CX CY
--- ---- ----- ----
A 11 A 21
B 12 C 22
C 13 D 23
The following results are desired:
C1 C2 CX CY
---- ---- ---- ----
A 11 A 21
B 12 - -
C 13 C 22
Which of the following joins will yield the desired results?
A. SELECT * FROM tab2 LEFT OUTER JOIN tab1 ON c1=cx
B. SELECT * FROM tab1 INNER JOIN tab2 ON c1=cx
C. SELECT * FROM tab2 FULL OUTER JOIN tab1 ON c1=cx
D. SELECT * FROM tab1 LEFT OUTER JOIN tab2 ON c1=cx
D 25. Which of the following extenders allows data to be presented in a three-dimensional format?
A. DB2 AVI Extender
B. DB2 XML Extender
C. DB2 Text Extender
D. DB2 Spatial Extender
D 26. Given the create statements:
CREATE DISTINCT TYPE kph AS INTEGER WITH COMPARISONS
CREATE DISTINCT TYPE mph AS INTEGER WITH COMPARISONS
CREATE TABLE speed_limits
(route_num SMALLINT,
canada_sl KPH NOT NULL,
us_sl MPH NOT NULL)
Which of the following is a valid query?
A. SELECT route_num FROM speed_limits WHERE canada_sl >; 80
B. SELECT route_num FROM speed_limits WHERE canada_sl >; kph
C. SELECT route_num FROM speed_limits WHERE canada_sl >; us_sl
D. SELECT route_num FROM speed_limits WHERE canada_sl >; kph(80)
?D 27. Given the following UPDATE statement:
UPDATE address2 SET house_building=
(SELECT building FROM address1
WHERE address2.id = address1.id)
WHERE house_building IS NULL
Which of the following describes the result of the statement?
A. The statement will succeed.
B. The statement will fail because a subquery cannot exist in an UPDATE statement.
C. The statement will succeed only if ADDRESS1.ID and ADDRESS2.ID are defined as primary keys.
D. The statement will succeed if the data retrieved from the subquery does not have duplicate values for ADDRESS1.ID.
C 28. A table called EMPLOYEE has the following columns:
NAME
DEPARTMENT
PHONE_NUMBER
Which of the following will allow USER1 to modify the PHONE_NUMBER column?
A. GRANT INDEX (phone_number) ON TABLE employee TO user1
B. GRANT ALTER (phone_number) ON TABLE employee TO user1
C. GRANT UPDATE (phone_number) ON TABLE employee TO user1
D. GRANT REFERENCES (phone_number) ON TABLE employee TO user1
C 29. A developer is building a Windows embedded SQL application that will access DB2 UDB for OS/390 or OS/400 servers. Which of the following products is required to be installed on the Windows system in order to build the application?
A. DB2 UDB Personal Edition
B. DB2 Connect Personal Edition
C. DB2 Personal Developer's Edition
D. DB2 UDB Workgroup Server Edition
?C 30. A database administrator has supplied the following information:
Protocol: TCP/IP
Port Number: 446
Host Name: ZEUS
Database Name: SAMPLE
Database Server Platform: OS/400
Which are the appropriate commands to set up the ability to connect to the database?
A. CATALOG TCPIP NODE zeus REMOTE zeus SERVER 446 OSTYPE os400 DATABASE sample;
B. CATALOG TCPIP DATABASE sample REMOTE zeus SERVER 446 OSTYPE os400;
CATALOG DATABASE dcssam AS sample AT NODE zeus AUTHENTICATION dcs;
C. CATALOG TCPIP NODE zeus REMOTE zeus SERVER 446 OSTYPE os400;
CATALOG DCS DB dcssam AS sample;
CATALOG DATABASE dcssam AS sample AT NODE zeus AUTHENTICATION dcs;
D. CATALOG TCPIP NODE sample REMOTE sample SERVER 446 OSTYPE os400;
CATALOG DCS DB sample AS dcssam;
CATALOG DATABASE dcssam AS sample AT NODE zeus AUTHENTICATION dcs;
B 31. Given the two following table definitions:
ORG
deptnumb INTEGER
deptname CHAR(30)
manager INTEGER
division CHAR(30)
location CHAR(30)
STAFF
id INTEGER
name CHAR(30)
dept INTEGER
job CHAR(20)
years INTEGER
salary DECIMAL(10,2)
comm DECIMAL(10,2)
Which of the following statements will display each department, by name, and the total salary of all employees in the department?
A. SELECT a.deptname, SUM(b.salary) FROM org a, staff b WHERE a.deptnumb=b.dept ORDER BY a.deptname
B. SELECT a.deptname, SUM(b.salary) FROM org a, staff b WHERE a.deptnumb=b.dept GROUP BY a.deptname
C. SELECT a.deptname, SUM(b.salary) FROM org a INNER JOIN staff b ON a.deptnumb=b.dept ORDER BY a.deptname
D. SELECT b.deptname, SUM(a.salary) FROM org a INNER JOIN staff b ON a.deptnumb=b.dept GROUP BY a.deptname
？B 32. A client application on OS/390 or OS/400 must access a DB2 server on AIX. At a minimum, which of the following products is required to provide DRDA Application Server functionality on the DB2 server?
A. DB2 Connect Enterprise Edition
B. DB2 UDB Workgroup Server Edition
C. DB2 Connect Enterprise Edition and DB2 UDB Workgroup Server Edition
D. DB2 Connect Enterprise Edition and DB2 UDB Enterprise Server Edition
A 33. Given the following table definition:
STAFF
id INTEGER
name CHAR(20)
dept INTEGER
job CHAR(20)
years INTEGER
salary DECIMAL(10,2)
comm DECIMAL(10,2)
Where the job column contains job types: manager, clerk, and salesperson.
Which of the following statements will return the data with all managers together, all clerks together, and all salespeople together in the output?
A. SELECT * FROM staff ORDER BY job
B. SELECT job, name FROM staff GROUP BY name, job
C. SELECT * FROM staff GROUP BY name, job, id, dept, years, salary, comm
D. SELECT * FROM staff ORDER BY name, job, id, dept, years, salary, comm
A 34. Which of the following SQL statements can remove all rows from a table named COUNTRY?
A. DELETE FROM country
B. DELETE TABLE country
C. DELETE * FROM country
D. DELETE ALL FROM country
D 35. USER3 has created table TAB1 and has no additional authorities.
Which of the following statements is USER3 authorized to execute?
A. GRANT LOAD ON TABLE tab1 TO user8
B. GRANT CONTROL ON TABLE tab1 TO user8
C. GRANT OWNERSHIP ON TABLE tab1 TO user8
D. GRANT ALL PRIVILEGES ON TABLE tab1 TO user8
AC 36. Given the following table definitions:
CREATE TABLE employee
(empid INT NOT NULL PRIMARY KEY,
emp_fname CHAR(30),
emp_lname CHAR(30)
)
CREATE TABLE payroll
(empid INTEGER,
weeknumber INTEGER,
paycheck DECIMAL(6,2),
CONSTRAINT fkconst FOREIGN KEY (empid)
REFERENCES employee (empid) ON DELETE SET NULL,
CONSTRAINT chk1
CHECK (paycheck>;0 AND weeknumber BETWEEN 1 and 52))
The appropriate indexes exist to support the tables created with the previous CREATE statements. Which two of the following operations can cause the enforcement of a constraint defined on PAYROLL?
(Select 2)
A. Update of a row in PAYROLL
B. Deletion of a row in PAYROLL
C. Deletion of a row in EMPLOYEE
D. Addition of a new column to PAYROLL
E. Rollback of a row deletion on PAYROLL
B 37. When granted to USER1, which of the following will allow USER1 to ONLY access table data?
A. Administrative authority
B. SELECT privilege on the table
C. REFERENCES privilege on the table
D. SELECT privilege WITH GRANT OPTION on the table
C 38. A stored procedure has been created with the following statement:
CREATE PROCEDURE P1(INOUT VAR1 VARCHAR(10))...
From the command line processor (CLP), which is the correct way to call this procedure?
A. Call P1 (?)
B. Call P1 (DB2)
C. Call P1 ('DB2')
D. Call P1 ("DB2")
BE 39. Which two of the following SQL data types should be used to store a small binary image?
(Select 2)
A. CLOB
B. BLOB
C. VARCHAR
D. GRAPHIC
E. VARCHAR FOR BIT DATA
C 40. USER1 indicates that when running the following command from their client:
DB2 CONNECT TO sample USER user1
They receive the following error message:
SQL1013N The database alias name or database name "SAMPLE" could not be found. SQLSTATE=42705
Assuming a valid password was entered when prompted, which of the following is the cause of the error?
A. SAMPLE is not listening for incoming connections from clients.
B. The DB2 client version is not compatible with the server version.
C. The client's database directory does not contain an entry SAMPLE.
D. The client's node directory has an entry in it that points at a non-existent server.
B 41. Table T1 has a column C1 char(3) that contains strings in upper and lower case letters. Which of the following queries will find all rows where C1 is the string 'ABC' in any case?
A. SELECT * FROM t1 WHERE c1 = 'ABC'
B. SELECT * FROM t1 WHERE UCASE (c1) ' =ABC'
C. SELECT * FROM t1 WHERE IGNORE_CASE (c1 = 'ABC')
D. SELECT * FROM t1 WHERE c1 = 'ABC' WITH OPTION CASE INSENSITIVE
D 42. Assuming the database has no distinct types, which of the following is an invalid data type on CREATE TABLE?
A. CLOB
B. DOUBLE
C. NUMERIC
D. DATETIME
？A 43. Given the two following tables:
Tablename: NAMES
Name Number
Wayne Gretzky 99
Jaromir Jagr 68
Bobby Orr 4
Bobby Hull 23
Brett Hull 16
Mario Lemieux 66
Steve Yzerman 19
Claude Lemieux 19
Mark Messier 11
Mats Sundin 13
Tablename:POINTS
Name Points
Wayne Gretzky 244
Jaromir Jagr 168
Bobby Orr 129
Bobby Hull 93
Brett Hull 121
Mario Lemieux 189
Joe Sakic 94
Which of the following statements will display the player's name, number and points for all players with an entry in both tables?
A. SELECT names.name, names.number, points.points
FROM names INNER JOIN points ON names.name=points.name
B. SELECT names.name, names.number, points.points
FROM names FULL OUTER JOIN points ON names.name=points.name
C. SELECT names.name, names.number, points.points
FROM names LEFT OUTER JOIN points ON names.name=points.name
D. SELECT names.name, names.number, points.points
FROM names RIGHT OUTER JOIN points ON names.name=points.name
A 44. Which of the following products is designed for analyzing data with more than two dimensions?
A. DB2 OLAP Server
B. DB2 Warehouse Manager
C. DB2 Relational Connect
D. DB2 Data Links Manager
B 45. Given the following table definition:
STAFF
id INTEGER
name CHAR(20)
dept INTEGER
job CHAR(20)
years INTEGER
salary DECIMAL(10,2)
comm DECIMAL(10,2)
Which of the following SQL statements will return a result set that satisfies these conditions?
Displays the total number of employees in each department
Displays the corresponding department ID for each department
Includes only departments with at least one employee receiving a commission (comm) greater than 5000
Sorted by the department employee count from greatest to least
A. SELECT dept, COUNT(*) FROM staff GROUP BY dept HAVING comm >; 5000 ORDER BY 2 DESC
B. SELECT dept, COUNT(*) FROM staff
WHERE comm >; 5000
GROUP BY dept, comm
ORDER BY 2 DESC
C. SELECT dept, COUNT(*) FROM staffF GROUP BY dept HAVING MAX(comm) >; 5000 ORDER BY 2 DESC
D. SELECT dept, comm, COUNT(id) FROM staff WHERE comm >; 5000 GROUP BY dept, comm ORDER BY 3 DESC
B 46. Which of the following DB2 data types CANNOT be used to contain the date an employee was hired?
A. CLOB
B. TIME
C. VARCHAR
D. TIMESTAMP
C 47. Which of the following DB2 UDB isolation levels will lock no rows during read processing?
A. Read Stability
B. Repeatable Read
C. Uncommitted Read
D. Cursor Stability
C(D) 48. Given that the following statements were executed in order:
CREATE TABLE tab1 (c1 CHAR (1))
INSERT INTO tab1 VALUES ('b')
CREATE VIEW view1 AS SELECT c1 FROM tab1 WHERE c1 ='a'
INSERT INTO view1 VALUES ('a')
INSERT INTO view1 VALUES ('b')
How many rows would be returned from the following statement?
SELECT c1 FROM tab1
A. 0
B. 1
C. 2 //理论上
D. 3 //答案有争议//做的实验
B 49. Given the following SQL statements:
CREATE TABLE birthday1
(day INT CHECK(day BETWEEN 1 AND 31),
month INT CHECK(month BETWEEN 1 AND 6),
year INT)
CREATE TABLE birthday2
(day INT CHECK(day BETWEEN 1 AND 31),
month INT CHECK(month BETWEEN 7 AND 12),
year INT)
CREATE VIEW birthdays AS
SELECT * FROM birthday1
UNION ALL //不删除重复行，若是UNION就删除重复行
SELECT * FROM birthday2
INSERT INTO birthday1 VALUES( 22,10, 1973)
INSERT INTO birthday1 VALUES( 40, 8, 1980)
INSERT INTO birthday1 VALUES( 8, 3, 1990)
INSERT INTO birthday1 VALUES( 22, 10, 1973)
INSERT INTO birthday1 VALUES( 3, 3, 1960)
What will be the result of the following SELECT statement?
SELECT COUNT(*) FROM birthdays
A. 0
B. 2
C. 3
D. 5
A 50. For which of the following database objects can locks be obtained?
A. Table（行和表可以被锁定）
B. Trigger
C. Column
D. User-defined Data Type
D 51. Given the following statements:
CREATE TABLE t1
(c1 INTEGER,
c2 INTEGER,
c3 DECIMAL(15,0 ))
INSERT INTO t1 VALUES (1, 2, 3.0)
Which of the following will cause C1 to be decremented each time a row is deleted from the T2 table?
A. ALTER TABLE t1 ADD CHECK(t2) c1 = c1 - 1
B. CREATE VIEW v1 (c1) AS (SELECT COUNT(*) FROM t2)
C. ALTER TABLE t1 ADD FOREIGN KEY (c1) REFERENCES t2 ON DELETE CASCADE
D. CREATE TRIGGER trig1 AFTER DELETE ON t2 FOR EACH ROW MODE DB2SQL UPDATE t1 SET c1 = c1 – 1
C 52. An embedded SQL application is bound with Cursor Stability isolation level. It issues a request that retrieves 20 rows out of 200000 in the table. Which of the following describes the locks that are held as a result of this request?
A. None of the rows are locked.
B. The retrieved(检索)rows are locked.
C. The last row accessed is locked.
D. The rows not previously updated by another application are locked.
A 53. An application bound with isolation level Uncommitted Read updates a row. Which of the following is true regarding row locking in this application?
A. The application acquires no row locks.
B. The row lock is released when the cursor accessing the row is closed.
C. The row lock is released when the application issues a COMMIT statement.
D. The row lock is released when the cursor accessing the row is moved to the next row.
A 54. To set up a client that can access DB2 UDB through DB2 Connect Enterprise Edition, which of the following is the minimum software client that must be installed?
A. DB2 Runtime Client
B. DB2 Personal Edition
C. DB2 Administration Client
D. DB2 Application Development Client
浙公网安备 33010602011771号