IBM DB2 9 Fundamentals certification (Exam 730): Sample questions about tables, Part 2

Answer ten sample questions about tables from the IBM DB2 9 Fundamentals certification exam study guide to check your level of DB2 knowledge, in Part 2 of the DB2 quiz series.

The following sample exam questions are taken from DB2 9 Fundamentals Certification Study Guide, by Roger E. Sanders (copyright 2007) and are reprinted here with permission from MC Press.

Test your knowledge of DB2 9 by answering the questions below, which are from a real, previous DB2 9 Fundamentals certification exam (Exam 730). When you make your answer selection, click on "Get Answer" to check your answers and read the explanations. If you haven't done so already, be sure to take part 1 of this DB2 9 quiz series about tables.

Sample Questions

Question 1

Given the following two tables:

NAMES

NAME               NUMBER
Wayne Gretsky       99
Jaromir Jagr           68
Bobby Orr                4
Bobby Hull              23
Brett Hull                16
Mario Lemieux       66
Mark Messier         11

POINTS

NAME               POINTS
   
Wayne Gretsky       244
Jaromir Jagr           168
Bobby Orr               128
Brett Hull                 121
Mario Lemieux       189
Joe Sacik                94

Which of the following statements will display the player name, number, and points for all players that have scored points?

A. SELECT p.name, n.number, p.points FROM names n INNER JOIN points p ON n.name = p.name
B. SELECT p.name, n.number, p.points FROM names n LEFT OUTER JOIN points p ON n.name = p.name
C. SELECT p.name, n.number, p.points FROM names n RIGHT OUTER JOIN points p ON n.name = p.name
D. SELECT p.name, n.number, p.points FROM names n FULL OUTER JOIN points p ON n.name = p.name

Question 2

Given the following statement:

CREATE TABLE t1
col1  SMALLINT NOT NULL PRIMARY KEY,
col2  VARCHAR(200) NOT NULL WITH DEFAULT NONE,
col3  DECIMAL(5,2) CHECK (col3 >= 100.00),
col4  DATE NOT NULL WITH DEFAULT)

Which of the following definitions will cause the CREATE TABLE statement to fail?

A. COL1
B. COL2
C. COL3
D. COL4

Question 3

Given the following queries:

SELECT c1 FROM tab1;
SELECT c1 FROM tab2;

Which of the following set operators can be used to produce a result data set that contains only records that are not found in the result data set produced by each query after duplicate rows have been eliminated?

A. UNION
B. INTERSECT
C. EXCEPT
D. MERGE

Question 4

Given the following table:

TAB1

COL1       COL2
      
A                 10
B                 20
C                30
D                40
E                50

And the following SQL statements:

DECLARE c1 CURSOR WITH HOLD FOR
SELECT * FROM tab1 ORDER BY col_1;
OPEN c1;
FETCH c1;
FETCH c1;
FETCH c1;
COMMIT;
FETCH c1;
CLOSE c1;
FETCH c1;

Which of the following is the last value obtained for COL_2?

A. 20
B. 30
C. 40
D. 50

Question 5

Given the following scenario:

Table TABLE1 needs to hold specific numeric values up to 9999999.999 in column COL1. Once TABLE1 is populated, arithmetic operations will be performed on data stored in col-umn COL1.

Which of the following would be the most appropriate DB2 data type to use for column COL1?

A. INTEGER
B. REAL
C. NUMERIC(7,3)
D. DECIMAL(10,3)

Question 6

Application A wants to read a subset of rows from table TAB1 multiple times. Which of the following isolation levels should Application A use to prevent other users from making modifications and additions to table TAB1 that will affect the subset of rows read?

C. Cursor Stability

Question 7

Given the following two tables:

EMPLOYEE

ID     NAME                   DEPTID
  
01     Mick Jagger             10
02     Keith Richards        20
03     Ronnie Wood          20
04     Charlie Watts          20
05     Bill Wyman              30
06     Brian Jones              -

ID     DEPTNAME
 
10       Executive Staff
20       Sales
30       Marketing
40       Engineering
50       Human Resources

Which two of the following queries will display the employee name and department name for all employees that are in Sales?

