CYBERCORP_AUDIT > awanse.sql

🕶️ Audyt Kadrowy SQL – Awans nie z tej planety

ANOMALIE AWANSÓW · PODZAPYTANIA · HAVING · NOT EXISTS

Rozszerzone śledztwo kadrowe w firmie CyberCorp S.A.. Twoim zadaniem jest wykrycie wszystkich anomalii w systemie awansów przy użyciu zaawansowanych zapytań SQL.

DDL + DML – baza testowa 6 kwerend audytowych NOT EXISTS · HAVING · porównania dat
STRUKTURA I DANE TESTOWE

Najpierw odpal ten blok SQL w swojej bazie (np. MySQL), aby zbudować środowisko testowe.

SQL: Struktura tabel i dane (DDL/DML) 
-- Struktura bazy danych:

CREATE TABLE pracownicy (
  id INT PRIMARY KEY,
  imie VARCHAR(50),
  nazwisko VARCHAR(50)
);

CREATE TABLE historia_stanowisk (
  id INT PRIMARY KEY,
  pracownik_id INT,
  stanowisko VARCHAR(100),
  data_od DATE,
  data_do DATE,
  FOREIGN KEY (pracownik_id) REFERENCES pracownicy(id)
);

CREATE TABLE awanse (
  id INT PRIMARY KEY,
  pracownik_id INT,
  stanowisko_docelowe VARCHAR(100),
  data_awansu DATE,
  FOREIGN KEY (pracownik_id) REFERENCES pracownicy(id)
);

-- Przykładowe dane:
INSERT INTO pracownicy VALUES 
(1, 'Jakub', 'Nowak'),
(2, 'Maria', 'Kwiatkowska'),
(3, 'Łukasz', 'Zalewski');

INSERT INTO historia_stanowisk VALUES 
(1, 1, 'Asystent',         '2023-01-01', '2024-01-01'),
(2, 1, 'Starszy Asystent', '2024-01-02', '2025-01-01'),
(3, 2, 'Asystent',         '2023-01-01', '2024-06-30'),
(4, 2, 'Kierownik',        '2024-07-01', '2025-12-31'),
(5, 3, 'Specjalista IT',   '2023-01-01', '2025-12-31');

INSERT INTO awanse VALUES 
(1, 3, 'Dyrektor IT', '2025-01-01'); -- Podejrzany awans (Łukasz Zalewski)
MISJA: 6 ZADAŃ AUDYTOWYCH

1. 🛸 Awans na stanowisko nigdy wcześniej nie zajmowane

Znajdź awanse, dla których nie istnieje żaden rekord w historia_stanowisk z tym samym stanowiskiem (NOT EXISTS).

SQL: Rozwiązanie 1 (NOT EXISTS) 
SELECT p.imie, p.nazwisko, a.stanowisko_docelowe
FROM awanse a
JOIN pracownicy p ON a.pracownik_id = p.id
WHERE NOT EXISTS (
  SELECT 1
  FROM historia_stanowisk h
  WHERE h.pracownik_id = a.pracownik_id
    AND h.stanowisko = a.stanowisko_docelowe
);

2. 📉 Awans na stanowisko niższe niż ostatnie

Znajdź ostatnie stanowisko pracownika i porównaj z celem awansu. Tu uproszczenie: porównanie alfabetyczne nazw stanowisk.

SQL: Rozwiązanie 2 (MAX + porównanie) 
SELECT p.imie, p.nazwisko,
       h.stanowisko AS ostatnie_stanowisko,
       a.stanowisko_docelowe
FROM pracownicy p
JOIN historia_stanowisk h ON p.id = h.pracownik_id
JOIN awanse a ON p.id = a.pracownik_id
WHERE h.data_do = (
  SELECT MAX(data_do)
  FROM historia_stanowisk
  WHERE pracownik_id = p.id
)
AND a.stanowisko_docelowe < h.stanowisko; -- porównanie alfabetyczne

3. 🔁 Wielokrotny awans na to samo stanowisko

Użyj GROUP BY i HAVING COUNT(*) > 1, aby znaleźć duplikaty awansów na to samo stanowisko.

SQL: Rozwiązanie 3 (GROUP BY + HAVING) 
SELECT p.imie, p.nazwisko,
       a.stanowisko_docelowe,
       COUNT(*) AS liczba_awansow
FROM awanse a
JOIN pracownicy p ON p.id = a.pracownik_id
GROUP BY p.id, a.stanowisko_docelowe
HAVING COUNT(*) > 1;

4. 📜 Historia przed i po awansie

Połącz awanse z historią stanowisk, pokazując, co pracownik zajmował bezpośrednio przed (lub w dniu) awansu.

SQL: Rozwiązanie 4 (JOIN + warunek daty) 
SELECT p.imie, p.nazwisko,
       h.stanowisko AS poprzednie,
       a.stanowisko_docelowe,
       a.data_awansu
FROM pracownicy p
JOIN historia_stanowisk h ON p.id = h.pracownik_id
JOIN awanse a ON p.id = a.pracownik_id
WHERE h.data_do <= a.data_awansu
ORDER BY p.id, a.data_awansu;

5. 🔙 Re-awans na stare stanowisko

Znajdź przypadki, gdzie stanowisko docelowe awansu było już kiedyś zajmowane przez tego pracownika.

SQL: Rozwiązanie 5 (JOIN po stanowisku) 
SELECT DISTINCT p.imie, p.nazwisko, a.stanowisko_docelowe
FROM awanse a
JOIN pracownicy p ON p.id = a.pracownik_id
JOIN historia_stanowisk h ON h.pracownik_id = p.id
WHERE h.stanowisko = a.stanowisko_docelowe;

6. ⚠️ Awans przed zakończeniem poprzedniego stanowiska

Znajdź awanse, gdzie data_awansu jest wcześniejsza niż data_do dowolnego stanowiska (nakładanie etatów).

SQL: Rozwiązanie 6 (porównanie zakresów dat) 
SELECT p.imie, p.nazwisko,
       h.stanowisko AS poprzednie_stanowisko,
       h.data_do,
       a.stanowisko_docelowe,
       a.data_awansu
FROM pracownicy p
JOIN historia_stanowisk h ON p.id = h.pracownik_id
JOIN awanse a ON p.id = a.pracownik_id
WHERE a.data_awansu < h.data_do;

Odszyfruj system, wyłap nielogiczne awanse i przygotuj raport dla działu HR CyberCorp S.A.