How to Create and Use Views in SQL

In this blog, we’ll cover: 

• What are SQL Views 
• Why Views are used 
• Syntax for creating a View 
• How to update and drop Views 
• Real-life examples of Views 
• Advantages and limitations of Views 

By the end, you’ll know how to create, use, and manage SQL Views like a pro.  

What is a View in SQL? 

A View is a stored query in the database that can be treated as a table. It doesn’t hold the data itself but fetches it from the base tables. 

 Think of it as a saved query that you can reuse whenever you want. 

For example, if you frequently need to check the names and salaries of employees from the employees table, instead of writing the query every time, you can just create a View and fetch it directly. 

Why Use Views in SQL? 

There are several reasons why developers and database admins use Views: 

1. Simplify Complex Queries 

• If a query involves multiple joins or conditions, you can save it as a View for easy use later. 

2. Security 

• Views can restrict access by exposing only the necessary columns instead of the whole table. 

3. Reusability 

• You can use the View in multiple queries without rewriting the logic. 

4. Data Abstraction 

• Users can see a simplified representation of data without worrying about the actual database structure. 

Syntax of Creating a View 

The basic syntax of a View in SQL is: 

CREATE VIEW view_name AS 

SELECT column1, column2, ... 

FROM table_name 

WHERE condition; 

Example 1: Creating a Simple View 

Suppose we have an employees table: 

Now, if we want to create a View that shows only the employees from the IT department: 

CREATE VIEW IT_Employees AS 

SELECT emp_name, salary 

FROM employees 

WHERE department = 'IT'; 

 This creates a View named IT_Employees. 

Now, whenever we run: 

SELECT * FROM IT_Employees; 

We’ll get: 

Example 2: View with Multiple Tables 

Let’s say we have two tables: 

employees 

departments 
| dept_id | dept_name | location | 

If we want a View to show employee names with their department location: 

CREATE VIEW Emp_Department_Info AS 

SELECT e.emp_name, d.dept_name, d.location 

FROM employees e 

JOIN departments d ON e.department = d.dept_id; 

Now, a simple: 

SELECT * FROM Emp_Department_Info; 

gives you all employees with department details. 

Updating a View 

You can change the definition of an existing View using CREATE OR REPLACE VIEW. 

Example: 

CREATE OR REPLACE VIEW IT_Employees AS 

SELECT emp_name, salary, department 

FROM employees 

WHERE department = 'IT'; 

Dropping a View 

If a View is no longer required, you can drop it using: 

DROP VIEW view_name; 

Example: 

DROP VIEW IT_Employees; 

Advantages of Views 

• Makes queries simple and reusable 
• Provides security by limiting data access 
• Hides complex joins from users 
• Ensures consistency in data representation 

Limitations of Views 

• Performance can be slower for very large datasets 
• Some Views (especially complex ones) may not be updatable 
• Too many Views can make database management harder 

Real-Life Use Case of Views 

Imagine an E-commerce Database with tables like customers, orders, and products. 

The sales team only needs to see: 

• Customer Name 
• Order Date 
• Product Purchased 

Instead of giving them full table access, you create a View: 

CREATE VIEW Sales_Report AS 

SELECT c.customer_name, o.order_date, p.product_name 

FROM customers c 

JOIN orders o ON c.customer_id = o.customer_id 

JOIN products p ON o.product_id = p.product_id; 

Now, the sales team just runs: 

SELECT * FROM Sales_Report; 

They get exactly what they need without extra details.  

Conclusion 

SQL Views are an excellent way to simplify queries, secure data access, and improve reusability. By treating them like virtual tables, developers can save time and ensure consistency in database operations. 

If you’re preparing for interviews, make sure you understand: 

• How to create Views 
• How to update and drop Views 
• Real-world use cases of Views 

Because SQL Views are a favorite topic of interviewers when testing database knowledge. 

 Want to master SQL and database design with hands-on projects? 
Check out Uncodemy’s SQL Course and boost your career in backend development.