第二模块：join 、聚合、窗口简单练习 (使用 employee 员工表；employee_address 员工住址信息表； employee_connection 员工联系方式表； 表) -- 10. 求薪水在2000-4000元的员工信息(员工工号，职位，部门，电话，邮箱，街道，城市) -- 分别使用普通join和CTE两种语法实现 -- 11. 求按照城市分布的平均薪资和总薪资，并按照城市内的总薪资倒序排序，只取每个城市总薪资的top1 -- 12. 按照部门划分，求每个部门下员工所在城市数量最多的城市信息。

10. 使用普通join语法实现：

SELECT e.emp_id, e.job_title, e.department, ec.phone_number, ec.email, ea.street, ea.city 
FROM employee e 
JOIN employee_connection ec ON e.emp_id = ec.emp_id 
JOIN employee_address ea ON e.emp_id = ea.emp_id 
WHERE e.salary BETWEEN 2000 AND 4000;

使用CTE语法实现：

WITH emp_info AS (
    SELECT e.emp_id, e.job_title, e.department, ec.phone_number, ec.email, ea.street, ea.city 
    FROM employee e 
    JOIN employee_connection ec ON e.emp_id = ec.emp_id 
    JOIN employee_address ea ON e.emp_id = ea.emp_id 
)
SELECT * FROM emp_info WHERE salary BETWEEN 2000 AND 4000;

11.

SELECT city, AVG(salary) AS avg_salary, SUM(salary) AS total_salary 
FROM employee e 
JOIN employee_address ea ON e.emp_id = ea.emp_id 
GROUP BY city 
ORDER BY total_salary DESC 
LIMIT 1;

12.

WITH dept_city_count AS (
    SELECT e.department, ea.city, COUNT(*) AS city_count 
    FROM employee e 
    JOIN employee_address ea ON e.emp_id = ea.emp_id 
    GROUP BY e.department, ea.city
), 
max_city_count AS (
    SELECT department, MAX(city_count) AS max_count 
    FROM dept_city_count 
    GROUP BY department
)
SELECT d.department, d.city, d.city_count 
FROM dept_city_count d 
JOIN max_city_count m ON d.department = m.department AND d.city_count = m.max_count;