Zum Inhalt springen
aviral gupta
Kontakt aufnehmen

// B3.6 · ca. 30 Min. · Einstieg

Projekt: ein Ausleihbericht

Nach diesem Projekt schreiben Sie einen Bericht, der jedes Mitglied behält, zählt, was es ausgeliehen hat und noch hat, und dessen Summen Sie mit den Daten prüfen.

Lektion 6 von 6 in B3 Joins und Aggregate

Ende des Moduls

Danach können Sie

  • Jedes Mitglied mit LEFT JOIN im Bericht behalten und seine Ausleihen mit count(l.id) zählen
  • Offene Ausleihen mit FILTER zählen und das letzte Ausleihdatum je Mitglied mit max finden
  • Je Genre berichten, nie ausgeliehene Bücher per Anti-Join finden und die Summen prüfen

  1. Aufwärmen · Aufgabe 1 von 7

    Aufwärmen: Der Bericht soll jedes Mitglied zeigen, auch die, die nie ein Buch ausgeliehen haben. Wie sollte sein FROM-Teil beginnen?

  2. Vorhersagen · Aufgabe 2 von 7

    Sagen Sie es voraus, bevor Sie weiterlesen. Margaret hat nie ein Buch ausgeliehen. Was zeigt die Spalte loans in ihrer Zeile?

    SELECT m.name, count(*) AS loans
FROM members m
LEFT JOIN loans l ON l.member_id = m.id
GROUP BY m.id, m.name;

  3. Üben · Aufgabe 3 von 7

    Ergänzen Sie das Argument von count, damit Margarets Zeile 0 Ausleihen zeigt.

    SELECT m.name, count(____) AS loans
FROM members m
LEFT JOIN loans l ON l.member_id = m.id
GROUP BY m.id, m.name
ORDER BY m.name;
    SELECT m.name, count() AS loans FROM members m LEFT JOIN loans l ON l.member_id = m.id GROUP BY m.id, m.name ORDER BY m.name;

  4. Üben · Aufgabe 4 von 7

    Ordnen Sie jeder Spalte des Berichts den Ausdruck zu, der sie berechnet.

  5. Üben · Aufgabe 5 von 7

    Bringen Sie die Zeilen des Berichts je Mitglied in die Reihenfolge, die SQL verlangt.

    1. 1.ORDER BY m.name;
    2. 2.GROUP BY m.id, m.name
    3. 3.FROM members m
    4. 4.SELECT m.name, count(l.id) AS loans
    5. 5.LEFT JOIN loans l ON l.member_id = m.id

  6. Denksport · Aufgabe 6 von 7

    Knobelaufgabe. Margaret hat überhaupt keine Ausleihen. Was zeigt die Spalte open in ihrer Zeile?

    SELECT m.name,
       count(*) FILTER (WHERE l.returned_on IS NULL) AS open
FROM members m
LEFT JOIN loans l ON l.member_id = m.id
GROUP BY m.id, m.name;

  7. Anwenden · Aufgabe 7 von 7

    Mini-Aufgabe. Schreiben Sie den Bericht je Stadt: für jede Stadt die Zahl der Mitglieder, ihre Ausleihen und ihre offenen Ausleihen, wobei jedes Mitglied zählt, auch Margaret, deren Stadt NULL ist (zeigen Sie sie als unknown). Sagen Sie zuerst voraus, was in der Zeile Berlin steht, und prüfen Sie dann, dass die Spalte der Ausleihen 5 ergibt.

    Prüfen Sie Ihr Ergebnis anhand dieser Liste

Selbst programmieren

Lesen Sie das ausgearbeitete Beispiel und lösen Sie dann die Übungen. Ihr Code läuft in Ihrem Browser oder auf Ihrem Computer und wird nie hochgeladen.

Ausgearbeitetes Beispiel

Ein Bericht je Buch und seine Probe

