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

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

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

[推荐]ORACLE SQL

经典查询练手第五篇(不懂装懂,永世饭桶!)

 

——通过知识共享树立个人品牌。

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

 接上四篇:

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

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

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

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

 

本篇数据查询属于复杂业务,难度比较高,请继续努力,通过我为大家设立的这个系列,循序渐进,只要你对每一篇,每一个试题都实践测试,认真练习。我相信你对常用、经典的、复杂的SQL已能熟能生巧,信手拈来! 


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

表名:REGIONS

序号

列名

数据类型

长度

小数位

标识

主键

允许空

默认值

说明

1

REGION_ID

NUMBER

2

REGION_NAME

VARCHAR2

25

 表名:COUNTRIES

序号

列名

数据类型

长度

小数位

标识

主键

允许空

默认值

说明

1

COUNTRY_ID

CHAR

2

2

COUNTRY_NAME

VARCHAR2

40

3

REGION_ID

NUMBER

表名:LOCATIONS

序号

列名

数据类型

长度

小数位

标识

主键

允许空

默认值

说明

1

LOCATION_ID

NUMBER

4

0

2

STREET_ADDRESS

VARCHAR2

40

3

POSTAL_CODE

VARCHAR2

12

4

CITY

VARCHAR2

30

5

STATE_PROVINCE

VARCHAR2

25

6

COUNTRY_ID

CHAR

2

表名:DEPARTMENTS

序号

列名

数据类型

长度

小数位

标识

主键

允许空

默认值

说明

1

DEPARTMENT_ID

NUMBER

4

0

2

DEPARTMENT_NAME

VARCHAR2

30

3

MANAGER_ID

NUMBER

6

0

4

LOCATION_ID

NUMBER

4

0

表名:JOBS

序号

列名

数据类型

长度

小数位

标识

主键

允许空

默认值

说明

1

JOB_ID

VARCHAR2

10

2

JOB_TITLE

VARCHAR2

35

3

MIN_SALARY

NUMBER

6

0

4

MAX_SALARY

NUMBER

6

0

表名:EMPLOYEES

序号

列名

数据类型

长度

小数位

标识

主键

允许空

默认值

说明

1

EMPLOYEE_ID

NUMBER

6

0

2

FIRST_NAME

VARCHAR2

20

3

LAST_NAME

VARCHAR2

25

4

EMAIL

VARCHAR2

25

5

PHONE_NUMBER

VARCHAR2

20

6

HIRE_DATE

DATE

7

7

JOB_ID

VARCHAR2

10

8

SALARY

NUMBER

8

2

9

COMMISSION_PCT

NUMBER

2

2

10

MANAGER_ID

NUMBER

6

0

11

DEPARTMENT_ID

NUMBER

4

0

ER图:

 SQL完成以下问题列表:

1. 哪些部门的人数比90 号部门的人数多。
2. Den(FIRST_NAME)、Raphaely(LAST_NAME)的领导是谁(非关联子查询)。
3. Den(FIRST_NAME)、Raphaely(LAST_NAME) 领导谁(非关联子查询)。
4. Den(FIRST_NAME)、Raphaely(LAST_NAME) 的领导是谁(关联子查询)。
5. Den(FIRST_NAME)、Raphaely(LAST_NAME) 领导谁(关联子查询)。
6. 列出在同一部门共事,入职日期晚但工资高于其他同事的员工:名字、工资、入职日期
(关联子查询)。
7. 哪些员工跟Den(FIRST_NAME)、Raphaely(LAST_NAME)不在同一个部门(非关联子查询)。
8. 哪些员工跟Den(FIRST_NAME)、Raphaely(LAST_NAME)不在同一个部门(关联子查询)。
9.  Finance部门有哪些职位(非关联子查询)。
10. Finance部门有哪些职位(关联子查询)。

各试题解答如下(欢迎大家指出不同的方法或建议!):

/*——–1、哪些部门的人数比90号部门的人数多。———*/SQL

