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.

RucWu3ZLvDFNp

Plik bazy pracownicy.db.
Plik DB o rozmiarze 52.00 KB w języku polskim

Baza danych

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.

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
Program SQLiteStudio.
Źródło: Contentplus.pl sp. z o.o., licencja: CC BY-SA 3.0.

Kwerendy warunkowe

  1. Zacznijmy od przeglądu wszystkich informacji zapisanych w bazie. Wybierzmy wszystkie rekordyrekordrekordy z tabeli pracownicy.

Linia 1. SELECT asterysk FROM pracownicy średnik.

W tej kwerendzie nie nakładamy żadnych warunków. Symbol * oznacza, że pobieramy wszystkie pola rekordów.

  1. 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.

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.

Sposób sortowania określamy za pomocą słów kluczowych, ASC – rosnąco, DESC – malejąco.

  1. 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.

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.
Ć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.
Linia 1. SELECT imie przecinek nazwisko przecinek stanowisko przecinek placa. Linia 2. FROM pracownicy. Linia 3. WHERE NOT placa średnik.

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.

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.

Połączenie klauzul ORDER BYLIMIT 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 BYLIMIT znajdą odpowiedź, na pytanie: „Jaka jest minimalna i maksymalna płaca w firmie?”.

Kwerendy zliczające

  1. 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.
  1. 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.

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.

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.

Wykorzystujemy tu zaprzeczenie przy użyciu słowa kluczowego NOT w warunku dopasowania imienia.

  1. 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.
  1. 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.

W bazie SQLite3 daty zapisane w standardzie ISO 8601, czyli według wzorca RRRR‑MM‑DD, przechowywane są jako TEXT.

Wartości unikalne

  1. Jakie stanowiska istnieją w badanej firmie? Ile ich jest?

Linia 1. SELECT stanowisko FROM pracownicy średnik.

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.

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.

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.

Wartości skrajne

  1. 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.

Funkcja MIN() działa tu tak samo jak w arkuszu kalkulacyjnym.

  1. 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.

Argumentami funkcji MIN()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