💡 Aufgabe 9

Öffne die Datei in Azure Data Studio und löse die Aufgaben.

Modul 105 - Datenbanken mit SQL bearbeiten

Über dieses Script

Dieses Script wurde im Rahmen des "Moduls 105" für ICT Berufsbildung Zentralschweiz erstellt und dient als Grundlage für das erfolgreiche Absolvieren der Kompetenznachweise.

Symbole

Über den Kurs

In fünf Tagen soll ein Verständnis für die Erstellung und Bearbeitung von relationalen Datenbanken mit SQL (Structured Query Language) erlangt werden.

💡 Aufgabe 6

Öffne die Datei in Azure Data Studio und löse die Aufgaben.

Für das Lösen der DML-Aufgaben benötigte Dateien:

• Jeder hat MS SQL-Server auf seinem PC installiert.

• Jeder kennt die verfügbaren Datentypen und kann sie identifizieren.

• Jeder kennt die DDL-Befehle und kann sie anwenden.

• Jeder kann Constraints anlegen und verändern.

• Jeder kennt die DML-Befehle.

• Jeder kennt die Möglichkeiten in der Programmierung bzw. in der Befehlszeilenanwendung.

• Jeder hat die Projektarbeit erfolgreich abgeschlossen.

• Jeder hat die Umsetzungsarbeit (Kompetenznachweis) erfolgreich abgeschlossen.

• Jeder hat seine Kursbewertung im SEPHIR ausgefüllt.

Merkmale einer Transaktion

• Die Anweisungen laufen nach dem Prinzip ab: "ALLES ODER NICHTS" (vgl. ).

• Vor und nach einer Transaktion herrscht absolute Konsistenz.

• Änderungen werden erst gespeichert und für andere Benutzer sichtbar, wenn die

  Transaktion beendet ist.

• Solange die Transaktion läuft, sind alle betroffenen Daten gesperrt.

• Transaktionen sollten wegen der Sperren so kurz wie möglich dauern und möglichst

  wenig SQL-Code kapseln.

• Es besteht die Möglichkeit des Zurückrollens (Rollback), wenn in der Transaktion ein

  Fehler auftritt oder diese abbricht. Es handelt sich dabei um ein implizites Rollback in

  den Zustand vor der Transaktion.

• Für die Rückgängigmachung wird ein Logbuch geführt, auch Transaktions-Protokoll

  genannt. Dieses ist logisch aufgebaut, bestehend aus Anweisungen und nicht aus Zuständen.

Transaktionssteuerung bei SQL Server

Eine Transaktion kann mit folgenden Anweisungen gesteuert werden.

Starten einer Transaktion

• Name für bessere Übersicht optional

Savepoint erstellen

• Zurückrollen bis zum Savepoint möglich

• Transaktion und Anweisungen vor Savepoint bleiben bestehen

Transaktion abschliessen

• Alle Änderungen werden unwiderruflich gespeichert und für alle sichtbar

Transaktion zurückrollen

• Alle Änderungen werden unwiderruflich verworfen

Hier könnte deine Frage stehen!

Wie lässt sich in Azure Data Studio eine DB löschen, wenn diese noch verwendet wird?

Wie richte ich eine Connection in einem geöffneten Notebook in Azure Data Studio ein?

Bei Attach to über das Dropdown-Menü Change Connection wählen und anschliessend die gespeicherte Connection mit der entsprechenden Datenbank wählen. Danach Connect.

Beim Restore erhalte ich den Fehler, dass der DATA-Pfad nicht gefunden werden kann:

Alter DB

Spaltenname ändern

• Jeder kennt die DML-Befehle und kann sie anwenden.

• Jeder kennt den Sinn, Zweck und die Anwendung von Transaktionssteuerungen.

• Jeder kennt einfache DQL-Befehle.

• Jeder kennt die verschiedenen JOIN-Typen.

• Jeder kann mit Hilfe von DCL-Befehlen Benutzer anlegen und Rechte vergeben.

• Jeder kann spezielle Funktionen vom SQL-Server anwenden.

• Jeder absolviert erfolgreich die schriftliche Einzelprüfung.

• Jeder hat mit der Projektarbeit begonnen.

💡 Aufgabe 8

Öffne die Datei in Azure Data Studio und löse die Aufgaben.

• Jeder kann komplexere DQL-Abfragen mit Aggregatfunktionen, Unterabfragen und Mengenoperationen erstellen.

• Jeder kennt die Funktionalität von Views und kann solche im DBMS erstellen.

Datentypen SQL Server

Datentypen ORACLE

Kursunterlagen

Alle inhaltlich relevanten Informationen zum Kurs sind in diesem gitbook zu finden und nach Unterrichtstagen gruppiert.

Die Lösungen zu den Aufgaben sowie die Prüfungen sind auf Moodle zugänglich.

Moodle

Zugang auf Moodle: ​‌

SQL Cheat-Sheet

Das SQL Cheat-Sheet könnte hilfreich sein, druck es aus, wenn du möchtest.

Installation der Software

Microsoft SQL Server 2019 Express Edition

Mit dieser kostenlosen Einstiegsdatenbank kannst du mithilfe von SQL Server-Programmierfunktionen kleine datengesteuerte Anwendungen mit einer maximalen Datenträgergrösse von bis zu 10 GB erstellen, die sich editionsübergreifend skalieren lassen, wenn Datenmenge wächst.

Azure Data Studio

Das Azure Data Studio wird im Kurs verwendet, um mit dem SQL Server zu arbeiten.

Einrichten der DB-Connection

Im Azure Data Studio lässt sich eine Datenbank-Verbindung wie folgt einrichten: 1. Klick auf New Connection:

2. Im Register Saved Connections beim Feld Server den Hostnamen (Name des Geräts) eingeben. Den Authentication type bei Windows Authentication belassen und als Database master auswählen. Mit dem abschliessenden Connect wird die Verbindung gespeichert:

Views sind auf dem Server gespeicherte SELECT-Anweisungen. Mit jeder View, die man ausführt, führ man die darunterliegende Abfrage aus. Für den Anwender sieht das dann aus wie eine Tabelle, es ist aber nur eine Virtuelle Tabelle. Eine View bietet einen selektiven Einblick in Tabelleninhalte und kann dadurch eine oder mehrere Tabellen abstrahieren und somit die Komplexität vereinfachen für den Endbenutzer. Zudem kann man hier in der Rechtevergabe gezielt gestalten, sodass ein Benutzer z.B. keinen Zugriff auf eine Tabelle hat, dafür aber auf eine View, welche die Tabelle abfragt.

View Erstellen

• WITH ENCRYPTION: View-Definition ist in den Systemtabellen verschlüsselt (SQL Server).

• WITH CHECK OPTION: DML nur innerhalb der View. Das heisst, per INSERT oder UPDATE können nur Werte geschrieben werden, welche von der View auch angezeigt werden.

  Beispiel: Wenn die View ViewXY eine Abfrage mit Bedingung WHERE feld1 > 10 enthält, liefert folgende Anweisung einen Fehler: UPDATE ViewXY SET feld1=5;

View Abfragen

Eine View kann grundsätzlich wie eine Tabelle abgefragt werden:

View ändern/löschen

Gleiche Syntax wie bei CREATE VIEW bis auf die Anfangsklausel. Bei einer Änderung müssen alle Optionen wieder gesetzt werden.

Einschränkungen bei Views

Die SELECT-Anweisung der View darf folgende Elemente nicht enthalten:

• ORDER BY-Klausel

• INTO-Schlüsselwort

• Verweis auf temporäre Tabellen

Eine DML auf eine View ist nicht möglich, wenn sie folgendes enthält:

• Aggregatfunktionen

• GROUP BY

• TOP

• UNION

• DISTINCT

• Berechnete Spalten in der SELECT-Klausel

💡 Aufgabe 7

Öffne die Datei in Azure Data Studio und löse die Aufgaben.

Aufgaben zu Transaktionen

Erstelle mit dem angehängten Script die neue Datenbank "Kontoverwaltung" und führe darauf einige Transaktionen durch. Die Tabelle "TransactionLog" ist optional für schnelle Leute.

Löse folgende Aufgaben mit TRANSAKTIONEN

1. Erstelle eine Transaktion, weche zwei DML-Befehle (INSERT) enthält: 1) Eine negative Buchung auf das eine Konto (abbuchen) und 2) eine positive Buchung auf das andere Konto (gleicher Betrag, diesmal plus). Setze jeweils als Ausführungsdatum automatisch den jetzigen Moment ein (Datum / Zeit) und setze den Status auf "OK".

2. Ergänze die Transaktion mit zwei weiteren DML-Befehlen (UPDATE), welche den Kontostand (AccountBalance) der beiden Konten aktualisiert.

3. Optional: Erstelle je Transaktion einen Log-Eintrag.

Fortgeschrittene Aufgaben zu dieser Datenbank

Siehe

Basis: Datenbank "Kontoverwaltung" aus Transaktionen - Aufgaben

Erstelle eine Prozedur für Sofort-Buchungen (PROCEDURE & TRANSACTION)

1. Erstelle eine Prozedur Account_Transfer, welche als Parameter 2 Kontonummern (@FromAccount, @ToAccount), den Betrag, die Währung und eine Bemerkung akzeptiert. In einer Transaktion sollen 2 INSERTs und 2 UPDATEs ausgeführt werden: Je Konto ein INTERT auf die Tabelle Posting und ein UPDATE auf die Tabelle Account.

2. Teste die Prozedur mit EXEC Account_Transfer <Argumente...>. und prüfe die Einträge in der Tabelle Posting und Kontostände in Account vor und nachher.

Kontostände bei Buchungen automatisch aktualisieren (TRIGGER)

1. Erstelle einen TRIGGER Tr_Posting_Insert für jedes INSERT in die Tabelle Posting, welcher automatisch den neuen Kontostand für das Konto aus INSERT berechnet und aktualisiert. Die obigen UPDATE-Befehle werden also in den TRIGGER verschoben und in der Transaktion werden nur noch die zwei INSERTs (Buchungs-Datensätze) ausgeführt.

2. Teste wieder mit EXEC Account_Transfer 1, 2, 10, 'CHF', 'Otto bezahlt für Noras Zeug';

