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 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.

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 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;

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.

¨ 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

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.
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:

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.
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:

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.
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,

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;