当前位置:Gxlcms > 数据库问题 > 数据库的查询语句

数据库的查询语句

时间:2021-07-01 10:21:17 帮助过:12人阅读

01.查询员工表所有数据,并说明使用*的缺点

select *from emp;

02.查询职(job)为‘PRESIDENT‘的员工的工资

select sal from emp where job=’PRESIDENT’;

03.查询佣金为0或为null的员工的信息

select *from emp where comm is null or comm=0;

03.查询入职日期在1981-5-1到1981-12-31之间的所有员工的信息

select *from emp

where hiredate between to_date(‘1-5月-1981‘)   and to_date(‘31-12月-1981‘);

05.查询所有名字长度为4的员工的员工的编号,姓名

select empno,ename from emp where length(ename)=4;

 

06. 显示10号部门饿所有经理和20号部门的所有员工

select *from emp where (deptno=10 and job=‘MANAGER‘) or   deptno=20;

07. 显示姓名没有‘L‘字的员工的详细信息或含有‘SM‘字的员工信息

select *from emp where ename not like ‘%L%‘ or ename like   ‘%SM%‘;

08. 显示各个部门经理的工资

select deptno,sal from emp where job=‘MANAGER‘;

09.显示佣金收入比工资高的员工的详细信息

select *from emp where COMM>SAL;

10.把hiredate列看做是员工的生日,求本月过生日的员工:

select *from emp

where extract(month from hiredate)=extract(month from   sysdate);

11. 把hiredate列看做是员工的生日,求下月过生日的员工

select *from emp

where   to_char(hiredate,‘mm‘)=to_char(add_months(sysdate,1),‘mm‘);

12.求1982年入职的员工

select *from emp where to_char(hiredate,‘yyyy‘)=1982;

13. 求1981年下半年入职的员工

select *from emp

where hiredate between to_date(‘1981-7-1‘,‘yyyy-mm-dd‘)

and to_date(‘1982-1-1‘,‘yyyy-mm-dd‘)-1;

select *from emp

where extract(year from hiredate)=1981 and extract(month   from hiredate)>6;

14. 求1981年各个月入职的员工个数

select to_char(hiredate,‘mm‘),count(*) from emp

where to_char(hiredate,‘yyyy‘)=‘1981‘

group by to_char(hiredate,‘mm‘)

order by to_char(hiredate,‘mm‘);

select extract(month from hiredate),count(*) from emp

where extract(year from hiredate)=1981

group by extract(month from hiredate)

order by extract(month from hiredate);

 

子查询

01 .查询各个部门的平均工资

select deptno,avg(nvl(sal,0)) from emp group by deptno;

02.显示各种职位的最低工资

select job,min(sal) from emp group by job;

03.按照入职日期由新到旧排列员工信息

select *from emp order by hiredate desc;

04.查询员工的基本信息,附加其上级的姓名(自关联)

select e.*,d.ename from emp e,emp d

where e.mgr=d.empno;

05.显示工资比’ALLEN’高的所有员工的姓名和工作

select ename,job,sal from emp

where sal>

(select sal from emp where ename=‘ALLEN‘);

06.显示与scott从事相同工作的员工的信息(子查询)

select *from emp

where job=

(select job from emp where ename=‘SCOTT‘);

 07.显示销售部(‘SALES’)员工的姓名

select e.ename from emp e inner join dept d

on e.deptno=d.deptno

where d.dname=‘SALES‘;

08.显示与30号门’MARTIN’员工工资相同的员工的姓名和工资

select ename,sal from emp

where sal=

(select sal from emp where ename=‘MARTIN‘ and deptno=30);

09.查询所有工资高于平均工资(包括所有员工)的销售人员

select *from emp

where sal>

(select avg(nvl(sal,0)) from emp) and job=‘SALESMAN‘;

 

10.显示所有职员的姓名及其所在部门的名称和工资(表连接)

select e.ename,e.sal,d.dname

from emp e inner join dept d

on e.deptno=d.deptno;

 

11.查询在研发部(RESEARCH)工作人员的编号,姓名,工作部门,工作所在地

select e.empno,e.ename,e.deptno,d.loc

from emp e inner join dept d

on e.deptno=d.deptno

where d.dname=‘RESEARCH‘;

12.查询各个部门的名称和员工人数

select d.dname,e.deptno,count(*)

from emp e inner join dept d

on e.deptno=d.deptno

group by e.deptno,d.dname;

13.查询各个部门员工工资大于平均工资(平均工资包括所有员工)的人数和员工职位(子查询)

