Problem SQL Query

[hvgg-user]

Hallo zusammen,

ich bin neu hier im Forum und habe ein kleines Problem bezüglich SQL.

Ich habe folgende DB mit zwei Tabellen in 3nf:
1.Person: Ssn(Pk), Name, Vorname, G, Geb, Land, Plz, Str
2.Stadt: Plz(Pk), Land(Pk), Stadt

G ist das Geschlecht (F/M) und Geb ist der Geburtstag im SQL-Date Format.

Die DB enthält zufallsgenerierte Testdaten. Dazu soll ich ein paar (2 Seite ) Fragen beantworten. Meine SQL-Kenntnisse sind leider nicht ganz so hervorragend. Vielleicht könnt ihr mir ja helfen.
Ich habe vorallem mit diesen Aufgaben Probleme, die anderen gehen einigermaßen:

1. Selektiere die Anzahl aller vorkommenden Städtenamen
-> Mit Select Count(Stadt) From Stadt; kommt halt nur die Anzahl der gesamten Einträge raus, aber nicht die Anzahl der verschiedenen Städte. ?

2. Selektiere alle Personen, die denselben Geburtstag haben wie die älteste bzw. jüngste Person in der Datenbank Selektiere alle Personen, die denselben Geburtstag haben wie die älteste bzw. jüngste Person in der Datenbank
-> komme ich garnicht mit zurecht.

3. Selektiere alle Kombinationen von Vorname und Geschlecht, sowie die Häufigkeit jeder Kombination, absteigend sortiert nach dieser Häufigkeit.
-> Select Distinct Vorname, G, Count(Vorname) From Person Order by Count(Vorname); geht so leider nicht..


Wäre cool, wenn ihr mir helfen könnt.

lg Moritz

[ebis]

Hallo zusammen,
1. Selektiere die Anzahl aller vorkommenden Städtenamen
-> Mit Select Count(Stadt) From Stadt; kommt halt nur die Anzahl der gesamten Einträge raus, aber nicht die Anzahl der verschiedenen Städte. ?

[HIGHLIGHT="SQL"]Select Count(DISTINCT Stadt) From Stadt[/HIGHLIGHT]

2. Selektiere alle Personen, die denselben Geburtstag haben wie die älteste bzw. jüngste Person in der Datenbank

Eine Möglichkeit wäre
[HIGHLIGHT="SQL"]CREATE TABLE Test (Ssn INT PRIMARY KEY ,
Name VARCHAR(10),
Geb DATETIME)
INSERT INTO Test SELECT 1, 'y1', '2000-01-01'
UNION ALL SELECT 2, 'm', '1950-01-01'
UNION ALL SELECT 3, 'o', '1900-01-01'
UNION ALL SELECT 4, 'y2', '2000-01-01'
SELECT T.Ssn, t.Geb, y.Geb as youngest, o.Geb as Oldest
FROM test AS T
LEFT JOIN (SELECT MAX (Geb) AS Geb
FROM Test
) AS y ON y.Geb = t.Geb
LEFT JOIN (SELECT MIN (Geb) AS Geb
FROM Test
) AS o ON o.GEB = t.GEB
WHERE y.Geb IS NOT NULL OR o.Geb is NOT NULL
DROP Table Test[/HIGHLIGHT][HIGHLIGHT="Result"]
Ssn Geb youngest Oldest
----------- ----------- ---------- ------------
1 2000-01-01 2000-01-01 NULL
3 1900-01-01 NULL 1900-01-01
4 2000-01-01 2000-01-01 NULL[/HIGHLIGHT]

3. Selektiere alle Kombinationen von Vorname und Geschlecht, sowie die Häufigkeit jeder Kombination, absteigend sortiert nach dieser Häufigkeit.
-> Select Distinct Vorname, G, Count(Vorname) From Person Order by Count(Vorname); geht so leider nicht..

[HIGHLIGHT="SQL"]Select Vorname, G, Count(Vorname) AS Anzahl
From Person
GROUP BY Vorname, G
Order by Anzahl[/HIGHLIGHT]

[hvgg-user]

ok, danke.

Ich habe noch ein kleines Problem. Habe schon verschiedene Sachen funktioniert aber es klappt leider entweder gar nicht oder es kommen viel zu viele Tupel raus.
"
Suche alle Paare (Name, Vorname, Land, Plz) von Personen heraus, die im selben Land und in derselben Stadt leben"

Select * From Person As p1, Person As p2, Stadt As s1, Stadt As s2 
Where p1.Land = s1.Land AND p1.Plz = s1.Plz AND s1.Land = s2.Land AND s1.Stadt = s2.Stadt;

Kann mir jemand einen Tipp geben (gerne auch die Lösung)?

lg mo

[Falk Prüfer]

Hallo,

...Suche alle Paare (Name, Vorname, Land, Plz) von Personen heraus, die im selben Land und in derselben Stadt leben"...

Das ist mit deinem Datenmodell nicht möglich, da es gibt keine eindeutige Zuordnung einer Person zu einer Stadt gibt!

Gruß Falk

[hvgg-user]

ok... wieso sollte das nicht möglich sein?

Wenn ich einen Join der beiden Tabellen mache habe ich ja Tupel von Person-(Land)-Stadt Kombinationen, also gehört zu jeder Person eine eindeutige Stadt. Daraus müsste ich dann eigentlich die entsprechenden Paare bilden können.

Ich habe auch vorsichtshalber meinen Lehrer gefragt und der meinte meine DB-Struktur sei korrekt und die Aufgabe ist damit lösbar.

Wenn das absolut falsch sein sollte, dann bitte ich um Aufklärung aber im Moment sehe ich nicht wieso das nicht möglich sein sollte.

lg mo

[frauwue]

