Design a Library Database in T-SQL

Design a normalized relational database with stored procedures and triggers.

Introduction

30 Second Summary

Every library needs a system to track its books, who wrote them, who borrowed them, and when they're due back. Behind the scenes, a well-designed database makes all of that possible.

In this project, you will design and build a complete relational database for a library management system using T-SQL. You'll create tables with proper relationships, write complex queries, and implement business logic with stored procedures and triggers.

What You'll Build

A normalized relational database with 6 tables, stored procedures, views, triggers, and indexes that powers a library's book lending operations.

By the end of this project, you'll have:

  • A database schema of 6 normalized tables with proper relationships, primary keys, foreign keys, and constraints.
  • A set of stored procedures that handle borrowing and returning books with full validation and error handling.
  • Reporting views and complex queries demonstrating JOINs, subqueries, and aggregations.
  • Secret Mission: A monthly report procedure using window functions like RANK and LAG to generate book popularity rankings.

Are there any prerequisites?

Basic SQL knowledge (SELECT, INSERT, WHERE) is helpful but not required. This project includes full installation instructions for SQL Server 2022 Express and SSMS 22, so you don't need anything pre-installed.

Zainstaluj SQL Server i SSMS

Zanim zaczniesz projektować bazę danych, potrzebujesz dwóch narzędzi: serwera bazy danych, który przechowuje dane, i edytora, w którym piszesz zapytania SQL.

W tym kroku zainstalujesz SQL Server 2022 Express (darmowy serwer bazy danych od Microsoftu) i SQL Server Management Studio 22 (SSMS, graficzny edytor do pisania T-SQL). Po instalacji połączysz się z serwerem i potwierdzisz, że wszystko działa.

In this step, get ready to:
  • Zainstalować SQL Server 2022 Express na swoim komputerze.
  • Zainstalować SQL Server Management Studio 22 (SSMS).
  • Połączyć się z serwerem i potwierdzić działające połączenie.
Zainstaluj SQL Server 2022 Express

SQL Server 2022 Express to darmowa edycja serwera bazy danych Microsoftu. Obsługuje do 10 GB danych i 1 GB pamięci RAM. To wystarczy do nauki i małych projektów.

  • Sprawdź, czy SQL Server jest już zainstalowany na twoim komputerze. Otwórz PowerShell i uruchom tę komendę:
Get-Service -Name 'MSSQL$SQLEXPRESS'

✔️ Widzę Status: Running

SQL Server Express jest już zainstalowany i uruchomiony. Przejdź do następnego podkroku (instalacja SSMS).

ⓧ Widzę usługę, ale z innym statusem

SQL Server Express jest zainstalowany, ale nie uruchomiony. Uruchom go za pomocą tej komendy:

Start-Service -Name 'MSSQL$SQLEXPRESS'

Po uruchomieniu usługi przejdź do następnego podkroku.

ⓧ Widzę błąd lub usługa nie znaleziona

SQL Server nie jest zainstalowany. Zainstaluj go teraz, wykonując poniższe kroki.

  • Otwórz przeglądarkę i wejdź na stronę pobierania SQL Server 2022 Express:
  • https://www.microsoft.com/en-us/download/details.aspx?id=104781
  • Pobierz plik SQL2022-SSEI-Expr.exe i uruchom go.
  • Wybierz typ instalacji Basic.
  • Zaakceptuj warunki licencji klikając Accept.
  • Pozostaw domyślną lokalizację instalacji i kliknij Install.
  • Poczekaj na zakończenie instalacji. Instalator pobierze i zainstaluje wszystkie wymagane komponenty automatycznie.

Co robi instalacja Basic?

Typ Basic instaluje silnik bazy danych z domyślnymi ustawieniami. Tworzy instancję o nazwie SQLEXPRESS, która nasłuchuje na porcie dynamicznym. Nie musisz konfigurować nic ręcznie.

Po zakończeniu instalacji zobaczysz ekran potwierdzenia z informacjami o instancji. Zanotuj nazwę instancji SQLEXPRESS wyświetloną na ekranie.

  • Zweryfikuj instalację, uruchamiając ponownie komendę w PowerShell:
Get-Service -Name 'MSSQL$SQLEXPRESS'

Powinieneś zobaczyć Status: Running.

Usługa nie uruchamia się?

Upewnij się, że uruchomiłeś PowerShell jako Administrator (kliknij prawym przyciskiem na PowerShell i wybierz Run as Administrator). Instalacja SQL Server wymaga uprawnień administratora.

Jeśli instalacja się nie powiodła, spróbuj uruchomić instalator ponownie jako Administrator.

Potrzebujesz pomocy? Pomóż mi rozwiązać problem z instalacją SQL Server Express

Zainstaluj SQL Server Management Studio 22

SSMS to graficzne narzędzie do zarządzania bazami danych SQL Server. Pisanie zapytań, przeglądanie tabel i analizowanie planów wykonania odbywa się właśnie tutaj.

  • Sprawdź, czy SSMS jest już zainstalowany. Naciśnij klawisz Windows i wpisz SQL Server Management Studio.

✔️ Widzę SQL Server Management Studio 22

SSMS 22 jest już zainstalowany. Przejdź do następnego podkroku (połączenie z serwerem).

ⓧ Widzę starszą wersję SSMS

Masz zainstalowaną starszą wersję SSMS. Zainstaluj SSMS 22 obok niej. Otwórz PowerShell jako Administrator i uruchom:

winget install Microsoft.SQLServerManagementStudio.22

Komenda wymaga uprawnień administratora. Visual Studio Installer otworzy się i przeprowadzi instalację.

Po zakończeniu instalacji, naciśnij klawisz Windows i wpisz SQL Server Management Studio. Powinieneś zobaczyć wersję 22.

ⓧ Nie widzę SSMS w wynikach wyszukiwania

SSMS nie jest zainstalowany. Zainstaluj go za pomocą jednej z poniższych metod.

Metoda 1 (zalecana): Otwórz PowerShell jako Administrator i uruchom:

winget install Microsoft.SQLServerManagementStudio.22

Metoda 2 (alternatywna): Jeśli komenda winget nie działa, pobierz plik vs_SSMS.exe ze strony Microsoft Learn:

Po zakończeniu instalacji, naciśnij klawisz Windows i wpisz SQL Server Management Studio. Powinieneś zobaczyć aplikację w wynikach.

Dlaczego winget a nie tradycyjny instalator?

SSMS 22 nie ma tradycyjnego instalatora MSI. Microsoft dystrybuuje go przez Visual Studio Installer. Komenda winget automatyzuje pobranie i uruchomienie bootstrappera, oszczędzając kilka kroków.

Winget nie jest rozpoznawany?

Upewnij się, że masz Windows 10 w wersji 1809 lub nowszej. Zaktualizuj aplikację App Installer ze sklepu Microsoft Store. Po aktualizacji zamknij i otwórz PowerShell ponownie.

Jeśli winget dalej nie działa, użyj metody 2 z pobieraniem pliku vs_SSMS.exe bezpośrednio ze strony Microsoft.

Potrzebujesz pomocy? Pomóż mi zainstalować SSMS 22 na Windows

Połącz się z serwerem w SSMS

Teraz sprawdzisz, czy SQL Server działa poprawnie, łącząc się z nim przez SSMS.

  • Naciśnij klawisz Windows i wpisz SQL Server Management Studio.
  • Naciśnij Enter aby otworzyć aplikację.
  • Okno Connect to Server powinno pojawić się automatycznie przy pierwszym uruchomieniu. Jeśli się nie pojawi, kliknij Object Explorer > Connect > Database Engine.
  • W polu Server name wpisz localhost\SQLEXPRESS.
  • W polu Authentication pozostaw Windows Authentication.
  • Zaznacz pole Trust server certificate.
  • Kliknij Connect.

Dlaczego Trust server certificate?

SSMS 22 domyślnie wymaga szyfrowanego połączenia (Encryption: Mandatory). Lokalna instalacja SQL Server Express nie ma zainstalowanego certyfikatu TLS.

Zaznaczenie Trust server certificate mówi SSMS, żeby zaakceptował samopodpisany certyfikat serwera. W środowisku deweloperskim na localhost jest to bezpieczne.

Po połączeniu powinieneś zobaczyć w panelu Object Explorer nazwę serwera, wersję SQL Server i nazwę użytkownika Windows.

Nie możesz się połączyć?

Upewnij się, że wpisałeś dokładnie localhost\SQLEXPRESS (z backslashem, nie forwardem). Wielkość liter nie ma znaczenia.

Sprawdź, czy usługa SQL Server działa. Otwórz PowerShell i uruchom Get-Service -Name 'MSSQL$SQLEXPRESS'. Status musi być Running.

Potrzebujesz pomocy? Pomóż mi połączyć SSMS z localhost SQLEXPRESS

Twoje środowisko jest gotowe. W następnym kroku utworzysz bazę danych i zaprojektujesz tabele z relacjami.

Utwórz Bazę Danych i Tabele

Masz zainstalowane narzędzia i jesteś połączony z serwerem SQL. Teraz pora zaprojektować strukturę bazy danych, która będzie przechowywać wszystkie informacje biblioteki.

Dobra baza danych zaczyna się od schematu. Schemat to zestaw tabel, kolumn, kluczy i reguł, które pilnują spójności danych. W tym kroku utworzysz 6 tabel z relacjami i wypełnisz je przykładowymi danymi.

In this step, get ready to:
  • Utworzyć bazę danych LibraryDB z 6 znormalizowanymi tabelami.
  • Wstawić przykładowe dane (autorzy, książki, czytelnicy, wypożyczenia).
  • Zweryfikować constrainty, testując nieprawidłowe dane.
Utwórz bazę danych i tabele encji

Każda baza danych zaczyna się od polecenia CREATE DATABASE. Następnie definiujesz tabele z kolumnami, typami danych i ograniczeniami (constraintami).

  • Otwórz nowe okno zapytania w SSMS, naciskając Ctrl+N.
  • Wklej poniższy kod, który tworzy bazę danych i dwie pierwsze tabele:
-- Tworzenie bazy danych LibraryDB
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'LibraryDB')
    CREATE DATABASE LibraryDB;
GO

USE LibraryDB;
GO

-- Tabela autorów
CREATE TABLE Authors (
    AuthorId INT IDENTITY(1,1) PRIMARY KEY,
    FirstName NVARCHAR(100) NOT NULL,
    LastName NVARCHAR(100) NOT NULL,
    BirthDate DATE NULL,
    Country NVARCHAR(50) NULL
);
GO

-- Tabela książek
CREATE TABLE Books (
    BookId INT IDENTITY(1,1) PRIMARY KEY,
    Title NVARCHAR(200) NOT NULL,
    ISBN NVARCHAR(13) NOT NULL UNIQUE,
    PublishedYear INT NOT NULL CHECK (PublishedYear > 0 AND PublishedYear <= YEAR(GETDATE())),
    Genre NVARCHAR(50) NOT NULL,
    TotalCopies INT NOT NULL DEFAULT 1 CHECK (TotalCopies > 0),
    AvailableCopies INT NOT NULL DEFAULT 1 CHECK (AvailableCopies >= 0)
);
GO

