DB2 Practice 1

1. Which clause should be included in a ALTER TABLE statement to update a table definition with anew column?

ADD COLUMN

ALTER COLUMN

APPEND COLUMN

RENAME COLUMN

The correct answer is "ADD COLUMN" because when using the ALTER TABLE statement to update a table definition with a new column, the ADD COLUMN clause is used. This clause allows the user to add a new column to the existing table.

Explanation

The correct answer is "ADD COLUMN" because when using the ALTER TABLE statement to update a table definition with a new column, the ADD COLUMN clause is used. This clause allows the user to add a new column to the existing table.

2. Which command will delete all rows from a table without generating log records?

TRIM

DROP

DELETE

TRUNCATE

TRUNCATE command is used to delete all rows from a table without generating log records. It is a faster and more efficient method compared to the DELETE command because it does not generate individual log records for each deleted row. Instead, it deallocates the data pages and records the deallocation in the log file as a single operation. This makes TRUNCATE a preferred choice when the goal is to quickly remove all data from a table.

Explanation

TRUNCATE command is used to delete all rows from a table without generating log records. It is a faster and more efficient method compared to the DELETE command because it does not generate individual log records for each deleted row. Instead, it deallocates the data pages and records the deallocation in the log file as a single operation. This makes TRUNCATE a preferred choice when the goal is to quickly remove all data from a table.

3. Which SQL statement will retrieve the employee number (EMPNO), hire date (HIREDATE), andsalary (SALARY) for each employee from a table named EMPLOYEE who was hired before 1998and earns a salary of less than $35,000.00 per year?

SELECT empno, hiredate, salaryFROM employeeFOR hiredate < '1998-01-01' AND salary < 35000

SELECT empno, hiredate, salaryFROM employeeWHERE hiredate < '1998-01-01' AND salary < 35000

SELECT empno, hiredate, salaryFROM employeeWHERE hiredate < '1998-01-01' OR salary < 35000

SELECT empno, hiredate, salaryFROM employee FOR hiredate < '1998-01-01' OR salary < 35000

The correct answer is the second option:
SELECT empno, hiredate, salary
FROM employee
WHERE hiredate
This statement uses the WHERE clause to filter the results based on two conditions: hiredate being before 1998 and salary being less than $35,000. It will retrieve the employee number, hire date, and salary for each employee who meets these criteria from the EMPLOYEE table.

Explanation

The correct answer is the second option:
SELECT empno, hiredate, salary
FROM employee
WHERE hiredate
This statement uses the WHERE clause to filter the results based on two conditions: hiredate being before 1998 and salary being less than $35,000. It will retrieve the employee number, hire date, and salary for each employee who meets these criteria from the EMPLOYEE table.

4. Which statement about triggers is true?

A trigger is designed to return specific types of values and contains executable code.

A trigger is designed to return specific types of values but contains no executable code.

A trigger performs actions to capture details about an activity like a join operation againstmultiple tables.

A trigger performs actions in response to an event like an INSERT, UPDATE or DELETEoperation on a table.

A trigger is a type of database object that is designed to perform actions in response to specific events, such as an INSERT, UPDATE, or DELETE operation on a table. It is not designed to return specific types of values, nor does it contain executable code. Instead, a trigger is used to capture details about an activity, such as a join operation against multiple tables. Therefore, the correct statement about triggers is that they perform actions in response to an event on a table.

Explanation

A trigger is a type of database object that is designed to perform actions in response to specific events, such as an INSERT, UPDATE, or DELETE operation on a table. It is not designed to return specific types of values, nor does it contain executable code. Instead, a trigger is used to capture details about an activity, such as a join operation against multiple tables. Therefore, the correct statement about triggers is that they perform actions in response to an event on a table.

5. Which tool allows users to connect to a DB2 database using a wizard?

Control Center

Universal Connection Expert

DB2 Connection Pool Manager

IBM Data Studio

IBM Data Studio is the correct answer because it is a comprehensive tool that allows users to connect to a DB2 database using a wizard. It provides a user-friendly interface with step-by-step instructions to establish a connection to the database. This tool offers various features such as database administration, SQL development, and performance tuning, making it a suitable choice for connecting to a DB2 database. Control Center, Universal Connection Expert, and DB2 Connection Pool Manager do not specifically provide a wizard for connecting to a DB2 database.

Explanation

IBM Data Studio is the correct answer because it is a comprehensive tool that allows users to connect to a DB2 database using a wizard. It provides a user-friendly interface with step-by-step instructions to establish a connection to the database. This tool offers various features such as database administration, SQL development, and performance tuning, making it a suitable choice for connecting to a DB2 database. Control Center, Universal Connection Expert, and DB2 Connection Pool Manager do not specifically provide a wizard for connecting to a DB2 database.

6. If the following command is executed: CREATE DATABASE test What is the page size (inkilobytes) of the database?

4

8

16

32

The page size of the database created with the command "CREATE DATABASE test" is 4 kilobytes. This means that each page in the database will have a size of 4 kilobytes, which is a common default page size for many database systems. A larger page size can improve performance for certain types of workloads, but it also requires more memory and storage space.

Explanation

The page size of the database created with the command "CREATE DATABASE test" is 4 kilobytes. This means that each page in the database will have a size of 4 kilobytes, which is a common default page size for many database systems. A larger page size can improve performance for certain types of workloads, but it also requires more memory and storage space.

7. Which DB2 object is used to provide a logical grouping of other database objects?

Alias

Index

Schema

Collection

A schema in DB2 is used to provide a logical grouping of other database objects. It acts as a container for organizing and categorizing related tables, views, indexes, procedures, and other database objects. By using schemas, it becomes easier to manage and maintain the database as it allows for better organization and separation of objects based on their purpose or functionality.

Explanation

A schema in DB2 is used to provide a logical grouping of other database objects. It acts as a container for organizing and categorizing related tables, views, indexes, procedures, and other database objects. By using schemas, it becomes easier to manage and maintain the database as it allows for better organization and separation of objects based on their purpose or functionality.

8. Which operation normally does NOT require an exclusive lock?

SELECT

GRANT

DROP

BIND

The SELECT operation in a database normally does not require an exclusive lock. An exclusive lock is used to prevent other transactions from accessing or modifying the data while a transaction is in progress. However, a SELECT operation only reads the data and does not modify it, so it does not need to acquire an exclusive lock. This allows multiple SELECT operations to be executed concurrently without blocking each other. On the other hand, operations like GRANT, DROP, and BIND may modify the database and therefore require an exclusive lock.

Explanation

The SELECT operation in a database normally does not require an exclusive lock. An exclusive lock is used to prevent other transactions from accessing or modifying the data while a transaction is in progress. However, a SELECT operation only reads the data and does not modify it, so it does not need to acquire an exclusive lock. This allows multiple SELECT operations to be executed concurrently without blocking each other. On the other hand, operations like GRANT, DROP, and BIND may modify the database and therefore require an exclusive lock.

9. What factor influences lock escalation?

Table size

Buffer space

Number of locks

Available real storage

Lock escalation is a process in database management systems where a lock on a lower-level object is escalated to a higher-level object, reducing the overall number of locks held. The number of locks is a factor that influences lock escalation. As the number of locks increases, the system may escalate the locks to a higher level to improve performance and reduce overhead. This helps to minimize the resources required to manage locks and improves concurrency in the system. Therefore, the number of locks is an important factor in determining when lock escalation occurs.

Explanation

