查询在“IT”部门工作且工资高于该部门平均工资的员工信息 分数 3

要查询在"IT"部门工作且工资高于该部门平均工资的员工信息，你需要结合两个查询，首先计算IT部门的平均工资，然后使用这个平均值作为条件筛选员工。这通常涉及两个步骤：

1. 计算IT部门的平均工资：

   SELECT AVG(salary) as avg_salary
   FROM employees
   WHERE department = 'IT';

2. 使用平均工资查询符合条件的员工：

   SELECT *
   FROM employees
   WHERE department = 'IT'
   AND salary > (SELECT AVG(salary) FROM employees WHERE department = 'IT');

如果在编程中，例如使用Python with SQLAlchemy ORM:

from sqlalchemy import create_engine, select

engine = create_engine('sqlite:///your_database.db')

with engine.connect() as connection:
    # 计算IT部门的平均工资
    avg_salary_query = select([func.avg(employees.salary)]).where(employees.department == 'IT').correlate_except(employees)
    avg_salary = connection.execute(avg_salary_query).scalar()

    # 查询工资高于平均工资的员工
    high_salary_employees = connection.execute(
        select(employees)
        .where(employees.department == 'IT')
        .and_(employees.salary > avg_salary)
    )
    
high_salary_employees.all()