oracle数据库sql语句练习

正在学习oracle数据库sql语句，从网上找了各种资料学习整理了下面一些有代表性的题目，希望对初学sql的同学们有帮助。

使用scott/tiger用户下的emp表和dept表完成下列练习

1．列出至少有一个员工的所有部门
>select * from dept
>where deptno in
>(select deptno from emp group by deptno having count(*) > 0);

2．列出薪金比“SMITH”多的所有员工。
>SELECT * FROM emp
>WHERE sal >
>(SELECT sal FROM emp WHERE ename = 'SMITH');

3．列出所有员工的姓名及其直接上级的姓名。(自连接)\
>SELECT worker.empno w_empno,worker.ename w_ename,manager.empno m_empno,manager.ename m_ename
>FROM emp worker,emp manager
>WHERE worker.mgr = manager.empno;

4．列出受雇日期晚于其直接上级的所有员工。(自连接)
>SELECT worker.empno w_empno,worker.ename w_ename,manager.empno m_empno,manager.ename m_ename,
worker.hiredate w_hiredate,manager.hiredate m_hiredate
>FROM emp worker,emp manager
>WHERE worker.mgr = manager.empno AND worker.hiredate > manager.hiredate;

5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。(外连接)
>SELECT e.empno,e.ename,e.job,d.deptno,d.dname
>FROM emp e RIGHT OUTER JOIN dept d
>ON e.deptno = d.deptno;
6．列出所有“CLERK”（办事员）的姓名及其部门名称。
>SELECT e.ename,e.job,d.deptno,d.dname
>FROM emp e,dept d
>WHERE e.deptno = d.deptno AND e.job = 'CLERK';

可以应用 子查询
select (select dname from dept where deptno=a.deptno) as dname ，ename

from emp a

where job=‘CLERK‘;
7．列出最低薪金大于1500的各种工作。
>SELECT job FROM emp GROUP BY job HAVING MIN(sal) > 1500;

8．列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。
>SELECT e.ename, e.job, d.dname, d.deptno FROM emp e, dept d
>WHERE e.deptno in
>(select d.deptno from dept d WHERE d.dname = 'SALES')
> AND d.dname = 'SALES';

9．列出薪金高于公司平均薪金的所有员工。
>SELECT empno,ename,job,sal FROM emp WHERE sal >
>(SELECT AVG(sal) FROM emp);

10．列出与“SCOTT”从事相同工作的所有员工。
>SELECT empno,ename,job FROM emp WHERE job =
>(SELECT job FROM emp WHERE ename = 'SCOTT')
>AND ename <> 'SCOTT';

11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
>SELECT ename,job,sal FROM emp
>WHERE sal in (select sal FROM emp WHERE deptno = 30);

12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。 >ALL
>SELECT ename,job,sal FROM emp
>WHERE sal >ALL(select sal FROM emp WHERE deptno = 30);

13．列出在每个部门工作的员工数量、平均工资和平均服务期限。
>SELECT deptno,COUNT(*) count_num,avg(sal),avg(SYSDATE - hiredate) FROM

emp
>GROUP BY deptno;

14．列出所有员工的姓名、部门名称和工资。 内连接
1。
>SELECT e.ename,d.dname,e.job,e.sal
>FROM emp e,dept d
>WHERE e.deptno = d.deptno;
2.
>SELECT e.ename,d.dname,e.job,e.sal
>FROM emp e NATURAL JOIN dept d; -- 两个表必须含有同名且类型相同的列
3.
>SELECT e.ename,d.dname,e.job,e.sal
>FROM emp e JOIN dept d
>USING(deptno); -- 两个表中相同列的类型必须相同
4.
>SELECT e.ename,d.dname,e.job,e.sal
>FROM emp e JOIN dept d
>ON(e.deptno = d.deptno) -- 各种条件
15．列出所有部门的详细信息和部门人数。
>SELECT dept.*,(select count(*) FROM emp WHERE deptno = dept.deptno) sum
>FROM dept;
16．列出各种工作的最低工资。
>SELECT job,MIN(sal) min_sal FROM EMP GROUP BY job;
17．列出MANAGER（经理）的最低薪金。
>SELECT sal,job FROM emp WHERE sal in
>(SELECT min(sal) FROM emp GROUP BY job) AND job = 'MANAGER';


