Task06 秋招秘籍ABC

Task06 秋招秘籍ABC

A

• 1.各部门工资最高的员工（难度：中等）

  • 创建Employee 表，包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id

    +----+-------+--------+--------------+
    | Id | Name  | Salary | DepartmentId |
    +----+-------+--------+--------------+
    | 1  | Joe   | 70000  | 1            |
    | 2  | Henry | 80000  | 2            |
    | 3  | Sam   | 60000  | 2            |
    | 4  | Max   | 90000  | 1            |
    +----+-------+--------+--------------+
    

    CREATE TABLE Employee(
    Id INTEGER PRIMARY KEY,
    Name VARCHAR(64),
    Salary INTEGER,
    DepartmentId INTEGER
    );
    
    -- 查看是否创建成功
    show columns from Employee;
    desc Employee;
    
    INSERT INTO Employee VALUES (1, 'Joe', 70000, 1),
    							(2, 'Henry', 80000, 2),
    							(3, 'Sam', 60000, 2),
    							(4, 'Max', 90000, 1);
    select * from Employee;
    
    

• 创建Department 表，包含公司所有部门的信息

  +----+----------+
  | Id | Name     |
  +----+----------+
  | 1  | IT       |
  | 2  | Sales    |
  +----+----------+
  

  CREATE TABLE Department(
  Id INTEGER,
  Name VARCHAR(64)
  );
  INSERT INTO Department VALUES(1, 'IT'),
  							  (2, 'Sales');
  
  
  
  

• 编写一个 SQL 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资

  +------------+----------+--------+
  | Department | Employee | Salary |
  +------------+----------+--------+
  | IT         | Max      | 90000  |
  | Sales      | Henry    | 80000  |
  +------------+----------+--------+
  

  SELECT 
       Dep.Name AS Department
      ,Emp.Name AS Employee
      ,Emp.Salary AS Salary
  FROM
      Employee AS Emp
  INNER JOIN
      Department AS Dep
  ON Emp.DepartmentId=Dep.Id
  WHERE Emp.Salary IN (
      SELECT 
          MAX(Salary) 
      FROM 
          Employee AS E
      WHERE 
          E.DepartmentId=Emp.DepartmentId
          );
  

  

• 最近事儿比较多，先开个坑，后续补上，，，