Vraag over subquerie met correlatie.

[dirk_decibel]

Voor een opdracht op school moeten we de volgende vraag beantwoorden:

"Geef de naam en het geboortejaar van de overleden presidenten die ouder zijn geworden dan de gemiddelde overlijdensleeftijd die in dezelfde staat zijn geboren als de eerstgenoemde president."
Deze vraag moet opgelost worden met behulp van een subquerie met correlatie.

Daarbij maken we gebruik van de onderstaande tabel:
PRESIDENT = ( president pres_name: CHAR(20), naam van de president
birth_yr: NUMBER(4), geboortejaar
yrs_serv: NUMBER(2), aantal dienstjaren
de¬ath_age: NUMBER(2), leeftijd bij over¬lijden
party: CHAR(10), partij waartoe behorend
state_born: CHAR(15) staat waarin geboren
);


Zelf kom ik niet verder dan onderstaande querie, maar daar lijkt hij alleen te kijken naar welke presidenten er allemaal ouder zijn geworden dan het totale gemiddelde, niet het gemiddelde per staat. Iemand suggesties misschien?

Select eerste.pres_name, eerste.birth_yr, eerste.death_age
From PRESIDENT eerste , PRESIDENT tweede
Where eerste.pres_name=tweede.pres_name
And eerste.death_age>
(Select AVG(death_age)
From PRESIDENT
where eerste.state_born=tweede.state_born);

 

[Tha Devil]

wat wordt bedoelt met "de eerstgenoemde president"?

 

[dirk_decibel]

wat wordt bedoelt met "de eerstgenoemde president"?

Hehe, ik moest hem ook 10 keer lezen voordat ik eindelijk wist wat ze wilden. Het is de bedoeling dat je per staat alle presidenten geeft die ouder zijn geworden dan de gemiddelde overlijdensleeftijd in die staat. Tenminste, dat is zoals ik het lees.

 

[Tha Devil]

Probeer deze eens:

SELECT pres_name, birth_yr, state_born
FROM president
WHERE death_age > ANY (SELECT AVG(death_age) FROM president GROUP BY state_born)
ORDER BY birth_yr

 

[dirk_decibel]

Probeer deze eens:

SELECT pres_name, birth_yr, state_born
FROM president
WHERE death_age > ANY (SELECT AVG(death_age) FROM president GROUP BY state_born)
ORDER BY birth_yr

Die werkt niet, je krijgt ook waarden terug die kleiner zijn dan het gemiddelde per staat (of zelfs kleiner dan het totale gemiddelde). Waarom hij niet werkt is mij eerlijk gezegd ook een raadsel, al ben ik niet bekend met wat "ANY" precies doet .

Overigens heb ik na een hele hoop klooien de werkende querie te pakken:


SELECT pres_name, birth_yr
FROM PRESIDENT p1
GROUP BY pres_name, birth_yr, death_age, state_born
HAVING death_age>(SELECT AVG(death_age)
FROM PRESIDENT p2
WHERE p1.state_born=p2.state_born
GROUP BY state_born);

Het schuin gedrukte gedeelte bleek essentieel te zijn. Zonder dat stukje vertikt ie het en geeft SQL Worsheet de volgende foutmelding:

HAVING death_age>(SELECT AVG(death_age)
*
FOUT in regel 4:
.ORA-01427: Enkele-rij subselect levert meer dan één rij op.

Nou wist ik dat er op de een of andere manier 2 tabellen gekoppeld moesten worden aangezien de vraag eiste dat de oplossing gegeven moest worden met correlatie/synchronisatie. Aangezien ik dat altijd beschouw als een "join" van de hoofdquery met de subquery en de gegevens toch echt allemaal in dezelfde tabel te vinden waren dus maar eens geprobeerd om 2 instanties van die tabel aan elkaar te koppelen in de subquery en voila, het werkt.

Zou iemand me alsnog iets kunnen vertellen over waarom die "join" dan toch persé nodig is? Het is dat ik wist dat er zoiets in moest zitten vanwege de vraagstelling maar uit mezelf was ik er niet opgekomen, ergo: ik snap er eigenlijk nog steeds de ballen van.