Most Asked DBMS Interview Questions with answers

 

Most Asked DBMS Interview Questions:-

1. What is a DBMS?

• Answer: A DBMS (Database Management System) is software that manages, organizes, and controls access to a database. It ensures data integrity, security, and efficient data retrieval.

2. What are the primary functions of a DBMS?

• Answer: The primary functions of a DBMS include data storage, data retrieval, data manipulation, data modeling, and data security.

3. What is the difference between DBMS and RDBMS?

• Answer: A DBMS is a general system for managing databases, while an RDBMS (Relational DBMS) is a specific type of DBMS that manages data in a tabular form with relations or tables. RDBMS enforces referential integrity and supports SQL for querying.

4. Explain ACID properties in DBMS.

• Answer: ACID stands for Atomicity, Consistency, Isolation, and Durability. It ensures that database transactions are reliable. Atomicity guarantees that a transaction is treated as a single, indivisible unit. Consistency ensures that the database remains in a consistent state before and after a transaction. Isolation ensures that concurrent transactions do not interfere with each other. Durability ensures that once a transaction is committed, its effects are permanent.

5. What is normalization in the context of a relational database?

• Answer: Normalization is the process of organizing data in a relational database to eliminate redundancy and improve data integrity. It involves creating tables and relationships that minimize data duplication.

6. Explain the differences between primary key and unique key.

• Answer: A primary key is used to uniquely identify each record in a table and enforces the uniqueness constraint. Only one primary key is allowed per table. A unique key also enforces uniqueness but can be used to maintain data integrity without serving as the primary identifier for records.

7. What is a foreign key?

• Answer: A foreign key is a field in a table that establishes a link between two tables. It enforces referential integrity by ensuring that values in the foreign key column match values in the primary key of another table.

8. What is a join in SQL, and what are its types?

• Answer: A join in SQL is used to combine data from two or more tables based on a related column. Types of joins include INNER JOIN, LEFT JOIN (or LEFT OUTER JOIN), RIGHT JOIN (or RIGHT OUTER JOIN), and FULL JOIN (or FULL OUTER JOIN).

9. What is a stored procedure in a DBMS?

• Answer: A stored procedure is a precompiled collection of SQL statements that can be executed as a single unit. It is stored in the database and can be called multiple times, providing modularity and security.

10. What is indexing in a DBMS?

• Answer: Indexing is a technique used to improve the speed of data retrieval operations by creating a data structure that allows for fast lookups. Common types of indexes include B-tree and hash indexes.

11. What is the difference between a clustered and a non-clustered index?

• Answer: A clustered index determines the physical order of data in a table, and a table can have only one clustered index. A non-clustered index does not affect the physical order of data and is used to improve query performance.

12. What is SQL injection, and how can it be prevented?

• Answer: SQL injection is a security vulnerability where malicious SQL statements are inserted into input fields. It can be prevented by using parameterized queries and input validation.

13. What is a view in a DBMS?

• Answer: A view is a virtual table based on the result of a SQL SELECT query. It simplifies complex queries and provides a security mechanism to control data access.

14. Explain the differences between DELETE and TRUNCATE in SQL.

• Answer: DELETE is a DML command used to remove specific rows from a table and can be rolled back. TRUNCATE is a DDL command used to remove all rows from a table and cannot be rolled back.

15. What is the difference between a database and a schema?

• Answer: A database is a container that holds one or more schemas. A schema is a logical container for database objects like tables, views, and procedures.

16. What is the difference between a primary key and a candidate key?

• Answer: A primary key is a specific candidate key chosen to be the main identifier for a table. Candidate keys are potential keys that could serve as the primary key.

17. Explain the concept of data concurrency in a DBMS.

• Answer: Data concurrency is the simultaneous access and modification of data by multiple transactions. DBMS must ensure data consistency while allowing concurrent access.

18. What is a composite key?

• Answer: A composite key is a key that consists of two or more columns used together to uniquely identify a row in a table.

19. What is the difference between a DDL and a DML statement in SQL?

• Answer: DDL (Data Definition Language) statements are used to define or modify the structure of a database, tables, or schemas. DML (Data Manipulation Language) statements are used to manipulate data within the database, such as SELECT, INSERT, UPDATE, and DELETE.

20. Explain the difference between a super key and a candidate key.

• Answer: A super key is a set of one or more columns that can uniquely identify a row in a table. A candidate key is a minimal super key, meaning it is a super key with no unnecessary columns.

21. What is the purpose of the GROUP BY clause in SQL?

• Answer: The GROUP BY clause is used to group rows with identical values in specified columns into summary rows. It is typically used with aggregate functions like COUNT, SUM, AVG, etc.

22. What is a self-join in SQL?

• Answer: A self-join is a join where a table is joined with itself. It is often used when you want to relate rows within the same table, such as when dealing with hierarchical data.

23. What is the difference between a unique constraint and a unique index?

• Answer: A unique constraint enforces data integrity by ensuring that all values in a column are unique, while a unique index improves query performance by creating an index on the unique column.

24. What is data redundancy, and why is it important to minimize it?

• Answer: Data redundancy occurs when the same data is stored in multiple places. Minimizing it is important to save storage space, reduce the risk of data inconsistencies, and maintain data integrity.

25. What is the purpose of the HAVING clause in SQL?