Lock escalation is a process in database management systems where a lock on a lower-level object is escalated to a higher-level object, reducing the overall number of locks held. The number of locks is a factor that influences lock escalation. As the number of locks increases, the system may escalate the locks to a higher level to improve performance and reduce overhead. This helps to minimize the resources required to manage locks and improves concurrency in the system. Therefore, the number of locks is an important factor in determining when lock escalation occurs.

10. What is used in conjunction with a foreign key to define a relationship between two tables?

Primary key

Partitioning key

Check constraint

Unique constraint

A primary key is used in conjunction with a foreign key to define a relationship between two tables. The primary key is a unique identifier for each record in a table, and it is used as a reference in the foreign key of another table to establish a connection between the two. This relationship ensures data integrity and allows for efficient data retrieval and manipulation through join operations.

Explanation

A primary key is used in conjunction with a foreign key to define a relationship between two tables. The primary key is a unique identifier for each record in a table, and it is used as a reference in the foreign key of another table to establish a connection between the two. This relationship ensures data integrity and allows for efficient data retrieval and manipulation through join operations.

11. When an application using a temporary table terminates or disconnects from the databaseunexpectedly, what happens to the temporary table and any data stored in it?

The data in the table is deleted and the table persists.

The data in the table is deleted and the table is implicitly dropped.

The temporary table is converted to a base table and the data is deleted.

The temporary table is converted to a base table and the data is persistent.

When an application using a temporary table terminates or disconnects from the database unexpectedly, the data stored in the temporary table is deleted and the table itself is implicitly dropped. This means that both the table structure and the data it contained are completely removed from the database.

Explanation

When an application using a temporary table terminates or disconnects from the database unexpectedly, the data stored in the temporary table is deleted and the table itself is implicitly dropped. This means that both the table structure and the data it contained are completely removed from the database.

12. Which case will require a sequence to be dropped and recreated instead of being modified by theALTER SEQUENCE statement?

Change the data type of the sequence.

Reset the sequence to its starting value.

Establish new minimum or maximum values.

Change the increment between future values.

When the data type of a sequence needs to be changed, it cannot be modified directly using the ALTER SEQUENCE statement. This is because the data type of a sequence is determined when it is created and cannot be altered afterwards. Therefore, in order to change the data type of a sequence, it needs to be dropped and recreated with the desired data type.

Explanation

When the data type of a sequence needs to be changed, it cannot be modified directly using the ALTER SEQUENCE statement. This is because the data type of a sequence is determined when it is created and cannot be altered afterwards. Therefore, in order to change the data type of a sequence, it needs to be dropped and recreated with the desired data type.

13. Which SQL statement should be used to select the minimum and maximum salaries (SALARY), byjob code (JOB), from a table EMPLOYEE?

SELECT job, MIN(salary), MAX(salary)FROM employeeGROUP BY job;

SELECT job, MIN(salary), MAX(salary)FROM employeeORDER BY job;

SELECT job, MIN(salary), MAX(salary) FROM employeeGROUP BY job, MIN(salary), MAX(salary);

SELECT JOB, MIN(salary), MAX(salary)FROM employeeORDER BY job, MIN(salary), MAX(salary);

The correct answer is "SELECT job, MIN(salary), MAX(salary)
FROM employee
GROUP BY job;"

This statement is correct because it uses the GROUP BY clause to group the salaries by job code, allowing us to calculate the minimum and maximum salaries for each job. The MIN(salary) function returns the minimum salary for each job, while the MAX(salary) function returns the maximum salary for each job. This statement will give us the desired result of selecting the minimum and maximum salaries by job code from the EMPLOYEE table.

Explanation

The correct answer is "SELECT job, MIN(salary), MAX(salary)
FROM employee
GROUP BY job;"

This statement is correct because it uses the GROUP BY clause to group the salaries by job code, allowing us to calculate the minimum and maximum salaries for each job. The MIN(salary) function returns the minimum salary for each job, while the MAX(salary) function returns the maximum salary for each job. This statement will give us the desired result of selecting the minimum and maximum salaries by job code from the EMPLOYEE table.

14. What is the primary purpose of a view?

To enable uniqueness.

To ensure data clustering.

To combine data from multiple tables.

To optimize queries ran against multiple tables.

The primary purpose of a view is to combine data from multiple tables. Views allow users to retrieve and manipulate data from multiple tables as if it were a single table. This simplifies the querying process and provides a more organized and efficient way to access data. By creating a view, users can avoid the need to write complex join queries every time they need to retrieve data from multiple tables. Views also provide an added layer of security by allowing users to access only the data they need without exposing the underlying tables.

Explanation

The primary purpose of a view is to combine data from multiple tables. Views allow users to retrieve and manipulate data from multiple tables as if it were a single table. This simplifies the querying process and provides a more organized and efficient way to access data. By creating a view, users can avoid the need to write complex join queries every time they need to retrieve data from multiple tables. Views also provide an added layer of security by allowing users to access only the data they need without exposing the underlying tables.

15. What is the purpose of the Query Tuner?

To automatically capture and stop rogue queries.

To provide recommendations and analysis for tuning a single query.

To provide recommendations and analysis for tuning up to 100 queries.

To recommend indexes and to guide DBAs through the process of creating new indexes.

The purpose of the Query Tuner is to provide recommendations and analysis for tuning a single query. This tool helps optimize the performance of a specific query by suggesting improvements and providing analysis on its execution plan. It helps identify potential bottlenecks, suggests index changes, and provides insights on how to improve the query's efficiency.

Explanation

The purpose of the Query Tuner is to provide recommendations and analysis for tuning a single query. This tool helps optimize the performance of a specific query by suggesting improvements and providing analysis on its execution plan. It helps identify potential bottlenecks, suggests index changes, and provides insights on how to improve the query's efficiency.

16. Which command is used to back out a subset of database changes that have been made within a unit of work?

ROLLBACK TO SAVEPOINT

COMMIT TO SAVEPOINT

ROLLBACK

COMMIT

The ROLLBACK TO SAVEPOINT command is used to undo a subset of database changes that have been made within a unit of work. It allows you to revert back to a specific savepoint within a transaction, discarding any changes made after that savepoint. This is useful in situations where you want to undo certain changes without rolling back the entire transaction. The other options, COMMIT TO SAVEPOINT, ROLLBACK, and COMMIT, do not provide the functionality to selectively undo changes within a unit of work.

Explanation

The ROLLBACK TO SAVEPOINT command is used to undo a subset of database changes that have been made within a unit of work. It allows you to revert back to a specific savepoint within a transaction, discarding any changes made after that savepoint. This is useful in situations where you want to undo certain changes without rolling back the entire transaction. The other options, COMMIT TO SAVEPOINT, ROLLBACK, and COMMIT, do not provide the functionality to selectively undo changes within a unit of work.

17. Which object is a stored procedure defined into?

Table

Schema

Package

Database

A stored procedure is a set of SQL statements that are stored in a database and can be executed later. These procedures are typically organized and grouped into logical units called schemas. A schema is a container that holds database objects such as tables, views, and stored procedures. Therefore, a stored procedure is defined within a schema, allowing for better organization and management of database objects.

Explanation

A stored procedure is a set of SQL statements that are stored in a database and can be executed later. These procedures are typically organized and grouped into logical units called schemas. A schema is a container that holds database objects such as tables, views, and stored procedures. Therefore, a stored procedure is defined within a schema, allowing for better organization and management of database objects.

18. Which privilege is required to invoke a user defined function?

CALL

USAGE

EXECUTE

REFERENCES

To invoke a user-defined function, the privilege required is EXECUTE. This privilege allows the user to execute or run the function. The EXECUTE privilege is necessary to access and utilize the functionality provided by the user-defined function. It grants the user the authority to execute the code within the function and retrieve the desired results. Without the EXECUTE privilege, the user would not be able to invoke the user-defined function and utilize its functionality.