Konto-Überziehung verhindern (FUNCTION & TRIGGER)

1. Erstelle eine Funktion Is_Posting_Allowed, welche als Argument eine Konto-ID und einen Betrag akzeptiert, wenn der Betrag < 0 ist prüft, ob der Kontostand das her gibt (max. 500.- darf man überziehen) und wenn ja true zurück gibt, sonst false.

2. Erstelle einen neuen TRIGGER, der mit einem IF die Funktion Is_Posting_Allowed nutzt und entsprechend die UPDATEs der Konten ausführt ODER ein ROLLBACK der ganzen Transaktion durchführt. Hier ist ein Beispiel, welches dich unterstützen kann. Statt IF EXISTS (...) erfolgt dann dein Funktionsaufruf.

3. Teste nun wieder und vergleiche vor und nachher die Kontostände und die Tabelle Posting.

4. Optional kannst du im TRIGGER noch eine Fehlermeldung ausgeben und / oder einen Log-Eintrag machen.

Ermögliche Buchungsaufträge mit Ausführungsdatum (PROCEDURE)

1. Erweitere die Prozedur mit einem Buchungsdatum. Die erstellten Buchungsdatensätze sollen nun das künftige Datum und dem Status "Pending" erhalten.

2. Erstelle eine neue Prozedur Process_Pending_Postings (ohne Argumente), welche alle Buchungen mit Status "Pending" prüft, ob das Buchungsdatum >= dem aktuelle Datum ist und für diese Buchungen den Kontostand anpasst und den Status auf "OK" setzt.

3. Teste die neue Prozedur ebenfalls mit EXEC Process_Pending_Postings.

Datenbankprodukte

Microsoft SQL Server

SQL Server bietet gutes Laufzeitverhalten, Skalierbarkeit und Mehrnutzerbetrieb. Die Installation und der Betrieb ist eher aufwändig. Zusätzlich gibt es MSSQL als Cloud Dienst (Azure SQL Server).

SQL Server entstand aus Zusammenarbeit der Firmen Microsoft und Sybase Ende 80er und orientiert sich am Standard der aktuellen SQL-Version. Verwendet als Datenbanksprache die SQL-Variante T-SQL.

Marktführer

Oracle zusammen mit SQL Server und DB2. Ursprung des Oracle DB-Systems geht auf die Entwicklung des DB-Prototypensystem R von Edgar F. Codd im Jahre 1970 zurück.

Data Platform Map

Diskussion

💬 Wofür braucht es Datenbanken?

Datenbankbegriffe

Hauptmerkmale der Datenbanksprache SQL

• SQL ist eine Anweisungssprache

• SQL arbeitet tabellen- und mengenorientiert sowie deklarativ

• Merkmale einer Programmiersprache fehlen

• Kein Undo möglich!

• Nicht case-sensitiv Empfehlung: Schlüsselwörter in Großbuchstaben, Tabellen- und Spaltennamen in Kleinbuchstaben

• Beliebiger Einsatz von Leerzeichen, Zeilenumbrüchen und Tabulatoren

Meilensteine in der Geschichte von SQL

1974: SEQUEL: Vorläufer von SQL, von IBM für das System R entwickelt

1979: Auf Oracle folgen viele SQL-Produkte von IBM, Sybase, Ingres und anderen.

Transact-SQL (T-SQL), Dialekt von Sybase/Microsoft Mitte der 80er entwickelt: Implementation und proprietäre Ergänzung des (ANSI/ISO)-SQL-Standards, universelle Schnittstelle zu allen SQL Server-Produkten von Microsoft. Hauptabsicht dieser Entwicklung war prozedurale Konstrukte anzubieten (parallel dazu: Oracle mit PL/SQL).

1999: wichtige Überarbeitungen und Erweiterungen (z.B. if, Trigger, Prozeduren, rekursive Abfragen)

Sprachbereiche

SQL ist die Standard-Sprache und –Schnittstelle zur Definition (DDL), Zugriffsverwaltung (DCL), Manipulation (DML) und Abfrage (DQL) relationaler Datenbanken.

💡 Aufgabe 2

Löse in Moodle die Aufgabe "Datentypen identifizieren".

Datenbank anlegen

Eine neue Datenbank wird in der master-Datenbank angelegt:

USE master;

Bevor Datenbankobjekte angelegt werden können, muss das "Gefäss" dafür existieren: Die Datenbank.

CREATE DATABASE database_name;

Datenbanken lassen sich nur als Mitglied von bestimmten Gruppen bzw. als Inhaber von bestimmten Rollen anlegen. Nach dem Anlegen einer Datenbank ist diese betriebsbereit, aber noch ohne Strukturen und ohne Inhalt.

Mit USE database_name wird bestimmt, dass auf der DB-Instanz diese DB verwendet wird.

CREATE DATABASE database_name
GO
USE database_name;

Datenbank umbenennen

ALTER DATABASE database_name MODIFY Name = other_database_name;

Tabelle anlegen

Mit dem Befehl CREATE TABLE kann eine neue Tabelle erstellt werden. Der Befehl ist nach dem folgenden Schema aufgebaut:

CREATE TABLE table_name (
		spalte1 datentyp [ NULL | NOT NULL ] ,
		spalte2 datentyp [ NULL | NOT NULL ] ,
		spaltex datentyp [ NULL | NOT NULL ]
);

Beispiel:

CREATE TABLE kurse (
    kursid int NOT NULL, 
    kursname varchar(50) NOT NULL, 
    beginn smalldatetime, 
    ende smalldatetime
);

Alternative: Tabelle über SELECT anlegen

# SQL Server
SELECT * INTO neuetabelle FROM … ;

# Oracle
CREATE TABLE neuetabelle as SELECT … ;

Restriktionen bei Oracle

• Tabellenname muss mit einem Buchstaben beginnen und darf nur A-Z, a-z, 0-9, _, $ und # enthalten

• Tabellenname darf maximal 30 Zeichen lang sein

💡 Aufgabe 3

Öffne die Datei in Azure Data Studio und löse die Aufgaben.

Tabelle ändern

Spalte hinzufügen

ALTER TABLE table_name ADD spalte datentyp [NOT NULL];

Spalte ändern (z.B. Datentyp oder NOT NULL)

ALTER TABLE table_name ALTER COLUMN spalte datentyp [NOT NULL];

ALTER TABLE table_name MODIFY COLUMN spalte datentyp [NOT NULL];

Spalte löschen

ALTER TABLE table_name DROP COLUMN spalte;

Tabelle löschen

Dieser Befehl löscht die gesamte Tabellenstruktur aus der Datenbank:

DROP TABLE table_name;

Im Vergleich dazu das Löschen von Tabelleninhalt

TRUNCATE (DDL)

Löscht alle Datensätze einer Tabelle bedingungslos. Es ist eine sogenannte bulk-Operation (Massenoperation) und wird nicht protokolliert, weil es sich um ein einen DDL-Befehl handelt.

TRUNCATE TABLE table_name;

DELETE (DML)

Löscht alle Datensätze oder selektive (dazu mehr unter DML). Diese Operation wird protokolliert, da es sich um eine DML-Anweisung handelt.

DELETE FROM table_name 
[WHERE ...];

💡 Aufgabe 4

Öffne die Datei in Azure Data Studio und löse die Aufgaben.

Sicherungsvarianten

SQL Server unterstützt die folgenden Sicherungsvarianten:

Vollständige Datenbanksicherung: Enthält alle Daten einer bestimmten Datenbank zum Zeitpunkt der Sicherung. Bei einem Restore wird aus dieser Sicherung die komplette Datenbank wiederhergestellt.

Differenzielle Sicherung: Basiert auf der letzten vollständigen Sicherung und enthält nur die Daten, die sich geändert haben. Es werden nur jene Datenblöcke gesichert, die sich seit der letzten vollständigen Sicherung geändert haben. Bei einem Restore wird auch die entsprechende Vollsicherung benötigt. Voraussetzung für die differenzielle Sicherung ist also eine vollständige Datenbanksicherung.

Transaktionsprotokoll-Sicherung: Das Transaktionsprotokoll beinhaltet alle Veränderungen auf der Datenbank, die seit der letzten vollständigen, differenziellen oder Transaktionsprotokoll-Sicherung abgeschlossen wurden. Mithilfe einer Transaktionsprotokollsicherung kann der Zustand der Datenbank bei einem Ausfall bis zu diesem Zeitpunkt wiederhergestellt werden.

Sicherungsziele

Die gängigste Methode ist, Datenbanksicherungen auf einer Festplatte anzulegen. Alternativ kann auch ein Band gewählt werden. Aus Sicherheitsgründen sollten Datenbank und Sicherung auf separaten Medien platziert werden.

Sicherungsmedium

Ein Sicherungsmedium ist eine Datei, in welcher ein oder mehrere Sicherungssätze gespeichert werden können. Ein Sicherungssatz ist das Ergebnis einer vollständigen, differenziellen oder einer Transaktionsprotokoll-sicherung und wird als .bak-Datei abgelegt. Wenn der gleiche Dateiname für mehrere Sicherungen der Datenbank verwendet wird, wird die Backup-Datei mit den Standardeinstellungen nicht überschrieben, sondern der Sicherungsvorgang wird eine weitere, zusätzliche Sicherung in dieser Datei (Sicherungsmedium) speichern.

Ein Sicherungsmedium ermöglicht es, einfach auf Sicherungsziele zuzugreifen, ohne sich jedes Mal über Pfade und Speicherorte Gedanken machen zu müssen. Dadurch erhöht sich die Übersicht beim Sichern. Beim Anlegen eines solchen Mediums wird ein Ziel festgelegt, auf das in der Folge über den Namen des Mediums zugegriffen werden kann.

Wiederherstellen der Datenbank

Wiederherstellen der Datenbank von einer älteren Version

Wiederherstellungsmodelle bei SQL Server

Die Wahl der Sicherungsvariante hängt eng mit dem Wiederherstellungsmodell der Datenbank zusammen. Beim Wiederherstellungsmodell handelt es sich um eine Datenbankeigenschaft, welche festlegt, wie SQL Server die Daten zum Transaktionsprotokoll speichert und verwaltet, wenn die Transaktionen abgeschlossen sind. In SQL stehen drei Modelle zur Auswahl:

