Aufgabe zu Datenbankabfrage und SQL

[askan]

Hallo Datenbank-Kenner und -Profis,

ich habe eine Aufgabe zu Datenbankabfragen mit der ich leider nicht fertig werde.
Könnt ihr mir bitte behiflich sein???

Ich habe eine Database-Aufgabe zu einer Autowerkstatt. Dabei habe ich bereits Datenbanken zu dem Hersteller, Kunde, Mechaniker, KFZ-Modell, Reparatur, Rechnung, Ersatzteil erstellt. Nun muss ich folgende Abfragen ausführen, bei denen ich leider nicht weiterkomme:

-Welche Kunden haben im Jahr 2007 mehr als einmal denselben Wagen zur Reparatur gebracht?

-Welches Ersatzteil je Hersteller wurde im Jahr 2008 am häufigsten verwendet?

-Anzahl Kunden je Hersteller?

-Umsatz je Mechaniker?

-Anteil der Zahlungsarten in Abhängigkeit von der Rechnungshöhe
(in 100€-Gruppen)?

Ich danke im voraus für eure Hilfe.

[dbwizard]

Hallo Datenbank-Kenner und -Profis,

ich habe eine Aufgabe zu Datenbankabfragen mit der ich leider nicht fertig werde.
Könnt ihr mir bitte behiflich sein???

Ich habe eine Database-Aufgabe zu einer Autowerkstatt. Dabei habe ich bereits Datenbanken zu dem Hersteller, Kunde, Mechaniker, KFZ-Modell, Reparatur, Rechnung, Ersatzteil erstellt. Nun muss ich folgende Abfragen ausführen, bei denen ich leider nicht weiterkomme:

-Welche Kunden haben im Jahr 2007 mehr als einmal denselben Wagen zur Reparatur gebracht?

-Welches Ersatzteil je Hersteller wurde im Jahr 2008 am häufigsten verwendet?

-Anzahl Kunden je Hersteller?

-Umsatz je Mechaniker?

-Anteil der Zahlungsarten in Abhängigkeit von der Rechnungshöhe
(in 100€-Gruppen)?

Ich danke im voraus für eure Hilfe.

- Das Datenmodel dazu wäre hilfreich, ansonsten wir hier etwas "im Trüben" fischen
- Und ich nehme an, dass du Tabellen erstellt hast für deine Entitäten, und nicht einzelne Datenbanken, das wäre etwas zuviel des Guten...?
- Und hast du schon etwas probiert ?



Gruss

[Wiedehopf]

Hallo Askan, Ich hoffe, du hast inzwischen selbs nachgedacht. Dadurch lernt man besser, als wenn einem jemand die Arbeit abnimmt. Unter Datenbanken verstehst du sicher Tabellen. Die Tabelle KFZ-Modelle würde ich besser Kfz nennen. Sie sollte u. a. die Felder Kennzeichen, Hersteller_id, Modell_id und Kunde_id enthalten. Bei mit hat jede Tabelle als Primärschlüssel das Feld id. Die Fremdschlüssel haben den Namen der Tabelle plus _id. Ich mach das mal hier auch so.
Du hast nun 5 Aufgaben genannt. Die 1. löst man mit HAVING, etwa so:

SELECT Kunde_id FROM Reparatur GROUP BY kunde_id, Kfz_id HAVING count(*)>1

Dadurch erhälst du aber nur die Kunden_id-Nummern, der Kunden, die dasselbe Fahrzeug mehr als einmal zur Reparatur gebracht haben. Wenn du mehr Angaben zu den Kunden haben möchtst, braust du einen Join mit der Kundentabelle. Etwa so:

SELECT Kunde.name FROM Reparatur INNER JOIN Kunde on Reparatur.kunde_id=Kunde.id GROUP BY Kunde.name, Reparatur.Kfz_id HAVING count(*)>1

Zur 2. Abfrage:
Die Tabelle Ersatzteil sollte u.a. die Felder Teil_id, Menge und Reparatur_id enthalten, wobei alle Ersatzteile in der Tabelle Teil aufgelistet sind, denn bei der Erfassung der Reparaturdaten möchte der Monteur nicht die Bezeichnung des Teils eingeben, sondern aus einer Liste wählen. Ich gehe mal davon aus, dass die Hersteller_id des Ersatzteils mit in der Eratzteil-Tabelle steht. Das ist zwar redundant, vereinfacht aber die ohnehin komplizierte Abfrage. Mit:

SELECT Hersteller_id,Teil_id, sum(menge) as menge FROM Ersatzteile WHERE date_to_string(datum,'%Y')='2008' GROUP BY Hersteller_id,Teil_id

erhältst du für jeden Hersteller und jedes Teil die Mengensumme. Du Willst aber nur jeweils die höchste Summe. Dazu klammern wir die ganze Abfrage ein und betrachten sie wie eine Tabelle, man könnte auch implizite View dazu sagen, und ermittlen das Maximum (Teil_id können wir weglassen):

SELECT Hersteller_id,max(menge) as max_menge FROM ( SELECT Hersteller_id, sum(menge) as menge FROM Ersatzteile WHERE date_to_string(datum,'%Y')='2008' GROUP BY Hersteller_id,Teil_id ) GROUP BY Hersteller_id

Wir wollen aber Wissen, welche Teile das sind. Dazu gibt es bei Oracle die Vektor-Abfrage. Ich weiß nicht, ob das z. B. bei MySql auch so geht:

SELECT Hersteller_id, Teil_id,menge FROM (SELECT Hersteller_id,Teil_id, sum(menge) as menge FROM Ersatzteile WHERE date_to_string(datum,'%Y')='2008' GROUP BY Hersteller_id,Teil_id)
WHERE (Hersteller_id,menge) IN (SELECT Hersteller_id,max(menge) as max_menge FROM ( SELECT Hersteller_id, sum(menge) as menge FROM Ersatzteile WHERE date_to_string(datum,'%Y')='2008' GROUP BY Hersteller_id,Teil_id ) GROUP BY Hersteller_id)

Dann müsstest do noch je einen Join auf die Teile- und die Herstellertabelle machen. Wie das geht, hast du ja bei der ersten Aufgabe gesehen.
Ich habe mal auf meiner Oracle-Datenbank folgendes probiert:

SELECT Zahl, Zahl2,fl FROM (SELECT Zahl,Zahl2, sum(fl) as fl FROM Test GROUP BY Zahl,Zahl2)
WHERE (Zahl,fl) IN (SELECT Zahl,max(fl) as max_fl FROM ( SELECT Zahl, sum(fl) as fl FROM Test GROUP BY Zahl,Zahl2 ) GROUP BY Zahl)

Das hat geklappt.
Nun zur dritten Aufgabe:
Unter Hersteller habe ich bisher die Hersteller der Ersatzteile verstanden. Hier sind aber die Hersteller der Kfz gemeint. Die können, müssen aber nicht, identisch sein. Das ist schon einfacher:

SELECT Hersteller_id,count(distinct Kunde_id) as Anzahl FROM Reparatur GROUP BY Hersteller

Mit count(distinct… zählst du die unterschiedlichen Kundennummern.
Aufgabe 4:
Umsatz je Mechaniker müsstest du, wenn du das bisherige verstanden hast, selbt hinkriegen:
Wir gehen davon aus, dass in jeder Rechnung die id-Nr. der Reparatur steht, und in der Reparatur-Tabelle steht die Nr. des Mechanikers. Wir verbinden also die drei Tabellen. Ich mach das mal so, wie das bei Oracle üblich ist:

SELECT Mechaniker.Name, sum(Rechnung.Betrag) as Umsatz FROM Mechaniker, Rechnung, Reparatur WHERE Rechnung.Reparatur_id=Reparatur.id AND Reparatur.Mechaniker_id=Mechaniker.id GROUP BY Mechaniker.Name

Für die letzte Aufgabe braucht man die Funktion floor, um die Rechnungsbeträge in 100€-Schritte einzuteilen. Etwa so:

SELECT count(distinc Zahlungsart) as Anzahl, floor(Betrag/100) FROM Rechnung GROUP BY floor(Betrag/100)

Um die Ausgabe etwas besser zu gestalten könnte man auch Schreiben:

SELECT count(distinc Zahlungsart) as Anzahl, 'von '||floor(Betrag/100)*100||' bis '||ceil( Betrag/100)*100-0.01 FROM Rechnung GROUP BY floor(Betrag/100)

So das war's. Du hattest Glück, dass ich etwas Zeit hatte. Probier mal, was auf deiner Datenbank anders sein muss. Ich weis ja gar nicht, welche Datenbank du verwendest.