18．列出所有员工的年工资，按年薪从低到高排序。
>SELECT ename NAME,job JOB,sal * 12 MANUAL_salary FROM emp ORDER BY MANUAL_salary;
19. 查询职员表中的职员姓名、薪水、奖金，使用函数处理空值。 注：NULL参与运算，结果为NULL，所以有些情况需要处理NULL
1。 nvl函数 nvl(expr1,expr2) 若expr1为NULL则取值expr2
>SELECT ename,sal,comm, sal + nvl(comm,0) FROM emp;
2。 nvl2（expr1,expr2,expr3) 若expr1为NULL，则取值expr3，若不为NULL ，则返回expr2
>SELECT ename,sal,comm, nvl2(comm,sal+comm,sal) salary from emp;
3。 coalesce(expr1[,expr2][,expr3.....) 返回第一个非空表达式的结果
>SELECT ename,sal,comm, coalesce(sal+comm,sal) salary FROM emp；
20.在使用UNION这类集合操作符时，如果select列表的列名不同，必须使用列位置来排序。
查询部门表和职员表，列出所有的部门编码和部门名字，以及所有的职员编码和职员名字，并以部门彪马和职员编码
作为升序排列的标准
>SELECT deptno, dname FROM dept
>UNION
>SELECT empno,ename FROM emp
>ORDER BY 1;
21.多列分组
计算每个部门每个职位的平均薪水和最高薪水
>SELECT deptno,job,AVG(sal) avg_sal,MAX(sal) max_sal FROM emp GROUP BY deptno,job ORDER BY deptno;
22.查询出薪水比本部门平均薪水高的员工信息
1。
>SELECT deptno, ename ,sal
>FROM emp e
>WHERE sal > (SELECT AVG(sal) FROM emp WHERE deptno = e.deptno)
>ORDER BY deptno;
2。行内视图，出现在FROM短语中的子查询
>SELECT e.deptno, e.ename, e.sal
>FROM emp e,
>(SELECT deptno,AVG(sal) avg_sal FROM emp GROUP BY deptno) x
>WHERE e.deptno = x.deptno
>and e.sal > x.avg_sal
>ORDER BY e.deptno;
==============================================================================================================================

====
先以system 登陆 用alter user hr identified by hr 将hr密码改为hr

以下操作请使用hr登陆你的数据库

23.多表查询，注意连接条件，连接n个表，最少需要n-1个连接条件，避免笛卡尔积。
查询员工姓名，以及所在部门名称，和部门所在城市
>SELECT https://www.sodocs.net/doc/ee1860997.html,st_name ename,
>d.department_name dname, l.city
>FROM employees e, departments d, locations l
>WHERE e.department_id = d.department_id
>AND d.location_id = l.location_id;
24.编写一个查询，确定所有是经理的员工
> SELECT last_name from employees where
>(employee_id in (select manager_id from employees));
25.编写一个查询，确定每一个国家的最高薪水 (将子查询的结果作为父查询的输入）
>SELECT max（salary），country_id from
>(select salary, department_id, location_id, country_id from
employees natural join departments natural join locations) group by country_id;
26.查找所有拥有一个或多个员工的部门
>select department_name from departments where deparetment_id in
>(select distinct(department_id) from employees);
27.查找一个国家中员工的平均薪水
>select avg(salary),country_id from
>(select salary ,department_id,location_id,country_id from
> employees natural join departments natural join locations)
>group by country_id;
28.查找所有工作部门在United Kingdom的员工
>SELECT last_name FROM employees WHERE department_id in
>(SELECT department_id FROM departments WHERE loaction_id in
>(SELECT location_id from locations WHERE　country_id =
>(SELECT country_id from countries WHERE country_name = 'United Kingdom')
>));
29.查找所有工资超过平均水平并且工作在IT部门的员工。
>SELECT last_name FROM employees
>WHERE (sal > （SELECT AVG(sal) FROM employees))
>AND
>(department_id in (SELECT department_id from departments WHERE department_name LIKE 'IT%'));
30.查找所有部门中薪水在平均薪水值一下的员工
1。关联子查询
关联子查询是一个比单行子查询和多行子查询更复杂的执行方法，它的功能更强大。
如果一个子查询引用了父查询的字段，这样它的结果也将依赖于父查询结果。这样就不可能在父查询值比较前进行子查询值比较。
>SELECT https://www.sodocs.net/doc/ee1860997.html,st_name, p.department_id FROM employees p
>WHERE p.salary < (SELECT avg(s.salary) FROM employees s
>WHERE s.department_id = p.department_id);
在上面这个例子中，子查询引用了一个字段： p.department_id，它定义在父查询的select列表中，
这表示，不是只对子查询进行一次值比较，而是对父查询中的每一行记录在子查询中进行比较。

该查询的执行顺序是这样的：
1、从EMPLOYEES 表的第一个行记录开始查询。
2、读取当前记录行的department_id 和 salary字段值
3、对第2

步得到得到department_id执行子查询
4、比较第3步子查询结果与第二步得到的salary值，如果salary小于子查询结果，则返回该行记录。
5、继续查询employees表的下一行记录。
6、重复第2步。

单行或多行子查询都会在外查询值标胶前进行一次值比较；而关联子查询则必须在外查询的每一行记录上进行一次值比较。

2。以上题目亦可采用行内视图的方式实现，先把所有部门的计算出来
>SELECT https://www.sodocs.net/doc/ee1860997.html,st_name,p.department_id FROM employees p,
>(select department_id, avg(salary) avg_sal FROM employees GROUP BY department_id) s
>WHERE p.department_id = s.department_id
>AND p.salary < s.avg_sal;
31.查找那些经理办公地点在United Kingdom的员工
>SELECT last_name FROM employees
>WHERE manager_id in
>(SELECT employee_id from employees WHERE department_id in
>(SELECT department_id from departments WHERE location_id in
>(SELECT location_id from locations WHERE country_id = 'UK')));
32.查找平均薪水最高的工作
>SELECT job_title FROM jobs natural join employees group by job_title
>having avg(salary) = (SELECT max(avg(salary)) FROM employees GROUP BY job_id);