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. 选择姓氏为SmithDoe的员工的所有数据
  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. 选择姓氏为SmithDoe的员工的所有数据
/* 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;

相关说明:

1、VIP会员无限制任意下载,免积分。立即前往开通>>

2、下载积分可通过日常 签到绑定邮箱 以及 积分兑换 等途径获得!

3、本站资源大多存储在云盘,如出现链接失效请评论反馈,如有密码,均为:www.ipipn.com。

4、所有站内资源仅供学习交流使用。未经原版权作者许可,禁止用于任何商业环境,否则后果自负。为尊重作者版权,请购买正版作品。

5、站内资源来源于网络公开发表文件或网友分享,如侵犯您的权益,请联系管理员处理。

6、本站提供的源码、模板、软件工具等其他资源,都不包含技术服务,请大家谅解!

7、源码、模板等资源会随着技术、坏境的升级而存在部分问题,还请慎重选择。

PS.源码均收集自网络,如有侵犯阁下权益,请发信件至: admin@ipipn.com .


源站网 » SQL语句练习-员工管理

发表评论

赞助本站发展 维持服务器消耗

全站源码免费下载 立刻赞助