select count(*),job from emp

where sal>

(select avg(nvl(sal,0)) from emp)

group by job;

14.查询工资相同的员工的工资和姓名(子查询)

select sal,ename

from emp e

where exists

(select 1 from emp where e.sal=sal and   e.ename<>ename);

select sal,ename from emp e where(select count(*) from emp

where sal=e.sal group by sal)>1;

select e.sal,e.ename from emp e,emp d

where e.sal=d.sal and e.ename<>d.ename;

15.查询工资最高的3名员工信息(排序)

select rownum,e.* from

(select *from emp order by sal desc) e

where rownum<=3;

16.按工资进行排名:排名从1开始,工资相同排名相同(如果两个并列第1则没有地2名,从第三名继续排)

select ename,rank() over(order by sal desc) from emp;

 17.求入职日期相同的(年月日相同)的员工

select *from emp e

where exists

(select 1 from emp where e.hiredate=hiredate and   e.ename<>ename);

18.查询每个部门的最高工资

select deptno,max(sal) from emp

group by deptno;

19.查询每个部门,每个职位的最高工资

select deptno,job,max(sal) from emp

group by deptno,job

order by deptno asc;

20.查询每个员工的信息及工资级别,用到表(Salgrade)

select e.*,

decode (s.grade,1,‘第五等工资‘,

            2,‘第四等工资‘,

            3,‘第三等工资‘,

            4,‘第二等工资‘,

            5,‘第一等工资‘)   new_sal

from emp e,salgrade s

where e.sal between s.losal and s.hisal;

21.查询工资最高的第6-10名

select * from

(select rownum r,e.* from

(select *from emp order by sal desc) e)

where r>=6 and r<=10;

22.查询各个部门工资最高的员工信息

select *from emp e

where e.sal=

(select max(sal) from emp where e.deptno=deptno group by   deptno );

select *from emp e,(select deptno,max(sal) m from emp group   by deptno) d

where e.deptno=d.deptno and e.sal=d.m;

23.查询每个部门工资最高的前2名员工

select * from

(select e.*,row_number() over(partition by deptno order by   sal) r from emp e)

where r<=2;

select *from emp e

where 2>

(select count(*) from emp where sal>e.sal and   e.deptno=deptno)

order by deptno,sal desc;

24.查询出有3个以上下属的员工信息(自关联)

select *from emp e

where (select count(*) from emp where e.empno=mgr)>2;

select *from emp e

where exists

(select mgr,count(*) from emp

where e.empno=mgr

group  by mgr having   count(*)>2);

25.查询所有大于本部平均工资的员工信息

select *from emp e

where sal>

(select avg(nvl(sal,0)) from emp where e.deptno=deptno   group by deptno)

order by sal;

26.查询平均工资最高的部门信息

select e.deptno,e.r,d.* from

(select rownum s,deptno,r from

(select deptno,avg(nvl(sal,0)) r from emp

group by deptno

order by avg(nvl(sal,0)) desc)) e,dept d

where s=1 and e.deptno=d.deptno;

27.查询大于各个部门总工资的平均值的部门信息

select s.deptno,s.av,d.* from

(select e.deptno,avg(nvl(sal,0)) av from emp e

where (select avg(nvl(sal,0)) from emp where   e.deptno=deptno)>

(select avg(nvl(sal,0)) from emp)

group by deptno) s,dept d

where s.deptno=d.deptno;

select e.deptno,avg(nvl(e.sal,0)),d.dname,d.loc from emp   e,dept d

where (select avg(nvl(sal,0)) from emp where   e.deptno=deptno)>

(select avg(nvl(sal,0)) from emp) and e.deptno=d.deptno

group by e.deptno,d.dname,d.loc;

28.查询大于各个部门总工资平均值的部门下的员工信息

select e.* from emp e

where sal>

(select avg(nvl(sal,0)) from emp where e.deptno=deptno);

 29.查询么有员工的部门信息

select *from dept

where deptno not in

(select deptno from emp group by deptno);

复杂查询


1.列出至少有一个雇员的所有部门信息(嵌套子查询)

select *   from dept d

where exists

(select e.deptno from   emp e where e.deptno=d.deptno);

select distinct * from dept d

where   d.deptno in

(select   e.deptno from emp e where e.deptno=d.deptno);

2、列出薪金(工资)比‘SMITH‘多的所有雇员信息(嵌套子查询)

select *   from emp

where   sal>

(select   sal from emp e where e.ename=‘SMITH‘);

