Zadanie 3. E‑learning

Szkoła postanowiła wprowadzić dla uczniów dodatkowe zajęcia z informatyki z wykorzystaniem systemu e‑learning. W plikach: Osoby.txt, Listy.txt, Punktacja.txt znajdują się informacje na temat: uczniów szkoły, którzy uczyli się w  systemie e‑learning w okresie od 1.09.2014 do 15.02.2015, list zadań oraz uzyskanych przez uczniów wyników. Pierwszy wiersz każdego z plików jest wierszem nagłówkowym, a dane w wierszach rozdzielone są znakami tabulacji.

Plik o nazwie Osoby.txt zawiera 60 wierszy z informacjami na temat osób, które wysyłały rozwiązania zadań poprzez system. Są to: identyfikator danej osoby (id_osoby), jej nazwisko (nazwisko), imię (imie) oraz nazwa grupy, do której osoba ta została przydzielona (grupa).

Przykład:

id_osoby	imie		nazwisko	grupa
35			Joanna		Matura		G3
36   		Anna		Piasecka   	G3
37   		Katarzyna 	Zienowicz 	G3

W pliku Listy.txt znajduje się 11 wierszy z  informacjami na temat list zadań zamieszczonych w systemie: numerem listy (id_listy), nazwą listy zadań (nazwa) oraz terminem oddania (termin_oddania).

Przykład:

id_listy	nazwa		termin_oddania
7			C7			2015-01-26
8			P1          2014-11-10

Plik o nazwie Punktacja.txt zawiera 653 wiersze z informacjami o wynikach uczniów. Są to: liczba porządkowa (lp), identyfikator danej osoby (id_osoby), identyfikator listy zadań (id_listy), liczba punktów zdobytych przez daną osobę (punkty) oraz data przesłania rozwiązania listy zadań (data).

Przykład:

lp   id_osoby  id_listy  punkty    data
1    1         1         12        2014-10-20
2    1         2         12        2014-11-03
3    1         8         22        2014-11-10

Wykorzystując dane zawarte w tych plikach oraz dostępne narzędzia informatyczne, wykonaj poniższe zadania, a wyniki zapisz w pliku o nazwie Wyniki_elearning.txt. Wyniki do każdego zadania poprzedź numerem oznaczającym to zadanie.

Do oceny oddajesz:

• plik Wyniki_elearning.txt zawierający odpowiedzi do poszczególnych zadań (odpowiedź do każdego zadania powinna być poprzedzona jego numerem)

• plik(i) zawierający(e) komputerową realizację twoich obliczeń

Zadanie 3.1

Utwórz czytelne zestawienie tabelaryczne, w którym dla każdej grupy podasz, ile osób otrzymało liczbę punktów równą 10, 11, 12. W swoich obliczeniach weź pod uwagę wszystkie listy zadań.

Pokaż rozwiązanieazurewhite

Pokaż rozwiązanie

Odpowiedź dla programu Microsoft Access:

R191o6azENkgV

Zrzut ekranu przedstawia tabelę o nagłówkach:
Punkty, G1, G2, G3, G4, G5.
W kolumnie Punkty znajdują się liczby: 10, 11, 12.
W kolumnach G1, G2, G3, G4, G5 znajdują się wartości liczbowe.

Linia 1. TRANSFORM COUNT otwórz nawias okrągły asterysk zamknij nawias okrągły. Linia 2. SELECT Punktacja kropka punkty. Linia 3. FROM Osoby. Linia 4. INNER JOIN Punktacja ON Punktacja kropka id podkreślnik osoby znak równości Osoby kropka id podkreślnik osoby. Linia 5. WHERE Punktacja kropka punkty IN otwórz nawias okrągły 10 przecinek 11 przecinek 12 zamknij nawias okrągły. Linia 6. GROUP BY Punktacja kropka punkty. Linia 7. PIVOT Osoby kropka grupa średnik.

TRANSFORM COUNT(*)
SELECT Punktacja.punkty
FROM Osoby
INNER JOIN Punktacja ON Punktacja.id_osoby = Osoby.id_osoby
WHERE Punktacja.punkty IN (10, 11, 12)
GROUP BY Punktacja.punkty
PIVOT Osoby.grupa;

Odpowiedź dla programu LibreOffice Base:

