DB2 test-1 with answers

db2 sql interview questions

Moderator: Moderator Group

Post Reply
ballaravi@gmail.com
Member
Posts: 16
Joined: Fri Jun 25, 2010 10:50 am

DB2 test-1 with answers

Post by ballaravi@gmail.com » Wed Dec 08, 2010 2:34 pm

If i am incorrect,please correct me


(1) In the WHERE clause what is BETWEEN and IN? –

a) Group of items refered
b) BETWEEN supplies a range of values while IN supplies a list of values
c) Both A and B
d) None of the above


(2) Which of the following degrades performance?

a) Union
b) Commit
c) Union All
d) Intersection All


(3) How to find out the total number of rows in a DB2 table?

a) COUNT(*)
b) DISTINCT
c) MAX
d) GROUP BY


(4) The Number of bytes occupied by SQLCA is

a) 128 bytes
b) 136 bytes
c) 142 bytes
d) 138 bytes


(5) DCLGEN supplied with DB2 produces _______________ and say whether it is must / not required.

a) SQLCA in side program and it is must
b) Table declaration and it is must
c) Table declaration and it is not required
d) reads the catalog to check for table and must


(6) Give the Sql Code for Program name not found in the PLAN_NAME

a) -811
b) -817
c) -805
d) -803


(7) How do you retrieve the first 5 characters of FIRSTNAME column of DB2 table EMP ?

a) FIRSTNAME(SUBSTR)
b) FIRSTNAME(SUBSTR,1,5)
c) SUBSTR(FIRSTNAME,1,5)
d) SUBSTR(1,5,FIRSTNAME)


(8) What is SPUFI

a) SQL processor User Friendly Interface
b) SQL Processor User Friendly Interaction
c) SQL Processor Using File Input
d) SQL Processing User Friendly Input


(9) Where are the access path information are stored?

a) Plan table
b) Package table
c) Files
d) None of the above


(10) How do you eliminate duplicate values in DB2 SELECT ?

a) DISTINCT
b) ORDER BY
c) GROUP BY
d) COUNT


(11) Given the following DDL statements:
CREATE TABLE tab1 (a INT, b INT, c INT)
CREATE VIEW v1 AS SELECT a,b,c FROM tab1 WHERE a > 250 WITH CHECK OPTION
Which of the following INSERT is correct


a) INSERT INTO tab1 VALUES (200, 2, 3)
b) INSERT INTO v1 VALUES (300, 2, 3)
c) INSERT INTO tab1 VALUES (350, 2, 3)
d) INSERT INTO v1 VALUES (250, 2, 3)


(12) 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 RIGHT OUTER JOIN points ON names.name=points.name
b) SELECT names.name,names.number, points.points FROM names INNER 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) None of the above


(13) Which of the following occurs if an application ends abnormally during an active unit of work?

a) The unit of wok moves to pending state
b) The unit of work moves to CHECK_PENDING
c) The unit of work is rolled back
d) The unit of work remains active


(14) For which of the following database objects can locks be explicitly obtained?

a) User-defined Data Type
b) Columns
c) Triggers
d) Tables


(15) Where are referential constraint definitions stored?

a) The explain tables
b) SYSIBM.SYSTRIGGERS
c) The user table
d) The system catalog tables


(16) What type of locks are handled by ISOLATION

a) Page level
b) Table level
c) Table space level
d) House hold locks


(17) Which SQLCode suits apt that denote a Timestamp error

a) -408
b) -305
c) -911
d) -818


(18) SQLCODE -924 describes

a) Dead lock or Resource unavailable
b) DB2 Connection error
c) Commit not void
d) None of the above


(19) SQLCODE -183 describes

a) String representation of date is invalid.
b) DB2 connection error
c) Not a valid range
d) None of the above


(20) A table contains a list of all seats on an airplane. A seat consists of 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) Uncommitted Read
c) Repeatable Read



(21) SQLCODE -911 describes

a) Dead lock or Resource unavailable
b) Resource type not available
c) Unsucessful execution
d) Object has been deleted


(22) What is CHECK PENDING ?

a) Table is checked against the constraint
b) Database is checked against the referential constraint
c) LOADed with ENFORCE NO option, then the table is left in CHECK PENDING status
d) Table is left in check pending status



(23) Select name, salary from employee-table where exists (select * from dept where deptno LIKE 'c%')
The above SQL will fetch the names and salaries of all the employees where department name starts with 'c'


a) True
b) False
c) Query is incorrect - throws error
d) None of the above


(24) SQLCODE -205 describes

a) Object already exists
b) No primary key mentioned
c) Column name is not column of table
d) None of the above

If i am incorrect,please correct me

srini1508
Member
Posts: 14
Joined: Fri Sep 30, 2011 4:37 pm

Post by srini1508 » Wed Oct 12, 2011 1:40 pm

7) How do you retrieve the first 5 characters of FIRSTNAME column of DB2 table EMP ?

a) FIRSTNAME(SUBSTR)
b) FIRSTNAME(SUBSTR,1,5)
c) SUBSTR(FIRSTNAME,1,5)
d) SUBSTR(1,5,FIRSTNAME)


the answer would be C not D

User avatar
dbzTHEdinosauer
Moderator
Posts: 981
Joined: Mon Oct 02, 2006 8:31 pm

Post by dbzTHEdinosauer » Wed Oct 12, 2011 10:13 pm

(4) The Number of bytes occupied by SQLCA is

a) 128 bytes
b) 136 bytes
c) 142 bytes
d) 138 bytes

b

(5) DCLGEN supplied with DB2 produces _______________ and say whether it is must / not required.

a) SQLCA in side program and it is must
b) Table declaration and it is must
c) Table declaration and it is not required
d) reads the catalog to check for table and must

c

those are a couple that i saw quickly,
sqlcode you can easily look-up
using IBM's z/OS Message Help
you should bookmark the above link.
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.

brahan
Member
Posts: 4
Joined: Fri May 11, 2012 9:33 am

Post by brahan » Fri May 11, 2012 11:11 am

(9) Where are the access path information are stored?

a) Plan table
b) Package table
c) Files
d) None of the above

Ravi(Shanker????) Balla,
Are you sure it is the Plan Table???Isnt it the Package Table?

brahan
Member
Posts: 4
Joined: Fri May 11, 2012 9:33 am

Post by brahan » Fri May 11, 2012 11:22 am

(7) How do you retrieve the first 5 characters of FIRSTNAME column of DB2 table EMP ?

a) FIRSTNAME(SUBSTR)
b) FIRSTNAME(SUBSTR,1,5)
c) SUBSTR(FIRSTNAME,1,5)
d) SUBSTR(1,5,FIRSTNAME)

Ravi(Shanker????) Balla,
Isnt the rite option c???

Anuj Dhawan
Moderator
Posts: 1625
Joined: Sat Aug 09, 2008 9:02 am
Location: Mumbai, India

Post by Anuj Dhawan » Fri May 11, 2012 1:14 pm

Why are you investing time on old therads, there is hardly any chance you'll get a response from the thread starter at this stage.
Regards,
Anuj

Post Reply

FREE TUTORIALS

Tutorials
Free tutorials from mainframegurukul
  • JCL Tutorial
    Covers all important JCL concepts.
  • Cobol Tutorial
    This tutorials covers all Cobol Topics from STRING to COMP-3.
  • DB2 Tutorial
    DB2 Tutorial focuses on DB2 COBOL Programming.
  • SORT Tutorial
    This Tutorial covers all important aspects of DFSORT with examples
  • CICS Tutorial
    This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions



Other References
Mainframe Tools and others