创建了一个员工信息数据库，创建了一个部门信息表，创建了一个员工信息表，列出在部门sales工作的员工的姓名，假定不知道销售部的部门编号。

假设部门信息表名为"departments"，员工信息表名为"employees"，其中员工信息表中有一个"department_id"列用于表示员工所在的部门编号。

您可以使用以下SQL查询来列出在销售部门工作的员工姓名：

SELECT employees.employee_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id
WHERE departments.department_name = 'sales';

这个查询语句使用了INNER JOIN将两个表连接起来，然后使用WHERE子句来限制只查询部门名称为"sales"的员工信息。最后，SELECT子句只选择了员工姓名这一列。

相关问题

1. 在sales数据库中创建视图v_seller：列出销售员的编号、姓名、性别、地址。

可以使用以下SQL语句在sales数据库中创建视图v_seller：

CREATE VIEW v_seller AS
SELECT seller_id, seller_name, seller_gender, seller_address
FROM seller;

这里假设sales数据库中有一个名为seller的表，其中包含销售员的信息，包括seller_id、seller_name、seller_gender和seller_address列。执行以上SQL语句后，就可以通过查询v_seller视图来获取销售员的编号、姓名、性别和地址信息。

，编写Python代码，完成下述嵌入式SQL的查询要求。 0.创建企业人事数据库1. 列出至少有四个员工的所有部门信息。 2. 列出所有员工的姓名及其直接上级的姓名，没有领导的雇员也列出。 3. 列出受雇日期早于其直接上级的所有员工编号/姓名/部门名称。 4. 列出最低薪金大于1500的各种工作及此从事此工作的全部雇员人数。 5. 列出在部门sales工作的员工的姓名，假定不知道销售部的部门编号。

# 导入必要的模块
import sqlite3

# 连接数据库
conn = sqlite3.connect('company.db')
cursor = conn.cursor()

# 创建表
cursor.execute('''
CREATE TABLE IF NOT EXISTS department (
    dept_no TEXT PRIMARY KEY,
    dept_name TEXT NOT NULL,
    location TEXT
)
''')

cursor.execute('''
CREATE TABLE IF NOT EXISTS employee (
    emp_no INTEGER PRIMARY KEY,
    emp_name TEXT NOT NULL,
    job TEXT NOT NULL,
    mgr_no INTEGER,
    hire_date DATE NOT NULL,
    salary INTEGER NOT NULL,
    dept_no TEXT NOT NULL,
    FOREIGN KEY (dept_no) REFERENCES department(dept_no),
    FOREIGN KEY (mgr_no) REFERENCES employee(emp_no)
)
''')

# 插入数据
cursor.execute("INSERT INTO department VALUES ('d001', 'Marketing', 'Tokyo')")
cursor.execute("INSERT INTO department VALUES ('d002', 'Finance', 'Osaka')")
cursor.execute("INSERT INTO department VALUES ('d003', 'Sales', 'Kyoto')")
cursor.execute("INSERT INTO department VALUES ('d004', 'IT', 'Tokyo')")

cursor.execute("INSERT INTO employee VALUES (1001, 'John', 'Manager', NULL, '2000-01-01', 3000, 'd001')")
cursor.execute("INSERT INTO employee VALUES (1002, 'Mary', 'Manager', NULL, '2001-01-01', 4000, 'd002')")
cursor.execute("INSERT INTO employee VALUES (1003, 'Tom', 'Salesman', 1001, '2002-01-01', 2000, 'd001')")
cursor.execute("INSERT INTO employee VALUES (1004, 'Jane', 'Clerk', 1001, '2003-01-01', 1500, 'd001')")
cursor.execute("INSERT INTO employee VALUES (1005, 'Peter', 'Salesman', 1001, '2004-01-01', 2500, 'd001')")
cursor.execute("INSERT INTO employee VALUES (1006, 'David', 'Clerk', 1002, '2005-01-01', 1200, 'd002')")
cursor.execute("INSERT INTO employee VALUES (1007, 'Lucy', 'Clerk', 1002, '2006-01-01', 1300, 'd002')")
cursor.execute("INSERT INTO employee VALUES (1008, 'Jack', 'Programmer', 1002, '2007-01-01', 2000, 'd004')")
cursor.execute("INSERT INTO employee VALUES (1009, 'Rose', 'Manager', 1001, '2008-01-01', 5000, 'd003')")
cursor.execute("INSERT INTO employee VALUES (1010, 'Bob', 'Salesman', 1009, '2009-01-01', 1800, 'd003')")
cursor.execute("INSERT INTO employee VALUES (1011, 'Judy', 'Salesman', 1009, '2010-01-01', 1900, 'd003')")
cursor.execute("INSERT INTO employee VALUES (1012, 'Mike', 'Clerk', 1009, '2011-01-01', 1400, 'd003')")

# 提交更改
conn.commit()

# 查询要求1
cursor.execute('''
SELECT department.dept_name, COUNT(employee.emp_no) AS num_of_employees
FROM department JOIN employee ON department.dept_no = employee.dept_no
GROUP BY department.dept_name
HAVING COUNT(employee.emp_no) >= 4
''')
print("At least 4 employees in each department:")
for row in cursor.fetchall():
    print(row)

# 查询要求2
cursor.execute('''
SELECT e1.emp_name, e2.emp_name AS manager_name
FROM employee e1 LEFT JOIN employee e2 ON e1.mgr_no = e2.emp_no
''')
print("Employee names and their managers' names:")
for row in cursor.fetchall():
    print(row)

# 查询要求3
cursor.execute('''
SELECT e1.emp_no, e1.emp_name, d1.dept_name
FROM employee e1 JOIN employee e2 ON e1.mgr_no = e2.emp_no
JOIN department d1 ON e1.dept_no = d1.dept_no
WHERE e1.hire_date < e2.hire_date
''')
print("Employees hired before their managers:")
for row in cursor.fetchall():
    print(row)

# 查询要求4
cursor.execute('''
SELECT job, COUNT(emp_no) AS num_of_employees
FROM employee
GROUP BY job
HAVING MIN(salary) > 1500
''')
print("Jobs with minimum salary > 1500 and the number of employees in the job:")
for row in cursor.fetchall():
    print(row)

# 查询要求5
cursor.execute('''
SELECT emp_name
FROM employee JOIN department ON employee.dept_no = department.dept_no
WHERE dept_name = 'Sales'
''')
print("Employee names who work in the sales department:")
for row in cursor.fetchall():
    print(row)

# 关闭连接
conn.close()