本文主要是介绍经典查询练手第五篇，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
--经典查询练手第五篇
HR@PROD2> desc EMPLOYEESName 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 DATEJOB_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 630 650 4580 3460 52. 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 King3. 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 Colmenares4. 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 King5. 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 Colmenares6. 列出在同一部门共事，入职日期晚但工资高于其他同事的员工：名字、工资、入职日期(关联子查询)。
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-0865 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 Zlotkey100 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 Mavris101 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_MGR10. 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这篇关于经典查询练手第五篇的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
