Oracle SQL：经典查询练手第五篇
略复杂业务 熟能生巧 信手拈来
使用hr 表还是上篇那些表 不再复述
任务:
- 哪些部门的人数比90 号部门的人数多。
- Den(FIRST_NAME)、Raphaely(LAST_NAME)的领导是谁(非关联子查询)。
- Den(FIRST_NAME)、Raphaely(LAST_NAME) 领导谁(非关联子查询)。
- Den(FIRST_NAME)、Raphaely(LAST_NAME) 的领导是谁(关联子查询)。
- Den(FIRST_NAME)、Raphaely(LAST_NAME) 领导谁(关联子查询)。
- 列出在同一部门共事，入职日期晚但工资高于其他同事的员工：名字、工资、入职日期(关联子查询)。
- 哪些员工跟Den(FIRST_NAME)、Raphaely(LAST_NAME)不在同一个部门(非关联子查询)。
- 哪些员工跟Den(FIRST_NAME)、Raphaely(LAST_NAME)不在同一个部门(关联子查询)。
- Finance部门有哪些职位(非关联子查询)。
- Finance部门有哪些职位(关联子查询)
我的解答:
1 --1.哪些部门的人数比90 号部门的人数多
2 -- 先算90号部门人数
3 SELECT COUNT(1) FROM EMPLOYEES e WHERE e.DEPARTMENT_ID=90;
4 -- 再算每个部门的人数
5 SELECT COUNT(1),ee.DEPARTMENT_ID FROM EMPLOYEES ee GROUP BY ee.DEPARTMENT_ID;
6 --大于
7 SELECT COUNT(1),ee.DEPARTMENT_ID FROM EMPLOYEES ee GROUP BY ee.DEPARTMENT_ID HAVING COUNT(1)>(SELECT COUNT(1) FROM EMPLOYEES e WHERE e.DEPARTMENT_ID=90) ORDER BY ee.DEPARTMENT_ID;
1 --2.Den(FIRST_NAME)、Raphaely(LAST_NAME)的领导是谁(非关联子查询)。
2 -- 查询出Den(FIRST_NAME)、Raphaely(LAST_NAME)的领导
3 SELECT e.MANAGER_ID FROM EMPLOYEES e WHERE e.FIRST_NAME='Den' AND e.LAST_NAME='Raphaely';
4 -- 关联employee_id和manager_id
5 -- 人
6 SELECT * FROM EMPLOYEES ee WHERE ee.EMPLOYEE_ID=(SELECT e.MANAGER_ID FROM EMPLOYEES e WHERE e.FIRST_NAME='Den' AND e.LAST_NAME='Raphaely');
1 --3.Den(FIRST_NAME)、Raphaely(LAST_NAME) 领导谁(非关联子查询)
2 -- 查出这个人的employee_id
3 SELECT e.EMPLOYEE_ID FROM EMPLOYEES e WHERE e.FIRST_NAME='Den' AND e.LAST_NAME='Raphaely';
4 -- 领导是这个employee_id
5 SELECT ee.FIRST_NAME||' '||ee.LAST_NAME FROM EMPLOYEES ee WHERE ee.MANAGER_ID=(SELECT e.EMPLOYEE_ID FROM EMPLOYEES e WHERE e.FIRST_NAME='Den' AND e.LAST_NAME='Raphaely');
--4.Den(FIRST_NAME)、Raphaely(LAST_NAME) 的领导是谁(关联子查询)
-- 不知道关联子查询 还需要查阅资料
SELECT e.FIRST_NAME||' '||e.LAST_NAME FROM EMPLOYEES e WHERE EXISTS (SELECT 1 FROM EMPLOYEES ee WHERE ee.FIRST_NAME='Den' AND ee.LAST_NAME='Raphaely' AND ee.MANAGER_ID=e.EMPLOYEE_ID);
--5.Den(FIRST_NAME)、Raphaely(LAST_NAME) 领导谁(关联子查询)
SELECT e.FIRST_NAME||' '||e.LAST_NAME FROM EMPLOYEES e WHERE EXISTS (SELECT 1 FROM EMPLOYEES ee WHERE ee.FIRST_NAME='Den' AND ee.LAST_NAME='Raphaely' AND ee.EMPLOYEE_ID=e.MANAGER_ID);
--6.列出在同一部门共事，入职日期晚但工资高于其他同事的员工：名字、工资、入职日期(关联子查询)。
SELECT e.FIRST_NAME||' '||e.LAST_NAME 姓名,e.SALARY 工资,e.HIRE_DATE 入职日期 FROM EMPLOYEES e WHERE EXISTS(
SELECT 1 FROM EMPLOYEES ee WHERE e.HIRE_DATE>ee.HIRE_DATE AND e.SALARY>ee.SALARY AND e.DEPARTMENT_ID=ee.DEPARTMENT_ID
);
-- 没有思路 不明白
姓名 工资 入职日期
---------------------------------------------- ---------- ---------
Girard Geoni 2800 03-2月 -08
Kevin Mourgos 5800 16-11月-07
Steven King 24000 17-6月 -03
Bruce Ernst 6000 21-5月 -07
Alexander Hunold 9000 03-1月 -06
Nancy Greenberg 12008 17-8月 -02
Jose Manuel Urman 7800 07-3月 -06
Shelli Baida 2900 24-12月-05
Adam Fripp 8200 10-4月 -05
Matthew Weiss 8000 18-7月 -04
Jennifer Dilly 3600 13-8月 -05
Julia Dellinger 3400 24-6月 -06
Laura Bissot 3300 20-8月 -05
Shanta Vollman 6500 10-10月-05
Vance Jones 2800 17-3月 -07
Anthony Cabrio 3000 07-2月 -07
Randall Perkins 2500 19-12月-07
Martha Sullivan 2500 21-6月 -07
Douglas Grant 2600 13-1月 -08
Donald OConnell 2600 21-6月 -07
Kevin Feeney 3000 23-5月 -06
Alana Walsh 3100 24-4月 -06
Samuel McCain 3200 01-7月 -06
Timothy Gates 2900 11-7月 -06
Jean Fleaur 3100 23-2月 -06
Winston Taylor 3200 24-1月 -06
Michael Rogers 2900 26-8月 -06
Britney Everett 3900 03-3月 -05
Kelly Chung 3800 14-6月 -05
Alexis Bull 4100 20-2月 -05
Randall Matos 2600 15-3月 -06
John Seo 2700 12-2月 -06
Stephen Stiles 3200 26-10月-05
Mozhe Atkinson 2800 30-10月-05
Irene Mikkilineni 2700 28-9月 -06
Julia Nayer 3200 16-7月 -05
Hazel Philtanker 2200 06-2月 -08
Ki Gee 2400 12-12月-07
Steven Markle 2200 08-3月 -08
Sarah Bell 4000 04-2月 -04
Nandita Sarchand 4200 27-1月 -04
Lisa Ozer 11500 11-3月 -05
Clara Vishney 10500 11-11月-05
Eleni Zlotkey 10500 29-1月 -08
Gerald Cambrault 11000 15-10月-07
Alberto Errazuriz 12000 10-3月 -05
Tayler Fox 9600 24-1月 -06
Harrison Bloom 10000 23-3月 -06
Danielle Greene 9500 19-3月 -07
Jack Livingston 8400 23-4月 -06
Mattea Marvins 7200 24-1月 -08
Ellen Abel 11000 11-5月 -04
Karen Partners 13500 05-1月 -05
John Russell 14000 01-10月-04
Peter Tucker 10000 30-1月 -05
David Bernstein 9500 24-3月 -05
Jonathon Taylor 8600 24-3月 -06
Alyssa Hutton 8800 19-3月 -05
Peter Hall 9000 20-8月 -05
Christopher Olsen 8000 30-3月 -06
Elizabeth Bates 7300 24-3月 -07
William Smith 7400 23-2月 -07
Nanette Cambrault 7500 09-12月-06
Sundar Ande 6400 24-3月 -08
David Lee 6800 23-2月 -08
选定了 65 行
--7.哪些员工跟Den(FIRST_NAME)、Raphaely(LAST_NAME)不在同一个部门(非关联子查询)
SELECT e.FIRST_NAME||' '||e.LAST_NAME FROM EMPLOYEES e WHERE e.DEPARTMENT_ID NOT in (SELECT ee.DEPARTMENT_ID FROM EMPLOYEES ee WHERE ee.FIRST_NAME='Den' AND ee.LAST_NAME='Raphaely');
...
--8.哪些员工跟Den(FIRST_NAME)、Raphaely(LAST_NAME)不在同一个部门(关联子查询)
SELECT e.FIRST_NAME||' '||e.LAST_NAME 姓名 FROM EMPLOYEES e
WHERE NOT EXISTS (SELECT 1 FROM EMPLOYEES ee WHERE e.DEPARTMENT_ID=ee.DEPARTMENT_ID AND ee.FIRST_NAME='Den' AND ee.LAST_NAME='Raphaely');
...
--9.Finance部门有哪些职位(非关联子查询)
SELECT DISTINCT(e.JOB_ID) FROM EMPLOYEES e WHERE e.DEPARTMENT_ID IN (SELECT d.DEPARTMENT_ID FROM DEPARTMENTS d WHERE d.DEPARTMENT_NAME='Finance');
--10.Finance部门有哪些职位(关联子查询)。
-- 注意部门id相等条件 之前没有加 结果就不对
SELECT DISTINCT(e.JOB_ID) FROM EMPLOYEES e WHERE EXISTS (SELECT 1 FROM DEPARTMENTS d WHERE d.DEPARTMENT_NAME='Finance' AND e.DEPARTMENT_ID=d.DEPARTMENT_ID);
原作者:http://database.51cto.com/art/201106/271577.htm#topx
