[SQL] Oefening SQL

[Flying35]

Hallo Tweakers,

Kan iemand mij helpen met een SQL query, ik weet niet hoe ik deze uit moet voeren, misschien weet iemand van jullie het.

De opdracht is om uit de volgende tabellen de naam te laten zien van het kind waarvan de ouders niet in dezelfde woonplaats wonen. Hiervan moet de naam + de woonplaats getoond worden. De tabellen zien er als volgt uit:

Tabel: Personen

id	Naam	Geboortedatum	Plaats
1	Marcel	3-6-1973	1
2	Peter	1-10-1957	2
3	Gitte	31-3-1977	1
4	Leon	1-5-1983	2
5	Inge	12-3-1985	3
6	Sienna	30-11-2006	1
7	Thomas	29-4-2010	1
8	Bas	1-1-2011	2

Tabel Woonplaats

PlaatsID	Woonplaats
1	Tilburg
2	Eindhoven
3	Veldhoven
4	Den Bosch

Tabel Ouderkind

Kind	Vader	Moeder
6	1	3
7	1	3
8	4	5

Als iemand me hiermee kan helpen bij voorbaat dank

[ Voor 10% gewijzigd door Flying35 op 19-12-2011 09:48 ]

[Onbekend]

En wat heb je zelf al geprobeerd?

Heb je al bijvoorbeeld een query die de vader en moeder per kind weergeeft?

[Wijnbo]

Wat heb je zelf al geprobeerd?

Edit : spuit 11

[ Voor 24% gewijzigd door Wijnbo op 19-12-2011 09:50 ]

[Cyphax]

Wat je hier wilt doen is de gegevens van alledrie de 3 tabellen via joins ophalen. Als je wilt kijken welke ouders niet dezelfde woonplaats hebben kun je dat eenvoudig zien in de tabel OuderKind. Je kunt de waardes van Vader en Moeder gewoon vergelijken.

[Remus]

Cyphax schreef op maandag 19 december 2011 @ 09:54:
Wat je hier wilt doen is de gegevens van alledrie de 3 tabellen via joins ophalen. Als je wilt kijken welke ouders niet dezelfde woonplaats hebben kun je dat eenvoudig zien in de tabel OuderKind. Je kunt de waardes van Vader en Moeder gewoon vergelijken.

Ik zou zelf niet kiezen voor joins, maar voor een (NOT) EXISTS query condition (afhankelijk van de exacte eis).

[Creepy]

Maar de vraag blijft: wat heb je zelf al geprobreerd en wat lukte daar niet mee? Ons je schoolopdracht laten maken is natuurlijk niet de bedoeling en je zou zelf al genoeg kennis moeten hebben lijkt me om de benodigde queries te kunnen maken of in elk geval een goede gok te kunnen doen.

[Flying35]

Ik heb al meerdere dingen geprobeerd maar deze niet bewaard ik het wel andere queries al uitgevoerd zoals Ouders met meerdere kinderen met daarbij hun woonplaats zoals deze query:


SELECT p.naam, w.plaats
FROM personen AS p
INNER JOIN ouderkind AS o
ON p.id = o.vader OR p.id = o.moeder
INNER JOIN woonplaats AS w
ON p.plaats = w.id
GROUP BY p.naam
HAVING COUNT(p.naam) >1

Ik weet alleen deze niet omdat dit weer een stapje verder is. Het is geen huiswerk maar het is voor mezelf om me goed voor te kunnen bereiden op een toets. Dit zijn oefeningen die ik voor mezelf bedacht heb om te kunnen oefenen.

[Hydra]

En ik maar denken dat de "vader" en "moeder" kolommen naar personen verwijzen

Flying35 schreef op maandag 19 december 2011 @ 10:24:
Ik heb al meerdere dingen geprobeerd maar deze niet bewaard ik het wel andere queries al uitgevoerd zoals Ouders met meerdere kinderen met daarbij hun woonplaats zoals deze query:


SELECT p.naam, w.plaats
FROM personen AS p
INNER JOIN ouderkind AS o
ON p.id = o.vader OR p.id = o.moeder
INNER JOIN woonplaats AS w
ON p.plaats = w.id
GROUP BY p.naam
HAVING COUNT(p.naam) >1

