--经典查询练手第三篇
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. 让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 输出结果的货币单位是￥和$。
HR@PROD2> SELECT TO_CHAR(SALARY,'L99,999.99') FROM HR.EMPLOYEES WHERE ROWNUM < 5; --因为默认是英文环境
TO_CHAR(SALARY,'L99,
--------------------
$2,600.00
$2,600.00
$4,400.00
$13,000.00
HR@PROD2> alter session set nls_currency=RMB;
Session altered.
HR@PROD2> SELECT TO_CHAR(SALARY,'L99,999.99') FROM HR.EMPLOYEES WHERE ROWNUM < 5;
TO_CHAR(SALARY,'L99,
--------------------
RMB2,600.00
RMB2,600.00
RMB4,400.00
RMB13,000.00
2. 列出前五位每个员工的名字，工资、涨薪后的的工资（涨幅为8%），以“元”为单位进行四舍五入。
HR@PROD2> select first_name||' '||last_name Name,salary,round(salary*1.08) new_sal from employees where rownum<5;
NAME SALARY NEW_SA
---------------------------------------------- ---------- ------
Donald OConnell 2600 2808
Douglas Grant 2600 2808
Jennifer Whalen 4400 4752
Michael Hartstein 13000 14040
3. 找出谁是最高领导，将名字按大写形式显示。
HR@PROD2> select upper(first_name||' '||last_name) name from employees where manager_id is null;
NAME
----------------------------------------------
STEVEN KING
4. 找出First_Name 为David，Last_Name 为Austin 的直接领导名字。
HR@PROD2> select a.first_name||' '||a.last_name name
from employees a left join employees b on (a.employee_id=b.manager_id)
where b.first_name='David' and b.last_name='Austin';
NAME
----------------------------------------------
Alexander Hunold
5. First_Name 为Alexander，Last_Name 为Hunold 领导谁。（谁向David 报告）。
HR@PROD2> select b.first_name||' '||b.last_name name
from employees a left join employees b on (a.employee_id=b.manager_id)
where a.first_name='Alexander' and a.last_name='Hunold';
NAME
----------------------------------------------
Bruce Ernst
David Austin
Valli Pataballa
Diana Lorentz
6. 哪些员工的工资高于他直接上司的工资，列出员工的名字和工资，上司的名字和工资。
HR@PROD2> select b.first_name||' '||b.last_name name,b.salary,a.first_name||' '||a.last_name name,a.salary
from employees a left join employees b on (a.employee_id=b.manager_id)
where b.salary>a.salary;
NAME SALARY NAME SALARY
---------------------------------------------- ---------- ---------------------------------------------- ----------
Lisa Ozer 11500 Gerald Cambrault 11000
Ellen Abel 11000 Eleni Zlotkey 10500
7. 哪些员工和Chen(LAST_NAME)同部门。
HR@PROD2> select first_name||' '||last_name name
from employees
where department_id = (select department_id from employees where last_name='Chen') and last_name<>'Chen';
NAME
----------------------------------------------
Nancy Greenberg
Daniel Faviet
Ismael Sciarra
Jose Manuel Urman
Luis Popp
6 rows selected.
8. 哪些员工跟De Haan(LAST_NAME)做一样职位。
HR@PROD2> select first_name||' '||last_name name
from employees
where job_id = (select job_id from employees where last_name='De Haan') and last_name<>'De Haan';
NAME
----------------------------------------------
Neena Kochhar
9. 哪些员工跟Hall(LAST_NAME)不在同一个部门。
HR@PROD2> select first_name||' '||last_name name
from employees
where department_id <> (select department_id from employees where last_name='Hall');
NAME
----------------------------------------------
Mozhe Atkinson
David Austin
Hermann Baer
Shelli Baida
Sarah Bell
Laura Bissot
Alexis Bull
Anthony Cabrio
John Chen
Kelly Chung
Karen Colmenares
Curtis Davies
Lex De Haan
Julia Dellinger
Jennifer Dilly
Bruce Ernst
Britney Everett
Daniel Faviet
Pat Fay
Kevin Feeney
Jean Fleaur
Adam Fripp
Timothy Gates
Ki Gee
Girard Geoni
William Gietz
Douglas Grant
Nancy Greenberg
Michael Hartstein
Shelley Higgins
Guy Himuro
Alexander Hunold
Vance Jones
Payam Kaufling
Alexander Khoo
Steven King
Neena Kochhar
Renske Ladwig
James Landry
Diana Lorentz
Jason Mallin
Steven Markle
James Marlow
Randall Matos
Susan Mavris
Samuel McCain
Irene Mikkilineni
Kevin Mourgos
Julia Nayer
Donald OConnell
TJ Olson
Valli Pataballa
Joshua Patel
Randall Perkins
Hazel Philtanker
Luis Popp
Trenna Rajs
Den Raphaely
Michael Rogers
Nandita Sarchand
Ismael Sciarra
John Seo
Stephen Stiles
Martha Sullivan
Winston Taylor
Sigal Tobias
Jose Manuel Urman
Peter Vargas
Shanta Vollman
Alana Walsh
Matthew Weiss
Jennifer Whalen
72 rows selected.
10. 哪些员工跟William（FIRST_NAME）、Smith(LAST_NAME)做不一样的职位。
HR@PROD2> select first_name||' '||last_name name
from employees
where job_id not in (select distinct job_id from employees where first_name='William' and last_name='Smith');
NAME
----------------------------------------------
Lex De Haan
Neena Kochhar
Karen Colmenares
Guy Himuro
Sigal Tobias
Shelli Baida
Alexander Khoo
Pat Fay
Michael Hartstein
Eleni Zlotkey
Gerald Cambrault
Alberto Errazuriz
Karen Partners
John Russell
Kevin Mourgos
Shanta Vollman
Payam Kaufling
Adam Fripp
Matthew Weiss
Steven King
William Gietz
Shelley Higgins
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
Susan Mavris
Hermann Baer
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
Douglas Grant
Donald OConnell
Den Raphaely
Jennifer Whalen
Nancy Greenberg
Luis Popp
Jose Manuel Urman
Ismael Sciarra
John Chen
Daniel Faviet
Diana Lorentz
Valli Pataballa
David Austin
Bruce Ernst
Alexander Hunold
77 rows selected.
11. 显示有提成的员工的信息：名字、提成、所在部门名称、所在地区的名称。
HR@PROD2> select e.first_name||' '||e.last_name name,e.commission_pct,d.department_name,l.city
from employees e, departments d, locations l
where e.department_id=d.department_id and d.location_id=l.location_id and e.commission_pct <> 0;
NAME COMMISSION_PCT DEPARTMENT_NAME CITY
---------------------------------------------- -------------- ------------------------------ ------------------------------
John Russell .4 Sales Oxford
Karen Partners .3 Sales Oxford
Alberto Errazuriz .3 Sales Oxford
Gerald Cambrault .3 Sales Oxford
Eleni Zlotkey .2 Sales Oxford
Peter Tucker .3 Sales Oxford
David Bernstein .25 Sales Oxford
Peter Hall .25 Sales Oxford
Christopher Olsen .2 Sales Oxford
Nanette Cambrault .2 Sales Oxford
Oliver Tuvault .15 Sales Oxford
Janette King .35 Sales Oxford
Patrick Sully .35 Sales Oxford
Allan McEwen .35 Sales Oxford
Lindsey Smith .3 Sales Oxford
Louise Doran .3 Sales Oxford
Sarath Sewall .25 Sales Oxford
Clara Vishney .25 Sales Oxford
Danielle Greene .15 Sales Oxford
Mattea Marvins .1 Sales Oxford
David Lee .1 Sales Oxford
Sundar Ande .1 Sales Oxford
Amit Banda .1 Sales Oxford
Lisa Ozer .25 Sales Oxford
Harrison Bloom .2 Sales Oxford
Tayler Fox .2 Sales Oxford
William Smith .15 Sales Oxford
Elizabeth Bates .15 Sales Oxford
Sundita Kumar .1 Sales Oxford
Ellen Abel .3 Sales Oxford
Alyssa Hutton .25 Sales Oxford
Jonathon Taylor .2 Sales Oxford
Jack Livingston .2 Sales Oxford
Charles Johnson .1 Sales Oxford
34 rows selected.
12. 显示Executive 部门有哪些职位。
HR@PROD2> select distinct j.job_title
from jobs j,departments d,employees e
where e.department_id=d.department_id and e.job_id=j.job_id and d.department_name='Executive';
JOB_TITLE
-----------------------------------
Administration Vice President
President
13. 整个公司中，最高工资和最低工资相差多少。
HR@PROD2> select max(salary)-min(salary) diff from employees;
DIFF
----------
21900
14. 提成大于0 的人数。
HR@PROD2> select count(*) from employees where commission_pct >0;
COUNT(*)
----------
35
15. 显示整个公司的最高工资、最低工资、工资总和、平均工资保留到整数位。
HR@PROD2> select max(salary) msal,min(salary) lsal,sum(salary) ssal, round(avg(salary)) rsal from employees;
MSAL LSAL SSAL RSAL
---------- ---------- ---------- ----------
24000 2100 691416 6462
16. 整个公司有多少个领导。
HR@PROD2> select count(sum_e.manager_id)
from (select distinct manager_id from employees where manager_id is not null) sum_e;
COUNT(SUM_E.MANAGER_ID)
-----------------------
18
17. 列出在同一部门入职日期晚但工资高于其他同事的员工：名字、工资、入职日期。
HR@PROD2> select distinct a.first_name||' '||a.last_name name,a.salary,a.hire_date
from employees a,employees b
where a.hire_date>b.hire_date and a.salary>b.salary and a.department_id=b.department_id; 2 3
NAME SALARY HIRE_DATE
---------------------------------------------- ---------- ---------
Donald OConnell 2600 21-JUN-07
Kelly Chung 3800 14-JUN-05
Irene Mikkilineni 2700 28-SEP-06
Sarah Bell 4000 04-FEB-04
Alberto Errazuriz 12000 10-MAR-05
Harrison Bloom 10000 23-MAR-06
Danielle Greene 9500 19-MAR-07
Alexander Hunold 9000 03-JAN-06
Nancy Greenberg 12008 17-AUG-02
Randall Perkins 2500 19-DEC-07
Winston Taylor 3200 24-JAN-06
Nandita Sarchand 4200 27-JAN-04
Clara Vishney 10500 11-NOV-05
Eleni Zlotkey 10500 29-JAN-08
Tayler Fox 9600 24-JAN-06
Mattea Marvins 7200 24-JAN-08
Alana Walsh 3100 24-APR-06
Michael Rogers 2900 26-AUG-06
Randall Matos 2600 15-MAR-06
Ki Gee 2400 12-DEC-07
Gerald Cambrault 11000 15-OCT-07
David Bernstein 9500 24-MAR-05
Peter Hall 9000 20-AUG-05
Kevin Mourgos 5800 16-NOV-07
Steven King 24000 17-JUN-03
Douglas Grant 2600 13-JAN-08
Jean Fleaur 3100 23-FEB-06
Mozhe Atkinson 2800 30-OCT-05
Jonathon Taylor 8600 24-MAR-06
Christopher Olsen 8000 30-MAR-06
Girard Geoni 2800 03-FEB-08
Bruce Ernst 6000 21-MAY-07
Jose Manuel Urman 7800 07-MAR-06
Adam Fripp 8200 10-APR-05
Jennifer Dilly 3600 13-AUG-05
Vance Jones 2800 17-MAR-07
Anthony Cabrio 3000 07-FEB-07
Samuel McCain 3200 01-JUL-06
Timothy Gates 2900 11-JUL-06
Julia Nayer 3200 16-JUL-05
Martha Sullivan 2500 21-JUN-07
Britney Everett 3900 03-MAR-05
John Seo 2700 12-FEB-06
Stephen Stiles 3200 26-OCT-05
Hazel Philtanker 2200 06-FEB-08
Steven Markle 2200 08-MAR-08
Jack Livingston 8400 23-APR-06
John Russell 14000 01-OCT-04
Peter Tucker 10000 30-JAN-05
Alyssa Hutton 8800 19-MAR-05
Shelli Baida 2900 24-DEC-05
Matthew Weiss 8000 18-JUL-04
Laura Bissot 3300 20-AUG-05
Shanta Vollman 6500 10-OCT-05
Lisa Ozer 11500 11-MAR-05
Ellen Abel 11000 11-MAY-04
Karen Partners 13500 05-JAN-05
Elizabeth Bates 7300 24-MAR-07
William Smith 7400 23-FEB-07
Nanette Cambrault 7500 09-DEC-06
Sundar Ande 6400 24-MAR-08
Julia Dellinger 3400 24-JUN-06
Kevin Feeney 3000 23-MAY-06
Alexis Bull 4100 20-FEB-05
David Lee 6800 23-FEB-08
65 rows selected.
1220
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
