【无标题】

Sql 练习

题目：准备的数据以及题目

-- 1.查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述
select e.id , e.ename ,e.salary , j.jname , j.description
from emp e, job j
where e.job_id = j.id;
-- 2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
select e.id , e.ename ,e.salary , j.jname , j.description,d.dname,d.loc
from emp e, job j,dept d
where e.job_id = j.id and e.dept_id = d.id;
-- 3.查询员工姓名，工资，工资等级
select  e.ename ,e.salary , s.grade
from emp e, salarygrade s
where e.salary between s.losalary and s.hisalary;

-- 4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
select  e.ename ,e.salary ,j.jname , j.description,d.dname, d.loc,s.grade
from emp e, salarygrade s,job j,dept d
where e.salary between s.losalary and s.hisalary and e.job_id = j.id and e.dept_id = d.id;
-- 5.查询出部门编号，部门名称，部门位置，部门人数

select d.id '部门编号',
       d.dname '部门名称',
			 d.loc '部门位置', 
			 c.c '部门人数'
from dept d,(select dept_id,count(id) c from emp group by dept_id) c
where d.id=c.dept_id;
-- 6.查询所有员工的姓名及直接上级的姓名，没有领导的员工也需要查询
select e.ename as "员工" ,e2.ename as "领导"
from emp e left join emp e2 
on e.mgr = e2.id;