Ćwiczenia z SQL w Access'97.pdf

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

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

Ć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

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

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

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika: