Home

Top 10 Database Interview Questions

Here are some Top 10 database interview questions, along with brief explanations and examples:

  1. What is a database and how is it different from a spreadsheet?

A database is a structured collection of data that is stored and accessed electronically, while a spreadsheet is a program used to organize and analyze data in tabular form. In general, databases are better suited for managing large amounts of data, while spreadsheets are better for small data sets or simple data analysis.

2. What is the purpose of normalization in a database?

Normalization is the process of organizing data in a relational database to minimize data redundancy and improve data integrity. It involves breaking down a table into smaller, more specific tables, and linking them together through relationships, in order to prevent data inconsistencies and ensure that each piece of data is stored in only one place.

Example: 

CREATE TABLE Order (
OrderID INT PRIMARY KEY,
CustomerID INT NOT NULL,
OrderDate DATETIME,
...
)

3. What is a primary key in a database?

A primary key is a unique identifier for each row in a table, used to enforce referential integrity and to improve the performance of database queries. For example, in a table of employees, the employee ID might be used as a primary key, to ensure that each employee has a unique identifier and that the data for each employee is stored in only one row.

Example:

CREATE TABLE Employee (
EmployeeID INT PRIMARY KEY,
LastName VARCHAR(255) NOT NULL,
FirstName VARCHAR(255),
Address VARCHAR(255),
City VARCHAR(255),
...
)

4. How does indexing work in a database?

Indexing is a method of improving the performance of database queries by creating a separate data structure that allows the database software to quickly locate specific rows in a table. Indexing is typically done on the columns that are used most frequently in WHERE clauses of queries.

Example:

CREATE INDEX idx_LastName ON Employee (LastName);

5. What is a foreign key in a database?

A foreign key is a column or set of columns in one table that refers to the primary key of another table. It creates a relationship between the two tables, which allows data in one table to be related to data in another table. This enables us to maintain referential integrity in our data.

Example:

CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
ProductID INT NOT NULL,
Quantity INT NOT NULL,
...
FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
)

6. What is a SQL join and how does it work?

A SQL join is a method of combining data from two or more tables in a relational database. It is used to retrieve data from multiple tables as if they were a single table. An example would be “SELECT * FROM table1 JOIN table2 ON table1.id = table2.id” which retrieves all columns from both table1 and table2, where the id columns are equal.

Example:

SELECT * FROM Employee
JOIN Orders ON Employee.EmployeeID = Orders.EmployeeID;

7. What is a stored procedure and why is it used?

A stored procedure is a precompiled collection of SQL statements that are stored in a database and can be executed as a single unit. They are typically used to perform complex or repetitive operations, and are especially useful for tasks that are executed frequently or require a high level of security.

Stored procedures are executed on the database server, which can reduce the amount of data that needs to be sent between the application and the database, and thus improve performance. They also provide a way to encapsulate complex logic or business rules, so that they can be easily reused across multiple applications or parts of an application.

Here’s an example of a simple stored procedure that retrieves a list of orders for a given customer:

CREATE PROCEDURE GetOrdersByCustomerID(IN customerID INT)
BEGIN
    SELECT * FROM Orders WHERE CustomerID = customerID;
END

You can then execute this stored procedure by calling it like so:

CALL GetOrdersByCustomerID(100);

You can also use stored procedures to perform operations that update, insert or delete data on the table like :

CREATE PROCEDURE UpdateEmployee (@id INT, @name VARCHAR(50))
AS
BEGIN
    UPDATE Employee SET name = @name WHERE id = @id
END

This stored procedure takes two parameters: an employee ID and a new name, and updates the employee’s name in the database.

In general, stored procedures offer several benefits, such as security, maintainability, and performance optimization, therefore they are widely used in database management systems to improve the overall efficiency and security of database operations.

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

A clustered index is a type of index that physically reorders the data rows in a table to match the indexed column or columns. A table can have only one clustered index, as the data can be physically stored in only one order.

A non-clustered index, on the other hand, uses a separate data structure to store the index data, rather than modifying the actual data rows. A table can have multiple non-clustered indexes, each with a different column or set of columns as the index key.

Here’s an example of creating a clustered index on the “id” column of a table “Employee”:

CREATE CLUSTERED INDEX idx_Employee_id ON Employee (id)

The above query creates a clustered index on the “id” column of “Employee” table. This will physically reorder the data rows in the table to match the index, so that the data is stored in the same order as the index keys.

Here’s an example of creating a non-clustered index on the “LastName” column of a table “Employee”:

CREATE INDEX idx_Employee_LastName ON Employee (LastName)

The above query creates a non-clustered index on the “LastName” column of the “Employee” table. This creates a separate data structure that the database can use to quickly locate rows with a specific last name, without modifying the physical order of the data rows in the table.

Clustered indexes are useful for improving the performance of queries that need to retrieve a range of rows based on the indexed column, such as queries with a WHERE clause that includes a range of values.

Non-clustered indexes are useful for improving the performance of queries that need to retrieve a specific row based on the indexed column, such as queries with a WHERE clause that includes an equality comparison.

9. What is a transaction and why is it important?

A transaction in a database is a sequence of one or more SQL statements that are executed as a single unit of work, either all committed or all rolled back. Transactions are used to ensure that a set of statements are executed atomically, which means that they are executed completely or not at all.

The importance of transactions lies in the fact that they ensure data consistency and integrity. They allow multiple statements to be executed as a single logical operation, which ensures that the database remains in a consistent state even if an error occurs during the execution of one of the statements.

For example, consider a scenario where a user transfers money from one bank account to another. A transaction would be used to ensure that the following two statements are executed atomically:

UPDATE bank_accounts SET balance = balance - 1000 WHERE account_number = 12345;
UPDATE bank_accounts SET balance = balance + 1000 WHERE account_number = 67890;

If the first statement executes successfully and the second one does not due to any error, the first statement’s change will be undone so that the overall balance remains consistent.

A transaction can be started using the “BEGIN TRANSACTION” statement and committed using the “COMMIT” statement. And if there is any error occurred it can be rolled back using “ROLLBACK” statement.

In summary, transactions are a crucial aspect of database management systems, they ensure that data remains consistent and accurate, even in the presence of errors or failures, and ensure that a set of statements are executed as a single atomic operation.

10. What is a trigger in Database and why they are used ?

A trigger is a set of instructions that are automatically executed in response to a specific event in a database, such as an INSERT, UPDATE, or DELETE statement on a particular table. Triggers are used to enforce certain rules or constraints on the data in a database or to perform additional actions when a certain event occurs.

For example, a trigger might be used to automatically update a column in a table every time a row is updated or to automatically enforce referential integrity by canceling an update or delete statement if it would violate a foreign key constraint.

Here is an example of how you can create a trigger in SQL:

CREATE TRIGGER tr_example
AFTER UPDATE ON example_table
FOR EACH ROW
BEGIN
   IF NEW.value < 0 THEN
      SET NEW.value = 0;
   END IF;
END;

This trigger is created on a table example_table and is set to execute after an update statement on this table. The FOR EACH ROW clause means that this trigger will be activated for each row that’s affected by the update statement. Here the trigger is checking if the new value of the value column is less than 0, if it’s true it’s setting the new value to 0.

Triggers can also be used to call a stored procedure, update another table, or even perform complex logic depending on the requirement. Though one should be careful while using triggers as they can have adverse effects on the performance of the system and their usage should be well thought and justified.

Here are some Top 10 database interview questions, along with brief explanations and examples: A database is a structured collection of data that is stored and accessed electronically, while a spreadsheet is a program used to organize and analyze data in tabular form. In general, databases are better suited for…

Here are some Top 10 database interview questions, along with brief explanations and examples: A database is a structured collection of data that is stored and accessed electronically, while a spreadsheet is a program used to organize and analyze data in tabular form. In general, databases are better suited for…

Leave a Reply

Your email address will not be published. Required fields are marked *