setup.sql lädt die Bibliothek aus Modul B1: Bücher, Mitglieder und Ausleihen. main.sql baut den Bericht je Buch: wie oft jedes Buch ausgeliehen wurde, wie viele Exemplare noch unterwegs sind und das letzte Ausleihdatum. Danach listet es die Bücher, die niemand ausgeliehen hat, und die Summen, an denen Sie den Bericht prüfen. Auf Ihrem Computer: psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql.

main.sql

-- 1. Every book, lent or not: times lent, still out, last loan.
SELECT b.title,
       count(l.id) AS times_lent,
       count(l.id) FILTER (WHERE l.returned_on IS NULL) AS still_out,
       max(l.loaned_on) AS last_loan
FROM books b
LEFT JOIN loans l ON l.book_id = b.id
GROUP BY b.id, b.title
ORDER BY b.id;

-- 2. The books nobody has borrowed yet.
SELECT b.title
FROM books b
LEFT JOIN loans l ON l.book_id = b.id
WHERE l.id IS NULL
ORDER BY b.title;

-- 3. The totals, to check the report against.
SELECT count(*) AS loans,
       count(*) FILTER (WHERE returned_on IS NULL) AS open,
       count(DISTINCT member_id) AS borrowers
FROM loans;

setup.sql

CREATE TABLE books (
  id integer,
  title text,
  author text,
  year integer,
  genre text,
  copies integer
);

INSERT INTO books VALUES
  (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2),
  (2, 'Emma', 'Jane Austen', 1815, 'classic', 1),
  (3, 'Kindred', 'Octavia E. Butler', 1979, 'sci-fi', 1),
  (4, 'Beloved', 'Toni Morrison', 1987, 'fiction', 1),
  (5, 'The Hobbit', 'J. R. R. Tolkien', 1937, 'fantasy', 3),
  (6, 'Neuromancer', 'William Gibson', 1984, 'sci-fi', 1);

CREATE TABLE members (
  id integer,
  name text,
  email text,
  city text,
  joined date
);

INSERT INTO members VALUES
  (1, 'Ada', 'ada@example.com', 'Berlin', '2025-01-15'),
  (2, 'Grace', 'grace@example.com', 'Munich', '2025-03-02'),
  (3, 'Linus', NULL, 'Berlin', '2026-02-10'),
  (4, 'Margaret', 'margaret@example.com', NULL, '2026-05-20');

CREATE TABLE loans (
  id integer,
  book_id integer,
  member_id integer,
  loaned_on date,
  returned_on date
);

INSERT INTO loans VALUES
  (1, 1, 1, '2026-09-01', '2026-09-10'),
  (2, 3, 1, '2026-09-12', NULL),
  (3, 2, 2, '2026-09-05', '2026-09-20'),
  (4, 1, 3, '2026-09-15', NULL),
  (5, 6, 2, '2026-09-18', NULL);

Ausführen mit

psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql

Ausgabe

    title    | times_lent | still_out | last_loan
-------------+------------+-----------+------------
 Dune        |          2 |         1 | 2026-09-15
 Emma        |          1 |         0 | 2026-09-05
 Kindred     |          1 |         1 | 2026-09-12
 Beloved     |          0 |         0 |
 The Hobbit  |          0 |         0 |
 Neuromancer |          1 |         1 | 2026-09-18
(6 rows)

   title
------------
 Beloved
 The Hobbit
(2 rows)

 loans | open | borrowers
-------+------+-----------
     5 |    3 |         3
(1 row)
  • FROM books LEFT JOIN loans behält Beloved und The Hobbit, und count(l.id) gibt ihnen 0 statt 1.
  • GROUP BY b.id, b.title bildet eine Zeile je Buch; ORDER BY b.id behält die Reihenfolge des Katalogs.
  • max(l.loaned_on) ist das späteste Ausleihdatum; ein Buch ohne Ausleihen bekommt NULL, eine leere Zelle.
  • Die Probe: times_lent ergibt zusammen 5 und still_out 3, die Summen der dritten Abfrage.
