创建企业人事数据库,并插入数据,编写Python代码,完成下述嵌入式SQL的查询要求。 1. 列出至少有四个员工的所有部门信息。 2. 列出所有员工的姓名及其直接上级的姓名,没有领导的雇员也列出。 3. 列出受雇日期早于其直接上级的所有员工编号/姓名/部门名称。 4. 列出最低薪金大于1500的各种工作及此从事此工作的全部雇员人数。 5. 列出在部门sales工作的员工的姓名,假定不知道销售部的部门编号。
时间: 2023-06-27 08:05:49 浏览: 283
以下是创建企业人事数据库的SQL语句:
```
CREATE TABLE department (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50) NOT NULL
);
CREATE TABLE employee (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(50) NOT NULL,
job_title VARCHAR(50) NOT NULL,
salary DECIMAL(10, 2) NOT NULL,
hire_date DATE NOT NULL,
manager_id INT,
dept_id INT NOT NULL,
FOREIGN KEY (manager_id) REFERENCES employee(emp_id),
FOREIGN KEY (dept_id) REFERENCES department(dept_id)
);
```
以下是插入数据的SQL语句:
```
INSERT INTO department (dept_id, dept_name) VALUES (1, 'sales');
INSERT INTO department (dept_id, dept_name) VALUES (2, 'marketing');
INSERT INTO department (dept_id, dept_name) VALUES (3, 'engineering');
INSERT INTO employee (emp_id, emp_name, job_title, salary, hire_date, manager_id, dept_id) VALUES (1, 'John Smith', 'sales rep', 2000.00, '2020-01-01', NULL, 1);
INSERT INTO employee (emp_id, emp_name, job_title, salary, hire_date, manager_id, dept_id) VALUES (2, 'Jane Doe', 'sales rep', 1800.00, '2020-02-01', 1, 1);
INSERT INTO employee (emp_id, emp_name, job_title, salary, hire_date, manager_id, dept_id) VALUES (3, 'Bob Johnson', 'marketing manager', 3000.00, '2019-01-01', NULL, 2);
INSERT INTO employee (emp_id, emp_name, job_title, salary, hire_date, manager_id, dept_id) VALUES (4, 'Alice Williams', 'engineer', 2500.00, '2018-01-01', NULL, 3);
INSERT INTO employee (emp_id, emp_name, job_title, salary, hire_date, manager_id, dept_id) VALUES (5, 'Tom Brown', 'engineer', 1800.00, '2019-01-01', 4, 3);
```
以下是Python代码,实现查询要求:
```
import sqlite3
conn = sqlite3.connect('company.db')
c = conn.cursor()
# 1. 列出至少有四个员工的所有部门信息
c.execute('SELECT department.dept_name FROM department JOIN employee ON department.dept_id = employee.dept_id GROUP BY department.dept_name HAVING COUNT(employee.emp_id) >= 4')
print(c.fetchall())
# 2. 列出所有员工的姓名及其直接上级的姓名,没有领导的雇员也列出
c.execute('SELECT e1.emp_name, e2.emp_name FROM employee e1 LEFT JOIN employee e2 ON e1.manager_id = e2.emp_id')
print(c.fetchall())
# 3. 列出受雇日期早于其直接上级的所有员工编号/姓名/部门名称
c.execute('SELECT e1.emp_id, e1.emp_name, department.dept_name FROM employee e1 JOIN employee e2 ON e1.manager_id = e2.emp_id JOIN department ON e1.dept_id = department.dept_id WHERE e1.hire_date < e2.hire_date')
print(c.fetchall())
# 4. 列出最低薪金大于1500的各种工作及此从事此工作的全部雇员人数
c.execute('SELECT job_title, COUNT(emp_id) FROM employee WHERE salary >= 1500.00 GROUP BY job_title')
print(c.fetchall())
# 5. 列出在部门sales工作的员工的姓名,假定不知道销售部的部门编号
c.execute('SELECT emp_name FROM employee WHERE dept_id = (SELECT dept_id FROM department WHERE dept_name = "sales")')
print(c.fetchall())
conn.close()
```
阅读全文