probleem ontwerpen SQL queries

[anoniem]

:cry: Ik breek mijn hoofd over de volgende queries in sql plus (oracle). Een overzicht van de relaties van de door mij gebruikte tabellen kun je hier vinden: http://home.kabelfoon.nl/~aby/ Iemand suggesties ? De gevraagde queries: ·Formuleer een query die uitrekent hoeveel geld de Spaanse voetbalclubs in het 1999/2000 aan transferbedragen kwijt waren ·Formuleer een query die voor elk land het totale transferbedrag van de binnenlandse transfers berekent ·Formuleer een query die alle transfers weergeeft die binnen Engeland plaatsvonden en waarbij een speler in het seizoen 1998/1999 van het tweede niveau naar het eerste niveau overging ·Formuleer een query die aangeeft hoeveel geld Nederlandse clubs per saldo overhielden aan transfers naar Spanje in het seizoen 1999/2000 Ik hoop dat iemand mij hulp kan bieden !!!

[anoniem]

OK, lets go Query 1: [code:1:417cf1dd9f] SELECT transfers.bedrag FROM (SELECT SUM(Bedrag), [naar club] FROM transfer WHERE to_char(datum, 'yyyy') IN ('1999', '2000' ) GROUP BY [naar club]) TRANSFERS , clubland WHERE tranfers.[naar club] = clubland.club AND clubland.land = 'Spanje' ORDER BY clubland.club [/code:1:417cf1dd9f] Even een opmerking op het databaseontwerp. In de tabellen speler en transfer maak je netjes gebruik van technische id's. Helaas doe je dit niet in de andere tabellen. Geef een tabel altijd een technische id en gebruik deze id om de verschillende tabellen te koppelen. Wat de query betreft. De subquery tranfers berekent de som van de tranferbedragen en geroepeert deze per ontvangende club (de club die moet betalen dus). Vervolgens wordt in de buitenste query alleen die clubs gefilterd, die behoren bij clubs uit spanje. EVen voor de duidelijkheid. Ik weet niet hoe oracle met spaties in kolomnamen omgaat, maar ook dit is niet aan te raden. Gebruik indien nodig de underscore "_" om spaties aan te geven. Wil je nu het totaal aan ontvangen transfergelden, dan groepeer je de subquery op [van club]. Wellicht dat deze query je inzicht geeft in de volgende queries. Greetz, Taz

[anoniem]

beste tedior, ik heb in mijn model in sql die spaties al vervangen door een '_' ik krijg na uitvoering van jouw querie deze melding: SQL> SELECT transfers.bedrag 2 FROM (SELECT SUM(Bedrag), [naar_club] 3 FROM transfer 4 WHERE to_char(datum, 'yyyy') IN ('1999', '2000' ) 5 GROUP BY [naar_club]) TRANSFERS 6 , clubland 7 WHERE tranfers.[naar_club] = clubland.club 8 AND clubland.land = 'Spanje' 9 ORDER BY clubland.club 10 ; FROM (SELECT SUM(Bedrag), [naar_club] * FOUT in regel 2: .ORA-00936: Ontbrekende uitdrukking.

[anoniem]

:oops: tedior = Tazzie

[anoniem]

voor de volledigheid staan de scripts van de tabellen op: [url]http://home.kabelfoon.nl/~aby/[/url]

[anoniem]

Query 2: [code:1:83b724683e] SELECT nvl(SUM(transfer.bedrag),0) as total , land.naam FROM transfer , clubland , land WHERE clubland.club = transfer.naar_club AND (SELECT land FROM clubland WHERE club = transfer.naar_club) = (SELECT land FROM clubland WHERE club = transfer.van_club) AND clubland.land(+) = land.land AND land.naam = 'Spanje' GROUP BY land.naam [/code:1:83b724683e] Opmerking over de query. Het plusteken betekent een optionele koppeling, wat zoveel inhoudt dat ook de landen die geen onderlinge transfers hebben gehad worden getoond. Deze krijgen dat als transferwaarde 0 (zie de functie nvl). Dit dus ter volledigheid van de lijst. Je kan dit natuurlijk ook gewoon weglaten. (in welk geval de functie nvl niet nodig is) Nog enkele opmerkingen. (indien je dat advies wilt natuurlijk, anders gewoon negeren ;-)) Aangezien het onwaarschijnlijk is dat een club in meerdere landen ligt is het opnemen van de koppeltabel clubland niet nodig. Het aangeven van het land kan dan gewoon in de tabel club geschieden. Tevens valt mij op de in de tabel speler geen koppeling zit met de club tabel. Wellicht dat een speler dus niet bij een club behoort.... ?-) Lijkt mij tevens dat een speler niet bij meer dan 1 club kan horen. Indien dat wel het geval is, kan hij niet bij meer dan 1 club tegelijkertijd horen. Indien dat wel het geval is kan je werken met een koppeltabel en start en eindatum (recommended) of deze velden opnemen in de spelertabel, zodat een speler meerdere malen voor kan komen in deze tabel. Het datamodel zou voor mij dus zijn: SPELER: Speler_id Club_id Naam Nationaliteit TRANSFER: Transfer_id Speler_id Van_club_id Naar_club_id Datum Bedrag CLUB: Club_id Land_id Naam Niveau Seizoen LAND: Land_id Naam Niveaus Greetz, Taz

