FIRST OF ALL WE HAVE TO LEARN SQL AND PL-SQL TO BECAME A ORACLE APPLICATION DEVELOPER. SO WE HAVE TO LEARN ORACLE SQL-PLUS
SQL-NOTES:-
v To display table from database:
select * from tab;
v To display the structure of table:
select * from <tablename>
v To create table
create table <table name>(col name <data type><size>,col name <data type><size>);
Example:-create table sai (empid number(4),last_name varchar2(23))
v To see the structure of table:
desc/describe <name of table>;
v To insert record into database table:
insert into <table name> (<col1 name>,<col2 name>) values(………)
v To insert values on runtime:
insert into <table name> (<colname>,<col name>) values(<&col name>,<&col name>);
v To delete the record from table;
delete from <table name> where <col name>=<expr>;
v Update table column:
update <table name> set <col name>=<expr> where<col name>= <cond>:
v Delete Table:
delete from <tabel name>:it will delete whole data from table.
truncate from <table name> it will physically delete whole the date from table.
v Alter keyword: are used the structure of table:
alter table <table name> modify (col name <datatype><size>);
v To rename the table: It will work on 10 g only.
alter table <table name> rename column<old col name> to <new col name>
v To rename table name:
Alter table <old table name> rename to <new table name>
v Add extra column:
alter tabel <table name>add (<column name> <data type><size>)
v Distinct: are used to see only 1 record from duplicate records.
Select distinct <colum name> from <table name>
v is clause: are used to null values.
v Where clause: are used to filter on single record.
v Group by: are used to group the elements from multiple elements of same type.
v Having clause: are used with group by to filter multiple records.
Example:-
Select manager_id,count(employee_id)no_of_employees,
sum(salary),max(salary) from employees
group by manager_id
having count(employee_id)>5;
wildcard/pattern mattching: in oracle only column names are case-sensitive.
v like : are used to find similar records from database table:
select * From r where emp_name like 'r%'
v In( ): this are used for range searching;
select * from emp where department_id in(10,30);
v Not in(): opposite of in
lselect * from emp where department_id not in(10,30);
logical operator: There are four types of logical operator. They are following:
v And:
select * from emp where JOB=’CLERK’ and salary=5000
v Or:
select * from r where emp_id>2 or emp_id=5;
select * from r where emp_id=2 or emp_id=5;
v Between:
Select * from emp where deptno between 10 and 20;
Group function: There are five type of group function
v Sum, Max, Min, Count, Avg
Example:-
Select
distinct manager_id,count(emp_id)no_of_emp,max(salary),min(salary),avg(salary),sum(salary) from <table name>
group by manager_id;
Alias names:
We can pass alias name from 3 ways. We can understand with following example:
Select emp_name "emp name",manager_id as salesman,salary sal from <table name>
To create dump of any existing table:
Create table <table name> as select * from <table name>;
To copy structer of any existing table:
Create table <table name> as select * from <table name> where 1=2;
Sub queries in oracle: (=, <, <=, >, >=, not equal to (!=,<>,^!))
Those queries in which one parent has child quries. In this quries first child execute and then parent execute. There are four subqueries they are following:-
v Single-row-sub queries/scaler sub quries:
In this quries return single column along with single record
select * from emp where sal=
(
select max(sal) from emp
);
===============================
select * from emp where sal=
(
select max(sal) from emp where sal<
(select max(sal) sal from emp)
);
v Multiple-row-sub queries:
In these queries the sub queries return more then one record with in a single column;
In this we use three operator
• In:
select * from r where salary in
(select max(salary) from r)
• All:
Select * from emp where sal<all
(select count(empno) from emp);
• Any:
select * from emp where sal>any
(select avg(sal)from emp);
v Multiple-column-sub queries:
In this sub queries, it return more then one column along with more column. In this both queries have same no of column along with same data types.
It is used in those cases where we need values is where condition.
select * from emp where
(deptno,sal) in
(
select deptno,max(sal) from emp
group by deptno
)order by deptno(optional)
v Co-related sub queries:
In these query we split one table in two part after that we compare both parts to find our output.
For example:
1. select * from emp e
where 0=
(
select count(sal) from emp b
where e.sal<b.sal
);
=========================
2. How to find first 3 max sal:
select distinct sal,deptno from emp e
where 2>=
(
select count( distinct sal) from emp b
where e.sal<b.sal
);
Set opetators:
Set operators are used to combine the result of multiple queries and provide a single result.
There are 4 types of operator
1. Union
2. Union all
3. Intersect
4. Minus
Some common rules apply in set operator they are:
1. All the queries are same serial no along with same data type:
2. Order by clause are used in last operator.
We can easily understand set operators with the help of following example:
a={1,2,3,4,5}
b=(1,3,4,6,7}
aub={1,2,3,4,5,6,7}
It will display only single column
select deptno,job from emp where deptno=10
union
select deptno,job from emp where deptno< >10
Union all:
It will display single column along with duplicate records;
Intersect:
It compare all the records and display only single records:
a={1,3,4,6}
b={1,4,7,8}
a intersect b={1,4}
Example:
select job from emp where deptno=10
intersect
select job from emp where deptno=20
intersect
select job from emp where deptno=30
Minus:
It will display record only in first column
Example:
select job from emp where deptno=10
minus
select job from emp where deptno<>10
Dual operator:
Dual operator are system defined function which are used to perform arithmetic calculation.
In this there are single column and single record.
We can not use where condition in dual.
We can create our own table like dual to perform same task.
Dual operators are following:
Single row function:
1. Numerical function:
sqrt / power / mod / abs(ABSOLUTE)R+ / round / ceil / floor / sign / trunc / log / sin /
Example:
Select sqrt(2) from dual;
Select power(2,2) from dual;
Etc.
2. Char function:
These function are used to perform task through character.
Upper
Select upper(‘david’) from dual;
Lower:
Select lower(‘DAVID’) from dual;
Initcap:
Select initcap(‘david’) from dual;
Reverse:
Select reverse(‘david’) from dual;
Soundex:
Select soundex(‘smith’) from dual;
Output:
SOUN
----
R400
Lpad:
Select lpad(‘david’,10,’*’) from dual;
Output:
LPAD('RAHU
----------
*****david
Rpad:
Select rpad(‘david’,10,’*’) from dual;
Output:
RPAD('RAHU
----------
david*****
Rtrim:
Select rtrim(102.30,1) from dual;
Output:
RTRIM
-----
102.3
Ltrim:
Trim:
Ascii:
Select ascii(‘a’) from dual;
Output:
97
Chr:
Select chr(65) from dual;
Output:
C-
--
A
Translate:
select translate('david0','david0','nagpal')
from dual;
Output:
TRANSL
------------
nagpal
3. Date function:
By default date is 'dd-mon-yy' (date-month-year)
Some of date FORMATS in oracle is:
1. dd/month/yy
2. ddth/yy/mm
3. ddthsp/mon/yyyy
4. mm/dd/yy hh24 mi ss
Some of the date FUNCTIONS in oracle are:
1. to_char:
Example:
select to_char(sysdate,'mm/dd/yy') format1,
to_char(sysdate,'ddthsp/month/yy')format2,
to_char(sysdate,'ddth/yy/mm') format3,
to_char
(sysdate,'mm/dd/yy hh24 mi ss')format4
from dual;
Output:
FORMAT1 | FORMAT2 | FORMAT3 | FORMAT4
-------- --------------------------- ---------- -----------------
08/29/12 | twenty-ninth/august /12 | 29th/12/08 | 08/29/12 02 56 44
to_date:
To change database time format:
(alter session set NLS_DATE_FORMAT='DD/MM/YYYY')
select to_date('&date','dd/mm/yyyy') from dual
3. months_between:
Select months_between
('24-aug-12','12-mar-12')
FROM DUAL
Output:
MONTHS_BETWEEN('24-AUG-12','12-MAR-12')
--------------------------------------- --------------------------
5.38709677
Select trunc (months_between
(’24-aug-12’,’12-mar-12’))
From dual;
Output:
TRUNC(MONTHS_BETWEEN('24-AUG-12','12-MAR-12'))
---------------------------------------------- ---------------------------
5
4. add_months:
Select add_months
('&date','&add')
From dual;
Output:
Enter value for date: 29-aug-2012
Enter value for add: 2
ADD_MONTH
---------
29-OCT-12
5. next_day:
Select next_day
('&date','&day')
From dual;
Output:
Enter value for date: 29-aug-12
Enter value for day: wed
NEXT_DAY(
--------- ----------
05-SEP-12
6. last_day:
Select last_day('23-aug-12') from dual;
LAST_DAY(
--------- ---------
31-AUG-12
7. round:
we can round date easily with round keyword.
select round
(
to_date('&date','dd/mm/yy'),'&permater')
from dual;
Output:
Enter value for date: 12-aug-12
Enter value for parma
old 3: to_date('&date','dd/mm/yy'),'&parma
new 3: to_date('12-aug-12','dd/mm/yy'),'month')from dual
ROUND(TO_
---------
01-AUG-12
SQL>
Enter value for date: 12-aug-12
Enter value for parma
old 3: to_date('&date','dd/mm/yy'),'&parma
new 3: to_date('12-aug-12','dd/mm/yy'),'year')from dual
ROUND(TO_
---------
01-JAN-13
SQL> /
Enter value for date: 12-may-12
Enter value for parma
old 3: to_date('&date','dd/mm/yy'),'&parma
new 3: to_date('12-may-12','dd/mm/yy'),'year')from dual
ROUND(TO_
---------01-JAN-12
8. trunc : trunc and round work as the same but trunk can move to next year.
select trunc
(
to_date('&date','dd/mm/yy'),'&parma
from dual;
Output:
TRUNC(TO_
---------
15-AUG-47
SQL> /
Enter value for date: 15-aug-47
Enter value for parma
old 3: to_date('&date','dd/mm/yy'),'&parma
new 3: to_date('15-aug-47','dd/mm/yy'),'ddd')from dual
TRUNC(TO_
---------
15-AUG-47
SQL> /
Enter value for date: 16-aug-47
Enter value for parma
old 3: to_date('&date','dd/mm/yy'),'&parma
new 3: to_date('16-aug-47','dd/mm/yy'),'month')from dual
TRUNC(TO_
---------
01-AUG-47
9. Extract:
select extract(month from sysdate)sysmonth,
extract(year from sysdate)sysyear from dual;
Output:
SYSMONTH SYSYEAR
---------- ----------
8 2012
4. General function:
General function is most important part of all function.
1. Conversion function:
Some conversion in oracle to convert from one format to another format they are:
to_char:
are used to convert from number to string
9 is standard digit to convert into string
Example:
select to_char(123456,'9,999,99') from dual;
Output:
TO_CHAR(1
-------------------
1,234,56
to_date :
select to_char(to_date(12345,'J'),'JSP') from dual;
Output:
TO_CHAR(TO_DATE(12345,'J'),'JSP')
----------------------------------------
TWELVE THOUSAND THREE HUNDRED FORTY-FIVE
to_number:
are used to convert from string to number & data are same it no affect data.
Example:
select to_number('23') from dual;
Output:
TO_NUMBER('23')
----------------------
23
Some more general functions are following :
Greatest:
This function is used to calculate greatest values from user input according to user’s need and input.
For example:
select greatest(4,5,6) as "numberwise",
greatest('4','5','6')as "string" from dual
(in above example those values which is passed through string that count ascii value of input, in this example ascii value of six is 54 which is greater then rest of the values.)
Output:
numberwise s
-------- ------------
6 6
Least:
This function is reverse of sbove function.
For example:
Select least(4,5,6),least(‘4’,’5’,’6’) from dual;
Output:
Least (4 least(‘4
------------- --------
4 4
Nvl:
This function is used for null values.
For example:
Select emp.*,nvl(comm,0) as "null comm" From emp
where sal+nvl(comm,0)<=1000
Output:
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO null comm
------ ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20 0
7900 JAMES CLERK 7698 03-DEC-81 950 30 0
Nvl2:
This function are used to substitute for both null as well as not null & it accept number as well as string.
For example:
select nvl2(comm,'yes','no') as "string",nvl2(comm,1,0)as "num" from emp where sal+nvl(comm,0)<=2000
Output:
str num
--- ----------
no 0
yes 1
yes 1
yes 1
no 0
no 0
no 0
Decode: It is a function we can’t use conditions like greater then, where etc…
It works on <if>,<Then> rules. We can take strings as well as number along with decode function.
For example:
select decode(comm,null,0,1) from emp;
select decode(comm,null,'yes','No1') from emp;
Case:
Case is an statement. We can use condition in it.
For example:
select empno,ename,sal,
case
when sal>3000 then 'A'
when sal>2000 and sal<3000 then 'B'
when sal>1000 and sal<2000 then 'c'
else 'z'
end as grade from emp;
Output:
EMPNO ENAME SAL G
----- ---------- ---------- - ------------- ---------------- --------------- -------------- -----------------
7788 SCOTT 3000 z
7839 KING 5000 A
7844 TURNER 1500 c
7876 ADAMS 1100 c
----- ---------- ---------- -
7900 JAMES 950 z
7902 FORD 3000 z
7934 MILLER 1300 c
Compose: In this function we have to use ‘\0301’ to n…… of types other wise we can’t execute it.
For example:
select compose('a'||unistr('\0301')),
compose('a'||unistr('\0302')),
compose('a'||unistr('\0308')) from dual;
Output:
C C C
- - -
á â ä
Decompose: In this function we have to use ‘\0301’ to n…… of types other wise we can’t execute it.
For example:
select decompose(compose('a'||unistr('\0301'))),
decompose(compose('a'||unistr('\0302'))),
decompose(compose('a'||unistr('\0308'))) from dual;
Output:
DE DE DE
-- -- --
a´ a^ a¨
Pseudeo column in oracle:
Pseudo column are database object which are internally attach to each row of record . These are 4 pseudo column which provided in oracle are:
rowid / rownum / nextvalue /curr value
Rowid:
Rowid are are internally allocated to each & every record of a table.
Rowid are used for following purpose:
1) Fastest retrival of record
2) Delete dupliate record from a table bcz in oracle entire record is duplicate.
3) Rowid are unique across the world.
Rowid are devided in two types:
1. Extended:
Are attached for each & every record:these are format of extended rowid:(000000 fff bbbbbb rrr)
in these
000000 : is the object id
Ffff: is the unique file number.
bbbbbbb: is the unique block no.
rrrr: is for the record no.
2. Restricted:
are used for tablespace in orcale object.in restricted there are format(ffff bbbbbbb rrrr)
ffff: is the unique file number.
bbbbbbb: is the unique block no.
rrrr: is for the record no.
Rownum:
These are also physically allocated to each & every record of a table.
These are used to provide rownum to each & every column of a table.
If we delete some record in column oracle internally allocated rownum of each & every record on run time.
Through rownum we can filter the record 1 to < n > but we cannot perform task from mid term
for that u need to use set operator(union / minus).
we can use rownum =1 or
rownum<=<n number>
For notes click below:
http://www.techonthenet.com/oracle/sequences.php
Sequence:
Sequence are used to allocate unique number from oracle no need to insert manully.
There are two formats of sequence are:
next val / curr val
How to create a sequence:
Create sequence <sequence name>
Minvalue <value>
Maxvalue <value>
Start with <value>
Cycle
Cache <value>
How to create a report which save in bin file of data base
Answer:
for decode report
write on decode for eg ed decode.sql
and to execute write (@decode.sql;)
Reports
We can made report in sql. We can understand easily with the help of following syntax:
ttitle "matrix report"
btitle "*** End of Report ***"
break on report
compute sum of dept10 dept20 dept30 dept40 total on report
select * from
(select job,
sum(decode(deptno,10,sal))dept10,
sum(decode(deptno,20,sal))dept20,
sum(decode(deptno,30,sal))dept30,
sum(decode(deptno,40,sal))dept40,
sum(sal)total
from emp
group by job
);
ttitle off
btitle off
Description of above syntax:
Ttitle: it show the title of report. Once we on this then we have to close this title other wise our program will not execute properly. To close this keword we have to mention “ttitle off” at the end of table.
Btitle: it shows bottom title of the report. Once we on this then we have to close this title other wise our program will not execute properly. To close this keword we have to mention “btitle off” at the end of table.
Break on report: it show output of the table with break.
Compute sum of dept10 dept20 dept30 dept40 total on report
Above line show the sum of different salary of different deptno in different column.
Constrains In ORACLE
Constraint are rules applied on business which are used to restrict/validate business rule in oracle.
There are 2 types of constraint which are applied on table.
1. table level constraint
2. column level constraint
Data dictionary in Oracle:
Data dictionary are to see the constraint or object which you are applied on table .these are collection of database object which are used to see ( procedure / function / constraint / object / triggers /).
the object which we create first goes to user_objects & then goes to respective data dictionary view.
USER_CONSTRAINT are used to check the constraint which u are applied on table.
USER_CONS_COLUMNS are used to check constraint along with the column name.
Differentiate between table level / column level :
Column level:
These constraint are applied along column name and <constraint name>
Ex: comm > 100 / age between 18 and 30 / sal >1000
Table level:
These constraint are applied at the end of column and before the colosing of body.
Its are also called composite key constraint.
Ex: sal >comm./return_date eg. journey_date / date_of_admission date_of_birth
There are 5 constraint which are applied with the table data. They are following:
Primary key ( P)
eg SQL>create table p
(id number primary key ,ename varchar2(20));
In above example we create a table and assign a primary key to one column of that table. In this example we assign a primary key along with column named ‘id’ which means we do not insert any null value in that column.
How to drop primary table:
To delete primary table we have to use some syntax. If we want to delete primary table then we have to use “cascade constraint” along with drop keyword.
Syntax is :
SQl> drop table p cascade constraint;
Foreign key (R):
For create table with foreign key. Eg.
SQL>create table f (id number(06),ename varchar2(20),foreign key(id) references p(id));
In above syntax we create a table named F which is child table of primary table. In this example we use “foreign key(id)” which shows that we use foreign key along with id column, but we have to mention a references for execution of table. That reference shows that existing table is child table of primary table. For this we have to mention “references p(id)” along with all syntax.
In this table we cannot insert different record from primary table.
Difference between primary table and foreign table:
Primary table is parent table and foreign table is child table.
We cannot assign duplicate record into primary table , but we can enter duplicate record into foreign table but primary column must be same on primary table other wise it shows error.
How to alter primary table and foreign table:
To alter table to a primary table
alter table p add constraint pk_p_table primary key(id);
pk_p_table: it is a constraint which shows constrains of a primary table.
Some constraint which we use along with column level and table level conditions:
Delete : delete cascade
This constraint delete record automatically from child table when we delete record from primary table
For example:
alter table f add constraint fk_id foreign key(id) references p(id) on delete cascade.
In above example we alter a table and assign a foreign key along with id column and put references of table p which is a primary table along with this we use ‘on delete cascade’ which means when we delete any record from table p then common record between table p and f will delete.
On delete set null:
This constraint delete record automatically from child table when we delete record from primary table
For example:
alter table f add constraint fk_id foreign key(id) references p(id) on delete set null.
In above example we alter a table and assign a foreign key along with id column and put references of table p which is a primary table along with this we use ‘on delete cascade’ which means when we delete any record from table p then common record of table f will become null.
On update set null;(r nd d):
Some important information about constraint:
In these if u do not mention the name of constraint oracle by default assign the name of constraint which is in the format of
sys_c <oracle assign number>.
To check the constraint applied on oracle use ( in capital letter)
USER_CONSTRAINT
for Eg. select constraint_name from USER_CONSTRAINTS
USER_CONS_COLUMNS
Unique constraint:
In this constraint a user can insert unique record.
For example: email id, contact no etc.
We can easily understand unique key word with the help of following syntax.
Create table <table name> (col1,col2 constraint<constraint name> unique,col3…….coln);
In the above syntax unique constraint apply on col2.
Check constraint:
In this constraint we can pass some condition for example. Greater then,less then ,between …etc.
Eg.
Create table <table name> (col1,col2 constraint <constraint name> check(conition),col3…….);
In the above syntax check constraint apply on column2.
Composite key:
In this constraint we can assign primary key to more then once.
This is table level constraint.
Eg.
Create table compose (
rollno number(10),college varchar2(20),student varchar2(20),
primary key(college,rollno)
);
In the above example we can’t leave college & roll no column.
Some syntax:
UPDATE DEPT1 SET DEPTNO=DEPTNO+1;
Above syntax update dept1 table it increase 1 value on deptno.
Join in Oracle
In real industries almost each and every like finance / insurance / banking / health & science / transport / real state every where store the data in many different tables to make assured to data and to keep your date secure.
if need be we join that table to see the output result:
There are 4 types of join in oracle:
1. Cartesian join.
2. Simple join / eque join / natural join / inner join
3. Outer join(+)// 9i on wards(left outer join / right outer join / full outer join)
4. Self join
We can easily understand join with the help of following description:
1. Cartesian join:
In real life these join are not used because it join one table to another table in the form of product.
We can easily understand this join with the help of following syntax:
Select emp.*,dept.* from emp,dept;
Above syntax will multiply both table and display the output.
2. Simple join/Eque join/Natural join/Inner join:
Simple join is used in common database. We can call simple join as eque join/natural join/inner join.
All joins will display same output but there is clause difference. We can easily understand simple join with the help of following description and syntaxes:
E.g.
Simple join:
select empno, ename, sal, emp.deptno, dept.deptno, dname,loc
from emp , dept
Above syntax will give output in the form of product.
Eque join:
In this join we use simply “equal to(=)” operator.
E.g.
select empno,ename,sal , d.deptno,dname,loc from emp e , dept d
where e.deptno=d.deptno
order by deptno;
Output:
EMPNO ENAME SAL DEPTNO DNAME LOC
------ ---------- ---------- ---------- -------------- -----------
7782 CLARK 2450 10 ACCOUNTING NEW YORK
7839 KING 5000 10 ACCOUNTING NEW YORK
7934 MILLER 1300 10 ACCOUNTING NEW YORK
7566 JONES 2975 20 RESEARCH DALLAS
7902 FORD 3000 20 RESEARCH DALLAS
7876 ADAMS 1100 20 RESEARCH DALLAS
7369 SMITH 800 20 RESEARCH DALLAS
7788 SCOTT 3000 20 RESEARCH DALLAS
7521 WARD 1250 30 SALES CHICAGO
7844 TURNER 1500 30 SALES CHICAGO
7499 ALLEN 1600 30 SALES CHICAGO
Natural join:
Natural join will give same output like eque join but in this we can not use equal to operator. We can easily understand with the following example.
E.g.
select empno,ename,sal,deptno ,dname,loc
from emp natural join dept
order by deptno;
Output as above.
Inner join:
Inner join will give same output like above two joins but there is difference between clause.
E.g.
Select empno,ename,sal,d.deptno ,dname,loc
from emp e inner join dept d
on
e.deptno=d.deptno;
Output as above.
Outer join:
select empno,ename,sal,d.deptno ,dname,loc
from emp e , dept d
where e.deptno(+)=d.deptno.
In this we join one table to another table to get some extra records or unmatching records. In this we use (+)operator to join table to another table.
We can use (+)operator either left hand side or right hand side but we can not use it both hand side. In this from Oracle 9i onwards oracle comes with
1. Left outer join
2. Right outer join
3. Full outer join
Left outer join:
Basically we use outer join to get some extra record. Left join check that record which don’t have matching value from another table. It will display that record on the left side. It will check left side table only.
For e.g.
Select empno,ename,sal,d.deptno ,dname,loc
from emp e left outer join dept d
on e.deptno=d.deptno
Output:
EMPNO ENAME SAL DEPTNO DNAME LOC
------ ---------- ---------- ---------- -------------- ---------
40 OPERATIONS BOSTON (extra Record in dept table)
7839 KING 5000 10 ACCOUNTING NEW YORK
7934 MILLER 1300 10 ACCOUNTING NEW YORK
7782 CLARK 2450 10 ACCOUNTING NEW YORK
7369 SMITH 800 20 RESEARCH DALLAS
7902 FORD 3000 20 RESEARCH DALLAS
7876 ADAMS 1100 20 RESEARCH DALLAS
7788 SCOTT 3000 20 RESEARCH DALLAS
7566 JONES 2975 20 RESEARCH DALLAS
7900 JAMES 950 30 SALES CHICAGO
7499 ALLEN 1600 30 SALES CHICAGO
Right outer join:
This join work as same but it check right side table. If it find extra record from right side table then display other wise not.
For e.g.
select empno,ename,sal,d.deptno,dname,loc
from emp e right outer join dept d
on e.deptno=d.deptno;
Output:
7839 KING 5000 10 ACCOUNTING NEW YORK
7934 MILLER 1300 10 ACCOUNTING NEW YORK
7782 CLARK 2450 10 ACCOUNTING NEW YORK
7369 SMITH 800 20 RESEARCH DALLAS
7902 FORD 3000 20 RESEARCH DALLAS
7876 ADAMS 1100 20 RESEARCH DALLAS
7788 SCOTT 3000 20 RESEARCH DALLAS
7566 JONES 2975 20 RESEARCH DALLAS
7900 JAMES 950 30 SALES CHICAGO
7499 ALLEN 1600 30 SALES CHICAGO
40 OPERATIONS BOSTON (extra Record in dept table)
Full outer join:
This join check both side and display all the records.
For e.g.
select empno,ename,sal,d.deptno,dname,loc
from emp e full outer join dept d
on e.deptno=d.deptno;
Output:
EMPNO ENAME SAL DEPTNO DNAME LOC
----- ---------- ---------- ---------- -------------- --------
1002 sams 2100
7369 SMITH 800 20 RESEARCH DALLAS
7499 ALLEN 1600 30 SALES CHICAGO
7521 WARD 1250 30 SALES CHICAGO
7566 JONES 2975 20 RESEARCH DALLAS
7654 MARTIN 1250 30 SALES CHICAGO
7698 BLAKE 2850 30 SALES CHICAGO
7782 CLARK 2450 10 ACCOUNTING NEW YORK
7788 SCOTT 3000 20 RESEARCH DALLAS
7839 KING 5000 10 ACCOUNTING NEW YORK
7844 TURNER 1500 30 SALES CHICAGO
40 OPERATIONS BOSTON
Self join:
we split one table into two copies and take alias to compare one column to another column:
For e.g.
select w.empno,w.ename,w.sal,m.ename manager_name
from emp w ,emp m where w.empno = m.mgr;
Output:
EMPNO ENAME SAL MANAGER_NA
------- ---------- ---------- ----------
7902 FORD 3000 SMITH
7698 BLAKE 2850 ALLEN
7698 BLAKE 2850 WARD
7839 KING 5000 JONES
7698 BLAKE 2850 MARTIN
7839 KING 5000 BLAKE
7839 KING 5000 CLARK
7566 JONES 2975 SCOTT
7698 BLAKE 2850 TURNER
7788 SCOTT 3000 ADAMS
7698 BLAKE 2850 JAMES
EMPNO ENAME SAL MANAGER_NA
------- ---------- ---------- ----------
7566 JONES 2975 FORD
7782 CLARK 2450 MILLER
View
View is used for clients. View has five types they are following:
1. Simple view:
Create view <view name> as select * from emp;
2. Complex view/join view
Create view r_complex as
select e.empno,e.ename,e.sal,
d.deptno,d.dname from emp e,
dept d
where e.empno=d.deptno;
3. Read only view:
create view read_view as select * from emp
with read only
4. Check view:
create view check_view as select * from emp where deptno=20 and sal>=2000
with check option
5. Force view:
create force view force_view as select *from t_ipl
One anther view are
6. MATERIALIZED VIEW:
CREATE MATERIALIZED VIEW scott.emp AS
SELECT * FROM scott.emp;
INDEX
Index has five types they are following:
1. Simple index(one column):
Create index <index name> on <table name><column name>
2. Complex index(2 column):
Create index <index name> (<col name1,col name2);
3. Unique index:
4. Bitmap index( gender / marital status / type (dr/cr) / ):
Create bitmap index on <table name><column name>
5. Cluster index:
TO BE CONTINUED
No comments:
Post a Comment