19 Übungen zu Aggregatfunktionen

Lösen Sie diese 19 SQL-Aggregatfunktionen-Übungen und verbessern Sie Ihre SQL-Kenntnisse! Üben Sie die Verwendung von Aggregatfunktionen mit GROUP BY, HAVING, Unterabfragen und mehr. Enthält eine Lösung und eine detaillierte Erklärung für jede Übung.

Aggregatfunktionen sind ein wichtiger Teil von SQL. Sie ermöglichen es Ihnen, verschiedene Statistiken zu berechnen und Berichte zu erstellen, die Sie mit einzeiligen Operationen wie Filtern nicht hätten erstellen können. Es ist wichtig, die Aggregatfunktionen häufig zu üben, da sie ein wichtiger Bestandteil Ihrer SQL-Kenntnisse sind. Fangen wir an!

Dieser Artikel enthält 19 brandneue Aggregatfunktionen, die mit unserer Bibliotheksdatenbank geübt werden. Wir behandeln das Gruppieren mit GROUP BY, das Filtern von Daten mit HAVING, die Verwendung von Aggregatfunktionen in Unterabfragen und die Aggregation auf mehreren Ebenen. Außerdem frischen wir Ihr Wissen über die Arbeit mit JOINs und NULLs sowie über die Ordnung von Daten nach verschiedenen Statistiken auf.

Wenn Sie bereit sind, diese Konzepte in realen Szenarien anzuwenden, besuchen Sie unseren Kurs Basic SQL Reporting ! Er deckt alle Aggregatfunktionskonzepte ab, die Sie in diesem Artikel üben werden, sowie weitere wichtige Themen wie die Verwendung von CASE WHEN.

Wir empfehlen auch, unsere monatlichen Herausforderungen in unserem Monatliche SQL-Herausforderung Track auszuprobieren. Dabei handelt es sich um eine Sammlung von Übungsaufgaben - jeden Monat werden neue veröffentlicht -, die speziell darauf ausgerichtet sind, Ihre SQL-Kenntnisse zu verbessern und auf dem neuesten Stand zu halten.

Was sind Aggregatfunktionen?

Aggregatfunktionen führen Berechnungen mit einer Reihe von Werten durch und geben als Ergebnis einen einzigen Wert zurück. Übliche Aggregatfunktionen sind:

• SUM() - Berechnet die Summe aller Werte in jeder Gruppe.
• AVG() - Berechnet den Durchschnittswert aller Werte in jeder Gruppe.
• COUNT() - Gibt die Anzahl der Werte in jeder Gruppe zurück.
• MIN() und MAX() - Gibt den kleinsten bzw. größten Wert in jeder Gruppe zurück.

Diese Funktionen sind besonders nützlich bei der Erstellung von Berichten, in denen Sie verschiedene Metriken berechnen müssen.

Benötigen Sie eine ausführlichere Erklärung? Schauen Sie sich unser SQL Aggregate Functions Cheat Sheet an, das alle Aggregatfunktionen, ihre Anwendungsfälle und ihre Interaktionen mit GROUP BY behandelt.

Warum SQL-Aggregationen üben?

Regelmäßiges Üben hilft Ihnen, Ihre Fähigkeiten zu verbessern, damit Sie Daten schneller und effizienter analysieren und bearbeiten können. Mit zunehmender Übung werden Sie in der Lage sein, komplexere Probleme zu lösen und Ihre Abfragen besser zu optimieren.

SQL zu üben ist unerlässlich, wenn Sie eine Karriere als Datenanalyst, Datenbankentwickler oder eine andere Position anstreben, in der Sie mit vielen Daten arbeiten. Sehen Sie sich unseren Artikel GROUP BY-Übungen an, um Ihre Berichterstattungsfähigkeiten weiter zu verfeinern.

Der Datensatz

Werfen wir einen Blick auf den Datensatz, mit dem wir für diese Übungsfragen arbeiten werden.

Der Datensatz besteht aus fünf Tabellen: book, author, book_author, patron und book_loan. Hier ist das Schema:

Die Informationen über Bücher werden in der Tabelle book. Sie hat die folgenden Spalten:

• book_id - Eine eindeutige ID für jedes Buch und der Primärschlüssel der Tabelle.
• title - Der Titel des Buches
• publication_year - Das Jahr, in dem das Buch veröffentlicht wurde. Kann NULL sein, wenn es unbekannt ist.
• genre - Das Genre des Buches, z.B. 'Fantasy' oder 'Mystery'.
• pages - Die Anzahl der Seiten des Buches.

