SQL语法练习5

1.返回员工拥有的部门,员工信息含员工部门
2.查询员工薪资大于小薇的 员工信息
3.返回员工所属领导信息
4.返回入职时间早于领导 入职时间
5.返回从事财务工作的员工信息
6.求每个部门 最低员工薪资
7.返回员工薪资大于 平均薪资员工

CREATE TABLE dept (
  `dept_no` int NOT NULL COMMENT '部门id',
  `dept_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '部门名称',
  `dept_address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '部门地址',
  PRIMARY KEY (`dept_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
CREATE TABLE emp (
  `emp_number` int DEFAULT NULL COMMENT '员工编号',
  `emp_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '员工编号',
  `emp_post` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '员工职务',
  `emp_leader_number` int DEFAULT NULL COMMENT '员工领导编号',
  `emp_hiredate` datetime DEFAULT NULL COMMENT '员工入职时间',
  `emp_salary` double(10,0) DEFAULT NULL COMMENT '员工薪水',
  `emp_bonus` int DEFAULT NULL COMMENT '员工奖金',
  `emp_deptno` int DEFAULT NULL COMMENT '员工对外部门表外键'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

INSERT INTO dept (`dept_no`, `dept_name`, `dept_address`) VALUES ('1', '技术部门', '湖北武汉市');
INSERT INTO dept (`dept_no`, `dept_name`, `dept_address`) VALUES ('2', '财务部门', '中国上海市');
INSERT INTO dept (`dept_no`, `dept_name`, `dept_address`) VALUES ('3', '设计部门', '湖北孝感市');

INSERT INTO emp (`emp_number`, `emp_name`, `emp_post`, `emp_leader_number`, `emp_hiredate`, `emp_salary`, `emp_bonus`, `emp_deptno`) VALUES ('1001', '李薇', 'CEO', NULL, '2021-11-01 11:32:46', '20000', '10000', '1');
INSERT INTO emp (`emp_number`, `emp_name`, `emp_post`, `emp_leader_number`, `emp_hiredate`, `emp_salary`, `emp_bonus`, `emp_deptno`) VALUES ('1002', '小薇', 'CFO', '1001', '2021-10-01 11:32:46', '5000', '10000', '2');
INSERT INTO emp (`emp_number`, `emp_name`, `emp_post`, `emp_leader_number`, `emp_hiredate`, `emp_salary`, `emp_bonus`, `emp_deptno`) VALUES ('1004', '张三', 'CTO', NULL, '2021-11-01 11:32:46', '80000', '10000', '1');
INSERT INTO emp (`emp_number`, `emp_name`, `emp_post`, `emp_leader_number`, `emp_hiredate`, `emp_salary`, `emp_bonus`, `emp_deptno`) VALUES ('1005', '李四', '技术总监', '1004', '2021-11-01 11:32:46', '20000', '10000', '1');
INSERT INTO emp (`emp_number`, `emp_name`, `emp_post`, `emp_leader_number`, `emp_hiredate`, `emp_salary`, `emp_bonus`, `emp_deptno`) VALUES ('1006', '王麻子', '客服', NULL, '2022-03-02 11:49:45', '3500', NULL, NULL);

-- 1.返回员工拥有的部门,员工信息含员工部门
SELECT * FROM emp JOIN  dept ON dept.dept_no=emp.emp_deptno;

-- 2.查询员工薪资大于小薇的员工信息
SELECT * FROM emp WHERE emp.emp_salary>(SELECT emp_salary FROM emp WHERE emp.emp_number=1002);

-- 3.返回员工所属领导信息
SELECT * FROM emp as a,emp as b WHERE a.emp_leader_number=b.emp_number;

-- 4.返回入职时间早于领导的入职时间
SELECT * FROM emp as a, emp as b WHERE a.emp_number=b.emp_leader_number and a.emp_hiredate>b.emp_hiredate;

--5.返回从事财务工作的员工信息
SELECT * FROM emp JOIN dept ON emp.emp_deptno=dept.dept_no and dept.dept_no=2;

--6.求每个部门最低员工薪资
SELECT *,min(emp_salary) FROM emp JOIN dept ON emp.emp_deptno=dept.dept_no GROUP BY emp_deptno;

--7.返回员工薪资大于平均薪资员工
SELECT *FROM emp WHERE emp_salary>(SELECT AVG(emp_salary)FROM emp);