15 Database Management Interview Questions and Answers

Database management is a critical skill in the tech industry, underpinning everything from application development to data analytics. Mastery of database systems, whether relational or non-relational, is essential for ensuring data integrity, optimizing performance, and enabling efficient data retrieval. With the increasing reliance on data-driven decision-making, proficiency in database management has become a highly sought-after competency.

This article offers a curated selection of interview questions designed to test your knowledge and problem-solving abilities in database management. By working through these questions, you will gain a deeper understanding of key concepts and be better prepared to demonstrate your expertise in a professional setting.

Database Management Interview Questions and Answers

1. Explain the concept of ACID properties.

ACID properties ensure reliable database transactions:

• Atomicity: A transaction is treated as a single unit, either fully completing or not at all.
• Consistency: Transactions move the database from one valid state to another, adhering to all rules.
• Isolation: Concurrent transactions do not affect each other, keeping intermediate states invisible.
• Durability: Once committed, transactions remain so, even after a system failure.

2. Write a SQL query to join two tables, Customers and Orders, and retrieve all customers who have placed an order.

To find customers who have placed an order, use an INNER JOIN on the Customers and Orders tables based on a common column, typically the customer ID.

Example:

SELECT Customers.CustomerID, Customers.CustomerName, Orders.OrderID
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

This query retrieves only customers with matching orders.

3. What is a primary key and how does it differ from a unique key?

A primary key uniquely identifies each row in a table and cannot be null. A unique key also ensures uniqueness but can accept a single null value. A table can have multiple unique keys but only one primary key.

Key differences:

• Uniqueness: Both ensure unique values.
• Null Values: Primary keys disallow nulls; unique keys allow one null.
• Multiplicity: One primary key per table; multiple unique keys allowed.

4. Explain the concept of indexing and its types.

Indexing optimizes database performance by reducing disk access during queries. Types include:

• Primary Index: Automatically created with a primary key, ensuring uniqueness and non-null values.
• Secondary Index: Created on non-primary key columns for faster access without altering data order.
• Clustered Index: Sorts data rows based on the index key, with only one allowed per table.
• Unique Index: Ensures all values in the indexed column are unique.
• Composite Index: Indexes multiple columns, useful for multi-column queries.
• Bitmap Index: Efficient for columns with low cardinality, using bitmaps.

5. Write a SQL query to retrieve the top 5 highest paid employees from an Employee table.

To retrieve the top 5 highest-paid employees:

SELECT employee_name, salary
FROM Employee
ORDER BY salary DESC
LIMIT 5;

This query orders employees by salary in descending order and limits the output to the top 5.

6. Explain the difference between OLTP and OLAP systems.

OLTP systems manage transactional data with quick query processing and data integrity, suitable for applications like banking. OLAP systems focus on querying and reporting, optimized for complex queries and data analysis, used in business intelligence.

Key differences:

• Purpose: OLTP for transactions; OLAP for analysis.
• Data Volume: OLTP handles many small transactions; OLAP deals with large historical data.
• Query Complexity: OLTP queries are simple; OLAP queries are complex.
• Normalization: OLTP databases are highly normalized; OLAP databases are often denormalized.
• Response Time: OLTP requires quick responses; OLAP can tolerate longer times.

7. What are stored procedures and when would you use them?

Stored procedures are precompiled SQL statements stored on the database server, offering performance, maintainability, and security benefits. They centralize business logic and are useful for complex operations.

Example:

CREATE PROCEDURE GetEmployeeDetails
    @EmployeeID INT
AS
BEGIN
    SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
END;

This procedure retrieves employee details based on an ID.

8. Write a SQL query to implement a recursive CTE (Common Table Expression).

A recursive CTE allows for recursive queries, useful for hierarchical data like organizational charts.

Example:

WITH RECURSIVE EmployeeHierarchy AS (
    SELECT EmployeeID, ManagerID, EmployeeName
    FROM Employees
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.ManagerID, e.EmployeeName
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT * FROM EmployeeHierarchy;

This CTE finds employee hierarchies starting from top-level employees.

9. Explain the concept of sharding in databases.

Sharding distributes data across multiple machines to improve performance and scalability. Each shard is a separate database containing a portion of the data.

Benefits include:

• Scalability: Handles more queries and larger datasets.
• Performance: Parallel query processing across shards.
• Availability: Other shards operate if one fails.

Sharding introduces complexity in data distribution and maintenance.

10. What is a transaction log and why is it important?

A transaction log records all transactions and changes, aiding in data recovery, ensuring atomicity and durability, managing concurrency, and providing an audit trail.

11. Write a SQL query to partition a table by a specific column.

Partitioning divides a large table into smaller pieces for improved performance and easier maintenance. Here’s an example of range partitioning by a specific column:

CREATE TABLE sales (
    id INT,
    sale_date DATE,
    amount DECIMAL(10, 2)
)
PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p0 VALUES LESS THAN (2010),
    PARTITION p1 VALUES LESS THAN (2015),
    PARTITION p2 VALUES LESS THAN (2020),
    PARTITION p3 VALUES LESS THAN (2025)
);

This partitions the sales table by sale_date.

12. Explain the CAP theorem and its implications for distributed databases.

The CAP theorem states that distributed systems cannot simultaneously provide consistency, availability, and partition tolerance. You can only achieve two of these guarantees at any time:

• CA: Consistency and availability without partition tolerance.
• CP: Consistency and partition tolerance, sacrificing availability during network issues.
• AP: Availability and partition tolerance, without always providing the most recent data.

13. Write a SQL query to implement a trigger that logs changes to a table.

A trigger is a stored procedure that runs automatically when certain events occur, such as changes to a table. To log changes, create a logging table and define a trigger.

Example:

-- Create a logging table
CREATE TABLE changes_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    table_name VARCHAR(255),
    operation VARCHAR(255),
    changed_data TEXT,
    change_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create a trigger to log changes
CREATE TRIGGER log_changes
AFTER UPDATE ON your_table
FOR EACH ROW
BEGIN
    INSERT INTO changes_log (table_name, operation, changed_data)
    VALUES ('your_table', 'UPDATE', CONCAT('Old: ', OLD.column_name, ' New: ', NEW.column_name));
END;

This trigger logs updates to your_table.

14. What are some common data security measures in databases?

Common data security measures include:

• Encryption: Protects data at rest and in transit.
• Access Control: Restricts access based on roles.
• Authentication: Ensures only authorized users access the database.
• Auditing and Monitoring: Detects and responds to security breaches.
• Data Masking: Protects sensitive data in non-production environments.
• Backup and Recovery: Ensures data restoration after incidents.
• Patch Management: Keeps software updated against vulnerabilities.

15. What are NoSQL databases and when would you use them?

NoSQL databases are non-relational and handle large volumes of data, distributed data stores, and real-time analytics. Types include:

• Document Stores: Use JSON, BSON, or XML documents (e.g., MongoDB).
• Key-Value Stores: Store data as key-value pairs (e.g., Redis).
• Column-Family Stores: Use columns instead of rows (e.g., Cassandra).
• Graph Databases: Use graph structures (e.g., Neo4j).

Use NoSQL for high scalability, flexible schema, large data volumes, and real-time analytics.