Oracle SQL：经典入门级查询练习（1）

下面是一些sql语句的练习题目，后面附加有详细的答案，可作为入门练习参考。

用SQL完成以下问题列表：
1、列出至少有一个员工的所有部门。
2、列出薪金比“SMITH”多的所有员工。
3、列出所有员工的姓名及其直接上级的姓名。
4、列出受雇日期早于其直接上级的所有员工。
5、列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
6、列出所有“CLERK”(办事员)的姓名及其部门名称。
7、列出最低薪金大于1500的各种工作。
8、列出在部门“SALES”(销售部)工作的员工的姓名，假定不知道销售部的部门编号。
9、列出薪金高于公司平均薪金的所有员工。
10、列出与“SCOTT”从事相同工作的所有员工。
11、列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
12、列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
13、列出在每个部门工作的员工数量、平均工资和平均服务期限。
14、列出所有员工的姓名、部门名称和工资。
15、列出所有部门的详细信息和部门人数。
16、列出各种工作的最低工资。
17、列出各个部门的MANAGER(经理)的最低薪金。
18、列出所有员工的年工资,按年薪从低到高排序。

/*查看有哪些表存在*/
select * from tab;
 
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BONUS                          TABLE   
DEPT                           TABLE   
EMP                            TABLE   
SALGRADE                       TABLE   
/*查看bonus表的全部数据*/
SELECT  * FROM bonus;

/*查看dept表的全部数据*/
SELECT  * FROM dept;

/*查看emp表的全部数据*/
SELECT  * FROM emp;

/*查看salgrade表的全部数据*/ 
SELECT  * FROM salgrade;


/*1、列出至少有一个员工的所有部门。*/
SELECT COUNT(empno),deptno FROM emp GROUP BY deptno HAVING  COUNT(empno)>

/*2、列出薪金比“SMITH”多的所有员工。*/
SELECT * FROM emp WHERE sal >(SELECT sal FROM emp WHERE ename='SMITH');/*注意括号不能省略*/

/*3、列出所有员工的姓名及其直接上级的姓名。*/
SELECT e.ename,m.ename FROM emp e,emp m WHERE e.mgr = m.empno;

/*4、列出受雇日期早于其直接上级的所有员工。*/
SELECT e.empno,e.ename,m.empno, e.hiredate,m.hiredate FROM emp e,emp m WHERE e.mgr = m.empno AND e.hiredate < m.hiredate;
select a.ename from emp a where a.hiredate<(select hiredate from emp b where b.empno=a.mgr);


/*5、列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门*/
 SELECT d.dname,e.empno,e.ename,e.job,e.hiredate,e.sal FROM emp e RIGHT JOIN dept d ON e.deptno=d.deptno;
 select a.dname,b.empno,b.ename,b.job,b.mgr,b.hiredate,b.sal,b.deptno from dept a left join emp b on a.deptno=b.deptno; 

/*6、列出所有“CLERK”(办事员)的姓名及其部门名称。*/
SELECT e.ename,d.dname FROM emp e ,dept d WHERE e.deptno = d.deptno AND e.job = 'CLERK';
select a.ename,b.dname from emp a join dept b on a.deptno=b.deptno and a.job='CLERK';   

/*7、列出最低薪金大于1500的各种工作。*/
SELECT DISTINCT(e.job) FROM emp e group by job HAVING MIN(e.sal)>1500;

/*8、列出在部门“SALES”(销售部)工作的员工的姓名，假定不知道销售部的部门编号。*/
SELECT e.ename FROM emp e JOIN dept d ON e.deptno = d.deptno AND d.dname='SALES';
SELECT e.ename FROM emp e,dept d WHERE e.deptno = d.deptno AND d.dname='SALES';
select ename from emp where deptno=(select deptno from dept where dname='SALES'); 

/*9、列出薪金高于公司平均薪金的所有员工。*/
SELECT ename FROM emp WHERE sal > (select avg(sal) from emp);

/*10、列出与“SCOTT”从事相同工作的所有员工。*/
SELECT ename FROM emp WHERE job = (SELECT job FROM emp WHERE ename='SCOTT') AND ename!='SCOTT';/*SCOTT不算在内*/
select ename from emp where job=(select job from emp where ename='SCOTT');  /*SCOTT算在内*/

/*11、列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。*/
SELECT ename,sal FROM emp WHERE sal IN (SELECT sal FROM emp WHERE deptno=30) AND deptno!=30;
SELECT ename,sal FROM emp WHERE sal IN (SELECT sal FROM emp WHERE deptno=30) AND deptno<>30;

/*12、列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。*/
SELECT ename,sal FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno=30) AND deptno!=30;/*其实没有必要加 deptno!=30这个条件，因为已经是大于部门30中的最大薪金*/
select ename,sal from emp where sal>(select max(sal) from emp where deptno=30);

/*13、列出在每个部门工作的员工数量、平均工资和平均服务期限。*/
SELECT COUNT(empno),SUM(sal)/COUNT(empno),AVG((SYSDATE-hiredate)/365) FROM emp GROUP BY(deptno);  
SELECT COUNT(empno),AVG(sal),AVG((SYSDATE-hiredate)/365) FROM emp GROUP BY(deptno);

/*14、列出所有员工的姓名、部门名称和工资。*/
SELECT e.ename,d.dname,e.sal FROM emp e, dept d WHERE e.deptno=d.deptno;
SELECT e.ename,d.dname,e.sal FROM emp e JOIN dept d ON e.deptno=d.deptno;
select a.ename,(select b.dname from dept b where b.deptno=a.deptno) as deptname,sal from emp a

/*15、列出所有部门的详细信息和部门人数。*/
SELECT d.deptno,d.dname,d.loc, (SELECT COUNT(empno) from emp e WHERE e.deptno = d.deptno GROUP BY e.deptno) as emp_count FROM dept d;

/*16、列出各种工作的最低工资。*/
SELECT job, MIN(sal) FROM emp GROUP BY job;
SELECT job, MIN(sal),MAX(sal),AVG(sal) FROM emp GROUP BY job;/*查看最低、最高、平均工资*/

/*17、列出各个部门的MANAGER(经理)的最低薪金。*/
SELECT deptno, MIN(sal) FROM (SELECT sal,deptno FROM emp WHERE job='MANAGER') GROUP BY deptno;
SELECT deptno, MIN(sal),MAX(sal),AVG(sal) FROM (SELECT sal,deptno FROM emp WHERE job='MANAGER') GROUP BY deptno;/*查看最低、最高、平均工资*/
select deptno,min(sal) from emp where job='MANAGER' group by deptno;

/*18、列出所有员工的年工资,按年薪从低到高排序。*/
SELECT ename,12*(sal+NVL(comm,0)) AS year_sal FROM emp ORDER BY year_sal;/*NVL(variable,x,)表示当变量variable为NULL的时候用x来替代*/