Free Cheat-test Samples and Demo Questions Download
Adobe exams Adobe
Apple exams Apple
Avaya exams Avaya
Check Point exams Check Point
Cisco exams Cisco
Citrix exams Citrix
CIW exams CIW
CompTIA exams CompTIA
CWNP exams CWNP
EC-Council exams EC-Council
EMC exams EMC
Exin exams Exin
Fortinet exams Fortinet
GIAC exams GIAC
Hitachi exams Hitachi
HP exams HP
IBM exams IBM
Isaca exams Isaca
ISC exams ISC
ISEB exams ISEB
Juniper exams Juniper
LPI exams LPI
McAfee exams McAfee
Microsoft exams Microsoft
Oracle exams Oracle
PMI exams PMI
Riverbed exams Riverbed
SNIA exams SAP
Sun exams SAS
Symantec exams Symantec
VMware exams VMware
All certification exams

IBM 000-514 Exam - Cheat-Test.com

Free 000-514 Sample Questions:

1. Given the table called NAME with the following column and data:
lname
----------
Smith
SMITH
SmiTh
smith
Which two of the following SQL statements will return all four rows in upper case?
A. SELECT CAPS(lname) FROM name
B. SELECT UCASE(lname) FROM name
C. SELECT STRUPR(lname) FROM name
D. SELECT TOUPPER(lname) FROM name
E. SELECT TRANSLATE(lname) FROM name
Answer: BE

2. Given the table:
COUNTRY
id name person cities
1 Argentina 1 -------
and the code:
EXEC SQL DECLARE C1 CURSOR FOR SELECT id, name FROM country;
EXEC SQL OPEN C1;
EXEC SQL FETCH C1 INTO :id, :name;
EXEC SQL FETCH C1 INTO :id, :name;
Which of the following SQLCODE/SQLSTATEs will be contained in the SQLCA after the last fetch?
A. SQLCODE 0, SQLSTATE 00000
B. SQLCODE 100, SQLSTATE 02000
C. SQLCODE -100, SQLSTATE 00100
D. SQLCODE -501, SQLSTATE 24501
E. SQLCODE -1224, SQLSTATE 55032
Answer: B

3. Which two of the following Embedded SQL statements contain host variables?
A. DECLARE C1 CURSOR WITH HOLD FOR S1
B. CONNECT TO sample USER :userid USING :passwd
C. SELECT TABSCHEMA, TABNAME FROM SYSCAT.TABLES WHERE TABNAME=?
D. DECLARE C1 CURSOR FOR SELECT DEPT FROM STAFF WHERE JOB='Mgr'
E. DECLARE C1 CURSOR FOR SELECT * FROM EMPLOYEE WHERE LASTNAME=?
F. SELECT WORKDEPT INTO :wd:wdind FROM EMPLOYEE WHERE LASTNAME = 'SMITH'
Answer: BF

4. Given the following CLI Code:
SQLHANDLE henv, hdbc, hstmt1, hstmt2 ;
hstmt1=NULL;
hstmt2=NULL;
SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv ) ;
SQLAllocHandle( SQL_HANDLE_DBC, henv, hdbc );
SQLConnect( * hdbc, "SAMPLE", SQL_NTS, "DB2CERT", SQL_NTS, "DB2CERT", SQL_NTS );
SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt1 ) ;
Assuming all of the above calls return SQL_SUCCESS, which of the following return codes would be expected from the following function call made immediately after the above?
SQLExecDirect(hstmt2, "SELECT * FROM SYSIBM.SYSTABLES", SQL_NTS);
A. SQL_ERROR
B. SQL_SUCCESS
C. SQL_INVALID_HANDLE
D. SQL_SUCCESS_WITH_INFO
Answer: C

5. Which two of the following lines of code would only be useful in SQLJ applications?
A. ResultSetMetaData rsmd=rs.getMetaData();
B. DefaultContext ctx = DefaultContext.getDefaultContext();
C. CallableStatement callStmt = con.prepareCall("CALL outcli(?)");
D. ResultSet rs=stmt.execute("SELECT * FROM ORG WHERE deptnumb = ?");
E. #sql public iterator WithHoldCurs with (holdability=true) (String EmpNo);
Answer: BE

