Baza danych Uczniowie.mdb

Zadania:

1. Tabele:

Założyć bazę danych uczniowie.mdb o strukturze danych:
Uczniowie-dane - zip
Uczniowie1_dane - zip
uczzsbd1.mdb

1) UCZNIOWIE (NRU, nazwisko, imie)
a) Wpisać  polecenie kwerendy przy pomocy języka SQL
Zapytanie, Nowe zapytanie,
Widok SQL,
Wpisać lub skopiować poniższe polecenie:
create table Uczniowie ( NRU integer,  nazwisko text(20) , imie text(20), data_ur date,   constraint klucz primary key (NRU) );

Zapisac kwerendę pod nazwą Create Uczniowie
Wykonac komendę - kwerenda tworząca tabelę -
!

Uzupełnić warunki  na pola w tabeli projektu UCZNIOWIE:
Klucz – NRU

Nr ucz

Pole nazwisko
Nazwisko

Pole Imie
Imię


Pole data_ur
SELECT DISTINCTROW Uczniowie.NRU, Uczniowie.nazwisko, Przedmioty.nazwap, Oceny.Ocena
FROM Uczniowie INNER JOIN (Przedmioty INNER JOIN Oceny ON Przedmioty.NRP = Oceny.NRP) ON Uczniowie.NRU = Oceny.NRU
ORDER BY Uczniowie.NRU;
Data urodz
W polu Opis można wprowadzić odpowiedni komentarz, np. NRU --> Nr dzienniika

b) wykorzystać istniejącą tabelę wykonaną w programie dBase  o schemacie:  UCZNIOWIE (NRU, imie, nazwisko, inne_dane),
- zaimportować:
Plik, pobierz dane zewnętrzne, importuj - wskazać źródło i nazwę pliku danych
i zmodyfikować do wersji podanej wyżej

Wprowadzić nowe tabele Przedmioty i Oceny:
Tabela, Nowy, Nowa tabela, wprowadzić pola z ograniczeniami jak wyżej:
NRP, NRU, NRK - integer jak wyżej
nazwap - text(20)
ocena - liczba , pojedyncza precyzja, miejsca dzies: 1

2) PRZEDMIOTY (NRP, nazwap)
Klucz - NRP - numer przedmiotu
Przedmioty (NRP integer, nazwap text (40)

3) OCENY (NRK, NRU,NRP, ocena)
Oceny (NRK integer, NRU integer, NRP integer, ocena single, constraint klucz primary key (NRK))
Pola : NRK, NRU, NRP - integer j.w.
ocena - liczba pojedyncza precyzja, miejsca dzies - 1
Klucz - NRK - nr kolejny.
Założyć formularze na podstawie tabel i wprowadzić dane ze swojej klasy lub uzupełnic jeśli zaimportowano część danych.

Tabele te można utworzyć tez dokładając do schematu tabeli słowo CREATE i uruchomić SQL

4)  Utorzyć tabelę konta (IdK, PESEL, Nazwisko, Stan)
W polu PESEL uwzględnić właściwości pola jak na rys. poniżej
Właściwości
W polach Nazwisko w we właściwościach pola na pozycji Format wprowadzić znak > (zamiana na duże litery)

W polu stan wprowadzić we właściwościach pola Wygenerować i uruchomić kwerendę przy kryteriach:
Stan

Wprowadzić 2 pozycje do tabeli  KONTA

2. Kwerendy (zapytania)

2.1. KWERENDY WYBIERAJACE

Służą do uzyskiwania informacji z bazy danych wg określonych kryteriów
Najprostsze zapytanie oparte na jednej tabeli UCZNIOWIE

1 metoda - przy wykorzystaniu okna projektu kwerendy - QBE - Query By Example - sposób wizualny
Kwerenda z tabeli Uczniowie

II metoda - Widok zapytania w języku SQL
Structured Query Language
SELECT Uczniowie.* FROM Uczniowie;
lub    SELECT * FROM Uczniowie;

1a) Wyprowadzić listę uczniów wraz z przedmiotami i ocenami w układzie:
nr ucznia, nazwisko, przedmiot, ocena (kwerenda wybierająca)

1 metoda - przy wykorzystaniu okna projektu kwerendy - QBE - sposób wizualny

Kwerendy, Nowy, Widok projektu, Tabele - dodaj, wskazujemy tabele: Uczniowie, Przedmioty, Oceny,
Przeciągamy pola: NRU, nazwisko  z tabeli Uczniowie (lub klikamy 2 razy - wskoczy) nazwap z tabeli Przedmioty
i ocena z  tabeli Oceny
Ewentualnie w kolumnie NRU lub  nazwisko zaznaczamy Sortuj rosnąco.
Kwerenda wybierająca