Hier eine Momentaufnahme der Daten in der Tabelle:

book_id	title	publication_year	genre	pages
1	1984	1949	Political Fiction	328
2	Animal Farm	1945	Political Fiction	112
3	The Hobbit	1937	Fantasy	310
4	The Fellowship of the Ring	1954	Fantasy	423

Informationen über Autoren werden in der Tabelle gespeichert author. Sie hat die folgenden Spalten:

• author_id - Eine eindeutige ID für jeden Autor und den Primärschlüssel der Tabelle.
• author_name - Den vollständigen Namen oder das Pseudonym des Autors.
• country - Das Land des Autors.

Hier sind einige der Daten aus der Tabelle:

author_id	author_name	country
1	George Orwell	United Kingdom
2	J.R.R. Tolkien	United Kingdom
3	Isaac Asimov	United States
4	Agatha Christie	United Kingdom

Daten über Personen, die Bücher in der Bibliothek ausleihen, werden in der Tabelle gespeichert patron. Sie hat die folgenden Spalten:

• patron_id - Eine eindeutige ID für jeden Kunden und den Primärschlüssel der Tabelle.
• patron_name - Den vollständigen Namen des Kunden.
• registration_date - Das Datum, an dem sich der Kunde im Bibliothekssystem angemeldet hat.

Hier sind einige der Daten in der Tabelle:

patron_id	patron_name	registration_date
1	Alice Johnson	2024-01-15
2	Bob Smith	2024-03-22
3	Charlie Brown	2024-05-10
4	David Wilson	2024-06-01

Die Viele-zu-Viele-Beziehung zwischen Autoren und den Büchern, die sie geschrieben haben, wird in der book_author Tabelle gespeichert. Sie hat die folgenden Spalten:

• book_author_id - Eine eindeutige ID für jedes Buch-Autor-Paar und den Primärschlüssel der Tabelle.
• author_id - Die ID des Autors.
• book_id - Die ID des Buches, das der Autor geschrieben hat.

Hier sind einige der Daten in der Tabelle:

book_author_id	author_id	book_id
1	1	1
2	1	2
3	2	3
4	2	4

Die Viele-zu-Viele-Beziehung zwischen Besuchern und den von ihnen ausgeliehenen Büchern wird in der Tabelle book_loan Tabelle gespeichert. Sie hat die folgenden Spalten:

• loan_id - Eine eindeutige ID für jede Ausleihe und den Primärschlüssel der Tabelle.
• book_id - Die ID des ausgeliehenen Buches.
• patron_id - Die ID des Benutzers, der das Buch ausgeliehen hat.
• loan_date - Das Datum, an dem das Buch ausgeliehen wurde.
• due_date - Das Datum, an dem das Buch zurückgegeben werden muss.
• return_date - Das tatsächliche Datum, an dem das Buch zurückgegeben wurde.

Hier sind einige der Daten aus der Tabelle:

loan_id	book_id	patron_id	loan_date	due_date	return_date
1	1	1	2024-01-20	2024-02-20	2024-02-15
2	8	1	2024-02-01	2024-03-01	2024-02-28
3	3	2	2024-02-10	2024-03-10	2024-03-05
4	4	3	2024-03-15	2024-04-10	2024-04-15

Kehren Sie zu diesem Abschnitt zurück, wenn Sie beim Lösen der Aufgaben Tabellen- oder Spaltennamen vergessen haben. Wenn Sie Hinweise zur Syntax benötigen, halten Sie unser Spickzettel SQL für Datenanalyse bereit. Es deckt alle Werkzeuge ab, die Ihnen bei der Lösung dieser Aufgaben helfen können. Sie können es sogar im PDF-Format herunterladen und ausdrucken, damit es Ihnen bei zukünftigen Übungen hilft!

Üben Sie diese SQL-Aggregatfunktionen

Lösen Sie die Übungen selbst und sehen Sie sich dann die Lösungen unter jeder Übung an. Dort finden Sie auch weitere Erklärungen zu jeder Lösung, falls Sie nicht weiterkommen.

Übung 1: Bücher im System

Übung: Zählen Sie die Anzahl der in der Datenbank gespeicherten Bücher.

Lösung:

SELECT COUNT(book_id)
FROM book;

Erläuterung: Wir verwenden die Funktion COUNT(), um die Anzahl der Zeilen in der book Tabelle.

