Most Asked Infosys Database Management System (DBMS & SQL) Interview Questions

Mastering SQL: A Comprehensive Guide for Beginners

In today's data-driven world, understanding databases is crucial. SQL, the Structured Query Language, is the cornerstone of database management. This comprehensive guide will walk you through the fundamentals of SQL, empowering you to effectively manage and manipulate data.

What is DBMS and how is it different from RDBMS?

A Database Management System (DBMS) is a software application designed to manage and interact with databases. It allows users to create, maintain, access, and update data efficiently. Key functionalities include data definition (creating database structures), data manipulation (inserting, updating, deleting, retrieving data), data security (controlling access and permissions), and data integrity (ensuring data accuracy and consistency).

A Relational Database Management System (RDBMS) is a specific type of DBMS that utilizes the relational model to organize data. This model uses tables with rows (records) and columns (attributes) to represent data and relationships between them. Key differences between DBMS and RDBMS include: RDBMS adhere to strict relational principles (e.g., normalization), whereas general DBMS systems might use other data models. RDBMS excel at structured data management, while some DBMS cater to semi-structured or unstructured data. Examples of RDBMS include MySQL, PostgreSQL, Oracle, and SQL Server; while examples of non-relational DBMS include MongoDB (NoSQL) and Apache Cassandra.

Explain primary key, foreign key, and candidate key.

In relational databases, keys are crucial for maintaining data integrity and relationships between tables. A primary key is a unique identifier for each record in a table. It guarantees that every row is distinguishable and prevents duplicate entries. A primary key must contain only unique values, and cannot contain NULL values.

A candidate key is any attribute or set of attributes that can act as a primary key. A table may have multiple candidate keys, but only one can be designated as the primary key. Choosing the most suitable attribute as primary key often involves selecting an attribute that is both unique and relatively stable.

A foreign key establishes a link between two tables. It's a column in one table that refers to the primary key of another table, thereby creating a relationship. Foreign keys ensure referential integrity, meaning that you can't have a value in a foreign key that doesn't exist as a primary key in the related table. For example, in an "Orders" table, the "CustomerID" could be a foreign key referencing the "Customers" table's "CustomerID" primary key.

What is normalization? Explain 1NF, 2NF, and 3NF with examples.

Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. This is achieved by strategically dividing larger tables into smaller ones and defining relationships between them. There are several normal forms, each representing a higher degree of normalization.

1NF (First Normal Form): Eliminate repeating groups of data within a table. Each column should contain atomic values (indivisible values). For instance, if you have a table with multiple phone numbers in a single column, you should split it into multiple columns (phone1, phone2, etc.), each containing a single phone number.

2NF (Second Normal Form): Must first be in 1NF. Eliminate redundant data caused by partial dependencies. Partial dependency occurs when a non-key attribute is dependent on only part of the primary key (if the primary key is composite). For example, if you have an 'orders' table with 'orderID', 'productID', and 'productName', 'productName' is partially dependent on 'productID' and can be removed to a separate 'products' table.

3NF (Third Normal Form): Must first be in 2NF. Eliminate redundant data caused by transitive dependencies. A transitive dependency exists when a non-key attribute depends on another non-key attribute rather than the primary key directly. For example, consider a 'employees' table with 'employeeID', 'departmentID', and 'departmentName'. 'departmentName' transitively depends on 'departmentID', therefore it should be in a separate 'department' table.

What are ACID properties of a transaction?

ACID properties are crucial for maintaining data integrity and consistency in database transactions. A transaction is a sequence of operations performed as a single logical unit of work. If any part of the transaction fails, the entire transaction is rolled back to maintain data integrity. The ACID properties are:

• Atomicity: The entire transaction is treated as a single, indivisible unit. Either all operations within the transaction are completed successfully, or none are. No partial changes are allowed.
• Consistency: The transaction maintains the integrity constraints of the database. It ensures that the database remains in a valid state before and after the transaction.
• Isolation: Concurrent transactions are isolated from each other. Each transaction behaves as if it is the only transaction executing. Different isolation levels (read uncommitted, read committed, repeatable read, serializable) define the degree of isolation.
• Durability: Once a transaction is committed, its changes are permanently stored in the database and survive even system failures.

Difference between delete, truncate, and drop commands in SQL.

These three commands manipulate tables but have distinct functions:

