1/28/16
A
view is a database object that is a logical representation of a table. It is
delivered from a table but has no storage of its own and often may be used in
the same manner as a table.
A
view takes the output of the query and treats it as a table, therefore a view
can be thought of as a stored query or a virtual table.
TYPES
Ø
Simple view
Ø
Complex view
Simple
view can be created from one table where as complex view can be created from
multiple tables.
WHY VIEWS?
ü Provides additional level of
security by restricting access to a predetermined set of rows and/or columns of
a table.
ü
Hide the
data complexity.
ü
Simplify
commands for the user.
VIEWS WITHOUT DML
Ø
Read only
view
Ø
View with
group by
Ø
View with
aggregate functions
Ø
View with
rownum
Ø
Partition
view
Ø
View with
distinct
Ex:
SQL> Create view
dept_v as select *from dept with read only;
SQL> Create view dept_v as select deptno,
sum(sal) t_sal from emp group by deptno; SQL> Create view stud as select rownum no, name, marks from student;
SQL> Create view student as select *from
student1 union select *from student2; SQL> Create view stud as select distinct no,name from student;
VIEWS WITH DML
Ø
View with
not null column -- insert with out not null column not possible
-- update not null column to null is not possible
-- delete possible
Ø
View with
out not null column which was in base table -- insert not possible
-- update, delete possible
Ø
View with
expression -- insert , update not possible
-- delete possible
Ø
View with
functions (except aggregate) -- insert, update not possible
-- delete possible
Ø View was created but the underlying
table was dropped then we will get the message like “ view has errors ”.
Ø View was created but the base table
has been altered but still the view was with the initial definition, we have to
replace the view to affect the changes.
Ø
Complex view
(view with more than one table) -- insert not possible
-- update, delete possible (not always)
CREATING VIEW WITHOUT HAVING THE BASE TABLE
SQL> Create
force view stud as select *From student;
-- Once the base table was created then the view is
validated.
VIEW WITH CHECK OPTION CONSTRAINT
SQL>
Create view stud as select *from student
where marks = 500 with check option constraint Ck;
- Insert possible with marks value as 500
- Update possible excluding marks column
- Delete possible
DROPPING VIEWS
SQL> drop
view dept_v;
Free Video Tutorials(YouTube)
Manual Testing Full Course for Beginners
Java Full Course for Beginners
Python Full Course for Beginners
JMeter Full Course for Beginners
Big Data Testing Full course for Beginners
Agile + Jira Tool Full Course for Beginners
Appium (Android & ios) Full Course for Beginners
Linux & Shell Scripting for Beginners
Big Data/Hadoop Testing Videos for Beginners
ContinuousIntegration with Maven, Git & Github
Selenium withPython Robot Framework for Beginners
Appium(MobileApp Testing) for Beginners
TestNG Tutorials with IntelliJ IDE
Selenium with Java+Cucumber BDD Framework Development
Cucumber for Beginners
API Automation Testing using RestAssured(BDD Approach)
Channel Link: https://goo.gl/2XKXRB

0 comments:
Post a Comment