Co robi ten kod?

  • IF NOT EXISTS sprawdza, czy baza danych już istnieje, zanim ją utworzy. Dzięki temu możesz uruchomić skrypt wielokrotnie bez błędu.
  • IDENTITY(1,1) automatycznie numeruje nowe wiersze od 1, zwiększając o 1 z każdym nowym rekordem.
  • CHECK na PublishedYear blokuje wstawienie roku publikacji z przyszłości lub wartości ujemnej.
  • DEFAULT 1 na TotalCopies oznacza, że jeśli nie podasz liczby kopii, baza automatycznie ustawi 1.
  • Wykonaj zapytanie, naciskając F5 lub klikając przycisk Execute na pasku narzędzi.
  • W panelu Messages na dole powinieneś zobaczyć komunikat Commands completed successfully..

Widzisz błąd przy wykonaniu?

  • Upewnij się, że jesteś połączony z serwerem localhost\SQLEXPRESS. Sprawdź status połączenia w dolnym pasku SSMS.
  • Jeśli widzisz błąd o braku uprawnień, upewnij się, że łączysz się przez Windows Authentication.
  • Pomóż mi zdebugować błąd tworzenia bazy danych w SSMS.

Teraz dodasz tabelę łączącą książki z autorami (relacja wiele-do-wielu) oraz tabelę czytelników.

  • Wklej poniższy kod poniżej poprzedniego (w tym samym oknie zapytania) i zaznacz tylko nowy fragment przed wykonaniem:
-- Tabela łącząca książki z autorami (wiele-do-wielu)
CREATE TABLE BookAuthors (
    BookId INT NOT NULL,
    AuthorId INT NOT NULL,
    CONSTRAINT PK_BookAuthors PRIMARY KEY (BookId, AuthorId),
    CONSTRAINT FK_BookAuthors_Books FOREIGN KEY (BookId) REFERENCES Books(BookId),
    CONSTRAINT FK_BookAuthors_Authors FOREIGN KEY (AuthorId) REFERENCES Authors(AuthorId)
);
GO

-- Tabela czytelników
CREATE TABLE Members (
    MemberId INT IDENTITY(1,1) PRIMARY KEY,
    FirstName NVARCHAR(100) NOT NULL,
    LastName NVARCHAR(100) NOT NULL,
    Email NVARCHAR(150) NOT NULL UNIQUE,
    Phone NVARCHAR(15) NULL,
    JoinDate DATE NOT NULL DEFAULT GETDATE(),
    IsActive BIT NOT NULL DEFAULT 1
);
GO

Co robi ten kod?

Tabela BookAuthors to tabela łącząca (junction table). Jedna książka może mieć wielu autorów, a jeden autor może napisać wiele książek. Klucz główny składa się z dwóch kolumn (composite key), co gwarantuje unikalność każdej pary książka-autor.

Klucze obce (FOREIGN KEY) pilnują, że nie da się powiązać książki z nieistniejącym autorem. Baza odrzuci taką próbę z błędem.

  • Zaznacz nowo wklejony fragment kodu i naciśnij F5, aby go wykonać.
  • Sprawdź, że w panelu Messages widzisz Commands completed successfully..

Na koniec dodasz dwie tabele do śledzenia wypożyczeń. Aktywne wypożyczenia trafiają do Loans, a po zwrocie książki rekord jest przenoszony do LoanHistory.

  • Wklej poniższy kod i zaznacz go do wykonania:
-- Tabela aktywnych wypożyczeń
CREATE TABLE Loans (
    LoanId INT IDENTITY(1,1) PRIMARY KEY,
    BookId INT NOT NULL,
    MemberId INT NOT NULL,
    LoanDate DATE NOT NULL DEFAULT GETDATE(),
    DueDate DATE NOT NULL,
    CONSTRAINT FK_Loans_Books FOREIGN KEY (BookId) REFERENCES Books(BookId),
    CONSTRAINT FK_Loans_Members FOREIGN KEY (MemberId) REFERENCES Members(MemberId)
);
GO

-- Tabela historii wypożyczeń
CREATE TABLE LoanHistory (
    LoanHistoryId INT IDENTITY(1,1) PRIMARY KEY,
    BookId INT NOT NULL,
    MemberId INT NOT NULL,
    LoanDate DATE NOT NULL,
    DueDate DATE NOT NULL,
    ReturnDate DATE NOT NULL DEFAULT GETDATE(),
    CONSTRAINT FK_LoanHistory_Books FOREIGN KEY (BookId) REFERENCES Books(BookId),
    CONSTRAINT FK_LoanHistory_Members FOREIGN KEY (MemberId) REFERENCES Members(MemberId)
);
GO

Dlaczego dwie osobne tabele na wypożyczenia?

Rozdzielenie aktywnych wypożyczeń od historycznych to częsty wzorzec w systemach produkcyjnych. Tabela Loans pozostaje mała i szybka, bo zawiera tylko kilkanaście aktualnych rekordów. Historyczna tabela LoanHistory rośnie z czasem, ale nie spowalnia codziennych operacji.

  • Zaznacz nowy fragment i naciśnij F5.
  • W Object Explorer po lewej stronie rozwiń Databases, kliknij prawym przyciskiem na Databases i wybierz Refresh.
  • Rozwiń LibraryDB > Tables. Powinieneś zobaczyć 6 tabel: Authors, BookAuthors, Books, LoanHistory, Loans, Members.

Nie widzisz tabel w Object Explorer?

  • Kliknij prawym przyciskiem na Tables i wybierz Refresh. Object Explorer nie odświeża się automatycznie po wykonaniu zapytań.
  • Jeśli nie widzisz LibraryDB w drzewie, kliknij prawym przyciskiem na Databases i wybierz Refresh.
  • Pomóż mi znaleźć moje tabele w SSMS Object Explorer.
  • Zapisz plik, naciskając Ctrl+S. Zapisz go jako 01_CreateDatabase.sql w wybranym folderze na pulpicie.

✔️ Awesome, I've got everything!

Sprawdź, że masz 6 tabel widocznych w Object Explorer pod LibraryDB > Tables.

ⓧ I'd like to double check the full code

Oto pełny plik 01_CreateDatabase.sql do porównania:

-- Tworzenie bazy danych LibraryDB
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'LibraryDB')
    CREATE DATABASE LibraryDB;
GO

USE LibraryDB;
GO

-- Tabela autorów
CREATE TABLE Authors (
    AuthorId INT IDENTITY(1,1) PRIMARY KEY,
    FirstName NVARCHAR(100) NOT NULL,
    LastName NVARCHAR(100) NOT NULL,
    BirthDate DATE NULL,
    Country NVARCHAR(50) NULL
);
GO

-- Tabela książek
CREATE TABLE Books (
    BookId INT IDENTITY(1,1) PRIMARY KEY,
    Title NVARCHAR(200) NOT NULL,
    ISBN NVARCHAR(13) NOT NULL UNIQUE,
    PublishedYear INT NOT NULL CHECK (PublishedYear > 0 AND PublishedYear <= YEAR(GETDATE())),
    Genre NVARCHAR(50) NOT NULL,
    TotalCopies INT NOT NULL DEFAULT 1 CHECK (TotalCopies > 0),
    AvailableCopies INT NOT NULL DEFAULT 1 CHECK (AvailableCopies >= 0)
);
GO

-- Tabela łącząca książki z autorami (wiele-do-wielu)
CREATE TABLE BookAuthors (
    BookId INT NOT NULL,
    AuthorId INT NOT NULL,
    CONSTRAINT PK_BookAuthors PRIMARY KEY (BookId, AuthorId),
    CONSTRAINT FK_BookAuthors_Books FOREIGN KEY (BookId) REFERENCES Books(BookId),
    CONSTRAINT FK_BookAuthors_Authors FOREIGN KEY (AuthorId) REFERENCES Authors(AuthorId)
);
GO

-- Tabela czytelników
CREATE TABLE Members (
    MemberId INT IDENTITY(1,1) PRIMARY KEY,
    FirstName NVARCHAR(100) NOT NULL,
    LastName NVARCHAR(100) NOT NULL,
    Email NVARCHAR(150) NOT NULL UNIQUE,
    Phone NVARCHAR(15) NULL,
    JoinDate DATE NOT NULL DEFAULT GETDATE(),
    IsActive BIT NOT NULL DEFAULT 1
);
GO

-- Tabela aktywnych wypożyczeń
CREATE TABLE Loans (
    LoanId INT IDENTITY(1,1) PRIMARY KEY,
    BookId INT NOT NULL,
    MemberId INT NOT NULL,
    LoanDate DATE NOT NULL DEFAULT GETDATE(),
    DueDate DATE NOT NULL,
    CONSTRAINT FK_Loans_Books FOREIGN KEY (BookId) REFERENCES Books(BookId),
    CONSTRAINT FK_Loans_Members FOREIGN KEY (MemberId) REFERENCES Members(MemberId)
);
GO

-- Tabela historii wypożyczeń
CREATE TABLE LoanHistory (
    LoanHistoryId INT IDENTITY(1,1) PRIMARY KEY,
    BookId INT NOT NULL,
    MemberId INT NOT NULL,
    LoanDate DATE NOT NULL,
    DueDate DATE NOT NULL,
    ReturnDate DATE NOT NULL DEFAULT GETDATE(),
    CONSTRAINT FK_LoanHistory_Books FOREIGN KEY (BookId) REFERENCES Books(BookId),
    CONSTRAINT FK_LoanHistory_Members FOREIGN KEY (MemberId) REFERENCES Members(MemberId)
);
GO
Wstaw przykładowe dane

Puste tabele nie pozwalają przetestować zapytań. Teraz wypełnisz je realistycznymi danymi, żebyś mógł pisać zapytania na prawdziwych rekordach.

  • Otwórz nowe okno zapytania, naciskając Ctrl+N.
  • Wklej poniższy kod, który wstawia autorów i książki:
USE LibraryDB;
GO

-- Wstaw autorów
INSERT INTO Authors (FirstName, LastName, BirthDate, Country) VALUES
('Henryk', 'Sienkiewicz', '1846-05-05', 'Polska'),
('Adam', 'Mickiewicz', '1798-12-24', 'Polska'),
('Stanislaw', 'Lem', '1921-09-12', 'Polska'),
('Olga', 'Tokarczuk', '1962-01-29', 'Polska'),
('Andrzej', 'Sapkowski', '1948-06-21', 'Polska'),
('J.K.', 'Rowling', '1965-07-31', 'Wielka Brytania'),
('George', 'Orwell', '1903-06-25', 'Wielka Brytania'),
('Frank', 'Herbert', '1920-10-08', 'USA'),
('Isaac', 'Asimov', '1920-01-02', 'USA'),
('Stephen', 'King', '1947-09-21', 'USA');
GO

