Select语句
来源:互联网 发布:阿里云学生认证 编辑:程序博客网 时间:2024/06/10 23:56
(1)查询所有工种为CLERK的员工的姓名及其部门名称。select ename,dname from scott.emp t1 inner join scott.dept t2 on t1.deptno=t2.deptno where job='CLERK';(2)查询所有部门及其员工信息,包括那些没有员工的部门。select * from scott.emp t1 right join scott.dept t2 on t1.deptno=t2.deptno;(3)查询所有员工及其部门信息,包括那些还不属于任何部门的员工。select * from scott.emp t1 left join scott.dept t2 on t1.deptno=t2.deptno;(4)查询在SALES部门工作的员工的姓名信息。select * from scott.emp where deptno=(select deptno from scott.dept where dname='SALES');(5)查询所有员工的姓名及其直接上级的姓名。select t1.ename as 员工姓名,t2.ename 经理姓名 from scott.emp t1,scott.emp t2 where t1.mgr=t2.empno;(6)查询入职日期早于其上级领导的所有员工的信息。select t1.ename as 员工姓名,t2.ename 经理姓名 from scott.emp t1,scott.emp t2 where t1.mgr=t2.empno and t1.hiredate<t2.hiredate;(7)查询从事同一种工作但不属于同一部门的员工信息。select t1.ename,t1.job,t1.deptno,t2.ename,t2.job,t2.deptno from scott.emp t1 cross join scott.emp t2 where t1.job=t2.job and t1.deptno <>t2.deptno;(8)查询10号部门员工及其领导的信息。select t1.ename as 员工姓名,t2.ename 经理姓名 from scott.emp t1,scott.emp t2 where t1.mgr=t2.empno and t1.deptno=10;(9)使用UNION将工资大于2500的雇员信息与工作为ANALYST的雇员信息合并。select * from scott.emp where sal>2500 union select * from scott.emp where job='ANALYST';(10)通过INTERSECT集合运算,查询工资大于2500,并且工作为ANALYST的雇员信息。select * from scott.emp where sal>2500 intersectselect * from scott.emp where job='ANALYST';(11)使用MINUS集合查询工资大于2500,但工作不是ANALYST的雇员信息。select * from scott.emp where sal>2500minusselect * from scott.emp where job='ANALYST';(12)查询工资高于公司平均工资的所有员工信息。select * from scott.emp where sal>(select avg(sal) from scott.emp);(13)查询与SMITH员工从事相同工作的所有员工信息。select * from scott.emp where job=(select job from scott.emp where ename='SMITH');(14)查询工资比SMITH员工工资高的所有员工信息。select * from scott.emp where sal>(select sal from scott.emp where ename='SMITH');(15)查询比所有在30号部门中工作的员工的工资都高的员工姓名和工资。select ename,sal from scott.emp where sal>all(select sal from scott.emp where deptno=30);(16)查询部门人数大于5的部门的员工信息。select * from scott.emp where deptno in (select deptno from scott.emp group by deptno having count(*)>5);(17)查询所有员工工资都大于2000的部门的信息。select * from scott.dept where deptno in(select deptno from scott.emp group by deptno having min(sal)>2000);(18) 查询人数最多的部门信息。select * from scott.dept where deptno in (select deptno from (select deptno,count(*) as 人数 from scott.emp group by deptno) where 人数=(select max(人数) from(select deptno,count(*) as 人数 from scott.emp group by deptno)));(19)查询至少有一个员工的部门信息。select * from scott.dept where deptno in(select deptno from scott.emp group by deptno having count(*)>=1);(20)查询工资高于本部门平均工资的员工信息。select * from scott.emp e where sal>(select avg(sal) from scott.emp group by deptno having e.deptno=deptno);(21)查询工资高于本部门平均工资的员工信息及其部门的平均工资。select * from((select * from scott.emp e where sal>(select avg(sal) from scott.emp group by deptno having e.deptno=deptno)) t1 inner join;(select avg(sal),deptno from scott.emp group by deptno) t2 on t1.deptno=t2.deptno);(22)查询每个员工的领导所在部门的信息。select * from scott.dept where deptno in(select distinct deptno from scott.emp where empno in(select distinct mgr from scott.emp));(23)查询平均工资低于2000的部门及其员工信息。select * from scott.emp t1,scott.dept t2 where t1.deptno=t2.deptno and t1.deptno in(select deptno from scott.emp group by deptno having avg(sal)<2000);
阅读全文
0 0
- select语句
- SELECT语句
- SELECT 语句
- SELECT语句
- select 语句
- Select语句
- select 语句
- SELECT语句
- select语句
- select 语句
- select 语句
- select语句
- Select 语句
- select语句
- select语句
- SELECT语句
- select语句
- select 语句
- ios 面试详解题-三
- Github 安全军火库(转)
- Intellij打包Spark代码为jar后,执行出错
- vue中的事件
- L1-036. A乘以B
- Select语句
- Spring+Spring Security+OAuth2实现REST API权限控制
- 代理模式(静态代理和动态代理) JAVA
- 网易2017内推笔试2:数字游戏 [python]
- 第22章 钟灵毓秀的世界——三维地形的构建
- linux 简单驱动编写
- 《计算机操作系统》总结二(进程与线程)
- 11.14学习心得
- freeswitch 在redhat 7.0/centos 7.0上的编译安装