Beachten Sie zwei Dinge. Erstens hat die Abfrage keine WHERE Klausel, so dass keine Zeilen gefiltert werden; alle Zeilen in der Tabelle werden gezählt. Zweitens hat die Abfrage keine GROUP BY Klausel. Wenn Sie eine Aggregatfunktion ohne GROUP BY verwenden, werden alle Zeilen in einer Gruppe zusammengefasst und die Funktion wird auf alle Zeilen in dieser Gruppe angewendet. Unsere Abfrage zählt also alle Zeilen in der book Tabelle.

Übung 2: Nicht zurückgegebene Bücher

Übung: Zählen Sie, wie viele Bücher noch nicht zurückgegeben wurden (d.h. Bücher, die kein Rückgabedatum haben).

Lösung:

SELECT COUNT(loan_id)
FROM book_loan
WHERE return_date IS NULL;

Erläuterung: Bücher, die nicht zurückgegeben wurden, haben keine return_date in der book_loan Tabelle; ihre return_date ist NULL. Wir verwenden diese Bedingung in der WHERE Klausel, um nur die Bücher auszuwählen, die noch nicht zurückgegeben wurden.

Wir verwenden die Funktion COUNT(), um die Anzahl der Zeilen im resultierenden Datensatz zu zählen. Beachten Sie, dass das Argument, das mit der Funktion COUNT() verwendet wird, wichtig ist. Hier geben wir loan_id als Argument an, was die Datenbank anweist, alle Werte in den Spalten loan_id zu zählen.

Alternativ hätten wir auch COUNT(*) verwenden und einfach alle Zeilen in der Ergebnismenge zählen können, was zum gleichen Ergebnis geführt hätte. COUNT(return_date) wäre jedoch nicht geeignet: Das Ergebnis wäre 0. Alle return_dates im Ergebnis sind NULL aufgrund der Bedingung return_date IS NULL.

Auch in dieser Abfrage gibt es keine GROUP BY, so dass die Funktion COUNT() alle Leihgaben zählen wird, bei denen das Rückgabedatum leer ist.

Übung 3: Bücher nach Genre

Übung: Geben Sie für jedes Genre den Namen des Genres und die Anzahl der Bücher dieses Genres an.

Lösung:

SELECT
  genre,
  COUNT(book_id)
FROM book
GROUP BY genre;

Erläuterung: Dies ist die einfachste Übung von GROUP BY. Aus der Tabelle bookwählen wir das Genre aus.

Um sicherzustellen, dass die Funktion COUNT() für jedes Genre ein eigenes Ergebnis liefert, teilen wir den Datensatz mit GROUP BY genre in Gruppen auf. Dadurch werden Gruppen auf der Grundlage der Werte in der Spalte genre erstellt; Bücher mit demselben Wert in der Spalte "Genre" kommen in dieselbe Gruppe.

Die Funktion COUNT() bearbeitet jede Gruppe separat und zählt die Anzahl der Bücher in jeder Gruppe.

Wenn Sie eine Auffrischung der Kenntnisse über die Arbeit mit GROUP BY und Aggregatfunktionen benötigen, lesen Sie unseren vollständigen Überblick über GROUP BY und Aggregatfunktionen.

Übung 4: Autoren nach Land

Übung: Geben Sie für jedes Land den Namen und die Anzahl der Autoren an, die diesem Land zugeordnet sind.

Lösung:

SELECT
  country,
  COUNT(author_id)
FROM author
GROUP BY country;

Erläuterung: Dies ist eine weitere grundlegende GROUP BY Übung. Wir wählen Daten aus der Tabelle Autor aus und gruppieren sie nach den Werten in der Spalte country. Dann wenden wir COUNT(author_id) auf jede Gruppe an, um die Autoren zu zählen, die aus diesem Land stammen.

Übung 5: Seitenumfänge pro Genre

Übung: Zeigen Sie für jedes Genre vier Spalten an: den Genre-Namen, die minimale und maximale Seitenzahl für Bücher in diesem Genre und die difference zwischen der größten und der kleinsten Seitenzahl in jedem Buch.

Lösung:

SELECT
  genre,
  MIN(pages),
  MAX(pages),
  MAX(pages) - MIN(pages) AS difference
FROM book
GROUP BY genre;

Erläuterung: Dies ist eine weitere grundlegende GROUP BY-Übung. Wir wählen Daten aus der Tabelle author aus und gruppieren sie nach den Werten in der Spalte country. Dann wenden wir COUNT(author_id) auf jede Gruppe an, um die Autoren zu zählen, die aus diesem Land stammen.

Übung 5: Seitenbereiche pro Genre

Übung: Geben Sie für jedes Genre vier Spalten an: den Namen des Genres, die minimale und maximale Seitenzahl der Bücher dieses Genres und die Differenz zwischen der größten und der kleinsten Seitenzahl jedes Buches.