RNDtgFGYwGHhg

Zrzut ekranu przedstawia tabelę o nagłówkach:
Punkty, G1, G2, G3, G4, G5.
W kolumnie Punkty znajdują się liczby: 10, 11, 12.
W kolumnach G1, G2, G3, G4, G5 znajdują się wartości liczbowe. Pod spodem znajduje się kod: SELECT ”Punktacja”. ”punkty”, COUNT( CASE WHEN ”Osoby”. ”grupa” = 'G1' THEN ”Osoby”. ”grupa” END ) AS "G1", COUNT( CASE WHEN ”Osoby”. ”grupa” = 'G2' THEN ”Osoby”. ”grupa” END ) AS "G2", COUNT( CASE WHEN ”Osoby”. ”grupa” = 'G3' THEN ”Osoby”. ”grupa” END ) AS "G3", COUNT( CASE WHEN ”Osoby”. ”grupa” = 'G4' THEN ”Osoby”. ”grupa” END ) AS "G4", COUNT( CASE WHEN ”Osoby”. ”grupa” = 'G5' THEN ”Osoby”. ”grupa” END ) AS "G5" FROM ”Osoby” INNER JOIN ”Punktacja ON ”Punktacja”. ”id_osoby” = ”Osoby”. ”id_osoby” WHERE ”Punktacja”. ”punkty” IN ( 10.00, 11.00, 12.00 ) GROUP BY ”Punktacja”.”punkty”

Linia 1. SELECT Punktacja kropka punkty przecinek. Linia 2. COUNT otwórz nawias okrągły CASE WHEN Osoby kropka grupa znak równości apostrof G1 apostrof THEN Osoby kropka grupa END zamknij nawias okrągły AS cudzysłów G1 cudzysłów przecinek. Linia 3. COUNT otwórz nawias okrągły CASE WHEN Osoby kropka grupa znak równości apostrof G2 apostrof THEN Osoby kropka grupa END zamknij nawias okrągły AS cudzysłów G2 cudzysłów przecinek. Linia 4. COUNT otwórz nawias okrągły CASE WHEN Osoby kropka grupa znak równości apostrof G3 apostrof THEN Osoby kropka grupa END zamknij nawias okrągły AS cudzysłów G3 cudzysłów przecinek. Linia 5. COUNT otwórz nawias okrągły CASE WHEN Osoby kropka grupa znak równości apostrof G4 apostrof THEN Osoby kropka grupa END zamknij nawias okrągły AS cudzysłów G4 cudzysłów przecinek. Linia 6. COUNT otwórz nawias okrągły CASE WHEN Osoby kropka grupa znak równości apostrof G5 apostrof THEN Osoby kropka grupa END zamknij nawias okrągły AS cudzysłów G5 cudzysłów. Linia 7. FROM Osoby. Linia 8. INNER JOIN Punktacja ON Punktacja kropka id podkreślnik osoby znak równości Osoby kropka id podkreślnik osoby. Linia 9. WHERE Punktacja kropka punkty IN otwórz nawias okrągły 10 kropka 00 przecinek 11 kropka 00 przecinek 12 kropka 00 zamknij nawias okrągły. Linia 10. GROUP BY Punktacja kropka punkty.

SELECT Punktacja.punkty,
  COUNT( CASE WHEN Osoby.grupa = 'G1' THEN Osoby.grupa END ) AS "G1",
  COUNT( CASE WHEN Osoby.grupa = 'G2' THEN Osoby.grupa END ) AS "G2",
  COUNT( CASE WHEN Osoby.grupa = 'G3' THEN Osoby.grupa END ) AS "G3",
  COUNT( CASE WHEN Osoby.grupa = 'G4' THEN Osoby.grupa END ) AS "G4",
  COUNT( CASE WHEN Osoby.grupa = 'G5' THEN Osoby.grupa END ) AS "G5"
FROM Osoby
INNER JOIN Punktacja ON Punktacja.id_osoby = Osoby.id_osoby
WHERE Punktacja.punkty IN ( 10.00, 11.00, 12.00 )
GROUP BY Punktacja.punkty

Zadanie 3.2

Utwórz zestawienie, które dla każdej daty przedstawi, ile osób z nazwiskami kończącymi się na „-icz” brało udział w zajęciach w każdej z grup.