Explanation

To invoke a user-defined function, the privilege required is EXECUTE. This privilege allows the user to execute or run the function. The EXECUTE privilege is necessary to access and utilize the functionality provided by the user-defined function. It grants the user the authority to execute the code within the function and retrieve the desired results. Without the EXECUTE privilege, the user would not be able to invoke the user-defined function and utilize its functionality.

19. When an index is created for a table, where is the metadata for that index stored?

In the table definition.

In the system catalog.

In the schema that the index resides in.

In the metadata of the table the index was created for.

The metadata for an index is stored in the system catalog. The system catalog is a collection of database objects that contain information about the database, such as tables, indexes, and views. It is used by the database management system to keep track of the structure and properties of the database objects. Storing the index metadata in the system catalog allows for easy access and management of the index information by the database management system.

Explanation

The metadata for an index is stored in the system catalog. The system catalog is a collection of database objects that contain information about the database, such as tables, indexes, and views. It is used by the database management system to keep track of the structure and properties of the database objects. Storing the index metadata in the system catalog allows for easy access and management of the index information by the database management system.

20. Which isolation level offers the greatest protection of data but provides the least amount of concurrency?

Read Stability (RS)

Cursor Stability (CS)

Repeatable Read (RR)

Uncommitted Read (UR)

Repeatable Read (RR) isolation level offers the greatest protection of data but provides the least amount of concurrency. In this isolation level, a transaction locks all the rows it accesses until the transaction is completed, preventing other transactions from modifying or deleting those rows. This ensures that the data remains consistent throughout the transaction, as any changes made by other transactions are not visible within the current transaction. However, this locking mechanism reduces concurrency, as it restricts other transactions from accessing the locked rows, potentially leading to increased waiting times for accessing the data.

Explanation

Repeatable Read (RR) isolation level offers the greatest protection of data but provides the least amount of concurrency. In this isolation level, a transaction locks all the rows it accesses until the transaction is completed, preventing other transactions from modifying or deleting those rows. This ensures that the data remains consistent throughout the transaction, as any changes made by other transactions are not visible within the current transaction. However, this locking mechanism reduces concurrency, as it restricts other transactions from accessing the locked rows, potentially leading to increased waiting times for accessing the data.

21. In a trusted context environment, if a switch request is made with an authorization ID that is notallowed on a trusted connection, what state is the connection placed in?

Locked

Waiting

Pending

Unconnected

In a trusted context environment, if a switch request is made with an authorization ID that is not allowed on a trusted connection, the connection is placed in an "Unconnected" state. This means that the connection is not established or active, and further actions are required to establish a valid connection.

Explanation

In a trusted context environment, if a switch request is made with an authorization ID that is not allowed on a trusted connection, the connection is placed in an "Unconnected" state. This means that the connection is not established or active, and further actions are required to establish a valid connection.

22. If a user has been granted SYSCTRL authority, which activities can they perform?

Load operations

Backup/copy table spaces

Create and drop databases

Monitor database performance

If a user has been granted SYSCTRL authority, they can perform the activities of creating and dropping databases. This means they have the ability to create new databases and remove existing ones as needed. This authority allows them to have control over the database structure and organization.

Explanation

If a user has been granted SYSCTRL authority, they can perform the activities of creating and dropping databases. This means they have the ability to create new databases and remove existing ones as needed. This authority allows them to have control over the database structure and organization.

23. Which action needs to be performed in order to complete the definition of an application-periodtemporal table?

A transaction-start-id column must be defined for the table.

A BUSINESS_TIME period must be specified in a CREATE or ALTER of the table.

A history table must be defined and associated with the base table.

A unique index must be created that prevents overlapping of the BUSINESS_TIME period ofthe table.

To complete the definition of an application-period temporal table, a BUSINESS_TIME period must be specified in a CREATE or ALTER of the table. This means that a time period needs to be defined for the table, indicating when the data in the table is valid or active. This allows for tracking and managing the temporal aspect of the data, such as when it was valid or active in the past or will be in the future. By specifying the BUSINESS_TIME period, the table becomes an application-period temporal table, enabling temporal data management.

Explanation

To complete the definition of an application-period temporal table, a BUSINESS_TIME period must be specified in a CREATE or ALTER of the table. This means that a time period needs to be defined for the table, indicating when the data in the table is valid or active. This allows for tracking and managing the temporal aspect of the data, such as when it was valid or active in the past or will be in the future. By specifying the BUSINESS_TIME period, the table becomes an application-period temporal table, enabling temporal data management.

24. You have a business need to query DB2 10 and DB2 9 databases and you want to write anapplication that can run on most platforms unchanged. Which interface would you use to achieveyour goal?

CLI

JDBC

XML

RUBY

JDBC (Java Database Connectivity) would be the appropriate interface to use in this scenario. JDBC is a Java API that allows developers to interact with various databases, including DB2. It provides a consistent and platform-independent way to connect to and query databases, making it suitable for writing applications that can run on different platforms without requiring significant changes. By using JDBC, the application can easily connect to both DB2 10 and DB2 9 databases and perform the necessary queries.

Explanation

JDBC (Java Database Connectivity) would be the appropriate interface to use in this scenario. JDBC is a Java API that allows developers to interact with various databases, including DB2. It provides a consistent and platform-independent way to connect to and query databases, making it suitable for writing applications that can run on different platforms without requiring significant changes. By using JDBC, the application can easily connect to both DB2 10 and DB2 9 databases and perform the necessary queries.

25. User USER1 holds both DBADM and SECADM authority and you want to separate databaseadministration activities from security administration tasks. What authority must you have in orderto revoke SECADM authority from user USER1 and assign it to someone else?

DBADM

DBCTRL

SECADM

DBMAINT

To revoke SECADM authority from user USER1 and assign it to someone else, you must have SECADM authority yourself. This authority allows you to manage security administration tasks, including granting or revoking SECADM authority to other users. DBADM authority, on the other hand, is related to database administration activities and does not grant the ability to manage security administration tasks. Therefore, having DBADM authority is not sufficient for revoking SECADM authority. Similarly, DBCTRL and DBMAINT authorities are also unrelated to security administration tasks and do not provide the necessary privileges to revoke SECADM authority.

Explanation

To revoke SECADM authority from user USER1 and assign it to someone else, you must have SECADM authority yourself. This authority allows you to manage security administration tasks, including granting or revoking SECADM authority to other users. DBADM authority, on the other hand, is related to database administration activities and does not grant the ability to manage security administration tasks. Therefore, having DBADM authority is not sufficient for revoking SECADM authority. Similarly, DBCTRL and DBMAINT authorities are also unrelated to security administration tasks and do not provide the necessary privileges to revoke SECADM authority.

26. Which type of temporal table can be used to store only time-sensitive data?

Bitemporal

Time-period

System-period

Application-period

The application-period temporal table can be used to store only time-sensitive data. This type of table allows for tracking changes to data over time and is commonly used in applications where it is important to keep a record of when certain data was valid or active. This table type is specifically designed to store time-sensitive information and is different from other temporal table types such as bitemporal, time-period, and system-period tables.

Explanation

The application-period temporal table can be used to store only time-sensitive data. This type of table allows for tracking changes to data over time and is commonly used in applications where it is important to keep a record of when certain data was valid or active. This table type is specifically designed to store time-sensitive information and is different from other temporal table types such as bitemporal, time-period, and system-period tables.

