SQL Abfrage aus GA1 Winter 07/08 - FiAE

[Cleriker]

hi!

aus oben genannter prüfung geht folgende aufgabe hervor:

-- Ertrag

datum		anlagen_ID		tagesertrag(1)

11-11-2007	A1004			3,8

11-11-2007	A1005			5,1

...



-- SonnenStd

datum		plz			sonnenstunden

11-11-2007	51491			4

11-11-2007	79123			6

...


-- Anlage

anlagen_ID	plz			maxertrag(2)

A1004		51491			1,0

A1005		79123			1,2

...	


(1) = Tagesertrag in kWh

(2) = Maximal möglicher Ertrag pro Sonnenstunde in kWh


Erstellen Sie jeweils eine SQL-Abfrage, die

a) alle IDs der Anlagen auflistet, deren Tagesertrag am 11.11.2007 mehr als 20% unter

   dem maximal möglichen Tagesertrag liegt (9P.)

 die IDs aller Anlagen und deren jeweilige Summe der 

   Tageserträge im Zeitraum vom 01.10 bis 31.10.2007 auflistet (9P.)


c) die IDs der Anlagen am Ort mit der PLZ 51491 auflistet, die am 11.11.2007 

   unter dem Durchschnittsertrag aller Anlagen an diesem Ort und Tag liegen. (7P.)

[/code]

HF... für Lösungsvorschläge bin ich sehr dankbar :old

[Martin1983]

Ich frickel das mal als Übung unter dem prüfungsüblichen Zeitdruck zusammen ;p Kann falsch sein, das hätte ich aber hingeschrieben würde ich heute die Prüfung schreiben

Aufgabe a)

SELECT anlagen_ID FROM Anlage a

    JOIN Ertrag e USING (anlagen_ID)

    JOIN SonnenStd s USING (datum)

    WHERE datum = '11-11-2007'

    GROUP BY anlagen_ID

        HAVING e.tagesertrag < ((s.sonnenstunden * a.maxertrag) * 0.8);

Aufgabe

SELECT anlagen_ID, SUM(e.tagesertrag) FROM Anlage a

    JOIN Ertrag e USING (anlagen_ID)

    WHERE e.datum BETWEEN '01-10-2007'

        AND '31-10-2007'

    GROUP BY anlagen_ID;

Aufgabe c)

SELECT anlagen_ID FROM Anlage a

    JOIN Ertrag e USING (anlagen_ID)

    WHERE a.plz = '51491'

        AND e.datum = '11-11-2007'

    GROUP BY anlagen_ID

        HAVING e.tagesertrag < (SUM(e.tagesertrag) / COUNT(e.anlagen_ID));

Bearbeitet 6. Mai 2008 von Martin1983

[Cleriker]

mit dem join komm ich ja garnich klar, kannst du mir das erklären bitte?

[Martin1983]

mit dem join komm ich ja garnich klar, kannst du mir das erklären bitte?

Es gibt verschiedene Arten Tabellen zu verknüpfen. Die in den IHK-Lösungen gerne verwendete Lösung ist der sogenannte Old-School-Join, hierbei verknüpfst du mittels der WHERE Bedingung:

SELECT test1.dritte_spalte, test2.irgendeine_spalte FROM test1

    WHERE test1.id = test2.id;

Heutzutage wird jedoch in der Regel mit der JOIN-Anweisung gejoined, hierbei gibts es dann folgende 2 Möglichkeiten: Einmal mit USING was du verwendest wenn die Bezeichnungen der beiden Spalten über die die Tabellen verknüpft werden sollen die gleiche Bezeichnung haben (Wichtig: Spaltenname in Klammern, sonst ists mein ich syntaktisch falsch!!!):

SELECT test1.dritte_spalte test2.irgendeine_spalte FROM test1

    JOIN test2 USING (id);

Wenn du jedoch 2 unterschiedliche Spaltennamen hast über die du verknüpfst verwendest du ON:

SELECT test1.dritte_spalte test2.irgendeine_spalte FROM test1

    JOIN test2 ON (test1.id = test2.irgendeine_andere_spalte);

[Cleriker]

ich glaub ich versag morgen in GA1.... hab grad die arbeit versucht fertig zu machen. mitm sequenzdiagramm komm ich net wirklich klar und bis auf die zwei handlungsschritte mit dem ERM und nem struktogramm siehts echt düster aus...

[E=mc²]

@Martin1983: musst du nich INNERJOIN verwenden anstelle von JOIN?

machts eigentlich einen unterschied ob ich schreibe:

test1 JOIN test2

oder

test2 JOIN test1

?

[Martin1983]

Ich hab keine Ahnung ) Das ist wie gesagt die Lösung die ich hinschreiben würde, was nicht heisst das sie richtig ist Auch wenn ich meiner Meinung nach dafür sehr viele Punkte verdient hätte ^^

[dunker]

Beim INNER JOIN müsste das egal sein.

Beim Outer Jedoch nicht.

tbl1 LEFT OUTER JOIN tbl2

tbl2 LEFT OUTER JOIN tbl1

mfg

[SoL_Psycho]

Kannst statt Left Outer Join auch einfach Left Join schreiben

Tabelle1 Left Join Tabelle 2 == Tabelle 2 Right Join Tabelle1

Afaik

P.S.: Ja beim Inner Join ists egal, da du mengentechnisch nur die Schnittmenge der Tabellen abfragst, daher ist es egal welche Tabelle du mit welcher "verinnerjoinst"

[dunker]

bei mysql geht das weis nicht wie es in anderen DBMs ausshaut.

Oracle verlangt glaub ich das outer.

in der prüfung denk ich ist beides richtig.

mfg

[SoL_Psycho]

Hmm sicher?

Dachte das hätten mittlerweile alle implementiert, weil Left Joins doch immer Left Outer Joins sind? :confused:

Also MSSQL Server kennt afaik nur Left Joins, keine Left Outer (mehr)...

[dunker]

also unser Daten bank Lehrer meinte das es egal ist wie wir es in der Prüfung schreiben.

ausserdem kommt outer join in der AP dran bzw ist noch nie dran gekommen.

es kann natürlich sein das mittlerweile alle DBMs ünerstützen.

mfg