Datenbanken

DDL

Tabellen anlegen mit SQLite3

Syntax des CREATE!Befehls

CREATE TABLE tabellenname ( Spaltenname1 datentyp1 [feldbeschränkung] Spaltenname2 datentyp2 [feldbeschränkung] […] PRIMARY KEY (spaltenliste) FOREIGN KEY (spaltenname) REFERENCES tabellenname(spaltenname) [ON DELETE CASCADE] CHECK (bedingung) )

Zum Anlegen einer Tabelle in einer Datenbank führt man die Spalten auf, die man für notwendig erachtet und weist ihnen Datentypen zu. Beispiel: CREATE TABLE abteilung ( abteilungsID INTEGER NOT NULL PRIMARY KEY, bezeichnung VARCHAR(50) ) Darüber hinaus können weitere Festlegungen getroffen werden:  Die Feldbeschränkung NOT NULL: schreibt vor, dass die Spalten bei der Eingabe von Datensätzen immer gefüllt werden müssen.  PRIMARY KEY definiert eine Spalte als Primärschlüssel. Dabei kann das Schlüsselwort PRIMARY KEY direkt hinter der Spaltendefinition stehen oder aber am Ende des CREATE-Befehls (wie in der obigen allgemeinen Definition). Letzteres ist erforderlich, wenn der Primärschlüssel aus mehreren Schlüsselfeldern besteht. Eine als Primärschlüssel gesetzte Spalte verlangt auf jeden Fall einen Eintrag, diese Spalte sollte also automatisch NOT NULL gesetzt sein. Einige Datenbanksysteme verlangen aber auch bei Primarschlüsselfeldern noch den Zusatz NOT NULL (bei SQLite3 ist dieses nicht notwendig!)  CHECK: nur bestimmte Werte sind zulassen: CHECK (gehalt >0) kann an das Ende der Tabellendefinition gesetzt werden.  FOREIGN KEY REFERENCES: definiert über eine Spalte der aktuellen Tabelle eine Beziehung zu einer Tabelle, die die Spalteninhalte als Primärschlüssel hat. Sobald eine Spalte mit einem Fremdschlüssel versehen wird, können dort nur noch Werte eingegeben werden, die auch in der als Primärschlüssel gesetzten Spalte der anderen Tabelle vorhanden sind. Wichtig ist daher, dass beide Spalten denselben Datentyp besitzen, sie können aber unterschiedlich heißen. Eine Tabelle, die einen Fremdschlüssel besitzt, heißt abhängige Tabelle oder „Detailtabelle“. Die Tabelle mit dem dazugehörigen Primärschlüssel heißt Vatertabelle oder Mastertabelle. Beispiel: Die Tabelle Abteilung ist die Vatertabelle der Tabelle mitarbeiter. Die Beziehung erfolgt über die Felder Mitarbeiter.abteilungsID und Abteilung.abteilungsID. Daher kann die Tabelle Mitarbeiter auch erst erstellt werden, wenn die Tabelle Abteilung mit ihrem Primärschlüssel existiert. Andererseits kann ein Datensatz in einer Mastertabelle nur gelöscht werden, wenn es in der Detailtabelle keine abhängigen Datensätze mehr gibt. Der Zusatz ON DELETE CASCADE erlaubt dagegen „kaskadierendes Löschen“: Zu einem zu löschenden Masterdatensatz werden sofort auch alle abhängigen Detaildatensätze gelöscht (dazu später mehr!). Bei der Erstellung der Tabellen einer Datenbank ist die Reihenfolge zu beachten: Immer erst die Mastertabelle, erst dann die Detailtabellen anlegen!

Datenbanken

DDL

Aufgaben! ! Für die Datenbank der Firma PCMeile€Berger€OHG ist das folgende vorläufige (und noch nicht optimale) Beziehungsdiagramm der Geschäftsdatenbank gegeben:

1) Bestimme für alle Tabellen des Beziehungsdiagramms die Master! und die Detailtabellen. 2) Erläutere, an welchen Stellen das Datenbankschema eventuell noch überarbeitet werden muss. 3) Definiere in SQLiteExpertPersonal3 unter File/NewDatabase eine neue Datenbank mit dem Namen Firma an. Gib dann im Reiter SQL die CREATE!Befehle für alle Tabellen des Beziehungsdiagramms ein. Halte dich für die zu verwendenden Datentypen an die Tabellendefinitionen auf der nachfolgenden Seite. Überlege, in welcher Reihenfolge die Tabellen erstellt werden können. Gib gleich bei der Erstellung der Tabellen die Primärschlüssel (falls vorhanden) und auch die Fremdschlüssel laut Beziehungsdiagramm an. Baue die Verbesserungen aus Aufgabenteil 1) noch nicht ein. Tipp: Da bei der Erstellung der Tabellen natürlich Fehler gemacht werden, solltest du die CREATE! Statements zusätzlich in eine Editor!Datei kopieren und diese als Datei firma.txt sichern. Über den Menü!Befehl SQL/Load SQL!script besitzt du in SQLiteExpertPersonal3 nämlich die Möglichkeit, die Datenbank nach dem Löschen aller Tabellen durch Einladen des Skriptes stets schnell wieder aufzubauen. Wie in Java können auch hier übrigens die Zeichen /* … */ oder !!! verwendet werden, um Teile des Skriptes (z.B. schon korrekt eingeladene CREATE!Statements) auszukommentieren.

Datenbanken

DDL

Tabellen der Datenbank Firma PCMeile€Berger€OHG Kategorie

Produkt

kategorieID

INTEGER NOT NULL

produktID

INTEGER NOT NULL

bezeichnung

VARCHAR(50)

bezeichnung

VARCHAR(50)

herstellerID

INTEGER

nettopreis

DECIMAL(10,2)

Bestellung bestellnr

VARCHAR(20) NOT NULL

lagerbestand

INTEGER

kundenID

INTEGER

kategorieID

INTEGER

bestelldatum

DATE

rechnungsdatum

DATE

gesamtbetrag

DECIMAL(10,2)

Abteilung abteilungsID

INTEGER

abteilung

VARCHAR(50)

Hersteller herstellerID

INTEGER NOT NULL

herstellername

VARCHAR(50)

Zahlungsart

Kunde kundenID

INTEGER NOT NULL

name

VARCHAR(50)

vorname

VARCHAR(50)

zahlungsartID

CHAR(1) NOT NULL

strasse

VARCHAR(50)

zahlungsart

VARCHAR(50)

plz

CHAR(14)

telefon

VARCHAR(50)

email

VARCHAR(50)

zahlungsartID

CHAR(1)

Verguetung verguetungsgruppe

CHAR(1)

gehalt

DECIMAL(10,2)

Mitarbeiter Ort

mitarbeiterID

INTEGER NOT NULL

plz

CHAR(14)

name

VARCHAR(50)

Ort

VARCHAR(50)

vorname

VARCHAR(50)

strasse

VARCHAR(50)

plz

CHAR(14)

Positionen bestellnr

VARCHAR(20)

abteilungsID

INTEGER

produktID

INTEGER

email

VARCHAR(50)

anzahl

INTEGER

verguetungsgruppe

CHAR(1)

Datenbanken

DML

Datensätze eingeben mit SQLite3

Syntax des INSERT!Befehls:

INSERT INTO tabellenname (spaltenliste) VALUES (werte)

Nachdem man die Tabellen angelegt hat, möchte man sie mit Daten füllen. Mit dem INSERT!Befehl wird ein neuer Datensatz erzeugt. Die Anzahl der Spalten, die man im INSERT!Befehl angibt, muss mindestens eins sein und kann maximal die Anzahl der Spalten der Tabelle betragen. Die Reihenfolge der einzugebenden Werte muss dabei mit der Reihenfolge der Spalten übereinstimmen. Spalten, die zuvor als NOT NULL definiert wurden, müssen gefüllt werden. Für jeden einzugebenden Datensatz ist ein neues INSERT!Statement abzusetzen. Beispiel: INSERT INTO Mitarbeiter (mitarbeiterID, name, vorname, abteilungsID) VALUES (20, 'Schmidt', 'Walter', 3) Sollen alle Spalten in der Reihenfolge ihrer Tabellendefinition gefüllt werden, kann man die Spaltennamen auch ganz weglassen und nur die Werte angeben: INSERT INTO abteilung VALUES (1, 'Geschaeftsfuehrung'); Die Datenbank prüft bei der Eingabe von Daten, ob die Speicherung des Datensatzes gültig ist. So wird geprüft, ob  der eingegebene Datentyp gültig ist: Im INTEGER!Feld kann man z.B. keinen String speichern,  notwendige Feldinhalte eingegeben wurden: Eine als NOT NULL gesetzte Spalte erfordert die Eingabe von Daten.  der Fremdschlüssel wirken kann: Es wird automatisch geprüft, ob der eingegebene Wert der Fremdschlüsselspalte auch in der Mastertabelle vorhanden ist.  die Werte des Primärschlüsselfeldes eindeutig sind (doppelte Werte können nicht eingegeben werden). Aufgabe: 1. Über das Administrations!Tool SQLiteExpertPersonal3können wir Daten recht einfach über den Reiter „Data“ in die bestehenden Tabellen eingeben. Wir wollen das Füllen der Tabellen hier aber über das oben angegebene DML!Statement durchführen: Gib in jede der erstellten Tabellen 3!4 Datensätze ein. Beachte wieder die Reihenfolge der Bearbeitung der Tabellen und erläutere deine Vorgehensweise mit Hilfe des Begriffs „referentielle Integrität“. Wenn du wie bei der Erstellung der Tabellen gleich eine txt! Datei mitschreibst, kannst du bei Fehleingaben immer wieder neu aufsetzen.

