Übung Abfragen mit Access

TERRA-Datenbasis

TERRA Datenbasis TU-Dresden - Fakultät Informatik mit Datenbestand 1992 (noch mit DDR!!!)

TERRA-Datenbasis

Achtung!

Die Lösungen sind in Standard-SQL abgebildet und bedürfen der Anpassung sowie Aktualisierung in ACCESS

Es sei die relationale Datenbank TERRA mit Informationen über die politische Geographie_ und die Topographie der Erde gegeben. Formal lässt sich die Datenbank TERRA wie folgt beschreiben:

TERRA =

{LAND,LANDESTE,STADT,KONTINENT,BERG,EBENE,SEE,MEER,FLUSS,INSEL,WUESTE,ORGANISA,BENACHBA,ISTMITG,HATSITZ,GEHT_UEB,LIEGTAN,UMFASST,GEO_ FLUS,GE0_SEE,GEO_MEER,GEO_INSE,GEO_WUES,GEO_EBEN, GEO_BERG}

LAND(L_NAME,L_ID,EINWOHNER,ZUWACHS,FLAECHE,BSP,HAUPTSTADT, LTID,SYSTEM,REGCHEF)

LANDESTE(LT NAME,LTID,L_ID,EINWOHNER,LAGE,HAUPTSTADT)

STADT(ST_NAME,L_ID,LTID,EINWOHNER,BREITE,LAENGE) KONTINENT(KNAME,FLAECHE)

BERG(B_NAME, GEBIRGE, HOEHE, JAHR, LAENGE, BREITE)

EBENE(E_NAME,HOEHE,FLAECHE)

SEE(S_NAME,TIEFE,FLAECHE)

MEER(M_NAME,TEFE)

FLUSS(F_NAME,FLUSS,SEE,MEER,LAENGE,LAENGEU,BREITEU,LAENGEM, BREITEM)

INSEL(I_NAME, INSELGRUPP, FLAECHE, LAENGE,BREITE)

WUESTE(W_NAME, FLAECHE, WUESTENART)

ORGANISA(O_NAME, ABKUERZ, ART)

BENACHBA(LAND1,LAND2)

IST_MITG(L_ID,ABKUERZ,ART)

HATSITZ (ST_NAME, LTID, L_ID, ABKUERZ)

GEHT_UEB(MEERI, MEER2)

LIEGT_AN(ST_NAME,LTID, L_ID,F_NAME,S_NAME,M_NAME)

UMFASST(L_ID, K_NAME,PROZENT)

GEO_FLUS(LTID,L_ID, FSAME)

GEO_SEE(LTID,L_ID,S_NAME)

GEOMEER(LTID,L_ID,M_NAME)

GEOINSE(LT_ID,LI_D,I_NAME)

GEO_WUES(LTID, L_ID,W_NAME)

GEO_EBEN(LTID,L_ID,E_NAME)

GEOBERG(LTID,L_ID,B_NAME).

Die verwendeten Tabellen und Merkmalsnamen sind zum größten Teil selbsterklärend. Insbesondere bedeuten:

BSP - Bruttosozialprodukt, LAENGEU, BREITEU, LAENGEM, BREITEM - geographische Länge und Breite des Ursprunges bzw. der Mündung eines Flusses.

1.   Ermitteln Sie die Namen aller Kontinente.

Lösung:

select k_name

from kontinen;

(5 - relevante Sätze)

2.     Gesucht sind alle Inseln (Name) der Inselgruppe der Philippinen.

Lösung:

select i_name

from insel

where inselgrupp = 'Philippinen';

(9 - relevante Sätze)

3.     Gesucht sind alle Wüsten (Name) der Art "Sandwueste" mit einer Fläche größer als 25000 km2.

Lösung:

select w_name

from wueste

where wuestenart = 'Sandwueste'

and flaeche > 25000;

(9 - relevante Sätze)

4.     Gesucht sind alle Berge der Alpen (Beachten Sie, daß die Alpen in Teilgebirge aufgegliedert sind).

Lösung:

select bergname

from berg

where gebirge like '%Alpen%';

(4 - relevante Sätze)

