oracle之连接查询和子查询练习题1

连接查询和子查询练习题1

一、使用scott/tiger用户下的emp表和dept表完成下列练习，表的结构说明如下： emp员工表 (empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno部门编号)
dept部门表 (deptno部门编号/dname部门名称/loc地点) 工资 ＝ 薪金 ＋ 佣金

1. 列出在每个部门工作的员工数量、平均工资和平均服务年限。

SELECT DEPTNO,COUNT(1) AS EMPNUM,AVG(SAL+NVL(COMM,0)) AS AVGSAL ,AVG(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12) FROM EMP GROUP BY DEPTNO;

2. 列出所有员工的姓名、部门名称和工资。

SELECT T1.ENAME,T2.DNAME,T1.SAL FROM EMP T1,DEPT T2 WHERE T1.DEPTNO=T2.DEPTNO;

3. 列出从事同一种工作但属于不同部门的员工的一种组合。

SELECT DISTINCT T1.* FROM EMP T1,EMP T2 WHERE T1.JOB=T2.JOB AND T1.DEPTNO<>T2.DEPTNO;

4. 列出所有部门的详细信息和部门人数。

SELECT * FROM DEPT JOIN(SELECT DEPTNO,COUNT(1) FROM EMP GROUP BY DEPTNO) USING(DEPTNO);</