SQL Beispiele 1-20

[gst]

die (hoffentlich richtigen ;) loesungen zu den beispielen 1-20:

1) SELECT DISTINCT gebiet FROM Klassifikation;

2) SELECT Nachname, Jahr FROM Kunde WHERE Status = 'Student' AND Jahr <= 1970;

3) SELECT Titel FROM Buch WHERE Titel LIKE 'K%' OR Titel LIKE 'L%';

4) SELECT titel,anzahl FROM (SELECT exemplar.isbn, Count(InvNr) AS anzahl FROM Exemplar WHERE RegalNr = 5 GROUP BY exemplar.isbn) AS foo, buch WHERE foo.isbn=buch.isbn;

(anm 4: aus der fragestellung geht nicht klar hervor was die loesung ausgeben soll (siehe thread "Bsp 4"))

5) SELECT titel,kunde.knr,nachname,von FROM ausleihe,exemplar,buch,kunde WHERE ausleihe.invnr = exemplar.invnr AND exemplar.isbn = buch.isbn AND ausleihe.knr=kunde.knr AND bis IS NULL;

6) SELECT titel,kunde.knr,nachname,von FROM ausleihe,exemplar,buch,kunde WHERE ausleihe.invnr = exemplar.invnr AND exemplar.isbn = buch.isbn AND ausleihe.knr=kunde.knr AND bis IS NULL AND von < '2002-01-01';

7) SELECT vorname,nachname FROM kunde WHERE knr NOT IN (SELECT knr FROM ausleihe);

8) SELECT nachname,vorname FROM autor WHERE isbn IN (SELECT isbn FROM klassifikation WHERE gebiet = 'Mathematik');

9) SELECT DISTINCT nachname,jahr FROM exemplar,ausleihe,kunde WHERE exemplar.invnr = ausleihe.invnr AND ausleihe.knr = kunde.knr AND isbn IN (SELECT isbn FROM klassifikation WHERE gebiet = 'Mathematik');

10) SELECT nachname,vorname,knr FROM kunde where knr NOT IN (SELECT kunde.knr FROM exemplar,ausleihe,kunde WHERE exemplar.invnr = ausleihe.invnr AND ausleihe.knr = kunde.knr AND isbn IN (SELECT isbn FROM klassifikation WHERE gebiet = 'EDV'));

(anm zu 10: in aufgabe 9 das "IS" zu "IS NOT" aendern funktioniert nicht da es auch matchen wuerde wenn der user min. ein buch aus einem anderen gebiet ausgeborgt haette).

11) UPDATED: SELECT Gebiet,COUNT(exemplar.invnr) FROM ausleihe,exemplar,klassifikation WHERE ausleihe.invnr = exemplar.invnr AND klassifikation.isbn = exemplar.isbn AND bis IS NULL GROUP BY gebiet;

12) noch keine loesung

13) SELECT regalnr, COUNT(regalnr) AS anzahl FROM exemplar WHERE invnr NOT IN (SELECT invnr FROM ausleihe WHERE bis IS NULL) GROUP BY regalnr;

14) SELECT regalnr,SUM(kaufpreis) AS gesamtpreis, AVG(kaufpreis) AS durchschnittspreis FROM exemplar GROUP BY regalnr;

15) SELECT invnr,buch.isbn,titel,kaufpreis FROM exemplar,buch WHERE exemplar.isbn=buch.isbn AND kaufpreis >= (SELECT MAX(kaufpreis) FROM exemplar);

16) SELECT nachname,vorname FROM (SELECT knr,COUNT(knr) AS entlehnnr FROM ausleihe GROUP BY knr) AS kundenentlehn, kunde WHERE entlehnnr >=2 AND kunde.knr = kundenentlehn.knr;

17) SELECT * FROM (SELECT nachname,vorname,COUNT(*) AS anzahl FROM ausleihe,exemplar,autor WHERE autor.isbn= exemplar.isbn AND ausleihe.invnr = exemplar.invnr GROUP BY nachname, vorname) AS autcount2 WHERE autcount2.anzahl >= (SELECT MAX(anzahl) FROM (SELECT COUNT(*) AS anzahl FROM ausleihe,exemplar,autor WHERE autor.isbn= exemplar.isbn AND ausleihe.invnr = exemplar.invnr GROUP BY nachname, vorname) AS autcount);