Ändern und ausführen

Tab rückt ein, Umschalt+Tab rückt aus. Um den Editor mit der Tastatur zu verlassen, drücken Sie Esc und dann Tab.

Beim ersten Ausführen lädt Ihr Browser PostgreSQL herunter (bis zu 10.1 MB) und speichert es im Cache. Jeder Lauf beginnt mit einer leeren Datenbank. Ihr Code bleibt auf Ihrem Gerät.

Übungen

Übung 1 von 2

Schritt 1: der Bericht je Mitglied

Die Vorlage verbindet loans und members mit einem Inner Join, also fehlt Margaret, und sie zählt mit count(*). Machen Sie daraus den Bericht je Mitglied: jedes Mitglied, mit name, loans (Zahl der Ausleihen), open (noch nicht zurückgegebene Ausleihen) und last_loan (Datum der letzten Ausleihe), nach Namen sortiert.

Tab rückt ein, Umschalt+Tab rückt aus. Um den Editor mit der Tastatur zu verlassen, drücken Sie Esc und dann Tab.

Beim ersten Ausführen lädt Ihr Browser PostgreSQL herunter (bis zu 10.1 MB) und speichert es im Cache. Jeder Lauf beginnt mit einer leeren Datenbank. Ihr Code bleibt auf Ihrem Gerät.

Hinweise
  1. Hinweis 1

    Beginnen Sie FROM members m und holen Sie die Ausleihen mit LEFT JOIN loans l ON l.member_id = m.id dazu, damit Margaret bleibt.

  2. Hinweis 2

    Zählen Sie eine Spalte von loans, count(l.id), nicht count(*): Margarets NULL-Zeile muss als 0 zählen.

  3. Hinweis 3

    open ist count(l.id) FILTER (WHERE l.returned_on IS NULL); last_loan ist max(l.loaned_on).

Eine Lösung zeigen

Ein möglicher Lösungsweg. Ihrer kann anders aussehen und trotzdem alle Prüfungen bestehen.

SELECT m.name,
       count(l.id) AS loans,
       count(l.id) FILTER (WHERE l.returned_on IS NULL) AS open,
       max(l.loaned_on) AS last_loan
FROM members m
LEFT JOIN loans l ON l.member_id = m.id
GROUP BY m.id, m.name
ORDER BY m.name;
Auf dem eigenen Computer ausführen

Installieren Sie PostgreSQL 18 oder neuer. Speichern Sie diese Dateien in einem Ordner, öffnen Sie dort ein Terminal und führen Sie die Befehle unten aus.

main.sql

-- setup.sql has loaded books, members and loans.
SELECT m.name, count(*) AS loans
FROM loans l
JOIN members m ON m.id = l.member_id
GROUP BY m.id, m.name
ORDER BY m.name;

test.sql

-- test: Jedes Mitglied erscheint, Margaret mit 0 Ausleihen, 0 offenen und ohne letzte Ausleihe
-- output:
--    name   | loans | open | last_loan
-- ----------+-------+------+------------
--  Ada      |     2 |    1 | 2026-09-12
--  Grace    |     2 |    1 | 2026-09-18
--  Linus    |     1 |    1 | 2026-09-15
--  Margaret |     0 |    0 |
-- (4 rows)

setup.sql

CREATE TABLE books (
  id integer,
  title text,
  author text,
  year integer,
  genre text,
  copies integer
);

INSERT INTO books VALUES
  (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2),
  (2, 'Emma', 'Jane Austen', 1815, 'classic', 1),
  (3, 'Kindred', 'Octavia E. Butler', 1979, 'sci-fi', 1),
  (4, 'Beloved', 'Toni Morrison', 1987, 'fiction', 1),
  (5, 'The Hobbit', 'J. R. R. Tolkien', 1937, 'fantasy', 3),
  (6, 'Neuromancer', 'William Gibson', 1984, 'sci-fi', 1);

