Zadanie 7
Matura z informatyki, maj 2023, poziom rozszerzony
Wymaganie: II.4) przygotowując opracowania rozwiązań złożonych problemów, posługuje się wybranymi aplikacjami w stopniu zaawansowanym: c) projektuje i tworzy relacyjną bazę złożoną z wielu tabel oraz sieciową aplikację bazodanową dla danych związanych z rozwiązywanym problemem, formułuje kwerendy, tworzy i modyfikuje formularze oraz raporty, stosuje język SQL do wyszukiwania informacji w bazie i do jej modyfikacji, uwzględnia kwestie integralności danych, bezpieczeństwa i ochrony danych w bazie. P.II.3) przygotowuje opracowania rozwiązań problemów, posługując się wybranymi aplikacjami: wyszukuje informacje, korzystając z bazy danych opartej na co najmniej dwóch tabelach, definiuje relacje, stosuje filtrowanie, formułuje kwerendy (7.1–7.4).
Treść zadania
Zadanie 7. Gry planszowe
Pewien serwis internetowy prowadzi ranking gier planszowych. Baza serwisu została zapisana w trzech plikach.
Plik gry.txt zawiera informacje o grach planszowych. W każdym wierszu zapisano:
- id_gry – unikatowy numer gry planszowej
- nazwa – tytuł gry planszowej
- kategoria – kategoria, do jakiej została zakwalifikowana gra planszowa; każda gra należy tylko do jednej kategorii.
Plik gracze.txt zawiera informacje o graczach. W każdym wierszu zapisano:
- id_gracza – unikatowy numer gracza
- imie – imię gracza
- nazwisko – nazwisko gracza
- wiek – wiek gracza.
Plik oceny.txt zawiera oceny wystawione grom przez poszczególnych graczy. W każdym wierszu zapisano:
- id_gry – numer gry planszowej
- id_gracza – numer gracza
- stan – zawiera jedną z możliwych wartości: posiada, chce kupic, sprzedal, opisującą, czy użytkownik posiada daną grę, czy ją sprzedał lub czy zamierza ją zakupić
- ocena – zawiera ocenę gry przez gracza, wyrażoną liczbą całkowitą w zakresie od 0 do 10.
We wszystkich plikach dane w wierszach są rozdzielone znakami tabulacji, a pierwszy wiersz w każdym pliku jest wierszem nagłówkowym.
Z wykorzystaniem dostępnych narzędzi informatycznych podaj odpowiedzi do zadań 7.1.–7.4.
Zadanie 7.1. (0–1) Podaj tytuł gry, która otrzymała najwięcej ocen.
Zadanie 7.2. (0–2) Dla każdej gry z kategorii „imprezowa” podaj średnią jej ocen z dokładnością do dwóch miejsc po przecinku.
Zadanie 7.3. (0–2) Podaj liczbę graczy, którzy nie posiadają żadnej z ocenianych przez siebie gier (nie mają żadnej gry ze stanem „posiada”), a wystawili co najmniej jedną ocenę.
Zadanie 7.4. (0–3) W ocenianiu gier planszowych uczestniczą osoby w wieku od 10 do 99 lat. Osoby oceniające gry podzielono na trzy kategorie wiekowe: juniorzy (do 19 lat), seniorzy (od 20 do 49 lat) oraz weterani (od 50 lat). Wykonaj zestawienie, w którym dla każdej kategorii wiekowej podasz największą liczbę ocen wystawionych jednej grze przez użytkowników z tej kategorii wiekowej oraz nazwy gier z tą liczbą ocen. Jeżeli gier, które otrzymały taką samą największą liczbę ocen od użytkowników z danej kategorii wiekowej, jest więcej niż jedna – podaj tytuły ich wszystkich.
Zadanie 7.5. (0–2) Do bazy dołączamy tabelę sklep (cennik gier w pewnym sklepie) z polami: id_gry, cena, promocja (true – cena promocyjna, false – zwykła). Tabele gry i sklep są połączone relacją jeden do wielu: gra może mieć dwie ceny (w promocji i bez), a tabela sklep nie musi zawierać wszystkich gier. Zapisz zapytanie SQL, w wyniku którego uzyskamy informację, ile należałoby zapłacić za zakup w tym sklepie po jednej sztuce ze wszystkich gier logicznych (kategoria „logiczna”) dostępnych w cenach promocyjnych.
Źródło: arkusz CKE MINP-R0-100-2305. Otwórz oryginalny PDF
Rozwiązanie
Odpowiedzi z zasad oceniania CKE:
- 7.1. K2
- 7.2. 5 sekund 8,16; Avalone 8,25; Colt Express 7,54; Jenga 8,16; Koncept 8,37; Mamy szpiega 8,22; Przebiegle wielblady 7,73; Sushi Go 8,07; Swiatowy Konflikt 7,80; Szeryf z Nottingham 7,88
- 7.3. 334
- 7.4. juniorzy – Terraformacja Marsa i K2 (po 6 ocen); seniorzy – K2 (24); weterani – Robinson Crusoe (28)
Zakładamy że pliki zostały zaimportowane do bazy SQL (np. SQLite) jako tabele gry, gracze, oceny.
7.1. Tytuł gry z największą liczbą ocen:
SELECT g.nazwa, COUNT(*) AS ile
FROM oceny o JOIN gry g ON g.id_gry = o.id_gry
GROUP BY g.id_gry, g.nazwa
ORDER BY ile DESC
LIMIT 1;
7.2. Średnia ocen każdej gry z kategorii „imprezowa”:
SELECT g.nazwa, ROUND(AVG(o.ocena), 2) AS srednia
FROM oceny o JOIN gry g ON g.id_gry = o.id_gry
WHERE g.kategoria = 'imprezowa'
GROUP BY g.id_gry, g.nazwa
ORDER BY g.nazwa;
7.3. Gracze, którzy wystawili co najmniej jedną ocenę i nie mają żadnej gry ze stanem „posiada”:
SELECT COUNT(DISTINCT o.id_gracza)
FROM oceny o
WHERE o.id_gracza NOT IN (
SELECT id_gracza FROM oceny WHERE stan = 'posiada'
);
7.4. Liczba ocen każdej gry w każdej kategorii wiekowej, a potem gry z maksimum w danej kategorii:
WITH oceny_kat AS (
SELECT
CASE
WHEN gr.wiek <= 19 THEN 'juniorzy'
WHEN gr.wiek <= 49 THEN 'seniorzy'
ELSE 'weterani'
END AS kat,
o.id_gry,
COUNT(*) AS ile
FROM oceny o
JOIN gracze gr ON gr.id_gracza = o.id_gracza
GROUP BY kat, o.id_gry
)
SELECT k.kat, g.nazwa, k.ile
FROM oceny_kat k
JOIN gry g ON g.id_gry = k.id_gry
WHERE k.ile = (SELECT MAX(ile) FROM oceny_kat m WHERE m.kat = k.kat);
7.5. Przykładowe rozwiązanie z zasad oceniania CKE:
SELECT SUM(cena)
FROM Gry
INNER JOIN Sklep ON Gry.id_gry = Sklep.id_gry
WHERE Gry.kategoria = 'logiczna' AND Sklep.promocja = true;
Wyniki 7.1–7.4 wpisz do wyniki7.txt, każdy poprzedzony numerem zadania.
Zad. 7.3 — „nie posiada żadnej gry” oznacza brak rekordu ze stanem „posiada”, ale gracz musi mieć co najmniej jedną ocenę w pliku oceny.txt. Liczenie także graczy bez żadnej oceny daje 351 zamiast 334 — za taką odpowiedź zasady oceniania przyznają 1 pkt. Zad. 7.4 — liczymy oceny wystawione jednej grze przez graczy z danej kategorii wiekowej (a nie liczbę ocen wystawionych przez jedną osobę). Granice kategorii: 19 lat to junior, 20 i 49 – senior, 50 – weteran. Przy remisie trzeba podać wszystkie tytuły (u juniorów są dwa). Podanie id_gry zamiast tytułów to według zasad oceniania 2 pkt z 3. Zad. 7.2 — średnią podajemy z dokładnością do 2 miejsc po przecinku; za samo zaokrąglenie jest osobny punkt.
Strona arkusza CKE z treścią zadania
Rozwiązanie
7.1. Gra z największą liczbą ocen
JOIN oceny → gry, GROUP BY id_gry, ORDER BY COUNT(*) DESC LIMIT 1.
7.2. Średnia ocen per kategoria
JOIN oceny → gry, GROUP BY kategoria, AVG(ocena) zaokrąglić do 2 miejsc.
7.3. Gracze bez “posiada”, ale z oceną
Antyjoin: gracze obecni w oceny, ale nieobecni wśród rekordów stan = 'posiada'.
7.4. Lider każdej kategorii wiekowej
CTE z CASE WHEN przypisującym kategorię wiekową, następnie COUNT(*) per gracz, w drugiej CTE MAX(ile) per kategoria, na końcu JOIN aby wyciągnąć liderów (mogą być remisy).
Rozumiesz, jak to rozwiązać?
Przećwicz podobne typy zadań w aplikacji
matury-online.pl ma tysiące zadań pogrupowanych po dziedzinach. Sprawdź, czy temat „baza danych, SQL, JOIN, GROUP BY, ranking gier planszowych” zrobisz samodzielnie.
Otwórz matury-online.pl