SQL语句练习题

SQL语句练习题一

1、选择部门30中的所有员工；

SQL> select * from emp where deptno = 30;

2、列出所有办事员(CLERK)的姓名，编号和部门编号；

SQL> select empno,ename,deptno from emp where job='CLERK';

3、找出奖金高于工资的员工；

SQL> select * from emp where comm>sal;

4、找出奖金高于工资的60%的员工；

SQL> select * from emp where comm>(sal*0.6);

5、找出部门10中的所有经理(MANAGER)和部门20中所有的办事员(CLERK)的详细资料；

SQL>select * from emp
where (deptno=10 and job='MANAGER') or (deptno=20 and job='CLERK');

6、找出部门10中所有的经理(MANAGER),部门20中所有办事员(CLERK),既不是经理又不是办事员但其工资大于或等于2000的所有员工的详细资料；

select * from emp
where (deptno=10 and job='MANAGER')
or (deptno=20 and job='CLERK')
or ((Job!='MANAGER' and job!='CLERK') and sal > =2000);

7、找出收取奖金的员工的不同工作；

SQL> select job from emp where comm is not null;
SQL> select job from emp where not comm is null;
SQL> select distinct job from emp where not comm is null;

8、找出不收取奖金或者收取的奖金低于100的员工；

SQL> select * from emp where comm is null or comm <100;

9、找出各月倒数第三天受雇的所有员工；

select to_char(hiredate,'  dd')day1 from emp;
select to_char(last_day(hiredate),'  dd')day2 from emp;
select * from emp where ((to_char(last_day(hiredate),'  dd') - to_char(hiredate,'  dd')) =2);

10、找出早于12年前受雇的员工；

select to_char(hiredate,'yyyy')year1 from emp;
select to_char(sysdate,' yyyy')year2 from emp;
select * from emp where (to_char(sysdate,' yyyy') - to_char(hiredate,'yyyy')) > 13 ;

11、以首字母大写的方式显示所有员工的姓名；

SQL> select initcap(ename) from emp;

12、显示正好为5个字符的员工的姓名；

SQL> select ename from emp where length(ename)=5;

13、显示不带有“R”的员工姓名；

SQL> select ename from emp where ename not like '%R%';

14、显示所有员工姓名的前三个字符；

SQL> select ename,substr(ename,1,3) from emp;

15、显示所有员工的姓名，用“a”替换所有的“A”；

SQL> select replace(ename,'A','a') from emp;

16、显示满10年服务年限的员工的姓名和受雇日期；

SQL>select ename,hiredate from emp where months_between(sysdate,hiredate)>120;

17、显示员工的详细资料，按姓名排序；
排序默认用的是升序

SQL> select * from emp order by ename;

SQL> select * from emp order by ename asc;

18、显示员工的姓名和受雇日期，根据其服务年限，将最老的员工排在最前面；

SQL> select ename,hiredate from emp order by hiredate asc;

19、显示所有员工的姓名、工作和工资，按工作的降序排序，若工作相同则按工资排序；

SQL> select ename,job,sal from emp order by job desc,sal asc;

20、显示所有员工姓名、加入公司的年份和月份，按受雇日期所在月排序，若月份相同则将最早年份的员工排在最前面；

select  ename,to_char(hiredate,'yyyy') year,to_char(hiredate,'   mm') month from emp

order by month,year asc;

21、显示在一个月为30天的情况所有员工的日工资，忽略余数；

select ename,trunc(sal/30)day_sal from emp;

22、找出(任何年份的)2月受聘的所有员工；

select ename from emp where ((to_char(hiredate,' mm') -02)=00);

23、对于每个员工，显示其加入公司的天数；

select ename,hiredate,sysdate-hiredate from emp;

24、显示姓名字段的任何位置包含“A”的所有员工的姓名；

SQL> select ename from emp where ename like '%A%';

25、以年月日的方式显示所有员工的服务年限；

SQL> select ename,hiredate,
	 trunc(months_between(sysdate,hiredate)/12)year,
	 trunc(mod(months_between(sysdate,hiredate),12))month,
	 trunc(sysdate-add_months(hiredate,months_between(sysdate,hiredate))) day
	 from emp; 

SQL练习题二

1、列出至少有一个员工的所有部门编号、名称，并统计出这些部门的平均工资、最低工资、最高工资

select d.deptno, d.dname, avg(e.sal) , min(e.sal), max(e.sal),count(e.empno)
from emp e right join dept d on e.deptno = d.deptno
group by d.deptno, d.dname
having  count(e.empno)>0;

2、列出薪金比SMITH或者ALLEN多的所有员工的编号、姓名、部门名称、其领导姓名

select e.empno , e.ename,  d.dname ,m.ename
from emp e join dept d on e.deptno = d.deptno
  join emp m on e.mgr = m.empno
where e.sal > (select max(sal) from emp where ename in ('SMITH','ALLEN'))

3、列出所有员工的编号、姓名及其直接上级的编号、姓名，显示的结果按领导年工资的降序排列

