查询每个部门中入职日期一致的员工信息

INSERT into worker values (10004,‘孙大圣2’,‘1986-02-12’,‘女’,‘2019-06-23’,‘销售部’)
INSERT into worker values (10005,‘孙大圣3’,‘1968-08-12’,‘男’,‘2017-06-23’,‘销售部’)
INSERT into worker values (10006,‘孙大圣4’,‘1988-06-12’,‘女’,‘2014-06-23’,‘销售部’)
INSERT into worker values (10007,‘孙大圣5’,‘1998-07-12’,‘女’,‘2018-06-23’,‘财务部’)
INSERT into worker values (10008,‘孙大圣6’,‘1999-10-12’,‘男’,‘2019-06-23’,‘财务部’)
INSERT into worker values (10009,‘孙大圣7’,‘1988-01-12’,‘女’,‘2014-06-23’,‘财务部’)
INSERT into worker values (100010,‘孙大圣8’,‘1975-08-12’,‘女’,‘2018-06-23’,‘财务部’)
INSERT into worker values (100011,‘孙大圣9’,‘1997-12-12’,‘男’,‘2017-06-23’,‘财务部’)
INSERT into worker values (100012,‘孙大圣10’,‘1997-04-12’,‘男’,‘2017-06-23’,‘行政部’)
INSERT into worker values (100013,‘孙大圣11’,‘1998-05-12’,‘女’,‘2016-06-23’,‘行政部’)
INSERT into worker values (100014,‘孙大圣12’,‘1996-08-12’,‘男’,‘2019-06-23’,‘行政部’)
INSERT into worker values (100015,‘孙大圣13’,‘1997-04-12’,‘女’,‘2018-06-23’,‘行政部’)
INSERT into worker values (100016,‘孙大圣14’,‘1988-08-12’,‘男’,‘2016-06-23’,‘行政部’)
INSERT into worker values (100017,‘孙大圣15’,‘1998-09-12’,‘女’,‘2019-06-23’,‘行政部’)

1. 使用内连接，查询所有员工的信息
2. 查询某一员工所在部门的主管信息
3. 查询销售部大于30岁的员工信息
4. 查询每个部门中年龄最小的员工
5. 查询每个部门中入职日期一致的员工信息

1. 使用内连接，查询所有员工的信息

SELECT
	*
FROM
	department,
	worker
WHERE
	department.department = worker.worker_department

2. 查询某一员工所在部门的主管信息

SELECT
	worker.worker_name,
	department.mangager,
	worker.worker_department
FROM
	department,
	worker
WHERE
	department.department = worker.worker_department

3. 查询销售部大于30岁的员工信息

SELECT
	*
FROM
	worker w
WHERE
	w.worker_department = '销售部'
HAVING
	w.worker_date < '1992-4-1'

4. 查询每个部门中年龄最小的员工

方法一：

SELECT
	*
FROM
	worker w
WHERE
	NOT EXISTS (
		SELECT
			1
		FROM
			worker
		WHERE
			worker_department = w.worker_department
		AND worker_date > w.worker_date
	)

– 方法二：

SELECT
	*
FROM
	worker
WHERE
	worker_date IN (
		SELECT
			MAX(worker_date)
		FROM
			worker
		GROUP BY
			worker_department
	)

5. 查询每个部门中入职日期一致的员工信息

SELECT
	*
FROM
	worker
WHERE
	(
		worker_time,
		worker_department
	) IN (
		SELECT
			worker_time,
			worker_department
		FROM
			worker
		GROUP BY
			worker_department,
			worker_time
		HAVING
			COUNT(*) > 1
	)
ORDER BY
	worker_time