II metoda - Widok zapytania w języku SQL

SELECT DISTINCTROW Uczniowie.NRU, Uczniowie.nazwisko, Przedmioty.nazwap, Oceny.Ocena
FROM Uczniowie INNER JOIN (Przedmioty INNER JOIN Oceny ON Przedmioty.NRP = Oceny.NRP) ON Uczniowie.NRU = Oceny.NRU
ORDER BY Uczniowie.NRU;


1b) Zapytania - kryteria
1b1)  Wygenerować i uruchomić kwerendę przy kryteriach:

Nazwisko na litere i
- Like "*i"              - gwiazdka oznacza wszystkie znaki, na końcu i  lub imię na literę a - Like "*a"

Zapis warunków w 2 liniach oznacza kruterium OR – LUB a w jednej linii - kryterium AND czyli I
Kryteria

W SQL

SELECT DISTINCTROW Oceny.NRK, Oceny.NRU, Uczniowie.nazwisko, Uczniowie.imie, Oceny.NRP, Przedmioty.nazwap, Oceny.Ocena
FROM Uczniowie INNER JOIN (Przedmioty INNER JOIN Oceny ON Przedmioty.NRP = Oceny.NRP) ON Uczniowie.NRU = Oceny.NRU
WHERE ((Uczniowie.nazwisko Like "*i")) OR ((Uczniowie.imie Like "*a"))
ORDER BY Oceny.NRU;


1b2)   Wygenerować i uruchomić kwerendę przy kryteriach:
 NRU <10 i Ocena < 2 lub Ocena >4

Kryteria

W języku SQL

SELECT DISTINCTROW Oceny.NRK, Oceny.NRU, Uczniowie.nazwisko, Uczniowie.imie, Oceny.NRP, Przedmioty.nazwap, Oceny.Ocena
FROM Uczniowie INNER JOIN (Przedmioty INNER JOIN Oceny ON Przedmioty.NRP = Oceny.NRP) ON Uczniowie.NRU = Oceny.NRU
WHERE ((Oceny.NRU<10) AND (Oceny.Ocena<2 Or Oceny.Ocena>4));

2) Wyprowadzić średnie ocen każdego ucznia:

a) Utworzenie kwerendy w oknie projektu kwerendy i formuły QBE - Query By Example
Oceny średnie

b)  Kwerenda powyższa widoczna w języku SQL - Structured Query Language
SELECT
DISTINCTROW Oceny.NRU, Uczniowie.nazwisko, Avg(Oceny.Ocena) AS [Średnia:Ocena]
FROM Uczniowie INNER JOIN (Przedmioty INNER JOIN Oceny ON Przedmioty.NRP = Oceny.NRP) ON Uczniowie.NRU = Oceny.NRU
GROUP BY Oceny.NRU, Uczniowie.nazwisko;


3) Wyświetlić średnią ocen wybranego ucznia (kwerenda wybierająca z parametrem)
a) Zapytanie utworzyć w widoku projektu, następnie przejść na widok SQL
Średnia ocen ucznia

b)  Kwerenda powyższa widoczna w języku SQL - Structured Query Language
SELECT DISTINCTROW Oceny.NRU, Uczniowie.nazwisko, Avg(Oceny.Ocena) AS [Średnia:Ocena]
FROM Uczniowie INNER JOIN (Przedmioty INNER JOIN Oceny ON Przedmioty.NRP = Oceny.NRP) ON Uczniowie.NRU = Oceny.NRU
GROUP BY Oceny.NRU, Uczniowie.nazwisko
HAVING ((Oceny.NRU=[Podaj NRU]));

Możemy wykonać np. jak pokazano poniżej na rysunku

4)  Wyświetlić średnią każdego ucznia z każdego przedmiotu

Poniżej przykład realizacji zadania
Średnie ocen każdego ucznia z każdego przedmiotu

W języku SQL:
SELECT DISTINCTROW Uczniowie.NRU, Uczniowie.nazwisko, Przedmioty.nazwap, Avg(Oceny.Ocena) AS [Średnia:Ocena]
FROM Uczniowie INNER JOIN (Przedmioty INNER JOIN Oceny ON Przedmioty.NRP = Oceny.NRP) ON Uczniowie.NRU = Oceny.NRU
GROUP BY Uczniowie.NRU, Uczniowie.nazwisko, Przedmioty.nazwap;