5.     Gesucht sind alle Flüsse (Name), die in die Ostsee oder Nordsee münden und über 1000 km lang sind.

Lösung:

select f_name

from fluss

where (meer = 'Ostsee' or meer = 'Nordsee')

and laenge between 1000 and 10000;

(4 - relevante Sätze)

6.     Geben Sie für alle Millionenstädte, die in den Tropen liegen, die Namen und ihre Koordinaten an (Die Tropen liegen zwischen 23,27 Grad nördlicher und 23,27 Grad südlicher Breite).

Lösung:

select st name,laenge,breite

from stadt

where einwohner > 1000000

and einwohner < 20000000

and breite <= 23.27

and breite >= -23.27;

(28 - relevante Sätze)

7.     Gesucht sind alle Hochgebirge (Gebirge mit Bergen über 3000 m Höhe).

Lösung:

select distinct gebirge

from berg

where hoehe > 3000

and hoehe < 10000

and gebirge <> '*';

(28 - relevante Sätze)

8.     Geben Sie die Namen, die jeweilige Länderkennung und die Zahl der Einwohner für alle Länder an, die mehr als 45 Millionen Einwohner haben. Ordnen Sie dabei die Länder in absteigender Reihenfolge nach der Einwohnerzahl.

Lösung:

select l_ame,l_id,einwohner

from land

where einwohner > 45000000

order by einwohner desc;

(19 - relevante Sätze)

9.     Gesucht ist der Anteil der Meere an der Erdoberfläche (Angabe in Prozent). Hinweis: Der Erdradius beträgt 6370 km.

Lösung:

select 'Anteil der Meere an der Erdoberflaeche: ', ((4*3.1416*6.37*6.37)- sum(flaeche))/(4*3.1416*6.37*6.37)*100

from kontinent;

oder:

select 100-(100*sum(flaeche)/(4*3.1416*6.37*6.37))

from kontinent;

Beachte: Erdradius 6370km, O = 4*7r*R2 (Gesamtflaeche -Flaeche)/(Gesamtflaeche)*100 Multiplikation mit 6.37 statt 6370 resultiert aus der Normierung des Datenbestandes

(1 - relevanter Satz)

10.   Wie hoch ist die Einwohnerzahl des vereinigten Deutschlands?

Lösung:

select sum(einwohner)

from land

where l_id in ('DDR','D');

Beachte: Schalter GENAU GLEICH muß auf ON stehen, sonst (OFF):

select sum(einwohner)

from land

where l_id like 'DDR' or l_id like 'D__ '; mit __für 3 Leerzeichen

(1 - relevanter Satz)

11.   Gesucht sind alle Inselgruppen sowie die Anzahl der zugehörigen Inseln.

Lösung:

select inselgrupp,' mit ',count(*),' Inseln'

from insel

where inselgrupp <> '*'

group by inselgrupp;

(35 - relevante Sätze)

12.   Gesucht sind alle Flüsse mit mehr als zwei an ihnen liegenden Städten, sortiert nach dieser Anzahl.

Lösung:

select f_name,count(*)

from liegt_an

where f_name <> '*'

group by f_name

having count(*) > 2

order by 2;

Elbe nicht im Ergebnis, da z.Zt. Elbe nur für Hamburg erfaßt

(5 - relevante Sätze)

13.   Gesucht sind alle Städte, in denen mehrere Organisationen ihren Sitz haben, sortiert nach der Anzahl der Organisationen.

Lösung:

select st_name,count(*)

from hat_sitz

where st_name <> '*'

group by st name

having count(*) >= 2

order by 2;

(5 - relevante Sätze)

14.   Ermitteln Sie die Namen aller Flüsse, die sowohl durch Deutschland als auch durch Österreich fließen.

Lösung:

mit Kenntnis des l_id:

select distinct x.f_name

from geoflus x,geoflus y

where x.f_name = y.f_name

and x.l_id = 'D' and y.l_id = 'A';

ohne Kenntnis des l_id:

select distinct x.f_name

from geo flus x,geo flus y

where x.f_name = y.f_name

and x.l_id in

