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
1 Simple view
2 Complex view
Simple view can be created from one table where as complex
view can be created from multiple tables.
WHY
VIEWS?
1
Provides additional level of security by restricting access to a
predetermined set of rows and/or columns of a table.
2
Hide the data complexity.
3
Simplify commands for the user.
VIEWS WITHOUT DML
1
Read only view
2
View with group by
3
View with aggregate functions
4
View with rownum
5
Partition view
6
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
1
View with not null column
-- insert with out not null
column not possible
-- update not null column to null
is not possible
-- delete possible
2
View with out not null column which was in base table -- insert not
possible
-- update, delete possible
3
View with expression -- insert , update not possible
-- delete possible
4
View with functions (except
aggregate) -- insert, update not possible
--
delete possible
5
View was created but the underlying table was dropped then we will
get the message like “ view has errors ”.
6
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.
7
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;
DATA MODEL
ALL_VIEW
DBA_VIEW
USER_VIEWS
SYNONYM AND SEQUENCE
SYNONYM
A synonym is a database object, which is used as an alias
for a table, view or sequence.
TYPES
3 Private
4 Public
Private synonym is available to the particular user who
creates.
Public synonym is created by DBA which is available to all
the users.
ADVANTAGES
1
Hide the name and owner of the object.
2
Provides location transparency for remote objects of a distributed
database.
CREATE AND DROP
SQL> create synonym s1 for emp;
SQL> create public synonym s2 for emp;
SQL> drop synonym s1;
SEQUENCE
A sequence is a database object, which can generate unique,
sequential integer values.
It can be used to automatically generate primary key or
unique key values.
A sequence can be either in an ascending or descending
order.
Syntax:
Create
sequence <seq_name> [increment bty n] [start with n] [maxvalue n]
[minvalue n] [cycle/nocycle]
[cache/nocache];
By defalult the sequence starts with 1, increments by 1
with minvalue of 1 and with nocycle,
nocache.
Cache option pre-alloocates a set of sequence numbers and
retains them in memory for faster access.
Ex:
SQL> create sequence s;
SQL> create sequence s increment by 10
start with 100 minvalue 5 maxvalue 200 cycle
cache
20;
USING SEQUENCE
SQL> create table student(no number(2),name varchar(10));
SQL> insert into student values(s.nextval, ‘saketh’);
1
Initially currval is not defined and nextval is starting value.
2
After that nextval and currval are always equal.
CREATING ALPHA-NUMERIC SEQUENCE
SQL> create sequence s start with 111234;
SQL> Insert into student values
(s.nextval || translate
(s.nextval,’1234567890’,’abcdefghij’));
ALTERING SEQUENCE
We can alter the sequence to perform
the following.
1
Set or eliminate minvalue or maxvalue.
2
Change the increment value.
3
Change the number of cached sequence numbers.
Ex:
SQL> alter sequence s minvalue 5;
SQL> alter sequence s increment by 2;
SQL> alter sequence s cache 10;
DROPPING SEQUENCE
SQL> drop sequence s;
JOINS
1
The purpose of a join is to combine the data across tables.
2
A join is actually performed by the where clause which combines the
specified rows of tables.
3
If a join involves in more than two tables then oracle joins first
two tables based on the joins condition and then compares the result with the
next table and so on.
TYPES
1
Equi join
2
Non-equi join
3
Self join
4
Natural join
5
Cross join
6
Outer join
Ø Left outer
Ø Right outer
Ø Full outer
7
Inner join
8
Using clause
9
On clause
Assume that we have the following tables.
SQL> select * from dept;
DEPTNO DNAME LOC
------ ----------
----------
10 mkt hyd
20 fin bang
30 hr bombay
SQL> select * from emp;
EMPNO ENAME
JOB MGR DEPTNO
----------
---------- ---------- ---------- ----------
111 saketh analyst 444 10
222 sudha clerk 333 20
333 jagan
manager 111 10
444 madhu engineer 222 40
EQUI JOIN
A join which contains an ‘=’ operator in the joins
condition.
Ex:
SQL> select empno,ename,job,dname,loc
from emp e,dept d where e.deptno=d.deptno;
EMPNO ENAME
JOB DNAME LOC
---------- ---------- ----------
---------- ----------
111 saketh
analyst mkt hyd
333 jagan
manager mkt hyd
222
sudha
clerk fin bang
USING CLAUSE
SQL> select empno,ename,job ,dname,loc from emp e join dept d
using(deptno);
EMPNO ENAME
JOB DNAME LOC
---------- ---------- ----------
---------- ----------
111 saketh
analyst mkt hyd
333 jagan
manager mkt hyd
222
sudha
clerk fin bang
ON CLAUSE
SQL> select
empno,ename,job,dname,loc from emp e join dept d on(e.deptno=d.deptno);
EMPNO ENAME
JOB DNAME LOC
---------- ---------- ----------
---------- ----------
111 saketh
analyst mkt hyd
333
jagan
manager mkt hyd
222
sudha
clerk fin bang
NON-EQUI JOIN
A join which contains an operator other than ‘=’ in the
joins condition.
Ex:
SQL> select empno,ename,job,dname,loc
from emp e,dept d where e.deptno >
d.deptno;
EMPNO
ENAME JOB DNAME
LOC
---------- ---------- ----------
---------- ----------
222
sudha clerk mkt hyd
444
madhu engineer mkt
hyd
444
madhu engineer fin
bang
444
madhu engineer hr
bombay
SELF JOIN
Joining the table itself is called self join.
Ex:
SQL> select
e1.empno,e2.ename,e1.job,e2.deptno from emp e1,emp e2 where
e1.empno=e2.mgr;
EMPNO ENAME
JOB DEPTNO
---------- ---------- ---------- ----------
111 jagan analyst 10
222 madhu clerk 40
333 sudha manager 20
444 saketh engineer 10
NATURAL JOIN
Natural join compares all the common columns.
Ex:
SQL> select empno,ename,job,dname,loc
from emp natural join dept;
EMPNO ENAME JOB
DNAME LOC
---------- ---------- ---------- ---------- ----------
111 saketh analyst
mkt hyd
333 jagan
manager mkt hyd
222 sudha clerk fin bang
No comments:
Post a Comment