首页 > 其他分享 >9. 子查询练习题

9. 子查询练习题

时间：2024-05-28 15:57:23浏览次数：32  
标签：练习题 salary name employees 查询 department id select

1.查询和Zlotkey相同部门的员工姓名和工资

select first_name , last_name , salary 
from employees e 
where department_id = (select department_id
						from employees e2
						where last_name = 'Zlotkey'
						);

2.查询工资比公司平均工资高的员工的员工号，姓名和工资。

select employee_id , first_name , last_name , salary 
from employees e 
where salary > (select avg(salary)
				from employees e2
				);

3.选择工资大于所有JOB_ID='SA_MAN'的员工的工资的员工的last_name， job_id, salary

select last_name , job_id , salary 
from employees e 
where salary > all (select salary
					from employees e2
					where job_id = 'SA_MAN'
					);

4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓

select employee_id , last_name 
from employees e 
where department_id in (select department_id
						from employees e2
						where last_name like '%u%'
						);

5.查询在部门的location_id为1700的部门工作的员工的员工号

select e.employee_id 
from employees e join departments d 
on e.department_id = d.department_id 
where d.location_id = 1700;

select employee_id 
from employees e 
where department_id in (select department_id
						from departments d
						where location_id = 1700
						);

6.查询管理者是King的员工姓名和工资

select e.first_name , e.last_name , e.salary 
from employees e join employees e2 
on e.manager_id = e2.employee_id 
where e2.last_name = 'King';

select last_name , salary 
from employees e 
where manager_id  in (select employee_id 
						from employees e2 
						where last_name = 'King'
						);

7.查询工资最低的员工信息:last_name，salary

select last_name , salary 
from employees e 
where salary <= (select min(salary)
				from employees e2
				);

8.查询平均工资最低的部门信息

select department_id , avg(salary) avg_salary 
from employees e 
group by department_id 
having avg_salary <= all(select avg(salary)
					     from employees e2 
					     group by department_id 
					     );
	
select *
from departments d
where department_id = (
						select department_id 
						from employees e 
						group by department_id 
						having avg(salary) <= all(select avg(salary)
												  from employees e2 
												  group by department_id 
												  )
					   );

9.查询平均工资最低的部门信息和该部门的平均工资(相关子查询)

select department_id , avg(salary) avg_salary 
from employees e 
group by department_id 
having avg_salary <= all(select avg(salary)
					  from employees e2 
					  group by department_id 
					  );
				
					 
select d.*, t_avg_salary.avg_salary
from departments d join (
						select department_id , avg(salary) avg_salary
						from employees e 
						group by department_id 
						having avg_salary <= all(select avg(salary)
												  from employees e2 
												  group by department_id 
												  )
						) t_avg_salary
on d.department_id = t_avg_salary.department_id;

select d.*, t_avg_salary.avg_salary
from departments d join (
						select department_id , avg(salary) avg_salary
						from employees e 
						group by department_id 
						order by avg_salary
						limit 1
						) t_avg_salary
on d.department_id = t_avg_salary.department_id;

10.查询平均工资最高的 job 信息

select job_id , avg(salary) avg_salary 
from employees e 
group by job_id 
order by avg_salary DESC
limit 1;

11.查询平均工资高于公司平均工资的部门有哪些?

select department_id 
from employees e 
group by department_id 
having avg(salary) > (select avg(salary)
					  from employees e2
					  );

12.查询出公司中所有 manager 的详细信息

select distinct *
from employees e join employees e2 
on e.manager_id = e2.employee_id ;

select distinct e2.*
from employees e join employees e2 
on e.manager_id = e2.employee_id ;

13.各个部门中 最高工资中最低的那个部门的 最低工资是多少?

## 理解错意思了
select max(salary) max_salary 
from employees e 
group by department_id 
having max_salary <= all (select max(salary)
						  from employees e2
						  group by department_id
						  );

## 方式一
select min(salary)
from employees e 
group by department_id 
having department_id = (
						select department_id 
						from employees e1
						group by department_id 
						having max(salary) <= all (select max(salary)
												  from employees e2
												  group by department_id
						  							)
						)
		
## 方式二，当然里面也可以用limit
select min(salary)
from employees e , (
						select department_id 
						from employees e1
						group by department_id 
						having max(salary) <= all (select max(salary)
												  from employees e2
												  group by department_id
						  							)
						)t_dep_id
where e.department_id = t_dep_id.department_id;

14,查询平均工资最高的部门的 manager 的详细信息:last_name，department_id,email，salary

select last_name , department_id , email , salary 
from employees e
where employee_id = (select manager_id 
					from employees e1 
					where manager_id is not null
					group by department_id 
					having avg(salary) >= all(select avg(salary)
											  from employees e2
											  where manager_id is not null
											  group by department_id
											  )
					);
SELECT last_name, department_id, email, salary
FROM employees
WHERE employee_id IN (
    SELECT DISTINCT manager_id
    FROM employees e, (
        SELECT department_id, AVG(salary) avg_sal
        FROM employees
        GROUP BY department_id
        ORDER BY avg_sal DESC
        LIMIT 0,1
    ) t_dept_avg_sal
    WHERE e.`department_id` = t_dept_avg_sal.`department_id`
);

15，査询部门的部门号，其中不包括job_id是"ST_CLERK"的部门号

select department_id 
from employees e 
group by department_id
having department_id not in (select department_id
						     from employees e2
							 where job_id = 'ST_CLERK'
							 );

