MariaDB-Kurs / Monatsumsätze, Kundenkäufe und unverkaufte Produkte auswerten

Monatliche Warenumsätze, Kundenkäufe und unverkaufte Produkte verwenden unterschiedliche Ergebniseinheiten, obwohl sie dieselben Tabellen auswerten. Definieren Sie den Zahlungsstatus, den enthaltenen Betrag und die Bedeutung von „unverkauft“. Vergleichen Sie Gesamtbeträge erst, wenn Zeitraum und Auswahlbedingungen übereinstimmen.

Kennzahlen und betrachtete Grundgesamtheit festlegen

In dieser Übung bedeutet Warenumsatz: Menge mal gespeicherter Einzelpreis beim Kauf für Positionen bezahlter Bestellungen. Versandkosten und stornierte Bestellungen bleiben ausgeschlossen. Das ist eine vereinfachte Auswertung, keine vollständige Regel für Buchhaltung, Steuern oder Erstattungen. Ein unverkauftes Produkt ist hier ein registriertes Produkt, das noch nie in einer bezahlten Bestellung vorkam.

Prüfen Sie die Kriterien für GROUP BY und HAVING und die Risiken vervielfachter Beträge beim Verbinden mehrerer Tabellen. Legen Sie für jeden Bericht fest, wofür eine Ergebniszeile steht und welche Datensätze berücksichtigt werden.

Bericht Eine Zeile steht für Grundgesamtheit im Beispiel
Monatlicher Warenumsatz Jahr und Monat Bezahlte Positionen aus 2026
Käufe je Kunde Einen Kunden Gesamte vorhandene Historie, einschließlich Nullbeträgen
Unverkaufte Produkte Ein Produkt Keine Position in einer bezahlten Bestellung der gesamten Historie

Alle anfänglichen Bestellungen liegen in 2026. Daher können Sie die monatlichen und kundenbezogenen Summen in diesem Datenbestand abgleichen. Kommen Bestellungen aus anderen Jahren hinzu, erfasst die Kundenabfrage sie weiterhin, die Monatsabfrage für 2026 jedoch nicht. Vereinheitlichen Sie die Zeiträume ausdrücklich, bevor Sie gleiche Summen erwarten.

Einen von Hand prüfbaren Datenbestand vorbereiten

Verbinden Sie sich als course_user mit dem Übungsserver und wählen Sie shopcourse. Die Anleitung zum Client und zu UTF-8-SQL-Dateien beschreibt das Speichern der Blöcke und SOURCE C:/mariadb-lab/lesson.sql; mit dem tatsächlichen Dateipfad. SQL wird innerhalb des MariaDB-Clients ausgeführt.

Kontrollieren Sie SELECT VERSION(), DATABASE(); und SHOW TABLES;, bevor Sie c42_customers, c42_products, c42_orders und c42_items anlegen. Die Namen gehören nur zu dieser Übung. Existieren die Tabellen schon, prüfen Sie Definitionen und Datensätze und bewahren Sie benötigte Inhalte auf. Ersetzen Sie keine Anwendungstabellen.

USE shopcourse;
CREATE TABLE c42_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c42_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c42_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c42_order_customer FOREIGN KEY (customer_id)
  REFERENCES c42_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c42_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c42_item_order FOREIGN KEY (order_id)
  REFERENCES c42_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c42_item_product FOREIGN KEY (product_id)
  REFERENCES c42_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c42_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c42_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c42_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c42_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

Die koreanischen Namen und Ortswerte bleiben unverändert. Produkte 1–4 sind Notizbuch, Kugelschreiber, Becher und Schreibtischlampe. Die Ortswerte der Kunden 1 und 3 stehen für Seoul, der von Kunde 2 für Busan und der von Kunde 4 für Daejeon. Erhalten Sie die UTF-8-Zeichenfolgen, um die Ergebnisse nachzuvollziehen.

Bestellung 101 enthält Waren im Wert von 11500, Bestellung 102 von 8000 und Bestellung 103 von 19500. Zusammen ergibt das 39000. Die 1500 der stornierten Bestellung 104 sind ausgeschlossen. Eigenständige Tabellennamen verhindern, dass Änderungen aus anderen Übungen diese Vergleichsbasis beeinflussen.

Nach Jahr und Monat gruppieren und Bestellungen zählen

SELECT DATE_FORMAT(o.ordered_at,'%Y-%m') AS month_label,
 COUNT(DISTINCT o.order_id) AS paid_orders,
 SUM(i.quantity*i.unit_price) AS goods_sales
FROM c42_orders o JOIN c42_items i ON i.order_id=o.order_id
WHERE o.status='paid' AND o.ordered_at>='2026-01-01' AND o.ordered_at<'2027-01-01'
GROUP BY DATE_FORMAT(o.ordered_at,'%Y-%m') ORDER BY month_label;
Monat Bezahlte Bestellungen Warenbetrag
2026-01 2 19500.00
2026-02 1 19500.00

Die Verknüpfung enthält fünf bezahlte Positionen, aber nur drei Bestellungen. COUNT(DISTINCT order_id) zählt daher Bestellungen, während SUM die Positionsbeträge addiert. Im Januar würde COUNT(*) drei Positionszeilen statt zwei Bestellungen zählen. Die COUNT-DISTINCT-Referenz beschreibt das Zählen unterschiedlicher Werte.