Lösung:

Erläuterung: Um Statistiken für jedes Genre zu erhalten, gruppieren Sie die Daten aus der book Tabelle nach der Spalte genre.

Verwenden Sie die Aggregatfunktionen MIN(pages) und MAX(pages), um die minimale und maximale Seitenzahl zu berechnen. In der dritten Spalte verwenden Sie MIN(pages) - MAX(pages), um die Differenz für jede Gruppe zu berechnen. Schließlich benennen Sie die letzte Spalte mit AS in difference um.

Übung 6: Genres von großen Büchern

Übung: Geben Sie für jedes Genre die durchschnittliche Seitenzahl aller Bücher dieses Genres an. Geben Sie nur Genres an, bei denen das durchschnittliche Buch mehr als 250 Seiten hat. Nennen Sie die Spalte mit den durchschnittlichen Seiten avg_pages.

Lösung:

SELECT
  genre,
  AVG(pages) AS avg_pages
FROM book
GROUP BY genre
HAVING AVG(pages) >= 250;

Erläuterung: Diese Übung ähnelt der vorherigen: Wir gruppieren die Bücher nach Genre und berechnen die durchschnittliche Anzahl der Bücher in jedem Genre mit AVG(). Allerdings gibt es hier ein neues Element: HAVING. HAVING wird verwendet, um Gruppen zu filtern und Gruppen zu finden, für die eine Aggregatfunktion eine bestimmte Bedingung erfüllt. In unserem Fall suchen wir nach Gruppen (Genres), bei denen die durchschnittliche Seitenzahl größer oder gleich 250 ist.

Beachten Sie, dass HAVING anders funktioniert als WHERE. WHERE wird verwendet, um einzelne Zeilen vor der Gruppierung zu filtern, während HAVING verwendet wird, um Zeilen nach der Gruppierung zu filtern. Über den Unterschied zwischen HAVING und WHERE können Sie in unserem Artikel HAVING vs. WHERE in SQL lesen : Was Sie wissen sollten.

Übung 7: Moderne Gattungen

Übung: Geben Sie das durchschnittliche Erscheinungsjahr für jedes Buchgenre an. Runden Sie das Jahr auf eine ganze Zahl. Geben Sie nur Gattungen an, bei denen das durchschnittliche Erscheinungsjahr nach 1940 liegt.

Lösung:

SELECT
  genre,
  ROUND(AVG(publication_year))
FROM book
GROUP BY genre
HAVING ROUND(AVG(publication_year)) > 1940;

Erläuterung: Diese Übung ähnelt der vorherigen: Wir gruppieren die Bücher nach genre und berechnen das durchschnittliche Erscheinungsjahr mit AVG(publication_year). Dann filtern wir mit HAVING nach Gattungen, deren durchschnittliches Erscheinungsjahr größer als 1940 ist.

Übung 8: Bücher mit mehreren Autoren

Übung: Geben Sie für Bücher, die von mehr als einem Autor geschrieben wurden, den Titel und die Anzahl der Autoren an.

Lösung:

SELECT 
  title,
  COUNT(author_id)
FROM book b
JOIN book_author ba
  ON b.book_id = ba.book_id
GROUP BY b.book_id
HAVING COUNT(author_id) > 1;

Erläuterung: Zunächst müssen wir für jedes Buch die Autoren finden. Zu diesem Zweck verbinden wir die Tabellen book und book_author über ihre gemeinsame book_id. Dadurch werden die Daten für jedes Buch mit den Daten für seine Autoren kombiniert: eine Zeile für jede Buch-Autoren-Kombination.

Anschließend gruppieren wir die Zeilen nach book_id: Alle Zeilen, die sich auf dasselbe Buch beziehen, befinden sich in derselben Gruppe. Somit befinden sich alle Autoren für jedes Buch in der gleichen Gruppe. Dann wenden wir die Funktion COUNT(author_id) an, um die Autoren in jeder Gruppe zu zählen.

Schließlich filtern wir alle Bücher mit nur einem Autor heraus, indem wir HAVING COUNT(author_id) > 1 verwenden.

Übung 9: Die letzte Ausleihe eines Buches

Übung: Zeigen Sie für jedes Buch dessen title und das Datum der letzten Ausleihe an. Benennen Sie die zweite Spalte last_loaned. Zeigen Sie NULL in der zweiten Spalte für alle Bücher an, die noch nie ausgeliehen wurden.

Lösung:

SELECT
  book.title,
  MAX(book_loan.loan_date) AS last_loaned
