2015年8月3日--oracle

–2015年8月3日11:00:42
–子查询
–单行子查询：返回一行一列
–多行子查询：多行一列
–多列子查询：多行多列
/SELECT FROM emp
WHERE sal>(SELECT sal FROM emp WHERE ename=’JONES’ )
*/
–exce0
–1

/*SELECT ename,sal FROM emp
WHERE sal=(SELECT MAX(sal)FROM emp)*/

–2
/*SELECT ename ,job ,sal FROM emp
WHERE job=
(SELECT job FROM emp WHERE empno=7369)
AND sal>
(SELECT sal FROM emp WHERE empno=7369)*/
–exce1
–1
/*SELECT ename,hiredate FROM emp
WHERE hiredate=
(SELECT min(hiredate) FROM emp )*/

–2
/*SELECT ename ,sal ,dname
FROM emp e,dept d
WHERE loc =’CHICAGO’
AND e.deptno =d.deptno
AND sal>
(SELECT sal FROM emp WHERE ename =’SMITH’)*/

–3
/*SELECT ename ,hiredate FROM emp
WHERE hiredate<
(SELECT MIN(hiredate) FROM emp WHERE deptno =20)*/

–4
/*SELECT e.deptno ,dname ,COUNT(empno)
FROM emp e ,dept d
WHERE e.deptno =d.deptno
GROUP BY e.deptno ,dname
HAVING COUNT(empno)<
(SELECT AVG(COUNT(empno) )FROM emp GROUP BY deptno)*/

–2015年8月3日14:14:04
–> all any

)*/

–13
/*SELECT ename ,sal, avgsal ,sal-avgsal
FROM emp e ,
(
SELECT deptno ,AVG(sal ) avgsal
FROM emp
GROUP BY deptno
) b
WHERE e.deptno =b.deptno
AND e. sal >b.avgsal*/

–14
/*
SELECT dname
FROM dept d,emp e
WHERE d.deptno =e.deptno
GROUP BY e.deptno ,dname
HAVING COUNT(e.deptno)>0*/

–15
/*SELECT ename
FROM emp
WHERE sal>
(
SELECT sal
FROM emp
WHERE ename =’SMITH’
)*/

–16
/*SELECT e.ename
FROM emp m,emp e
WHERE m.empno= e.mgr
AND e.hiredate

HAVING AVG(sal)

(
SELECT MAX( AVG(sal))
FROM emp
GROUP BY deptno
)