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
1
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
2
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 Opis
ANSI - 92
(Access 03)
*
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
3
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
Data ostatniego wydruku 2011-04-15 11:16
4