20150321_StartMondial - home.hs

Übung SQL / Mondial
Dr. Christian Senger
DB & IS II, SS 2015
21.03.2015
Einleitung
Im Folgenden sind zur Wiederholung eine Reihe von SQL-Übungsaufgaben zu lösen. Grundlage für die Aufgaben ist die Mondial Datenbank, auf deren Tabellen ihr Zugriff habt.
Vorbereitende Arbeiten
Öffne SQLPLUS und melde dich mit deinem Benutzeraccount (analog Account Rechenzentrum) und Passwort1 am Oracle Server mit der SID „oracledbwi“ an:
sqlplus <dein rechenzentrums account>/<passwort>@oracledbwi
bzw.
sqlplus <dein rechenzentrums account>@oracledbwi2
Du kannst dein Passwort jederzeit mit dem folgenden Befehl ändern:
alter user <dein account> identified by <neues passwort>;
Als Frontend kannst du entweder SQLPLUS (Windows Applikation), SQLPLUS Kommandozeilen Applikation) oder den SQL-Developer3 benutzen.
Für die folgenden vorbereitenden Schritte ist aber SQLPLUS am geeignetsten. Gib das folgende Kommando ein:
SELECT
*
FROM
mondial.city
WHERE
name LIKE 'Pa%';
Der Zugriff auf Tabellen in einem anderen Schema erfolgt durch voranstellen des Schemanamens vor den Tabellennamen (hier also mondial). Wenn einem das auf Dauer zu schreibintensiv ist, kann man sich ein Synonym einrichten, etwa so:
create synonym city for mondial.city;
In Zukunft kann man nun mittels des Synonyms city auf die Tabelle city im Schema
mondial zugreifen:
1
Falls du dich bisher noch nicht (auch nicht letztes Semester) am neuen Server mit der SID oracledbwi abgemeldet hast, so lautet dein initiales Passwort „kein“ - dies muss bei der ersten Anmeldung (mit SQLPLUS) geändert werden.
2
SQLPlus fragt euch dann nach eurem Passwort, das verdeckt eingegeben wird
3
Start >> Alle Programme >> Entwicklung >> Datenbanken >> Oracle ... >> Anwendungsentwicklung
SELECT
*
FROM
city
WHERE
name LIKE 'Pa%';
Verschaffe dir als nächstes einen Überblick, welche Tabellen es in der Mondial-Datenbank
gibt. Dies geschieht durch Zugriff auf das Metaschema der Oracle-Datenbank (Hinweis: Der
Schemaname muss groß geschrieben werden).
SELECT
table_name
FROM
all_tables
WHERE
owner='MONDIAL';
Ok, 28 Tabellen, ganz schön viel Arbeit, wenn wir für jede ein Synonym einrichten wollen,
aber es geht auch einfacher:
Wir basteln uns die notwendigen Statements mittels SQL einfach selbst4. Ausgangspunkt ist
das obige Statement, das nun um konstante Ausdrücke erweitert wird:
SELECT
'create synonym '||table_name||
' for mondial.'||table_name||';'
FROM
all_tables
WHERE
owner='MONDIAL';
Aha, ganz schön praktisch, jetzt nur noch mit dem Befehl
set pagesize 0
die störenden Überschriften weg und dann einfach mittels einmal Copy&Paste die Synonyme
anlegen.
Nochmal zurück zum Metaschema in dem alle Informationen über Benutzer, Tabellen, Spalten, Schlüssel, Fremdschlüssel, Constraints, Zugriffrechten, usw. der relationalen Datenbank
abgelegt sind.
Einen guten Einstiegspunkt für die Metainformationen bietet die View dictionary, die alle
Tabellen und Views mit Metainformationen auflistet und weiterhin für jeden Datensatz noch
eine kurze Beschreibung bereithält, für was die Tabelle/der View gut ist. Nutze diesen Einstiegspunkt und suche mittels eines SQL-Statements nach einer Tabelle in der Informationen
über die Attribute und deren Typen der einzelnen Tabellen zu finden sind.
Erstelle ein SQL-Statement das alle Attribute mit Datentypen für die Tabelle city im Schema mondial ausgibt.
Auf der Homepage findest du Informationen über das Mondial Schema. Besorge dir diese
Informationen und bearbeite mit deren Hilfe die untenstehenden Aufgaben mit dem Frontend
deiner Wahl. Versuche zumindest die ersten 10 Aufgaben zu schaffen.
4
Das nennt man SQL to generate SQL
Die korrekten Ausgabedatensätze auf die Fragen findest du auf der Homepage.
Übungsaufgaben
1.
2.
3.
4.
5.
6.
7.
8.
Welche Städte liegen auf dem Äquator?
Welche Organisationen haben ihren Sitz in Wien?
Welche Hauptstädte sind zugleich Sitz von Organisationen?
Welche Organisationen haben ihren Sitz in Asien (Ausgabe nach Name sortiert)?
Wie heißt die größte Insel?
Gib alle Länder ohne Berge aus.
Gib die Namen aller Hauptstädte, sortiert nach Name aus.
Gib die Namen und Einwohnerzahl aller Hauptstäde, absteigend sortiert nach Einwohnerzahl aus.
9. Gib die Stadt mit den meisten Einwohnern aus.
10. Welcher Fluss fließt durch die meisten Länder?
11. Wie lang ist die Außengrenze von Deutschland?
12. Wie viele Inseln gehören zu den kleinen Antillen?
13. Welche Inselgruppen bestehen aus sechs Inseln?
14. Welches ist die Inselgruppe mit den meisten Inseln?
15. Wie viele Protestanten gibt es auf der Welt?
16. In welchem Land werden die meisten Sprachen gesprochen?
17. Gib die Religionen zusammen mit ihren Anhängern, absteigend sortiert nach Anzahl
Anhänger aus
18. Welche Religion hat die geringste Anzahl Anhänger?
19. Wie heißt der höchste Berg Amerikas?
20. Ordne jedem See die am ehesten von der Größe her passende Insel zu und gib die Paare aus.
21. Wie heißt der „Hausberg“ von Tokyo5?
22. Gib alle Binnenländer (d.h. Länder ohne direkten Zugang zum Meer) aus (sortiert nach
dem Namen der Länder).
Anhang
Wenn du die Aufgaben auf deinem Rechner durchführen willst, kannst du dir dafür die Daten
der Mondial-Datenbank auf deinen Rechner laden. Die Daten stehen im Oracle und im
MySQL-Format auf der Homepage zur Verfügung, eine Portierung auf andere relationale
Datenbanken ist aber auch recht einfach, es müssen zumeist nur die Tabellendefinitionen angepasst werden. Eine ca. 40 seitige Einführung zu SQL anhand der Mondial Datenbank findest du ebenfalls auf der Kurshomepage.
Hinweis
Der Oracle Server befindet sich auf dem Rechner mit dem Namen iwi-w-vm-dbo.hskarlsruhe.de, Port 1521. Die Datenbankinstanz ist oracledbwi.hskarlsruhe.de
5
Im Schema Mondial gibt es eine Funktion abstand, die den Abstand zwischen zwei Koordinaten errechnet
Wenn du die Aufgaben zuhause und ohne Verbindung zur Oracle Datenbank an der Hochschule machen willst, findest du auf der Kurshomepage die DDL und DML-Statements für
Oracle und MySQL. Weiterhin findest du dort Anleitungen wie du dich von Zuhause aus mit
der Oracle-Datenbank an der Hochschule verbinden kannst.