经典查询练手第四篇

最新推荐文章于 2017-04-03 19:44:27 发布

blockheading

最新推荐文章于 2017-04-03 19:44:27 发布

阅读量417

点赞数

分类专栏： Oracle PLSQL/SQL

本文链接：https://blog.csdn.net/u013169075/article/details/53872037

版权

Oracle PLSQL/SQL 专栏收录该内容

28 篇文章 1 订阅

订阅专栏

HR@PROD2> desc EMPLOYEES
 Name						       Null?	Type
 ----------------------------------------------------- -------- ------------------------------------
 EMPLOYEE_ID					       NOT NULL NUMBER(6)
 FIRST_NAME							VARCHAR2(20)
 LAST_NAME					       NOT NULL VARCHAR2(25)
 EMAIL						       NOT NULL VARCHAR2(25)
 PHONE_NUMBER							VARCHAR2(20)
 HIRE_DATE					       NOT NULL DATE
 JOB_ID 					       NOT NULL VARCHAR2(10)
 SALARY 							NUMBER(8,2)
 COMMISSION_PCT 						NUMBER(2,2)
 MANAGER_ID							NUMBER(6)
 DEPARTMENT_ID							NUMBER(4)

HR@PROD2> desc DEPARTMENTS 
 Name						       Null?	Type
 ----------------------------------------------------- -------- ------------------------------------
 DEPARTMENT_ID					       NOT NULL NUMBER(4)
 DEPARTMENT_NAME				       NOT NULL VARCHAR2(30)
 MANAGER_ID							NUMBER(6)
 LOCATION_ID							NUMBER(4)

HR@PROD2> DESC REGIONS
 Name						       Null?	Type
 ----------------------------------------------------- -------- ------------------------------------
 REGION_ID					       NOT NULL NUMBER
 REGION_NAME							VARCHAR2(25)

HR@PROD2> desc locations
 Name						       Null?	Type
 ----------------------------------------------------- -------- ------------------------------------
 LOCATION_ID					       NOT NULL NUMBER(4)
 STREET_ADDRESS 						VARCHAR2(40)
 POSTAL_CODE							VARCHAR2(12)
 CITY						       NOT NULL VARCHAR2(30)
 STATE_PROVINCE 						VARCHAR2(25)
 COUNTRY_ID							CHAR(2)
 
HR@PROD2> desc jobs
 Name						       Null?	Type
 ----------------------------------------------------- -------- ------------------------------------
 JOB_ID 					       NOT NULL VARCHAR2(10)
 JOB_TITLE					       NOT NULL VARCHAR2(35)
 MIN_SALARY							NUMBER(6)
 MAX_SALARY							NUMBER(6)

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

1. 各个部门平均、最大、最小工资、人数，按照部门号升序排列。
HR@PROD2> select max(salary) msal,min(salary) isal,avg(salary) asal,count(*) 
from employees 
group by department_id 
order by department_id asc;

      MSAL	 ISAL	    ASAL   COUNT(*)
---------- ---------- ---------- ----------
      4400	 4400	    4400	  1
     13000	 6000	    9500	  2
     11000	 2500	    4150	  6
      6500	 6500	    6500	  1
      8200	 2100 3475.55556	 45
      9000	 4200	    5760	  5
     10000	10000	   10000	  1
     14000	 6100 8955.88235	 34
     24000	17000 19333.3333	  3
     12008	 6900 8601.33333	  6
     12008	 8300	   10154	  2
      7000	 7000	    7000	  1
	  
2. 各个部门中工资大于5000的员工人数。
HR@PROD2> select department_id,count(*) 
from employees 
where salary>5000 
group by department_id 
order by department_id;

DEPARTMENT_ID	COUNT(*)
------------- ----------
	   20	       2
	   30	       1
	   40	       1
	   50	       5
	   60	       2
	   70	       1
	   80	      34
	   90	       3
	  100	       6
	  110	       2
		       1

11 rows selected.

3. 各个部门平均工资和人数，按照部门名字升序排列。
HR@PROD2> select e.department_id,d.department_name,avg(e.salary) asal,count(*)
from employees e,departments d
where e.department_id=d.department_id
group by e.department_id,d.department_name
order by d.department_name;  