select e.empno, e.ename , m.empno, m.ename,(m.sal + ifnull(m.comm,0))*12 年薪
from emp e left join emp m on e.mgr = m.empno
order by (m.sal + ifnull(m.comm,0))*12 desc;

4、列出雇佣日期早于其直接上级的所有员工的编号、姓名、部门名称、部门位置、部门人数

select e.empno , e.ename , d.dname , d.loc , n.ct
from emp e join dept d on e.deptno = d.deptno
  left join emp m on e.mgr = m.empno
  join (select deptno , count(empno) ct
    from emp
    group by deptno) n on e.deptno = n.deptno
where e.hiredate < m.hiredate;

5、列出部门名称和这些部门的员工信息(数量、平均工资)，同时列出那些没有员工的部门

select d.deptno, d.dname , count(e.empno) , avg(e.sal)
from emp e right join dept d on e.deptno = d.deptno
group by d.deptno, d.dname

6、列出所有CLERK的姓名及其部门名称，部门的人数，工资等级

select e.ename , d.dname , n.ct , s.grade
from emp e join dept d on e.deptno = d.deptno
  join salgrade s on e.sal between s.losal and s.hisal
  join (select deptno , count(empno) ct
    from emp
    group by deptno) n on e.deptno = n.deptno
where e.job = 'CLERK';

7、列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数及所在部门名称、位置、平均工资

select n.job, n.ct, n.avg_sal , d.dname , d.loc
from emp e join dept d on e.deptno = d.deptno
  join (select job, count(e.empno) ct , avg(sal) avg_sal
    from emp e
    group by e.job
    having min(e.sal) > 1500) n on e.job = n.job

8、列出在部门SALES工作的员工的姓名、基本工资、雇佣日期、部门名称，假定不知道该部门的部门编号

select e.ename, e.sal , e.hiredate, d.dname
from emp e join dept d on e.deptno = d.deptno
where d.dname = 'SALES'

9、列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级

select e.ename , d.dname , m.ename , s.grade
from  emp e , dept d, emp m, salgrade s
where e.deptno = d.deptno and e.mgr = m.empno and e.sal between s.losal and s.hisal
  and e.sal > (select avg(sal) from emp)

10、列出与SCOTT从事相同工作的所有员工及部门名称，部门人数

select e.*, d.dname, n.ct
from emp e , dept d , (
  select deptno , count(empno) ct
  from emp
  group by deptno) n
where e.deptno = d.deptno and e.deptno = n.deptno
  and e.job = (select job from emp where ename = 'SCOTT');

11、列出公司各个工资等级雇员的数量、平均工资

select s.grade,count(empno), avg(sal)
from emp e , salgrade s
where e.sal between s.losal and s.hisal 
group by s.grade

12、列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称

select sal from emp where deptno = 30
select e.ename, e.sal , d.dname
from emp e join dept d on e.deptno = d.deptno
where sal > all(select sal from emp where deptno = 30);

13、列出在每个部门工作的员工数量、平均工资和平均服务期限

select deptno, count(empno),avg(sal) , avg(datediff(now(),hiredate)/365)
from emp
group by deptno

14、列出所有员工的姓名、部门名称和工资

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

15、列出所有部门的详细信息和部门人数

select d.*, nt.ct
from dept d left join (select deptno , count(1) ct from emp group by deptno) nt 
on d.deptno = nt.deptno

16、列出各种工作的最低工资及从事此工作的雇员姓名

select ename from emp where sal in (select min(sal) from emp group by job);

17、列出各个部门的MANAGER的最低薪金、姓名、部门名称、部门人数

select nt_sal.min_sal, e.ename, d.dname,nt_ct.ct
from emp e,dept d,
	(select deptno, min(sal) min_sal from emp where job = 'MANAGER' group by deptno) nt_sal ,
	(select deptno , count(1) ct from emp group by deptno) nt_ct
where e.deptno = d.deptno 
    and e.deptno = nt_sal.deptno 
    and e.deptno = nt_ct.deptno 
    and e.job = 'MANAGER'

18、列出所有员工的年工资，所在部门名称，按年薪从低到高排序

select e.sal + ifnull(comm,0) 年薪, d.dname 
from emp e join dept d on e.deptno = d.deptno order by 年薪;

19、列出某个员工的上级主管及所在部门名称，并要求出这些主管中的薪水超过3000

select e.ename, m.ename, d.dname
from emp e,emp m,dept d
where e.mgr = m.empno and m.deptno = d.deptno and m.sal >= 3000

20、求出部门名称中带‘S’字符的部门员工的工资合计、部门人数

select d.deptno , sum(e.sal) , count(1)
from emp e right join dept d on e.deptno = d.deptno
where d.dname like '%S%'
group by d.deptno;

21、给任职日期超过30年或者87年雇佣的雇员加薪，加薪原则：10部门增长10%，20部门增长20%，30部门增长30%，以此类推

select sal, sal*(1+ deptno/100)
from emp
where adddate(curdate(), interval -30 year) > hiredate or year(hiredate) = 1987;