select查询练习

查询练习

-- 1.查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述
select * from emp
select * from emp,job where emp.job_id = job.id
select emp.id,emp.ename,emp.salary,job.jname,description from emp,job where emp.job_id = job.id


-- 2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
select emp.id,emp.ename,emp.salary,job.jname,job.description,dept.dname,dept.loc
from emp,job,dept 
where emp.job_id = job.id and emp.dept_id = dept.id       -- 多表连接中where后外键用and

-- 3.查询员工姓名，工资，工资等级
emp.salary >= salarygrade.losalary and emp.salary <= salarygrade.hisalary

select emp.ename,emp.salary,salarygrade.grade from emp,salarygrade 
where emp.salary >= salarygrade.losalary and emp.salary <= salarygrade.hisalary

-- 4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
select emp.ename,emp.salary,job.jname,job.description,dept.dname,dept.loc,emp.bonus
from job,emp,dept
where emp.job_id = job.id and emp.dept_id = dept.id
-- 还可以换成显示外连接
-- from emp
-- inner join job on emp.job_id = job.id
-- inner join dept on dept_id = emp.dept_id
-- 


-- 5.查询出部门编号、部门名称、部门位置、部门人数
select dept.id,dept.dname,dept.loc,count(job.id) from dept,emp,job
where job.id = emp.job_id and emp.dept_id = dept.id