Lösung

SQL Sprache
• Die strukturierte Abfragesprache SQL (englisch:
Structured Query Language) bildet einen
Standard zur Formulierung von Abfragen.
• Das SQL und das Abfragefenster bilden zwei
verschiedene Darstellungsformen derselben
Abfrage.
• Wenn man Einstellungen des Abfragefensters
ändern, aktualisiert Access automatisch den
korrespondierenden SQL- Befehl und
umgekehrt.
1
Beispiel.
Es sei die Tabelle FIRMA mit folgende
Struktur:
FIRMA(Steuerkode,Name,Kapital)
Welche sind die Firmen mit den Kapital
größer als 20.000 GE
2
3
4
• SQL ist eine deskriptive Anfragesprache.
Es wird nicht spezifiziert, wie ein Ergebnis
bestimmt werden soll, sondern was als
Ergebnis erwartet wird.
• In SQL besteht die Möglichkeit, über
sogenannte Unterabfragen, eine komplexe
Anfrage in Teilen zu entwickeln.
• SQL ist gleichzeitig DDL (Data Definition
Language) und DML (Data Manipulation
Language).
5
SQL als DDL
Mit Hilfe der DDL kann man wichtige Probleme lösen, u.
zwar:
• Schemadefinition
• Schemaänderung
• Zugriffspfade zur Anfrageoptimierung
• Generierung von Sichten.
Die wichtigsten Befehle sind:
• CREATE TABLE
• ALTER TABLE
• CREATE INDEX
• CREATE VIEW
• DROP TABLE
• DROP INDEX
• DROP VIEW
6
Der Befehl CREATE TABLE
Bevor mit einer Tabelle gearbeitet werden kann,
muß die Struktur der Daten vereinbart werden
(also der Feldname, Datentyp und Länge).In
allgemeiner Form wird eine Tabelle
folgendermaßen definiert:
CREATE TABLE Tabellenname
(Feld1 Datentyp[(Länge)] [NOT NULL],
Feld2 Datentyp[(Länge)] [NOT NULL],
Feld3 Datentyp[(Länge)] [NOT NULL], …
[CONSTRAINT Indexname {PRIMARY KEY|
UNIQUE|NOT NULL}]);
7
Beispiel 1
Man definiert die Tabelle „LIEFERANT“ mit 4
Attributen(Feldern):
CREATE TABLE LIEFERANT
(Lieferantkode INTEGER NOT NULL,
Lieferantname TEXT(25),
Adresse TEXT(25),Produktkode INTEGER);
Beispiel 2
Man definiert die Tabelle
KONTO(filiale,kontonr,kname,saldo)
mit Hilfe des DBVS Access
8
9
Wenn dieser
Befehl ausgeführt
wird, wird in
Access die
KONTO Tabelle
mit der
betreffenden
Struktur gebildet
10
Der Befehl ALTER TABLE
Bestehende Tabellen können geändert werden, indem ein
neues Feld eingefügt wird:
ALTER TABLE Tabellenname ADD COLUMN
Feldname Datentyp ... / DROP COLUMN
Feldname Datentyp ...;
Beispiele
ALTER TABLE Konto
ADD COLUMN Eröffnungsdatum DATE;
ALTER TABLE WARE ADD COLUMN
Standardpreis CURRENCY;
ALTER TABLE Personal
DROP COLUMN Geburtsdatum;
11
Der Befehl CREATE INDEX
Die Idee, die hinter der Definition eines Zugriffspfades
steckt ist, den Aufwand bei der Suche nach Daten zu
den Änderungs-operationen hin zu verlagern.
Die Methode besteht in der Definierung eines Indexes. Der
Befehl ist:
CREATE [UNIQUE] INDEX Indexbezeichner
ON Tabellenname (Feldname [order]
[, Feldname [order]]…) [CLUSTER];
wobei:
• ORDER kann ASC (Default) oder DESC sein;
• CLUSTER besagt daß der Index ein „clustering – index“
ist (höchstens einer pro Tabelle – physisches
Gruppieren);
• UNIQUE besagt, daß keine Duplikate möglich sind, kann
zur Wahrung der Schlüsseleinzigkeit gebraucht werden.
12
Beispiel 1
CREATE INDEX Städtenamen ON
Stadt(Name);
Beispiel 2
CREATE INDEX X ON Konto(Filiale, Kname)
CLUSTER;
CREATE UNIQUE INDEX Y
ON Konto(Filiale,Kontonr);
13
Der Befehl CREATE VIEW
Anstatt Daten physisch in ihrer Struktur zu ändern,
erlauben relationale Systeme mittels des
Sichtenkonzepts (engl. VIEWS) auch logische
Änderungen.
Views (Sichten) sind das Ergebnis einer Query, auf dem
weitere Operationen durchgeführt werden. Sie sind
virtuelle Relationen.
Der Befehl ist:
CREATE VIEW Sichtenname (S1, S2, …Sn)
AS SELECT F1, F2, …Fn
FROM T1 [, …, Tk]
[WHERE Bedingung]
[GROUP BY Feldname, …]
[WITH CHECK OPTION];
14
Mit dem Klausel WITH CHECK OPTION kann
unbefugtes Aktualisieren verhindert werden.
Beispiel 1
Zu den bereits existierenden Tabellen
LAND(Name, Einwohnerzahl, Hauptstadt,
LKode) und STADT(Name, Einwohnerzahl,
Lkode) soll mit Hilfe des Sichtenkonzepts eine
Tabelle "Hauptstadt" angelegt werden. Diese
soll sowohl den Name als auch den
prozentualen Anteil der Bevölkerung der
Hauptstadt an der Gesamtbevölkerung des
Zugehörigen Landes beinhalten:
15
CREATE VIEW Hauptstadt(Name, Bevölkerungsanteil)
AS SELECT Land.Haupstadt,
Stadt.Einwohnerzahl/Land.Einwohnerzahl * 100
FROM Stadt, Land
WHERE Land.Hauptstadt=Stadt.Name;
Beispiel 2
Bilde die Sicht aller Kunden und der Filialen, bei denen
diese entweder einen Kredit oder ein Konto haben.
CREATE VIEW Kundschaft AS
(SELECT filiale, kname FROM Konto)
UNION
(SELECT filiale, kname FROM Kredit);
16
Der Befehl DROP TABLE
Zum löschen der Schemadefinition benutzt man das Befehl
DROP TABLE Tabellenname;
Beispiel
DROP TABLE Land;
DROP TABLE Konto;
Bemerkung. Mit diesem Befehl entfernt man eine gesamte
Tabelle. Bei Löschen der Basistabellen werden die
darauf definierten Sichten und Index - Strukturen auch
gelöscht. Um unbedachte Fehler zu vermeiden,
verbieten einige Systeme das Löschen von Relationen,
die noch Tupel enthalten oder als Basis für Sichten
dienen.
17
Der Befehl DROP INDEX
Rolle: Löschen von Zugriffspfaden.
DROP INDEX Indexbezeichner;
Beispiel: DROP INDEX Y;
Der Befehl DROP VIEW
Rolle: Löschen von Sichten.
DROP VIEW Sichtenname;
Durch die Entfernung von Sichten keine Tupel
gelöscht werden.
Beispiel: DROP VIEW Kundschaft;
18
SQL als DML
Zur Verarbeitung der Daten betrachtet man im Rahmen der DML die
Punkte:
•
Suchen
•
Einfügen
•
Verändern
•
Löschen
Suchen der Daten
Man benutzt eine Basisstruktur, mit 3 Klauseln:
•
SELECT Felderliste – entspricht der Projektion und zählt die
gewünschten Felder in der Zieltabelle auf;
•
FROM Tabellenliste – entspricht dem Kartesischen Produkt der
Tabellen;
•
WHERE Bedingungen – entspricht dem Selektionsprädikat und
bezieht sich auf die Tabellen in der FROM Klausel. Die
Bedingung kann auch die Verknüpfungskriterien enthalten.
19
Die minimale Struktur einer SQL Anfrage ist:
SELECT
[ALL|DISTINCT|DISTINCTROW|TOP n]
{*|Feld1 [AS alias][,Feld2…]}
FROM Tabelle1,Tabelle2,…Tabelle m
WHERE Bedingung;
und enspricht dem Ausdruck:
F1,F2,…Fn ( Bedingung (T1 x T2 x … x Tm))
20
Bemerkungen.
• ALL – das Ergebnis wird alle Datensätze enthalten;
• DISTINCT – die doppelten Werte des betreffenden
Feldes werden im Ergebnis nicht eingetragen;
• DISTINCTROW – beseitigt die doppelten Datensaetze
aus der Datenquelle erhalten durch die Vereinigung der
Tabellen;
• TOP n – Im Ergebnis werden nur die ersten „n“
Datensätze angezeigt;
• * - Wenn * statt einer Felderliste angegeben wird,
werden alle Felder der Tabellen angezeigt.
• Die WHERE Klausel enthält auch die JOIN
Bedingungen.
• Die Bedingungen dürfen nicht Agregatfunktionen
enthalten.
• Die FROM Klausel kann auch Abfragennamen enthalten.
21
Beispiele durch Projektion
Es sei die Tabelle
STADT(Name, Einwohner, Lcode)
Erste Frage: Welche sind die Namen und Einwohnerzahlen aller
Städte ?
Lösung:
SELECT Name, Einwohnerzahl FROM Stadt;
Zweite Frage: Welche Länder (Lcode) sind in der Tabelle Stadt
abgelegt?
Lösung:
SELECT DISTINCT Landeskode FROM Stadt;
Dritte Frage: Alle Informationen aus Tabelle LAND.
Lösung:
SELECT * FROM Land;
22
Beispiele durch Selektion
Frage: Welche Städte mit mehr als 2 Millionen Einwohner
gibt es ?
Lösung:
SELECT Name, Einwohnerzahl
FROM Stadt
WHERE Einwohnerzahl > 2000000;
Bemerkung.
Innerhalb des Prädikates sind im allgemeinen Folgende
Operatoren zugelassen:
• Vergleichsoperatoren : >=, <, =, >, >=, <> (ungleich);
• BETWEEN
• IN
• NOT IN
• IS NULL ; IS NOT NULL
• Vergleichsoperator von Zeichenketten: LIKE
23
Beispiel 1
Man soll eine Liste mit den gelagerten
Waren aus den Lager 2 und 3 erhalten,
die die Preise bestimmt haben.
SELECT Benennung, Preis
FROM Lager
WHERE Lnr IN(2,3) AND Menge > 0 AND
Preis IS NOT NULL;
24
Beispiel 2
Man soll eine Liste der Studenten im zweiten,
dritten oder vierten Jahr anzeigen, deren Name
mit dem Buchstaben A anfängt und die bei der
Informatikprüfung Noten zwischen 8 und 10
erhalten haben.
SELECT Name, Note
FROM Leistungsspiegel
WHERE Jahr IN(2,3,4) AND Name LIKE „A*“ AND
Note BETWEEN 8 AND 10;
Bemerkung. Die SELECT - Klausel kann auch
Aggregatfunktionen enthalten.
25
Frage: Welche ist die Summe der Einwohnerzahlen aller Städte
aus Deutschland?
Lösung:
SELECT SUM(Einwohnerzahl)As Gesamt
FROM Stadt
WHERE Lkode=“D“;
Frage: Ermittelt die Anzahl aller Städte.
Lösung:
SELECT COUNT(*)As Städtezahl FROM Stadt;
Bemerkung.
Die Funktion COUNT hat zwei Arten u. zwar:
• COUNT (*) liefert die Anzahl der Saetze in der Ergebnistabelle;
• COUNT (DISTINCT Feldname) liefert die Anzahl der Saetze, die
verschiedene Werte bezüglich des angegebenen Feldes
aufweisen.
SELECT COUNT(DISTINCT Name) As LISTE
FROM Stadt;
26
Frage: Wie viele Kunden Leasingverträge mit der Firma X
in der Zeitspanne 1–30 Juni 2005 unterschrieben haben.
Lösung:
SELECT COUNT(*) AS Nr_Verträge
FROM Verträge
WHERE VDatum BETWEEN #06/01/05# AND
#06/30/05#;
Frage: Welche sind die Gesamtausgaben des Monats
August? Aber ihr Mittelwert?
Lösung:
SELECT SUM(Ausgaben) AS Gesamtausgaben,
AVG(Ausgaben) AS Mittelwert
FROM Kalkül
WHERE Monat=“August“;
27
Frage: Welche ist die größte, bzw. die
kleinste Anzahl der Abwesenheiten, die
vom Anfang des Jahres bis zum 31 März
2006 von den Angestellten im
Wirtschaftsbereich registriert wurden?
Lösung:
SELECT MAX([nr_abw]) AS Max_abw,
MIN([nr_absw]) AS Min_abw
FROM Personal
WHERE per IN(„jan“, „feb“, „märz“) AND
Bereich = „Wirtschaft“;
28
Gruppierung der Ergebnisstabelle
Man braucht noch zwei Klauseln : GROUP
BY und HAVING.
Frage: Wieviele Städte gibt es in den
einzelnen Ländern ?
Lösung:
SELECT Landeskode, COUNT(*)
FROM Stadt
GROUP BY Landeskode;
29
Problem: Finde alle Länder (Landeskode)
mit mehr als vier Millionenstädten.
Lösung:
SELECT Landeskode FROM Stadt
WHERE Einwohnerzahl > 1000000
GROUP BY Landeskode
HAVING COUNT(*) > 4;
30
Problem: Man ermittle die durschschnittliche bzw.
die gesamte Anzahl der Aktien, welche in den
Städten Bukarest und Ploiesti ausgestellt
wurden, eingeteilt nach ihrem Nennwert.
Lösung:
SELECT Stadt, Nennwert,
AVG([Anz_Akt]) AS Durchschnitt,
SUM([Anz_Akt]) AS Total
FROM Kapital
GROUP BY Stadt, Nennwert
HAVING Stadt IN(„Bukarest“, „Ploiesti“);
31
Problem: Man soll die Liste der schlecht –
zahlenden Kunden erhalten, welche
unbezahlte Rechnungen in einem Wert
höher als 2000 RON haben.
Lösung:
SELECT KName,
SUM([unbez_Wert]) AS Total
FROM Forderungen
GROUP BY KName
HAVING SUM(unbez_Wert) > 2000;
32
Problem: Man soll eine Übersicht derjenigen
Gebäudearten erhalten die durchschnittlich einen
Versicherungswert von höher als 30000 RON haben
und für welche mehr als 50 Personen eine
Versicherung abschlieesen wollten.
Lösung:
SELECT Art, AVG(versich_Wert) AS Durchschnitt,
COUNT(*) AS Versich_Gesamtanzahl
FROM Versicherungen
GROUP BY Art
HAVING AVG(versich_Wert) > 30000 AND
COUNT(Versich_kode) > 50;
33
Sortierung der Ergebnisstabelle
Die Tabellen sind per Definition ungeordnet. Für
eine bestimmte Reihenfolge festzulegen,
benutzt man eine ORDER BY – Klausel.
SELECT [Bereich] Felderliste
FROM Tabellenliste
[WHERE Bedingung]
[GROUP BY...]
[HAVING ... ]
[ORDER BY Feld1 [ASC/DESC]
[, Feld2 [ASC/DESC],..];
34
Bemerkung. Als Ordnungskriterium sind
auch Ferlderkombinationen, über Namen
oder Spaltenposition in der
Ausgaberelation spezifizierbar.
Problem: Zeig alle Städte Schwedens nach
Einwohnerzahl aufsteigend geordnet an:
Lösung:
SELECT Name, Einwohnerzahl FROM Stadt
WHERE Landeskode = „S“
ORDER BY Einwohnerzahl;
35
Problem: Ermittelt alle Länder nach Anzahl der
Städte sortiert. Bei gleicher Anzahl von Städten
sortiert aufsteigend nach Ländernamen.
Lösung:
SELECT Land.Name, COUNT(*)
FROM Stadt, Land
WHERE Stadt.Lkode = Land.LKode
GROUP BY Land.Name
ORDER BY 2, Land.Name;
Die Zahl 2 legt die zweite Spalte als
Ordnungskriterium fest. Eine Formulierung der
Art ORDER BY COUNT(*), Land.Name ist
unmöglich!
36
c) durch JOIN
Für Join die allgemeine Form (Equijoin)
lautet:
SELECT Feldernliste
FROM T1,T2, …Tm
WHERE Ti.Feldname = Tj.Feldname AND
Tq.Feldname = Tk.Feldname AND …
Tm-1.Feldname = Tm.Feldname;
37
Problem: Namen und Einwohnerzahl aller
Hauptstädte.
Lösung:
SELECT Stadt.Name,
Stadt.Einwohnerzahl
FROM Stadt, Land
WHERE Stadt.Name = Land. Hauptstadt;
Bemerkung.
Die WHERE-Klausel dieser Abfrage stellt
die eigentliche Beziehung dar.
38
Problem: Finde alle Staaten, zu denen
mindestens eine Beschreibung einer Stadt
vorhanden ist.
Lösung:
SELECT DISTINCT Land.Name
FROM Stadt, Land
WHERE Stadt.Lkode=Land.LKode;
39
Problem: Man soll alle, an die Kunden monatlich
gezahlten Zinsen berechnen und anzeigen, je
nach den angesammelten Summen bei einer
jährlichen Zinsenrate von 11%.
Lösung:
SELECT KUNDEN.Name,
KONTO.Summe*(0.11/12) AS Zins
FROM Kunden,Konto
WHERE KUNDEN.Kundenkode =
KONTO.Kundenkode
ORDER BY KUNDEN.Kundenkode;
40
Problem: Man soll eine Übersicht erstellen,
welche die fakturierten bzw. die einkassierten
Summen für jede Rechnung und jeden Kunden
anzeigt.
Lösung:
SELECT Rechnung.RNr, Kunde.KKode,
Rechnung.RSumme,
Einkassierungen.ESumme
FROM Rechnung, Kunde, Einkassierungen
WHERE Rechnung.KKode= Kunde.KKode
AND Kunde.KKode= Einkassierungen.KKode
ORDER BY Kunde.KKode;
41
Bemerkung. Der "normale" Join erzeugt nur Datensätze
in der Ergebnistabelle, wenn der Schluesselwert der
ersten Tabelle in der zweiten Tabelle vorkommt. Dieser
Join wird in Access INNER JOIN genannt.
Im Gegensatz dazu erzeugt der OUTER_JOIN in der
Ergebnistabelle zumindest alle Tupel einer der beiden
Tabellen: linker Outer Join (LEFT JOIN) oder rechter
Outer JOIN (RIGHT JOIN).
Die Verknüpfung der Tabellen kann mit Hilfe der
WHERE Klausel (wie in obenen Beispiele) oder mit Hilfe
der FROM Klausel definiert sein.
42
Inner-join
Es seien die Tabellen
FIRMA(Steuerkode, Name, Kapital)
43
Und
KONTO(Kontonr,Kundenkode, Summe, Zinsprozent,
Geldeinheit) gegeben.
44
Problem: Finde alle Kunden zu denen
mindestens ein Konto haben und zeige die
berechnete Zinsen an.
Lösung:
SELECT
FIRMA.Name,KONTO.Geldeinheit,KONT
O.Summe,
(KONTO.Summe*KONTO.Zinsprozent/12)
/100 AS Zins
FROM FIRMA INNER JOIN KONTO ON
FIRMA.Steuerkode=KONTO.Kundenkode;
45
46
Left outer join
Problem: Ermittelt volständige Informationen über
die Kunden(Firmen) (mit- oder ohne Konto) und
ihre Konto.
Lösung:
SELECT FIRMA.Steuerkode,FIRMA.Name,
FIRMA.Kapital,KONTO.Kontonr,KONTO.Geldeinheit,
KONTO.Summe*KONTO.Zinsprozent/12)/100 AS Zins
FROM FIRMA LEFT JOIN KONTO ON
FIRMA.Steuerkode=KONTO.Kundenkode
ORDER BY FIRMA.Name;
47
48
Right outer join
SELECT FIRMA.Steuerkode,FIRMA.Name,
FIRMA.Kapital KONTO.Kontonr,KONTO.Geldeinheit,
KONTO.Summe*KONTO.Zinsprozent/12)/100 AS Zins
FROM FIRMA RIGHT JOIN KONTO
ON FIRMA.Steuerkode=KONTO.Kundenkode
ORDER BY FIRMA.Name;
49
Autojoin
Im dieser Fall muß man zwei Mal dieselbe
Tabelle analysieren, deshalb macht man
einen Trick, mit Hilfe den sogennanten
„Etiquetten“
Erste Frage: Gibt es Städte in verschiedene
Ländern, die den gleichen Namen tragen?
Lösung:
SELECT A.Name
FROM Stadt A, Stadt B
WHERE A.Name = B.Name;
50
Zweite Frage: Namen und Preise aller
Radiogeräte welche den selben Preis
haben.
Lösung:
SELECT M.Name, K.Preis
FROM Produkte M, Produkte K
WHERE M.Preis = K.Preis AND
K.Name = „Radiogerät“;
51