Skip to main content

C2090-730 Online Exam

DB2 9 Family Fundamentals

302 questions available ยท Page 1 of 31

View study plans
Question 1 Single choice

Given the following insert statement:

INSERT INTO product ( pid, description ) VALUES ( '100-100-01', XMLPARSE ( DOCUMENT '<product
xmlns="http://posample.org" pid=''100-100-01'' > <description> <name>Snow Shovel, Basic 22in</name>
<details>Basic Snow Shovel, 22in wide, straight handle with D-Grip</details> <price>9.99</price> <weight>1
kg</weight> </description> </product>' PRESERVE WHITESPACE ) );

Which of the following table definitions will support the insert statement above?

  1. A

    CREATE TABLE product

    ( pid XML NOT NULL PRIMARY KEY,
    name VARCHAR(128),
    price DECIMAL(30,2),
    promoprice DECIMAL(30,2),
    promostart DATE,
    promoend DATE,
    description XML);

  2. B

    CREATE TABLE product
    ( pid VARCHAR(10) NOT NULL PRIMARY KEY,
    name VARCHAR(128),
    price DECIMAL(30,2),
    promoprice DECIMAL(30,2),
    promostart DATE,
    promoend DATE,
    description XML);

  3. C

    CREATE TABLE product
    ( pid XML NOT NULL PRIMARY KEY,
    name VARCHAR(128),
    price DECIMAL(30,2),
    promoprice DECIMAL(30,2),
    promostart DATE,
    promoend DATE,
    description VARCHAR(1000));

  4. D

    CREATE TABLE product
    ( pid VARCHAR(10) NOT NULL PRIMARY KEY,
    name VARCHAR(128),
    price DECIMAL(30,2),
    promoprice DECIMAL(30,2),
    promostart DATE,
    promoend DATE,
    description VARCHAR(1000));

Show answer and explanation

Correct answer: B

Explanation

48(67,2112

:KLFKRIWKHIROORZLQJLVWKHORZHVWFRVW'%SURGXFWWKDWFDQEHOHJDOO\LQVWDOOHGRQD:LQGRZV
VHUYHUWKDWKDV&38V"

$'%(YHU\SODFH
%'%([SUHVV(GLWLRQ
&'%:RUNJURXS6HUYHU(GLWLRQ
''%(QWHUSULVH6HUYHU(GLWLRQ
&RUUHFW$QVZHU
%

48(67,2112

:KLFKRIWKHIROORZLQJSURGXFWVLVDOORZHGWRDFFHVVRWKHU'%VHUYHUVEXWFDQQRWDFFHSW

$FOLHQWDSSOLFDWLRQRQ]26PXVWDFFHVVD'%GDWDEDVHRQD6RODULV6HUYHU$WDPLQLPXP

ZKLFKRIWKHIROORZLQJSURGXFWVPXVWEHLQVWDOOHGRQWKH6RODULVZRUNVWDWLRQ"

UHTXHVWVIURPRWKHUUHPRWHFOLHQWV"

$'%3HUVRQDO(GLWLRQ

%'%:RUNJURXS6HUYHU(GLWLRQ

&'%(QWHUSULVH6HUYHU(GLWLRQ

''%'DWD:DUHKRXVH(GLWLRQ

&RUUHFW$QVZHU
$

48(67,2112

$'%&RQQHFW(QWHUSULVH(GLWLRQ

%'%:RUNJURXS6HUYHU(GLWLRQ

&'%:RUNJURXS6HUYHU(GLWLRQDQG'%&RQQHFW(QWHUSULVH(GLWLRQ

''%(QWHUSULVH6HUYHU(GLWLRQDQG'%&RQQHFW(QWHUSULVH(GLWLRQ

&RUUHFW$QVZHU
'

48(67,2112

:KLFKRIWKHIROORZLQJLVWKHORZHVWFRVW'%SURGXFWWKDWFDQEHOHJDOO\LQVWDOOHGRQDQ+38;VHUYHU"

$'%([SUHVV&

%'%([SUHVV

&'%3HUVRQDO(GLWLRQ

''%(QWHUSULVH6HUYHU(GLWLRQ

&RUUHFW$QVZHU
'

Question 2 Single choice

A table was created using the following DDL:

CREATE TABLE employee
(id SMALLINT NOT NULL,
name VARCHAR(9),
dept SMALLINT CHECK (dept BETWEEN 10 AND 100),
job CHAR(10) CHECK (job IN ('Sales','Mgr','Clerk')), hiredate DATE, salary DECIMAL(7,2),
comm DECIMAL(7,2),
PRIMARY KEY (id),
CONSTRAINT yearsal CHECK (YEAR(hiredate) > 2004 OR salary > 80500) );

Which of the following INSERT statements will fail?

  1. A

    INSERT INTO employee VALUES (2, 'Smith', 80, 'Mgr', '09/03/2006', 80000, NULL)

  2. B

    INSERT INTO employee VALUES (4, 'Smith', 86, 'Mgr', '07/14/2003', 90000, NULL)

  3. C

    INSERT INTO employee VALUES (1, 'Smith', 55, 'Sales', '07/14/2003', NULL, NULL)

  4. D

    INSERT INTO employee VALUES (3, 'Smith', 33, 'Analyst', '11/26/2006', 90000, NULL)

Show answer and explanation

Correct answer: D

Question 3 Single choice

An application needs a table for each connection that tracks the ID and Name of all items previously ordered and committed within the connection. The table also needs to be cleaned up and automatically removed each time a connection is ended. Assuming the ITEMS table was created with the following SQL statement:

CREATE TABLE items
item_no INT,
item_name CHAR(5),
item_qty INT)

Which of the following SQL statements will provide the table definition that meets the specified requirements?

  1. A

    DECLARE GLOBAL TEMPORARY TABLE tracker
    AS (SELECT item_no, item_name FROM items) WITH NO DATA ON COMMIT PRESERVE ROWS
    ON DISCONNECT DROP TABLE

  2. B

    DECLARE GLOBAL TEMPORARY TABLE tracker
    AS (SELECT item_no, item_name FROM items) WITH NO DATA ON COMMIT PRESERVE ROWS

  3. C

    CREATE TABLEsystmp.tracker
    AS (SELECT item_num, item_name FROM items) WITH NO DATA ON COMMIT PRESERVE ROWS

  4. D

    CREATE TABLE tracker
    AS (SELECT item_num, item_name FROM items)
    ON COMMIT PRESERVE ROWS
    ON DISCONNECT DROP TABLE

Show answer and explanation

Correct answer: B

Question 4 Single choice

Given the following requirements:

Create a table to contain employee data, with a unique numeric identifier automatically assigned when a row is

added, has an EDLEVEL column that permits only the values 'C', 'H' and 'N', and permits inserts only when a corresponding value for the employee's department exists in the DEPARTMENT table.

Which of the following CREATE statements will successfully create this table?

  1. A

    CREATE TABLEemp (
    empno SMALLINT NEXTVAL GENERATED ALWAYS AS IDENTITY, firstname VARCHAR(30) NOT
    NULL,
    lastname VARCHAR(30) NOT NULL,
    workdept CHAR(3) NOT NULL,
    edlevel CHAR(1),
    PRIMARY KEY emp_pk (empno),
    FOREIGN KEY emp_workdept_fk ON (workdept) REFERENCES department (deptno), CHECK edlevel_ck VALUES (edlevel IN ('C','H','N')),
    );

  2. B

    CREATE TABLEemp (
    empno SMALLINT NOT NULL GENERATED BY DEFAULT AS IDENTITY, firstname VARCHAR(30) NOT
    NULL,
    lastname VARCHAR(30) NOT NULL,
    workdept CHAR(3),
    edlevel CHAR(1),
    CONSTRAINT emp_pk PRIMARY KEY (empno),
    CONSTRAINT emp_workdept_fk FOREIGN KEY (workdept) REFERENCES department (deptno),
    CONSTRAINT edlevel_ck CHECK edlevel VALUES ('C','H','N') );

  3. C

    CREATE TABLEemp (
    empno SMALLINT NEXTVAL GENERATED BY DEFAULT AS IDENTITY, firstname VARCHAR(30) NOT
    NULL,
    lastname VARCHAR(30) NOT NULL,
    workdept CHAR(3) NOT NULL,
    edlevel CHAR(1) CHECK IN ('C','H','N')),
    CONSTRAINT emp_pk PRIMARY KEY (empno),
    CONSTRAINT emp_workdept_fk FOREIGN KEY department (deptno) REFERENCES (workdept)
    );

  4. D

    CREATE TABLEemp (
    empno SMALLINT NOT NULL GENERATED BY DEFAULT AS IDENTITY, firstname VARCHAR(30) NOT
    NULL,
    lastname VARCHAR(30) NOT NULL,
    workdept CHAR(3),
    edlevel CHAR(1),
    CONSTRAINT emp_pk PRIMARY KEY (empno),
    CONSTRAINT emp_workdept_fk FOREIGN KEY (workdept) REFERENCES department (deptno),
    CONSTRAINT edlevel_ck CHECK (edlevel IN ('C','H','N')) );

Show answer and explanation

Correct answer: D

Question 5 Single choice

When defining a referential constraint between the parent table T2 and the dependent table T1, which of the following is true?

  1. A

    The list of column names in the FOREIGN KEY clause can be a subset of the list of column names in the primary key of T2 or a UNIQUE constraint that exists on T2.

  2. B

    The list of column names in the FOREIGN KEY clause can be a subset of the list of column names in the primary key of T1 or a UNIQUE constraint that exists on T1.

  3. C

    The list of column names in the FOREIGN KEY clause must be identical to the list of column names in the primary key of T2 or a UNIQUE constraint that exists on T2.

  4. D

    The list of column names in the FOREIGN KEY clause must be identical to the list of column names in the primary key of T1 or a UNIQUE constraint that exists on T1.

Show answer and explanation

Correct answer: C

Question 6 Single choice

Given the following two tables:

TAB1
C1 C2
__ __
A 11
B 12
C 13
TAB2
CX CY
__ __
A 21
C 22
D 23

The following results are desired:

C1 C2 CX CY
__ __ __ __
A 11 A 21
C 13 C 22
-- -- D 23

Which of the following queries will yield the desired results?

  1. A

    SELECT * FROM tab1 INNER JOIN tab2 ON c1=cx

  2. B

    SELECT * FROM tab1 LEFT OUTER JOIN tab2 ON c1=cx

  3. C

    SELECT * FROM tab1 FULL OUTER JOIN tab2 ON c1=cx

  4. D

    SELECT * FROM tab1 RIGHT OUTER JOIN tab2 ON c1=cx

Show answer and explanation

Correct answer: D

Question 7 Single choice

Given the following two tables:

TAB1
R1
--
A
A
A
B
B
C
C
D
E
TAB2
R2
--
A
A
B
B
C
C
D
Which of the following queries returns the following result set?

RETVAL
------E

  1. A

    SELECT r1 ASretval FROM tab1 INTERSECT
    SELECT r2 AS retval FROM tab2

  2. B

    SELECT r1 ASretval FROM tab1 EXCEPT
    SELECT r2 AS retval FROM tab2

  3. C

    SELECT DISTINCT r1 ASretval
    FROM tab1, tab2
    WHERE r1 <> r2

  4. D

    SELECT r1 ASretval FROM tab1 UNION
    SELECT r2 AS retval FROM tab2

Show answer and explanation

Correct answer: B

Question 8 Single choice

In which of the following situations would DB2 retain resources associated with a transaction at COMMIT time?

  1. A

    A cursor is defined as WITH HOLD.

  2. B

    Another user executes the same transaction.

  3. C

    The application program amends during COMMIT.

  4. D

    The transaction terminates abnormally during COMMIT.

Show answer and explanation

Correct answer: A

Question 9 Single choice 
CREATE TABLE customer (cid BIGINT NOT NULL PRIMARY KEY, info XML)

How many names will be returned for this XQuery?

  1. A

    0

  2. B

    1

  3. C

    2

  4. D

    3

Show answer and explanation

Correct answer: C

Question 10 Single choice

Which of the following SQL statements will return the year and average salary for all employees hired within a given year that have a salary greater than $30,000?

  1. A

    SELECT * FROM t1
    UNION
    SELECT * FROM t2

  2. B

    SELECT * FROM t1
    UNION DISTINCT
    SELECT * FROM t2

  3. C

    SELECT * FROM t1
    INTERSECT
    SELECT * FROM t2

  4. D

    SELECT * FROM t1
    WHERE (c1,c2)=
    (SELECT c1,c2 FROM t2)

Show answer and explanation

Correct answer: B