Learn How to Create, Update, and Use Views to Simplify Queries - Textnotes

Learn How to Create, Update, and Use Views to Simplify Queries

Master SQL Views in this beginner-friendly guide. Learn how to create, update, and drop views, and understand their advantages. Practice creating a view of top 10 highest-paid employees and simplify queries using views for better database management.

1. Introduction

A view in SQL is a virtual table created from a query.

  1. Views do not store data physically but display data from one or more tables.
  2. Useful for simplifying complex queries, security, and data abstraction.

Key Points:

  1. Can be queried like a table.
  2. Useful for restricted access to sensitive columns.
  3. Automatically updates when the underlying tables change.

2. Creating Views

Syntax:


CREATE VIEW ViewName AS
SELECT column1, column2, ...
FROM TableName
WHERE condition;

Example:


-- Create a view for top 10 highest-paid employees
CREATE VIEW TopEmployees AS
SELECT Name, Department, Salary
FROM Employees
ORDER BY Salary DESC
LIMIT 10; -- Use TOP 10 in SQL Server

Querying a View:


SELECT * FROM TopEmployees;

3. Updating Views

  1. Views can be updated if they reference a single table and meet certain criteria.

Example:


-- Update salary via view (if allowed)
UPDATE TopEmployees
SET Salary = Salary + 5000
WHERE Name = 'John Doe';

Note:

  1. Some views cannot be updated if they use aggregates, joins, or group by.

4. Dropping Views

Syntax:


DROP VIEW ViewName;

Example:


DROP VIEW TopEmployees;

5. Advantages of Views

  1. Simplify complex queries – encapsulate multi-table queries.
  2. Data abstraction – hide sensitive columns from users.
  3. Security – grant access to views instead of tables.
  4. Reusability – reuse common query logic.
  5. Consistency – ensure users see a consistent dataset.

6. Practical Exercises

  1. Create a view for the top 10 highest-paid employees.
  2. Use a view to list employees in a specific department.
  3. Create a view that joins Employees and Departments to simplify queries.
  4. Update a view (if allowed) to change salaries or departments.
  5. Drop a view after use and ensure the underlying table data remains unchanged.

7. Tips for Beginners

  1. Use views to simplify frequently used queries.
  2. Avoid overusing views with complex joins for performance-critical queries.
  3. Combine views with indexing for optimized query performance.
  4. Use views to restrict access to sensitive columns in production databases.


Next Step: After mastering views, the next module is Stored Procedures and Functions, where you’ll learn to create reusable SQL logic, pass parameters, and improve database performance.