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);