A. SELECT e.name, d.deptname
FROM employee e, department d
WHERE e.deptid = d.id AND d.id = '20'
B. SELECT e.name, d.deptname
FROM employee e FULL OUTER JOIN department d
ON e.deptid = d.id
WHERE d.id = '20'
C. SELECT e.name, d.deptname
FROM employee e RIGHT OUTER JOIN department d
ON e.deptid = d.id
WHERE d.id = '20'
D. SELECT e.name, d.deptname FROM employee e LEFT OUTER JOIN department d
ON e.deptid = d.id
WHERE d.id = '20'
E. SELECT e.name, d.deptname
FROM employee e INNER JOIN department d
ON e.deptid = d.id
WHERE d.id = '20'

Question 8

Given the following set of statements:

CREATE TABLE tab1 (col1 INTEGER, col2 CHAR(20));
COMMIT;
INSERT INTO tab1 VALUES (123, 'Red');
INSERT INTO tab1 VALUES (456, 'Yellow');
SAVEPOINT s1 ON ROLLBACK RETAIN CURSORS;
DELETE FROM tab1 WHERE col1 = 123;
INSERT INTO tab1 VALUES (789, 'Blue');
ROLLBACK TO SAVEPOINT s1;
INSERT INTO tab1 VALUES (789, 'Green');
UPDATE tab1 SET col2 = NULL WHERE col1 = 789;
COMMIT;

Which of the following records would be returned by the following statement?

SELECT * FROM tab1

A. COL1    COL2
    
123        Red
456        Yellow
2 record(s) selected

B. COL1    COL2
    
456        Yellow
1 record(s) selected

C. COL1    COL2
    
123        Red
456        Yellow
789        -
3 record(s) selected

D. COL1    COL2
    
123        Red
456        Yellow
789        Green
3 record(s) selected

Question 9

Which of the following is used to indicate a column will not accept NULL values and can be referenced in another table's foreign key specification?

A. Check constraint
B. Unique constraint
C. Default constraint
D. Informational constraint

Question 10

An application has acquired a Share lock on a row in a table and now wishes to update the row. Which of the following statements is true?:

A. The application must release the row-level Share lock it holds and acquire an Update lock on the row
B. The application must release the row-level Share lock it holds and acquire an Update lock on the table
C. The row-level Share lock will automatically be converted to a row-level Up-date lock
D. The row-level Share lock will automatically be escalated to a table-level Up-date lock

Question 1

The correct answer is C -- SELECT p.name, n.number, p.points FROM names n RIGHT OUTER JOIN points p ON n.name = p.name

When a right outer join operation is performed, rows that would have been returned by an inner join operation, together with all rows stored in the rightmost table of the join operation (i.e., the table listed last in the OUTER JOIN clause) that would have been eliminated by the inner join operation are returned in the result data set produced. In this case, we want to see all records found in the POINTS table, along with any corresponding records found in the NAMES table, so a right outer join is the appropriate join operation to use.

Question 2

The correct answer is B -- COL2

Because column COL2 was defined using a varying-length character string (VARCHAR) data type, the default value provided for the default constraint must be enclosed in single quotes. Had the value 'NONE' been provided instead of the value NONE, the column COL2 would have been created. Instead, because column COL2 could not be created, the table TAB1 was not created.

Question 3

The correct answer is C -- EXCEPT

When the EXCEPT set operator is used, the result data sets produced by each individual query are combined, all duplicate rows found are eliminated, and all records found in the first result data set that have a corresponding record in the second result data set are elimi-nated, leaving just the records that are not found in both result data sets. When the UNION set operator is used, the result data sets produced by each individual query are combined and all duplicate rows are eliminated; when the INTERSECT set operator is used, the result data sets produced by each individual query are combined, all duplicate rows found are eliminated, and all records found in the first result data set that do not have a corresponding record in the second result data set are eliminated, leaving just the records that are found in both result data sets; and MERGE is not a set operator.

Question 4

The correct answer is C -- 40

When a cursor that has been declared with the WITH HOLD option specified (as in the example shown) is opened, it will remain open across transaction boundaries until it is explicitly closed; otherwise, it will be implicitly closed when the transaction that opens it is terminated. In this example, the cursor is opened, the first three rows are fetched from it, the transaction is committed (but the cursor is not closed), another row is fetched from it, and then the cursor is closed. Thus, the last value obtained will be:

TAB1

COL1       COL2
      
D                 40

Question 5

The correct answer is D -- DECIMAL(10, 3)

