************************
JOINS
************************
SQL>
create table emp(name varchar2(20),salary number(10));
Table
created.
SQL>
insert into emp values('&name',&salary);
Enter value
for name: ashu
Enter value
for salary: 10000
old 1: insert into emp
values('&name',&salary)
new 1: insert into emp values('ashu',10000)
1 row
created.
SQL> /
Enter value
for name: asma
Enter value
for salary: 1200
old 1: insert into emp
values('&name',&salary)
new 1: insert into emp values('asma',1200)
1 row
created.
SQL> /
Enter value
for name: asif
Enter value
for salary: 2000
old 1: insert into emp values('&name',&salary)
new 1: insert into emp values('asif',2000)
1 row
created.
SQL> /
Enter value
for name: arif
Enter value
for salary: 1000
old 1: insert into emp
values('&name',&salary)
new 1: insert into emp values('arif',1000)
1 row
created.
SQL> /
Enter value
for name: niyas
Enter value
for salary: 3000
old 1: insert into emp
values('&name',&salary)
new 1: insert into emp values('niyas',3000)
1 row
created.
SQL>
select * from emp;
NAME SALARY
--------------------
----------
ashu 10000
asma 1200
asif 2000
arif 1000
niyas 3000
SQL>
create table emp1(name varchar2(20),empid number(10));
Table
created.
SQL>
insert into emp1 values('&name',&empid);
Enter value
for name: fathi
Enter value
for empid: 12
old 1: insert into emp1
values('&name',&empid)
new 1: insert into emp1 values('fathi',12)
1 row
created.
SQL> /
Enter value
for name: sumi
Enter value
for empid: 32
old 1: insert into emp1
values('&name',&empid)
new 1: insert into emp1 values('sumi',32)
1 row
created.
SQL> /
Enter value
for name: priya
Enter value
for empid: 11
old 1: insert into emp1
values('&name',&empid)
new 1: insert into emp1 values('priya',11)
1 row
created.
SQL> /
Enter value
for name: wahab
Enter value
for empid: 10
old 1: insert into emp1
values('&name',&empid)
new 1: insert into emp1 values('wahab',10)
1 row
created.
SQL> /
Enter value
for name: sweety
Enter value
for empid: 09
old 1: insert into emp1
values('&name',&empid)
new 1: insert into emp1 values('sweety',09)
1 row
created.
SQL>/
Enter value
for name: asma
Enter value
for empid: 1200
old 1: insert into emp1
values('&name',&empid)
new 1: insert into emp1 values('asma',1200)
1 row
created.
SQL>
select * from emp1;
NAME EMPID
--------------------
----------
fathi 12
sumi 32
priya 11
wahab 10
sweety 9
asma 1200
6 rows
selected.
NATURAL
JOIN
************
SQL>select
emp.name,salary from emp,emp1 where emp.name=emp1.name
NAME SALARY
--------------------
----------
asma 1200
LEFT OUTER
JOIN
****************
SQL>select
emp.name,salary from emp left outer join emp1 on emp.name=emp1.name
NAME SALARY
--------------------
----------
asma 1200
asif 2000
arif 1000
niyas 3000
ashu 10000
RIGHT OUTER
JOIN
*****************
SQL>select
emp1.name,empid from emp right outer join emp1 on emp.name=emp1.name
NAME EMPID
--------------------
----------
asma 1200
sweety 9
sumi 32
wahab 10
fathi 12
priya 11
6 rows
selected.
FULL JOIN
***********
SQL>select
emp1.name,emp.name,emp1.empid,salary from emp full join emp1 on
emp.name=emp1.name
NAME NAME EMPID SALARY
--------------------
-------------------- ---------- ----------
asma asma 1200 1200
asif 2000
arif 1000
niyas
3000
ashu
10000
sweety 9
sumi
32
wahab 10
fathi 12
priya 11
10 rows
selected.
RESULT:
Thus the nested
queries and join operations are executed and verifiedin DBMS.
No comments:
Post a Comment