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;

2. 選擇所有員工的姓氏，不重複

select distinct lastname
from employees;

3. 選擇姓氏為Smith的員工的所有數據

select *
from employees
where lastname = 'Smith';

4. 選擇姓氏為Smith或Doe的員工的所有數據

/* With OR */
SELECT *
FROM Employees
WHERE LastName = 'Smith'
   OR LastName = 'Doe';

/* With IN */
SELECT *
FROM Employees
WHERE LastName IN ('Smith', 'Doe');

5. 選擇在部門14中工作的員工的所有數據

select *
from employees
where department = 14;

6. 選擇在部門37或部門77中工作的員工的所有數據

/* With OR */
select *
from employees
where department = 37
   or department = 77;

/* With IN */
select *
from employees
where department in (37, 77);

7. 選擇姓氏以S開頭的員工的所有數據

SELECT *
FROM Employees
WHERE LastName LIKE 'S%';

8. 選擇所有部門預算的總和

select sum(budget)
from departments;

9. 選擇每個部門的員工數量（隻需要顯示部門ID和員工數量）

select department, count(*)
from employees
group by department;

10. 選擇員工的所有數據，包括每個員工的部門數據

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;

11. 選擇每個員工的姓名和姓氏，以及員工部門的名稱和預算

/* 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;

12. 選擇為預算超過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);

13. 選擇預算大於所有部門平均預算的部門

SELECT *
FROM Departments
WHERE Budget >
      (
          SELECT AVG(Budget)
          FROM Departments
      );

14. 選擇擁有兩名以上員工的部門的名稱

/* 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;

15. 選擇為預算第二低的部門工作的員工的姓名和姓氏

select name, lastname
from employees
where department = (
    select id
    from departments
    order by budget
    limit 1 offset 1);

16. 添加一個名為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);

17. 將所有部門的預算減少10％

UPDATE Departments
SET Budget = Budget * 0.9;

18. 將所有員工從研究部門（代碼77）重新分配給IT部門（代碼14）

UPDATE Employees
SET Department = 14
WHERE Department = 77;

19. 從表中刪除IT部門的所有員工（代碼14）

DELETE
FROM Employees
WHERE Department = 14;

20. 從表中刪除所有在預算大於或等於60,000的部門工作的員工

DELETE
FROM Employees
WHERE Department IN
      (
          SELECT id
          FROM Departments
          WHERE Budget >= 60000
      );

21. 從表中刪除所有員工

DELETE
FROM Employees;