-- Wstaw książki
INSERT INTO Books (Title, ISBN, PublishedYear, Genre, TotalCopies, AvailableCopies) VALUES
('Quo Vadis', '9788308049451', 1896, 'Powieść historyczna', 3, 3),
('Pan Tadeusz', '9788373271234', 1834, 'Poemat epicki', 2, 2),
('Solaris', '9788308061234', 1961, 'Science Fiction', 4, 4),
('Bieguni', '9788308041234', 2007, 'Powieść', 2, 2),
('Krew elfów', '9788375780631', 1994, 'Fantasy', 3, 3),
('Harry Potter i Kamien Filozoficzny', '9780747532743', 1997, 'Fantasy', 5, 5),
('Rok 1984', '9780451524935', 1949, 'Dystopia', 3, 3),
('Diuna', '9780441172719', 1965, 'Science Fiction', 2, 2),
('Fundacja', '9780553293357', 1951, 'Science Fiction', 3, 3),
('Lsnienie', '9780385121675', 1977, 'Horror', 2, 2),
('Ogniem i mieczem', '9788308051234', 1884, 'Powieść historyczna', 2, 2),
('Potop', '9788308052345', 1886, 'Powieść historyczna', 2, 2),
('Cyberiada', '9788308063456', 1965, 'Science Fiction', 3, 3),
('Ostatnie zyczenie', '9788375780624', 1993, 'Fantasy', 3, 3),
('Folwark zwierzecy', '9780451526342', 1945, 'Satyra polityczna', 2, 2);
GO

-- Wstaw powiązania książek z autorami
INSERT INTO BookAuthors (BookId, AuthorId) VALUES
(1, 1), (2, 2), (3, 3), (4, 4), (5, 5),
(6, 6), (7, 7), (8, 8), (9, 9), (10, 10),
(11, 1), (12, 1), (13, 3), (14, 5), (15, 7);
GO

Co robi ten kod?

Wstawiasz 10 autorów (polscy klasycy i autorzy zagraniczni) oraz 15 książek z różnych gatunków. Każda książka ma unikalny ISBN, rok publikacji i liczbę kopii.

Tabela BookAuthors łączy książki z autorami. Henryk Sienkiewicz (AuthorId 1) ma trzy książki (BookId 1, 11, 12). Stanisław Lem (AuthorId 3) ma dwie (BookId 3, 13). To demonstruje relację wiele-do-wielu.

  • Naciśnij F5, aby wykonać cały skrypt.
  • W panelu Messages powinieneś zobaczyć komunikaty potwierdzające wstawienie wierszy (np. (10 rows affected), (15 rows affected)).

Teraz wstaw czytelników i dane o wypożyczeniach.

  • Wklej poniższy kod poniżej poprzedniego fragmentu i zaznacz go do wykonania:
-- Wstaw czytelników
INSERT INTO Members (FirstName, LastName, Email, Phone, JoinDate) VALUES
('Jan', 'Kowalski', 'jan.kowalski@email.com', '500100200', '2024-01-15'),
('Anna', 'Nowak', 'anna.nowak@email.com', '500200300', '2024-02-20'),
('Piotr', 'Wisniewski', 'piotr.wisniewski@email.com', '500300400', '2024-03-10'),
('Maria', 'Wojcik', 'maria.wojcik@email.com', '500400500', '2024-04-05'),
('Tomasz', 'Kaminski', 'tomasz.kaminski@email.com', '500500600', '2024-05-12');
GO

-- Wstaw aktywne wypożyczenia
INSERT INTO Loans (BookId, MemberId, LoanDate, DueDate) VALUES
(1, 1, '2026-05-20', '2026-06-20'),
(3, 1, '2026-05-25', '2026-06-25'),
(6, 2, '2026-05-28', '2026-06-28'),
(7, 3, '2026-06-01', '2026-07-01'),
(5, 4, '2026-06-03', '2026-07-03');

-- Aktualizuj dostępne kopie
UPDATE Books SET AvailableCopies = AvailableCopies - 1 WHERE BookId IN (1, 3, 6, 7, 5);
GO

-- Wstaw historyczne wypożyczenia
INSERT INTO LoanHistory (BookId, MemberId, LoanDate, DueDate, ReturnDate) VALUES
(3, 2, '2026-01-10', '2026-02-10', '2026-02-05'),
(6, 1, '2026-01-15', '2026-02-15', '2026-02-10'),
(9, 3, '2026-02-01', '2026-03-01', '2026-02-28'),
(1, 4, '2026-02-15', '2026-03-15', '2026-03-10'),
(5, 5, '2026-03-01', '2026-04-01', '2026-03-25'),
(8, 1, '2026-03-10', '2026-04-10', '2026-04-08'),
(7, 2, '2026-04-01', '2026-05-01', '2026-04-28'),
(13, 3, '2026-04-15', '2026-05-15', '2026-05-10'),
(6, 4, '2026-04-20', '2026-05-20', '2026-05-18'),
(10, 5, '2026-05-01', '2026-06-01', '2026-05-28');
GO

Co robi ten kod?

Wstawiasz 5 czytelników, 5 aktywnych wypożyczeń i 10 rekordów historycznych. Polecenie UPDATE zmniejsza AvailableCopies dla wypożyczonych książek. Dzięki temu kolumna odzwierciedla rzeczywistą dostępność.

Jan Kowalski (MemberId 1) ma 2 aktywne wypożyczenia. Anna Nowak (MemberId 2) ma 1. Te dane przydadzą się później przy testowaniu limitu 3 wypożyczeń.

  • Zaznacz nowy fragment kodu i naciśnij F5.
  • Zapisz plik jako 02_InsertSampleData.sql w tym samym folderze co poprzedni plik (Ctrl+S).
  • Sprawdź dane szybkim zapytaniem. Otwórz nowe okno (Ctrl+N) i uruchom:
USE LibraryDB;
SELECT COUNT(*) AS AuthorCount FROM Authors;
SELECT COUNT(*) AS BookCount FROM Books;
SELECT COUNT(*) AS MemberCount FROM Members;
SELECT COUNT(*) AS ActiveLoanCount FROM Loans;
SELECT COUNT(*) AS HistoryCount FROM LoanHistory;

Powinieneś zobaczyć wyniki: 10 autorów, 15 książek, 5 czytelników, 5 aktywnych wypożyczeń i 10 rekordów historycznych.

Liczby się nie zgadzają?

  • Jeśli widzisz 0 wierszy w którejś tabeli, prawdopodobnie nie zaznaczyłeś odpowiedniego fragmentu kodu przed wykonaniem. Zaznacz ponownie odpowiedni INSERT i naciśnij F5.
  • Jeśli widzisz błąd o duplikacie klucza (UNIQUE KEY), oznacza to, że dane zostały już wstawione. Możesz kontynuować.
  • Pomóż mi naprawić problem z wstawianiem danych do LibraryDB.

✔️ Awesome, I've got everything!

Sprawdź, że wszystkie COUNT zwracają prawidłowe wartości. Możesz zamknąć to okno zapytania bez zapisywania.

ⓧ I'd like to double check the full code

Oto pełny plik 02_InsertSampleData.sql do porównania:

USE LibraryDB;
GO

-- Wstaw autorów
INSERT INTO Authors (FirstName, LastName, BirthDate, Country) VALUES
('Henryk', 'Sienkiewicz', '1846-05-05', 'Polska'),
('Adam', 'Mickiewicz', '1798-12-24', 'Polska'),
('Stanislaw', 'Lem', '1921-09-12', 'Polska'),
('Olga', 'Tokarczuk', '1962-01-29', 'Polska'),
('Andrzej', 'Sapkowski', '1948-06-21', 'Polska'),
('J.K.', 'Rowling', '1965-07-31', 'Wielka Brytania'),
('George', 'Orwell', '1903-06-25', 'Wielka Brytania'),
('Frank', 'Herbert', '1920-10-08', 'USA'),
('Isaac', 'Asimov', '1920-01-02', 'USA'),
('Stephen', 'King', '1947-09-21', 'USA');
GO

-- Wstaw książki
INSERT INTO Books (Title, ISBN, PublishedYear, Genre, TotalCopies, AvailableCopies) VALUES
('Quo Vadis', '9788308049451', 1896, 'Powieść historyczna', 3, 3),
('Pan Tadeusz', '9788373271234', 1834, 'Poemat epicki', 2, 2),
('Solaris', '9788308061234', 1961, 'Science Fiction', 4, 4),
('Bieguni', '9788308041234', 2007, 'Powieść', 2, 2),
('Krew elfów', '9788375780631', 1994, 'Fantasy', 3, 3),
('Harry Potter i Kamien Filozoficzny', '9780747532743', 1997, 'Fantasy', 5, 5),
('Rok 1984', '9780451524935', 1949, 'Dystopia', 3, 3),
('Diuna', '9780441172719', 1965, 'Science Fiction', 2, 2),
('Fundacja', '9780553293357', 1951, 'Science Fiction', 3, 3),
('Lsnienie', '9780385121675', 1977, 'Horror', 2, 2),
('Ogniem i mieczem', '9788308051234', 1884, 'Powieść historyczna', 2, 2),
('Potop', '9788308052345', 1886, 'Powieść historyczna', 2, 2),
('Cyberiada', '9788308063456', 1965, 'Science Fiction', 3, 3),
('Ostatnie zyczenie', '9788375780624', 1993, 'Fantasy', 3, 3),
('Folwark zwierzecy', '9780451526342', 1945, 'Satyra polityczna', 2, 2);
GO

-- Wstaw powiązania książek z autorami
INSERT INTO BookAuthors (BookId, AuthorId) VALUES
(1, 1), (2, 2), (3, 3), (4, 4), (5, 5),
(6, 6), (7, 7), (8, 8), (9, 9), (10, 10),
(11, 1), (12, 1), (13, 3), (14, 5), (15, 7);
GO

-- Wstaw czytelników
INSERT INTO Members (FirstName, LastName, Email, Phone, JoinDate) VALUES
('Jan', 'Kowalski', 'jan.kowalski@email.com', '500100200', '2024-01-15'),
('Anna', 'Nowak', 'anna.nowak@email.com', '500200300', '2024-02-20'),
('Piotr', 'Wisniewski', 'piotr.wisniewski@email.com', '500300400', '2024-03-10'),
('Maria', 'Wojcik', 'maria.wojcik@email.com', '500400500', '2024-04-05'),
('Tomasz', 'Kaminski', 'tomasz.kaminski@email.com', '500500600', '2024-05-12');
GO

-- Wstaw aktywne wypożyczenia
INSERT INTO Loans (BookId, MemberId, LoanDate, DueDate) VALUES
(1, 1, '2026-05-20', '2026-06-20'),
(3, 1, '2026-05-25', '2026-06-25'),
(6, 2, '2026-05-28', '2026-06-28'),
(7, 3, '2026-06-01', '2026-07-01'),
(5, 4, '2026-06-03', '2026-07-03');

-- Aktualizuj dostępne kopie
UPDATE Books SET AvailableCopies = AvailableCopies - 1 WHERE BookId IN (1, 3, 6, 7, 5);
GO

-- Wstaw historyczne wypożyczenia
INSERT INTO LoanHistory (BookId, MemberId, LoanDate, DueDate, ReturnDate) VALUES
(3, 2, '2026-01-10', '2026-02-10', '2026-02-05'),
(6, 1, '2026-01-15', '2026-02-15', '2026-02-10'),
(9, 3, '2026-02-01', '2026-03-01', '2026-02-28'),
(1, 4, '2026-02-15', '2026-03-15', '2026-03-10'),
(5, 5, '2026-03-01', '2026-04-01', '2026-03-25'),
(8, 1, '2026-03-10', '2026-04-10', '2026-04-08'),
(7, 2, '2026-04-01', '2026-05-01', '2026-04-28'),
(13, 3, '2026-04-15', '2026-05-15', '2026-05-10'),
(6, 4, '2026-04-20', '2026-05-20', '2026-05-18'),
(10, 5, '2026-05-01', '2026-06-01', '2026-05-28');
GO
Zweryfikuj constrainty testując nieprawidłowe dane