3、列出所有雇员的姓名及其上级的姓名(自表外连接)

select   e.ename 员工,d.ename 老板 from emp e left join emp d

on   e.mgr=d.empno;

select   e.ename 员工,d.ename 老板 from emp e,emp d

where e.mgr=d.empno(+);

4、列出入职日期(雇佣日期)早于其直接上级的所有雇员(自表连接)

select *   from emp e

where   e.hiredate>

(select   hiredate from emp where e.mgr=empno);

5、列出部门名称和这些部门的雇员,同时列出那些没有雇员的部门(外连接)

select   d.dname,e.ename from dept d left join emp e on d.deptno=e.deptno;

select d.dname,e.ename from dept d,emp e where e.deptno(+)=d.deptno;

6、列出所有‘CLERK‘(办事员)的姓名及其部门名称(内连接)

select   e.ename,d.dname from emp e join   dept d

on e.deptno=d.deptno and   e.job=‘CLERK‘;

7、列出各种工作的最低薪金,并使最低薪金大于1500(分组和过滤)

select   job,min(sal) from emp

group by job

having min(sal)>1500;

8、列出从事sales(销售)工作的雇员的姓名,假定不知道销售部的部门编号
(使用嵌套子查询进行连接)

select   e.ename from emp e where e.deptno=

(select   d.deptno from dept d where d.dname=upper(‘sales‘));

9、列出薪金高于公司平均水平的所有雇员信息(聚合函数)

select *   from emp

where   sal>

(select avg(nvl(sal,0))   from emp);

 

10、列出与"SCOTT"从事相同工作的所有雇员(嵌套自查询)

select *   from emp

where   job=

(select   job from emp where ename=upper(‘SCOTT‘));

11、列出薪金等于在部门30工作的雇员的所有姓名和薪金(有问题)

select   e.ename,e.sal,e.deptno from emp e

where   e.sal in

(select sal from   emp where deptno=30);

select ename,sal,deptno from emp where deptno=30;

12、列出薪金高于在部门30工作的所有雇员的姓名和薪金(嵌套子查询)

select   ename,sal from emp

where   sal>

(select max(sal)   from emp where deptno=30);  

select   ename,sal from emp

where   sal>all

(select   sal from emp where deptno=30);

13、列出每个部门的信息(外连接)
以及该部门中(分组)雇员的数量(外连接并分组)

select   d.dname,d.loc,count(e.empno) from   emp e right join   dept d

on   e.deptno=d.deptno

group by   d.dname,d.loc;

14、列出所有雇员的姓名、部门名称和薪金(外连接)

select   e.ename,d.dname,e.sal

from   emp e left join dept d

on d.deptno=e.deptno;

15、列出从事同一种工作但属于不同的部门的雇员的不同组合(自表笛卡尔积)

select   e.ename,d.ename from emp e,emp d

where e.job=d.job and   e.deptno<>d.deptno and e.ename<d.ename;--比较字母的大小,是两个字符串的字母逐一比较的

16、列出分配有雇员数量(聚合函数)的所有部门(按部门分组并进行外连接)的详细信息,即使分配有零个雇员

这求出的是至少有一名雇员的部门信息.

 

select   d.deptno,d.dname,d.loc,count(e.empno) from   emp e right join   dept d

on   e.deptno=d.deptno

group by d.deptno,d.dname,d.loc;

select   d.deptno,d.dname,d.loc,count(e.empno) from   emp e,dept d

where   e.deptno(+)=d.deptno

group by   d.deptno,d.dname,d.loc;

 

17、列出各种类别工作(分组)的最低工资

select   job,min(sal) from emp

group by   job;

19、列出按计算字段的排序的所有雇员的年薪(计算字段指的是年薪)

select   (sal+nvl(COMM,0))*12 as avs from emp

order by   avs;

20、列出薪金水平处于第四位的雇员信息(多重嵌套查询)

select *   from emp

where   sal=

(select sal from

(select rownum   r,sal from

(select sal from   emp order by   sal desc))

where   r=4);

select *   from (select e.*,dense_rank() over(order by   sal desc) d from emp e)

where d=4;

21、查询各部门薪水最高的员工

select *   from emp e

where   sal=

(select max(sal)   from emp where e.deptno=deptno group by deptno);

select *   from emp e,(select max(sal)   sal from emp group by   deptno) s

where e.sal=s.sal;

数据库的查询语句

标签:关联   nvl   dna   char   har   部门   back   rank   复杂查询   

人气教程排行