Examples of Sql complex select queries

Examples of Sql complex select queries

 select count(MGR),count(sal) from salesmans;  
 select ename,(sal+nvl(comm,0)) as totalsal from salesmans;  
 select * from salesmans where sal> any(select sal from salesmans where sal<3000);  
 select * from salesmans where sal> all(select sal from salesmans where sal<3000);  
 select ename,deptno,sal from salesmans order by deptno,sal desc;  
 Create table salesmans1 as select * from salesmans where 1=2;  
 Select * from salesmans where sal>=1000 And sal<2000;  
 select * from salesmans where rowid in (select decode(mod(rownum,2),0,rowid, null) from salesmans);  
 select * from salesmans where rowid in (select decode(mod(rownum,2),0,null ,rowid) from salesmans);  
 select * from dept where deptno not in (select deptno from salesmans);  
 select * from dept a where not exists (select * from salesmans b where a.deptno = b.deptno);  
 select salesmansno,ename,b.deptno,dname from salesmans a, dept b where a.deptno(+) = b.deptno and salesmansno is null;  
 select distinct sal from salesmans a where 3 >= (select count(distinct sal) from salesmans b where a.sal <= b.sal) order by a.sal desc;  
 select distinct sal from salesmans a where 3 >= (select count(distinct sal) from salesmans b where a.sal >= b.sal);  
 select distinct hiredate from salesmans a where &n = (select count(distinct sal) from salesmans b where a.sal >= b.sal);  
 select * from salesmans a where rowid = (select max(rowid) from salesmans b where a.salesmansno=b.salesmansno);  
 delete from salesmans a where rowid != (select max(rowid) from salesmans b where a.salesmansno=b.salesmansno);  
 select count(salesmansNO), b.deptno, dname from salesmans a, dept b where a.deptno(+)=b.deptno group by b.deptno,dname;  
 select ename,sal/12 as monthlysal from salesmans;  
 select * from salesmans where deptno=11 or deptno=12;  
 select * from salesmans where deptno=20 and sal>3500;  
 select * from salesmans where job not in ('SALESMAN','SalesExec');  
 select * from salesmans where ename in('JS','BLE','SCT','KG','FD');  
 select * from salesmans where exists(select * from dept where salesmans.deptno=dept.deptno);  
 (Select * from salesmans) Union (Select * from salesmans1);  
 (Select * from salesmans) Intersect (Select * from salesmans1);  
 (Select * from salesmans) Minus (Select * from salesmans1);  
 SELECT deptno, sum(sal) As totalsal FROM salesmans GROUP BY deptno HAVING COUNT(salesmansno) > 2;  
 select * from salesmans where ename like'S____';  
 select * from salesmans where ename like'%R';  
 select distinct sal from salesmans e1 where 3 = (select count(distinct sal) from salesmans e2 where e1.sal <= e2.sal);  
 select distinct sal from salesmans e1 where 3 = (select count(distinct sal) from salesmans e2 where e1.sal >= e2.sal);  
 select * from salesmans where rownum <= &n;  
 select * from salesmans minus select * from salesmans where rownum <= (select count(*) - from salesmans);