Lekcja 14

Zadanie z Matury’2007: Dane użytkowników telefonów komórkowych, znajdują się w trzech
plikach tekstowych. Dane w jednym wierszu są rozdzielone znakami tabulacji.
W pliku kandydaci.txt znajdują się następujące informacje:
Id_k id kandydata
Nazwisko nazwisko kandydata
Imie imię kandydata
Wzrost wzrost kandydata w cm
Nazwa_sieci sieć telefonii komórkowej, z której korzysta kandydat
Wiek wiek kandydata
Symbol_woj symbol województwa, z którego pochodzi kandydat
Przykład
Id_k
1
Nazwisko Imie
Wzrost
Adamczuk Magdalena 147
Nazwa_sieci
Hejka
Wiek Symbol_woj
69
G
W pliku zawyk.txt znajdują się następujące informacje:
Id_k id kandydata
Zainteresowania - zainteresowania kandydata
Wyksztalcenie - wykształcenie kandydata
Przykład
Id_k
14
Zainteresowania
polityka
Wyksztalcenie
średnie
W pliku woj.txt znajdują się następujące dane:
Wojewodztwo - nazwa województwa
Symbol_woj - jednoliterowy symbol województwa
Przykład
Wojewodztwo
Dolnośląskie
Symbol_woj
D
Zadania:
1. Podaj liczby kobiet i mężczyzn wśród kandydatów. Imiona kobiet kończą się literą „a”.
2. Utwórz zestawienie zawierające informacje o liczbie kandydatów korzystających z
poszczególnych sieci telefonii komórkowej.
3. Utwórz zestawienie zawierające informację o liczbach kandydatów z poszczególnych
województw. Podaj nazwy województw, z których zgłosiło się więcej niż 20 kandydatów.
4. Utwórz zestawienie zawierające listę kandydatów (imię, nazwisko, nazwę województwa),
którzy mają wykształcenie średnie lub wyższe, interesują się grami komputerowymi i nie
przekroczyli 50-ego roku życia. Podaj, ile wśród nich jest osób z wykształceniem wyższym,
a ile osób z wykształceniem średnim.
5. Organizator programu telewizyjnego planuje zakwalifikować do programu tylko te
osoby,które interesują się polityką lub sportem. Ponadto dla kobiet wymagany jest wzrost co
najmniej 168 cm, w przypadku mężczyzn minimalny wzrost wynosi 175 cm. Podaj, ile
kobiet i ilu mężczyzn spełnia powyższe kryteria.
6. Sieć ‘Hejka’ zmieniła nazwę na ‘Heyhoo’. Zaktualizuj swoja bazę danych.
Import danych tekstowych z pliku Kandydaci.txt do tabeli Kandydaci
1. Otwórz program Open Calc, otwórz w nim plik tekstowy jako typ csv
2. Zaznacz w arkuszu wszystkie dane i skopiuj do schowka: Edycja - Kopiuj lub CRTL-C
3. Otwórz program Open Base, utwórz nową bazę danych (nie rejestruj) i wpisz jej nazwę: telefony
4. Wybierz w oknie bazy danych: Tabele i wklej zawartość schowka Edycja-Wklej lub CTRL-V
podaj nazwę tabeli: Kandydaci
opcje wklejania:  Definicje i dane
 użyj pierwszego wiersza jako nazw kolumn
