Binary Cipher
(For Learning purpose only, as it is won’t come in the exam)
1)Application B wants to read a subset of rows from table EMPLOYEE multiple times inside the same transaction. Which isolation level should be used in order to guarantee that every time
the same set of rows is returned?
a) Currently committed.
b) Read stability
c) Repeatable Read
d) Uncommitted Read
Ans:-C
2)What are the entities used to model data in the relational Model?
– A collection instances of entities.
– Relations, attributes and tuples.
– A collection instances of record types.
– Tables, rows and columns.
Ans:-B
3)if TAB1 is created using the following statement CREATE table tab1 (col1 integer not null, col2 char(3) constraint cst1 check (col1 in (1,2,3,4))) Which of the following statements will successfully insert a record into table TAB1?
insert into tab1 values(0,’a’)
insert into tab1 values(null, ‘abc’)
insert into tab1 values(4,’a’)
(4,’abcdefhijklmnopq’)
Ans:-C
4)When attempting to establish a connection to a database residing on your local machine, which command can help u determine why the following message was displayed. Sql1013N the database alias name or database name “SAMPLE” could not be found. SQLSTATE=42705
list database directory
list admin node directory
list node directory
list dcs directory
Ans:-A
5)which of the following will give u the best performance to large amounts of data into a db2 table
a) Import
b) Load
c) Fast import
d) bulk insert
Ans:-B
6)Best define transaction is?
A sequence of one or more SQL operations grouped together, also known as single unit of work
A set of independent operations that can be executed in parallel
A data isolation level that can help prevent deadlocks by allowing reads on previously committed data.
An object of a DB2 database
Ans:-A
7) consider the following cmds:-
• create tablespace mytbsp1 managed by automatic
• what will the command result in?
a) Creation of a normal table space called mytbsp1 that is managed automatically.
b) Creation of a user table space called mytbsp1 that is managed automatically.
c) Creation of a temporary table space called mytbsp1 that is managed automatically.
d) This command will return an error to the user.
Ans:-D
8) which data model was created with the focus of providing better data independence?
Relational
Hierarchical
Semantic
Network
Ans:-A
9 )A dirty read occurs when?
A transaction read the same row of data twice and returns diff data values with each read.
A search based on some criterion returns additional rows after consecutive searches during a transaction.
Uncommitted data is returned, but the transaction that originated them as rolled back.
Two transactions read and then attempt to update the same data, the second update will overweight the first update before it is committed.
Ans:-C
10) How can an application modify the isolation level of operations running against a db2 database?
Isolation level can be changed for a particular SQL statement.
Isolation liven can only because changed for the whole user section and all operations in that section will be effected.
Isolation level to be used can be specified by executing the commit operation.
Isolation level is determined at the moment an application connects to the database. To change the isolation level, an application is required to create the new connection specifying desired level.
Ans:-D
11)considering the SQL statement below, which option best describes what app.tax is
select app.tax(salary) from employee
user defined function
stored procedure
Trigger
User defined type
Ans:-A
12) which of the following programming languages can be user to develop UDF’s?
java, JavaScript, SQL
C/C++, java, .NET languages
SQL, C#, .nET , Perl
Perl,C/C++, SQL
Ans:-D
13)what is authorization?
Authorization is a process that checks whether you have sufficient provides to perform the desired databases operation
Authorization is the process where the DBA gathers info to see who will have access to the database.
Authorization is the process where the DB database checks with windows security to see if you have access to the DB2 database
Authorization is a process that validates that you are who you claim to be by verifying your user ID and password.
Ans:-A
14)if circular logging is used, the following types of backup are allowed
•online full backup
•offline full backup
•online incremental backup
•offline incremental backup
Ans:-B
15)which of the following is not a supported type of trigger?
–After
–Before
–During
–Instead of
Ans:-C
16) which of the following is not true?
Structured query language is the standard language for manipulating data in relational
database management system
Dbms act as an interface used to communicate with databases
The hierarchal data model organizes data in tables with columns and rows.
DB2 is database management system.
Ans:-D
17) When does authentication takes place?
Just before DB2 executes is SQL statement sent by a client application
•When the user connects to the database
•When the user disconnects to the database
It is part of db2 server process.
Ans:-B
18) Your company has asked you to create a database to store and organize data about the structure of the company. You want to store branch and employee data as well as data on the products that each employee in your company deals with. In order to reduce redundancy, how many tables should you create? Consider that your model should be tailored to reduce the possible number of NULL values in the table(s).
1
2
3
4
Ans:-C
19) which of the following tools would u use if you need to execute SQL statements against a Db2 database from the command line?
DB2 Health center
Db2 command line processor
Db2 command line advisor
Db2 control center
Ans:-B
20) which of the following is true about table spaces inDB2?
A table space is a file managed by the operating system where db2 can store data.
A table spaces is always associated with the buffer pool
A table space’s to store user’s temporary table data is automatically is created when database is created.
A table space can be managed by the database (DB2) or by external application
Ans:-B
21) which of the following statement is true?
the owner of a table does not automatically receive control privilege for the table
the owner of a table automatically receives control privilege but does not automatically receive all other table privileges.
if the control prev is revoked from the table owner, all the other privies that are automatically granted to the owned table ,when the table was created are automatically reviled.
If the control privilege is revoked from the table owner, all other privileges that were automatically granted to the owner when the table was created need to be explicitly revoked.
Ans:-C
22) which of the following installation packages does not contain the GUI administration
tools?
DB2 data server enterprise edition
Db2 data server express-C edition
IBM data server client
IBM data server Runtime
Ans:-D
23) Application A is running under the repeatable read isolation level and holds an update lock on table TAB!. 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?
Repeatable Read
Read stability
Cursor Stability
Uncommitted Read
Ans:-D
24) which of the following us characteristic of high availability?
It allows compression of data
It allows definition of a policy to guarantee quality of service to applications.
It allows replication of data to a standby server that can take over in case of failure.
It allows high performance of a DB2 server by distributing workload across several machines.
Ans:-C
25) which of the following is true about Static SQL?
It takes advantage of the table statistics are runtime.
The structure of the SQL statement must be completely specifies at the precompilation
phase.
It is compiled and executed by an application at run-time.
Static SQL does not exist.
Ans:-B
26) which of the following is true about relation databases and the relation data model?
A table does not allow duplicates and a relation does.
A domain is the smallest unit of data.
A column is the equivalent to tuple.
A domain is the set of some possible values for a specific field.
Ans:-D
27)which of the following is NOT a SQL/XML function in DB2 9.7?
•XMLQUERY
•XMLEXISTS
•EXISTS
•XMLCAST
Ans:-C
28) which of the following elements are shown in a DB2 access plan?
Configuration parameters relevant to optimization.
Properties for operators.
Statistics
All of the above.
Ans:-D
29 In embedded SQL, which of the following is an example of how to establish a connection to the sample database in DB2?
•EXEC SQL connect to sample
•DB2 Connect to DB sample
•EXEC connect to sample
•EXEC db2 connect to database sample
Ans:-D
30) In db2 9.7 which of the following functions can be used to create tables from XML documents?
•XMLTABLE
•TABLEXML
•XML2RELATIONAL
•BLE
Ans:-A
31) what is the default isolation level in Db2 9.7 for Linux, Unix and windows for new databases?
Cursor stability
Cursor stability with current committed semantics
•Uncommitted read
•None of the above
Ans:-B
32) which of the following statements revoke access to sample database from user TOM?
Revoke connect on sample from user tom
Revoke access from user tom on database
Revoke access from user tom on sample.
Revoke connect on database from user tom.
Ans:-A
33) which of the following statements is false regarding buffer pools?
Buffer pools work as cache, storing data that is read from table spaces.
Pages in a buffer pool can be 4k,8k,16k,32k in size
A database can only have one Buffer pool.
The size of a buffer pool can be automatically resized STMM.
Ans:-C
34) Assuming that you are currently connected to a db2 database which of the following
commands will terminate your connection?
•Db2 connect terminate
•Db2 connect end
•Db2 Connect kill
•Db2 Connect finalise
Ans:-A
35)In which of the following Logging type , SECONDARY log files are allocated if
Primary Log files are full?
a) Incremental
b) Circular
c) Archival
d) Delta
Ans:-B
36)Given the option below which of the following it can remove a table from DB2?
remove
delete
del
drop
Ans:-D
37) Which of the following is true about wellformed xml document?
mess or more root nodes
tags can here at most are attribute
always has a single document node
end tags are optional
Ans:-C
38) Which of the following is true about table space in DB2?
a table space is a file managed by the as where db2 can store data
a TS is always associates with a buffer pool
a TS to store user’s temporary table data is automatically created when DB is created
a TS can be managed by the database (db2) or by an entered application
Ans:-B
39) When using Dbms which do not apply
Accesses data using DML such as SQL
define your DB schema using a DDL
data is stored in a hierarchical model
you can use normalization to avoid redundant data in tables
Ans:-C
40) Which of the following objects you would need create in order to executed a block of code every time table is up date
an after trigger
a UDF
a stored procedure
an user defined type
Ans:-A
41)Using DB2 CPP to access a remote DB for the 1st time it is necessary
Catalog the remote system or node and then catalog the DB written the remote node
First register the remote system user in system catalog and then setup a SQL Communication to the remote node
db2 cannot use DB is remote system unless SYSADMIN authority is grated to local user
first list all the remote DB with the db2 list DB directly command and the select the remote DB from the list
Ans:-A
42)Which of the following is true aboutcolumns?
each columns consists of one or more records
columns are store the individual pieces of info are stores for each record
columns must be designated a specific data type
columns are also known as records
Ans:-C
43) Which of following delete sample database?
•db2 drop sample
•db2 delete database sample
•db2 drop sample DB
•db2 drop database sample
Ans:-D
44)Which of the following is not part of the x query flow expression?
for clause
let clause
order by clause
with clause
Ans:-D
45)which of the following statements is not true regarding table spaces on DB2?
a)a table space is a logical object in between logical table and physical containers.
b)All tables, indexes and other data are stored in table space.
c)A table space is a logical object required to store data, indexes and tables in temp
memory.
d)A table space is always associated to a buffer pool.
Ans:-D
46) which is true about RDBMS
a column can store value of different data type
a table consists of column and rows
rows in the same table can have a different set columns
rows are also known as fields of the Table
Ans:-B
47) Which is true about dynamic SQL
it is precompiled about binded in dynamic DB
the structure of an SQL statement must be completely specified at precompile time
it is compiled and executed by a application at run time
dynamic SQL does not exist
Ans:-C
48)Which is an xml –based language for transforming xml document?
XHTML
XSLT
HTML
JAVA
Ans:-B
49)What are the entities used to model data in relational model?
a collection of instances of entities
relation attributes and types
a collection on of instance of record type
table row and rows
Ans:-B
50)SQL used to remove data from table users
•remove table users
•drop table users
•delete from table users
•alter table users
Ans:-C
51)Which of the following describes the fact that a committed transaction guarantee that all the operations are completed and in a roll backed transaction effected of all operations are reverted
consistency.
atomicity
isolation
durability
Ans:-B
52)statement that can add records to table
a. addrec
b. addrecord
c. insert
d. add
Ans:-C
53 Importing a large amount of data in db2 db. Considering you must have logged intl.about the row imported and that want to perform the load as fast as possible which of the tools would be best choice for the join
db2 insert
db2 import
db2 load
db2 clp
Ans:-C
54)which of the following is characteristic difference b/w cursor stability and currently committed
a. reader block reader
b. reader block writer
c. writer block reader
d. writer block writer
Ans:-D
55 which of the following statement is false about transactions
a. operations inside a transaction are executed in a parallel
b. only 1 commit statement can be executed inside the same transaction
c. transaction guarantee atomicity of a group of operation
d. a rollback or commit statement finalize a transaction
Ans:-A
56 Assuming that you are current connected to test db which of the following will allow the view of the database setting with details
a. db2 get db cfg
b. db2 list db cfg
c. db2 list all from db cfg
d. db2 get all from db cfg
Ans:-A
57)which of the following statement grants user john the ability to insert data to
table 1
a. grant add on table to john
b. grant insert on table 1 to john
c. grant add on table 1 to user john
d. grant insert on table1 to user john
Ans:-D
58)when a user is connected to database which of the following privileges is required for the user to use a package
a. bind
b. bindadd
c. execute
d. use
Ans:-A
59) which of the following operation will perform an offline table space recovery
a. restore database sample (mytbsp) offline from/tbspbkp
b. restore database sample table space (mytbsp) from / tbspbkp
c. restore database table space (mytbsp) online from /tbspbkp
d. there is no option to restore a table space in db2
Ans:-D
60)Which of the following is a tool to configure automatic database backup
a. Configure automatic maintenance wizard
b. design advisor
c. explain tool
d. export utility
Ans:-A
61) Considering the SQL statements below, which option best describes what ABS in
UPDATE tb1 SET col1 =ABS (col2)
a)Stored Procedure
b)Built in function
c)Trigger
d)User Defined
Ans:-B
62) Which of the following tools can be used to develop stored procedures?
a) DB2CLP
b) DB2 command Editor
c) IBM Data studio
d) All of the above
Ans:-D
63)Which of the following options can be used to check the minimum point is time (PIT) for a table space restore?
a)List Table spaces show ALL
b)Restore Database sample table space (mytbsp) from 1 tbspbkp)
c)Restore database sample table space ( mytbsp) online from / tbspbkp
d)get snapshot for Table space on <database>
Ans:-D
64)Which of the following is true about relational Databases?
A) A column can store values of different data types.,
B) A table consists of columns and rows.
c) Rows in the same table can have a diff set of columns
d) Rows are also known as fields of the table.
Ans:-B
65) Best describes what an incremental cumulative backup is?
a) Backup all of the data that has changed since the last successful full or delta
backup.
b) Backup of a single table space.
c) Backup of the entire database.
d) Backup all of the data that has changed since the last full backup.
Ans:-A
66) what is SQL/XML?
a)SQL/XML is a communication protocol for DB2 databases.
b) is a part of the XQuery standard and provides various publishing functions to transform XML data into relational form and vice versa.
c)SQL.XML is a language that provides various publishing functions to transform XML data into
rational form and vice verso,
d) Is an extension to SQL standard and provides various publishing functions to transform XML data into relational form and vice versa.
Ans:-B
67) Which of the following is NOT true about columns?
a)Each col consists of one or more fields.
b)Each col contains a specific type of information
c)Col must be designated a specific data type
d)Col are also known as fields.
Ans:-A
68)In embedded SQL code, which of the following is true about delimiters?
a) Used by the OS to delimit system variable declaration
b) Used by the DB to indicate the end of Column
c) Used by the compiler to indicate the end of the program.
d) Used by the precompiler to identify sql statements to be translated.
Ans:-D
69) which of the following is false about views
a. do not contain real data
b. any view can be updated independent of its definition
c. when changes are made to data through a view the data is changed in the underlying
table
d. can be used interchangeably with table when retrieving data
Ans:-b
70) Which of the following is not a tool that comes with db2
A.db2 command window
B.db2 command line processor
C.db2 script editor
D.db2 first steps
Ans:-c
71) A company has a large amount of data to store on wants to be able to do the
following with the data
a)text files
b)database
c)spreadsheets
d)comma delimited data files
Ans:-B
72 given the following table definition Sales
Sales_date date
Sales_person char(20)
a)delete * from sales where year(sales_date)=1995
b)drop from sales where year(sales_date)=1995
c)drop * from sales where year(sale_date)=1995
d)delete from sales where year(sales_date)=1995
Ans:-D
73) which of the following statement is false
a)primary key uniquely identifies a row in a table
b)foreign key is asset of column that refers to a primary key of another table
c)a primary key must always be defined in a table
d)primary keys and foreign key are used to enforce data integrity
Ans:-C
74) given the following update statement
Update employees set workdept=(select deptno from department)where workdept is null
Which of the following describes the result if this statement is executed?
a)the statement will fail because an update statement will not contain a subquery
b)the statement will fail because the workdept column appears in the set and where clause at the same time
c)the statement will succed if table dept has only one row
d)the statement will only succed if every record in the employees table has a null value in the workdept column
Ans:-C
75)given the following create table statement
Create table student_marks
a)select student_name from student_marks where phys=’’
b) select student_name from student_marks where phys=NULL
c) select student_name from student_marks where phys=0
d)select student_name from student_marks where phys is NULL
Ans:-D
76) When using a relational database which of the following is false?
a)retrieve data using sql’s data manipulation language(dml)
b)define the datbase structure using sql’s ddl
c)store the data in class instance and fields
d)use normalisation to avoid redundant data in your tables.
Ans:-C
77) which of the following is false about the information model?
a. provides the details to implement objects in db’s.
b. defines relationships between manged objects.
c. provides an abstract mangement of objects to a conceptual level.
d. provides a conceptual /abstract model for designers.
Ans:-A
78) what functionality does automatic storage provide?
a. automatic storage simplifies storage management by allowing you to specify storage paths where the database management can place tables space data
b. automatic storage is an option that allows transactions that are eritten to do database to be automatically comitted
c. automatic storage is a function of database that allows tables to be backedup automatically on a set schedule
d auto storage can be used to automatically reorganize the data as a physical media is order to improve performance
Ans:-A
79) Which of the following is created automatically when a db2 database is created?
Temp space1 table space
Sys cat space table space
IBMDEFAULT buffer pool
All the above
Ans:-D
80)Which of the following is true about the information model?
Provides the specific of objects in a detailed level
Describes objects but not relationships between them
Provides the details to implement objects in database
Provides conceptual abstract in database
Ans:-D
81) what are the advantages of using stored procedures?
Reduce network traffic
Centralize the location where code is stored and changed
Enforcement of business rules
All the above
Ans:-D
82)create table color(cid integer not null primary key,c.desc char(20))
create view object(oid char(2),odesc char(20), cid integer references color(cid)on select cascade) which options correctly describes the change in 2 tables after the statement below is executed.
Delete from color where cid=2
Only rows in table color will be affected
Rows in both color and object with cid=2 will be deleted
Only rows in table object will be affected
No rows will be deleted
Ans:-B
83)Which of the following stores db2 configuration information?
Dbmcfg
Dbcfg
Db2 profile registey
All the above
Ans:-D
84)If secondary log files are to be allocated until the unit of work commits or stores is exhausted , which type of logging is used
Infinite logging
Archival logging
Circular logging
It cannot be done in db2
Ans:-A
85) Which of the following privileges permits a user to create objects with in a schema?
Control
Alter
References
Createin
Ans:-D
86) Which of the following is false about keys in the relation model
Foreign keys are used to identify foreign columns
Primary keys unique identify each tuple of the relation
Primary keys are defined by a subset of attributes of a relation
A relation can have multiple foreign keys
Ans:-C
87)Which of the following privileges permits a user to update the comments associated with a table?
Control
Alter
Createin
References
Ans:-B
88)Which of the following statements is true about pure xml technology?
With pure xml technology there is a part of the db2 engine that can natively handle xquery
In pure xml,a query can combine sql with xml constructs;therefore integrating relational and hierarchical data in one query
In pure xml,the format in which xml data is processed is the same as the format in which it is stored, that is, there is no conversion required
All the above
Ans:-D
89)Which of the following best describe the advantages of executing the set of dml statements inside a single transactions
The dbms records those operations in the audit logs since they are grouped inside a transaction
The dbms stores the transaction operation so that the client applications can return the transaction easily
The dbms guarantees that either all changes performed by the dml are stored or in case of errors,the changes are undone leaving the database in a consists state
The dbms guarantees that all operations are always executed successfully
Ans:-C
90) BUFFER POOL is associated with table space so that they can increase performance by cashing data in memory, which is from
A. A table space can only be associated with a buffer pool that has same page size
B. A table space can only be associated with a buffer pool that has a smaller or same page size
C. A table space can only be associated with a buffer pool that has larger or same page size
D.A tablespace can only be associated with a buffer pool that has larger page size
Ans:-A
91)which of the following programming language can be used to develop applications that required access to a DB2 database application
A. java
B. php
C. c#
D. all the above
Ans:-D
92)which is not DML statement?
A. alter
B. insert
C. select
D. update
Ans:-A
93) which is false about relational database?
A. each column contains a specific type of information
B. a primary key can be composed of only one attribute
C. each column stores individual piece of information for a record
D. foreign key are an attribute in one relation whose values match primary key of another relation
Ans:-D
94)DB2 authentication server is defined within
A. database configuration
B. database managed configuration
C. DB2 registery
D. systems catalog
Ans:-B
95) which of the followoing is not XQuery function
A. db2_fn:Xml column
B. db2_fn:sal query
C. db2_xml record
D. none of the above
Ans:-C
96)false about keys in relational model
A. foreign keys are used to identify foreign columns and rows in a relationship
B. primary keys uniquely identify each tuple of the relation
C. primary keys are defined by a subset of attribute of a relation
D.a relation can have multiple foreign keys
Ans:-C
97) which of the following table space is not created by default
user temporary space
syscatspace
system temporary space
user space1
Ans:-A
98) After the following SQL statement is executed
GRANT All PRIVILeGES ON TABLE STUDENT TO USER USER1
ASSUMING USER USER1 HAS NO OTHERR AUTHORITIES OR PRIVILAGES WHICH OF THE
FOLLOWING ACTIONS IS USER1 ALLOWED TO PERFORM?
a)grant all privilages on table student to other users
b)drop a view associated to the table student
c)drop the table student
d)none of the above
Ans:-C
99)which of the following is free version of db2?
a)DB2 Express-c
b )DB2 Express
c )DB2 Enterprise
d )DB2 workgroup
Ans:-A
100) create or replace proc1(p1 integer,out p2 varchar(20))
Begin…….. End@
Using db2clp, which of the following commands would successfully executed procedure proc1?
a)call proc1(1,default)
b) call proc1(1,?)
c) call proc1(1)
d)none of the above
Ans:-B
101) which of the following is a reason for using database to store data instead of many alternatives?
a)database store data in an instructed way for faster insert performance
b)db’s are cheaper to use than other alternatives
c)db’s are the interface function of most os’s, thus its widely available to applications
d)databases allow multiple users to handle and change the data without losing data and its
consistency
Ans:-D
102) which of the following options best describes a domain found in the table below?
Create table employee(id integer not null,name varchar930) not null,extensions integer not null,manager varchar(30) not null,primary key(id));
Sample contenet of the table employee at a given point in time:
Id NAME Extension Manager
1 john 53412 y
2 Susan p 54123 N
3 jennifer L 51234 N
a)Manager domain(n)
b)Name Domain=(set of all positive names)
c)Extension Domain(53412)
d)ID domain=(1,2,3)
Ans:-B
103)consider table tab1 defined as below:
Considerably the following statements are successfully executed in clp with auto commit disabled, how many rows are inserted in tab1?
Insert nto tab1 values(1,’john’)
Insert nto tab1 values(1,’john’)
Commit
Insert nto tab1 values(1,’john’)
Rollback
a)0
b)1
c)2
d)3
Ans:-C
104) Current_employees past_employees
Empid Integer not null Empid integer not null
Name char(20) Name char(20)
Salary decimal(10,2) Salary decimal(10,2)
Assuming both tables contain data, which of the following statements will not successfully add the 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)
Ans:-D
105)Current_employees past_employees
Empid Integer not null Empid integer not null
Name char(20) Name char(20)
Salary decimal(10,2) Salary decimal(10,2)
Enabled char(1)
Assuming past_employees is empty and current_employees is populated, which of the following statement copy all employees(having the enabled column with a value of ‘N’) from current_employees to past_employees?
a)insert into past_employees values(current_employees where enabled=’N’)
b) insert into past_employees values(select empid,name,salary from current_employees)
c) insert into past_employees values(select * from current_employees where
enabled=’N’)
d) insert into past_employees values(select empid,name,salary from current_employees where enabled in (‘N’))
Ans:-D
106)which of the following describes the C in Db2 Express-c edition
a)c stands for C language
b)it is free to develop,destroy
c)it is 2gb memoey installable
d)all the above
Ans:-b
107)Assume a table which contains the following columns
Emp_id ,Emop_name,Phone,Email,Salary Which of the following is the simplest way to restrict user from viewing salary information
While still allowing them to see the other values?
A.encrypt the tables data
B.create a view that does not contain the salary column. Grant access to the view and revoke access from the original table.
C.revoke select access from the original table from the salary column from users who should not see salary data
D.store salary data in a separate table and grant select privilege for that table to the appropriate users
Ans:-B
108) given the following two tables:
Names(table)
Student _name Student_number
Wayne gretzky 99
Jaromirjagr 68
Bobby orr 4
Brett hull 23
Mario Lemieux 66
Marks(table)
Name Marks
Waynegretzky 80
Bobby orr 94
Brett hull 77
Mario Lemieux 83
How many rows would be returned using the following statement?
Select distinct student_name from names,marks
a)5
b)20
c)9
d)6
Ans:-A
109) Consider the following scenario. You have 3 tables with the same name(TABLE_1)under 3 different schemas( SYSIBM,DEFAULT AND DB2INSTL).You are cancelled to a db2 database in your linux server as default and issue the following statement.
Select * from table_1
Which table will you be selected data from.
a)you get an error because your query is ambiguous, as the table schema is not specified.
b)db2 inst1.table_1
c)default .table_1
d)sysibm.table_1
Ans:-B
110)
col_1 col_2
A 10
B 12
C 14
Col_A col_B
A 21
C 23
D 25
Assuming the following results are desired
Col_1 col_2 col_A col_B
A 10 A 21
B 12 – –
C 14 C 23
D 25 – –
Which 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 tab1full outer join tab2 on col_1=col_A
Ans:-B
111)What is trusted context?
a)It is a special area in a buffer pool that can be written only by a seletec set of users.
b)it is a db2 capabilty that allows application to change users without breaking the connection
to the database .
c)it is a db2 capability that allows users to establish a connection to the database without
providing username or password.
d)it is a types of container in a table space that allows faster I/O operations.
Ans:-C
112)Consider a table tab1 having the following value
Col1 col2
A 10
B 20
c 30
A 10
C 20
D 40
Which of the following statements can be used to modify the values in tab1 so that
the final context is as follows
Col1 clo2
C 30
D 40
A• Delete from tab1 Insert into tab1 values(‘c’ 40) Insert into values(‘d’ 40)
B• Update tab1 set col1null where col2in (10,20)
C• Delete from tab1 where col2 not in (30,40)
D• Delete from tab1 where col2 in (30,40)
Ans:-C
113)
col_1 col_2
A 10
B 12
C 14
Col_A col_B
A 21
C 23
D 25
Assuming the following results are desired
Col_1 col_2 col_A col_B
A 10 A 21
B 12 – –
C 14 C 23
– – D 25
Which of the following joins will produce the desired results?
Select * from tab1 full outer join tab2 on col_1=col_A
Select * from tab1 inner join tab2 on col_1=col_A.
Select * from tab1 left outer join tab2 on col_1=col_A
Select * from tab1 right outer join tab2 on col_1=col_A
Ans:-A
114
GUESTS
Name age
Jenny 34
Rodney 36
Oliver 21
Angie 42
Tom 28
Vinlenzo 25
What names would be displayed as the results of the following
SQL Query
Select name from guests where name like ‘%0_’
a. Oliver.
b. Vinlenzo
c)Tom
d. Rodney
Ans:-C
115) Which of hte following is true?
Embedded SQL can only be static
Static sql is normally faster than dynamic SQL
TDBC and ODBC use static SQL
SQLJ is embedded dynamic SQL in java
Ans:-D
116) Which of the following is true about DB2 instances?
Instances are related between each other through the dbmcfg
If instance A crashes, no other instance running in the system will work either
If instance A is down , databases in the instance are inaccessible
Within instance you can only create one and only are database
Ans:-C
117) Which of the following is true about IBM data studio?
Data studio is installed automatically when DB is installed
Data studio can be use to develop triggers when the CREATE TRIGGER statement is executed as a script
Data studio stand-alone package is free, while the IDF package is chargeable
Data studio, regardless the package supports shell-sharing.
Ans:-B
118) Which of the following statements is false?
Attributes in the relational model map to rows in a relational database
In an ERD, an entity is represented with a rectangle
ONE -To-ONE and many – to- many are relationships you can represent in a ERD
Entities in the relational model map to tables in a relational database
Ans:-A
119)Which of the following best describes the incremental backup functionality?
BACKUP ALL OF THE DATA THAT HAS CHANGED SINCE THE LAST SUCCESSFUL OR
DELTA BACKUP
Backup of a single table space
Backup of the entire database
Backup all of the data that has changed since the last full backup
Ans:-A
120) Which of the following types of logs contain committed and externalized data?
Online active logs
Offline active logs
Passive logs
Active logs
Ans:-A
121) Which of the following is false about the DB2 environment?
A TCP IP port uniquely identifies a DB2 instance
There can be more than 1 instance running at a given time
There is a conflict if you attempt to create a database in instance B with a database name that has already been used in instance A
There is a conflict if you attempt to create a table space in database A with a table space name that has already been used in the same database.
Ans:-C
122)Which of the following should you inspect when you encounter a problem with DB2?
The db2diag.log file
The DB2 information center
The administration center
The administration notification log
Above all
Ans:-E
123) Which of the following is the ideal version of DB2 for a small ; startup company will no budget to invest in software?
DB2 Express-C
DB2 express
Db2 Enterprise
DB2 Workgroup
Ans:-A
124)Which of the following is not a valid DB2 client
IBM data server client
IBM dataserver runtime client
DB2 Runtime Client merge module for windows
IBM dataserver Thin client
Ans:-D
125) Which of the following is false about the DB2 editors , clients and drivers?
All DB2 clients and drivers are available at no cost
DB2 express Edition is the only server edition available at no cost
DB2 Server include DB2 client components
A system running DB2 workgroup can be used to connect to a database in a system running DB2 express using the clp
Ans:-B
126) Identify the option with entities that can be used in ERD
Product, color, Height
Yellow, Big, Long
John, dog, airplane
Person, Animal, plant
Ans:-D
127) Which of the following is true about database logging?
Database logging can be turned off completely at the database level
primary logs are allocated on demand when needed
Archive logging must be enabled to recover from situations where human errors introduced incorrect data into the database
Circular logging is more appropriate in product Environment
Ans:-D
128) Which of the following is true about a well-formed XML document?
Has one or more root element
tags can have utmost one attribute
attribute values must be enclosed in double quotes
End tags are optional
Ans:-C
129) Which of the following are entities that should use a one-to-man relationship in an ERD?
person, car
Company, Employee
Grand father, john
Mother , child
Ans:-D
130) Given the following table
COL1 COL2
========= ============
A 10
B 20
C 30
A 10
D 40
C 30
Assuming that the following results are desired
TAB1
—–
COL1 COL2
====== = ========
A 10
B 20
C 30
D 40
Which of the following statements will produce the desired results?
SELECT UNIQUE * FROM TAB1
SELECT DISTINCT * FROM TAB1
SELECT UNIQUE(*) FROM TAB1
SELECT DISTINCT(*) FROM TAB1
Ans:-B
131) WHICH OF THE FOLLOWING IS not a type of recovery?
Version-restart recovery
Auto-restart recovery
crash recovery
Roll-forward recovery
Ans:-B
132) In embedded SQL code, which of the following is true about delimiters?
Used byte the os to delimit system variable declaration
Used by the database to indicate the end of a column
used by compiler to indicate the end of the program
Used by PRE compiler to identify SQL statements to be translated
Ans:-D
133) What functionality does a automatic storage provide?
Automatic Storage simplifies management by allowing you to specifies storage paths where the data base manager can place table space data
Automatic storage is an option that allows transaction id=s that are written to the database to be automatically committed.
Automatic Storage is a function of DB2 that allows tables to be backed up automatically on a set schedule
Automatic Storage can be used to automatically reorgannize the data on the physical media in order to improve the performance.
Ans:-A
134) Which of the following is false about IBM Data Studio?
IBM Data Studio is free, open source software.
IBM Data Studio is based on Eclipse.
IBM Data Studio IDE package allows for shell-sharing.
IBM Data Studio can be used to develop stored procedures, UDFs and SQL or XQuery scripts.
Ans:-A
135)Choose the option that is true about DB2 backups:
A database backup taken online means that users can access the data while the database while the backup is performed.
You must use the “ONLINE” keyword to indicate a backup will be performed online.
There is no “ONLINE” keyword to indicate a backup will be performed online.
All of the above.
Ans:-B
136)Which of the following events will NOT cause a trigger to be activated?
DELETE
SELECT
INSERT
UPDATE
Ans:-B
137)Which of the following is NOT a characteristic of a relational index?
An index is automatically created for foreign keys.
An index can be unique or non-unique.
An index can consist of one or more columns.
An index can be physically stored in a way that is closely aligned to the data it points to
Ans:-D
138)Which of the following is true about scripts?
Operating System scripts can invoke SQL scripts.
SQl scripts are platform independent.
If a CREATE TABLE statement is a part of a SQL script, you cannot use the default statement terminator.
All of the above.
Ans:-B
139) Which of the best describes the benefits of supporting XQuery’s TRANSFORM expression in DB2:
It can be used to modify both XML and relational data, providing a common language for developers to handle the database data.
When used in a UPDATE statement, it simplifies the update of XML documents stored in the database.
It is very useful to transform XML documents into other formats using stylesheets.
It can be used to automatically clean up unnecessary tags in a XML document, providing smaller documents thus saving space in the database.
Ans:-B
140)Consider the following scenario. We have a table,table_1,which has only one columen storing an integer value. The following statements are executed successfully in the order shown:
CREATE SEQUENCE my_sqy START WITH 1 INCREMENT BY 1 CACHE 5
INSERT INTO table_1 VALUES(NEXT VALUE FOR my_seq);
INSERT INTO table_1 VALUES(NEXT VALUE FOR my_seq);
After a db2 terminate and reconnection to the database, the following statement was issued:
INSERT INTO table_1 VALUES(NEXT VALUE FOR my_seq);
SELECT * FROM table_1
What value will be the last value returned by the query?
1
2
3
6
Ans:-C
141)Your company has asked you to create a database to store and organize data about the structure of the company. You want to store branch, employee data, and product information in three tables BRANCH, EMPLOYEE and PRODUCT. An employee is assigned to work at a branch on a specific product.
With this information, indicate what relationship you should use between these tables considering normalization.
BRANCH is dependent on EMPLOYEE, PRODUCT is the parent of EMPLOYEE.
EMPLOYEE is the parent of PRODUCT and BRANCH.
BRANCH and PRODUCT are parents of EMPLOYEE.
PRODUCT is the parent of BRANCH and EMPLOYEE.
Ans:-D
142) Which of the following is true about instances?
Many instances can be created inside a database.
Many databases can be created inside an instance.
For a given instance you can only create one database.
Two or more instances cannot be running simultaneously.
Ans:-B
143)Which of the following statements is true?
You must always restore a database on the same system where the backup was taken since the backup includes table space layout information.
A table space can be restored from a database backup.
A database backup taken on Windows can be restored on a Linux System.
Full database backup can only be taken offline.
Ans:-B
144)Which of the following is true about database logging?
Secondary logs are allocated dynamically when needed.
infinite logging is possible by setting LOGPRIMARY to -1.
Archival logging is the default type of logging.
Circular logging is recommended for production environments.
Ans:-D
145)Which of the following are entities in an ERD?
House, Size, Height.
Green, Small, Short.
Susan, Cat, Airplane.
Student, Tree, Planet.
Ans:-D
146) Which of the following is true about crash recovery?
It requires online archive logs
It redoes what was committed, and undoes what was not according to the active logs.
It is only possible in circular logging.
It allows for recovery in case of corruption of the data due to human error.
Ans:-B
147) Which of the following is true?
The help command is invoked from the DB2- CLP using”?”.
The Administration Notification log in Windows can be reviewed using the Windows Event Viewer.
If you delete the db2diag.log file, a new one will be generated the next time a problem or warning happens on DB2.
All of the above.
Ans:-C
Got to learn a lot of things from this article. It can be used for any kind of business. Pegasi Media Group the world's leading database vendor and the most preferred organization all over the globe. Our brand is well-known in the market. The organizations of all the sizes have experienced our services and are quite pleased with it. We perform the activities like data cleansing, data appending, data updating, data validation, and data verification on a regular basis before the final delivery. You have also got the tremendous flexibility to choose the geographical location and reach out to the prospects that you target.IBM DB2 Database Software Users Email List