Concepts and Code for U: SQL Queries

Tuesday, September 18, 2007

SQL Queries

Query to retrive top 3 records:

select distinct (a.sal) from empkris a where 3 =(select count (distinct(b.sal)) from empkris bwhere a.sal<=b.sal);

Odd number of records:

select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);

Even number of records:

select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp);

1:25 AM | Permalink

To get the list of employees who works under sales department using cursor

Posted by Unknown | 3:49 AM

To get the list of employees who works under sales department using cursor

declare
cursor c is select E.empno,E.ename,E.sal,D.dname
from emp E,dept D where d.dname='SALES';
vno emp.empno%type;
vname emp.ename%type;
vsal emp.sal%type;
vdname dept.dname%type;
begin
IF c%ISOPEN THEN
CLOSE c;
end if;
open c;
loop
FETCH c into vno,vname,vsal,vdname;
EXIT when c%NOTFOUND;
dbms_output.put_line('THE EMPS DETAILS:'||vno||' '||vname||' '||vsal||' '||vdname);
end loop;
end;