不说废话,大牛初始亦菜鸟,成功之路贵执行,马上行动!如果想获得更优质的显示效果,请在Chrome、Firefox等现代浏览器浏览本站。

[推荐]ORACLE SQL:经典查询练手第三篇(不懂装懂,永世饭桶!)

:: 数据库精典 EricHu 718℃ 0评论

[推荐]ORACLE SQL经典查询练手第三篇(不懂装懂,永世饭桶!)

 本文与大家共同讨论与分享ORACLE SQL的一些常用经典查询,欢迎大家补充,同时你认为有那些经典的也可分享出来。在本文中,对每一个问题,你要是认为有什么更好的解决方法也欢迎你及时提出。交流与分享才能共同进步嘛,感谢!

 接上两篇:

[推荐]ORACLE SQL:经典查询练手第一篇

[推荐]ORACLE SQL:经典查询练手第二篇

        本篇相对上两篇来说难度有所增加,继续努力,通过我为大家设立的这个系列,只要你对每一篇,每一个试题都实践测试,认真练习。我相信你对常用、经典的SQL已能熟能生巧,信手拈来!

本文使用ORACLE自带的人力资源(HR)实例数据,本文所用表结构如下:

HR.EMPLOYEES员工表结构如下:

HR.DEPARTMENTS表结构如下:

HR.REGIONS表结构如下:

SQL> DESC HR.REGIONS;
Name        Type         Nullable Default Comments
———– ———— ——– ——- ——–
REGION_ID   NUMBER
REGION_NAME VARCHAR2(25) Y

 

用SQL完成以下问题列表:

1. 让SELECT TO_CHAR(SALARY,’L99,999.99′) FROMEMPLOYEES WHEREROWNUM < 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、改变NLS_LANG 的值,让SELECT TO_CHAR(SALARY,’L99,999.99′) FROM HR.EMPLOYEES WHERE ROWNUM < 5 输出结果的货币单位是¥和$。———*/
—–在没有设置NLS_LANG的情况下:

SQL> SELECT TO_CHAR(SALARY,’L99,999.99′)
2  FROM HR.EMPLOYEES
3  WHERE ROWNUM < 5;

TO_CHAR(SALARY,’L99,999.99′)
—————————-
24,000.00
20,000.00
20,000.00
9,000.00

SQL> SELECT TO_CHAR(SALARY,’$99,999.99′)
2  FROM HR.EMPLOYEES
3  WHERE ROWNUM < 5;

TO_CHAR(SALARY,’$99,999.99′)
—————————-
$24,000.00
$20,000.00
$20,000.00
$9,000.00

/*–说明:对于’$99,999.99’格式符:
L:表示强制显示当地货币符号
$: 表示显示美元符号
9: 表示一个数字
0: 表示强制0显示
.: 表示一个小数点
,: 表示一个千位分隔符
————–*/

/*——–2、列出前五位每个员工的名字,工资、涨薪后的的工资(涨幅为8%),以“元”为单位进行四舍五入。———*/

SQL> SELECT FIRST_NAME,SALARY,ROUND(SALARY * 1.08) FROM HR.EMPLOYEES
2  WHERE ROWNUM <=5;

FIRST_NAME               SALARY ROUND(SALARY*1.08)
——————– ———- ——————
Steven                 24000.00              25920
Neena                  20000.00              21600
Lex                    20000.00              21600
Alexander               9000.00               9720
Bruce                   6000.00               6480

/*——–3、找出谁是最高领导,将名字按大写形式显示。———*/
SQL> SELECT UPPER(FIRST_NAME || ‘ ‘ || LAST_NAME) AS NAME
2  FROM HR.EMPLOYEES
3  WHERE MANAGER_ID IS NULL;

NAME
———————————————-
STEVEN KING

/*——–4、找出David 的直接领导的名字。———*/
SQL> SELECT UPPER(FIRST_NAME ||’ ‘ || LAST_NAME) AS NAME
2  FROM HR.EMPLOYEES
3  WHERE EMPLOYEE_ID IN(
4  SELECT MANAGER_ID FROM HR.EMPLOYEES
5  WHERE FIRST_NAME = ‘David’ AND LAST_NAME = ‘Austin’);