The decimal (DECIMAL or NUMERIC) data type is used to hold the number—the precision is 10 because 10 numbers will be displayed and the scale is 3 because the number contains three decimal places.

Question 6

When the Repeatable Read isolation level is used, the effects of one transaction are completely isolated from the effects of other concurrent transactions; when this isolation level is used, every row that's referenced in any manner by the owning transaction is locked for the duration of that transaction. As a result, if the same SELECT SQL statement is issued multiple times within the same transaction, the result data sets produced are guaranteed to be the identical. Other transaction are prohibited from performing insert, update, or delete operations that would affect any row that has been accessed by the owning transaction as long as that transaction remains active.

Question 7

The correct answers are A and E --

An inner join can be thought of as the cross product of two tables, in which every row in one table that has a corresponding row in another table is combined with that row to produce a new record. The syntax for a SELECT statement that performs an inner join operation is:

``` SELECT [* | [Expression] <<AS> [NewColumnName]> ,...] FROM [[TableName] <<AS> [CorrelationName]> ,...] [JoinCondition]
```

Where:

Expression
Identifies one or more columns whose values are to be returned when the SELECT statement is executed. The value specified for this option can be any valid SQL language element; however, corresponding table or view column names are commonly used.
NewColumnName
Identifies a new column name that is to be used in place of the corresponding table or view column name specified in the result data set returned by the SELECT statement.
TableName
Identifies the name(s) assigned to one or more tables that data is to be retrieved from.
CorrelationName
Identifies a shorthand name that can be used when referencing the table name specified in the TableName parameter
JoinCondition
Identifies the condition to be used to join the tables specified. Typically, this is a WHERE clause in which the values of a column in one table are compared with the values of a similar column in another table.

The following syntax can also be used to create a SELECT statement that performs an inner join operation:

``` SELECT [* | [Expression] <<AS> [NewColumnName]> ,...] FROM [[TableName1] <<AS> [CorrelationName1]> ,] <INNER> JOIN [[TableName2] <<AS> [CorrelationName2]>] ON [JoinCondition]
```

Where:

Expression
Identifies one or more columns whose values are to be returned when the SELECT statement is executed. The value specified for this option can be any valid SQL language element; however, corresponding table or view column names are commonly used.
NewColumnName
Identifies a new column name to be used in place of the corre-sponding table or view column name specified in the result data set returned by the SELECT statement.
TableName1
Identifies the name assigned to the first table data is to be re-trieved from.
CorrelationName1
Identifies a shorthand name that can be used when referencing the leftmost table of the join operation.
TableName2
Identifies the name assigned to the second table data is to be re-trieved from.
CorrelationName2
Identifies a shorthand name that can be used when referencing the rightmost table of the join operation.
JoinCondition
Identifies the condition to be used to join the two tables specified.

Question 8

The correct answer is C --

DB2 uses a mechanism known as a savepoint, to allow an application to break the work being performed by a single large transaction into one or more subsets. Once created, a savepoint can be used in conjunction with a special form of the ROLLBACK SQL statement to return a database to the state it was in at the point in time a particular savepoint was created. The syntax for this form of the ROLLBACK statement is:

``` ROLLBACK <WORK> TO SAVEPOINT <[SavepointName]>
```

where:

SavepointName   Identifies the name assigned to the savepoint that indicates the point in time that operations performed against the database are to be rolled back (backed out) to.

So, in this example, every operation performed between the time savepoint S1 was created and the ROLLBACK TO SAVEPOINT statement was executed was undone.

Question 9

The correct answer is B -- Unique constraint

