CREATE TABLE employees employee_id int6 NOT NULL DEFAULT 0 first_name varchar20 DEFAULT NULL last_name varchar25 NOT NULL email varchar25 NOT NULL phone_number varchar20 DEFAULT NULL hire_date

3、查询每个部门的平均工资和总工资

SELECT d.department_id, d.department_name, AVG(e.salary) as avg_salary, SUM(e.salary) as total_salary FROM employees e JOIN departments d ON e.department_id = d.department_id GROUP BY d.department_id, d.department_name

4、查询没有分配部门的员工信息

SELECT e.employee_id, e.first_name, e.last_name FROM employees e WHERE e.department_id IS NULL

5、查询每个部门的员工数量

SELECT d.department_id, d.department_name, COUNT(e.employee_id) as employee_count FROM employees e JOIN departments d ON e.department_id = d.department_id GROUP BY d.department_id, d.department_name

6、查询每个部门的经理姓名和联系电话

SELECT d.department_id, d.department_name, e.first_name, e.last_name, e.phone_number FROM departments d JOIN employees e ON d.manager_id = e.employee_id

7、查询每个部门的员工数量，并按照员工数量降序排序

SELECT d.department_id, d.department_name, COUNT(e.employee_id) as employee_count FROM employees e JOIN departments d ON e.department_id = d.department_id GROUP BY d.department_id, d.department_name ORDER BY employee_count DESC

8、查询每个部门的平均工资，并按照平均工资升序排序

SELECT d.department_id, d.department_name, AVG(e.salary) as avg_salary FROM employees e JOIN departments d ON e.department_id = d.department_id GROUP BY d.department_id, d.department_name ORDER BY avg_salary AS