nie twórz klucza głównego
zastosuj wszystkie kolumny
dobierz typy danych w polach:
w polach: ID_k , wzrost oraz wiek ustaw typ INTEGER
w polu Symbol_woj ustaw typ varchar(1)
5. Otwórz tabelę i zobacz zaimportowane dane. Zamknij tabelę
6. Otwórz projekt tabeli: prawo-kliknij nazwę tabeli i wybierz: Edytuj, lub ikoną Edytuj:
ustaw klucz na polu Id_k : prawo-kliknij na nagłówku wiersza i wybierz Klucz_główny
Jeśli się nie uda,
to otwórz okienko poleceń SQL:
z menu Narzędzia / SQL
napisz polecenie w języku SQL
i Wykonaj
PO wykonaniu polecenia SQL odśwież tabele (z menu Widok / Odśwież tabele).
Podobnie zaimportuj dane z pliku woj.txt
Typ danych w polu Symbol_woj: varchar(1). Ustaw klucz na polu Symbol_woj.
Podobnie zaimportuj dane z pliku zawyk.txt
Typ danych w polu Id_k : INTEGER. Ustaw klucz na polu Id_k.
Zbuduj relacje między tabelami: z menu Narzędzia / Relacje otwórz okienko relacji,
dodaj wszystkie 3 tabele i połącz odpowiednie pola:
Jeśli się nie uda – spróbuj wykonać polecenia SQL w okienku Narzędzia / SQL
ALTER TABLE "Kandydaci" ADD FOREIGN KEY ("Id_k")
REFERENCES "Zawyk" ("Id_k")
ALTER TABLE "Kandydaci" ADD FOREIGN KEY ("Symbol_woj")
REFERENCES "Woj" ("Symbol_woj")
Po pomyślnym wykonaniu poleceń SQL odśwież tabele (menu Widok / Odśwież tabele) i sprawdź w oknie
Narzędzia / Relacje czy widać utworzone relacje.
Rozwiązania zadań:
1. Zliczenie kobiet i mężczyzn
% zastępuje dowolny łańcuch znaków,
inaczej niź w Accessie, tam była *
SELECT "Imie" FROM "Kandydaci" WHERE "Imie" LIKE '%a' - wyświetli imiona kobiet
SELECT Count("Imie") FROM "Kandydaci" WHERE "Imie" LIKE '%a'
- zliczy imiona kobiet
SELECT Count("Imie") FROM "Kandydaci" WHERE "Imie" NOT LIKE '%a' - zliczy mężczyzn
Uwaga: w Open Base nie ma pojęcia „kwerenda funkcjonalna”! Kwerenda tylko wybiera i grupuje!
jeśli zechcesz przepisać wszystkie kobiety do nowej tabeli, to rozwiązaniem nie jest kwerenda
(w sensie: query = zapytanie i zwrócenie wyniku), lecz polecenie, napisane w okienku Narzędzia / SQL:
SELECT "Imie","Nazwisko" INTO "Kobiety"
FROM "Kandydaci" WHERE "Imie" LIKE '%a'
Wykonaj i koniecznie Odśwież tabele aby zobaczyć efekt.
2. Zliczenie klientów poszczególnych sieci
SELECT "Nazwa_sieci", COUNT("Id_k") AS "ilu_klientow"
FROM "Kandydaci"
GROUP BY "Nazwa_sieci"
3. Podaj liczbę kandydatów z poszczególnych województw, wybierz
województwa z których jest przynajmniej 20 kandydatów
SELECT "Wojewodztwo", COUNT("Id_k") AS "Ilosc"
FROM "Kandydaci", "Woj"
WHERE "Kandydaci"."Symbol_woj" = "Woj"."Symbol_woj"
GROUP BY "Wojewodztwo"
HAVING (COUNT("Id_k")>20)
4. Zestawienie kandydatów (imię, nazwisko, nazwę województwa), którzy mają wykształcenie średnie lub
wyższe, interesują się grami komputerowymi i nie przekroczyli 50-ego roku życia. Podaj, ile wśród nich
jest osób z wykształceniem wyższym, a ile osób z wykształceniem średnim
SELECT "Nazwisko", "Imie", "Wojewodztwo", "Zainteresowania", "Wyksztalcenie", "Wiek"
FROM "Kandydaci", "Zawyk", "Woj"
WHERE "Kandydaci"."Id_k" = "Zawyk"."Id_k"
AND "Kandydaci"."Symbol_woj" = "Woj"."Symbol_woj"
AND "Kandydaci"."Wiek" <= 50
AND "Zainteresowania" LIKE 'gry%'
AND ("Wyksztalcenie" = 'średnie' OR "Wyksztalcenie" = 'wyższe')
Ilu z nich ma wykształcenie średnie a ilu wyższe: (Odp: 8/8, źródłem kwerendy - poprzednia kwerenda)
SELECT "Wyksztalcenie", COUNT("Id_k") AS "ilu"
FROM "4" GROUP BY "Wyksztalcenie"
5. Wybierz osoby, które interesują się polityką lub sportem, a ponadto dla kobiet wymagany jest wzrost co
najmniej 168 cm, w przypadku mężczyzn minimalny wzrost wynosi 175 cm. Podaj, ile kobiet i ilu
mężczyzn spełnia powyższe kryteria.
Lista (21 osób):
SELECT "Nazwisko", "Imie", "Wzrost", "Zainteresowania"
FROM "Kandydaci", "Zawyk"
WHERE "Kandydaci"."Id_k" = "Zawyk"."Id_k"
AND ( "Zainteresowania" = 'polityka' OR "Zainteresowania" = 'sport' )
AND (
"Imie" LIKE '%a' AND "Wzrost" >= 168
OR
"Imie" NOT LIKE '%a' AND "Wzrost" >= 175
)
Licznik kobiet (Odp: 14):
SELECT COUNT("Id_k") FROM "Kandydaci", "Zawyk"
WHERE "Kandydaci"."Id_k" = "Zawyk"."Id_k"
AND "Imie" LIKE '%a'
AND "Wzrost" >= 168
AND ("Zainteresowania" = 'polityka' OR "Zainteresowania" = 'sport')
6. Aktualizacja nazwy sieci ‘Hejka’ na ‘Heyhoo’ – jako polecenie SQL (nie kwerenda!)
UPDATE "Kandydaci" SET "Nazwa_sieci"='Heyhoo' WHERE "Nazwa_sieci"='Hejka'
Nazwy tabel i kolumn w cudzysłowach ” ”, zaś wartości tekstowe w pojedynczych apostrofach ‘ ‘.