Difference between revisions of "View"

From rbachwiki
Jump to navigation Jump to search
 
(4 intermediate revisions by the same user not shown)
Line 8: Line 8:
  FROM customer
  FROM customer
  WHERE lower(region) = 'south'
  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';


[[#Select Command|Back To Top]]-[[Main_Page| Home]] - [[Oracle_SQL|Category]]
[[#Select Command|Back To Top]]-[[Main_Page| Home]] - [[Oracle_SQL|Category]]

Latest revision as of 16:03, 14 November 2017

  • 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