SQL 練習/員工管理

關係模式

練習

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. 選擇每個部門的員工數量（您只需要顯示部門程式碼和員工數量）。

點選檢視解決方案

SELECT Department, COUNT(*)
  FROM Employees
  GROUP BY Department;

10. 選擇所有員工資料，包括每個員工的部門資料。

點選檢視解決方案

SELECT *
 FROM Employees E INNER JOIN Departments D
 ON E.Department = D.Code;

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

點選檢視解決方案

/* Without labels */
SELECT Employees.Name, LastName, Departments.Name AS DepartmentsName, Budget
  FROM Employees INNER JOIN Departments
  ON Employees.Department = Departments.Code;

/* With labels */
SELECT E.Name, LastName, D.Name AS DepartmentsName, Budget
  FROM Employees E INNER JOIN Departments D
  ON E.Department = D.Code;

12. 選擇為預算超過 60,000 美元的部門工作的員工的姓名和姓氏。

點選檢視解決方案

/* Without subquery */
SELECT Employees.Name, LastName
  FROM Employees INNER JOIN Departments
  ON Employees.Department = Departments.Code
    AND Departments.Budget > 60000;

/* With subquery */
SELECT Name, LastName FROM Employees
  WHERE Department IN
  (SELECT Code FROM Departments WHERE Budget > 60000);

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

點選檢視解決方案

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

14. 選擇員工人數超過兩個的部門名稱。

點選檢視解決方案

/*With subquery*/
SELECT D.Name FROM Departments D
  WHERE 2 < 
  (
   SELECT COUNT(*) 
     FROM Employees
     WHERE Department = D.Code
  );

/* With IN and subquery */
SELECT Name FROM Departments
  WHERE Code 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 = Code
  GROUP BY Departments.Name
  HAVING COUNT(*) > 2;

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

點選檢視解決方案

/* With subquery */
SELECT e.Name, e.LastName
FROM Employees e 
WHERE e.Department = (
       SELECT sub.Code 
       FROM (SELECT * FROM Departments d ORDER BY d.budget LIMIT 2) sub 
       ORDER BY budget DESC LIMIT 1);

/* With subquery */
SELECT Name, LastName 
FROM Employees 
WHERE Department IN (
  SELECT Code 
  FROM Departments 
  WHERE Budget = (
    SELECT TOP 1 Budget 
    FROM Departments 
    WHERE Budget IN (
      SELECT DISTINCT TOP 2 Budget 
      FROM Departments 
     ORDER BY Budget ASC
    ) 
    ORDER BY Budget DESC
  )
);

16. 新增一個名為“質量保證”的新部門，預算為 40,000 美元，部門程式碼為 11。在此部門中新增名為“Mary Moore”的員工，其社會安全號碼為 847-21-9811。

點選檢視解決方案

INSERT INTO Departments
  VALUES ( 11 , 'Quality Assurance' , 40000);

INSERT INTO Employees
  VALUES ( '847219811' , 'Mary' , 'Moore' , 11);

/*注意：在 SQL 中引用數字雖然有效，但不是最佳實踐。社會安全號碼不應被引用，因為它是一個整數。*/

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 Code FROM Departments
      WHERE Budget >= 60000
  );

21. 從表中刪除所有員工。

點選檢視解決方案

DELETE FROM Employees;

建立表程式碼

 CREATE TABLE Departments (
   Code INTEGER PRIMARY KEY NOT NULL,
   Name VARCHAR NOT NULL ,
   Budget REAL NOT NULL 
 );
 
 CREATE TABLE Employees (
   SSN INTEGER PRIMARY KEY NOT NULL,
   Name TEXT NOT NULL ,
   LastName VARCHAR NOT NULL ,--since question 2 asks about removing duplicate - text must be converted if the answer is using distinct
   Department INTEGER NOT NULL , 
   CONSTRAINT fk_Departments_Code FOREIGN KEY(Department) 
   REFERENCES Departments(Code)
 );

點選檢視 MySQL 語法。

CREATE TABLE Departments (
  Code INTEGER PRIMARY KEY,
  Name varchar(255) NOT NULL ,
  Budget decimal NOT NULL 
);

CREATE TABLE Employees (
  SSN INTEGER PRIMARY KEY,
  Name varchar(255) NOT NULL ,
  LastName varchar(255) NOT NULL ,
  Department INTEGER NOT NULL , 
  foreign key (department) references Departments(Code) 
) ENGINE=INNODB;

點選檢視 Oracle 語法。

CREATE TABLE Departments (
  Code INT PRIMARY KEY NOT NULL,
  Name VARCHAR(100) NOT NULL ,
  Budget NUMBER NOT NULL 
);

CREATE TABLE Employees (
  SSN INT PRIMARY KEY NOT NULL,
  Name VARCHAR(30) NOT NULL ,
  LastName VARCHAR(30) NOT NULL ,
  Department INT NOT NULL , 
  CONSTRAINT fk_Departments_Code FOREIGN KEY(Department) REFERENCES Departments(Code)
);

樣本資料集

INSERT INTO Departments(Code,Name,Budget) VALUES(14,'IT',65000);
INSERT INTO Departments(Code,Name,Budget) VALUES(37,'Accounting',15000);
INSERT INTO Departments(Code,Name,Budget) VALUES(59,'Human Resources',240000);
INSERT INTO Departments(Code,Name,Budget) VALUES(77,'Research',55000);

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