• DELETE: Removes rows from a table based on a specified condition. It's possible to delete specific rows using a WHERE clause, and if no condition is specified, all rows will be removed. The table structure remains intact.
• TRUNCATE: Removes all rows from a table quickly. It is a faster operation than DELETE, as it doesn't log each row deletion individually. The table structure remains intact, though indexes and related constraints are reset.
• DROP: Completely removes a table and all its associated data and structure. Once dropped, the table ceases to exist, and its data cannot be recovered without a backup.

What are joins in SQL? Explain inner join, left join, and right join.

Joins are used to combine rows from two or more tables based on a related column between them. They are essential for retrieving data from multiple tables simultaneously. There are several types of joins, including:

• INNER JOIN: Returns only the rows where the join condition is met in both tables. If a row in one table doesn't have a matching row in the other table based on the join condition, it's not included in the result.
• LEFT (OUTER) JOIN: Returns all rows from the left table (the table specified before LEFT JOIN), even if there's no match in the right table. For rows in the left table without a match, the columns from the right table will have NULL values.
• RIGHT (OUTER) JOIN: Returns all rows from the right table (the table specified after RIGHT JOIN), even if there's no match in the left table. For rows in the right table without a match, the columns from the left table will have NULL values.

What is a stored procedure? How is it different from a function?

A stored procedure is a pre-compiled set of SQL statements stored in a database. These procedures can be called and reused by applications. Stored procedures offer benefits including improved performance (due to pre-compilation), enhanced security (by controlling access to underlying data), and simplified application development (by encapsulating complex logic). Stored procedures can modify data (insert, update, delete) and perform other database operations.

A function is similar to a stored procedure but primarily differs in that it returns a single value. Functions are typically used to perform calculations or retrieve specific data, as opposed to altering the database as extensively as stored procedures might.

What are triggers in SQL? Give an example use case.

Triggers are procedural code automatically executed in response to specific events on a particular table or view in a database. These events can include INSERT, UPDATE, or DELETE operations. Triggers can perform actions such as data validation, logging changes, and enforcing business rules. They are especially useful for maintaining data integrity and audit trails.

Example Use Case: Imagine an inventory management system. You could create a trigger on the 'Products' table that automatically updates the inventory quantity when a product is sold (a DELETE operation on the 'Orders' table). The trigger would decrement the quantity of the sold product in the 'Products' table, ensuring data consistency between orders and inventory.

Explain indexing and its types.

Indexing is a data structure technique used to speed up data retrieval operations. Indexes create pointers to data within a table, allowing the database system to quickly locate specific rows based on specified columns without having to scan the entire table. Similar to the index at the back of a book, database indexes facilitate faster lookups. Several types of indexes exist, including:

• B-tree index: A balanced tree data structure suitable for various search operations, supporting both range queries (e.g., finding all records between two values) and equality queries (e.g., finding records with a specific value).
• Hash index: Uses a hash function to map keys to their locations; efficient for equality searches, but not suitable for range queries.
• Full-text index: Optimized for searching text data based on keywords, allowing efficient search for documents containing particular words or phrases.

Difference between SQL and NoSQL databases.

SQL (relational) and NoSQL (non-relational) databases differ significantly in their data model, scalability, and application use cases.

SQL Databases: Employ a relational data model, organizing data into tables with rows and columns. They excel at managing structured data with well-defined relationships and require a schema (predefined structure) before data entry. SQL databases often prioritize data consistency and integrity. Examples include MySQL, PostgreSQL, Oracle, and SQL Server.

NoSQL Databases: Use various data models (key-value, document, graph, column-family) to store and manage data, making them more flexible than SQL databases for handling semi-structured and unstructured data. They excel in scalability and performance with large datasets but might offer less data integrity than relational databases. Examples include MongoDB, Cassandra, Redis, and Neo4j.

What is CAP theorem in databases?

The CAP theorem, also known as Brewer's theorem, states that in a distributed database system, you can only simultaneously guarantee two out of the following three properties:

• Consistency: All nodes see the same data at the same time. Every read receives the most recent write or an error.
• Availability: Every request receives a response, without guarantee that it contains the most recent write.
• Partition tolerance: The system continues to operate despite network partitions. This is typically considered a necessity in distributed systems.

Choosing a database often involves trade-offs based on the CAP theorem. For instance, a system prioritizing consistency and partition tolerance might sacrifice availability during a network partition (e.g., a read operation might fail to ensure consistency).

Conclusion

This comprehensive guide has introduced fundamental SQL concepts crucial for database management. Mastering SQL empowers you to interact with databases effectively, analyze data, and build robust applications. Continue exploring advanced SQL techniques and different database systems to broaden your expertise in this critical area of technology.

``` ```