FROM book
LEFT JOIN book_loan 
  ON book.book_id = book_loan.book_id
GROUP BY 
  book.book_id,
  book.title;

Erläuterung: Verwenden Sie LEFT JOIN, um die Spalten book und book_loan zu verbinden, um sicherzustellen, dass auch Bücher, die nie ausgeliehen wurden, in der Ergebnismenge enthalten sind. Gruppieren Sie die Ergebnisse nach book_id und book_title.

Beachten Sie, dass Sie die Ergebnisse nicht nur nach dem Titel gruppieren sollten; wenn zwei Bücher denselben Titel haben, würden sie fälschlicherweise in dieselbe Gruppe eingeordnet werden. Gruppieren Sie nach book_id (da es jedes Buch eindeutig identifiziert) und title (da SQL einen Fehler auslöst, wenn eine nicht aggregierte Spalte in SELECT nicht in GROUP BY eingefügt wird). Sie können darüber in unserem Artikel 7 Common GROUP BY Errors lesen.

Um das neueste loan_date zu erhalten, verwenden Sie MAX(loan_date). Die späteren Daten werden als "größer" behandelt. Wenn es für dieses Buch keine Buchausleihen gibt, sind alle Ausleihdaten NULL und die Funktion MAX() gibt NULL für dieses Buch zurück.

Übung 10: Buchausleihen pro Monat

Übung: Zeigen Sie, wie viele Buchausleihen in jedem Monat eines jeden Jahres vergeben wurden. Zeigen Sie drei Spalten:

• Der Jahres- und Monatsteil von loan_date als Zahlen in den ersten beiden Spalten. Nennen Sie sie loan_year und loan_month
• Eine Spalte, die zählt, wie viele Bücher in diesem Monat ausgeliehen wurden.

Ordnen Sie das Ergebnis nach dem Jahr und dann nach dem Monat, wobei ältere Daten zuerst angezeigt werden.

Lösung:

SELECT
  EXTRACT(YEAR FROM loan_date) AS loan_year,
  EXTRACT(MONTH FROM loan_date) AS loan_month,
  COUNT(loan_id)
FROM book_loan
GROUP BY 
  EXTRACT(MONTH FROM loan_date),
  EXTRACT(YEAR FROM loan_date)
ORDER BY 
  loan_year,
  loan_month;

Erläuterung: Wir verwenden EXTRACT(YEAR FROM loan_date) und EXTRACT(MONTH FROM loan_date), um die Jahres- und Monatsteile von loan_date zu erhalten. Wir verwenden wiederum EXTRACT() in GROUP BY, um Ausleihen aus denselben Monaten zusammenzufassen.

Wir verwenden die Funktion COUNT(), um die Anzahl der in jedem Monat vergebenen Darlehen zu berechnen.

Schließlich ordnen wir die Ergebnisse nach loan_year und loan_month. Beachten Sie, dass Sie die Spaltenalias in der Anweisung ORDER BY verwenden können. In der GROUP BY müssen Sie jedoch noch die Funktionen verwenden; wenn diese Klausel verarbeitet wird, ist die Funktion EXTRACT() (und damit die neuen Spalten) noch nicht definiert worden. Weitere Informationen zu diesem Thema finden Sie in unserem Artikel SQL Order of Operations.

Übung 11: Beliebte Bücher

Übung: Zeigen Sie für jedes Buch title an, wie oft es ausgeliehen wurde und von wie vielen verschiedenen Personen es ausgeliehen wurde. Benennen Sie die beiden letzten Spalten times_loaned und different_patrons.

Lösung:

SELECT
  title,
  COUNT(loan_id) AS times_loaned,
  COUNT(DISTINCT patron_id) AS different_patrons
FROM book b
LEFT JOIN book_loan bl
  ON b.book_id = bl.book_id
GROUP BY
  b.title,
  b.book_id;

Erläuterung: Um die Buchausleihen für jedes Buch zu finden, müssen Sie die Tabellen book und book_loan. Verwenden Sie LEFT JOIN, um sicherzustellen, dass auch Bücher, die noch nie ausgeliehen wurden, im Ergebnis auftauchen.

Wir wollen die Ausleihen für jedes Buch gruppieren, also müssen wir sowohl nach book_id als auch nach dem Buch title gruppieren (aus demselben Grund, den wir in Übung 9 besprochen haben).

Wir wollen zählen, wie oft das Buch ausgeliehen wurde und wie viele verschiedene Besucher das Buch ausgeliehen haben. Zu diesem Zweck müssen wir die Funktion COUNT() zweimal verwenden.