NAME
———————————————-
ALEXANDER HUNOLD

–或采用以下方法

SQL> SELECT UPPER( EMP1.FIRST_NAME ||’ ‘ ||  EMP1.LAST_NAME) AS NAME
2  FROM HR.EMPLOYEES EMP1,HR.EMPLOYEES EMP2
3  WHERE EMP1.EMPLOYEE_ID = EMP2.MANAGER_ID
4  AND EMP2.FIRST_NAME = ‘David’ AND EMP2.LAST_NAME =  ‘Austin';

NAME
———————————————-
ALEXANDER HUNOLD

/*——–5、First_Name 为Alexander,LAST_NAME为Hunold领导谁。(谁向David 报告)。———*/
SQL> SELECT UPPER(FIRST_NAME ||’ ‘ || LAST_NAME) AS NAME
2  FROM HR.EMPLOYEES
3  WHERE MANAGER_ID IN(
4  SELECT EMPLOYEE_ID FROM HR.EMPLOYEES
5  WHERE FIRST_NAME = ‘Alexander’ AND LAST_NAME = ‘Hunold’);

NAME
———————————————-
BRUCE ERNST
DAVID AUSTIN
VALLI PATABALLA
DIANA LORENTZ

–或采用以下方法

SQL> SELECT UPPER( EMP1.FIRST_NAME || ‘ ‘ ||  EMP1.LAST_NAME) AS NAME
2  FROM HR.EMPLOYEES EMP1,HR.EMPLOYEES EMP2
3  WHERE EMP1.MANAGER_ID = EMP2.EMPLOYEE_ID
4  AND EMP2.FIRST_NAME = ‘Alexander’ AND EMP2.LAST_NAME =  ‘Hunold';

NAME
———————————————-
BRUCE ERNST
DAVID AUSTIN
VALLI PATABALLA
DIANA LORENTZ

/*——–6、哪些员工的工资高于他直接上司的工资,列出员工的名字和工资,上司的名字和工资。———*/

SQL> SELECT E.FIRST_NAME,E.SALARY,M.FIRST_NAME,M.SALARY
2  FROM EMPLOYEES E,EMPLOYEES M
3  WHERE E.MANAGER_ID = M.EMPLOYEE_ID AND E.SALARY > M.SALARY;

FIRST_NAME               SALARY FIRST_NAME               SALARY
——————– ———- ——————– ———-
Lisa                   11500.00 Gerald                 11000.00
Ellen                  11000.00 Eleni                  10500.00

–要是只列出员工的名字与工资的话,还可以这样:

SQL> SELECT E.FIRST_NAME,E.SALARY
2  FROM EMPLOYEES E WHERE E.SALARY >
3 (SELECT M.SALARY FROM EMPLOYEES M
4  WHERE E.MANAGER_ID = M.EMPLOYEE_ID);

FIRST_NAME               SALARY
——————– ———-
Lisa                   11500.00
Ellen                  11000.00

/*——–7、哪些员工和Chen(LAST_NAME)同部门。———*/

SQL> SELECT FIRST_NAME FROM EMPLOYEES
2  WHERE DEPARTMENT_ID IN
3  (SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE LAST_NAME = ‘Chen’)
4  AND LAST_NAME <> ‘Chen';

FIRST_NAME
——————–
Nancy
Daniel
Ismael
Jose Manuel
Luis
–或者–

SQL> SELECT E1.FIRST_NAME FROM EMPLOYEES E1,EMPLOYEES E2
2  WHERE E1.DEPARTMENT_ID = E2.DEPARTMENT_ID
3  AND E2.LAST_NAME = ‘Chen’ AND E1.LAST_NAME <> ‘Chen';

FIRST_NAME
——————–
Nancy
Daniel
Ismael
Jose Manuel
Luis

