Chinaunix

标题: 一起学习写SQL [打印本页]

作者: www_xylove 时间: 2013-08-15 23:19
标题: 一起学习写SQL
作为一名dba，会写sql是一项基本功。
如果在做dba之前，从事过开发，可能写sql就相对容易些，如果直接就上手dba，可能写sql的能力会差一些。
本人也没有从事过开发，写sql也是在工作中慢慢的学会的。
不过，这也没有关系，多练习练习，就会了。
下面总共有sql练习55道题，这55道题目是itpub网友提供的，但没有提供作者的名字，这里先行谢谢了。
原版的55道题目，既有题也有答案，因为是学习写sql，不是去看人家怎么写的，我这里的练习的是自己这几天写出来的，
当然也参考了原作者的答案。原作者的文档命名为《Oracle_SQL：经典查询练手四篇(全)》可以到itpub上下载。

下面的sql基本上是我自己写出来的，因为我也是在不断的学习写sql中，有写的不妥的地方，请网友们指出，谢谢。
好了，转入正题，下面我们来一起写sql：

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

1．列出至少有一个员工的所有部门。
select dname from scott.dept a,(
select deptno,count(*) from scott.emp  where deptno is not null group by deptno order by count(*) desc
) b
where a.deptno = b.deptno

DNAME
ACCOUNTING
RESEARCH
SALES

2. 列出薪金比“MARTIN”多的所有员工。
select * from scott.emp b where sal>
(select sal from scott.emp where ename='MARTIN')

3. 列出所有员工的姓名及其直接上级的姓名。
select a.empno as "员工编号",
   a.ename as "员工姓名",
   b.ename as "员工的直接领导"
  from scott.emp a, scott.emp b
where a.mgr = b.empno(+)
员工编号员工姓名员工的直接领导
7902 FORD JONES
7900 JAMES BLAKE
7844 TURNER BLAKE
7654 MARTIN BLAKE
7521 WARD BLAKE
7499 ALLEN BLAKE
7934 MILLER CLARK
7782 CLARK KING
7698 BLAKE KING
7566 JONES KING
7369 SMITH FORD
7839 KING
4．列出受雇日期早于其直接上级的所有员工。
select a.empno as "员工编号",
   a.ename as "员工姓名",
   a.hiredate as "员工受雇日期",
   b.ename as "员工的直接领导",
   b.hiredate "员工的直接领导受雇日期"
  from scott.emp a, scott.emp b
where a.mgr = b.empno(+)
and a.hiredate<b.hiredate
员工编号员工姓名员工受雇日期员工的直接领导员工的直接领导受雇日期
7521 WARD 1981/2/22 BLAKE 1981/5/1
7499 ALLEN 1981/2/20 BLAKE 1981/5/1
7782 CLARK 1981/6/9 KING 1981/11/17
7566 JONES 1981/4/2 KING 1981/11/17
7698 BLAKE 1981/5/1 KING 1981/11/17
7369 SMITH 1980/12/17 FORD 1981/12/3
红色标示的这些员工就是比直接领导受雇早的员工。

5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门

select a.empno, a.ename, b.dname, b.deptno
  from scott.emp a, scott.dept b
where a.deptno(+) = b.deptno

EMPNO ENAME DNAME DEPTNO
7782 CLARK ACCOUNTING 10
7934 MILLER ACCOUNTING 10
7902 FORD RESEARCH 20
7369 SMITH RESEARCH 20
7566 JONES RESEARCH 20
7900 JAMES SALES       30
7844 TURNER SALES    30
7654 MARTIN SALES    30
7521 WARD SALES    30
7499 ALLEN SALES    30
7698 BLAKE SALES    30
OPERATIONS       40

等同于：
select a.empno, a.ename, b.dname, b.deptno
  from scott.emp a
right join scott.dept b
on a.deptno = b.deptno

6．列出所有“CLERK”（办事员）的姓名及其部门名称。
select c.job,c.ename,b.dname from scott.dept b,
(select a.ename,a.deptno,a.job from scott.emp a where job ='CLERK') c
where c.deptno = b.deptno

JOB       ENAME       DNAME
CLERK SMITH RESEARCH
CLERK JAMES SALES
CLERK MILLER ACCOUNTING

7. 列出最低薪金大于1500的各种工作。
select a.job,min(a.sal) from scott.emp a group by a.job having min(sal) >1500;

JOB          MIN(A.SAL)
PRESIDENT 5000
MANAGER 2450
ANALYST 3000

8．列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。
select a.ename from scott.emp a ,(
select b.deptno from scott.dept b where dname ='SALES') c where
c.deptno = a.deptno
或
select ename from scott.emp where deptno = (select deptno from scott.dept where dname ='SALES');

ENAME
ALLEN
WARD
MARTIN
BLAKE
TURNER
JAMES

9．列出薪金高于公司平均薪金的所有员工。
select ename from scott.emp where sal > (
select round(avg(sal)) from scott.emp )
ENAME
JONES
BLAKE
CLARK
KING
FORD