[anoniem]

Indien je de spaties hebt verwijderd kan je de blokhaken achterwege laten.... Greetz, Taz

[anoniem]

die tabel clubland is ook opgenomen om de clubs die er ingevoerd mogen worden in de tabel club te beperken dat diegene die in die tabel staan. zodat er bijvoorbeel geen japanse club ingevoerd kan worden (buiten UEFA) is dit een verkeerde gedachte ?

[anoniem]

na verwijdering van blokken: SQL> SELECT transfers.bedrag 2 FROM (SELECT SUM(Bedrag), 3 FROM transfer 4 WHERE to_char(datum, 'yyyy') IN ('1999', '2000') 5 GROUP BY) TRANSFERS 6 , clubland 7 WHERE tranfers = clubland.clubnaam 8 AND clubland.land = 'Spanje' 9 ORDER BY clubland.clubnaam 10 ; FROM transfer * FOUT in regel 3: .ORA-00936: Ontbrekende uitdrukking. P.S. het datum formaat is bij mij TO_DATE ('09-06-1999', 'dd-mm-yyyy') maakt dit uit?

[anoniem]

[quote:74667df068="blyzard_007"]die tabel clubland is ook opgenomen om de clubs die er ingevoerd mogen worden in de tabel club te beperken dat diegene die in die tabel staan. zodat er bijvoorbeel geen japanse club ingevoerd kan worden (buiten UEFA) is dit een verkeerde gedachte ?[/quote:74667df068] Ik geloof niet dat ik je helemaal begrijp. Je neemt de tabel op om het invoeren van de clubs in de tabel club te beperken tot diegene die in welke tabel staan? Indien je wilt voorkomen dat aan de tabel clubs geen clubs uit bepaalde landen kan worden toegevoegd, zou ik een veld in de tabel land opnemen. BV. mag_clubs_invoeren of een kortere versie ervan. Dan kan je bij het toevoegen van clubs alleen die landen tonen, waarbij mag_clubs_invoeren op true staat. Zodoende kan dan een land als Japan wel voorkomen in de tabel land, maar kan geen clubs hebben. Natuurlijk kan je door middel van het zogenaamde direct hacken in de database dan alsnog een club aan dat land toevoegen. Ik ga er echter vanuit dat het toevoegen van een club via een user-interface gaat en dus het blokkeren van clubs uit landen die niet mogen worden toegevoegd d.m.v. de business logica wordt ondervangen. Eventueel kan je altijd nog een constraint op het veld land_id leggen in de clubs tabel. Naast de foreign key relatie leg je dan ook vast dan bijbehorend veld mag_clubs_hebben op true moet staan.... Greetz, Taz

[anoniem]

[quote:ba86db5796="blyzard_007"]na verwijdering van blokken: SQL> SELECT transfers.bedrag 2 FROM (SELECT SUM(Bedrag), 3 FROM transfer 4 WHERE to_char(datum, 'yyyy') IN ('1999', '2000') 5 GROUP BY) TRANSFERS 6 , clubland 7 WHERE tranfers = clubland.clubnaam 8 AND clubland.land = 'Spanje' 9 ORDER BY clubland.clubnaam 10 ; FROM transfer * FOUT in regel 3: .ORA-00936: Ontbrekende uitdrukking. [/quote:ba86db5796] Who, de blokhaken verwijderen. Niet het veld. De aggregate functie SUM verlangt twee velden. Namelijk het veld dat moet worden berekent (bedrag) en het veld waarop de berekening moet wordne uitgevoerd. dus: SELECT transfers.bedrag 2 FROM (SELECT SUM(Bedrag), [b:ba86db5796]transfer.naar_club[/b:ba86db5796] 3 FROM transfer 4 WHERE to_char(datum, 'yyyy') IN ('1999', '2000') 5 GROUP BY [b:ba86db5796]transfer.naar_club[/b:ba86db5796]) TRANSFERS. 6 , clubland 7 WHERE tranfers[b:ba86db5796].naar_club[/b:ba86db5796] = clubland.clubnaam 8 AND clubland.land = 'Spanje' 9 ORDER BY clubland.clubnaam De velden die na het group by statement komen moeten overeenkomen met de velden in de select clause, met uitzondering van de aggregate functie: VB SELECT SUM(bedrag), bedrijf, mdw_naam FROM aankopen GROUP BY bedrijf, mdw_naam Bivenstaande toont de totale som van aankopen per bedrijf en medewerker. Indien je alleen per bedrijf wilt zien, dan laat je mdw_naam weg uit zowel de SELECT als de GROUP BY dus: VB SELECT SUM(bedrag), bedrijf FROM aankopen GROUP BY bedrijf De GROUP BY geeft dus aan op basis van welke criteria hij de records bij elkaar moet vegen. [quote:ba86db5796] P.S. het datum formaat is bij mij TO_DATE ('09-06-1999', 'dd-mm-yyyy') maakt dit uit?[/quote:ba86db5796] Het datumformaat maakt in die zin niet veel uit, alswel dat je op moet letten dat de beide formaten overeenstemmen. Ik gebruikte de functie to_char(date, 'yyyy') om alleen het jaar terug te krijgen. Ditkan echter elk willekeurig formaat zijn. Dus ook to_char(date, 'yyyy?mm?dd') resultaat is dan 2002?05?27 Greetz, Taz