CREATE TABLE members (
  id integer,
  name text,
  email text,
  city text,
  joined date
);

INSERT INTO members VALUES
  (1, 'Ada', 'ada@example.com', 'Berlin', '2025-01-15'),
  (2, 'Grace', 'grace@example.com', 'Munich', '2025-03-02'),
  (3, 'Linus', NULL, 'Berlin', '2026-02-10'),
  (4, 'Margaret', 'margaret@example.com', NULL, '2026-05-20');

CREATE TABLE loans (
  id integer,
  book_id integer,
  member_id integer,
  loaned_on date,
  returned_on date
);

INSERT INTO loans VALUES
  (1, 1, 1, '2026-09-01', '2026-09-10'),
  (2, 3, 1, '2026-09-12', NULL),
  (3, 2, 2, '2026-09-05', '2026-09-20'),
  (4, 1, 3, '2026-09-15', NULL),
  (5, 6, 2, '2026-09-18', NULL);

psql führt die Dateien auf Ihrem eigenen PostgreSQL-Server aus. Nehmen Sie eine leere Datenbank, die Sie wegwerfen können: Hängen Sie -d und ihren Namen an den Befehl an.

Programm ausführen:

psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql

Für die Prüfungen gibt es auf Ihrem Computer noch keinen Befehl. Sie stehen in test.sql: Jede Prüfabfrage gibt t aus, wenn sie besteht.

Übung 2 von 2

Schritt 2: der Bericht je Genre

Schreiben Sie zwei Abfragen. Erstens den Bericht je Genre: jedes Genre, mit genre, books (Zahl der Bücher, jedes einmal gezählt), loans und open, nach Genre sortiert; beginnen Sie bei books und holen Sie die Ausleihen mit LEFT JOIN dazu. Zweitens die Genres, deren Bücher nie ausgeliehen wurden, nur mit genre, nach Genre sortiert.

Tab rückt ein, Umschalt+Tab rückt aus. Um den Editor mit der Tastatur zu verlassen, drücken Sie Esc und dann Tab.

Beim ersten Ausführen lädt Ihr Browser PostgreSQL herunter (bis zu 10.1 MB) und speichert es im Cache. Jeder Lauf beginnt mit einer leeren Datenbank. Ihr Code bleibt auf Ihrem Gerät.

Hinweise
  1. Hinweis 1

    FROM books b LEFT JOIN loans l ON l.book_id = b.id, dann GROUP BY b.genre.

  2. Hinweis 2

    Dune hat zwei Ausleihen und erscheint nach dem Join zweimal: count(DISTINCT b.id) zählt es einmal.

  3. Hinweis 3

    Ein Genre ohne Ausleihen hat count(l.id) = 0; diese Prüfung betrifft eine Gruppe, gehört also in HAVING.

Eine Lösung zeigen

Ein möglicher Lösungsweg. Ihrer kann anders aussehen und trotzdem alle Prüfungen bestehen.

SELECT b.genre,
       count(DISTINCT b.id) AS books,
       count(l.id) AS loans,
       count(l.id) FILTER (WHERE l.returned_on IS NULL) AS open
FROM books b
LEFT JOIN loans l ON l.book_id = b.id
GROUP BY b.genre
ORDER BY b.genre;

SELECT b.genre
FROM books b
LEFT JOIN loans l ON l.book_id = b.id
GROUP BY b.genre
HAVING count(l.id) = 0
ORDER BY b.genre;
Auf dem eigenen Computer ausführen

Installieren Sie PostgreSQL 18 oder neuer. Speichern Sie diese Dateien in einem Ordner, öffnen Sie dort ein Terminal und führen Sie die Befehle unten aus.

main.sql

-- setup.sql has loaded books, members and loans.
SELECT genre, count(*) AS books
FROM books
GROUP BY genre
ORDER BY genre;

test.sql

