Ćwiczenie SQL – Awans nie z tej planety

🕶️ Ćwiczenie SQL – Awans nie z tej planety

W firmie CyberCorp S.A. jeden z pracowników awansował na stanowisko, którego nigdy wcześniej nie zajmował. Twoim zadaniem jest wykryć, kto mógł sfałszować dane kadrowe.

📂 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:


-- Pracownicy
INSERT INTO pracownicy VALUES 
(1, 'Jakub', 'Nowak'),
(2, 'Maria', 'Kwiatkowska'),
(3, 'Łukasz', 'Zalewski');

-- Historia stanowisk
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');

-- Awans
INSERT INTO awanse VALUES 
(1, 3, 'Dyrektor IT', '2025-01-01'); -- Podejrzany awans!

🧠 Zadanie:

Użyj zapytania SQL, aby znaleźć pracowników, którzy otrzymali awans na stanowisko, którego nigdy wcześniej nie zajmowali.

✅ Przykładowe rozwiązanie:


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
);

Spróbuj samodzielnie wykryć oszusta w danych!

🕶️ Awans nie z tej planety – wersja rozszerzona

Rozszerzone śledztwo kadrowe w firmie CyberCorp S.A. – wykryj wszystkie anomalie w systemie awansów!

🛸 1. Awans na stanowisko, którego nigdy wcześniej nie zajmowano:


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:


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 nazw wymaga standaryzacji!

🔁 3. Wielokrotny awans na to samo stanowisko:


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 (pełny raport):


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. Awans na stanowisko, które już kiedyś miał (re-awans?):


SELECT 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 przyznany przed zakończeniem poprzedniego stanowiska:


SELECT p.imie, p.nazwisko, h.data_do, 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, sprawdź nielogiczne awanse i zgłoś podejrzane ruchy kadrowe!