[anoniem]

SQL> SELECT transfers.bedrag 2 FROM (SELECT FROM transfer 3 WHERE to_char(datum, 'yyyy') IN ('1999', '2000') 4 GROUP BY transfer.naar_club) TRANSFERS 5 , clubland 6 WHERE tranfers.naar_club = clubland.clubnaam 7 AND clubland.land = 'Spanje' 8 ORDER BY clubland.clubnaam 9 ; FROM (SELECT FROM transfer * FOUT in regel 2: .ORA-00936: Ontbrekende uitdrukking.

[anoniem]

[quote:3bd52e8dd8="blyzard_007"]SQL> SELECT transfers.bedrag 2 FROM (SELECT FROM transfer 3 WHERE to_char(datum, 'yyyy') IN ('1999', '2000') 4 GROUP BY transfer.naar_club) TRANSFERS 5 , clubland 6 WHERE tranfers.naar_club = clubland.clubnaam 7 AND clubland.land = 'Spanje' 8 ORDER BY clubland.clubnaam 9 ; FROM (SELECT FROM transfer * FOUT in regel 2: .ORA-00936: Ontbrekende uitdrukking.[/quote:3bd52e8dd8] Wel op blijven letten... ;-) SELECT transfers.bedrag 2 FROM (SELECT [b:3bd52e8dd8]SUM(transfer.bedrag) as Bedrag, transfer.naar_club [/b:3bd52e8dd8] FROM transfer 3 WHERE to_char(datum, 'yyyy') IN ('1999', '2000') 4 GROUP BY transfer.naar_club) TRANSFERS 5 , clubland 6 WHERE tranfers.naar_club = clubland.clubnaam 7 AND clubland.land = 'Spanje' 8 ORDER BY clubland.clubnaam

[anoniem]

Tazzie sorry ik ben niet zo'n expert in SQL maar ik probeer het te leren. Simpele vraagstukken lukken mij nog wel maar als het ingewikkelder wordt dan ontbreekt het mij nog aan ervaring. ik krijg nog een laatste foutmelding waar ik niets van begrijp, ik hoop dat je nog een antwoord hebt. SQL> SELECT transfers.bedrag 2 FROM (SELECT SUM(transfer.bedrag) as Bedrag, transfer.naar_club 3 FROM transfer 4 WHERE to_char(datum, 'yyyy') IN ('1999', '2000') 5 GROUP BY transfer.naar_club) TRANSFERS 6 , clubland 7 WHERE tranfers.naar_club = clubland.clubnaam 8 AND clubland.land = 'Spanje' 9 ORDER BY clubland.clubnaam; WHERE tranfers.naar_club = clubland.clubnaam * FOUT in regel 7: .ORA-00904: Ongeldige kolomnaam.

[anoniem]

[quote:fb334ff821="blyzard_007"]Tazzie sorry ik ben niet zo'n expert in SQL maar ik probeer het te leren. [/quote:fb334ff821] Geeft niet. Bedoelde het vriendelijk. Heb het ook ooit moeten leren en P-SQL is nu eenmaal net ff iets anders als T-SQL. [quote:fb334ff821] Simpele vraagstukken lukken mij nog wel maar als het ingewikkelder wordt dan ontbreekt het mij nog aan ervaring. [/quote:fb334ff821] Begrijpelijk, daarom doe ik er zoveel mogelijk uitleg bij... ;-) [quote:fb334ff821] ik krijg nog een laatste foutmelding waar ik niets van begrijp, ik hoop dat je nog een antwoord hebt. [/quote:fb334ff821] Uiteraard... [quote:fb334ff821] SQL> SELECT transfers.bedrag 2 FROM (SELECT SUM(transfer.bedrag) as Bedrag, transfer.naar_club 3 FROM transfer 4 WHERE to_char(datum, 'yyyy') IN ('1999', '2000') 5 GROUP BY transfer.naar_club) TRANSFERS 6 , clubland 7 WHERE tranfers.naar_club = clubland.clubnaam 8 AND clubland.land = 'Spanje' 9 ORDER BY clubland.clubnaam; WHERE tranfers.naar_club = clubland.clubnaam * FOUT in regel 7: .ORA-00904: Ongeldige kolomnaam.[/quote:fb334ff821] Deze is relatief simpel. Er staat een schrijffout in. (Wellicht mijn fout) SELECT transfers.bedrag 2 FROM (SELECT SUM(transfer.bedrag) as Bedrag, transfer.naar_club 3 FROM transfer 4 WHERE to_char(datum, 'yyyy') IN ('1999', '2000') 5 GROUP BY transfer.naar_club) TRANSFERS 6 , clubland 7 WHERE tran[b:fb334ff821]s[/b:fb334ff821]fers.naar_club = clubland.clubnaam 8 AND clubland.land = 'Spanje' 9 ORDER BY clubland.clubnaam In regel 7 stond tranfers i.p.v. transfers. Wanneer je een foutmelding krijgt als deze, dan is er altijd een van de volgende dingen aan de hand. 1. (Meestal schrijffout) 2. Rechten staan niet goed Bekijk dus bij deze fouten allereerst kritisch je statements, dan eventueel naar de rechten kijken. Greetz, Taz

[anoniem]

hij werkt nu. :) stom dat ik het niet zag van die spelfout :o alleen ik krijg nu de 2 bedragen (er zijn dus 2 transfers geweest) kan ik nu nog iets wijzigen zodat die 2 ook nog opgeteld worden.

