VIEWS in SQL

A view in SQL is a virtual table that is based on the result set of a SELECT statement. It contains rows and columns, just like a real table, and the fields in a view are fields from one or more real tables in the database. Views are used to simplify complex queries, enhance security by restricting access to a subset of data, and present data in a specific format without altering the underlying tables.

Creating a View Let's create a simple example using the employees table. Table: employees

employee_id	first_name	last_name	department	salary
1	John	Doe	Sales	50000
2	Jane	Smith	Marketing	60000
3	Michael	O'Connor	Sales	55000
4	Sarah	O'Brien	HR	45000
5	Laura	Wilson	Sales	70000

Example: Create a View Suppose we want to create a view that shows the full name and department of employees in the Sales department.

SQL Syntax

CREATE VIEW SalesEmployees AS
SELECT 
    CONCAT(first_name, ' ', last_name) AS full_name, 
    department, 
    salary
FROM 
    employees
WHERE 
    department = 'Sales';

Explanation:

CREATE VIEW SalesEmployees AS: This part creates a new view named SalesEmployees.
SELECT CONCAT(first_name, ' ', last_name) AS full_name, department, salary: This part selects the first name and last name concatenated as full_name, along with the department and salary columns.
FROM employees: This specifies the employees table as the source of the data.
WHERE department = 'Sales': This filters the rows to include only those where the department is 'Sales'.

Using the View Now, let's query the SalesEmployees view.

SQL

SELECT * FROM SalesEmployees;

full_name	department	salary
John Doe	Sales	50000
Michael O'Connor	Sales	55000
Laura Wilson	Sales	70000

This query retrieves all columns from the SalesEmployees view, showing the full name, department, and salary of employees in the Sales department.

Previous Next