Mysql系列基础篇-SQL分类（多表的DQL）的练习

部门表

create table dept(id int primary key auto_increment,dname varhcar(50),address varchar(50));

insert into dept(dname,address) values
('教研部','北京'),
('学工部','上海'),
('销售部','广州'),
('财务部','深圳');

员工表

create table emp(id int primary key auto_increment,ename varchar(50),job_id int,mgr int,joindate date,salary decimal(7,2),bonus decimal(7,2),dept_id int,
constraint emp_job_fk foreign key (job_id) references job(id),
constraint emp_dep_fk foreign key(dept_id) references dept(id);

insert into emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) values
(1001,'孙悟空',4,1004,'2000-12-17','8000.00',null,2),
(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',3),
(1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',3),
(1004,'唐僧',2,1009,'2001-04-02','29750.00',null,2),
(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',3),
(1006,'宋江',2,1009,'2001-05-01','28500.00',null,3),
(1007,'刘备',2,1009,'2001-09-01','24500.00',null,1),
(1008,'猪八戒',4,1004,'2007-04-19','30000.00',null,2),
(1009,'罗贯中',1,null,'2001-11-17','50000.00',null,1),
(1010,'吴用',3,1006,'2001-09-06','15000.00','0.00',3),
(1011,'沙僧',4,1004,'2007-05-23','11000.00',null,3),
(1012,'李逵',4,1006,'2001-12-03','9500.00',null,3),
(1013,'小白龙',4,1006,'2001-12-03','3000.00',null,2),
(1014,'关羽',4,1007,'2002-01-23','1300.00',null,1);

职位表

create table job(id int primary key auto_increment,jname varchar(20),des varchar(50));

insert into job(jname,des) values
('董事长','管理整个公司，接单'),
('经理','管理部门员工'),
('销售人员','向客人推销产品'),
('文员','使用办公软件');

薪资等级表

create table salarygrade(grade int primary key,losalary int,hisalary int);

insert into salarygrade(grade,losalary,hisalary) values
(1,7000,12000),
(2,12000,14000),
(3,14000,20000),
(4,20000,32000),
(5,32000,39000);

需求：
1:查询所有员工信息，查询员工编号，员工姓名，工资，职务名称，职务描述

select t1.id,t1.ename,t1.salary,t2.jname,t2.des 
from 
emp t1,
job t2  
where t1.job_id = t2.id;

2:查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置

select t1.id,t1.ename,t1.salary,t2.jname,t2.des,t3.dname,t3.address from
emp t1,
job t2,
dept t3  
where t1.job_id = t2.id and t1.dept_id=t3.id;

3:查询员工姓名，工资，工资等级

select t1.ename,t1.salary,t2.grade 
from 
emp t1,
salarygrade t2 
where t1.salary between t2.losalary and t2.hisalary;

4:查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级

select t1.ename,t1.salary,t4.grade,t2.jname,t2.des,t3.dname,t3.address from 
emp t1,
job t2,
dept t3,
salarygrade t4  
where 
t1.job_id = t2.id and  t1.dept_id=t3.id and t1.salary between t4.losalary and t4.hisalary;

5:查询部门编号，部门名称，部门位置，部门人数

select t1.id,t1.dname,t1.address ,t2.total 
from 
dept t1,
(select dept_id,count(id) total from emp group by dept_id) t2 
where 
t1.id = t2.dept_id;

6:查询所有员工的姓名及其直接上级的姓名，没有领导的员工也要查询

select t1.ename,t1.mgr,t2.id,t2.ename 
from 
emp t1,
emp t2 
where t1.id = t2.mgr;