Working with DB2 Tables, Views, and Indexes

102.	Which of the following is a valid DB2 data type? A. NUMBER B. INTERVAL C. BYTE D. NUM
103.	Which of the following DB2 data types does NOT have a fixed length? A. INT B. CHAR C. XML D. DOUBLE
104.	Which of the following is the best statement to use to create a user-defined data type that can be used to store currency values? A. CREATE DISTINCT TYPE currency AS NUMERIC(7,2) B. CREATE DISTINCT TYPE currency AS SMALLINT C. CREATE DISTINCT TYPE currency AS BIGINT D. CREATE DISTINCT TYPE currency AS DOUBLE
105.	Which of the following DB2 data types can be used to store 1000 MB of single-byte character data? A. BLOB B. CLOB C. DBCLOB D. GRAPHIC
106.	Which of the following DB2 data types CANNOT be used to create an identity column? A. SMALLINT B. INTEGER C. NUMERIC D. DOUBLE
107.	Given the requirements to store employee names, employee numbers, and when employees were hired, which of the following built-in data types CANNOT be used to store the day an employee was hired? A. Character Large Object B. Time C. Varying-Length Character String D. Timestamp
108.	Given the requirements to store customer names, billing addresses, and telephone numbers, which of the following would be the best way to define the telephone number column for a table if all customers were located in the same country? A. PHONE CHAR(15) B. PHONE VARCHAR(15) C. PHONE LONG VARCHAR D. PHONE CLOB(1K)
109.	Which of the following strings can NOT be inserted into an XML column using XMLPARSE()? A. "<employee />" B. "<name>John Doe</name>" C. "<?xml version='1.0' encoding='UTF-8' ?>" D. "<p></p>"
110.	Which two of the following are optional and do not have to be specified when creating a table? A. Table name B. Column name C. Default constraint D. Column data type E. NOT NULL constraint
111.	Which of the following is a NOT a valid reason for defining a view on a table? A. Restrict users' access to a subset of table data B. Ensure that rows inserted remain within the scope of a definition C. Produce an action as a result of a change to a table D. Provide users with an alternate view of table data
112.	Given the following CREATE TABLE statement: CREATE TABLE table2 LIKE table1 Which two of the following will NOT occur when the statement is executed? A. TABLE2 will have the same column names and column data types as TABLE1 B. TABLE2 will have the same column defaults as TABLE1 C. TABLE2 will have the same nullability characteristics as TABLE1 D. TABLE2 will have the same indexes as TABLE1. E. TABLE2 will have the same referential constraints as TABLE1
113.	If the following SQL statements are executed: CREATE TABLE tab1 (id SMALLINT NOT NULL PRIMARY KEY, name VARCHAR(25)); CREATE TABLE tab2 (empid SMALLINT, weekno SMALLINT, payamt DECIMAL(6,2), CONSTRAINT const1 FOREIGN KEY (empid) REFERENCES taba(id) ON UPDATE NO ACTION); Which of the following statements is true? A. Only values that exist in the ID column of table TAB1 are allowed to be inserted in the EMPID column of table TAB2 B. The updating of values in the ID column of table TAB1 is not allowed C. Only values that do not already exist in the ID column of table TAB1 are allowed to be inserted in the EMPID column of table TAB2 D. When values that exist in the ID column of table TAB1 are updated, corresponding values in the EMPID column of table TAB2 are updated as well
114.	Which of the following CANNOT be used to restrict specific values from being inserted into a column in a particular table? A. Index B. Check constraint C. Referential constraint D. Default constraint
115.	If table TAB1 is created using the following statement: CREATE TABLE tab1 (col1 INTEGER NOT NULL, col2 CHAR(5), CONSTRAINT cst1 CHECK (col1 in (1, 2, 3))) Which of the following statements will successfully insert a record into table TAB1? A. INSERT INTO tab1 VALUES (0, 'abc') B. INSERT INTO tab1 VALUES (NULL, 'abc') C. INSERT INTO tab1 VALUES (ABS(2), 'abc') D. INSERT INTO tab1 VALUES (DEFAULT, 'abc')
116.	Given the following table definition: EMPLOYEES ---------------------- EMPID INTEGER NAME CHAR(20) SALARY DECIMAL(10,2) If the following SQL statement is executed: CREATE UNIQUE INDEX empid_ui ON employees (empid) Which two of the following statements are true? A. Multiple null values are allowed in the EMPID column of the EMPLOYEES table. B. No null values are allowed in the EMPID column of the EMPLOYEES table. C. One (and only one) null value is allowed in the EMPID column of the EMPLOYEES table. D. No other unique indexes can be created on the EMPLOYEES table. E. Every value found in the EMPID column of the EMPLOYEES table will be different.
117.	If the following SQL statements are executed: CREATE TABLE make (makeid SMALLINT NOT NULL PRIMARY KEY, make VARCHAR(25)); CREATE TABLE model (modelid SMALLINT, model VARCHAR(25), makeid SMALLINT, CONSTRAINT const1 FOREIGN KEY (makeid) REFERENCES make(makeid) ON DELETE RESTRICT); And each table created is populated as follows: MAKE MAKEID MAKE ------ -------- 1 Ford 2 Chevrolet 3 Toyota MODEL MODELID MODEL MAKEID ------- ------- -------- 1 Mustang 1 2 Escort 1 3 Malibu 2 4 Camry 3 If the following SQL statement is executed: DELETE FROM make WHERE makeid = 1 What is the total number of rows that will be deleted? A. 0 B. 1 C. 2 D. 3
118.	Given the statement: CREATE TABLE tablea (col1 INTEGER NOT NULL, CONSTRAINT const1 CHECK (col1 in (100, 200, 300)) Which of the following can be inserted into TABLEA? A. 0 B. NULL C. 100 D. '100'
119.	Which of the following deletion rules on CREATE TABLE will allow parent table rows to be deleted if a dependent row exists? A. ON DELETE RESTRICT B. ON DELETE NO ACTION C. ON DELETE SET NO VALUE D. ON DELETE CASCADE
120.	Which of the following is NOT a characteristic of a unique index? A. Each column in a base table can only participate in one unique index, regardless of how the columns are grouped (the same column cannot be used in multiple unique indexes) B. In order for an index to be used to support a unique constraint, it must have been defined with the UNIQUE attribute C. A unique index cannot be created for a populated table if the key column specified contains more than one NULL value D. A unique index can only be created for a non-nullable column
121.	If the following statement is executed: CREATE TABLE employee (empid INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 5), name VARCHAR(20), dept INT CHECK (dept BETWEEN 1 AND 20), hiredate DATE WITH DEFAULT CURRENT DATE, salary DECIMAL(7,2), PRIMARY KEY(empid), CONSTRAINT cst1 CHECK (YEAR(hiredate) > 2006 OR Salary > 60500)); Which of the following INSERT statements will fail? A. INSERT INTO employee VALUES (15, 'Smith', 5, '01/22/2004', 92500.00) B. INSERT INTO employee VALUES (DEFAULT, 'Smith', 2, '10/07/2002', 80250.00) C. INSERT INTO employee VALUES (20, 'Smith', 5, NULL, 65000.00) D. INSERT INTO employee VALUES (DEFAULT, 'Smith', 10, '11/18/2004', 60250.00)
122.	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
123.	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 column 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)
124.	Given the following statement: CREATE TABLE tab1 (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
125.	Which type of key is defined on the child table to implement a referential constraint? A. Unique key B. Primary key C. Foreign key D. Composite key
126.	Which of the following is NOT true about schemas? A. If a schema name is not specified, either by qualifying a database object name or by executing the SET CURRENT SCHEMA statement, the authorization ID of the current session user is used as the schema name by default B. The value assigned to the CURRENT SCHEMA special register is persistent across database restarts C. A schema enables the creation of multiple objects in a database without encountering namespace collisions D. When most database objects are created, they are either implicitly or explicitly assigned to a schema
127.	If the following statement is executed: CREATE TABLE tab1 (col1 INTEGER NOT NULL, col2 INTEGER, CONSTRAINT const1 FOREIGN KEY (col2) REFERENCES tab1(col1)); How many unique indexes are defined for table TAB1? A. 0 B. 1 C. 2 D. 3
128.	When does a view get populated? A. When it is created B. When it is referenced in an INSERT statement C. The first time any executable SQL statement references it D. Any time an executable SQL statement references it
129.	Given the following statements: CREATE TABLE table1 (col1 INTEGER, col2 CHAR(3)); CREATE VIEW view1 AS SELECT col1, col2 FROM table1 WHERE col1 < 100 WITH LOCAL CHECK OPTION; Which of the following INSERT statements will execute successfully? A. INSERT INTO view1 VALUES (50, abc) B. INSERT INTO view1 VALUES(100, abc) C. INSERT INTO view1 VALUES(50, 'abc') D. INSERT INTO view1 VALUES(100, 'abc')
130.	Given the following tables: TABLEA EMPIDNAME ----- ------- 1 USER1 2 USER2 TABLEB EMPID WEEKNO PAYAMT ----- ------ ------- 1 1 1000.00 1 2 1000.00 2 1 2000.00 and the fact that TABLEB was defined as follows: CREATE TABLE tableb (empid SMALLINT, weekno SMALLINT, payamt DECIMAL(6,2), CONSTRAINT const1 FOREIGN KEY (empid) REFERENCES tablea(empid) ON DELETE NO ACTION) If the following command is issued: DELETE FROM tablea WHERE empid=2 How many rows will be deleted from TABLEA and TABLEB? A. TABLEA - 0, TABLEB - 0 B. TABLEA - 0, TABLEB - 1 C. TABLEA - 1, TABLEB - 0 D. TABLEA - 1, TABLEB - 1
131.	Which of the following actions will NOT cause a trigger to be fired? A. INSERT B. DELETE C. ALTER D. UPDATE
132.	The following triggers were defined for a table named SALES in the order shown: CREATE TRIGGER trigger_a NO CASCADE BEFORE UPDATE ON sales REFERENCING NEW AS new FOR EACH ROW SET new.commission = sale_amt * .05 WHERE invoice = n.invoice; CREATE TRIGGER trigger_b AFTER INSERT ON sales REFERENCING NEW AS new FOR EACH ROW UPDATE sales SET bill_date = CURRENT DATE + 30 DAYS WHERE invoice = n.invoice; CREATE TRIGGER trigger_c NO CASCADE BEFORE DELETE ON sales FOR EACH ROW SIGNAL SQLSTATE ‘75005' SET MESSAGE_TEXT = ‘Deletes not allowed!'; Which of the following statements is NOT true? A. Once a row has been added to the SALES table, it cannot be removed B. Whenever a row is inserted into the SALES table, the value in the BILL_DATE column is automatically set to 30 days from today C. Each time a row is inserted into the SALES table, trigger TRIGGER_A is fired first, followed by trigger TRIGGER_B D. Whenever a row in the SALES table is updated, all three triggers are fired but nothing happens because none of the triggers have been coded to trap update operations
133.	Which of the following is NOT a difference between a unique index and a primary key? A. A primary key is a special form of a unique constraint; both use a unique index. B. Unique indexes can be defined over one or more columns; primary keys can only be defined on a single column. C. A table can have many unique indexes but only one primary key. D. Unique indexes can be defined over one or more columns that allow null values; primary keys cannot contain null values.
134.	Which of the following CREATE TABLE statements will NOT be successful? A. CREATE TABLE t1 (c1 XML NOT NULL UNIQUE, c2 INT) B. CREATE TABLE t1 (c1 XML NOT NULL, c2 CHAR(20)) C. CREATE TABLE t1 (c1 XML CHECK (c1 IS VALIDATED), c2 INT) D. CREATE TABLE t1 (c1 XML, c2 XML)
135.	If the following SQL statement is executed: CREATE TABLE sales (invoice_no NOT NULL PRIMARY KEY, sales_date DATE, sales_amt NUMERIC(7,2)) IN tbsp0, tbsp1, tbsp2, tbsp3 PARTITION BY RANGE (sales_date NULLS FIRST) (STARTING ‘1/1/2007' ENDING '12/31/2007' EVERY 3 MONTHS) Which of the following statements is true? A. Administrative tasks such as backing up, restoring, and reorganizing data stored in the SALES table must be done at the table level; not at the partition level B. Data can be quickly rolled in and out of the SALES table by using the ATTACH PARTITION and DETACH PARTITION clauses of the ALTER TABLE statement C. If an index is created for the SALES table, its data must be stored in table space TBSP0 D. When resolving queries against the SALES table, each partition used is scanned asynchronously and the results of each partition scan are merged to produce the result data set returned
136.	Which of the following is NOT a characteristic of a declared temporary table? A. Declared temporary tables are not persistent and can only be used by the application that creates them B. It is possible for many applications to create declared temporary tables that have the same name C. Declared temporary tables are created by executing a CREATE TABLE statement with the DECLARED GLOBAL TEMPORARY clause specified D. Once the application that created a global temporary table is terminated, any records in the table are deleted and the table is automatically destroyed Data Concurrency

Answers

102.	The correct answer is D. NUMBER, INTERVAL, and BYTE are not valid DB2 data types. The terms DECIMAL, DEC, NUMERIC, and NUM are used to denote the decimal data type. The decimal data type is used to store numeric values that contain both whole and fractional parts, separated by a decimal point. The exact location of the decimal point is determined by the precision and the scale of the value (the scale is the number of digits used by the fractional part). The maximum precision allowed for decimal values is 31 digits, and the corresponding scale must be a positive number less than the precision of the number. If no precision or scale is specified, a scale of 5 and a precision of 0 is used by default - DECIMAL(5,0).
103.	The correct answer is C. The XML data type is used to store XML documents in their native format. The amount of storage space set aside to store an XML document varies and is determined in part, by the size and characteristics of the XML document being stored. The integer data type is used to store numeric values that have a precision of 10 digits. The range for integer values is -2,147,483,648 to 2,147,483,647, and 4 bytes of storage space is required for every integer value stored. The terms INTEGER and INT are used to denote the integer data type. The fixed-length character string data type is used to store character string values that are between 1 and 254 characters in length. The amount of storage space needed to store a fixed-length character string value can be determined by solving the following equation: (Number of characters x 1) = Bytes required. (A fixed amount of storage space is allocated, even if all of the space allocated is not needed-short strings are padded with blanks.) The terms CHARACTER and CHAR are used to denote the fixed-length character string data type. The double-precision floating-point data type is used to store a 64-bit approximation of a real number. This number can be zero, or it can fall within the range -1.79769E+308 to -2.225E-307 or 2.225E-307 to 1.79769E+308. Each double-precision floating-point value can be up to 53 digits in length, and 8 bytes of storage space is required for every value stored. The terms DOUBLE, DOUBLE PRECISION, and FLOAT are used to denote the double-precision floating-point data type.
104.	The correct answer is A. The decimal data type is used to store numeric values that contain both whole and fractional parts, separated by a decimal point. The terms DECIMAL, DEC, NUMERIC, and NUM are used to denote the decimal data type. Since currency values contain both whole and fractional parts, the decimal data type is the best choice to base a user-defined data type on. And to create a distinct data type named CURRENCY that can be used to store numeric data you would execute a CREATE DISTINCT TYPE SQL statement that looks like the one shown in Answer A.
105.	The correct answer is B. The character large object (CLOB) data type is used to store single-byte character data. The binary large object (BLOB) data type is used to store binary data; the double-byte character large object (DBCLOB) data type is used to store double-byte character data; and the fixed-length double-byte character string (GRAPHIC) data type is used to store double-byte character data strings.
106.	The correct answer is D. The data type assigned to an identity column must be a numeric data type with a scale of 0; therefore, the only data types that can be assigned to an identity column are: SMALLINT, INTEGER, BIGINT, DECIMAL/NUMERIC, or a user-defined data type that is based on one of these data types.
107.	The correct answer is B. A date value can be stored using a date (DATE), timestamp (TIMESTAMP), or character string (CHAR) data type. The time (TIME) data type, on the other hand, can only be used to store a time value.
108.	The correct answer is A. Although each data type specified is valid, the CHAR(15) data type will only require 16 bytes of storage whereas the VARCHAR(15) data type will need 20 bytes of storage, the LONG VARCHAR data type will need 40 bytes, and the CLOB(1K) data type will require over 1024 bytes of storage space.
109.	The correct answer is C. The XMLPARSE function parses a character string and returns an XML value; the character string expression to be parsed must evaluate to a well-formed XML document that conforms to XML 1.0, as modified by the XML Namespaces recommendation. Answers A, B, and D are character strings that are comprised of a starting tag, an optional value, and a corresponding ending tag. As a result, these strings can be converted into a well-formed, but small, XML documents.
110.	The correct answers are C and E. At a minimum, when a new table is created, a table name, one or more column names, and corresponding column data types must be specified. Primary keys, constraints (NOT NULL, default, check, unique, referential integrity, and informational), and table space information is optional and is not required.
111.	The correct answer is C. A trigger is used to produce an action as a result of a change to a table. Views provide users with alternate ways to see table data. And because a view can reference the data stored in any number of columns found in the base table it refers to, views can be used, together with view privileges, to control what data a user can and cannot see. Furthermore, if a view is created with the WITH [LOCAL | CASCADED] CHECK OPTION specified, it can be used to ensure that all rows added to a table through it conform to its definition.
112.	The correct answers are D and E. When the CREATE TABLE … LIKE … statement is executed, each column of the table that is created will have exactly the same name, data type and nullability characteristic as the columns of the source table used to create the new table. Furthermore, if the EXCLUDING COLUMN DEFAULTS option is not specified (which is the case in this example), all column defaults will be copied as well. However, the new table will not contain any unique constraints, foreign key constraints, triggers, or indexes that exist in the original.
113.	The correct answer is A. The Insert Rule for a referential constraint guarantees that a value can never be inserted into the foreign key of a child table unless a matching value can be found in the corresponding parent key of the associated parent table. Any attempt to insert records into a child table that violates this rule will result in an error, and the insert operation will fail. The Insert Rule for a referential constraint is implicitly created when the referential constraint itself is created. In this example, the EMPID column of table TAB2 is a foreign key (in a child table) that references the ID column (the parent key) of table TAB1 (the parent table). Therefore, because of the Insert Rule, values cannot be added to the EMPID column of table TAB2 that do not already exist in the ID column of table TAB1.
114.	The correct answer is D. A unique index, a check constraint, and a referential constraint place restrictions on what can and cannot be stored in the column(s) they are associated with. A default constraint, however, is used to provide a default value for a particular column if no data is provided for that column when data is inserted into a table; if a value is provided for the column, the default value is ignored.
115.	The correct answer is C. A check constraint is 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. Check constraints are comprised of one or more predicates that collectively are known as the check condition. This check condition is compared with the data value provided and the result of this comparison is returned as the value TRUE, FALSE, or Unknown. If the check constraint returns the value TRUE, the value is acceptable, so it is added to the database. If, on the other hand, the check constraint returns the value FALSE or Unknown, the operation attempting to place the value in the database fails, and all changes made by that operation are backed out. In this example, the check constraint CST1 defined for table TAB1 only allows the values 1, 2, or 3 to be entered into column COL1. The INSERT statement shown in Answer C is the only INSERT statement that has a valid value specified for column COL1.
116.	The correct answers are C and E. When a unique index is created for a column, every value found in that column must be unique, and one of the column's unique values can be the null value.
117.	The correct answer is A. The ON DELETE RESTRICT ensures that whenever a delete operation is performed on the parent table of a referential constraint, the value for the foreign key of each row in the child table will have the same matching value in the parent key of the parent table that it had before the delete operation was performed. Therefore, in this example no row will be deleted from the MAKE because two rows exist in the MODEL table that references the row the DELETE statement is trying to remove. Had the ON DELETE CASCADE definition been used instead, the delete operation would have succeeded and the tables would have looked like this: MAKE MAKEID MAKE ------ ---------- 2 Chevrolet 3 Toyota MODEL MODELID MODEL MAKEID ----- ------- ------- 3 Malibu 2 4 Camry 3 On the other hand, if the ON DELETE SET NULL definition had been used, the delete operation would have succeeded and the tables would have looked like this: MAKE MAKEID MAKE ---- ---------- 2 Chevrolet 3 Toyota MODEL MODELID MODEL MAKEID ------ -------- ------ 1 Mustang _ 2 Escort _ 3 Malibu 2 4 Camry 3
118.	The correct answer is C. The check constraint (CONST1) for TABLEA will only allow the values 1, 2, or 3 to be entered into column COL1. The NOT NULL constraint prohibits null values, the value 0 is not a valid value, and the value '1' is a character value (the column COL1 was defined using a numeric data type).
119.	The correct answer is D. The ON DELETE RESTRICT delete rule and the ON DELETE NO ACTION delete rule prevent the deletion of parent rows in a parent table if dependent rows that reference the primary row being deleted exist in the corresponding child table, and the ON DELETE SET NO VALUE delete rule is an invalid rule. On the other hand, the ON DELETE CASCADE delete rule will allow rows in the parent table to be deleted; if dependent rows that reference the primary row being deleted exist in the corresponding child table, they will be deleted as well.
120.	The correct answer is D. A unique index allows one and only one NULL value; the value "NULL" means a column's value is undefined and distinct from any other value. The remaining characteristics are true for unique indexes.
121.	The correct answer is D. In this example, the statement "INSERT INTO employee VALUES (DEFAULT, 'Smith', 10, '11/18/2004', 60250.00)" will fail because the hire date and the salary specified violates check constraint CST1 - YEAR(hiredate) > 2006 OR salary > 60500).
122.	The correct answer is B. 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 Database 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 resolve 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.
123.	The correct answer is D. 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.
124.	The correct answer is B. 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.
125.	The correct answer is B. To create a referential constraint, you define a primary key, using one or more columns in the parent table, and you define a foreign key for one or more corresponding columns in the child table that reference the parent table's primary key. (The list of column names in the foreign key clause must be identical to the list of column names specified in the primary key OR a unique constraint for the columns in the parent table that are referenced by the foreign key in the child must exist in order for a referential constraint to be successfully created.)
126.	The correct answer is B. The value assigned to the CURRENT SCHEMA special register is not persistent across database restarts. Therefore, if you assign a value to the CURRENT SCHEMA special register, disconnect from the database, and reconnect, the CURRENT SCHEMA special register will contain your authentication ID - not the value you assigned it earlier.
127.	The correct answer is A. Since no unique or primary constraints were included in the table definition, no unique indexes are created.
128.	The correct answer is D. A view is a named specification of a result table that is populated whenever the view is referenced in an SQL statement. (Each time a view is referenced, a query is executed and the results are retrieved from the underlying table and returned in a table-like format.) Like base tables, views can be thought of as having columns and rows. And in most cases, data can be retrieved from a view the same way it can be retrieved from a table.
129.	The correct answer is C. If the WITH LOCAL CHECK OPTION clause of with the CREATE VIEW SQL statement is specified, insert and update operations performed against the view that is created are validated to ensure that all rows being inserted into or updated in the base table the view refers to conform to the view's definition (otherwise, the insert/update operation will fail). So what exactly does this mean? Suppose a view was created using the following CREATE VIEW statement: CREATE VIEW priority_orders AS SELECT * FROM orders WHERE response_time < 4 WITH LOCAL CHECK OPTION Now, suppose a user tries to insert a record into this view that has a RESPONSE_TIME value of 6. The insert operation will fail because the record violates the view's definition. Had the view not been created with the WITH LOCAL CHECK OPTION clause, the insert operation would have been successful, even though the new record would not be visible to the view that was used to add it. Because VIEW1 was created using a SELECT statement that only references rows that have a value less than 100 in COL1 and because VIEW1 was created with the WITH LOCAL CHECK OPTION specified, each value inserted into COL1 (using VIEW1) must be less than 100. In addition, because COL2 was defined using a character data type, all values inserted into COL2 must be enclosed in single quotes. The INSERT statements shown in Answers B and D will fail because the value to be assigned to COL1 exceeds 100; the INSERT statement shown in Answer A will fail because the value "abc" is not enclosed in single quotation marks.
130.	The correct answer is A. The ON DELETE NO ACTION definition ensures that whenever a delete operation is performed on the parent table in a referential constraint, the value for the foreign key of each row in the child table will have a matching value in the parent key of the parent table (after all other referential constraints have been applied). Therefore, no row will be deleted from TABLEA because a row exists in TABLEB that references the row the DELETE statement is trying to remove. And because the ON DELETE CASCADE definition was not used, no row will be deleted from TABLEB.
131.	The correct answer is C. Whenever an insert operation, an update operation, or a delete operation is performed against the subject table or view, a trigger can be activated (fired).
132.	The correct answer is D. Triggers are only fired if the trigger event they have been designed to watch for takes place against the subject table they are designed to interact with. In this example, no UPDATE trigger was defined; therefore, no triggers are fired when the sales table is updated. Trigger TRIGGER_C will be fired anytime a delete operation is performed against the SALES table and triggers TRIGGER_A and TRIGGER_B will be fired in the order they were created whenever an insert operation is performed against the SALES table. Trigger TRIGGER_A is designed to calculate a sales commission for an invoice based on the sale amount; trigger TRIGGER_B is designed to assign a value to the BILL_DATE column that is 30 days from today; and trigger TRIGGER_C is designed to display an error message whenever anyone tries to delete records from the SALES table.
133.	The correct answer is B. Both primary keys and unique indexes can be defined over one or more columns in a table.
134.	The correct answer is A. The XML data type can be used to store well-formed XML documents in their native format. A table can contain any number of XML columns; however each XML column used has the following restrictions: It cannot be part of any index except an XML index. It cannot be included as a column of a primary key or unique constraint. It cannot be a foreign key of a referential constraint. It cannot have a specified default value or a WITH DEFAULT clause-if the column is nullable, the default value for the column is the null value. It cannot be used in a table with a distribution key. It cannot be used in range-clustered or range-partitioned tables. In addition, XML columns can only be referenced in a check constraint if the check constraint contains the VALIDATED predicate. (The VALIDATED predicate checks to see if an XML value has been validated using the XMLVALIDATE() function. The XMLVALIDATE() function returns a copy of the input XML value, augmented with information obtained from XML schema validation, including default values and type annotations. If the value of the column is null, the result of the VALIDATED predicate is unknown; otherwise, the result is either TRUE or FALSE.)
135.	The correct answer is B. The SALES table in the example is partitioned such that each quarter's data is stored in a different data partition, and each partition resides in a different table space. Advantages of using table partitioning include: Easy roll-in and roll-out of data. Rolling in partitioned table data allows a new range to be easily incorporated into a partitioned table as an additional data partition. Rolling out partitioned table data allows you to easily separate ranges of data from a partitioned table for subsequent purging or archiving. Data can be quickly rolled in and out by using the ATTACH PARTITION and DETACH PARTITION clauses of the ALTER TABLE statement. Easier administration of large tables. Table level administration becomes more flexible because administrative tasks can be performed on individual data partitions. Such tasks include: detaching and reattaching of a data partition, backing up and restoring individual data partitions, and reorganizing individual indexes. In addition, time consuming maintenance operations can be shortened by breaking them down into a series of smaller operations. For example, backup operations can be performed at the data partition level when the each data partition is placed in separate table space. Thus, it is possible to backup one data partition of a partitioned table at a time. Flexible index placement. With table partitioning, indexes can be placed in different table spaces allowing for more granular control of index placement. Better query processing. When resolving queries, one or more data partitions may be automatically eliminated, based on the query predicates used. This functionality, known as Data Partition Elimination, improves the performance of many decision support queries because less data has to be analyzed before a result data set can be returned.
136.	The correct answer is C. Unlike base tables, whose descriptions and constraints are stored in the system catalog tables of the database to which they belong, declared temporary tables are not persistent and can only be used by the application that creates them-and only for the life of the application. (Once the application that created the global temporary table is terminated, any records in the table are deleted and the table itself is destroyed.) When the application that creates a declared temporary table terminates, the rows of the table are deleted, and the definition of the table is dropped. (However, data stored in a temporary table can exist across transaction boundaries.) Another significant difference between the two centers around naming conventions: Base table names must be unique within a schema, but because each application that defines a declared temporary table has its own instance of that table, it is possible for many applications to create declared temporary tables that have the same name. And where base tables are created with the CREATE TABLE SQL statement, declared temporary tables are created with the DECLARE GLOBAL TEMPORARY TABLE statement.