Constrainty mają sens tylko wtedy, gdy naprawdę blokują błędne dane. Teraz przetestujesz je, celowo wstawiając nieprawidłowe rekordy.

  • W nowym oknie zapytania (Ctrl+N) uruchom poniższe zapytania jedno po drugim, aby zobaczyć błędy:
USE LibraryDB;
GO

-- Test 1: Wypożyczenie nieistniejącej książki (BookId = 999)
-- Powinien pojawić się błąd FOREIGN KEY
INSERT INTO Loans (BookId, MemberId, LoanDate, DueDate)
VALUES (999, 1, GETDATE(), DATEADD(DAY, 30, GETDATE()));
GO

-- Test 2: Wstawienie książki z rokiem publikacji z przyszłości
-- Powinien pojawić się błąd CHECK constraint
INSERT INTO Books (Title, ISBN, PublishedYear, Genre)
VALUES ('Testowa', '1234567890123', 2099, 'Test');
GO

-- Test 3: Duplikat ISBN (ten sam ISBN co Quo Vadis)
-- Powinien pojawić się błąd UNIQUE constraint
INSERT INTO Books (Title, ISBN, PublishedYear, Genre)
VALUES ('Duplikat', '9788308049451', 2020, 'Test');
GO

Czego się spodziewać?

  • Test 1 zwróci błąd z frazą FOREIGN KEY constraint. Baza nie pozwala na wypożyczenie książki, której nie ma w tabeli Books.
  • Test 2 zwróci błąd z frazą CHECK constraint. Rok 2099 przekracza dozwolony zakres.
  • Test 3 zwróci błąd z frazą UNIQUE KEY. Dwie książki nie mogą mieć tego samego ISBN.

Każdy z tych błędów oznacza, że Twoja baza prawidłowo pilnuje spójności danych. To właśnie jest cel constraintów.

  • Zaznacz każde zapytanie testowe osobno i naciśnij F5. Upewnij się, że widzisz czerwony komunikat błędu dla każdego z nich.

Nie widzisz błędu?

  • Jeśli INSERT przeszedł bez błędu, sprawdź czy na pewno masz dane w tabelach (uruchom wcześniejsze COUNT zapytania). Puste tabele nie wywołają błędów FK.
  • Upewnij się, że pracujesz w kontekście bazy LibraryDB (sprawdź dropdown z nazwą bazy w pasku narzędzi SSMS).
  • Pomóż mi zrozumieć dlaczego constrainty nie blokują moich testowych INSERTów.

Masz kompletny schemat bazy danych z 6 tabelami, przykładowymi danymi i przetestowanymi constraintami. W następnym kroku napiszesz złożone zapytania, które wyciągną przydatne informacje z tych danych.

Napisz Złożone Zapytania

Twoja baza danych jest gotowa i wypełniona danymi. Masz 6 tabel, relacje między nimi i przykładowe wypożyczenia.

Teraz czas wyciągnąć z tych danych użyteczne informacje. W tym kroku napiszesz zapytania, które łączą dane z wielu tabel, liczą statystyki i odpowiadają na pytania biznesowe. To właśnie te umiejętności pracodawcy testują na rozmowach kwalifikacyjnych.

In this step, get ready to:
  • Napisać zapytania z INNER JOIN i LEFT JOIN łączące dane z wielu tabel.
  • Napisać zapytania z agregacjami (GROUP BY, HAVING, COUNT, AVG).
  • Napisać podzapytania (subqueries) rozwiązujące złożone problemy biznesowe.
Napisz zapytania z JOINami

Dane w bazie relacyjnej są rozłożone w osobnych tabelach. Żeby zobaczyć pełny obraz (np. kto wypożyczył jaką książkę jakiego autora), musisz połączyć te tabele za pomocą JOIN.

  • Otwórz nowe okno zapytania w SSMS naciskając Ctrl+N.
  • Wpisz poniższy kod, który ustawia kontekst bazy danych i wyświetla listę aktywnych wypożyczeń łącząc 5 tabel:
USE LibraryDB;
GO

-- 1. Lista aktywnych wypożyczeń z nazwami książek, autorów i czytelników (INNER JOIN + LEFT JOIN)
SELECT 
    l.LoanId,
    m.FirstName + ' ' + m.LastName AS MemberName,
    b.Title AS BookTitle,
    a.FirstName + ' ' + a.LastName AS AuthorName,
    l.LoanDate,
    l.DueDate,
    DATEDIFF(DAY, l.LoanDate, l.DueDate) AS DaysAllowed
FROM Loans l
INNER JOIN Members m ON l.MemberId = m.MemberId
INNER JOIN Books b ON l.BookId = b.BookId
INNER JOIN BookAuthors ba ON b.BookId = ba.BookId
INNER JOIN Authors a ON ba.AuthorId = a.AuthorId
ORDER BY l.LoanDate DESC;
GO

What does this code do?

To zapytanie łączy 5 tabel w jednym SELECT, żeby pokazać pełne informacje o każdym aktywnym wypożyczeniu.

  • INNER JOIN łączy tylko te wiersze, które mają dopasowanie w obu tabelach. Jeśli wypożyczenie nie miałoby przypisanego czytelnika, nie pojawiłoby się w wyniku.
  • Aliasy (l, m, b, a) skracają nazwy tabel i poprawiają czytelność.
  • DATEDIFF(DAY, l.LoanDate, l.DueDate) oblicza liczbę dni między datą wypożyczenia a terminem zwrotu.
  • Zaznacz całe zapytanie (od USE do GO) i naciśnij F5 aby je wykonać.
  • W zakładce Results powinieneś zobaczyć 5 wierszy z kolumnami: LoanId, MemberName, BookTitle, AuthorName, LoanDate, DueDate i DaysAllowed.

Nie widzisz wyników?

  • Upewnij się, że zaznaczasz cały blok od USE LibraryDB do końcowego GO. Bez USE zapytanie może szukać tabel w złej bazie.
  • Sprawdź czy w pasku stanu na dole SSMS widnieje LibraryDB jako aktywna baza danych.
  • Jeśli widzisz błąd o nieistniejącej kolumnie, sprawdź aliasy tabel. Każdy alias musi odpowiadać temu po FROM lub JOIN.

Potrzebujesz pomocy z błędem?

  • Dodaj poniżej (po GO) drugie zapytanie, które znajduje książki nigdy nie wypożyczone:
-- 2. Książki które nigdy nie były wypożyczone (LEFT JOIN + IS NULL)
SELECT 
    b.Title,
    b.ISBN,
    b.Genre
FROM Books b
LEFT JOIN Loans l ON b.BookId = l.BookId
LEFT JOIN LoanHistory lh ON b.BookId = lh.BookId
WHERE l.LoanId IS NULL AND lh.LoanHistoryId IS NULL;
GO

Dlaczego LEFT JOIN zamiast INNER JOIN?

INNER JOIN zwraca tylko wiersze z dopasowaniem w obu tabelach. Gdybyśmy użyli INNER JOIN z Loans, dostalibyśmy tylko książki KTÓRE BYŁY wypożyczone.

LEFT JOIN zwraca wszystkie wiersze z lewej tabeli (Books), nawet jeśli nie mają dopasowania w prawej (Loans/LoanHistory). Filtr WHERE l.LoanId IS NULL zostawia tylko te, które nie mają żadnego wypożyczenia.

  • Zaznacz zapytanie nr 2 i naciśnij F5.
  • Powinieneś zobaczyć listę książek takich jak Pan Tadeusz, Bieguni czy Ogniem i mieczem, które nie pojawiły się w żadnym wypożyczeniu.

Wynik jest pusty?

  • Jeśli wynik jest pusty, sprawdź czy w kroku 2 uruchomiłeś plik 02_InsertSampleData.sql w całości. Brak danych w LoanHistory sprawi, że LEFT JOIN nie zadziała poprawnie.
  • Upewnij się, że warunek WHERE sprawdza kolumny l.LoanId IS NULL i lh.LoanHistoryId IS NULL (nie BookId).

Pomóż mi zrozumieć wynik mojego LEFT JOIN.

Napisz zapytania z agregacjami

Agregacje pozwalają odpowiadać na pytania biznesowe: które książki są najpopularniejsze, jaki jest średni czas wypożyczenia. Używasz GROUP BY do grupowania danych i funkcji takich jak COUNT czy AVG do obliczania statystyk.

  • Dodaj poniżej kolejne zapytanie, które łączy aktywne wypożyczenia z historią (UNION ALL) i pokazuje TOP 5 najpopularniejszych książek:
-- 3. TOP 5 najpopularniejszych książek (agregacja z UNION)
SELECT TOP 5
    b.Title,
    a.FirstName + ' ' + a.LastName AS AuthorName,
    COUNT(*) AS TotalLoans
FROM (
    SELECT BookId FROM Loans
    UNION ALL
    SELECT BookId FROM LoanHistory
) AS AllLoans
INNER JOIN Books b ON AllLoans.BookId = b.BookId
INNER JOIN BookAuthors ba ON b.BookId = ba.BookId
INNER JOIN Authors a ON ba.AuthorId = a.AuthorId
GROUP BY b.Title, a.FirstName, a.LastName
ORDER BY TotalLoans DESC;
GO

What does this code do?

  • UNION ALL łączy wiersze z tabeli Loans (aktywne) i LoanHistory (zwrócone) w jeden zbiór. Dzięki temu liczymy WSZYSTKIE wypożyczenia, nie tylko aktywne.
  • GROUP BY grupuje wyniki po tytule i autorze, a COUNT(*) liczy ile razy każda książka została wypożyczona.
  • TOP 5 w połączeniu z ORDER BY TotalLoans DESC zwraca 5 książek z największą liczbą wypożyczeń.
  • Zaznacz zapytanie nr 3 i naciśnij F5.
  • Powinieneś zobaczyć 5 wierszy z tytułami książek, nazwiskami autorów i liczbą wypożyczeń (TotalLoans). Harry Potter i Solaris powinny być wysoko w rankingu.

Błąd przy GROUP BY?

  • Każda kolumna w SELECT, która nie jest w funkcji agregującej (COUNT, SUM, AVG), musi być wymieniona w GROUP BY. Jeśli brakuje a.FirstName lub a.LastName w GROUP BY, zobaczysz błąd.
  • Sprawdź czy podzapytanie z UNION ALL ma nawias zamykający i alias AS AllLoans.

Pomóż mi naprawić błąd GROUP BY.

  • Dodaj zapytanie obliczające średni czas wypożyczenia dla każdego gatunku literackiego:
-- 6. Średni czas wypożyczenia per gatunek
SELECT 
    b.Genre,
    COUNT(*) AS TotalReturns,
    AVG(DATEDIFF(DAY, lh.LoanDate, lh.ReturnDate)) AS AvgDaysLoaned