Zuerst verwenden wir COUNT(loan_id), um die Anzahl der Ausleihen des Buches zu zählen.

Die zweite Verwendung von COUNT() ist interessanter: Wir wollen die verschiedenen Besucher zählen, die das Buch ausgeliehen haben. Wenn jemand dasselbe Buch mehrmals ausgeliehen hat, wollen wir ihn nur einmal zählen. Daher verwenden wir COUNT(DISTINCT patron_id). Durch die Verwendung von DISTINCT wird sichergestellt, dass die ID eines Kunden nur einmal gezählt wird, selbst wenn er dieselben Bücher mehrmals ausgeliehen hat.

Sie können mehr darüber lesen, indem Sie Was ist der Unterschied zwischen ZÄHLEN(*), ZÄHLEN(1), ZÄHLEN(Spalte) und ZÄHLEN(DISTINKT) lesen?

Übung 12: Überfällige Bücher

Übung: Zeigen Sie für jeden Kunden den Namen und die Anzahl der Bücher an, die überfällig sind (d.h. mit einem Rückgabedatum nach dem Fälligkeitsdatum).

Lösung:

SELECT
  patron_name,
  COUNT(book_id) AS overdue_books
FROM patron p
LEFT JOIN book_loan bl
  ON p.patron_id = bl.patron_id AND return_date > due_date
GROUP BY
  p.patron_id,
  patron_name;

Erläuterung: Verbinden Sie patron und book_loan mit LEFT JOIN, um sicherzustellen, dass auch Kunden, die keine überfälligen Buchausleihen haben, im Ergebnis enthalten sind.

Um nur Ausleihen auszuwählen, bei denen das Rückgabedatum nach dem Fälligkeitsdatum liegt, verwenden Sie eine kombinierte Verknüpfungsbedingung: ON p.patron_id = bl.patron_id AND return_date > due_date. Der erste Teil verbindet nur die Zeilen, die tatsächlich miteinander in Beziehung stehen. Der zweite Teil wird als zusätzlicher Filter verwendet, um nur die Zeilen zu verbinden, in denen return_date > due_date.

Beachten Sie, dass dies ein Unterschied zur Verwendung einer WHERE Klausel später in der Abfrage ist. Die WHERE Klausel verwirft alle Zeilen, in denen loan_id IS NULL vorkommt. Wir möchten jedoch diese Zeilen behalten, um Kunden einzuschließen, die keine überfälligen Bücher haben.

Wir gruppieren die Zeilen nach patron_id und patron_name (aus denselben Gründen wie in Übung 9). Schließlich verwenden wir COUNT(book_id), um die überfälligen Bücher für jeden Kunden zu zählen. COUNT() liefert 0 für Kunden, die noch nie Bücher ausgeliehen haben, und für Kunden, die ihre Bücher immer pünktlich zurückgegeben haben.

Übung 13: Durchschnittliche Autoren pro Genre

Übung: Geben Sie für jedes Genre den Namen des Genres und die durchschnittliche Anzahl der Autoren an, die Bücher dieses Genres haben. Benennen Sie die zweite Spalte average_authors_per_book

Lösung:

WITH number_of_authors AS (
  SELECT
    book_id,
    COUNT(author_id) AS author_count
  FROM book_author
  GROUP BY book_id
)
SELECT
  genre,
  AVG(author_count) AS average_authors_per_book
FROM number_of_authors na
JOIN book b
  ON na.book_id = b.book_id
GROUP BY genre;

Erläuterung: Hier verwenden wir eine Konstruktion, die als Common Table Expression (CTE) bezeichnet wird. Weitere Informationen zu CTEs finden Sie in unserem Leitfaden zu gemeinsamen Tabellenausdrücken.

Kurz gesagt, ein CTE ermöglicht es Ihnen, eine benannte temporäre Ergebnismenge zu erstellen, die Sie in der Abfrage verwenden können. Sie erstellen eine CTE mit dieser Syntax:

WITH <cte_name> AS (query)

Jede Abfrage innerhalb der Klammer fungiert als virtuelle Tabelle mit dem Namen cte_name und ist für die Hauptabfrage zugänglich (die Anweisung SELECT nach der schließenden Klammer des CTE).

In der CTE berechnen wir die Anzahl der Autoren für jedes Buch. Wir wählen die book_id und die Anzahl der Autoren aus. Dies ist eine ähnliche Abfrage wie die in Übung 8.

In der äußeren Abfrage verknüpfen wir unsere number_of_authors CTE mit der book Tabelle, um das Genre jedes Buches anzuzeigen. Dann verwenden wir AVG(author_count) und GROUP BY Genre, um das Endergebnis zu erhalten.

