Database Interview Question

Top 50 popular database interview questions along with answers and examples:

1. What is a database?

   - Answer: A database is a structured collection of data that is organized and stored in a way that enables efficient retrieval, updating, and management. It typically includes tables, relationships, and constraints.

2. What is SQL?

   - Answer: SQL (Structured Query Language) is a programming language used for managing and manipulating relational databases. It includes commands like SELECT, INSERT, UPDATE, and DELETE.

3. Explain the difference between SQL and NoSQL databases.

   - Answer: SQL databases are relational and use a structured schema, while NoSQL databases are non-relational and have a flexible or schema-less data model. SQL databases are suitable for structured data, and NoSQL databases are more flexible for unstructured or semi-structured data.

4. What is normalization, and why is it important?

   - Answer: Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. It involves dividing large tables into smaller, related tables. It is important because it minimizes data duplication and ensures consistent data.

5. Explain the concept of a primary key.

   - Answer: A primary key is a unique identifier for each record in a table. It ensures that each row in the table is uniquely identified and serves as a reference point for establishing relationships with other tables.

6. What is an index in a database?

   - Answer: An index is a data structure that improves the speed of data retrieval operations on a database table. It is created on one or more columns of a table and provides a quick lookup mechanism.

7. What is a foreign key?

   - Answer: A foreign key is a column or set of columns in a database table that refers to the primary key of another table. It establishes a link between the two tables, enforcing referential integrity.

8. Explain the ACID properties in database transactions.

   - Answer: ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties ensure the reliability and integrity of database transactions. Atomicity ensures that transactions are treated as a single unit, Consistency ensures that the database remains in a valid state, Isolation prevents interference between transactions, and Durability guarantees that committed transactions persist even after a system failure.

9. What is the difference between a stored procedure and a function?

   - Answer: Both stored procedures and functions are database objects that contain a set of SQL statements. However, a stored procedure does not return a value, while a function returns a value. Functions can be used in SELECT statements, whereas stored procedures cannot.

10. Explain the concept of a view in a database.

- 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 result of a query as a table. Views can be used to simplify complex queries, restrict access to certain columns, or join tables.

11. What is the difference between DELETE and TRUNCATE in SQL?

- Answer: DELETE is used to remove rows from a table based on a condition, and it can be rolled back. TRUNCATE removes all rows from a table, and it cannot be rolled back. TRUNCATE is faster than DELETE.

12. Explain the concept of a join in SQL.

- Answer: A join in SQL is used to combine rows from two or more tables based on a related column between them. Common types of joins include INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.

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

- Answer: The GROUP BY clause in SQL is used to group rows that have the same values in specified columns. It is often used with aggregate functions like COUNT, SUM, AVG, etc., to perform calculations on grouped data.

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

- Answer: A clustered index determines the physical order of data rows in a table, and there can be only one per table. A non-clustered index does not affect the physical order and can be created on multiple columns.

15. Explain the concept of a transaction in a database.

- Answer: A transaction is a sequence of one or more SQL statements that are executed as a single unit of work. It follows the ACID properties and ensures that all operations within the transaction are completed successfully or none are completed.

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

- Answer: The HAVING clause in SQL is used in conjunction with the GROUP BY clause to filter the results of a grouped query based on a specified condition. It is similar to the WHERE clause but operates on aggregated data.

17. What is the role of the ORDER BY clause in SQL?

- Answer: The ORDER BY clause in SQL is used to sort the result set of a query in ascending or descending order based on one or more columns. It is often used with SELECT statements to control the presentation of data.

18. Explain the concept of an SQL injection.

- Answer: SQL injection is a type of security vulnerability where an attacker inserts malicious SQL code into a query. This can manipulate the database, extract sensitive information, or execute unintended commands.

19. What is the purpose of the COMMIT and ROLLBACK statements in SQL?

- Answer: The COMMIT statement in SQL is used to permanently save the changes made during a transaction. The ROLLBACK statement is used to undo the changes and restore the database to its state before the transaction started.

20. Explain the concept of a subquery in SQL.

- Answer: A subquery in SQL is a query nested inside another query. It can be used within SELECT, FROM, WHERE, and other clauses to retrieve data that will be used in the main query. Subqueries can be correlated or non-correlated.

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

- Answer: UNION is used to combine the result sets of two or more SELECT statements, removing duplicates. UNION ALL also combines result sets but includes all rows, including duplicates.

22. Explain the concept of database normalization and its different forms.

- Answer: Database normalization is the process of organizing data to reduce redundancy and dependency by dividing tables into smaller, related tables. Forms of normalization include First Normal Form (1NF), Second Normal Form (2NF), and Third Normal Form (3NF).

23. What is an SQL trigger, and when would you use it?

- Answer: An SQL trigger is a set of instructions that are automatically executed (or "triggered") in response to certain events on a particular table or view. Triggers are used to enforce business rules, perform validations, or log changes.

24. Explain the concept of a stored procedure in SQL.

- Answer: A stored procedure is a precompiled set of one or more SQL statements that can be executed by calling the procedure's name. Stored procedures can accept parameters, perform actions, and return results.

25. What is the purpose of the SQL NULL value?

- Answer: The SQL NULL value represents an unknown or missing data value in a table. It is not the same as an empty string or zero. NULL is used to indicate that a data value does not exist in the database.

26. Explain the concept of database denormalization and when it might be useful.

