4. MySQL多表查询练习题

 数据库表

CREATE TABLE departments (
    dept_no     CHAR(4)         PRIMARY KEY COMMENT '部门编码',
    dept_name   VARCHAR(40)     NOT NULL UNIQUE COMMENT '部门名称'
) COMMENT '部门表';

CREATE TABLE dept_emp (
    emp_no      INT             NOT NULL COMMENT '部门编码',
    dept_no     CHAR(4)         NOT NULL COMMENT '雇员ID',
    FOREIGN KEY (emp_no)  REFERENCES employees (emp_no)    ON DELETE CASCADE,
    FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE,
    PRIMARY KEY (emp_no,dept_no)
) COMMENT '雇员与部门关系表';

CREATE TABLE salaries (
    emp_no      INT             NOT NULL COMMENT '雇员ID',
    salary      INT             NOT NULL COMMENT '工资',
    month       INT             NOT NULL COMMENT '月份',
    level       INT             NOT NULL COMMENT '工资等级',
    FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE
) COMMENT '薪资表'; 

-- 向雇员表中插入数据
insert into employees values
('100','1990-08-19','JACK','M','20160811'),
('101','1970-08-12','TOM','M','20100606'),
('102','1996-03-19','JAMES','M','20140101'),
('103','1987-04-28','KETTY','F','20130910'),
('104','1983-05-19','JIM','F','20160418');

-- 向部门表中插入数据
insert into departments values
('1001','A'),
('1002','B'),
('1003','C'),
('1004','D');

-- 向部门与雇员关系表中插入数据
insert into dept_emp values
('100','1001'),
('101','1001'),
('102','1002'),
('103','1003'),
('104','1004');

-- 想工资表中插入数据
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);

1.查看部门表中的所有记录

select * from departments

2.查看员工表中的所有记录

select * from employees

3.查询所有员工所属的部门

select a.emp_no,a.name,b.dept_no,b.dept_name from employees a

          left join dept_emp b on a.emp_no = b.emp_no

          left join departments c on c.dept_no = a.dept_no

4.查询属于A部门的员工

select a.name from employees a

          left join dept_emp b on a.emp_no = b.emp_no

          left join departments c on c.dept_no = b.dept_no where c.dept_name = 'A'

5.查询属于A部门员工的每月平均工资

SELECT AVG(salary),d.month FROM employees a

              LEFT JOIN dept_emp b ON a.emp_no = b.emp_no

              LEFT JOIN departments c ON c.dept_no = b.dept_no

              LEFT JOIN salaries d ON a.emp_no = d.emp_no WHERE c.dept_name = 'A' group by d.month

6.查询所有员工的薪水总和

select sum(salary) from salaries

7.查询各个部门的员工平均工资

select avg(a.salary),b.dept_no from salaries a   join dept_emp b on a.emp_no = b.emp_no group by b.dept_no

8.查询不同工资水平下各有多少员工

SELECT COUNT(DISTINCT emp_no),LEVEL FROM salaries GROUP BY LEVEL

9.查询名字中包含M的员工的每月平均工资

SELECT AVG(salary),MONTH FROM salaries a

              LEFT JOIN employees b ON a.emp_no = b.emp_no WHERE b.name LIKE '%M%' GROUP BY MONTH

10.查询所有女性中工资最高的员工

select distinct b.name,b.emp_no from salaries a

           left join employees b on a.emp_no = b.emp_no where salary = ( select max(salary) from salaries a left join employees b on a.emp_no = b.emp_no where b.gender = 'F') and b.gender = 'F'

11.查询每月员工工资之和大于30000的部门有哪些

SELECT SUM(a.salary),b.dept_no,a.month,c.dept_name FROM salaries a

               JOIN dept_emp b ON a.emp_no = b.emp_no

               LEFT JOIN departments c ON c.dept_no = b.dept_no GROUP BY b.dept_no,a.month

               HAVING SUM(a.salary) > 30000