Program: bazy danych – SQL

Ćwiczenia MS Access/SQL
Pojęcia podstawowe:
1. Obiekty relacyjnych baz danych:
 Tabela
 Kolumna (atrybut)
 Rekord (wiersz)
 Pole
 Klucze (podstawowe, obce)
2. Typy danych
3. Normalizacja danych, powiązania między tabelami
4. Kwerendy i SQL (polecenie SELECT)
I. Zadania podstawowe
1. Wyświetlanie zawartości tabeli
a) Polecenie: Wyświetl wszystkie rekordy z tabeli Pracownicy.
b) Polecenie: Wyświetl dane wszystkich klientów.
2. Sortowanie ORDER
a) Polecenie: Wyświetl wszystkie rekordy z tabeli Pracownicy posortowane
alfabetycznie według nazwiska, a następnie według imienia.
b) Polecenie: Wyświetl wszystkie rekordy z tabeli Produkty posortowane malejąco
według ceny jednostkowej.
3. Wyświetlanie poszczególnych atrybutów danej tabeli
a) Polecenie: Wyświetl ID zamówień, daty zamówień, miasta i kraje odbiorców z
tabeli Zamówienia.
b) Polecenie: Wyświetl imiona, nazwiska i miasta zamieszkania poszczególnych
pracowników, posortowane alfabetycznie według nazwiska, a następnie imienia i
nazwy miasta.
4. Wyświetlanie części rekordów tabeli
a) Polecenie: Wypisz pierwsze 4 kategorie produktów i ich opis.
b) Polecenie: Wyświetl wszystkie informacje na temat 10 najdroższych produktów
z tabeli Produkty.
c) Polecenie: Wypisz pierwsze 25% wszystkich zamówień, wyświetl numer
zamówienia, opis i ilość dla zamówienia.
II. Wybór wierszy
1. Wybór wierszy: klauzula WHERE (łączenie warunków, kolejność wykonywanych
poleceń: AND, OR, nawiasy, Operatory porównań.
a) Polecenie: Wyświetl wszystkie produkty, których stan magazynowy jest
mniejszy niż 30.
b) Polecenie: Wyświetl nazwy, adresy i miasta dostawców ze Szwecji,
posortowane według nazw firm.
1
c) Polecenie: Wyświetl wszystkie zamówienia, które zostały złożone po 1997 roku
i wysłane do Londynu.
d) Polecenie: Wyświetl wszystkie zamówienia na których cena jednostkowa była
większa od 200 lub ilość zamawianego towaru przekraczała 100.
2. Polecenie BETWEEN AND
a) Polecenie: Wyświetl wszystkich pracowników (imie, nazwisko, stanowisko)
przyjętych do pracy w 1 kwartale 1994 roku.
b) Polecenie: Wyświetl wszystkie zamówienia, dla których fracht mieści się w
granicach 80-120 zł.
3. Polecenie IN
Polecenie: Wyświetl ID dostawcy, firmę, Przedstawiciela i jego stanowisko
pochodzących z Niemiec, Szwecji lub Holandii.
4. Polecenie LIKE
a) Polecenie: Wybierz kategorie, do których zostały zaklasyfikowane owoce.
b) Polecenie: Wyświetl informacje na temat wszystkich pracowników, których
imię zaczyna się na literę „A”.
Zgodność
Brak zgodności
Rodzaj zgodności Wzorzec (zwraca True) (zwraca False)
Wiele znaków
a*a
aa, aBa, aBBBa aBC
*ab*
abc, AABB, Xab aZb, bac
Znak specjalny
a[*]a
a*a
aaa
Wiele znaków
ab*
abcdefg, abc
cab, aab
Jeden znak
a?a
aaa, a3a, aBa
aBBBa
Jedna cyfra
a#a
a0a, a1a, a2a
aaa, a10a
Zakres znaków
[a-z]
f, p, j
2, &
Poza zakresem
[!a-z]
9, &, %
b, a
Nie cyfra
[!0-9]
A, a, &, ~
0, 1, 9
Połączenie
a[!b-m]# An9, az0, a99
abc, aj0
5. Wybór nieznanych wartości IS (NOT) NULL
a) Polecenie: wypisz informacje o pracownikach, dla których jest wypełnione pole
region.
b) Polecenie: wyświetl nazwy tych dostawców, którzy nie posiadają strony
internetowej.
6. Wybór nie powtarzających się informacji DISTINCT
a) Polecenie: Wypisz wszystkie miasta, z których pochodzą klienci.
b) Wypisz wszystkie miasta, w których dokonano zamówień w sierpniu 1996.
2
III. Zastosowanie funkcji i innych operacji na atrybutach; aliasy
1. Funkcje ciągów znaków: sumowanie tekstów, LEFT, RIHGT, SUBSTRING, REPLACE
Funkcje tekstowe
ASCII
LENGTH
RTRIM
CHAR
LOCATE
SPACE
CONCAT LTRIM SUBSTRING
LCASE
RIGHT
UCASE
LEFT
a) Polecenie: Zamień w imionach literę „N” na znak „_”.
b) Polecenie: Wypisz nazwy miast, oraz pierwsze 3 znaki i ostatnie 45 znaków.
c) Polecenie: Wypisz imiona i nazwiska pracowników, zamieniając nazwiska na
litery drukowane.
2. Operatory arytmetyczne: + - / * %
a) Polecenie: Powiększ cenę jednostkową wszystkich produktów o 10% i wypisz
jako nową kolumnę.
b) Wyznacz wartości poszczególnych produktów w magazynie (wyświetl wraz z
nazwami produktów).
3. Funkcje arytmetyczne, np. ABS lub ROUND
Funkcje numeryczne
ABS
ATAN
FLOOR SIN
LOG
SQRT
CEILING POWER TAN
COS
RAND MOD
EXP
SIGN
Polecenie: Zaokrąglij do pełnych złotych cenę produktów i dopisz walutę.
4. Funkcje daty: DAY, MONTH, DAYOFYEAR, YEAR, DATEADD
Funkcje daty i czasu
CURDATE
DAYOFYEAR MONTH
CURTIME
YEAR
WEEK
NOW
HOUR
QUARTER
DAYOFMONTH MINUTE
MONTHNAME
DAYOFWEEK
DAYNAME
SECOND
a) Polecenie: wypisz dzisiejszą datę, dzień, miesiąc i rok w osobnych kolumnach.
3
b) Wypisz pracowników (imię i nazwisko) oraz datę wygaśniecia 10-letniej
umowy o pracę tych pracowników, którzy mają mniej niż 50 lat a staż pracy
większy niż 13 lat, oraz w momencie zatrudnienia byli przed 35 rokiem życia.
5. Literały – ciąg znaków
SELECT kolumna1, „literał” , kolumna2 FROM tabela
Sumowanie pól znakowych
a) Polecenie: Wyświetl w jednej kolumnie o nazwie dane: nazwisko, imię i
stanowisko pracownika
b) Polecenie: Wyświetl w jednej kolumnie o nazwie dane: nazwisko, imię, w
następnej literał: pracuje od, data zatrudnienia pracownika
IV. Zastosowanie funkcji agregujących
1. Funkcje agregujące; klauzula GROUP BY i HAVING
a) Wyświetl sumę kosztów frachtu ze wszystkich zamówień.
b) Wyświetl sumę wartości produktów w magazynie.
c) Polecenie: Zlicz ilość rekordów znajdujących się w tablicy Produkty.
d) Polecenie: Wyświetl wartość sprzedaży poszczególnych produktów, bez
rabatów (ID produktu i wartość sprzedaży).
e) Polecenie: Wyświetl średnie ilości sprzedaży według poziomów przyznanych
rabatów.
f)
Polecenie: Wypisz zamówienia, posortowane malejąco wg ceny do zapłaty
przez klienta.
g) Polecenie: j.w., przy czym wyświetlane są tylko zamówienia o kwocie
przekraczającej 5000 zł.
h) Polecenie: Wypisz największą, najmniejszą, przeciętną cenę produktu oraz
całkowity zasób magazynu.
V. Łączenie tabel
1. Złączenia wewnętrzne: INNER JOIN
a) Polecenie: wyświetl informacje o zamówieniach połączone z danymi spedytora.
b) Polecenie: wyświetl nazwy miast, posortowane alfabetycznie, wraz z nazwami
regionów (tabele: Miasto, Region).
c) Polecenie: wyświetl imiona i nazwiska pracowników oraz nazwy miast, które są
pod ich opieką (tabele: Pracownicy, Pracownicy_miasto, Miasto).
d) Polecenie: wyświetl miasta, które są pod opieką pracowników o numerach 1 i
2.
e) Polecenie: utwórz ranking pracowników według wartości dokonanej przez nich
sprzedaży (uwzględniającej rabaty).
4
2. CROSS JOIN (tylko wyjaśnienie)
3. Złączenia zewnętrzne: LEFT|RIGHT OUTER JOIN
a) Polecenie: dopisz do tabeli pracowników nowego pracownika. Następnie
wyświetl imiona i nazwiska pracowników wraz z ID miast, które są pod ich opieką,
uwzględniając także w zestawieniu osoby nie opiekujące się żadnym miastem.
b) Polecenie: Wypisz wszystkich klientów (id, nazwe firmy, oraz miasto i kraj), i
sprawdź, czy zrobili zamówienie w pierwszym półroczu 1998 roku.
4. Podzapytania
a) Polecenie: Wypisz 5 produktów o najtańszych produktów w grupie wśród
produktów o ponadprzeciętnych cenach.
b) Polecenie: wyświetl nazwiska i imiona pracowników, którzy nie sprzedali
niczego do Argentyny.
5. Operator UNION
Polecenie: Wyświetl w jednej tablicy miasto, nazwe firmy i przedstawiciela, klienta
i dostawce, przy czym wyświetl typ relacji klient czy dostawca, w osobnym polu.
VI. Zadania podsumowujące:
1. Dla każdego zamówienia dopisz nazwę produktu, i oblicz cenę końcową
2. Wypisz klientów, firmę, miasto i kraj pochodzenia, którzy dokonali zamówienia w
pierwszym półroczu 1998 i posortuj wg daty zamówienia chronologicznie
3. Pokaż wszystkie zamówienia wg produktów zaczynających się na literę C w 1997 roku
4. Wyświetl produkty (nie wycofane), ich cenę jednostkową, oraz wielkość zapasów z
kategorii na literę N.
VII. Informacje uzupełniające
1. Pozostałe obiekty relacyjnych baz danych: indeksy, widoki, wyzwalacze, raporty,
formularze, itd.
2. Podsumowanie składni polecenia SELECT.
3. Zastosowania SQL do definicji i modyfikacji danych.
4. Rozszerzenia SQL: PL/SQL, Transact-SQL, Embedded SQL.
5. Systemy zarządzania bazą danych.
6. Zadania administracyjne związane z bazami danych.
5