- Answer: Database denormalization is the process of intentionally introducing redundancy into a database by combining tables or adding redundant data. It is used to improve query performance in situations where read operations significantly outnumber write operations.

27. What is the role of the SQL LIKE operator, and how is it used?

- Answer: The SQL LIKE operator is used to search for a specified pattern in a column. It is often used with wildcard characters (% for multiple characters, _ for a single character) to perform pattern matching.

28. Explain the concept of an outer join in SQL.

- Answer: An outer join in SQL returns all rows from one table and the matched rows from another table. It can be LEFT OUTER JOIN, RIGHT OUTER JOIN, or FULL OUTER JOIN, depending on which table's rows are included.

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 return different values or perform different actions based on specified conditions.

30. Explain the concept of database indexing and its advantages.

- Answer: Database indexing is the process of creating an index on one or more columns of a table to improve the speed of data retrieval operations. Indexing enhances query performance by facilitating quicker data access.

31. What is the difference between a primary key and a unique key in SQL?

- Answer: Both primary key and unique key enforce uniqueness, but a table can have only one primary key, whereas it can have multiple unique keys. Additionally, the primary key column(s) cannot contain NULL values.

32. Explain the concept of a self-join in SQL.

- Answer: A self-join is a SQL operation where a table is joined with itself. This can be useful when working with hierarchical or recursive relationships within the same table.

33. What is the purpose of the SQL GROUP_CONCAT() function?

- Answer: The GROUP_CONCAT() function is used to concatenate values from multiple rows into a single string, grouped by a specified column. It is often used in conjunction with GROUP BY to aggregate data.

34. Explain the difference between a candidate key, a primary key, and a foreign key.

- Answer: A candidate key is a column or set of columns that could be used as the primary key. The primary key is the chosen candidate key used to uniquely identify each record. A foreign key is a column that establishes a link between two tables.

35. What is the purpose of the SQL JOIN clause, and how does it work?

- Answer: The SQL JOIN clause is used to combine rows from two or more tables based on a related column between them. There are different types of joins, including INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.

36. Explain the concept of database sharding and when it is beneficial.

- Answer: Database sharding is the practice of breaking a large database into smaller, more manageable pieces called shards. Each shard is stored on a separate server. Sharding is beneficial for improving scalability and distributing the workload.

37. What is the purpose of the SQL TRIGGER statement?

- Answer: The SQL TRIGGER statement is used to specify a set of actions to be performed automatically when a specified event (such as INSERT, UPDATE, DELETE) occurs on a particular table or view.

38. Explain the concept of a surrogate key in database design.

- Answer: A surrogate key is a unique identifier assigned to each record in a table, typically as an integer or a system-generated value. It is used as a primary key and has no business meaning, serving only for identification purposes.

39. What is the purpose of the SQL DISTINCT keyword?

- Answer: The SQL DISTINCT keyword is used to retrieve unique values from a specified column in a table. It eliminates duplicate rows from the result set.

40. Explain the concept of database transactions and the use of the BEGIN TRANSACTION, COMMIT, and ROLLBACK statements.

- Answer: A database transaction is a sequence of one or more SQL statements that are executed as a single unit of work. The BEGIN TRANSACTION statement marks the beginning of a transaction, COMMIT saves the changes, and ROLLBACK undoes the changes if an error occurs.

41. What is the purpose of the SQL UNION operator?

- Answer: The SQL UNION operator is used to combine the result sets of two or more SELECT statements into a single result set. It removes duplicate rows by default.

42. Explain the concept of database views.

- Answer: A database view is a virtual table based on the result of a SELECT query. It does not store data itself but provides a way to represent the result of a query as a table. Views can be used to simplify complex queries, restrict access to certain columns, or join tables.

43. What is the purpose of the SQL CASCADE option in foreign key constraints?

- Answer: The CASCADE option in foreign key constraints is used to automatically propagate changes in the primary key to the foreign key. For example, if a referenced primary key is updated, the change is cascaded to the corresponding foreign key.

44. Explain the concept of the SQL BETWEEN operator.

- Answer: The SQL BETWEEN operator is used to filter the result set based on a range of values. It is often used in the WHERE clause to specify a range for a column.

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

- Answer: The SQL EXISTS operator is used to test whether a subquery returns any rows. If the subquery returns at least one row, the EXISTS condition is considered true.

46. Explain the concept of database indexing and its types.

- Answer: Database indexing is the process of creating an index on one or more columns of a table to improve the speed of data retrieval operations. Types of indexing include clustered and non-clustered indexes.

47. What is the purpose of the SQL RANK() function?

- Answer: The SQL RANK() function is used to assign a rank to each row within the result set based on the values of one or more columns. It is often used in conjunction with the ORDER BY clause.

48. Explain the concept of database constraints.

- Answer: Database constraints are rules that are applied to columns or tables to ensure the integrity of the data. Common constraints include PRIMARY KEY, UNIQUE, NOT NULL, CHECK, and FOREIGN KEY.

49. What is the purpose of the SQL GROUPING SETS clause?

- Answer: The SQL GROUPING SETS clause is used to define multiple grouping sets within a single query. It allows you to perform aggregations at different levels in a single result set.

50. Explain the concept of the SQL COALESCE function.

- Answer: The SQL COALESCE function is used to return the first non-null expression in a list. It is often used to provide a default value when dealing with null values.