VIEWS
- view is a table whose contents are taken from other tables through the execution of a query.
- kind of tables which we have seen uptil now are called permanent base tables.
- views contain no data of their own, hence they are called virtual tables or viewed tables.
- View is a query that is executed when the view is used in a SQL statement.
CREATE VIEW
you can define views using CREATE VIEW statement.
Syntax of Create view
CREATE VIEW <viewname> AS
(SELECT <col1>,<col2> FROM <tablename> WHERE <condition>)
CREATE VIEW ahmdstaff
AS (SELECT * from Salespeople WHERE city='ahmd');
- The query in the view is known as UNDERLYING QUERY, and the tables it uses are called underlying tables.
- Through views, you can give people access to some but not all the information in a table.(SELECTIVE ACCESS TO YOUR DATABASE)
- E.g. if you wanted your salespeople to be able to look at Salespeople table,but not each others commission,
Salespeople(SNUM,SNAME,CITY,COMM)
CREATE VIEW Salesown AS (SELECT snum,sname,city FROM Salespeople);
RENAMING COLUMNS IN VIEWS
Use AS argument to rename output columns.
CREATE VIEW ahmdstaff
AS (SELECT snum AS SalesPerson_Num from Salespeople WHERE city='ahmd');
Orders(ONUM,AMOUNT,ODATE,CNUM,SNUM)
UPDATING VIEWS
- updations to the views are passed on to the underlying tables.
UPDATE salesown SET city='mumbai' WHERE sname='nayan';
DROP VIEW ahmdstaff;
CONDITIONS FOR UPDATEABLE VIEWS
i)views must be defined from single table.
ii)To insert records from view,then P.K. columns and all NOT NULL columns must be included in the view.
INSERT INTO salesown VALUES (26,'ramesh','ahmd');
UPDATE salesown SET city='ahmd' WHERE snum=25;