Views for security and abstraction in SQL

Introduction

Views help keep data safe and simple. They show only what you want others to see.

You want to hide sensitive columns like passwords from users.

You want to show only certain rows of a big table to some users.

You want to make complex data easier to understand by showing a simple table.

You want to reuse a common query without writing it again.

You want to control what parts of data different teams can access.

Syntax

SQL

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

A view is like a saved query that looks like a table.

You can select from a view just like a normal table.

Examples

This view shows only employee IDs and names, hiding other details.

SQL

CREATE VIEW EmployeeNames AS
SELECT EmployeeID, Name
FROM Employees;

This view shows only customers who are active.

SQL

CREATE VIEW ActiveCustomers AS
SELECT * FROM Customers
WHERE Status = 'Active';

This view summarizes total sales per product.

SQL

CREATE VIEW SalesSummary AS
SELECT ProductID, SUM(Quantity) AS TotalSold
FROM Sales
GROUP BY ProductID;

Sample Program

This example creates an Employees table, adds data, then creates a view showing only IT department employees with their IDs and names. Finally, it selects all from the view.

SQL

CREATE TABLE Employees (
  EmployeeID INT,
  Name VARCHAR(50),
  Salary DECIMAL(10,2),
  Department VARCHAR(50)
);

INSERT INTO Employees VALUES
(1, 'Alice', 70000, 'HR'),
(2, 'Bob', 80000, 'IT'),
(3, 'Charlie', 75000, 'IT');

CREATE VIEW IT_Employees AS
SELECT EmployeeID, Name
FROM Employees
WHERE Department = 'IT';

SELECT * FROM IT_Employees;

Important Notes

Views do not store data themselves; they show data from tables.

Using views can improve security by limiting data exposure.

Views can simplify complex queries for users who don't need all details.

Summary

Views act like windows to your data, showing only what you want.

They help keep sensitive data safe and make data easier to use.

You create views with a SELECT query saved as a virtual table.