VIEWS

VIEW:

A View is a "Virtual Table". It is not like a simple table, but is a virtual table which contains columns and data from
different tables

A View does not contain any data, it is a set of queries that are applied to one or more tables that is stored within the
database as an object. After creating a view from some table(s),it used as a reference of those tables and when executed,
it shows only those data which are already mentioned in the query during the creation of the View.


view ensure the security of data by restiricting access to:
-specific rows of a table.
-specific rows and columns of a table.
-rows fetched by using join
-specific rows and columns of a table.


we can create the VIEW by using the CREATE VIEW  statement .

ex:
CREATE VIEW EMPVIEW

AS

SELECT E.ID,E.NAME,E.SAL,A.BRANCH_NAME,A.BRANCH_ID

FROM EMP E JOIN DEPARTMENT A

ON E.DEPT_NO=A.BRANCH_ID


GUIDELINES FOR CREATING VIEWS:
------------------------------
->The name of a view must follow the rules for identifies and must not be the same as that of the table on which it is based
->a view can not derive its data from temporary tables.
->in view ORDER BY cannot be used in SELECT statement .


Restrictions at the time of modyfying data through VIEWS:
View do  not maintain the saparate copy the data ,but onley display the data present in the base tables.so we can modyfy the
base tables by modyfying the data in the view.

->we cannot modify the data in a view if the modification affects onley one table at a time.
->we can not change a column that is the result of calculation,such as a computed column or aggregate function.

ALTERING VIEWS
--------------

ALTER VIEW view_name
AS
select statements

RENAMING THE VIEW:
-----------------

SP_RENAME OLD_VIEWNAME,NEW_VIEWNAME

DROPING THE VIEW:
----------------
DROP VIEW VIEW_NAME

EX:



use venkat
go



CREATE TABLE V_DEPT
     (
BranchId  INT IDENTITY(1,1)
                             CONSTRAINT PK_V_DEPT_BranchId  PRIMARY KEY,
BranchName VARCHAR(25) NOT NULL
)

CREATE TABLE V_STUDENT
(
StudentId int IDENTITY(101,1),
StudentName VARCHAR(20) NOT NULL,
BranchName varchar(25) NOT NULL,
BranchId INT NOT NULL
CONSTRAINT [FK_ V_STUDENT_BranchId] FOREIGN KEY(BranchId) REFERENCES V_DEPT(BranchId)
)

----INSERTING THE ELEMENTS INTO THE TABLES----
INSERT INTO V_DEPT(BranchName)
VALUES('CSE'),
('IT'),
('ECE'),
('EEE'),
('MECH')


INSERT INTO V_STUDENT(StudentName,BranchName,BranchId)
VALUES('MUNNA', 'IT' ,2),
('ASHOK', 'ECE' ,3),
('HANEEF', 'EEE',4),
('ASWIN','CSE',1),
('MANOJ','CSE',1),
('ANU','IT',2),
('KRANTHI','ECE',3),
('KANTH','EEE',4)

------------
SELECT * FROM V_DEPT
SELECT * FROM V_STUDENT



-----CREATING THE VIEW----

CREATE VIEW STDVIEW1
AS
SELECT StudentName,
BranchName
FROM V_STUDENT





------UPDATE THE TABLE V_STUDENT---
UPDATE V_STUDENT SET StudentName='rajum'
WHERE StudentId=101

----UPDATE THE VIEW-----

UPDATE STDVIEW1 SET BranchName='IT'
WHERE StudentName='raju'

 
©chantidodda