创建企业人事数据库,编写Python代码,完成下述嵌入式SQL的查询要求。 1. 列出至少有四个员工的所有部门信息。 2. 列出所有员工的姓名及其直接上级的姓名,没有领导的雇员也列出。 3. 列出受雇日期早于其直接上级的所有员工编号/姓名/部门名称。 4. 列出最低薪金大于1500的各种工作及此从事此工作的全部雇员人数。 5. 列出在部门sales工作的员工的姓名,假定不知道销售部的部门编号。
时间: 2023-11-27 15:54:12 浏览: 75
以下是Python代码实现嵌入式SQL查询:
```python
import sqlite3
# 连接企业人事数据库
conn = sqlite3.connect('enterprise.db')
# 查询1:列出至少有四个员工的所有部门信息
sql1 = '''
SELECT d.department_id, d.department_name, COUNT(*) AS employee_count
FROM employees e JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_id
HAVING COUNT(*) >= 4
ORDER BY d.department_id
'''
print('查询1结果:')
for row in conn.execute(sql1):
print(row)
# 查询2:列出所有员工的姓名及其直接上级的姓名,没有领导的雇员也列出
sql2 = '''
SELECT e.employee_name, m.employee_name AS manager_name
FROM employees e LEFT JOIN employees m ON e.manager_id = m.employee_id
ORDER BY e.employee_id
'''
print('查询2结果:')
for row in conn.execute(sql2):
print(row)
# 查询3:列出受雇日期早于其直接上级的所有员工编号/姓名/部门名称
sql3 = '''
SELECT e.employee_id, e.employee_name, d.department_name
FROM employees e JOIN departments d ON e.department_id = d.department_id
WHERE e.hire_date < (SELECT hire_date FROM employees WHERE employee_id = e.manager_id)
ORDER BY e.employee_id
'''
print('查询3结果:')
for row in conn.execute(sql3):
print(row)
# 查询4:列出最低薪金大于1500的各种工作及此从事此工作的全部雇员人数
sql4 = '''
SELECT e.job_title, COUNT(*) AS employee_count
FROM employees e JOIN (
SELECT job_title, MIN(salary) AS min_salary
FROM employees
GROUP BY job_title
HAVING MIN(salary) > 1500
) m ON e.job_title = m.job_title AND e.salary = m.min_salary
GROUP BY e.job_title
'''
print('查询4结果:')
for row in conn.execute(sql4):
print(row)
# 查询5:列出在部门sales工作的员工的姓名
sql5 = '''
SELECT employee_name
FROM employees
WHERE department_id = (
SELECT department_id
FROM departments
WHERE department_name = 'sales'
)
'''
print('查询5结果:')
for row in conn.execute(sql5):
print(row)
# 关闭数据库连接
conn.close()
```
阅读全文