What is a View?

A view is a virtual table based on the result of a SQL SELECT query. Unlike a table, a view does not store data physically. Instead, it displays data from one or more underlying (base) tables whenever it is queried.

Views are commonly used to simplify complex queries, hide sensitive data, and provide controlled access to specific information.

Interview Answer

"A view is a virtual table created using a SQL SELECT statement. It doesn't store data itself but retrieves data from one or more base tables whenever it is queried. We use views to simplify complex queries, improve security by hiding sensitive columns, and provide users with customized access to data."

Advertisement

Create a View

CREATE VIEW HR_Employees AS
SELECT Name, Salary
FROM Employees
WHERE Department = 'HR';

Retrieve data from the view:

SELECT *
FROM HR_Employees;

How It Works

• The view stores only the SQL query definition.
• Every time you query the view, the database retrieves fresh data from the underlying table.
• Any changes made to the base table are immediately reflected in the view.

Why Use Views?

Views are useful for:

• Simplifying complex SQL queries
• Hiding sensitive columns
• Restricting user access
• Presenting customized data
• Improving code reusability

Real-Time Example

In one project, HR users only needed employee names and salaries. Instead of giving direct access to the Employees table, we created an HR_Employees view that exposed only the required columns.

View vs Table

Note: A normal view becomes invalid if the underlying table is dropped. (Materialized views are an exception because they physically store the query results.)

Indexes and Performance

An index is a database object that improves the speed of data retrieval.

It works like the index of a book. Instead of reading every page, the database jumps directly to the required records.

Interview Answer

"Indexes improve SQL query performance by allowing the database to locate matching records quickly instead of scanning the entire table. They're especially useful for WHERE, JOIN, and ORDER BY operations."

Create an Index

CREATE INDEX idx_name
ON Employees(Name);

Example query:

SELECT *
FROM Employees
WHERE Name = 'Amit';

With an index, this query executes much faster.

How Indexes Work

Most databases implement indexes using B-Trees (Balanced Trees).

Instead of scanning every row:

Table Scan
↓

Read every row
↓

Find matching record

The database uses the index:

Index Lookup
↓

Jump directly to matching records

Advantages of Indexes

• Faster SELECT queries
• Faster searching
• Faster filtering
• Faster sorting
• Better JOIN performance

Disadvantages of Indexes

Indexes also have costs:

• Slower INSERT
• Slower UPDATE
• Slower DELETE
• Additional storage space

Every data modification must also update the index.

Types of Indexes

Real-Time Example

A customer search query was taking several seconds because the Name column wasn't indexed. After creating an index on the Name column, the response time reduced dramatically.

Normalization (1NF, 2NF, 3NF)

Normalization is the process of organizing database tables to reduce data redundancy and improve data integrity.

Instead of storing duplicate information in one large table, normalization divides data into smaller, related tables.

Interview Answer

"Normalization is the process of organizing data to eliminate redundancy and improve consistency. The most commonly used normal forms are 1NF, 2NF, and 3NF."

First Normal Form (1NF)

A table is in 1NF if:

• Every column contains atomic (single) values.
• No repeating groups exist.

Example

❌ Not 1NF

✅ 1NF

Second Normal Form (2NF)

A table is in 2NF if:

• It is already in 1NF.
• Every non-key column depends on the entire primary key.
• Partial dependencies are removed.

Third Normal Form (3NF)

A table is in 3NF if:

• It is already in 2NF.
• There are no transitive dependencies.
• Non-key columns depend only on the primary key.

Summary

Real-Time Example

Customer information and order details were originally stored in a single table. We normalized the database by separating customers and orders into different tables linked through foreign keys, reducing redundancy and improving data consistency.

Stored Procedures

A Stored Procedure is a precompiled collection of SQL statements stored inside the database that can be executed whenever required.

Stored procedures improve performance, increase reusability, and simplify database operations.

Interview Answer

"A stored procedure is a reusable, precompiled SQL program stored in the database. It accepts parameters, executes SQL statements, and can return result sets."

Example

CREATE PROCEDURE GetEmployeeByID(IN emp_id INT)
BEGIN
    SELECT *
    FROM Employees
    WHERE EmployeeID = emp_id;
END;

Execute the procedure:

CALL GetEmployeeByID(101);

Advantages

• Better performance
• Code reusability
• Improved security
• Easier maintenance
• Accepts parameters
• Can call other procedures

Real-Time Example

Instead of writing the same employee retrieval query in multiple applications, we created a stored procedure that returned employee details based on Employee ID.

Triggers

A Trigger is a database object that executes automatically when a specific event occurs on a table.

Supported events include:

• INSERT
• UPDATE
• DELETE

Triggers act like automated watchers that react whenever data changes.

Interview Answer

"Triggers automatically execute predefined SQL statements whenever an INSERT, UPDATE, or DELETE operation occurs on a table."

Why Use Triggers?

Triggers are commonly used for:

• Data validation
• Audit logging
• History tracking
• Automatic updates
• Business rule enforcement

Example

Whenever an employee salary changes, a trigger automatically inserts the old and new salary into a salary history table.

Types of Triggers

Notes

• Triggers execute automatically.
• They don't return result sets.
• Excessive triggers may reduce database performance.

Functions

A Function is a reusable database object that accepts input, performs calculations or processing, and returns a value.

Functions can be used directly inside SQL queries.

Interview Answer

"Functions accept input parameters, perform calculations or business logic, and return a value. Unlike stored procedures, functions can be used inside SQL statements."

Types of Functions

Built-in Functions

Examples include:

• COUNT()
• SUM()
• AVG()
• MIN()
• MAX()
• String functions
• Date functions

User-Defined Functions (UDFs)

Developers create these to implement reusable business logic.

Real-Time Example

We created a user-defined function to calculate employee bonuses based on salary and years of experience. The function was used directly inside reporting queries.

Frequently Asked Questions (FAQs)

1. What is a View in SQL?

A view is a virtual table created from a SQL SELECT statement. It does not store data physically but retrieves data from one or more underlying tables whenever it is queried.

Views are commonly used to:

• Simplify complex queries
• Hide sensitive information
• Control user access
• Improve query readability

2. What is the Difference Between a View and a Table?

(Materialized views are an exception because they physically store query results.)

3. How Does an Index Affect Performance?

Indexes improve the speed of:

• Searching
• Filtering
• Sorting
• JOIN operations

However, they also increase the time required for:

• INSERT
• UPDATE
• DELETE

because the indexes must also be maintained whenever the data changes.

4. What is the Difference Between a Clustered and a Non-Clustered Index?

5. What is Normalization? Explain 1NF, 2NF, and 3NF.

Normalization organizes data to reduce redundancy and improve data integrity.

• 1NF: Removes repeating groups and stores atomic values.
• 2NF: Removes partial dependencies.
• 3NF: Removes transitive dependencies.

6. What is the Difference Between a Stored Procedure and a Trigger?

7. What is the Difference Between a Stored Procedure and a Function?