Data Manipulation Language (DML): Advanced Techniques for Data Management

Question 1

What is the main reason for using SAVEPOINTs in transaction management?

Accepted Answer

To mark a specific point in a transaction that can be returned to later

Answer

To reverse the entire transaction

Answer

To establish transaction boundaries

Answer

To run DML statements outside of a transaction

Question 2

In database management, what is the purpose of the COMMIT statement? How does it differ from the ROLLBACK statement?

Accepted Answer

COMMIT makes changes within a transaction permanent, while ROLLBACK cancels the transaction and restores the database to its previous state.

Answer

COMMIT initiates a transaction, while ROLLBACK terminates it.

Answer

Both COMMIT and ROLLBACK make changes permanent in the database.

Answer

COMMIT and ROLLBACK have no impact on data manipulation.

Question 3

Which Data Manipulation Language (DML) statement is used to insert new data into a table?

Accepted Answer

INSERT

Answer

UPDATE

Answer

DELETE

Answer

MERGE

Question 4

Which SQL clause specifies the columns to be updated in an UPDATE statement?

Accepted Answer

SET

Answer

WHERE

Answer

ORDER BY

Answer

GROUP BY

Question 5

What is the primary purpose of a transaction in a database management system?

Accepted Answer

To ensure data integrity and consistency

Answer

To improve query performance

Answer

To allow for concurrent access by multiple users

Answer

To recover data in case of a system failure

Question 6

Which DML statement permanently saves changes made within a transaction?

Accepted Answer

COMMIT

Answer

ROLLBACK

Answer

SAVEPOINT

Answer

RELEASE

Question 7

Which of the following is NOT a benefit of using the MERGE statement?

Accepted Answer

Increased data security

Answer

Improved performance

Answer

Simplified syntax

Answer

Reduced code duplication

Question 8

Which SQL clause can be used to specify conditions for deleting rows in a DELETE statement?

Accepted Answer

WHERE

Answer

HAVING

Answer

ORDER BY

Answer

GROUP BY

Question 9

What is the key difference between a SAVEPOINT and a COMMIT?

Accepted Answer

A SAVEPOINT marks a point in a transaction that can be rolled back to, while a COMMIT permanently saves changes.

Answer

Question 10

What is the primary purpose of using constraints in a database?

Accepted Answer

To ensure data integrity and consistency

Answer

To improve query performance

Answer

To allow for concurrent access by multiple users

Answer

To recover data in case of a system failure

Question 11

What's the primary goal of transaction management in DML?

Accepted Answer

Maintain data accuracy

Answer

Increase database speed

Answer

Enhance data security

Answer

Restore lost data

Question 12

What is a primary advantage of using the MERGE statement in DML?

Accepted Answer

It combines INSERT and UPDATE operations efficiently in one statement.

Answer

It removes duplicate records from a table.

Answer

It performs complex data joins across multiple tables.

Answer

It automatically enforces referential integrity constraints.

Question 13

Which DML statement makes changes to a database table permanent?

Accepted Answer

COMMIT

Answer

INSERT

Answer

UPDATE

Answer

SAVEPOINT

Question 14

Which of these DML statements is used to add a new record to a table?

Accepted Answer

INSERT

Answer

SELECT

Answer

UPDATE

Answer

DELETE

Question 15

Which SQL statement is used to fetch data from a database table?

Accepted Answer

SELECT

Answer

INSERT

Answer

UPDATE

Question 16

Which of the following is NOT a Data Manipulation Language (DML) statement in SQL?

Accepted Answer

SELECT

Answer

INSERT

Answer

UPDATE

Answer

DELETE

Question 17

Which DML statement makes changes to a database table permanent?

Accepted Answer

COMMIT

Answer

INSERT

Answer

UPDATE

Answer

ROLLBACK

Question 18

Which DML statement is used to add a new record to a table?

Accepted Answer

INSERT INTO

Answer

MERGE

Answer

DELETE

Answer

UPDATE

Question 19

What is the purpose of using COMMIT in DML?

Accepted Answer

To make changes within a transaction permanent

Answer

To start a new transaction

Answer

To undo changes within a transaction

Question 20

Which is NOT a valid constraint type in DML?

Accepted Answer

Uniqueidentifier

Answer

Foreign Key

Answer

Primary Key

Answer

Check

Question 21

What is the key difference between UPDATE and MERGE statements?

Accepted Answer

MERGE can perform both INSERT and UPDATE operations, while UPDATE can only modify existing records

Answer

MERGE can only perform INSERT operations, while UPDATE can only modify existing records

Question 22

What is the purpose of using SAVEPOINT in DML?

Accepted Answer

To create a temporary point within a transaction that can be rolled back to if needed

Answer

To start a new transaction

Answer

To make changes within a transaction permanent

Question 23

What is the purpose of ROLLBACK in DML?

Accepted Answer

To undo all changes made within a transaction

Answer

To make changes within a transaction permanent

Answer

To start a new transaction

Answer

To create a savepoint within a transaction

Question 24

What is the advantage of using subqueries in DML statements?

Accepted Answer

To retrieve data from multiple tables or perform complex calculations within a single statement

Answer

To improve the performance of DML statements

Answer

To make DML statements more readable