Bazy danych
Kwerendy (zapytanie, pole obliczeniowe, grupowanie)
Tabela STUDENCI
W celu uzyskiwania informacji z bazy danych i pracą z danymi opracowano specjalny język zapytań - SQL.
Graficznym jego odpowiednikiem programie Access są obiekty zwane kwerendami. Gdy konstruujemy
kwerendy w specjalnym oknie Access tworzy jej kod w tle za pomocą instrukcji języka SQL. Istnieją dwa
podstawowe rodzaje kwerend: wybierające i funkcjonalne.
Wybierające służą do uzyskiwania informacji z bazy danych według określonych kryteriów. Pobierają one
dane z jednej lub wielu tabeli (albo też innych kwerend). W wyniku działania kwerendy zostaje „utworzona”
wirtualna tabela z wynikami. Po zakończeniu działania kwerendy ta wirtualna tabela przestaje istnieć.
Najprostsze kwerendy potrafią wyświetlić określone pola i rekordy. Kwerendy mogą dokonywać obliczeń i
wyniki przedstawiać w osobnych polach, mogą podsumowywać i grupować informacje, a także umożliwić
analizę wpływu danych jednego typu na inne dane - kwerendy krzyżowe.
Funkcjonalne nie tylko potrafią modyfikować dane w tabelach - kwerenda aktualizująca, ale też tworzyć
tabele. Kwerendy dołączające dodają rekordy z innej tabeli, usuwające - usuwają wybrane rekordy.
1. Utwórz kwerendę, która wyświetli NAZWISKO i IMIE studenta.
-
z zakładki Kwerendy wybieramy polecenie Utwórz kwerendę w
widoku projektu. Zaznaczamy tabelę Studenci, klikamy w przycisk
Dodaj (okienko z polami tabeli pojawi się w górnej części kreatora
kwerend), klikamy w przycisk Zamknij.
-
wstawiamy pole NAZWISKO i pole IMIE do odpowiednich pól
kreatora za pomocą podwójnego kliknięcia w te nazwy. Można też
przeciągnąć te nazwy do kreatora, albo wybrać klękając w Pole:.
-
uruchamiamy kwerendę i wirtualną tabelę z wynikami za pomocą
ikony
w pasku narzędzi
-
zapisujemy kwerendę z nazwą WYB01 - jej ikona pojawia się w panelu bazy i będzie ją można uruchamiać
wiele razy
-
wracamy do trybu projektowania kwerendy pukając w ikonę
-
zamykamy kwerendę
2. Utwórz kwerendę o nazwie WYB02, która wyświetli pola NAZWISKO i IMIE. Wybierz rekordy
zawierające wyraz „Anna”.
-
za pomocą widoku projektu, jak w poprzednim przykładzie tworzymy identyczną kwerendę i zapisujemy
pod nazwą WYB02
-
w wierszu Kryteria: wpisujemy wyraz „Anna” (cudzysłowy
zostaną wstawione automatycznie)
-
w wierszu Sortowanie: wybierz polecenie Rosnąco w
kolumnie NAZWISKO, a wyświetlone rekord zostaną
odpowiednio uporządkowane
-
po uruchomieniu kwerendy zostanie wyświetlone tylko 17 rekordów.
-
ustaw w następnym wierszu Lub: wyraz „Katarzyna” - wtedy zostaną wyświetlone rekordy osób o imieniu
Anna lub Katarzyna
3. Utwórz kwerendę o nazwie WYB03, która wyświetli pola NUMER, NAZWISKO, IMIE, SZKOLA, dla
osób, które mają na imię „Anna” i ukończyły szkołę o symbolu „LO”.
-
tworzymy kwerendę w widoku projektu, wstawiamy 4 pola i zapisujemy pod nazwą WYB03
-
w wierszu Kryteria, kolumnie IMIE wpisujemy wyraz „Anna”, a w kolumnie SZKOLA
wpisujemy „LO”
-
po uruchomieniu wyświetlone zostaną rekordy dla Ań, które ukończyły Liceum
Ogólnokształcące.
UWAGA. Aby poprawnie sformułować kryterium należy sprawdzić, w jaki sposób wpisano informacje do
tabeli, na podstawie której wyświetlane są informacje w kwerendzie. Na przykład pole PLEC (płeć) zawiera
litery „K” lub „M” na określenie kobiet i mężczyzn, pole ZDAL (zdał egzamin) zawiera wyraz „tak” lub
„nie”, a pole AKADEMIK litery „T” lub „N”. Pole URODZONY zawiera daty urodzin wpisane według
schematu RRRR-MM-DD i próba wpisania jej w kryterium w innej kolejności będzie błędem.
Tabela przedstawia różne przykłady wpisów do kryteriów
Pole
Wyrażenie
Opis – wyświetlane są rekordy które:
IMIE
„Anna”
Pole IMIE jest równe Anna
IMIE
„Anna” OR „Joanna”
Pole IMIE jest równe Anna lub Joanna
IMIE
In(„Anna”; „Joanna”)
Pole IMIE jest równe Anna lub Joanna
NUMER
>=100 AND <= 200
Pole NUMER jest od 100 do 200
NUMER
BETWEEN 100 AND 200
Pole NUMER jest od 100 do 200
NUMER
50 OR >100
Pole NUMER jest równe 50 lub większe od 100
MIASTO
NOT „Przemyśl”
Pole MIASTO bez Przemyśla
SZKOLA
Like „L*”
Pole SZKOLA zaczyna się na literę L
IMIE
Like „A*a”
Pole IMIE zaczyna się na „A” i kończy się na „a”
IMIE
Like „*na*”
Pole IMIE zawiera w środku litery „na”
IMIE
Like „A?a*”
Pole IMIE zaczyna się na literę A, druga litera dowolna, trzecia „a” i
reszta dowolna
IMIE
Like „[A-D]*”
Pole IMIE może zaczynać się na litery A, B, C, D
IMIE
Like „[!A-D]*”
Pole IMIE nie może zaczynać się na litery A, B, C, D
ULICA
Like „* #”
Pole ULICA ma dowolny początek, spację i ostatnią cyfrę
NAZWISKO
>=”N”
Pole NAZWISKO zaczyna się na litery od N do Z
ULICA
Is Null
Pole ULICA jest puste
4. Utwórz kwerendę o nazwie WYB04, która wyświetli NAZWISKO, IMIĘ, DOCHOD, dla tych studentów,
którzy mają dochody w przedziale 500..600 zł.
5. Utwórz kwerendę o nazwie WYB05, która wyświetli NAZWISKO, IMIĘ, dla tych studentów, którzy
urodzili się w 1974 roku.
-
w kreatorze wstawiamy również pole URODZONY pomimo tego, że
nie będziemy go wyświetlać
-
likwidujemy „fajkę”
- pole nie będzie wyświetlane w kwerendzie
-
wpisujemy kryterium wyboru rekordów (możemy pominąć znaki #, gdyż system wstawia je sam)
Kwerenda z zapytaniem (parametryczna)
6. Utwórz kwerendę z zapytaniem i imię studenta. Wyświetlone zostaną Nazwiska tych osób. Kwerenda o
nazwie WYB06.
-
ustawiamy projekt według schematu
-
zamiast konkretnego imienia wstawiamy w wierszu Kryteria, w kwadratowych nawiasach zapytanie
-
uruchamiamy kwerendę i w pojawiającym się okienku zapytania wpisujemy odpowiednie imię
-
wyświetlone zostaną odpowiednie rekordy
-
powtórne uruchomienie kwerendy znów wywoła okienko zapytania i będzie można wyświetlić inne osoby.
Pole obliczeniowe
7. Utwórz kwerendę o nazwie WYB07,
która wyświetli pole DOCHOD oraz
utworzy nowe pola: PODATEK i
OSOBA, które będą zawierały:
podatek- 45% z pola dochód, osoba - nazwisko i imię
rozdzielone spacją. Wyświetlamy rekordy, dla osób
których dochód przekracza 1000 zł.
-
sposób tworzenia nowego pola opisany jest po
dwukropku, za nazwą pola
Podsumowanie
8. Utwórz kwerendę o nazwie WYB08, która wyświetli średnią
ocenę uzyskaną przez studentów w każdym mieście.
-
ustawiamy kolumny w projekcie według schematu. Pole
ŚREDNIA będzie wyświetlało ocenę studenta
-
nowy wiersz Podsumowanie: pojawi się po wybraniu z
listwy przycisku
-
kolumna MIASTO ma ustawione Grupuj według, co
oznacza, że wszystkie miasta o tej samej nazwie będą
wyświetlane jako jedno i policzona zostanie dla nich średnia (suma, ilość, maksimum, minimum w
zależności od wybranej opcji)
-
kolumna ŚREDNIA - wybieramy zamiast polecenia Grupuj według polecenie Średnia
-
uruchamiamy kwerendę w identyczny sposób
UWAGA. Suma, średnia, maksimum i minimum muszą być wybrane dla pól zawierających liczby lub daty
(nie można podsumować tekstów). Natomiast opcję policz (zliczanie ilości rekordów) można wybrać dla
dowolnego pola.
Do samodzielnej realizacji
1.
Wyświetl kolumny: nazwisko, imie, dochod. Wybierz wszystkie kobiety z imieniem Anna.
2.
Wyświetl kolumny: nazwisko, imie, dochod. Wybierz wszystkie osoby mieszkające w Przemyślu.
3.
Wyświetl kolumny: nazwisko, imie. Wybierz wszystkie kobiety mieszkające w Przemyślu.
4.
Wyświetl kolumny: nazwisko, imie. Wybierz wszystkie kobiety o imieniu Anna mieszkające w
Przemyślu.
5.
Wyświetl kolumny: nazwisko, imie. Wybierz osoby, które zdały egzamin i zostały przyjęte.
6.
Wyświetl kolumny: nazwisko, imie. Wybierz osoby, które mieszkają w Przemyślu lub Krośnie.
7.
Wyświetl wszystkie kolumny. Wybierz wszystkie rekordy. Uporządkuj je według rosnących dochodów
8.
Wyświetl wszystkie kolumny. Wybierz wszystkie rekordy. Uporządkuj je alfabetycznie według nazwisk i
imion
9.
Wyświetl kolumny: nazwisko, imie, dochod
10. Wybierz wszystkie osoby mieszkające w Przemyślu. Uporządkuj je według malejących dochodów.
11. Wyświetl kolumny: nazwisko, imie, dochod. Wybierz rekordy osób, które nie mieszkają w Przemyślu i
nie mają na imię Anna
12. Wyświetl kolumny: nazwisko, imie, miasto. Wyświetl rekordy dla osób mieszkających w mieście
zaczynającym się na literę K
13. Wyświetl kolumny: nazwisko, imie, urodzony. Wyświetl rekordy dla osób urodzonych w roku 1973.
Uporządkuj je rosnąco według dat urodzin.
14. Wyświetl kolumny: nazwisko, imie. Użyj zapytania o imię
15. Wyświetl kolumny: nazwisko, imie, urodzony
16. Wyświetl rekordy dla osób urodzonych w roku 1973. Użyj zapytania o imię. Uporządkuj je rosnąco
według dat urodzin.
17. Wyświetl kolumny: nazwisko, imie, ocena. Wyświetl rekordy, w których ocena mieści się w przedziale
<minimum..maksimum>. Użyj zapytania o minimum i maksimum.
18. Wyświetl listę miast, z których pochodzą studenci (każde miasto jeden raz)
19. Wyświetl listę imion i uporządkuj ją alfabetycznie
20. Wyświetl listę imion osób, których imię ma dokładnie 6 liter.
21. Wyświetl kolumny: nazwisko, imie, dochod i nową kolumnę o nazwie PODATEK, której wartość
wyliczamy z wzoru: podatek=22%*dochod.
22. Wyświetl kolumny nazwisko i imię. Utwórz nową kolumnę o nazwie PERSONA, która zawiera sklejone
nazwisko i imię ze spacją w środku. Uporządkuj tabelę według nazwisk i imion.
23. Wyświetl kolumny nazwisko, imię i utwórz nową kolumnę o nazwie ROK, która będzie pokazywała rok
urodzenia osoby.
24. Utwórz nową kolumnę o nazwie ROK. Wyświetl listę wszystkich lat urodzenia.
25. Wyświetl kolumny nazwisko, imię i utwórz nową kolumnę o nazwie DNI, która będzie pokazywała dni
życia poszczególnych osób do dzisiaj
Wyświetl kolumny nazwisko, imię i utwórz nową kolumnę o nazwie DNI, która będzie pokazywała dni życia
poszczególnych osób do dzisiaj. Użyj zapytania o liczbę dni życia osób z przedziału pomiędzy minimum i
maksimum i wybierz tylko te osoby.