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