230 lines
4.5 KiB
Plaintext
230 lines
4.5 KiB
Plaintext
|
Datenbanken und Queries
|
|||
|
|
|
|||
|
|
Datenbank OPT (Standarddaten, Optimierung)
|
|||
|
|
|
|||
|
|
|
|||
|
|
Einfache Kommandos und Anfragen
|
|||
|
|
|
|||
|
|
delete x;
|
|||
|
|
delete inc;
|
|||
|
|
|
|||
|
|
query 3 * 7
|
|||
|
|
|
|||
|
|
query "Ralf Hartmut G<>ting" contains "Ralf"
|
|||
|
|
|
|||
|
|
create x: int
|
|||
|
|
|
|||
|
|
query x
|
|||
|
|
|
|||
|
|
update x := 7
|
|||
|
|
|
|||
|
|
query x
|
|||
|
|
|
|||
|
|
let inc = fun(n:int) n + 1
|
|||
|
|
|
|||
|
|
query inc
|
|||
|
|
|
|||
|
|
query inc(7)
|
|||
|
|
|
|||
|
|
list algebras
|
|||
|
|
|
|||
|
|
list algebra StandardAlgebra
|
|||
|
|
|
|||
|
|
Ausf<EFBFBD>hrbare Ebene
|
|||
|
|
|
|||
|
|
"Wieviele Tupel hat die Postleitzahlenrelation?"
|
|||
|
|
|
|||
|
|
query plz count
|
|||
|
|
|
|||
|
|
"Finde die Orte, die im Namen "wald" enthalten.
|
|||
|
|
|
|||
|
|
query plz feed filter[.Ort contains "wald"] consume
|
|||
|
|
|
|||
|
|
Wieviele sind das?
|
|||
|
|
|
|||
|
|
query plz feed filter[.Ort contains "wald"] count
|
|||
|
|
|
|||
|
|
"Finde die Postleitzahlen f<>r Hagen." (mit Index)
|
|||
|
|
|
|||
|
|
query plz_Ort plz exactmatch["Hagen"] consume
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
Mit Optimierer
|
|||
|
|
==============
|
|||
|
|
|
|||
|
|
"Wieviele Postleitzahlen geh<65>ren zu Orten mit <20>ber 10000 Einwohnern?"
|
|||
|
|
|
|||
|
|
select count(*)
|
|||
|
|
from [orte as o, plz as p]
|
|||
|
|
where [o:ort = p:ort, o:bevt > 100]
|
|||
|
|
|
|||
|
|
|
|||
|
|
Query mit 5 Bedingungen (3 Selektion, 2 Join)
|
|||
|
|
|
|||
|
|
select count(*)
|
|||
|
|
from [orte as o, plz as p1, plz as p2]
|
|||
|
|
where [o:ort = p1:ort, o:bevt > 70, p1:plz = p2:plz + 7, p2:plz > 60000,
|
|||
|
|
p2:ort contains "d"]
|
|||
|
|
|
|||
|
|
-> 156
|
|||
|
|
|
|||
|
|
[erzeugten Plan ansehen]
|
|||
|
|
|
|||
|
|
Ergebnis ansehen:
|
|||
|
|
|
|||
|
|
select *
|
|||
|
|
from [orte as o, plz as p1, plz as p2]
|
|||
|
|
where [o:ort = p1:ort, o:bevt > 70, p1:plz = p2:plz + 7, p2:plz > 60000,
|
|||
|
|
p2:ort contains "d"]
|
|||
|
|
|
|||
|
|
|
|||
|
|
Datenbank GERMANY (Geodaten)
|
|||
|
|
============================
|
|||
|
|
|
|||
|
|
Queries
|
|||
|
|
Mit graphischer Oberfl<66>che: Kategorien Germany.cat
|
|||
|
|
|
|||
|
|
Mit Optimierer
|
|||
|
|
==============
|
|||
|
|
|
|||
|
|
"Finde die Nachbarkreise von Magdeburg"
|
|||
|
|
|
|||
|
|
select * from kreis where [gebiet touches magdeburg, not(kname contains "Magdeburg")]
|
|||
|
|
|
|||
|
|
|
|||
|
|
"Finde die Gebiete, die vom Rhein durchquert werden und deren Fl<46>che gr<67><72>er als 1000 ist; gib den Namen, die Bev<65>lkerungszahl und das Gebiet an."
|
|||
|
|
|
|||
|
|
|
|||
|
|
select [k:kname, k:bev, k:gebiet]
|
|||
|
|
from [kreis as k, fluss as f]
|
|||
|
|
where [f:fname = "Rhein", k:gebiet intersects f:fverlauf, k:flaeche > 1000]
|
|||
|
|
|
|||
|
|
|
|||
|
|
"Finde die <20>brigen Gebiete" (graphisch anders darstellen)
|
|||
|
|
|
|||
|
|
select [k:kname, k:bev, k:gebiet]
|
|||
|
|
from [kreis as k, fluss as f]
|
|||
|
|
where [f:fname = "Rhein", k:gebiet intersects f:fverlauf, k:flaeche <= 1000]
|
|||
|
|
|
|||
|
|
|
|||
|
|
Den Rhein darstellen:
|
|||
|
|
|
|||
|
|
select * from fluss where fname = "Rhein"
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
"Welche Kreise liegen im Umkreis von 100 km um Dortmund?" (Einheiten stimmen nicht wirklich)
|
|||
|
|
|
|||
|
|
select * from kreis where distance(dortmund, gebiet) < 1
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
Welche Autobahnen <20>berqueren den Rhein?
|
|||
|
|
|
|||
|
|
select [a:aname, a:anr, a:averlauf]
|
|||
|
|
from [autobahn as a, fluss as f]
|
|||
|
|
where [f:fname = "Rhein", a:averlauf intersects f:fverlauf]
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
Datenbank BERLIN (Geodaten, Moving Objects)
|
|||
|
|
===========================================
|
|||
|
|
|
|||
|
|
|
|||
|
|
Queries
|
|||
|
|
Graphische Oberfl<66>che Hintergrund: berlinU.ses
|
|||
|
|
Ausf<EFBFBD>hrbare Ebene
|
|||
|
|
delete zug1;
|
|||
|
|
delete sixthirty;
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
"Sei zug1 der erste Zug der Linie U7Hin."
|
|||
|
|
|
|||
|
|
let zug1 = U7Hin feed extract[zug]
|
|||
|
|
|
|||
|
|
|
|||
|
|
"Zu welcher Zeit war zug1 unterwegs?"
|
|||
|
|
|
|||
|
|
query deftime(zug1)
|
|||
|
|
|
|||
|
|
|
|||
|
|
"Welche Strecke ist zug1 gefahren?"
|
|||
|
|
|
|||
|
|
query trajectory(zug1)
|
|||
|
|
|
|||
|
|
|
|||
|
|
"Wo war zug1 um 6:30?"
|
|||
|
|
|
|||
|
|
let sixthirty = theInstant(2003, 11, 20, 6, 30)
|
|||
|
|
|
|||
|
|
query val(zug1 atinstant sixthirty)
|
|||
|
|
|
|||
|
|
|
|||
|
|
"Wo war zug1 zwischen 6:30 und 6:45?"
|
|||
|
|
|
|||
|
|
query trajectory(zug1 atperiods theperiod(theminute(2003,11,20,6,30), theminute(2003,11,20,6,44)))
|
|||
|
|
|
|||
|
|
|
|||
|
|
"Wo waren die Z<>ge der Linie U7Hin, die um 8 Uhr unterwegs waren, zu dieser Zeit?
|
|||
|
|
|
|||
|
|
query U7Hin feed extend[at8: val(.zug atinstant eight)] filter[not(isempty(.at8))]
|
|||
|
|
project[at8] consume
|
|||
|
|
|
|||
|
|
Andere Formulierung:
|
|||
|
|
|
|||
|
|
query U7Hin feed filter[.zug present eight] extend[at8: val(.zug atinstant eight)]
|
|||
|
|
project[at8] consume
|
|||
|
|
|
|||
|
|
|
|||
|
|
"Wann halten Z<>ge der Linie U6Hin an der Station Mehringdamm"
|
|||
|
|
|
|||
|
|
Mehringdamm ist schon definiert in der Datenbank.
|
|||
|
|
|
|||
|
|
query mehringdamm
|
|||
|
|
|
|||
|
|
query U6Hin feed extend[atMD: deftime(.zug at mehringdamm)] project[atMD] consume
|
|||
|
|
|
|||
|
|
|
|||
|
|
"Finde alle Z<>ge, die um 9 Uhr unterwegs waren."
|
|||
|
|
|
|||
|
|
query nine
|
|||
|
|
|
|||
|
|
query Trains feed filter[.zug present nine] consume
|
|||
|
|
|
|||
|
|
|
|||
|
|
"Wo waren alle Z<>ge um 9 Uhr, die dann fuhren?"
|
|||
|
|
|
|||
|
|
query Trains feed filter[.Trip present nine] extend[at9: val(.Trip atinstant nine)]
|
|||
|
|
project[Id, Line, Up, at9] consume
|
|||
|
|
|
|||
|
|
Queries mit Optimierer:
|
|||
|
|
|
|||
|
|
"Finde alle Z<>ge, die um 9 Uhr unterwegs waren."
|
|||
|
|
|
|||
|
|
select *
|
|||
|
|
from trains
|
|||
|
|
where present(trip, nine)
|
|||
|
|
|
|||
|
|
"Wo waren alle Z<>ge um 9 Uhr, die dann fuhren?"
|
|||
|
|
|
|||
|
|
select [id, line, up, val(atinstant(trip, nine)) as at9]
|
|||
|
|
from trains
|
|||
|
|
where present(trip, nine)
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
Query aus Flyer:
|
|||
|
|
===============
|
|||
|
|
Graphische Oberfl<66>che Hintergrund: berlin.ses
|
|||
|
|
|
|||
|
|
"Finde Name, Stra<72>e und Ort aller italienischen Restaurants, die nicht weiter als 2 km vom Brandenburger Tor liegen."
|
|||
|
|
|
|||
|
|
select [r:name, r:strasse, r:geodata]
|
|||
|
|
from [restaurants as r, sehenswuerdpoi as s]
|
|||
|
|
where [s:name = "Brandenburger Tor", r:art = "Italienisch", distance(r:geoData, s:geoData) < 2000]
|