ansheng’s blog!

SQL语句练习-员工管理

创建表

CREATE TABLE departments
(
    Id     INTEGER PRIMARY KEY NOT NULL, -- 部门ID
    Name   varchar(100),                 -- 部门名称
    Budget FLOAT                         -- 部门预算
);

CREATE TABLE employees
(
    SSN        INTEGER PRIMARY KEY NOT NULL,
    Name       varchar(100)        NOT NULL,
    LastName   varchar(100)        NOT NULL,
    Department INTEGER             NOT NULL,
    CONSTRAINT fk_Departments_Code FOREIGN KEY (Department)
        REFERENCES Departments (Id)
);

预置数据

INSERT INTO departments
VALUES (14, 'IT', 65000),
       (37, '财务', 15000),
       (59, '人力资源', 240000),
       (77, '研发', 55000);

INSERT INTO Employees
VALUES ('123234877', 'Michael', 'Rogers', 14),
       ('152934485', 'Anand', 'Manikutty', 14),
       ('222364883', 'Carol', 'Smith', 37),
       ('326587417', 'Joe', 'Stevens', 37),
       ('332154719', 'Mary-Anne', 'Foster', 14),
       ('332569843', 'George', 'O''Donnell', 77),
       ('546523478', 'John', 'Doe', 59),
       ('631231482', 'David', 'Smith', 77),
       ('654873219', 'Zacary', 'Efron', 59),
       ('745685214', 'Eric', 'Goldsmith', 59),
       ('845657245', 'Elizabeth', 'Doe', 14),
       ('845657246', 'Kumar', 'Swamy', 14);

题目

  1. 选择所有员工的姓氏
  2. 选择所有员工的姓氏，不重复
  3. 选择姓氏为Smith的员工的所有数据
  4. 选择姓氏为Smith或Doe的员工的所有数据
  5. 选择在部门14中工作的员工的所有数据
  6. 选择在部门37或部门77中工作的员工的所有数据
  7. 选择姓氏以S开头的员工的所有数据
  8. 选择所有部门预算的总和
  9. 选择每个部门的员工数量（只需要显示部门ID和员工数量）
  10. 选择员工的所有数据，包括每个员工的部门数据
  11. 选择每个员工的姓名和姓氏，以及员工部门的名称和预算
  12. 选择为预算超过60,000的部门工作的员工的姓名和姓氏
  13. 选择预算大于所有部门平均预算的部门
  14. 选择拥有两名以上员工的部门的名称
  15. 选择为预算第二低的部门工作的员工的姓名和姓氏
  16. 添加一个名为Quality Assurance的新部门，预算为40,000美元，部门代码为11，在该部门添加一名名为Mary Moore的员工，SSN为847-21-9811
  17. 将所有部门的预算减少10％
  18. 将所有员工从研究部门（代码77）重新分配给IT部门（代码14）
  19. 从表中删除IT部门的所有员工（代码14）
  20. 从表中删除所有在预算大于或等于60,000的部门工作的员工
  21. 从表中删除所有员工

答案

  1. 选择所有员工的姓氏
select lastname
from employees;
  1. 选择所有员工的姓氏，不重复
select distinct lastname
from employees;
  1. 选择姓氏为Smith的员工的所有数据
select *
from employees
where lastname = 'Smith';
  1. 选择姓氏为Smith或Doe的员工的所有数据
/* With OR */
SELECT *
FROM Employees
WHERE LastName = 'Smith'
   OR LastName = 'Doe';

/* With IN */
SELECT *
FROM Employees
WHERE LastName IN ('Smith', 'Doe');
  1. 选择在部门14中工作的员工的所有数据
select *
from employees
where department = 14;
  1. 选择在部门37或部门77中工作的员工的所有数据
/* With OR */
select *
from employees
where department = 37
   or department = 77;

/* With IN */
select *
from employees
where department in (37, 77);
  1. 选择姓氏以S开头的员工的所有数据
SELECT *
FROM Employees
WHERE LastName LIKE 'S%';
  1. 选择所有部门预算的总和
select sum(budget)
from departments;
  1. 选择每个部门的员工数量（只需要显示部门ID和员工数量）
select department, count(*)
from employees
group by department;
  1. 选择员工的所有数据，包括每个员工的部门数据
SELECT SSN, E.Name AS Name_E, LastName, D.Name AS Name_D, Department, id, Budget
FROM Employees E
         INNER JOIN Departments D
                    ON E.Department = D.id;
  1. 选择每个员工的姓名和姓氏，以及员工部门的名称和预算
/* Without labels */
SELECT Employees.Name, LastName, Departments.Name AS DepartmentsName, Budget
FROM Employees
         INNER JOIN Departments
                    ON Employees.Department = Departments.ID;

/* With labels */
SELECT E.Name, LastName, D.Name AS DepartmentsName, Budget
FROM Employees E
         INNER JOIN Departments D
                    ON E.Department = D.ID;
  1. 选择为预算超过60,000的部门工作的员工的姓名和姓氏
/* Without subquery */
SELECT Employees.Name, LastName
FROM Employees
         INNER JOIN Departments
                    ON Employees.Department = Departments.id
                        AND Departments.Budget > 60000;

/* With subquery */
SELECT Name, LastName
FROM Employees
WHERE Department IN
      (SELECT id FROM Departments WHERE Budget > 60000);
  1. 选择预算大于所有部门平均预算的部门
SELECT *
FROM Departments
WHERE Budget >
      (
          SELECT AVG(Budget)
          FROM Departments
      );
  1. 选择拥有两名以上员工的部门的名称
/* With subquery */
SELECT Name
FROM Departments
WHERE id IN
      (
          SELECT Department
          FROM Employees
          GROUP BY Department
          HAVING COUNT(*) > 2
      );

/* With UNION. This assumes that no two departments have the same name */
SELECT Departments.Name
FROM Employees
         INNER JOIN Departments
                    ON Department = id
GROUP BY Departments.Name
HAVING COUNT(*) > 2;
  1. 选择为预算第二低的部门工作的员工的姓名和姓氏
select name, lastname
from employees
where department = (
    select id
    from departments
    order by budget
    limit 1 offset 1);
  1. 添加一个名为Quality Assurance的新部门，预算为40,000美元，部门代码为11，在该部门添加一名名为Mary Moore的员工，SSN为847-21-9811
INSERT INTO Departments
VALUES (11, 'Quality Assurance', 40000);

INSERT INTO Employees
VALUES ('847219811', 'Mary', 'Moore', 11);
  1. 将所有部门的预算减少10％
UPDATE Departments
SET Budget = Budget * 0.9;
  1. 将所有员工从研究部门（代码77）重新分配给IT部门（代码14）
UPDATE Employees
SET Department = 14
WHERE Department = 77;
  1. 从表中删除IT部门的所有员工（代码14）
DELETE
FROM Employees
WHERE Department = 14;
  1. 从表中删除所有在预算大于或等于60,000的部门工作的员工
DELETE
FROM Employees
WHERE Department IN
      (
          SELECT id
          FROM Departments
          WHERE Budget >= 60000
      );
  1. 从表中删除所有员工
DELETE
FROM Employees;