Recent Posts

Tuesday, 26 April 2016

Data Retrieval Language Part 2

HAVING
     This will work as where clause which can be used only with group by because of absence of where clause in group by.
E.g:
select deptno,count(*),sum(sal),max(sal),min(Sal) from emp group by deptno having count(*)>5;
select deptno,count(*),sum(sal),max(sal),min(sal),avg(sal) from emp where deptno=20 group by dept no;
     Here we use where clause but we used where clause before the group by.

Sequences

* Sequence is a database object which automatically generates unique numeric values on a particular column in the table. Sequence default values start with 1 and it is incremented by +1 up to hard disk capacity.
* Sequence always generates numeric values we can apply the same sequence on more than one column in sequences on different columns in the same table.
* Sequence never generates character values.
* Sequence generates positive values ascending order and descending order.
* Sequence generates negative values ascending order and descending order.
* Sequence mechanism we can’t apply an alpha numeric columns.
Syntax for sequence
First we should create sequence.
Syntax
create sequence <sequence name>
E.g: 
create sequence s1;
Syntax to applying the sequence on a particular column in the table
Syntax: 
insert into<table name> values ( sequence.nextval,’&columnname’,’&columnname’);
E.g: 
insert into emp1 values (s1.nextval,’&ename’,&sal);
     In the above Example nextval represents psudo column and it has two advantages.
1. It initiates the sequence number and also it gives the nE.gt value of the sequence.
Syntax to know about the current value of the sequence
Select sequencename.currval from dual
E.g:
select s1.currval from dual;
     In the above E.gample currval represents psudo column and it gives the current value of the sequence.
To apply the Syntax on other table
insert into emp2 values(s1.nextval,'&ename','&sal');
Creating a sequence with starting ending incrimination value:
Syntax: 
create sequence <sequence name>
        minvalue<integer>
        maxvalue<integer>
    Incremented by integer
E.g:
create sequence s2
     minvalue 101
     maxvalue 110
  incremented by 1;
Note: If sequence reached MAX value then we will get error message
Syntax to alter the sequence
alter sequence s1
          Maxvalue integer
          Increment by integer;
 (Here it can’t possible modify min value)
E.g:
alter sequence s1
   maxvalue 120
   increment by 2;
Example for applying the sequence more than one column on same table.
insert into emp2 values(s1.nextval,'&ename',s1.nextval);
Example for creating sequence with descending order
create sequence s3
  minvalue 1
  maxvalue 10
  increment by -1;
Note: Here it takes first maximum value nE.gt it takes minimum value.
Example for creating the sequences with the negative values
create sequence s4
   minvalue -5
   maxvalue -1
  increment by1
Example for creating the sequence with the negative values with positive increments:
create sequence s5
   minvalue -5
   maxvalue -1
increment by -1;
Example for applying for the two different sequences on two different columns in the same table.
insert into emp1 values(s4.nE.gtval,'&ename',s5.nE.gtval);
How to see the list of all sequences
E.g:
select * from user_sequences;
     Data dictionary tables are user_tab_columns, recyclebin, user_sequences
Creating the sequence with the cycle option
E.g:
create sequence s8
 minvalue 10
 maxvalue 15
 cycle nocache;
Output:
select * from rose;

NO     NAME       SAL
---  ---------  --------
 1    siva         1
 2    bhargav      2
 3    reddy        3
11    rohit       11
45    ravi        45
44    sonal       44
43    vinamra     43
10    vishal      10
12    narad       12
13    saradh      13
14    bharath     14

NO     NAME       SAL
---  ---------  ------
15     suneel     15
10     isha       10
11     chawla     11
Note:  If we give cycle no cache then we will get sequences on columns with cycle.
     That means if minvalue 10 and maxvalue is 15 then after entering five records it is not possible to enter but here we need not bother about limit. It will automatically take from min value.
Syntax to drop sequence
E.g:
drop sequence s2;
Q) Why we should not drop Table by Alter?
Ans) Because table is database object but column is not a database object.

Joins

* This is a mechanism which is used to combine or add one or more than one table.
* Joins are classified into following types those are
1. Equi join/ simple join/ inner join
2. Natural join
3. Non – equi join
4. Outer join
5. Self join
6. Cross join/ Cross product join / Cartesian product join
     Whenever we need to get complete and whole information regarding a particular point then we need to join the tables.
1. Simple or equi or Inner join
* In this join the matching rows will be displayed as output.
* In this join we need to use to equal operator in the where clause of the select statement.
* If we want to perform the join operation at least we require one common column between the tables.
* In this join whenever the rows are not matching those records will not be displayed.
Syntax
SELECT * FROM <tab1>,<tab2>, -----------,<tab n> where <condition>
E.g:
select e1.*,e2.* from std e1,std e2 where e1.deptno=e2.deptno;
select e1.*,e2.no, e2.name from std e1, std e2 where e1.deptno=e2.deptno;
select e1.*,e2.* from std e1 inner join std1 e2 on e1.deptno=e2.deptno;
2. Natural join
     This join was introduced from Oracle 9i onwards, equi join and natural join both are E.gactly same as far as output concerned. There are there difference between equi join and natural join.
* No need to check where condition.
* No need to mention table name before common column.
* Common column will be displayed in front of the output.
E.g:
select * from emp natural join dept;
Note: 
     for Example two tables contain same structure(eno,ename,marks) then its not possible to do natural join.
3. Non equi join
     In this join we need to use only relational operator such as >,<,>=,<=,!= E.gcept ‘=’ operator. The main advantage of non equi join is even through there is no common column. We can perform the join operation.
E.g:
select * from manu m1, manu2 m2 where m1.no>=m2.no;
select * from manu m1, manu2 m2 where m1.no>=m2.no and m1.deptno!=m2.deptno;
4. Outer join 
     In equi join there might be a chance of losing information to recover the lossed information we need to use outer joins. Outer joins are broadly divide into three types.
1. Left outer joins
2. Right outer joins
3. Full outer joins
1. Left outer join
     This join is the combination of equi join operation plus lossed information from the left hand side of the table.
E.g:
select * from manu m1 left outer join manu1 m2 on m1.deptno=m2.deptno;
Oracle 8i model Left outer joins
select * from manu m1, manu1  m2 where m1.deptno=m2.deptno(+);
2. Right outer join
     This join is the combination of equi join operation + lossed information from the right hand side table.
E.g:
select * from manu m1 right outer join manu1 m2 on m1.deptno=m2.deptno;
Oracle 8i model Right outer join
select * from manu m1, manu1  m2 where m1.deptno(+)=m2.deptno;
3. Full outer join
     This join is the combination of equi join operation + lossed information from left hand side + lossed information from right hand side table.
E.g:
select * from manu full outer join manu1 on manu.deptno=manu1.deptno;
Oracle 8i model
select * from manu m1, manu1 m2 where m1.deptno=m2.deptno(+)
union
select * from manu m1, manu1 m2 where m1.deptno(+)=m2.deptno;
5. Self join
     Joining a table by itself known as self join. Whenever we have relationship between two columns in the same table then we need to use self join.
E.g:
select e1.empno,e2.mgr from emp e1,emp e2 where e1.empno=e2.mgr;
No use in real time
6. Cross join
     Cross join is the product of two or more than two tables it means that m no of rows in one table n no of tables in another table then we will get the product of mXn.
E.g
select * from emp1 cross join dept1;

Next Tutorial  Transaction Control Language (TCL)

Previous Tutorial  Data Retrieval Language Part 1

1 comment: