SQL Basics | DDL: Data Definition Language | VIEW

CREATE VIEW

The statement for creating a view is as follows.

CREATE VIEW "VIEW_NAME" AS "SQL statement";

The SQL statement can be any SQL.

Creating a VIEW

For example, suppose you have the following table.

customer table

Column name	Data type
first_name	char(50)
last_name	char(50)
address	char(50)
city	char(50)
country	char(25)
birth_date	datetime

To create a view that contains the three fields first_name, last_name, and country from this table, enter the following.

CREATE VIEW v_customer
AS SELECT first_name, last_name, country
FROM customer;

This creates a view named v_customer.

v_customer view

Column name	Data type
first_name	char(50)
last_name	char(50)
country	char(25)

VIEW created by joining tables

You can also join two tables by using a view. This lets users check the information they need through a view without directly combining two different tables. Suppose you have the following two tables.

store_information table

store_name	sales	txn_date
Los Angeles	1500	Jan-05-2018
San Diego	250	Jan-07-2018
Los Angeles	300	Jan-08-2018
Boston	700	Jan-08-2018

Geography table

region_name	store_name
East	Boston
East	New York
West	Los Angeles
West	San Diego

You can create a view that includes sales by region with the following command.

CREATE VIEW v_region_sales
AS SELECT A1.region_name region, SUM(A2.sales) sales
FROM geography A1, store_Information A2
WHERE A1.store_name = A2.store_name
GROUP BY A1.region_name;

This creates a view named v_region_sales. The view contains sales data for each region. To retrieve data from this view, enter the following.

SELECT * FROM v_region_sales;

The result of the command above is as follows.

region	sales
east	700
west	2050

DROP VIEW

Use the DROP VIEW command to delete an existing view.

DROP VIEW "[database_name.]VIEW_NAME";