[推荐]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′) FROM HR.EMPLOYEES WHERE ROWNUM
输出结果的货币单位是¥和$。
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
—–在没有设置NLS_LANG的情况下:
SQL
> SELECT TO_CHAR(SALARY,’L99,999.99′
)
2 FROM
HR.EMPLOYEES
3 WHERE ROWNUM
;
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
;
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
;
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 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
作者:EricHu(DB、C/S、B/S、WebService、WCF、PM等)
服务器托管,北京服务器托管,服务器租用 http://www.fwqtg.net
机房租用,北京机房租用,IDC机房托管, http://www.fwqtg.net
相关推荐: Springboot3+微服务实战12306高性能售票系统-相逢何必曾相识
download:Springboot3+微服务实战12306高性能售票系统 莫辞更坐弹一曲音乐,古琴,传统文化莫辞更是中国古代文人雅士中的代表人物之一,他是一位爱好古琴的文艺青年。据史料记载,莫辞更常常在宴会上弹奏古琴,给人们带来美妙的音乐享受。其中最有名的…