【SQL】语句综合练习

完成下面语句

语句是基于Oracle下的HR数据库进行的

1. 每个员工的所有信息

   select * from employees;
   

2. 每个人的部门编号，姓名，薪水

   select department_id,last_name,salary from employees;
   

3. 每个人的年薪

    select last_name, 12*salary from employees;
   

4. 求每个人的年薪，列的别名：“年薪”

    select last_name, 12*salary  "年薪" from employees;
   

5. 求 10 这个部门的所有员工

   select last_name from employees where department_id =10;
   

6. 求名字是 KING 的这个人的信息

   select * from employees where last_name ='King';
   

7. 求薪水大于 2000 的员工信息

   select * from employees where salary>2000;
   

8. 求部门不是 10 的员工信息

   select * from employees where department_id <> 10;
   

9. 求薪水在 800 和 1500 之间的员工信息(包含 800 和 1500)

   select * from employees where salary between 800 and 1500;
   

10. 列出 deptno 为 10 或者 30，并且工资>2000 的所有人

    select last_name from employees deptno in(10,30)and salary>2000;
    

11. 利用 in 操作符，列出部门 10 和 20 的人员

    select * from employees where department_id  in(10,20);
    

12. 利用 like 操作符，查处名字中含有"H"的人员

    select * from employees where last_name like '%H%';
    

13. 分别利用 like 操作符和正则表达式，查处名字中含有"S"或者"M"的人员

    select * from employees where last_name like '%S%' or last_name like '%M%';
    

14. 计算 emp 表中的所有人员的平均薪水

    select avg(salary) from employees;
    

15. 计算 emp 表中最高薪水

    select max(salary) from employees;
    

16. 计算 emp 表中最低薪水

    select min(salary) from employees;
    

17. 计算 emp 表中薪水大于 1000 的人员的个数

    select count(*) from employees where salary>1000;
    

18. 计算 emp 表中薪水的总和

    select sum(salary) from employees;
    

19. 计算 emp 表中薪水和津贴的总和

    select sum(salary+salary*1+nvl(commssion_pct,0), from employees;
    

20. 求各部门最高薪水

    select department_id ,max(salary) from employees  group by department_id;
    

21. 按照部门和职位分组，分别求最高薪水，该组人员个数

    select  department_id ,job_id, count(*),max(salary) from employees group by job_id , department_id;
    

22. 求薪水最高的员工姓名

    select last_name,salary from employees where salary = (select max(salary) from employees);
    

23. 求平均薪水是 2000 以上的部门

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

24. 求每个部门的平均薪水，并按照薪水降序排列

    select department_id,avg(salary) from employees group by department_id order by avg(salary) desc;
    

25. 求每个部门薪水在 1200 以上的雇员的平均薪水、最高薪水，并且分组结果中只包含平均薪水大于 1500 的部门，排序按照部门平均薪水倒序排列

    select  department_id avg(salary),max(salary) from employees where salary>1200 group by department_id having avg(salary)>1500 order by avg(salary) desc;
    

26. 把雇员按部门分组， 求最高薪水， 部门号， 过滤掉名字中第二个字母是'A'的， 要求分组后的平均薪水>1500， 按照部门编号倒序排列

    select department_id,max(salary) from employees where last_name not like '_A%' group by department_id having avg(salary)>1500 order by department_id desc;
    

27. 求平均薪水最高的部门的部门编号

    错误： select e.department_id ,max(e.s) from (select m.department_id,avg(m.salary) s from employees m group by m.department_id) e  department_id 不是分组函数不能和聚合函数放在一起
     select e.department_id ,e.s from (select department_id,avg(salary) s from employees  group by department_id) e  where s = (select max(avg(salary)) maxs from employees group by department_id);
    

28. 求出 emp 表中哪些人是经理人，打印出名字和编号

    select distinct m.employee_id,m.last_name from employees w,employees m where w.manager_id= m.employee_id;
    

29. 求比普通员工的最高薪水还要高的经理人名称

    select distinct m.employee_id,m.last_name from employees w,employees m where w.manager_id= m.employee_idand m.salary > (select max(w.salary) from employees w,employees m where w.manager_id= m.employee_id);
    

30. 每个部门平均薪水的等级(需要用到表的连接)

    select m.department_id,m.avgs, gr.gra from (select department_id,avg(salary) avgs from employees group by department_id)  m,job_grades gr  where m.avgs between gr.lowest_sal and gr.highest_sal;
    select m.department_id,m.avgs, gr.gra from (select department_id,avg(salary) avgs from employees group by department_id)  m inner join job_grades gr on m.avgs between gr.lowest_sal and gr.highest_sal;
    

31. 求部门经理人中平均薪水最低的部门名称

    select e.department_name from (select d.department_name,avg(m.salary) ams from employees w,employees m,departments d  where m.employee_id=w.manager_id and m.department_id = d.department_id  group by d.department_name) e where e.ams = (select min(avg(m.salary)) mms from employees w,employees m,departments d  where m.employee_id=w.manager_id and m.department_id = d.department_id  group by d.department_name);
    

32. 求薪水最高的前 5 名雇员

    select rownum ,last_name,salary from (select last_name,salary from employees order by salary desc)where rownum <6;
    

33. 求薪水最高的第 6 到第 10 名雇员

select last_name,salary from ( select rownum rn ,last_name,salary from (select last_name,salary from employees order by salary desc))where rn between 6 and 10;