MySQL human resource management system

1. Design background

This exercise is mainly designed for human resource management system, including three tables: department table and employee table.
Department table is mainly used to store department name, address and other information
 Employee table is mainly used to store employee name, position, superior supervisor number, monthly salary, department number and other information

2. Table creation / data preparation

-- Creating database of human resource management system
drop database if exists HR;
create database HR default charset utf8;
-- Switch database context
use HR;
drop table if exists TbEmp;
drop table if exists TbDept;

-- Create department table
create table TbDept
(
deptno tinyint primary key, -- Department number
dname varchar(10) not null, -- Department name
dloc varchar(20) not null   -- Department location
);
-- Add Department record
insert into TbDept values (10, 'Accounting department', 'Beijing');
insert into TbDept values (20, 'R & D department', 'Chengdu');
insert into TbDept values (30, 'Sales Department', 'Chongqing');
insert into TbDept values (40, 'Operation and maintenance department', 'Shenzhen');

-- Create employee table
create table TbEmp
(
empno int primary key,      -- Employee number
ename varchar(20) not null, -- Employee name
job varchar(20) not null,   -- Employee positions
mgr int,            -- Supervisor number
sal int not null,       -- Monthly salary of employees
dno tinyint         -- Department No
);

-- Add foreign key constraint
alter table TbEmp add constraint fk_dno foreign key (dno) references TbDept(deptno);

-- Add employee record
insert into TbEmp values (7800, 'Zhang Sanfeng', 'CEO', null, 9000, 20);
insert into TbEmp values (2056, 'Qiao Feng', 'analyst', 7800, 5000, 20);
insert into TbEmp values (3088, 'Li Mo worry', 'Designer', 2056, 3500, 20);
insert into TbEmp values (3211, 'Zhang Wuji', 'Programmer', 2056, 3200, 20);
insert into TbEmp values (3233, 'Qiu Chu Ji', 'Programmer', 2056, 3400, 20);
insert into TbEmp values (3251, 'Zhang Cu Shan', 'Programmer', 2056, 4000, 20);
insert into TbEmp values (5566, 'Song Yuan Qiao', 'Accountant', 7800, 4000, 10);
insert into TbEmp values (5234, 'Guo Jing', 'Cashier', 5566, 2000, 10);
insert into TbEmp values (3344, 'Huang Rong', 'Sales Supervisor', 7800, 3000, 30);
insert into TbEmp values (1359, 'Hu Yi Dao', 'Salesperson', 3344, 1800, 30);
insert into TbEmp values (4466, 'Miao Ren Feng', 'Salesperson', 3344, 2500, 30);
insert into TbEmp values (3244, 'Ouyang Feng', 'Programmer', 3088, 3200, 20);
insert into TbEmp values (3577, 'Yang Guo', 'accounting', 5566, 2200, 10);
insert into TbEmp values (3588, 'Zhu nine Zhen', 'accounting', 5566, 2500, 10);

3. Exercises

1) Query the name and salary of the employee with the highest salary

select ename, sal from TbEmp where sal=(select max(sal) from TbEmp);

2) Query employee's name and annual salary (monthly salary * 12)

select ename, sal*12 as annSal from TbEmp;

3) Query the number and number of departments with employees

select dno, count(dno) from TbEmp group by dno;

4) Query the name and number of all departments

select dname, ifnull(total, 0) from TbDept t1 left outer join
(select dno, count(dno) as total from TbEmp group by dno) t2
on deptno=dno;

5) Query the name and salary of the highest paid employee (except Boss)

select ename, sal from TbEmp where sal=(select max(sal) from TbEmp where mgr is not null);

6) Query the name and salary of employees whose salary exceeds the average salary

select ename, sal from TbEmp where sal>(select avg(sal) from TbEmp);

7) Query the name, department number and salary of employees whose salary exceeds the average salary of their department

-- Writing method1:
select ename, sal, t1.dno from TbEmp as t1,
(select dno, avg(sal) as avgSal from TbEmp group by dno) as t2 
where t1.dno=t2.dno and t1.sal>t2.avgSal;

-- Writing method2:
select ename, sal, t1.dno from TbEmp as t1 inner join
(select dno, avg(sal) as avgSal from TbEmp group by dno) as t2 
on t1.dno=t2.dno and t1.sal>t2.avgSal;

8) Query the name of the highest paid person, salary and department

select ename, sal, dname from TbDept as t1 inner join
(select ename, sal, t2.dno from TbEmp as t2 inner join
(select dno, max(sal) as maxSal from TbEmp group by dno) t3
on t2.dno=t3.dno and sal=maxSal) as t4 on deptno=dno;

9) Check the supervisor's name and position

select ename, job from TbEmp where empno in 
(select distinct mgr from TbEmp where mgr is not null);

10) Query the name and salary of the top 3 employees

select ename, sal from TbEmp order by sal desc limit 3;
select ename, sal from TbEmp order by sal desc limit 0,3;
select ename, sal from TbEmp order by sal desc limit 3 offset 0;

11) No. 4-8 employees for salary

select ename, sal from TbEmp order by sal desc limit 3,5;
select ename, sal from TbEmp order by sal desc limit 5 offset 3;