FROM LoanHistory lh
INNER JOIN Books b ON lh.BookId = b.BookId
GROUP BY b.Genre
HAVING COUNT(*) >= 1
ORDER BY AvgDaysLoaned DESC;
GO

What does this code do?

  • AVG(DATEDIFF(DAY, lh.LoanDate, lh.ReturnDate)) oblicza średnią liczbę dni od wypożyczenia do zwrotu dla każdego gatunku.
  • Korzystamy tylko z LoanHistory bo tylko zwrócone książki mają datę zwrotu (ReturnDate).
  • HAVING COUNT(*) >= 1 filtruje grupy po agregacji. W odróżnieniu od WHERE, HAVING działa na wynikach GROUP BY.
  • Zaznacz zapytanie nr 6 i naciśnij F5.
  • Powinieneś zobaczyć listę gatunków z liczbą zwrotów i średnim czasem wypożyczenia w dniach.

AvgDaysLoaned pokazuje NULL?

  • NULL pojawia się gdy DATEDIFF otrzymuje wartość NULL. Sprawdź czy w tabeli LoanHistory kolumna ReturnDate ma prawidłowe daty (nie NULL).
  • Upewnij się, że argumenty DATEDIFF są w kolejności: jednostka, data początkowa, data końcowa. DATEDIFF(DAY, lh.LoanDate, lh.ReturnDate) jest poprawne.

Pomóż mi zdiagnozować problem z AVG i DATEDIFF.

Napisz podzapytania

Podzapytania (subqueries) to zapytania zagnieżdżone wewnątrz innego zapytania. Pozwalają odpowiadać na pytania typu: kto wypożyczył więcej niż średnia, czyje wszystkie książki były wypożyczone.

  • Dodaj zapytanie znajdujące czytelników, którzy wypożyczyli więcej książek niż wynosi średnia:
-- 4. Czytelnicy którzy wypożyczyli więcej książek niż średnia (podzapytanie)
SELECT 
    m.FirstName + ' ' + m.LastName AS MemberName,
    COUNT(*) AS TotalLoans
FROM (
    SELECT MemberId FROM Loans
    UNION ALL
    SELECT MemberId FROM LoanHistory
) AS AllLoans
INNER JOIN Members m ON AllLoans.MemberId = m.MemberId
GROUP BY m.FirstName, m.LastName
HAVING COUNT(*) > (
    SELECT AVG(LoanCount) FROM (
        SELECT COUNT(*) AS LoanCount
        FROM (
            SELECT MemberId FROM Loans
            UNION ALL
            SELECT MemberId FROM LoanHistory
        ) AS Sub
        GROUP BY MemberId
    ) AS AvgSub
);
GO

Jak działa to zagnieżdżone podzapytanie?

To zapytanie ma trzy poziomy zagnieżdżenia. Najgłębszy poziom łączy Loans i LoanHistory przez UNION ALL. Środkowy poziom grupuje po MemberId i liczy wypożyczenia każdego czytelnika. Najwyższy poziom oblicza AVG z tych liczb.

Klauzula HAVING porównuje liczbę wypożyczeń każdego czytelnika z tą średnią. Tylko ci, którzy mają więcej, trafiają do wyniku.

  • Zaznacz zapytanie nr 4 i naciśnij F5.
  • Powinieneś zobaczyć czytelników, których łączna liczba wypożyczeń (aktywnych + historycznych) przekracza średnią.

Błąd składni w podzapytaniu?

  • Każde podzapytanie w nawiasach musi mieć alias (np. AS Sub, AS AvgSub). Brak aliasu powoduje błąd składni.
  • Policz nawiasy otwierające i zamykające. Trzy poziomy zagnieżdżenia wymagają dokładnego dopasowania nawiasów.

Pomóż mi naprawić podzapytanie.

  • Dodaj ostatnie zapytanie, które używa NOT EXISTS do znalezienia autorów, których WSZYSTKIE książki były wypożyczone przynajmniej raz:
-- 5. Autorzy których wszystkie książki były wypożyczone przynajmniej raz
SELECT 
    a.FirstName + ' ' + a.LastName AS AuthorName,
    COUNT(DISTINCT ba.BookId) AS TotalBooks
FROM Authors a
INNER JOIN BookAuthors ba ON a.AuthorId = ba.AuthorId
WHERE NOT EXISTS (
    SELECT 1 FROM BookAuthors ba2
    INNER JOIN Books b ON ba2.BookId = b.BookId
    WHERE ba2.AuthorId = a.AuthorId
    AND NOT EXISTS (
        SELECT 1 FROM Loans l WHERE l.BookId = ba2.BookId
        UNION
        SELECT 1 FROM LoanHistory lh WHERE lh.BookId = ba2.BookId
    )
)
GROUP BY a.FirstName, a.LastName;
GO

Jak działa podwójne NOT EXISTS?

To wzorzec logiczny zwany "relational division". Czytaj od wewnątrz: wewnętrzne NOT EXISTS sprawdza, czy książka NIE była wypożyczona. Zewnętrzne NOT EXISTS sprawdza, czy taki autor NIE MA żadnej takiej niewypożyczonej książki.

Podwójna negacja daje nam: autorzy, dla których nie istnieje książka, która nigdy nie była wypożyczona. Czyli autorzy, których KAŻDA książka została wypożyczona co najmniej raz.

  • Zaznacz zapytanie nr 5 i naciśnij F5.
  • Powinieneś zobaczyć listę autorów (np. Olga Tokarczuk, Isaac Asimov), których każda książka w bazie ma przynajmniej jedno wypożyczenie.

Wynik zawiera wszystkich autorów?

  • Sprawdź czy wewnętrzne podzapytanie używa UNION (nie UNION ALL). Tutaj UNION łączy wyniki z Loans i LoanHistory usuwając duplikaty.
  • Upewnij się, że warunek ba2.AuthorId = a.AuthorId koreluje podzapytanie z zapytaniem zewnętrznym. Bez tego podzapytanie sprawdza WSZYSTKICH autorów.

Pomóż mi zrozumieć logikę NOT EXISTS.

  • Zapisz plik naciskając Ctrl+S.
  • Nazwij go 03_ComplexQueries.sql i zapisz w tym samym folderze co poprzednie pliki.

✔️ Awesome, I've got everything!

Sprawdź czy plik jest zapisany i zawiera wszystkie 6 zapytań.

ⓧ I'd like to double check the full code

USE LibraryDB;
GO

-- 1. Lista aktywnych wypożyczeń z nazwami książek, autorów i czytelników (INNER JOIN + LEFT JOIN)
SELECT 
    l.LoanId,
    m.FirstName + ' ' + m.LastName AS MemberName,
    b.Title AS BookTitle,
    a.FirstName + ' ' + a.LastName AS AuthorName,
    l.LoanDate,
    l.DueDate,
    DATEDIFF(DAY, l.LoanDate, l.DueDate) AS DaysAllowed
FROM Loans l
INNER JOIN Members m ON l.MemberId = m.MemberId
INNER JOIN Books b ON l.BookId = b.BookId
INNER JOIN BookAuthors ba ON b.BookId = ba.BookId
INNER JOIN Authors a ON ba.AuthorId = a.AuthorId
ORDER BY l.LoanDate DESC;
GO

-- 2. Książki które nigdy nie były wypożyczone (LEFT JOIN + IS NULL)
SELECT 
    b.Title,
    b.ISBN,
    b.Genre
FROM Books b
LEFT JOIN Loans l ON b.BookId = l.BookId
LEFT JOIN LoanHistory lh ON b.BookId = lh.BookId
WHERE l.LoanId IS NULL AND lh.LoanHistoryId IS NULL;
GO

-- 3. TOP 5 najpopularniejszych książek (agregacja z UNION)
SELECT TOP 5
    b.Title,
    a.FirstName + ' ' + a.LastName AS AuthorName,
    COUNT(*) AS TotalLoans
FROM (
    SELECT BookId FROM Loans
    UNION ALL
    SELECT BookId FROM LoanHistory
) AS AllLoans
INNER JOIN Books b ON AllLoans.BookId = b.BookId
INNER JOIN BookAuthors ba ON b.BookId = ba.BookId
INNER JOIN Authors a ON ba.AuthorId = a.AuthorId
GROUP BY b.Title, a.FirstName, a.LastName
ORDER BY TotalLoans DESC;
GO

-- 4. Czytelnicy którzy wypożyczyli więcej książek niż średnia (podzapytanie)
SELECT 
    m.FirstName + ' ' + m.LastName AS MemberName,
    COUNT(*) AS TotalLoans
FROM (
    SELECT MemberId FROM Loans
    UNION ALL
    SELECT MemberId FROM LoanHistory
) AS AllLoans
INNER JOIN Members m ON AllLoans.MemberId = m.MemberId
GROUP BY m.FirstName, m.LastName
HAVING COUNT(*) > (
    SELECT AVG(LoanCount) FROM (
        SELECT COUNT(*) AS LoanCount
        FROM (
            SELECT MemberId FROM Loans
            UNION ALL
            SELECT MemberId FROM LoanHistory
        ) AS Sub
        GROUP BY MemberId
    ) AS AvgSub
);
GO

-- 5. Autorzy których wszystkie książki były wypożyczone przynajmniej raz
SELECT 
    a.FirstName + ' ' + a.LastName AS AuthorName,
    COUNT(DISTINCT ba.BookId) AS TotalBooks
FROM Authors a
INNER JOIN BookAuthors ba ON a.AuthorId = ba.AuthorId
WHERE NOT EXISTS (
    SELECT 1 FROM BookAuthors ba2
    INNER JOIN Books b ON ba2.BookId = b.BookId
    WHERE ba2.AuthorId = a.AuthorId
    AND NOT EXISTS (
        SELECT 1 FROM Loans l WHERE l.BookId = ba2.BookId
        UNION
        SELECT 1 FROM LoanHistory lh WHERE lh.BookId = ba2.BookId
    )
)
GROUP BY a.FirstName, a.LastName;
GO

-- 6. Średni czas wypożyczenia per gatunek
SELECT 
    b.Genre,
    COUNT(*) AS TotalReturns,
    AVG(DATEDIFF(DAY, lh.LoanDate, lh.ReturnDate)) AS AvgDaysLoaned
FROM LoanHistory lh
INNER JOIN Books b ON lh.BookId = b.BookId
GROUP BY b.Genre
HAVING COUNT(*) >= 1
ORDER BY AvgDaysLoaned DESC;
GO

Masz teraz zestaw złożonych zapytań demonstrujących JOINy, agregacje i podzapytania. W następnym kroku zbudujesz widoki i procedury składowane, które zamienią te zapytania w gotowe do użycia narzędzia biznesowe.

Utwórz Widoki i Procedury Składowane

W poprzednim kroku napisałeś złożone zapytania, które wyciągają cenne informacje z bazy danych.

Problem z zapytaniami ad hoc polega na tym, że musisz je uruchamiać ręcznie za każdym razem. Prawdziwa aplikacja potrzebuje gotowych widoków raportowych i procedur składowanych, które enkapsulują logikę biznesową. W tym kroku zbudujesz warstwę logiki, którą przyszłe API w C# będzie wywoływać bezpośrednio.

