Interview Q&A

• LEFT JOIN: Returns all records from the left table, and matched records from the

right table. If there's no match, NULL values are returned for the right table's

columns.

• RIGHT JOIN: Returns all records from the right table, and matched records from the

left table. If there's no match, NULL values are returned for the left table's columns.

The UNION ALL operator combines the result sets of two or more queries, but unlike

UNION, it does not remove duplicate rows.

Example:

SELECT name FROM employees

UNION ALL

SELECT name FROM contractors;

This will return all names from both employees and contractors, including duplicates.

subquery in the FROM clause allows you to treat the result of a query as a temporary table,

which can then be joined or queried further.

Example:

SELECT avg_salary

FROM (SELECT AVG(salary) AS avg_salary FROM employees) AS avg_table;

This query calculates the average salary using a subquery in the FROM clause.

Views

view in SQL is a virtual table that is defined by a SELECT query. It does not store data

itself but rather provides a way to view and work with the results of a query as if it were a

table. Views allow users to access specific, formatted data without modifying the underlying

tables directly.

• Purpose: Simplify complex queries, abstract the database schema, and improve

security by exposing only the necessary data.

Example:

CREATE VIEW employee_view AS

SELECT id, name, department

FROM employees;

You can then query the view like a regular table:

SELECT * FROM employee_view;

n indexed view (also called a materialized view) in SQL Server is a view that has a

unique clustered index created on it. This results in the view storing the query results

physically in the database, similar to a table. It is useful for improving performance when you

have complex queries that aggregate data, as the results of the view are precomputed and

stored.

• Benefits:
• Faster read performance for complex aggregations or queries.
• Reduces the need for recomputing the result of a complex query each time

the view is accessed.

• Downside:
• Insert, update, or delete operations on the underlying tables will incur

dditional overhead because the indexed view must also be updated.

Example:

CREATE VIEW employee_summary AS

SELECT department, AVG(salary) AS avg_salary

FROM employees

GROUP BY department;

• - Create a clustered index on the view

CREATE UNIQUE CLUSTERED INDEX idx_employee_summary

ON employee_summary(department);

nd query data without modifying the underlying tables directly.

• Views do not support procedural logic, loops, or variables.
• Stored Procedure:
• A stored procedure is a set of SQL statements that can be executed as a

single unit. It can include complex logic like loops, conditionals, and multiple

queries.

• Stored procedures can perform INSERT, UPDATE, DELETE, and other

operations on the database. They can also return results.

Example of a stored procedure:

CREATE PROCEDURE GetEmployeeSalary(IN emp_id INT)

BEGIN

SELECT salary FROM employees WHERE id = emp_id;

END;

• View:
• A view is a virtual table defined by a SELECT query. It provides a way to view

and query data without modifying the underlying tables directly.

• Views do not support procedural logic, loops, or variables.
• Stored Procedure:
• A stored procedure is a set of SQL statements that can be executed as a

single unit. It can include complex logic like loops, conditionals, and multiple

queries.

• Stored procedures can perform INSERT, UPDATE, DELETE, and other

operations on the database. They can also return results.

Example of a stored procedure:

CREATE PROCEDURE GetEmployeeSalary(IN emp_id INT)

BEGIN

SELECT salary FROM employees WHERE id = emp_id;

END;

stored procedure is a precompiled collection of SQL statements that can be executed as

single unit. It allows you to encapsulate logic, such as data manipulation, complex queries,

or repetitive tasks, into reusable blocks. Stored procedures can accept parameters, perform

ctions like SELECT, INSERT, UPDATE, and DELETE, and return results.

• Benefits: Improved performance (since the procedure is precompiled), code

reusability, centralized logic, and security (by restricting direct access to tables).

Example:

CREATE PROCEDURE GetEmployeeDetails (IN emp_id INT)

BEGIN

SELECT * FROM employees WHERE id = emp_id;

END;