/*——–8、哪些员工跟De Haan(LAST_NAME)做一样职位。———*/
SQL> SELECT FIRST_NAME FROM EMPLOYEES
2  WHERE JOB_ID IN
3  (SELECT JOB_ID FROM EMPLOYEES
4  WHERE LAST_NAME = ‘De Haan’)
5  AND LAST_NAME <> ‘De Haan';

FIRST_NAME
——————–
Neena

–或者–

SQL> SELECT E1.FIRST_NAME FROM EMPLOYEES E1,EMPLOYEES E2
2  WHERE E1.JOB_ID = E2.JOB_ID
3  AND E2.LAST_NAME = ‘De Haan’ AND E1.LAST_NAME <> ‘De Haan';

FIRST_NAME
——————–
Neena

/*——–9、哪些员工跟Hall(LAST_NAME)不在同一个部门。———*/

SQL> SELECT FIRST_NAME || ‘ ‘ || LAST_NAME FROM HR.EMPLOYEES
2  WHERE DEPARTMENT_ID NOT IN(
3  SELECT DEPARTMENT_ID FROM HR.EMPLOYEES
4  WHERE LAST_NAME = ‘Hall’);

FIRST_NAME||”||LAST_NAME
———————————————-
Steven King
Neena Kochhar
Lex De Haan
Alexander Hunold
Bruce Ernst
David Austin
Valli Pataballa
Diana Lorentz
Nancy Greenberg
–…初始有72条数据

–或者:

SQL> SELECT e1.FIRST_NAME FROM EMPLOYEES e1,EMPLOYEES e2
2  WHERE e1.DEPARTMENT_ID = e2.DEPARTMENT_ID(+)
3  and e2.LAST_NAME(+) = ‘Hall’
4  and e2.LAST_NAME IS NULL;

/*——-10、哪些员工跟William(FIRST_NAME)、Smith(LAST_NAME)做不一样的职位。——–*/

SQL> SELECT FIRST_NAME || ‘ ‘ || LAST_NAME FROM HR.EMPLOYEES
2  WHERE JOB_ID <> (SELECT DISTINCT JOB_ID FROM EMPLOYEES
3  WHERE FIRST_NAME = ‘William’ AND LAST_NAME = ‘Smith’);

FIRST_NAME||”||LAST_NAME
———————————————-
Steven King
Neena Kochhar
Lex De Haan
Alexander Hunold
—-…初始有77条数据

 

 

 

/*——–11、显示有提成的员工的信息:名字、提成、所在部门名称、所在地区的名称。———*/

SQL> SELECT E.FIRST_NAME || ‘ ‘ || E.LAST_NAME AS NAME,
2  E.COMMISSION_PCT,D.DEPARTMENT_NAME,L.CITY
3  FROM HR.EMPLOYEES E,HR.DEPARTMENTS D,HR.LOCATIONS L
4  WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
5  AND D.LOCATION_ID = L.LOCATION_ID
6  AND E.COMMISSION_PCT IS NOT NULL;

/*——–12、显示Executive部门有哪些职位。———*/
SQL> SELECT DISTINCT E.JOB_ID FROM HR.EMPLOYEES E,HR.DEPARTMENTS D
2  WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID
3  AND D.DEPARTMENT_NAME = ‘Executive';

JOB_ID
———-
AD_PRES
AD_VP

/*——–13、整个公司中,最高工资和最低工资相差多少。———*/
SQL> SELECT MAX(SALARY) – MIN(SALARY) FROM HR.EMPLOYEES;

MAX(SALARY)-MIN(SALARY)
———————–
21900

/*——–14、提成大于0 的人数。———*/
SQL> SELECT COUNT(*) AS 提成大小0的人数 FROM HR.EMPLOYEES
2  WHERE COMMISSION_PCT > 0;

提成大小0的人数
—————
35
–或者

SQL> SELECT COUNT(COMMISSION_PCT) AS 提成大小0的人数
2   FROM HR.EMPLOYEES
3   WHERE COMMISSION_PCT > 0;
提成大小0的人数
—————
35

