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

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

Worum geht es?

In dieser Übung werden die vier zentralen DML-Befehle INSERT, SELECT, UPDATE und DELETE an einem Buchkatalog geübt (siehe Kapitel 5 - SQL-Grundlagen und Kapitel 6 - SQL DML-Funktionen). Der Expertenteil führt zu Transaktionssicherheit, UPSERT und gezieltem Löschen über Subqueries.

Was Sie dafür brauchen

• Kapitel 5 und 6.
• Ein PostgreSQL-Server und ein SQL-Editor.
• Eine Datei aufgabe12.sql.

Welche Kompetenzen Sie erwerben und zeigen

• Sie fügen Daten einzeln und mehrzeilig ein und deuten Constraint-Fehler.
• Sie lesen und filtern Daten mit WHERE, IN, LIKE und Vergleichsoperatoren.
• Sie ändern und löschen Daten gezielt mit WHERE.
• Sie sichern riskante Operationen mit Transaktionen ab und setzen UPSERT und RETURNING ein.

Pädagogische Einordnung

• Reproduktion: Daten einfügen und Constraints testen (Teil A).
• Reorganisation und Transfer: Daten filtern und ändern (Teile B und C).
• Reflexion, Problemlösung und Urteilsbildung: riskante Löschungen absichern und fortgeschrittene Muster einsetzen (Teil D).

Arbeitsaufträge

Die Übung ist auf etwa zwei Stunden ausgelegt. Teil D ist der Expertenteil.

Schema (Buchkatalog)

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)
);

Teil A - Einfügen und Constraints testen

1. Mindestens drei Autorinnen/Autoren einfügen, davon mindestens ein INSERT mit mehreren Zeilen; danach mit SELECT kontrollieren.
2. Mindestens fünf Bücher einfügen, darunter eines mit Genre 'Education', eines mit 'Non-Fiction' und eines mit isbn = '978-0-00-000003-5'.
3. Mindestens drei absichtlich fehlerhafte Inserts durchführen (doppelte email, doppelte isbn, price = -5, nicht existierende author_id) und die Fehlermeldung dokumentieren.

Teil B - Lesen (SELECT)

1. Alle Bücher; nur Titel und Preis; Bücher mit price > 20.
2. Bücher mit Genre 'Education' oder 'Non-Fiction' (mit IN).
3. Autoren, deren pen_name mit A beginnt (mit LIKE); Bücher nach 2022-12-31 und genre <> 'Thriller'.

Teil C - Ändern (UPDATE)

1. Preis des Buchs mit isbn = '978-0-00-000003-5' auf 16.90 setzen.
2. Bei einer Autorin/einem Autor country und email in einem UPDATE ändern.
3. Preis aller 'Education'-Bücher um 2.00 erhöhen; bei einem Buch published_date auf NULL setzen. Nach jedem Update mit SELECT prüfen.

Teil D - Expertenteil: Sicher löschen und fortgeschrittene Muster

1. Transaktionssicheres Massenlöschen: Alle Bücher mit price < 13.00 löschen, aber vorher absichern:

   BEGIN;
   SELECT count(*) FROM book WHERE price < 13.00;   -- check how many are affected
   DELETE FROM book WHERE price < 13.00;
   SELECT count(*) FROM book;                        -- verify the result
   ROLLBACK;   -- or COMMIT if the result is correct

   Erklären, wozu BEGIN und ROLLBACK hier dienen und was ein DELETE ohne WHERE angerichtet hätte.

2. UPSERT: Ein Buch mit einer bereits vorhandenen isbn „einfügen oder aktualisieren” mit INSERT ... ON CONFLICT:

   INSERT INTO book (author_id, title, isbn, price, genre)
   VALUES (1, 'New Edition', '978-0-00-000003-5', 18.90, 'Education')
   ON CONFLICT (isbn) DO UPDATE
     SET title = EXCLUDED.title, price = EXCLUDED.price;

   Danach zeigen, dass keine doppelte Zeile entstanden ist, sondern die bestehende aktualisiert wurde.

3. Löschen über Subquery: Alle Bücher löschen, deren Autorin/Autor aus einem bestimmten Land stammt, ohne die author_id von Hand herauszusuchen:

   DELETE FROM book
   WHERE author_id IN (SELECT author_id FROM author WHERE country = 'Austria');

4. RETURNING: Ein UPDATE oder DELETE mit RETURNING * ausführen und erklären, wozu die zurückgegebenen Zeilen nützlich sind.

5. Fremdschlüssel-Verhalten: Versuchen, eine Autorin/einen Autor zu löschen, die/der noch Bücher hat; die Fehlermeldung dokumentieren und erklären, in welcher Reihenfolge korrekt gelöscht werden muss (oder wie ON DELETE CASCADE das ändern würde).

Wissenscheck

1. Warum ist ein DELETE oder UPDATE ohne WHERE gefährlich?
2. Wozu dienen BEGIN und ROLLBACK beim Testen einer riskanten Löschung?
3. Was macht INSERT ... ON CONFLICT ... DO UPDATE?
4. Wie löscht man Zeilen abhängig von Werten einer anderen Tabelle?
5. Welcher Constraint verhindert das Löschen einer Autorin, die noch Bücher hat?

Abgabe

• Eine SQL-Datei aufgabe12.sql mit Schema, allen Statements in sinnvoller Reihenfolge, den Constraint-Tests und dem Expertenteil D, jeweils kurz kommentiert.

---

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