27. Which product is used to customize execution environments for the purpose of controlling systemresources so that one department or service class does not overwhelm the system?

PureScale

Data partitioning feature

Workload manager

Self-tuning memory manager

The workload manager is used to customize execution environments in order to control system resources. It allows for the allocation and prioritization of resources based on department or service class, preventing any one department or service class from overwhelming the system.

Explanation

The workload manager is used to customize execution environments in order to control system resources. It allows for the allocation and prioritization of resources based on department or service class, preventing any one department or service class from overwhelming the system.

28. When a COMMIT statement is executed, what happens?

The current transaction is terminated and a new transaction boundary is started.

Open cursors defined WITH HOLD are closed, but their data is retained.

Data stored in global temporary tables is automatically deleted.

All locks held on the database are automatically released.

When a COMMIT statement is executed, it signifies the end of the current transaction and the beginning of a new transaction boundary. This means that any changes made within the current transaction are permanently saved and become visible to other transactions. Additionally, any locks held on the database are automatically released, allowing other transactions to access the affected data. However, the COMMIT statement does not have any impact on open cursors defined with the WITH HOLD option, as they remain open and retain their data. Similarly, the COMMIT statement does not automatically delete data stored in global temporary tables.

Explanation

When a COMMIT statement is executed, it signifies the end of the current transaction and the beginning of a new transaction boundary. This means that any changes made within the current transaction are permanently saved and become visible to other transactions. Additionally, any locks held on the database are automatically released, allowing other transactions to access the affected data. However, the COMMIT statement does not have any impact on open cursors defined with the WITH HOLD option, as they remain open and retain their data. Similarly, the COMMIT statement does not automatically delete data stored in global temporary tables.

29. Which DB2 object can be used to improve the execution performance of qualified SELECTstatements?

Materialized Query Table

Sequence Object

SQL Procedure

Trigger

A Materialized Query Table (MQT) can be used to improve the execution performance of qualified SELECT statements in DB2. An MQT is a table that stores the result of a query and is updated periodically to reflect changes in the underlying data. This allows for faster retrieval of data as the query result is already pre-calculated and stored in the table. By using an MQT, the database can avoid executing the query every time it is requested, resulting in improved performance for SELECT statements.

Explanation

A Materialized Query Table (MQT) can be used to improve the execution performance of qualified SELECT statements in DB2. An MQT is a table that stores the result of a query and is updated periodically to reflect changes in the underlying data. This allows for faster retrieval of data as the query result is already pre-calculated and stored in the table. By using an MQT, the database can avoid executing the query every time it is requested, resulting in improved performance for SELECT statements.

30. Which type of constraint can be used to ensure that an INTEGER column in a table will never beassigned more than one record that contains a NULL value?

Unique constraint

Primary key constraint

Informational constraint

Column default constraint

A unique constraint can be used to ensure that an INTEGER column in a table will never be assigned more than one record that contains a NULL value. This constraint ensures that all values in the column are unique and does not allow duplicate values, including NULL. Therefore, by applying a unique constraint to the INTEGER column, it guarantees that only one record can have a NULL value in that column.

Explanation

A unique constraint can be used to ensure that an INTEGER column in a table will never be assigned more than one record that contains a NULL value. This constraint ensures that all values in the column are unique and does not allow duplicate values, including NULL. Therefore, by applying a unique constraint to the INTEGER column, it guarantees that only one record can have a NULL value in that column.

31. By default, where is the data stored for a column defined with an XML data type?

In an XML index.

In line with the rest of the data for the table.

In an XML storage object that is separate from the table.

In line with the rest of the data for the table, for XML columns less than 32KB.

The data for a column defined with an XML data type is stored in an XML storage object that is separate from the table. This means that the XML data is stored in a separate location, allowing for efficient storage and retrieval of XML data. Storing the XML data separately also helps to maintain the integrity and performance of the table by keeping the XML data separate from the rest of the data.

Explanation

The data for a column defined with an XML data type is stored in an XML storage object that is separate from the table. This means that the XML data is stored in a separate location, allowing for efficient storage and retrieval of XML data. Storing the XML data separately also helps to maintain the integrity and performance of the table by keeping the XML data separate from the rest of the data.

32. Given an EMPLOYEES table and a SALES table, a user wants to produce a list of all employeesand their associated revenue, even if no revenue exists. Which SQL statement will produce thedesired list?

SELECT employees.name, sales.revenueFROM employeesINNER JOIN sales ON employees.id = sales.emp_id

SELECT employees.name, sales.revenueFROM employeesINNER JOIN sales ON sales.emp_id = employees.id

SELECT employees.name, sales.revenueFROM salesLEFT OUTER JOIN employees ON employees.id = sales.emp_id

SELECT employees.name, sales.revenueFROM salesRIGHT OUTER JOIN employees ON employees.id = sales.emp_id

The correct answer is the fourth option. This SQL statement will produce the desired list of all employees and their associated revenue, even if no revenue exists. It uses a RIGHT OUTER JOIN to join the SALES table with the EMPLOYEES table based on the emp_id column. This ensures that all rows from the EMPLOYEES table are included in the result set, even if there is no matching revenue in the SALES table. The SELECT statement then selects the employees' names and the associated revenue from the SALES table.

Explanation

The correct answer is the fourth option. This SQL statement will produce the desired list of all employees and their associated revenue, even if no revenue exists. It uses a RIGHT OUTER JOIN to join the SALES table with the EMPLOYEES table based on the emp_id column. This ensures that all rows from the EMPLOYEES table are included in the result set, even if there is no matching revenue in the SALES table. The SELECT statement then selects the employees' names and the associated revenue from the SALES table.

33. What type of mechanism is a simple token value that is used to refer to a much bigger large object(LOB)?

Reference

Address

Pointer

Locator

A locator is a type of mechanism that is used to refer to a much larger object, such as a large object (LOB). It is a simple token value that acts as a reference to the larger object, allowing for efficient retrieval and manipulation of the object's data. This mechanism is commonly used in database systems to manage and access large data objects.

Explanation

A locator is a type of mechanism that is used to refer to a much larger object, such as a large object (LOB). It is a simple token value that acts as a reference to the larger object, allowing for efficient retrieval and manipulation of the object's data. This mechanism is commonly used in database systems to manage and access large data objects.

34. Which type of lock allows the lock owner and all concurrent applications to read, but not update,the locked data?

Share (S)

Update (U)

Exclusive (X)

Intent Exclusive (IX)

A Share (S) lock allows the lock owner and all concurrent applications to read the locked data but not update it. This means that multiple users can access the data simultaneously for reading purposes, but none of them can make changes to it. The Share lock ensures that the data remains consistent and prevents any conflicts that may arise from concurrent updates.

Explanation

A Share (S) lock allows the lock owner and all concurrent applications to read the locked data but not update it. This means that multiple users can access the data simultaneously for reading purposes, but none of them can make changes to it. The Share lock ensures that the data remains consistent and prevents any conflicts that may arise from concurrent updates.

35. Application APP_A is performing updates to table TAB1 using the cursor stability (CS) isolationlevel. If application APP_B wants to retrieve all rows from table TAB1 without waiting forapplication APP_A to finish making updates, what isolation level must application APP_B use?

Read Stability (RS)

Repeatable Read (RR)

Uncommitted Read (UR)

Cursor Stability (CS)

If application APP_B wants to retrieve all rows from table TAB1 without waiting for application APP_A to finish making updates, it must use the Uncommitted Read (UR) isolation level. This isolation level allows APP_B to read uncommitted data, meaning it can access rows that have been modified but not yet committed by APP_A. This ensures that APP_B can retrieve the most up-to-date data without waiting for any ongoing updates.

