Braindump Loesungen

system · 28. Juli 2009 um 12:28

Disclaimer: Dieser Thread wurde aus dem alten Forum importiert. Daher werden eventuell nicht alle Formatierungen richtig angezeigt. Der ursprüngliche Thread beginnt im zweiten Post dieses Threads.

mantra · 28. Juli 2009 um 12:28

Braindump Loesungen
In der FSI ist ein Braindump der Klausur vom letzten Jahr ( http://fsi/dw/_media/pruefungen/bachelor/konzmod-braindump-20080723.pdf?id=pruefungen%3Abachelor%3Aindex&cache=cache ), und wir ham uns mal hingesetzt um die sql aufgabe zu loesen

erstmal die Tables erstellen und sachen einfuegen (nicht Teil der aufgabe, aber wenn man ausprobieren will obs alles passt…)

– erstellen der tabellen

CREATE TABLE KAFFEE (
KAFFEE VARCHAR(24) primary key,
KOSTEN int,
PREiS int
);

create table FILIALE (
filialID int primary key,
plz varchar(5),
ort varchar(30),
strasse varchar(30),
sitzplaetze int
);

create table verkaeufe(
kaffee references kaffee(kaffee) not null,
filialID references filiale(filialid) not null,
primary key (kaffee, filialid),
datum varchar(10),
anzahl int
);

–Befuellen

INSERT INTO kaffee values(‘aber’, ‘50’, ‘65’);
INSERT INTO kaffee values(‘hallo’, ‘40’, ‘55’);
INSERT INTO kaffee values(‘hallo1’, ‘140’, ‘155’);
INSERT INTO kaffee values(‘hallo2’, ‘240’, ‘255’);
INSERT INTO kaffee values(‘hallo3’, ‘340’, ‘355’);
INSERT INTO kaffee values(‘hallo4’, ‘440’, ‘455’);
INSERT INTO kaffee values(‘hallo5’, ‘540’, ‘555’);
INSERT INTO kaffee values(‘hallo6’, ‘640’, ‘655’);
INSERT INTO kaffee values(‘hallo7’, ‘740’, ‘755’);
INSERT INTO kaffee values(‘hallo8’, ‘840’, ‘855’);

INSERT INTO filiale values(‘1’, ‘73000’, ‘ort1’,‘strasse1’, ‘5’);
INSERT INTO filiale values(‘2’, ‘73002’, ‘ort2’,‘strasse2’, ‘7’);
INSERT INTO filiale values(‘3’, ‘73002’, ‘ort3’,‘strasse3’, ‘9’);
INSERT INTO filiale values(‘4’, ‘73004’, ‘ort4’,‘strasse4’, ‘11’);
INSERT INTO filiale values(‘5’, ‘73005’, ‘ort5’,‘strasse5’, ‘13’);
INSERT INTO filiale values(‘6’, ‘73006’, ‘ort6’,‘strasse6’, ‘15’);
INSERT INTO filiale values(‘7’, ‘73007’, ‘ort7’,‘strasse7’, ‘17’);
INSERT INTO filiale values(‘8’, ‘83000’, ‘ort8’,‘strasse8’, ‘19’);
INSERT INTO filiale values(‘9’, ‘93000’, ‘ort9’,‘strasse9’, ‘21’);

INSERT INTO VERKAEUFE values(‘aber’, ‘1’, ‘1999-05-31’, ‘106’);
INSERT INTO VERKAEUFE values(‘hallo’, ‘1’, ‘1999-05-30’, ‘109’);
INSERT INTO VERKAEUFE values(‘hallo1’, ‘2’, ‘1199-05-30’, ‘106’);
INSERT INTO VERKAEUFE values(‘aber’, ‘4’, ‘99-05-38’, ‘103’);
INSERT INTO VERKAEUFE values(‘aber’, ‘5’, ‘99-05-37’, ‘102’);
INSERT INTO VERKAEUFE values(‘hallo’, ‘6’, ‘99-05-36’, ‘101’);
INSERT INTO VERKAEUFE values(‘aber’, ‘7’, ‘99-05-35’, ‘103’);
INSERT INTO VERKAEUFE values(‘aber’, ‘8’, ‘99-05-34’, ‘109’);
INSERT INTO VERKAEUFE values(‘aber’, ‘9’, ‘99-05-12’, ‘19’);
INSERT INTO VERKAEUFE values(‘hallo1’, ‘10’, ‘99-05-23’, ‘140’);
INSERT INTO VERKAEUFE values(‘hallo’, ‘1’, ‘99-05-13’, ‘1’);
INSERT INTO VERKAEUFE values(‘hallo7’, ‘1’, ‘99-05-35’, ‘100’);

– die Loesungen

SELECT (preis - kosten) as rentabilitaet,
kaffee
FROM kaffee
order by rentabilitaet ASC;

SELECT sum(sitzplaetze) as gesamt,
plz
FROM filiale
where plz like ‘73___’
group by plz
order by plz;

create view sold as
select sum(anzahl) as verkauft,
kaffee
from verkaeufe
group by kaffee;

SELECT count() as rang,
k1.verkauft,
k1.kaffee
from sold k1,
sold k2
where k1.verkauft <= k2.verkauft
GROUP BY k1.kaffee,
k1.verkauft
having count() <= 3
order by rang asc;

drop view sold;

// der drop am ende ist nicht teil der Aufgabenstellung

als loesung sollte rauskommen:
RENTABILITAET KAFFEE

15 aber
15 hallo
15 hallo1
15 hallo2
15 hallo8
15 hallo4
15 hallo5
15 hallo6
15 hallo7
15 hallo3

10 rows selected

GESAMT PLZ

5 73000
16 73002
11 73004
13 73005
15 73006
17 73007

6 rows selected

create view succeeded.
RANG VERKAUFT KAFFEE

1 542 aber
2 210 hallo
3 106 hallo1

3 rows selected

drop view sold succeeded.

hoffe mal das hilft beim sql - spass

madcoma · 28. Juli 2009 um 12:39

hab das genauso gelöst.
wenn die aufgaben morgen auch auf dem level sind, muss man sich überhaupt keine gedanken machen

Puetz · 28. Juli 2009 um 13:03

super, danke!

Oliver · 28. Juli 2009 um 14:37

Hey Danke, echt cool

John_Galt · 28. Juli 2009 um 15:38

Die dritte Aufgabe lässt sich einfacher ohne Views lösen, ich weiß aber nicht, ob man das verwenden darf (letztes Jahr wars ja nicht erlaubt):

SELECT Kaffee, Anzahl, Rang
FROM (SELECT Kaffee, SUM(Anzahl) AS Anzahl, DENSE_RANK() OVER(ORDER BY SUM(anzahl) DESC)
AS Rang FROM Kaffee JOIN verkaeufe USING(Kaffee) GROUP BY Kaffee)
WHERE Rang <= 3;

KAFFEE ANZAHL RANG

aber 542 1
hallo 210 2
hallo1 106 3

Berschi · 28. Juli 2009 um 16:26

hab auch ne Frage.
Angenommen, gefragt ist nicht nach den TOP3, sondern den FLOP3, wie müsste man die Aufgabe dann abändern?

John_Galt · 28. Juli 2009 um 16:30

EDIT:

Funktioniert so nicht, sorry

Mago · 28. Juli 2009 um 16:39

könnte man nicht einfach nach k2 gruppieren und ausgeben?

Michi_D · 28. Juli 2009 um 16:59

einfach statt

where k1.verkauft <= k2.verkauft

where k1.verkauft >= k2.verkauft

=

Hätte ich mal gesagt…

madcoma · 28. Juli 2009 um 17:00

Genau, so funktionierts auch.

John_Galt · 28. Juli 2009 um 17:12

Da stimmt dann der Rang aber nicht mehr. Auf die schnelle sollte es so gehen:

select Rang, Verkauft, Kaffee FROM (SELECT count(*) as rang, k1.verkauft, k1.kaffee from sold k1, sold k2 where k1.verkauft <= k2.verkauft GROUP BY k1.kaffee, k1.verkauft order by rang desc) WHere rownum <=3 order by rang asc;

madcoma · 28. Juli 2009 um 17:17

warum stimmt dann der rang nicht mehr?
der schlechteste bekommt rang 1, alle besseren rang(vorgänger)+1

das ist doch korrekt?

John_Galt · 28. Juli 2009 um 17:33

Naja, das ist Definitionssache.
Es wurde nach den letzten drei gefragt. Und letzter ist halt letzter und nicht erster. Aber das kann man denke ich sehen wie man will.

madcoma · 28. Juli 2009 um 17:38

Stimmt, Ansichtssache.
Ich war von einer “Flop 3” ausgegangen, wo der “Gewinner” der schlechteste ist.

sotsoguk · 28. Juli 2009 um 20:30

so würde ich die FLOP 3 machen (also 3. wär jetzt der schlechteste)

select u1.name, u1.sell, 4 - count() as Rang
from umsatz u1, umsatz u2
where u1.sell >= u2.sell
group by u1.name
having (Rang >0 and Rang <=3)
[i]having ( 4 - count() > 0 and 4 - count(*) <=3)[/i]
order by Rang;

kann ich in der having clause aliase nehmen oder nicht? weil in der übung wurde anscheind gesagt es geht nicht, aber mit mysql geht die abfrage und auch im internet unter dem allgemeinen begriff “sql” hab ichs so gesehen

EDIT: okay anscheinend is das allgemein nicht erlaubt, also keine aliases in der having clause.

neverpanic · 28. Juli 2009 um 21:41

Ähnlich kann man auch FLOP N mit den richtigen Rangnummern machen (ungetestet, falls jemand Zeit hat, kann ers ja ausprobieren):

EDIT: Code ist Bullshit!

SELECT u1.name , u1.sell , COUNT(u1.*) - COUNT(u2.*) + 1 AS rang FROM umsatz u1 , umsatz u2 WHERE u1.sell >= u2.sell GROUP BY u1.name HAVING COUNT(u2.*) <= 3 ORDER BY rang;

[quote=sotsoguk]kann ich in der having clause aliase nehmen oder nicht? weil in der übung wurde anscheind gesagt es geht nicht, aber mit mysql geht die abfrage und auch im internet unter dem allgemeinen begriff “sql” hab ichs so gesehen[/quote]Nein, kannst du nicht, ist ein Spezialfeature von MySQL. Immer im Kopf behalten: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. In allen Teilen, die nach SELECT kommen funktionieren die Aliasse laut Standard.

sotsoguk · 28. Juli 2009 um 21:56

bei mir mit mySql gehts nicht, aber wird wahrscheinlich an mySql liegen, da er count(u1.*) nicht annimmt.

andere frage noch

müsste ich nicht auch noch u1.sell mit in die group by klausel nehmen? ich dachte man muss jede nicht-Aggregatfunktion in group by
aufnehmen

neverpanic · 28. Juli 2009 um 22:01

[quote=sotsoguk]bei mir mit mySql gehts nicht, aber wird wahrscheinlich an mySql liegen, da er count(u1.*) nicht annimmt.[/quote]Gerade getestet, geht hier auch nicht, mal schauen, ob ich noch ne funktionierende Lösung finde.

[quote=sotsoguk]müsste ich nicht auch noch u1.sell mit in die group by klausel nehmen? ich dachte man muss jede nicht-Aggregatfunktion in group by aufnehmen[/quote]Ja.

neverpanic · 28. Juli 2009 um 22:14

Heureka! Flop 2 mit richtigen Rängen am Kaffee-Beispiel von oben:

CREATE VIEW sold AS
  SELECT
    SUM(anzahl) as anzahl
    , kaffee
  FROM
    verkaeufe
  GROUP BY
    kaffee;

SELECT
  s2.kaffee
  , s2.anzahl
  , COUNT(*) - s2.rang + 1 AS rang
FROM
  sold s1,
  (
    SELECT
      s1.kaffee
      , s1.anzahl
      , COUNT(*) AS rang
    FROM
      sold s1
    JOIN
      sold s2
    ON
      s1.anzahl >= s2.anzahl
    GROUP BY
      s1.kaffee,
      s1.anzahl
    HAVING
      COUNT(*) <= 2
    ORDER BY
      rang
  ) s2
GROUP BY
  s2.kaffee,
  s2.anzahl,
  s2.rang
ORDER BY
  rang;

DROP VIEW sold;

IMHO über Klausurniveau. Auf das fertige Ergebnis nochmal Kreuzprodukt mit der Original-Tabelle und Gruppieren nach der FlopN-Anfrage, damit man mittels COUNT(*) die Anzahl der Datensätze in der Original-Tabelle zur verfügung hat.