(select l_id from land where l_name = 'BundesrepublikDeutschland')

and y.l_id in

(select l_id from land

where l_name = 'Oesterreich');

(1 - relevanter Satz)

15.   Gesucht ist die größte Insel der Welt (Name und Fläche der Insel).

Lösung:

select i name,flaeche

from insel

where flaeche in

(select max(flaeche)

from insel

where flaeche < 99999999999);

(1 - relevanter Satz)

16.   Gesucht sind Namen und Hauptstädte aller Länder, die nicht Mitglied der UNO sind.

Lösung:

select l_name,hauptstadt

from land where l_id not in

(select l_id

from ist_mitg

where abkuerz = 'UNO');

Ergebnis ist semantisch falsch, aber das Datenmaterial gibt nicht mehr her !

(79 - relevante Sätze)

17.   Geben Sie von jedem Land in Afrika den Namen, die Anzahl der Einwohner, die Fläche, das Bruttosozialprodukt sowie den prozentualen Flächenanteil am Kontinent an.

Lösung:

select l_name,einwohner,flaeche,bsp,prozent

from land,umfasst

where land.l_id = umfasst.l_id

and k_name = 'Afrika';

 oder:    am Kontinent

select 1_name

Xland.flaeche/(kontinen.flaeche*1000000)*100

from land,umfasst,kontinen

where land.l_id = umfasst.l_id

and umfasst.k_name = kontinen.k_name

and kontinen.k_name = 'Afrika'

and land.flaeche <> 99999999999;

(56 - relevante Sätze)

18.   Geben Sie alle Länder (Name), von denen Tdle auf mehreren Kontinenten liegen, zusammen mit den jeweiligen Kontinenten an.

Lösung:

select distinct l_name (oder l_name,k_name)

from land_l,umfasst_u

where l.l_id = u.l_id and l.l_id in

(select l_id from umfasst

group by l_id

having count(*) >=2);

19.   Geben Sie zu jedem Land in Europa die Ebenen (des Staates) und deren Höhe an, sortieren Sie nach der Höhe.

Lösung:

select distinct l_name,geo_eben.e name,hoehe

from umfasst,land,ebene,geo_eben

where k_name = 'Europa'

and umfasst.l_id = land.l_id

and geo_eben.l id = land.l_id

and geo_eben.e name = ebene.e name

order by 4;

(6 - relevante Sätze)

20.   Ein Land, in dem mehr als 10 Prozent der Bevölkerung in Großstädten lebt, gilt als stark urbanisiert. Als Großstädte werden Städte mit mehr als 500000 Einwohnern betrachtet. Welche Länder der EG gelten demzufolge als stark urbanisiert?

Lösung:

select l_name,sum(s.einwohner/l.einwohner)

from stadt s,land 1,ist_mitg m1

where s.einwohner between 500000

and 20000000

and s.l_id = l.l_id

and l.l_id = ml.l_id

and abkuerz = 'EG'

group by l_name,l_einwohner

having sum(s.einwohner/l.einwohner) > 0.1;

(5 - relevante Sätze)

21.   Geben Sie zu jedem Fluß, der in einen anderen fließt, an, in welches Meer oder See dieser (der zuletztgenannte Fluß) mündet.

Lösung:

select a1.f_name,a1.fluss,b1.meer,b1.see

from fluss a1,fluss b1

where a1.fluss = b1.f_name

and (bl.meer <> '*' or b1.see <> '*' );

Beachte: in select-Klausel auch sum(s.einwohner)/l.einwohner möglich, in having-klausel nicht

oder: and (bl.meer in (select m name from meer) or bl.see in (select s name from see))

(43 - relevante Sätze)

22.   Gesucht sind alle US-amerikanischen Millionenstädte (Name) und die Organisationen (Name), die gegebenenfalls ihren Sitz in diesen Städten haben.

Lösung:

22. in dBASE IV nicht möglich, hier SYBASE-Losung: select stadt.stmame,abkuerz from stadt,hat sitz where stadt.st name *= hat sitz.st name and stadt.l_id='USA' and einwohner > 1000000;

(28 - relevante Sätze)