Alle Datensätze einer Tabelle anzeigen Das umfangreiche SELECT!Statement – der Kern der Datenbanksprachen SQL – werden wir uns später anschauen. Damit wir uns den gesamten Inhalt der Tabellen aber schon anzeigen lassen können, überprüfen wir unsere Tabellen durch das Statement: SELECT * FROM tabellenname;

Datensätze ändern und löschen Problemstellungen: Mitarbeiter Rudi Rakete zieht um in die Frauenstr. 23, 12487 Berlin. Mitarbeiter Kunibert Fröhlich verlässt die Firma. Daten unterliegen Veränderungen und müssen ständig aktualisiert werden. Der Update! und Delete! Befehl wird diesen laufenden Änderungen gerecht.

Datenbanken Syntax des UPDATE*Befehls:

DML UPDATE tabellenname SET spaltenname1 = wert1, spaltenname2 = wert2, spaltenname3 = wert3, […] WHERE auswahlbedingung;

UPDATE MITARBEITER SET plz= '12487', strasse = 'Frauenstr. 23' WHERE name = 'Rakete' and vorname = 'Rudi'; Achtung: Vergisst man die WHERE*Klausel, so werden alle Datensätze der Tabelle Kunde verändert, und alle Kunden haben somit dieselbe Adresse. Es gibt in SQL keinen UNDO*Befehl! Aufgaben:& 2. Ändere den Wohnsitz von Rudi Rakete. Welches Problem tritt hierbei auf? 3. Mitarbeiterin Eva Schmidt hat geheiratet und heißt nun Eva Klammer. Aktualisiere die Tabelle. 4. Durch geschickte Nachbestellung beim Großhändler haben alle Produkte der Kategorie „Tastatur“ aktuell einen Lagerbestand von 1000 Stück. Führe die Aktualisierung durch. 5. Der Gesamtpreis zur Bestellnummer 4 wurde falsch berechnet. Aktualisiere ihn!& 6. Die Postleitzahl 53000 von Bonn in der Tabelle Ort ist falsch. Aktualisiere die Postleitzahl in allen Tabellen der Datenbank durch die korrekte Postleitzahl 53111.& & Syntax des DELETE*Befehls:

DELETE FROM tabellenname [WHERE auswahlbedingung]

Ohne Auswahlbedingung, also durch den Befehl DELETE FROM tabellenname, wird der Inhalt der gesamten Tabelle gelöscht. Erst die Auswahlbedingung ermöglicht ein gezieltes Löschen von Datensätzen: Beispiel: & DELETE FROM Kunde where kundeID=1; löscht nur den einen Datensatz mit der Kundennummer 1. Beim Löschen von Datensätzen werden auch die Integritätsregeln der Datenbank berücksichtigt. Wenn ein Fremdschlüssel auf eine Tabelle und noch Datensätze in der zugehörigen Detailtabelle existieren, wird das Datenbanksystem das Löschen des ent* sprechenden Datensatzes ablehnen. Liegt also im Beispiel in der Tabelle Bestellung&nur ein einziger Datensatz des Kunden mit der Kunden*ID 1 vor, so wird das Löschen verhindert. Man muss also zunächst die Datensätze aus der abhängigen Detailtabelle löschen. Übungen:& 7. Lösche in der Tabelle Ort die falsche Postleitzahl von Bonn! 8. Mitarbeiter Kunibert Fröhlich verlässt die Firma. Lösche ihn in der Datenbank. 9. Der Kunde Klaus Brinks storniert seine Bestellung mit der Nummer 3. Beachte, dass eine Bestellung aus verschiedenen Positionen bestehen kann. Erläutere das Problem! 10. Durch ein Insert*Skript hat es in der Tabelle Bestellung Fehler bei den Datumsfeldern gegeben. Wenn man eine neue Tabelle Bestellung_neu erstellt als select * from bestellung, stimmen die Datumsangaben wieder, allerdings sind die Felder nun vom Typ NUM. Wie kann man hier eine geschickte Aktualisierung der Tabelle Bestellung durch ein Update vornehmen? Hinweis: Mit einer Funktion SUBSTR(Zeichenkette,Startposition,Teilstringlänge) kann man innerhalb eines SELECT*Statements Zeichenketten „zerpflücken“ : SELECT SUBSTR(bestelldatum,1,2) FROM Bestellung_neu liefert den Tag eines Datums (also z.B. die 12 in 12.10.2012). Mit der Funktion DATE() lässt sich ein String in ein Datumsfeld umwandeln: SELECT DATE('2012'||'-'||10||'-'||12) liefert das Datum 12.10.2012. Beachte die amerikanische Datumsschreibweise!

Datenbanken Tabellendefinitionen+ändern+

DDL + mit SQLite3

ALTER TABLE tabellenname ADD spaltenname datentyp; RENAME TO name;

Syntax&des&&ALTER/Befehls:&

In&den&meisten&Datenbanksystemen&kann&der&ALTER/Befehl&erheblich&mehr,&als&hier&für&SQLite3&angegeben& wird.&Für&unsere&Zwecke&reicht&diese&Definition&aber&aus.&&

&

Problemstellung:&Bei&der&Tabellendefinition&der&Tabelle&Mitarbeiter&wurde&&ein&Fehler& gemacht:&Eine&Spalte&für&das&Geburtsdatum&der&Mitarbeiter&wurde&vergessen.&

Durch& ALTER TABLE mitarbeiter ADD geburtsdatum DATE; wird&die&Spalte&„Geburtsdatum“&als&letzte&Spalte&der&Tabelle&angehängt.&Es&ist&nicht&möglich&Spalten& zwischen&vorhandenen&einzufügen.&Leider&ist&es&in&SQLite3&auch&nicht&möglich,&nachträglich&einen& PRIMARY KEY oder&einen&FOREIGN KEY&zu&definieren.&Ebenso&kann&man&nicht&wie&in&anderen& Datenbanksystemen&Spalten&löschen&oder&Datentypen&von&Spalten&ändern.&Es&soll&aber&gezeigt&werden,&wie& man&sich&behelfen&kann,&wenn&die&Tabelle&schon&mit&Daten,&die&man&nicht&verlieren&möchte,&gefüllt&ist:& Weiterführendes&Beispiel:&&Wir&nehmen&an,&bei&der&Tabelle&Verguetung&wurde&vergessen,&&das&Attribut& verguetungsgruppe&als&Primärschlüssel&zu&deklarieren:& Zunächst&wird&die&Tabelle&umbenannt&in&eine& Tabelle&Verguetung_alt.& Anschließend&erzeugt&man&erneut&die&korrekt& aufgesetzte&Tabelle&Verguetung&mit&Angabe&des& Primärschlüssels.&

ALTER TABLE Verguetung RENAME to Verguetung_alt;

CREATE TABLE Verguetung ( verguetungsgruppe INTEGER NOT NULL PRIMARY KEY, Nun&holt&man&sich&die&Datensätze&aus&der&Tabelle& gehalt Decimal(10,2) Verguetung_alt+&in&die&Tabelle&Verguetung.& ); Abschließend&entfernt&man&die&Tabelle& Verguetung_alt&durch&ein&drop/Kommando.& INSERT into Verguetung select * Dieses&ist&allerdings&nur&möglich,&wenn&die&zu& FROM Verguetung_alt; löschende&Tabelle&nicht&Mastertabelle&einer& DROP table Verguetung_alt; weiteren&Tabelle&ist.&& Syntax&des&DROP/Befehls:&