10．列出与“BLAKE”从事相同工作的所有员工。
select b.ename,b.job from scott.emp b,(
select a.job from scott.emp a where ename='BLAKE') c
where b.job = c.job
或
select ename,job from scott.emp b where job = (select a.job from scott.emp a where ename='BLAKE')

ENAME JOB
JONES MANAGER
BLAKE MANAGER
CLARK MANAGER

11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
select a.ename, a.sal
  from scott.emp a
where a.sal in (select b.sal from scott.emp b where b.deptno = 30)
and a.deptno <> 30;
12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
select ename,sal from scott.emp where sal> all (
select sal from scott.emp where deptno=30)
或
select ename,sal from scott.emp where sal>(select max(sal) from scott.emp where deptno=30)
ENAME SAL
JONES 2975.00
KING 5000.00
FORD 3000.00

13．列出在每个部门工作的员工数量、平均工资和平均服务期限。
select b."部门平均工资","部门员工数量","平均服务年限" from scott.dept a,
(select deptno,
   round(avg(sal)) as "部门平均工资",
   count(*) as "部门员工数量",
   round(avg(to_number(to_char(hiredate, 'yyyymmdd')))) as "平均服务年限"
  from scott.emp
where deptno is not null
group by deptno) b
where a.deptno = b.deptno

部门平均工资部门员工数量平均服务年限
1567 6 19810664
2258 3 19807607
1875 2 19815366

14．列出所有员工的姓名、部门名称和工资。
select ename, dname, sal
  from scott.dept a, (select * from scott.emp b) b
where a.deptno = b.deptno
ENAME DNAME SAL
CLARK ACCOUNTING 2450.00
MILLER ACCOUNTING 1300.00
FORD RESEARCH 3000.00
SMITH RESEARCH 800.00
JONES RESEARCH 2975.00
JAMES SALES 950.00
TURNER SALES 1500.00
MARTIN SALES 1250.00
WARD SALES 1250.00
ALLEN SALES 1600.00
BLAKE SALES 2850.00

15．列出所有部门的详细信息和部门人数。
select b.deptno, b.dname, b.loc, count(ename)
  from scott.emp a, (select * from scott.dept) b
where a.deptno(+) = b.deptno
group by b.deptno, b.dname, b.loc;
或
select a.deptno,
   a.dname,
   a.loc,
   (select  count(*)
      from scott.emp b
      where b.deptno = a.deptno
      group by b.deptno) as deptcount
  from scott.dept a;

20 RESEARCH DALLAS 3
40 OPERATIONS BOSTON 0
10 ACCOUNTING NEW YORK 2
30 SALES CHICAGO 6

16．列出各种工作的最低工资。
select job,min(sal) from scott.emp group by job

JOB MIN(SAL)
CLERK 800
SALESMAN 1250
PRESIDENT 5000
MANAGER 2450
ANALYST 3000

17．列出各个部门的MANAGER（经理）的最低薪金。
select b.ename as "部门经理",
      (select c.dname as "部门"
         from scott.dept c
         where c.deptno = a.deptno) as "部门",
      min(a.sal) as "部门经理最低工资"
   from scott.emp a, scott.emp b
where a.mgr = b.empno(+)
   and b.ename <> 'KING'
group by b.ename, a.deptno
order by a.deptno desc

部门经理部门部门经理最低工资
BLAKE SALES       950
FORD RESEARCH 800
JONES RESEARCH 3000
CLARK ACCOUNTING 1300

18．列出所有员工的年工资,按年薪从低到高排序。
select ename, (sal + nvl(comm, 0)) * 12 as "年薪"
  from scott.emp
order by "年薪";

作者: www_xylove 时间: 2013-08-15 23:20
SQL学习2-10:
1. 找出EMP表中的姓名（ENAME）第三个字母是A 的员工姓名。
2. 找出EMP表员工名字中含有A 和N的员工姓名。
3. 找出所有有佣金的员工，列出姓名、工资、佣金，显示结果按工资从小到大，佣金从大到小。
4. 列出部门编号为20的所有职位。
5. 列出不属于SALES 的部门。
6. 显示工资不在1000 到1500 之间的员工信息：名字、工资，按工资从大到小排序。
7. 显示职位为MANAGER 和SALESMAN，年薪在15000 和20000 之间的员工的信息：名字、职位、年薪。
8. 说明以下两条SQL语句的输出结果：
SELECT EMPNO,COMM FROM EMP WHERE COMM IS NULL;
SELECT EMPNO,COMM FROM EMP WHERE COMM = NULL;
9. 让SELECT 语句的输出结果为
SELECT * FROM SALGRADE;
SELECT * FROM BONUS;
SELECT * FROM EMP;
SELECT * FROM DEPT;
……
列出当前用户有多少张数据表，结果集中存在多少条记录。
10. 判断SELECT ENAME,SAL FROM EMP WHERE SAL > '1500'是否报错，为什么？

1. 找出EMP表中的姓名（ENAME）第三个字母是A 的员工姓名。
select ename from scott.emp where ename like '__A%'
ENAME
BLAKE
CLARK
2. 找出EMP表员工名字中含有A 和N的员工姓名。
select ename
  from scott.emp
