Given the following two tables:
TAB1
R1
—
ABCDE
TAB2
R2
—
ABCD

Which of the following queries returns the following result set?
RETVAL
——
E

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?

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

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?

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?

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?

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?