In this step, get ready to:
  • Utworzyć widoki raportowe pokazujące aktywne wypożyczenia i popularne książki.
  • Napisać procedurę składowaną do wypożyczania książek z walidacją.
  • Napisać procedurę składowaną do zwracania książek z przeniesieniem do historii.
Utwórz widoki raportowe

Widok w SQL to zapisane zapytanie, które działa jak wirtualna tabela. Zamiast pisać skomplikowany JOIN za każdym razem, wywołujesz widok jednym SELECT.

  • W SSMS utwórz nowe okno zapytania naciskając Ctrl+N.
  • Upewnij się, że w pasku narzędzi baza danych jest ustawiona na LibraryDB (lista rozwijana obok przycisku Execute).
  • Utwórz widok aktywnych wypożyczeń wklejając poniższy kod:
USE LibraryDB;
GO

-- Widok: aktywne wypożyczenia
CREATE OR ALTER VIEW vw_ActiveLoans AS
SELECT 
    l.LoanId,
    m.MemberId,
    m.FirstName + ' ' + m.LastName AS MemberName,
    m.Email,
    b.BookId,
    b.Title AS BookTitle,
    b.ISBN,
    l.LoanDate,
    l.DueDate,
    CASE 
        WHEN l.DueDate < GETDATE() THEN 'Przetrzymana'
        WHEN DATEDIFF(DAY, GETDATE(), l.DueDate) <= 3 THEN 'Do zwrotu wkrotce'
        ELSE 'W terminie'
    END AS LoanStatus
FROM Loans l
INNER JOIN Members m ON l.MemberId = m.MemberId
INNER JOIN Books b ON l.BookId = b.BookId;
GO

Co robi ten widok?

  • Łączy tabele Loans, Members i Books w jedną wirtualną tabelę.
  • Kolumna LoanStatus automatycznie oblicza status: 'Przetrzymana' gdy termin minął, 'Do zwrotu wkrotce' gdy zostały 3 dni lub mniej, albo 'W terminie'.
  • CREATE OR ALTER pozwala bezpiecznie uruchamiać skrypt wielokrotnie bez konieczności usuwania widoku.
  • Zaznacz cały kod i wykonaj go naciskając F5.
  • Zweryfikuj widok uruchamiając:
SELECT * FROM vw_ActiveLoans;

Powinieneś zobaczyć listę aktywnych wypożyczeń z kolumnami MemberName, BookTitle, LoanStatus i innymi.

Widzisz błąd 'Invalid object name'?

Sprawdź, że w pasku narzędzi SSMS wybrana jest baza LibraryDB, a nie master.

Upewnij się, że wykonałeś USE LibraryDB; GO na początku skryptu.

Pomocy z widokami w T-SQL

  • Teraz dodaj drugi widok pokazujący popularność książek. Wklej poniższy kod poniżej poprzedniego w tym samym oknie zapytania:
-- Widok: popularne książki
CREATE OR ALTER VIEW vw_PopularBooks AS
SELECT 
    b.BookId,
    b.Title,
    b.Genre,
    b.TotalCopies,
    b.AvailableCopies,
    ISNULL(stats.TotalLoans, 0) AS TotalLoans
FROM Books b
LEFT JOIN (
    SELECT BookId, COUNT(*) AS TotalLoans
    FROM (
        SELECT BookId FROM Loans
        UNION ALL
        SELECT BookId FROM LoanHistory
    ) AS AllLoans
    GROUP BY BookId
) AS stats ON b.BookId = stats.BookId;
GO

Co robi ten widok?

  • Łączy aktywne wypożyczenia z tabeli Loans i historyczne z LoanHistory za pomocą UNION ALL, żeby policzyć łączną liczbę wypożyczeń każdej książki.
  • ISNULL(stats.TotalLoans, 0) zamienia NULL na 0 dla książek, które nigdy nie były wypożyczone.
  • LEFT JOIN gwarantuje, że widok pokaże wszystkie książki, nawet te bez wypożyczeń.
  • Zaznacz kod widoku vw_PopularBooks i wykonaj go naciskając F5.
  • Zweryfikuj widok uruchamiając:
SELECT * FROM vw_PopularBooks ORDER BY TotalLoans DESC;

Powinieneś zobaczyć 15 książek posortowanych od najpopularniejszej, z kolumną TotalLoans pokazującą liczbę wypożyczeń.

TotalLoans pokazuje 0 dla wszystkich książek?

Upewnij się, że w kroku 2 poprawnie wykonałeś plik 02_InsertSampleData.sql z danymi wypożyczeń w tabelach Loans i LoanHistory.

Sprawdź dane uruchamiając SELECT COUNT(*) FROM Loans; i SELECT COUNT(*) FROM LoanHistory; Obie tabele powinny mieć rekordy.

Pomocy z widokiem vw_PopularBooks

Utwórz procedurę wypożyczenia książki

Procedura składowana to nazwany blok kodu T-SQL zapisany na serwerze. Aplikacja wywołuje procedurę zamiast budować zapytania ręcznie, co zwiększa bezpieczeństwo i wydajność.

Dlaczego procedury zamiast bezpośrednich INSERT?

Procedura usp_BorrowBook enkapsuluje 4 walidacje i 2 operacje w jednym atomowym wywołaniu. Gdyby API wykonywało te kroki osobno, błąd w połowie mógłby zostawić bazę w niespójnym stanie (np. książka wypożyczona, ale licznik kopii niezmieniony).

Transakcja wewnątrz procedury gwarantuje, że albo wszystkie operacje się wykonają, albo żadna.

  • Dodaj procedurę wypożyczenia poniżej widoków w tym samym pliku:
-- Procedura: wypożycz książkę
CREATE OR ALTER PROCEDURE usp_BorrowBook
    @MemberId INT,
    @BookId INT
AS
BEGIN
    SET NOCOUNT ON;

    -- Sprawdź czy czytelnik istnieje i jest aktywny
    IF NOT EXISTS (SELECT 1 FROM Members WHERE MemberId = @MemberId AND IsActive = 1)
    BEGIN
        RAISERROR('Czytelnik nie istnieje lub jest nieaktywny.', 16, 1);
        RETURN;
    END

    -- Sprawdź czy książka istnieje
    IF NOT EXISTS (SELECT 1 FROM Books WHERE BookId = @BookId)
    BEGIN
        RAISERROR('Książka nie istnieje.', 16, 1);
        RETURN;
    END

    -- Sprawdź dostępność kopii
    IF (SELECT AvailableCopies FROM Books WHERE BookId = @BookId) <= 0
    BEGIN
        RAISERROR('Brak dostępnych kopii tej książki.', 16, 1);
        RETURN;
    END

    -- Sprawdź limit wypożyczeń (max 3)
    DECLARE @ActiveLoans INT;
    SELECT @ActiveLoans = COUNT(*) FROM Loans WHERE MemberId = @MemberId;

    IF @ActiveLoans >= 3
    BEGIN
        RAISERROR('Czytelnik osiągnął limit 3 aktywnych wypożyczeń.', 16, 1);
        RETURN;
    END

    -- Wypożycz książkę
    BEGIN TRANSACTION;
    BEGIN TRY
        INSERT INTO Loans (BookId, MemberId, LoanDate, DueDate)
        VALUES (@BookId, @MemberId, GETDATE(), DATEADD(DAY, 30, GETDATE()));

        UPDATE Books 
        SET AvailableCopies = AvailableCopies - 1 
        WHERE BookId = @BookId;

        COMMIT TRANSACTION;
        PRINT 'Książka wypożyczona pomyślnie.';
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END;
GO

Jak działa ta procedura?

Procedura przechodzi przez 4 walidacje zanim wykona operację:

  • Czy czytelnik istnieje i ma IsActive = 1.
  • Czy książka istnieje w bazie.
  • Czy AvailableCopies > 0 (są wolne egzemplarze).
  • Czy czytelnik nie ma już 3 aktywnych wypożyczeń.

Jeśli wszystko przejdzie, BEGIN TRANSACTION otwiera transakcję. INSERT tworzy rekord wypożyczenia, UPDATE zmniejsza licznik kopii. Jeśli cokolwiek zawiedzie, ROLLBACK cofa obie operacje.

  • Zaznacz kod procedury usp_BorrowBook i wykonaj go naciskając F5.
  • Przetestuj procedurę wywołując ją z prawidłowymi danymi:
EXEC usp_BorrowBook @MemberId = 2, @BookId = 8;

W zakładce Messages powinieneś zobaczyć komunikat: Książka wypożyczona pomyślnie..

Widzisz błąd 'Czytelnik osiągnął limit'?

Oznacza to, że wybrany czytelnik ma już 3 aktywne wypożyczenia. Sprawdź aktywne wypożyczenia czytelnika uruchamiając SELECT * FROM Loans WHERE MemberId = 2; i wybierz innego czytelnika lub inny MemberId.

Pomocy z procedurą usp_BorrowBook

Utwórz procedurę zwrotu książki

Procedura zwrotu musi przenieść rekord z tabeli aktywnych wypożyczeń do historii, usunąć go z Loans i przywrócić dostępną kopię.

  • Dodaj procedurę zwrotu poniżej usp_BorrowBook w tym samym pliku:
-- Procedura: zwróć książkę
CREATE OR ALTER PROCEDURE usp_ReturnBook
    @LoanId INT
AS
BEGIN
    SET NOCOUNT ON;

    -- Sprawdź czy wypożyczenie istnieje
    IF NOT EXISTS (SELECT 1 FROM Loans WHERE LoanId = @LoanId)
    BEGIN
        RAISERROR('Wypożyczenie o podanym ID nie istnieje.', 16, 1);
        RETURN;
    END

    DECLARE @BookId INT, @MemberId INT, @LoanDate DATE, @DueDate DATE;

    SELECT @BookId = BookId, @MemberId = MemberId, @LoanDate = LoanDate, @DueDate = DueDate
    FROM Loans WHERE LoanId = @LoanId;

    BEGIN TRANSACTION;
    BEGIN TRY
        -- Przenieś do historii
        INSERT INTO LoanHistory (BookId, MemberId, LoanDate, DueDate, ReturnDate)
        VALUES (@BookId, @MemberId, @LoanDate, @DueDate, GETDATE());

        -- Usuń z aktywnych wypożyczeń
        DELETE FROM Loans WHERE LoanId = @LoanId;

        -- Zwiększ dostępne kopie
        UPDATE Books 
        SET AvailableCopies = AvailableCopies + 1 
        WHERE BookId = @BookId;

        COMMIT TRANSACTION;
        PRINT 'Książka zwrócona pomyślnie.';
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END;
GO

Co robi ta procedura krok po kroku?

  • Sprawdza czy @LoanId istnieje w tabeli Loans.
  • Zapisuje dane wypożyczenia do zmiennych lokalnych.
  • W transakcji: wstawia rekord do LoanHistory, usuwa z Loans, zwiększa AvailableCopies o 1.
  • Jeśli którakolwiek operacja zawiedzie, ROLLBACK cofa wszystkie zmiany w ramach transakcji.
  • Zaznacz kod procedury usp_ReturnBook i wykonaj go naciskając F5.
  • Sprawdź ID aktywnego wypożyczenia uruchamiając:
SELECT LoanId, BookId, MemberId FROM Loans;
  • Przetestuj zwrot, używając jednego z wyświetlonych LoanId:
