10 CRUD Operations Interview Questions and Answers

CRUD operations—Create, Read, Update, and Delete—are fundamental to database management and are essential skills for any developer working with data storage and retrieval. Mastery of CRUD operations ensures efficient and effective interaction with databases, which is crucial for maintaining data integrity and optimizing application performance. Understanding these operations is key to building robust and scalable applications.

This article offers a curated selection of interview questions focused on CRUD operations, designed to help you demonstrate your proficiency in managing and manipulating data. By reviewing these questions and their answers, you will be better prepared to showcase your technical expertise and problem-solving abilities in your upcoming interviews.

CRUD Operations Interview Questions and Answers

1. Describe how you would retrieve all records from the Employees table where the Position is ‘Manager’.

CRUD operations, which stand for Create, Read, Update, and Delete, are the fundamental functions of persistent storage. To retrieve records from the Employees table where the Position is ‘Manager’, use the Read operation with a SELECT statement in SQL:

SELECT * FROM Employees WHERE Position = 'Manager';

This query selects all columns from the Employees table where the Position column is ‘Manager’.

2. Write a Python function to update the Salary of an employee in the Employees table based on their ID.

To update an employee’s salary in the Employees table using Python, the sqlite3 library can be utilized. This library facilitates interaction with SQLite databases, which are lightweight and suitable for demonstrations. Here’s a Python function for updating an employee’s salary:

import sqlite3

def update_employee_salary(employee_id, new_salary):
    try:
        conn = sqlite3.connect('employees.db')
        cursor = conn.cursor()
        
        cursor.execute('''
            UPDATE Employees
            SET Salary = ?
            WHERE ID = ?
        ''', (new_salary, employee_id))
        
        conn.commit()
        
    except sqlite3.Error as e:
        print(f"An error occurred: {e}")
        
    finally:
        if conn:
            conn.close()

# Example usage
update_employee_salary(1, 75000)

3. Explain the difference between DELETE and TRUNCATE in SQL. When would you use each?

The DELETE and TRUNCATE commands in SQL both remove data from a table but differ in their operation and use cases.

DELETE:

• Removes specific rows based on a condition in the WHERE clause.
• Is a DML operation, allowing rollback within a transaction.
• Can be slower due to individual row logging.
• Triggers can be activated.

TRUNCATE:

• Removes all rows, resetting the table.
• Is a DDL operation, not allowing rollback.
• Generally faster as it deallocates data pages.
• Does not activate triggers.

Use DELETE for specific row removal or when rollback is needed. Use TRUNCATE for quickly clearing a table without rollback.

4. How would you handle concurrent updates to the same record in a database to prevent data inconsistency?

Concurrent updates to the same database record can cause data inconsistency. Strategies to manage this include:

• Pessimistic Locking: Locks the record during a transaction, blocking others until the lock is released.
• Optimistic Concurrency Control: Allows multiple transactions to read and update, checking for conflicts before committing.
• Versioning: Assigns a version number to each record, checking for changes before committing.
• Timestamping: Uses timestamps to ensure no other transaction has modified the record before committing.

5. Explain the concept of optimistic locking and how it can be used in CRUD operations.

Optimistic locking involves using a version number or timestamp with database records. When a record is read, its version number is also read. Before an update, the version number is checked to ensure it hasn’t changed. If it has, the update is aborted.

Example:

import sqlite3

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

cursor.execute('''
CREATE TABLE IF NOT EXISTS items (
    id INTEGER PRIMARY KEY,
    name TEXT,
    version INTEGER
)
''')

cursor.execute('INSERT INTO items (name, version) VALUES (?, ?)', ('item1', 1))
conn.commit()

def update_item(item_id, new_name, current_version):
    cursor.execute('''
    UPDATE items
    SET name = ?, version = version + 1
    WHERE id = ? AND version = ?
    ''', (new_name, item_id, current_version))
    
    if cursor.rowcount == 0:
        raise Exception("Update failed due to version mismatch")

try:
    update_item(1, 'updated_item1', 1)
    conn.commit()
except Exception as e:
    print(e)

conn.close()

6. Write a SQL transaction to transfer funds between two accounts, ensuring that both the debit and credit operations are completed successfully.

A SQL transaction ensures that a sequence of operations is completed successfully or not at all, maintaining database integrity. This is vital in financial operations, like transferring funds between accounts, where both debit and credit must be completed.

Example of a SQL transaction for fund transfer:

START TRANSACTION;

<ul>
<li>Debit from Account A</li>
</ul>
UPDATE accounts
SET balance = balance - 100
WHERE account_id = 'A';

<ul>
<li>Credit to Account B</li>
</ul>
UPDATE accounts
SET balance = balance + 100
WHERE account_id = 'B';

<ul>
<li>Check for errors and commit or rollback</li>
</ul>
IF @@ERROR = 0
    COMMIT;
ELSE
    ROLLBACK;

7. Discuss the potential security risks associated with CRUD operations and how you would mitigate them.

Create: The risk is malicious data injection. Mitigate by validating and sanitizing inputs.

Read: Unauthorized access is a risk. Use authentication and authorization, and encrypt data.

Update: Unauthorized modification is a risk. Use authentication, authorization, and maintain an audit trail.

Delete: Unauthorized deletion can lead to data loss. Use role-based access control and consider soft deletes.

8. Explain how you would optimize a slow-running query that performs a complex JOIN operation across multiple tables.

To optimize a slow query with complex JOINs, consider:

• Indexing: Index columns used in JOIN conditions and WHERE clauses.
• Query Refactoring: Simplify the query with subqueries or CTEs.
• Analyze Execution Plan: Identify bottlenecks and optimize them.
• Avoid SELECT *: Specify only needed columns.
• Use Appropriate JOIN Types: Choose efficient JOIN types.
• Partitioning: Consider partitioning large tables.
• Materialized Views: Use for frequently run queries with infrequent data changes.
• Database Configuration: Ensure proper performance settings.

9. Explain the concept of ACID properties in databases and how they relate to CRUD operations.

ACID properties ensure reliable database transactions and consistent states. Here’s how they relate to CRUD operations:

• Atomicity: Ensures a transaction is a single unit, either fully succeeding or failing, preventing partial updates.
• Consistency: Ensures transactions bring the database from one valid state to another, maintaining rules and integrity.
• Isolation: Ensures transactions don’t interfere with each other, important in multi-user environments.
• Durability: Guarantees committed transactions remain so, even after failures.

10. Describe how to implement soft deletes and why they might be used instead of hard deletes.

Soft deletes involve marking a record as inactive rather than removing it, maintaining data integrity and allowing recovery. This contrasts with hard deletes, where records are permanently removed.

Example:

class Record(models.Model):
    name = models.CharField(max_length=100)
    is_deleted = models.BooleanField(default=False)

    def delete(self):
        self.is_deleted = True
        self.save()

# Usage
record = Record.objects.get(id=1)
record.delete()

In this example, the delete method sets the is_deleted flag to True instead of removing the record.