• Answer: The HAVING clause is used in combination with the GROUP BY clause to filter the results of aggregate functions. It operates on the grouped data after aggregation.

26. What is a database transaction?

• Answer: A database transaction is a sequence of one or more SQL statements that are executed as a single unit of work. It should follow the ACID properties, ensuring data consistency and integrity.

27. What is the difference between a heap table and a clustered table?

• Answer: A heap table is an unordered storage structure, while a clustered table is stored in a specific order determined by the clustered index. Clustered tables are typically faster for range queries.

28. Explain the concept of referential integrity in DBMS.

• Answer: Referential integrity is a database constraint that ensures that relationships between tables are maintained by enforcing foreign key constraints. It prevents the creation of orphaned records and ensures data consistency.

29. What is the purpose of the SQL CASE statement?

• Answer: The SQL CASE statement is used to perform conditional logic within a query. It allows you to perform different actions based on specified conditions.

30. What is the difference between a UNION and a UNION ALL in SQL?

• Answer: UNION combines the result sets of two or more SELECT queries, removing duplicates. UNION ALL also combines result sets but retains all rows, including duplicates.

31. What is the difference between a database index and a database view?

• Answer: An index is a data structure that improves query performance by enabling quick data retrieval. A view is a virtual table based on a query, simplifying complex queries and providing a security mechanism.

32. What is the purpose of the SQL ORDER BY clause?

• Answer: The SQL ORDER BY clause is used to sort the result set of a query in ascending or descending order based on one or more columns.

33. Explain the difference between a full backup and an incremental backup.

• Answer: A full backup copies all the data in a database, while an incremental backup copies only the data that has changed since the last backup. Incremental backups are faster but require a series of backups to restore to a specific point in time.

34. What is the difference between a deadlock and a livelock in the context of DBMS?

• Answer: A deadlock is a situation where two or more transactions are unable to proceed because they are each waiting for a resource held by the other. A livelock is a situation where transactions are actively trying to resolve a deadlock but end up in an unproductive loop.

35. What is a stored function in a DBMS?

• Answer: A stored function is a database object that takes input parameters, performs some computations, and returns a single value. It can be called within SQL statements.

36. Explain the concept of database replication.

• Answer: Database replication is the process of creating and maintaining duplicate copies of a database to ensure data availability, fault tolerance, and load distribution. Changes made to one copy are propagated to other copies.

37. What is a trigger in a DBMS?

• Answer: A trigger is a database object that automatically executes a specified set of actions when certain events, such as INSERT, UPDATE, or DELETE operations, occur in the database.

38. What is a NoSQL database, and when is it used?

• Answer: A NoSQL database is a non-relational database that is used for handling large volumes of unstructured or semi-structured data. It is often used in big data and real-time applications.

39. Explain the differences between a candidate key and a surrogate key.

• Answer: A candidate key is a unique key that can serve as the primary key for a table. A surrogate key is a system-generated unique identifier, often an auto-incrementing number, used as a primary key when there is no natural candidate key.

40. What is the purpose of the SQL ROLLBACK statement?

• Answer: The ROLLBACK statement is used to undo changes made in a transaction, returning the database to its state before the transaction started.

41. What is a materialized view in a DBMS?

• Answer: A materialized view is a precomputed table that stores the results of a query, allowing for faster data retrieval. It is periodically refreshed to reflect changes in the source data.

42. What is the purpose of the SQL IN operator?

• Answer: The SQL IN operator is used to filter results by specifying a list of values for a column. It returns rows where the column value matches any value in the list.

43. What is a NoSQL database model, and name a few popular NoSQL databases?

• Answer: NoSQL databases use various data models like document-based (e.g., MongoDB), key-value (e.g., Redis), column-family (e.g., Cassandra), and graph-based (e.g., Neo4j) to store and manage data.

44. Explain the concept of database sharding.

• Answer: Database sharding is a technique for distributing data across multiple servers or databases to improve scalability and performance. Each shard contains a subset of the data.

45. What is the purpose of the SQL LIKE operator?

• Answer: The SQL LIKE operator is used to filter results based on a pattern, such as searching for strings that match a specific pattern using wildcard characters (% and _).

46. What is the CAP theorem in the context of distributed databases?

• Answer: The CAP theorem states that in a distributed database, you can have at most two out of three guarantees: Consistency (all nodes see the same data at the same time), Availability (every request receives a response without guaranteeing the most recent data), and Partition Tolerance (the system continues to operate even in the presence of network partitions).

47. What is a database index scan, and how does it differ from a table scan?

• Answer: A database index scan is an operation that uses an index to quickly locate and retrieve specific rows from a table, while a table scan involves scanning the entire table sequentially to find the desired data. Index scans are typically faster.

48. Explain the concept of database denormalization.

• Answer: Database denormalization is the process of intentionally introducing redundancy into a database design to improve query performance. It is done to reduce the number of joins needed in complex queries.

49. What is the purpose of the SQL GROUP_CONCAT function?

• Answer: The GROUP_CONCAT function is used in MySQL to concatenate values from multiple rows into a single string within a group of rows, typically used with the GROUP BY clause.

50. What is the difference between OLAP and OLTP databases?

• Answer: OLAP (Online Analytical Processing) databases are used for complex queries and data analysis, typically read-heavy operations. OLTP (Online Transaction Processing) databases are designed for transactional operations, such as insert, update, and delete, with a focus on maintaining data integrity.