Pokaż rozwiązanieazurewhite

Pokaż rozwiązanie

Odpowiedź dla programu Microsoft Access:

I propozycja rozwiązania:

R1FXVQXnEqGWX

Zrzut ekranu przedstawia tabelę o nagłówkach:
Data, G2, G3, G4.
W kolumnie Data wypisano daty.
W kolumnach G2, G3, G4 wypisano wartości 2 oraz 1.

Linia 1. TRANSFORM COUNT otwórz nawias okrągły Osoby kropka id podkreślnik osoby zamknij nawias okrągły. Linia 2. SELECT Punktacja kropka data. Linia 3. FROM Punktacja. Linia 4. INNER JOIN Osoby ON Osoby kropka id podkreślnik osoby znak równości Punktacja kropka id podkreślnik osoby. Linia 5. WHERE Osoby kropka Nazwisko LIKE apostrof asterysk icz apostrof. Linia 6. GROUP BY Punktacja kropka data. Linia 7. PIVOT Osoby kropka grupa średnik.

TRANSFORM COUNT(Osoby.id_osoby)
SELECT Punktacja.data
FROM Punktacja
INNER JOIN Osoby ON Osoby.id_osoby = Punktacja.id_osoby
WHERE Osoby.Nazwisko LIKE '*icz'
GROUP BY Punktacja.data
PIVOT Osoby.grupa;

II propozycja rozwiązania:

R12pegpWvtSeI

Zrzut ekranu przedstawia tabelę o nagłówkach:
Data, Grupa, Ilosc_osob.
W kolumnie Data wypisano daty.
W kolumnie Grypa wypisano oznaczenia grup.
W kolumnie Ilosc_osob wypisano wartości liczbowe 2 lub 1.

Linia 1. SELECT Punktacja kropka data przecinek Osoby kropka grupa przecinek COUNT otwórz nawias okrągły Osoby kropka id podkreślnik osoby zamknij nawias okrągły AS Liczba podkreślnik osob. Linia 2. FROM Punktacja. Linia 3. INNER JOIN Osoby ON Osoby kropka id podkreślnik osoby znak równości Punktacja kropka id podkreślnik osoby. Linia 4. WHERE Osoby kropka nazwisko LIKE apostrof asterysk icz apostrof. Linia 5. GROUP BY Punktacja kropka data przecinek Osoby kropka grupa średnik.

SELECT Punktacja.data, Osoby.grupa, COUNT(Osoby.id_osoby) AS Liczba_osob
FROM Punktacja
INNER JOIN Osoby ON Osoby.id_osoby = Punktacja.id_osoby
WHERE Osoby.nazwisko LIKE '*icz'
GROUP BY Punktacja.data, Osoby.grupa;

Odpowiedź dla programu LibreOffice Base:

I propozycja rozwiązania:

RXKlaup02qQ18

Zrzut ekranu przedstawia tabelę o nagłówkach:
Data, G2, G3, G4.
W kolumnie Data wypisano daty.
W kolumnach G2, G3, G4 wypisano wartości 2 oraz 1.Pod spodem znajduje się kod: SELECT "Punktacja"."data", COUNT( CASE WHEN "Osoby"."grupa" = 'G1' THEN "Osoby"."grupa" END ) AS "G1", COUNT( CASE WHEN "Osoby"."grupa" = 'G2' THEN "Osoby"."grupa" END ) AS "G2", COUNT( CASE WHEN "Osoby"."grupa" = 'G3' THEN "Osoby"."grupa" END ) AS "G3", COUNT( CASE WHEN "Osoby"."grupa" = 'G4' THEN "Osoby"."grupa" END ) AS "G4", COUNT( CASE WHEN "Osoby"."grupa" = 'G5' THEN "Osoby"."grupa" END ) AS "G5", FROM "Punktacja" INNER JOIN "Osoby" ON "Osoby"."id_osoby" = "Punktacja"."id_osoby" WHERE "Osoby"."nazwisko" LIKE '%icz' GROUP BY "Punktacja"."data"

