Ćwiczenie SQL – Zaginiony pendrive

💽 Ćwiczenie SQL – Zaginiony pendrive

W firmie DataHunter Inc. zaginął nośnik z danymi raportów. Każdy raport musi być zatwierdzony przez dwóch różnych menedżerów. Znajdź raporty, które nie zostały zatwierdzone zgodnie z procedurą.

📂 Struktura bazy danych:


CREATE TABLE raporty (
  id INT PRIMARY KEY,
  nazwa VARCHAR(100),
  autor_id INT
);

CREATE TABLE uzytkownicy (
  id INT PRIMARY KEY,
  imie VARCHAR(50),
  nazwisko VARCHAR(50),
  rola VARCHAR(20)
);

CREATE TABLE zatwierdzenia (
  id INT PRIMARY KEY,
  raport_id INT,
  zatwierdzil_id INT,
  data_zatwierdzenia DATE,
  FOREIGN KEY (raport_id) REFERENCES raporty(id),
  FOREIGN KEY (zatwierdzil_id) REFERENCES uzytkownicy(id)
);

📌 Przykładowe dane:


-- Użytkownicy
INSERT INTO uzytkownicy VALUES 
(1, 'Jan', 'Kowalski', 'autor'),
(2, 'Anna', 'Zielińska', 'menedżer'),
(3, 'Tomasz', 'Wójcik', 'menedżer'),
(4, 'Ewa', 'Mazur', 'menedżer');

-- Raporty
INSERT INTO raporty VALUES 
(1, 'Raport sprzedaży Q1', 1),
(2, 'Raport kosztów IT', 1),
(3, 'Analiza ryzyka', 1);

-- Zatwierdzenia
INSERT INTO zatwierdzenia VALUES 
(1, 1, 2, '2024-10-01'),
(2, 1, 3, '2024-10-02'),
(3, 2, 2, '2024-11-01'), -- tylko jedno zatwierdzenie!
(4, 3, 4, '2024-12-01'); -- tylko jedno zatwierdzenie!

🧠 Zadanie:

Znajdź wszystkie raporty, które mają mniej niż 2 zatwierdzenia.

✅ Przykładowe rozwiązanie:


SELECT r.nazwa, COUNT(z.id) AS liczba_zatwierdzen
FROM raporty r
LEFT JOIN zatwierdzenia z ON r.id = z.raport_id
GROUP BY r.id, r.nazwa
HAVING COUNT(z.id) < 2;

Nie wszystkie raporty są bezpieczne... Odszukaj je, zanim trafią w niepowołane ręce.

💽 Zaginiony pendrive – Rozszerzone zapytania SQL

Rozszyfruj dane i zidentyfikuj raporty z lukami w zatwierdzeniach. Czas gra na niekorzyść!

🔎 1. Raporty bez żadnego zatwierdzenia:


SELECT r.nazwa
FROM raporty r
LEFT JOIN zatwierdzenia z ON r.id = z.raport_id
WHERE z.id IS NULL;

🧨 2. Raporty zatwierdzone dwukrotnie przez tego samego menedżera:


SELECT raport_id, zatwierdzil_id, COUNT(*) AS ilosc
FROM zatwierdzenia
GROUP BY raport_id, zatwierdzil_id
HAVING COUNT(*) > 1;

🧍 3. Raporty zatwierdzone tylko przez jednego menedżera – kto?


SELECT r.nazwa, u.imie, u.nazwisko
FROM raporty r
JOIN zatwierdzenia z ON r.id = z.raport_id
JOIN uzytkownicy u ON z.zatwierdzil_id = u.id
GROUP BY r.id
HAVING COUNT(DISTINCT z.zatwierdzil_id) = 1;

🗃️ 4. Lista raportów z liczbą zatwierdzeń i nazwiskami zatwierdzających:


SELECT r.nazwa,
       COUNT(z.id) AS liczba_zatwierdzen,
       GROUP_CONCAT(CONCAT(u.imie, ' ', u.nazwisko) SEPARATOR ', ') AS zatwierdzajacy
FROM raporty r
LEFT JOIN zatwierdzenia z ON r.id = z.raport_id
LEFT JOIN uzytkownicy u ON z.zatwierdzil_id = u.id
GROUP BY r.id;

🗑️ 5. Usuń raporty zatwierdzone przez mniej niż dwóch menedżerów (jeśli brak bezpieczeństwa!):


DELETE FROM raporty
WHERE id IN (
  SELECT r.id
  FROM raporty r
  LEFT JOIN zatwierdzenia z ON r.id = z.raport_id
  GROUP BY r.id
  HAVING COUNT(DISTINCT z.zatwierdzil_id) < 2
);

Ostrzeżenie: Nieautoryzowane dane mogą spowodować katastrofę. Wyciągnij raporty z mroku.