ANSHENG'S BLOG
SQL语句练习-员工管理
# sqlpublishDate · 2019-10-12 / lastEditedTime · 2022-04-06 04:49:00

创建表

  • 部门表
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);

题目

  • 选择所有员工的姓氏

  • 选择所有员工的姓氏,不重复

  • 选择姓氏为Smith的员工的所有数据

  • 选择姓氏为SmithDoe的员工的所有数据

  • 选择在部门14中工作的员工的所有数据

  • 选择在部门37或部门77中工作的员工的所有数据

  • 选择姓氏以S开头的员工的所有数据

  • 选择所有部门预算的总和

  • 选择每个部门的员工数量(只需要显示部门ID和员工数量)

  • 选择员工的所有数据,包括每个员工的部门数据

  • 选择每个员工的姓名和姓氏,以及员工部门的名称和预算

  • 选择为预算超过60,000的部门工作的员工的姓名和姓氏

  • 选择预算大于所有部门平均预算的部门

  • 选择拥有两名以上员工的部门的名称

  • 选择为预算第二低的部门工作的员工的姓名和姓氏

  • 添加一个名为Quality Assurance的新部门,预算为40,000美元,部门代码为11,在该部门添加一名名为Mary Moore的员工,SSN为847-21-9811

  • 将所有部门的预算减少10%

  • 将所有员工从研究部门(代码77)重新分配给IT部门(代码14)

  • 从表中删除IT部门的所有员工(代码14)

  • 从表中删除所有在预算大于或等于60,000的部门工作的员工

  • 从表中删除所有员工

答案

  • 选择所有员工的姓氏
select lastname from employees;
  • 选择所有员工的姓氏,不重复
select distinct lastname from employees;
  • 选择姓氏为Smith的员工的所有数据
select * from employees where lastname = 'Smith';
  • 选择姓氏为SmithDoe的员工的所有数据
/* With OR */ SELECT * FROM Employees WHERE LastName = 'Smith' OR LastName = 'Doe'; /* With IN */ SELECT * FROM Employees WHERE LastName IN ('Smith', 'Doe');
  • 选择在部门14中工作的员工的所有数据
select * from employees where department = 14;
  • 选择在部门37或部门77中工作的员工的所有数据
/* With OR */ select * from employees where department = 37 or department = 77; /* With IN */ select * from employees where department in (37, 77);
  • 选择姓氏以S开头的员工的所有数据
SELECT * FROM Employees WHERE LastName LIKE 'S%';
  • 选择所有部门预算的总和
select sum(budget) from departments;
  • 选择每个部门的员工数量(只需要显示部门ID和员工数量)
select department, count(*) from employees group by department;
  • 选择员工的所有数据,包括每个员工的部门数据
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;
  • 选择每个员工的姓名和姓氏,以及员工部门的名称和预算
/* 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;
  • 选择为预算超过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);
  • 选择预算大于所有部门平均预算的部门
SELECT * FROM Departments WHERE Budget > ( SELECT AVG(Budget) FROM Departments );
  • 选择拥有两名以上员工的部门的名称
/* 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;
  • 选择为预算第二低的部门工作的员工的姓名和姓氏
select name, lastname from employees where department = ( select id from departments order by budget limit 1 offset 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);
  • 将所有部门的预算减少10%
UPDATE Departments SET Budget = Budget * 0.9;
  • 将所有员工从研究部门(代码77)重新分配给IT部门(代码14)
UPDATE Employees SET Department = 14 WHERE Department = 77;
  • 从表中删除IT部门的所有员工(代码14)
DELETE FROM Employees WHERE Department = 14;
  • 从表中删除所有在预算大于或等于60,000的部门工作的员工
DELETE FROM Employees WHERE Department IN ( SELECT id FROM Departments WHERE Budget >= 60000 );
  • 从表中删除所有员工
DELETE FROM Employees;