DEPARTMENT_ID DEPARTMENT_NAME			   ASAL   COUNT(*)
------------- ------------------------------ ---------- ----------
	  110 Accounting			  10154 	 2
	   10 Administration			   4400 	 1
	   90 Executive 		     19333.3333 	 3
	  100 Finance			     8601.33333 	 6
	   40 Human Resources			   6500 	 1
	   60 IT				   5760 	 5
	   20 Marketing 			   9500 	 2
	   70 Public Relations			  10000 	 1
	   30 Purchasing			   4150 	 6
	   80 Sales			     8955.88235 	34
	   50 Shipping			     3475.55556 	45

11 rows selected.

4. 列出每个部门中有同样工资的员工的统计信息，列出他们的部门号，工资，人数。
HR@PROD2> select a.department_id,a.salary,count(*)
from employees a,employees b
where a.department_id=b.department_id and a.employee_id <> b.employee_id and a.salary=b.salary
group by a.department_id,a.salary
order by a.department_id;  2    3    4    5  

DEPARTMENT_ID	  SALARY   COUNT(*)
------------- ---------- ----------
	   50	    2200	  2
	   50	    2400	  2
	   50	    2500	 20
	   50	    2600	  6
	   50	    2700	  2
	   50	    2800	  6
	   50	    2900	  2
	   50	    3000	  2
	   50	    3100	  6
	   50	    3200	 12
	   50	    3300	  2
	   50	    3600	  2
	   60	    4800	  2
	   80	    6200	  2
	   80	    7000	  2
	   80	    7500	  2
	   80	    8000	  2
	   80	    9000	  2
	   80	    9500	  6
	   80	   10000	  6
	   80	   10500	  2
	   80	   11000	  2
	   90	   17000	  2

23 rows selected.

5. 列出同部门中工资高于1000 的员工数量超过2 人的部门，显示部门名字、地区名称。
HR@PROD2> select d.department_name,l.city,count(*)
from departments d,locations l,employees e
where d.location_id=l.location_id and e.department_id=d.department_id and salary>1000
group by d.department_name,l.city
having count(*)>2;  2    3    4    5  

DEPARTMENT_NAME 	       CITY				COUNT(*)
------------------------------ ------------------------------ ----------
IT			       Southlake			       5
Sales			       Oxford				      34
Shipping		       South San Francisco		      45
Purchasing		       Seattle				       6
Executive		       Seattle				       3
Finance 		       Seattle				       6

6 rows selected.

6. 哪些员工的工资，高于整个公司的平均工资，列出员工的名字和工资（降序）。
HR@PROD2> select first_name||' '||last_name name, salary
from employees e,(select avg(salary) asal from employees ) a
where  e.salary>a.asal;  

NAME						   SALARY
---------------------------------------------- ----------
Michael Hartstein				    13000
Susan Mavris					     6500
Hermann Baer					    10000
Shelley Higgins 				    12008
William Gietz					     8300
Steven King					    24000
Neena Kochhar					    17000
Lex De Haan					    17000
Alexander Hunold				     9000
Nancy Greenberg 				    12008
Daniel Faviet					     9000
John Chen					     8200
Ismael Sciarra					     7700
Jose Manuel Urman				     7800
Luis Popp					     6900
Den Raphaely					    11000
Matthew Weiss					     8000
Adam Fripp					     8200
Payam Kaufling					     7900
Shanta Vollman					     6500
John Russell					    14000
Karen Partners					    13500
Alberto Errazuriz				    12000
Gerald Cambrault				    11000
Eleni Zlotkey					    10500
Peter Tucker					    10000
David Bernstein 				     9500
Peter Hall					     9000
Christopher Olsen				     8000
Nanette Cambrault				     7500
Oliver Tuvault					     7000
Janette King					    10000
Patrick Sully					     9500
Allan McEwen					     9000
Lindsey Smith					     8000
Louise Doran					     7500
Sarath Sewall					     7000
Clara Vishney					    10500
Danielle Greene 				     9500
Mattea Marvins					     7200
David Lee					     6800
Lisa Ozer					    11500
Harrison Bloom					    10000
Tayler Fox					     9600
William Smith					     7400
Elizabeth Bates 				     7300
Ellen Abel					    11000
Alyssa Hutton					     8800
Jonathon Taylor 				     8600
Jack Livingston 				     8400
Kimberely Grant 				     7000

51 rows selected.

--或者如下方法
select first_name||' '||last_name name,salary
from employees
where salary > (select avg(salary) from employees);

