What is the command used to define view in SQL?

Table of Contents

What is the command used to define view in SQL?

We use the “Create View” command in SQL programming for creating and making a view. As a result of this command, you can make a virtual table that stores or combines data from one or more database tables.

In this blog we will explain in detail about What is the command used to define view in SQL, what SQL views are, how to create them with examples, and why they’re so useful in databases.

Enroll now and take the first step towards a successful career. Click here to join our courses today!

What are SQL Views?

In SQL programming, views are virtual tables created from real database tables. They do not store any data physically. Instead of that when you use them, views give you the output of a specific SQL query rather than storing data themselves.

Let’s take an example: you have a database with many tables and complicated relationships between them. It can be very difficult to write queries that get specific information about what we exactly want. This is where views come in helpful.

Level up your skills in SQL Programming. Enroll now and become an expert.

How to Create a View In SQL?

To create a view, you use the CREATE VIEW statement.

Basic Syntax for creating view in SQL

Let’s create a view in SQL using two tables “Employee1” and “Employee2”.

Table Name: Employee1

Employee ID	First Name	Last Name
1	Sana	Khan
2	Simran	Kaur
3	John	Doe

Table Name: Employee2

EmployeeID	Salary
1	50000
2	60000
3	55000

Now, let’s create a view that combines data from these tables into a single view called EmployeeData.

This view will include the first name, last name, and salary of each employee.

Query:

Output from the EmployeeData View:

FirstName	LastName	Salary
Sana	Khan	50000
Simran	Kaur	60000
John	Doe	55000

The ‘EmployeeData’ view combines data from the ‘Employee1’ and ‘Employee2’ tables based on the common EmployeeID column.

So, it gives you a single view of all the information about your employees, with their first name, last name, and salary.

Here’s an explanation of the SQL code:

First we use the CREATE VIEW command to define our view named EmployeeData.
The SELECT command specifies the columns we want in the view.
We select the FirstName and LastName from the Employee1 table and the Salary from the Employee2 table.
The FROM clause indicates the query which tables to get data from, in this case Employee1 and Employee2 tables.
The JOIN clause connects the two tables based on the common column EmployeeID. This ensures that the data from both tables is combined correctly.

Also Read: SQL Queries Interview Questions

Let’s look at a few different situations where we used the view command in SQL to help you understand the concept better.

Create a view that shows the names and ages of all employees

Employee ID	First Name	Last Name	Age
1	Sana	Khan	30
2	Simran	Kaur	25
3	John	Doe	28

Syntax:

Query :

Output:

First Name	Last Name	Age
Sana	Khan	30
Simran	Kaur	25
John	Doe	28

Also Read: Which SQL Keyword is used to retrieve a maximum value?

Create a view that combines data from the “Customers” and “Orders” tables to display customer names and their corresponding order details.

Customers Table:

CustomerID	CustomerName	ContactEmail
1	Customer A	customerA@email
2	Customer B	customerB@email
3	Customer C	customerC@email

Orders Table:

OrderID	CustomerID	OrderDate	TotalAmount
101	1	2023-01-15	500.00
102	2	2023-01-16	750.00
103	1	2023-01-17	300.00

Syntax:

Query :

Output:

CustomerName	OrderID	OrderDate	TotalAmount
Customer A	101	2023-01-15	500.00
Customer B	102	2023-01-16	750.00
Customer C	103	2023-01-17	300.00

Create a view that provides a summary of monthly expenses for different cost categories.

Table Name: Expenses

TransactionDate	ExpenseCategory	ExpenseAmount
2023-01-15	Office Supplies	100.00
2023-01-20	Travel	250.00
2023-02-10	Office Supplies	75.00
2023-02-12	Utilities	150.00
2023-03-05	Travel	200.00
2023-03-20	Office Supplies	80.00

Syntax:

Query:

Output:

Year	Month	ExpenseCategory	TotalExpense
2023	1	Office Supplies	100.00
2023	1	Travel	250.00
2023	2	Office Supplies	75.00
2023	2	Utilities	150.00
2023	3	Travel	200.00
2023	3	Office Supplies	80.00

Conclusion:

I hope you got an answer to the question “What is the command used to define view in SQL?”

The basic syntax for creating a view is the ‘CREATE VIEW’ command.
Joining tables in a view requires understanding the relationships between the tables and the common columns.
The way you work with your data can be made a lot better by making and using views in SQL.

Enroll now in our SQL classes at Technogeeks in Pune, and contact us for free career counseling.

FAQ

What is the command used to define view in SQL?

“CREATE VIEW” is the command used to define view in SQL. It makes a virtual table based on a query.

How many types of views are in SQL?

In SQL, there are two types of views –

Simple Views
Complex Views.

The major difference between Simple views & complex views are – Simple views are based on a single table, while complex views involve multiple tables.

What is the difference between a view and a query?

The basic difference between View and SQL are – View in SQL is a saved query that is kept in the database. A query, on the other hand, is a one-time request to get data or change it.

View make it possible to group and use multiple complex queries, which makes them faster and safer.