Einfach: SQL Server verwaltet das zugehörige Transaktionsprotokoll automatisch, inaktive Teile des Protokolls werden automatisch gelöscht. Das Transaktionsprotokoll wird automatisch abgeschnitten, sobald die Transaktionen sicher im dauerhaften Speicher angekommen sind. Das Transaktionsprotokoll wird somit immer klein gehalten und kann nicht unbegrenzt wachsen. Nachteil: Das Transaktionsprotokoll kann nicht extra gesichert werden. Dieses Modell wird nur bei Test- oder Entwicklungsdatenbanken empfohlen.

Vollständig (point-in-time recovery): Es werden alle Transaktionen im Protokoll gehalten und der inaktive Teil nicht gelöscht. Erst wenn die Transaktionsprotokollsicherung ausgeführt wird, wird der inaktive Teil gelöscht. Im Fall einer Wiederherstellung der Datenbank wird zuerst die DB Sicherung und danach die Transaktionsprotokollsicherung eingespielt. Denn nur mit der Transaktionsprotokollsicherung erreicht man, dass alles bis zur letzten Datenbankänderung restored wurde. Nachteil: Das Transaktionsprotokoll kann schnell ziemlich gross werden, falls nicht laufend eine Transaktionsprotokoll- Sicherung durchgeführt wird und dieses gelöscht wird. In diesem Fall läuft man Gefahr, dass die Festplatte voll und die Datenbank lahmgelegt wird. Dieses Modell wird üblicherweise bei Produktionsdatenbanken verwendet.

Massenprotokolliert: Ähnlich wie beim vollständigen Modell, das Transaktionsprotokoll wächst allerdings nicht so schnell. Diese Option wird vor allem bei Datenbanken mit vielen Massenoperationen (bulk inserts) gewählt.

Durch die ISO-Norm für SQL/PSM (Persistent Stored Modules) wurde SQL um die Mächtigkeit einer strukturierten Programmiersprache erweitert, welche sich in SQL einbetten lässt und ihrerseits wieder SQL-Anweisungen einbettet. Die Hauptobjekte dieser persistenten Module sind:

• Funktionen

• Prozeduren

• Trigger

Bei all diesen Objekten handelt es sich um eigenständige Objekte in einer Datenbank. Zudem kennt die SQL-Erweiterung Kontrollstrukturen (für die Ablaufsteuerung) sowie lokale und globale Variablen. Die nachfolgenden Ausführungen geben einen kleinen Einblick in die SQL/PSM-Konstrukte.

Variablen

Es gibt lokale und globale Variablen.

Globale Variablen sind vordefiniert und beginngn mit @@.

Ein Beispiel für eine vordefinierte globale Variabel ist @@Error: Diese wird nach jeder SQL- Anweisung mit einem Wert belegt. Jeder Wert bedeutet eine Warnung oder einen Fehler, wobei 0 "no problem" heisst. Da @@Error eine globale Variable ist, kann sie immer und überall aufgerufen werden.

Lokale Variabeln kann man selber deklarieren und beginnen mit @.

Im Gegensatz zu den globalen sind lokale Variablen nur innerhalb eines Stapels oder einer Prozedur gültig. Von aussen kann auf diese nicht zugegriffen werden. Deklariert werden sie mit DECLARE, belegt mit SET oder SELECT.

DECLARE @lokale_variable Datentyp;
SET @lokale_variable = {Ausdruck oder (Select-Anweisung)};

Prozeduren

Prozeduren funktionieren in der Regel nach dem Prinzip: Ich rufe dir etwas, gebe dir etwas mit, bekomme nichts zurück. Benutzerdefinierten Prozeduren werden beim Aufruf keine, ein oder mehrere Argumente übergeben (Werte oder Aktualparameter). Im Gegensatz zu Funktionen geben Prozeduren in den meisten Fällen keine Rückgabe aus.

Expliziter Aufruf erfolgt mit EXEC. Argumente werden ohne Klammern übergeben.

Syntax bei SQL Server:

CREATE PROCEDURE prozedur_name
    [@parameter_name Datentyp]
AS 
BEGIN
    procedure_body 
END;
    

Für Aktualparameter werden keine Klammern verlangt.

Ein allfälliger Rückgabe-Wert muss ausserhalb der Prozedur in einer (anderen) Variablen abgefangen (DECLARE) und könnte so in die weitere Verarbeitung einbezogen werden.

Benutzerdefinierte Funktionen

Neben den bereits kennengelernten vordefinierten Funktionen können auch benutzerdefinierte Funktionen angelegt werden. Wie den Prozeduren können Funktionen keine, ein oder mehrere Argumente (Aktualparameter) übergeben werden. Rückgabe der Funktion ist genau ein Wert.

Syntax bei SQL Server:

CREATE FUNCTION funktion_name
    ( [@parameter_name (Datentyp)] )
RETURNS Datentyp
AS 
BEGIN
    function_body
    RETURN (Ausdruck) 
END;

Die RETURNS Deklaration ist Pflicht, ebenso wie die RETURN Anweisung im function_body.

BEGIN und END sind optional, aber empfohlen.

Der Aufruf von Funktionen ist Bestandteil einer SELECT-Anweisung oder eines mathematischen Ausdrucks. Klammern werden immer benötigt, auch wenn keine Argumente übergeben werden.

Kontrollstrukturen

Ein Anweisungsblock wird mit BEGIN ... END definiert.

💡Aufgaben

Lade die SQL Datei herunter und löse die Aufgaben für Funktionen und Trigger:

Trigger

Beim Trigger handelt es sich um ein persistentes, prozedurales Modul, das durch eine Ereignis automatisch und unsichtbar ausgelöst ("gefeuert", "getriggert") wird. Mögliche Ereignisse sind:

• INSERT

• UPDATE

• DELETE

Trigger werden oft benutzt zur Sicherung der relationalen und sonstigen Einschränkungen (Constraints). Sie dienen oft zur Prüfung bzw. Sicherung der Integrität und Konsistenz (Stichwort prozedurale Integrität) und können auch als Hilfe zum Führen des Tagebuchs eingesetzt werden: Wer hat z.B. wann was gelöscht? Trigger werden oft eingesetzt, um Plausibilitäten zu prüfen und Geschäftsregeln durchzusetzen. Trigger feuern meistens mehrere DML-Anweisungen und laufen als Ganzes atomar.

Trigger werden nicht direkt (explizit) aufgerufen. Trigger werden keine Argumente übergeben, ebenso geben sie keine Rückgabewerte aus.

Syntax bei SQL Server:

CREATE TRIGGER trigger_name
ON {table | view}
{FOR | AFTER | INSTEAD OF} {INSERT [,] UPDATE [,] DELETE} AS
BEGIN
    Anweisungsblock 
END;

• Trigger werden durch Ereignisse (verbunden auf Tabellen und Sichten) ausgelöst.

• FOR und AFTER Trigger (Synonyme) feuern, nachdem die das Ereignis auslösende SQL-

  Anweisung ihre Wirkung vollständig getan hat.

• INSTEAD OF Trigger treten anstelle der Operation in Kraft, fangen die feuernde SQL-

  Anweisung ab und verhindern sie, legen aber eine logische Tabelle an. In Kombination mit DELETE wird die logische Tabelle "deleted" gefüllt, um zu sehen, was gelöscht werden würde. Dasselbe Prinzip gilt auf für INSERT: Hier kennt die logische Tabelle "inserted" alle Zeilen, die von INSERT (oder auch UPDATE) betroffen wären und persistiert werden wollen.

💡Aufgaben

Löse jetzt noch die Aufgaben für Trigger aus der Aufgaben-Datei, die du oben schon heruntergeladen hast.

Das Dienstprogramm sqlcmd

Um eine Verbindung zu SQL Server herzustellen, ist nicht unbedingt eine grafische Oberfläche wie das Management Studio nötig, sondern es können dafür genauso Dienstprogramme von der Kommandozeile aus benutzt werden. Ein solches Dienstprogramm ist die Befehlszeilenanwendung sqlcmd. Dieses Tool ist vor allem bei regelmässig auszuführenden Datenbank- bzw. Wartungstasks sehr nützlich und hilfreich.

sqlcmd kann in drei Modi ausgeführt werden:

SQLCMD-Modus im SSMS (SQL Server Management Studio)

In diesem Modus können über die grafische Oberfläche des SSMS entweder sqlcmd- oder T-SQL-Anweisungen eingegeben werden. Kennzeichnend ist, dass quasi vor jedem sqlcmd-Befehl ein : steht. Ein :!! bedeutet, dass ein Befehl in der Windows Command-Shell ausgeführt wird. Oft verwendete Befehle sind:

• :help zeigt Liste der Befehle

• :connect server[\instance] [-U user [-P password]] stellt Verbindung her

• :out lenkt den Output einer Anweisung in ein File z.B.

• :setvar belegt oder löscht eine sqlcmd Variable

• :exit verlässt sqlcmd unmittelbar

sqlcmd im interaktiven Modus

In diesem Modus wird ausschliesslich über die Eingabeaufforderung gearbeitet und werden vor allem ad hoc-Queries abgesetzt. Welche Parameter mit diesem Programm verwendet werden können und was diese bedeuten, kann über die Eingabeaufforderung mit -? angezeigt werden. Bei der Angabe der Parameter wird zwischen Gross- und Kleinschreibung unterschieden. Ohne Angabe eines Parameters baut sqlcmd eine vertraute Verbindung zum lokalen Server auf. Eine erfolgreich hergestellte Verbindung wird dadurch deutlich, dass eine Zeilennummer am Anfang jeder Zeile eingeblendet wird.

sqlcmd im Command-Modus

Dieser Modus bietet für einen DBA den wahrscheinlich grössten Nutzen. In diesem Modus wird ebenfalls über die Eingabeaufforderung ein kompletter Befehl abgesetzt und mit ENTER ausgeführt. So kann beispielsweise mit einem Befehl eine Verbindung zu einer Instanz hergestellt, eine Abfrage ausgeführt und deren Ergebnis in eine Datei geschrieben werden.