Explanation

If application APP_B wants to retrieve all rows from table TAB1 without waiting for application APP_A to finish making updates, it must use the Uncommitted Read (UR) isolation level. This isolation level allows APP_B to read uncommitted data, meaning it can access rows that have been modified but not yet committed by APP_A. This ensures that APP_B can retrieve the most up-to-date data without waiting for any ongoing updates.

36. If a table named MY_TAB contains 100 rows and the following statement is executed:

What will happen?

The last 5 rows in the table will be deleted.

The first 5 rows in the table will be deleted.

The statement will fail because a subquery cannot be used with a DELETE statement.

The statement will fail because a table name was not specified with the DELETE statement.

The given statement will delete the last 5 rows in the table named MY_TAB.

Explanation

The given statement will delete the last 5 rows in the table named MY_TAB.

37. What functionality allows users to perform a UNION operation between a DB2 table and an Oracleview?

Oracle connect

Trusted context

Oracle federation

Distributed request

The functionality that allows users to perform a UNION operation between a DB2 table and an Oracle view is called Distributed request. This functionality enables users to access and combine data from different databases, in this case, a DB2 table and an Oracle view, using a single query or request. It facilitates data integration and retrieval from multiple sources, providing a unified view of the data.

Explanation

The functionality that allows users to perform a UNION operation between a DB2 table and an Oracle view is called Distributed request. This functionality enables users to access and combine data from different databases, in this case, a DB2 table and an Oracle view, using a single query or request. It facilitates data integration and retrieval from multiple sources, providing a unified view of the data.

38. If the following result set is desired:

Which SQL statement must be executed?

SELECT lastname, firstnme, salary, jobFROM employeeORDER BY 3FETCH FIRST 5 ROWS ONLY

SELECT lastname, firstnme, salary, jobFROM employeeORDER BY 3 DESCFETCH FIRST 5 ROWS ONLY

SELECT lastname, firstnme, salary, jobFROM employeeORDER BY 3FETCH FIRST 5 ROWS

SELECT lastname, firstnme, salary, jobFROM employeeORDER BY 3 DESCFETCH FIRST 5 ROWS

The correct answer is SELECT lastname, firstnme, salary, job
FROM employee
ORDER BY 3 DESC
FETCH FIRST 5 ROWS ONLY. This statement will retrieve the last name, first name, salary, and job columns from the employee table, order the result set by the third column (salary) in descending order, and fetch only the first 5 rows. This will give the desired result set.

Explanation

The correct answer is SELECT lastname, firstnme, salary, job
FROM employee
ORDER BY 3 DESC
FETCH FIRST 5 ROWS ONLY. This statement will retrieve the last name, first name, salary, and job columns from the employee table, order the result set by the third column (salary) in descending order, and fetch only the first 5 rows. This will give the desired result set.

39. What type of large object (LOB) is used to store LOB data together with the formatted rows ondata pages, instead of in a separate LOB storage object?

Inline

Binary

Internal

Partitioned

Inline LOBs are used to store LOB data together with the formatted rows on data pages, instead of in a separate LOB storage object. This means that the LOB data is stored directly within the table's data pages, making it more efficient and reducing the need for additional storage objects.

Explanation

Inline LOBs are used to store LOB data together with the formatted rows on data pages, instead of in a separate LOB storage object. This means that the LOB data is stored directly within the table's data pages, making it more efficient and reducing the need for additional storage objects.

40. Which statement will prevent concurrent application processes from performing anything otherthan read-only operations against a table named TAB1?

LOCK TABLE tab1 IN READ MODE

LOCK TABLE tab1 IN SHARE MODE

LOCK TABLE tab1 IN EXCLUSIVE MODE

LOCK TABLE tab1 IN READ-ONLY MODE

The statement "LOCK TABLE tab1 IN SHARE MODE" will prevent concurrent application processes from performing anything other than read-only operations against the table named TAB1. This mode allows multiple sessions to read from the table simultaneously, but it prevents any session from modifying the data.

Explanation

The statement "LOCK TABLE tab1 IN SHARE MODE" will prevent concurrent application processes from performing anything other than read-only operations against the table named TAB1. This mode allows multiple sessions to read from the table simultaneously, but it prevents any session from modifying the data.

41. An SQL function designed to convert temperatures from Fahrenheit to Celsius was created asfollows:CREATE FUNCTION conv_temp(IN temp_f FLOAT)RETURNS INTEGERNO EXTERNAL ACTIONSPECIFIC convert_ftocRETURN INT((temp_f-32)/1.8) How can this function be used to convert average temperature (AVG_TEMP) data stored in a tablecalled CLIMATE_INFO?

CALL conv_temp(climate_info.avg_temp);

SELECT conv_temp(avg_temp) FROM climate_info;

CALL convert_ftoc(climate_info.avg_temp);

SELECT convert_ftoc(avg_temp) FROM climate_info;

The correct answer is "SELECT conv_temp(avg_temp) FROM climate_info;". This is because the conv_temp function is being used to convert the average temperature (avg_temp) data stored in the CLIMATE_INFO table. The SELECT statement is used to retrieve the converted temperature values from the table.

Explanation

The correct answer is "SELECT conv_temp(avg_temp) FROM climate_info;". This is because the conv_temp function is being used to convert the average temperature (avg_temp) data stored in the CLIMATE_INFO table. The SELECT statement is used to retrieve the converted temperature values from the table.

42. A new user named USER1 needs to retrieve information from a database named MYDB. Whatauthority must be granted to user USER1?

DBCTRL authority

SQLADM authority

DATAACCESS authority

ACCESSCTRL authority

The correct answer is DATAACCESS authority. This authority allows the user to retrieve information from the database. It grants the necessary privileges to access and read data from the tables in the database.

Explanation

The correct answer is DATAACCESS authority. This authority allows the user to retrieve information from the database. It grants the necessary privileges to access and read data from the tables in the database.

43. When is an INTENT EXCLUSIVE (IX) lock required?

When a transaction intends to read or change data.

When a transaction intends to change but not read data.

When a transaction intends to read but not change data.

When a transaction intends to change the system catalog.

An INTENT EXCLUSIVE (IX) lock is required when a transaction intends to read or change data. This type of lock indicates that the transaction has the intention to modify the data, and it is used to prevent conflicts with other transactions. By acquiring an IX lock, the transaction signals its intention to modify the data, and other transactions can be aware of this intent and acquire appropriate locks to avoid conflicts. Therefore, an IX lock is necessary when a transaction intends to read or change data.

Explanation

An INTENT EXCLUSIVE (IX) lock is required when a transaction intends to read or change data. This type of lock indicates that the transaction has the intention to modify the data, and it is used to prevent conflicts with other transactions. By acquiring an IX lock, the transaction signals its intention to modify the data, and other transactions can be aware of this intent and acquire appropriate locks to avoid conflicts. Therefore, an IX lock is necessary when a transaction intends to read or change data.

44. Which statement about INSERT operations is true?

The INSERT statement is used to insert rows into a table, view, or table function.

Inserted values must satisfy the conditions of any check constraints defined on the table.

When performing an INSERT operation, the values being inserted must adhere to any check constraints that have been defined on the table. This means that the values must meet the specified conditions set by the constraints. If the values do not satisfy these conditions, the INSERT statement will fail. This ensures data integrity and prevents the insertion of invalid or inconsistent data into the table.

Explanation

When performing an INSERT operation, the values being inserted must adhere to any check constraints that have been defined on the table. This means that the values must meet the specified conditions set by the constraints. If the values do not satisfy these conditions, the INSERT statement will fail. This ensures data integrity and prevents the insertion of invalid or inconsistent data into the table.