DROP TABLE tabellenname; Der&Drop/Befehl&ist&mit&Vorsicht&zu&genießen!&Nach&seiner&Ausführung&sind&alle&Daten&und&auch&die& Definition&der&zugehörigen&Tabelle&weg.&Es&gibt&keine&Möglichkeit&mehr&diese&Daten&über&einen&Undo/ Befehl&zurückzuholen!& & Aufgaben:+ Die&nachfolgenden&Aufgaben&lassen&sich&natürlich&einfach&im&Administrationstool&erledigen,&es&sollen&hier& aber&tatsächlich&nur&die&SQL/Befehle&benutzt&werden!&& 1. Führe&die&im&vorstehenden&Text&beschriebenen&Änderungen&&an&der&Tabelle&Mitarbeiter&an&deiner& Firmen/Datenbank&durch.&Aktualisiere&dann&die&Geburtsdaten&mittels&Updates.& & &

Datenbanken

DDL

2. Ändere die Tabelle Hersteller ab, füge ein neues Attribut kontaktperson hinzu. Aktualisiere die Spalte kontaktperson mittels Updates. 3. In den Tabellen Kunde, Mitarbeiter und Ort wurde das Attribut plz jeweils mit dem TYP CHAR(14) ersehen. Dieser Typ ist für Orte in Deutschland zu groß angelegt. Ändere ihn in allen Tabellen passend ab. Beachte dabei, dass die Tabelle Ort eine Mastertabelle für die Tabellen Mitarbeiter und Kunde ist und die Tabelle Kunde für die Tabelle Bestellung! 4. In Aufgabe 2 von AB07"DDL hast du noch weitere Stellen aufgeführt, an denen das Datenbankschema geändert werden muss. Führe nun auch diese Änderungen in deiner Datenbank durch, so dass endgültig alle Tabellen vollständig mit Primärschlüssel und Fremdschlüssel versehen sind. Das vollständiges Datenbankschema soll dann so aussehen:

Zusätzlich soll die Tabelle Positionen „kaskadierendes Löschen“ erlauben: Wenn ein Datensatz aus der Tabelle Bestellung gelöscht wird, müssen gleichzeitig alle Positionen zu dieser Bestellung verschwinden. Probiere dieses „kaskadierende Löschen“ auch aus: Lösche alle Bestellungen und Positionen zur Bestellnummer 4.

Datenbanken

QL mit SQLite3!

Aufbau!des!SELECT%Befehls! ! ! Syntax des SELECT!Befehls:

SELECT FROM tabellenname [WHERE auswahlbedingung] [GROUP BY spaltenliste] [HAVING auswahlbdingung] [ORDER BY spaltenliste]

Nachdem man die Tabellen angelegt und mit Daten gefüllt hat, kann man endlich mit der Datenbank arbeiten. Hauptsächlich wird man Daten abfragen wollen. Dazu gibt es den SELECT!Befehl. Die Befehle in eckigen Klammern [ ] sind optional:!  Hinter SELECT werden die auszuwählenden Spalten angegeben. Die Werte erscheinen in der Reihenfolge, in der sie abgefragt werden.  Mit FROM wählt man die Tabelle aus, in der sich die abzufragenden Spalten befinden.  Mit WHERE kann man die Suche einschränken, indem man z.B. den Wert einer Spalte festlegt, der nötig ist, damit die anderen gesuchten Werte des Datensatzes angezeigt werden.  Mit GROUP BY können Werte zu Gruppen zusammengefasst werden, um z.B. Berechnungen durchzuführen.  Über den HAVING!Befehl können die Gruppen wiederum eingeschränkt werden.  Mit ORDER BY lassen sich die Ergebnisse sortiert anzeigen.

Problemstellung: Mitarbeiterin Eva Klammer aus der Personalabteilung soll eine Gesamtübersicht über alle PLZ!Bereiche erstellen, in denen die Mitarbeiter ihrer Firma wohnen. Der!Grundbefehl!SELECT!–!FROM! Alle Spalten einer Tabelle ausgeben: Wenn man alle Daten einer Tabelle abfragen möchte, kann man ein * als Platzhalter verwenden. Spalten auswählen: Um nur bestimmte Spalten einer Tabelle auszugeben, fügt man eine Liste von Spaltennamen an: Alias für Spaltennamen und Tabellen vergeben: Tabellen# und Spaltennamen kann man durch Aliasnamen abkürzen. Der Sinn dieser Aktion erschließt sich erst, wenn man Funktionen verwendet oder Abfragen über mehrere Tabellen erstellt. Identische Zeilen in der Ausgabe zusammenfassen: Zwei Zeilen sind identisch, wenn sie in allen Spalten denselben Wert besitzen:

SELECT * FROM Mitarbeiter;

SELECT name, vorname, strasse, plz FROM Mitarbeiter; SELECT plz FROM Mitarbeiter AS M; SELECT plz FROM Mitarbeiter M; bzw. SELECT plz AS Postleitzahl FROM Mitarbeiter; SELECT DISTINCT plz FROM Mitarbeiter;

Aufgaben! 1. Ermittle aus der Tabelle Mitarbeiter die Mitarbeiter#ID, die Abteilungsnummer und die Vergütungsgruppe. 2. Liste alle Artikel mit deren Bezeichnungen, Preisen und Lagerbeständen auf. 3. Liste aus der Tabelle Kunde die Namen und die jeweilige Zahlungsart auf. 4. Gib alle Hersteller an. 5. Es sollen alle verschiedenen Postleitzahlen der Kunden ermittelt werden.

Datenbanken

QL

Problemstellung: Mitarbeiter Rudi Rakete möchte aus der Gesamtprodukt9Palette den Lagerbestand aller PCs auflisten, die weniger alle 500,00 € netto kosten. In einer zweiten Abfrage möchte er alle Produkte ermitteln, die noch keine Preisangabe haben. SELECT#mit#Bedingung# Suche einschränken: Oft möchte man nicht alle Datensätze, sondern den Suchbereich weiter einschränken: Abfragen mit Vergleichsoperatoren: Mit den Vergleichsoperatoren (=, > , < und ihre Kombinationen) kann auf exakte Übereinstimmung oder auf Mindest9 bzw. Höchstwerte geprüft werden.

SELECT bezeichnung, nettopreis FROM Produkt WHERE kategorieID=1; SELECT * FROM Produkt WHERE nettopreis < 500.00;

Intervalle können mit [NOT]#BETWEEN abgefragt werden, das gilt für Zahlen und auch für Buchstaben.

SELECT * FROM Produkt WHERE bezeichnung IN ('DELL 300', 'DELL 500');

Vergleiche von Text, dessen genauen Wert man nicht kennt, werden mit [NOT]#LIKE und [NOT] IN selektiert. Als Platzhalter für Text beliebiger Länge kann das „%“9Zeichen, für genau ein Zeichen der Unterstrich „_“ verwendet werden.

SELECT * FROM Produkt WHERE bezeichnung LIKE 'D%L%'; (liefert alle “DELL‘s”)

Verknüpfungen mit OR: Die Verknüpfung mit OR achtet darauf, dass eine der Bedingungen zutrifft: Verknüpfungen mit AND: Bei der Verknüpfung mit AND müssen beide Bedingungen zutreffen: Abfragen mit NOT: Es wird geprüft, ob ein Feld einen Eintrag enthält oder ob ein Feld einen bestimmten Eintrag nicht hat.

SELECT * FROM Produkt WHERE nettopreis BETWEEN 500.00 AND 1000.00;

SELECT * FROM Produkt WHERE bezeichnung LIKE 'HP___'; (3 Unterstriche für 3 Zeichen) SELECT bezeichnung, nettopreis FROM Produkt WHERE kategorieID=1 OR nettopreis < 500.00 SELECT bezeichnung, nettopreis FROM Produkt WHERE kategorieID=1 AND nettopreis < 500.00 SELECT produktID,bezeichnung FROM Produkt WHERE nettopreis IS [NOT] NULL; oder SELECT * FROM Produkt WHERE NOT bezeichnung LIKE 'D%L%'; #