Eine bezahlte Bestellung ohne Positionen erscheint in dieser inneren Verknüpfung nicht. Soll sie mitgezählt werden, berechnen Sie die Kennzahl aus der dafür vorgesehenen Bestellungsmenge. Eine Gruppierung ausschließlich nach der Monatsnummer könnte außerdem Januar verschiedener Jahre vermischen. Jahr und Monat vermeiden diese Vermischung; der Zeitraum enthält seinen Beginn und schließt die nächste Jahresgrenze aus.

Die Abfrage erzeugt keine Nullzeilen für fehlende Monate. Dafür benötigen Sie eine ausdrücklich vorgegebene Kalendermenge. Die offizielle GROUP-BY-Referenz erläutert Gruppierung und Filterreihenfolge.

Kunden ohne Käufe in der Auswertung behalten

SELECT c.customer_id,c.customer_name,
 COALESCE(SUM(CASE WHEN o.status='paid' THEN i.quantity*i.unit_price ELSE 0 END),0) AS paid_goods
FROM c42_customers c LEFT JOIN c42_orders o ON o.customer_id=c.customer_id
LEFT JOIN c42_items i ON i.order_id=o.order_id
GROUP BY c.customer_id,c.customer_name ORDER BY c.customer_id;

Für die Kundenkennungen 1–4 ergeben sich 19500.00, 19500.00, 0.00 und 0.00. LEFT JOIN erhält die Kunden, CASE berücksichtigt nur bezahlte Positionen und COALESCE ersetzt eine fehlende Summe durch die Zahl 0. Kunde 3 hat ausschließlich eine stornierte Bestellung; Kunde 4 hat keine Bestellung. Beide bleiben im Bericht.

Wird die Bedingung für bezahlte Bestellungen in WHERE verschoben, können diese Kunden verschwinden. Platzieren Sie Filter entsprechend der gewünschten Kundenmenge. Ergänzen Sie bei einer zeitlichen Eingrenzung auch den Zeitraum an einer Stelle, die Kunden ohne passende Käufe weiterhin erhält.

Summieren Sie Versandkosten einer Bestellung nicht unmittelbar über deren Positionen: Der Betrag würde pro Position wiederholt. Dieser Bericht addiert ausschließlich Warenbeträge. Bestellungsbezogene Gebühren benötigen eine getrennte Berechnung auf Bestellungsebene oder eine vorherige Aggregation.

Produkte ohne irgendeinen bezahlten Verkauf finden

SELECT p.product_id,p.product_name FROM c42_products p
WHERE NOT EXISTS(SELECT 1 FROM c42_items i JOIN c42_orders o ON o.order_id=i.order_id
 WHERE i.product_id=p.product_id AND o.status='paid') ORDER BY p.product_id;

Nur Produkt 4, die Schreibtischlampe (스탠드), erscheint. NOT EXISTS prüft, ob keine zugehörige Position einer bezahlten Bestellung existiert. Ein Produkt, das ausschließlich in stornierten Bestellungen vorkäme, wäre nach dieser Definition ebenfalls unverkauft.

„Noch nie verkauft“, „ohne Verkauf in 2026“ und „in keiner Bestellung irgendeines Status vorhanden“ sind unterschiedliche Fragen. Passen Sie die Bedingungen der gewünschten Bedeutung an. Die Übung zu EXISTS und NOT EXISTS hilft bei Existenzfragen; die offizielle EXISTS-Referenz beschreibt den Test auf vorhandene Zeilen.

Summen, Auslassungen und Zeitgrenzen abgleichen

Im ursprünglichen Bestand ergeben sowohl die beiden Monatswerte als auch die vier Kundenwerte zusammen 39000. Bei Abweichungen vergleichen Sie Primärschlüssel, Zeitraum, Status und verwendeten Einzelpreis. Prüfen Sie danach Zeilenvervielfachung und Filter nach LEFT JOIN. Eine plausible Gesamtsumme allein belegt nicht die Richtigkeit jeder Zeile und jedes Berichts.

Reale Auswertungen benötigen zusätzlich einen einheitlichen Beobachtungsstand, eine Zeitzonenregel sowie Regeln für Stornierungen und Erstattungen. Die festen DATETIME-Werte dieser Übung halten die Zeitgrenzen unabhängig vom Ausführungstag. Übergeben Sie angeforderte Zeiträume in Anwendungen als Treiberparameter, statt Texte in SQL zusammenzusetzen.

Prüfen Sie vor einer Wiederholung Datenbank, Definitionen und Daten und sichern Sie benötigte Inhalte. Entfernen Sie die vier Tabellen nur als entbehrliche Übungsobjekte, zuerst die abhängigen Tabellen. DROP TABLE löscht Struktur und Datensätze und lässt sich nicht durch ein gewöhnliches ROLLBACK rückgängig machen. Halten Sie diese Bereinigung von Transaktionsversuchen getrennt.

DROP TABLE shopcourse.c42_items;
DROP TABLE shopcourse.c42_orders;
DROP TABLE shopcourse.c42_products;
DROP TABLE shopcourse.c42_customers;