(anm: irgendwie sieht die loesung zu bsp 17 ziemlich kompliziert aus - gibts da ev. eine einfachere loesung?)

18) FALSCH: (SELECT insgesamt.isbn,insgesamt.anzahl - ausgeborgt.anzahl AS vorhanden FROM (SELECT isbn,COUNT(ausleihe.invnr) AS anzahl from ausleihe,exemplar WHERE bis IS NULL AND exemplar.invnr = ausleihe.invnr GROUP BY isbn) AS ausgeborgt, (SELECT isbn,COUNT(invnr) AS anzahl FROM exemplar GROUP BY isbn) AS insgesamt WHERE ausgeborgt.isbn=insgesamt.isbn);

(anm: loesung zu 18 ist falsch, da sie nur buecher auflistet die gerade ausgeborgt sind und > 1mal vorhanden sind, hab sie trotzdem einmal gepostet, ev. hat ja wer lust die loesung zu fixen ;)

19) SELECT verlag,SUM(kaufpreis) AS gesamtkosten FROM exemplar,buch WHERE exemplar.isbn = buch.isbn GROUP BY verlag;

20) noch keine loesung

/gst

[PliniusSecundus]

4) SELECT Titel, Count(InvNr) AS anzahl FROM Exemplar,Buch WHERE Exemplar.ISBN = Buch.ISBN AND RegalNr = 5 GROUP BY Titel;
Es können auch Bücher gleichen Titels in einem Regal stehen, die aber nicht gleiche ISBN haben - also verschiedene Bücher sind - Ich würde daher sagen GROUP BY ISBN.

[gst]

Original geschrieben von PliniusSecundus

Es können auch Bücher gleichen Titels in einem Regal stehen, die aber nicht gleiche ISBN haben - also verschiedene Bücher sind - Ich würde daher sagen GROUP BY ISBN.

hmmm... stimmt, das verkompliziert aber dann den gesamten query (oder ich bin heut einfach schon zu muede).

einfach nach isbn group'en kann man ja nicht, da man dann in diesem fall keinen select nach dem titel machen koennte.

SELECT titel,anzahl FROM (SELECT exemplar.isbn, Count(InvNr) AS anzahl FROM Exemplar WHERE RegalNr = 5 GROUP BY exemplar.isbn) AS foo, buch WHERE foo.isbn=buch.isbn;

sollte mit diesem query das richtige ergebnis liefern, ev. gehts auch einfacher...

[nd19]

hmm...

ich habe zwar keine ahnungob das stimmt, aber ich habe das viel einfacher.

select Titel, count(InvNr) as anzahl
from Exemplar, Buch
where RegalNr= 5
group by Titel;

da bekomme ich 14 Bücher raus von denen jeweils 4 im regal 5 sein sollten (egal ob ausgeliehen oder nicht)

bye,
ND

[LordOfTheBite]

jep, das hab ich auch bei bsp 4

bei bsp 11 müssen alle klassifikationsgebiete angezeigt werden, also auch jene, wo kein buch dazu jemals ausgeliehen wurde:


select
Gebiet, count(InvNr)-1 as Anzahl
from
(
(
select
k.Gebiet, e.InvNr
from
Klassifikation k
natural join Exemplar e
natural join Ausleihe a
where
a.bis is null
) union (
select
Gebiet, 0
from
Klassifikation
)
) as plus
group by
Gebiet;

[LordOfTheBite]

achja, query 12:

select
textcat(k.Nachname,textcat(', '::varchar,k.Vorname)) as Name
from
Kunde k
natural join Ausleihe a
natural join Exemplar e
natural join Klassifikation kl
group by
k.KNr, k.Vorname, k.Nachname
having
count(distinct kl.Gebiet) >= all (
select
count(distinct klass.Gebiet)
from
Klassifikation klass
);




es ist anzunehmen, dass gefragt werde wird wie denn die eine oder andere query funktioniert

in eigenem interesse deshalb nur die queries abschreiben, die man wirklich versteht (100%-ig ;) versteht)

peter

[Zentor]

zu 12,20 hab ich
12.) select *
from Kunden k
where not exists(select Gebiet
from Klassifikation l, Exemplar e, Ausleihe a
where a.Knr = k.Knr and
e.InvNr = a.InvNr and
and l.Gebiet not in (select Gebiet
from Klassifikation
)
)
20.) select Telefon, Nachname, Vorname
from Kunde
where KNr in ( select KNr
from Ausleihe
where InvKn in (select InvKn
from Exemplar
where ISBN in ( select ISBN
from Autor
where Vorname = 'Karl' and Nachname= 'May'
)
)
)
ansonsten abweichende Lösungen von mir sind noch:
4.) select Buch.Titel, count(Exemplar.ISBN)
from Buch,Exemplar
where Exemplar.RegalNR = 5 and Buch.ISBN = Exemplar.ISBN
group by Buch.Titel

11.) select distinct Gebiet, count(Gebiet)
from Klassifikation
where ISBN in ( select ISBN
from Exemplar
where InvNR in (select InvNR
from Ausleihe
where bis = null
)
)

13.) select distinct RegalNr, count(RegalNr)
from Exemplar
where InvNR in (select InvNR
from Ausleihe
where bis = null
)

16.) select Nachname, Vorname
from Kunde
where Knr in ( select Knr
from Ausleihe
where count(KNr)>=10
)

17.) select distinct Autor.*,count(Ausleihe.InvNr)
from Autor,Ausleihe
where Ausleihe.von not null

18.) select InvNR,ISBN
from Exemplar
where count(ISBN) > 1
weis nicht was davon zu gebrauchen ist :D aber 12 is ziemlich wahrscheinlich falsch. Haben das mit dem SELECT (...) AS noch nicht versucht mal schaun ob sich was besseres ergibt.
mfg Zentor

[LordOfTheBite]

Original geschrieben von Zentor
zu 12,20 hab ich
12.) select *
from Kunden k
where not exists(select Gebiet
from Klassifikation l, Exemplar e, Ausleihe a
where a.Knr = k.Knr and
e.InvNr = a.InvNr and
and l.Gebiet not in (select Gebiet
from Klassifikation
)
)

weis nicht was davon zu gebrauchen ist :D aber 12 is ziemlich wahrscheinlich falsch. Haben das mit dem SELECT (...) AS noch nicht versucht mal schaun ob sich was besseres ergibt.
mfg Zentor

ich bin mir ziemlich sicher, dass deine innere query select gebiet from ... immer null ergibt, weil ja die bedingung ist, dass das gebiet nicht in der liste aller gebiete sein muss.

also kommt bei der inneren query immer 0 rows heraus und es werden immer alle kunden angezeigt

[Dimitrij]

Original geschrieben von LordOfTheBite

bei bsp 11 müssen alle klassifikationsgebiete angezeigt werden, also auch jene, wo kein buch dazu jemals ausgeliehen wurde:



Danke für den Hinweis. Das hatte ich auch falsch.
Ich hab mir eine andere Lösung überlegt, hoffentlich stimmt's:

select gebiet,count(invnr) as anzahl
from
Klassifikation
natural left join
(Exemplar natural join Ausleihe)
where bis is null
group by gebiet;

[Dimitrij]

Ich glaub, eine mögliche Lösung für Bsp 12 wäre:


select K.knr, K.Nachname, K.Vorname
from Kunde K natural join
(
select B.knr,count(B.gebiet) as anzahl
from
(
select distinct knr,gebiet
from Ausleihe natural join Exemplar natural join Klassifikation
)B
group by B.knr
)A
where
(
A.anzahl=
(
select count(gebiet)
from
(
select distinct gebiet
from klassifikation
)C
)
);

[MarvinTheRobot]

15) SELECT invnr,buch.isbn,titel,kaufpreis FROM exemplar,buch WHERE exemplar.isbn=buch.isbn AND kaufpreis >= (SELECT MAX(kaufpreis) FROM exemplar);

Muss ich da >= verwenden? MAX selektiert mir ja schon den höchsten wert. da müsste doch = auch reichen oder?

mfg, Phil.

[Cha]

Könnte Bsp 18 vielleicht so aussehen?

select InvNr, ISBN from Exemplar
where exists (select RegalNr, count(RegalNr) as Anzahl from Exemplar where InvNr not in (select InvNR
from Ausleihe where bis = null) group by RegalNr
having count(RegalNr) > 1);


es (sollte) listet alle Exemplare auf, die mehr als 1mal vorhanden sind (also ausgeborgte nicht!! -- es steht ja "alle vorhandenen")