SQL Server-Integration in die Windows PowerShell

Windows PowerShell-Befehle können als wertvolle Ergänzung zum SQL Server Management Studio angesehen werden. Die Windows PowerShell kann nicht nur T-SQL-Befehle ausführen, sondern stellt darüber hinaus eine mächtige Umgebung zur Skripterstellung dar, in welcher eine objektorientierte, auf dem .NET Framework basierende Skriptsprache zur Verfügung gestellt wird. Ein Vorteil der PowerShell-Umgebung ist, dass mit dieser in einer Windows- Infrastruktur verschiedene Server verwaltet werden können: Windows, Exchange, SharePoint, SQL Server, etc.

Das Dienstprogramm sqlps

Damit mit der Windows PowerShell auf SQL Server zugegriffen werden kann, liefert Microsoft das Dienstprogramm sqlps mit. Bei diesem Programm handelt es sich um eine speziell für die Verwendung mit SQL Server angepasste PowerShell-Oberfläche (quasi eine PowerShell mit geladenen SQL-Erweiterungen), in welcher – ähnlich wie in einem Dateisystem – in der Verzeichnisstruktur des SQLSERVER-Laufwerks navigiert und eine bestimmte Aktion ausgeführt werden kann. Zudem kann sqlps für Folgendes verwendet werden:

• - Interaktives Ausführen von Windows PowerShell-Befehlen

• - Ausführen von Windows PowerShell-Skriptdateien

• - Ausführen von SQL Server-Cmdlets

Cmdlets: vordefinierte PowerShell-Skripts

Da in der PowerShell kein entsprechender Laufwerksbefehl zum Anzeigen eines Tabelleninhalts resp. überhaupt zum Ausführen von SQL-Befehlen existiert, muss hierfür ein sogenanntes SQL Server-Cmdlet verwendet werden: in diesem Fall das Invoke-Sqlcmd. Dieses spezifische Cmdlet ruft das Dienstprogramm sqlcmd auf und dient dazu, SQL-Anweisungen auszuführen. Das Cmdlet wird in der Regel vor die SQL- Direktive gesetzt. Cmdlets werden mit dem sqlps-Module geladen und können als vordefinierte Skripte angesehen werden, die einem bestimmten Zweck dienen. Neben dem Invoke-Sqlcmd gibt es noch eine Reihe weiterer Cmdlets wie z.B.:

• Get-Command

• Get-Help

• Backup-SqlDatabase

• Restore-SqlDatabase

• Get-SqlCredential

• Get-SqlDatabase

💡Aufgaben

--------------------------------------------------------
-- SQLCMD-Modus im SSMS: siehe Datei SQLCMD_SSMS.sql
--------------------------------------------------------

--------------------------------------------------------
-- interaktiver Modus: vor allem fuer ad hoc Queries
--------------------------------------------------------
-- Anzeige der Hilfe
sqlcmd -?

-- Vertraute Verbindung zum lokalen Server
sqlcmd

USE northwind;

SELECT * FROM employees;

-- Die Ausfuehrung der Anweisung wird ueber GO + Enter gestartet
GO[n] Die Anweisung wird n-mal ausgefuehrt

/* Aufgabe 
Verbindung aufbauen (SQL Server Authentifizierung) 
und alle Kundennamen mit Angabe von Ort und Land in ein Text-File ausgeben
*/

--------------------------------------------------------
-- Command Modus: kompletter Befehl wird abgesetzt und mit ENTER ausgefuehrt
--------------------------------------------------------
-- Beispiele
sqlcmd -S RRJ -d Northwind -Q "SELECT FirstName, LastName FROM dbo.employees"

sqlcmd -S RRJ -d Northwind -i G:\Documents\SQL\Queries\EmployeeQuery.sql -o G:\Documents\SQL\Output\Employees.txt

-- Beispiel fuer SQL-File mit Variabeln
SELECT FirstName, LastName 
FROM dbo.employees 
WHERE employeeID > '$(id)'
  AND city = '$(city)'

sqlcmd -S RRJ -d Northwind -i G:\Documents\SQL\Queries\EmployeeQuery_V.sql -v id="2" city="London" -o G:\Documents\SQL\Output\Employees.txt

/* Aufgabe 
Aufgabe von oben in Command-Modus schreiben
*/

--------------------------------------------------------
-- Windows PowerShell:
--------------------------------------------------------
-- Powershell ISE als grafische Oberflaeche mit mehr Funktionen und fuer komfortableres Arbeiten mit der Skriptsprache
-- Als Einstieg beide Oberflaechen oeffnen und Beispielbefehle absetzen:
-- Pruefen, ob das Module vorhanden ist
Get-Module -ListAvailable -Name Sqlps;
-- Sqlps-Module importieren. 
Import-Module Sqlps -DisableNameChecking;
-- Hierfuer muessen auf dem System Skripte ausgefuehrt werden duerfen.
-- Dies wuerde wie folgt aktiviert werden:
Set-ExecutionPolicy -ExecutionPolicy RemoteSigned
Set-ExecutionPolicy Undefined

-- Im folgenden wird PowerShell aus SSMS gestartet und verwendet.
-- Im Kontext von SSMS ist dieses Modul automatisch geladen.
-- PowerShell parallel dazu aus SSMS starten
Geladene Module: get-module
Verfuegbare Module: get-module -ListAvailable -Name SQL*
Cmdlets: get-command -module SQLSERVER

get-psdrive listet die Rechner-Bereiche (Laufwerke) wie in einem Dateisystem auf:
Teile der Registry, physische Laufwerke, Zertifikate, SQL Server, etc.


mit get-psdrive SQLSERVER-Laufwerk durchlaufen zu
Die Datenbank Northwind in NW umbenennen und zurueck:
PS SQLSERVER:\SQL\RRJ\DEFAULT\Databases>
ren Northwind NW
ren NW Northwind


Tabellen anschauen:
PS SQLSERVER:\SQL\RRJ\DEFAULT\Databases\Northwind\tables>
cd Northwind\Tables
dir

Tabelleninhalt ausgeben:
PS SQLSERVER:\SQL\RRJ\DEFAULT\Databases\Northwind>
Verfuegbare Cmdlets: get-command -module SQLSERVER
Invoke-Sqlcmd -Query "SELECT * from employees;"
Invoke-Sqlcmd "SELECT lastname, firstname, email, password from employees;"
Invoke-Sqlcmd "SELECT * from categories;"
Invoke-Sqlcmd -Query "SELECT @@VERSION;"
-- Provider ist die SQL PowerShell

-- Systemzeit anzeigen
Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery;" -ServerInstance "RRJ"

-- Variable definieren und in Abfrage einbinden
$name = "Buchanan"
-- Ausgabe der Variable
write-host $name
-- Variable in Abfrage einbinden
Invoke-Sqlcmd -Query "SELECT * from employees where lastname = '$($name)';"

-- Hilfeseite mit Beispielen zu SQL-Abfragen 
Get-Help Invoke-Sqlcmd -Examples

=========================================================
-- Use Case: Datenbank sichern
-- Mit der Express-Version ist der SQL Server Agent nur eingeschraenkt verfuegbar.
-- Mit Powershell laesst sich eine DB trotzdem relativ einfach sichern
-- Noetig sind ein kleines Powershell-Skript und den Aufgabenplaner von Windows

-- Direktes Ausfuehren des Skripts im Verzeichnis c:\temp: .\BackupDB.ps1

-- Im Aufgabenplaner Aufgabe erstellen, im Reiter Aktionen > Neu > Programm starten waehlen,
-- ..\WindowsPowerShell\v1.0\powershell.exe waehlen und bei den Argumenten den vollen Pfad
-- inkl. Skriptname eintragen. Abschliessend Trigger (Zeitplan) definieren.

SQL Server - Berechtigungskonzept

Bei SQL Server erfolgt der Zugriff auf Datenbanken in zwei Stufen: Mit dem Server Login meldet man sich auf dem SQL Server an, mit dem Database User greift man auf eine Datenbank zu. In jeder einzelnen Datenbank, auf die ein Anwender zugreifen möchte, wird ein separater User benötigt. Dieser User wird beim Anlegen einem Login zugeordnet. So kommt ein Endanwender lediglich mit dem Login in Kontakt. Ein Passwort ist nur für das Login erforderlich. Damit in der Praxis die Zugriffsverwaltung übersichtlich bleibt und erleichtert wird, ist es ratsam, dem einem Login zugeordneten User jeweils denselben Namen zu vergeben.

Die Authentifizierung am SQL Server kann entweder über ein Windows-Konto oder über ein SQL Server-Konto erfolgen. Bei der Windows-Authentifizierung werden Windows-Domänenkonten als Logins auf dem SQL Server registriert. Diese Variante hat für den Endanwender den Vorteil, dass dieser sich keinen weiteren Kontonamen samt Passwort für die Anmeldung merken muss. Für alle anderen Anwender ausserhalb der Windows-Domäne muss ein eigenes SQL Server-Login samt Passwort erstellt werden.

Principal erstellen und einer Rolle zuweisen

-- SQL Server-Login erstellen
USE master;
CREATE LOGIN anmelde_name
WITH PASSWORD = 'passwort', CHECK_POLICY = OFF;

-- Dem Login eine Serverrolle zuweisen
ALTER SERVER ROLE rollen_name
ADD MEMBER anmelde_name;

-- Einem Login ein User zuordnen
USE mydatabase;
CREATE USER user_name FOR LOGIN anmelde_name;

-- Dem User eine Datenbankrolle zuweisen
ALTER ROLE rollen_name ADD MEMBER user_name;

Principial löschen

-- Server Login löschen
DROP LOGIN anmelde_name;

-- Datenbank User löschen
drop user user_name;

DCL - Verwalten von Berechtigungen

Objektberechtigungen erlauben den Zugriff auf Objekte innerhalb der Datenbank. Anweisungsberechtigungen werden "gewöhnlichen" Datenbankbenutzern in der Regel nicht gewährt. Sie beziehen sich nicht auf bestehende Objekte, sondern legen fest, wer Datenbankobjekte erstellen, verwalten und sichern darf.