• Function:
• Return Type: Always returns a value (can be scalar or table).
• Used in Queries: Can be used in SELECT, WHERE, and ORDER BY clauses.
• Side Effects: Typically designed to perform calculations or return a result

without modifying the database.

• Stored Procedure:
• No Return Type: May or may not return values (using output parameters or

result sets).

• Used for Actions: Typically used for performing database operations (like

INSERT, UPDATE, DELETE).

• Side Effects: Designed to perform operations that modify data or perform

business logic.

Example:

Function (returns a value):

CREATE FUNCTION GetEmployeeSalary (emp_id INT) RETURNS DECIMAL

BEGIN

RETURN (SELECT salary FROM employees WHERE id = emp_id);

END;

• Stored Procedure (performs an action):

CREATE PROCEDURE UpdateEmployeeSalary (IN emp_id INT, IN new_salary

DECIMAL)

BEGIN

UPDATE employees SET salary = new_salary WHERE id = emp_id;

END;

• Stored Procedures: The RETURN keyword in a stored procedure is typically used to

exit the procedure and can optionally return an integer value (usually indicating the

success or failure of the procedure). The return value is often used for error handling

or status reporting.

Indexing

n index in SQL is a database object that improves the speed of data retrieval operations

on a table. It works by maintaining a sorted order of column values in a separate structure,

llowing for faster search and retrieval compared to scanning every row in the table. The

index essentially creates a quick lookup table to find specific values.

Example of an index creation:

CREATE INDEX idx_column_name ON table_name(column_name);

Indexes are particularly useful for queries that involve WHERE, JOIN, ORDER BY, and GROUP

BY clauses.

• Clustered Index:
• The data in the table is physically sorted based on the clustered index.
• Each table can have only one clustered index (usually the primary key).
• Fast for queries that involve range-based retrieval (e.g., BETWEEN or ORDER

BY).

• Non-Clustered Index:
• The data in the table is not physically sorted. The non-clustered index is

stored separately from the actual data.

• A table can have multiple non-clustered indexes.
• Faster for point lookups (single value queries) but less efficient for

range-based searches.

Indexes can slow down data modification operations:

• INSERT: When a new row is inserted, the index must also be updated to include the

new value, adding extra overhead.

• UPDATE: If the indexed column is updated, the index must be modified to reflect the

new value, which can be slower.

• DELETE: When a row is deleted, the corresponding index entries must be removed,

causing additional overhead.

While indexes speed up SELECT queries, they can reduce the performance of INSERT,

UPDATE, and DELETE operations due to the extra work needed to maintain the index.

composite index is an index that is created on multiple columns. It is used when a query

filters or sorts based on more than one column. Composite indexes are especially useful

when queries frequently involve conditions on multiple columns.

• Example Use Case: If a query filters by both last_name and first_name,

creating a composite index on (last_name, first_name) will speed up the

query.

Example of creating a composite index:

CREATE INDEX idx_lastname_firstname ON employees(last_name,

first_name);

• Unique Index:
• Ensures that the values in the indexed columns are unique.
• Can be created on any column and allows NULL values (in most DBMS).
• A table can have multiple unique indexes.
• Primary Key Index:
• Enforces the uniqueness of the column(s) and also guarantees NOT NULL

constraint on the column(s).

• A table can only have one primary key.
• Automatically creates a unique index.

In short, both enforce uniqueness, but the primary key also guarantees that the column(s)

cannot be NULL.

covering index is an index that contains all the columns needed by a query, meaning the

query can be answered entirely by the index without accessing the actual table data. This

can improve query performance by reducing disk I/O.

Example:

If a query selects id, name, and salary from the employees table and there is an index

on (id, name, salary), the index itself is sufficient to satisfy the query, and the

database doesn't need to access the table at all.

transaction is a sequence of SQL operations that are treated as a single unit of work. A

transaction must either be fully completed (committed) or fully undone (rolled back) to

