Employee_Attendance_Management_System/sql_scripts/business_queries.sql

-- 1. 查询所有在职员工的姓名和职位
-- 查询类型：比较条件
SELECT Name, Position
FROM Employee
WHERE EmploymentStatus = '在职';

-- 2. 查询所有已批准的年假记录
-- 查询类型：比较条件
SELECT e.Name, l.LeaveType, l.StartDate, l.EndDate
FROM LeaveRequest l
JOIN Employee e ON l.EmployeeID = e.EmployeeID
WHERE l.LeaveType = '年假' AND l.RequestStatus = '已批准';

-- 3. 查询1985年6月21日后入职的员工姓名和职位
-- 查询类型：范围比较
SELECT Name, Position
FROM Employee
WHERE HireDate > '1985-06-21';

-- 4. 查询所有在2024年1月到2024年3月间请病假的员工及其假期天数
-- 查询类型：范围比较
SELECT e.Name, l.LeaveDays, l.StartDate, l.EndDate
FROM LeaveRequest l
JOIN Employee e ON l.EmployeeID = e.EmployeeID
WHERE l.LeaveType = '病假' AND l.StartDate BETWEEN '2024-01-01' AND '2024-03-31';

-- 5. 查询所有名字中包含“王”字的员工
-- 查询类型：字符串相似比较
SELECT Name
FROM Employee
WHERE Name LIKE '%王%';

-- 6. 查询所有职位为“技术总监”或者“产品经理”的员工
-- 查询类型：集合比较
SELECT Name, Position
FROM Employee
WHERE Position IN ('技术总监', '产品经理');

-- 7. 查询所有缺勤记录的员工姓名和考勤日期
-- 查询类型：集合比较
SELECT e.Name, a.AttendanceDate
FROM Attendance a
JOIN Employee e ON a.EmployeeID = e.EmployeeID
WHERE a.AttendanceStatus = '缺勤';

-- 8. 查询所有2024年1月缺勤超过3天的员工姓名
-- 查询类型：集合比较
SELECT e.Name
FROM AttendanceSummary a
JOIN Employee e ON a.EmployeeID = e.EmployeeID
WHERE a.AbsentDays > 3 AND a.Month LIKE '2024-01%';

-- 9. 查询所有迟到超过5分钟的员工
-- 查询类型：比较条件
SELECT e.Name, a.AttendanceDate
FROM Attendance a
JOIN Employee e ON a.EmployeeID = e.EmployeeID
WHERE TIMESTAMPDIFF(MINUTE, a.CheckInTime, '09:00:00') > 5;

-- 10. 查询所有2024年5月1日至5月5日请假超过2天的员工
-- 查询类型：范围比较
SELECT e.Name, l.LeaveDays
FROM LeaveRequest l
JOIN Employee e ON l.EmployeeID = e.EmployeeID
WHERE l.StartDate BETWEEN '2024-05-01' AND '2024-05-05'
  AND l.LeaveDays > 2;

-- 11. 查询所有职位为“经理”或“主管”的员工及其入职日期
-- 查询类型：集合比较
SELECT Name, Position, HireDate
FROM Employee
WHERE Position IN ('经理', '主管');

-- 12. 查询所有2024年12月1日的考勤记录
-- 查询类型：范围比较
SELECT e.Name, a.AttendanceDate, a.AttendanceStatus
FROM Attendance a
JOIN Employee e ON a.EmployeeID = e.EmployeeID
WHERE a.AttendanceDate = '2024-12-01';

-- 13. 查询所有名字以“张”开头的员工
-- 查询类型：字符串相似比较
SELECT Name
FROM Employee
WHERE Name LIKE '张%';

-- 14. 查询所有部门是“研发部”且在2024年12月有请假记录的员工
-- 查询类型：多表连接
SELECT e.Name, l.LeaveType, l.StartDate, l.EndDate
FROM Employee e
JOIN Department d ON e.DepartmentID = d.DepartmentID
JOIN LeaveRequest l ON e.EmployeeID = l.EmployeeID
WHERE d.DepartmentName = '研发部' AND l.StartDate BETWEEN '2024-12-01' AND '2024-12-31';

-- 15. 查询所有部门为“人事部”的员工，并返回其主管姓名
-- 查询类型：多表连接
SELECT e.Name AS EmployeeName, m.Name AS ManagerName
FROM Employee e
JOIN Department d ON e.DepartmentID = d.DepartmentID
JOIN Employee m ON d.ManagerID = m.EmployeeID
WHERE d.DepartmentName = '人事部';

-- 16. 查询所有2024年7月未请假员工的姓名
-- 查询类型：多表连接
SELECT e.Name
FROM Employee e
LEFT JOIN LeaveRequest l ON e.EmployeeID = l.EmployeeID
WHERE l.LeaveType IS NULL OR l.StartDate NOT LIKE '2024-07%';

-- 17. 查询所有员工及其部门名称
-- 查询类型：多表连接
SELECT e.Name, d.DepartmentName
FROM Employee e
JOIN Department d ON e.DepartmentID = d.DepartmentID;

-- 18. 查询所有职位为“销售经理”的员工及其所在的部门名称
-- 查询类型：多表连接
SELECT e.Name, d.DepartmentName
FROM Employee e
JOIN Department d ON e.DepartmentID = d.DepartmentID
WHERE e.Position = '销售经理';

-- 19. 查询2024年上半年未请过假的员工姓名
-- 查询类型：嵌套查询
SELECT e.Name
FROM Employee e
WHERE e.EmployeeID NOT IN (
    SELECT DISTINCT EmployeeID
    FROM LeaveRequest
    WHERE LeaveStartDate BETWEEN '2024-01-01' AND '2024-06-30'
       OR LeaveEndDate BETWEEN '2024-01-01' AND '2024-06-30'
);

-- 20. 查询所有已经提交申请且状态为“已批准”的年假记录
-- 查询类型：EXISTS查询
SELECT e.Name, l.LeaveType, l.StartDate, l.EndDate
FROM Employee e
JOIN LeaveRequest l ON e.EmployeeID = l.EmployeeID
WHERE EXISTS (
    SELECT 1
    FROM LeaveRequest lr
    WHERE lr.EmployeeID = e.EmployeeID AND lr.RequestStatus = '已批准'
);