经典查询练手第三篇

--经典查询练手第三篇

HR@PROD2> desc EMPLOYEES Name       Null?Type ----------------------------------------------------- -------- ------------------------------------ EMPLOYEE_ID       NOT NULL NUMBER(6) FIRST_NAMEVARCHAR2(20) LAST_NAME       NOT NULL VARCHAR2(25) EMAIL       NOT NULL VARCHAR2(25) PHONE_NUMBERVARCHAR2(20) HIRE_DATE       NOT NULL DATE JOB_ID        NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_IDNUMBER(6) DEPARTMENT_IDNUMBER(4)HR@PROD2> desc DEPARTMENTS  Name       Null?Type ----------------------------------------------------- -------- ------------------------------------ DEPARTMENT_ID       NOT NULL NUMBER(4) DEPARTMENT_NAME       NOT NULL VARCHAR2(30) MANAGER_IDNUMBER(6) LOCATION_IDNUMBER(4)HR@PROD2> DESC REGIONS Name       Null?Type ----------------------------------------------------- -------- ------------------------------------ REGION_ID       NOT NULL NUMBER REGION_NAMEVARCHAR2(25)HR@PROD2> desc locations Name       Null?Type ----------------------------------------------------- -------- ------------------------------------ LOCATION_ID       NOT NULL NUMBER(4) STREET_ADDRESS VARCHAR2(40) POSTAL_CODEVARCHAR2(12) CITY       NOT NULL VARCHAR2(30) STATE_PROVINCE VARCHAR2(25) COUNTRY_IDCHAR(2) HR@PROD2> desc jobs Name       Null?Type ----------------------------------------------------- -------- ------------------------------------ JOB_ID        NOT NULL VARCHAR2(10) JOB_TITLE       NOT NULL VARCHAR2(35) MIN_SALARYNUMBER(6) MAX_SALARYNUMBER(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.00RMB13,000.002. 列出前五位每个员工的名字，工资、涨薪后的的工资（涨幅为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   2808Douglas Grant     2600   2808Jennifer Whalen      4400   4752Michael Hartstein    13000  140403. 找出谁是最高领导，将名字按大写形式显示。HR@PROD2> select upper(first_name||' '||last_name) name from employees where manager_id is null;NAME----------------------------------------------STEVEN KING4. 找出First_Name 为David，Last_Name 为Austin 的直接领导名字。HR@PROD2> select a.first_name||' '||a.last_name namefrom 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 Hunold5. First_Name 为Alexander，Last_Name 为Hunold 领导谁。（谁向David 报告）。HR@PROD2> select b.first_name||' '||b.last_name namefrom 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 ErnstDavid AustinValli PataballaDiana Lorentz6. 哪些员工的工资高于他直接上司的工资，列出员工的名字和工资，上司的名字和工资。HR@PROD2> select b.first_name||' '||b.last_name name,b.salary,a.first_name||' '||a.last_name name,a.salaryfrom 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      11000Ellen Abel    11000 Eleni Zlotkey       105007. 哪些员工和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 GreenbergDaniel FavietIsmael SciarraJose Manuel UrmanLuis Popp6 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 Kochhar9. 哪些员工跟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 AtkinsonDavid AustinHermann BaerShelli BaidaSarah BellLaura BissotAlexis BullAnthony CabrioJohn ChenKelly ChungKaren ColmenaresCurtis DaviesLex De HaanJulia DellingerJennifer DillyBruce ErnstBritney EverettDaniel FavietPat FayKevin FeeneyJean FleaurAdam FrippTimothy GatesKi GeeGirard GeoniWilliam GietzDouglas GrantNancy GreenbergMichael HartsteinShelley HigginsGuy HimuroAlexander HunoldVance JonesPayam KauflingAlexander KhooSteven KingNeena KochharRenske LadwigJames LandryDiana LorentzJason MallinSteven MarkleJames MarlowRandall MatosSusan MavrisSamuel McCainIrene MikkilineniKevin MourgosJulia NayerDonald OConnellTJ OlsonValli PataballaJoshua PatelRandall PerkinsHazel PhiltankerLuis PoppTrenna RajsDen RaphaelyMichael RogersNandita SarchandIsmael SciarraJohn SeoStephen StilesMartha SullivanWinston TaylorSigal TobiasJose Manuel UrmanPeter VargasShanta VollmanAlana WalshMatthew WeissJennifer Whalen72 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 HaanNeena KochharKaren ColmenaresGuy HimuroSigal TobiasShelli BaidaAlexander KhooPat FayMichael HartsteinEleni ZlotkeyGerald CambraultAlberto ErrazurizKaren PartnersJohn RussellKevin MourgosShanta VollmanPayam KauflingAdam FrippMatthew WeissSteven KingWilliam GietzShelley HigginsPeter VargasRandall MatosCurtis DaviesTrenna RajsJoshua PatelJohn SeoStephen StilesRenske LadwigHazel PhiltankerKi GeeMichael RogersJason MallinTJ OlsonJames MarlowMozhe AtkinsonLaura BissotSteven MarkleJames LandryIrene MikkilineniJulia NayerSusan MavrisHermann BaerKevin FeeneyAlana WalshVance JonesSamuel McCainBritney EverettSarah BellRandall PerkinsTimothy GatesJennifer DillyKelly ChungAnthony CabrioJulia DellingerAlexis BullNandita SarchandGirard GeoniMartha SullivanJean FleaurWinston TaylorDouglas GrantDonald OConnellDen RaphaelyJennifer WhalenNancy GreenbergLuis PoppJose Manuel UrmanIsmael SciarraJohn ChenDaniel FavietDiana LorentzValli PataballaDavid AustinBruce ErnstAlexander Hunold77 rows selected.11. 显示有提成的员工的信息：名字、提成、所在部门名称、所在地区的名称。HR@PROD2> select e.first_name||' '||e.last_name name,e.commission_pct,d.department_name,l.cityfrom 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     OxfordKaren Partners   .3 Sales     OxfordAlberto Errazuriz   .3 Sales     OxfordGerald Cambrault   .3 Sales     OxfordEleni Zlotkey   .2 Sales     OxfordPeter Tucker   .3 Sales     OxfordDavid Bernstein   .25 Sales     OxfordPeter Hall      .25 Sales     OxfordChristopher Olsen   .2 Sales     OxfordNanette Cambrault   .2 Sales     OxfordOliver Tuvault  .15 Sales     OxfordJanette King  .35 Sales     OxfordPatrick Sully  .35 Sales     OxfordAllan McEwen  .35 Sales     OxfordLindsey Smith   .3 Sales     OxfordLouise Doran   .3 Sales     OxfordSarath Sewall  .25 Sales     OxfordClara Vishney  .25 Sales     OxfordDanielle Greene   .15 Sales     OxfordMattea Marvins   .1 Sales     OxfordDavid Lee   .1 Sales     OxfordSundar Ande   .1 Sales     OxfordAmit Banda   .1 Sales     OxfordLisa Ozer  .25 Sales     OxfordHarrison Bloom   .2 Sales     OxfordTayler Fox   .2 Sales     OxfordWilliam Smith  .15 Sales     OxfordElizabeth Bates   .15 Sales     OxfordSundita Kumar   .1 Sales     OxfordEllen Abel   .3 Sales     OxfordAlyssa Hutton  .25 Sales     OxfordJonathon Taylor    .2 Sales     OxfordJack Livingston    .2 Sales     OxfordCharles Johnson    .1 Sales     Oxford34 rows selected.12. 显示Executive 部门有哪些职位。HR@PROD2> select distinct j.job_titlefrom jobs j,departments d,employees ewhere e.department_id=d.department_id and e.job_id=j.job_id and d.department_name='Executive'; JOB_TITLE-----------------------------------Administration Vice PresidentPresident13. 整个公司中，最高工资和最低工资相差多少。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(*)----------3515. 显示整个公司的最高工资、最低工资、工资总和、平均工资保留到整数位。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_datefrom employees a,employees bwhere  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-07Kelly Chung     3800 14-JUN-05Irene Mikkilineni     2700 28-SEP-06Sarah Bell     4000 04-FEB-04Alberto Errazuriz    12000 10-MAR-05Harrison Bloom    10000 23-MAR-06Danielle Greene      9500 19-MAR-07Alexander Hunold     9000 03-JAN-06Nancy Greenberg     12008 17-AUG-02Randall Perkins      2500 19-DEC-07Winston Taylor     3200 24-JAN-06Nandita Sarchand     4200 27-JAN-04Clara Vishney    10500 11-NOV-05Eleni Zlotkey    10500 29-JAN-08Tayler Fox     9600 24-JAN-06Mattea Marvins     7200 24-JAN-08Alana Walsh     3100 24-APR-06Michael Rogers     2900 26-AUG-06Randall Matos     2600 15-MAR-06Ki Gee     2400 12-DEC-07Gerald Cambrault    11000 15-OCT-07David Bernstein      9500 24-MAR-05Peter Hall     9000 20-AUG-05Kevin Mourgos     5800 16-NOV-07Steven King    24000 17-JUN-03Douglas Grant     2600 13-JAN-08Jean Fleaur     3100 23-FEB-06Mozhe Atkinson     2800 30-OCT-05Jonathon Taylor      8600 24-MAR-06Christopher Olsen     8000 30-MAR-06Girard Geoni     2800 03-FEB-08Bruce Ernst     6000 21-MAY-07Jose Manuel Urman     7800 07-MAR-06Adam Fripp     8200 10-APR-05Jennifer Dilly     3600 13-AUG-05Vance Jones     2800 17-MAR-07Anthony Cabrio     3000 07-FEB-07Samuel McCain     3200 01-JUL-06Timothy Gates     2900 11-JUL-06Julia Nayer     3200 16-JUL-05Martha Sullivan      2500 21-JUN-07Britney Everett      3900 03-MAR-05John Seo     2700 12-FEB-06Stephen Stiles     3200 26-OCT-05Hazel Philtanker     2200 06-FEB-08Steven Markle     2200 08-MAR-08Jack Livingston      8400 23-APR-06John Russell    14000 01-OCT-04Peter Tucker    10000 30-JAN-05Alyssa Hutton     8800 19-MAR-05Shelli Baida     2900 24-DEC-05Matthew Weiss     8000 18-JUL-04Laura Bissot     3300 20-AUG-05Shanta Vollman     6500 10-OCT-05Lisa Ozer    11500 11-MAR-05Ellen Abel    11000 11-MAY-04Karen Partners    13500 05-JAN-05Elizabeth Bates      7300 24-MAR-07William Smith     7400 23-FEB-07Nanette Cambrault     7500 09-DEC-06Sundar Ande     6400 24-MAR-08Julia Dellinger      3400 24-JUN-06Kevin Feeney     3000 23-MAY-06Alexis Bull     4100 20-FEB-05David Lee     6800 23-FEB-0865 rows selected.