A2090-610 - Assessment: DB2 10.1 Fundamentals - Assessment
Go back to IBM
Which SQL statement will retrieve the department number (WORKDEPT) and average departmental salary (SALARY) for all departments in a table named EMPLOYEE and arrange the result set in ascending order by average departmental salary?
SELECT workdept, AVG(salary)FROM employeeGROUP BY workdeptORDER BY 2
What is the primary purpose of a view?
To combine data from multiple tables.
The EXCLUSIVE MODE option of the LOCK TABLE statement is used to prevent which of the following?
Concurrent application processes from performing any operations on the table.
Which privilege is required to invoke a user defined function?
Given the SQL statement: CREATE ALIAS total_sales FOR sales Which statement is valid?
SALES can be the name of an existing alias.
What type of mechanism is a simple token value that is used to refer to a much bigger large object (LOB)?
Which event will NOT cause a trigger to be fired?
Execution of LOAD REPLACE operation.
When an application using a temporary table terminates or disconnects from the database unexpectedly, what happens to the temporary table and any data stored in it?
The data in the table is deleted and the table is implicitly dropped.
What are INSTEAD OF triggers used for?
To perform insert, update, and delete operations against complex views.
When a COMMIT statement is executed, what happens?
The current transaction is terminated and a new transaction boundary is started.
Which statement about triggers is true?
A trigger performs actions in response to an event like an INSERT, UPDATE or DELETE operation on a table.
Which platform supports DB2 pureScale environments?
Which SQL statement will allow user USER1 to create a view on a table named EMPLOYEE?
GRANT SELECT ON TABLE employee TO user1
Which statement about BEFORE triggers is FALSE?
A BEFORE trigger is fired for each row in the set of affected rows instead of executing the trigger event.
Which function can be used to obtain values from XML documents that are to be inserted into one or more tables?
Which object is a stored procedure defined into?
Which statement regarding referential integrity constraints is true?
Primary keys and foreign keys are used to define relationships.
If the following command is executed: CREATE DATABASE test What is the page size (in kilobytes) of the database?
Which object is used to cache table and index data as it is read from disk?
Which type of temporal table can be used to store only time-sensitive data?
What is a function of a table check constraint?
To provide a method of ensuring a column's values are within a specific range.
A column mask that is to be used for row and column access control (RCAC) was created with the DISABLE option specified. What must be done if this mask is to be used to restrict access to data stored in a table named EMPLOYEE?
The column mask must be enabled; the EMPLOYEE table must be altered to activate column access control.
What is used in conjunction with a foreign key to define a relationship between two tables?
Which statement about application-period temporal tables is true?
Is based on explicitly-supplied timestamps that define the time periods during which data is valid.
Given the following statement: GRANT DELETE ON TABLE sales TO User1 WITH GRANT OPTION; Which statement is correct?
USER1 can remove records from the SALES table and grant the privilege to remove records from the SALES table to other users.
User USER1 wants to retrieve records from a table named EMPLOYEE that satisfy at least one of the following criteria: Which SQL statement will accomplish this?
SELECT * FROM employeeWHERE (hiredate < '1999-01-01' AND salary < 40000)OR (education = 'University')
Which statement regarding triggers is true?
Triggers cannot be modified, they must be dropped and recreated with new definitions.
When is a mask used to limit access to data in a table?
When row and column access control (RCAC) is used to protect the table.
In a trusted context environment, if a switch request is made with an authorization ID that is not allowed on a trusted connection, what state is the connection placed in?
Which statement is true about an index that is used to support a UNIQUE constraint?
It must be defined with the UNIQUE attribute.
Which type of table is best suited for recording results from application data manipulation that is not required to be persistent?
You have a business need to query DB2 10 and DB2 9 databases and you want to write an application that can run on most platforms unchanged. Which interface would you use to achieve your goal?
Which object can be enabled for compression?
Which statement about system-period temporal tables is true?
They can be queried without a time period specification.
What is the purpose of the Query Tuner?
To provide recommendations and analysis for tuning a single query.
Which SQL statement will give a user named USER1 the ability to run an embedded SQL application named BENEFITS that calls a package named CORPDATA.PKGA?
GRANT EXECUTE ON PACKAGE corpdata.pkga TO user1
After creating several new indexes and executing RUNSTATS, user USER1 wants to re-create a package that is stored in the database so it will take advantage of the new indexes. Which authority/privilege must user USER1 be granted in order to re-create the package?
What is the act of exchanging one lock an application holds on a resource for a more restrictive lock on the same resource known as?
Which SQL statement will retrieve the employee number (EMPNO), hire date (HIREDATE), and salary (SALARY) for each employee from a table named EMPLOYEE who was hired before 1998 and earns a salary of less than $35,000.00 per year?
SELECT empno, hiredate, salaryFROM employeeWHERE hiredate < '1998-01-01' AND salary < 35000
Which DB2 object limits the user's ability to retrieve data from a table by defining a SQL statement in the object?
Which isolation level offers the greatest protection of data but provides the least amount of concurrency?
Repeatable Read (RR)
What isolation level prevents dirty reads, nonrepeatable reads, and phantoms?
Repeatable read (RR)
What is the primary purpose for using the INCLUDE clause with a CREATE UNIQUE INDEX statement?
To improve performance of some queries by allowing index-only access.
Which tool allows users to connect to a DB2 database using a wizard?
IBM Data Studio
Which SQL statement should be used to select the minimum and maximum salaries (SALARY), by job code (JOB), from a table EMPLOYEE?
SELECT job, MIN(salary), MAX(salary)FROM employeeGROUP BY job;
Which type of lock allows the lock owner and all concurrent applications to read, but not update, the locked data?
A table named SALES contains a record for every sales transaction a company processes. A user wishes to see the number of transactions that are made by each salesman. Which SQL statement will produce the desired results?
SELECT name, COUNT(*) AS transactionsFROM salesGROUP BY name
Which statement will prevent concurrent application processes from performing anything other than read-only operations against a table named TAB1?
LOCK TABLE tab1 IN SHARE MODE
Which statement about large object (LOB) locators is true?
A LOB locator represents a value for a LOB resource that is stored in a database.
Which is NOT a valid reason for creating an index?
To order the columns of a table in ascending or descending sequence according to values in a row.