ok... wieso sollte das nicht möglich sein?

Möglich wäre das schon, wenn es in Deiner Personentabelle einen Bezug zur Stadt gäbe.

Gruß

[hvgg-user]

Jaja, aber in meiner Tabelle Stadt ist ja der Primary-Key die Kombination aus PLZ und Land. Und diese Informationen sind ja auch in meiner Tabelle Person enthalten und damit kann ich die Tabellen mittels Join eindeutig verknüpfen, so dass wenn ich in der Tabelle Person 1000 Sätze habe auch in meiner "Join-Tabelle" 1000 Sätze sind. Und hier drauf müsste ich dann eigentlich die o.g. Anfrage anwenden können. Oder?

lg mo

[frauwue]

Hallo,

das ist ja ein sehr abenteuerliches Konzept. Es gibt in Deutschland nicht nur Städte mit mehreren Postleitzahlen sondern auch Orte, die sich eine Postleitzahl teilen. Ganz davon zu schweigen, was es im Ausland alles geben kann.

Gruß

[Falk Prüfer]

Hallo hvgg-user,

ok... wieso sollte das nicht möglich sein?

Wenn ich einen Join der beiden Tabellen mache habe ich ja Tupel von Person-(Land)-Stadt Kombinationen, also gehört zu jeder Person eine eindeutige Stadt. Daraus müsste ich dann eigentlich die entsprechenden Paare bilden können.

Ich habe auch vorsichtshalber meinen Lehrer gefragt und der meinte meine DB-Struktur sei korrekt und die Aufgabe ist damit lösbar.

Wenn das absolut falsch sein sollte, dann bitte ich um Aufklärung aber im Moment sehe ich nicht wieso das nicht möglich sein sollte.

lg mo

OK, unter der Voraussetzung das es sich um ein rein hypothetisches Modell handelt, für das die einschränkende Bedingung gilt: Jeder Land/PLZ-Kombination ist eineindeutig eine Stadt zugeordnet, hätte dein Lehrer recht und die Aufgabe wäre damit lösbar.
Warum dies aber in der Realität nicht so ist hat dir frauwue schon erklärt.

Unter Gültigkeit der o.g. Bedingung wäre noch zu klären wie die Frage genau gemeint ist. Im Endeffekt ist es ein einfacher Join, sortiert nach der Stadt und ggfs. mit HAVING eingeschränckt auf die mit mindestens zwei Personen in einer einer Stadt.

Gruß Falk

[hvgg-user]

Ok, sorry ich habe vergessen zu sagen, dass zu einer PLZ immer nur eine Stadt gehören kann, mein Fehler.

Ich bin gerade dabei zu versuchen meine Anfrage "3. Selektiere alle Kombinationen von Vorname und Geschlecht, sowie die Häufigkeit jeder Kombination, absteigend sortiert nach dieser Häufigkeit." soweit einzuschränken, dass nur noch die Vornamen ausgewählt werden, die mindestens 5 mal vorkommen. Habe es hiermit versucht:

SELECT Vorname, G, Count(Vorname) AS Anzahl FROM Person Where Count(Vorname) >= 5 GROUP BY Vorname, G ORDER BY Anzahl;

aber dann kommt leider der Fehler "Invalid use of group function", ich weiß ehrlich gesagt nicht wieso.

lg mo

[dimitri]

Where Count(Vorname) >= 5

Das kannst Du in der WHERE Klausel nicht verwenden, da du im WHERE nur reel vorhandene Daten Filtern kannst.

Das COUNT Ergebnis wird aber erst gebildet, wenn die WHERE Klausel schon abgearbeitet ist. Für deine Zwecke gibt es die schon erwähnte HAVING Klausel.

Dim

[hvgg-user]

Thx.

Damit hätte ich alle Aufgaben gelöst, bis auf die mit den Paaren die in der selben Stadt und in dem selben Land wohnen.

Die Aufgabe mit den Personen die am gleichen Tag wie der Älteste/Jüngste Geburtstag haben, habe ich übrigens ein wenig kürzer lösen können:

Select * From Person Where EXTRACT( MONTH From Geb) = 
(Select EXTRACT(MONTH From MAX(Geb) ) From Person) 
AND EXTRACT( DAY From Geb) = (Select EXTRACT(DAY From MAX(Geb) ) From Person) 
ORDER BY Geb ;

Ich versuche das so zu machen, dass ich erstmal einen Join der beiden Tabellen mache und da dann die Personen rausfiltere, die nicht im gleichen Land und in der gleichen Stadt leben. Und genau da hänge ich
Can Anyone help?

lg mo

[frauwue]

select p1.Name, p1.Vorname,p1. Land,p1. Plz from person p1
where exists
(select p2.ssn from person p2
where p2.plz=p1.plz and p2.land=p1.land
and p1.ssn<>p2.ssn)
having count(DISTINCT p1.land,p1.plz) = 2
order by p1.land,p1.plz

Gruß

[hvgg-user]

Moin,

die Query von frauwue hat leider nicht so ganz funktioniert, aber die hier geht:

 Select Distinct p1.Name, p1.Vorname, p1.Plz, p1.Land From Person p1, Person p2 
Where p1.PLZ = p2.PLZ AND p1.Land = p2.Land AND p1.Ssn <> p2.Ssn 
Order By p1.Land, p1.Plz;

Danke an alle die mir geholfen haben, hätte die eine oder andere Sache sonst nicht so schnell hinbekommen.

lg mo

[frauwue]

Hallo,

mit Deiner Abfrage erhälst Du aber keine Paare sondern alle Leute, die jeweils in derselben Stadt wohnen. Das können auch mehr als zwei sein. Wahrscheinlich kommt das aber in Deiner Datenbank nicht vor.

Gruß