Db2 Practice 1

Reviewed by Editorial Team
The ProProfs editorial team is comprised of experienced subject matter experts. They've collectively created over 10,000 quizzes and lessons, serving over 100 million users. Our team includes in-house content moderators and subject matter experts, as well as a global network of rigorously trained contributors. All adhere to our comprehensive editorial guidelines, ensuring the delivery of high-quality content.
Learn about Our Editorial Process
| By Arafatkazi
A
Arafatkazi
Community Contributor
Quizzes Created: 3 | Total Attempts: 898
| Attempts: 412
SettingsSettings
Please wait...
  • 1/68 Questions

    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
Please wait...
About This Quiz

DB2 Practice 1 quiz assesses knowledge on DB2 database management, specifically focusing on database creation, performance optimization, and schema management. It is designed for learners aiming to enhance their DB2 skills, covering key aspects like sequence usage, table alteration, and temporal table definitions.

Db2 Practice 1 - Quiz

Quiz Preview

  • 2. 

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

    • TRIM

    • DROP

    • DELETE

    • TRUNCATE

    Correct Answer
    A. TRUNCATE
    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.

    Rate this question:

  • 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, salary FROM employee FOR hiredate < '1998-01-01' AND salary < 35000

    • SELECT empno, hiredate, salary FROM employee WHERE hiredate < '1998-01-01' AND salary < 35000

    • SELECT empno, hiredate, salary FROM employee WHERE hiredate < '1998-01-01' OR salary < 35000

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

    Correct Answer
    A. SELECT empno, hiredate, salary FROM employee WHERE hiredate < '1998-01-01' AND salary < 35000
    Explanation
    The correct answer is the second option:
    SELECT empno, hiredate, salary
    FROM employee
    WHERE hiredate < '1998-01-01' AND salary < 35000

    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.

    Rate this question:

  • 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 against multiple tables.

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

    Correct Answer
    A. A trigger performs actions in response to an event like an INSERT, UPDATE or DELETE operation 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.

    Rate this question:

  • 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

    Correct Answer
    A. IBM Data Studio
    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.

    Rate this question:

  • 6. 

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

    • 4

    • 8

    • 16

    • 32

    Correct Answer
    A. 4
    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.

    Rate this question:

  • 7. 

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

    • Alias

    • Index

    • Schema

    • Collection

    Correct Answer
    A. Schema
    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.

    Rate this question:

  • 8. 

    Which operation normally does NOT require an exclusive lock?

    • SELECT

    • GRANT

    • DROP

    • BIND

    Correct Answer
    A. SELECT
    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.

    Rate this question:

  • 9. 

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

    • Primary key

    • Partitioning key

    • Check constraint

    • Unique constraint

    Correct Answer
    A. Primary key
    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.

    Rate this question:

  • 10. 

    What factor influences lock escalation?

    • Table size

    • Buffer space

    • Number of locks

    • Available real storage

    Correct Answer
    A. Number of locks
    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.

    Rate this question:

  • 11. 

    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.

    Correct Answer
    A. To provide recommendations and analysis for tuning a single query.
    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.

    Rate this question:

  • 12. 

    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.

    Correct Answer
    A. The data in the table is deleted and the table is implicitly dropped.
    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.

    Rate this question:

  • 13. 

    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.

    Correct Answer
    A. Change the data type of the sequence.
    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.

    Rate this question:

  • 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.

    Correct Answer
    A. To combine data from multiple 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.

    Rate this question:

  • 15. 

    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 employee GROUP BY job;

    • SELECT job, MIN(salary), MAX(salary) FROM employee ORDER BY job;

    • SELECT job, MIN(salary), MAX(salary) FROM employee GROUP BY job, MIN(salary), MAX(salary);

    • SELECT JOB, MIN(salary), MAX(salary) FROM employee ORDER BY job, MIN(salary), MAX(salary);

    Correct Answer
    A. SELECT job, MIN(salary), MAX(salary) FROM employee GROUP BY job;
    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.

    Rate this question:

  • 16. 

    Which object is a stored procedure defined into?

    • Table

    • Schema

    • Package

    • Database

    Correct Answer
    A. Schema
    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.

    Rate this question:

  • 17. 

    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 of the table.

    Correct Answer
    A. A BUSINESS_TIME period must be specified in a CREATE or ALTER of the table.
    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.

    Rate this question:

  • 18. 

    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.

    Correct Answer
    A. In the system catalog.
    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.

    Rate this question:

  • 19. 

    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

    Correct Answer
    A. ROLLBACK TO SAVEPOINT
    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.

    Rate this question:

  • 20. 

    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

    Correct Answer
    A. Create and drop databases
    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.

    Rate this question:

  • 21. 

    Which privilege is required to invoke a user defined function?

    • CALL

    • USAGE

    • EXECUTE

    • REFERENCES

    Correct Answer
    A. EXECUTE
    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.

    Rate this question:

  • 22. 

    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

    Correct Answer
    A. JDBC
    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.

    Rate this question:

  • 23. 

    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)

    Correct Answer
    A. Repeatable Read (RR)
    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.

    Rate this question:

  • 24. 

    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

    Correct Answer
    A. Unconnected
    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.

    Rate this question:

  • 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

    Correct Answer
    A. SECADM
    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.

    Rate this question:

  • 26. 

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

    • Bitemporal

    • Time-period

    • System-period

    • Application-period

    Correct Answer
    A. Application-period
    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.

    Rate this question:

  • 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

    Correct Answer
    A. Workload manager
    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.

    Rate this question:

  • 28. 

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

    • Materialized Query Table

    • Sequence Object

    • SQL Procedure

    • Trigger

    Correct Answer
    A. Materialized Query Table
    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.

    Rate this question:

  • 29. 

    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

    Correct Answer
    A. Locator
    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.

    Rate this question:

  • 30. 

    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.

    Correct Answer
    A. The current transaction is terminated and a new transaction boundary is started.
    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.

    Rate this question:

  • 31. 

    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)

    Correct Answer
    A. Share (S)
    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.

    Rate this question:

  • 32. 

    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)

    Correct Answer
    A. Uncommitted Read (UR)
    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.

    Rate this question:

  • 33. 

    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.revenue FROM employees INNER JOIN sales ON employees.id = sales.emp_id

    • SELECT employees.name, sales.revenue FROM employees INNER JOIN sales ON sales.emp_id = employees.id

    • SELECT employees.name, sales.revenue FROM sales LEFT OUTER JOIN employees ON employees.id = sales.emp_id

    • SELECT employees.name, sales.revenue FROM sales RIGHT OUTER JOIN employees ON employees.id = sales.emp_id

    Correct Answer
    A. SELECT employees.name, sales.revenue FROM sales RIGHT OUTER JOIN employees ON employees.id = sales.emp_id
    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.

    Rate this question:

  • 34. 

    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

    Correct Answer
    A. Unique constraint
    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.

    Rate this question:

  • 35. 

    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.

    Correct Answer
    A. In an XML storage object that is separate from the table.
    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.

    Rate this question:

  • 36. 
    If a table named MY_TAB contains 100 rows and the following statement is executed:What will happen? - ProProfs

    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.

    Correct Answer
    A. The last 5 rows in the table will be deleted.
    Explanation
    The given statement will delete the last 5 rows in the table named MY_TAB.

    Rate this question:

  • 37. 

    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

    Correct Answer
    A. Inline
    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.

    Rate this question:

  • 38. 

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

    • Oracle connect

    • Trusted context

    • Oracle federation

    • Distributed request

    Correct Answer
    A. Distributed request
    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.

    Rate this question:

  • 39. 

    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

    Correct Answer
    A. LOCK TABLE tab1 IN SHARE MODE
    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.

    Rate this question:

  • 40. 
    If the following result set is desired:Which SQL statement must be executed? - ProProfs

    If the following result set is desired:Which SQL statement must be executed?

    • SELECT lastname, firstnme, salary, job FROM employee ORDER BY 3 FETCH FIRST 5 ROWS ONLY

    • SELECT lastname, firstnme, salary, job FROM employee ORDER BY 3 DESC FETCH FIRST 5 ROWS ONLY

    • SELECT lastname, firstnme, salary, job FROM employee ORDER BY 3 FETCH FIRST 5 ROWS

    • SELECT lastname, firstnme, salary, job FROM employee ORDER BY 3 DESC FETCH FIRST 5 ROWS

    Correct Answer
    A. SELECT lastname, firstnme, salary, job FROM employee ORDER BY 3 DESC FETCH FIRST 5 ROWS ONLY
    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.

    Rate this question:

  • 41. 

    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.

    Correct Answer
    A. 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.

    Rate this question:

  • 42. 

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

    • Uncommitted read (UR)

    • Read stability (RS)

    • Cursor stability (CS)

    • Repeatable read (RR)

    Correct Answer
    A. Repeatable read (RR)
    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.

    Rate this question:

  • 43. 

    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;

    Correct Answer
    A. SELECT conv_temp(avg_temp) FROM climate_info;
    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.

    Rate this question:

  • 44. 

    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

    Correct Answer
    A. DATAACCESS authority
    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.

    Rate this question:

  • 45. 

    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.

    Correct Answer
    A. A schema with the userid of the UDF definer.
    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.

    Rate this question:

  • 46. 

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

    • XMLTABLE

    • XMLPARSE

    • XMLEXISTS

    • XMLATTRIBUTES

    Correct Answer
    A. XMLTABLE
    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.

    Rate this question:

  • 47. 

    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.

    • If an INSERT statement omits any column from the inserted row that is defined as NULL or NOT NULL WITH DEFAULT, the statement will fail.

    • If the underlying table of a view being referenced by an INSERT statement has one or more unique indexes, each row inserted does not have to conform to the constraints imposed by those indexes.

    Correct Answer
    A. Inserted values must satisfy the conditions of any check constraints defined on 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.

    Rate this question:

  • 48. 

    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

    Correct Answer
    A. It must be defined with the UNIQUE attribute.
    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.

    Rate this question:

  • 49. 

    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

    Correct Answer
    A. Lock conversion/promotion
    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.

    Rate this question:

Quiz Review Timeline (Updated): Mar 21, 2023 +

Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.

  • Current Version
  • Mar 21, 2023
    Quiz Edited by
    ProProfs Editorial Team
  • Sep 17, 2016
    Quiz Created by
    Arafatkazi

Recent Quizzes

Intelligent IBM DB2 Database Assessment Test Intelligent IBM DB2 Database Assessment Test
DB2 Basics, Part 1 DB2 Basics, Part 1
DB2 Basics, Part 3 DB2 Basics, Part 3
db2 mock test 3 db2 mock test 3
db2 mock test Quiz db2 mock test Quiz
IBM DB2 Database Quiz IBM DB2 Database Quiz
Back to Top Back to top
Advertisement
×

Wait!
Here's an interesting quiz for you.

We have other quizzes matching your interest.