Aufgaben# 6. Liste alle Artikel der Tabelle Produkt auf, deren Nettopreis höher als 100 Euro liegt. 7. Liste alle Mitarbeiter auf, die in der Abteilung 2 beschäftigt sind. 8. Liste alle Artikel auf, die zur Kategorie „Monitor“ gehören. 9. Die Firma möchte eine Jubiläumsaktion starten. Dazu lässt sie sich erstens Kunden sowohl aus Bonn als auch aus Münster anzeigen und zweitens alle Kunden ausgeben, die wie der Firmengründer (Dieter Schulz) heißen. Verbinde beide Werbeaktionen der Firma. Beachte, dass du die jeweilige Auswahl in Klammern setzen musst. 10. Gib alle Kunden an, deren Kundennummer kleiner als 3 ist und die nicht in Münster wohnen. 11. Gib ein SELECT9Statement an, das alle Kunden ausgibt, die in Bonn wohnen oder deren PLZ nicht mit 5 beginnt und deren Zahlungsart ‚PayPal‘ ist. Erläutere daran die Rangfolge logischer Operatoren, indem du unterschiedlich klammerst und die Ergebnisdatensätze vergleichst. Rangfolge#logischer#Operatoren:#Logische Operatoren werden in der Reihenfolge NOT, AND, OR ausgewertet. Bedingungen in Klammern werden vorrangig überprüft.

Datenbanken

QL

SortierenundGruppieren Problemstellung:FüreineWerbeaktioninAhlensollenalleKundenausBonnalphabetischsortiert ausgegebenwerden.

 Datengeordnetanzeigenlassen: DieOrdnungentsprichtdemDatentypderbetroffenen Spalte:Textewerdenalphabetisch,ZahlenderGrößeihrer Wertenachgeordnet.

SELECT name, vorname FROM kunde ORDER BY name;

SELECT name, vorname MankanndieAusgabeauchnachmehrerenSpaltensortieren FROM kunde lassen.DieReihenfolgewirdvonderPositionderSpalteim WHERE plz = '53111' ORDER BY name, vorname; ORDERBYbestimmt. UmkehrungderOrdnung: MankanndieDatenmitdemZusatzDESCentgegender „Standardordnung“(ASC),dievomkleinstenzumgrößten Werterfolgt,anzeigenlassen:

SELECT name, vorname FROM kunde WHERE plz = '53111' ORDER BY name DESC; 

Übungen 1. GibeineweitereKundin„AnnaSchulz“ausdemPLZͲBereich53111einundsortieredanndieKundenaus diesemBereichabsteigendnachNameundVorname.Wasfälltauf? 2. ListealleProdukteinderReihenfolgederKategorieIDunddannalphabetischauf. 3. ListealleMitarbeiternachihrerVergütungsgruppeunddannnachderAbteilungauf.Die Vergütungsgruppesollabsteigendsortiertwerden. 4. GiballeArtikelderKategorie4(Festplatten)absteigendnachdemPreisan. 5. ListealleKunde,dieperKreditkarte(ID=3)bezahlen,nachPostleitzahlenbezirkensortiertauf. 6. SelektiereName,Vorname,StraßeundPLZderKundenausAhlenundsortieredabeizunächstnach Postleitzahl,dannnachNamenundVornamen. 7. DieKundenausdemPLZͲBezirk59229sindwichtiger.ÄnderedieAbfrageaus6soab,dassdiese Kundenobenstehen.

SELECTmitGruppenbildung Problemstellung:DerChefunsererFirmamöchtewissen,wievieleKundendieeinzelnen PostleitzahlenbezirkeinAhlenaufweisen.

 Wertedurchcount(*)zusammenfassen: Count(*)wirdwieeinSpaltennameverwendet.Dieganze TabellewirdzueinerGruppezusammengefasst.

DurchAngabeeinerweiterenSpaltekanndieAnzahlder ZeilenzudenzugehörigenSpaltenwertengezähltwerden. EinschränkungderGruppenbildung: DiedurchGROUPBYentstandenenErgebnisselassensich durchHAVINGeinschränken(HAVINGistdasWHEREdes GROUPBY)

SELECT count(*) FROM kunde; SELECT plz, count(*) FROM kunde WHERE plz='59227'OR plz ='59229' GROUP BY plz; SELECT plz, count(*) FROM kunde GROUP BY plz having count(*) >=2;

 Übungen 1. SorgefüreinesortierteAusgabederPLZͲBezirkenachderAnzahlderdortlebendenKunden.DieStadt mitdenmeistenKundensolldabeizuerstausgegebenwerdenunddieauszugebendePostleitzahlsollte mindestens10Kundenhaben. 2. GibdieAnzahlderArtikelproKategorie,dieteurerals400€sind,aus. 3. GibdieBestellnummernvonallenBestellungenausderTabellePositionenaus,beidenenfünfArtikel odermehrbestelltwurden.SorgedabeiauchfürdieAusgabederGrößenach. 

Datenbanken

QL

SELECT%mit%Mengenoperationen! Aus!der!Mengenlehre!kennen!wir!die!Vereinigung,!den! Durchschnitt!und!die!Differenz!von!Mengen:! Wir!interpretieren!!die!Datensätze!zweier!Tabellen!A!und!B!als! Mengen!und!übersetzen!die!Mengenoperationen! Vereinigung,!Durchschnitt!und!Differenz!in!die! Datenbanksprache:! !

! Die!Vereinigungsmenge " #!ist!die!Ergebnismenge!aller!Datensätze,!die!in! Tabelle!A!oder!in!Tabelle!B!oder!in!beiden!Tabellen!enthalten!sind.!! Hierfür!verwendet!SQL!das!Schlüsselwort!UNION:! SELECT spaltenliste FROM tabelleA UNION SELECT spaltenliste FROM tabelleB

! " #!

$ #!!

!

!

Die!Durchschnittsmenge! $ #enthält!nur!diejenigen!Datensätze,!die!sowohl!in! Tabelle!A!und!in!Tabelle!B!enthalten!sind.!! Hierfür!verwendet!SQL!das!Schlüsselwort!INTERSECT:% SELECT spaltenliste FROM tabelleA ! INTERSECT SELECT spaltenliste FROM tabelleB !

Die!Differenzmenge! %#!ist!die!Menge!aller!Datensätze!der!Tabelle!A,!die!nicht! in!Tabelle!B!enthalten!sind.!! Hierfür!verwendet!SQL!das!Schlüsselwort!EXCEPT!(bzw.!MINUS%je!nach! Dialekt):! ! %#!

SELECT spaltenliste FROM tabelleA EXCEPT SELECT spaltenliste FROM tabelleB

Mengenoperationen!werden!meist!zur!Zusammenführung!oder!Bereinigung!von!Datensätzen!benötigt.! Man!kann!z.B.!Ergebnisse!mehrerer!Abfragen!zusammenführen.! !

!

!

Problemstellung:!Es!gibt!Mitarbeiter,!die!gleichzeitig!auch!Kunden!sind.!In!der!PersonalP abteilung!will!man!sich!darüber!Übersicht!verschaffen:! P!Gib!die!VorP!und!Nachnamen!Kunden!und!Mitarbeiter!aus,! P!Gib!alle!Mitarbeiter!aus,!deren!Kombination!aus!VorP!und!Nachnamen!auch!in!der! !!Kundentabelle!vorkommt! P!Gib!alle!Mitarbeiter!aus,!deren!Kombination!aus!VorP!und!Nachnamen!nicht!in!der! !!Kundentabelle!vorkommt.!

Übungen% 1. Gib!einen!Mitarbeiter!als!Kunden!ein!(z.B.!Klaus!Brinks)!und!teste!an!den!beiden!Tabellen!Kunde! und!Mitarbeiter!das!!SELECT!mit!Mengenoperationen.! 2. Mitarbeiter!Brinks!und!Meyer!haben!sich!die!Bearbeitung!des!Kundenstamms!alphabetisch! aufgeteilt:!Beim!Buchstaben!„S“!hat!es!aber!nun!ein!Problem!gegeben.!Herr!Brinks!sollte!alle! Kunden!mit!„Sch“!betreuen,!Herr!Meyer!alle!anderen!Kunden!mit!Anfangsbuchstaben!„S“.!Die! beiden!verwalten!ihre!Vertragsabschlüsse!auf!ihren!Notebooks!in!einer!lokalen!Datenbank!und! überspielen!die!Tabellen!dann!an!die!Zentrale.!Sie!besitzen!in!der!lokalen!Datenbank!daher!

