经典查询练手第五篇

--经典查询练手第五篇

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)

1. 哪些部门的人数比90 号部门的人数多。
2. Den(FIRST_NAME)、Raphaely(LAST_NAME)的领导是谁(非关联子查询)。
3. Den(FIRST_NAME)、Raphaely(LAST_NAME) 领导谁(非关联子查询)。
4. Den(FIRST_NAME)、Raphaely(LAST_NAME) 的领导是谁(关联子查询)。
5. Den(FIRST_NAME)、Raphaely(LAST_NAME) 领导谁(关联子查询)。
6. 列出在同一部门共事，入职日期晚但工资高于其他同事的员工：名字、工资、入职日期(关联子查询)。
7. 哪些员工跟Den(FIRST_NAME)、Raphaely(LAST_NAME)不在同一个部门(非关联子查询)。
8. 哪些员工跟Den(FIRST_NAME)、Raphaely(LAST_NAME)不在同一个部门(关联子查询)。
9. Finance部门有哪些职位(非关联子查询)。
10. Finance部门有哪些职位(关联子查询)

1. 哪些部门的人数比90号部门的人数多。
HR@PROD2> select department_id,count(*) 
from employees 
group by department_id
having count(*) > (select count(*) from employees where department_id=90);   

DEPARTMENT_ID	COUNT(*)
------------- ----------
	  100	       6
	   30	       6
	   50	      45
	   80	      34
	   60	       5
	   
2. Den(FIRST_NAME)、Raphaely(LAST_NAME)的领导是谁(非关联子查询)。
HR@PROD2> select first_name||' '||last_name name 
from employees 
where employee_id in (select manager_id from employees where first_name='Den' and last_name='Raphaely');   

NAME
----------------------------------------------
Steven King

3. Den(FIRST_NAME)、Raphaely(LAST_NAME) 领导谁(非关联子查询)。
HR@PROD2> select first_name||' '||last_name name
from employees
where manager_id in (select employee_id from employees where first_name='Den' and last_name='Raphaely');  

NAME
----------------------------------------------
Alexander Khoo
Shelli Baida
Sigal Tobias
Guy Himuro
Karen Colmenares
	
4. Den(FIRST_NAME)、Raphaely(LAST_NAME) 的领导是谁(关联子查询)。
HR@PROD2> SELECT FIRST_NAME || ' ' || LAST_NAME
FROM EMPLOYEES EMP1
WHERE EXISTS (
SELECT 1 FROM EMPLOYEES EMP2
WHERE FIRST_NAME = 'Den'
AND LAST_NAME = 'Raphaely'
AND EMP2.MANAGER_ID = EMP1.EMPLOYEE_ID);  

FIRST_NAME||''||LAST_NAME
----------------------------------------------
Steven King


5. Den(FIRST_NAME)、Raphaely(LAST_NAME) 领导谁(关联子查询)。
HR@PROD2> SELECT FIRST_NAME || ' ' || LAST_NAME
FROM EMPLOYEES EMP1
WHERE EXISTS (
SELECT 1 FROM EMPLOYEES EMP2
WHERE FIRST_NAME = 'Den'
AND LAST_NAME = 'Raphaely'
AND EMP2.EMPLOYEE_ID = EMP1.MANAGER_ID); 

FIRST_NAME||''||LAST_NAME
----------------------------------------------
Alexander Khoo
Shelli Baida
Sigal Tobias
Guy Himuro
Karen Colmenares

6. 列出在同一部门共事，入职日期晚但工资高于其他同事的员工：名字、工资、入职日期(关联子查询)。
HR@PROD2> SELECT FIRST_NAME||' '||LAST_NAME NAME,SALARY,HIRE_DATE
FROM EMPLOYEES E1 
WHERE EXISTS(
SELECT 1 FROM EMPLOYEES E2
WHERE  E1.HIRE_DATE>E2.HIRE_DATE 
AND E1.SALARY>E2.SALARY
AND E1.DEPARTMENT_ID=E2.DEPARTMENT_ID);  2    3    4    5    6    7  

