Database Interview Questions

Here are the top 20 most commonly asked interview questions for SQL and NoSQL, along with their answers:

SQL Interview Questions with Answers

1. What is SQL?

   • Answer: SQL (Structured Query Language) is a standardized programming language used to manage and manipulate relational databases. It is used for tasks such as querying data, updating data, inserting records, and deleting records.

2. What is the difference between SQL and NoSQL?

   • Answer: SQL databases are relational, structured, and use tables to store data, while NoSQL databases are non-relational, flexible, and can store data in a variety of formats such as key-value, document, columnar, or graph formats. SQL is typically used in applications requiring strong consistency, while NoSQL is better suited for unstructured data and large-scale applications.

3. What are the different types of JOINs in SQL?

   • Answer:
     • INNER JOIN: Returns records that have matching values in both tables.
     • LEFT JOIN (OUTER): Returns all records from the left table and matched records from the right table. Unmatched records from the right table will be NULL.
     • RIGHT JOIN (OUTER): Returns all records from the right table and matched records from the left table. Unmatched records from the left table will be NULL.
     • FULL OUTER JOIN: Returns records when there is a match in either the left or right table. Unmatched records will be NULL.
     • CROSS JOIN: Returns the Cartesian product of both tables (every combination of rows from both tables).

4. What is normalization and denormalization?

   • Answer:
     • Normalization is the process of organizing data in a way that reduces redundancy and dependency, typically through multiple tables linked by foreign keys.
     • Denormalization is the process of combining tables to improve read performance at the cost of redundancy and additional storage.

5. What is the difference between WHERE and HAVING?

   • Answer: WHERE is used to filter rows before grouping (used in SELECT, UPDATE, DELETE queries), while HAVING is used to filter groups after using aggregate functions (used with GROUP BY).

6. What are aggregate functions in SQL?

   • Answer: Aggregate functions operate on a set of values to return a single value. Common aggregate functions are:
     • COUNT()
     • SUM()
     • AVG()
     • MAX()
     • MIN()

7. What is a primary key and foreign key?

   • Answer:
     • Primary Key uniquely identifies each record in a table. It must be unique and cannot contain NULL values.
     • Foreign Key is a field in one table that is a primary key in another table, used to create a relationship between the two tables.

8. What is an index?

   • Answer: An index is a database object that improves the speed of data retrieval operations on a table at the cost of additional space and maintenance time. It is created on columns that are frequently searched.

9. What are subqueries and how do they differ from joins?

   • Answer: A subquery is a query nested within another query. It is used to retrieve data that will be used by the outer query. A join combines data from two or more tables based on a related column, whereas a subquery provides a result that is used in another query (SELECT, INSERT, UPDATE, DELETE).

10. 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 helps in encapsulating business logic, improving performance, and enhancing security.

11. What are triggers in SQL?

    • Answer: A trigger is a special type of stored procedure that automatically executes or fires when certain events occur, such as an INSERT, UPDATE, or DELETE operation on a table.

12. What is a transaction in SQL?

    • Answer: A transaction is a sequence of one or more SQL operations that are treated as a single unit. It ensures data consistency using the ACID properties:
      • Atomicity
      • Consistency
      • Isolation
      • Durability

13. Explain the difference between UNION and UNION ALL.

    • Answer:
      • UNION returns unique rows from multiple SELECT queries (removes duplicates).
      • UNION ALL returns all rows, including duplicates.

14. What is a view in SQL?

    • Answer: A view is a virtual table created by a SELECT query that presents data from one or more tables. It does not store data itself but provides a way to simplify complex queries or secure data access.

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

    • Answer:
      • Clustered Index determines the physical order of data in the table and can only have one per table.
      • Non-clustered Index creates a separate object that points to the table’s data and can have multiple indexes per table.

16. What is data integrity and how is it ensured in SQL?

    • Answer: Data integrity ensures the accuracy and consistency of data. It is enforced using constraints like PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, and NOT NULL.

17. What is a schema in SQL?

    • Answer: A schema is a logical container for database objects such as tables, views, and procedures. It is used to organize database elements and manage security.

18. Explain the difference between CHAR and VARCHAR data types.

    • Answer:
      • CHAR is a fixed-length character data type. If the data is shorter than the specified length, it is padded with spaces.
      • VARCHAR is a variable-length character data type, which uses only the necessary space to store the data.

19. What is a composite key?

    • Answer: A composite key is a combination of two or more columns in a table that uniquely identifies a record. It is used when no single column can uniquely identify a row.

