Zadanie 8

Odpowiedzi z zasad oceniania CKE:

• 8.1. Rafael Blake, suma kwot mandatów 3700
• 8.2. listopad, 1766 punktów karnych
• 8.3. (posortowane po numerze rejestracyjnym) Sandra Wheeler BHW4028, Quynn Travis CJX1859, Jasper Santos KNM7950, Honorato Roth NIP6570, Ivy Bowen QFR8936, Helen White SRU9727, Quentin Tyler XHK9060, Kevin Wright YYS1077

Najwygodniej rozwiązać SQL-em po zaimportowaniu trzech CSV (separator ;) jako tabel. Poniżej rozwiązania w SQLite / standardowym SQL oraz alternatywa w Pythonie pandas.

8.1. Kierowca z największą sumą kwot mandatów:

SELECT k.Imie, k.Nazwisko, SUM(t.Kwota) AS suma_kwot
FROM rejestr r
  JOIN kierowcy k    ON r.IdOsoby = k.IdOsoby
  JOIN taryfikator t ON r.IdWykroczenia = t.IdWykroczenia
GROUP BY k.IdOsoby, k.Imie, k.Nazwisko
ORDER BY suma_kwot DESC
LIMIT 1;

8.2. Miesiąc z najmniejszą sumą punktów karnych za wykroczenia o id 3–6:

SELECT strftime('%Y-%m', r.Data) AS miesiac, SUM(t.Punkty) AS pkt
FROM rejestr r
  JOIN taryfikator t ON r.IdWykroczenia = t.IdWykroczenia
WHERE t.IdWykroczenia BETWEEN 3 AND 6
GROUP BY miesiac
ORDER BY pkt ASC
LIMIT 1;

8.3. Numery rejestracyjne i właściciele, którzy NIE są w rejestr.txt, sortowane alfabetycznie po NrRejestracyjny:

SELECT k.NrRejestracyjny, k.Imie, k.Nazwisko
FROM kierowcy k
WHERE k.IdOsoby NOT IN (SELECT IdOsoby FROM rejestr)
ORDER BY k.NrRejestracyjny ASC;

Alternatywa Pythonem (pandas):

import pandas as pd
kierowcy    = pd.read_csv("kierowcy.txt",    sep=";")
taryfikator = pd.read_csv("taryfikator.txt", sep=";")
rejestr     = pd.read_csv("rejestr.txt",     sep=";", parse_dates=["Data"])

# 8.1.
d = (rejestr.merge(taryfikator, on="IdWykroczenia")
            .merge(kierowcy,    on="IdOsoby"))
s = d.groupby(["IdOsoby","Imie","Nazwisko"])["Kwota"].sum().sort_values(ascending=False)
print("8.1.", s.head(1))

# 8.2.
d2 = d[d["IdWykroczenia"].between(3,6)]
d2["miesiac"] = d2["Data"].dt.to_period("M")
pkt = d2.groupby("miesiac")["Punkty"].sum().sort_values()
print("8.2.", pkt.head(1))

# 8.3.
brak = kierowcy[~kierowcy["IdOsoby"].isin(rejestr["IdOsoby"])] \
            .sort_values("NrRejestracyjny")
brak[["NrRejestracyjny","Imie","Nazwisko"]].to_csv("wyniki8_3.csv", index=False)

Wszystkie odpowiedzi zapisujemy do wyniki8.txt, każdą poprzedzając numerem podzadania.

8.4. Przykładowe rozwiązanie z zasad oceniania CKE:

SELECT Fotoradar.IdFotoradaru
FROM Fotoradar
LEFT JOIN Rejestr
  ON Fotoradar.IdFotoradaru = Rejestr.IdFotoradaru
WHERE Rejestr.IdFotoradaru IS NULL;

albo z podzapytaniem: ... WHERE Fotoradar.IdFotoradaru NOT IN (SELECT Rejestr.IdFotoradaru FROM Rejestr).