[anoniem]

Tuurlijk kan je die ook wijzigen. Hij wordt nu gegroepeert per ontvangende club. Dit zou betekenen dat je twee transfers hebt gehad naar verschillende clubs. Maar je wilt de optelling per land hebben. [code:1:27db33b5ce] SELECT SUM(transfer.bedrag), clubland.land FROM clubland, transfer WHERE transfer.naar_club = clubland.land AND to_char(datum, 'yyyy') IN ('1999', '2000') AND clubland.land = 'Spanje' GROUP BY clubland.land [/code:1:27db33b5ce] Nu heb je geen buitenste select meer. Met jouw datamodel was de buitenste loop niet nodig. Dit omdat de naam van het land ook in de clubland tabel staat (wat tevens redundante info is). Meestal maak ik voor het ophalen van de benodigde gegevens een aparte select, waarbij ik in de buitenste loop bepaal, welke overige gegevens ik wil tonen en hoe. Over het algemeen werkt dit perfect, het is alleen niet altijd nodig. Mijn excuses. Nog nagedacht over het bestaansrecht van de clubland tabel? Greetz, Taz

[anoniem]

ja ik heb er nog over nagedacht maar mijn docent wil dat het zo blijft helaas. ik zag trouwens bij je gegevens dat je in zwijndrecht woonde, cool want ik woon in H.I.Ambacht (de wereld is klein). Kun je in plaats van te posten mij mailen op aby@kabelfoon.nl want ik zit op mijn werk nu. Ik zou je namelijk nog wat vragen willen stellen. Gister heb ik geprobeerd om je toe te voegen met msn maar dat lukte op 1 of andere manier niet. Ik heb gister geprobeerd om die 2e querie te maken maar het lukte mij niet jij een idee?

[anoniem]

Heb je inmiddels gemaild...

[anoniem]

Beetje later dan beloofd, maar moest op een klant wachten... :-) [code:1:c0f2b12c20] SELECT (nvl(ink_uit_spanje.bedrag,0) - nvl(uit_naar_spanje.bedrag, 0)) as NETTO_RESULT , club.clubnaam as club FROM club , clubland , (SELECT SUM(transfer.bedrag) as bedrag , transfer.van_club as club FROM transfer , clubland WHERE transfer.naar_club = clubland.clubnaam AND clubland.land = 'SPANJE' GROUP BY transfer.van_club) ink_uit_spanje , (SELECT SUM(transfer.bedrag) as bedrag , transfer.naar_club as club FROM transfer , clubland WHERE transfer.van_club = clubland.clubnaam AND clubland.land = 'SPANJE' GROUP BY transfer.naar_club) uit_naar_spanje WHERE club.clubnaam = ink_uit_spanje.club(+) AND club.clubnaam = uit_naar_spanje.club(+) AND club.clubnaam = clubland.clubnaam AND clubland.land = 'Nederland' ORDER BY club.clubnaam [/code:1:c0f2b12c20] Zou moeten werken, is iets anders ingestoken... Indien vragen, pboom@cgey.nl Greetz, Taz