A unique constraint can be used to ensure that the value(s) assigned to one or more columns when a record is added to a base table are always unique; once a unique constraint has been defined for one or more columns, any operation that attempts to place duplicate values in those columns will fail. Although a unique, system-required index is used to enforce a unique constraint, there is a distinction between defining a unique constraint and creating a unique index; even though both enforce uniqueness, a unique index allows NULL values and generally cannot be used in a referential constraint. A unique constraint on the other hand, does not allow NULL values and can be referenced in a foreign key specification. (The value "NULL" means a column's value is undefined and distinct from any other value, including other NULL values).

A check constraint (also known as a table check constraint) can be used to ensure that a particular column in a base table is never assigned an unacceptable value—once a check constraint has been defined for a column, any operation that attempts to place a value in that column that does not meet specific criteria will fail. The default constraint can be used to ensure that a particular column in a base table is assigned a predefined value (unless that value is overridden) each time a record is added to the table. The predefined value provided could be null (if the NOT NULL constraint has not been defined for the column), a user-supplied value compatible with the column's data type, or a value furnished by the DB2 Da-tabase Manager. Unlike other constraints, informational constraints are not enforced during insert and update processing. However, the DB2 SQL optimizer will evaluate information provided by an informational constraint when considering the best access plan to use to re-solve a query. As a result, an informational constraint may result in better query performance even though the constraint itself will not be used to validate data entry/modification.

Question 10

The correct answer is C -- The row-level Share lock will automatically be converted to a row-level Up-date lock

The correct answer is C. If a transaction holding a lock on a resource needs to acquire a more restrictive lock on the same resource, the DB2 Database Manager will attempt to change the state of the existing lock to the more restrictive state. The action of changing the state of an existing lock to a more restrictive state is known as lock conversion. Lock conver-sion occurs because a transaction can hold only one lock on a specific data resource at any given time. In most cases, lock conversion is performed on row-level locks, and the conver-sion process is fairly straightforward. For example, if an Update (U) lock is held and an Exclusive (X) lock is needed, the Update (U) lock will be converted to an Exclusive (X) lock.

Go back to the list of sample DB2 9 exam questions.

Start the conversation

Send me notifications when other members comment.

• OmniSci analytics tools to come embedded in HP workstations

Under the terms of an agreement between OmniSci and HP revealed on Thursday, OmniSci's BI platform will come as a preloaded ...

• Economic downturn shows businesses true value of analytics

Business intelligence is proving to be a critical tool for enterprises as they attempt to survive during the sudden economic ...

• Key differences of a data scientist vs. data engineer

Data scientists and data engineers often work together, and sometimes the positions are treated as the same. Read on to find out ...

SearchAWS

• COVID-19 ups the fight against Amazon package porch pirates

Many unlucky online shoppers have fallen prey to 'porch pirates' who steal packages from their homes. It's a problem that could ...

• Amazon bets big on last mile delivery service improvements

Amazon has made a significant investment in building out its last mile delivery services to grab a piece of the growing grocery ...

• Amazon moves into autonomous vehicle market

Amazon aims to lower transportation costs and complete the last mile of delivery with autonomous technology. COVID-19 could ...

SearchContentManagement

• Enterprise search software comparison

Enterprise search has always been both a necessity and a challenge, and vendors have sought to bring improvements to the market ...

• New OpenText CE apps, services roll out

OpenText containerizes its applications for cloud deployment; adds raft of content services and features for customer experience ...

• Box Shield adds malware detection

Box Inc. quarantines malware-infected files with features that allow content to be viewed, with admin and security team alerts to...

SearchOracle

• Oracle Autonomous Database shifts IT focus to strategic planning

This handbook looks at what Oracle Autonomous Database offers to Oracle users and issues that organizations should consider ...

• Oracle Autonomous Database features free DBAs from routine tasks

Oracle Autonomous Database can automate routine administrative and operational tasks for DBAs and improve productivity, but ...

• Oracle co-CEO Mark Hurd dead at 62, succession plan looms

Oracle co-CEO Mark Hurd's abrupt death at 62 has put the software giant in the position of naming his replacement, and the ...

SearchSAP

• SAP Cloud Platform app helps German citizens find their way home

Rückholprogramm, an SAP Cloud Platform app, was built in just a few days to fulfill a request from the German Federal Foreign ...

• SAP S/4HANA deployment options: Legacy ERP vs. cloud

Take this tour of the on-premises S/4HANA and its two cloud siblings and get expert advice on major differences in cost and ...

SearchSQLServer

• SQL Server database design best practices and tips for DBAs

Good database design is a must to meet processing needs in SQL Server systems. In a webinar, consultant Koen Verbeeck offered ...

• SQL Server in Azure database choices and what they offer users

SQL Server databases can be moved to the Azure cloud in several different ways. Here's what you'll get from each of the options ...

• Using a LEFT OUTER JOIN vs. RIGHT OUTER JOIN in SQL

In this book excerpt, you'll learn LEFT OUTER JOIN vs. RIGHT OUTER JOIN techniques and find various examples for creating SQL ...

Close