Zadanie 8a
Do tabelek relacyjnej bazy danych realizującej poniższy diagram ER (zadanie 6) ...
Zadanie 8a
Do tabelek relacyjnej bazy danych realizującej poniższy diagram ER (zadanie
6) ...
... wprowadź następujące fakty/dane:
.....................................................................................
Kwerendy (=zapytania [ang. queries]) służą do "wyciągania" użytecznych informacji z zapisanych w tabelkach danych ...
Okienko kwerend może się znajdować w trybach:
Uwaga: w Accesie nie trzeba koniecznie znać języka SQL gdyż można tworzyć kwerendy w języku wizualnym QBE, a one są automatycznie tłumaczone na SQL !. "Jet" czyli tzw silnik bazodanowy (ang. engine) Accesu rozumie język SQL ...
Istnieją następujące typy kwerend:
Kwerendy aktualizujące - modyfikuje pola danego rekordu tabeli na podstawie innych pól tego rekordu; modyfikuje w ten sposób wszystkie rekordy spełniające podany warunek
Kwerendy dołączające - dołączają rekordy do tabel.
Kwerendy tworzące tabele - tworzą nową tabelę.
Ćwiczenie 9
Opisy kwerend funkcjonalnych
są dostępne w "Pomocy" programu Access. Spróbuj wpisać w polu "Wpisz pytanie
do Pomocy" (prawy górny róg okna programu Access) hasło "kwerendy", a następnie wybierz opcję "Typy kwerend --
informacje" ...
Tworzymy nową kwerendę guzikiem "Nowy" | "Widok projektu" (NIE włączamy żadnego kreatora !).
Jeśli wprowadzimy taką kwerendę:
to po jej "uruchomieniu" (czyli przejściu do trybu "Widok arkusza danych") dostaniemy:
Zadanie 9a
W magazynowej bazie danych realizującej poniższy diagram ER ...
... postaraj się utworzyć kwerendy (w siatce projektowe) dające opisane niżej
efekty.
Zapisuj każdą utworzoną kwerendę pod nazwą "Zadanie 9a (pkt ?)".
(Kto nie ma własnej bazy może użyć "bd02.mdb").
Aby kwerenda wybierająca stała się "kwerendą wybierającą z podsumowaniem"
trzeba spowodować aby się pojawił wiersz "Podsumowanie:" - włącza się go guzikiem
w okienku
projektowym kwerendy.
| Zasada: Można pogrupować rekordy tabeli i w każdej grupie rekordów obliczać funkcje agregującą danego pola lub wyrażenia |
Grupy rekordów definiuje się przez podanie pola (lub pól) które są równe w grupie (pod polem grupującym, w linii "Podsumowanie:" wybierz "Grupuj według").
Istnieją następujące funkcje agregujące:
patrz też pomoc Accesu:
podsumowanie
Obliczenia w kwerendzie -- informacje
Funkcje agregujące ...
Przykład 1:
Obliczamy ile jest zamówień na dany towar ([id towaru]):
Po uruchomieniu kwerendy dostaniemy:
Przykład 2:
To samo co poprzednio, dodatkowo użyjemy "Wyrażenia" w podsumowaniu";
(oczywiście jest to przykład "teoretyczny")
Zadanie 9b
W magazynowej bazie danych utwórz kwerendy dające opisane niżej efekty.
Zapisuj każdą utworzoną kwerendę pod nazwą "Zadanie 9b (pkt ?)".
(Można użyć magazynowej bazy danych bd02.mdb).
Zadanie 9c (;-);)
Na przykładzie kwerendy obliczającej "łączną wartość zamówień danego klienta"
pokaż, że jest różnica gdy tę wartość obliczmy na następujące sposoby:
Obliczaj łączną wartość zamówień na dwa sposoby w tej samej kwerendzie i tak dobierz dane w tabelach żeby było widać że coś jest nie tak ...
Aby utworzyć kwerendę parametryczną wystarczy użyć w kryteriach identyfikatora który nigdzie nie występuje.
Można zdefiniować typ parametrów kwerendy (menu kontekstowe obok tabelek | Parametry ...); wtedy nie da się wprowadzić wartości nieodpowiedniego typu.
Zadanie 9d
W magazynowej bazie danych chce zobaczyć zamówienia pewnego (dowolnego) klienta
które zostały złożone w podanym okresie od-do; ma to być kwerenda parametryczna;
po jej uruchomieniu powinno się pojawić pytanie o imię i nazwisko klienta oraz o
okres od-do; użyj operatora "between ? and ?"; ustaw odpowiednio typ
parametrów kwerendy !.
Jeśli w kwerendzie wybierającej z podsumowaniem:
to można wynik takiej kwerendy przedstawić w postaci "arkusza kalkulacyjnego", przy czym:
(w poniższej tabelce zakładamy że P1 przyjmuje wartości A, B, C oraz że P2 przyjmuje wartości 1,2,3,4)
| P1 | 1 | 2 | 3 | 4 |
| A | W dla grupy rekordów w której P1=A oraz P2=1 | ? | ? | ? |
| B | ? | W
dla grupy rekordów w której dla P1=B oraz P2=2 |
? | ? |
| C | ? | ? | ? | ? |
Trzeba zmienić typ kwerendy na kwerendę krzyżową, pojawi się linia
"Krzyżowe:" w projekcie kwerendy;
a następnie w linii "Krzyżowe:"
wybrać które pole grupujące ma być nagłówkiem wiersza,
wybrać które pole grupujące ma być nagłówkiem kolumny,
wybrać które zagregowane pole ma być wartością.
Zadanie 9e
W magazynowej bazie danych utwórz kwerendę krzyżową pokazującą wartość zamówień
na poszczególne towary z podziałem na miesiące. Kwerenda ta powinna wyglądać
tak:
Wskazówki do
zadania 9e:
Na początku utwórz taką kwerendę::
| nazwa towaru | miesiąc | suma zamówień |
| qqq1 | 01/2003 | 1232 |
| qqq1 | 02/2003 | 100 |
| qqq2 | 01/2003 | 1232 |
| qqq2 | 02/2003 | 100 |
| qqq2 | 03/2003 | 2000 |
| ... |
aby to osiągnąć musisz grupować wg:
Towary.[id towaru] i Towary.nazwa (ale pokazywać
tylko nazwę towaru !)
oraz wg pola wyliczanego następującej postaci:
miesiąc : Format([data];"mm/rrrr");
(funkcja Format() przedstawia datę w wybranym przez nas
formacie, "mm" - miesiąc, "rrrr" - rok)
ponadto dla każdej grupy rekordów musisz zagregować sumę iloczynów ilosc*cena,
czyli:
suma zamówień : Suma([cena]*[ilosc])
Następnie zamień powyższą kwerendę na kwerendę krzyżową - w tym celu:
1. zmień typ kwerendy z wybierającej na krzyżową (w trybie projektowania
kwerendy, menu kontekstowe, opcja "Typ kwerendy");
pojawi się linia "Krzyżowe:" ...
2. jako nagłówek wiersza (w linii "Krzyżowe:") wybierz nazwę towaru;
jako nagłówek kolumny wybierz miesiąc;
jako wartość wybierz sumę zamówień.
Powinno to wyglądać tak:
a po uruchomieniu kwerendy ...
(koniec wskazówek do zadania 9e.)
SQL to "tekstowy" język do zapisywania kwerend (w przeciwieństwie do QBE który jest "wizualny") ...
Dlaczego QBE musi być tłumaczone na SQL ?
Odp:
gdyż Jet - czyli silnik bazodanowy Accesu - "rozumie" tylko SQL-a
!
SQL to standard używany przez wszystkie relacyjne bazy danych;
istnieją następujące wersje SQL-a:
Najważniejsze polecenie języka SQL to polecenie SELECT ...
ogólna postać polecenia SELECT:
SELECT [predykat] { * | tabela.* | [tabela.]pole1 [AS alias1] [, [tabela.]pole2 [AS alias2] [, ...]] }
FROM wyrażenie_tabelowe [, ...] [IN zewnętrzna_baza_danych]
[WHERE... ]
[GROUP BY... ]
[HAVING... ]
[ORDER BY... ]opis polecenia SELECT dialektu SQL Access-u - patrz tutaj
dokumentacja języka SQL Postgres-u i InterBase-a - patrz tutaj
proste przykłady poleceń SQL - patrz tutaj
Przykład 1:
Następująca kwerenda w języku wizualnym QBE:
Dająca w wyniku:
Po przetłumaczeniu na SQL wygląda tak:
SELECT Towary.nazwa,
Sum(Zamowienia.ilosc) AS SumaOfilosc,
Sum(Towary.cena) AS SumaOfcena,
Sum([ilosc]*[cena]) AS wartość,
Sum([ilosc])*Sum([cena]) AS [zła wartość]
FROM Towary INNER JOIN
(Klienci INNER JOIN Zamowienia
ON Klienci.[id klienta] = Zamowienia.[id klienta])
ON Towary.[id towaru] = Zamowienia.[id towaru]
WHERE (((Klienci.[imie i nazwisko])="Jan Kowalski1"))
OR (((Klienci.[imie i nazwisko])="Jan Kowalski2"))
GROUP BY Towary.nazwa
HAVING (((Towary.nazwa)="qqq3")) OR (((Towary.nazwa)="qqq4"))
ORDER BY Towary.nazwa;
Uwaga 1: warunek WHERE działa na złączeniu tabel; warunek HAVING działa po grupowaniu/agregowaniu (czyli na tabeli z "podsumowaniami" grup rekordów).
Uwaga 2: w HAVING możemy się odwoływać do pól grupujących, jak i do zagregowanych wartości !.
Zadanie 10
Do powyższej kwerendy dodaj warunek wybierający wiersze dla których "wartość > 1000".
Zobacz jak taka kwerenda wygląda po (automatycznym) przetłumaczeniu na SQL !.
Do tej pory gdy złączaliśmy tabele Zamówienia i Klienci to widzieliśmy tylko tych klientów którzy złożyli przynajmniej jedno zamówienie ...
Można zmienić typ złączenia tak że zobaczymy także klientów bez zamówień, a pola
tabeli Zamówienia będą miały dla takich klientów wartość NULL. W widoku
projektu kwerendy kliknij na linię łączącą dwie tabele i wybierz "Właściwości
sprzężenia", następnie kliknij odpowiedni guzik ...
Zadanie 10a
Utwórz kwerendę pokazującą klientów i ich zamówienia, w tym także
klientów którzy nie złożyli żadnego zamówienia (ale bez informacji o towarach -
NIE wprowadzaj tabeli towary !). Zobacz jak ta kwerenda wygląda w SQLu (LEFT/RIGHT
JOIN); zajrzyj także tutaj.
Zadanie 10b
Chce zobaczyć tabelę zawierającą klientów i ich zamówienia w tym także
klientów którzy nie złożyli żadnego zamówienia ORAZ informacje o towarach
występujących na tych zamówieniach.
Zauważ że nie można mieszać różnych rodzajów złączeń tabel w dowolny sposób !
(musi powstać "ścieżka skierowana" w okienku projektowym kwerendy).
Zobacz jak ta kwerenda wygląda w SQLu.
Kwerendy złożone to takie w których używa się nie tylko tabel, ale także wcześniej zdefiniowanych kwerend ...
Kwerendy złożone pozwalają budować bardzo skomplikowaną kwerendę "z klocków" (każdy klocek budowli sam w sobie jest prosty, ale cała budowla - niekoniecznie !).
Zadanie 11Ceny (id ceny, cena, data, id towaru)
Chcemy otrzymać tabelę w której dla każdego towaru jest widoczna jego bieżąca cena (czyli ta z maksymalną datą).
Wskazówki do zadania 11:
1. sporządź kwerendę z podsumowaniem, opartą na tabeli Ceny, w której grupujemy
wg pola [id towaru] i obliczamy maksymalną datę; nazwij tę kwerendę Cena2;
2. sporządź kwerendę w której występują tabele: Ceny, Towary i kwerenda Cena2;
złącz odpowiednio te tabele/kwerendy aby otrzymać wynikową tabelę następującej
postaci:
| id towaru | nazwa towaru | aktualna cena | data |
| 1 | qqq1 | 15zl | 1/10/2003 |
| 2 | qqq2 | 25zl | 5/10/2003 |
Powiedzmy że z tabeli Zamówienia chcemy wybrać zamówienia klientów których id znajdują się w tabeli:
Wybrani_kli (id klienta)
Wystarczy złączyć tabelę Zamówienia i Wybrani_kli na polu [id klienta] (złączenie
naturalne typu INNER JOIN) !.
W takim złączeniu z iloczynu kartezjańskiego obu tabel wybieramy tylko te tylko
te rekordy w których pola [id klienta] są równe, tak więc w szczególności
wybieramy tylko te zamówienia których [id klienta] figuruje w tabeli Wybrani_kli
(a że [id klienta] w Wybrani_kli jest kluczem, każde zamówienie wystąpi tylko
raz).
Zadanie 12
Mamy następujący diagram:
Zdefiniuj odpowiednie tabele, następnie sporządź kwerendę:
Wskazówki do zadania 12 (... a właściwie kompletne rozwiązanie tego zadania):
Na powyższym ERD brak kluczy [id ksiazki] i [id slowa], które należało dodać.
Diagram realizujemy przy pomocy 3 tabelek: Ksiazki, Slowa_kluczowe, Ksiazki_o
(reprezentuje związek wiele-do-wielu).
Zadanie to można rozwiązać przy pomocy następujących kwerend:
Uwaga 1: sposób użycia tabeli Poszukiwane_slowa
w kwerendzie "Książki 1" to opisana wyżej metoda wybierania z tabeli podzbioru
rekordów, który jest określony w innej tabeli (właśnie w tabeli Poszukiwane_slowa);
warto nałożyć indeks na [słowo] w tabeli Słowa_kluczowe !
Uwaga 2: w "Ksiązki 2", w kryteriach, użyto kwerendy zwracającej
pojedynczą wartość !
Uwaga 3: jak uaktualniać otwartą
kwerendę po modyfikacji tabeli Poszukiwane_slowa ?
Odp: klawisz Shift+F9
(Koniec wskazówek do zadania 12.)
Zadanie 12a
Wypróbuj rozwiązanie powyższego zadania dla następujących danych:
| książka | zawiera słowa kluczowe |
| k1 | a |
| k2 | a,b |
| k3 | a,b,c |
Poszukaj książek z następującymi zbiorami słów kluczowych:
Zadanie 12b (?)
Rozwiąż zadanie 12 stosując zagnieżdżone
kwerendy z parametrem. Kwerendy powinny mieć nazwy "Slowa 1", "Slowa 2", ...;
jeśli szukamy książki z "k" słowami kluczowymi, to powinniśmy uruchomić kwerendę
"Slowa k" !.
.....................................................................
Zadanie X1 (długie)
Pewna przychodnia lekarska gromadzi następujące informacje o pacjentkach w
takich arkuszach kalkulacyjnych:
Przenieś dane z arkusza do tabeli Access-u:
Oraz skonstruuj następujące kwerendy:
