SQL练习（五）

最新推荐文章于 2019-03-02 13:14:32 发布

zeroo2333

最新推荐文章于 2019-03-02 13:14:32 发布

阅读量554

点赞数

分类专栏： SQL

本文链接：https://blog.csdn.net/qq_19342635/article/details/79886651

版权

SQL 专栏收录该内容

7 篇文章 1 订阅

订阅专栏

http://www.cnblogs.com/huyong/archive/2011/06/27/2090980.html

/*
第五篇
*/
1. 哪些部门的人数比90 号部门的人数多。
select e.department_id from employee e where e.department_id in 
(select department_id from employee group by department_id having count(*)>
(select count(*) from employee where department_id='90'))

2. Den(FIRST_NAME)、Raphaely(LAST_NAME)的领导是谁（非关联子查询）。
select employee_name from employee where employee_id=(select manager_id from employee where first_name='Den' and last_name='Raphaely');

3. Den(FIRST_NAME)、Raphaely(LAST_NAME) 领导谁（非关联子查询）。
select employee_name from employee where manager_id=(select employee_id from employee where first_name='Den' and last_name='Raphaely');

4. Den(FIRST_NAME)、Raphaely(LAST_NAME) 的领导是谁（关联子查询）。
select e1.employee_name from employee e1 where EXISTS
(select 1 from employee e2 where e2.manager_id = e1.employee_id and e2.first_name='Den' and e2.last_name='Raphaely' )

5. Den(FIRST_NAME)、Raphaely(LAST_NAME) 领导谁（关联子查询）。
select e1.employee_name from employee e1 where EXISTS
(select 1 from employee e2 where e2.employee_id = e1.manager_id and e2.first_name='Den' and e2.last_name='Raphaely' )

6. 列出在同一部门共事，入职日期晚但工资高于其他同事的员工：名字、工资、入职日期
（关联子查询）。
select e.ename,e.salery,e.hiredate from employee e where EXISTS
(SELECT 1 FROM employee e1, employee e2 where e1.department_id = e2.department_id and e1.hiredate>e2.hiredate and e1.salery>e2.salery);

7. 哪些员工跟Den(FIRST_NAME)、Raphaely(LAST_NAME)不在同一个部门（非关联子查询）。
select e.* from employee e where  e.department_id != (select e2.department_id from employee e2 where e2.first_name = 'Den' and e2.last_name='Raphaely');

8. 哪些员工跟Den(FIRST_NAME)、Raphaely(LAST_NAME)不在同一个部门（关联子查询）。
select e1.* from employee e1where NOT EXISTS
(select 1 from employee e2 where e1.department_id = e2.department_id and e2.first_name = 'Den' and e2.last_name='Raphaely') ;

9.  Finance部门有哪些职位（非关联子查询）。
select job_id from employee where department_id=(select department_id from department where department_name='Finance');

10. Finance部门有哪些职位（关联子查询）。
select distinct job_id from employee e where EXISTS 
(select 1 from department d d.department_name = 'Finance' and d.department_id = e.department_id);

EXISTS用法：就是判断后面的语句是否存在，如果不存在就啥都不返回。还有NOT EXISTS用法，与之相反。

select 1 的用法就是，如果后面条件都符合则返回1。一般这两个用法都会在一起连用。