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);