Db2 Practice 1

Approved & Edited by ProProfs Editorial Team
The editorial team at ProProfs Quizzes consists of a select group of subject experts, trivia writers, and quiz masters who have authored over 10,000 quizzes taken by more than 100 million users. This team includes our in-house seasoned quiz moderators and subject matter experts. Our editorial experts, spread across the world, are rigorously trained using our comprehensive guidelines to ensure that you receive the highest quality quizzes.
Learn about Our Editorial Process
| By Arafatkazi
A
Arafatkazi
Community Contributor
Quizzes Created: 3 | Total Attempts: 830
Questions: 68 | Attempts: 375

SettingsSettingsSettings
Db2 Practice 1 - Quiz

.


Questions and Answers
  • 1. 

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

    • A.

      4

    • B.

      8

    • C.

      16

    • D.

      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:

  • 2. 

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

    • A.

      Materialized Query Table

    • B.

      Sequence Object

    • C.

      SQL Procedure

    • D.

      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:

  • 3. 

    When is it appropriate to use a sequence?

    • A.

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

    • B.

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

    • C.

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

    • D.

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

    Correct Answer
    C. When you want to automatically generate a numeric value that is not tied to any specific column or table.
    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.

    Rate this question:

  • 4. 

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

    • A.

      ADD COLUMN

    • B.

      ALTER COLUMN

    • C.

      APPEND COLUMN

    • D.

      RENAME COLUMN

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

    Rate this question:

  • 5. 

    Which object is a stored procedure defined into?

    • A.

      Table

    • B.

      Schema

    • C.

      Package

    • D.

      Database

    Correct Answer
    B. 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:

  • 6. 

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

    • A.

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

    • B.

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

    • C.

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

    • D.

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

    Correct Answer
    B. 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:

  • 7. 

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

    • A.

      Oracle connect

    • B.

      Trusted context

    • C.

      Oracle federation

    • D.

      Distributed request

    Correct Answer
    D. 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:

  • 8. 

    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?

    • A.

      CLI

    • B.

      JDBC

    • C.

      XML

    • D.

      RUBY

    Correct Answer
    B. 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:

  • 9. 

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

    • A.

      Alias

    • B.

      Index

    • C.

      Schema

    • D.

      Collection

    Correct Answer
    C. 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:

  • 10. 

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

    • A.

      Tree

    • B.

      Node

    • C.

      Trigger

    • D.

      Contexts

    • E.

      Sequence

    • F.

      Table

    Correct Answer(s)
    C. Trigger
    E. Sequence
    F. Table
    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.

    Rate this question:

  • 11. 

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

    • A.

      Bitemporal

    • B.

      Time-period

    • C.

      System-period

    • D.

      Application-period

    Correct Answer
    D. 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:

  • 12. 

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

    • A.

      In the table definition.

    • B.

      In the system catalog.

    • C.

      In the schema that the index resides in.

    • D.

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

    Correct Answer
    B. 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:

  • 13. 

    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?

    • A.

      PureScale

    • B.

      Data partitioning feature

    • C.

      Workload manager

    • D.

      Self-tuning memory manager

    Correct Answer
    C. 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:

  • 14. 

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

    • A.

      Reference

    • B.

      Address

    • C.

      Pointer

    • D.

      Locator

    Correct Answer
    D. 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:

  • 15. 

    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?

    • A.

      Inline

    • B.

      Binary

    • C.

      Internal

    • D.

      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:

  • 16. 

    What is the purpose of the Query Tuner?

    • A.

      To automatically capture and stop rogue queries.

    • B.

      To provide recommendations and analysis for tuning a single query.

    • C.

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

    • D.

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

    Correct Answer
    B. 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:

  • 17. 

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

    • A.

      Control Center

    • B.

      Universal Connection Expert

    • C.

      DB2 Connection Pool Manager

    • D.

      IBM Data Studio

    Correct Answer
    D. 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:

  • 18. 

    Which two activities indicate the need for a data warehouse?

    • A.

      Confirm product inventory.

    • B.

      Monitor product availability.

    • C.

      Summarize sales by region.

    • D.

      Identify patterns for products sold in the last five years.

    • E.

      Associate one or more products with a purchase order.

    Correct Answer(s)
    C. Summarize sales by region.
    D. Identify patterns for products sold in the last five years.
    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.

    Rate this question:

  • 19. 

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

    • A.

      To combine data from multiple sources.

    • B.

      To discover hidden relationships in data.

    • C.

      To analyze large amounts of data to find patterns.

    • D.

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

    Correct Answer
    D. To make changes to a small number of records within a single transaction.
    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.

    Rate this question:

  • 20. 

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

    • A.

      Read Stability (RS)

    • B.

      Cursor Stability (CS)

    • C.

      Repeatable Read (RR)

    • D.

      Uncommitted Read (UR)

    Correct Answer
    C. 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:

  • 21. 

    What factor influences lock escalation?

    • A.

      Table size

    • B.

      Buffer space

    • C.

      Number of locks

    • D.

      Available real storage

    Correct Answer
    C. 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:

  • 22. 

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

    • A.

      Share (S)

    • B.

      Update (U)

    • C.

      Exclusive (X)

    • D.

      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:

  • 23. 

    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?

    • A.

      Read Stability (RS)

    • B.

      Repeatable Read (RR)

    • C.

      Uncommitted Read (UR)

    • D.

      Cursor Stability (CS)

    Correct Answer
    C. 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:

  • 24. 

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

    • A.

      LOCK TABLE tab1 IN READ MODE

    • B.

      LOCK TABLE tab1 IN SHARE MODE

    • C.

      LOCK TABLE tab1 IN EXCLUSIVE MODE

    • D.

      LOCK TABLE tab1 IN READ-ONLY MODE

    Correct Answer
    B. 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:

  • 25. 

    When is an INTENT EXCLUSIVE (IX) lock required?

    • A.

      When a transaction intends to read or change data.

    • B.

      When a transaction intends to change but not read data.

    • C.

      When a transaction intends to read but not change data.

    • D.

      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:

  • 26. 

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

    • A.

      Lock escalation

    • B.

      Lock conversion/promotion

    • C.

      Lock switch/exchange

    • D.

      Lock substitution

    Correct Answer
    B. 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:

  • 27. 

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

    • A.

      Uncommitted read (UR)

    • B.

      Read stability (RS)

    • C.

      Cursor stability (CS)

    • D.

      Repeatable read (RR)

    Correct Answer
    D. 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:

  • 28. 

    Which operation normally does NOT require an exclusive lock?

    • A.

      SELECT

    • B.

      GRANT

    • C.

      DROP

    • D.

      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:

  • 29. 

    When a COMMIT statement is executed, what happens?

    • A.

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

    • B.

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

    • C.

      Data stored in global temporary tables is automatically deleted.

    • D.

      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:

  • 30. 

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

    • A.

      ROLLBACK TO SAVEPOINT

    • B.

      COMMIT TO SAVEPOINT

    • C.

      ROLLBACK

    • D.

      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:

  • 31. 

    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? 

    • A.

      CALL conv_temp(climate_info.avg_temp);

    • B.

      SELECT conv_temp(avg_temp) FROM climate_info;

    • C.

      CALL convert_ftoc(climate_info.avg_temp);

    • D.

      SELECT convert_ftoc(avg_temp) FROM climate_info;

    Correct Answer
    B. 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:

  • 32. 

    If a table named MY_TAB contains 100 rows and the following statement is executed:What will happen?

    • A.

      The last 5 rows in the table will be deleted.

    • B.

      The first 5 rows in the table will be deleted.

    • C.

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

    • D.

      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:

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

    • A.

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

    • B.

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

    • C.

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

    • D.

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

    Correct Answer
    D. 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 two operations are allowed in the body of an SQL scalar user-defined function?

    • A.

      CALL statements.

    • B.

      External file access.

    • C.

      Use of a scratch pad.

    • D.

      COMMIT statements.

    • E.

      SQL control statements.

    Correct Answer(s)
    A. CALL statements.
    E. SQL control statements.
    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.

    Rate this question:

  • 35. 

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

    • A.

      TRIM

    • B.

      DROP

    • C.

      DELETE

    • D.

      TRUNCATE

    Correct Answer
    D. 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:

  • 36. 

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

    • A.

      XMLTABLE

    • B.

      XMLPARSE

    • C.

      XMLEXISTS

    • D.

      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:

  • 37. 

    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?

    • A.

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

    • B.

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

    • C.

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

    • D.

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

    Correct Answer
    A. SELECT * FROM employee WHERE (hiredate < '1999-01-01' AND salary < 40000) OR (education = 'University')
    Explanation
    The correct answer is the first option: SELECT * FROM employee
    WHERE (hiredate < '1999-01-01' AND salary < 40000)
    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.

    Rate this question:

  • 38. 

    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?

    • A.

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

    • B.

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

    • C.

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

    • D.

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

    Correct Answer
    B. 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:

  • 39. 

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

    • A.

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

    • B.

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

    • C.

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

    • D.

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

    Correct Answer
    B. 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:

  • 40. 

    Which statement about INSERT operations is true?

    • A.

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

    • B.

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

    • C.

      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.

    • D.

      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
    B. 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:

  • 41. 

    Which statement about application-period temporal tables is true?

    • A.

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

    • B.

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

    • C.

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

    • D.

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

    Correct Answer
    B. Is based on explicitly-supplied timestamps that define the time periods during which data is valid.
    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.

    Rate this question:

  • 42. 

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

    • A.

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

    • B.

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

    • C.

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

    • D.

      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:

  • 43. 

    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?

    • A.

      Locked

    • B.

      Waiting

    • C.

      Pending

    • D.

      Unconnected

    Correct Answer
    D. 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:

  • 44. 

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

    • A.

      Load operations

    • B.

      Backup/copy table spaces

    • C.

      Create and drop databases

    • D.

      Monitor database performance

    Correct Answer
    C. 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:

  • 45. 

    What is the purpose of a role?

    • A.

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

    • B.

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

    • C.

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

    • D.

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

    Correct Answer
    B. To group a collection of privileges together so that they can be simultaneously granted to and revoked from multiple users.
    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.

    Rate this question:

  • 46. 

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

    • A.

      GRANT ALTER ON SEQUENCE myseq TO user1

    • B.

      GRANT USAGE ON SEQUENCE myseq TO user1

    • C.

      GRANT SELECT ON SEQUENCE myseq TO user1

    • D.

      GRANT COMMENT ON SEQUENCE myseq TO user1

    Correct Answer
    A. GRANT ALTER ON SEQUENCE myseq TO user1
    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.

    Rate this question:

  • 47. 

    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?

    • A.

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

    • B.

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

    • C.

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

    • D.

      The EMPLOYEE table must be altered to activate column access control; row permission must be granted to everyone who needs to use thecolumn mask.

    Correct Answer
    A. The column mask must be enabled; 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.

    Rate this question:

  • 48. 

    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?

    • A.

      DBADM

    • B.

      DBCTRL

    • C.

      SECADM

    • D.

      DBMAINT

    Correct Answer
    C. 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:

  • 49. 

    A table named EMPLOYEE was created as follows:Which action will prevent unauthorized users from accessing SSN data?

    • A.

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

    • B.

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

    • C.

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

    • D.

      Create a view for the EMPLOYEE table that does not contain the SSN column and require unauthorized users to use the view.

    Correct Answer
    D. Create a view for the EMPLOYEE table that does not contain the SSN column and require unauthorized users to use the view.
    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.

    Rate this question:

  • 50. 

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

    • A.

      GRANT CREATETAB ON DATABASE TO user1

    • B.

      GRANT SELECT ON TABLE employee TO user1

    • C.

      GRANT CREATEVIEW ON DATABASE TO user1

    • D.

      GRANT REFERENCES ON TABLE employee TO user1

    Correct Answer
    B. GRANT SELECT ON TABLE employee TO user1
    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.

    Rate this question:

Quiz Review Timeline +

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

Related Topics

Back to Top Back to top
Advertisement
×

Wait!
Here's an interesting quiz for you.

We have other quizzes matching your interest.