oracle 一些经典sql（转自网络整理）
第一篇
--------1．列出至少有一个员工的所有部门。---------
SQL> select dname from dept where deptno in(select deptno from emp);
SQL> select dname from dept where deptno in(select deptno from emp group by deptno having count(deptno) >=1);
--------2．列出薪金比“SMITH”多的所有员工。----------
SQL> select * from emp where sal > (select sal from emp where ename = 'SMITH');
--------3．列出所有员工的姓名及其直接上级的姓名。----------
SQL> select a.ename,(select ename from emp b where b.empno=a.mgr) as boss_name from emp a;
--------4．列出受雇日期早于其直接上级的所有员工。----------
SQL> select a.ename from emp a where a.hiredate<(select hiredate from emp b where b.empno=a.mgr);
--------5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门----------
SQL> 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”（办事员）的姓名及其部门名称。----------
SQL> select a.ename,b.dname from emp a join dept b on a.deptno=b.deptno and a.job='CLERK';
--------7．列出最低薪金大于1500的各种工作。----------
SQL> select distinct job as HighSalJob from emp group by job having min(sal)>1500;
--------8．列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。----------
SQL> select ename from emp where deptno=(select deptno from dept where dname='SALES');
--------9．列出薪金高于公司平均薪金的所有员工。----------
SQL> select ename from emp where sal>(select avg(sal) from emp);
--------10．列出与“SCOTT”从事相同工作的所有员工。--------
SQL> select ename from emp where job=(select job from emp where ename='SCOTT');
--------11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。---------
SQL> select a.ename,a.sal from emp a where a.sal in (select b.sal
from emp b where b.deptno=30) and a.deptno<>30;
--------12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。---------
SQL> select ename,sal from emp where sal>(select max(sal) from emp where deptno=30);
--------13．列出在每个部门工作的员工数量、平均工资和平均服务期限。---------
SQL> select (select b.dname from dept b where a.deptno=b.deptno) as deptname ,count(deptno) as deptcount,avg(sal) as deptavgsal
from emp a group by deptno;
--------14．列出所有员工的姓名、部门名称和工资。---------
SQL> select a.ename,(select b.dname from dept b where b.deptno=a.deptno) as deptname,sal from emp a;
--------15．列出所有部门的详细信息和部门人数。---------
SQL> select a.deptno,a.dname,a.loc,(select count(deptno) from emp b where b.deptno=a.deptno group by b.deptno) as deptcount from dept a;
--------16．列出各种工作的最低工资。---------
SQL> select job,avg(sal) from emp group by job;
--------17．列出各个部门的MANAGER（经理）的最低薪金。--------
SQL> select deptno,min(sal) from emp where job='MANAGER' group by deptno;
--------18．列出所有员工的年工资,按年薪从低到高排序。---------
SQL> select ename,(sal+nvl(comm,0))*12 as salpersal from emp order by salpersal;
第二篇
--------1. 找出EMP表中的姓名（ENAME）第三个字母是A 的员工姓名。---------
SQL> SELECT ENAME FROM SCOTT.EMP WHERE ENAME LIKE '__A%';
-------2. 找出EMP表员工名字中含有A 和N的员工姓名。----------
SQL> SELECT ENAME FROM SCOTT.EMP WHERE ENAME LIKE '%A%' AND ENAME LIKE '%N%';
--------3. 找出所有有佣金的员工，列出姓名、工资、佣金，显示结果按工资从小到大，佣金从大到小。----------
SQL> SELECT ENAME,SAL + COMM AS WAGE,COMM
FROM SCOTT.EMP
ORDER BY WAGE,COMM DESC;
-------4. 列出部门编号为20的所有职位。----------
SQL> SELECT DISTINCT JOB FROM EMP WHERE DEPTNO = 20;
-------5. 列出不属于SALES 的部门。----------
SQL> SELECT DISTINCT * FROM SCOTT.DEPT WHERE DNAME <> 'SALES';
或者
SQL> SELECT DISTINCT * FROM SCOTT.DEPT WHERE DNAME != 'SALES';
SQL> SELECT DISTINCT * FROM SCOTT.DEPT WHERE DNAME NOT IN('SALES');
SQL> SELECT DISTINCT * FROM SCOTT.DEPT WHERE DNAME NOT LIKE 'SALES';
---6. 显示工资不在1000 到1500 之间的员工信息：名字、工资，按工资从大到小排序。---------
SQL> SELECT ENAME,SAL + COMM AS WAGE FROM SCOTT.EMP
WHERE SAL + COMM NOT BETWEEN 1000 AND 1500
ORDER BY WAGE DESC;
--或者
SQL> SELECT ENAME,SAL + COMM AS WAGE FROM SCOTT.EMP
2 WHERE SAL + COMM < 1000 OR SAL + COMM > 1500
3 ORDER BY WAGE DESC;
/*----- 7. 显示职位为MANAGER 和SALESMAN，年薪在15000 和20000 之间的员工的信息：名字、职位、年薪。----------*/
SQL> SELECT ENAME 姓名,JOB 职位,(SAL + COMM) * 12 AS 年薪
2 FROM SCOTT.EMP
3 WHERE (SAL + COMM) * 12 BETWEEN 15000 AND 20000
4 AND JOB IN('MANAGER','SALESMAN');
/*----- 8. 说明以下两条SQL语句的输出结果：
SELECT EMPNO,COMM FROM EMP WHERE COMM IS NULL;
SELECT EMPNO,COMM FROM EMP WHERE COMM = NULL;
--说明：IS NULL是判断某个字段是否为空，为空并不等价于为空字符串或为数字0；
--而 =NULL 是判断某个值是否等于 NULL，NULL = NULL和NULL <> NULL都为 FALSE。
第三篇
/*--------1、改变NLS_LANG 的值，让SELECT TO_CHAR(SALARY,'L99,999.99') FROM HR.EMPLOYEES WHERE ROWNUM < 5 输出结果的货币单位是￥和$。---------*/
-----在没有设置NLS_LANG的情况下：
SQL> SELECT TO_CHAR(SALARY,'L99,999.99')
FROM HR.EMPLOYEES
WHERE ROWNUM < 5;
/*--------2、列出前五位每个员工的名字，工资、涨薪后的的工资（涨幅为8%），以“元”为单位进行四舍五入。---------*/
SQL> SELECT FIRST_NAME,SALARY,ROUND(SALARY * 1.08) FROM HR.EMPLOYEES
WHERE ROWNUM <=5;
/*--------3、找出谁是最高领导，将名字按大写形式显示。---------*/
SQL> SELECT UPPER(FIRST_NAME || ' ' || LAST_NAME) AS NAME
FROM HR.EMPLOYEES
WHERE MANAGER_ID IS NULL;
/*--------4、找出David 的直接领导的名字。---------*/
SQL> SELECT UPPER(FIRST_NAME ||' ' || LAST_NAME) AS NAME
FROM HR.EMPLOYEES
WHERE EMPLOYEE_ID IN(
SELECT MANAGER_ID FROM HR.EMPLOYEES
WHERE FIRST_NAME = 'David' AND LAST_NAME = 'Austin');
/*--------5、First_Name 为Alexander，LAST_NAME为Hunold领导谁。（谁向David 报告）。---------*/
SQL> SELECT UPPER(FIRST_NAME ||' ' || LAST_NAME) AS NAME
FROM HR.EMPLOYEES
WHERE MANAGER_ID IN(
SELECT EMPLOYEE_ID FROM HR.EMPLOYEES
WHERE FIRST_NAME = 'Alexander' AND LAST_NAME = 'Hunold');
/*--------6、哪些员工的工资高于他直接上司的工资，列出员工的名字和工资，上司的名字和工资。---------*/
SQL> SELECT E.FIRST_NAME,E.SALARY,M.FIRST_NAME,M.SALARY
FROM EMPLOYEES E,EMPLOYEES M
WHERE E.MANAGER_ID = M.EMPLOYEE_ID AND E.SALARY > M.SALARY;
--要是只列出员工的名字与工资的话，还可以这样：
SQL> SELECT E.FIRST_NAME,E.SALARY
FROM EMPLOYEES E WHERE E.SALARY >
(SELECT M.SALARY FROM EMPLOYEES M
WHERE E.MANAGER_ID = M.EMPLOYEE_ID);
/*--------7、哪些员工和Chen(LAST_NAME)同部门。---------*/
SQL> SELECT FIRST_NAME FROM EMPLOYEES
WHERE DEPARTMENT_ID IN
(SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE LAST_NAME = 'Chen')
AND LAST_NAME <> 'Chen';
--或者--
SQL> SELECT E1.FIRST_NAME FROM EMPLOYEES E1,EMPLOYEES E2
WHERE E1.DEPARTMENT_ID = E2.DEPARTMENT_ID
AND E2.LAST_NAME = 'Chen' AND E1.LAST_NAME <> 'Chen';
/*--------8、哪些员工跟De Haan(LAST_NAME)做一样职位。---------*/
SQL> SELECT FIRST_NAME FROM EMPLOYEES
WHERE JOB_ID IN
(SELECT JOB_ID FROM EMPLOYEES
WHERE LAST_NAME = 'De Haan')
AND LAST_NAME <> 'De Haan';
--或者--
SQL> SELECT E1.FIRST_NAME FROM EMPLOYEES E1,EMPLOYEES E2
WHERE E1.JOB_ID = E2.JOB_ID
AND E2.LAST_NAME = 'De Haan' AND E1.LAST_NAME <> 'De Haan';
/*--------9、哪些员工跟Hall(LAST_NAME)不在同一个部门。---------*/
SQL> SELECT FIRST_NAME || ' ' || LAST_NAME FROM HR.EMPLOYEES
WHERE DEPARTMENT_ID NOT IN(
SELECT DEPARTMENT_ID FROM HR.EMPLOYEES
WHERE LAST_NAME = 'Hall');
--或者：
SQL> SELECT e1.FIRST_NAME FROM EMPLOYEES e1,EMPLOYEES e2
WHERE e1.DEPARTMENT_ID = e2.DEPARTMENT_ID(+)
and e2.LAST_NAME(+) = 'Hall'
and e2.LAST_NAME IS NULL;
/*-------10、哪些员工跟William（FIRST_NAME）、Smith(LAST_NAME)做不一样的职位。--------*/
SQL> SELECT FIRST_NAME || ' ' || LAST_NAME FROM HR.EMPLOYEES
WHERE JOB_ID <> (SELECT DISTINCT JOB_ID FROM EMPLOYEES
WHERE FIRST_NAME = 'William' AND LAST_NAME = 'Smith');
/*--------11、显示有提成的员工的信息：名字、提成、所在部门名称、所在地区的名称。---------*/
SQL> SELECT E.FIRST_NAME || ' ' || E.LAST_NAME AS NAME,
E.COMMISSION_PCT,D.DEPARTMENT_NAME,L.CITY
FROM HR.EMPLOYEES E,HR.DEPARTMENTS D,HR.LOCATIONS L
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND D.LOCATION_ID = L.LOCATION_ID
AND E.COMMISSION_PCT IS NOT NULL;
/*--------12、显示Executive部门有哪些职位。---------*/
SQL> SELECT DISTINCT E.JOB_ID FROM HR.EMPLOYEES E,HR.DEPARTMENTS D
WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID
AND D.DEPARTMENT_NAME = 'Executive';
/*--------13、整个公司中，最高工资和最低工资相差多少。---------*/
SQL> SELECT MAX(SALARY) - MIN(SALARY) FROM HR.EMPLOYEES;
/*--------14、提成大于0 的人数。---------*/
SQL> SELECT COUNT(*) AS 提成大小0的人数 FROM HR.EMPLOYEES
WHERE COMMISSION_PCT > 0;
--或者
SQL> SELECT COUNT(COMMISSION_PCT) AS 提成大小0的人数
FROM HR.EMPLOYEES
WHERE COMMISSION_PCT > 0;
/*--------15、显示整个公司的最高工资、最低工资、工资总和、平均工资保留到整数位。---------*/
SQL> SELECT MAX(NVL(SALARY,0)) AS 最高工资,
MIN(NVL(SALARY,0)) AS 最低工资,
SUM(NVL(SALARY,0)) AS 工资总和,
ROUND(AVG(NVL(SALARY,0))) AS 平均工资
FROM HR.EMPLOYEES;
/*--------16、整个公司有多少个领导。---------*/
SQL> SELECT COUNT(DISTINCT(MANAGER_ID)) FROM HR.EMPLOYEES
2 WHERE MANAGER_ID IS NOT NULL;
/*--------17、列出在同一部门入职日期晚但工资高于其他同事的员工：
名字、工资、入职日期。---------*/
SQL> SELECT DISTINCT E1.FIRST_NAME || ' ' || E1.LAST_NAME AS 姓名,
E1.SALARY AS 工资,E1.HIRE_DATE AS 入职日期
FROM HR.EMPLOYEES E1,HR.EMPLOYEES E2
WHERE E1.DEPARTMENT_ID = E2.DEPARTMENT_ID
AND E1.HIRE_DATE > E2.HIRE_DATE
AND E1.SALARY > E2.SALARY
ORDER BY 工资 DESC;
第四篇
/*--------1、各个部门平均、最大、最小工资、人数，按照部门号升序排列。---------*/
SQL> SELECT DEPARTMENT_ID AS 部门号,AVG(SALARY) AS 平均工资
,MAX(SALARY) AS 最高工资,MIN(SALARY) AS 最低工资
,COUNT(*) AS 人数
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
ORDER BY DEPARTMENT_ID ASC;
/*--------2、各个部门中工资大于5000的员工人数。---------*/
SQL> SELECT DEPARTMENT_ID,COUNT(*) FROM EMPLOYEES
WHERE SALARY > 5000
GROUP BY DEPARTMENT_ID;
/*--------3、各个部门平均工资和人数，按照部门名字升序排列。---------*/
SQL> SELECT DPTNAME,AVG(SALARY),COUNT(*) FROM
(SELECT
(SELECT DEPT.DEPARTMENT_NAME FROM DEPARTMENTS DEPT
WHERE DEPT.DEPARTMENT_ID = EMP.DEPARTMENT_ID) DPTNAME,
EMP.SALARY
FROM EMPLOYEES EMP)
GROUP BY DPTNAME
ORDER BY DPTNAME;
--或者--
SQL> SELECT DEPT.DEPARTMENT_NAME,AVG(EMP.SALARY),COUNT(*)
FROM EMPLOYEES EMP,DEPARTMENTS DEPT
WHERE EMP.DEPARTMENT_ID = DEPT.DEPARTMENT_ID
GROUP BY DEPT.DEPARTMENT_NAME
ORDER BY DEPT.DEPARTMENT_NAME;
/*--------4、列出每个部门中有同样工资的员工的统计信息，
列出他们的部门号，工资，人数。---------*/
SQL> SELECT EMP1.DEPARTMENT_ID,EMP1.SALARY,COUNT(*) CNT
FROM EMPLOYEES EMP1,EMPLOYEES EMP2
WHERE EMP1.DEPARTMENT_ID = EMP2.DEPARTMENT_ID AND
EMP1.SALARY = EMP2.SALARY
AND EMP1.EMPLOYEE_ID <> EMP2.EMPLOYEE_ID
GROUP BY EMP1.DEPARTMENT_ID,EMP1.SALARY;
/*--------5、列出同部门中工资高于1000 的员工数量超过2 人的部门，
显示部门名字、地区名称。---------*/
SQL> SELECT D.DEPARTMENT_NAME,L.CITY,COUNT(*)
FROM EMPLOYEES E,DEPARTMENTS D,LOCATIONS L
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND
D.LOCATION_ID = L.LOCATION_ID AND
E.SALARY > 1000
GROUP BY D.DEPARTMENT_NAME,L.CITY
HAVING COUNT(*) > 2;
/*--------6、哪些员工的工资，高于整个公司的平均工资，
列出员工的名字和工资（降序）。---------*/
SQL> SELECT FIRST_NAME || ' ' || LAST_NAME,SALARY
FROM EMPLOYEES
WHERE SALARY > (
SELECT AVG(SALARY)
FROM EMPLOYEES
)
ORDER BY SALARY DESC;
/*--------7、哪些员工的工资，介于50号 和80号 部门平均工资之间。---------*/
SQL> SELECT FIRST_NAME || ' ' || LAST_NAME AS NAME,SALARY
FROM EMPLOYEES
WHERE SALARY
BETWEEN
(SELECT AVG(SALARY) FROM EMPLOYEES
WHERE DEPARTMENT_ID = 50)
AND (SELECT AVG(SALARY) FROM EMPLOYEES
WHERE DEPARTMENT_ID = 80);
/*--------8、所在部门平均工资高于5000 的员工名字。---------*/
SQL> SELECT FIRST_NAME || ' ' || LAST_NAME AS NAME,SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN
(SELECT DEPARTMENT_ID FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING AVG(SALARY) > 5000);
/*--------9、列出各个部门中工资最高的员工的信息：名字、部门号、工资。---------*/
SQL> SELECT FIRST_NAME || ' ' || LAST_NAME AS NAME
,SALARY,DEPARTMENT_ID
FROM EMPLOYEES
WHERE (DEPARTMENT_ID,SALARY) IN
(SELECT DEPARTMENT_ID,MAX(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID);
/*--------10、最高的部门平均工资是多少。---------*/
SQL> SELECT MAX(AVGSALARY)
FROM(SELECT DEPARTMENT_ID,AVG(SALARY) AVGSALARY
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID);
