但行好事
莫论前程❤

Oracle数据库sql语句练习(3)

本篇相对上两篇来说难度有所增加,继续努力

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

HR.EMPLOYEES员工表结构如下:

img

HR.DEPARTMENTS表结构如下:

img

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') 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、改变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
--...初始有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 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

61 rows selected
赞(0) 打赏
未经允许不得转载:刘鹏博客 » Oracle数据库sql语句练习(3)
分享到: 更多 (0)

评论 抢沙发

评论前必须登录!

 

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