Oracle11g普通查询练习

最近请假学车，又学数据库，每天只能睡5个小时左右 实在有点儿累……等忙过这阵子再补上详细的题干

不过我觉得其实题干可有可无，应该能从代码看出题意 哈哈

初学SQL，水平有限，我先把自己编的东西都记录下来，等日后水平有所提高，再来改进这些代码

题目源于两个视频：

“MLDN魔乐科技_Oracle课堂15_SQL语法练习1.rar” 和“ MLDN魔乐科技_Oracle课堂15_SQL语法练习2.rar”

应该可以搜得到，我就不赘述了

20.
SELECT e.deptno 部门编号,COUNT(empno) 部门人数，SUM(sal) 工资合计
FROM emp e
WHERE e.deptno IN (SELECT d.deptno FROM dept d WHERE d.dname LIKE ('%S%'))
GROUP BY e.deptno
;


19.
SELECT e.empno,e.ename,m.ename,m.sal
FROM emp e,emp m
WHERE e.mgr=m.empno AND m.sal>=3000
;


18.
SELECT ename,(sal+NVL(comm,0))
FROM emp
ORDER BY sal ASC
;  



17.
SELECT e.deptno,MIN(sal)
FROM emp e
WHERE e.job IN ('MANAGER')
GROUP BY deptno
ORDER BY e.deptno ASC
;  





SELECT ename,mst.j,mst.ms
FROM emp,
(SELECT job j,MIN(sal) ms FROM emp GROUP BY job)mst
WHERE job IN mst.j AND sal IN mst.ms
;




SELECT DISTINCT d.deptno,d.dname,dc.c
FROM dept d,
(SELECT deptno d,COUNT(empno) c FROM emp GROUP BY deptno)dc
WHERE dc.d=d.deptno
;



SELECT e.empno,e.ename,e.deptno,d.dname,e.sal
FROM emp e,dept d
WHERE e.deptno=d.deptno 
;

SELECT DISTINCT e.deptno 部门名称,dn.c 部门人员,ds.s 部门平均工资,dh.h 部门平均雇佣期
FROM emp e,
(SELECT deptno d,COUNT(empno) c FROM emp GROUP BY deptno)dn,
(SELECT deptno d,AVG(sal) s FROM emp GROUP BY deptno)ds,
(SELECT deptno d,AVG(MONTHS_BETWEEN(sysdate,hiredate)/12) h FROM emp GROUP BY deptno)dh
WHERE e.deptno=dn.d AND dn.d=ds.d AND ds.d=dh.d
;


SELECT e.ename,e.sal,d.dname
FROM emp e,dept d
WHERE 
sal >ALL (SELECT e.sal FROM emp e WHERE e.deptno=30) AND e.deptno=d.deptno
; 



SELECT e.ename,e.deptno,d.dname
FROM emp e,dept d
WHERE e.job IN (SELECT job FROM emp WHERE ename IN ('SCOTT')) AND e.ename NOT IN ('SCOTT') AND e.deptno=d.deptno
;


SELECT e.empno,e.ename,d.dname,e.mgr,m.ename,e.sal,s.grade
FROM emp e,emp m,dept d,salgrade s
WHERE e.sal>(SELECT AVG(sal) a FROM emp) AND e.mgr=m.empno(+) AND e.deptno=d.deptno AND e.sal BETWEEN s.losal AND s.hisal
;


SELECT e.ename,sn.deptno,e.deptno
FROM emp e,
(SELECT deptno
FROM dept
WHERE dname IN ('SALES'))sn
WHERE e.deptno=sn.deptno 
;


SELECT e.job,COUNT(e.job)
FROM emp e,
(SELECT job FROM emp WHERE sal>1500 GROUP BY job)st
WHERE st.job=e.job
GROUP BY e.job;


SELECT e.ename en,e.deptno ed,d.dname,dd.dno,dd.cou
FROM emp e,dept d,
(SELECT e.deptno dno,COUNT(e.deptno) cou
FROM emp e
GROUP BY e.deptno)dd
WHERE e.job IN('CLERK') AND dd.dno=e.deptno AND e.deptno=d.deptno;


SELECT e.deptno,COUNT(e.deptno)
FROM dept d,emp e,
WHERE d.deptno=e.deptno AND dd.ed=e.deptno
GROUP BY e.deptno;



SELECT *
FROM emp e,dept d,
(SELECT e.deptno eno
FROM emp e,dept d
WHERE e.deptno(+)=d.deptno
GROUP BY e.deptno)dd
WHERE e.deptno=dd.eno AND e.deptno=d.deptno
;


SELECT mhi.en,mhi.ena,d.dname 
FROM dept d,
(SELECT e.empno en,e.ename ena,e.hiredate eh,e.deptno ed,m.empno mn,m.ename mna,m.hiredate mh
FROM emp e,emp m
WHERE e.mgr=m.empno)mhi
WHERE mhi.ed=d.deptno AND mhi.eh<mhi.mh
;



SELECT e.ename na,e.mgr,m.ename
FROM emp e,emp m
WHERE e.mgr=m.empno(+)
;


SELECT * FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename IN ('SMITH'));



SELECT d.dname,st.dno,st.cno
FROM dept d,
(SELECT deptno dno,COUNT(empno) cno FROM emp GROUP BY deptno HAVING COUNT(empno)>1)st
WHERE d.deptno=st.dno;