Ik weet alleen deze niet omdat dit weer een stapje verder is. Het is geen huiswerk maar het is voor mezelf om me goed voor te kunnen bereiden op een toets. Dit zijn oefeningen die ik voor mezelf bedacht heb om te kunnen oefenen.

Je sleept echt dingen bij mekaar hier. Hoezo denk je die group-by nodig te hebben?

Om de kind-IDs op te halen hoef je alleen een query te doen waarbij je de rows uit de kind/vader/moeder tabel ophaalt waar de plaats IDs ongelijk zijn. Verder hoef je dan alleen om de naam te tonen dat resultaat te joinen met de kind tabel. Leef je uit

[ Voor 86% gewijzigd door Hydra op 19-12-2011 10:30 ]

[Onbekend]

Inderdaad.

Anders gezegd:
In jouw query is de hoofdtabel "personen".
Jij wilt iets van de kinderen alleen weten, dus zou jouw hoofdtabel "Ouderkind" moeten zijn.

[pedorus]

Flying35 schreef op maandag 19 december 2011 @ 10:24:
Ik weet alleen deze niet omdat dit weer een stapje verder is. Het is geen huiswerk maar het is voor mezelf om me goed voor te kunnen bereiden op een toets. Dit zijn oefeningen die ik voor mezelf bedacht heb om te kunnen oefenen.

Klinkt onwaarschijnlijk, maar goed Wat me opvalt in je query is dat alle tabellen maar 1 keer voorkomen, met 1 alias. In je voorbeeldvraag is er 1 tabel die maarliefst 3 keer moet voorkomen (dus met 3 verschillende namen).

Overigens heb ik ook nog een vraag: Geef de ouders waarvan de kinderen allemaal in dezelfde plaats (zijn gaan) wonen. Wel even wat extra testdata maken daarvoor natuurlijk

[raptorix]

Volgens mij maak je het zelf veel te lastig met aggregate functions, zo uit me hoofd mag je gewoon iets doen als:

Select * from Personen where Personen.ID in (select id from ouderkind where vader=moeder)

[Dido]

raptorix schreef op maandag 19 december 2011 @ 11:01:
Volgens mij maak je het zelf veel te lastig met aggregate functions, zo uit me hoofd mag je gewoon iets doen als:

Select * from Personen where Personen.ID in (select id from ouderkind where vader=moeder)

Als je alle kinderen wilt waarvan de moeder dezelfde persoon is als de vader wel ja. Ik vermoed dat dat niet vaak wat op gaat leveren in de praktijk

[Thasquealer]

Dido schreef op maandag 19 december 2011 @ 11:06:
[...]

Als je alle kinderen wilt waarvan de moeder dezelfde persoon is als de vader wel ja. Ik vermoed dat dat niet vaak wat op gaat leveren in de praktijk

Ik neem aan dat de namen van vaders niet in die tabel worden opgenomen, maar de plaatsen.
Of er moet een extra tabel voor ouders zijn, en dat moeten de id's voorstellen.
Maar omdat die info ontbreekt, zou ik er vanuit gaan dat dat de id's voor de plaatsen zijn.

[ Voor 19% gewijzigd door Thasquealer op 19-12-2011 11:15 ]

[Hydra]

Dido schreef op maandag 19 december 2011 @ 11:06:
[...]

Als je alle kinderen wilt waarvan de moeder dezelfde persoon is als de vader wel ja.

Die 'fout' maakte ik aanvankelijk ook: in die tabel staan de IDs van de woonplaatsen kennelijk, het zijn geen personen.

[pedorus]

Hydra schreef op maandag 19 december 2011 @ 11:16:
Die 'fout' maakte ik aanvankelijk ook: in die tabel staan de IDs van de woonplaatsen kennelijk, het zijn geen personen.

Dat zou een verkeerde database-design zijn. Het is ook niet zo, aangezien er bijvoorbeeld geen woonplaats 5 is maar wel een persoon 5 (moeder van kind 8 ).

[Dido]

Hydra schreef op maandag 19 december 2011 @ 11:16:
Die 'fout' maakte ik aanvankelijk ook: in die tabel staan de IDs van de woonplaatsen kennelijk, het zijn geen personen.

Wat is plaats 5 dan van die laatste moeder?

[djluc]

