Naszym zadaniem jest zdobycie informacji o pewnej firmie. Do dyspozycji mamy bazę danych SQLite3 zapisaną w pliku pracownicy.db, którą pobieramy i zapisujemy na dysku. Jako narzędzie pracy wykorzystamy program SQLiteStudio. Informacje na temat instalacji i używania tego narzędzia zawarte zostały w e‑materiale Definiowanie schematu bazy danych w języku SQL, etap IIPkXbedRKMDefiniowanie schematu bazy danych w języku SQL, etap II.
Na początku warto poznać schemat bazyschemat bazyschemat bazy.
Ćwiczenie 1
Po uruchomieniu programu SQLiteStudio wybierz polecenie Bazy danych | Dodaj bazę danych i wskaż położenie pliku pracownicy.db. Następnie połącz się z bazą, dwukrotnie klikając nazwę bazy w panelu Bazy danych.
Kliknij dwa razy nazwę tabeli pracownicy, w zakładce Struktura zobaczysz nazwy i typy danych pól, zawartych w tabeli. Po wybraniu zakładki Dane zobaczysz przechowywane w tabeli wartości. Jeżeli klikniesz zakładkę DDL, zobaczysz polecenie SQL, które zostało użyte do utworzenia danej tabeli.
R1Sg8JX8lLZ4U
Tabela pracownicy: pola i typy danych.
Tabela pracownicy: pola i typy danych.
Źródło: Contentplus.pl sp. z o.o., licencja: CC BY-SA 3.0.
W tabeli pracownicy znajdują się pola:
id – VARCHAR, klucz główny, tekstowy identyfikator pracownika,
nazwisko, imie, stanowisko – VARCHAR, dane tekstowe,
dataz – VARCHAR, pole przechowujące datę zatrudnienia pracownika jako tekst w formacie ISO8601 „YYYY‑MM‑DD”, czyli cztery cyfry roku, dwie miesiąca i dwie dnia oddzielone znakiem łącznik‑minus,
placa – NUMERIC, pole zawierające płacę,
dzial – INTEGER, pole zawierające numer działu.
Do budowania zapytań w programie SQLiteStudio używa się edytora. Wywołujemy go za pomocą polecenia Narzędzia | Edytor. Można korzystać z kilku okien edytora jednocześnie. Polecenia zakończone średnikiem wpisuje się lub wkleja, można je także umieszczać w wielu wierszach. W jednym oknie edytora możliwe jest wpisanie wielu poleceń.
Wykonanie polecenia następuje po kliknięciu ikony niebieskiej strzałki lub po naciśnięciu klawisza F9. W sytuacji, gdy zapisanych jest wiele poleceń, wykonywane jest to, które wybraliśmy kliknięciem myszy.
R1PVuV6XuwDbe
Zrzut ekranu środowiska SQLiteStudio. W kolumnie po lewej tabele bazy danych pracownicy i uczniowie. W głównej części okna wybrano zakładkę Zapytanie, gdzie w liniach wyświetlono kod SELECT * FROM pracownicy WHERE placa > 2250 ORDER BY placa ASC; oraz SELECT imie, nazwisko, stanowisko, placa FROM pracownicy. Kod w liniach 1‑4 podświetlono na niebiesko. Poniżej obszar zakładki Widok siatki, a w nim wyniki podświetlonego zapytania w polach: id, nazwisko, imie, plec, stanowisko, dataz, placa i dzial. Pod spodem obszar Status z wyświetlona listą statusów.
Program SQLiteStudio.
Źródło: Contentplus.pl sp. z o.o., licencja: CC BY-SA 3.0.
Kwerendy warunkowe
Zacznijmy od przeglądu wszystkich informacji zapisanych w bazie. Wybierzmy wszystkie rekordyrekordrekordy z tabeli pracownicy.
Linia 1. SELECT asterysk FROM pracownicy średnik.
SELECT * FROM pracownicy;
W tej kwerendzie nie nakładamy żadnych warunków. Symbol * oznacza, że pobieramy wszystkie pola rekordów.
Dowiedzmy się czegoś o zarobkach pracowników: kto zarabia więcej niż 2250 zł?
Linia 1. SELECT asterysk.
Linia 2. FROM pracownicy.
Linia 3. WHERE placa zamknij nawias ostrokątny 2250 średnik.
SELECT *
FROM pracownicy
WHERE placa > 2250;
Warunki dodajemy w postaci wyrażeń w klauzuli WHERE.
Rekordy będące wynikiem kwerendy możemy porządkować za pomocą klauzuli ORDER BY. Uzupełniamy poprzedni przykład:
Linia 1. SELECT asterysk.
Linia 2. FROM pracownicy.
Linia 3. WHERE placa zamknij nawias ostrokątny 2250.
Linia 4. ORDER BY placa ASC średnik.
SELECT *
FROM pracownicy
WHERE placa > 2250
ORDER BY placa ASC;
Sposób sortowania określamy za pomocą słów kluczowych, ASC – rosnąco, DESC – malejąco.
Chcielibyśmy poznać imiona, nazwiska i stanowiska osób, którym firma płaci od 1000 do 2250 zł. Wyniki uporządkujemy rosnąco według stanowisk.
Linia 1. SELECT imie przecinek nazwisko przecinek stanowisko przecinek placa.
Linia 2. FROM pracownicy.
Linia 3. WHERE placa zamknij nawias ostrokątny znak równości 1000 AND placa otwórz nawias ostrokątny znak równości 2250.
Linia 4. ORDER BY stanowisko ASC średnik.
SELECT imie, nazwisko, stanowisko, placa
FROM pracownicy
WHERE placa >= 1000 AND placa <= 2250
ORDER BY stanowisko ASC;
Możemy łączyć warunki za pomocą logicznego operatoraoperatory logicznelogicznego operatora koniunkcji AND lub używać logicznej alternatywy, czyli operatora OR. W przypadku koniunkcji obydwa warunki muszą być prawdziwe, aby rekord został wybrany. W przypadku alternatywy prawdziwy musi być przynajmniej jeden warunek.
Zamiast koniunkcji dwóch warunków wyznaczających zakres szukanych wartości można użyć operatora BETWEEN wartość1 AND wartość2, który wyszukuje wartości w zakresie <wartość1; wartość2>:
Linia 1. SELECT imie przecinek nazwisko przecinek stanowisko przecinek placa.
Linia 2. FROM pracownicy.
Linia 3. WHERE placa BETWEEN 1000 AND 2250.
Linia 4. ORDER BY stanowisko ASC średnik.
SELECT imie, nazwisko, stanowisko, placa
FROM pracownicy
WHERE placa BETWEEN 1000 AND 2250
ORDER BY stanowisko ASC;
Ćwiczenie 2
Wykonaj podane zapytania. Kiedy warunek w klauzuli WHERE jest prawdziwy?
Linia 1. SELECT imie przecinek nazwisko przecinek stanowisko przecinek placa.
Linia 2. FROM pracownicy.
Linia 3. WHERE placa średnik.
SELECT imie, nazwisko, stanowisko, placa
FROM pracownicy
WHERE placa;
Linia 1. SELECT imie przecinek nazwisko przecinek stanowisko przecinek placa.
Linia 2. FROM pracownicy.
Linia 3. WHERE NOT placa średnik.
SELECT imie, nazwisko, stanowisko, placa
FROM pracownicy
WHERE NOT placa;
Pierwsze zapytanie zwraca wszystkie rekordy zawierające w polu placa dodatnią wartość całkowitą, która interpretowana jest jako logiczna wartość prawda (ang. true). Drugie zapytanie zwróci rekordy zawierające w polu placa wartości, które interpretowane są jako logiczny fałsz (ang. false). W przypadku analizowanej bazy drugie zapytanie nie zwróci niczego, ponieważ wszyscy pracownicy mają przypisaną jakąś płacę.
Wyniki kwerend zawierają czasem wiele rekordów – ich liczbę możemy ograniczyć za pomocą klauzuli LIMIT, np.:
Linia 1. SELECT imie przecinek nazwisko przecinek stanowisko przecinek placa.
Linia 2. FROM pracownicy.
Linia 3. WHERE placa BETWEEN 1000 AND 2250.
Linia 4. ORDER BY stanowisko ASC.
Linia 5. LIMIT 5 średnik.
SELECT imie, nazwisko, stanowisko, placa
FROM pracownicy
WHERE placa BETWEEN 1000 AND 2250
ORDER BY stanowisko ASC
LIMIT 5;
Klauzula LIMIT może przyjmować dwa parametry, czyli liczbę rekordów do wypisania i liczbę rekordów, które powinniśmy pominąć. Właściwość ta często wykorzystywana jest w serwisach internetowych wyświetlających – strona po stronie – listy obiektów. Przykład wykorzystania:
Linia 1. SELECT imie przecinek nazwisko przecinek stanowisko przecinek placa.
Linia 2. FROM pracownicy.
Linia 3. WHERE placa BETWEEN 1000 AND 2250.
Linia 4. ORDER BY stanowisko ASC.
Linia 5. LIMIT 5 OFFSET 4 średnik.
SELECT imie, nazwisko, stanowisko, placa
FROM pracownicy
WHERE placa BETWEEN 1000 AND 2250
ORDER BY stanowisko ASC
LIMIT 5 OFFSET 4;
Połączenie klauzul ORDER BY i LIMIT można wykorzystać do odnajdywania rekordów zawierających pola z wartością najmniejszą lub największą.
Ćwiczenie 3
Przygotuj zapytania, które za pomocą klauzuli ORDER BY i LIMIT znajdą odpowiedź, na pytanie: „Jaka jest minimalna i maksymalna płaca w firmie?”.
Minimalna płaca to 1000.
Linia 1. SELECT placa FROM pracownicy SELECT placa FROM pracownicy ORDER BY placa ASC LIMIT 1 średnik.
SELECT placa FROM pracownicy SELECT placa FROM pracownicy ORDER BY placa ASC LIMIT 1;
Maksymalna płaca to 4650.
Linia 1. SELECT placa FROM pracownicy SELECT placa FROM pracownicy ORDER BY placa DESC LIMIT 1 średnik.
SELECT placa FROM pracownicy SELECT placa FROM pracownicy ORDER BY placa DESC LIMIT 1;
Kwerendy zliczające
Na pewno ważną informacją jest to, ilu pracowników zatrudnia firma. Do zliczania użyjemy funkcji COUNT().
Linia 1. SELECT COUNT otwórz nawias okrągły asterysk zamknij nawias okrągły FROM pracownicy średnik.
SELECT COUNT(*) FROM pracownicy;
Sprawdźmy, ile kobiet pracuje w analizowanej firmie. Wykorzystamy założenie, że wyłącznie imiona kobiet kończą się na literę „a”.
Linia 1. SELECT COUNT otwórz nawias okrągły asterysk zamknij nawias okrągły.
Linia 2. FROM pracownicy.
Linia 3. WHERE imie LIKE apostrof procent a apostrof średnik.
SELECT COUNT(*)
FROM pracownicy
WHERE imie LIKE '%a';
Operator LIKE pozwala na przeszukiwanie tekstu na podstawie wzorca, w którym symbol % oznacza dowolny znak występujący wielokrotnie, a symbol _ dowolny znak występujący raz.
Ważne!
Przyjęte założenie, że imiona kobiet kończą się na literę „a”, jest oczywiście uproszczeniem – pokazane zostało w celu objaśnienia kwerendy wykorzystującej dopasowanie tekstu. W kolejnym kroku zbudujemy kwerendę dającą rozwiązanie dokładne.
Aby uniknąć polegania na heurystyceheurystykaheurystyce, wykorzystajmy kolumnę plec.
Linia 1. SELECT COUNT otwórz nawias okrągły asterysk zamknij nawias okrągły.
Linia 2. FROM pracownicy.
Linia 3. WHERE plec znak równości apostrof k apostrof średnik.
SELECT COUNT(*)
FROM pracownicy
WHERE plec = 'k';
Jak można zauważyć, wyniki są różne. Aby sprawdzić, które imiona kobiet nie kończą się na „a”, wykonajmy następujący kod:
Linia 1. SELECT imie.
Linia 2. FROM pracownicy.
Linia 3. WHERE plec znak równości apostrof k apostrof AND imie NOT LIKE apostrof procent a apostrof średnik.
SELECT imie
FROM pracownicy
WHERE plec = 'k' AND imie NOT LIKE '%a';
Wykorzystujemy tu zaprzeczenie przy użyciu słowa kluczowego NOT w warunku dopasowania imienia.
W podobny sposób możemy uzyskać liczbę zatrudnionych mężczyzn.
Linia 1. SELECT COUNT otwórz nawias okrągły asterysk zamknij nawias okrągły.
Linia 2. FROM pracownicy.
Linia 3. WHERE plec znak równości apostrof m apostrof średnik.
SELECT COUNT(*)
FROM pracownicy
WHERE plec = 'm';
Nakładanie warunków na pola z datami jest w bazach SQLite proste. Zobaczmy, ilu pracowników zostało zatrudnionych przed rokiem 2000.
Linia 1. SELECT COUNT otwórz nawias okrągły asterysk zamknij nawias okrągły.
Linia 2. FROM pracownicy.
Linia 3. WHERE dataz otwórz nawias ostrokątny apostrof 2000 minus 01 minus 01 apostrof średnik.
SELECT COUNT(*)
FROM pracownicy
WHERE dataz < '2000-01-01';
W bazie SQLite3 daty zapisane w standardzie ISO 8601, czyli według wzorca RRRR‑MM‑DD, przechowywane są jako TEXT.
Wartości unikalne
Jakie stanowiska istnieją w badanej firmie? Ile ich jest?
Linia 1. SELECT stanowisko FROM pracownicy średnik.
SELECT stanowisko FROM pracownicy;
Taka kwerenda zwraca listę powtarzających się stanowisk kolejnych pracowników. Możemy spróbować następującego sposobu:
Linia 1. SELECT COUNT otwórz nawias okrągły stanowisko zamknij nawias okrągły FROM pracownicy średnik.
SELECT COUNT(stanowisko) FROM pracownicy;
Jednak jest to złe rozwiązanie, bo otrzymamy tylko liczbę wszystkich rekordów w tabeli. Musimy więc nakazać zwracanie wartości unikalnych.
Linia 1. SELECT DISTINCT stanowisko FROM pracownicy średnik.
SELECT DISTINCT stanowisko FROM pracownicy;
Prawie się udało, a to dzięki użyciu klauzuli DISTINCT, która zwraca tylko unikalne wartości. Ale chyba nie chcemy ręcznie sprawdzać liczby zwróconych pól? Połączmy więc funkcję zliczającą i klauzulę wymuszającą unikalność wartości.
Linia 1. SELECT COUNT otwórz nawias okrągły DISTINCT stanowisko zamknij nawias okrągły FROM pracownicy średnik.
SELECT COUNT (DISTINCT stanowisko) FROM pracownicy;
Wartości skrajne
Który z pracowników ma najdłuższy staż pracy? Zapytajmy o najwcześniejszą datę zatrudnienia.
Linia 1. SELECT imie przecinek nazwisko przecinek MIN otwórz nawias okrągły dataz zamknij nawias okrągły FROM pracownicy średnik.
SELECT imie, nazwisko, MIN(dataz) FROM pracownicy;
Funkcja MIN() działa tu tak samo jak w arkuszu kalkulacyjnym.
Kto pracuje najkrócej? Prawidłową odpowiedź uzyskamy, gdy znajdziemy osobę zatrudnioną najpóźniej.
Linia 1. SELECT imie przecinek nazwisko przecinek MAX otwórz nawias okrągły dataz zamknij nawias okrągły FROM pracownicy średnik.
SELECT imie, nazwisko, MAX(dataz) FROM pracownicy;
Argumentami funkcji MIN() i MAX() są pola zawierające badane wartości, w tym wypadku: daty zatrudnienia.
Słownik
heurystyka
heurystyka
podejście, które pozwala uzyskać rozwiązanie przybliżone, tylko w szczególnych przypadkach dokładne
operatory logiczne
operatory logiczne
pozwalają łączyć warunki proste w złożone, należą do nich AND (koniunkcja), OR (alternatywa) i NOT (negacja); wynikiem utworzonych w ten sposób wyrażeń jest PRAWDA lub FAŁSZ
rekord
rekord
w kontekście relacyjnej bazy danych to jeden wiersz tabeli, zawierający wartości wszystkich pól; rekordem nazywa się również wiersze uzyskiwane w wynikach zapytań – każdy taki wiersz zawiera wartości z pól wskazanych w zapytaniu
schemat bazy
schemat bazy
schematyczny opis tabel, zawartych w nich kolumn oraz ich typów danych, a także relacji, czyli związków między tabelami