[ez-toc]
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
[coding-blocks block=”basic-syntax-of-view”]
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:
[coding-blocks block=”sql-view-query-1″]
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:
[coding-blocks block=”view-syntax2″]
Query :
[coding-blocks block=”view-sql-query2″]
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:
[coding-blocks block=”view-sql-syntax3″]
Query :
[coding-blocks block=”view-in-sql-query3″]
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:
[coding-blocks block=”sql-view-syntax4″]
Query:
[coding-blocks block=”sql-view-query4″]
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.
Why is the view used in SQL?
Views are used in SQL to make it easier to get data & most importantly make data safer.
They give you a structured and uniform way to get to your data, so you don’t have to write complicated queries over and over again.
