Wednesday, October 23, 2013

Sub Queries



-------- Simple Sub Queries ----------------
SELECT Ename, Sal, Job
FROM Emp
WHERE Sal > (SELECT Sal
            FROM Emp
            WHERE Empno = 7566);
           
SELECT Empno, Ename, Hiredate, Sal
FROM Emp
WHERE Hiredate > (SELECT Hiredate
                  FROM Emp
                  WHERE Ename = 'TURNER')
ORDER BY Sal;

SELECT Ename, Sal, Job
FROM Emp
WHERE Job = (SELECT Job
             FROM Emp
             WHERE Ename = UPPER('smith'))
ORDER BY Sal;          


SELECT Empno, Ename, Sal, Job
FROM Emp
WHERE Deptno = (SELECT Deptno
                FROM Dept
                WHERE Dname = 'SALES');
               
SELECT Empno, Ename, Sal, Comm, Sal + NVL( Comm, 0 )
FROM Emp
WHERE Deptno = (SELECT Deptno
                FROM Dept
                WHERE Loc = 'DALLAS'); 
       
------------- SubQueries With Group Functions --------------
SELECT Ename, Job , Sal
FROM Emp
WHERE Sal = (SELECT MAX(Sal)
            FROM Emp);
           
SELECT Ename, Job, Sal
FROM Emp
WHERE Sal = (SELECT MIN(Sal)
            FROM Emp);

SELECT Ename, Job, Sal
FROM Emp
WHERE Sal > (SELECT AVG(Sal)
            FROM Emp);

SELECT Ename, Job, Sal
FROM Emp
WHERE Sal < (SELECT STDDEV(Sal)
            FROM Emp);         


No comments:

Post a Comment

Best Blogger TipsGet Flower Effect