1
Ćwiczenia z SQL w MS Access 97
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 przejdz
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.
2
Ćwiczenia z SQL w MS Access 97
1.2. 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 znalez
ć potrzebny plik.
¨ Przejdz
do okna kwerendy definiującej dane (patrz ćwiczenie 1.1.2).
¨ W oknie kwerendy wprowadz
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;
3
Ćwiczenia z SQL w MS Access 97
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.
¨ Przejdz
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.
¨ Przejdz
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
¨ Przejdz
do okna kwerendy definiującej dane (patrz ćwiczenie 1.1.2).
¨ W oknie kwerendy wprowadz
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
¨ Przejdz
do okna kwerendy definiującej dane (patrz ćwiczenie 1.1.2).
¨ W oknie kwerendy wprowadz
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
4
Ćwiczenia z SQL w MS Access 97
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.
Wprowadz
dane do tablicy nauczyciele.
¨ Otwórz bazÄ™ szkoÅ‚a.
¨ W oknie kwerendy definiujÄ…cej dane wprowadz
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.
Wprowadz
dane do tablicy klasy.
¨ Otwórz bazÄ™ szkoÅ‚a.
¨ W oknie kwerendy definiujÄ…cej dane wprowadz
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.
Wprowadz
dane do tablicy nauka.
¨ Otwórz bazÄ™ szkoÅ‚a.
¨ W oknie kwerendy definiujÄ…cej dane wprowadz
9 nowych rekordów, korzystając z polecenia:
5
Ćwiczenia z SQL w MS Access 97
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 ;
6
Ćwiczenia z SQL w MS Access 97
1.3. 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));
7
Ćwiczenia z SQL w MS Access 97
Ć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.
Wprowadz
dane do tablicy klienci.
¨ W oknie kwerendy definiujÄ…cej dane wprowadz
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.
Wprowadz
dane do tablicy grupy.
¨ W oknie kwerendy definiujÄ…cej dane wprowadz
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.
Wprowadz
dane do tablicy plyty.
¨ W oknie kwerendy definiujÄ…cej dane wprowadz
10 nowych rekordów, korzystając z polecenia:
8
Ćwiczenia z SQL w MS Access 97
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.
Wprowadz
dane do tablicy wypozyczenia.
¨ W oknie kwerendy definiujÄ…ceh dane wprowadz
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,
9
Ćwiczenia z SQL w MS Access 97
(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;