ensure the integrity of the database. Transactions provide the foundation for the ACID

properties (Atomicity, Consistency, Isolation, Durability) that ensure reliable and consistent

operations.

re saved and visible to other transactions.

• When to use: After the transaction operations have completed successfully and you

want to ensure that the changes are saved to the database.

Example:

BEGIN TRANSACTION;

UPDATE employees SET salary = 5000 WHERE id = 1;

COMMIT;

The COMMIT statement is used to finalize a transaction by making all the changes made

during the transaction permanent. It ensures that all changes made during the transaction

are saved and visible to other transactions.

• When to use: After the transaction operations have completed successfully and you

want to ensure that the changes are saved to the database.

Example:

BEGIN TRANSACTION;

UPDATE employees SET salary = 5000 WHERE id = 1;

COMMIT;

The ROLLBACK statement is used to undo all changes made during a transaction. If an error

occurs or something goes wrong, ROLLBACK ensures that the database is restored to its

state before the transaction began.

• When to use: If any part of the transaction fails or if you wish to cancel the changes

made during the transaction.

Example:

BEGIN TRANSACTION;

UPDATE employees SET salary = 5000 WHERE id = 1;

• - Something goes wrong

ROLLBACK;

SAVEPOINT is a way to set a point within a transaction to which you can later roll back if

necessary. It provides more granular control, allowing partial rollback rather than undoing the

entire transaction.

• When to use: When you want to mark certain stages within a transaction and allow

for partial rollback if an error occurs.

Example:

BEGIN TRANSACTION;

SAVEPOINT sp1;

UPDATE employees SET salary = 5000 WHERE id = 1;

• - Something goes wrong

ROLLBACK TO sp1; -- Rolls back to the savepoint, undoing only the

changes after it

COMMIT;

The isolation level in SQL defines how transactions interact with each other in terms of

visibility of data. The isolation level affects the balance between data consistency and

transaction concurrency.

• READ UNCOMMITTED: Allows dirty reads. One transaction can see uncommitted

changes made by another transaction. This is the lowest level of isolation.

• READ COMMITTED: Prevents dirty reads, but allows non-repeatable reads (data

can change during the transaction).

• REPEATABLE READ: Prevents dirty reads and non-repeatable reads, but phantom

reads (new rows can appear in a query) are still possible.

• SERIALIZABLE: The highest isolation level. Prevents dirty reads, non-repeatable

reads, and phantom reads by making transactions execute sequentially.

Can you explain the different isolation

levels (e.g., READ UNCOMMITTED, READ COMMITTED, REPEATABLE

READ, SERIALIZABLE)?

The isolation level in SQL defines how transactions interact with each other in terms of

visibility of data. The isolation level affects the balance between data consistency and

transaction concurrency.

• READ UNCOMMITTED: Allows dirty reads. One transaction can see uncommitted

changes made by another transaction. This is the lowest level of isolation.

• READ COMMITTED: Prevents dirty reads, but allows non-repeatable reads (data

can change during the transaction).

• REPEATABLE READ: Prevents dirty reads and non-repeatable reads, but phantom

reads (new rows can appear in a query) are still possible.

• SERIALIZABLE: The highest isolation level. Prevents dirty reads, non-repeatable

reads, and phantom reads by making transactions execute sequentially.

• Implicit Transactions: Automatically begin when a database operation is executed.

The DBMS treats each individual statement as a transaction and automatically

commits after each statement, unless explicitly rolled back.

• Explicit Transactions: Transactions that the user manually controls using BEGIN

TRANSACTION, COMMIT, and ROLLBACK. The user decides when the transaction

begins and ends.

Normalization is the process of organizing a database to reduce redundancy and

dependency by dividing large tables into smaller, manageable tables and defining

relationships between them. This process aims to improve the structure of the database by

minimizing the chances of data anomalies (insertion, update, and deletion anomalies).