/*——–15、显示整个公司的最高工资、最低工资、工资总和、平均工资保留到整数位。———*/

SQL> SELECT MAX(NVL(SALARY,0)) AS 最高工资,
2           MIN(NVL(SALARY,0)) AS 最低工资,
3           SUM(NVL(SALARY,0)) AS 工资总和,
4           ROUND(AVG(NVL(SALARY,0))) AS 平均工资
5  FROM HR.EMPLOYEES;

最高工资       最低工资       工资总和       平均工资
———-    ———-      ———-     ———
24000          2100           698011         6523

/*——–16、整个公司有多少个领导。———*/
SQL> SELECT COUNT(DISTINCT(MANAGER_ID))  FROM HR.EMPLOYEES
2  WHERE MANAGER_ID IS NOT NULL;

COUNT(DISTINCT(MANAGER_ID))
—————————
18

/*——–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 1996101
Karen Partners                                   13500.00 199715
Alberto Errazuriz                                12000.00 1997310
Nancy Greenberg                                  12000.00 1994817
Lisa Ozer                                        11500.00 1997311
Ellen Abel                                       11000.00 1996511
Gerald Cambrault                                 11000.00 19991015
Clara Vishney                                    10500.00 19971111
Eleni Zlotkey                                    10500.00 2000129
Harrison Bloom                                   10000.00 1998323
Peter Tucker                                     10000.00 1997130
Tayler Fox                                        9600.00 1998124
Danielle Greene                                   9500.00 1999319
David Bernstein                                   9500.00 1997324
Peter Hall                                        9000.00 1997820
Alyssa Hutton                                     8800.00 1997319
Jonathon Taylor                                   8600.00 1998324
Adam Fripp                                        8200.00 1997410
Christopher Olsen                                 8000.00 1998330
Jack Livingston                                   8000.00 1998423
Matthew Weiss                                     8000.00 1996718
Jose Manuel Urman                                 7800.00 199837
Nanette Cambrault                                 7500.00 1998129
William Smith                                     7400.00 1999223
Elizabeth Bates                                   7300.00 1999324
Charles Johnson                                   7211.00 200014
Mattea Marvins                                    7200.00 2000124
Shanta Vollman                                    6500.00 19971010
Kevin Mourgos                                     5800.00 19991116
Nandita Sarchand                                  4200.00 1996127
Alexis Bull                                       4100.00 1997220
Sarah Bell                                        4000.00 199624
Britney Everett                                   3900.00 199733
Kelly Chung                                       3800.00 1997614
Jennifer Dilly                                    3600.00 1997813
Julia Dellinger                                   3400.00 1998624
Laura Bissot                                      3300.00 1997820
Julia Nayer                                       3200.00 1997716
Samuel McCain                                     3200.00 199871
Stephen Stiles                                    3200.00 19971026
Winston Taylor                                    3200.00 1998124
Alana Walsh                                       3100.00 1998424
Jean Fleaur                                       3100.00 1998223
Anthony Cabrio                                    3000.00 199927
Kevin Feeney                                      3000.00 1998523
Michael Rogers                                    2900.00 1998826
Shelli Baida                                      2900.00 19971224
Timothy Gates                                     2900.00 1998711
Girard Geoni                                      2800.00 200023
Mozhe Atkinson                                    2800.00 19971030
Vance Jones                                       2800.00 1999317
Irene Mikkilineni                                 2700.00 1998928
John Seo                                          2700.00 1998212
Donald OConnell                                   2600.00 1999621
Douglas Grant                                     2600.00 2000113
Randall Matos                                     2600.00 1998315
Martha Sullivan                                   2500.00 1999621
Randall Perkins                                   2500.00 19991219
Ki Gee                                            2400.00 19991212
Hazel Philtanker                                  2200.00 200026
Steven Markle                                     2200.00 200038

61 rows selected

 

转载请注明:RDIFramework.NET » [推荐]ORACLE SQL:经典查询练手第三篇(不懂装懂,永世饭桶!)

喜欢 (0)or分享 (0)
发表我的评论
取消评论

表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址