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