Berechtigungen vergeben

Dies geschieht mit dem Befehl GRANT.

GRANT SELECT, INSERT, UPDATE, DELETE ON customers TO administrator;

Rechte an alle vergeben: TO PUBLIC. WITH GRANT OPTION: User darf diese Berechtigung weitergeben.

Berechtigungen entziehen

Dies geschieht mit dem Befehl REVOKE.

REVOKE bedingt dann CASCADE, wenn WITH GRANT OPTION vergeben wurde.

Berechtigungen verweigern

Mit DENY kann man Berechtigungen explizit verweigern, damit diese nicht indirekt über Rollenmitgliedschaften erlangt werden können.

💡 Aufgabe 10

Öffne die Datei in Azure Data Studio und löse die Aufgaben.

⚡️ Ausprobieren

Öffne diese Beispiel-Datenbank in einem neuen Fenster: https://www.w3schools.com/sql/trysqlserver.asp?filename=trysql_func_sqlserver_concat

Hier kannst du direkt im Browser SQL Statements ausprobieren. Probiere die Beispiele hier mit einem ⚡️-Symbol aus.

Aggregatfunktionen

Mit Aggregatfunktionen werden Werte über mehrere Datenzeilen hinweg zusammengefasst (Multiple-Row-Funktionen). NULL-Werte werden dabei ignoriert. Ohne Gruppierung: Alle Ergebniszeilen werden zu einem Wert zusammengefasst. Mit Gruppierung: Ein Ergebniswert pro Gruppe.

-- Kleinster, grösster und durchschnittlicher Wert einer Spalte
SELECT AVG(spalte1) AS Durchschnitt,
       MIN(spalte1) AS Minimum,
       MAX(spalte1) AS Maximum
FROM tabelle;

-- Anzahl Werte einer Spalte
SELECT COUNT(*) AS Anzahl
FROM tabelle;


-- Anzahl Werte einer Spalte (inkl. NULL-Werte)
SELECT COUNT(ISNULL(spalte1,'leer')) AS Anzahl
FROM tabelle;

⚡️ Ausprobieren

-- 1. Kleinster, groesster und durchschnittlicher Preis
SELECT AVG(Price) Durchschnitt,
       MIN(Price) Minimum,
       MAX(Price) Maximum
FROM Products;

-- 2. Anzahl Produkte, die teurer als 40$ sind
SELECT COUNT(Price) Anzahl
FROM Products
WHERE Price > 40;

Gruppierung

• Gruppenbildung nach einer oder mehreren Spalten

• Für jede Ausprägung wird eine Gruppe und damit eine Ergebniszeile gebildet

• Jede Spalte ohne Aggregatfunktion in der SELECT-Klausel muss in der GROUP BY-Klausel enthalten sein

-- Kleinster, grösster und durchschnittlicher Wert einer Spalte pro Gruppe
SELECT spalte1 AS Gruppe
       AVG(spalte2) AS Durchschnitt,
       MIN(spalte2) AS Minimum,
       MAX(spalte2) AS Maximum
FROM tabelle
GROUP BY spalte1;

SELECT spalte1, spalte2
       AVG(spalte3) AS Durchschnitt,
       MIN(spalte3) AS Minimum,
       MAX(spalte3) AS Maximum
FROM tabelle
GROUP BY spalte1, spalte2;

Gruppierung einschränken

Um Gruppierte Werte einzuschränken, gibt es die HAVING Klausel. Dabei gilt zu merken:

• Die WHERE-Klausel kommt vor der Gruppierung

• HAVING schränkt nach der Gruppierung ein (Selektionskriterium auf Aggregate)

SELECT  spalte2 AS Gruppe,
        AVG(spalte1) AS Durchschnitt,
        MIN(spalte1) AS Minimum,
        MAX(spalte1) AS Maximum
FROM tabelle
WHERE spalte1 > 10
GROUP BY spalte2
HAVING AVG(spalte1) > 20;

⚡️ Ausprobieren

-- 1. Kleinster, groesster und durchschnittlicher Preis pro Kategorie
SELECT AVG(Price) Durchschnitt,
       MIN(Price) Minimum,
       MAX(Price) Maximum
FROM Products
GROUP BY CategoryID;

-- 2. nur bei Durchscnitt > 25$
SELECT AVG(Price) Durchschnitt,
       MIN(Price) Minimum,
       MAX(Price) Maximum
FROM Products
GROUP BY CategoryID
HAVING AVG(Price) > 25;

Unterabfragen

Unterabfragen kommen immer dann zum Einsatz, wenn das Ergebnis einer Abfrage als Kriterium für eine andere Abfrage verwendet werden muss. Unterabfragen werden immer in runde Klammern gesetzt und stehen immer auf der rechten Seite des Vergleichsausdrucks.

SELECT spalte1, spalte2, spalte3
FROM tabelle1
WHERE spalte2 > (SELECT spalte1
                 FROM tabelle2);

Regeln für Unterabfragen

• Unterabfragen dürfen bei single-row-Vergleichsoperatoren nur eine Spalte und eine Ergebniszeile liefern (sonst kommt es zu einem Fehler)

• Bei Verwendung von IN, ANY und ALL können mehrere Ergebniszeilen geliefert werden

• Unterabfragen können auf mehreren Ebenen geschachtelt werden

• Die innere Abfrage wird immer einmal vor der äusseren Abfrage gestartet

• Ein Unterabfrage kann sich auf dieselbe Tabelle wie die Hauptabfrage oder auch auf beliebige andere Tabellen beziehen

Korrelierte Unterabfragen

Bei der korrelierten Unterabfrage wird in der Unterabfrage auf eine Spalte aus der Hauptabfrage verwiesen. Aus Performancegründen nicht immer sinnvoll (JOIN unter Umständen geeigneter).

SELECT spalte1, spalte2, spalte3
FROM tabelle1 AS a
WHERE spalte2 >	(SELECT spalte1 		
                 FROM tabelle2 AS b
                 WHERE a.spalte1 = b.spalte2); 

⚡️ Ausprobieren

-- 1. Produkte, die überdurchschnittlich teuer sind.
SELECT * FROM Products
WHERE Price > (SELECT AVG(Price) 
               FROM Products);
               
-- 2. Produkte aus den USA
SELECT * FROM Products
WHERE SupplierID IN (SELECT SupplierID 
                     FROM Suppliers 
                     WHERE Country = 'USA')
                     
-- 3. Produkte, die schon mehr als 350 mal bestellt wurden
SELECT * 
FROM Products AS p
WHERE 350 < (SELECT SUM(Quantity) 
             FROM OrderDetails AS d 
             WHERE d.ProductID = p.ProductID);

Unterabfragen in der FROM-Klausel

JOIN mit einer Unterabfrage. Die gejointe Unterabfrage kann als virtuelle Tabelle betrachtet werden und wird auch als Inline-View bezeichnet. Wichtig ist, dass die Inline-View einen Alias-Namen erhält.

SELECT spalte1, spalte2, spalte3
FROM tabelle1
INNER JOIN (SELECT spalte1, spalte2 
            FROM tabelle2) AS tabelle3 
ON tabelle1.spalte1 = tabelle3.spalte1 
WHERE tabelle1.spalte3 < tabelle3.spalte2;

Mengen-Operationen

• UNION [ALL] Verbindet mehrere SELECT-Anweisungen zu einem Gesamtergebnis ALL: Duplikate aus den verschiedenen Anweisungen werden nicht unterdrückt.

• INTERSECT Bringt alle Zeilen, die jede der SELECT-Anweisungen zurückliefert. Dies ist die Schnittmenge.

• EXCEPT Bringt alles aus der ersten SELECT-Anweisung, das in der folgenden Anweisung nicht vorkommt. Unter Oracle MINUS.

Regeln für Mengen-Operationen

• Die erste SELECT-Anweisung gibt vor:

  • Spaltennamen

  • Reihenfolge

  • Datentypen

• Spaltenanzahl und Datentypen müssen bei allen Anweisungen übereinstimmen

• ORDER BY kommt erst nach der letzten Anweisung

• WHERE-Klausel in jeder Anweisung separat

SELECT spalte1, spalte2, spalte3
FROM tabelle1
UNION
SELECT spalte1, spalte2, spalte3
FROM tabelle2;

⚡️ Ausprobieren

-- 1. Namen und Adressen von Kunden und Lieferanten
SELECT CustomerName as Name, Address
FROM Customers
UNION
SELECT SupplierName as Name, Address
FROM Suppliers;

-- 2. Stadte, in welchen Kunden leben, aber keine Lieferanten
SELECT City
FROM Customers
EXCEPT
SELECT City
FROM Suppliers;

Vordefinierte SQL-Funktionen

SQL Server stellt eine riesige Menge vordefinierter (integrierter) Funktionen zur Verfügung. Eine integrierte SQL Server-Funktion ist entweder deterministisch oder nicht deterministisch.

• Funktionen sind deterministisch, wenn sie bei jedem Aufrufen mit bestimmten Eingabewerten immer das gleiche Ergebnis zurückgeben.

• Funktionen sind nicht deterministisch, wenn sie bei jedem Aufrufen selbst mit denselben bestimmten Eingabewerten verschiedene Ergebnisse zurückliefern können.

Bei den hier behandelten Funktionen handelt es sich um sogenannte Single-Row-Funktionen (Skalar-Funktionen). Diese liefern aus einem Ausdruck einer Zeile einen Wert zurück. Im Gegensatz dazu fassen Multiple-Row-Funktionen (auch als Aggregatfunktionen bekannt) mehrere Werte aus unterschiedlichen Datensätzen zu einem Wert zusammen. Single-Row- bzw. Skalar-Funktionen werden für jede Zeile einmal separat ausgeführt und können sowohl in der SELECT- als auch in der WHERE-Klausel verwendet werden. Single- Row-Funktionen können keine, eine oder mehrere Übergabeparameter (auch Argumente genannt) besitzen. Auch hier gibt es Syntax-Unterschiede bei den Herstellern. Der gesamte Funktionsumfang ist jedoch sehr ähnlich. Funktionen lassen sich in verschiedene Typen einteilen. Oft verwendete Typen sind:

• Zeichenfolgenfunktionen

