A view is a virtual table based on the result set of an SQL statement. Just like a real table, a view consist of rows and columns. The fields in a view are from one or more real tables in the database. Generally speaking, a table has a set of definitions that physically stores data. A view also has a set of definitions built on top of table(s) or other view(s) that does not physically store data. The purpose of creating views is to make sure that the user does not have access to all the data and is being restricted through a view. Also, it's better to create a view if we have a query based on multiple tables so that we can use it straightaway rather than writing a whole PSQL again and again.

Database views are created using the CREATE VIEW statement. Views can be created from a single table or multiple tables, or another view.

The basic CREATE VIEW syntax is as follows:

CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE [condition];

Let's take a look at each of these commands:

You can then query this view as though it were a table. (In PostgreSQL, at the time of writing, views are read-only by default.) You can SELECT data from a view just as you would from a table and join it to other tables; you can also use WHERE clauses. Each time you execute a SELECT query using the view, the data is rebuilt, so it is always up-to-date. It is not a frozen copy stored at the time the view was created.

Let's create a view on supplier and order tables. But, before that, let's see what the structure of the suppliers and orders table is:

CREATE TABLE suppliers
(supplier_id number primary key,
Supplier_name varchar(30),
Phone_number number);
CREATE TABLE orders
(order_number number primary key,
Supplier_id  number references suppliers(supplier_id),
Quanity number,
Is_active varchar(10),
Price number);
CREATE VIEW active_supplier_orders AS 
SELECT suppliers.supplier_id, suppliers.supplier_name  orders.quantity, orders.price 
FROM suppliers
INNER JOIN orders
ON suppliers.supplier_id = orders.supplier_id
WHERE suppliers.supplier_name = 'XYZ COMPANY'
And orders.active='TRUE';

The preceding example will create a virtual table based on the result set of the SELECT statement. You can now query the PostgreSQL VIEW as follows:

SELECT * FROM active_supplier_orders;

DROP VIEW IF EXISTS view_name;

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name(s)
WHERE condition;

CREATE OR REPLACE VIEW active_supplier_orders AS 
SELECT suppliers.supplier_id, suppliers.supplier_name  orders.quantity, orders.price,order. order_number 
FROM suppliers
INNER JOIN orders
ON suppliers.supplier_id = orders.supplier_id
WHERE suppliers.supplier_name = 'XYZ COMPANY'
And orders.active='TRUE';;