Wenn Sie keine CTE verwenden möchten, können Sie das gleiche Ergebnis auch mit einer Unterabfrage erzielen:

SELECT
  genre,
  AVG(author_count) AS average_authors_per_book
FROM (
  SELECT
    book_id,
    COUNT(author_id) AS author_count
  FROM book_author
  GROUP BY book_id) AS na
  JOIN book b
    ON na.book_id = b.book_id
  GROUP BY genre;

Übung 14: Anzahl der von Lesern gelesenen Seiten

Übung: Geben Sie für jeden Besucher den Namen und die Gesamtzahl der gelesenen Seiten an (d. h. die Seitenzahl aller Bücher, die er ausgeliehen hat). Wir gehen davon aus, dass sie jedes Buch vollständig gelesen haben. Geben Sie alle Bücher an, auch die, die noch nicht zurückgegeben wurden.

Zeigen Sie nur Ergebnisse für Kunden an, die mehr als 1.000 Seiten gelesen haben.

Lösung:

SELECT
  patron_name,
  SUM(pages) AS total_pages_read
FROM book b
JOIN book_loan bl
  ON b.book_id = bl.book_id
JOIN patron p
  ON p.patron_id = bl.patron_id
GROUP BY 
  p.patron_id,
  p.patron_name
HAVING SUM(pages) > 1000;

Erläuterung: Verbinden Sie drei Tabellen mit einem regulären JOIN: patron, book_loan, und book. Verwenden Sie SUM(pages), um die Anzahl der Seiten aller Bücher, die der Kunde ausgeliehen hat, zu summieren. Filtern Sie mit HAVING SUM(pages) > 1000, um nur Kunden anzuzeigen, die mehr als 1000 Seiten gelesen haben.

Hinweis: Da wir die Besucher anzeigen wollen, die mehr als 1000 Seiten gelesen haben, ist es nicht nötig, hier LEFT JOIN oder FULL JOIN zu verwenden. Die Besucher, die 0 Seiten gelesen haben, werden ohnehin mit unserer HAVING Bedingung herausgefiltert.

Übung 15: Kunden ohne ausgeliehene Bücher

Übung: Zeigen Sie die Gesamtzahl der Kunden, die noch nie ein Buch ausgeliehen haben.

Lösung:

SELECT COUNT(p.patron_id)
FROM patron p
WHERE NOT EXISTS (
  SELECT * FROM book_loan
  WHERE patron_id = p.patron_id
);

Erläuterung: Um nur die Besucher anzuzeigen, die noch nie Bücher ausgeliehen haben, filtern Sie das Ergebnis mit einer WHERE NOT EXISTS Klausel. Für jeden Kunden, der noch nie Bücher ausgeliehen hat, gibt es keinen book_loan Eintrag mit dem id dieses Kunden. Verwenden Sie eine Unterabfrage, um einen Satz von Buchausleihen für jeden Kunden zu finden, und verwenden Sie dann das Ergebnis dieser Unterabfrage in der WHERE NOT EXISTS Klausel. Dadurch wird sichergestellt, dass alle Kunden in der resultierenden Menge keine entsprechenden book_loanshaben.

Verwenden Sie schließlich die Funktion COUNT(), um die ausgewählten patron_ids zu zählen.

Übung 16: Autoren und Zielgruppen

Übung: Zeigen Sie für jeden Autor, wie viele verschiedene Kunden sein Buch ausgeliehen haben.

Lösung:

SELECT
  a.author_name,
  COUNT(DISTINCT patron_id) AS distinct_patrons
FROM author a
JOIN book_author ba
  ON a.author_id = ba.author_id
JOIN book b
  ON b.book_id = ba.book_id
LEFT JOIN book_loan bl
  ON bl.book_id = b.book_id
GROUP BY
  a.author_id,
  a.author_name;

Erklären Sie: Verbinden Sie vier Tabellen: author, book_author, book, und book_loan. Verwenden Sie eine reguläre JOIN mit den ersten drei Tabellen und eine LEFT JOIN zwischen book und book_loan. Die LEFT JOIN sorgt dafür, dass das Buch auch dann im Ergebnis angezeigt wird, wenn es nie ausgeliehen wurde.

SELECT Der Name des Autors wird in die Tabelle COUNT(DISTINCT patron_id) eingetragen, um alle Kunden zu zählen, die Bücher ausgeliehen haben. Wenn die Bücher des Autors nie ausgeliehen wurden, gibt COUNT() 0 zurück.