EXEC usp_ReturnBook @LoanId = 1;

Powinieneś zobaczyć komunikat: Książka zwrócona pomyślnie..

  • Zweryfikuj, że zwrot zadziałał prawidłowo uruchamiając:
-- Sprawdź czy rekord zniknął z aktywnych wypożyczeń
SELECT * FROM Loans WHERE LoanId = 1;

-- Sprawdź czy pojawił się w historii
SELECT TOP 1 * FROM LoanHistory ORDER BY LoanHistoryId DESC;

Pierwsze zapytanie powinno zwrócić 0 wierszy. Drugie powinno pokazać zwróconą właśnie książkę z dzisiejszą datą w kolumnie ReturnDate.

Widzisz błąd 'Wypożyczenie o podanym ID nie istnieje'?

Użyłeś LoanId który nie istnieje w tabeli Loans. Uruchom SELECT LoanId FROM Loans; żeby zobaczyć dostępne ID i użyj jednego z nich.

Pomocy ze zwrotem książki

  • Zapisz plik jako 04_ViewsAndProcedures.sql naciskając Ctrl+S.

✔️ Awesome, I've got everything!

Sprawdź, że plik jest zapisany. Masz teraz 2 widoki i 2 procedury składowane gotowe do użycia.

ⓧ I'd like to double check the full code

USE LibraryDB;
GO

-- Widok: aktywne wypożyczenia
CREATE OR ALTER VIEW vw_ActiveLoans AS
SELECT 
    l.LoanId,
    m.MemberId,
    m.FirstName + ' ' + m.LastName AS MemberName,
    m.Email,
    b.BookId,
    b.Title AS BookTitle,
    b.ISBN,
    l.LoanDate,
    l.DueDate,
    CASE 
        WHEN l.DueDate < GETDATE() THEN 'Przetrzymana'
        WHEN DATEDIFF(DAY, GETDATE(), l.DueDate) <= 3 THEN 'Do zwrotu wkrotce'
        ELSE 'W terminie'
    END AS LoanStatus
FROM Loans l
INNER JOIN Members m ON l.MemberId = m.MemberId
INNER JOIN Books b ON l.BookId = b.BookId;
GO

-- Widok: popularne książki
CREATE OR ALTER VIEW vw_PopularBooks AS
SELECT 
    b.BookId,
    b.Title,
    b.Genre,
    b.TotalCopies,
    b.AvailableCopies,
    ISNULL(stats.TotalLoans, 0) AS TotalLoans
FROM Books b
LEFT JOIN (
    SELECT BookId, COUNT(*) AS TotalLoans
    FROM (
        SELECT BookId FROM Loans
        UNION ALL
        SELECT BookId FROM LoanHistory
    ) AS AllLoans
    GROUP BY BookId
) AS stats ON b.BookId = stats.BookId;
GO

-- Procedura: wypożycz książkę
CREATE OR ALTER PROCEDURE usp_BorrowBook
    @MemberId INT,
    @BookId INT
AS
BEGIN
    SET NOCOUNT ON;

    -- Sprawdź czy czytelnik istnieje i jest aktywny
    IF NOT EXISTS (SELECT 1 FROM Members WHERE MemberId = @MemberId AND IsActive = 1)
    BEGIN
        RAISERROR('Czytelnik nie istnieje lub jest nieaktywny.', 16, 1);
        RETURN;
    END

    -- Sprawdź czy książka istnieje
    IF NOT EXISTS (SELECT 1 FROM Books WHERE BookId = @BookId)
    BEGIN
        RAISERROR('Książka nie istnieje.', 16, 1);
        RETURN;
    END

    -- Sprawdź dostępność kopii
    IF (SELECT AvailableCopies FROM Books WHERE BookId = @BookId) <= 0
    BEGIN
        RAISERROR('Brak dostępnych kopii tej książki.', 16, 1);
        RETURN;
    END

    -- Sprawdź limit wypożyczeń (max 3)
    DECLARE @ActiveLoans INT;
    SELECT @ActiveLoans = COUNT(*) FROM Loans WHERE MemberId = @MemberId;

    IF @ActiveLoans >= 3
    BEGIN
        RAISERROR('Czytelnik osiągnął limit 3 aktywnych wypożyczeń.', 16, 1);
        RETURN;
    END

    -- Wypożycz książkę
    BEGIN TRANSACTION;
    BEGIN TRY
        INSERT INTO Loans (BookId, MemberId, LoanDate, DueDate)
        VALUES (@BookId, @MemberId, GETDATE(), DATEADD(DAY, 30, GETDATE()));

        UPDATE Books 
        SET AvailableCopies = AvailableCopies - 1 
        WHERE BookId = @BookId;

        COMMIT TRANSACTION;
        PRINT 'Książka wypożyczona pomyślnie.';
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END;
GO

-- Procedura: zwróć książkę
CREATE OR ALTER PROCEDURE usp_ReturnBook
    @LoanId INT
AS
BEGIN
    SET NOCOUNT ON;

    -- Sprawdź czy wypożyczenie istnieje
    IF NOT EXISTS (SELECT 1 FROM Loans WHERE LoanId = @LoanId)
    BEGIN
        RAISERROR('Wypożyczenie o podanym ID nie istnieje.', 16, 1);
        RETURN;
    END

    DECLARE @BookId INT, @MemberId INT, @LoanDate DATE, @DueDate DATE;

    SELECT @BookId = BookId, @MemberId = MemberId, @LoanDate = LoanDate, @DueDate = DueDate
    FROM Loans WHERE LoanId = @LoanId;

    BEGIN TRANSACTION;
    BEGIN TRY
        -- Przenieś do historii
        INSERT INTO LoanHistory (BookId, MemberId, LoanDate, DueDate, ReturnDate)
        VALUES (@BookId, @MemberId, @LoanDate, @DueDate, GETDATE());

        -- Usuń z aktywnych wypożyczeń
        DELETE FROM Loans WHERE LoanId = @LoanId;

        -- Zwiększ dostępne kopie
        UPDATE Books 
        SET AvailableCopies = AvailableCopies + 1 
        WHERE BookId = @BookId;

        COMMIT TRANSACTION;
        PRINT 'Książka zwrócona pomyślnie.';
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END;
GO

Masz teraz kompletną warstwę logiki biznesowej. Widoki dostarczają gotowe raporty, a procedury obsługują operacje wypożyczania i zwrotów. W następnym kroku dodasz trigger, który będzie pilnował limitu wypożyczeń nawet przy bezpośrednich INSERT do tabeli, oraz indeksy przyspieszające zapytania.

Dodaj Trigger i Indeksy

W poprzednim kroku stworzyłeś widoki i procedury składowane, które implementują logikę biznesową biblioteki. Procedura usp_BorrowBook sprawdza limit wypożyczeń, ale co jeśli ktoś obejdzie procedurę i wstawi rekord bezpośrednio do tabeli?

Potrzebujesz drugiej linii obrony na poziomie samej tabeli. W tym kroku dodasz trigger, który zablokuje takie próby, oraz indeksy, które przyspieszą najczęściej wykonywane zapytania.

In this step, get ready to:
  • Utworzyć trigger INSTEAD OF INSERT blokujący przekroczenie limitu wypożyczeń.
  • Dodać indeksy nieklastrowane na kolumnach używanych w JOIN i WHERE.
  • Przetestować trigger i zmierzyć wpływ indeksów na wydajność.
Utwórz trigger blokujący przekroczenie limitu

Procedura usp_BorrowBook już sprawdza limit 3 aktywnych wypożyczeń. Trigger trg_CheckLoanLimit to dodatkowe zabezpieczenie na poziomie tabeli, które działa nawet przy bezpośrednim INSERT.

  • W SSMS utwórz nowy plik zapytania (Ctrl+N).
  • Wpisz początek pliku z deklaracją bazy, a następnie kod triggera:
USE LibraryDB;
GO

-- Trigger: blokuj wypożyczenie jeśli czytelnik ma 3 aktywne
CREATE OR ALTER TRIGGER trg_CheckLoanLimit
ON Loans
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS (
        SELECT i.MemberId
        FROM inserted i
        INNER JOIN Loans l ON i.MemberId = l.MemberId
        GROUP BY i.MemberId
        HAVING COUNT(*) >= 3
    )
    BEGIN
        RAISERROR('Nie można wypożyczyć - czytelnik osiągnął limit 3 aktywnych wypożyczeń.', 16, 1);
        RETURN;
    END

    -- Jeśli walidacja przeszła, wstaw rekordy
    INSERT INTO Loans (BookId, MemberId, LoanDate, DueDate)
    SELECT BookId, MemberId, LoanDate, DueDate
    FROM inserted;
END;
GO

Co robi ten trigger?

INSTEAD OF INSERT przechwytuje każdy INSERT do tabeli Loans zanim dane trafią do bazy. Trigger "zastępuje" standardowe działanie INSERT własną logiką.

  • Pseudotabela inserted zawiera wiersze, które ktoś próbuje wstawić.
  • Trigger łączy inserted z istniejącymi wypożyczeniami i sprawdza, czy czytelnik ma już 3 aktywne.
  • Jeśli limit nie jest przekroczony, trigger sam wykonuje INSERT z danych z inserted.

Dlaczego INSTEAD OF, a nie AFTER?

Trigger AFTER INSERT uruchamia się po wstawieniu danych. Musiałbyś wtedy usuwać nieprawidłowy rekord i cofać transakcję. INSTEAD OF pozwala w ogóle nie wstawiać danych, co jest czystszym rozwiązaniem dla walidacji.

  • Zaznacz cały kod i wykonaj go naciskając F5.
  • W zakładce Messages powinieneś zobaczyć komunikat potwierdzający wykonanie polecenia bez błędów.

Widzisz błąd przy tworzeniu triggera?

  • Upewnij się, że na górze pliku masz USE LibraryDB; i GO. Bez tego SSMS może próbować utworzyć trigger w złej bazie.
  • Sprawdź, czy tabela Loans istnieje. W Object Explorer rozwiń LibraryDB > Tables i potwierdź, że widzisz dbo.Loans.

Dlaczego mój trigger nie działa prawidłowo?

Dodaj indeksy nieklastrowane

Indeksy nieklastrowane przyspieszają wyszukiwanie na kolumnach, po których najczęściej filtrujesz lub łączysz tabele. Bez nich SQL Server musi skanować całą tabelę wiersz po wierszu.

  • Dopisz poniższy kod na końcu pliku 05_TriggerAndIndexes.sql, poniżej triggera:
-- Indeksy nieklastrowane
CREATE NONCLUSTERED INDEX IX_Books_ISBN 
ON Books (ISBN);
GO

CREATE NONCLUSTERED INDEX IX_Loans_MemberId 
ON Loans (MemberId) 
INCLUDE (BookId, LoanDate, DueDate);
GO

CREATE NONCLUSTERED INDEX IX_Loans_BookId 
ON Loans (BookId) 
INCLUDE (MemberId, LoanDate);
GO

CREATE NONCLUSTERED INDEX IX_LoanHistory_MemberId 
ON LoanHistory (MemberId) 
INCLUDE (BookId, LoanDate, ReturnDate);
GO