Datenbanken Tabellen Kunden_Brinks und Kunden_Meyer, in der sie der Einfachheit halber nur die ID als Primärschlüssel und den Namen und Vornamen der Kunden speichern. a) Erstelle die Tabellen Kunden_Brinks und Kunden_Meyer in deiner Datenbank und gib die folgenden Datensätze ein. Kunden_Brinks Kunden_Meyer LfdNr Name Vorname LfdNr Name Vorname 1 Kohl Walter 1 Hesse Herrmann 2 Schulz Dieter 2 Schulz Dieter 3 Schmidt Anne 3 Stode Sonja 4 Schrott Theo 4 Westhoff Franz b) Liste nun alle Namen derjenigen Kunden auf, mit denen Mitarbeiter Brinks und Außendienstmitarbeiter Meyer Verkaufsabschlüsse getätigt haben. Was fällt auf? Verwende anschließend den Befehl UNION$ALL. c) Liste nun doppelte Datensätze auf, die sowohl bei Mitarbeiter Meyer, als auch bei Mitarbeiter Brinks vorkommen, um zu verhindern, dass ein Kunde von zwei verschiedenen Mitarbeitern betreut wird. d) Liste nun alle Kunden des Mitarbeiter Brinks auf, die nicht auch vom Mitarbeiter Meyer betreut werden. e) Erweitere deine Tabellen um zwei Spalten „Ort“ für den Kundensitz und „Status“ für Stammkunde (S) oder nicht#Stammkunde (N). Fülle die neuen Spalten in den schon existierenden Datensätze mit Inhalten (Orte: Köln, Essen, Bochum, Bonn, Werl). Selektiere nun alle Kunden aus der Tabelle Kunden_Brinks mit dem Status S und alle Kunden aus der Tabelle Kunden_Meyer, die aus Köln kommen. f) Liste alle Kunden der Mitarbeiter Brinks und Meyer auf, die in Köln wohnen. g) Liste alle Kunden des Mitarbeiters Brinks auf, außer diejenigen des Mitarbeiters Meyer, die in Köln wohnen.

QL

Datenbanken

QL

