Answer: DBMS stands for Database Management System. It is a software system that enables the creation, organization, and management of databases.
What is the difference between DBMS and RDBMS?
Answer: DBMS is a broader term, while RDBMS (Relational Database Management System) is a type of DBMS that manages data in a tabular format with the help of a relational model.
What is a database?
Answer: A database is a structured collection of data that is organized and stored for easy retrieval and management.
What is normalization?
Answer: Normalization is the process of organizing data to reduce redundancy and improve data integrity by eliminating undesirable characteristics like insertion, update, and deletion anomalies.
Explain the ACID properties in DBMS.
Answer: ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties ensure the reliability of database transactions.
What is a primary key?
Answer: A primary key is a unique identifier for each record in a table. It ensures data integrity and provides a way to uniquely identify a record.
Explain the concept of foreign key.
Answer: A foreign key is a field in a database table that is used to establish a link between the data in two tables. It creates a relationship between the tables.
What is indexing in databases?
Answer: Indexing is a technique used to improve the speed of data retrieval operations on a database table. It creates a data structure to search for specific records more efficiently.
What is a stored procedure?
Answer: A stored procedure is a precompiled collection of one or more SQL statements that can be executed as a single unit. It is stored in the database and can be called by name.
Explain the concept of a view in DBMS.
Answer: A view is a virtual table based on the result of a SELECT query. It does not store the data itself but provides a way to represent the data stored in one or more tables.
What is a trigger in DBMS?
Answer: A trigger is a set of instructions that are automatically executed (or ‘triggered’) in response to certain events on a particular table or view.
What is the purpose of the GROUP BY clause in SQL?
Answer: The GROUP BY clause is used in SQL to arrange identical data into groups based on one or more columns, often used with aggregate functions like COUNT, SUM, AVG, etc.
What is the difference between UNION and JOIN in SQL?
Answer: UNION is used to combine the result sets of two or more SELECT statements, while JOIN is used to retrieve data from two or more tables based on a related column between them.
Explain the concept of a deadlock in DBMS.
Answer: A deadlock occurs when two or more transactions are unable to proceed because each is waiting for the other to release a lock.
What is the purpose of the COMMIT and ROLLBACK statements?
Answer: COMMIT is used to save the changes made during the current transaction, and ROLLBACK is used to undo the changes made during the current transaction.
What is the difference between a heap table and a clustered table?
Answer: A heap table is a table without a clustered index, and its data is stored in an unordered structure. A clustered table, on the other hand, has a clustered index, and its data is stored in the order of the index.
Explain the concept of a NoSQL database.
Answer: NoSQL databases are non-relational databases that provide a mechanism for storage and retrieval of data in a format other than the tabular relations used in relational databases.
What is the CAP theorem in the context of distributed databases?
Answer: The CAP theorem states that it is impossible for a distributed database system to simultaneously provide all three of the following guarantees: Consistency, Availability, and Partition tolerance.
What is data denormalization?
Answer: Data denormalization is the process of introducing redundancy into a table by incorporating data from related tables. It is done to improve query performance.
Explain the concept of data warehousing.
Answer: Data warehousing involves the collection, storage, and management of data from various sources to provide meaningful business insights and decision support.
What is the purpose of the HAVING clause in SQL?
Answer: The HAVING clause is used in SQL to filter the results of a GROUP BY clause based on a specified condition.
Explain the concept of a composite key.
Answer: A composite key is a combination of two or more columns in a database table that uniquely identifies each record in the table.
What is the role of an index in a database?
Answer: An index in a database is used to speed up the retrieval of rows from a table by creating a separate data structure that allows for faster data access.
What is the purpose of the DDL (Data Definition Language) in SQL?
Answer: DDL is used to define the structure of a database, including creating, altering, and deleting tables and defining constraints.
Explain the concept of referential integrity.
Answer: Referential integrity is a property of a relational database that ensures relationships between tables remain consistent. It is enforced using primary and foreign key constraints.
What is the purpose of the SQL SELECT statement?
Answer: The SQL SELECT statement is used to retrieve data from one or more tables in a database. It can be used to specify the columns to retrieve, the conditions for retrieval, and more.
What is the difference between a left join and an inner join?
Answer: A left join returns all rows from the left table and the matching rows from the right table. An inner join returns only the matching rows from both tables.
Explain the concept of a surrogate key.
Answer: A surrogate key is a system-generated key used as the primary key for a table. It is often an artificial or synthetic key and does not carry any business meaning.
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.
Explain the concept of data concurrency in a database.
Answer: Data concurrency refers to the simultaneous access and modification of the same data by multiple transactions. It needs to be managed to ensure data consistency.
What is a database schema?
Answer: A database schema is a collection of database objects, including tables, views, indexes, and more. It defines the structure of the database.
Explain the concept of a materialized view.
Answer: A materialized view is a database object that contains the result set of a query and is stored physically on disk. It is periodically refreshed to reflect changes in the underlying data.
What is a subquery in SQL?
Answer: A subquery is a query embedded within another query. It is used to retrieve data that will be used by the main query as a condition or for comparison.
Explain the concept of a database transaction.
Answer: A database transaction is a sequence of one or more SQL statements executed as a single unit. It ensures the consistency and integrity of the database.
What is the role of the SQL WHERE clause?
Answer: The SQL WHERE clause is used to filter the results of a query based on a specified condition. It is used in conjunction with the SELECT, UPDATE, and DELETE statements.