DATAHUNTER_INC > zaginiony_pendrive.sql

💽 Zaginiony pendrive – SQL

RAPORTY · ZATWIERDZENIA · GROUP BY · HAVING

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. Twoim zadaniem jest namierzenie raportów, które nie spełniają procedury bezpieczeństwa.

baza testowa: raporty · użytkownicy · zatwierdzenia zliczanie zatwierdzeń DISTINCT · GROUP_CONCAT · DELETE z podzapytaniem
BAZA TESTOWA – STRUKTURA + DANE

📂 1. Struktura bazy danych

Utwórz tabele systemu raportów i zatwierdzeń.

SQL: CREATE TABLE 
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)
);

📌 2. Przykładowe dane

Wstaw użytkowników, raporty i zatwierdzenia (w tym celowo niepoprawne).

SQL: INSERT – użytkownicy, raporty, zatwierdzenia 
-- 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!

🧠 3. Raporty zbyt słabo zabezpieczone

Znajdź wszystkie raporty, które mają mniej niż 2 zatwierdzenia (baza ćwiczenia podstawowego).

SQL: mniej niż 2 zatwierdzenia 
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.

MISJE ROZSZERZONE – ANALIZA INCYDENTU

🔎 1. Raporty bez żadnego zatwierdzenia

Znajdź raporty, które nie mają ani jednego wpisu w tabeli zatwierdzenia.

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

🧨 2. Raporty zatwierdzone kilka razy przez tę samą osobę

Wykryj przypadki, gdy ten sam menedżer zatwierdził raport więcej niż raz.

SQL: GROUP BY raport + menedżer 
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 (i kto to)

Znajdź raporty, które mają dokładnie jednego unikalnego zatwierdzającego i wypisz jego dane.

SQL: COUNT(DISTINCT) = 1 
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

Zbuduj raport zbiorczy z agregacją nazwisk menedżerów.

SQL: GROUP_CONCAT 
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

Ćwiczenie teoretyczne – spróbuj usunąć z tabeli raporty, które nie spełniają zasady „min. 2 menedżerów”.

SQL: DELETE z podzapytaniem 
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 raporty mogą wyciec poza firmę. Wyciągnij je z mroku zanim zrobi to ktoś inny.