add

About Me

My photo
Oracle Apps - Techno Functional consultant

Friday, December 13

Simple sub-queries and with Group functions

-------- 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: