Aufgabe 12 - SQL DML (INSERT, SELECT, UPDATE, DELETE)

Aufgabe 12 - SQL DML (INSERT, SELECT, UPDATE, DELETE)

Worum geht es?

In dieser Übung arbeiten Sie mit den vier zentralen DML-Befehlen INSERT, SELECT, UPDATE und DELETE an einem Buchkatalog (siehe Kapitel 5 - SQL-Grundlagen und Kapitel 6 - SQL DML-Funktionen). Sie fügen Daten ein, fragen sie gefiltert ab, ändern und löschen sie gezielt.

In dieser Übung üben Sie:

• Anlegen: zwei Tabellen mit CREATE TABLE inkl. grundlegender Constraints erstellen.
• Einfügen: Daten mit INSERT INTO einzeln und mehrfach hinzufügen.
• Abfragen: Daten mit SELECT lesen und filtern (WHERE, AND/OR/NOT, LIKE, IN, Vergleichsoperatoren).
• Ändern: Daten mit UPDATE (einzelne/mehrere Zeilen, mehrere Spalten) anpassen.
• Löschen: Daten mit DELETE gezielt über WHERE entfernen und das Risiko eines fehlenden WHERE verstehen.
• Erkennen: typische Constraint-Fehler (UNIQUE, CHECK, FOREIGN KEY) deuten.

Benötigte Unterlagen

• SQL-Editor (z. B. psql, pgAdmin, DBeaver oder VSCode mit SQL-Plugin),
• Zugriff auf eine PostgreSQL-Instanz,
• eine Datei aufgabe12.sql, in der alle Befehle in sinnvoller Reihenfolge gesammelt werden.

Szenario: Buchkatalog

Eine kleine Buchhandlung verwaltet Autorinnen/Autoren und Bücher in einer Datenbank.

• Tabelle author speichert Autorendaten.
• Tabelle book speichert Bücher.

Tabellen

Erstellen Sie diese Tabellen in PostgreSQL mit den folgenden Strukturen:

CREATE TABLE author (
  author_id   SERIAL PRIMARY KEY,
  pen_name    VARCHAR(80)  NOT NULL,
  email       VARCHAR(120) NOT NULL UNIQUE,
  country     VARCHAR(60),
  birth_year  INTEGER CHECK (birth_year BETWEEN 1900 AND 2020)
);

CREATE TABLE book (
  book_id        SERIAL PRIMARY KEY,
  author_id      INTEGER NOT NULL REFERENCES author(author_id),
  title          VARCHAR(160) NOT NULL,
  isbn           VARCHAR(20)  NOT NULL UNIQUE,
  published_date DATE,
  price          NUMERIC(6,2) NOT NULL CHECK (price >= 0),
  genre          VARCHAR(40)
);

Arbeitsaufträge

Teil A - Testdaten einfügen

1. Testdaten einfügen (Autoren)

   • Fügen Sie mindestens 3 Autorinnen/Autoren ein.
   • Mindestens ein INSERT soll mehrere Zeilen auf einmal einfügen.
   • Geben Sie anschließend mit SELECT alle Autorinnen/Autoren mit author_id aus, um die Einfügungen zu kontrollieren.

2. Testdaten einfügen (Bücher)

   • Fügen Sie mindestens 5 Bücher ein.
   • Fügen Sie mindestens ein Buch mit dem Genre 'Education' und eines mit dem Genre 'Non-Fiction' ein.
   • Fügen Sie ein Buch mit der isbn '978-0-00-000003-5' ein.
   • Verwenden Sie beim Einfügen für author_id aus der Tabelle author.

3. Daten kontrollieren

   • Prüfen Sie mit SELECT, ob alle Daten korrekt eingefügt wurden.

4. Constraint-Tests (absichtlich fehlerhaft)

   Führen Sie mindestens drei der folgenden Tests durch und dokumentieren Sie den Fehler (Fehlermeldung / kurze Beschreibung):

   • UNIQUE: Versuch, einen zweiten Autor mit bereits verwendeter email einzufügen.
   • UNIQUE: Versuch, ein zweites Buch mit derselben isbn einzufügen.
   • CHECK: Versuch, ein Buch mit price = -5 einzufügen.
   • FOREIGN KEY: Versuch, ein Buch mit einer nicht existierenden author_id einzufügen (z. B. 9999).

---

Teil B - Daten lesen (SELECT)

Schreiben Sie jeweils eine SQL-Abfrage für die folgenden Anforderungen:

5. Alle Bücher anzeigen (alle Spalten).

6. Nur Titel und Preis aller Bücher anzeigen.

7. Alle Bücher mit price > 20 anzeigen.

8. Alle Bücher, deren genre entweder 'Education' oder 'Non-Fiction' ist (verwenden Sie IN).

9. Alle Autorinnen/Autoren, deren pen_name mit A beginnt (verwenden Sie LIKE).

10. Alle Bücher, die nach dem 2022-12-31 erschienen sind und genre <> 'Thriller' ist.

---

Teil C - Daten ändern (UPDATE)

11. Preis eines bestimmten Buchs ändern

• Ändern Sie den Preis des Buchs mit der isbn = '978-0-00-000003-5' auf 16.90.

12. Mehrere Spalten in einer Zeile ändern

• Ändern Sie bei einer Autorin / einem Autor Ihrer Wahl country und email in einem einzigen UPDATE.

13. Mehrere Zeilen ändern

• Erhöhen Sie den Preis aller Bücher im Genre 'Education' um 2.00.

14. NULL setzen

• Setzen Sie bei einem Buch Ihrer Wahl published_date auf NULL.

Dokumentieren Sie nach jedem Update mit einem passenden SELECT, ob die Änderung korrekt ist.

---

Teil D - Daten löschen (DELETE)

15. Ein Buch löschen

• Löschen Sie ein Buch Ihrer Wahl über die isbn.

16. Mehrere Bücher löschen

• Löschen Sie alle Bücher mit price < 13.00.

17. FOREIGN KEY-Verhalten beobachten

• Versuchen Sie, eine Autorin / einen Autor zu löschen, die/der noch Bücher in book hat.
• Dokumentieren Sie, was passiert.
• Löschen Sie anschließend zuerst die zugehörigen Bücher und danach die Autorin / den Autor.

Abgabe

• Geben Sie eine SQL-Datei aufgabe12.sql ab.
• Die Datei enthält:
  • das komplette CREATE TABLE-Schema,
  • alle INSERT/SELECT/UPDATE/DELETE-Statements in sinnvoller Reihenfolge,
  • kurze Kommentare zu den Constraint-Tests.

Qualitätskriterien

• Alle Statements sind lauffähig und reproduzierbar.
• SELECT-Abfragen beantworten die Aufgaben exakt.
• Änderungen und Löschungen sind gezielt (konsequente Verwendung von WHERE).
• Constraint-Tests sind nachvollziehbar dokumentiert.

---

Wissens-Check

• Frage 1: Warum ist ein DELETE oder UPDATE ohne WHERE-Klausel gefährlich?
• Frage 2: Was passiert beim Versuch, ein Buch mit einer nicht existierenden author_id einzufügen, und welcher Constraint greift?
• Frage 3: Worin unterscheiden sich die Operatoren IN und LIKE in einer WHERE-Klausel?

---

HTL Villach, Schuljahr 2025-2026,
https://www.htl-villach.at