Lg

[skytale]

Frage: Warum kann ich bei 13 count(RegalNr) schreiben wenn ich nach RegalNr gruppiere und muss nicht count(InvNr) schreiben??

@18 Könnt das nicht eine Lösung sein??
select InvNr, ISBN
from Buch, Exemplar
where Buch.ISBN=Exemplar.ISBN
group by Buch.ISBN
having count(InvNr)>1

[oskar]

@13

Es muss ja auch das Regal angezeigt werden, welches 0 Exemplare hat!

Mein Vorschlag:

SELECT Exemplar.regalnr, Count(exemplar.invnr) - Count(ausleihe.invnr) as Anzahl
FROM Exemplar LEFT JOIN Ausleihe using (invnr)
GROUP BY Exemplar.regalnr;

[Dimitrij]

Original geschrieben von oskar
@13

Es muss ja auch das Regal angezeigt werden, welches 0 Exemplare hat!

Mein Vorschlag:

SELECT Exemplar.regalnr, Count(exemplar.invnr) - Count(ausleihe.invnr) as Anzahl
FROM Exemplar LEFT JOIN Ausleihe using (invnr)
GROUP BY Exemplar.regalnr;

ich glaub, das funktioniert nicht immer richtig, denn die Zahl, die du abziehst, stimmt nicht immer.
du musst berücksichtigen, dass in der Relation "Ausleihe" nicht nur die Exemplare stehen, die derzeit entlehnt sind, sondern alle, die jemals entlehnt wurden.
wenn du hier http://websql.dbai.tuwien.ac.at/dmlu/students.php?server=1&db=dmlu
deine Anfrage mit der Anfrage

select * from exemplar left join ausleihe using(invnr);

vergleichst, siehst du, dass deine Anfrage nicht so ganz funktioniert.

Ich hoffe, ich irre nicht. Wenn doch, tut's mir leid. Ich bin noch Datenbank-Anfänger.

[Guinness]

folgende beispiele sind meiner meinung in diesem thread bisher falsch gepostet worden:

beispiel 4


select b.titel, count(e.invnr)
from buch b, exemplar e
where b.isbn=e.isbn
group by b.titel, e.regalnr
having e.regalnr=5


beispiel 18


select distinct e1.invnr, e1.isbn
from exemplar e1
where exists (select e2.invnr, e2.isbn
from exemplar e2
where e1.isbn=e2.isbn and e1.invnr<>e2.invnr)


bitte vergleicht einmal und schreibt mir, was ihr davon haltet!
danke!
ciao, guinness

[qmp]

@Guinness:
Bsp4: Du solltest nicht nach Titel gruppieren, da der nicht eindeutig ist (es kann Bücher mit gleichem Titel aber unterschiedlicher ISBN geben).
siehe Lösung von gst weiter oben

Bsp18: müsste stimmen



Bsp13: mein Vorschlag
SELECT DISTINCT E2.RegalNR, (SELECT Count(*)
FROM Exemplar E
WHERE E.RegalNR=E2.RegalNR AND NOT EXISTS (SELECT * FROM Ausleihe A WHERE A.InvNR=E.InvNR AND A.bis IS NULL)) AS Anzahl
FROM Exemplar E2

Bsp19: es ist auch möglich dass von einem Verlag keine Exemplare vorhanden sind => gesamtkosten=0

SELECT Verlag,Sum(Gesamtkosten) FROM
((SELECT B.Verlag,Sum(E.Kaufpreis) AS Gesamtkosten
FROM Buch B INNER JOIN Exemplar E ON E.ISBN=B.ISBN GROUP BY B.Verlag)
UNION
(SELECT DISTINCT B2.Verlag, 0 FROM Buch B2)) AS Alle
GROUP BY Verlag

[Kenny]

hat er nicht gestern gesagt alle bsp lassen sich mit den einfachsten sql-befehlen lösen, ohne outer join, left join , union usw solche sachen... ?!

[skytale]

Wenn ich das Schema anwende: "Alle Kunden, die aus keinem Klassifikationsgebiet nicht ein Buch ausgeborgt haben"
Könnte dann das folgende Statement stimmen??

