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.

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.

Pokaż rozwiązanie

R1Sg8JX8lLZ4U

Tabela pracownicy: pola i typy danych.

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.

Kwerendy warunkowe

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

SELECT * FROM pracownicy;

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

2. Dowiedzmy się czegoś o zarobkach pracowników: kto zarabia więcej niż 2250 zł?

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:

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.

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

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>:

SELECT imie, nazwisko, stanowisko, placa
FROM pracownicy
WHERE placa BETWEEN 1000 AND 2250
ORDER BY stanowisko ASC;

SELECT imie, nazwisko, stanowisko, placa
FROM pracownicy
WHERE placa;

SELECT imie, nazwisko, stanowisko, placa
FROM pracownicy
WHERE NOT placa;

Wyniki kwerend zawierają czasem wiele rekordów – ich liczbę możemy ograniczyć za pomocą klauzuli LIMIT, np.:

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:

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

SELECT placa FROM pracownicy SELECT placa FROM pracownicy ORDER BY placa ASC LIMIT 1;

SELECT placa FROM pracownicy SELECT placa FROM pracownicy ORDER BY placa DESC LIMIT 1;

Kwerendy zliczające

4. Na pewno ważną informacją jest to, ilu pracowników zatrudnia firma. Do zliczania użyjemy funkcji COUNT().

SELECT COUNT(*) FROM pracownicy;

5. Sprawdźmy, ile kobiet pracuje w analizowanej firmie. Wykorzystamy założenie, że wyłącznie imiona kobiet kończą się na literę „a”.

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.

Aby uniknąć polegania na heurystyceheurystykaheurystyce, wykorzystajmy kolumnę plec.

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:

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.

6. W podobny sposób możemy uzyskać liczbę zatrudnionych mężczyzn.

SELECT COUNT(*)
FROM pracownicy
WHERE plec = 'm';

7. Nakładanie warunków na pola z datami jest w bazach SQLite proste. Zobaczmy, ilu pracowników zostało zatrudnionych przed rokiem 2000.

SELECT COUNT(*)
FROM pracownicy
WHERE dataz < '2000-01-01';

Wartości unikalne

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

SELECT stanowisko FROM pracownicy;

Taka kwerenda zwraca listę powtarzających się stanowisk kolejnych pracowników. Możemy spróbować następującego sposobu:

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.

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.

SELECT COUNT (DISTINCT stanowisko) FROM pracownicy;

Wartości skrajne

9. Który z pracowników ma najdłuższy staż pracy? Zapytajmy o najwcześniejszą datę zatrudnienia.

SELECT imie, nazwisko, MIN(dataz) FROM pracownicy;

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

10. Kto pracuje najkrócej? Prawidłową odpowiedź uzyskamy, gdy znajdziemy osobę zatrudnioną najpóźniej.

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