子查询练习题

子查询练习题

员工表， 部门表， 工资表

-- 第一题 
create table departments(
dept_no char(4) primary key ,
dept_name1 varchar(40) not null unique
)

create table employees(
emp_no int primary key auto_increment,
birth_date DATE not null,
name varchar(14) not null,
hire_date DATE not null
)

create table salaries(
emp_no int  not null,
salary int not null,
month int not null,
level int not null,
constraint emp_id_fk foreign key(emp_no) references employees(emp_no)
)

create table dept_emp(
emp1_no int not null,
dept1_no char(4) not null,
constraint emp1_id_fk foreign key(emp1_no) references employees(emp_no),
constraint dept1_id_fk foreign key(dept1_no) references departments(dept_no)
)

-- 第二题
insert into departments values('1001','A')
insert into departments values('1002','B')
insert into departments values('1003','C')
insert into departments values('1004','D')

-- 第三题

insert into employees values('100','1990-08-19','JACK','20160811')
insert into employees values('101','1970-08-12','TOM','20100606')
insert into employees values('102','1996-03-19','JAMES','20140101')
insert into employees values('103','1987-04-28','KETTY','20130910')
insert into employees values('104','1983-05-19','JIM','20160418')

-- 第四题
insert into salaries values('100','12000','201601',2),('101','9000','201601',1),('102','90000','201601',10),('103','2300','201601',1),('104','4000','201601',1),('100','12000','201602',2),('101','9000','201602',1),('102','90000','201602',10),('103','2300','201602',1),('104','4000','201602',1),('100','12000','201603',2),('101','9000','201603',1),('102','90000','201603',10),('103','2300','201603',1),('104','4000','201603',1),('100','12000','201604',2),('101','9000','201604',1),('102','90000','201604',10),('103','2300','201604',1),('104','4000','201604',1)

-- 第五题
insert into dept_emp values('100','1001')
insert into dept_emp values('101','1001')
insert into dept_emp values('102','1002')
insert into dept_emp values('103','1003')
insert into dept_emp values('104','1004')

-- 第六题 分别查看部门表和员工表中的所有记录
select * from departments
select * from employees

-- 第七题 将雇员表按照出生日期进行降序排列，并找到前三条数据

select birth_date from employees order by birth_date desc limit 3

-- 第八题 查询2016年1月份的工资平均值
select avg(salary) from salaries where month='201601'

select sum(salary)/count(201601) from salaries

-- 第九题 查询雇员ID小于103且名字以J开头的员工信息
select * from employees where emp_no <103 and name like'J%'

-- 第十题 查询雇员ID在101-103之间的员工信息（两种方式）
select * from employees where emp_no>=101 and emp_no<=103
select * from employees where emp_no in(101,102,103)
select * from employees where emp_no between 101 and 103

-- 第十一题 查询所有名字以M结尾的雇员信息，并按照入职时间进行倒叙排序
select * from employees where  name like'%M' order by birth_date desc

-- 第十二题 .查询每个员工所属的部门

select * from employees e LEFT JOIN dept_emp d on e.emp_no=d.emp1_no LEFT JOIN departments d1 on d.dept1_no=d1.dept_no;
select d1.dept_name1,e.name from employees e LEFT JOIN dept_emp d on e.emp_no=d.emp1_no LEFT JOIN departments d1 on d.dept1_no=d1.dept_no;

-- 第十三题  查询属于A部门的员工
select * from employees where emp_no in(select emp1_no from dept_emp where dept1_no=(select dept_no from departments where dept_name1='A'))

-- 第十四题 查询属于A部门员工的平均工资

select Avg(salary) from salaries where emp_no in(select emp_no from employees where emp_no in(select emp1_no from dept_emp where dept1_no=(select dept_no from departments where dept_name1='A')))

-- 第十五题 查询所有员工的薪水总和

select * from employees e  LEFT JOIN dept_emp d ON e.emp_no=d.emp1_no INNER JOIN departments d1 ON d.dept1_no=d1.dept_no INNER JOIN salaries s ON s.emp_no=e.emp_no; 

select SUM(salary) from salaries;

-- 第十六题 查询各个部门的员工平均工资

select AVG(s.salary) from departments d LEFT JOIN dept_emp d1 ON d.dept_no=d1.dept1_no LEFT JOIN salaries s ON d1.emp1_no=s.emp_no GROUP BY d.dept_name1

select * from departments d LEFT JOIN dept_emp d1 ON d.dept_no=d1.dept1_no LEFT JOIN salaries s ON d1.emp1_no=s.emp_no GROUP BY d.dept_name1