select K.Vorname, K.Nachname
from Kunde K
where not exists(select Gebiet
from Klassifikation
where Gebiet not in(select KL.Gebiet from Ausleihe A, Exemplar E, Klassifikation KL
where A.InvNr=E.InvNr and E.ISBN=KL.ISBN and A.Knr=K.KNr))

[tricipitinus]

jou, also das hab ich auch so verstanden..
lieber einfache queries, dafür massig, damit man routine gewinnt ;)

[LordOfTheBite]

@kenny:

wenn er das gesagt hat, dann hat er die angabe einfacher interpretiert als wir ;)

[Kenny]

ja das glaub ich auch, hier wird oft einiges komplizierter interpretiert ...

keep simple :)


http://stud3.tuwien.ac.at/%7Ee0025798/infoworx/banner.gif

[thita]

ad beispiel 16: mein lösungsvorschlag:

<code>select nachname, vorname from kunde where kunde.knr=ausleihe.knr group by nachname,vorname
having count(nachname)>=10<code>

[thita]

Original geschrieben von gst
die (hoffentlich richtigen ;) loesungen zu den beispielen 1-20:

16) SELECT nachname,vorname FROM (SELECT knr,COUNT(knr) AS entlehnnr FROM ausleihe GROUP BY knr) AS kundenentlehn, kunde WHERE entlehnnr >=2 AND kunde.knr = kundenentlehn.knr;

/gst


...ich glaub 7 kunden mit mehr od. gleich 10 entlehnungen ist doch zu viel...oder? ....wenn ich mir alle tupel bei ausleihe so ansehe und ich auf die kundennummer schau, können es meiner meinung nach nur 2 kunden sein!

[RS250]

Hi, glaub ich hab eine recht gute Lösung für 16:

select Nachname, Vorname
from Kunde
where KNr in
(select KNr
from Ausleihe
Group by KNr
having count(Knr)>=10);

Da kommen mir genau 2 Kunden raus und das sollt eigentlich stimmen!

[RS250]

Ist zwar vielleicht etwas verwirrend, gibt aber glaub ich das richtige aus!

select Nachname, Vorname
from Autor A, Exemplar E
where A.ISBN = E.ISBN and InvNr in
(select Invnr
from Ausleihe
Group by InvnR
having count(InvNr) >= all (select count(InvNr)
from Ausleihe
Group by InvNr));

Die Lösung die für Bsp. 17 schon geposted ist, gibt als Anzahl der Entlehnungen 23 aus, die am häufigsten ausgeliehenen Bücher wurden aber nur 6 Mal ausgeliehen, hoffe das das stimmt was ich da von mir gebe, glaub aber schon!

[offline]

könnte so einfach?

select Vorname , Nachname ,Telefon from kunde K where not
exists((select distinct isbn from autor where vorname='Karl' and
nachname='May')
except
(select distinct E.isbn from ausleihe A, exemplar E , autor Y where
A.invnr=E.invnr and E.isbn=Y.isbn and vorname='Karl' and
nachname='May'));

[Geli]

@RS 250 Bsp17

ich glaub nicht, dass das so passt. Weil der Karl May eindeutig am öftesten augeborgt wurde.
Was dein Code macht, so weit ich das versteh, is ja für jedes einzelne Exemplar die Anzahl der Entlehnungen berechnen. Aber für eine Autor gibt's ja mehrere Exemplare.

Ich hab allerdings auch noch keinen Verbesserungsvorschlag.

lg, Geli

[Geli]

Mein Vorschlag zu dem Beispiel wär das hier.


select RegalNr, count (InvNr)-1 as vorhanden
from ( select RegalNr, InvNr
from Exemplar
where InvNr not in
( select InvNr
from Ausleihe
where bis = null)

union

select RegalNr, 0
from Exemplar
) as R1

group by RegalNr

das "union" is drin, weil es ja auch Regale geben Könnt, von denen alle Bücher ausgeborgt worden sind.

lg, Geli

[twenty]

@ BSP10
ich hab da eine etwas einfachere Query:

Select Kunde.Nachname, Kunde.Vorname, Kunde.KNr
From Kunde
Where Kunde.KNr not in (
Select A.KNr
From Ausleihe A, Klassifikation Kl, Exemplar E
Where A.InvNr = E.InvNr AND E.ISBN = Kl.ISBN AND Kl.Gebiet like '%elletristik' )

