View

• A view is the representation of a SQL statement that is stored in memory so that it can easily be re-used
  • A view gives a view of the table as defined by the select statement in the view defination
  • View does not store data separately
  • Only definition (query) of the view is stored
  • the data is retrieved from the underlying table based on the view definition

CREATE VIEW customer_south AS
SELECT customer_id, first_name, region
FROM customer
WHERE lower(region) = 'south'

EXECUTE the VIEW

SELECT * FROM customer_south

this will run the view

MODIFY A VIEW

CREATE OR REPLACE VIEW customer_south AS
SELECT customer_id, first_name, country,region
FROM customer
WHERE lower(region) = 'south'

UPDATING THE VIEW

Updating records in a view will update the underlying table

UPDATE customer_south
SET address_line1 = "1123 Main street"
WHERE customer_id = 10;

CREATING A VIEW FROM MORE THAN ONE TABLE

CREATE VIEW SALES_SOUTH AS
SELECT S.SALES_DATE, S.ORDER_ID, S.PRODUCT_ID, C.REGION
FROM SALES S, CUSTOMER C
WHERE S.CUSTOMER_ID = C.CUSTOMER_ID
AND C.REGION = 'SOUTH';

Back To Top- Home - Category