What is the Command Used to Define View in SQL?

The "CREATE VIEW" statement is used to create a view using one or more source tables. To create a view, remember that you must have permission to access all the schemas and tables referenced in the query.

The Syntax to create a view using CREATE VIEW statement:

The command used to create a view in sql is "CREATE VIEW".The Create View command can assign a name to a particular column in the newly created view. If the list of column names is specified, it should have the same number of elements as the number of columns produced for each query.

Note that only the column names are determined; the data type, length, and other characteristics of each column are derived from the definition of the source table. If we do not provide a list of the column in the “CREATE VIEW”, the column in view takes the name of the corresponding column in the query. The list of column names should be specified if the query includes a calculated column or if it has two columns with the same name.

What is Statement used to Define View in SQL?

The Select statement defines the rows and columns that need to be displayed in the view. The statement can take one or more tables or other views.

let's take a simple example to see how a select statement is used to define the view

The data in view comes from the CustomerMaster, ItemMaster, and OrderMaster. These tables are the source tables for the view as data in these tables are visible through the view. The view appears as a table, and its content looks just like the query result we would obtain once we run the query.

Once the view is defined we can use it in the select statement just like a real table as shown in FIGURE 1.

Examples

Let's see a few examples and try to understand how the views are created using the "CREATE VIEW" statement.

TableName: EMP

Now let's create a view called "Employees" on the "EMP" table

The output of the above query is:

Once a view is created above command will show the created view "Employees".

In the above example, the view "Employees" is created by selecting all the data from the source table.

TableName: SALESREPS

Let's create a view showing selected salesperson information.

The output of the above query is:

Once a view is created above command will show the created view "INFO"

In the above example, the view "Info" is created by selecting the specified columns from the source table.

TableName: SALESREP

Let's create a view that contains EMP_NUM, NAME, AGE, REP_OFFICE, and Title where the age of employees is above 35.

The output of the above query is:

Once a view is created above command will show the created view "EMP1".

In the above example, the view "EMP1" is created by selecting the column for SALESREP where the age is greater than 35.