unterschied zu gst ist, dass auch Leute in die Query einbezogen werden, die noch nie ein Buch ausgelieben haben (die haben auch definitiv noch kein belletristisches Buch ausgeliehen)

muss man eigentlich immer
... like '%athematik' usw....
schreiben statt ganz einfach
....= 'Mathematik' ...
, auch wenn es Einträge mit 'mathematik' gibt (in der Frage wird Mathematik großgeschrieben)

wie seht ihr das ?

[RS250]

@ Geli

hast leider recht, aber jetzt fällt mir auch nix mehr ein!

[RS250]

könnt das funktionieren???

select Vorname, Nachname, Telefon
from Kunde
where Knr in
(select KNr
from Ausleihe
where InvNr in
(select InvNr
from Exemplar
where ISBN in
(select ISBN
from Autor
where Vorname = 'Karl' and Nachname = 'May'
)
)
)

[Firefox]

Hallo Leute!

Ihr setzt SubSelects sehr extensiv ein, was meiner Meinung nach die ganze Sache teilweise unnötig verkompliziert....

Ich poste mal meine komplette Lösung als Word, bin natürlich für jegliche Art von Kritik offen!

LG

Firefox

[jeuneS2]

Meine Lösung, die einer bereits geposteten ziemlich ähnlich ist, aber noch ein bissi einfacher:


select Telefon, Nachname, Vorname
from Kunde k

where not exists
((select ISBN
from Autor
where Nachname='May'
and Vorname='Karl')
except
(select ISBN
from Exemplar natural join Ausleihe
where Knr=k.Knr)
)


oder gibts da einen Haken?

[Lexsa]

Tschuldigung, aber kann mir da irgendwer bei meinem Denkfehler helfen?
Ich habe als Eingabe nämlich folgendes:

select distinct B.Titel, K.Knr, K.Vorname, K.Nachname, A.von
from Buch B, Exemplar E, Ausleihe A, Kunde K
where A.InvNr=E.InvNr and E.ISBN=B.ISBN and A.KNr=K.KNr and A.von in (select von
from Ausleihe A
where bis = null)

Eigentlich würd ich sagen, dass das nur eine andere verschachtelte Aufforderung von eurem ist, doch es ergibt ein vollkommen anderes Ergebnis.
Außerdem bleiben bei eurer Eingabe ja auch drei Felder bei von frei - doch das würde doch bedeuten, dieses Buch ist nicht entlehnt.
Da ich jedoch der Mehrheit vertraue, nehm ich an, ich hab einfach nur nen Denkfehler, wär also nett, wenn mir da wer helfen könnt.

[LordOfTheBite]

@lexsa:
ich denke das distinct ist falsch

und wieso brauchst du da überhaupt ein subselect ?
imho ginge das ganz einfach so:


select
b.titel, k.knr, k.Nachname, a.von
from
buch b, exemplar e, ausleihe a, kunde k
where
b.isbn=e.isbn and e.invnr=a.invnr and a.bis is null and k.knr=a.knr;


edit:

achja, wenn du per von selektierst, kriegst du auch bücher die an tagen ausgeliehen wurden wo andere bücher ausgeliehen wurden, also kriegst du mit deiner abfrage alle bücher die an tagen ausgeliehen wurden wo zumindest ein anderes buch ausgeliehen wurde das bisher noch nicht zurückgegeben wurde

das ist der fehler !

peter

[Lexsa]

Danke!
Jetzt versteh ich auch meinen Fehler.
Alex

[Bernie]

ich glaube bei den lösungen für bsp. 5 die bisher gepostet wurden is noch ein denkfehler.

es sollen ja die bücher aufgelistet werden, die gerade ausgeborgt sind. das ist genau dann der fall, wenn BIS NULL ist, und VON NOT NULL. Die drei Bücher, bei denen von NULL ist, wurden ja quasi noch nie ausgeborgt. oder?


ok, ausgeborgt wurden sie eigentlich schon, sonst wüden sie ja nicht in der Ausleihliste stehen. Aber wieso fehlt dann das datum bei "von"?

[gck]

Nur kurze Frage: wer hat denn nun alle Karl May Buecher gelesen?
Bei mir kommt der Schild (sic!), der Kaiser und eine gewisse Jane Doe raus!
Habt ihr das auch?