Top DBMS Interview Questions for Freshers
Are you preparing for your first DBMS interview and wondering what questions you might face?
Understanding the key DBMS interview questions for freshers can give you more clarity.
With this guide, you’ll be well-prepared to tackle these DBMS interview questions and answers for freshers and make a strong impression in your interview.
Practice DBMS Interview Questions and Answers
Below are the top 50 DBMS interview questions for freshers with answers:
1. How do you normalize a database table to 3NF?
Answer:
Normalization involves organizing tables to reduce redundancy and dependency. To achieve 3NF, remove transitive dependencies by ensuring that non-key attributes are not dependent on other non-key attributes.
2. Design a table structure for storing information about employees, including their department, salary, and manager.
Answer:
Create separate tables for Employees, Departments, and Managers with foreign keys linking them to handle relationships effectively and avoid redundancy.
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(100),
DepartmentID INT,
ManagerID INT,
Salary DECIMAL(10,2),
FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID),
FOREIGN KEY (ManagerID) REFERENCES Managers(ManagerID)
);
3. How do you create an ER diagram for an e-commerce database?
Answer:
Identify entities such as Customers, Orders, Products, and relationships like Customer-Places-Order, Order-Contains-Product, then map attributes and cardinalities.
4. Write a SQL query to enforce referential integrity between two tables: Orders and Customers.
Answer:
Ensure referential integrity by defining a foreign key constraint in the Orders table referencing the Customers table.
ALTER TABLE Orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);
5. How would you design a database schema for a library management system?
Answer:
Design tables like Books, Members, Loans, and Authors, defining relationships with foreign keys and ensuring normalization to 3NF for efficient data management.
6. Write a SQL query to find the second highest salary from an Employees table.
Answer:
Use the DISTINCT keyword and a subquery to skip the highest salary and select the next highest one.
SELECT MAX(Salary) FROM Employees
WHERE Salary < (SELECT MAX(Salary) FROM Employees);
7. How do you retrieve all employees who work in the same department as ‘John’?
Answer:
Join the Employees table on the DepartmentID and filter using a subquery to match John‘s department.
SELECT * FROM Employees
WHERE DepartmentID = (SELECT DepartmentID FROM Employees WHERE Name = ‘John’);
8. Write a SQL query to display the total sales by each salesperson, including those who have not made any sales.
Answer:
Use a LEFT JOIN between the Sales and Salesperson tables and group by SalespersonID to include those with no sales.
SELECT s.SalespersonID, SUM(s.Amount) AS TotalSales
FROM Salesperson sp
LEFT JOIN Sales s ON sp.SalespersonID = s.SalespersonID
GROUP BY s.SalespersonID;
9. How do you update the salary of employees by 10% if their performance rating is ‘A’?
Answer:
Use the UPDATE statement with a WHERE clause to filter employees based on their performance rating.
UPDATE Employees
SET Salary = Salary * 1.1
WHERE PerformanceRating = ‘A’;
10. Write a SQL query to delete all records from a Sales table where the sale amount is less than the average sale amount.
Answer:
Use a subquery to calculate the average sale amount and delete records below this threshold.
DELETE FROM Sales
WHERE Amount < (SELECT AVG(Amount) FROM Sales);
11. How do you create an index on a column to improve query performance in SQL?
Answer:
Create an index on frequently queried columns to speed up search operations, especially on large tables.
CREATE INDEX idx_employee_name ON Employees(Name);
12. What is a composite index, and how do you create one in SQL?
Answer:
A composite index is an index on multiple columns, improving query performance when filtering by those columns together.
CREATE INDEX idx_employee_dept ON Employees(DepartmentID, Name);
13. How do you identify slow queries and optimize them in a database?
Answer:
Use EXPLAIN to analyze the query execution plan, then optimize by adding indexes, rewriting queries, or normalizing tables.
EXPLAIN SELECT * FROM Employees WHERE DepartmentID = 10;
14. Write a SQL query that utilizes a covering index for a query on Employees fetching Name and DepartmentID.
Answer:
Create a covering index that includes all the columns in the SELECT list to optimize query performance.
CREATE INDEX idx_covering ON Employees(Name, DepartmentID);
15. How do you avoid using indexes in situations where they can degrade performance?
Answer:
Avoid indexing columns with low cardinality, frequent updates, or those involved in wide range queries.
16. How do you ensure that a set of operations in a SQL database is atomic?
Answer:
Use a transaction to group operations and ensure atomicity, either committing all or rolling back in case of an error.
BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance – 100 WHERE AccountID = 1;
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;
COMMIT;
17. Write a SQL command to roll back a transaction if an error occurs during execution.
Answer:
Use ROLLBACK within the transaction block to revert changes if an error occurs.
BEGIN TRANSACTION;
UPDATE Orders SET Status = ‘Shipped’ WHERE OrderID = 1;
IF @@ERROR != 0 ROLLBACK;
COMMIT;
18. How do you implement optimistic concurrency control in a database?
Answer:
Use version numbers or timestamps to detect conflicts and allow multiple transactions to execute simultaneously without locking resources.
19. Write a SQL query that uses a savepoint to partially roll back a transaction.
Answer:
Use SAVEPOINT to create a point within a transaction to which you can roll back if necessary.
BEGIN TRANSACTION;
UPDATE Orders SET Status = ‘Processing’ WHERE OrderID = 1;
SAVEPOINT savepoint1;
UPDATE Orders SET Status = ‘Shipped’ WHERE OrderID = 2;
ROLLBACK TO savepoint1;
COMMIT;
20. How do you resolve deadlocks in a SQL database?
Answer:
Deadlocks can be resolved by ensuring consistent locking order, using short transactions, or configuring deadlock detection mechanisms.
21. How do you grant specific privileges to a user in a SQL database?
Answer:
Use the GRANT statement to assign specific privileges like SELECT, INSERT, UPDATE, or DELETE to a user.
GRANT SELECT, INSERT ON Employees TO ‘username’;
22. Write a SQL query to revoke all privileges from a user except SELECT.
Answer:
Use the REVOKE statement to remove all but the SELECT privilege.
REVOKE ALL PRIVILEGES ON Employees FROM ‘username’;
GRANT SELECT ON Employees TO ‘username’;
23. How do you implement row-level security in a database?
Answer:
Use views or policies to restrict access to specific rows in a table based on user roles or attributes.
24. Write a SQL query to enforce a foreign key constraint between two tables.
Answer:
Use FOREIGN KEY constraints to enforce referential integrity between related tables.
ALTER TABLE Orders
ADD CONSTRAINT fk_customer_id
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);
25. How do you ensure data integrity when inserting records into a table with a unique constraint?
Answer:
Use INSERT IGNORE or ON CONFLICT clauses to handle unique constraint violations gracefully.
INSERT INTO Employees (EmployeeID, Name) VALUES (1, ‘John’)
ON CONFLICT (EmployeeID) DO NOTHING;
26. How do you use a common table expression (CTE) in a SQL query?
Answer:
Use CTEs to define a temporary result set that can be referenced within the main query for improved readability and maintainability.
WITH EmployeeCTE AS (
SELECT EmployeeID, Name FROM Employees WHERE DepartmentID = 1
)
SELECT * FROM EmployeeCTE WHERE Name LIKE ‘A%’;
27. Write a SQL query to calculate the cumulative sum of sales in a Sales table.
Answer:
Use the SUM() window function to calculate the cumulative sum of a column.
SELECT SalespersonID, SaleDate,
SUM(Amount) OVER (ORDER BY SaleDate) AS CumulativeSales
FROM Sales;
28. How do you implement a recursive query in SQL to find all subordinates of an employee?
Answer:
Use a recursive CTE to traverse hierarchical data like organizational charts.
WITH RECURSIVE Subordinates AS (
SELECT EmployeeID, Name FROM Employees WHERE ManagerID = 1
UNION
SELECT e.EmployeeID, e.Name FROM Employees e
INNER JOIN Subordinates s ON e.ManagerID = s.EmployeeID
)
SELECT * FROM Subordinates;
29. Write a SQL query to find the top 3 highest salaries in each department.
Answer:
Use the ROW_NUMBER() window function to rank salaries within each department and filter the top 3.
WITH RankedSalaries AS (
SELECT DepartmentID, Name, Salary,
ROW_NUMBER() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS Rank
FROM Employees
)
SELECT * FROM RankedSalaries WHERE Rank <= 3;
30. How do you optimize a query that uses a large number of joins?
Answer:
Optimize join queries by ensuring indexes on the join columns, limiting the result set, and considering the join order.
31. How do you create a stored procedure that inserts a new employee and returns the employee ID?
Answer:
Use a stored procedure to encapsulate the insert logic and return the generated EmployeeID.
CREATE PROCEDURE InsertEmployee (IN Name VARCHAR(100), OUT
EmployeeID INT)
BEGIN
INSERT INTO Employees (Name) VALUES (Name);
SET EmployeeID = LAST_INSERT_ID();
END;
32. Write a trigger that automatically updates a LastModified timestamp when a record in a table is updated.
Answer:
Use an AFTER UPDATE trigger to update the LastModified column after a record is modified.
CREATE TRIGGER UpdateLastModified
AFTER UPDATE ON Employees
FOR EACH ROW
BEGIN
SET NEW.LastModified = NOW();
END;
33. How do you implement a trigger to enforce a business rule in a database?
Answer:
Use triggers to enforce business rules such as preventing negative account balances during transactions.
34. Write a stored procedure that calculates and returns the total sales for a specific salesperson.
Answer:
Encapsulate the sum calculation in a stored procedure to return the total sales for a given SalespersonID.
CREATE PROCEDURE GetTotalSales (IN SalespersonID INT, OUT TotalSales DECIMAL)
BEGIN
SELECT SUM(Amount) INTO TotalSales FROM Sales WHERE SalespersonID = SalespersonID;
END;
35. How do you handle errors in stored procedures to ensure proper rollback in case of failure?
Answer:
Use DECLARE to define error handlers and ROLLBACK to revert transactions in case of an error.
36. How do you back up a SQL database using a command?
Answer:
Use the BACKUP command to create a backup of the database, specifying the destination file.
BACKUP DATABASE myDatabase TO DISK = ‘C:\Backup\myDatabase.bak’;
37. Write a SQL command to restore a database from a backup file.
Answer:
Use the RESTORE command to restore the database from a specified backup file.
RESTORE DATABASE myDatabase FROM DISK = ‘C:\Backup\myDatabase.bak’;
38. How do you perform a point-in-time recovery in an SQL database?
Answer:
Perform point-in-time recovery by restoring the full backup and applying transaction logs up to a specific time.
39. Write an SQL command to take a differential backup of a database.
Answer:
Use the BACKUP command with the DIFFERENTIAL option to back up changes since the last full backup.
BACKUP DATABASE myDatabase TO DISK = ‘C:\Backup\myDatabase_diff.bak’ WITH DIFFERENTIAL;
40. How do you ensure that your database backup strategy is reliable?
Answer:
Regularly test backup and restore processes, maintain off-site backups, and use redundant storage solutions.
41. How do you implement a many-to-many relationship in a relational database?
Answer:
Use a junction table that contains foreign keys referencing the primary keys of the related tables.
CREATE TABLE CourseStudent (
CourseID INT,
StudentID INT,
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID),
FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
);
42. Write a SQL query to implement a star schema for a sales data warehouse.
Answer:
Design a central fact table for sales and surrounding dimension tables for time, products, and customers.
CREATE TABLE SalesFact (
SaleID INT PRIMARY KEY,
ProductID INT,
CustomerID INT,
TimeID INT,
Amount DECIMAL(10,2),
FOREIGN KEY (ProductID) REFERENCES Products(ProductID),
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID),
FOREIGN KEY (TimeID) REFERENCES Time(TimeID)
);
43. How do you design a database for a high-availability system?
Answer:
Implement replication, sharding, and clustering, and use failover strategies to ensure high availability.
44. Write an SQL query to implement an audit log for tracking changes to a specific table.
Answer:
Use a trigger to insert change records into an audit table whenever the original table is modified.
CREATE TRIGGER LogChanges
AFTER UPDATE ON Employees
FOR EACH ROW
BEGIN
INSERT INTO EmployeeAudit (EmployeeID, ChangeDate) VALUES (NEW.EmployeeID, NOW());
END;
45. How do you handle schema evolution in a database with minimal downtime?
Answer:
Use techniques like online schema changes, versioning, and database migrations to evolve the schema without impacting availability.
46. How do you implement sharding in a distributed database system?
Answer:
Shard the database by partitioning data across multiple servers based on a sharding key like customer ID or region.
47. Write an SQL command to create a materialized view that caches query results for faster access.
Answer:
Use the CREATE MATERIALIZED VIEW statement to create a view that stores query results persistently.
CREATE MATERIALIZED VIEW SalesSummary AS
SELECT ProductID, SUM(Amount) AS TotalSales FROM Sales GROUP BY ProductID;
48. How do you handle data consistency in a distributed database with eventual consistency?
Answer:
Use strategies like conflict resolution, versioning, and quorum-based writes to ensure data consistency in distributed systems.
49. Write an SQL query that utilizes a window function to calculate a moving average.
Answer:
Use the AVG() window function with a specified window frame to calculate the moving average.
SELECT Date,
AVG(Sales) OVER (ORDER BY Date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MovingAvg
FROM SalesData;
50. How do you ensure data privacy and compliance in a database?
Answer:
Implement encryption, access controls, data masking, and logging to ensure compliance with privacy regulations like GDPR.
Final Words
Getting ready for an interview can feel overwhelming, but going through these DBMS fresher interview questions can help you feel more confident.
With the right preparation, you’ll ace your DBMS interview but don’t forget to practice the DBMS basic coding and query-related interview questions too!
Frequently Asked Questions
1. What are the most common interview questions for Database Management Systems?
Common interview questions for Database Management Systems typically focus on SQL queries, normalization, transactions, and indexing.
2. What are the important DBMS topics freshers should focus on for interviews?
Important DBMS topics freshers should focus on include ER models, normalization, SQL, ACID properties, and indexing.
3. How should freshers prepare for DBMS technical interviews?
Freshers should prepare for DBMS technical interviews by practicing SQL queries, understanding normalization, and studying key concepts like transactions and indexing.
4. What strategies can freshers use to solve DBMS programming questions during interviews?
Freshers can use strategies like breaking down problems into smaller parts and writing out SQL queries step by step to solve DBMS programming questions during interviews.
5. Should freshers prepare for advanced DBMS topics in interviews?
Yes, freshers should prepare for advanced DBMS topics like stored procedures, triggers, and transaction management if the role requires in-depth database knowledge.
Explore More DBMS Resources
Explore More Interview Questions
Thirumoorthy serves as a teacher and coach. He obtained a 99 percentile on the CAT. He cleared numerous IT jobs and public sector job interviews, but he still decided to pursue a career in education. He desires to elevate the underprivileged sections of society through education
Thirumoorthy serves as a teacher and coach. He obtained a 99 percentile on the CAT. He cleared numerous IT jobs and public sector job interviews, but he still decided to pursue a career in education. He desires to elevate the underprivileged sections of society through education
Related Posts
Best Apps to Learn Web Development
Ever thought about building your own website or launching a career in tech but don’t know where to start? With the …