where ename like '%A%'
and ename like '%N%';

ENAME
ALLEN
MARTIN
3. 找出所有有佣金的员工，列出姓名、工资、佣金，显示结果按工资从小到大，佣金从大到小。
select ename, sal, comm
  from scott.emp
where comm is not null
order by sal,comm desc
ENAME    SAL       COMM
MARTIN 1250.00 1400.00
WARD 1250.00 500.00
TURNER 1500.00 0.00
ALLEN 1600.00 300.00

4. 列出部门编号为20的所有职位。
select job from scott.emp where deptno = 20
5. 列出不属于SALES 的部门。
select dname from scott.dept where dname <>'SALES'
6. 显示工资不在1000 到1500 之间的员工信息：名字、工资，按工资从大到小排序。
select ename,sal  from scott.emp where sal >1500 or sal<1000 order by sal desc
ENAME SAL
KING 5000.00
FORD 3000.00
JONES 2975.00
BLAKE 2850.00
CLARK 2450.00
ALLEN 1600.00
JAMES 950.00
SMITH 800.00

7. 显示职位为MANAGER 和SALESMAN，年薪在15000 和20000 之间的员工的信息：名字、职位、年薪
select ename, job, (sal * 12 + nvl(comm, 0)) as "年薪"
  from scott.emp
where job in ('MANAGER', 'SALESMAN')
and ((sal * 12 + nvl(comm, 0)) >= 15000 and
   (sal * 12 + nvl(comm, 0)) <= 20000)
order by "年薪" desc

ENAME JOB 年薪
ALLEN SALESMAN 19500
TURNER SALESMAN 18000
MARTIN SALESMAN 16400
WARD SALESMAN 15500

8. 说明以下两条SQL语句的输出结果：
SELECT EMPNO,COMM FROM EMP WHERE COMM IS NULL;
EMPNO COMM
7369
7566
7698
7782
7839
7900
7902
7934
SELECT EMPNO,COMM FROM EMP WHERE COMM = NULL;
没有选择行

9. 让SELECT 语句的输出结果为
SELECT * FROM SALGRADE;
SELECT * FROM BONUS;
SELECT * FROM EMP;
SELECT * FROM DEPT;
……
列出当前用户有多少张数据表，结果集中存在多少条记录。
（这个过于简单，就不示例了.）

10. 判断SELECT ENAME,SAL FROM EMP WHERE SAL > '1500'是否报错，为什么？
ENAME SAL
ALLEN 1600.00
JONES 2975.00
BLAKE 2850.00
CLARK 2450.00
KING 5000.00
FORD 3000.00

不会报错：将自动转换（隐式转换）为字符类型的数字。

作者: www_xylove 时间: 2013-08-15 23:20
SQL学习3-17:
1. 让SELECT TO_CHAR(SALARY,'L99,999.99') FROM HR.EMPLOYEES WHERE  ROWNUM < 5 输出结果的货币单位是￥和$。
2. 列出前五位每个员工的名字，工资、涨薪后的的工资（涨幅为8%），以“元”为单位进行四舍五入。
3. 找出谁是最高领导，将名字按大写形式显示。
4. 找出First_Name 为David，Last_Name为Austin 的直接领导名字。
5. First_Name 为Alexander，Last_Name为Hunold领导谁。（谁向David 报告）。
6. 哪些员工的工资高于他直接上司的工资，列出员工的名字和工资，上司的名字和工资。
7. 哪些员工和Chen(LAST_NAME)同部门。
8. 哪些员工跟De Haan(LAST_NAME)做一样职位。
9. 哪些员工跟Hall(LAST_NAME)不在同一个部门。
10. 哪些员工跟William（FIRST_NAME）、Smith(LAST_NAME)做不一样的职位。
11. 显示有提成的员工的信息：名字、提成、所在部门名称、所在地区的名称。
12. 显示Executive部门有哪些职位。
13. 整个公司中，最高工资和最低工资相差多少。
14. 提成大于0 的人数。
15. 显示整个公司的最高工资、最低工资、工资总和、平均工资保留到整数位。
16. 整个公司有多少个领导。
17. 列出在同一部门入职日期晚但工资高于其他同事的员工：名字、工资、入职日期。

1. 让SELECT TO_CHAR(SALARY,'L99,999.99') FROM HR.EMPLOYEES WHERE  ROWNUM < 5 输  出结果的货币单位是￥和$。
SELECT
   TO_CHAR(SALARY, 'L99,999.99'),
   TO_CHAR(SALARY, '$99,999.99')
  FROM HR.EMPLOYEES
WHERE ROWNUM < 5

TO_CHAR(SALARY,'L99,999.99') TO_CHAR(SALARY,'$99,999.99')
      ￥24,000.00    $24,000.00
      ￥17,000.00    $17,000.00
      ￥17,000.00    $17,000.00
      ￥9,000.00       $9,000.00