6. The following commands are issued against a database containing table USERID.ORG:
CREATE ALIAS user1.org FOR sample.org
CREATE TABLE org.sample ( c CHAR(1))
CREATE ALIAS sample.org FOR userid.org
CREATE ALIAS userid.sample FOR sample.org
The user SAMPLE issues the following statement on the command line processor:
SELECT * FROM sample
For which of the following database objects will access be attempted?
A. org.sample
B. sample.org
C. userid.org
D. sample.sample
Answer: D

7. The following commands are issued against a database containing table USERID.ORG:
CREATE ALIAS user1.org FOR userid.org
CREATE TABLE org.sample ( c CHAR(1))
CREATE ALIAS sample.org FOR userid.org
CREATE ALIAS userid.sample FOR sample.org
Which of the following statements will NOT have the same results as this SELECT statement:
SELECT * FROM org
when issued by USER1?
A. SELECT * FROM user1.org
B. SELECT * FROM org.sample
C. SELECT * FROM sample.org
D. SELECT * FROM userid.org
E. SELECT * FROM userid.sample
Answer: B

8. An embedded dynamic SQL application selects data from the CUST.ADD table. User FOO needs to bind the application using default options. User BAR needs to run this application after FOO has performed the bind.
Which two of the following statements best describe the privileges/authorizations required for users BAR and FOO?
A. FOO needs BINDADD privilege on database
B. BAR needs EXECUTE privilege on the package
C. Both FOO and BAR need SELECT privilege for CUST.ADD
D. FOO needs BINDADD privilege on database and SELECT privilege for CUST.ADD
E. BAR needs SELECT privilege for CUST.ADD and EXECUTE privilege on the package
Answer: AE

9. User FOO needs to bind an application which uses embedded dynamic SQL using the DYNAMICRULES BIND option. The application selects data from the table CUST.ADD, and the application package already exists in the database. User BAR needs to run this application.
Which two of the following statements describe the required privileges?
A. FOO needs BINDADD privilege on database
B. BAR needs EXECUTE privilege on the package
C. Both FOO and BAR need SELECT privilege for CUST.ADD
D. FOO needs BINDADD privilege on database and SELECT privilege for CUST.ADD
E. BAR needs SELECT privilege for CUST.ADD and EXECUTE privilege on the package
Answer: BD

10. Given the table T1, created by:
CREATE TABLE t1
(
id INTEGER GENERATED BY DEFAULT AS IDENTITY,
c1 CHAR(3)
)
The following SQL statements are issued:
INSERT INTO t1 VALUES (2, 'def')
INSERT INTO t1 VALUES (DEFAULT, 'abc')
INSERT INTO t1 VALUES (DEFAULT, 'ghi')
Which of the following rows are returned from the following SELECT?
SELECT id FROM t1 ORDER BY id
A. 1 2 2
B. 1 2 3
C. 2 3 4
D. 0 1 2
Answer: A

11. Given the following table (C1 is INTEGER):
T1
C1
--
4
3
2
1
and the stored procedure definition:
CREATE PROCEDURE proc(OUT o1 INT)
READS SQL DATA LANGUAGE SQL
BEGIN
DECLARE v1, v2, v3 INTEGER;
DECLARE cur1 CURSOR FOR
SELECT c1 FROM t1 ORDER BY c1;
DECLARE EXIT HANDLER FOR NOT FOUND
SET o1 = v1;
OPEN cur1;
FETCH cur1 INTO v2;
FETCH cur1 INTO v3;
SET v1 = v2*2 + v3;
fetch_loop:
LOOP
SET v2 = v3;
FETCH cur1 INTO v3;
SET v1 = v1 + v2*2 + v3;
END LOOP fetch_loop;
END
If the CALL proc(:hv) statement is embedded in an application, what is the value of :hv after it executes?
A. 24
B. 20
C. 21
D. 25
Answer: C

12. Given the tables, the relationships and the statements:
EMPLOYEE DEPT
emp_num emp_name dept dept_id dept_name
1 Adams 1 1 Planning
2 Jones 1 2 Support
3 Smith 2
4 Williams 1
Relationship: employee.dept is a foreign key on dept.dept_id.
stmt = "INSERT INTO employee VALUES (5,'Jones',3);
EXEC SQL EXECUTE IMMEDIATE :stmt;
stmt = "INSERT INTO employee VALUES (6,'Johnson',2);
EXEC SQL EXECUTE IMMEDIATE :stmt;
How many rows are successfully inserted?
A. One row is inserted in DEPT
B. Two rows are inserted in DEPT
C. One row is inserted in EMPLOYEE
D. Two rows are inserted in EMPLOYEE
E. One row is inserted in DEPT and 1 row is inserted in EMPLOYEE
F. One row is inserted in DEPT and 2 rows are inserted in EMPLOYEE
Answer: C

