Ć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;
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#;
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
Rodzaj zgodności Wzorzec
Zgodność (zwraca True)
Brak zgodności (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;
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;
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 FLOOR SIN ATAN 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 SECOND DAYNAME
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
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 ŚredniaIlość FROM [Opisy zamówień]
GROUP BY Rabat;
f) Polecenie: Wypisz zamówienia, posortowane malejąco wg ceny do zapłaty przez klienta.
SELECT IDZamówienia, SUM(CCur(Round(Ilość*CenaJednostkowa*(1- Rabat),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*(1- Rabat),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;
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)
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;