2. 列出前五位每个员工的名字，工资、涨薪后的的工资（涨幅为8%），以“元”为单位进行四舍五入。
select first_name,round(to_char(salary)),round(to_char(salary+salary*0.0

) from (
(select rownum as rn,a.* from hr.employees a order by salary desc) b )
where rn <6

FIRST_NAME ROUND(TO_CHAR(SALARY)) ROUND(TO_CHAR(SALARY+SALARY*0.
Steven                         24000             25920
Neena                         17000             18360
Lex                            17000             18360
Alexander                   9000             9720
Bruce                         6000             6480

3. 找出谁是最高领导，将名字按大写形式显示。
select upper(first_name || ' ' || last_name) as "最高领导人"
  from hr.employees
where manager_id is null;

最高领导人
STEVEN KING

4. 找出First_Name 为David，Last_Name为Austin 的直接领导名字。
select employee_id, first_name, last_name
  from hr.employees b
where b.employee_id = (select a.manager_id
                        from hr.employees a
                     where a.first_name = 'David'
                        and a.last_name = 'Austin')

EMPLOYEE_ID FIRST_NAME    LAST_NAME
103                Alexander       Hunold

5. First_Name 为Alexander，Last_Name为Hunold领导谁。（谁向David 报告）。
select c.first_name || ' ' || c.last_name as "David Austin 的直接上司"
  from hr.employees c
where c.employee_id =
   (select b.manager_id
      from hr.employees b
      where b.employee_id =
            (select a.manager_id
               from hr.employees a
               where a.first_name = 'David'
               and a.last_name = 'Austin'))

David Austin 的直接上司

Lex De Haan

6. 哪些员工的工资高于他直接上司的工资，列出员工的名字和工资，上司的名字和工资。
select c.employee_id as "员工ID",
   c.first_name || ' ' || c.last_name as "员工姓名",
   c.salary as "员工薪资",
   a.employee_id as "员工直接上级ID",
   a.first_name || ' ' || a.last_name as "员工直接上级姓名",
   a.salary as "员工直接上级薪资"
  from hr.employees a, hr.employees c
where a.employee_id(+) = c.manager_id
and c.salary > a.salary
order by 员工薪资 desc

员工ID 员工姓名员工薪资员工直接上级ID 员工直接上级姓名员工直接上级薪资
168 Lisa Ozer 11500.00 148 Gerald Cambrault 11000.00
174 Ellen Abel 11000.00 149 Eleni Zlotkey       10500.00

7. 哪些员工和Chen(LAST_NAME)同部门。
select b.last_name, b.department_id
  from hr.employees b,
   (select a.department_id from hr.employees a where last_name = 'Chen') c
where b.department_id = c.department_id

LAST_NAME DEPARTMENT_ID
Greenberg 100
Faviet 100
Chen 100
Sciarra 100
Urman 100
Popp 100

8. 哪些员工跟De Haan(LAST_NAME)做一样职位。
select b.last_name,b.job_id
  from hr.employees b,
   (select a.job_id from hr.employees a where last_name = 'De Haan') c
where b.job_id = c.job_id

LAST_NAME JOB_ID
Kochhar AD_VP
De Haan AD_VP

9. 哪些员工跟Hall(LAST_NAME)不在同一个部门。

select b.last_name, b.department_id
  from hr.employees b,
   (select a.department_id from hr.employees a where last_name = 'Chen') c
where b.department_id <> c.department_id

10. 哪些员工跟William（FIRST_NAME）、Smith(LAST_NAME)做不一样的职位。
select * from hr.employees a,(
select b.job_id from hr.employees b where b.first_name='William' and b.last_name='Smith' and b.job_id='SA_REP') c
where a.job_id <> c.job_id;

11. 显示有提成的员工的信息：名字、提成、所在部门名称、所在地区的名称。
select g.first_name || ' ' || g.last_name as "员工",
   g.commission_pct as "提成",
   g.department_id as "部门",
   x.region_name as "地区"
  from hr.employees g,
   (select f.region_name
      from hr.regions f
      where f.region_id in
            (select e.region_id
               from hr.countries e
               where e.country_id in
                     (select d.country_id
                        from hr.locations d
                     where d.location_id in
                           (select distinct b.location_id
                              from hr.departments b,
                                    (select a.department_id
                                       from hr.employees a
                                       where a.commission_pct is not null
                                       and a.department_id is not null) c
                              where b.department_id = c.department_id)))) x
where g.commission_pct is not null;

员工             提成    部门       地区
John Russell 0.40 80 Europe
Karen Partners 0.30 80 Europe
Alberto Errazuriz 0.30 80 Europe
Gerald Cambrault 0.30 80 Europe
Eleni Zlotkey 0.20 80 Europe
Peter Tucker 0.30 80 Europe
David Bernstein 0.25 80 Europe
Peter Hall 0.25 80 Europe
Christopher Olsen 0.20 80 Europe
Nanette Cambrault 0.20 80 Europe
Oliver Tuvault 0.15 80 Europe
Janette King 0.35 80 Europe
Patrick Sully 0.35 80 Europe
Allan McEwen 0.35 80 Europe
Lindsey Smith 0.30 80 Europe
Louise Doran 0.30 80 Europe
Sarath Sewall 0.25 80 Europe
Clara Vishney 0.25 80 Europe
Danielle Greene 0.15 80 Europe
Mattea Marvins 0.10 80 Europe
David Lee 0.10 80 Europe
Sundar Ande 0.10 80 Europe
Amit Banda 0.10 80 Europe
Lisa Ozer 0.25 80 Europe
Harrison Bloom 0.20 80 Europe
Tayler Fox 0.20 80 Europe
William Smith 0.15 80 Europe
Elizabeth Bates 0.15 80 Europe
Sundita Kumar 0.10 80 Europe
Ellen Abel 0.30 80 Europe
Alyssa Hutton 0.25 80 Europe
Jonathon Taylor 0.20 80 Europe
Jack Livingston 0.20 80 Europe
Kimberely Grant 0.15 Europe
Charles Johnson 0.10 80 Europe

12. 显示Executive部门有哪些职位。
select b.job_id,count(*) from hr.employees b where b.department_id in (
select a.department_id from hr.departments a where a.department_name ='Executive')
group by b.job_id

JOB_ID COUNT(*)
AD_VP       2
AD_PRES 1

13. 整个公司中，最高工资和最低工资相差多少。
select max(salary) - min(salary) from hr.employees;

14. 提成大于0 的人数。
select * from hr.employees a where a.commission_pct is not null;

15. 显示整个公司的最高工资、最低工资、工资总和、平均工资保留到整数位。
select max(nvl(salary, 0)),
   min(nvl(salary, 0)),
   sum(nvl(salary, 0)),
   round(avg(nvl(salary, 0)))
  from hr.employees;

16. 整个公司有多少个领导。
select distinct a.first_name || ' ' || a.last_name as "公司领导"
  from hr.employees a, hr.employees c
where a.employee_id(+) = c.manager_id

公司领导
Lex De Haan
Nancy Greenberg
Shanta Vollman
Alberto Errazuriz
Shelley Higgins
Payam Kaufling
John Russell
Eleni Zlotkey
Adam Fripp
Gerald Cambrault
Michael Hartstein
Steven King
Alexander Hunold
Den Raphaely
Matthew Weiss
Kevin Mourgos
Neena Kochhar
Karen Partners

17. 列出在同一部门入职日期晚但工资高于其他同事的员工：名字、工资、入职日期。
select distinct a.first_name || ' ' || a.last_name,
            a.hire_date,
            a.salary,
            a.department_id
  from hr.employees a, hr.employees b
where a.department_id = b.department_id
and a.hire_date > b.hire_date
and a.salary > b.salary
order by a.department_id
A.FIRST_NAME||''||A.LAST_NAME HIRE_DATE SALARY DEPARTMENT_ID
Shelli Baida 1997/12/24 2900.00 30
Nandita Sarchand 1996/1/27 4200.00 50
Sarah Bell 1996/2/4 4000.00 50
Matthew Weiss 1996/7/18 8000.00 50
Alexis Bull 1997/2/20 4100.00 50
Britney Everett 1997/3/3 3900.00 50
Adam Fripp 1997/4/10 8200.00 50
Kelly Chung 1997/6/14 3800.00 50
Julia Nayer 1997/7/16 3200.00 50
Jennifer Dilly 1997/8/13 3600.00 50
Laura Bissot 1997/8/20 3300.00 50
Shanta Vollman 1997/10/10 6500.00 50
Stephen Stiles 1997/10/26 3200.00 50
Mozhe Atkinson 1997/10/30 2800.00 50
Winston Taylor 1998/1/24 3200.00 50
John Seo 1998/2/12 2700.00 50
Jean Fleaur 1998/2/23 3100.00 50
Randall Matos 1998/3/15 2600.00 50
Alana Walsh 1998/4/24 3100.00 50
Kevin Feeney 1998/5/23 3000.00 50
Julia Dellinger 1998/6/24 3400.00 50
Samuel McCain 1998/7/1 3200.00 50
Timothy Gates 1998/7/11 2900.00 50
Michael Rogers 1998/8/26 2900.00 50
Irene Mikkilineni 1998/9/28 2700.00 50
Anthony Cabrio 1999/2/7 3000.00 50
Vance Jones 1999/3/17 2800.00 50
Martha Sullivan 1999/6/21 2500.00 50
Donald OConnell 1999/6/21 2600.00 50
Kevin Mourgos 1999/11/16 5800.00 50
Ki Gee 1999/12/12 2400.00 50
Randall Perkins 1999/12/19 2500.00 50
Douglas Grant 2000/1/13 2600.00 50
Girard Geoni 2000/2/3 2800.00 50
Hazel Philtanker 2000/2/6 2200.00 50
Steven Markle 2000/3/8 2200.00 50
Ellen Abel 1996/5/11 11000.00 80
John Russell 1996/10/1 14000.00 80
Karen Partners 1997/1/5 13500.00 80
Peter Tucker 1997/1/30 10000.00 80
Alberto Errazuriz 1997/3/10 12000.00 80
Lisa Ozer 1997/3/11 11500.00 80
Alyssa Hutton 1997/3/19 8800.00 80
David Bernstein 1997/3/24 9500.00 80
Peter Hall 1997/8/20 9000.00 80
Clara Vishney 1997/11/11 10500.00 80
Tayler Fox 1998/1/24 9600.00 80
Harrison Bloom 1998/3/23 10000.00 80
Jonathon Taylor 1998/3/24 8600.00 80
Christopher Olsen 1998/3/30 8000.00 80
Jack Livingston 1998/4/23 8400.00 80
Nanette Cambrault 1998/12/9 7500.00 80
William Smith 1999/2/23 7400.00 80
Danielle Greene 1999/3/19 9500.00 80
Elizabeth Bates 1999/3/24 7300.00 80
Gerald Cambrault 1999/10/15 11000.00 80
Mattea Marvins 2000/1/24 7200.00 80
Eleni Zlotkey 2000/1/29 10500.00 80
David Lee 2000/2/23 6800.00 80
Sundar Ande 2000/3/24 6400.00 80
Nancy Greenberg 1994/8/17 12000.00 100
Jose Manuel Urman 1998/3/7 7800.00 100

作者: www_xylove 时间: 2013-08-15 23:21
SQL学习4-10:
1. 各个部门平均、最大、最小工资、人数，按照部门号升序排列。
2. 各个部门中工资大于5000的员工人数。
3. 各个部门平均工资和人数，按照部门名字升序排列。
4. 列出每个部门中有同样工资的员工的统计信息，列出他们的部门号，工资，人数。
5. 列出同部门中工资高于10000 的员工数量超过2 人的部门，显示部门名字、地区名称。
6. 哪些员工的工资，高于整个公司的平均工资，列出员工的名字和工资（降序）。
7. 哪些员工的工资，介于50号和80号部门平均工资之间。
8. 所在部门平均工资高于5000 的员工名字。
9. 列出各个部门中工资最高的员工的信息：名字、部门号、工资。
10. 最高的部门平均工资是多少。

1. 各个部门平均、最大、最小工资、人数，按照部门号升序排列。
select a.department_id as "部门",
   count(a.employee_id) as "部门人数"，
   trunc(avg(a.salary)) as "平均工资",
   max(a.salary) as "最高工资",
   min(a.salary) as "最低工资"
  from hr.employees a
where a.department_id is not null
group by a.department_id
order by a.department_id

部门部门人数平均工资最高工资最低工资
10 1 4400 4400 4400
20 2 9500 13000 6000
30 6 4150 11000 2500
40 1 6500 6500 6500
50 45 3475 8200 2100
60 5 5760 9000 4200
70 1 10000 10000 10000
80 34 8955 14000 6100
90 3 19333 24000 17000
100 6 8600 12000 6900
110 2 10150 12000 8300

2. 各个部门中工资大于5000的员工人数。
select a.department_id, a.salary, count(a.employee_id)
  from hr.employees a
where a.salary > 5000
and a.department_id is not null
group by a.department_id, a.salary
order by a.department_id;

DEPARTMENT_ID SALARY COUNT(A.EMPLOYEE_ID)
20 6000.00 1
20 13000.00 1
30 11000.00 1
40 6500.00 1
50 5800.00 1
50 6500.00 1
50 7900.00 1
50 8000.00 1
50 8200.00 1
60 6000.00 1
60 9000.00 1
70 10000.00 1
80 6100.00 1
80 6200.00 2
80 6400.00 1
80 6800.00 1
80 7000.00 2
80 7200.00 1
80 7300.00 1
80 7400.00 1
80 7500.00 2
80 8000.00 2
80 8400.00 1
80 8600.00 1
80 8800.00 1
80 9000.00 2
80 9500.00 3
80 9600.00 1
80 10000.00 3
80 10500.00 2
80 11000.00 2
80 11500.00 1
80 12000.00 1
80 13500.00 1
80 14000.00 1
90 17000.00 2
90 24000.00 1
100 6900.00 1
100 7700.00 1
100 7800.00 1
100 8200.00 1
100 9000.00 1
100 12000.00 1
110 8300.00 1
110 12000.00 1

3. 各个部门平均工资和人数，按照部门名字升序排列。
select a.department_id as "部门",
   b.department_name as "部门名称",
   count(a.employee_id) as "部门人数",
   trunc(avg(a.salary)) as "平均工资"
  from hr.employees a, hr.departments b
where a.department_id is not null
and a.department_id = b.department_id
group by a.department_id,b.department_name
order by a.department_id

部门部门名称部门人数平均工资
10 Administration 1 4400
20 Marketing 2 9500
30 Purchasing 6 4150
40 Human Resources 1 6500
50 Shipping 45 3475
60 IT 5 5760
70 Public Relations 1 10000
80 Sales 34 8955
90 Executive 3 19333
100 Finance 6 8600
110 Accounting 2 10150

4. 列出每个部门中有同样工资的员工的统计信息，列出他们的部门号，工资，人数。
select b.department_id as "部门", b.salary as "工资", count(b.employee_id) as "员工数量"
  from hr.employees b,
   (select a.salary, count(*)
      from hr.employees a
      group by a.salary
      having count(*) > 1) x
where b.salary = x.salary
and b.department_id is not null
group by b.department_id, b.salary
order by count(b.employee_id) desc

部门    工资    员工数量
50 2500.00 5
50 3200.00 4
50 3100.00 3
50 2800.00 3
50 2600.00 3
80 9500.00 3
80 10000.00 3
80 9000.00 2
80 10500.00 2
50 3600.00 2
50 2200.00 2
90 17000.00 2
50 3300.00 2
80 8000.00 2
50 2900.00 2
80 7000.00 2
80 11000.00 2
80 7500.00 2
50 2400.00 2
80 6200.00 2
60 4800.00 2
50 3000.00 2
50 2700.00 2
60 6000.00 1
60 9000.00 1
30 2900.00 1
30 11000.00 1
100 8200.00 1
80 12000.00 1
110 12000.00 1
30 3100.00 1
30 2800.00 1
100 12000.00 1
40 6500.00 1
70 10000.00 1
50 4200.00 1
50 8200.00 1
50 6500.00 1
60 4200.00 1
30 2500.00 1
20 6000.00 1
100 9000.00 1
50 8000.00 1
30 2600.00 1

验证一下，部门50，工资都是2500的员工数量是不是5名员工。

select count(*) from hr.employees a where a.department_id =50  and salary = 2500
COUNT(*)
5

或者 5名员工的详细信息
select a.employee_id, a.first_name, a.last_name, a.salary, a.department_id
  from hr.employees a
where a.department_id = 50
and salary = 2500

EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY DEPARTMENT_ID
131 James Marlow 2500.00 50
140 Joshua Patel 2500.00 50
144 Peter Vargas 2500.00 50
182 Martha Sullivan 2500.00 50
191 Randall Perkins 2500.00 50

验证没有问题，的确是5名员工。

5. 列出同部门中工资高于10000 的员工数量超过2 人的部门，显示部门名字、地区名称。
select b.department_name, c.city
  from hr.employees a, hr.departments b, hr.locations c
where a.department_id in
   (select d.department_id
      from hr.departments d
      where d.department_id is not null)
and a.salary > 10000
and a.department_id = b.department_id
and b.location_id = c.location_id
group by a.department_id, b.department_name, c.city
having count(a.employee_id) > 2

DEPARTMENT_NAME CITY
Sales                Oxford
Executive          Seattle

6. 哪些员工的工资，高于整个公司的平均工资，列出员工的名字和工资（降序）。
select b.first_name ||' '|| b.last_name ,b.salary
  from hr.employees b
where b.salary > (select round(avg(salary)) from hr.employees a)
  order by b.salary desc

7. 哪些员工的工资，介于50号和80号部门平均工资之间。
select x.first_name || ' ' || x.last_name as "在50#和80#平均工资之间的员工",
   x.salary as "员工的工资",
   (select distinct round(avg(a.salary))
      from hr.employees a
      where a.department_id = 50) as "50#部门的平均工资",
   (select distinct round(avg(b.salary))
      from hr.employees b
      where b.department_id = 80) as "80#部门的平均工资"
  from hr.employees x
where x.salary between (select distinct round(avg(a.salary))
                        from hr.employees a
                        where a.department_id = 50) and
   (select distinct round(avg(b.salary))
      from hr.employees b
      where b.department_id = 80)
order by x.salary desc

我把50#部门的平均工资和80#部门的平均工资也列出来，目的是通过比较，结果集更加清晰。

在50#和80#平均工资之间的员工员工的工资 50#部门的平均工资 80#部门的平均工资
Alyssa Hutton 8800.00 3476 8956
Jonathon Taylor 8600.00 3476 8956
Jack Livingston 8400.00 3476 8956
William Gietz 8300.00 3476 8956
John Chen 8200.00 3476 8956
Adam Fripp 8200.00 3476 8956
Matthew Weiss 8000.00 3476 8956
Christopher Olsen 8000.00 3476 8956
Lindsey Smith 8000.00 3476 8956
Payam Kaufling 7900.00 3476 8956
Jose Manuel Urman 7800.00 3476 8956
Ismael Sciarra 7700.00 3476 8956
Louise Doran 7500.00 3476 8956
Nanette Cambrault 7500.00 3476 8956
William Smith 7400.00 3476 8956
Elizabeth Bates 7300.00 3476 8956
Mattea Marvins 7200.00 3476 8956
Oliver Tuvault 7000.00 3476 8956
Kimberely Grant 7000.00 3476 8956
Sarath Sewall 7000.00 3476 8956
Luis Popp 6900.00 3476 8956
David Lee 6800.00 3476 8956
Shanta Vollman 6500.00 3476 8956
Susan Mavris 6500.00 3476 8956
Sundar Ande 6400.00 3476 8956
Charles Johnson 6200.00 3476 8956
Amit Banda 6200.00 3476 8956
Sundita Kumar 6100.00 3476 8956
Bruce Ernst 6000.00 3476 8956
Pat Fay 6000.00 3476 8956
Kevin Mourgos 5800.00 3476 8956
David Austin 4800.00 3476 8956
Valli Pataballa 4800.00 3476 8956
Jennifer Whalen 4400.00 3476 8956
Diana Lorentz 4200.00 3476 8956
Nandita Sarchand 4200.00 3476 8956
Alexis Bull 4100.00 3476 8956
Sarah Bell 4000.00 3476 8956
Britney Everett 3900.00 3476 8956
Kelly Chung 3800.00 3476 8956
Jennifer Dilly 3600.00 3476 8956
Renske Ladwig 3600.00 3476 8956
Trenna Rajs 3500.00 3476 8956

8. 所在部门平均工资高于5000 的员工名字。
select b.first_name || ' ' || b.last_name as "部门员工",
   b.salary as "员工工资",
   c.department_id as "部门",
   c."部门平均工资"
  from hr.employees b,
   (select a.department_id, round(avg(a.salary)) as "部门平均工资"
      from hr.employees a
      where a.department_id is not null
      group by a.department_id) c
where b.department_id = c.department_id
and c."部门平均工资" > 5000
and b.salary > c."部门平均工资"

部门员工员工工资部门部门平均工资
Steven King 24000.00 90 19333
Alexander Hunold 9000.00 60 5760
Bruce Ernst 6000.00 60 5760
Nancy Greenberg 12000.00 100 8600
Daniel Faviet 9000.00 100 8600
John Russell 14000.00 80 8956
Karen Partners 13500.00 80 8956
Alberto Errazuriz 12000.00 80 8956
Gerald Cambrault 11000.00 80 8956
Eleni Zlotkey 10500.00 80 8956
Peter Tucker 10000.00 80 8956
David Bernstein 9500.00 80 8956
Peter Hall 9000.00 80 8956
Janette King 10000.00 80 8956
Patrick Sully 9500.00 80 8956
Allan McEwen 9000.00 80 8956
Clara Vishney 10500.00 80 8956
Danielle Greene 9500.00 80 8956
Lisa Ozer 11500.00 80 8956
Harrison Bloom 10000.00 80 8956
Tayler Fox 9600.00 80 8956
Ellen Abel 11000.00 80 8956
Michael Hartstein 13000.00 20 9500
Shelley Higgins 12000.00 110 10150

9. 列出各个部门中工资最高的员工的信息：名字、部门号、工资。
select b.department_id, b.first_name || ' ' || b.last_name, b.salary
  from hr.employees b,
   (select a.department_id, max(a.salary) as maxsal
      from hr.employees a
      where a.department_id is not null
      group by a.department_id
      order by a.department_id asc) c
where b.department_id = c.department_id
and b.salary = c.maxsal
order by b.salary desc;
或者
SELECT b.FIRST_NAME || ' ' || b.LAST_NAME AS NAME,
   b.SALARY,
   b.DEPARTMENT_ID
  FROM HR.EMPLOYEES b
WHERE (b.DEPARTMENT_ID, b.SALARY) IN
   (SELECT a.DEPARTMENT_ID, MAX(a.SALARY)
      FROM HR.EMPLOYEES a
      GROUP BY a.DEPARTMENT_ID);

DEPARTMENT_ID B.FIRST_NAME||''||B.LAST_NAME SALARY
90 Steven King 24000.00
80 John Russell 14000.00
20 Michael Hartstein 13000.00
100 Nancy Greenberg 12000.00
110 Shelley Higgins 12000.00
30 Den Raphaely 11000.00
70 Hermann Baer 10000.00
60 Alexander Hunold 9000.00
50 Adam Fripp 8200.00
40 Susan Mavris 6500.00
10 Jennifer Whalen 4400.00

10. 最高的部门平均工资是多少。
select max(round(avg(a.salary)))
  from hr.employees a
where a.department_id is not null
group by a.department_id

或者
SELECT MAX(AVGSALARY)
FROM(
SELECT DEPARTMENT_ID,ROUND(AVG(SALARY)) AVGSALARY
  FROM hr.EMPLOYEES
GROUP BY DEPARTMENT_ID
);

MAX(ROUND(AVG(A.SALARY)))
19333

作者: jackson198574 时间: 2013-08-18 07:52
回复 1# www_xylove

好帖子！顶！谢谢LZ分享！

作者: 开着Q7去西藏 时间: 2013-08-19 09:34
谢谢lz分享，

作者: wgzlong 时间: 2014-04-20 21:17
谢谢楼主分享，讲的很好理解

作者: dingning239 时间: 2014-04-23 13:43
版主这个帖子很好，适合大家学习提高SQL编写能力

作者: CxHeny 时间: 2014-08-03 13:16
楼主，我sql学的很差，感谢了

作者: dzminglong 时间: 2014-08-07 13:18
感谢分享，学习学习

作者: shlyxq 时间: 2014-09-28 16:18
我有一点困惑，只有题目，没有表结构的话，怎么写。还要去别的地方下么

欢迎光临 Chinaunix (http://bbs.chinaunix.net/)