45. Which statement is true about an index that is used to support a UNIQUE constraint?

It must not contain more than one column.

It cannot be used in a referential constraint.

It must be defined with the UNIQUE attribute.

It must be defined as UNIQUE WHERE NOT NULL

An index used to support a UNIQUE constraint must be defined with the UNIQUE attribute because this attribute ensures that the index enforces uniqueness on the column or columns it is defined on. Without the UNIQUE attribute, the index would not be able to enforce the constraint and duplicate values would be allowed in the column or columns.

Explanation

An index used to support a UNIQUE constraint must be defined with the UNIQUE attribute because this attribute ensures that the index enforces uniqueness on the column or columns it is defined on. Without the UNIQUE attribute, the index would not be able to enforce the constraint and duplicate values would be allowed in the column or columns.

46. When a user-defined function (UDF) is created and no schema is specified, what schema is usedto store the UDF?

The SYSIBM schema.

The SYSFUN schema.

A schema with the name of the UDF.

A schema with the userid of the UDF definer.

When a user-defined function (UDF) is created without specifying a schema, the UDF is stored in a schema that has the same name as the user ID of the UDF definer. This means that the UDF is associated with the user who created it and can be accessed within their schema. This allows for better organization and management of UDFs within the database system.

Explanation

When a user-defined function (UDF) is created without specifying a schema, the UDF is stored in a schema that has the same name as the user ID of the UDF definer. This means that the UDF is associated with the user who created it and can be accessed within their schema. This allows for better organization and management of UDFs within the database system.

47. What isolation level prevents dirty reads, nonrepeatable reads, and phantoms?

Uncommitted read (UR)

Read stability (RS)

Cursor stability (CS)

Repeatable read (RR)

The correct answer is "Repeatable read (RR)". This isolation level prevents dirty reads, nonrepeatable reads, and phantoms. Dirty reads occur when a transaction reads uncommitted data from another transaction. Nonrepeatable reads happen when a transaction reads the same data multiple times but gets different results due to other transactions modifying the data. Phantoms refer to new rows that appear in subsequent reads within a transaction due to other transactions inserting new data. Repeatable read ensures that once a transaction reads a row, it will see the same data throughout the transaction, preventing these issues.

Explanation

The correct answer is "Repeatable read (RR)". This isolation level prevents dirty reads, nonrepeatable reads, and phantoms. Dirty reads occur when a transaction reads uncommitted data from another transaction. Nonrepeatable reads happen when a transaction reads the same data multiple times but gets different results due to other transactions modifying the data. Phantoms refer to new rows that appear in subsequent reads within a transaction due to other transactions inserting new data. Repeatable read ensures that once a transaction reads a row, it will see the same data throughout the transaction, preventing these issues.

48. Which two operations are allowed in the body of an SQL scalar user-defined function?

CALL statements.

External file access.

Use of a scratch pad.

COMMIT statements.

SQL control statements.

In the body of an SQL scalar user-defined function, two operations that are allowed are CALL statements and SQL control statements. CALL statements are used to invoke other functions or procedures within the function's body. SQL control statements, such as IF, CASE, and LOOP, are used to control the flow of execution within the function. External file access and use of a scratch pad are not allowed in the body of an SQL scalar user-defined function. COMMIT statements are used to save changes to the database and are not typically used within a function.

Explanation

In the body of an SQL scalar user-defined function, two operations that are allowed are CALL statements and SQL control statements. CALL statements are used to invoke other functions or procedures within the function's body. SQL control statements, such as IF, CASE, and LOOP, are used to control the flow of execution within the function. External file access and use of a scratch pad are not allowed in the body of an SQL scalar user-defined function. COMMIT statements are used to save changes to the database and are not typically used within a function.

Submit

49. Which function can be used to obtain values from XML documents that are to be inserted into oneor more tables?

XMLTABLE

XMLPARSE

XMLEXISTS

XMLATTRIBUTES

The XMLTABLE function can be used to obtain values from XML documents that are to be inserted into one or more tables. This function allows for the extraction of data from XML documents and the transformation of that data into relational rows and columns, making it ideal for inserting XML data into database tables.

Explanation

The XMLTABLE function can be used to obtain values from XML documents that are to be inserted into one or more tables. This function allows for the extraction of data from XML documents and the transformation of that data into relational rows and columns, making it ideal for inserting XML data into database tables.

50. User USER1 wants to retrieve records from a table named EMPLOYEE that satisfy at least one ofthe following criteria:

The employee's hire date (HIREDATE) is before 1999 and the employee's salary (SALARY) is

less than $40,000.00 a year.

The employee has attended university

Which SQL statement will accomplish this?

SELECT * FROM employeeWHERE (hiredate < '1999-01-01' AND salary < 40000)OR (education = 'University')

SELECT * FROM employeeWHERE (hiredate < '1999-01-01') OR (salary < 40000)OR (education = 'University')