20. What are transactions and what is their importance?

    • Answer: A transaction is a sequence of operations performed as a single unit. They are important for ensuring the database's ACID properties, ensuring data consistency and reliability in multi-step operations.

---

NoSQL Interview Questions with Answers

1. What is NoSQL?

   • Answer: NoSQL (Not Only SQL) refers to a class of databases that do not use the traditional relational model. NoSQL databases are flexible, scalable, and can handle unstructured or semi-structured data.

2. What are the types of NoSQL databases?

   • Answer:
     • Key-Value Stores: Store data as key-value pairs (e.g., Redis, DynamoDB).
     • Document Stores: Store data as documents (e.g., MongoDB, CouchDB).
     • Column-Family Stores: Store data in columns rather than rows (e.g., Cassandra, HBase).
     • Graph Databases: Store data as nodes and relationships (e.g., Neo4j).

3. What is the CAP Theorem?

   • Answer: The CAP Theorem states that a distributed database system can provide only two of the following three properties:
     • Consistency: Every read operation will return the most recent write.
     • Availability: Every request will receive a response (either success or failure).
     • Partition Tolerance: The system can handle network partitions.

4. What is sharding in NoSQL databases?

   • Answer: Sharding is the process of distributing data across multiple machines to improve scalability and performance. Each shard contains a portion of the data.

5. What is denormalization in NoSQL?

   • Answer: Denormalization in NoSQL refers to storing data in a way that reduces the need for joins, often by storing repeated or redundant data to optimize read performance.

6. What is the difference between SQL and NoSQL?

   • Answer: SQL databases are relational, table-based, and enforce a fixed schema, whereas NoSQL databases are non-relational, schema-less, and support a variety of data models like key-value, document, column-family, and graph databases.

7. What is a document store in NoSQL?

   • Answer: A document store is a type of NoSQL database that stores data as documents (e.g., JSON, BSON, or XML) rather than rows and columns, making it highly flexible (e.g., MongoDB, CouchDB).

8. What is a key-value store in NoSQL?

   • Answer: A key-value store is a NoSQL database that stores data as key-value pairs, where the key is a unique identifier and the value is the associated data (e.g., Redis, DynamoDB).

9. What is a column-family store?

   • Answer: A column-family store is a type of NoSQL database where data is stored in columns rather than rows. Each column family is a set of rows with a shared schema (e.g., Cassandra, HBase).

10. What is a graph database?

    • Answer: A graph database uses graph structures for semantic queries. Data is stored in nodes (entities) and edges (relationships) with properties (e.g., Neo4j).

11. What is eventual consistency in NoSQL?

    • Answer: Eventual consistency is the consistency

model used in many NoSQL databases, where updates to the data are propagated to all nodes eventually but not necessarily immediately.

1. What is data replication in NoSQL?

   • Answer: Data replication is the process of copying data across multiple nodes or servers to ensure high availability and fault tolerance in NoSQL databases.

2. What is an index in NoSQL databases?

   • Answer: An index in NoSQL databases speeds up data retrieval by creating a reference (lookup table) for specific values, similar to indexing in relational databases.

3. How do you scale NoSQL databases?

   • Answer: NoSQL databases scale horizontally by adding more servers or nodes to handle increased load and distribute data. This is often done via sharding and replication.

4. What is MapReduce in NoSQL?

   • Answer: MapReduce is a programming model used for processing large datasets in parallel. It divides the task into a "Map" step (distribute data) and a "Reduce" step (aggregate results).

5. What are the limitations of NoSQL databases?

   • Answer: NoSQL databases may lack full ACID transactions, making them less suitable for applications requiring strict consistency. They can also struggle with complex queries and relational data models.

6. What is a document in MongoDB?

   • Answer: A document in MongoDB is a record stored in BSON (Binary JSON) format. It is flexible, schema-less, and can contain nested structures.

7. How does CouchDB handle conflicts in distributed systems?

   • Answer: CouchDB uses Multi-Version Concurrency Control (MVCC) and resolves conflicts during replication by using versioning and allowing developers to manually resolve conflicting changes.

8. What is an atomic operation in NoSQL?

   • Answer: An atomic operation ensures that a database operation is completed entirely or not at all, preventing partial updates that could cause data inconsistencies.

9. What is the role of a NoSQL database in big data?

   • Answer: NoSQL databases are ideal for handling big data because they support horizontal scaling, flexible data models, and high availability, making them suitable for processing large volumes of unstructured or semi-structured data.