Wszystkie ćwiczenia są tworzone w oparciu o SQL zaimplementowany w MS Access 97.
1.1. Podstawowe operacje w MS Access
Ćwiczenie 1.1.1.
Utwórz nową bazę danych pod nazwą testowa.
Uruchom program MS Access dwukrotnie klikając jego ikonę, znajdująca się na pulpicie lub wybierając polecenie z menu Start - Programy.
Wybierz możliwość utworzenia nowej bazy danych.
Zachowaj w wybranym katalogu pod nazwą testowa. Rozszerzenie .mdb charakteryzujące bazy danych MS Access zostanie dodane przez program.
Rozszerzenia plików nie zawsze są wyświetlane. Zależne jest to od parametrów MS Windows.
Dopiero po wykonaniu powyższych kroków można przejść do tworzenia tablic, wprowadzania do nich danych oraz przeszukiwania ich.
Wszystkie zadania będą wykonywane w oknie SQL, którego otwieranie zostanie opisanie w ćwiczeniu 1.1.2.
Ćwiczenie 1.1.2.
Otwórz bazę danych pod nazwa testowa, która powstała w ćwiczeniu 1.1.1.
W oknie bazy danych przejdź do okna kwerendy i wybierz przycisk nowy.
Z okna zatytułowanego Nowa kwerenda wybierz polecenie Widok projekt.
Zaakceptuj operacje Widok Projekt przez wybranie OK.
Zamknij przyciskiem okno Pokaż tabele.
Otwórz okno SQL, wybierając z paska menu polecenie Kwerenda, następnie opcje Wyłączenie SQL oraz Definiująca dane.
kwerenda składająca - kwerenda tego typu pozwala utworzyć tablice. Służy również do wybierania danych spełniających określone warunki, łącząc atrybuty z jednej lub kilku tablic lub innych kwerend.
Każda użyta instrukcja select przy wyświetlaniu wybranych danych musi zwracać taka sama liczbę pól w tej samej kolejności. Odpowiadające sobie pola musza mieć zgodne typy danych.
kwerenda przekazująca - kwerenda przekazująca polecenia bezpośrednio do baz danych, jak na przykład Microsoft SQL Server, przy użyciu poleceń, które są akceptowane przez serwer, czyli są napisane w zrozumiałym dla niego języku. W przypadku przykładowej bazy utworzonej w poniższych ćwiczeniach nie ma racji bytu. Pojawia się przy dużych projektach, obsługujących kilka lub kilkanaście tysięcy rekordów i setki tablic.
kwerenda definiująca dane - służy do tworzenia lub zmiany tablic i innych kwerend.
W MS Access również należy akceptować polecenia wysyłane do bazy danych. Jeżeli chcemy wprowadzić kolejne polecenie SQL, powinno się poprzednie usunąć z ekranu. W przeciwnym wypadku może pojawić się błąd. Wynika to z tego, że Access wykonuje wszystkie polecenia znajdujące się na ekranie, zaczynając od pierwszego. Może się wiec zdarzyć, że polecenia zostały już wcześniej wykonane. Jeżeli będą to polecenia wyświetlania danych, to dane zostaną jedynie powtórnie uwidocznione na ekranie. Jeżeli jednak będzie to polecenie tworzące tablicę, usuwające rekordy lub atrybuty, pojawi się wtedy błąd składniowy.
Szkoła
Utwórz bazę, która będzie wykorzystywana w szkole do ułatwienia przydziału zajęć dla nauczycieli, jak i również umożliwi przeglądanie przypisanych zajęć poszczególnym klasom.
Założenia:
Każdy z nauczycieli ma jedną specjalizację, czyli prowadzi tylko jeden przedmiot.
Każdy z nauczycieli ma lekcje w różnych klasach.
Ćwiczenie 1.2.1.
Zgodnie z ćwiczeniem 1.1.1 utwórz bazę pod nazwą szkoła.
Przy powyższych założeniach będą potrzebne w bazie następujące tablice:
Tablice:
nauczyciele - identyfikator, nazwisko, imię, specjalizacja
klasy - identyfikator, klasy, ilość uczniów
nauka - identyfikator klasy, pochodzący z tablicy klasy, identyfikator nauczyciela uczącego przedmiotu, pochodzący z tablicy nauczycieli, liczba godzin danego przedmiotu w tygodniu.
Kolejnym zadaniem po ustaleniu założeń potrzebnych do utworzenia bazy, będzie wykreowanie tablic. Wszystkie tabele są generowane przy użyciu języka SQL w oknie kwerendy definiującej dane. Nie można też zapomnieć o wysyłaniu poleceń SQL do bazy danych przez wybranie przycisku uruchamiającego kwerendę.
Ćwiczenie 1.2.2.
W bazie szkoła utwórz tablicę nauczyciele.
Otwórz bazę szkoła.
Aby otworzyć istniejącą bazę danych należy uruchomić Accessa, a następnie we wstępnym oknie
MS Access wybrać opcję otwierania istniejącej bazy danych.
Jeżeli na liście plików nie ma potrzebnej bazy, należy wybrać Więcej plików... Pojawi się wtedy okno Otwórz, w którym należy znaleźć potrzebny plik.
Przejdź do okna kwerendy definiującej dane (patrz ćwiczenie 1.1.2).
W oknie kwerendy wprowadź poniższe polecenie SQL:
create table nauczyciele (id_naucz char(4) constraint
id_naucz_pk primary key,
nazwisko char(15)
imie char(15)
przedmiot char(10));
Wyślij polecenie do bazy.
Przy tworzeniu atrybutu id_naucz posłużyłeś się klauzulą constraint, która pozwala skonstruować pole primary key. W innej sytuacji pozwoli na ustawienie połączenia między tablicami za pomocą klucza obcego (patrz ćwiczenie 1.2.6).
Aby sprawdzić, czy została utworzona prawidłowa tablica, powinieneś wyświetlić jej zawartość lub obejrzeć jej strukturę.
Ćwiczenie 1.2.3.
Wyświetl zawartość i strukturę tablicy nauczyciele.
W oknie kwerendy składającej wypisz poniższe polecenie:
Select * from nauczyciele;
Na ekranie zostanie wyświetlona pusta tablica nauczyciele. Jednak sama możliwość zobaczenia jej świadczy o prawidłowości utworzenia.
Aby powrócić do okna poleceń SQL wystarczy użyć przycisku widok, znajdującego się na pasku narzędzi oraz następnie wybrać opcję Widok SQL.
Kolejne ćwiczenie pozwoli natomiast obejrzeć zawartość tablicy oraz jej strukturę. Zadanie to jednak nie korzysta z języka SQL.
Ćwiczenie 1.2.4.
Wyświetl zawartość i strukturę tablicy nauczyciele.
Przejdź do okna bazy danych.
Wybierz kartę tabele.
Otwórz tablicę nauczyciele przez wybranie przycisku Otwórz lub przez dwukrotne kliknięcie ikony z lewej strony nazwy tablicy.
Przejdź do okna projektu przez naciśnięcie przycisku widok.
Do widoku projektu można też przejść bezpośrednio z ona bazy danych przy wyświetlonej karcie tabel. Wystarczy podświetlić tablicę i wybrać przycisk Projekt, znajdujący się po prawej stronie okna.
W kolumnie pod nazwą Nazwa pola znajdują się nazwy atrybutów. Druga kolumna pod nazwą Typ danych zawiera rodzaj przechowywanych danych. Natomiast kolumna pod nazwą Opis przechowuje dodatkowy tekst, opisujący atrybut. Kolumny Nazwa pola i Typ danych muszą być obowiązkowo wypełnione, natomiast kolumna Opis może pozostać pusta. W dolnej części ekranu znajduje się miejsce na opisanie Właściwości atrybutu. Na przykładzie właściwości id_naucz widać, że atrybut może przechowywać co najwyżej 4 znakowe dane, wypełnienie pola nie jest wymagane. Można wprowadzić atrybut o zerowej długości, czyli może pozostać pusty. Zaznaczone jest, że jest to atrybut indeksowany oraz, że jest to primary key. O tym ostatnim świadczy symbol kluczyka po lewej stronie nazwy atrybutu.
Ćwiczenie 1.2.5.
W bazie szkoła utwórz tablicę klasy.
Otwórz bazę szkoła
Przejdź do okna kwerendy definiującej dane (patrz ćwiczenie 1.1.2).
W oknie kwerendy wprowadź poniższe polecenie SQL.
create table key (id_klasy char(5) constraint
id_klasy_pk primary key, ilosc integer);
Wyślij polecenie do bazy.
Ćwiczenie 1.2.6.
W bazie szkoła utwórz tablicę nauka.
Otwórz bazę szkoła
Przejdź do okna kwerendy definiującej dane (patrz ćwiczenie 1.1.2).
W oknie kwerendy wprowadź poniższe polecenie SQL.
create table nauka (id_klasy char(5) constraint
klasa_przedm_fk references klasy (id_klasy),
id_naucz char(4) constraint id_naucz_fk references
nauczyciele (id_naucz),
liczba_godz integer);
Wyślij polecenie do bazy.
Użycie klauzuli constraint pozwala na stworzenie połączenia między atrybutem id_klas a atrybutem id_klas znajdującym się w tablicy klasy. Sam link (połączenie) nosi nazwę klasa_przedm_fk. Tworzenie nazw zależy wyłącznie od użytkownika. Jednak przyjęcie reguły wprowadzenia do nazwy linka znaków fk (ang. Foreign key), pozwoli na łatwe odróżnienie ich pól od pól primary key.
Taki utworzony link można porównać z kanałem, którym są przesyłane dane między tablicami.
Po wykonaniu wszystkich zadań związanych z tworzeniem tablic, należy zapoznać się ze schematem relacji między nimi. Relacje zostały nawiązane już w momencie kreowania atrybutów.
Ćwiczenie 1.2.7.
Wyświetl relacje, które zachodzą między tablicami w bazie szkoła.
Rozwiń menu Narzędzia i wybierz opcję Relacje.
Między tablicą klasy a tablicą nauka zachodzi tzw. Relacja jeden do wielu. Świadczą o tym symbole, które pojawiły się przy liniach symbolizujących linki między tablicami. Przy atrybucie id_klasy w tablicy klasy i przy atrybucie id_naucz w tablicy nauczyciele pojawiła się 1. A przy atrybutach o tych samych nazwach, ale znajdujących się w tablicy nauka pojawił się symbol nieskończoności 8. Przez bazę link między tablicami klasy a nauka jest tłumaczony następująco:
Każda klasa może wielokrotnie występować w tablicy nauka.
Taka sama relacja jeden do wielu zachodzi między tablicą nauczyciele a tablicą nauka i tłumaczona jest przez bazę następująco:
Każdy nauczyciel może wielokrotnie występować w tablicy nauka.
W tej chwili biorąc pod uwagę zachodzące powiązania między tablicami, można je interpretować w taki sposób, że:
Każdy nauczyciel może uczyć w wielu klasach, a w każdej z nich może prowadzić różną ilość godzin zajęć.
Mogą również występować relacje jeden do jednego. Pojawiają się one wtedy, kiedy atrybut z jednej tablicy odpowiada tylko jednemu atrybutowi z drugiej tablicy.
Ćwiczenie 1.2.8.
Wprowadź dane do tablicy nauczyciele.
Otwórz bazę szkoła.
W oknie kwerendy definiującej dane wprowadź 5 nowych rekordów, korzystając z polecenia:
insert into nauczyciele
values (...);
Ćwiczenie 1.2.9.
Wyświetl zawartość tablicy nauczyciele (patrz ćwiczenie 1.2.3).
Ćwiczenie 1.2.10.
Wprowadź dane do tablicy klasy.
Otwórz bazę szkoła.
W oknie kwerendy definiującej dane wprowadź 9 nowych rekordów, korzystając z polecenia:
insert into klasy
values (...);
Ćwiczenie 1.2.11.
Wyświetl zawartość tablicy klasy (patrz ćwiczenie 1.2.3).
Ćwiczenie 1.2.12.
Wprowadź dane do tablicy nauka.
Otwórz bazę szkoła.
W oknie kwerendy definiującej dane wprowadź 9 nowych rekordów, korzystając z polecenia:
insert into nauka
values (...);
Ćwiczenie 1.2.13.
Wyświetl zawartość tablicy nauka.
Po zbudowaniu tablic i wpisaniu w nie danych, będziemy wyszukiwać potrzebne dane. W pierwszym ćwiczeniu z tej grupy zadaniem będzie wyliczenie liczby godzin, jakie ma do przepracowania wybrany nauczyciel. Dodatkowym utrudnieniem będzie nadanie nazwy atrybutowi, który powstanie dla wyliczonych danych. Do tego celu używa się słowa as (pol. jako). Użyte w poniższym ćwiczeniu wyrażenie:
select sum(liczba_godz) as suma_godzin
tłumaczone jest jako:
wykonaj sumowanie atrybutu liczba godzin i wyświetl w kolumnie widocznej jako suma_godzin
Ćwiczenie 1.2.14.
Wylicz liczbę godzin przepracowanych przez wybranego nauczyciela we wszystkich klasach.
Otwórz bazę szkoła.
W oknie kwerendy składającej dane wykonaj polecenie:
select sum(liczba_godz) as suma_godzin
from nauka
where id_naucz = `jkow';
Ćwiczenie 1.2.15.
Wylicz liczbę godzin przepracowanych przez każdego z nauczycieli we wszystkich klasach. Kolumnę z wynikiem nazwij suma_godzin.
Otwórz bazę szkoła.
W oknie kwerendy składającej dane wykonaj polecenie:
select id_naucz, sum(liczba_godz) as suma_godzin
from nauka
group by id_naucz;
Ćwiczenie 1.2.16.
Wylicz liczbę godzin dla każdej klasy. Kolumnę z wynikiem nazwij suma_godzin.
Otwórz bazę szkoła.
W oknie kwerendy składającej dane wykonaj polecenie:
select id_klasy, sum(liczba_godz) as suma_godzin
from nauka
group by id_klasy;
Ćwiczenie 1.2.17.
Wyświetl wszystkie klasy, w których zajęcia prowadzi określony nauczyciel, np. Kowalski.
Otwórz bazę szkoła.
W oknie kwerendy składającej dane wykonaj polecenie:
select nauka.id_klasy
from nauczyciele
where nauka.id_naucz = nauczyciele.id_naucz
and nauczyciele.nazwisko = `kowalski';
Wypożyczalnia płyt DVD
Utwórz bazę, pomocną przy obsłudze wypożyczalni DVD. W bazie powinny być zapisane dane wszystkich klientów, ich wypożyczenia oraz informacje o dostępnych płytach.
Założenia:
Każdy z klientów może wypożyczyć kilka płyt jednocześnie.
Dzienny koszt wypożyczenia płyty zależny od grupy, do której przynależy płyta.
Ćwiczenie 1.3.1.
Zgodnie z ćwiczeniem 1.1.1 utwórz bazę pod nazwą wypożyczalnia.
Przy powyższych założeniach będą potrzebne w bazie następujące tablice:
Tablice:
klienci - identyfikator klienta, nazwisko, imię, dokładny adres
grupy - identyfikator grupy, opis, dzienny koszt wypozyczenia
płyty - identyfikator płyty, tytuł, grupa tematyczna, reżyser
wypożyczenia - identyfikator klienta, identyfikator płyty, data wypożyczenia, data zwrotu, koszt wypożyczenia.
Analogicznie do ćwiczeń z rozdziału 1.2, w przypadku bazy wypożyczeń należy utworzyć odpowiednie tablice.
Ćwiczenie 1.3.2.
Utwórz tabelę klienci.
W oknie kwerendy definiującej dane utwórz tablicę:
create table klienci (id_klienta char(4) constraint
id_klienta_pk primary key,
nazwisko char(15),
imie char(15),
ulica char(15),
kod char(6),
miasto char(10);
Ćwiczenie 1.3.3.
Utwórz tablicę grupy.
W oknie kwerendy definiującej dane utwórz tablicę:
create table grupy (grupa char(5) constraint grupa_pk
primary key
opis char(20),
koszt money);
Ćwiczenie 1.3.4.
Utwórz tablicę plyty.
W oknie kwerendy definiującej dane utwórz tablicę:
create table plyty (id_plyty char(5) constraint id_plyty_pk
primary key,
tytul char(50),
grupa char(5), constraint grupa_fk references
grupy(grupa));
Ćwiczenie 1.3.5.
Utwórz tabelę wypozyczenia.
W oknie kwerendy definiującej dane utwórz tablicę:
create table wypozyczenia (id_klienta char(4) constraint
id_klienta_fk references klienci(id_klienta),
id_plyty char(5) constraint id_plyty_fk references
plyty(id_plyty),
data_wyp date,
data_zwr date);
Aby sprawdzić, czy tablice zostały dobrze utworzone oraz czy powiązania między tablicami są prawidłowe, należy wyświetlić relacje zachodzące między nimi.
Ćwiczenie 1.3.6.
Przedstaw relacje, zachodzące w bazie Wypożyczalnia płyt DVD.
W menu Narzędzia wybierz Relacje.
Przy przeglądaniu relacji może się zdarzyć, że nie wszystkie tablice zostały wyświetlone. W takiej sytuacji należy przywołać podręczne menu przez naciśnięcie prawego klawisza myszy.
Po wybraniu opcji Pokaż tabelę z okna o tym samym tytule należy wybrać tablice, które są potrzebne do wyświetlania relacji.
Można również skorzystać z opcji pokaż wszystko. Zostaną wtedy wyświetlone wszystkie tablice bez odpowiadania na żadne dodatkowe pytania.
Kolejnym zadaniem jest wprowadzenie danych do istniejących tablic oraz wyświetlenie ich bez podawania żadnych warunków.
Ćwiczenie 1.3.7.
Wprowadź dane do tablicy klienci.
W oknie kwerendy definiującej dane wprowadź 6 nowych rekordów, korzystając z polecenia:
insert into klienci
values (...);
np.
insert into klienci values (`0001', `Kowalski', `Jan',
`Kowalska 12', `55-555', `Opole');
Ćwiczenie 1.3.8.
Wyświetl zawartość tablicy klienci.
Ćwiczenie 1.3.9.
Wprowadź dane do tablicy grupy.
W oknie kwerendy definiującej dane wprowadź 5 nowych rekordów, korzystając z polecenia:
insert into grupy
values (...);
np.
insert into grupy values (`przyg', `przygodowy', `8');
Ćwiczenie 1.3.10.
Wyświetl zawartość tablicy grupy.
Ćwiczenie 1.3.11.
Wprowadź dane do tablicy plyty.
W oknie kwerendy definiującej dane wprowadź 10 nowych rekordów, korzystając z polecenia:
insert into plyty
values (...);
np.
insert into plyty values (`0001', `Piąty element', `sc-fi');
Ćwiczenie 1.3.12.
Wyświetl zawartość tablicy plyty.
Ćwiczenie 1.3.13.
Wprowadź dane do tablicy wypozyczenia.
W oknie kwerendy definiująceh dane wprowadź 7 nowych rekordów, korzystając z polecenia:
insert into wypozyczenia
values (...);
np.
insert into wypozyczenia values (`0001', `0001', `02-01-1999', 03-01-1999');
Ćwiczenie 1.3.14.
Wyświetl zawartość tablicy wypozyczenia.
Celem tych ćwiczeń jest jedynie wyjaśnienie podstawowych pojęć i zaznajomienie z pewnymi regułami. Jednak życie często komplikuje sytuacje i proste polecenia select nie są poleceniami wystarczającymi.
Ćwiczenie 1.3.15.
Przedstaw listę wypożyczeń dokonanych przez klienta (identyfikator klienta, jego nazwisko i wypożyczone (identyfikator i tytuł), data wypożyczenia i data zwrotu).
select wypozyczenia.id_klienta, klienci.nazwisko,
klienci.imie, wypozyczenia.id_plyty, plyty.tytul,
wypozyczenia.data_wyp, wypozyczenia.data_zwr
from wypozyczenia, klienci, plyty
where wypozyczenia.id_klienta = klienci.id_klienta
and wypozyzcenia.id_plyty = plyty.id_plyty;
Ćwiczenie 1.3.16.
Kiedy i przez kogo była wypożyczona płyta z identyfikatorem 0001 oraz ile dni była przetrzymywana przez klienta. Oprócz identyfikatora klienta podaj również jego nazwisko. Kolumnę, która powstanie dla liczby dni nazwij liczba_dni.
select wypozyczenia.id_klienta, klienci.nazwisko,
wypozyczenia.data_wyp, wypozyczenia.data_zwr,
(wypozyczenia.data_zwr - wypozyczenia.data_wyp) as liczba_dni
from wypozyczenia, klienci
where wypozyczenia.id_klienta = klienci.id_klienta
and wypozyczenia.id_plyty = `0001';
Ćwiczenie 1.3.17.
Jaki dochód każdorazowo przyniosły wypożyczenia każdej z płyt. Kolumnę przedstawiającą ilość dni nazwij liczba_dni, a kolumnę z dochodem nazwij dochod.
select wypozyczenia.id_plyty, wypozyczenia.data_wyp,
wypozyczenia.data_zwr, (wypozyczenia.data_zwr - wypozyczenia.data_wyp) as liczba_dni, plyty.grupa,
(liczba_dni * grupy.koszt) as dochod
from plyty, grupy, wypozyczenia
where wypozyczenia.id_plyty = plyty.id_plyty
and plyty.grupa = grupy.grupa;
Ćwiczenie 1.3.18.
Podaj całkowity dochód, jaki przyniosła każda z wypożyczonych płyt. Kolumnę przedstawiającą całkowity dochód nazwij dochod_calkowity.
select wypozyczenia.id_plyty, sum((wypozyczenia.data_zwr - wypozyczenia.data_wyp)*grupy.koszt) as dochod_calkowity
from wypozyczenia, plyty, grupy
where wypozyczenia.id_plyty = plyty.id_plyty
and plyty.grupa = grupa.grupa
group by wypozyczenia.id_plyty;
Ćwiczenie 1.3.19.
Ile razy była wypożyczana każda z płyt. Kolumnę z ilością wypożyczeń nazwij ilosc_wypozyczen.
select id_plyty, count(id_plyty) as ilosc_wypozyczen
from wypozyczenia
group by id_plyty;
6
Ćwiczenia z SQL w MS Access'97