NAME						   SALARY HIRE_DATE
---------------------------------------------- ---------- ---------
Girard Geoni					     2800 03-FEB-08
Kevin Mourgos					     5800 16-NOV-07
Steven King					    24000 17-JUN-03
Bruce Ernst					     6000 21-MAY-07
Alexander Hunold				     9000 03-JAN-06
Nancy Greenberg 				    12008 17-AUG-02
Jose Manuel Urman				     7800 07-MAR-06
Shelli Baida					     2900 24-DEC-05
Adam Fripp					     8200 10-APR-05
Matthew Weiss					     8000 18-JUL-04
Jennifer Dilly					     3600 13-AUG-05
Julia Dellinger 				     3400 24-JUN-06
Laura Bissot					     3300 20-AUG-05
Shanta Vollman					     6500 10-OCT-05
Vance Jones					     2800 17-MAR-07
Anthony Cabrio					     3000 07-FEB-07
Randall Perkins 				     2500 19-DEC-07
Martha Sullivan 				     2500 21-JUN-07
Douglas Grant					     2600 13-JAN-08
Donald OConnell 				     2600 21-JUN-07
Kevin Feeney					     3000 23-MAY-06
Alana Walsh					     3100 24-APR-06
Samuel McCain					     3200 01-JUL-06
Timothy Gates					     2900 11-JUL-06
Jean Fleaur					     3100 23-FEB-06
Winston Taylor					     3200 24-JAN-06
Michael Rogers					     2900 26-AUG-06
Britney Everett 				     3900 03-MAR-05
Kelly Chung					     3800 14-JUN-05
Alexis Bull					     4100 20-FEB-05
Randall Matos					     2600 15-MAR-06
John Seo					     2700 12-FEB-06
Stephen Stiles					     3200 26-OCT-05
Mozhe Atkinson					     2800 30-OCT-05
Irene Mikkilineni				     2700 28-SEP-06
Julia Nayer					     3200 16-JUL-05
Hazel Philtanker				     2200 06-FEB-08
Ki Gee						     2400 12-DEC-07
Steven Markle					     2200 08-MAR-08
Sarah Bell					     4000 04-FEB-04
Nandita Sarchand				     4200 27-JAN-04
Lisa Ozer					    11500 11-MAR-05
Clara Vishney					    10500 11-NOV-05
Eleni Zlotkey					    10500 29-JAN-08
Gerald Cambrault				    11000 15-OCT-07
Alberto Errazuriz				    12000 10-MAR-05
Tayler Fox					     9600 24-JAN-06
Harrison Bloom					    10000 23-MAR-06
Danielle Greene 				     9500 19-MAR-07
Jack Livingston 				     8400 23-APR-06
Mattea Marvins					     7200 24-JAN-08
Ellen Abel					    11000 11-MAY-04
Karen Partners					    13500 05-JAN-05
John Russell					    14000 01-OCT-04
Peter Tucker					    10000 30-JAN-05
David Bernstein 				     9500 24-MAR-05
Jonathon Taylor 				     8600 24-MAR-06
Alyssa Hutton					     8800 19-MAR-05
Peter Hall					     9000 20-AUG-05
Christopher Olsen				     8000 30-MAR-06
Elizabeth Bates 				     7300 24-MAR-07
William Smith					     7400 23-FEB-07
Nanette Cambrault				     7500 09-DEC-06
Sundar Ande					     6400 24-MAR-08
David Lee					     6800 23-FEB-08

65 rows selected.

7. 哪些员工跟Den(FIRST_NAME)、Raphaely(LAST_NAME)不在同一个部门(非关联子查询)。
HR@PROD2> SELECT FIRST_NAME||' '||LAST_NAME NAME
FROM EMPLOYEES E1
WHERE DEPARTMENT_ID <> (
SELECT DEPARTMENT_ID
FROM EMPLOYEES E2
WHERE FIRST_NAME='Den' and LAST_NAME='Raphaely');  

NAME
----------------------------------------------
Ellen Abel
Sundar Ande
Mozhe Atkinson
David Austin
Hermann Baer
Amit Banda
Elizabeth Bates
Sarah Bell
David Bernstein
Laura Bissot
Harrison Bloom
Alexis Bull
Anthony Cabrio
Gerald Cambrault
Nanette Cambrault
John Chen
Kelly Chung
Curtis Davies
Lex De Haan
Julia Dellinger
Jennifer Dilly
Louise Doran
Bruce Ernst
Alberto Errazuriz
Britney Everett
Daniel Faviet
Pat Fay
Kevin Feeney
Jean Fleaur
Tayler Fox
Adam Fripp
Timothy Gates
Ki Gee
Girard Geoni
William Gietz
Douglas Grant
Nancy Greenberg
Danielle Greene
Peter Hall
Michael Hartstein
Shelley Higgins
Alexander Hunold
Alyssa Hutton
Charles Johnson
Vance Jones
Payam Kaufling
Janette King
Steven King
Neena Kochhar
Sundita Kumar
Renske Ladwig
James Landry
David Lee
Jack Livingston
Diana Lorentz
Jason Mallin
Steven Markle
James Marlow
Mattea Marvins
Randall Matos
Susan Mavris
Samuel McCain
Allan McEwen
Irene Mikkilineni
Kevin Mourgos
Julia Nayer
Donald OConnell
Christopher Olsen
TJ Olson
Lisa Ozer
Karen Partners
Valli Pataballa
Joshua Patel
Randall Perkins
Hazel Philtanker
Luis Popp
Trenna Rajs
Michael Rogers
John Russell
Nandita Sarchand
Ismael Sciarra
John Seo
Sarath Sewall
Lindsey Smith
William Smith
Stephen Stiles
Martha Sullivan
Patrick Sully
Jonathon Taylor
Winston Taylor
Peter Tucker
Oliver Tuvault
Jose Manuel Urman
Peter Vargas
Clara Vishney
Shanta Vollman
Alana Walsh
Matthew Weiss
Jennifer Whalen
Eleni Zlotkey

