EX.NO:3
NESTED
QUERIES AND JOINS
AIM:
To execute
nested queries and join commands in SQL.
NESTED QUERIES :
A subquery is a query within
a query. In Oracle, you can create subqueries within your SQL statements. These
subqueries can reside in the WHERE clause, the FROM clause, or the SELECT
clause.
JOINS:
Join is a query in which data is returned from two
or more tables.
How the join will be performed:
Step 1: Make the Cartesian product of the given
tables.
Step 2: Check for the equality on common attributes
for the given tables.
Natural join:
It returns the
matching rows from the table that are being joined.
Syntax:
>select
<attribute> from TN where TN1.attribute=TN2.attribute.
Inner join:
It returns the
matching rows from the table that are being joined.
Syntax:
>select
<attribute> from TN1 innerjoin TN2 on TN1.attribute=TN2.attribute.
Left outer join:
It returns all
the rows from the table1 even when they are unmatched.
Syntax:
1. select
<attribute> from TN1 left outer join TN2 on TN1.attribute=TN2.attribute.
2. select <attribute> from TN where
TN1.attribute(+)=TN2.attribute.
Right outer join:
It returns all
the rows from the table2 even when they are unmatched.
Syntax:
1. select
<attribute> from TN1 right outer join TN2 on TN1.attribute=TN2.attribute.
2. select <attribute> from TN where
TN1.attribute=(+)TN2.attribute.
Full join:
It is the
combination of both left outer and right outer join.
Syntax:
>select
<attribute> from TN1 full join TN2 on TN1.attribute=TN2.attribute.
Sample Output
NESTED QUERIES -
Table Creation
SQL> create table emp_det(eno number(3) not null, ename
varchar2(25),
address
varchar2(30),
basic_sal
number(12,2),job_status varchar2(15),dno number(3));
Table
created.
SQL>
create table pro_det(pno number(3) not null,pname varchar2(30),
no_of_staff
number(3));
Table
created.
SQL>
create table work_in(pno number(3),eno number(3),pjob char(12));
Table
created.
SQL>
desc emp_det;
Name
Null? Type
-----------------------------------------
-------- ----------------------------
ENO NOT NULL
NUMBER(3)
ENAME VARCHAR2(25)
ADDRESS VARCHAR2(30)
BASIC_SAL NUMBER(12,2)
JOB_STATUS VARCHAR2(15)
DNO
NUMBER(3)
SQL> desc
pro_det;
Name
Null? Type
-----------------------------------------
-------- ----------------------------
PNO NOT NULL
NUMBER(3)
PNAME VARCHAR2(30)
NO_OF_STAFF NUMBER(3)
SQL>
desc work_in;
Name
Null? Type
-----------------------------------------
-------- ----------------------------
PNO
NUMBER(3)
ENO
NUMBER(3)
PJOB
CHAR(12)
SQL>
insert into emp_det values(&eno,'&ename','&address',&basic_sal,
'&job_status',&dno);
Enter value
for eno: 1
Enter value
for ename: SaravanaKumar
Enter value
for address: GandhiNagar
Enter value
for basic_sal: 8000
Enter value
for job_status: Manager
Enter value
for dno: 10
old 1: insert into emp_det
values(&eno,'&ename','&address',&basic_sal,
'&job_status',&dno)
new 1: insert into emp_det
values(1,'SaravanaKumar','GandhiNagar',
8000,'Manager',10)
1 row
created.
SQL> /
Enter value
for eno: 2
Enter value
for ename: Mahendran
Enter value
for address: RainbowColony
Enter value
for basic_sal: 5000
Enter value
for job_status: Supervisor
Enter value
for dno: 10
old 1: insert into emp_det
values(&eno,'&ename','&address',&basic_sal,
'&job_status',&dno)
new 1: insert into emp_det values(2,'Mahendran','RainbowColony',5000,
'Supervisor',10)
1 row
created.
SQL> /
Enter value
for eno: 3
Enter value
for ename:Rajkumar
Enter value
for address: EastCoastRoad
Enter value
for basic_sal: 10000
Enter value
for job_status: Professor
Enter value
for dno: 2
old 1: insert into emp_det
values(&eno,'&ename','&address',&basic_sal,
'&job_status',&dno)
new 1: insert into emp_det
values(3,'RajKumar','EastCoastRoad',10000,
'Professor',2)
1 row
created.
SQL> /
Enter value
for eno: 4
Enter value
for ename: Shirley
Enter value
for address: KKnagar
Enter value
for basic_sal: 8000
Enter value
for job_status: AsstManager
Enter value
for dno: 3
old 1: insert into emp_det
values(&eno,'&ename','&address',&basic_sal,
'&job_status',&dno)
new 1: insert into emp_det
values(4,'Shirley','KKnagar',8000,
'AsstManager',3)
1 row
created.
SQL>
alter table emp_det modify(ename varchar2(15), address varchar2(15));
Table
altered.
SQL>
select * from emp_det;
ENO ENAME ADDRESS BASIC_SAL JOB_STATUS DNO
----------
--------------- --------------- ---------- --------------- ----------
1
SaravanaKumar GandhiNagar 8000 Manager 10
2
Mahendran RainbowColony 5000 Supervisor 10
3
RajKumar EastCoastRoad 10000 Professor 2
4 Shirley KKnagar 8000 AsstManager 3
SQL>
insert into pro_det values(&pno,'pname',&no_of_staff);
Enter value
for pno: 1
Enter value
for no_of_staff: 2
old 1: insert into pro_det
values(&pno,'pname',&no_of_staff)
new 1: insert into pro_det values(1,'pname',2)
1 row
created.
SQL> /
Enter value
for pno: 2
Enter value
for no_of_staff: 3
old 1: insert into pro_det
values(&pno,'pname',&no_of_staff)
new 1: insert into pro_det values(2,'pname',3)
1 row
created.
SQL> /
Enter value
for pno: 3
Enter value
for no_of_staff: 1
old 1: insert into pro_det values(&pno,'pname',&no_of_staff)
new 1: insert into pro_det values(3,'pname',1)
1 row
created.
SQL>
select * from pro_det;
PNO PNAME NO_OF_STAFF
----------
------------------------------ -----------
1 pname
2
2 pname 3
3 pname 1
SQL>update
pro_det set pname='DBMS' where pno=1;
1 row
updated.
SQL>
update pro_det set pname='COMPILER' where pno=2;
1 row
updated.
SQL>update
pro_det set pname='C' where pno=3;
1 row
updated.
SQL>
select * from Pro_det;
PNO PNAME NO_OF_STAFF
----------
------------------------------ -----------
1 DBMS 2
2
COMPILER
3
3 C 1
SQL>
insert into work_in values(&pno,&eno,'&pjob');
Enter value
for pno: 1
Enter value
for eno: 1
Enter value
for pjob: Programmer
old 1: insert into work_in
values(&pno,&eno,'&pjob')
new 1: insert into work_in
values(1,1,'Programmer')
1 row
created.
SQL> /
Enter value
for pno: 2
Enter value
for eno: 1
Enter value
for pjob: Analyst
old 1: insert into work_in
values(&pno,&eno,'&pjob')
new 1: insert into work_in values(2,1,'Analyst')
1 row
created.
SQL> /
Enter value
for pno: 1
Enter value
for eno: 2
Enter value
for pjob: Analyst
old 1: insert into work_in
values(&pno,&eno,'&pjob')
new 1: insert into work_in values(1,2,'Analyst')
1 row
created.
SQL> /
Enter value
for pno: 2
Enter value
for eno: 2
Enter value
for pjob: Programmer
old 1: insert into work_in
values(&pno,&eno,'&pjob')
new 1: insert into work_in
values(2,2,'Programmer')
1 row
created.
SQL>
select * from work_in;
PNO ENO PJOB
--
---------- ------------
1 1 Programmer
2 1 Analyst
1 2 Analyst
2 2 Programmer
NESTED QUERIES
(i) SQL>
select ename from emp_det where dno not in(select dno from
emp_det
where ename = 'SaravanaKumar');
ENAME
---------------
RajKumar
Shirley
(ii)SQL>
select ename, dno from emp_det where dno = (select dno from
emp_det
where ename = 'RajKumar');
ENAME DNO
---------------
----------
RajKumar 2
(iii)SQL>
select ename from emp_det where eno in(select eno from work_in
where pno = (select pno from
pro_det
where pname = 'DBMS')) order by ename;
ENAME
---------------
Mahendran
SaravanaKumar
(iv)SQL>
select ename, basic_sal from emp_det where dno = 2 and
basic_sal>(select
max(basic_sal) from
emp_det
where dno = 10) order by ename;
ENAME BASIC_SAL
---------------
----------
RajKumar 10000
(v)SQL>
select pno,pname from pro_det where exists(select pno from
work_in
where work_in.pno = pro_det.
pno);
PNO PNAME
------
------------------------------
1 DBMS
2 COMPILER
(vi)SQL>select
ename, job_status,basic_sal from emp_det where (dno,basic_sal)
in (select dno,basic_sal
from emp_det where ename
='RajKumar');
ENAME JOB_STATUS BASIC_SAL
---------------
--------------- ----------
RajKumar Professor 10000
(vii)SQL>select
* from emp_det where basic_sal=(select max(basic_sal) from emp_det);
ENO ENAME ADDRESS BASIC_SAL JOB_STATUS DNO
------
--------------- --------------- ---------- --------------- ----------
3 RajKumar EastCoastRoad 10000 Professor 2
(viii)SQL>select
max(basic_sal) from emp_det where basic_sal< (select max(basic_sal)
from emp_det);
MAX(BASIC_SAL)
---------------
8000
(ix)SQL>
select * from emp_det where basic_sal < (select avg(basic_sal) from
emp_det);
ENO ENAME ADDRESS BASIC_SAL JOB_STATUS DNO
---- --------------- --------------- ---------- --------------- ----------
2 Mahendran RainbowColony 5000
Supervisor 10
No comments:
Post a Comment