-- test: Der Bericht je Genre, dann fantasy und fiction als nie ausgeliehene Genres
-- output:
--   genre  | books | loans | open
-- ---------+-------+-------+------
--  classic |     1 |     1 |    0
--  fantasy |     1 |     0 |    0
--  fiction |     1 |     0 |    0
--  sci-fi  |     3 |     4 |    3
-- (4 rows)
--
--   genre
-- ---------
--  fantasy
--  fiction
-- (2 rows)

setup.sql

CREATE TABLE books (
  id integer,
  title text,
  author text,
  year integer,
  genre text,
  copies integer
);

INSERT INTO books VALUES
  (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2),
  (2, 'Emma', 'Jane Austen', 1815, 'classic', 1),
  (3, 'Kindred', 'Octavia E. Butler', 1979, 'sci-fi', 1),
  (4, 'Beloved', 'Toni Morrison', 1987, 'fiction', 1),
  (5, 'The Hobbit', 'J. R. R. Tolkien', 1937, 'fantasy', 3),
  (6, 'Neuromancer', 'William Gibson', 1984, 'sci-fi', 1);

CREATE TABLE members (
  id integer,
  name text,
  email text,
  city text,
  joined date
);

INSERT INTO members VALUES
  (1, 'Ada', 'ada@example.com', 'Berlin', '2025-01-15'),
  (2, 'Grace', 'grace@example.com', 'Munich', '2025-03-02'),
  (3, 'Linus', NULL, 'Berlin', '2026-02-10'),
  (4, 'Margaret', 'margaret@example.com', NULL, '2026-05-20');

CREATE TABLE loans (
  id integer,
  book_id integer,
  member_id integer,
  loaned_on date,
  returned_on date
);

INSERT INTO loans VALUES
  (1, 1, 1, '2026-09-01', '2026-09-10'),
  (2, 3, 1, '2026-09-12', NULL),
  (3, 2, 2, '2026-09-05', '2026-09-20'),
  (4, 1, 3, '2026-09-15', NULL),
  (5, 6, 2, '2026-09-18', NULL);

psql führt die Dateien auf Ihrem eigenen PostgreSQL-Server aus. Nehmen Sie eine leere Datenbank, die Sie wegwerfen können: Hängen Sie -d und ihren Namen an den Befehl an.

Programm ausführen:

psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql

Für die Prüfungen gibt es auf Ihrem Computer noch keinen Befehl. Sie stehen in test.sql: Jede Prüfabfrage gibt t aus, wenn sie besteht.

Häufige Fehler

Eine ausgewählte Spalte fehlt in GROUP BY

SELECT m.name, count(l.id) AS loans
FROM members m
LEFT JOIN loans l ON l.member_id = m.id
GROUP BY m.id;

Was psql ausgibt

ERROR:  column "m.name" must appear in the GROUP BY clause or be used in an aggregate function

Warum, und die Lösung

Jede Ausgabezeile steht für eine Gruppe, und PostgreSQL weiß hier nicht, dass eine Mitglieds-id nur einen Namen hat. Führen Sie jede ausgewählte Spalte, die nicht in einem Aggregat steht, in GROUP BY auf: GROUP BY m.id, m.name. Behalten Sie m.id dort, damit zwei Mitglieder mit gleichem Namen getrennt bleiben.

Eine Spalte ohne Tabellenangabe, die beide Tabellen haben

SELECT m.name, count(id) AS loans
FROM members m
LEFT JOIN loans l ON l.member_id = m.id
GROUP BY m.id, m.name;

Was psql ausgibt

ERROR:  column reference "id" is ambiguous

Warum, und die Lösung

members und loans haben beide eine Spalte id, also könnte ein bloßes id beide meinen. Stellen Sie den Tabellen-Alias davor. Für den Bericht muss es die Spalte von loans sein, count(l.id); count(m.id) würde Margarets Zeile wieder als 1 zählen.

Ein Aggregat in WHERE, um Mitglieder ohne Ausleihen zu finden