• Mathematische Funktionen

• Datums- und Uhrzeitfunktionen

• Konvertierungsfunktionen

• Systemfunktionen

Zeichenfolgenfunktionen

Zeichenfolgenfunktionen führen Operationen für den Eingabewert einer Zeichenfolge (vom Typ char oder varchar) aus und geben eine Zeichenfolge oder einen numerischen Wert zurück. Werden Argumente übergeben, die keine Zeichenfolgewerte sind, wird der Eingabetyp implizit in einen Textdatentyp konvertiert.

Mathematische Funktionen

Mathematische Funktionen führen Berechnungen basierend auf Eingabewerten aus, die als Parameter für die Funktionen bereitgestellt werden und geben einen numerischen Wert zurück.

Datums- und Uhrzeitfunktionen

Datums- und Uhrzeitfunktionen führen Operationen für Datums- und Zeiteingabewerte aus und geben eine Zeichenfolge, eine Zahlen-, Datums- oder Zeitwert zurück.

Konvertierungsfunktionen

Mittels einer Konvertierungs-Funktion kann beispielsweise ein Datumsformat explizit umgewandelt werden. Die Formatangabe wird von jeder Sprachversion und Spracheinstellung gleich interpretiert. Während die Konvertierungs-Funktion für Datumswerte bei SQL Server standardmässig CONVERT darstellt, verwendet ORACLE die Funktion TO_DATE.

Bei SQL Server existiert alternativ die Funktion CAST. Diese wird vor allem für die Konvertierung zwischen dezimalen und numerischen Werten verwendet und wird in der Regel der SQL Server-spezifischen Funktion CONVERT vorgezogen. CONVERT kommt bei Microsoft dagegen immer im Zusammenhang mit komplexeren Datums- und Uhrzeitkonvertierungen zum Einsatz. Bei ORACLE existieren neben TO_DATE() noch die Funktionen TO_CHAR() und TO_NUMBER().

Systemfunktionen

Systemfunktionen führen Operationen für den Eingabewert einer Zeichenfolge (vom Typ char oder varchar) aus und geben eine Zeichenfolge oder einen numerischen Wert zurück.

Zulässige Datentypkonvertierungen bei SQL Server

Implizite Konvertierungen sind Konvertierungen, die ohne Angabe der CAST- oder CONVERT- Funktion durchgeführt werden. Explizite Konvertierungen sind Konvertierungen, die die Angabe der CAST- oder CONVERT-Funktion erfordern. In der folgenden Abbildung werden alle expliziten und impliziten Datentypkonvertierungen aufgeführt, die für die vom SQL Server-System bereitgestellten Datentypen zulässig sind.

💡 Gruppenarbeit

Jede Gruppe bereitet einen kleinen Vortrag zu einem Funktionstyp vor und erarbeitet 4 Anwendungsbeispiele.

Anschliessend präsentiert jede Gruppe ihren Funktionstyp.

Tabellen miteinander verknüpfen

Verknüpfung von zwei Tabellen kann, muss aber nicht über die Primär-/Fremdschlüssel-Beziehung erfolgen. Die meisten Joins sind EQUI JOINS. Darin werden zwei Tabellen über übereinstimmende Spalten verknüpft. Als Join-Operator wird ein = verwendet. Beispiel: Rechnungs- und Kundentabelle werden über die Kundennummer, die in beiden Tabellen enthalten ist, verknüpft.

Die seltene Sonderform eines Joins ist der NONEQUI JOIN. Bei Nonequi Joins gibt es keine direkt aufeinander referenzierenden Spalten. Als Join-Operator wird kein = verwendet, sondern meist kommt ein BETWEEN … AND … zum Einsatz.

⚡️ Ausprobieren

Öffne diese Beispiel-Datenbank in einem neuen Fenster:

Hier kannst du direkt im Browser SQL Statements ausprobieren. Probiere die Beispiele hier mit einem ⚡️-Symbol aus.

Die JOIN Arten

Angenommen, du hast die folgenden zwei Tabellen, welche je eine Spalte haben:

Beachte, dass die Einträge 1 und 2 nur bei A, 5 und 6 nur bei B vorhanden sind. 3 und 4 gibt es bei beiden. Folgende Tabelle erklärt, wie die Ausgabe aussehen würde, wenn diese zwei Tabellen mit den verschiedenen JOIN Arten verknüpft werden:

INNER JOIN

• JOIN-Bedingung in eigener Klausel

• WHERE-Klausel wird reingehalten

• JOIN-Typ widerspiegelt sich in JOIN-Ausdruck

⚡️ Ausprobieren

JOIN über WHERE-Klausel

Es ist auch möglich, zwei Tabellen über eine einfache WHERE-Klausel zu verbinden. Dies ist aber weniger performant. Es wird vor allem bei ORACLE eingesetzt, da vor Version 9i nur diese Möglichkeit bestand.

Weitere Bedingungen werden mit AND ergänzt Tabellennamen müssen bei Namensgleichheit von Spalten angegeben werden. Tabellennamen sollten vor allen Spaltennamen stehen.

⚡️ Ausprobieren

Tabellen-Alias

Wie du in den letzten zwei Beispielen sicher bemerkt hast, muss man so ziemlich viel schreiben. Um das etwas abzukürzen, kann man den Tabellen einen Alias geben, welcher dann in dieser Abfrage verwendet werden kann.

Dadurch kann man einfacher und schneller SQL-Statements schreiben, die übersichtlicher sind und dabei passieren auch noch weniger Schreibfehler.

⚡️ Ausprobieren

Passe die vorigen ⚡️Ausprobieren Beispiele an und verwende Tabellen-Aliasse.

OUTER JOIN

Syntaxmässig sind OUTER JOINs gleich wie INNER JOINs. Einfach, dass Statt INNER da LEFT OUTER, RIGHT OUTER oder FULL OUTER steht.

⚡️ Ausprobieren

Passe die vorigen ⚡️Ausprobieren Beispiele an und verwende die verschiedenen OUTER JOIN-Arten.

JOINS mit mehr als zwei Tabellen

⚡️ Ausprobieren

Weitere JOIN Arten

• CROSS JOIN

  • Beim CROSS JOIN handelt es sich um ein Kreuzprodukt (kartesisches Produkt)

  • Es werden alle möglichen Kombinationen gebildet (jeder mit jedem)

  • In der Regel ungewollt!

  • Wird z.B. zum Generieren von Testdaten verwendet

• SELF JOIN (rekursiver JOIN)

  • JOIN einer Tabelle mit sich selbst

  • Dient zur Abbildung von Hierarchien

  • Dieselbe Tabelle zweimal mit unterschiedlichem Alias verwenden

⚡️ Ausprobieren

💡 Eigene Aufgabe

• Überlege dir eine Aufgabe für eine Abfrage in der Northwind Datenbank.

• Gebe die Aufgabe (noch ungelöst) morgen deinem Instruktor ab.

• Die Aufgaben werden dann willkürlich zum lösen an die Klasse verteilt.

Kursplan

Dieser Kurs ist auf 5 Tage ausgelegt. Ob diese Tage en Bloc oder auf zwei Wochen verteilt liegen, spielt dabei keine Rolle.

Modulidentifikation und Handlungskompetenzen

Die detaillierte Modulidentifikation inkl. beschriebenen Handlungskompetenzen kann auf der Website von ict-berufsbildung.ch nachgelesen werden.‌

Leistungsbeurteilung

⚡️ Ausprobieren

Öffne diese Beispiel-Datenbank in einem neuen Fenster: https://www.w3schools.com/sql/trysqlserver.asp?filename=trysql_func_sqlserver_concat

Hier kannst du direkt im Browser SQL Statements ausprobieren. Probiere die Beispiele hier mit einem ⚡️-Symbol aus.

SELECT-Anweisung

DQL Anweisungen werden zur Auswahl und Anzeige von Daten aus der Datenbank verwendet. Abfragen erstellen eine temporäre, speicherresidente (nicht persistente) Tabelle mit 0 oder mehreren Datenzeilen. Sie beginnen immer mit SELECT, gefolgt von der FROM-Klausel.

Mit SELECT wird ausgewählt, welche Spalten der Tabelle in welcher Reihenfolge zurückgegeben werden sollen. Mit * werden alle Spalten ausgewählt, entspricht der Spaltenreihenfolge der in der zugrundeliegenden Tabelle.

Nach FROM gibt man den Namen der Tabelle an, welche abgefragt werden soll.

SELECT spalte1, spalte2, spalte3 FROM tabelle;

SELECT * FROM tabelle;

⚡️ Ausprobieren

-- 1. Zeigt alle Eigenschaften der Kunden an
SELECT * FROM Customers;

-- 2. Zeigt nur Name und Adresse der Kunden an
SELECT CustomerName, Address FROM Customers;

Spalten-Aliasnamen

Bei einer Abfrage können mit dem optionalen Schlüsselwort AS den Spalten (oder berechneten Spalten) neue Namen gegeben werden.

SELECT spalte1 AS alias1, ausdr1 AS alias2 FROM tabelle;

⚡️ Ausprobieren

-- Beispiel mit originalem Spaltennamen
SELECT CustomerName, ContactName FROM Customers;

-- Beispiel mit neuem Namen "Zuständiger Ansprechpartner"
SELECT CustomerName as Kunde, ContactName as [Zustaendiger Ansprechpartner] 
FROM Customers;

2. Probiere auch das aus:

-- Beispiel mit berechnetem Wert
SELECT CustomerID, CustomerID + 100 as [Neue Kundennummer] FROM [Customers];

Doppelte Zeilen unterdrücken

Mit dem Schlüsselwort DISTINCT nach SELECT, werden doppelte Einträge weggelassen. Als doppelter Eintrag gelten nur, wenn die Werte in alle angezeigten Spalten gleich sind.

SELECT DISTINCT spalte1 FROM tabelle;

⚡️ Ausprobieren

-- Beispiel mit doppelten Einträgen
SELECT Country FROM Suppliers;

-- Beispiel ohne doppelte Einträge
SELECT DISTINCT Country FROM Suppliers;

Ausdrücke und Verkettung

Berechnungsausdrücke werden wie Tabellenspalten angegeben.

