---

-- 1
SELECT
  full_name,
  hire_date,
  YEAR(hire_date) AS hire_year,
  MONTH(hire_date) AS hire_month,
  WEEKDAY(hire_date) AS hire_weekday
FROM employees;

-- 2
SELECT
  DATE_FORMAT(hire_date, '%Y-%m') AS hire_month,
  COUNT(*) AS total_hired
FROM employees
GROUP BY hire_month
ORDER BY total_hired DESC;

-- 3
SELECT *
FROM attendance
WHERE DATE(check_in) = '2025-04-11';

-- 4
SELECT *
FROM employees
WHERE hire_date >= CURDATE() - INTERVAL 60 DAY;

-- 5
SELECT
  e.full_name,
  a.check_in
FROM attendance a
JOIN employees e ON a.employee_id = e.id
WHERE DATE(a.check_in) = CURDATE()
  AND a.check_out IS NULL;

-- 6
SELECT
  full_name,
  hire_date,
  TIMESTAMPDIFF(MONTH, hire_date, CURDATE()) AS full_months_worked
FROM employees;

-- 7
SELECT
  id,
  employee_id,
  check_in,
  check_out,
  ROUND(TIMESTAMPDIFF(SECOND, check_in, check_out) / 3600.0, 1) AS hours_worked
FROM attendance;

-- 8
SELECT
  id,
  employee_id,
  DATE_FORMAT(check_in, '%d.%m.%Y') AS entry_date,
  DATE_FORMAT(check_in, '%H:%i') AS entry_time
FROM attendance;

-- 9
SELECT
  CONCAT(
    full_name,
    ' работает с ',
    LPAD(DAY(hire_date), 2, '0'),
    ' ',
    CASE MONTH(hire_date)
      WHEN 1 THEN 'января'
      WHEN 2 THEN 'февраля'
      WHEN 3 THEN 'марта'
      WHEN 4 THEN 'апреля'
      WHEN 5 THEN 'мая'
      WHEN 6 THEN 'июня'
      WHEN 7 THEN 'июля'
      WHEN 8 THEN 'августа'
      WHEN 9 THEN 'сентября'
      WHEN 10 THEN 'октября'
      WHEN 11 THEN 'ноября'
      WHEN 12 THEN 'декабря'
    END,
    ' ',
    YEAR(hire_date),
    ' года'
  ) AS work_info
FROM employees;

-- 10
SELECT
  e.full_name,
  ROUND(SUM(TIMESTAMPDIFF(SECOND, a.check_in, a.check_out)) / 3600.0, 1) AS total_hours
FROM attendance a
JOIN employees e ON a.employee_id = e.id
WHERE a.check_out IS NOT NULL
  AND DATE(a.check_in) BETWEEN '2025-04-01' AND '2025-04-30'
GROUP BY e.id, e.full_name
ORDER BY total_hours DESC;

-- 11
SELECT
  e.full_name,
  a.check_in
FROM attendance a
JOIN employees e ON a.employee_id = e.id
WHERE DATE(a.check_in) = CURDATE()
ORDER BY a.check_in ASC
LIMIT 1;