> SELECT DEPARTMENT_ID,COUNT(*FROM EMPLOYEES
2  GROUP BY DEPARTMENT_ID
3  HAVING COUNT(*>
4         (SELECT COUNT(*FROM EMPLOYEES
5          WHERE DEPARTMENT_ID = 90
6         );

DEPARTMENT_ID   COUNT(*)
———– ———-
           30          6
50         45
60          5
80         34
100          6

 

/*——-2、Den(FIRST_NAME)、Raphaely(LAST_NAME)的
领导是谁(非关联子查询)。———
*/SQL

> SELECT FIRST_NAME ||   || LAST_NAME
2  FROM EMPLOYEES
3  WHERE EMPLOYEE_ID =
4        (SELECT MANAGER_ID FROM EMPLOYEES
5         WHERE FIRST_NAME = Den
6         AND   LAST_NAME  = Raphaely
7        );

FIRST_NAME||||LAST_NAME
——————————————–
Steven King

/*——-3、Den(FIRST_NAME)、Raphaely(LAST_NAME)领导谁(非关联子查询)。———*/SQL

> SELECT FIRST_NAME ||   || LAST_NAME
2  FROM EMPLOYEES
3  WHERE MANAGER_ID IN
4        (SELECT EMPLOYEE_ID FROM EMPLOYEES
5         WHERE FIRST_NAME = Den
6         AND   LAST_NAME  = Raphaely
7        );

FIRST_NAME||||LAST_NAME
——————————————–
Alexander Khoo
Shelli Baida
Sigal Tobias
Guy Himuro
Karen Colmenares

或者

SQL
> SELECT FIRST_NAME ||   || LAST_NAME
2  FROM EMPLOYEES
3  WHERE MANAGER_ID =
4        (SELECT EMPLOYEE_ID FROM EMPLOYEES
5         WHERE FIRST_NAME = Den
6         AND   LAST_NAME  = Raphaely
7        );

FIRST_NAME||||LAST_NAME
——————————————–
Alexander Khoo
Shelli Baida
Sigal Tobias
Guy Himuro
Karen Colmenares

/*——-4、Den(FIRST_NAME)、Raphaely(LAST_NAME)的领导是谁(关联子查询)。———*/SQL

> SELECT FIRST_NAME ||   || LAST_NAME
2  FROM EMPLOYEES EMP1
3  WHERE EXISTS (
4        SELECT 1 FROM EMPLOYEES EMP2
5        WHERE FIRST_NAME = Den
6        AND LAST_NAME  = Raphaely
7        AND EMP2.MANAGER_ID = EMP1.EMPLOYEE_ID);

FIRST_NAME||||LAST_NAME
——————————————–
Steven King

/*——-5、Den(FIRST_NAME)、Raphaely(LAST_NAME)领导谁(关联子查询)。———*/SQL

> SELECT FIRST_NAME ||   || LAST_NAME
2  FROM EMPLOYEES EMP1
3  WHERE EXISTS (
4        SELECT 1 FROM EMPLOYEES EMP2
5        WHERE FIRST_NAME = Den
6        AND LAST_NAME  = Raphaely
7        AND EMP2.EMPLOYEE_ID = EMP1.MANAGER_ID); FIRST_NAME

||||LAST_NAME
——————————————–
Alexander Khoo
Shelli Baida
Sigal Tobias
Guy Himuro
Karen Colmenares

/*——-6、列出在同一部门共事,入职日期晚但工资高于其他同事的员工:
名字、工资、入职日期(关联子查询)。———
*/SQL

> SELECT FIRST_NAME ||   || LAST_NAME AS 姓名,
2          SALARY AS 工资,HIRE_DATE AS 入职日期
3  FROM EMPLOYEES EMP1
4  WHERE EXISTS (
5        SELECT 1 FROM EMPLOYEES EMP2
6        WHERE EMP2.DEPARTMENT_ID = EMP1.DEPARTMENT_ID
7        AND   EMP1.HIRE_DATE > EMP2.HIRE_DATE
8        AND   EMP1.SALARY    > EMP2.SALARY
9        );姓名                                                   工资 入职日期

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

61 rows selected

/*——-7、哪些员工跟Den(FIRST_NAME)、Raphaely(LAST_NAME)
不在同一个部门(非关联子查询)。———
*/SQL

> SELECT FIRST_NAME ||   || LAST_NAME
2  FROM EMPLOYEES
3  WHERE DEPARTMENT_ID <>
4        (SELECT DEPARTMENT_ID FROM EMPLOYEES
5         WHERE FIRST_NAME = Den
6         AND LAST_NAME  = Raphaely
7        );

FIRST_NAME||||LAST_NAME
——————————————–
Steven King
Neena Kochhar
Lex De Haan
Alexander Hunold
Bruce Ernst
David Austin
Valli Pataballa
等等

或者

SQL
> SELECT FIRST_NAME ||   || LAST_NAME
2  FROM EMPLOYEES
3  WHERE DEPARTMENT_ID NOT IN
4        (SELECT DEPARTMENT_ID FROM EMPLOYEES
5         WHERE FIRST_NAME = Den
6         AND LAST_NAME  = Raphaely
7        );

/*——-8、哪些员工跟Den(FIRST_NAME)、Raphaely(LAST_NAME)
不在同一个部门(关联子查询)。———
*/SQL

> SELECT FIRST_NAME ||   || LAST_NAME
2  FROM EMPLOYEES EMP1
3  WHERE NOT EXISTS (
4        SELECT 1 FROM EMPLOYEES EMP2
5        WHERE EMP1.DEPARTMENT_ID = EMP2.DEPARTMENT_ID
6        AND EMP2.FIRST_NAME =  Den
7        AND EMP2.LAST_NAME  =  Raphaely);

FIRST_NAME||||LAST_NAME
——————————————–
Kimberely Grant
Lex De Haan
Neena Kochhar
Steven King
Pat Fay
Michael Hartstein
Diana Lorentz
Valli Pataballa
等等

/*——-9、Finance部门有哪些职位(非关联子查询)。———*/SQL

> SELECT DISTINCT JOB_ID FROM EMPLOYEES
2  WHERE DEPARTMENT_ID = (
3        SELECT DEPARTMENT_ID FROM DEPARTMENTS
4        WHERE DEPARTMENT_NAME = Finance);

JOB_ID
——–
FI_ACCOUNT
FI_MGR

或者

SQL
> SELECT DISTINCT JOB_ID FROM EMPLOYEES
2  WHERE DEPARTMENT_ID IN (
3        SELECT DEPARTMENT_ID FROM DEPARTMENTS
4        WHERE DEPARTMENT_NAME = Finance);

JOB_ID
——–
FI_ACCOUNT
FI_MGR

/*——-10、Finance部门有哪些职位(关联子查询)。———*/
SQL
> SELECT DISTINCT JOB_ID FROM EMPLOYEES
2  WHERE EXISTS(
3        SELECT 1 FROM DEPARTMENTS
4        WHERE EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID
5        AND DEPARTMENTS.DEPARTMENT_NAME = Finance);

JOB_ID
——–
FI_ACCOUNT
FI_MGR

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

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

表情

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

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