Gruppieren Sie das Ergebnis nach der ID und dem Namen des Autors, um die Fehler zu vermeiden, über die wir bereits gesprochen haben.

Übung 17: Die ältesten Bücher

Übung: Finden Sie das/die älteste(n) Buch(er) in der Datenbank (d. h. das/die Buch(er) mit dem ältesten publication_year). Zeigen Sie nur zwei Spalten an: title und publication_year.

Denken Sie daran, dass es mehr als ein Buch mit dem ältesten Erscheinungsjahr geben kann.

Lösung:

SELECT 
  title,
  publication_year
FROM book
WHERE publication_year = (
  SELECT MIN(publication_year)
  FROM book
);

Erläuterung: Verwenden Sie eine Unterabfrage, um die ältesten Bücher zu finden. Wählen Sie nur die title und das Erscheinungsjahr der Bücher aus, deren publication_year gleich dem niedrigsten Erscheinungsjahr im System ist. Das früheste publication_year finden Sie mit MIN(publication_year). Verwenden Sie diesen Ausdruck in einer Unterabfrage und vergleichen Sie dann die publication_year der einzelnen Bücher mit dem Ergebnis der Unterabfrage.

Übung 18: Die meisten aktiven Benutzer

Übung: Finden Sie die Namen aller Kunden, die überdurchschnittlich viele Bücher ausgeliehen haben. Geben Sie die Anzahl der ausgeliehenen Bücher zusammen mit dem Namen an.

Lösung:

SELECT 
  patron_name,
  COUNT(*) AS loan_count
FROM patron
JOIN book_loan 
  ON patron.patron_id = book_loan.patron_id
GROUP BY patron_name
HAVING COUNT(*) > (
  SELECT COUNT(*)
  FROM book_loan
) / (
  SELECT COUNT(*)
  FROM patron
);

Erklären Sie: Verbinden Sie die patron und book_loan zusammen und gruppieren Sie die Ergebnisse nach dem Namen und der ID des Kunden. Um nur die Kunden anzuzeigen, die überdurchschnittlich viele Bücher ausgeliehen haben, verwenden Sie die Klausel HAVING, die die Anzahl der Ausleihen des aktuellen Kunden mit der durchschnittlichen Anzahl der ausgeliehenen Bücher pro Kunde vergleicht. Ermitteln Sie diesen Durchschnitt, indem Sie die Gesamtzahl der Ausleihen durch die Gesamtzahl der Kunden dividieren.

Da / in SQL eine ganzzahlige Division ist (was bedeutet, dass der Rest verworfen wird), verwenden Sie > (größer als), nicht >= (größer oder gleich), um die Werte in der HAVING -Klausel zu vergleichen.

Übung 19: Der produktivste Autor

Übung: Finden Sie den Autor, der die meisten Bücher geschrieben hat.

Lösung:

WITH authors_books_count AS (
  SELECT
    author_id,
    COUNT(*) AS book_count
  FROM book_author
  GROUP BY author_id
)
SELECT 
  author_name,
  book_count
FROM author
JOIN authors_books_count abc
  ON author.author_id = abc.author_id
WHERE book_count = (
  SELECT MAX(book_count)
  FROM authors_books_count
);

Erläuterung: In der CTE authors_books_count finden wir die Anzahl der Bücher, die jeder Autor geschrieben hat, indem wir die id des jeweiligen Autors verwenden.

In der äußeren Abfrage wählen wir die Autoren aus, deren Buchanzahl gleich der maximalen Buchanzahl ist. Wir verwenden eine Unterabfrage und die Funktion MAX(), um die maximale Buchanzahl aus der CTE auszuwählen und sie mit der book_count für jeden Autor zu vergleichen.

Lust auf mehr SQL-Aggregatfunktionen?

Das ist das Ende dieser SQL-Aggregatfunktion-Übungsreihe. Jetzt sind Ihre Kenntnisse über Aggregatfunktionen auf dem neuesten Stand! Sie können Ihr theoretisches Wissen mit diesen GROUP BY-Interview-Fragen überprüfen.

Wir haben verschiedene Aggregatfunktionen, GROUP BY, HAVING und vieles mehr behandelt! Wir haben verschiedene Arten von JOINs, Unterabfragen und die Arbeit mit NULLs geübt. Hungrig auf mehr? Schauen Sie sich die Übungen in unserem großen SQL-Praxis Track an; er enthält mehr als 100 Stunden SQL-Übungen! Oder probieren Sie unseren Monatliche SQL-Herausforderung, der jeden Monat einen neuen SQL-Übungskurs anbietet!