12 Data Manipulatie Query Talen
/
Informatica
1
12 Queries maken in TC en SQL • (ter verduidelijking) We kijken nog even naar bier-query q: – Geef alle paren van drinkers die niet samen naar een kroeg kunnen gaan en daar allebei een verschillend bier krijgen dat ze lusten. – We gaan deze query oplossen in TC en SQL, en SQL doen we als voorbeeld door TC naar SQL te vertalen.
/
Informatica
2
12 Query opstellen in TC • Eerste stap: vertaal de query naar een vorm van Nederlands die beter bij de TC aansluit: – We zoeken paren van drinkers; – Ze kunnen niet samen naar een kroeg gaan en daar een voorwaarde X vinden wordt: – Geen enkele kroeg voldoet aan voorwaarde X (en we hoeven niet te weten of de drinkers daar ook komen). – Alternatieve formulering: voor elke kroeg geldt dat ze niet voldoet aan voorwaarde X – X is: de kroeg schenkt een bier dat de ene drinker lust en een verschillend bier dat de andere drinker lust.
/
Informatica
3
12 Query opstellen in TC • Tweede stap altijd: begin met de nodige “exists” (∃)delen om een gevraagd tupel t samen te stellen. (Begin nooit met een ∀) • We zoeken paren van drinkers, en die komen voor in V (visits), dus: – { t | ∃v1 ∈V ( ∃v2 ∈V (t[eerste]=v1[d] ∧ t[tweede]=v2[d] ∧ v1[d] ≠ v2[d] ∧ de rest van de query ) ) }
/
Informatica
4
12 Query opstellen in TC • De rest van de query is: – a: “er bestaat geen kroeg die” en – b: “een bier schenkt dat de ene drinker lust en een verschillend bier (schenkt) dat de andere drinker lust. – ¬∃ s1 ∈ S ( b ) (er bestaat geen kroeg die) – ( ∃ l1 ∈ L ( s1[b] = l1[b] ∧ l1[d] = v1[d] ∧ (een bier schenkt dat de ene drinker lust) – ∃ s2 ∈ S ( ∃ l2 ∈ L ( s2[k] = s1 [k] ∧ s2[b] ≠ s1[b] ? (en dezelfde kroeg schenkt een ander bier) – s2[b] = l2[b] ∧ l2[d] = v2[d] (en de tweede drinker lust dat bier)
/
Informatica
5
12 Query opstellen in TC • Nu alles samenvoegen: { t | ∃v1 ∈V ( ∃v2 ∈V (t[eerste]=v1[d] ∧ t[tweede]=v2[d] ∧ v1[d] ≠ v2[d] ∧ ¬∃ s1 ∈ S ( ∃ l1 ∈ L ( s1[b] = l1[b] ∧ l1[d] = v1[d] ∧ ( ∃ s2 ∈ S ( ∃ l2 ∈ L ( s1[k] = s2 [k] ∧ s1[b] ≠ s2[b] ? s2[b] = l2[b] ∧ l2[d] = v2[d] ) ) ) ) ) ) }
/
Informatica
6
12 Vertaling TC naar SQL • Drie aandachtspunten: – De relaties met de gevraagde attributen moeten in de eerste “from” staan. Je kan altijd de hele reeks ∃ ... ( ∃ ... ∧ ... samenvoegen. (Stop bij de eerste ¬∃ of ∀ ) – Vertaal een ∀ naar een ¬∃ ¬ want die kan een “where not exists” worden. – De gekozen tupelvariabelen kunnen in SQL ineens worden gebruikt in een “as” constructie.
/
Informatica
7
12 Bier query q in SQL: • We zoeken paren van drinkers: { t | ∃v1 ∈V ( ∃v2 ∈V (t[eerste]=v1[d] ∧ t[tweede]=v2[d] ∧ v1[d] ≠ v2[d] ∧ de rest van de query ) ) } wordt select v1.d, v2.d from V as v1, V as v2 where v1.d <> v2.d and de rest van de query
/
Informatica
8
12 Bier query q in SQL • De rest van de query is eerst: “er bestaat geen kroeg die” ¬∃ s1 ∈ S ( ... ) wordt ... not exists ( select * from S as s1 where ... ) “een bier schenkt dat de ene drinker lust en een verschillend bier schenkt dat de andere drinker lust” Dit zijn weer een reeks ∃ constructies dus die kunnen ook allemaal in 1 “from” worden samengebracht. Dus: not exists ( select * from S as s1, L as l1, S as s2, L as l2 where ... )
/
Informatica
9
12 Bier query in SQL • Het geheel wordt dan: select v1.d, v2.d from V as v1, V as v2 where v1.d <> v2.d and not exists ( select * from S as s1, L as l1, S as s2, L as l2 where s1.b = l1.b and l1.d = v1.d and s1.k = s2.k and s1.b <> s2.b and s1.b <> s2.b and s2.b = l2.b and l2.d = v2.d )
/
Informatica
10
12 Aggregatie in SQL • Group By: om naar eigenschappen van een hele groep (verzameling of bag) tupels te vragen. • Having: om alleen sommige groepen te selecteren. • Aggregatie functies min, max, count, avg, om een eigenschap van een groep te berekenen. • Opgelet: aggregatiefuncties mogen niet zomaar worden gecombineerd. Dus “max(count(*))” mag niet! (via derived relations of views kan zo'n combinatie wel berekend worden)
/
Informatica
11
12 Bier-vragen met aggregatie • Geef alle kroegen, samen met het aantal bieren dat de kroeg schenkt. • Geef de kroegen die het grootste aantal bieren schenken. • Geef de bieren, samen met voor elk bier het aantal drinkers dat het bier lust en naar een kroeg gaat waar dat bier geschonken wordt. • Geef de bieren die in meer kroegen geschonken worden dan “De Koninck”. • Geef de drinkers die meer bieren lusten dan het gemiddelde aantal bieren dat drinkers van “Heineken” lusten. • Geef een overzicht van alle kroegen met per kroeg het aantal bezoekers van die kroeg die geen enkel bier lusten dat in die kroeg geschonken wordt.
/
Informatica
12
12 Voorbereiding lab 6 en colstructie 5 • Huiswerk tegen labsessie 6: – Maak de aggregatie-bieropgaven af (in SQL).
/
Informatica
13
