Nested Queries
Subquery:
Subquery is a query placed within another query. A subquery may
return either one or multiple rows.
The subquery (inner query) executes
once before the main query.
The result of the subquery is used
by the main query (outerquery).
Comparison operators fall into two classes:
single-row operators
( > , = , >= , < , < > , <=
)
and multiple-row operators ( IN , ANY , ALL ).
Ex:select ename from emp where sal=(select max(sal) from emp);
Guidelines for Using
Subqueries
•Enclose subqueries in parentheses.
•Place subqueries on the right side of the comparison
operator.
•Do not add an ORDER BY clause to a subquery.
•Use single-row operators with singlerow subqueries.
•Use multiple-row operators with multiple-row subqueries
correlated subquery.
When a
subquery takes data from main query it is called as correlated subquery.
EX:
Find out
names of employee whose salaries greater than salaries of their managers.
Select
e1.ename from emp e1
Where
e1.mgr in
(select
e2.empno from emp e2
Where
e2.sal<e1.sal);
oNorm� _ t l �m� �� eight:150%'>Selecting data from sailors
table
Selecting data from reserves
table
Selecting data from boat
table
1) find
the names of sailors who have reserved boat 103
2) find
the names of sailors who have reserved a red boat
3) Find
the name and age of the oldest sailors
4) Count
the number of sailors.
5) Count
the number of different sailors names
6) find
the names of sailors who have not reserved a red boat
7) find
the names of sailors who have not reserved boat 103
8) find
the names of sailors who have reserved at least one boat
9)
Compute
increments for the ratings of persons who have sailed two different
boats on the same day.
10) Find
the names of sailors who have reserved a red or a green boat
11) find
the all sids of sailors who have rating 10 or have reserved boat 104..
12) Find
the number of reservations for each red boat.
13) Find
the minimum age of the sailor.
14) Find
the sum of the rating of sailors
15) find the id and names of sailors who have reserved id=22 or
age<25.
Full join
SQL>
select s.sid,s.sname,o.orderdate
from
suppliers s
full join
orders o
on s.sid=o.sid;
SID SNAME ORDERDATE
----------
--------------- ---------
11 ibm 12-AUG-10
33 microsoft 09-NOV-11
11 ibm 07-SEP-09
44 sony
22 hewlett packard
13-DEC-12
type of join operation to
perform
Display
names of the employees and their loc.(cartisian product)
Display
names of the employees along their manager names.(self join)
Display
names of the employees who are working
in NEW YORK.(equi join)
Display
names of the employee,salary of each and grade of each .(non equi join)
SQL> select *
from student;
no rows selected
SQL> rollback;
Rollback
complete.
SQL> select *
from student;
ROLLNO NAME AGGR ADDR
----------
-------------------- ---------- ----------
1 akshay 98.5
2 shruthi 97.4
3 venkat 87.6
SQL> drop
table student;
Table dropped.
SQL> create table student(rollno number,name
varchar2(20));
Table created.
SQL> insert
into student values(10,'askhay');
1 row created.
SQL> insert
into student values(20,'ravi');
1 row created.
SQL> select *
from student;
ROLLNO NAME
----------
--------------------
10 askhay
20 ravi
SQL> commit;
Commit complete.
SQL> savepoint
p;
Savepoint
created.
SQL> update
student set name='madhu' where rollno=1;
0 rows updated.
SQL> update
student set name='madhu' where rollno=10;
1 row updated.
SQL> select *
from student;
ROLLNO NAME
----------
--------------------
10 madhu
20 ravi
SQL> rollback
to p;
Rollback
complete.
SQL> select *
from student;
ROLLNO NAME
----------
--------------------
10 askhay
20 ravi