KWERENDY PODSUMOWUJĄCE OPARTE NA FUNKCJACH AGREGUJĄCYCH
Należą do nich zapytania krzyżowe
Zapytanie krzyżowe

4a) Sporządzić kwerendę krzyżową na podstawie kwerendy Z-Uczniowie
Zapytanie nowy, kreatory zapytań - zapytanie krzyżowe

Zapytania krzyzowe

TRANSFORM Avg([Z-Oceny].Ocena) AS Wartość
SELECT [Z-Oceny].nazwisko, [Z-Oceny].nazwap, Avg([Z-Oceny].Ocena) AS [Średnia ocena]
FROM [Z-Oceny]
GROUP BY [Z-Oceny].nazwisko, [Z-Oceny].nazwap
PIVOT [Z-Oceny].Ocena;

Przykładowy wynik zapytania
Wyniki zapytania krzyzowego


4b) Zapytania z polami obliczeniowymi
Na podstawie tabeli oceny sporządzić kwerendę z dodatkowymi polami obliczeniowymi:
Ocena_raz_poltora:=Ocena*1,5
Ocena_zaok:=Int(Ocena)
Data: Date()
Czas: Time()

Pola obliczeniowe

KWERENDY FUNKCJONALNE

Kwerenda aktualizująca
5) Zamienić oceny >5,4 na 6 (kwerenda aktualizująca)
Zapytanie aktualizujące

W SQL:
UPDATE DISTINCTROW Oceny SET Oceny.Ocena = 6
WHERE ((Oceny.Ocena>5.4));

Kwerenda tworząca tabelę
Structured Query Language
6) Utworzyć tabelę archiwalną Uczn_ocen -  zawierającą informacje wymienione w zadaniu 1) (kwerenda tworząca tabelę)
Można utworzyć najpierw kwerendę wybierającą np. Uczniowie i oceny a później przejść z niej na kwerendę tworzącą tabelę.
Kwerendy, Nowy, Widok projektu, Pokazywanie tabeli - Kwerendy, Dodaj, Wskazujemy tabelę z kwerendą, np. Uczniowie i oceny,
przeciągamy gwiazdkę, Typ kwerendy - tworząca tabelę, uruchamiamy
Kwerenda tworząca tabelę

SELECT DISTINCTROW Uczniowie.*, Przedmioty.nazwap, Oceny.Ocena INTO Uczn_ocen
FROM Uczniowie INNER JOIN (Przedmioty INNER JOIN Oceny ON Przedmioty.NRP = Oceny.NRP) ON Uczniowie.NRU = Oceny.NRU;

Zapytanie usuwające

Najlepiej utworzyć zapytanie wybierające, sprawdzić jego działanie i zamienić na zapytanie usuwajace
7) Usunąć uczniów na literę A

Kwerenda usuwająca

DELETE DISTINCTROW Uczniowie.*, Uczniowie.nazwisko
FROM Uczniowie
WHERE ((Uczniowie.nazwisko Like "A*"));


Zapytanie dołaczające

8) Dołączyć tabelę Uczn1 do tabeli Uczniowie
Utworzyć kopię tabeli Uczniowie do tabeli Uczn1
Wykasować rekordy istniejące i wprowadzić nowe, o numerach 101, 102, napisy małymi literami.
Utworzyć zapytanie dołączające
- wskazać tabelę dołączaną, wybrać zapytanie dołączające, wskazać tabelę do której będziemy dołączać - tu Uczniowie
Dołączane pole można wskazać gwiazdką - wszystkie
Zap. dołączające

3. Formularze

1) Utworzyć formularz prezentujący dane z kwerendy w której są pola:   nr ucznia, nazwisko, przedmiot, ocena 
(Wyprowadzić listę uczniów wraz z przedmiotami i ocenami )
Formularz, Nowy,
Wskazać źródło formularza, np. zapytanie: Z-oceny
Kreatory formularzy: jednokolumnowy lub  tabelaryczny - wybieramy tabelaryczny
2) Utworzyć formularz do przeglądania, usuwania i wprowadzania danych do tabeli Uczniowie
Analogicznie j.w. Jako źródło danych wybieramy tabelę uczniowie, formularz jednokolumnowy

4) Raporty

Przygotować raport prezentujący listę uczniów
Raporty, Nowy,  wybrać źródło (tabelę lub kwerendę),  np. tabela Uczniowie
Autoraport: kolumnowy lub tabelaryczny --> utworzy się raport
albo Kreator raportów,  wybrać pola (> - jedno pole, >> - wszystkie pola), dalej, dodać ewentualne poziomy grupowania, ... , zakończ