Db2 Basics, Part 1

A ________ may contain multiple ________ (each behaving like a separate installation of DB2), and an instance can control multiple ________.

A DB2 ________ manages the storage, modification, and retrieval of data.

A DB2 database is a collection of related ________.

A DB2 database may be created with the

• A.

  DEFINE DATABASE command

• B.

  Create Database wizard

• C.

  CREATE DATABASE command

A DB2 database is implicitly ________ as soon as it is created.

A DB2 database can be explicitly cataloged using any of these 3 methods:      the ________ Center      the ________ Assistant      the ________ ________ command 

The ________ statement deletes existing database objects when they are no longer needed.

Any of the DB2 database objects is created using the _________ statement.

Which of the following is NOT a numeric data type?

• A.

  BINARY

• B.

  SMALLINT

• C.

  NUMERIC

• D.

  DOUBLE

• E.

  REAL

Which of the following is NOT a numeric data type?

• A.

  DECIMAL

• B.

  FLOAT

• C.

  LARGEINT

• D.

  DOUBLE PRECISION

• E.

  BIGINT

Which of the following is NOT a character data type?

• A.

  CHARACTER

• B.

  CLOB

• C.

  LONG VARCHAR

• D.

  DOUBLE CHARACTER

• E.

  CHARACTER VARYING

An equivalent term for CHARACTER VARYING is ________.

An equivalent term for CHARACTER LARGE OBJECT is ________.

The double-byte character data types (in alpha order) are ________, ________, ________, ________.

The date and time data types (in alpha order) are ________, _______, ________.

The binary and XML data types (in alpha order) are ________, _______.

Execution of an SQL statement in a basic client/server environment:      SQL statement is sent through a ________ from the ________ workstation to a database ________.      The database ________ processes the statement.      The results are sent back through the ________ to the ________. 

For every SQL statement executed, how many messages must go through the network?

Focus of client/server development:      Place the application logic on one or more ________.      Place the code that interacts directly with a database on a ________. 

Structured Query Language (SQL) is a standardized language used to work with ________ ________ and the ________ they contain.

SQL is a procedural programming language.

• A.

  True

• B.

  False

When using the WHERE clause predicate LIKE, the metacharacter (wildcard) "_" represents any single alphanumeric character.

• A.

  True

• B.

  False

When using the WHERE clause predicate LIKE, the metacharacter (wildcard) "%" represents any single alphanumeric character.

• A.

  True

• B.

  False

The searched update uses one of the normal forms of the UPDATE statement.  The positioned update uses a ________.

When using a positioned update, the UPDATE statement must contain a ________________ [CursorName] clause.

Like the update operation, a ________ operation may be searched or positioned.

When performing a positioned update or delete, a cursor must be ________, ________, and ________ on the target row.

Which of the following are true about common table expressions?

• A.

  Used to construct local temporary tables that reside in memory.

• B.

  The table exists only for the life of the SQL statement that defines it.

• C.

  The table can only be referenced by the SQL statement that created it.

• D.

  A common table expression can be used in place of a view.

• E.

  All of the above

A common table expression uses a ________ [TableName] <([ColumnName],...)>  and a ________ ([SelectStatement])  clause.

SELECT empno, lastname,        CASE SUBSTR(workdept, 1, 1)                 WHEN 'A'  THEN 'ADMINISTRATION'                 WHEN 'B'  ...    The above is an example of a ________ CASE expression. 

SELECT empno, lastname, job, salary,        CASE                  WHEN job in ('MANAGER', 'SUPRVSR')  THEN salary * 1.10                 WHEN job in ('DBA', 'SYS PROG')  THEN  ...    The above is an example of a ________ CASE expression.

A ________ allows retrieval of data from two or more tables.

SELECT * FROM [TableName1,TableName2]    The statement above, in which each row of the 1st table is concatenated with each row of the 2nd table, produces a type of join called a ________ ________.

If table A has 3 rows, table B has 4 rows, and table C has 5 rows, and the three tables are joined to form a Cartesian product, how many rows will be in the result dataset? 

• A.

  5 rows

• B.

  12 rows

• C.

  20 rows

• D.

  60 rows

A more common type of join uses the ________ clause to join two or more tables on one specific column.

SELECT lastname, deptnameFROM employee emp, department dWHERE emp.workdept = d.deptnoIn the statement above, emp and d are known as ________ ________.

Joining two or more tables that have a column in common is a type of ________ join.

The keyword JOIN is required when performing an inner join.

• A.

  True

• B.

  False

When using the JOIN keyword to do an inner join, use the ________ keyword before the join condition instead of the ________ keyword.

An inner join, when using the JOIN keyword, is the cross-product of two tables, where every row in one table that has a corresponding row in another table is combined with that row to produce a new record.

• A.

  True

• B.

  False

An inner join may only be done when each row in one table has a corresponding row in the other table.

• A.

  True

• B.

  False

If the keyword JOIN appears with no qualifiers, the keyword INNER is assumed.

• A.

  True

• B.

  False

The LEFT OUTER JOIN returns the same rows as an INNER JOIN, together with rows from the _________ table that would have been eliminated by the inner join.The RIGHT OUTER JOIN returns the same rows as an INNER JOIN, together with rows from the _________ table that would have been eliminated by the inner join.The FULL OUTER JOIN returns the same rows as an INNER JOIN, together with rows from _________ tables that would have been eliminated by the inner join.

________ ________ are a mechanism to combine two or more queries into a single query.

Which of the following are NOT set operators?

• A.

  UNION

• B.

  INNER JOIN

• C.

  EXCEPT ALL

• D.

  INTERSECT

• E.

  FULL OUTER JOIN

Using set operators, the results of each query are combined to produce a ________ result dataset.