SELECT d.dname,ed.c,ed.a,e.ename,ed.min
FROM dept d,emp e,
	(SELECT e.deptno ,COUNT(empno) c,AVG(sal) a,MIN(sal) min
	FROM dept d,emp e
	WHERE (e.deptno=d.deptno)
	GROUP BY e.deptno
	)ed 
WHERE d.deptno=ed.deptno AND e.sal=ed.min
;

==============================================================================

以下是txt版本的，内容与上边完全一样

20.

SELECT e.deptno 部门编号,COUNT(empno) 部门人数，SUM(sal) 工资合计
FROM emp e
WHERE e.deptno IN (SELECT d.deptno FROM dept d WHERE d.dname LIKE ('%S%'))
GROUP BY e.deptno
;




19.
SELECT e.empno,e.ename,m.ename,m.sal
FROM emp e,emp m
WHERE e.mgr=m.empno AND m.sal>=3000
;




18.
SELECT ename,(sal+NVL(comm,0))
FROM emp
ORDER BY sal ASC
;  





17.
SELECT e.deptno,MIN(sal)
FROM emp e
WHERE e.job IN ('MANAGER')
GROUP BY deptno
ORDER BY e.deptno ASC
;  










SELECT ename,mst.j,mst.ms
FROM emp,
(SELECT job j,MIN(sal) ms FROM emp GROUP BY job)mst
WHERE job IN mst.j AND sal IN mst.ms
;








SELECT DISTINCT d.deptno,d.dname,dc.c
FROM dept d,
(SELECT deptno d,COUNT(empno) c FROM emp GROUP BY deptno)dc
WHERE dc.d=d.deptno
;






SELECT e.empno,e.ename,e.deptno,d.dname,e.sal
FROM emp e,dept d
WHERE e.deptno=d.deptno 
;


SELECT DISTINCT e.deptno 部门名称,dn.c 部门人员,ds.s 部门平均工资,dh.h 部门平均雇佣期
FROM emp e,
(SELECT deptno d,COUNT(empno) c FROM emp GROUP BY deptno)dn,
(SELECT deptno d,AVG(sal) s FROM emp GROUP BY deptno)ds,
(SELECT deptno d,AVG(MONTHS_BETWEEN(sysdate,hiredate)/12) h FROM emp GROUP BY deptno)dh
WHERE e.deptno=dn.d AND dn.d=ds.d AND ds.d=dh.d
;




SELECT e.ename,e.sal,d.dname
FROM emp e,dept d
WHERE 
sal >ALL (SELECT e.sal FROM emp e WHERE e.deptno=30) AND e.deptno=d.deptno
; 






SELECT e.ename,e.deptno,d.dname
FROM emp e,dept d
WHERE e.job IN (SELECT job FROM emp WHERE ename IN ('SCOTT')) AND e.ename NOT IN ('SCOTT') AND e.deptno=d.deptno
;




SELECT e.empno,e.ename,d.dname,e.mgr,m.ename,e.sal,s.grade
FROM emp e,emp m,dept d,salgrade s
WHERE e.sal>(SELECT AVG(sal) a FROM emp) AND e.mgr=m.empno(+) AND e.deptno=d.deptno AND e.sal BETWEEN s.losal AND s.hisal
;




SELECT e.ename,sn.deptno,e.deptno
FROM emp e,
(SELECT deptno
FROM dept
WHERE dname IN ('SALES'))sn
WHERE e.deptno=sn.deptno 
;




SELECT e.job,COUNT(e.job)
FROM emp e,
(SELECT job FROM emp WHERE sal>1500 GROUP BY job)st
WHERE st.job=e.job
GROUP BY e.job;




SELECT e.ename en,e.deptno ed,d.dname,dd.dno,dd.cou
FROM emp e,dept d,
(SELECT e.deptno dno,COUNT(e.deptno) cou
FROM emp e
GROUP BY e.deptno)dd
WHERE e.job IN('CLERK') AND dd.dno=e.deptno AND e.deptno=d.deptno;




SELECT e.deptno,COUNT(e.deptno)
FROM dept d,emp e,
WHERE d.deptno=e.deptno AND dd.ed=e.deptno
GROUP BY e.deptno;






SELECT *
FROM emp e,dept d,
(SELECT e.deptno eno
FROM emp e,dept d
WHERE e.deptno(+)=d.deptno
GROUP BY e.deptno)dd
WHERE e.deptno=dd.eno AND e.deptno=d.deptno
;




SELECT mhi.en,mhi.ena,d.dname 
FROM dept d,
(SELECT e.empno en,e.ename ena,e.hiredate eh,e.deptno ed,m.empno mn,m.ename mna,m.hiredate mh
FROM emp e,emp m
WHERE e.mgr=m.empno)mhi
WHERE mhi.ed=d.deptno AND mhi.eh<mhi.mh
;






SELECT e.ename na,e.mgr,m.ename
FROM emp e,emp m
WHERE e.mgr=m.empno(+)
;




SELECT * FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename IN ('SMITH'));






SELECT d.dname,st.dno,st.cno
FROM dept d,
(SELECT deptno dno,COUNT(empno) cno FROM emp GROUP BY deptno HAVING COUNT(empno)>1)st
WHERE d.deptno=st.dno;






SELECT d.dname,ed.c,ed.a,e.ename,ed.min
FROM dept d,emp e,
(SELECT e.deptno ,COUNT(empno) c,AVG(sal) a,MIN(sal) min
FROM dept d,emp e
WHERE (e.deptno=d.deptno)
GROUP BY e.deptno
)ed 
WHERE d.deptno=ed.deptno AND e.sal=ed.min

;