SQL基本查询

根据Oracle数据库scott方案下的emp表和dept表，完成以下操作;

1.查询部门编号是20号的所有员工信息

set linesize 140;

select * from  scott.emp  where deptno=20;

2.查询所有工作为CLERK的员工的员工号、员工名和部门号

select empno,ename,deptno from scoot.empwhere job=’CLERK’;

3.查询奖金（COMM）高于工资（SAL）的员工信息

select * from  scott.emp  where comm>sal;

4.查询奖金高于工资的20%的员工信息

select * from  scott.emp  where comm>(sal*0.2);

5.查询部门是10并且工作为MANAGER和部门编号是20并且工作为CLERK的员工的信息

select * from scott.emp where deptno=10 andjob=’MANAGER’

union

select * from scott.emp where deptno=20 andjob=’ CLERK’

6.查询工作不是MANAGER和CLERK，并且工资大于或等于2000的员工信息

select * from scott.emp where job not in(‘MANAGER’,’CLERK’)

Intersect

select * from scott.emp where  sal>=2000;

7.查询有奖金的员工的不同工作

select distinct job from scott.emp wherecomm is not null and comm!=0;

8.查询所有员工的人数和他们的平均工资

select count(empno) as 总人数,avg(sal) as 平均工资 from scott.emp;

9.查询没有奖金或奖金低于100的员工信息

select * from scott.emp where (comm is nullor comm < 100);

10.查询最近两年入职的员工信息

select * from scott.emp wheremonths_between(sysdate,hiredate)/12<2;

11.查询员工工龄大于或等于10年的员工信息

1)select * from scott.emp wheremonths_between(sysdate,hiredate)/12>=2;

2)select * from scott.emp where (sysdate -hiredate)/365 >= 10

12.查询员工信息，要求以首字母大写的方式显示所有员工的姓名

1)select initcap(ename) from scott.emp;

2)select upper(substr(ename,1,1))+lower(substr(ename,2,length(ename)-1)) from scott.emp;

13.查询员工名正好为6个字符的员工的信息

select * from scott.emp where length（ename）= 6;

14.查询员工名字中不包含字母“S”员工

select * from scott.emp where ename notlike ‘%s%’;

15. 查询员工姓名的第2个字母为“M”的员工信息

select * from scott.emp where ename like‘_M%’;

16.查询所有员工姓名的前3个字符

1)select left(ename,3), ename  from scott.emp;

2) select ename 员工姓名,substr(ename,1,3)  fromscott.emp;

17. 查询所有员工的姓名，如果包含字母“s”，则用“S”替换

select replace(ename,'s','S') from scott.emp;

18.查询员工的姓名和入职日期，并按入职日期从先到后进行排列

select ename,hiredate from scott.emp orderby hiredate asc;

19.显示所有员工的姓名、工作、工资，按工作降序排列，若工种相同则按工资升序排列。

select ename,job,sal from emp order by jobdesc,sal asc;

20显示所有员工的姓名、入职的年份和月份，若入职日期所在的月份排序，若月份相同则按入职的年份排序

1)select ename,datename(yy,hiredate)+’年’+ datename(mm,hiredate) +’月’ from emp orderby datename(mm,hiredate), datename(yy,hiredate);

2)selectename,to_char(hiredate,'yyyy')||'-'||to_char(hiredate,'mm') from emp order byto_char(hiredate,'mm'),to_char(hiredate,'yyyy');

21.查询每个部门中的员工数量、平均工资和平均工作年限

select deptno,count(deptno),avg(sal),avg(months_between(sysdate,hiredate)/12)from scott.emp group by deptno;

22.查询各个部门的人数及平均工资

select deptno,count(*),avg(sal) from empgroup by deptno ；

23.查询各个工作的最低工资，并输出最低工资低于3000的工作名称

select job,min(sal) from scott.emp group byjob having min(sal)<3000;

24. 查询各个部门中的不同工种的最高工资

select max(sal),job,deptno from emp groupby deptno,job ;

25. 统计各个工种的员工人数与平均工资

select job,count(job),avg(sal) from scott.emp group by job;