13. Given the tables:
EMPLOYEE DEPT
emp_num emp_name dept dept_id dept_name
1 Adams 1 1 Planning
2 Jones 1 2 Support
3 Smith 2
4 Williams 1
and the statement:
ALTER TABLE employee
ADD FOREIGN KEY (dept) REFERENCES dept (dept_id)
ON DELETE CASCADE
How many rows will be deleted when the following statement is executed?
DELETE FROM employee WHERE dept=1
A. 0
B. 1
C. 2
D. 3
E. 4
F. 6
Answer: D

14. Given the tables:
COUNTRY NATION
id name id name
1 Argentina 1 Belgium
3 Cuba 2 USA
4 ------
and the code:
EXEC SQL DECLARE C1 CURSOR FOR
SELECT * FROM country WHERE name IS NOT NULL
UNION
SELECT * FROM nation
EXEC SQL OPEN C1
How many rows are in the result set?
A. 0
B. 1
C. 2
D. 3
E. 4
Answer: E

15. Given the tables:
COUNTRY STAFF
id name person cities id name
1 Argentina 1 10 1 Aaron
2 Canada 2 20 2 Adams
3 Cuba 2 10 3 Jones
4 Germany 1 0
5 France 3 5
6 Italy 1 5
the report:
id name number_of_countries
--------- ---------- --------------------------------
1 Aaron 3
and the SQL statement:
SELECT B.id,B.name,COUNT(DISTINCT A.name) AS number_of_countries
FROM country A, staff B
WHERE B.id=A.person
GROUP BY B.id,B.name
HAVING COUNT(DISTINCT A.name)>:count_var
Which of the following values does :count_var require to print out the above report?
A. 1
B. 2
C. 3
D. 4
Answer: B

16. Given the code:
EXEC SQL EXECUTE IMMEDIATE :sqlstmt
Which of the following values must sqlstmt contain so that all rows are deleted from the STAFF table?
A. NULL
B. DELETE FROM staff
C. DELETE * FROM staff
D. DELETE ANY FROM staff
E. DELETE ALL FROM staff
Answer: B

17. Given the following statements:
EXEC SQL INSERT INTO employee VALUES(:new_emp, :new_name)
EXEC SQL UPDATE company SET num_employees=num_employees+1
WHERE company_id=1
EXEC SQL COMMIT
Which of the following can be added to the database so that the company table will still be updated without the need for the explicit UPDATE SQL statement?
A. An INSERT trigger on COMPANY
B. An UPDATE trigger on COMPANY
C. An INSERT trigger on EMPLOYEE
D. An UPDATE trigger on EMPLOYEE
Answer: C

18. Which of the following CLI/ODBC functions will delete rows from a DB2 table?
A. SQLDelete()
B. SQL_Delete()
C. SQLExecute()
D. SQLBulkDelete()
Answer: C

19. Which of the following methods must be used for deleting a row using the Statement interface in JDBC?
A. delete()
B. update()
C. execute()
D. executeDelete()
E. executeUpdate()
Answer: E

20. Which of the following SQL extensions is required to redefine MAX() for a distinct data type?
A. triggers
B. stored procedures
C. user defined functions
D. user defined constraints
Answer: C

21. Which of the following is a benefit of user defined functions?
A. Improving application concurrency
B. Simplifying application maintenance
C. Reducing memory requirements on the server
D. Reducing disk space requirements on the server
Answer: B

22. Which of the following functions is NOT a valid CLI function for returning diagnostic information?
A. SQLError()
B. SQLGetError()
C. SQLGetSQLCA()
D. SQLGetDiagRec()
E. SQLGetDiagField()
Answer: B

23. Given the following variable declaration in a C language program:
SQLHANDLE henv;
Which of the following allocates an environment handle using CLI?
A. SQLAllocHandle(&henv, SQL_HANDLE_ENV);
B. SQLAllocHandle(SQL_HANDLE_ENV, &henv);
C. SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
D. SQLAllocHandle(SQL_NULL_HANDLE, SQL_HANDLE_ENV, &henv);
Answer: C