SELECT * FROM employeeWHERE (hiredate < '1999-01-01' OR (salary < 40000AND (education = 'University')

SELECT * FROM employeeWHERE (hiredate < '1999-01-01' AND salary < 40000AND (education = 'University')

The correct answer is the first option: SELECT * FROM employee
WHERE (hiredate OR (education = 'University')

This statement uses the OR operator to retrieve records that satisfy at least one of the given criteria. The first condition checks if the hire date is before 1999 and the salary is less than $40,000. The second condition checks if the employee has attended university. By using the OR operator, the statement will return records that meet either of these conditions.

Explanation

The correct answer is the first option: SELECT * FROM employee
WHERE (hiredate OR (education = 'University')

This statement uses the OR operator to retrieve records that satisfy at least one of the given criteria. The first condition checks if the hire date is before 1999 and the salary is less than $40,000. The second condition checks if the employee has attended university. By using the OR operator, the statement will return records that meet either of these conditions.

51. What is the primary function of an Online Transaction Processing (OLTP) workload?

To combine data from multiple sources.

To discover hidden relationships in data.

To analyze large amounts of data to find patterns.

To make changes to a small number of records within a single transaction.

The primary function of an Online Transaction Processing (OLTP) workload is to make changes to a small number of records within a single transaction. OLTP systems are designed for real-time transaction processing, where the focus is on quick and efficient processing of individual transactions. This involves tasks such as inserting, updating, and deleting records in a database. OLTP systems are optimized for high throughput and low latency, making them suitable for applications that require fast and reliable transaction processing, such as e-commerce, banking, and reservation systems.

Explanation

The primary function of an Online Transaction Processing (OLTP) workload is to make changes to a small number of records within a single transaction. OLTP systems are designed for real-time transaction processing, where the focus is on quick and efficient processing of individual transactions. This involves tasks such as inserting, updating, and deleting records in a database. OLTP systems are optimized for high throughput and low latency, making them suitable for applications that require fast and reliable transaction processing, such as e-commerce, banking, and reservation systems.

52. What is the purpose of a role?

To define exactly what a specific user can do within a particular database once they have beenauthenticated.

To group a collection of privileges together so that they can be simultaneously granted to andrevoked from multiple users.

To establish a credible relationship between DB2 and an external entity, such as a databaseadministrator or a middleware server.

To group a collection of users together so that they can be simultaneously granted and revokedspecific authorities and privileges.

The purpose of a role is to group a collection of privileges together so that they can be simultaneously granted to and revoked from multiple users. Roles allow for efficient management of user privileges by assigning a set of privileges to a role and then assigning that role to multiple users. This simplifies the process of granting and revoking privileges for a group of users, as it can be done by managing the role instead of individual user accounts.

Explanation

The purpose of a role is to group a collection of privileges together so that they can be simultaneously granted to and revoked from multiple users. Roles allow for efficient management of user privileges by assigning a set of privileges to a role and then assigning that role to multiple users. This simplifies the process of granting and revoking privileges for a group of users, as it can be done by managing the role instead of individual user accounts.

53. Which statement regarding triggers is true?

Only one INSERT, UPDATE, or DELETE trigger can be defined per table.

Triggers cannot be modified, they must be dropped and recreated with new definitions.

Adding a trigger to a table that already has rows in it will cause triggered actions to be fired.

Triggers can only be fired one time per statement irrespective of the number of rows affected.

not-available-via-ai

Explanation

not-available-via-ai

54. By default, a column defined as a TIMESTAMP includes which attributes?

No fractional digits or time zone.

6 fractional digits and no time zone.

12 fractional digits plus a time zone.

12 fractional digits and no time zone.

A column defined as a TIMESTAMP by default includes 6 fractional digits and does not include a time zone. This means that the column can store up to 6 digits of fractional seconds precision, allowing for more precise timestamp values. However, it does not store any information about the time zone, so it is assumed to be in the local time zone of the database.

Explanation

A column defined as a TIMESTAMP by default includes 6 fractional digits and does not include a time zone. This means that the column can store up to 6 digits of fractional seconds precision, allowing for more precise timestamp values. However, it does not store any information about the time zone, so it is assumed to be in the local time zone of the database.

55. Which is NOT a valid reason for creating an index?

To allow queries to run more efficiently.

To enforce constraints such as uniqueness on index keys.

To order the columns of a table in ascending or descending sequence according to values in arow.

To order the rows of a table in ascending or descending sequence according to the values in acolumn.

The statement "To order the columns of a table in ascending or descending sequence according to values in a row" is not a valid reason for creating an index. Indexes are used to improve query performance by allowing them to run more efficiently, enforce constraints such as uniqueness on index keys, and order the rows of a table in ascending or descending sequence according to the values in a column. However, they do not have any impact on the ordering of columns within a table.

Explanation

The statement "To order the columns of a table in ascending or descending sequence according to values in a row" is not a valid reason for creating an index. Indexes are used to improve query performance by allowing them to run more efficiently, enforce constraints such as uniqueness on index keys, and order the rows of a table in ascending or descending sequence according to the values in a column. However, they do not have any impact on the ordering of columns within a table.

56. Which two activities indicate the need for a data warehouse?

Confirm product inventory.

Monitor product availability.

Summarize sales by region.

Identify patterns for products sold in the last five years.

Associate one or more products with a purchase order.

The need for a data warehouse is indicated by activities that involve analyzing and summarizing large amounts of data over a specific period or for specific criteria. Summarizing sales by region requires aggregating sales data from multiple sources and organizing it by region. Identifying patterns for products sold in the last five years involves analyzing historical sales data to identify trends and patterns. Both activities require a data warehouse to store and process large volumes of data efficiently.

Explanation

The need for a data warehouse is indicated by activities that involve analyzing and summarizing large amounts of data over a specific period or for specific criteria. Summarizing sales by region requires aggregating sales data from multiple sources and organizing it by region. Identifying patterns for products sold in the last five years involves analyzing historical sales data to identify trends and patterns. Both activities require a data warehouse to store and process large volumes of data efficiently.

Submit

57. What is the act of exchanging one lock an application holds on a resource for a more restrictivelock on the same resource known as?

Lock escalation

Lock conversion/promotion

Lock switch/exchange

Lock substitution

Lock conversion/promotion refers to the act of exchanging one lock that an application holds on a resource for a more restrictive lock on the same resource. This means that the application is upgrading the lock to a higher level of restriction, potentially granting it more exclusive access to the resource. This can be useful in scenarios where the application initially acquires a lower level lock but later realizes that it needs a higher level of lock to perform certain operations on the resource.

Explanation

Lock conversion/promotion refers to the act of exchanging one lock that an application holds on a resource for a more restrictive lock on the same resource. This means that the application is upgrading the lock to a higher level of restriction, potentially granting it more exclusive access to the resource. This can be useful in scenarios where the application initially acquires a lower level lock but later realizes that it needs a higher level of lock to perform certain operations on the resource.

58. A table named EMPLOYEE was created as follows:

Which action will prevent unauthorized users from accessing SSN data?

Assign the SSN column to a restricted role that only authorized users can access.

Only grant ACCESSCTRL authority for the SSN column to users who need to access SSNdata.

Alter the table definition so that SSN data is stored in a separate schema that only authorizedusers can access.

Creating a view for the EMPLOYEE table that does not contain the SSN column and requiring unauthorized users to use the view will prevent them from accessing the SSN data. By excluding the SSN column from the view, unauthorized users will not be able to see or retrieve the sensitive SSN information. Instead, they will only have access to the columns included in the view, thus effectively restricting their access to the SSN data.

Explanation

Creating a view for the EMPLOYEE table that does not contain the SSN column and requiring unauthorized users to use the view will prevent them from accessing the SSN data. By excluding the SSN column from the view, unauthorized users will not be able to see or retrieve the sensitive SSN information. Instead, they will only have access to the columns included in the view, thus effectively restricting their access to the SSN data.

59. Which SQL statement will allow user USER1 to create a view on a table named EMPLOYEE?

GRANT CREATETAB ON DATABASE TO user1

GRANT SELECT ON TABLE employee TO user1

GRANT CREATEVIEW ON DATABASE TO user1

GRANT REFERENCES ON TABLE employee TO user1

The correct answer is "GRANT SELECT ON TABLE employee TO user1". This statement grants the user USER1 the permission to select data from the table named EMPLOYEE, which is a necessary permission for creating a view on that table. The other options do not grant the necessary permission for creating a view.

Explanation

The correct answer is "GRANT SELECT ON TABLE employee TO user1". This statement grants the user USER1 the permission to select data from the table named EMPLOYEE, which is a necessary permission for creating a view on that table. The other options do not grant the necessary permission for creating a view.

60. A table named STATES was created as follows:

What will happen?

An error will be returned and the operation will fail.

A warning will be returned and the unique index INDX1 will become the index for the primarykey.

When a table named STATES is created, if a unique index named INDX1 already exists, a warning will be returned instead of an error. The unique index INDX1 will then become the index for the primary key. This means that the existing unique index will be used as the primary key index instead of creating a new index specifically for the primary key.

Explanation

When a table named STATES is created, if a unique index named INDX1 already exists, a warning will be returned instead of an error. The unique index INDX1 will then become the index for the primary key. This means that the existing unique index will be used as the primary key index instead of creating a new index specifically for the primary key.

61. When is it appropriate to use a sequence?

When you want to control the order in which triggers are fired.

When you want to control the order in which stored procedures can be invoked.

When you want to automatically generate a numeric value that is not tied to any specific columnor table.

When you want to automatically generate a numeric value for each row that is added to aspecific table.

A sequence is appropriate to use when you want to automatically generate a numeric value that is not tied to any specific column or table. This means that the sequence can generate a unique value that can be used in various scenarios, such as generating primary keys for tables or generating unique identifiers for records. It provides a way to generate a unique number without being dependent on any specific column or table in the database schema.

Explanation

A sequence is appropriate to use when you want to automatically generate a numeric value that is not tied to any specific column or table. This means that the sequence can generate a unique value that can be used in various scenarios, such as generating primary keys for tables or generating unique identifiers for records. It provides a way to generate a unique number without being dependent on any specific column or table in the database schema.

62. Which two statements are true about foreign key constraints?(MORE THAN ONE OPTIONS)

The foreign key constraint columns must be supported by an index.

The foreign key constraint columns must be defined as NOT NULL.

The number and data types of foreign key constraint columns must match the parent key.

The columns of one foreign key constraint cannot be used in another foreign key constraint.

Foreign key constraints are used to enforce referential integrity in a database. The first statement is true because in order to efficiently enforce the constraint, the foreign key columns must be supported by an index. The second statement is false because the foreign key constraint columns can be defined as NULL, although they are typically defined as NOT NULL. The third statement is true because the number and data types of the foreign key constraint columns must match the parent key in order to establish a valid relationship between the tables. The fourth statement is false because the columns of one foreign key constraint can be used in another foreign key constraint. The fifth statement is true because to define a foreign key constraint, there must be an associated primary key or unique key in the same or different table to establish the relationship.

Explanation

Foreign key constraints are used to enforce referential integrity in a database. The first statement is true because in order to efficiently enforce the constraint, the foreign key columns must be supported by an index. The second statement is false because the foreign key constraint columns can be defined as NULL, although they are typically defined as NOT NULL. The third statement is true because the number and data types of the foreign key constraint columns must match the parent key in order to establish a valid relationship between the tables. The fourth statement is false because the columns of one foreign key constraint can be used in another foreign key constraint. The fifth statement is true because to define a foreign key constraint, there must be an associated primary key or unique key in the same or different table to establish the relationship.

Submit

63. Which statement about BEFORE triggers is FALSE?

A BEFORE trigger can be used to perform validation of input data.

A BEFORE trigger can be used to automatically generate values for newly inserted rows.

A BEFORE trigger is fired for each row in the set of affected rows before the trigger eventexecutes.

A BEFORE trigger is fired for each row in the set of affected rows instead of executing thetrigger event.

A BEFORE trigger is not fired for each row in the set of affected rows instead of executing the trigger event. This statement is false because a BEFORE trigger is actually fired for each row in the set of affected rows before the trigger event executes. It can be used to perform validation of input data and automatically generate values for newly inserted rows.

Explanation

A BEFORE trigger is not fired for each row in the set of affected rows instead of executing the trigger event. This statement is false because a BEFORE trigger is actually fired for each row in the set of affected rows before the trigger event executes. It can be used to perform validation of input data and automatically generate values for newly inserted rows.

64. Which statement is true regarding constraints?

A table can only have one unique key constraint.

A table can have multiple primary key constraints.

Informational constraints tell DB2 what rules the data conforms to, but the rules are notenforced.

Informational constraints in DB2 provide information to the database about the rules that the data should conform to, such as data types, lengths, and formats. However, these constraints are not enforced by the database itself. They are used for informational purposes only and do not restrict or validate the data in any way.

Explanation

Informational constraints in DB2 provide information to the database about the rules that the data should conform to, such as data types, lengths, and formats. However, these constraints are not enforced by the database itself. They are used for informational purposes only and do not restrict or validate the data in any way.

65. Which statement about application-period temporal tables is true?

Consists of explicitly-supplied timestamps and a separate associated history table.

Is based on explicitly-supplied timestamps that define the time periods during which data isvalid.

Is useful when one wants to keep both user-based period information and system-basedhistorical information.

Consists of a pair of columns with database-manager maintained values that indicate the periodwhen a row is current.

Application-period temporal tables are based on explicitly-supplied timestamps that define the time periods during which data is valid. This means that each row in the table has a start and end timestamp, indicating the period during which the data is considered valid. This allows for tracking changes and historical data in a systematic manner. The other statements are not true for application-period temporal tables.

Explanation

Application-period temporal tables are based on explicitly-supplied timestamps that define the time periods during which data is valid. This means that each row in the table has a start and end timestamp, indicating the period during which the data is considered valid. This allows for tracking changes and historical data in a systematic manner. The other statements are not true for application-period temporal tables.

66. A column mask that is to be used for row and column access control (RCAC) was created with theDISABLE option specified. What must be done if this mask is to be used to restrict access to datastored in a table named EMPLOYEE?

The column mask must be enabled; the EMPLOYEE table must be altered to activate columnaccess control.

The column mask must be enabled (column access control for the EMPLOYEE table will beactivated automatically).

The EMPLOYEE table must be altered to activate column access control (the column mask willbe enabled automatically).

To restrict access to data stored in the EMPLOYEE table using a column mask with the DISABLE option specified, the column mask must first be enabled. This can be done by altering the EMPLOYEE table to activate column access control. By enabling the column mask, the restrictions specified in the mask will be applied to the table, controlling access to the data. Therefore, the correct answer is that the column mask must be enabled and the EMPLOYEE table must be altered to activate column access control.

Explanation

To restrict access to data stored in the EMPLOYEE table using a column mask with the DISABLE option specified, the column mask must first be enabled. This can be done by altering the EMPLOYEE table to activate column access control. By enabling the column mask, the restrictions specified in the mask will be applied to the table, controlling access to the data. Therefore, the correct answer is that the column mask must be enabled and the EMPLOYEE table must be altered to activate column access control.

67. Which SQL statement will give user USER1 the ability to assign a comment to a sequence namedMYSEQ?

GRANT ALTER ON SEQUENCE myseq TO user1

GRANT USAGE ON SEQUENCE myseq TO user1

GRANT SELECT ON SEQUENCE myseq TO user1

GRANT COMMENT ON SEQUENCE myseq TO user1

The correct answer is "GRANT COMMENT ON SEQUENCE myseq TO user1". This statement grants the user USER1 the ability to assign a comment to the sequence named MYSEQ. The GRANT COMMENT statement specifically allows the user to add comments to the specified sequence, while the other options (GRANT ALTER, GRANT USAGE, GRANT SELECT) do not provide this specific ability.

Explanation

The correct answer is "GRANT COMMENT ON SEQUENCE myseq TO user1". This statement grants the user USER1 the ability to assign a comment to the sequence named MYSEQ. The GRANT COMMENT statement specifically allows the user to add comments to the specified sequence, while the other options (GRANT ALTER, GRANT USAGE, GRANT SELECT) do not provide this specific ability.

68. What are two valid objects that can be created in DB2?

Tree

Node

Trigger

Contexts

Sequence

Table

In DB2, a trigger is a valid object that can be created. A trigger is a set of actions that are automatically performed when a specified event occurs in a database. It is commonly used to enforce business rules, perform data validation, or maintain data integrity.

Another valid object that can be created in DB2 is a sequence. A sequence is a database object that generates a sequence of unique values, typically used to generate primary key values for a table. It provides an easy and efficient way to generate unique identifiers for records in a table.

Finally, a table is also a valid object that can be created in DB2. A table is a basic unit of data storage in a relational database, where data is organized in rows and columns. It is used to store and organize data in a structured manner, allowing for efficient data retrieval and manipulation.

Explanation

In DB2, a trigger is a valid object that can be created. A trigger is a set of actions that are automatically performed when a specified event occurs in a database. It is commonly used to enforce business rules, perform data validation, or maintain data integrity.

Another valid object that can be created in DB2 is a sequence. A sequence is a database object that generates a sequence of unique values, typically used to generate primary key values for a table. It provides an easy and efficient way to generate unique identifiers for records in a table.

Finally, a table is also a valid object that can be created in DB2. A table is a basic unit of data storage in a relational database, where data is organized in rows and columns. It is used to store and organize data in a structured manner, allowing for efficient data retrieval and manipulation.

Submit

Db2 Practice 1