16，选择所有没有管理者的员工的last_name

select last_name 
from employees e 
where manager_id is null;

17.查询员工号、姓名、雇用时间、工资，其中员工的管理者为'De Haan'

select employee_id , first_name , hire_date , salary 
from employees e 
where manager_id = (select employee_id
					from employees e2
					where last_name = 'De Haan'
					);

18.查询各部门中工资比本部门平均工资高的员工的员工号，姓名和工资(相关子査询)

select e.employee_id , e.last_name , e.salary 
from employees e join (select employee_id, department_id , avg(salary) avg_salary 
					   from employees e2
					   group by department_id) e1
on e.employee_id = e1.employee_id
where e.salary > e1.avg_salary;

19.查询每个部门下的部门人数大于 5 的部门名称(相关子查询)

select department_name 
from departments d 
where department_id in (select department_id 
						from employees e 
						group by department_id 
						having count(employee_id) > 5
						);
					
SELECT department_name
FROM departments d
WHERE 5 < (
	SELECT COUNT(*)
    FROM employees e
    WHERE d.`department_id` = e.`department_id`
);

20.查询每个国家下的部门个数大于 2的国家编号(相关子查询)

select country_id 
from locations l join departments d 
on l.location_id = d.location_id 
group by country_id 
having count(department_id) > 2;

select l.*
from locations l 
where 2 < (select count(*) 
			from departments d
			where l.location_id = d.location_id
		  );

标签：练习题,salary,name,employees,查询,department,id,select
From： https://www.cnblogs.com/ratillase/p/18218173

相关文章

  • Mybatis的mapper文件模糊查询TDengine数据库
    在mapper文件中使用#{}时会自动添加单引号，无法拼接使用%进行模糊查询，可以使用<bind>标签。MyBatis的标签用于创建一个新的变量，并将其绑定到上下文中。这通常用于创建复杂的SQL片段，这些片段可能包含从多个参数中组合而成的值。以下是<bind>标签的一个简单示例：<selectid="......
  • 共享内存练习题
    设计三个程序，要求三个程序申请一块共享内存，并分别映射到各自进程的地址空间，进程A和进程B对共享内存段中的数据进行修改，然后进程C不断输出共享内存段中的数据，并观察效果，要求实现进程间的互斥，避免竞争。进程A：/******************************************************************......
  • 小易大数据：大数据报告查询领域的黑马，这些优势让你无法忽视！
    随着大数据技术被运用到各行各业，风控领域也不例外，形成了基于大数据技术的大数据信用，也就是我们常说的大数据报告或者网贷大数据，在众多的查询平台中，小易大数据平台在市面上是比较受欢迎的，那在小易平台查大数据报告有哪些优势呢?本文带你了解一下。小易大数据在市面上......
  • SQLServer如何查询近3分钟最消耗CPU的SQL
    在SQLServer中，要查询近3分钟最消耗CPU的SQL语句，可以使用sys.dm_exec_query_stats动态管理视图结合sys.dm_exec_sql_text函数来获取SQL语句的文本。不过，直接查询近3分钟的数据可能需要一些额外的逻辑来筛选时间范围，因为sys.dm_exec_query_stats并不直接提供时间筛选的功能。一种......
  • MySQL数据库语法(五--＞多表查询)
    多表查询1.innerjoin：代表选择的是两个表的交差部分。内连接就是表间的主键与外键相连，只取得键值一致的，可以获取双方表中的数据连接方式。语法如下：SELECT列名1,列名2...FROM表1INNERJOIN表2ON表1.外键=表2.主键WhERE条件语句;2.leftjoin：代表选择的是前面......
  • Mysql单表普通查询(新手推荐)
    大家在学校里学mysql查询的时候，尤其是遇到比较复杂查询的时候，不知道有没有经常遇到问题。小编在学习mysql的时候，经常哪个怎么搞，得出的值也老是不对，今天在这里分享一些原理性的东西，帮助大家理解如何去查询基础 3、select...聚合函数from表名1、where.........
  • 消息队列练习题
    消息队列练习题进程A/**********************************************************************filename:mesqa.c*author:c7355608bs@163.com*date:2024/5/28*function:接收进程b的信号，读出消......
  • 消息队列练习题
    题目：要求进程A创建一条消息队列之后向进程B发送SIGUSR1信号，进程B收到该信号之后打开消息队列并把进程的PID作为消息写入到消息队列中，要求进程B在写入消息之后，发SIGUSR2信号给进程A，进程A收到该信号则从消息队列中读取消息并输出消息正文的内容。进程A的代码：//构造用于接收消息......
  • 系统编程练习题----使用消息队列实现两个进程之间的通信
    目录题目思路代码展示进程A进程B结果展示题目要求进程A创建一条消息队列之后向进程B发送SIGUSR1信号，进程B收到该信号之后打开消息队列并写入一段信息作为消息写入到消息队列中，要求进程B在写入消息之后，发SIGUSR2信号给进程A，进程A收到该信号则从消息队列中读取消息并输出消息正文......
  • MySQL函数查询目录树问题记录
    DELIMITER//CREATEFUNCTION`getChildXzqhList`(rootIdBIGINT)RETURNSVARCHAR(4000)BEGINSETSESSIONgroup_concat_max_len=1000000;--设置为1MB设置GROUP_CONCAT函数输出的最大长度大小，太小的话整体会被截掉RETURN(WITH......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99