SELECT m.name
FROM members m
LEFT JOIN loans l ON l.member_id = m.id
WHERE count(l.id) = 0
GROUP BY m.id, m.name;

Was psql ausgibt

ERROR:  aggregate functions are not allowed in WHERE

Warum, und die Lösung

WHERE läuft über einzelne Zeilen, bevor es die Gruppen und ihre Zählwerte gibt. Prüfen Sie die Zahl in HAVING: GROUP BY m.id, m.name HAVING count(l.id) = 0. Oder verzichten Sie auf das Gruppieren und nehmen den Anti-Join: LEFT JOIN loans l … WHERE l.id IS NULL.

PostgreSQL im Browser: PGlite 0.5.8 (PostgreSQL 18.3), Apache-2.0 und PostgreSQL License. Lizenz und Quellcode

Abschlussquiz

5 Fragen, ohne Hinweise. Ab 80 % ist die Lektion abgeschlossen.

Erledigen Sie zuerst alle Aufgaben oben, um das Abschlussquiz freizuschalten.

Problem melden

Etwas ist falsch oder unklar? Beschreiben Sie es kurz, dann wird es geprüft und korrigiert.

#

Mindestens 20 Zeichen.

Nur, wenn Sie eine Antwort wünschen.

Kernideen

Beginnen Sie bei der Tabelle, deren Zeilen alle erscheinen müssen

Ein Bericht "je Mitglied" muss jedes Mitglied zeigen, auch Margaret, die nie ein Buch ausgeliehen hat. Beginnen Sie also FROM members und holen Sie die Ausleihen mit LEFT JOIN loans l ON l.member_id = m.id dazu: Ein Inner Join würde sie verlieren. Dann bildet GROUP BY m.id, m.name eine Zeile je Mitglied. Gruppieren Sie nach der id und dem Namen, weil zwei Mitglieder gleich heißen könnten; der Name muss ohnehin in GROUP BY stehen, sonst lehnt PostgreSQL ihn in der SELECT-Liste ab. Schließen Sie mit ORDER BY ab, damit der Bericht immer gleich aussieht. Je Buch geht es genauso: FROM books LEFT JOIN loans, GROUP BY b.id, b.title.

Zählen Sie eine Spalte der optionalen Tabelle

Für Margaret fügt der LEFT JOIN eine Zeile mit NULL in jeder Spalte von loans hinzu. count(*) zählt diese Zeile und sagt 1. count(l.id) zählt nur Zeilen, in denen l.id nicht NULL ist, und sagt 0, die richtige Antwort. Dieselbe Falle steckt in FILTER: count(*) FILTER (WHERE l.returned_on IS NULL) zählt Margarets leere Zeile ebenfalls, weil auch ihr returned_on NULL ist. Schreiben Sie für die offenen Ausleihen count(l.id) FILTER (WHERE l.returned_on IS NULL). max(l.loaned_on) liefert das letzte Ausleihdatum; für Margaret ist es NULL, und psql zeigt eine leere Zelle.

Prüfen Sie den Bericht an den Daten

Ein Bericht, der läuft, kann trotzdem falsch sein. Vergleichen Sie ihn mit dem, was Sie wissen: fünf Ausleihen insgesamt, drei davon offen, also muss die Spalte der Ausleihen 5 ergeben und die der offenen 3. Joins vervielfachen Zeilen: Nach books LEFT JOIN loans erscheint Dune zweimal, einmal je Ausleihe. count(DISTINCT b.id) zählt es dann trotzdem einmal, aber sum(b.copies) würde seine Exemplare doppelt addieren. Zeilen ohne Partner sind die andere Probe: LEFT JOIN loans … WHERE l.id IS NULL listet die Bücher, die niemand ausgeliehen hat, hier Beloved und The Hobbit. Führen Sie neben dem Bericht eine kleine Summenabfrage aus.

Quellen

Zuletzt geprüft am 3. Oktober 2026