Ćwiczenia MS Access/SQL I. Zadania podstawowe 1

Ćwiczenia MS Access/SQL
I. Zadania podstawowe
1. Wyświetlanie zawartości tabeli
a) Polecenie: Wyświetl wszystkie rekordy z tabeli Pracownicy (wszystkie
atrybuty).
SELECT *
FROM Pracownicy;
b) Polecenie: Wyświetl dane (wszystkie atrybuty) wszystkich klientów.
SELECT *
FROM Klienci;
2. Sortowanie ORDER
a) Polecenie: Wyświetl wszystkie rekordy z tabeli Pracownicy (wszystkie atrybuty)
posortowane alfabetycznie według nazwiska, a następnie według imienia.
SELECT *
FROM Pracownicy
ORDER BY Nazwisko, Imię;
b) Polecenie: Wyświetl wszystkie rekordy z tabeli Produkty (wszystkie atrybuty)
posortowane malejąco według ceny jednostkowej.
SELECT *
FROM Produkty
ORDER BY CenaJednostkowa DESC;
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.
SELECT IDZamówienia, DataZamówienia, MiastoOdbiorcy, KrajOdbiorcy
FROM 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.
SELECT Imię, Nazwisko, Miasto
FROM Pracownicy
ORDER BY Nazwisko, Imię, Miasto;
4. Wyświetlanie części rekordów tabeli
a) Polecenie: Wypisz pierwsze 4 kategorie produktów i ich opis.
SELECT TOP 4 NazwaKategorii, Opis
FROM Kategorie;
1
b) Polecenie: Wyświetl wszystkie informacje na temat 10 najdroŜszych produktów
z tabeli Produkty.
SELECT TOP 10 *
FROM Produkty
ORDER BY CenaJednostkowa DESC;
c) Polecenie: Wypisz pierwsze 25% wszystkich zamówień, wyświetl numer
zamówienia, opis i ilość dla zamówienia.
SELECT TOP 25 PERCENT IDzamówienia, IDproduktu, Ilość
FROM [Opisy zamówień];
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.
SELECT *
FROM Produkty
WHERE StanMagazynu<30;
b) Polecenie: Wyświetl nazwy, adresy i miasta dostawców ze Szwecji,
posortowane według nazw firm.
SELECT NazwaFirmy, Adres, Miasto
FROM Dostawcy
WHERE Kraj='Szwecja';
c) Polecenie: Wyświetl wszystkie zamówienia, które zostały złoŜone po 1997 roku
i wysłane do Londynu.
SELECT *
FROM Zamówienia
WHERE (DataZamówienia>#12/31/1997#) AND (MiastoOdbiorcy='Londyn');
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.
SELECT *
FROM [Opisy zamówień]
WHERE (CenaJednostkowa>200) OR (Ilość>100);
2. Polecenie BETWEEN AND
a) Polecenie: Wyświetl wszystkich pracowników (imię, nazwisko, stanowisko)
przyjętych do pracy w 1 kwartale 1994 roku.
SELECT Imię, Nazwisko, Stanowisko
FROM Pracownicy
WHERE DataZatrudnienia BETWEEN #1/1/1994# AND #3/31/1994#;
2
b) Polecenie: Wyświetl wszystkie zamówienia, dla których fracht mieści się w
granicach 80-120 zł.
SELECT *
FROM Zamówienia
WHERE Fracht BETWEEN 80 AND 120;
3. Polecenie IN
Polecenie: Wyświetl ID dostawcy, firmę, Przedstawiciela i jego stanowisko
pochodzących z Niemiec, Szwecji lub Holandii.
SELECT IDDostawcy, NazwaFirmy, Przedstawiciel,
StanowiskoPrzedstawiciela
FROM Dostawcy
WHERE Kraj IN ('Niemcy', 'Szwecja', 'Holandia');
4. Polecenie LIKE
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
a) Polecenie: Wybierz kategorie, do których zostały zaklasyfikowane owoce.
SELECT *
FROM Kategorie
WHERE Opis LIKE '*owoce*';
b) Polecenie: Wyświetl informacje na temat wszystkich pracowników, których
imię zaczyna się na literę „A”.
SELECT *
FROM Pracownicy
WHERE Imię LIKE 'A*';
5. Wybór nieznanych wartości IS (NOT) NULL
a) Polecenie: wypisz informacje o pracownikach, dla których jest wypełnione pole
region.
SELECT *
FROM Pracownicy
WHERE Region IS NOT NULL;
3
b) Polecenie: wyświetl nazwy tych dostawców, którzy nie posiadają strony
internetowej.
SELECT NazwaFirmy
FROM Dostawcy
WHERE StronaMacierzysta IS NULL;
6. Wybór nie powtarzających się informacji DISTINCT
a) Polecenie: Wypisz wszystkie miasta, z których pochodzą klienci.
SELECT DISTINCT Miasto
FROM Klienci;
b) Wypisz wszystkie miasta, w których dokonano zamówień w sierpniu 1996.
SELECT DISTINCT MiastoOdbiorcy
FROM Zamówienia
WHERE DataZamówienia BETWEEN #8/1/1996# AND #8/31/1996#;
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 pracowników literę „N” na znak „_”.
SELECT Replace(Imię, 'n', '_')
FROM Pracownicy;
b) Polecenie: Wypisz nazwy miast, oraz pierwsze 3 znaki i ostatnie 45 znaków.
SELECT Nazwa_miasto, LEFT(Nazwa_miasto, 3) AS Pierwsze3, RIGHT
(Nazwa_miasto, 45) AS Ostatnie45
FROM Miasto;
c) Polecenie: Wypisz imiona i nazwiska pracowników, zamieniając nazwiska na
litery drukowane.
SELECT Imię, UCase(Nazwisko) AS NazwiskoDrukowanymi
FROM Pracownicy;
2. Operatory arytmetyczne: + - / * %
a) Polecenie: Powiększ cenę jednostkową wszystkich produktów o 10% i wypisz
jako nową kolumnę.
SELECT *, CenaJednostkowa*1.1 AS NowaCena
FROM Produkty;
4
b) Wyznacz wartości poszczególnych produktów w magazynie (wyświetl wraz z
nazwami produktów).
SELECT NazwaProduktu, StanMagazynu*CenaJednostkowa AS Wartość
FROM Produkty;
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ę.
SELECT NazwaProduktu, CCur(Round(CenaJednostkowa,0))
FROM Produkty;
4. Funkcje daty: DAY, MONTH, YEAR, DATE, 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.
SELECT DAY(Date()) AS Dzień, MONTH(Date()) AS Miesiąc, YEAR(Date())
AS Rok;
b) Wypisz pracowników (imię i nazwisko) oraz dzień (datę), w którym upływa
(upłynęło) 10 lat od momentu zatrudnienia. Uwzględnij tylko 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 (zakłada się dla
uproszczenia, Ŝe upływ roku następuje wraz z przejściem do kolejnego roku
kalendarzowego – np. pracownikowi zatrudnionemu 31.XII.00 roczny staŜ
przypisuje się juŜ 1.I.01).
SELECT Imię, Nazwisko, DateAdd('yyyy', 10, DataZatrudnienia)
FROM Pracownicy
WHERE (YEAR(Date()) - YEAR(DataUrodzenia) < 50)
AND (YEAR(Date()) - YEAR(DataZatrudnienia) > 13)
AND (YEAR(DataZatrudnienia) - YEAR(DataUrodzenia) < 35);
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
SELECT Nazwisko+' '+Imię+' '+Stanowisko AS Dane
FROM Pracownicy;
5
b) Polecenie: Wyświetl w jednej kolumnie o nazwie dane: nazwisko, imię, w
następnej literał: pracuje od, data zatrudnienia pracownika
SELECT Nazwisko, Imię, 'Pracuje od:', DataZatrudnienia
FROM Pracownicy;
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ń.
SELECT SUM(Fracht)
FROM Zamówienia;
b) Wyświetl sumę wartości produktów w magazynie.
SELECT SUM(StanMagazynu*CenaJednostkowa) AS SumaWartości
FROM Produkty;
c) Polecenie: Zlicz ilość rekordów znajdujących się w tablicy Produkty.
SELECT COUNT(*)
FROM Produkty;
d) Polecenie: Wyświetl wartość sprzedaŜy poszczególnych produktów, bez
rabatów (ID produktu i wartość sprzedaŜy).
SELECT IDProduktu, SUM(Ilość*CenaJednostkowa) AS WartośćBezRabatów
FROM [Opisy zamówień]
GROUP BY IDProduktu;
e) Polecenie: Wyświetl średnie ilości sprzedaŜy według poziomów przyznanych
rabatów.
SELECT Rabat, AVG(Ilość) AS
FROM [Opisy zamówień]
GROUP BY Rabat;
f)
ŚredniaIlość
Polecenie: Wypisz zamówienia, posortowane malejąco wg ceny do zapłaty
przez klienta.
SELECT IDZamówienia, SUM(CCur(Round(Ilość*CenaJednostkowa*(1Rabat),2))) AS DoZapłaty
FROM [Opisy zamówień]
GROUP BY IDZamówienia
ORDER BY 2 DESC;
g) Polecenie: j.w., przy czym wyświetlane są tylko zamówienia o kwocie
przekraczającej 5000 zł.
SELECT IDZamówienia, SUM(CCur(Round(Ilość*CenaJednostkowa*(1Rabat),2))) AS DoZapłaty
FROM [Opisy zamówień]
GROUP BY IDZamówienia
HAVING SUM(Round(Ilość*CenaJednostkowa*(1-Rabat),2))>5000
ORDER BY 2 DESC;
6
h) Polecenie: Wypisz największą, najmniejszą i przeciętną (niewaŜoną) cenę
produktu w magazynie.
SELECT MIN(CenaJednostkowa) AS Najmniejsza, MAX(CenaJednostkowa) AS
Największa, AVG(CenaJednostkowa) AS Średnia
FROM Produkty;
V. Łączenie tabel
1. Złączenia wewnętrzne: INNER JOIN
a) Polecenie: wyświetl ID zamówień połączone z danymi spedytora.
SELECT IDzamówienia, Spedytorzy.*
FROM Spedytorzy INNER JOIN Zamówienia ON Spedytorzy.IDspedytora =
Zamówienia.IDspedytora;
b) Polecenie: wyświetl nazwy miast, posortowane alfabetycznie, wraz z nazwami
regionów (tabele: Miasto, Region).
SELECT Nazwa_miasto, Nazwa_region
FROM Region INNER JOIN Miasto ON Region.IDregion = Miasto.IDregion;
c) Polecenie: wyświetl imiona i nazwiska pracowników oraz nazwy miast, które są
pod ich opieką (tabele: Pracownicy, Pracownicy_miasto, Miasto).
SELECT Imię, Nazwisko, Nazwa_miasto
FROM Pracownicy INNER JOIN (Miasto INNER JOIN Pracownicy_miasto ON
Miasto.IDmiasto = Pracownicy_miasto.IDmiasto) ON
Pracownicy.IDpracownika = Pracownicy_miasto.IDpracownicy;
d) Polecenie: wyświetl miasta, które są pod opieką pracowników o numerach 1 i
2.
SELECT DISTINCT Nazwa_miasto
FROM Miasto INNER JOIN Pracownicy_miasto ON Miasto.IDmiasto =
Pracownicy_miasto.IDmiasto
WHERE IDPracownicy IN (1,2);
e) Polecenie: utwórz ranking pracowników według wartości dokonanej przez nich
sprzedaŜy (uwzględniającej rabaty).
SELECT Pracownicy.IDPracownika, Imię, Nazwisko,
SUM(CCur(Round(Ilość*CenaJednostkowa*(1-Rabat),2)))
FROM (Pracownicy INNER JOIN Zamówienia ON Pracownicy.IDpracownika =
Zamówienia.IDpracownika) INNER JOIN [Opisy zamówień] ON
Zamówienia.IDzamówienia = [Opisy zamówień].IDzamówienia
GROUP BY Pracownicy.IDPracownika, Imię, Nazwisko
ORDER BY 4 DESC;
2. CROSS JOIN (tylko wyjaśnienie)
7
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.
SELECT Imię, Nazwisko, IDMiasto
FROM Pracownicy LEFT OUTER JOIN Pracownicy_miasto ON
Pracownicy.IDpracownika = Pracownicy_miasto.IDpracownicy;
4. Podzapytania
a) Polecenie: Wypisz 5 najtańszych produktów spośród produktów
o ponadprzeciętnych cenach.
SELECT TOP 5 *
FROM Produkty
WHERE CenaJednostkowa>
(SELECT AVG(CenaJednostkowa) FROM Produkty)
ORDER BY CenaJednostkowa;
b) Polecenie: wyświetl nazwiska i imiona pracowników, którzy nie sprzedali
niczego do Argentyny.
SELECT Nazwisko, Imię
FROM Pracownicy
WHERE IDPracownika NOT IN
(SELECT DISTINCT IDPracownika
FROM Zamówienia
WHERE KrajOdbiorcy='Argentyna');
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.
SELECT NazwaFirmy, Przedstawiciel, 'Klient'
FROM Klienci
UNION
SELECT NazwaFirmy, Przedstawiciel, 'Dostawca'
FROM Dostawcy;
8