CREATE NONCLUSTERED INDEX IX_LoanHistory_BookId 
ON LoanHistory (BookId) 
INCLUDE (MemberId, LoanDate, ReturnDate);
GO

Czym jest INCLUDE w indeksie?

Klauzula INCLUDE dodaje do indeksu dodatkowe kolumny na poziomie liści. SQL Server może odczytać te kolumny bezpośrednio z indeksu, bez odwoływania się do głównej tabeli.

  • IX_Books_ISBN przyspiesza wyszukiwanie po ISBN (używane w klauzuli WHERE).
  • IX_Loans_MemberId przyspiesza JOIN na MemberId i od razu zwraca BookId, LoanDate, DueDate bez dodatkowego odczytu.
  • Indeksy na LoanHistory przyspieszają zapytania raportowe, które łączą historię z innymi tabelami.
  • Zaznacz nowo dopisany kod indeksów i wykonaj go naciskając F5.
  • W zakładce Messages powinieneś zobaczyć 5 komunikatów potwierdzających utworzenie indeksów.

Widzisz błąd przy tworzeniu indeksu?

  • Jeśli widzisz komunikat o istniejącym indeksie, oznacza to, że uruchomiłeś kod drugi raz. Możesz go zignorować lub najpierw usunąć indeks komendą DROP INDEX.
  • Sprawdź, czy nazwy kolumn w kodzie dokładnie odpowiadają nazwom w definicjach tabel (wielkie i małe litery mają znaczenie w niektórych konfiguracjach).

Pomóż mi naprawić błąd przy tworzeniu indeksu nieklastrowanego.

Przetestuj trigger i zmierz wydajność

Czas sprawdzić, czy trigger działa poprawnie i czy indeksy rzeczywiście przyspieszają zapytania. Czytelnik Jan Kowalski (MemberId = 1) ma aktualnie 2 aktywne wypożyczenia. Spróbujesz dodać trzecie (powinno się udać) i czwarte (powinno zostać zablokowane).

  • Dopisz poniższy kod na końcu pliku 05_TriggerAndIndexes.sql i wykonaj go:
-- Testuj trigger: spróbuj wypożyczyć 4-tą książkę dla czytelnika z 3 aktywnymi
-- Najpierw dodaj 3-cie wypożyczenie dla MemberId = 1 (ma już 2)
EXEC usp_BorrowBook @MemberId = 1, @BookId = 9;
GO

-- Teraz próba 4-tego powinna się nie udać
EXEC usp_BorrowBook @MemberId = 1, @BookId = 7;
GO

Co powinno się wydarzyć?

  • Pierwsze wywołanie (BookId = 9, Fundacja) powinno wyświetlić: "Książka wypożyczona pomyślnie." Jan ma teraz 3 aktywne wypożyczenia.
  • Drugie wywołanie (BookId = 7, Rok 1984) powinno zwrócić błąd o przekroczeniu limitu. Procedura usp_BorrowBook sprawdza limit przed INSERT, więc to jej walidacja blokuje operację.
  • Gdyby ktoś pominął procedurę i wstawił INSERT bezpośrednio, trigger trg_CheckLoanLimit stanowiłby ostatnią linię obrony.
  • Teraz dopisz kod testujący wydajność z włączonymi statystykami IO:
-- Test wydajności: włącz statystyki IO
SET STATISTICS IO ON;
GO

SELECT * FROM vw_ActiveLoans WHERE MemberName LIKE 'Jan%';
GO

SELECT * FROM vw_PopularBooks WHERE TotalLoans > 1 ORDER BY TotalLoans DESC;
GO

SET STATISTICS IO OFF;
GO

Jak czytać statystyki IO?

SET STATISTICS IO ON wyświetla w zakładce Messages liczbę odczytów logicznych (logical reads) dla każdej tabeli. Im mniej odczytów, tym szybciej działa zapytanie.

Indeksy zmniejszają liczbę logical reads, bo SQL Server odczytuje tylko potrzebne strony danych zamiast skanować całą tabelę. Przy małej ilości danych testowych różnica może być niewielka, ale przy tysiącach wierszy byłaby bardzo wyraźna.

  • Zapisz plik jako 05_TriggerAndIndexes.sql naciskając Ctrl+S.
  • W zakładce Messages powinieneś widzieć komunikat o pomyślnym wypożyczeniu (pierwsza próba), błąd o limicie (druga próba), oraz statystyki logical reads dla obu zapytań na widokach.

Oba wywołania usp_BorrowBook zwracają błąd?

  • Jeśli pierwsze wywołanie też zwraca błąd limitu, to Jan prawdopodobnie ma już 3 wypożyczenia z wcześniejszych testów. Sprawdź ile ma aktywnych wypożyczeń zapytaniem: SELECT COUNT(*) FROM Loans WHERE MemberId = 1;.
  • Jeśli wynik to 3 lub więcej, zwróć jedną książkę komendą EXEC usp_ReturnBook @LoanId = <numer>; i spróbuj ponownie.

Moje testy triggera nie działają zgodnie z oczekiwaniami. Pomóż mi to naprawić.

✔️ Awesome, I've got everything!

Upewnij się, że plik 05_TriggerAndIndexes.sql jest zapisany. Cały kod (trigger, indeksy, testy) powinien być w jednym pliku.

ⓧ I'd like to double check the full code

Oto pełny plik 05_TriggerAndIndexes.sql do porównania:

USE LibraryDB;
GO

-- Trigger: blokuj wypożyczenie jeśli czytelnik ma 3 aktywne
CREATE OR ALTER TRIGGER trg_CheckLoanLimit
ON Loans
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS (
        SELECT i.MemberId
        FROM inserted i
        INNER JOIN Loans l ON i.MemberId = l.MemberId
        GROUP BY i.MemberId
        HAVING COUNT(*) >= 3
    )
    BEGIN
        RAISERROR('Nie można wypożyczyć - czytelnik osiągnął limit 3 aktywnych wypożyczeń.', 16, 1);
        RETURN;
    END

    -- Jeśli walidacja przeszła, wstaw rekordy
    INSERT INTO Loans (BookId, MemberId, LoanDate, DueDate)
    SELECT BookId, MemberId, LoanDate, DueDate
    FROM inserted;
END;
GO

-- Indeksy nieklastrowane
CREATE NONCLUSTERED INDEX IX_Books_ISBN 
ON Books (ISBN);
GO

CREATE NONCLUSTERED INDEX IX_Loans_MemberId 
ON Loans (MemberId) 
INCLUDE (BookId, LoanDate, DueDate);
GO

CREATE NONCLUSTERED INDEX IX_Loans_BookId 
ON Loans (BookId) 
INCLUDE (MemberId, LoanDate);
GO

CREATE NONCLUSTERED INDEX IX_LoanHistory_MemberId 
ON LoanHistory (MemberId) 
INCLUDE (BookId, LoanDate, ReturnDate);
GO

CREATE NONCLUSTERED INDEX IX_LoanHistory_BookId 
ON LoanHistory (BookId) 
INCLUDE (MemberId, LoanDate, ReturnDate);
GO

-- Testuj trigger: spróbuj wypożyczyć 4-tą książkę dla czytelnika z 3 aktywnymi
-- Najpierw dodaj 3-cie wypożyczenie dla MemberId = 1 (ma już 2)
EXEC usp_BorrowBook @MemberId = 1, @BookId = 9;
GO

-- Teraz próba 4-tego powinna się nie udać
EXEC usp_BorrowBook @MemberId = 1, @BookId = 7;
GO

-- Test wydajności: włącz statystyki IO
SET STATISTICS IO ON;
GO

SELECT * FROM vw_ActiveLoans WHERE MemberName LIKE 'Jan%';
GO

SELECT * FROM vw_PopularBooks WHERE TotalLoans > 1 ORDER BY TotalLoans DESC;
GO

SET STATISTICS IO OFF;
GO

Twoja baza danych jest kompletna. Masz tabele, relacje, zapytania, procedury, widoki, trigger i indeksy. Następny krok to Secret Mission, gdzie dodasz zaawansowany raport z funkcjami okna.

Secret mission

Dodaj zaawansowany raport z funkcjami okna (Window Functions)

Twoja baza danych jest kompletna, ale biblioteka potrzebuje jeszcze jednego raportu. Kierownik chce widzieć miesięczny ranking najpopularniejszych książek, narastające sumy wypożyczeń per czytelnik i porównanie miesiąc do miesiąca. Do tego celu użyjesz Window Functions, jednej z najpotężniejszych funkcji T-SQL.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely on your local machine. SQL Server Express runs as a Windows service that starts automatically with your computer, using a small amount of memory even when idle. Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Resources you used:

  • LibraryDB database on SQL Server 2022 Express (local instance localhost\SQLEXPRESS).
  • SQL Server 2022 Express (Windows service running in the background).
  • SQL Server Management Studio 22 (SSMS).
  • Your .sql script files (01_CreateDatabase.sql through 06_MonthlyReport.sql).

Keep everything running

No action needed. Choose this if you plan to continue building on this database (for example, connecting it to a C# REST API in a follow-up project).

  • SQL Server Express continues running as a background service. It uses minimal resources when idle.
  • Your LibraryDB database and all objects (tables, procedures, views, triggers, indexes) remain intact.
  • Your .sql files stay wherever you saved them.

Pause - I'll come back to this later

Stop the SQL Server service to free up memory. Your database and files stay on disk, ready to resume when you return.

  • Open Command Prompt as Administrator.
  • Stop the SQL Server Express service by running:
net stop MSSQL$SQLEXPRESS
  • When you want to resume, start the service again by running:
net start MSSQL$SQLEXPRESS
  • Your LibraryDB database and all .sql files remain on disk untouched.

Delete - I don't want to use this again

Remove all project resources and start fresh. Follow these steps in order.

  • Open SSMS and connect to localhost\SQLEXPRESS.
  • Drop the database by running this query in a new query window:
USE master;
GO
DROP DATABASE LibraryDB;
GO
  • Delete your .sql script files (01_CreateDatabase.sql through 06_MonthlyReport.sql) from wherever you saved them.
  • To uninstall SQL Server Management Studio 22, open Settings > Apps > Installed apps.
  • Search for SQL Server Management Studio, select it, and click Uninstall.
  • To uninstall SQL Server 2022 Express, search for sql in the same Installed apps list.
  • Select Microsoft SQL Server 2022 and click Uninstall.
  • In the SQL Server Setup wizard that appears, choose your SQLEXPRESS instance on the Select Instance page and select all features to remove.
  • Click Remove on the Ready to Remove page.
  • After removal completes, check Installed apps again for any remaining SQL Server components and uninstall them.

Nice Work!

Nice Work!

Nice work! You just designed and built a complete relational database for a library management system using T-SQL.

You've learned how to:

  • Design a normalized database schema with 6 tables, primary and foreign keys, CHECK constraints, and many-to-many relationships.
  • Write complex queries using JOINs, subqueries, and aggregations to extract meaningful insights from relational data.
  • Implement business logic with stored procedures, views, triggers, and indexes that enforce rules and optimize performance.
  • Build an advanced monthly report using window functions (RANK, SUM OVER, LAG) to generate rankings, running totals, and month-over-month comparisons.

Ready to quiz yourself?