列出薪金高于公司平均薪资的所有员工 (2)列出与"周八"从事相同工作的所有员工 (3)列出薪资等于部门30中员工的薪资的所有员工的姓名和薪金 (4)列出薪金高于在部门30工作的所有员工的薪资的员工姓名和薪资 (5)查询出最早工作的那个人的名字、入职时间和薪水
时间: 2023-06-15 10:03:09 浏览: 81
对这些问题,需要查询数据库中的员工表(employees)和薪资表(salaries),以及工作表(titles)和部门表(departments)。
(1)列出薪金高于公司平均薪资的所有员工:
```sql
SELECT e.emp_no, e.first_name, e.last_name, s.salary
FROM employees e
INNER JOIN salaries s ON e.emp_no = s.emp_no
WHERE s.salary > (SELECT AVG(salary) FROM salaries)
```
(2)列出与"周八"从事相同工作的所有员工:
```sql
SELECT e.emp_no, e.first_name, e.last_name, t.title
FROM employees e
INNER JOIN titles t ON e.emp_no = t.emp_no
WHERE t.title IN (
SELECT t.title
FROM employees e
INNER JOIN titles t ON e.emp_no = t.emp_no
WHERE e.first_name = '周八'
) AND e.first_name <> '周八'
```
(3)列出薪资等于部门30中员工的薪资的所有员工的姓名和薪金:
```sql
SELECT e.first_name, e.last_name, s.salary
FROM employees e
INNER JOIN salaries s ON e.emp_no = s.emp_no
WHERE s.salary = (
SELECT s2.salary
FROM employees e2
INNER JOIN salaries s2 ON e2.emp_no = s2.emp_no
INNER JOIN dept_emp de ON e2.emp_no = de.emp_no
WHERE de.dept_no = 'd003'
ORDER BY s2.salary DESC
LIMIT 1
)
```
(4)列出薪金高于在部门30工作的所有员工的薪资的员工姓名和薪资:
```sql
SELECT e.first_name, e.last_name, s.salary
FROM employees e
INNER JOIN salaries s ON e.emp_no = s.emp_no
INNER JOIN dept_emp de ON e.emp_no = de.emp_no
WHERE s.salary > (
SELECT s2.salary
FROM employees e2
INNER JOIN salaries s2 ON e2.emp_no = s2.emp_no
INNER JOIN dept_emp de2 ON e2.emp_no = de2.emp_no
WHERE de2.dept_no = 'd003'
ORDER BY s2.salary DESC
LIMIT 1
) AND de.dept_no <> 'd003'
```
(5)查询出最早工作的那个人的名字、入职时间和薪水:
```sql
SELECT e.first_name, e.last_name, s.salary, e.hire_date
FROM employees e
INNER JOIN salaries s ON e.emp_no = s.emp_no
WHERE e.hire_date = (
SELECT MIN(hire_date)
FROM employees
)
LIMIT 1
```
相关推荐
![application/x-rar](https://img-home.csdnimg.cn/images/20210720083606.png)
![doc](https://img-home.csdnimg.cn/images/20210720083327.png)
![doc](https://img-home.csdnimg.cn/images/20210720083327.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![docx](https://img-home.csdnimg.cn/images/20210720083331.png)