MCQs on Data Modification | SQL Server

Dive into SQL Server’s Data Modification techniques with 30 tailored MCQs on INSERT, UPDATE, DELETE statements, handling NULL values, bulk insert operations, and the basics of transactions for mastery.

---

INSERT, UPDATE, DELETE Statements

1. What is the result of the following statement?sqlCopy codeINSERT INTO Employees (Name, Age) VALUES ('John Doe', 30); a) Adds a new row with Name “John Doe” and Age 30
   b) Updates the row with Name “John Doe”
   c) Deletes the row with Name “John Doe”
   d) Throws an error as the table is empty
2. Which clause ensures only specific rows are updated in a table?
   a) WHERE
   b) HAVING
   c) GROUP BY
   d) SELECT
3. What happens when you execute the following statement? DELETE FROM Orders; a) Deletes all rows in the Orders table
   b) Deletes the Orders table
   c) Deletes rows where the condition is true
   d) Throws an error
4. Which keyword is used to modify existing records in a table?
   a) INSERT
   b) UPDATE
   c) DELETE
   d) SELECT
5. What is mandatory when using the DELETE statement to avoid deleting all rows unintentionally?
   a) WHERE clause
   b) ORDER BY clause
   c) JOIN clause
   d) LIMIT clause

---

Understanding NULL Values

6. What is the result of the following query if the “Address” field is NULL? SELECT Name FROM Customers WHERE Address = NULL; a) Returns all rows
   b) Returns no rows
   c) Returns rows with NULL in the Address field
   d) Throws an error
7. Which operator is used to check for NULL values in SQL Server?
   a) IS NULL
   b) = NULL
   c) IN NULL
   d) NULL CHECK
8. What will the following query return? SELECT ISNULL(NULL, 'No Address'); a) NULL
   b) ‘No Address’
   c) Throws an error
   d) An empty string
9. If a column contains NULL values, what is the result of SUM(column_name)?
   a) NULL
   b) Ignores NULL values and sums the rest
   c) Throws an error
   d) Returns 0
10. What happens when NULL values are compared using the equality operator (=)?
    a) Returns TRUE
    b) Returns FALSE
    c) Returns UNKNOWN
    d) Throws an error

---

Bulk Insert Operations

11. Which statement is used to perform bulk data insertion in SQL Server?
    a) BULK INSERT
    b) MASS INSERT
    c) BULK LOAD
    d) INSERT BULK
12. What does the “FIELDTERMINATOR” argument specify in the BULK INSERT statement?
    a) The character separating data fields in the file
    b) The line delimiter in the file
    c) The file path
    d) The maximum file size
13. What happens if the file path provided in a BULK INSERT statement is invalid?
    a) No rows are inserted, and an error is thrown
    b) Partial rows are inserted
    c) Only the valid rows are inserted
    d) The file is created automatically
14. Which option enables you to log errors during a BULK INSERT operation?
    a) ERRORFILE
    b) LOGFILE
    c) ERRORS LOG
    d) DEBUG LOG
15. Which file format is NOT supported by the BULK INSERT statement in SQL Server?
    a) CSV
    b) TXT
    c) XML
    d) DAT

---

Introduction to Transactions

16. What is the purpose of a transaction in SQL Server?
    a) To execute all operations as a single unit of work
    b) To automatically back up the database
    c) To create a new database user
    d) To optimize query performance
17. Which command is used to explicitly begin a transaction?
    a) START TRANSACTION
    b) BEGIN TRANSACTION
    c) TRANSACTION START
    d) BEGIN WORK
18. What happens if a transaction is not committed or rolled back?
    a) The changes remain in a pending state
    b) The changes are automatically committed
    c) The transaction is automatically rolled back
    d) Throws an error
19. What is the role of the COMMIT statement in a transaction?
    a) It permanently saves the changes made during the transaction
    b) It cancels all changes made during the transaction
    c) It starts a new transaction
    d) It temporarily saves the changes
20. When does a transaction rollback automatically?
    a) When an error occurs
    b) When the COMMIT statement is executed
    c) When the SAVEPOINT is reached
    d) When the transaction is idle for 5 minutes

---

Mixed Topics

21. What happens when you execute an UPDATE statement without a WHERE clause?
    a) Updates all rows in the table
    b) Updates only the first row
    c) Throws an error
    d) Updates no rows
22. Which keyword ensures atomicity when combining multiple SQL statements?
    a) TRANSACTION
    b) TRIGGER
    c) FUNCTION
    d) PROCEDURE
23. What will be the output of the following statement? INSERT INTO Employees DEFAULT VALUES; a) Inserts a row with default values
    b) Inserts a row with NULL values
    c) Throws an error
    d) Inserts a row with zeroes
24. Which clause is used to remove duplicate rows in a SELECT query?
    a) DISTINCT
    b) UNIQUE
    c) FILTER
    d) REMOVE
25. What is the result of the following statement? BEGIN TRANSACTION UPDATE Products SET Price = Price * 0.9; ROLLBACK TRANSACTION; a) The prices remain unchanged
    b) The prices are updated
    c) The transaction is committed
    d) Throws an error
26. Which isolation level is the default in SQL Server?
    a) READ COMMITTED
    b) READ UNCOMMITTED
    c) SERIALIZABLE
    d) REPEATABLE READ
27. What does the SAVEPOINT statement allow you to do in a transaction?
    a) Create intermediate points for rollback
    b) Automatically commit the transaction
    c) Add a comment to the transaction
    d) End the transaction
28. Which SQL command reverts all changes made during a transaction?
    a) ROLLBACK
    b) UNDO
    c) REVERT
    d) CANCEL
29. What happens if a BULK INSERT operation violates a primary key constraint?
    a) The operation is stopped with an error
    b) The row is skipped, and the operation continues
    c) The operation completes successfully
    d) All rows are inserted except duplicates
30. What is the difference between DELETE and TRUNCATE?
    a) DELETE can include a WHERE clause, TRUNCATE cannot
    b) TRUNCATE removes data and the table structure
    c) DELETE cannot use conditions
    d) TRUNCATE is slower than DELETE

---

Answer Key

Qno	Answer
1	a) Adds a new row with Name “John Doe” and Age 30
2	a) WHERE
3	a) Deletes all rows in the Orders table
4	b) UPDATE
5	a) WHERE clause
6	b) Returns no rows
7	a) IS NULL
8	b) ‘No Address’
9	b) Ignores NULL values and sums the rest
10	c) Returns UNKNOWN
11	a) BULK INSERT
12	a) The character separating data fields in the file
13	a) No rows are inserted, and an error is thrown
14	a) ERRORFILE
15	c) XML
16	a) To execute all operations as a single unit of work
17	b) BEGIN TRANSACTION
18	a) The changes remain in a pending state
19	a) It permanently saves the changes made during the transaction
20	a) When an error occurs
21	a) Updates all rows in the table
22	a) TRANSACTION
23	a) Inserts a row with default values
24	a) DISTINCT
25	a) The prices remain unchanged
26	a) READ COMMITTED
27	a) Create intermediate points for rollback
28	a) ROLLBACK
29	a) The operation is stopped with an error
30	a) DELETE can include a WHERE clause, TRUNCATE cannot