View

Creating, updating, and dropping views in SQL are essential tasks for database management. Views in SQL are virtual tables based on the result-set of a SQL statement. They provide a way to present data from one or more tables in a structured format, often simplifying complex queries or securing sensitive data.

 

Creating a View

To create a view in SQL, you use the CREATE VIEW statement followed by a name for the view and a SELECT statement that defines the view's structure.

Syntax:

CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition;

Example:

Let's say we have a database with a table employees:

CREATE TABLE employees ( emp_id INT PRIMARY KEY, emp_name VARCHAR(100), emp_dept VARCHAR(50), emp_salary DECIMAL(10, 2) );

Now, we create a view high_salary_employees to select employees with salaries above a certain threshold:

CREATE VIEW high_salary_employees AS SELECT emp_id, emp_name, emp_salary FROM employees WHERE emp_salary > 50000;

 

Updating a View

Views can be updated indirectly by updating the underlying base tables. However, some databases support updatable views under certain conditions (e.g., simple views without aggregation or joins).

Example:

Suppose we want to update the salary of an employee in the employees table:

UPDATE employees SET emp_salary = 55000 WHERE emp_id = 101;

If high_salary_employees view is based on this table and meets the criteria (emp_salary > 50000), the view will reflect this change.

 

Dropping a View

To remove a view from the database, you use the DROP VIEW statement followed by the name of the view.

Syntax:

DROP VIEW view_name;

Example:

To drop the high_salary_employees view:

DROP VIEW high_salary_employees;

 

Considerations

 View Limitations: Views are often limited by the complexity of the underlying SQL query. They cannot be indexed directly and may impact performance if based on complex queries.

Security: Views can be used to restrict access to specific columns or rows of a table, enhancing security by limiting exposure of sensitive data.

 

Views in SQL are powerful tools for simplifying complex queries, enhancing security, and organizing data access. Understanding how to create, update, and drop views is fundamental for effective database management.