100 rows selected.

8. 哪些员工跟Den(FIRST_NAME)、Raphaely(LAST_NAME)不在同一个部门(关联子查询)。
HR@PROD2> SELECT FIRST_NAME||' '||LAST_NAME NAME
FROM EMPLOYEES E1
WHERE NOT EXISTS(
SELECT 1
FROM EMPLOYEES E2
WHERE E2.FIRST_NAME='Den' and E2.LAST_NAME='Raphaely' AND E1.DEPARTMENT_ID=E2.DEPARTMENT_ID);   

NAME
----------------------------------------------
Kimberely Grant
Lex De Haan
Neena Kochhar
Steven King
Pat Fay
Michael Hartstein
Diana Lorentz
Valli Pataballa
David Austin
Bruce Ernst
Alexander Hunold
Jennifer Whalen
Hermann Baer
Charles Johnson
Jack Livingston
Jonathon Taylor
Alyssa Hutton
Ellen Abel
Sundita Kumar
Elizabeth Bates
William Smith
Tayler Fox
Harrison Bloom
Lisa Ozer
Amit Banda
Sundar Ande
David Lee
Mattea Marvins
Danielle Greene
Clara Vishney
Sarath Sewall
Louise Doran
Lindsey Smith
Allan McEwen
Patrick Sully
Janette King
Oliver Tuvault
Nanette Cambrault
Christopher Olsen
Peter Hall
David Bernstein
Peter Tucker
Eleni Zlotkey
Gerald Cambrault
Alberto Errazuriz
Karen Partners
John Russell
William Gietz
Shelley Higgins
Kevin Feeney
Alana Walsh
Vance Jones
Samuel McCain
Britney Everett
Sarah Bell
Randall Perkins
Timothy Gates
Jennifer Dilly
Kelly Chung
Anthony Cabrio
Julia Dellinger
Alexis Bull
Nandita Sarchand
Girard Geoni
Martha Sullivan
Jean Fleaur
Winston Taylor
Peter Vargas
Randall Matos
Curtis Davies
Trenna Rajs
Joshua Patel
John Seo
Stephen Stiles
Renske Ladwig
Hazel Philtanker
Ki Gee
Michael Rogers
Jason Mallin
TJ Olson
James Marlow
Mozhe Atkinson
Laura Bissot
Steven Markle
James Landry
Irene Mikkilineni
Julia Nayer
Kevin Mourgos
Shanta Vollman
Payam Kaufling
Adam Fripp
Matthew Weiss
Douglas Grant
Donald OConnell
Luis Popp
Jose Manuel Urman
Ismael Sciarra
John Chen
Daniel Faviet
Nancy Greenberg
Susan Mavris

101 rows selected.

9. Finance部门有哪些职位(非关联子查询)。
HR@PROD2> SELECT DISTINCT JOB_ID FROM EMPLOYEES
WHERE DEPARTMENT_ID = (
SELECT DEPARTMENT_ID FROM DEPARTMENTS
WHERE DEPARTMENT_NAME = 'Finance');  

JOB_ID
----------
FI_ACCOUNT
FI_MGR

10. Finance部门有哪些职位(关联子查询)
HR@PROD2> SELECT DISTINCT JOB_ID FROM EMPLOYEES
WHERE EXISTS(
SELECT 1 FROM DEPARTMENTS
WHERE EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID
AND DEPARTMENTS.DEPARTMENT_NAME = 'Finance');  

JOB_ID
----------
FI_ACCOUNT
FI_MGR