SELECT spalte1, ausdruck1, ausdruck2 FROM tabelle;

SELECT spalte2, (spalte1 – spalte3) * 5 AS Spaltenname FROM tabelle;

Auch Texte können verkettet werden:

SELECT spalte1 + ' zwischentext ' + spalte2 
FROM tabelle;

SELECT spalte1 || ' zwischentext ' || spalte2 
FROM tabelle;

⚡️ Ausprobieren

-- Beispiel mit berechnetem Wert und Textverkettung
SELECT 
CustomerID, 
CustomerName, 
CustomerID + 10000 AS Kundennummer,
CustomerName + ', ' + Address + ', ' + PostalCode + ' ' + City + ', ' + Country AS Anschrift
FROM Customers;

Bedingungen in Ausdrücken

Mit CASE kann in Ausdrücken nach bestimmten Bedingungen unterschieden werden, wie ein if-else in der Programmierung.

SELECT spalte1, spalte2,
CASE ausdr 
	WHEN bedingungsausdr1 THEN fall1
	WHEN bedingungsausdr2 THEN fall2
	… 
	[ELSE fallx] 
END [AS Spaltenname]
FROM tabelle

⚡️ Ausprobieren

SELECT OrderID, Quantity
FROM OrderDetails;

SELECT OrderID, Quantity,
CASE
    WHEN Quantity > 10 THEN 'Die Anzahl ist groesser als 10'
    WHEN Quantity = 10 THEN 'Die Anzahl ist 10'
    ELSE 'Die Anzahl ist kleiner als 10'
END AS QuantityText
FROM OrderDetails;

WHERE-Klausel

Schränkt die über SELECT zurückgegebenen Zeilen ein. WHERE folgt nach der FROM Klausel und besteht jeweils aus einem linken Vergleichsterm, einem Vergleichsoperator (=, >, <, >=, <=, <>, !=) und einem rechten Vergleichsterm.

...
WHERE spalte = ausdr;

Vergleichskriterien

Zahlenfelder

• Auswertung nach mathematischen Gesichtspunkten.

Textfelder

• Texte werden wie Datumswerte von einfachen Hochkommata umschlossen

• Vergleiche erfolgen von links nach rechts nach dem Alphabet

• Gross-/Kleinschreibung bei SQL Server in der Regel nicht relevant im Gegensatz zu ORACLE

Datumsfelder

• Alle Vergleichsoperatoren sind möglich

• Für gültige Vergleiche muss auf das Datumsformat geachtet werden

• Unterschiedlich bei verschiedenen Herstellern

• Numerische Datumsformate

  • '01.01.2015'

  • '01-01-2015'

• Alphanumerische Datumsformate (sprachenabhängig!)

  • '01 JANUAR 15'

  • 'JAN 1, 15'

⚡️ Ausprobieren

-- Alle Kunden aus Mexiko
SELECT * FROM Customers
WHERE Country = 'Mexico';

-- Alle Namen der Kunden aus Mexiko
SELECT CustomerName FROM Customers
WHERE Country = 'Mexico';

SELECT CustomerName FROM Customers

-- 1. Alle Kundennamen mit Anfangsbuchstaben > 'M'
SELECT * FROM Customers
WHERE CustomerName > 'M';

-- 2. Alle Produkte, die teurer als 50$ sind
SELECT * FROM Products WHERE Price > 50;

-- 3. Alle Bestellungen vor dem 15. Juli 1996
SELECT * FROM Orders WHERE OrderDate < '1996-07-15';

Mustervergleiche

Zusätzlich zu den einfachen Vergleichsoperatoren (=, >, <, >=, <=, <>, !=) gibt es auch noch etwas komplexere Operatoren. Einer davon ist LIKE.

LIKE sucht nach enthaltenem Text in einer Textspalte. Dabei gibt es verschiedene Platzhalter, die Beschreiben:

...
WHERE spalte LIKE '[ab]%';

⚡️ Ausprobieren

SELECT * FROM Customers

-- 1. Alle Kunden, die in einer Stadt leben, die mit "ber" beginnt.
SELECT * FROM Customers
WHERE City LIKE 'ber%';

-- 2. Alle Kunden, wleche an einem Ort wohnen, der mit "li" oder "la" beginnt
SELECT * FROM Customers
WHERE City LIKE 'l[ai]%';
-- oder
SELECT * FROM Customers
WHERE City LIKE 'li%' OR City LIKE 'la%';

-- 3. Kunden, welche "e" als zweiten Buchstaben im Namen haben.
SELECT * FROM Customers WHERE CustomerName LIKE '_e%';

-- 4. Kunden, deren Namen nicht mit "A" beginnt.
SELECT * FROM Customers
WHERE CustomerName LIKE '[^a]%';

Operator BETWEEN

• Für Vergleiche mit Werten in einem bestimmten Bereich

• Für Zahlen-, Datums- und Textkriterien möglich

  • … WHERE artpreis BETWEEN 10 AND 30;

  • … WHERE gebdat BETWEEN '01.01.2000' AND '30.09.2000'

  • … WHERE artbez BETWEEN 'k' AND 'mz'

• Grenzwerte sind immer mit dabei

• Keine zusätzlichen Vergleichsoperatoren nötig (wie z.B. = )

⚡️ Ausprobieren

SELECT * FROM Employees

SELECT * FROM Products

-- 1. Alle Mitarbeiter, die zwischen 1950 und 1959 geboren sind.
SELECT * FROM Employees
WHERE BirthDate BETWEEN '1950-01-01' AND '1959-12-31';

-- 2. Alle Produkte, die zwischen als 10$ und 20$ kosten.
SELECT * FROM Products WHERE Price BETWEEN 10 AND 20;

Mengenvergleich mit IN

• Vergleiche mit mehreren, explizit in runden Klammern angegebenen Werten

• Bei Verwendung von IN wird kein weiterer Operator mehr benötigt

• Gleichheit mit einem der enthaltenen Werte

• Entspricht von der Logik einer ODER-Verknüpfung

• Die Auflistung in IN kann wieder eine SELECT Abfrage sein.

⚡️ Ausprobieren

SELECT * FROM Customers

-- 1. Alle Kunden, die aus Bern oder Paris kommen
SELECT * FROM Customers
WHERE City IN ('Bern', 'Paris');

-- 2. Alle Produkte aus Italien
SELECT * 
FROM Products 
WHERE SupplierID IN (
  SELECT SupplierID 
  FROM Suppliers 
  WHERE Suppliers.Country = 'Italy'
);

Suchen von NULL-Werten

• Vergleiche mit NULL-Werten

• Wird auf Wahr / Falsch ausgewertet

• NULL in Kombination mit IS

SELECT artbez, artkat FROM artikel WHERE artpreis IS NULL;
SELECT artbez, artkat FROM artikel WHERE artpreis IS NOT NULL;

⚡️ Ausprobieren

-- 1. Alle ohne Region
SELECT * FROM Customers WHERE Region IS NULL;

-- 2. Alle mit FAX
SELECT * FROM Customers WHERE Fax IS NOT NULL;

Mehrere Kriterien

• Logische Und-Verknüpfung: AND

• Logische Oder-Verknüpfung: OR

• Jede Teilbedingung muss vollständig sein

  • Falsch: WHERE artnr = 1098 OR 1237

  • Richtig: WHERE artnr = 1098 OR artnr = 1237

• Logische Umkehrung: NOT

  • Falsch: WHERE artnr NOT = 1098

  • Richtig: WHERE NOT (artnr = 1098) WHERE NOT (artnr = 1098) AND NOT (artnr = 1237)

⚡️ Ausprobieren

• Nimm dir ein paar der vorherigen Beispiele vor und versuche sie mit NOT umzukehren.

• Nimm dir ein paar der vorherigen Beispiele vor und versuche sie mit AND oder OR zu verknüpfen.

Sortierung

• Sortierung über ORDER BY

• ORDER BY immer letzte Klausel innerhalb der Anweisung

• Sortiert wird immer am Schluss

• Sortierung nach ein oder mehreren Spalten

• Aufsteigende (ASC) und absteigende Sortierung (DESC)

• Sortierung nach Spalten- oder Aliasnamen oder durch Spaltenposition innerhalb der SELECT-Klausel

SELECT * FROM tabelle ORDER BY spalte [ASC|DESC];

⚡️ Ausprobieren

-- 1. Alle Mitarbeiter sortiert nach Alter (die Aeltesten zuerst)
SELECT * FROM Employees ORDER BY BirthDate ASC;

TOP Auswertungen

• TOP: Die "obersten" x Zeilen absolut oder in Prozent

• Reihenfolge wird durch Sortierung bestimmt

SELECT TOP anzahl [PERCENT] [WITH TIES] ...
FROM ... 
ORDER BY spalte [DESC];

SELECT ...
FROM ... 
WHERE ROWNUM <= anzahl;
ORDER BY spalte [DESC];

SELECT ...
FROM ... 
ORDER BY spalte [DESC]
LIMIT anzahl;

⚡️ Ausprobieren

-- Alle Mitarbeiter sortiert nach Alter (die Ältesten zuerst)
SELECT * FROM Employees ORDER BY BirthDate ASC

-- 1. Alle Mitarbeiter sortiert nach Alter (die Aeltesten zuerst)
SELECT TOP 3 * FROM Employees ORDER BY BirthDate ASC

Die DML Befehle werden verwendet, um den Inhalt einer Tabelle, also die Daten, zu manipulieren.

Aus der Sicht der Datenbankmaschine werden immer ganze Tupel geändert oder gelöscht. Es kann jeweils nur in einer Tabelle geändert oder gelöscht werden.

INSERT-Syntax

Alle Spalten einfügen

INSERT [INTO] tabelle
VALUES (wert1, wert2, wert3, …, wertN);

Die Anzahl und Reihenfolge der in der VALUES-Klausel angegebenen Werte muss mit der Tabellendefinition übereinstimmen. Möchte man nicht alle Werte setzen, kann man nach dem Tabellennamen die Namen der zu ändernden Spalten angeben.

Nur ausgewählte Spalten einfügen

INSERT [INTO] tabelle
(spalte2, spalte3, spalte6) VALUES (wert2, wert3, wert6);