24. Given the following table:
CREATE TABLE EMPLOYEE
(NAME CHAR(10), SALARY DEC)
INSERT INTO EMPLOYEE (NAME,SALARY) VALUES ('SMITH', 30000)
INSERT INTO EMPLOYEE (NAME) VALUES ('JONES')
INSERT INTO EMPLOYEE (NAME,SALARY) VALUES ('ALI', 35000)
Which of the following statements will produce the lowest value:
A. SELECT MAX(SALARY) FROM EMPLOYEE
B. SELECT MIN(SALARY) FROM EMPLOYEE
C. SELECT AVG(SALARY) FROM EMPLOYEE
D. SELECT SUM(SALARY) FROM EMPLOYEE
E. SELECT SUM(SALARY)/COUNT(*) FROM EMPLOYEE
Answer: E

25. Given the following table:
CREATE TABLE EMPLOYEE
(NAME CHAR(10), SALARY DEC NOT NULL WITH DEFAULT)
INSERT INTO EMPLOYEE (NAME,SALARY) VALUES ('SMITH', 30000)
INSERT INTO EMPLOYEE (NAME) VALUES ('JONES')
INSERT INTO EMPLOYEE (NAME,SALARY) VALUES ('ALI', 35000)
Which of the following statements will retrieve more than one row:
A. SELECT MAX(SALARY) FROM EMPLOYEE
B. SELECT AVG(SALARY) FROM EMPLOYEE
C. SELECT SUM(SALARY)/COUNT(*) FROM EMPLOYEE
D. SELECT SALARY/(SELECT SUM(SALARY) FROM EMPLOYEE) FROM EMPLOYEE
E. SELECT SALARY FROM EMPLOYEE WHERE SALARY IN (SELECT (SALARY/(SELECT SUM(SALARY) FROM EMPLOYEE)) FROM EMPLOYEE)
Answer: D

26. Which of the following will retrieve results that will only be in lower case:
A. SELECT NAME FROM EMPLOYEE WHERE NAME='ali'
B. SELECT NAME FROM EMPLOYEE WHERE LCASE(NAME)='ali'
C. SELECT UCASE(NAME) FROM EMPLOYEE WHERE LCASE(NAME)='ali'
D. SELECT NAME FROM EMPLOYEE WHERE NAME IN (SELECT NAME FROM EMPLOYEE WHERE LCASE(NAME)=LCASE('ALI'))
Answer: A

27. Given the expression:
WITH most_cities AS
(
SELECT b.id,b.name,a.cities
FROM country a, staff b
WHERE a.person = b.id AND
cities > :threshold
)
SELECT * FROM most_cities
In which of the following do MOST_CITIES exist?
A. memory
B. USERSPACE1
C. user tables
D. system catalog tables
Answer: A

28. Given the code:
EXEC SQL WITH most_cities AS
(
SELECT b.id,b.name,a.cities
FROM country a, staff b
WHERE a.person = b.id AND
cities > :threshold
)
SELECT id,name,cities FROM most_cities
INTO :id, :name, :cities
WHERE cities IN (SELECT MAX(cities) FROM most_cities)
Which of the following is the scope of MOST_CITIES?
A. The application
B. The DB2 instance
C. The current statement
D. The current unit of work
Answer: C

29. Given the SQL statement:
WITH management (name,totalpay,department,location) AS
(
SELECT a.name, (a.salary * :bonusrate), b.deptname, b.location
FROM staff a, org b
WHERE a.dept=b.deptnumb AND a.job='Mgr'
)
SELECT * FROM management WHERE totalpay > :threshold
The SQL statement shown above is an example of which of the following?
A. system table
B. temporary tablespace
C. user table in database
D. common table expression
E. permanent view for schema
Answer: D

30. Given the SQL expression:
WITH shifttime (starttime, endtime, shifts) AS
(
SELECT a.starttime, a.endtime, 0
FROM work a
WHERE a.starttime=2
UNION ALL
SELECT b.starttime, b.endtime+a.endtime, b.shifts+1
FROM work a, shifttime b
WHERE b.endtime=a.starttime
)
SELECT *
FROM shifttime
Which two of the following statements are correct about the SQL expression shown above?
A. The expression is recursive
B. The expression may contain an infinite loop
C. The expression contains an OUTER JOIN clause
D. The expression is not supported
E. The expression contains incorrect syntax for UNION
Answer: AB


© 2014 Cheat-Test.com, All Rights Reserved