SQL : DML Interview Questions and Answers (INSERT, DELETE, SELECT

 

DML Commands: Interview Questions & Answers

 DML Commands:

1.      INSERT

2.      UPDATE

3.      DELETE

4.      SELECT

INSERT:

The SQL INSERT statement is used to add new rows of data into a table in a relational database.

The basic syntax is:

 

INSERT INTO table_name (column1, column2, column3) VALUES (value1, value2, value3);

 ·       Basic SQL INSERT Questions

1. What is the purpose of the SQL INSERT statement?

2. How do you insert a single record into a table? Can you provide an example?

3. What syntax would you use to insert multiple records into a table in a single statement?

4. How do you handle inserting data with default values for certain columns?

5. Explain how to use the INSERT statement with a SELECT query. Can you provide an example?

 ·       Intermediate SQL INSERT Questions

6. What happens if you try to insert a record that violates a unique constraint?

7. How would you insert data into a table with foreign key constraints? What considerations do you need to keep in mind?

8. Can you explain the difference between using the INSERT INTO syntax with or without the column list?

9. How can you use the INSERT statement to insert data into a table that has a composition of non-nullable and nullable columns?

10. Can you perform an INSERT operation on a view? If so, what are the conditions?

 ·       Advanced SQL INSERT Questions

11. What is the purpose of the RETURNING clause in a SQL INSERT statement?

12. Can you explain how error handling works in SQL when performing insert operations?

13. How do transactions affect your ability to insert data into a database? Can you give an example of a transaction involving INSERT statements?

14. When would you want to use a CTE (Common Table Expression) with an INSERT operation?

15. How can you ensure data integrity when inserting records into a table that has cascading actions on delete?

 ·       Practical SQL INSERT Scenarios

16. Given a table structure, write an SQL statement to insert data into it.

17. How do you perform bulk inserts efficiently in SQL?

18. What methods are available in SQL to handle NULL values during an insert?

19. How do you determine the maximum ID value in a table for generating a new record without causing a conflict?

20. Describe a scenario where using the INSERT IGNORE or INSERT ON DUPLICATE KEY UPDATE syntax is useful.

 

Question 2: How can you insert multiple rows into a table in a single SQL statement?

Answer: You can insert multiple rows by using the following syntax:

 

INSERT INTO table_name (column1, column2, column3) VALUES

(value1a, value2a, value3a),

(value1b, value2b, value3b),

(value1c, value2c, value3c);

 

Question 3: Explain how to insert data into a table without specifying all columns.

Answer: If you do not want to specify all columns while inserting data, you can list the specific columns you want to insert values into. For example:

 

INSERT INTO table_name (column1, column3) VALUES (value1, value3);

 

In this case, the value for `column2` must either have a default defined in the table schema or can be NULL if that is allowed.

 

 Question 4: What happens if you try to insert a row with a primary key that already exists?

Answer: If you attempt to insert a row with a primary key that already exists, the database will generate an error due to the violation of the primary key constraint, which requires that all values in the primary key must be unique.

 

 Question 5: How can you insert data into a table from another table?

Answer: You can use the INSERT INTO ... SELECT statement to insert data from one table into another. The syntax is as follows:

INSERT INTO target_table (column1, column2) SELECT column1, column2 FROM source_table WHERE condition;

Question 6: Can you use the INSERT statement with a transaction?

Answer: Yes, you can use the INSERT statement within a transaction. This allows you to group multiple SQL statements for execution together, ensuring that either all of them succeed or none at all. For example:

 

BEGIN TRANSACTION;

INSERT INTO table_name (column1, column2) VALUES (value1, value2);

INSERT INTO table_name (column1, column2) VALUES (value3, value4);

COMMIT;

 Question 7: What is the difference between `INSERT` and `INSERT IGNORE`?

Answer: The `INSERT` statement will return an error if a duplicate entry violates a unique constraint (such as a primary key), while `INSERT IGNORE` will ignore the insert operation if it results in a duplicate entry, allowing the query to complete without error.

 Question 8: What is the `INSERT OR REPLACE` statement?

Answer: The `INSERT OR REPLACE` statement is a command that inserts a new row into a table. If the new row violates a unique constraint, the existing row will be deleted, and the new row will be inserted. This is supported in SQLite and may vary in syntax for other databases.

 Question 9: How can you check the success of an INSERT operation?

Answer: The success of an INSERT operation can be checked by evaluating the affected rows. In many database management systems, you can use functions like `ROW_COUNT()` or check the last inserted ID using `LAST_INSERT_ID()` (in MySQL) to confirm the rows inserted.

 

 Question 10: What will happen if you try to insert NULL values into a column that does not allow NULLs?

Answer: If you try to insert NULL values into a column that does not allow NULLs, the database will generate an error, as this violates the NOT NULL constraint set on that column.

UPDATE:

1. What is the purpose of the SQL UPDATE statement?

 Answer: 

The SQL `UPDATE` statement is used to modify existing records in a table. You can change one or more columns of a particular row or multiple rows based on a specified condition.

  2. How does the WHERE clause affect the SQL UPDATE statement?

 Answer: 

The `WHERE` clause is critical in the `UPDATE` statement because it determines which rows will be affected by the update. If you omit the `WHERE` clause, all records in the table will be updated.

 

Example:

UPDATE employees SET salary = salary + 1000   WHERE department = 'Sales';

 

In this example, only employees in the Sales department will have their salary updated.

  3. Can you update multiple columns in a single SQL UPDATE statement?

 Answer: 

Yes, you can update multiple columns in a single `UPDATE` statement by separating the columns with commas.

 Example:

 UPDATE employees SET salary = salary + 1000,     title = 'Senior Developer' WHERE employee_id = 123;

Here, both the `salary` and `title` columns are updated for the employee with `employee_id` 123.

 4. What happens if you run an UPDATE statement without a WHERE clause?

 Answer: 

If you run an `UPDATE` statement without a `WHERE` clause, all rows in the table will be updated. This is often not the intended action and can lead to data loss or corruption.

 5. How can you check which rows will be updated before performing an UPDATE?

 Answer: 

You can run a `SELECT` statement with the same conditions you would use in the `UPDATE` statement. This will show you which rows will be affected.

 Example:

 SELECT * FROM employees WHERE department = 'Sales';

 6. What are the best practices to follow when writing an UPDATE statement?

 Answer: 

- Always use a `WHERE` clause to specify which rows to update to avoid updating all rows.

- Use transactions to ensure data integrity, especially if updating multiple rows or tables.

- Take a backup of the data or ensure there's a way to roll back changes in case of errors.

- Test the `SELECT` statement first to ensure you're modifying the correct records.

- Be cautious with NULL values and data types.

 7. Is it possible to use a subquery in an UPDATE statement?

 Answer: 

Yes, you can use a subquery in an `UPDATE` statement to set the value of a column based on the result of another query.

 Example:

 UPDATE employees SET salary = (SELECT AVG(salary) FROM employees WHERE department = 'Sales') WHERE department = 'Marketing';

 In this example, the salary of employees in the Marketing department is updated to the average salary of the Sales department.

  8. What is the syntax of the UPDATE statement in SQL?

 Answer: 

The basic syntax of the `UPDATE` statement is as follows:

 UPDATE table_name  SET column1 = value1,      column2 = value2, WHERE condition;

  9. Can you perform an UPDATE operation on a table that is part of a join?

 Answer: 

Yes, you can update records in a table based on a join with another table.

 Example:

 UPDATE employees e SET e.salary = e.salary * 1.1 FROM departments d

WHERE e.department_id = d.id AND d.name = 'Sales';

 Here, employees in the Sales department will get a 10% salary increase.

  10. How would you rollback an update statement if you realize it was incorrect?

 Answer: 

If you are using transactions (recommended), you can roll back an `UPDATE` statement using the `ROLLBACK` command if no `COMMIT` has been issued.

 BEGIN TRANSACTION;

 UPDATE employees SET salary = 5000 WHERE employee_id = 123; 

ROLLBACK;  -- This will undo the update.

 If you did not use transactions, you would have to run another `UPDATE` statement to set the values back to their original state.

DELETE:

 

 Basic Questions

1. What is the purpose of the DELETE statement in SQL?

   - Explain the functionality of the DELETE statement to remove records from a table.

 2. How do you delete a single record from a table? Provide an example.

   - Example: `DELETE FROM employees WHERE employee_id = 1;`

 3. How do you delete multiple records at once?

   - Discuss using the WHERE clause to specify conditions for multiple records.

 4. What happens if you execute a DELETE statement without a WHERE clause?

   - Explain that this will delete all records from the table.

 5. How would you delete all rows in a table while maintaining the structure of the table?

   - Discuss the use of `DELETE FROM table_name` versus `TRUNCATE TABLE table_name`.

 Intermediate Questions

6. What is the difference between the DELETE and TRUNCATE commands?

   - Highlight the key differences in functionality, performance, and logging.

 7. How can you ensure that a DELETE operation is reversible?

   - Discuss the use of transactions and rollback.

 8. What are some potential risks of using the DELETE statement?

   - Talk about data loss, unintended deletions, and the importance of constraints.

 9. How would you use the DELETE statement in conjunction with JOIN clauses? Provide an example.

   - Example: `DELETE FROM orders WHERE order_id IN (SELECT order_id FROM order_items WHERE quantity < 10);`

 Advanced Questions

10. Explain the concept of cascading deletes in SQL. How can it be implemented?

    - Discuss foreign keys with cascading options and relational integrity.

 11. Can you delete records from multiple tables in a single SQL query? If so, how?

    - Explain cascading deletes or using multiple DELETE statements in a transaction.

 12. How can you delete records based on a condition from a subquery?

    - Example: `DELETE FROM employees WHERE employee_id NOT IN (SELECT employee_id FROM active_employees);`

 13. How do you handle DELETE statements in a database with high concurrency?

    - Discuss locking mechanisms and transaction isolation levels.

 14. What considerations should be made when deleting data in a production environment?

    - Discuss backups, data retention policies, and testing delete statements in a staging environment.

 Practical Questions

15. Write a query to delete records older than a certain date from a table.

    - Example: `DELETE FROM orders WHERE order_date < '2022-01-01';`

 16. How would you validate that the correct records are about to be deleted before executing a DELETE statement?

    - Discuss using a SELECT statement with the same WHERE clause to preview affected records.

 17. Given a situation where you have to delete records but must preserve the records for future reference, what strategy would you use?

    - Talk about soft deletion (e.g., using a flag to mark records as deleted).

 18. What considerations should be taken when implementing DELETE permissions in a database?

    - Discuss roles, permissions, and the principle of least privilege.

 Scenario-Based Questions

19. You see that a DELETE statement has affected more rows than expected. How would you troubleshoot this?

    - Explain checking the WHERE clause, understanding the data relationships, and running a SELECT statement to identify issues.

 20. Imagine you are tasked with deleting sensitive data from a table that must comply with data privacy regulations. What factors do you need to consider?

    - Discuss compliance, audit trails, data anonymization, and secure deletion practices.

 

 SELECT:

 

1.      What is use of select statement

2.      Difference between beow queries

Select count(*) from table

Select count(1) from table

Select count(a) from table

Select count(‘q’) from table

Select count(1)+ count(*) from table

Select a+a from table

Select 1+1 from table

Select 1+a from table

Select 9*9 from table

Select a*9 from table