Przykładowe zapytania SQL dla bazy danych biblioteki

Biorąc pod uwagę stworzoną przez nas bazę danych biblioteki, możemy zadać następujące pytania i odpowiednio sformułować zapytania SQL:

1. Zapytanie grupujące: Ile książek napisał każdy autor?

SELECT autorzy.imie, autorzy.nazwisko, COUNT(*) AS liczba_książek

FROM autorzy

INNER JOIN ksiazki ON autorzy.id = ksiazki.autor_id

GROUP BY autorzy.id;

• Wyjaśnienie: Zapytanie łączy tabele autorzy i ksiazki na podstawie autor_id. Następnie grupuje wyniki według identyfikatora autora i liczy liczbę książek dla każdego autora.

2. Zapytanie grupujące: Który rok miał najwięcej wydanych książek?

SELECT rok_wydania, COUNT(*) AS liczba_książek

FROM ksiazki

GROUP BY rok_wydania

ORDER BY liczba_książek DESC

LIMIT 1;

• Wyjaśnienie: Zapytanie grupuje książki według roku wydania, liczy liczbę książek dla każdego roku i sortuje wyniki malejąco po liczbie książek. Następnie wybiera tylko pierwszy wiersz, czyli rok z największą liczbą wydanych książek.

3. Zapytanie relacyjne: Wyświetl tytuły książek napisanych przez Jana Kowalskiego.

SELECT tytul

FROM ksiazki

INNER JOIN autorzy ON ksiazki.autor_id = autorzy.id

WHERE autorzy.imie = 'Jan' AND autorzy.nazwisko = 'Kowalski';

• Wyjaśnienie: Zapytanie łączy tabele ksiazki i autorzy i wybiera tytuły książek, gdzie autor ma imię "Jan" i nazwisko "Kowalski".

4. Zapytanie relacyjne: Którzy czytelnicy wypożyczyli książki napisane przez Annę Nowak?

Uwaga: Aby odpowiedzieć na to pytanie, musielibyśmy dodać do naszej bazy danych tabelę wypozyczenia, która łączyłaby czytelników z książkami. Załóżmy, że tabela wypozyczenia ma kolumny: id, czytelnik_id, ksiazka_id, data_wypozyczenia.

SELECT czytelnicy.imie, czytelnicy.nazwisko

FROM czytelnicy

INNER JOIN wypozyczenia ON czytelnicy.id = wypozyczenia.czytelnik_id

INNER JOIN ksiazki ON wypozyczenia.ksiazka_id = ksiazki.id

INNER JOIN autorzy ON ksiazki.autor_id = autorzy.id

WHERE autorzy.imie = 'Anna' AND autorzy.nazwisko = 'Nowak';

• Wyjaśnienie: To bardziej złożone zapytanie łączy wszystkie cztery tabele: czytelnicy, wypozyczenia, ksiazki i autorzy. Wybiera imiona i nazwiska czytelników, którzy wypożyczyli książki napisane przez Annę Nowak.

5. Zapytanie grupujące i relacyjne: Która książka została wypożyczona najwięcej razy?

SELECT ksiazki.tytul, COUNT(*) AS liczba_wypozyczen

FROM ksiazki

INNER JOIN wypozyczenia ON ksiazki.id = wypozyczenia.ksiazka_id

GROUP BY ksiazki.id

ORDER BY liczba_wypozyczen DESC

LIMIT 1;

Nadawanie uprawnień w bazach danych: szczegółowe wyjaśnienie

Co to są uprawnienia w bazie danych?

Uprawnienia w bazie danych określają, jakie działania może wykonywać dany użytkownik lub grupa użytkowników na obiektach w bazie (tabelach, widokach, procedurach itp.). Mogą to być uprawnienia do:

• Odczytu: przeglądania danych

• Modyfikacji: aktualizowania, usuwania danych

• Tworzenia: tworzenia nowych obiektów

• Wykonywania: uruchamiania procedur, funkcji

Jak nadać uprawnienia?

Sposób nadawania uprawnień zależy od systemu zarządzania bazą danych (RDBMS), którego używasz. Najczęściej używanym językiem do zarządzania uprawnieniami jest SQL.

Przykład dla MySQL:

GRANT SELECT, INSERT, UPDATE ON baza_danych.tabela TO 'uzytkownik'@'host';

• GRANT: Słowo kluczowe inicjujące polecenie nadania uprawnień.

• SELECT, INSERT, UPDATE: Określa jakie uprawnienia nadajemy (odczyt, wstawianie, aktualizacja). Możesz dodać więcej uprawnień, np. DELETE (usuwanie).

• ON baza_danych.tabela: Określa na jakim obiekcie nadajemy uprawnienia (baza danych, tabela).

• TO 'uzytkownik'@'host';: Określa dla kogo nadajemy uprawnienia (użytkownik o podanej nazwie, łączący się z określonego hosta).

Inne przykłady:

• Nadanie pełnych uprawnień:

GRANT ALL PRIVILEGES ON baza_danych.* TO 'uzytkownik'@'host';

• Nadanie uprawnień do tworzenia tabel:

GRANT CREATE TABLE ON baza_danych TO 'uzytkownik'@'host';

• Nadanie uprawnień do wykonywania procedury:

GRANT EXECUTE ON PROCEDURE baza_danych.procedura TO 'uzytkownik'@'host';

• Odwołanie uprawnień:

REVOKE SELECT, INSERT ON baza_danych.tabela FROM 'uzytkownik'@'host';

Ważne uwagi:

• Hierarchia uprawnień: W wielu systemach istnieje hierarchia uprawnień, np. użytkownik z uprawnieniami do modyfikacji ma również uprawnienia do odczytu.

• Role: Wiele systemów pozwala na tworzenie ról, które grupują uprawnienia. Możesz następnie przypisywać role do użytkowników.

• Bezpieczeństwo: Nadawaj uprawnienia z rozwagą. Zbyt szerokie uprawnienia mogą stanowić zagrożenie dla bezpieczeństwa danych.

• Specyfika systemów: Każdy system RDBMS ma swoje własne dialekty SQL i dodatkowe funkcje związane z zarządzaniem uprawnieniami.

Dlaczego zarządzanie uprawnieniami jest ważne?

• Bezpieczeństwo: Chroni dane przed nieautoryzowanym dostępem.

• Kontrola: Pozwala na precyzyjne określenie, kto może co robić w bazie danych.

• Zgodność: Pomaga spełnić wymagania dotyczące ochrony danych.

Zapytania SQL dotyczące uprawnień w bazie danych biblioteki

Rozbudowując naszą bazę danych biblioteki o aspekty związane z uprawnieniami użytkowników, możemy zadać następujące pytania:

Założenia:

• Dodajemy do tabeli czytelnicy kolumnę typ_konta (np. 'standard', 'premium', 'pracownik').

• Pracownicy mają dodatkowe uprawnienia, np. możliwość rezerwacji książek dla innych użytkowników.

• Wprowadzamy tabelę rezerwacje z polami: id, czytelnik_id, ksiazka_id, data_rezerwacji.

6. Którzy czytelnicy mają status "premium"?

SELECT imie, nazwisko

FROM czytelnicy

WHERE typ_konta = 'premium';

7. Które książki zostały zarezerwowane przez pracowników?

SELECT ksiazki.tytul

FROM ksiazki

INNER JOIN rezerwacje ON ksiazki.id = rezerwacje.ksiazka_id

INNER JOIN czytelnicy ON rezerwacje.czytelnik_id = czytelnicy.id

WHERE czytelnicy.typ_konta = 'pracownik';

8. Ile książek zostało zarezerwowanych przez czytelników standardowych na najbliższy miesiąc?

SELECT COUNT(*) AS liczba_rezerwacji

FROM rezerwacje

INNER JOIN czytelnicy ON rezerwacje.czytelnik_id = czytelnicy.id

WHERE czytelnicy.typ_konta = 'standard'

  AND data_rezerwacji BETWEEN CURDATE() AND LAST_DAY(CURDATE() + INTERVAL 1 MONTH);

• Wyjaśnienie: Zapytanie liczy rezerwacje złożone przez czytelników standardowych w bieżącym miesiącu. CURDATE() zwraca dzisiejszą datę, a LAST_DAY(CURDATE() + INTERVAL 1 MONTH) oblicza ostatni dzień bieżącego miesiąca.