Ausgewählte Spalten mit SET

INSERT [INTO] tabelle 
SET spalte2=wert2, spalte3=wert3, spalte6=wert6;

Aus SELECT einfügen

-- Alle Spalten
INSERT INTO tabelle1
    SELECT spalte1, spalte2, spalte3, spalte4, spalte5
    FROM tabelle2;
    
-- Ausgewählte Spalten

INSERT INTO tabelle1
    (spalte2, spalte3, spalte6)
    SELECT spalte1, spalte2, spalte3
    FROM tabelle2;

Beispiele

-- Alle Spalten setzen
INSERT INTO tabelle
VALUES (1, 'Meier', 'Ueli', NULL, '01.1.1970');

-- Selektiv Spalten setzen
INSERT INTO tabelle (spalte1, spalte2, spalte3, spalte6, spalte9)
    VALUES (1, 'Meier', 'Ueli', NULL, '01.1.1970');

-- Bulk-Insert aus anderer Tabelle
INSERT INTO tabelle1
    SELECT spalte1, spalte2, spalte3, spalte4, spalte5
    FROM tabelle2;

UPDATE-Syntax

UPDATE tabelle 
SET spalte1 = wert1, spalte2 = wert2, …, spalteN = wertN 
WHERE spalte1 = wertx

Beispiele

UPDATE tabelle
SET spalte1 = 10, spalte2 = 'Meier', spalte3 = 'Ueli'
WHERE spalte1 = 9;

UPDATE tabelle
SET spalte1 = spalte4
WHERE  spalte2 = 'N';

DELETE-Syntax

DELETE FROM tabelle
WHERE spalte1 = wert1;

Beispiele

DELETE FROM tabelle
WHERE spalte5 < '01.01.2000';

Referentielle Integrität

Wenn aus einer Anderen Tabelle ein Fremdschlüssel auf einen hier zu löschenden Datensatz zeigt, kann er nicht gelöscht werden. Um den Löschvorgang durchführen zu können, muss zuerst der Datensatz aus der anderen Tabelle gelöscht werden.

Constraints erstellen

Constraints werden optimalerweise direkt beim Anlegen der Tabelle definiert. Hier gibt es die Möglichkeit, die Constraints entweder auf Spaltenebene (bei der Spaltendefinition) oder auf Tabellenebene (nach den Spaltendefinitionen) zu erstellen.

Constraints auf Spaltenebene

Auf Tabellenebene

Hinzufügen von Constraints zu einer bereits bestehenden Tabelle

Falls die Tabelle bereits existiert, können die Constraints über ALTER auch nachträglich definiert werden.

Foreign Key

Primary Key

Primary Key (mit Namen)

Check

Check (mit Namen)

Constraint Löschen

💡 Aufgabe 5

Öffne die Datei in Azure Data Studio und löse die Aufgaben.

Die RentAMovie GmbH

Die RentAMovie GmbH verleiht Filme an diverse Kinos in der ganzen Schweiz. Um die Übersicht über alle Filme im Sortiment zu behalten, soll eine Datenbank angelegt werden. In dieser Datenbank müssen alle Filme mit dem Filmtitel, Regisseur sowie dem Erscheinungsjahr verwaltet werden können. Ausserdem muss jederzeit ersichtlich sein, welche Filme sich bei welchen Kinos befinden.

Von jedem Film gibt es mehrere DVDs im Sortiment, so dass ein Filmtitel gleichzeitig an diverse Kinos verliehen werden kann. Ein Kino kann zudem verschiedene Filme auf einmal ausleihen. Nach spätestens drei Monaten muss das Kino den ausgeliehenen Film wieder zurückgeben.

Projektaufgaben

AUFGABE 1 Erstelle die Datenbank Filmverleih, indem du diese mit dem File filmverleih_backup.bak wiederherstellst. Du stellst fest, dass bei der Tabelle Ausleihe die Primär und Fremdschlüssel fehlen und auch bei der Tabelle Inventar_Ausleihe fehlt der Fremdschlüssel, welche auf die Tabelle Ausleihe referenziert. Bitte füge diese nachträglich hinzu. (kurze Syntax)

AUFGABE 2 Bei der Tabelle Inventar-Ausleihe muss zudem gewährleistet sein, dass das Rückgabedatum gleich oder grösser als das Ausleihedatum ist, auch wenn das Rückgabedatum leer ist.

AUFGABE 3 Im Rahmen eines Inventarisierungs-Projekts wird in der Tabelle Inventar eine neue Spalte gefordert. Die neue Spalte soll Regal heissen und Text-Eingaben ermöglichen. Bitte füge die neue Spalte mit dem Standard-Wert 'nicht zugewiesen' hinzu.

AUFGABE 4 Der Einkaufsleiter der GmbH beauftragt dich, zwei neue Filme deiner Wahl in die Datenbank aufzunehmen. Überlege, in welchen Tabellen Einträge nötig sind und führe diese Einträge aus.

AUFGABE 5 Du fragst dich aus Neugier, ob in der Datenbank Regisseure erfasst sind, zu denen es keine Einträge in der Filmtabelle gibt. Wie findest du das heraus? Versuche, die Problemstellung auf zwei verschiedene Arten zu lösen.

AUFGABE 6 Die Marketingabteilung plant eine PR-Aktion und benötigt dazu eine Adressliste aller Kinos aus der Datenbank. Die Vorgaben sind: Name des Kinos in Grossbuchstaben, Kontaktname, Adresse, PLZ und Ort müssen konkateniert sein.

AUFGABE 7 Für das Verfassen eines Beitrags im Jahresbericht erlegt dir die Sekretärin des Geschäftsführers die Aufgabe auf, eine Liste zu erstellen, die aufzeigt, welche Kinos bisher wie viele Filme ausgeliehen haben. Bitte sortiere die Ausgabe nach der Anzahl absteigend.

AUFGABE 8 Der Inventarverantwortliche möchte wissen, welche Filme aktuell von welchem Kino seit wann ausgeliehen sind. Erstelle hierzu eine View, die ihn mit den notwendigen Informationen versorgt.

AUFGABE 9 Damit der Inventarverantwortliche auf, die eben erstellte View zugreifen kann, musst du für ihn einen Datenbank-Zugang bereitstellen (Login lager mit einem von dir bestimmten Passwort) und ihm anschliessend für die View Lese-Rechte erteilen. Stelle aber sicher, dass er sonst keine anderen Datenbanktabellen auslesen kann!

AUFGABE 10 Für den Fall, dass der Datenbankserver aussteigt und wichtige Daten möglicherweise verloren gehen, musst du im Auftrag des Geschäftsführers ein Backup der Datenbank erstellen. Fertige zu diesem Zweck ein SQL-Skript an, welches du einfach ausführen kannst.

AUFGABE 11 Dem Sicherheitsbeauftragten des Filmverleihs ist es ein Anliegen, dass die Adress- und Kino-Informationen in ein separates Datenbankschema ausgelagert werden. Erstelle hierzu ein neues Schema mit dem Namen cinema und verschiebe die betroffenen Tabellen in das neue Schema. Stelle zudem sicher, dass nur der dbo-User Zugriffs-Rechte auf das gesamte neue Schema hat und dem User lager sämtliche DQL- und DML-Statements in diesem Schema verweigert werden.

AUFGABE 12 Dir ist aufgefallen, dass in der Tabelle Film die Information über die Produktionsfirma fehlt. Füge die neue Spalte an zweitletzter (!) Stelle hinzu. Überlege genau, wie du dabei vorgehst.

AUFGABE 13 Im Sekretariat wurde festgestellt, dass die Einträge vom Jahr 2015 Betreffend Rückgabedatum um einen Tag nach vorne geschoben wurden.

Du wirst gebeten alle diese Änderungen wieder zu korrigieren.

AUFGABE 14 Die Ausleihe mit der InventarAusleiheID 26 hat gar nie stattgefunden. Du musst diese Ausleihung nun löschen. Überlege Dir, wo Daten überall gelöscht werden müssen und in welcher Reihefolge dies gemacht werden soll. Beachte, dass diese Löschung in einer Transaktion zuerst simuliert werden muss, bevor sie wirklich ausgeführt wird.

AUFGABE 15 Erstelle eine Liste aller Orte, welche ein Kino haben. Jeder Ort darf nur einmal aufgeführt werden, auch wenn dieser Ort mehrere Kinos zählt.

AUFGABE 16 In welchem Jahr wurden am meisten Filme gemietet. Die Ausgabe soll nur eine Spalte und eine Zeile anzeigen.

AUFGABE 17 Wie hoch ist die durchschnittliche Mietdauer? Ausgewertet werden nur diese Vermietungen, welche schon zurückgegeben wurden.

AUFGABE 18 Erstelle eine Abfrage, welche alle Filme zurückgibt. Die Ausgabe soll einen String ausgeben, welcher den Filmnamen und in Klammer (Regisseur-Team) falls nicht nur ein Regisseur am Film beteiligt war. Bitte erstelle diese Abfrage mit einem CASE.

AUFGBE 19 Du musst immer wieder eine Abfrage über die ganze Datenbank machen. Du entscheidest Dich nun eine View mit 8 Spalten und dem Namen Übersicht zu erstellen. Teste die View anschliessend

AUFGABE 20 Für die PR-Aktion von Aufgabe 6 erhältst du nun den Auftrag ein Schreiben an alle Kinos zu erstellen, in welchem du die Kunden auf die beiden neuen von dir erfassten Filme aufmerksam machen sollst. Setze dazu einen Serienbrief (Word) auf, in dem du die benötigten Daten (Adresse des Kinos, Anrede, neue Filme) direkt von der Datenbank beziehst. Wie du den Brief gestaltest und formulierst, ist ganz allein dir überlassen.

AUFGABE 21 Damit der Jahresbericht auch optisch was hergibt, möchte die Sekretärin die Liste, welche du in Aufgabe 7 für sie erstellt hast, als Diagramm in den Bericht einfliessen lassen. Nutze hierzu die Möglichkeiten von Excel, entscheide dich für das geeignete Diagramm und speichere dieses mit der entsprechenden Legende als Bild ab.