SQL Abfrage erstellen

[Student1986.]

Hallo,

wie kann würde für diese Aufgabenstellung die Lösung ausseehen:

Task: SQL-Abfragen

Gegeben sind folgende Relationen: (Primärschlüssel: unterstrichen, Fremdschlüssel: kursiv)

produkt (ean, bezeichnung, kategorie, ekPreis, listPreis) (View)
filiale (filNr, inhName, strasse, plz) (View)
sortiment (filNr, ean, vkPreis, preisRed, bestand) (View)
kunde (kundeNr, name, bonStufe) (View)
rechnung (rechnungNr, datum, bezahlt, kundeNr, filNr) (View)
rechnungPos (rechnungNr, datum, positionNr, ean, einzelPreis, menge) (View)

1. Jedes Produkt hat einen eindeutigen 15-stelligen EAN-Code (ean) und kann einer bestimmten Produktkategorie (kategorie) zugeordnet werden. Produkte werden zentral eingekauft und haben daher einen für alle Filialen einheitlichen Einkaufspreis (ekPreis). Die zentrale Einkaufsstelle schlägt einen Listenpreis (listPreis) vor, an den sich die Filialen allerdings nicht halten müssen.

2. Filialen werden durch eine eindeutige Filialnummer identifiziert (filNr) und sind durch den Namen des Filialleiters (inhName) und durch die Adresse (strasse, plz) gekennzeichnet.

3. Nicht jedes Produkt wird in jeder Filiale verkauft. In der Relation sortiment erfolgt die Zuordnung zwischen Produkten und Filialen. Ein Produkt kann in verschiedenen Filialen zu einem unterschiedlichen Preis (vkPreis) und auch als Sonderangebot (preisRed) verkauft werden. Der eigentliche Verkaufspreis ergibt sich daher aus vkPreis - preisRed. Der aktuelle Lagerbestand je Produkt und Filiale wird im Attribut bestand gespeichert.

4. Kunden werden durch eine eindeutige Kundennummer (kundeNr) identifiziert, haben einen Namen (name) und sind einer Bonitätsstufe (bonStufe 'A', 'B' oder 'C') zugeordnet.

5. Einkäufe von Kunden werden auf Rechnungen abgerechnet. Je Rechnung wird festgehalten, ob diese schon bezahlt wurde (bezahlt 'Y' oder 'N'). Jede Rechnung umfasst mehrere Rechnungspositionen. Es werden je Rechnungsposition ein Einzelpreis (einzelPreis) und die gekaufte Menge (menge) gespeichert.


Gesucht sind jene Kunden (Nummer, Name und Bonitätsstufe), die etwas gekauft haben aber kein einzige unbezahlte Rechnung haben.

Ich komme einfach auf keine Lösung die Sinn macht..... Verzweifle schon ein wenig.

Danke für euren Support

 

[itsy]

Öhm... Wenn ich nicht was übersehen hab und du nur Infos von Kunden (Nummer, Name und Bonitätsstufe) und Rechnung (Bezahlt ja/nein) brauchst ist das relativ simpel.

Du brauchst ne WHERE-Bedingung auf die Kundentabelle, die nur kundenNr selektiert, die keine unbezahlte Rechnung haben. Alle unbezahlten Rechnungen kannst du dir ja über ne Subquery ausgeben lassen):

kundenNr NOT IN (SELECT kundenNr FROM rechnung ...)

 

[Student1986.]

leider bin ich kompletter Neuling und weiß nicht wie es weitergeht.

Wäre sehr nett, wenn ich den ganzen Code bekommen könnte.

Danke

 

[itsy]

Dann mach mal die Übungen von diesem SQL-Tutorial. Ist nicht viel, aber gibt einen guten, ersten Einblick.

SELECT kundeNr, name, bonStufe
FROM kunde 
WHERE kundeNr NOT IN (SELECT kundeNr FROM rechnung WHERE bezahlt = 'N')

Du kannst in SQL anstatt bspw. WHERE kundeNr IN (1234, 5678) auch gegenüber einer anderen Subquery prüfen. D.h. das SELECT auf Rechnung holt alle kundeNr, die nicht bezahlt haben und das wird dann mit dem aktuellen Satz aus der Kunden-Tabelle verglichen. Jetzt noch negieren (NOT IN) und voila.

 

[Ephesus]

@itsy: Ich denke, dass da noch ein kleiner Fehler drin ist. Es werden auch Kunden angezeigt, die noch nichts gekauft haben.

Spoiler: Lösung

SELECT kundeNr, name, bonStufe
FROM kunde 
WHERE kundeNr IN (SELECT kundeNr FROM rechnung WHERE bezahlt = 'Y')

€dit sagt: Deine Lösung funktioniert auch. Not in mit einer, Subquery, die null zurückgibt, ergibt false. Ist allerdings nicht sofort ersichtlich.

 

[Drexel]

Deine Abfrage gibt nach meiner Einschätzung auch Kunden zurück, die unbezahlte Rechnungen haben, solange sie mind. eine bezahlte Rechnung haben.

 

[Ephesus]

stimmt Der Anti-Join von @itsy ist zusätzlich erforderlich.

Spoiler: korrigierte Lösung

SELECT kundeNr, name, bonStufe
FROM kunde 
WHERE kundeNr IN (SELECT kundeNr FROM rechnung WHERE bezahlt = 'Y')
AND kundeNr NOT IN (SELECT kundeNr FROM rechnung WHERE bezahlt = 'N')

 

[itsy]

@Ephesus: Stimmt, Kunden die keine Rechnung haben dürfen nicht angezeigt werden.

Ich glaube aber, dass bei deinem ersten JOIN (Überprüfung ob es für den Kunden eine Rechnung gibt), keine weitere Selektion gemacht werden darf (siehe überarbeitete Fassung unten).

Weil nach deiner Abfrage hätten wir das Problem, dass nur kunden angezeigt werden die min. 1 bezahlte Rechnung haben und min. 1 unbezahlte. Wenn es nur eine unbezahlte Rechnung gibt, wird der Kunde nicht angezeigt.

SELECT kundeNr, name, bonStufe
FROM kunde 
WHERE kundeNr IN (SELECT kundeNr FROM rechnung) -- Alle Kunden, die eine Rechnung haben 
AND kundeNr NOT IN (SELECT kundeNr FROM rechnung WHERE bezahlt = 'N') -- Aber sie dürfen keine einzige unbezahlte Rechnung haben

Mannomann, immer diese Trickfragen

Edit zum Edit: Puuh, in dem Fall (NOT IN gegen NULL) hätte ich ja Glück gehabt. Es explizit zu über eine weitere Bedigung auszuschließen ist wahrscheinlich aber sinnvoll. Grade falls mal irgendeine DB meint, NULL handling geringfügig anders zu implementieren.

 

[phil.]

Müssen (angehende) Programmierer nicht lesen können?
Ist das zu einfach, oder zu komplex geschrieben?
https://www.computerbase.de/forum/threads/fragen-hausaufgaben-co.1767679/