Funktionen(für(SELECT(1(Befehle! Funktionen!sind!Berechnungen,!die!auf!Felder!angewendet!werden.!Sie!lassen!sich!wie!folgt! untergliedern:! Aggregatfunktionen,!die!Werte!zusammenfassen,!z.!B.!Summen
 

Beispiele! Zählfunktion:! count()!berechnet!die!Anzahl!von!Datensätzen!oder! bestimmten!Werten!in!einer!Tabelle.!!

SELECT plz, count(*) FROM Kunde GROUP BY plz HAVING count(*) >= 10;

Gibt!man!bei!dem!Befehl!einen!Spaltennamen!an,! werden!die!Datensätze!gezählt,!bei!denen!die!Spalte! SELECT plz, count(email) nicht!NULL!ist.! FROM Kunde GROUP BY plz; SELECT sum(lagerbestand) AS Summe:! vorhandene_Monitore sum()!berechnet!die!Summe!von!Werten!einer! FROM Produkt bestimmten!Spalte.!Hier!können!Einschränkungen! WHERE kategorieID = 1; mit!where!oder!Gruppierungen!mit!group(by! vorgenommen!werden.!! Hier!ist!es!sinnvoll,!den!Spaltennamen!sum(…)(für! die!Summen!über!einen!Alias!(AS!…)!einen! brauchbaren!Namen!zu!geben.! Durchschnitt:! Die!Funktion!avg()!gibt!den!Durchschnitt!der!Werte! einer!bestimmten!Spalte!aus.! Maximum:! Die!Funktion!max()!sucht!den!höchsten!Wert!einer! bestimmten!Spalte.! Minimum:! Die!Funktion!min()!sucht!den!niedrigsten!Wert!einer! bestimmten!Spalte.!

SELECT kategorieID, sum(lagerbestand) FROM Produkt GROUP BY kategorieID; SELECT count(*), sum(lagerbestand), avg(nettopreis), max(nettopreis), min(nettopreis) FROM Produkt GROUP BY count(*)

Übungen1. Gruppiere!die!letzte!Abfrage!in!der!Tabelle!nach!der!Spalte!KategorieID.!Führe!danach!zur! besseren!Verwendung!für!jeden!Spaltennamen!Aliases!ein.! 2. Wie!groß!ist!die!höchste!Bestellmenge!in!der!Tabelle!Positionen.! 3. Wie!viel!wird!im!Durchschnitt!pro!Produkt!bestellt?! 4. Welcher!Kunde!steht!alphabetisch!am!Anfang!der!Liste?! 5. Wie!viele!Produkte!von!den!einzelnen!Herstellern!sind!im!Angebot?! ! ! ! !

Datenbanken

QL

b) Mathematische Funktionen In Abfragen können schon direkt Berechnungen durchgeführt werden Beispiele: Berechnungen: Rechnungen werden gleich in der Spalte, mit der gerechnet werden soll durchgeführt. Hier ist es wieder ratsam einen Alias zu vergeben. Sie können SQL auch als Taschenrechner benutzen! Funktionen: abs() liefert den absoluten Wert eines Wertes der angegebenen Spalte. length() gibt die Länge einer Zeichenkette an (in anderen Dialekten char_length oder strlen) mod(spaltenname,n) gibt den Rest einer Division des Wertes in der Spalte durch n an. Diese Funktion gibt es in sqlite3 leider nicht. Man kann sich aber behelfen: round(wert) rundet den Wert auf die nächste Ganzzahl round(wert,stellen) rundet den Wert auf n Stellen nach dem Komma. random() gibt eine Zufallszahl zwischen "9223372036854775808 und 9223372036854775807 zurück. Diese Zufalls zahl ist nicht entschlüsselungsresistent.

SELECT bezeichnung, nettopreis, nettopreis *1.19 AS Endpreis FROM Produkt; SELECT 6+12; SELECT abs(-19); -> liefert 19 SELECT name, length(name) from mitarbeiter; SELECT abteilung, abteilungsID FROM Abteilung where abteilungsID%2=0; SELECT round(avg(nettopreis),2) FROM Produkt; SELECT random();

Übungen 1. Die Tarifverhandlungen haben ergeben, dass alle Mitarbeiter ab sofort eine Gehaltssteigerung um 2,4 % erhalten. Aktualisiere die Gehälter in der Tabelle Verguetung. 2. Einer der Geschäftsführer der Firma hat gehört, dass Artikel, deren Bezeichnungen mehr als 17 Buchstaben lang sind, von den Kunden ungern gekauft werden. Er möchte daher wissen, welche Artikelnamen länger als 17 Zeichen lang sind. 3. Gib für jeden Kunden das bisherige Gesamtbestellvolumen auf ganze Zahlen gerundet aus, wenn es mehr als 1000 € beträgt. c) Datumsfunktionen Der Rückgabewert von Datumsfunktionen ist ein Datum oder eine Zeit. Beispiele: SELECT current_date, Aktuelle Zeit: current_date liefert das aktuelle Computerdatum, current_time; current_time die aktuelle Computerzeit. SELECT date(), time(), Entsprechendes erhält man bei Benutzung der datetime(); Funktionen date(), time() und datetime() SELECT bestellnr, gesamtbetrag Rechnen mit Datumsfunktionen: FROM Bestellung Das Rechnen mit Datumsformaten ist in sqlite3 WHERE etwas komplizierter als in anderen (strftime('%j',current_date)Datenbankdialekten: Die Funktion strftime() strftime('%j',bestelldatum))>=14 formatiert das Datumsfeld und liefert die Anzahl and rechnungsdatum is Null; der Tage seit Jahresbeginn.

Datenbanken

QL

Übungen 1. Die%Geschäftsleitung%möchte%gerne%die%Umsätze%der%Firma%nach%Monaten%aufgestellt%sehen.%Es% soll%eine%Liste%erstellt%werden,%die%für%jeden%Monat%der%vergangenen%Jahre%die%Bestellsumme% ausgibt.% 2. Der%Vertrieb%benötigt%dringend%Informationen%darüber,%wie%lange%es%von%der%Bestellung%eines% Kunden%bis%zur%Rechnungserstellung%%Ware%dauert.%Erstelle%eine%Liste,%die%die%Bestellnummer%und% die%Tage%in%absteigender%Reihenfolge%anzeigt.% % d) Zeichenkettenfunktionen Zur%Anpassung%von%Daten%verwendet%man%Funktionen,%die%Zeichenketten%zurückliefern.% SELECT vorname || ' ' || name, Verkettung:% strasse, plz, strasse Die%Zeichenverkettung%geschieht%über:% FROM Kunde; spaltenname1 || spaltenname2 Verwandlung% upper(spaltenname) lower(spaltenname)% geben%die%Zeichenketten%in%GroßQ%bzw.% Kleinschreibung%zurück.% Substrings:% Substr(Zeichenkette,Startposition)%gibt%die% restliche%Zeichenkette%ab%der%Startposition%zurück%

SELECT upper(name) FROM Kunde;

SELECT substr(name,1,3) FROM Kunde;

Substr(Zeichenkette,Startposition,n)%gibt%die% restliche%Zeichenkette%ab%der%Startposition%mit%den% nachfolgenden%n%Zeichen%zurück.% replace(Zeichenkette, von_zeichenkette, durch_zeichenkette) ersetzt%einen%bestimmten%Teil%eines%Textes%durch% die%angegebenen%Zeichen.%

SELECT replace(name,substr(name,2),'xxx xx') FROM Kunde;

ltrim(Zeichenkette) bzw.% rtrim(Zeichenkette) entfernt%vorgestellte%bzw.% nachfolgende%Leerzeichen%

SELECT rtrim(name) FROM Kunde;

Übungen 1. Die%Firma%möchte%an%ihre%Kunden%Prospekte%verteilen%lassen%und%braucht%dafür%Austrägerlisten,% die%nach%Straßenseiten%geordnet%sind.%Um%die%Sache%etwas%zu%vereinfachen,%soll%hier%einfach%nur% alle%Kunden%ausgegeben%werden,%die%eine%gerade%Hausnummer%haben.% 2. Es%soll%eine%Kurzliste%der%Mitarbeiter%der%Form%% Nachname,%Initiale,%% Email% Brinks,%K.%% % % [email protected]% Meyer,%P.% % % pMeyer@tQonline,de% erstellt%werden.%% 3. Für%eine%Umfrage%sollen%anonymisierte%Listen%mit%den%Spalten%Nachname,%Vorname,%Straße%und% Ort%erstellt%werden.%Vom%Nachnamen%des%Kunden%ist%nur%der%Anfangsbuchstabe%auszugeben,%die% restlichen%Buchstaben%sind%auszuQ“xen“,%von%der%Straße%sollen%nur%die%ersten%3%Buchstaben%und% sonst%nichts%erscheinen.%Erstelle%dazu%eine%Abfrage%dazu.%

Informatik

Joins

JOINS%% mit#SQLite3%

Die#Verknüpfung#von#Daten#über#mehrere#Tabellen#hinweg#bezeichnet#man#als#JOIN.#Grundsätzlich#gibt#es# zwei#Möglichkeiten#diese#Verknüpfung#durchzuführen:#

Problemstellung:#Die#Abteilung#„Verkauf“#benötigt#für#die#Innenrevision#eine#Aufstellung# aller#aktuellen#Bestellungen#mit#Kundennamen#und#Produktbezeichnung#in#der# # folgenden#Form:# # # #

Name%

Vorname

Bestellnr

ProduktID

Möbius# Walter#

Bezeichnung%

Anzahl

Bernd

2

2

DELL#300#

2#

Ralf#

1

3

Medion#1250#

1#

#

1. Der%Old%Style%JOIN# Die#Verknüpfung#der#zu#benutzenden#Tabellen#erfolgt#über#die#WHERETKlausel.#Dazu#werden#PrimärT#und# Fremdschlüssel#miteinander#verbunden.#Da#hier#Attribute#aus#verschiedenen#Tabellen#verknüpft#werden,# muss##im#SELECT#der#gesuchten#Spalte#zwingend#die#schon#bekannte#PunktTNotation#benutzt#werden,#um# zu#verdeutlichen,#aus#welcher#Tabelle#das#Attribut#stammt.#Zur#Vereinfachung#können#Tabellennamen#mit# einem#Alias#abgekürzt#werden.#Datenfelder,#die#über#diesen#Join#miteinander#verknüpft#werden,#müssen# über#einen#kompatiblen#Datentyp#verfügen.#Die#Spaltennamen#dürfen#verschieden#sein,#die#Datentypen# der#Attribute#müssen#jedoch#übereinstimmen.#Durch#einen#CAST()TBefehl#könnten#sie#gegebenenfalls# kompatibel#gemacht#werden.# Verknüpfung#über#eine#Spalte:# Der#Primärschlüssel#der#ersten#Tabelle#wird#mit#dem# Fremdschlüssel#der#anderen#Tabelle#in#der#WHERET Bedingung#verknüpft.# Verwendung#von#Aliasnamen:# Kürzer#lässt#sich#das#schreiben,#wenn#den# Tabellennamen#Aliases#gegeben#werden.#In#der# WHERETBedingung#können#über#AND#weitere# Bedingungen#angeknüpft#werden.# Erzwingung#einer#Kompatibilität# Angenommen#die#Kundennummer#in#der#Tabelle# wäre#vom#Typ#VARCHAR,#so#könnte#man#über#den# CASTTBefehl#eine#Umwandlung#in#INTEGER# erzwingen:#

SELECT Kunde.name, Bestellung.gesamtbetrag FROM Kunde, Bestellung WHERE Kunde.kundenID = Bestellung.kundenID; SELECT k.name, b.gesamtbetrag FROM Kunde k, Bestellung b WHERE k.kundenID = b.kundenID AND k.kundenID=2; SELECT k.name, b.gesamtbetrag FROM Kunde k, Bestellung b WHERE k.kundenID = CAST(b.kundenID AS INTEGER) AND k.kundenID=2;

1. Der%Innere%Verbund%(INNER%JOIN)%

Syntax#des#INNER%JOINS:#

SELECT spaltenliste FROM Tabellenname1 [INNER] JOIN Tabellenname2 ON Tabellenname1.spaltennameA = Tabellenname2.spaltennameA [INNER] JOIN Tabellenname3 ON Tabellenname2.spaltennameB = Tabellenname3.spaltennameB […]

Informatik

Joins

Mit SQL!92 wurde der Befehl JOIN eingeführt, der erweiterte Optionen für die Verknüpfung bereitstellte. Mit dem INNER JOIN erhält man grundsätzlich dasselbe Ergebnis wie mit der Verknüpfung über die WHERE! Bedingung. In einigen Datenbanksystemen kann der Vorsatz INNER auch weggelassen werden. Die Verknüpfung erfolgt wie zuvor über die Schlüsselfelder der beteiligten Tabellen. Diese Art JOIN wird auch CONDITTION$JOIN genannt: Verknüpfung über INNER$JOIN: Hier steht die Mastertabelle bei FROM :

SELECT k.name, k.vorname, b.gesamtbetrag FROM Kunde k INNER JOIN Bestellung b ON k.kundenID = b.kundenID;

Hier steht die abhängige Tabelle bei FROM:

SELECT m.name, m.vorname, a.abteilung FROM Mitarbeiter m INNER JOIN Abteilung a ON m.abteilungsID =a.abteilungsID; SELECT Kategorie.bezeichnung, sum(Positionen.anzahl) AS bestellt, sum(Positionen.anzahl)AS geliefert FROM Kategorie INNER JOIN Produkt ON Kategorie.kategorieID = Produkt.kategorieID INNER JOIN Positionen ON Produkt.produktID= Positionen.produktID GROUP BY Kategorie.bezeichnung; SELECT k.name, k.vorname, pr.bezeichnung FROM Kunde k INNER JOIN Bestellung b ON k.kundenID = b.kundenID INNER JOIN Positionen p ON b.bestellnr=p.bestellnr INNER JOIN Produkt pr ON p.produktID=pr.produktID ORDER BY k.name, k.vorname;

Verwendung von Gruppierungen: Da der SELECT!Befehl mit JOIN grundsätzlich wie der SELECT!Befehl ohne JOIN aufgebaut ist, können auch hier Gruppierungen verwendet werden. In diesem Join über 3 Tabellen listen wir auf, aus welchen Kategorien wie viele Artikel schon bestellt und ausgeliefert wurden.

Sortierungen: Nach wie vor sind auch Sortierungen möglich: Die Firma möchte wissen, welcher Kunde welche Produkte bestellt hat. Die Kunden sind durch ihre Kundennummer mit den Bestellungen verknüpft, die Bestellungen über die Bestellnummer mit den Positionen, die ihrerseits über die Artikelnummer mit den Artikeln verbunden ist.

Neben dem Condition$JOIN werden noch Varianten des INNER$JOIN unterschieden, die aber nicht von jedem Datenbanksystem unterstützt werden: SELECT k.name, k.vorname, Column Name$JOIN: b.gesamtbetrag Dieser Join vereinfacht die Verknüpfung, wenn die FROM Kunde k Spalten, über die die Verknüpfung erfolgen soll, INNER JOIN Bestellung b gleich benannt sind. Die Verknüpfung kann dann USING(kundenID); über das Schlüsselwort USING erfolgen. Natural JOIN: Er verknüpft über den gleichen Spaltennamen. Es ist also wichtig gleiche Spaltenbezeichnungen zu verwenden. Der Natural Join prüft nicht, ob seine Verknüpfung über den gleichen Spaltennamen auch brauchbare Ergebnisse liefert.

SELECT k.name, k.vorname, b.gesamtbetrag FROM Kunde k NATURAL JOIN Bestellung b ORDER BY k.name, k.vorname;

Informatik Self JOIN: Tabellen können auch als Self Join mit sich selbst verbunden werden. Dabei müssen die Tabellen über einen Alias umbenannt werden. Meistens werden sie einfach durchnummeriert: Cross Join: Er verbindet alle Zeilen der einen Tabelle mit allen Zeilen der anderen Tabelle:

Joins SELECT spaltenliste FROM Tabellenname alias1 INNER JOIN Tabellenname alias2; (s. Übungen) SELECT spaltenliste FROM Tabellenname1 CROSS JOIN Tabellenname2;

Übungen 1. Gib zu jedem Mitarbeiter die Bezeichnung seiner Abteilung an. 2. Gib eine Tabelle aus, die folgende Spalten enthält: Artikelbezeichnung, Artikelnettopreis, Herstellername. Sortiere die Liste zunächst nach Hersteller und anschließend nach Artikelnamen. 3. Gib eine Tabelle aus, die die Artikelbezeichnung, die Bestellmenge, die Kundennummer und Namen des Kunden enthält. Sortiere die Ausgaben nach dem Kundennamen. 4. Demonstriere die Funktionsweise des Self Joins an folgendem Beispiel: a) Erstelle eine Tabelle Ahnen mit den folgenden Spalten und gib die Beispieldaten ein name varchar(50) Vater varchar(50) Name Vater_______ Dieter Schulz Wilhelm Schulz Oliver Schulz Wilhelm Schulz Christine Messer Walter Messer Wilhelm Schulz Gustav Schulz Hermann Messer Walter Messer Gudrun Schulz Wilhelm Schulz b) Ermittle nun über einen Self Join die Geschwister.

3. Der äußere Verbund (OUTER JOIN) SELECT spaltenliste FROM Tabellenname1 Syntax des OUTER JOINS: richtung{LEFT|RIGHT} [OUTER] JOIN Tabellenname2 ON […] Manchmal möchte man Daten über Spalten miteinander verknüpfen, auch wenn es keine entsprechenden Spalteneinträge gibt: Beispiel: Die Bestellungen aller Kunden sollen aufgelistet werden, auch wenn sie noch nichts bestellt haben (sondern gerade erst den Katalog angefordert haben). Hierzu wird der OUTER JOIN benutzt, das OUTER kann meistens weggelassen werden. Sollen alle betreffenden Spalten der links vom Join angegebenen Tabelle ausgegeben werden, setzt man für die Richtung ein LEFT, sollen alle Spalten der rechts vom JOIN angegebenen Tabellen ausgegeben werden, setzt man ein RIGHT. SQLite3 unterstützt momentan nur den LEFT OUTER JOIN.

LEFT OUTER JOIN: Die Kundentabelle steht vor dem JOIN, also wird ein LEFT JOIN benutzt. Gleichzeitig wird mit dem INNER JOIN verknüpft, um sinnvolle Datensätze zu erhalten.

SELECT k.name, k.vorname, b.gesamtbetrag FROM Kunde k LEFT JOIN Bestellung b ON k.kundenID= b.kundenID;

Übungen 1. Du möchtest feststellen, ob in der Firma Produkte zu Ladenhütern wurden und listest daher für alle Produkte auf, ob sie überhaupt schon einmal bestellt wurden. 2. Verfeinere die Ausgabe in 1., indem du die jeweiligen Bestellmengen aufaddierst.

Informatik SUB"QUERYS$ $

Sub-Querys $ mit SQLite3$

Syntax der SUB"QUEREY:

SELECT spaltenliste FROM tabellenname WHERE spaltenname Vergleichsoperator ( SELECT abfrage )

Sub!Querys oder Sub!Selects sind Unterabfragen. Sie liefern eine Möglichkeit, innerhalb eines Befehls das Ergebnis einer Abfrage unmittelbar zu verwenden. Problemstellung: Die Geschäftsführung möchte wissen, welcher Mitarbeiter (Name, Vorname) im Oktober die Bestellungen mit dem höchsten Gesamtauftragsvolumen abgewickelt hat. Wir starten aber zunächst mit einer einfachen Problemstellung, die wir prinzipiell schon anders lösen können. Möchte man sich beispielsweise die Bestellung mit dem höchsten Auftragswert anzeigen lassen, so kann man statt der Abfrage: SELECT kundenID, bestelldatum, max(gesamtbetrag) FROM Bestellung auch zunächst über eine Unterabfrage den maximalen Gesamtbetrag aus der Tabelle Bestellung ermitteln und anschließend die dazugehörigen Daten selektieren: SELECT kundenID, bestelldatum, gesamtbetrag FROM Bestellung WHERE gesamtbetrag = (SELECT max(gesamtbetrag) FROM bestellung); Die Unterabfrage liefert einen Wert für den Vergleichsoperator innerhalb der WHERE!Bedingung. Folgende Varianten von Unterabfragen werden unterschieden: ! !

Unterabfragen, die einen Wert (Zeile) zurückgeben, Unterabfragen, die mehrere Werte (Zeilen) zurückgeben.

Grundsätzlich lassen sich Unterabfragen nicht nur in SELECT!Anweisungen, sondern auch in DELETE, UPDATE und INSERT!Anweisungen verwenden. Unterabfragen,$die$eine$Zeile$zurückgeben$ Diese Art von Unterabfragen erzeugt einen Selektionswert für die Hauptabfrage und liefert einen Wert oder genau eine Spalte (Feld). Daher arbeitet man mit den bekannten Vergleichsoperatoren wie =,>,>=,< oder (Select max(gesamtbetrag) from Bestellung where mitarbeiterID = 3); Während'bisher'mit'Vergleichsoperatoren'gearbeitet'wurde,'kann'man'auch'auf'Überstimmung'mit'dem' Ergebnis'einer'Unterabfrage'prüfen'und'verwendet'dazu'den'Mengenoperator'IN' select name, vorname' from Mitarbeiter where abteilungsID IN (Select abteilungsID from Abteilung where abteilung ='Personal'); Natürlich'kann'man'auch'mit''NOT#IN'prüfen,'ob'keine'Übereinstimmung'mit'der'Unterabfrage'besteht.' Schreibt'man'also'in'der'obigen'Abfrage'NOT#IN'statt'IN,'so'erhält'man'alle'komplementären' Mitarbeiternamen. Während'IN'prüft,'ob'eine'identischer'Vergleichswert'für'die'Hauptabfrage'in'der'Unterabfrage'vorhanden' ist,'prüft'EXISTS'generell'nur,'ob'ein'gültiger'Wert'in'der'Unterabfrage'für'die'formulierte'Abfrage'existiert:' '''''''

select name, vorname from Mitarbeiter where EXISTS (Select abteilungsID from Abteilung where abteilung = 'Personal');

Diese'Abfrage'liefert'alle'Mitarbeiternamen.'Mit'NOT#EXISTS'würde'man'wieder'das'komplementäre' Ergebnis'erhalten.'Da'EXISTS'nur'prüft,'ob'ein'gültiger'Vergleichswert'in'der'Unterabfrage'vorhanden'ist,'ist' es'notwendig'über'Mitarbeiter.abteilungsID = Abteilung.abteilungsID#die'Ausgabe'auf' korrespondierende'Datensätze'einzuschränken,'um'das'korrekte'Ergebnis'zu'erhalten' select M.name, M.vorname from Mitarbeiter M where EXISTS (Select A.abteilungsID from Abteilung A where A.abteilung ='Personal' and M.abteilungsID = A.abteilungsID);' # Übungen:# 3. Suche'mit'Hilfe'einer'Unterabfrage'alle'Mitarbeiter'im'Unternehmen'(Name,'Vorname),'die' gleichzeitig'auch'Kunden'im'Unternehmen'sind.' 4. Liste'alle'Bestellungsdaten'von'Kunden'auf,'die'in'Bonn'wohnen.' 5. Ermittle'alle'Mitarbeiter,'denen'ein'überdurchschnittliches'Gehalt'im'Vergleich'zum' Unternehmensdurchschnitt'gezahlt'wird.'' Hinweis:'In'der'Unterabfrage'sollte'zunächst'das'Durchschnittsgehalt'über'alle'Mitarbeiter' berechnet'werden.' 6. Setze'das'Gehalt'aller'Mitarbeiter,'die'im'Oktober'2012'eine'Bestellung'im'Gesamtwert'von' mindestens'1500'€'bearbeitet'haben'auf'die'Verguetungsgruppe'4.' 7. Löse'nun'die'Aufgabenstellung'in'der'Problemstellung'auf'Seite'1.' Hinweis:'Nutze'Aliases'für'die'zu'selektierenden'Felder.'

Informatik

Views

VIEWS mitSQLite3

MitSELECTͲBefehlenwerdenDatenauseinerodermehrerenTabelleninderDatenbankausgesuchtund ausgegeben.DabeiwirddasGesuchteentsprechenddergewünschtenSichtweisezusammengestellt. SolcheSichtweisenaufdieDatenkönnenmitdemBefehlCREATEVIEWgespeichertwerden.Gespeicherte SichtweisennenntmanVIEWS,zudeutsch=Ansichten.ViewsenthaltenkeineDaten,sonderZeiger.Die ZeigerweisenaufdieDatenbankspaltenindenBasistabellen.Viewscharakterisiertmandeshalbauchals virtuelleoderimaginäreTabellen.ModifikationenwieINSERT,DELETEundUPDATEwerdeninSQLitenicht unterstützt,hiermussmandenUmwegüberTriggergehen.   CREATE VIEW viewname [(spaltenliste)] SyntaxderVIEW:

AS SQL-Anweisung  VorteilevonVIEWS: x

VIEWSbietendieMöglichkeit,eineneingeschränktenZugriffaufInformationenderDatenbank

einzurichten. VereinfachungkomplexerDatenlogik,insbesonderebeiderZusammenführungmehrererTabellen.  Beispiel:ErstellungeinerEmaillistealsAuszugausderMitarbeitertabelle. x

CREATE VIEW v_emailliste AS SELECT Name, Vorname, Email, mitarbeiterID FROM Mitarbeiter; NachderErzeugungderViewkönnendieDatenwiebeieinernormalenTabelleabgefragtwerden:

SELECT * from v_emailliste; DerSELECTͲBefehleinderSQLͲAnweisungkannbeliebigkomplexwerden,eskönnenalsoauchJOINSund Unterabfragenverwendetwerden.IndiesemFallsolltezurÜbersichtaufjedenFalleineSpaltenliste definiertwerden.Mit

DROP VIEW v_emailliste; kanndieVIEWwiederentferntwerden. Übungen 1. ErstelleeineVIEW,diedenGesamtbetragunddenBruttobetragausderTabellebestellungausgibt. 2. ErstelleeineView,diedieGesamtumsatzsummeproKundeausderAuftragslisteerstellt. 3. ErstelleeineViewmitdemNamenv_bonner_kunden,diedieSpaltenname,vorname,strasseundplz ausderTabelleKundeundnurKundenausBonnenthält. 4. ErstelleineViewmitdemNamenv_artikelliste,diedieArtikelbezeichnung,denNettopreisundden NamedesHerstellersenthält. 5. ErstelleeineView,diealleBestellungeneinesKundenmitdenbestelltenArtikelnauflistet.DieSQLͲ AnweisungmussüberdieTabellenKunde,Bestellung,PositionenundProduktgehen. 

Informatik TRIGGER%

Trigger

% mit#SQLite3%

Das#Wort#Trigger#bedeutet#„Auslöser“.#Trigger#sind#automatisch#ablaufende#Befehle,#die#bei#einer# Speicherung#oder#Änderung#eines#Datensatzes#ausgelöst#werden.#Sie#werden#z.B.#eingesetzt,#um#während# des#Einfügens#oder#Löschens#eines#Datensatzes#Bedingungen#abzuprüfen,#die#nicht#in#der#eigentlichen# Abfrage#enthalten#sind.# CREATE TRIGGER triggername # [BEFORE|AFTER] [INSERT|UPDATE|DELETE] Syntax#des#TRIGGERS#in#SQLite3#(die# ON tabellenname Syntax#der#Trigger#ist#stark#von#dem## DBLDialekt#abhängig):# BEGIN

SQL-Anweisung ENDE; Trigger#können#durch#die#Anweisung## # DROP Trigger triggername; wieder#gelöscht#werden.# # Beispiele:## 1) In#einer#Tabelle#Statistik#der#Firma#soll#die#Anzahl#der#Artikel#und#die#Anzahl#der#Kunden#stetig# hoch#gezählt.#Wird#beispielsweise#ein#neuer#Kunde#aufgenommen,#soll#das#Feld#Kundenanzahl#in# der#Tabelle#Statistik#automatisch#erhöht#werden.# Vorgehensweise:# a) Erstellung#der#Tabelle#Statistik:# CREATE TABLE Statistik ( kundenanzahl INTEGER, artikelanzahl INTERGER ); b) Einfügen#eines#Datensatzes#der#aktuellen#Anzahl#(vorher#z.B.#selektieren)# INSERT INTO statistik(kundenanzahl,artikelanzahl) VALUES(100,50); c)###Erzeugen#eines#Triggers,#der#über#INSERTs#in#der#Tabelle#Kunde#wacht: CREATE TRIGGER upstat AFTER INSERT ON Kunde BEGIN UPDATE Statistik SET kundenanzahl = kundenanzahl +1; END; Wird#nun#ein#neuer#Datensatz#in#der#Tabelle#Kunde#aufgenommen,#so#erhöht#sich# automatisch#der#Zähler#in#der#Tabelle#Statistik.# 2) Trigger#können#neue#und#alte#Inhalte#von#Datensätze#über#die#Wörtchen#NEW#bzw.#OLD# unterscheiden.#Die#Verwendung#von#NEW#wird#erläutert.#Dazu#erzeugen#wir#eine#neue#Spalte#in#der#Tabelle# Kunde#mit#der#dienstlichen#Telefonnummer#telefon_dienstl#.#Für#die#Kunden#soll#diese#dienstliche# Telefonnummer##beim#Anlegen#eines#Datensatzes#automatisch#mit#der#privaten##Telefonnummer#vorbelegt# werden:# a)##Erzeugung#eines#Triggers: CREATE TRIGGER kunden_default_telefon AFTER INSERT ON kunde BEGIN Update kunde set telefon_dienstl = NEW.telefon where kundenID=NEW.kundeniD; END;

Informatik

Trigger

b) Einfügen eines Kunden und anschließende Überprüfung der Wirksamkeit des Triggers: INSERT INTO kunde ( kundenID, name, vorname, strasse, plz, telefon) VALUES (500, 'Meyer', 'Wilhelm', 'Wiesenweg', '59227', '069/9000008'); SELECT kundenID, name, telefon, telefon_dienstl FROM Kunde WHERE kundenID=500; Hier sollte nun die dienstliche Telefonnummer automatisch durch die private Telefonnummer ergänzt worden sein. 3) In Trigger können auch Kontrollstrukturen verwendet werden: Wenn der Artikelbestand unter den Mindestbestand fällt, soll in einer Tabelle Orders ein neuer Datensatz angelegt werden: a) Erzeugen der Tabelle Orders: CREATE TABLE Orders ( produktID INTEGER, datum DATE, bestand INTEGER ); b) Erzeugen des Triggers CREATE TRIGGER nachbestellung AFTER UPDATE ON Produkt WHEN (NEW.[lagerbestand] < OLD.[lagerbestand]) BEGIN INSERT into Orders(produktID, datum, bestand) VALUES (NEW.produktID,current_date, NEW.lagerbestand); END; c) Bestand für einen Artikel verändern: UPDATE Produkt SET lagerbestand =8 WHERE produktID=1; d) Überprüfung der Tabelle Orders:! SELECT * FROM Orders; Übungen 1. Erstelle einen Trigger downstat, der über Löschvorgänge in der Tabelle Kunde wacht und den Zähler kundenanzahl um eins erniedrigt, wenn ein Kunde gelöscht wird. 2. Erstelle einen Trigger für das Feld artikelanzahl in der Tabelle Statistik, die die Tabelle Produkt beobachten. 3. Erstelle eine Archivtabelle Kunden_archiv, in der nur der Name und der Vorname eines gelöschten Kunden zur Archivierung stehen sollen. Erstelle dann einen Trigger mit dem Namen Kunden_delete_log, der nach einer Löschung eines Kunden gleich diese alten Daten in die Archivierungstabelle schreibt. Benutze dabei den Vorsatz OLD. 4. Erstelle einen Trigger rechnungsdatum_check, der nach Einfügen des Rechnungsdatums in der Tabelle Bestellung prüft, ob das Rechnungsdatum nach dem Bestelldatum liegt. Finde eine Lösung für den Fall, dass die Eingabe fehlerhaft war. (Statt eines Triggers kann man hier auch einen CHECK bei der Tabellendefinition von Bestellung verwenden: Die Spalte rechnungsdatum wird dabei mit diesem CHECK belegt: CREATE table bestellung ( … lieferdatum DATE check(rechnungsdatum>bestelldatum)) )