Data ostatniego wydruku 2011-04-15 11:16
1
Bazy danych
Ćwiczenia 2
Temat: definiowanie tabel, maski wprowadzania, tworzenie relacji
1.
Utworzyć pustą bazę danych o nazwie BiuroNieruchomosci
2.
Utworzyć pustą tabelę o nazwie Biuro, atrybuty:
a.
biuroNR
b.
ulica
c.
miasto
d.
kodPocztowy
3.
Zaimportować dane z pliku Biuro.txt (jak?) oraz Nieruchomość.xls (jak?)
4.
Zaimportować pozostałe tabele
a.
Dla tabeli Personel, na etapie importu zmienić typ danych dla atrybutu
-
dataUr z formatu tekst na data
-
pensja na liczba całkowita
-
usunąć Pole 9
b.
Dla tabeli Wizyta, na etapie importu zmienić typ danych dla atrybutu
dataWizyty z formatu tekst na data.
c.
Dla tabeli Rejestracja, na etapie importu zmienić typ danych dla atrybutu
dataRejestracji z formatu tekst na data.
5.
Ustawić maski wprowadzania dla atrybutów:
a.
kodPocztowy (przykład: 24-200) w tbl.Biuro, tbl.Nieruchomość,
b.
dataUr (14.05.2000) w tbl.Personel
c.
dataWizyty (14.05.2000) w tbl.Wizyta
d.
dataRejestracji (14.05.2000) w tbl.Rejestracja
e.
telNr (500-500-0000) w tbl.Klient, tbl. WłascicielPrywatny
f.
nazwisko (NOWAK) w tbl.Personel, tbl. Klient, tbl.WłaścicielPrywatny
g.
biuroNr (B111) w tbl.Biuro
h.
klientNr (CR123) w tbl. Klient,
i.
włascicielNr(CO123) w tbl.WłaścicielPrywatny
6.
Utwórz relacje między tabelami (jak?)
7.
Uzupełnić kaŜdą z tabel dowolnymi danymi.
Bazy danych
Ćwiczenia 3
Temat: Budowanie zapytań SQL(Structured Query Language).
W oparciu o bazę danych utworzoną w ćwiczenie 2 utwórz kwerendy wg poniŜszych poleceń.
PROSTE ZAPYTANIA
Przykład 1.
Wyszukiwanie wszystkich kolumn i wierszy.
Podaj wszystkie dane wszystkich pracowników .
Zamknąć okno kwerendy, zapisać jako Kwerenda1
Przykład 2.
Wyszukanie wybranych kolumn i wszystkich wierszy
Podaj listę płac wszystkich pracowników z tabeli Personel; lista płac powinna zawierać
jedynie pola: numer pracownika, imię, nazwisko oraz pensję
Zamknąć okno kwerendy, zapisać jako Kwerenda2.
Data ostatniego wydruku 2011-04-15 11:16
2
Przykład 3.
Wykorzystanie DISTINCT
Podaj numery wszystkich nieruchomości, które zostały odwiedzone przez klientów.
Zamknąć okno kwerendy, zapisać jako Kwerenda3
Przykład 4.
Pola wyliczane
Podaj listę miesięcznych płac wszystkich pracowników z tabeli Personel; lista powinna
zawierać numer pracownika, jego imię, nazwisko oraz pole o nazwie: PensjaMiesięczna
zawierające wysokości pensji miesięcznych (w tabeli personel w kolumnie pensja zapisane
są roczne pobory)
Zamknąć okno kwerendy, zapisać jako Kwerenda4
Przykład 5.
Warunek selekcji: porównanie
Podaj wszystkich pracowników, których pensja jest wyŜsza niŜ 10 000; lista powinna
zawierać numer pracownika, jego imię nazwisko, pensję oraz stanowisko
Zamknąć okno kwerendy, zapisać jako Kwerenda5.
W SQL dostępne są następujące proste operatory porównania:
= równe
<> róŜne
< mniejsze niŜ
> większe niŜ
<= mniejsze niŜ lub równe
>= większe niŜ lub równe
Bardziej skomplikowane warunki moŜna budować uŜywając spójników logicznych AND (i),
OR (lub), oraz NOT (nie) a takŜe nawiasów w celu wskazanie kolejności działań.
Zasady obliczania wartości wyraŜenia logicznego są następujące:
••••
wyraŜenia oblicza się od lewej do prawej
••••
w pierwszej kolejności obliczane są podwyraŜenia w nawiasach
••••
NOT jest obliczane prze AND i OR
••••
AND jest obliczane przed OR
Przykład 6.
Zło
Ŝony warunek selekcji: porównanie
Podaj adresy wszystkich biur znajdujących się w Krakowie lub Lublinie.
Zamknąć okno kwerendy, zapisać jako Kwerenda6.
Przykład 7.
Warunek selekcji: warto
ści z zakresu (BETWEEN i NOT
BETEEEN)
Podaj wszystkich pracowników mających roczną pensję pomiędzy 20 000 a 30 000 PLN;
lista powinna zawierać pola: Pracownik (Imię, Nazwisko), Pensja
Zamknąć okno kwerendy, zapisać jako Kwerenda7.
Przykład 8.
Warunek selekcji: przynale
Ŝność do zbioru (IN lub NOT IN)
Podaj wszystkich kierowników i dyrektorów.
Zamknąć okno kwerendy, zapisać jako Kwerenda8.
W tym zapytaniu naleŜy szukać słów ‘Dyrektor’ i ‘kierownik’ pojawiających się w
kolumnie Stanowisko.
Data ostatniego wydruku 2011-04-15 11:16
3
Przykład 9.
Warunek selekcji: dopasowanie do wzorca (LIKE lub NOT LIKE)
Znajdź wszystkich właścicieli w których adresie występuje słowo Kraków; lista powinna
zawierać pola: imię nazwisko,
W SQL występują dwa szczególne symbole zastępcze
ANSI
–
89
(Access 03)
Opis
ANSI - 92
*
zastępuje ciąg znaków dowolnej długości (takŜe zero)
%
?
zastępuję dowolny (jeden) znak
_
Wszystkie pozostałe znaki we wzorcu reprezentują same siebie, np.:
•
Adres LIKE „H*” oznacza, ze pierwszym znakiem musi być H, ale pozostałe znaki
mogą być dowolne
•
Adres LIKE „H????” oznacza, Ŝe w adresie musza być dokładnie cztery znaki, z
których pierwszy to H
•
Adres LIKE „*e” oznacza dowolny ciąg znaków o długości co najmniej jeden, w
którym ostatni znak jest równy e
•
Adres LIKE „*ds.*” oznacza dowolny ciąg znaków dowolnej długości
zawierający skrót ds.
•
Adres NOT LIKE „H*” oznacza, ze pierwszym znakiem nie moŜe być H
Zamknąć okno kwerendy, zapisać jako Kwerenda9.
Przykład 10.
Warunek selekcji: warto
ści puste (IS NULL lub IS NOT NULL)
Podaj szczególowe informacje o wszystkich wizytach w nieruchomości PG4, po których
nie zgłoszono uwag.
Zamknąć okno kwerendy, zapisać jako Kwerenda10.
PORZĄDKOWANIE WYNIKU – klauzula ORDER BY
Przykład 11.
Porz
ądkowanie według jednej kolumny
Wygeneruj listę pensji wszystkich pracowników uporządkowaną malejąco według pensji;
lista niech zawiera pola: Imię, Nazwisko, Stanowisko, Pensja
Zamknąć okno kwerendy, zapisać jako Kwerenda11.
Przykład 12.
Porz
ądkowanie według wielu kolumn
Wygeneruj listę wybranych informacji dotyczących nieruchomości uporządkowana wg
rodzajów nieruchomości (rosnąco)i czynszu malejąco.
Zamknąć okno kwerendy, zapisać jako Kwerenda12.
ZASTOSOWANIE FUNKCJI AGREGUJĄCYCH
WyróŜnia się pięć funkcji agregujących:
COUNT – zwraca liczbę wartości występujących w określonej kolumnie
SUM – zwraca sumę wartości występujących w określonej kolumnie
AVG – zwraca średnią wartości występujących w określonej kolumnie
MIN – zwraca najmniejszą wartości występujących w określonej kolumnie
MAX – zwraca największą wartość występujących w określonej kolumnie
Data ostatniego wydruku 2011-04-15 11:16
4
Wymienione funkcje są obliczane na podstawie wartości jednej kolumny tabeli i zwracają
w wyniku jedną wartość.
!!Funkcje …………………………………. moŜna stosować zarówno do pól liczbowych,
jak i nieliczbowych.
!!Funkcje …………………………………. moŜna stosować jedynie do pól liczbowych.
Przykład 13.
Zastosowanie COUNT(*)
W ilu nieruchomościach miesięczny czynsz jest wyŜszy niŜ 350PLNł?
Zamknąć okno kwerendy, zapisać jako Kwerenda13
Przykład 14.
Zastosowanie COUNT (DISTINCT)
Ile nieruchomości odwiedzono w maju 2001 roku?
Zamknąć okno kwerendy, zapisać jako Kwerenda14
Przykład 15.
Zastosowanie COUNT i SUM
Oblicz ilu jest dyrekorów i jaka jest ich sumaryczna pensja.
Zamknąć okno kwerendy, zapisać jako Kwerenda15
Przykład 16.
Zastosowanie MIN, MAX i AVG
Oblicz najmniejszą, największą i średnią pensję pracownika. Wynik zapisz do kolumn
odpowiednio Min, Max i Avg
Zamknąć okno kwerendy, zapisać jako Kwerenda16