Ik zie heel die ouderkind tabel totaal niet zitten, ik assume (risky) dat je gewoon 1 vader en 1 moeder hebt en dat dit volstaat in het verzoek. Tevens ga ik er vanuit dat de gegevens van iedereen gewoon bekend zijn. Dus geen missende vaders en moeders.

Voorbeeld van een i.m.o. meer logische structuur gezien iedereen 1 ouder heeft van beide kanten.

SQL:

SELECT 
  child.id, 
  child.name, 
  child.date_of_birth, 
  father.id, 
  father.name,
  mother.id, 
  mother.name 
FROM 
  people AS child 
  INNER JOIN 
    people AS father 
    ON 
      child.father_id=father.id
  INNER JOIN 
    people AS mother 
    ON 
      child.mother_id=mother.id
WHERE mother.city_id!=father.city_id

Database test gebruikt, de tabel cities wordt niet gebruikt in het voorbeeld maar even voor de volledigheid wel toegevoegd.

SQL:

CREATE TABLE `cities` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

INSERT INTO `cities` VALUES(1, 'Amsterdam');
INSERT INTO `cities` VALUES(2, 'Rotterdam');
INSERT INTO `cities` VALUES(3, 'Den Haag');
INSERT INTO `cities` VALUES(4, 'Leiden');
INSERT INTO `cities` VALUES(5, 'Den Bosch');

