SQL基础练习01--部门员工信息查找

最新推荐文章于 2022-10-08 00:22:10 发布

windy杨树

最新推荐文章于 2022-10-08 00:22:10 发布

阅读量4.5k

点赞数 1

本文链接：https://blog.csdn.net/wuwu12dfda/article/details/84970408

版权

这篇博客通过一系列的SQL查询实例，涵盖了从查找最晚入职员工、入职第三晚员工，到薪资变动、部门领导薪水、员工姓名、涨薪次数等全方位的员工信息查询。内容覆盖了员工入职时间、当前薪水、部门分配、职位级别等多个维度。

摘要由CSDN通过智能技术生成

0 测评数据

 1 -- 员工信息表
 2 CREATE TABLE `employees` (
 3 `emp_no` INT(11) NOT NULL,
 4 `birth_date` DATE NOT NULL,
 5 `first_name` VARCHAR(14) NOT NULL,
 6 `last_name` VARCHAR(16) NOT NULL,
 7 `gender` CHAR(1) NOT NULL,
 8 `hire_date` DATE NOT NULL,
 9 PRIMARY KEY (`emp_no`));
10 
11 INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
12 INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
13 INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');
14 INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');
15 INSERT INTO employees VALUES(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12');
16 INSERT INTO employees VALUES(10006,'1953-04-20','Anneke','Preusig','F','1989-06-02');
17 INSERT INTO employees VALUES(10007,'1957-05-23','Tzvetan','Zielinski','F','1989-02-10');
18 INSERT INTO employees VALUES(10008,'1958-02-19','Saniya','Kalloufi','M','1994-09-15');
19 INSERT INTO employees VALUES(10009,'1952-04-19','Sumant','Peac','F','1985-02-18');
20 INSERT INTO employees VALUES(10010,'1963-06-01','Duangkaew','Piveteau','F','1989-08-24');
21 INSERT INTO employees VALUES(10011,'1953-11-07','Mary','Sluis','F','1990-01-22');

 1 -- 部门分配信息表
 2 CREATE TABLE `dept_emp` (
 3 `emp_no` INT(11) NOT NULL,
 4 `dept_no` CHAR(4) NOT NULL,
 5 `from_date` DATE NOT NULL,
 6 `to_date` DATE NOT NULL,
 7 PRIMARY KEY (`emp_no`,`dept_no`));
 8 
 9 INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01'); 
10 INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
11 INSERT INTO dept_emp VALUES(10003,'d004','1995-12-03','9999-01-01');
12 INSERT INTO dept_emp VALUES(10004,'d004','1986-12-01','9999-01-01');
13 INSERT INTO dept_emp VALUES(10005,'d003','1989-09-12','9999-01-01');
14 INSERT INTO dept_emp VALUES(10006,'d002','1990-08-05','9999-01-01');
15 INSERT INTO dept_emp VALUES(10007,'d005','1989-02-10','9999-01-01');
16 INSERT INTO dept_emp VALUES(10008,'d005','1998-03-11','2000-07-31');
17 INSERT INTO dept_emp VALUES(10009,'d006','1985-02-18','9999-01-01');
18 INSERT INTO dept_emp VALUES(10010,'d005','1996-11-24','2000-06-26');
19 INSERT INTO dept_emp VALUES(10010,'d006','2000-06-26','9999-01-01');

 1 -- 部门经理表
 2 CREATE TABLE `dept_manager` (
 3 `dept_no` CHAR(4) NOT NULL,
 4 `emp_no` INT(11) NOT NULL,
 5 `from_date` DATE NOT NULL,
 6 `to_date` DATE NOT NULL,
 7 PRIMARY KEY (`emp_no`,`dept_no`));
 8 
 9 INSERT INTO dept_manager VALUES('d001',10002,'1996-08-03','9999-01-01');
10 INSERT INTO dept_manager VALUES('d002',10006,'1990-08-05','9999-01-01');
11 INSERT INTO dept_manager VALUES('d003',10005,'1989-09-