7. 哪些员工的工资，介于50号 和80号部门平均工资之间。
HR@PROD2> select first_name||' '||last_name name
from employees
where salary between (select avg(salary) from employees where department_id='50') and (select avg(salary) from employees where department_id='80');  

NAME
----------------------------------------------
Jennifer Whalen
Pat Fay
Susan Mavris
William Gietz
Bruce Ernst
David Austin
Valli Pataballa
Diana Lorentz
John Chen
Ismael Sciarra
Jose Manuel Urman
Luis Popp
Matthew Weiss
Adam Fripp
Payam Kaufling
Shanta Vollman
Kevin Mourgos
Renske Ladwig
Trenna Rajs
Christopher Olsen
Nanette Cambrault
Oliver Tuvault
Lindsey Smith
Louise Doran
Sarath Sewall
Mattea Marvins
David Lee
Sundar Ande
Amit Banda
William Smith
Elizabeth Bates
Sundita Kumar
Alyssa Hutton
Jonathon Taylor
Jack Livingston
Kimberely Grant
Charles Johnson
Nandita Sarchand
Alexis Bull
Kelly Chung
Jennifer Dilly
Sarah Bell
Britney Everett

43 rows selected.

8. 所在部门平均工资高于5000 的员工名字。
HR@PROD2> select first_name||' '||last_name name,salary
from employees
where department_id in (select department_id from employees group by department_id having avg(salary)>5000);

NAME						   SALARY
---------------------------------------------- ----------
Nancy Greenberg 				    12008
Daniel Faviet					     9000
John Chen					     8200
Ismael Sciarra					     7700
Jose Manuel Urman				     7800
Luis Popp					     6900
Michael Hartstein				    13000
Pat Fay 					     6000
Hermann Baer					    10000
Steven King					    24000
Neena Kochhar					    17000
Lex De Haan					    17000
Shelley Higgins 				    12008
William Gietz					     8300
Susan Mavris					     6500
John Russell					    14000
Karen Partners					    13500
Alberto Errazuriz				    12000
Gerald Cambrault				    11000
Eleni Zlotkey					    10500
Peter Tucker					    10000
David Bernstein 				     9500
Peter Hall					     9000
Christopher Olsen				     8000
Nanette Cambrault				     7500
Oliver Tuvault					     7000
Janette King					    10000
Patrick Sully					     9500
Allan McEwen					     9000
Lindsey Smith					     8000
Louise Doran					     7500
Sarath Sewall					     7000
Clara Vishney					    10500
Danielle Greene 				     9500
Mattea Marvins					     7200
David Lee					     6800
Sundar Ande					     6400
Amit Banda					     6200
Lisa Ozer					    11500
Harrison Bloom					    10000
Tayler Fox					     9600
William Smith					     7400
Elizabeth Bates 				     7300
Sundita Kumar					     6100
Ellen Abel					    11000
Alyssa Hutton					     8800
Jonathon Taylor 				     8600
Jack Livingston 				     8400
Charles Johnson 				     6200
Alexander Hunold				     9000
Bruce Ernst					     6000
David Austin					     4800
Valli Pataballa 				     4800
Diana Lorentz					     4200

54 rows selected.

9. 列出各个部门中工资最高的员工的信息：名字、部门号、工资。
HR@PROD2> select e.first_name||' '||e.last_name name,e.department_id,e.salary
from employees e,(select department_id,max(salary) msal from employees group by department_id) s
where e.department_id=s.department_id and e.salary=s.msal; 

NAME					       DEPARTMENT_ID	 SALARY
---------------------------------------------- ------------- ----------
Nancy Greenberg 					 100	  12008
Den Raphaely						  30	  11000
Michael Hartstein					  20	  13000
Hermann Baer						  70	  10000
Steven King						  90	  24000
Shelley Higgins 					 110	  12008
Adam Fripp						  50	   8200
Susan Mavris						  40	   6500
John Russell						  80	  14000
Jennifer Whalen 					  10	   4400
Alexander Hunold					  60	   9000

11 rows selected.

--另一种简单的方法
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. 最高的部门平均工资是多少。
HR@PROD2> select max(avg(salary)) from employees group by department_id;

MAX(AVG(SALARY))
----------------
      19333.3333

blockheading

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
经典查询练手第四篇

HR@PROD2> desc EMPLOYEES Name Null? Type ----------------------------------------------------- -------- ------------------------------------ EMPLOYEE_ID NOT NULL NUMBER(6) F
复制链接

扫一扫

专栏目录