CREATE TABLE `people` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `date_of_birth` datetime NOT NULL,
  `father_id` int(11) DEFAULT NULL,
  `mother_id` int(11) DEFAULT NULL,
  `city_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

INSERT INTO `people` VALUES(1, 'Kind 1', '2000-12-06 11:29:05', 2, 3, 1);
INSERT INTO `people` VALUES(2, 'Mother 1', '1965-12-13 11:29:30', NULL, NULL, 1);
INSERT INTO `people` VALUES(3, 'Father 1', '1950-12-05 11:29:56', NULL, NULL, 2);
INSERT INTO `people` VALUES(4, 'Father 2', '1971-12-21 11:31:02', NULL, NULL, 2);
INSERT INTO `people` VALUES(5, 'Mother 2', '1970-12-04 11:31:12', NULL, NULL, 2);
INSERT INTO `people` VALUES(6, 'Child 2', '1990-12-29 11:31:53', 4, 5, 2);

[ Voor 70% gewijzigd door djluc op 19-12-2011 11:37 . Reden: voorbeeld getest en werkend. ]

[Onbekend]

djluc schreef op maandag 19 december 2011 @ 11:27:
Ik zie heel die ouderkind tabel totaal niet zitten:

Dat is toch goed zo?
Alle personen staan netjes bij elkaar, en de relaties staan in een andere tabel.
Als die kinderen later zelf weer kinderen krijgen blijf je een goed overzicht houden....

Maar we dwalen zo wel een beetje van de TS af.

[djluc]

Onbekend schreef op maandag 19 december 2011 @ 11:37:
[...]
Dat is toch goed zo?
Alle personen staan netjes bij elkaar, en de relaties staan in een andere tabel.
Als die kinderen later zelf weer kinderen krijgen blijf je een goed overzicht houden....

Maar we dwalen zo wel een beetje van de TS af.

Het voelt als een "belongsTo" relatie. Ofwel een kind hoort bij een vader en een kind hoort bij een moeder. Nu moet je gaan afdwingen dat die relaties in stand blijven. Bijvoorbeeld: Je verwijderd een kind. Je moet dan ook die relaties tabel gaan updaten terwijl ik daar eigenlijk geen enkel nu van in zie. Je kan in mijn voorbeeld gewoon niet 2 vaders hebben. Als dat een business case is moet het uiteraard wel met een koppeltabel maar in dit geval zie ik er totaal geen nut in.

Tevens kan je zo heel simpel de nog niet ingevoerde gegevens vinden. Bijvoorbeeld alle kinderen met een bekende vader maar nog niet ingevoerde moeder. Gewoon checken op IS NULL en klaar.

Waarom wel die extra koppeltabel?

Sorry trouwens @TS: Zitten wel een hoop database design en opmerkingen met termen tussen. De basis vraag van mij is: Waarom denk je die extra tabel nodig te hebben?

[ Voor 7% gewijzigd door djluc op 19-12-2011 11:42 ]

[pedorus]

Die koppeltabel is er omdat je anders relatief veel NULL-waardes krijgt. Het update-argument gaat niet op omdat een database prima dat soort relaties automatisch kan beheren. Het zoeken-argument klopt ook niet omdat je prima een not exists kan gebruiken. Sterker nog, je kunt jouw tabel eenvoudig maken met een simpele view.

Overigens is != niet standaard, dus lijkt me <> mooier. En de bonusvraag staat nog open.

[Dido]

Toch zit er wel iets in om geen koppeltabel te gebruiken. Ik gebruik ze zelfs normaliter ook alleen voor n:m relaties, en zolang we uitgaan van 0 of 1 vader en 0 of 1 moeder, dan is er geen enkel probleem mee om de tabel Persoon (Personen is meervoud en zou ik niet als tabelnaam gebruiken) te linken aan zichzelf via twee FK's voor vader en moeder.

[Onbekend]

Nu ik er een tweede keer naar kijk is zie ik pas dat de tabel "Personen" en de tabel "Ouderkind" de zelfde primary key hebben. In dat geval kunnen die twee tabellen ook beter samengevoegd worden.

Waarom zou je in dat geval voor onbekende ouders dan NULL inserten en niet gewoon het getal 0?

[Dido]

Onbekend schreef op maandag 19 december 2011 @ 12:01:
Waarom zou je in dat geval voor onbekende ouders dan NULL inserten en niet gewoon het getal 0?

Omdat je dan een "magic number" 0 introduceert, zijnde een onbekend persoon die vader en moeder is van heel veel andere personen?

[pedorus]

Dido schreef op maandag 19 december 2011 @ 11:57:
Toch zit er wel iets in om geen koppeltabel te gebruiken.

Eens, het lijkt me hier onnodige clutter die vooral de opdracht net iets moeilijker maakt. Het NULL-argument is ook kul omdat het waarschijnlijk niet eens scheelt in opslag ofzo.

Onbekend schreef op maandag 19 december 2011 @ 12:01:
Waarom zou je in dat geval voor onbekende ouders dan NULL inserten en niet gewoon het getal 0?

Omdat NULL de waarde is die binnen SQL hiervoor bedacht is. Omdat twee ouders die beiden op 0 staan niet aan elkaar gelijk hoeven te zijn.

[Verwijderd]

Moet ik de rest van de toets ook erop zetten!?

[justmental]

Dit is een standaard SQL opdracht.
De truc is de vraag omkeren.
Zoek iedereen waarbij niet voorkomt dat er een gerelateerde ouder is die in een andere plaats woont..

[Hydra]

pedorus schreef op maandag 19 december 2011 @ 11:20:
Dat zou een verkeerde database-design zijn. Het is ook niet zo, aangezien er bijvoorbeeld geen woonplaats 5 is maar wel een persoon 5 (moeder van kind 8 ).

Ik ging ervanuit dat die tabellen gewoon niet compleet waren, maar je hebt waarschijnlijk gelijk

[djluc]

pedorus schreef op maandag 19 december 2011 @ 11:51:
Die koppeltabel is er omdat je anders relatief veel NULL-waardes krijgt. Het update-argument gaat niet op omdat een database prima dat soort relaties automatisch kan beheren. Het zoeken-argument klopt ook niet omdat je prima een not exists kan gebruiken. Sterker nog, je kunt jouw tabel eenvoudig maken met een simpele view.

Overigens is != niet standaard, dus lijkt me <> mooier. En de bonusvraag staat nog open.

Veel null values maakt me eerlijk gezegd niets uit, lijkt op premature optimisation om daar in deze situatie druk mee te zijn. Null values gebruiken, de echte dus, vind ik absoluut een good practice. Het werkt erg duidelijk en is duidelijk anders dan een niet bestaande waarde. SELECT FROM messages where read IS NOT NULL etc. leest gemakkelijk weg en het is een duidelijk standaard.

Ik gebruik MySQL waarin != meest gebruikt is maar kan inderdaad goed dat je gelijk hebt. Zal het eens checken.

justmental schreef op maandag 19 december 2011 @ 12:25:
Dit is een standaard SQL opdracht.
De truc is de vraag omkeren.
Zoek iedereen waarbij niet voorkomt dat er een gerelateerde ouder is die in een andere plaats woont..

Dan lijkt het me een slechte vraag, er zijn genoeg situaties (producten met kenmerken/voorraden etc.) te bedenken waarin de structuur wel logisch is.