Linia 1. SELECT Punktacja kropka data przecinek. Linia 2. COUNT otwórz nawias okrągły CASE WHEN Osoby kropka grupa znak równości apostrof G1 apostrof THEN Osoby kropka grupa END zamknij nawias okrągły AS cudzysłów G1 cudzysłów przecinek. Linia 3. COUNT otwórz nawias okrągły CASE WHEN Osoby kropka grupa znak równości apostrof G2 apostrof THEN Osoby kropka grupa END zamknij nawias okrągły AS cudzysłów G2 cudzysłów przecinek. Linia 4. COUNT otwórz nawias okrągły CASE WHEN Osoby kropka grupa znak równości apostrof G3 apostrof THEN Osoby kropka grupa END zamknij nawias okrągły AS cudzysłów G3 cudzysłów przecinek. Linia 5. COUNT otwórz nawias okrągły CASE WHEN Osoby kropka grupa znak równości apostrof G4 apostrof THEN Osoby kropka grupa END zamknij nawias okrągły AS cudzysłów G4 cudzysłów przecinek. Linia 6. COUNT otwórz nawias okrągły CASE WHEN Osoby kropka grupa znak równości apostrof G5 apostrof THEN Osoby kropka grupa END zamknij nawias okrągły AS cudzysłów G5 cudzysłów. Linia 7. FROM Punktacja. Linia 8. INNER JOIN Osoby ON Osoby kropka id podkreślnik osoby znak równości Punktacja kropka id podkreślnik osoby. Linia 9. WHERE Osoby kropka nazwisko LIKE apostrof procent icz apostrof. Linia 10. GROUP BY Punktacja kropka data.

SELECT Punktacja.data,
  COUNT( CASE WHEN Osoby.grupa = 'G1' THEN Osoby.grupa END ) AS "G1",
  COUNT( CASE WHEN Osoby.grupa = 'G2' THEN Osoby.grupa END ) AS "G2",
  COUNT( CASE WHEN Osoby.grupa = 'G3' THEN Osoby.grupa END ) AS "G3",
  COUNT( CASE WHEN Osoby.grupa = 'G4' THEN Osoby.grupa END ) AS "G4",
  COUNT( CASE WHEN Osoby.grupa = 'G5' THEN Osoby.grupa END ) AS "G5"
FROM Punktacja
INNER JOIN Osoby ON Osoby.id_osoby = Punktacja.id_osoby
WHERE Osoby.nazwisko LIKE '%icz'
GROUP BY Punktacja.data

II propozycja rozwiązania:

R7vkEMdXW5vWS

Zrzut ekranu przedstawia tabelę o nagłówkach:
Data, Grupa, Ilosc_osob.
W kolumnie Data wypisano daty.
W kolumnie Grypa wypisano oznaczenia grup.
W kolumnie Ilosc_osob wypisano wartości liczbowe 2 lub 1. Pod spodem znajduje się kod: SELECT "Punktacja"."data", "Osoby"."grupa", COUNT( "Osoby"."id_osoby" ) AS "Liczba_osob" FROM "Punktacja" INNER JOIN "Osoby" ON "Osoby"."id_osoby" = "Punktacja"."id_osoby" WHERE "Osoby"."nazwisko" LIKE '%icz' GROUP BY "Punktacja"."data", "Osoby"."grupa"

Linia 1. SELECT Punktacja kropka data przecinek Osoby kropka grupa przecinek COUNT otwórz nawias okrągły Osoby kropka id podkreślnik osoby zamknij nawias okrągły AS cudzysłów Liczba podkreślnik osob cudzysłów. Linia 2. FROM Punktacja. Linia 3. INNER JOIN Osoby ON Osoby kropka id podkreślnik osoby znak równości Punktacja kropka id podkreślnik osoby. Linia 4. WHERE Osoby kropka nazwisko LIKE apostrof procent icz apostrof. Linia 5. GROUP BY Punktacja kropka data przecinek Osoby kropka grupa.

SELECT Punktacja.data, Osoby.grupa, COUNT( Osoby.id_osoby ) AS "Liczba_osob"
FROM Punktacja
INNER JOIN Osoby ON Osoby.id_osoby = Punktacja.id_osoby
WHERE Osoby.nazwisko LIKE '%icz'
GROUP BY Punktacja.data, Osoby.grupa

Odpowiedzi do zadania

Zadanie 3.1:

Zadanie 3.2: